mjhjmtu

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.

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

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 '

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