How to remove duplicates and print only those lines that contain a string and has the highest value in a specific column

Question

For example in the below example

I want to remove the duplicate ids from the second column and print only those ids that have the highest value in the third column

MS1199089 PT01000001.1.1 0.63 16.4 0.7 -   
MS0068379 PT01000007.1.1 0.96 13.9 0.4 core  
MS0950857 PT01000010.1.8 0.1 17.5 0.2 S11  
MS0736357 PT01000010.1.8 0.062 18.2 0.5 (Saprolegnia  
MS0260587 PT01000010.1.2 0.5 15.4 0.1 VII-activating  
MS0402367 PT01000010.1.2 0.6 15.1 0.1 V-activating  
MS0416158 PT01000010.1.2 0.65 15 0.1 Callorhinchus  
MS0438713 PT01000010.1.3 0.2 18.1 0 TRP3  
MS0436466 PT01000010.1.3 0.48 17 0 -  
MS0436647 PT01000010.1.3 0.49 16.9 0 Stomatobaculum  
MS0438816 PT01000010.1.3 0.59 16.7 0 peptidase  
MS0436602 PT01000010.1.3 0.55 16.8 0 Desulfosporosinus  

Output should be

MS1199089 PT01000001.1.1 0.63 16.4 0.7 -
MS0068379 PT01000007.1.1 0.96 13.9 0.4 core  
MS0950857 PT01000010.1.8 0.1 17.5 0.2 S11  
MS0416158 PT01000010.1.2 0.65 15 0.1 Callorhinchus  
MS0438816 PT01000010.1.3 0.59 16.7 0 peptidase  

Answer 1

A fairly KISS approach that avoids sorting would be to process the file twice, the first time to find the maximum values of each second column key, and the second to print the lines that contain those values:

$ awk 'NR==FNR{x[$2] = $3+0 > x[$2] ? $3 : x[$2]; next} $3 == x[$2] {print}' file file
MS1199089 PT01000001.1.1 0.63 16.4 0.7 -
MS0068379 PT01000007.1.1 0.96 13.9 0.4 core
MS0950857 PT01000010.1.8 0.1 17.5 0.2 S11
MS0416158 PT01000010.1.2 0.65 15 0.1 Callorhinchus
MS0438816 PT01000010.1.3 0.59 16.7 0 peptidase

As it stands, this will print all instances that tie for each maximum - you could add a !seen[$2]++ condition to limit it to the first occurrence.

Answer 2

I think this is what you are looking for:

$ sort -gt' ' -k3,3r foo.txt | awk '!seen[$2]++' | sort -t' ' -k2
MS1199089 PT01000001.1.1 0.63 16.4 0.7 -   
MS0068379 PT01000007.1.1 0.96 13.9 0.4 core  
MS0416158 PT01000010.1.2 0.65 15 0.1 Callorhinchus  
MS0438816 PT01000010.1.3 0.59 16.7 0 peptidase  
MS0950857 PT01000010.1.8 0.1 17.5 0.2 S11

Answer 3

It can be done with sort like so:

sort -uk2,2 <(sort -k3,3rn FileName)

Change FileName to the name of your file and run the command from within the same directory containing your file.

output:

MS1199089 PT01000001.1.1 0.63 16.4 0.7 -   
MS0068379 PT01000007.1.1 0.96 13.9 0.4 core  
MS0416158 PT01000010.1.2 0.65 15 0.1 Callorhinchus  
MS0438816 PT01000010.1.3 0.59 16.7 0 peptidase  
MS0950857 PT01000010.1.8 0.1 17.5 0.2 S11

---

To preserve original order, however, you can use grep with sort like so:

grep "$(sort -k3,3rn FileName | sort -uk2,2)" FileName

Change both occurrences of FileName to the name of your file and run the command from within the same directory containing your file.

output:

MS1199089 PT01000001.1.1 0.63 16.4 0.7 -
MS0068379 PT01000007.1.1 0.96 13.9 0.4 core
MS0950857 PT01000010.1.8 0.1 17.5 0.2 S11
MS0416158 PT01000010.1.2 0.65 15 0.1 Callorhinchus
MS0438816 PT01000010.1.3 0.59 16.7 0 peptidase

---

To save the output of either commands above to a file, add > SaveOutput.txt after them like so:

sort -uk2,2 <(sort -k3,3rn FileName) > SaveOutput.txt

and

grep "$(sort -k3,3rn FileName | sort -uk2,2)" FileName > SaveOutput.txt

The output will be saved to a file named SaveOutput.txt in the same directory.

Please see man sort and man grep for extra information.

Please also have a look at what LC_ALL=C does and why you might need it. Thank you to @bac0n for bringing it up in the comments.