Script To Get The Max From Column Based On Other Column Values

I need a script to read in a csv file(orig.csv) and output a reformatted csv file(format.csv) The orig csv file will look like this: Time,Label,frame,slot,SSN,Board,BT,SRN,LabelF

Solution 1:

This is a Perl solution to your problem. It keeps a hash %data of the data for each label that has the highest value in for MAX_val. It also keeps a list of labels in @labels that keeps track of new labels as they are encountered, so as to keep the output in the same order as the input.

As I said in my comment, there is a line in your data that has an empty column 13. I have added code to treat this as zero, which is unnecessary if that is an error in your post.

use strict;
use warnings;

open my $orig_fh,   '<', 'orig.csv'   or die $!;
open my $format_fh, '>', 'format.csv' or die $!;

print $format_fh scalar <$orig_fh>; # Copy header line

my %data;
my @labels;

while (<$orig_fh>) {
  chomp;
  my @fields = split /,/, $_, -1;
  my ($label, $max_val) = @fields[1,12];
  if ( exists $data{$label} ) {
    my $prev_max_val = $data{$label}[12] || 0;
    $data{$label} = \@fields if $max_val and $max_val > $prev_max_val;
  }
  else {
    $data{$label} = \@fields;
    push @labels, $label;
  }
}

for my $label (@labels) {
  print $format_fh join(',', @{ $data{$label} }), "\n";
}

output

Time,Label,frame,slot,SSN,Board,BT,SRN,LabelFrame,SRNAME,LabelID,Integrity,MAX_val
2014-03-17,lableA,1,8,0,,SPUB,1,NNN,NNN,1,100%,60
2014-03-17,lableB,2,8,0,,CCCB,2,NNN02,NNN02,1,100%,59
2014-03-17,lableC,2,6,0,,SCUA,2,NNN02,NNN02,1,100%,55
2014-03-17,lableD,2,4,1,,CCCB,2,NNN02,NNN02,1,100%,59
2014-03-17,lableE,2,2,7,,CCCB,2,NNN02,NNN02,1,100%,58