[SOLVED] how to filter a text file based on a range of numbers in a specific column using awk

nash-c · 03-23-2016, 01:31 PM

EUREKA!!

Ok just to clarify, I apologise grail for not copying and pasting your last command. I just tried this:

Code:

awk '$10 < -1000 && $10 > -3000{print > "filt_" FILENAME}' Int_DU145withTannopeaks.txt

and still got the following error:

Code:

awk: syntax error at source line 1
 context is
	$10 < -1000 && $10 > -3000{print > "filt_" >>>  FILENAME <<< }
awk: illegal statement at source line 1

I don't understand though why it works for you grail and not me. I'm probably doing something wrong still.

It seems that your suggestion ntubski works. When I tried using the command with the parentheses added, it worked fine for one single file. I then took it further and tried it on multiple files in my directory and this also worked great.

So for future readers, to clarify, you can filter a text file for a range of negative numbers in a specific column (column 10 in this case) on multiple files with this without for loops etc:

Code:

awk '$10 < -1000 && $10 > -3000{print > ("filt_" FILENAME)}' *.txt

or I still maintain that the original edit with the for loop does actually work too but perhaps with caveats:

Code:

for foo in $(ls -1 *.txt); do cat $foo | awk '{ if ($10 < -1000 && $10 >= -3000) print $0;}' >> filtered_$foo ; done

grail · 03-23-2016, 01:47 PM

@schneidz - it seems Apple use the idea that a rock steady base should never need upgrading ... cause who would ever use the command line on an Apple

All of the base utils (find, tar,...) are all ancient and it is a real pain when my Apple family members say "Can't you just do it like on your machine at home, I thought you knew how to use linux?" ... which hurts a little ... hehe

grail · 03-23-2016, 01:49 PM

Nice work nash-c and I do like the completeness of your answer for the future

Don't forget to mark your ticket as SOLVED

ntubski · 03-23-2016, 03:06 PM

Quote:

Originally Posted by nash-c

or I still maintain that the original edit with the for loop does actually work too but perhaps with caveats:

Code:

for foo in $(ls -1 *.txt); do cat $foo | awk '{ if ($10 < -1000 && $10 >= -3000) print $0;}' >> filtered_$foo ; done

It probably works for the particular set of files you have, but there are a few common mistakes that would cause failures if you had filenames with spaces; put the code into http://www.shellcheck.net/ to see (except ignore SC2016, it's a false positive in this case).

nash-c · 03-23-2016, 03:46 PM

Thanks again all for your patience and help.

I'll be doing more to teach myself how to learn awk (and programming in general) properly as well as looking into upgrading my version of awk. I use an iMac at work but I use Ubuntu/Linux PC at home so maybe I'll test the two against each other in future and see what difference it makes.

And I'm glad you like the "completeness" of my answer. I've been on bioinformatics forums before and many times I've been lost in threads and struggled to pull out the answer I needed. Hopefully this way it's clear for people.

Thanks and I'm sure I'll be back soon!!

grail · 03-24-2016, 06:44 AM

As a side note, Ubuntu normally defaults to mawk which also does not support all the same features as gawk which many on this site refer to when they say awk