Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I have a file like below which I have sorted based on the username field.

UserID score UserName
1234    200   Jack
5678    150   Jill
8543    200   Jill 
5678    100   John

I am trying to remove the rows which have the lowest score for the same usernames. So, I want to have the output as,

UserID score UserName
1234    200   Jack
8543    200   Jill 
5678    100   John
share|improve this question
    
Are the rows sorted by UserName and score? Or could the two Jill rows be in the opposite order? –  Hauke Laging Mar 13 at 16:31
    
Those 2 rows are not sorted. It is sorted only based on the username column. –  Ramesh Mar 13 at 16:32
    
Is it an option for you to make the input sorted by both columns? That would make the solution much easier (to make and to understand). –  Hauke Laging Mar 13 at 16:40
1  
What if Jill has 3 rows: do you want to keep the one highest, or remove the one lowest? –  glenn jackman Mar 13 at 16:46
    
I just need to keep the highest. –  Ramesh Mar 13 at 16:56

3 Answers 3

up vote 3 down vote accepted

The simplest approach would be to sort on the score field instead:

$ sort -nk2 file | awk '{k[$NF]=$0} END{for (i in k){print k[i]}}'
UserID score UserName
8543    200   Jill 
1234    200   Jack
5678    100   John

Or, in perl:

sort -nk2 file | perl -ane '$k{$F[$#F]}=$_; END{print "$k{$_}" for keys(%k)}'

The -a flag for perl turns on auto splitting, basically it will behave like awk, and split each line on white space, saving the fields in the array @F. The -n means process the input file line, by line.

$F[$#F] is the last element of @F, so the last field: the username. $k{$F[$#F]}=$_; saves each line in the hash %k where the keys are the usernames, overwriting whatever was there before. Since we first sort the file, this means that $k{username} will be the highest score for that username's entry. At the end, we print each line saved in %k.

share|improve this answer
    
As long as you're sorting, use -r and just print the first occurrence of each name –  Kevin Mar 13 at 16:58
    
It's difficult to give testing input in a comment thus I abused an edit. The task is to delete one, your code deletes three lines. –  Hauke Laging Mar 13 at 16:58
    
@HaukeLaging yes, the OP was not clear but he actually want to keep only the highest score (which is what I had understood in the first place) so I rolled back your edit. –  terdon Mar 13 at 17:00
    
@Kevin why is that simpler? You would still need to parse to get only one line per username. Am I missing something? –  terdon Mar 13 at 17:05
    
@RahulPatil see updated answer. –  terdon Mar 13 at 17:41

Try this:

awk 'NR==1{print $1,$2,$3}NR!=1{if($2>a[$3]){a[$3]=$2;b[$3]=$1}}
    END{for(x in a){print b[x],a[x],x}}' OFS="\t" file

UserID  score   UserName
1234    200     Jack
8543    200     Jill
5678    100     John

Or using perl:

perl -ane '$h{$F[$#F]}=[$F[$#F-1],"$_"] if $F[$#F-1] > $h{$F[$#F]}->[0];
    END{print "$h{$_}->[1]" for keys %h}' file
share|improve this answer
    
Good awk solution unless Jack or John happen to have a negative score ;-) Also no guarantees that input order will be retained –  1_CR Mar 13 at 17:17
    
Yeap, if that happened, we need one more check :). –  cuonglm Mar 13 at 17:22
    
keeping one array storing $0 would be a lot simpler –  glenn jackman Mar 13 at 17:39

An alternate without arrays:

$ awk '
seen == $NF {line = (ishigh > $2) ? line : $0; next}
line {print line}
{seen = $NF; ishigh = $2; line = $0}
END {print line}' file
UserID score UserName
1234    200   Jack
8543    200   Jill
5678    100   John
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.