1

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.

1
  • grep is no good for this; awk is probably the perfect tool.  If you search this site, you’ll find hundreds of questions very much like this.  We encourage you to do that; find a working solution and try to adapt it to your problem.  If you get stuck; edit your question to show what progress you made and what trouble you’re having. … … P.S. Since your data has “ties”, you should say how you want them broken. Jan 4, 2019 at 4:25

3 Answers 3

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
0
0

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 }'
0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .