2

I am trying to extract the column name with the maximum value in each row using bash script i.e., the column header value or the value from the same column in the first row. I am using the following to extract the max value from each row in a CSV file but can't find out how to print its column name along with the max value:

awk -F ',' '{max=$'$col1';for (i=1;i<=NF;i++) {if ($i > max){max=$i}};print " max: " max}' "$INPUT_PATH/tmp.csv" >>$INPUT_PATH/max1.csv

Example:

Sample CSV Data:

col1,col2,col3,col4
1,5,2,6
4,0,1,2
1,2,0,0
0,0,7,0

Desired Output:

col4 6 2
col1 4 1
col2 2 2
col3 7 3

Is there a way to do this in the above command or is there a better way to extract the desired information from the CSV file?

  • Along with the column name, how can i print the column value also i.e, if the max value is in column 5, it should print 5. – Ankit Vashistha Jul 23 '15 at 8:16
  • 3
    Sample data would be useful I think. – Sobrique Jul 23 '15 at 9:16
0

Your future self (and anyone else having to maintain the software) will thank you if you use a language like Python for this. Of course it's not going to be a one-liner, but at least it's readable Naive pseudo-code goes something like this (completely untested):

import csv
import defaultdict

with open('max1.csv') as file_handle:
    csv_reader = csv.reader(file_handle)
    headers = csv_reader.next()
    maxes = defaultdict(0) # Or negative infinity
    for values in csv_reader:
       for index in range(len(values)):
           if value > maxes[headers[index]]:
               maxes[headers[index]] = value
  • Could you please suggest the way in bash script or the changes in my awk for the same? – Ankit Vashistha Jul 23 '15 at 8:51
0

It is a bit unclear what you are asking, I assume you want to print for every row max value of the row and column header for column in wich this value is found:

BEGIN {
    FS = ",";
}
NR == 1 {
    for (i = 1; i <= NF; i++) {
        x[i] = $i;
    }
    next;
}
{
    max = $1 + 0;
    for (i = 1; i <= NF; i++) {
        if (max <= ($i + 0)) {
             v[x[i]] = $i + 0;
             max = (v[x[i]] >= max) ? v[x[i]] : max;
        }
    }

    printf("Row %d: Column(s): ", NR);
    for (i in v) {
        if (max == v[i])
            printf("%s ", i);
    }
    print "max value: " max;
}

You can save above in file.awk and run:

awk -f file.awk your input

So for given input:

col1,col2,col3,col4,col5,col6,col7,col8
-1,-2,-22,-4,-1,-2,-4,-8
-9,-3,-2,-1,-2,-4,-5,-7
0,-3,-2,-1,-10,-11,-2,-8

Output should be:

Row 2, Colums(s): col1 col5 max value: -1
Row 3, Colums(s): col4 col5 max value: -1
Row 4, Colums(s): col1 max value: 0
0

The following allows for a repeating maximum on the same line.

awk -F, 'NR==1 { split($0,head,FS); next }
         { max=0; delete a; 
           for(i=1;i<=NF;i++) if($i>=max){ max=$i; a[max]=a[max]head[i]" ("i"), " }
           print "max " max "\t" substr(a[max], 0, length(a[max])-2)
         }' file

input:

hdr A,hdr B,hdr C,hdr D,hdr E,hdr F
5,2,7,4,7,-9
1,5,4,3,2,1
1,5,9,9,5,3

output:

max 7   hdr C (3), hdr E (5)
max 5   hdr B (2)
max 9   hdr C (3), hdr D (4)
-1

The problem with CSV is it doesn't parse nicely with normal shell tools. They simply don't do it nicely. It can be done in trivial cases, but really - a scripting language is the tool for the job.

I'd be thinking more perl personally:

#!/usr/bin/env perl
use strict;
use warnings;
use Text::CSV;

my $csv = Text::CSV->new();

open ( my $input, "<", "your_file.csv" ) or die $!;
$csv->column_names( $csv->getline( $input ) );

while ( my $row = $csv->getline_hr( $input ) ) {
    my ( $highest, @rest ) = sort { $row->{$b} <=> $row->{$a} } keys %$row;
    print join( "\t", $highest, $row->{$highest} ), "\n";
}

Which if using as input:

first,second,third,fourth
1,3,4,5,
5,4,3,2,
1,1,4,1,

Will print:

fourth  5
first   5
third   4
  • 1
    "CSVs don't parse nicely" — this is completely false for basic CSV files. With the "CSV" files that MS Excel uses (with embedded newlines and commas within the double quotes), this is true, but that's not the same thing as a straightforward Comma Separated Value file. (This is why this site has different tags for csv and csv-simple.) – Wildcard Nov 22 '16 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.