6

I have a large file. I need to remove all lines in a file which have a number less than 60 in column 3.

Example file:

35110   Bacteria(100)   Proteobacteria(59)  Alphaproteobacteria(59)
12713   Bacteria(100)   Bacteroidetes(100)  Bacteroidia(100)

Desired output:

12713   Bacteria(100)   Bacteroidetes(100)  Bacteroidia(100)
8

No need for Gawk extensions:

awk -F '[()]' '$4 >= 60'

Here the awk field tokenizer specified via -F is a regex set []: fields get separated by either an opening or closing parenthesis, hence you see the number of your 3rd column is the 4th awk field.

  • Overall +1. I find [()] as a column separator pattern kind of unintuitive because it ignores the more obvious whitespace-delimited columns entirely and suggest gawk -F '[ \t()]+' '$5 >= 60' instead. – David Foerster Feb 18 '18 at 19:19
4

You can use awk (actually it must be the GNU AWK implementation gawk, not mawk which contains fewer features - you might have to install it sudo apt install gawk) for this job:

gawk '{match($3,/\((.+)\)/,m);if(m[1]>=60){print $0}}' MY_FILE

Now although admittedly this looks like black magic to the untrained eye, the logic is simple:

  • For every line, run the stuff inside the outermost curly braces:
  • First, match($3, /\((.+)\)/, m) matches the regular expression \((.+)\) (which matches an opening and closing round bracket, storing the content between the brackets as first capture group) against the third column $3 of the processed line of input and stores the resulting match array in the variable m.
  • Then, check the condition if (m[1] >= 60) i.e. if the value of the first capture group of the match (whatever is between the brackets in the input) is greater or equal to 60. If that is true, do {print $0}, which simply prints the whole currently processed line.
  • 1
    This is gawk specific I think (POSIX awk doesn't have the array version of match AFAIK) - however you can do a similar thing with split e.g. awk '{split($3,a,/[)(]/);} a[2]+0 >= 60' – steeldriver Feb 8 '18 at 22:02
  • I tried these command options. However, I am getting empty (0KB) output file. Please let me know if any error in this.... – Manoj Kumar Feb 8 '18 at 22:20
  • 1
    If it still does not work, I can only imagine your real input data does not match the data you gave us as an example... – Byte Commander Feb 8 '18 at 22:30
  • 1
    @ManojKumar no, you should take an excerpt of the actual data itswelf that you're working with, obscure anything that might be 'secret', and then take that obfuscated excerpt of data and add that to your original question as an edit to the question. That way we can see the real format of the data. – Thomas Ward Feb 8 '18 at 22:45
  • 1
    Use awk -F, '{. . .}' for comma separated data – steeldriver Feb 8 '18 at 23:38
3

Here's a perl alternate

perl -alne 'print unless $F[2] =~ /\((\d+)\)$/ && $1 < 60'
  • match and capture a parenthesized sequence of decimal digits at the end of the 3rd (zero-indexed) field
  • if a match is found, test the captured group's numerical value and print accordingly

Ex.

$ perl -alne 'print unless $F[2] =~ /\((\d+)\)$/ && $1 < 60' file
12713   Bacteria(100)   Bacteroidetes(100)  Bacteroidia(100)

Note that this implements the logic "remove all lines in a file which have a number less than 60 in column 3" as stated in your question - which is slightly different from printing lines that have a number greater than or equal to 60.


If your files really are comma separated (rather than whitespace delimited as shown in your question), then you will need to change the delimiter i.e.

perl -F, -lne 'print unless $F[2] =~ /\((\d+)\)$/ && $1 < 60'
0

If you don't want to learn/use command-line tools, you might as well open the file in LibreOffice Calc and just filter the data (Calc supports tab-separated files).

If you know any programming language, then it is trivial to write a small program to filter the data.

But if you have a large dataset to process, using a DBMS like MySQL would be easier, faster and intuitive.

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.