Extracting a single line containing the highest value in a given column from a text file from each consecutively numbered sub-group/family

Question

Within my text file, I would like to take the line containing the highest value present in column 3, from each consecutively numbered family (i.e. family_1, family_2 etc.) from column 2 and input these data into a new text file.

Input data:

TTGSCA  family_1    18.123083   681 36349   1
TTGSCA  family_1    18.123083   681 36349   1
CTTRAG  family_2    17.844843   685 37001   1
CTYAAG  family_2    16.95983    657 36170   1
.GCCAAR family_3    19.436863   698 35844   1
WGCCAA. family_3    19.99668    747 38506   1
.GCCAAS family_3    17.037859   599 31922   1
WGCCAA. family_3    19.99668    747 38506   1
CCACTK  family_4    17.200712   776 44550   1
CCACTY  family_4    18.86465    727 38616   1
MCACTT  family_4    18.0871 737 40399   1
MCACTT  family_4    18.0871 737 40399   1
YCACTT  family_4    19.369513   804 43376   -1
CCAYTT  family_4    16.193245   752 44296   1
CCAYTT  family_4    16.193245   752 44296   1
SCACTT  family_4    19.759317   687 34686   1

Output data:

TTGSCA  family_1    18.123083   681 36349   1
CTTRAG  family_2    17.844843   685 37001   1
WGCCAA. family_3    19.99668    747 38506   1
SCACTT  family_4    19.759317   687 34686   1

I'm not sure whether to use grep or awk, and how to combine these into a single function.

Answer 1

With GNU datamash (and a little help from cut):

$ datamash -Wf groupby 2 max 3 < file.txt | cut -f1-6
TTGSCA  family_1    18.123083   681 36349   1
CTTRAG  family_2    17.844843   685 37001   1
WGCCAA. family_3    19.99668    747 38506   1
SCACTT  family_4    19.759317   687 34686   1

Answer 2

I think datamash is probably the best tool, but here is a sort-unique alternative:

<infile sort -k2,2V -k3,3n | awk 'NR==1 || $2!=p; { p=$2 }'

Answer 3

Below is a cleaner way of getting the desired output than my previous answer. It does require sort to be used twice but it's a lot better than having sort, grep, and tail being used four times.

sort -k3r numbers | awk '!seen[$2]++' | sort -k2

Output:

TTGSCA  family_1    18.123083   681 36349   1
CTTRAG  family_2    17.844843   685 37001   1
WGCCAA. family_3    19.99668    747 38506   1
SCACTT  family_4    19.759317   687 34686   1