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.

  • 2
    Please don't post images of text, as they are hard to read, will not allow us to use the data in our tests, use more bandwidth, and are no good for screen readers. Please edit your question and replace it with raw text. Welcome to U/L! – Sparhawk Jan 4 at 4:10
  • No worries, thank you for the fix. I'm fairly sure I know what you want, but could you also please provide your expected output for your sample. Thanks! – Sparhawk Jan 4 at 4:17
  • Done! Cheers for the help (clearly new to this!!) – Alex Jan 4 at 4:21
  • 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. – G-Man Jan 4 at 4:25
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
  • Thanks steeldriver! Datamash worked a treat. – Alex Jan 7 at 0:21
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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