Cfrtjryk

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?

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

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

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)

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