3

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?

  • 1
    what's the values of the third column of your output? – aborruso Jan 15 at 19:27
  • @AnkitVashistha there a lot of replies. Is none good for you? – aborruso Feb 17 at 11:14
  • Is that third field of your Desired Output meant to be column number? In which case it would be 4 on the first line. – Rich Sep 15 at 16:37
1

Using Miller (https://github.com/johnkerl/miller) and running

 mlr --c2n merge-fields -a max -r "^[a-z]" -o value -k  then put '
  for (key, value in $*) {
    if (value == $value_max && key != "value_max") {
        $fieldName=key;
    }
}' then cut -f fieldName,value_max then reorder -f fieldName,value_max input.csv

you will have

col4 6
col1 4
col2 2
col3 7
| improve this answer | |
1

Using tr and datamash:

tr , '\t' < file.csv | datamash -H max 1-4 | datamash transpose

Output:

max(col1)   4
max(col2)   5
max(col3)   7
max(col4)   6

Notes:

  • The output can be cleaned up with some sed if the leading max() isn't wanted.

  • If the number of columns isn't specifically known, but is sure to be less than some large number, replace 1-4 with 1-1000, adding zeroes as needed.

  • To get an exact count replace the 4 with $(head -1 file.csv | tr , ' ' | wc -w), or (by parsing the header with POSIX shell) $(read x < file.csv; echo ${x##*l};).

    With the cleanup, and exact count the resulting uglier code would look like:

    tr , '\t' < file.csv | 
    datamash -H max 1-$(read x < file.csv; echo ${x##*l};) | 
    datamash transpose | sed 's/.*(\|)//g'
    

    Output:

    col1    4
    col2    5
    col3    7
    col4    6
    
| improve this answer | |
1

Solutions which set the initial value mx=0 will fail where all fields in the record are negative. Setting to $1 is safe and then fields can be looped like @Peter.O.

Just for fun, here is a slight awk variant iterating over the head array indices rather than creating a counter and looping

awk -F',' '
  NR==1{split($0,head,FS); next}
  {x=1; for  (h in head) if ($h>$x) x=h;print head[x], $x }
' file

Output

col4 6
col1 4
col2 2
col3 7
| improve this answer | |
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
| improve this answer | |
0

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
| improve this answer | |
  • 2
    "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
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
| improve this answer | |
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)
| improve this answer | |
0

Here's a bash solution.

#!/bin/bash
FILE=$1

OIFS=$IFS
IFS=,
# grab headers
headers=( $(head -1 $FILE) ) 
fields=${#headers[@]}

l=1
while read -a line; do
  (( l==1 )) && (( l-- )) && continue # skip header line
  maxi=0
  for((index=1;index<$fields;index++));do
    (( ${line[$maxi]} < ${line[$index]} )) && maxi=$index
  done
  echo ${headers[$maxi]} ${line[$maxi]}
done<$FILE
IFS=$OIFS

The header line is read into headers[] and l is a "first line flag" that gets unset on first iteration. Other than that, it's just a "find the max in array" function inside a fairly standard while read -a line loop.

Output from your sample CSV:

col4 6
col1 4
col2 2
col3 7

I don't think anyone has figured out yet what the third field of output was for. If it's for column number, then you'd need to just add $((maxi+1)) at the end of the echo line, thus:

  echo ${headers[$maxi]} ${line[$maxi]} $((maxi+1))

Since bash arrays are zero-indexed, adding one to maxi to make it one-indexed.

| improve this answer | |

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.