CODEWITHSUNDEEP
awk
VIPIN KUMAR
VIPIN KUMAR

Reputation: 3127

How to Print corresponding row for a maximum value of last column?

I am trying to print the entire row for a max value of last column based on second last column -

input file : file1.txt

2019-01-16 08:00:00.0   test1   28848859233
2019-01-16 08:00:00.0   test2   902006478
2019-01-16 08:00:00.0   test3   5385892905
2019-01-16 08:00:00.0   test1   4194204503
2019-01-15 08:00:00.0   test1   115598553821
2019-01-15 08:00:00.0   test2   59736397346
2019-01-15 08:00:00.0   test3   5508381147
2019-01-15 08:00:00.0   test4   39377518945
2019-01-15 08:00:00.0   test5   35371907528
2019-01-14 08:00:00.0   test1   115598553811
2019-01-14 08:00:00.0   test3   5408381147
2019-01-14 08:00:00.0   test4   346377518945

Expected output - 

2019-01-15 08:00:00.0   test1   115598553821
2019-01-15 08:00:00.0   test2   59736397346
2019-01-15 08:00:00.0   test3   5508381147
2019-01-14 08:00:00.0   test4   346377518945
2019-01-15 08:00:00.0   test5   35371907528

When I tried to use it for driving column(3) and max value from desired column (4) it worked 

awk '{if (a[$3] < $4) {a[$3]=$4}} END {PROCINFO["sorted_in"] = "@ind_num_asc" ; for (i in a) {print i, a[i]}}' file1.txt
test1 115598553821
test2 59736397346
test3 5508381147
test4 346377518945
test5 35371907528

I tried below command to print entire row but didn't work - 

awk '{if (a[$3] < $4) {a[$3]=$4;b[$0]=a[$3]}} END {PROCINFO["sorted_in"] = "@ind_num_asc" ;for (i in b) {print i, b[i]}}' file1.txt
2019-01-15 08:00:00.0   test4   39377518945 39377518945
2019-01-15 08:00:00.0   test2   59736397346 59736397346
2019-01-15 08:00:00.0   test3   5508381147 5508381147
2019-01-16 08:00:00.0   test2   902006478 902006478
2019-01-14 08:00:00.0   test4   346377518945 346377518945
2019-01-15 08:00:00.0   test5   35371907528 35371907528
2019-01-15 08:00:00.0   test1   115598553821 115598553821
2019-01-16 08:00:00.0   test3   5385892905 5385892905
2019-01-16 08:00:00.0   test1   28848859233 28848859233

Upvotes: 0

Views: 130

Answers (5)

karakfa
karakfa

Reputation: 67467

with sort/awk cooperation 

$ sort -k3,3 -k4nr file | awk '!a[$3]++'

2019-01-15 08:00:00.0   test1   115598553821
2019-01-15 08:00:00.0   test2   59736397346
2019-01-15 08:00:00.0   test3   5508381147
2019-01-14 08:00:00.0   test4   346377518945
2019-01-15 08:00:00.0   test5   35371907528

Upvotes: 1

VIPIN KUMAR
VIPIN KUMAR

Reputation: 3127

I figure out the problem, I should have store the $0 on driving column(3) for array b when desired condition met (find max value from $NF save into driving column(3) a[$3]=$4) not array a of column(3) into array b with entire line. something like this - 

awk '{if (a[$3] < $4) {a[$3]=$4;b[$3]=$0}} END {PROCINFO["sorted_in"] = "@ind_num_asc" ;for (i in b) {print b[i]}}' file1.txt
2019-01-15 08:00:00.0   test1   115598553821
2019-01-15 08:00:00.0   test2   59736397346
2019-01-15 08:00:00.0   test3   5508381147
2019-01-14 08:00:00.0   test4   346377518945
2019-01-15 08:00:00.0   test5   35371907528

Upvotes: 0

Shawn
Shawn

Reputation: 52336

Non-awk solution using the always handy GNU datamash:

$ datamash -Wsf groupby 3 max 4 < example.txt | cut -f 1-4
2019-01-15  08:00:00.0  test1   115598553821
2019-01-15  08:00:00.0  test2   59736397346
2019-01-15  08:00:00.0  test3   5508381147
2019-01-14  08:00:00.0  test4   346377518945
2019-01-15  08:00:00.0  test5   35371907528

Upvotes: 1

RavinderSingh13
RavinderSingh13

Reputation: 133428

1st Solution: Could you please try following.

awk '
{
  a[$3]=$NF>a[$3]?$NF:a[$3]
  b[$3,$NF]=$1 OFS $2
}
END{
  for(i in a){
    print b[i,a[i]],i,a[i]
  }
}'   Input_file

2nd solution: Following will take care of that output sequence of $3(3rd field) will be same as per Input_file's 3rd field sequence.

awk '
!c[$3]++{
  d[++count]=$3
}
{
  a[$3]=$NF>a[$3]?$NF:a[$3]
  b[$3,$NF]=$1 OFS $2
}
END{
  for(i=1;i<=count;i++){
   print b[d[i],a[d[i]]],d[i],a[d[i]]
  }
}'  Input_file

Explanation of above code:

awk '
!c[$3]++{                                ##Checking condition if array c with index $3 of current line is coming first time in array c if this is TRUE then assign it $3 as an index current line.
  d[++count]=$3                          ##Creating an aray d whose index as count variable value which will increment each time cursor comes here and assigning value of this array d to $3 here.
}                                        ##Closing block for array c here.
{                                        ##Starting block which will execute in all the lines for Input_file.
  a[$3]=$NF>a[$3]?$NF:a[$3]              ##Creating an array named a whose value is $NF of current line if value of $NF>a[$3] else it is NOT changing.
  b[$3,$NF]=$1 OFS $2                    ##Creating an array b whose index is $3,$NF and value will be $1 OFS $2.
}                                        ##Closing block here.
END{                                     ##Starting END block of awk program here.
  for(i=1;i<=count;i++){                 ##Starting a for loop from i=1 to till value of count here.
   print b[d[i],a[d[i]]],d[i],a[d[i]]    ##Printing value of array b whose index is d[i], array a whose index is d[i] value AND value of d[i].
  }                                      ##Closing block for, for loop now.
}'  Input_file                           ##Mentioning Input_file name here.

EDIT: Adding reason why OP's try is not working.

OP's code:

awk '{if (a[$3] < $4) {a[$3]=$4;b[$0]=a[$3]}} END {PROCINFO["sorted_in"] = "@ind_num_asc" ;for (i in b) {print i, b[i]}}' file1.txt

Explanation IMHO why code is not working: Since array b's values are NEVER getting deleted or changed(whenever a 3rd column's value is lesser or greater than its previous values) so that is the reason whe you are traversing through array b then it is printing all the values of array b. We need to change value of array b whenever value for 3rd field is lesser than its previous value.

Upvotes: 1

Tyl
Tyl

Reputation: 5252

Try this please:

$ awk '!n[$3] || n[$3]<$4{n[$3]=$4;l[$3]=$0;}END{for(i in l) print l[i]}' file1.txt
2019-01-15 08:00:00.0   test1   115598553821
2019-01-15 08:00:00.0   test2   59736397346
2019-01-15 08:00:00.0   test3   5508381147
2019-01-14 08:00:00.0   test4   346377518945
2019-01-15 08:00:00.0   test5   35371907528

I moved the condition outside, for concise and for efficiency.
Also I changed the key to the value of $3 where you are using entire line as key($0).
Since you are trying to ouput the entire line, so they should be the value, and the values of column 3 should be the keys.

Upvotes: 1

Related Questions