awk group by multiple columns and print max value with non-primary key

Question

i'm new to this site and trying to learn awk. i'm trying to find the maximum value of field3, grouping by field1 and print all the fields with maximum value. Field 2 contains time, that means for each item1 there is 96 values of field2,field3 and field4

input file: (comma separated)

item1,00:15,10,30
item2,00:45,20,45
item2,12:15,30,45
item1,00:30,20,56
item3,23:00,40,44
item1,12:45,50,55
item3,11:15,30,45

desired output:

item1,12:45,50,55
item2,12:15,30,45
item3,11:15,30,45

what i tried so far:

BEGIN{
FS=OFS=","}
{
if (a[$1]<$3){
   a[$1]=$3}
}
END{
for (i in a ){
print i,a[i]
}

but this only prints

item1,50
item2,30
item3,30

but i need to print the corresponding field2 and field4 with the max value as shown in the desired output. please help.

Note the output should be `item3,23:00,40,44`, since 40 is the largest 3rd column for item3. — fedorqui, May 30 '16 at 11:19
If it can occur then include lines with the same $1 and $3 but different $2 values in your input /output so we can see if you want the first or the last or all matching lines to be output. If it can't occur then state that. — Ed Morton, May 30 '16 at 12:32
@Ed Morton for same $1, $2 is different, $3 can be same. about your proposed solution it works, I just don't understand the consequence of not using the !($1 in max) block. Another thing that just came to my mind that in case group by two columns say $1 and $2 then how this !($1 in max) block and the sorting works? — foxx, May 30 '16 at 19:04
@fox 1) `for same $1, $2 is different, $3 can be same` then include that case in your sample input/output as I suggested so we can see how you want it handled (there are several options). 2) `consequence of not using the !($1 in max) block` - without it if you had input line `item4,11:15,0,45` you would get a blank line output instead of that input line (THINK about what value max[$1] has before you populate it). 3) You'd use `max[$1,$2]` and `!(($1,$2) in max)`. — Ed Morton, May 30 '16 at 19:23
i faced this blank line issue recently, but couldn't figure out why. thanks for your nice explanation. Now i know the reason, learned a good thing. — foxx, Jun 01 '16 at 04:50

fedorqui · Accepted Answer · 2016-05-30T11:38:04.253

The problem here is that you are not storing the whole line, so when you go through the final data there is no full data to print.

What you need to do is to use another array, say data[index]=full line:

BEGIN{
FS=OFS=","}
{
 if (a[$1]<$3){
   a[$1]=$3
   data[$1]=$0}       # store it here!
}
END {
   for (i in a )
       print data[i]  # print it here
}

Or as a one-liner:

$ awk 'BEGIN{FS=OFS=","} {if (a[$1]<$3) {a[$1]=$3; data[$1]=$0}} END{for (i in a) print data[i]}' file
item1,12:45,50,55
item2,12:15,30,45
item3,23:00,40,44

score 3 · Answer 2 · answered May 30 '16 at 11:36

3

With a little help of the sort command:

sort -t, -k1,1 -k3,3nr file | awk -F, '!seen[$1]++'

answered May 30 '16 at 11:36

hek2mgl

152,036
28
249
266

@EdMorton Thanks! :) Let me add something. I've recently told that the GNU `sort -u` extension is just limited in helpfulness. This case is a good example. While there is basically the `-u` flag, in most cases you need more detailed functionality for deduplicating data. While a lot GNU extension are helpful imo, at least for interactive usage, probably `sort -u` wasn't worth an extension. – hek2mgl May 30 '16 at 17:37
`-u` isn't a GNU extension, it's existed since day 1 in all `sort`s. It actually is extremely useful vs `sort | uniq`. It would be nice if you could associate it with a key though so you could do something like `sort -t, -k3,3nr -k1,1u file` and skip the pipe to awk! – Ed Morton May 30 '16 at 17:47
Damn! I thought it was, because it is imo doing too much on the one hand and too less on the other hand. While I actually sometimes wish `sort` would work like you suggested, wouldn't be `sort -t, -k3,3nr | uniq -k1,1u` more *unix* like since every tool does one good job? Actually I miss the feature more in `uniq` then in `sort`. – hek2mgl May 30 '16 at 17:53
1

my thinking is if you can sort numerically and/or in reverse on a key then why not uniquely too? Anyway, I don't see it happening and nbd. Maybe it is for the best, idk. This is the first time I've thought about it in 35 years of using the tools so I'm obviously not missing it too badly! – Ed Morton May 30 '16 at 17:59

Ed Morton · Answer 3 · 2016-05-30T12:28:53.953

To do this job robustly you need:

$ cat tst.awk
BEGIN { FS="," }
!($1 in max) {
    max[$1]  = $3
    data[$1] = $0
    keys[++numKeys] = $1
}
$3 > max[$1] {
    max[$1]  = $3
    data[$1] = $0
}
END {
    for (keyNr=1; keyNr<=numKeys; keyNr++) {
        print data[keys[keyNr]]
    }
}

$ awk -f tst.awk file
item1,12:45,50,55
item2,12:15,30,45
item3,23:00,40,44

When doing min/max calculations you should always seed your min/max value with the first value read rather than assuming it'll always be less than or greater than some arbitrary value (e.g. zero-or-null if you skip the !($1 in max) block above).

You need the keys array to preserve input order when printing the output. If you use in instead then the output order will be random.

Note that idiomatic awk syntax is simply:

<condition> { <action> }

not C-style:

{ if ( <condition> ) { <action> } }

awk group by multiple columns and print max value with non-primary key

3 Answers3

Linked

Related