LINUX: How to AWK to find maximums in every unique line

Viewed 76

I have a file with this record
myFile.txt :

firstname lastname note 
Ben Quick 12
Fred Free 10
Mark Marked 18
Mark Marked 20
Ben Quick 8

and want to print every unique person with his maximum note
the result will be like this

firstname lastname maxNote
Ben Quick 12
Fred Free 10
Mark Marked 20

so I have this awk script

#!/bin/awk -f


BEGIN {
    print "firstname lastname maxNote"
}

NR > 1 { 
    list[$1," ",$2]++
}

END {
    for(l in list)
        print l
}

it print only unique firstname + lastname
my question is : how can I sort them by note and print the whole line of each ?

your help is appreciated !

4 Answers

With your shown samples please try following. Written and tested with GNU awk should work with any awk.

awk '
FNR==1{
  print
  next
}
match($0,/[^0-9]*/){
  val=substr($0,RSTART,RLENGTH)
  sub(/[[:space:]]+$/,"",val)
}
{
  arr[val]=(arr[val]>$NF?arr[val]:$NF)
}
END{
  for(key in arr){
    print key,arr[key]
  }
}
'  Input_file

Explanation: Adding detailed explanation for above.

awk '                                     ##Starting awk program from here.
FNR==1{                                   ##Checking condition if this is first line.
  print                                   ##printing current line here.
  next                                    ##next will skip all further statements from here.
}
match($0,/[^0-9]*/){                      ##using match function to match till any digit comes in line.
  val=substr($0,RSTART,RLENGTH)           ##Creating val which has sub string of current line, with values of RSTART and RLENGTH.
  sub(/[[:space:]]+$/,"",val)             ##Substituting spaces coming at last in val wit NULL here.
}
{
  arr[val]=(arr[val]>$NF?arr[val]:$NF)    ##Creating array with index of val and keeping only maximum value by comparing with last field os current line.
}
END{                                      ##Starting END block of this program from here.
  for(key in arr){                        ##Traversing into arr elemennts now.
    print key,arr[key]                    ##Printing key and its value here.
  }
}
'  Input_file                             ##Mentioning Input_file name here.
$ awk 'NR==1     {print; next} 
                 {k=$1 FS $2} 
       max[k]<$3 {max[k]=$3} 
       END       {for(k in max) print k, max[k]}' file

firstname lastname note
Fred Free 10
Ben Quick 12
Mark Marked 20

another approach

$ sed 1q file; sed 1d file | sort -k1,2 -k3nr | awk '!a[$1,$2]++'

firstname lastname note
Ben Quick 12
Fred Free 10
Mark Marked 20

Using any awk, sort, and cut in any shell on every Unix box:

$ awk '{print (NR>1), $0}' file | sort -k1,1n -k4,4rn | cut -d' ' -f2- | awk '!seen[$1,$2]++'
firstname lastname note
Mark Marked 20
Ben Quick 12
Fred Free 10
Related