This is a test version of Biostars. For the public version, visit https://www.biostars.org.
extract lines with multiple conditions on multiple fields/column

I have a big file contains a huge amount of SNP data, I like to search multiple columns ( here column 3 to 5) and filter any lines contain values between 0.5 to 1.

    SNP NN1 NN2 NN3
1   AG  0.0 0.0 1.0
2   CC  0.2 0.0 2.0
3   CC  0.3 0.5 0.0
4   AA  0.5 0.5 1.0
5   GG  0.4 0.0 0.0

expected output;

    SNP NN1 NN2 NN3
1   AG  0.0 0.0 1.0
3   CC  0.3 0.5 0.0
4   AA  0.5 0.5 1.0

I could find awk scripts that use a single field but not multiple field/column for query. ex; awk -F "," '$3 >= 0.5 && $3 <= 1' input.csv > out.csv

Any help will be appreciated. Thanks a lot.

awk grep sed
• 10,391 views • link
updated by Arup Ghosh Arup Ghosh • written by Sam • Sam

Thanks, Arup for formatting the data. Also it would be helpful to know how to paste excel data on Biostar effectively.

not sure why the data looks messy though it looks good on the preview

• 0 views • link
written by Sam • Sam

You can share data snippets in Github and link that to biostars thread.

• 0 views • link
written by Arup Ghosh Arup Ghosh

Grouping the conditions and adding the "or" operator || should do the trick

awk -F "," '($3 >= 0.5 && $3 <= 1) || ($4 >= 0.5 && $4 <= 1) || ($5 >= 0.5 && $5 <= 1)' input.csv > out.csv

• 0 views • link
written by Sus • Sus

Thanks, Sus... But I have > 100 columns, is that possible to search a range of columns as a condition. for example. choose between $3 to $100

something like this ... awk -F "," '($3:$100 >= 0.5 && $3:$100 <= 1) input.csv > out.csv
• 0 views • link
written by Sam • Sam

I suggest you embed this in a for loop within awk with an option to exit the look (= go to next line) if any of the conditions is not fulfilled, basically iterating from the 3rd to the nth column.

• 0 views • link
written by ATpoint ATpoint

2 answers

The following awk command will loop through all the columns.

awk -F"," 'NR==1; NR > 1{for(i=2; i <= NF; i++) if($i >= 0.5 && $i <=1) {print; next}}' data.csv

Input: data.csv

SNP,NN1,NN2,NN3
AG,0.0,0.0,1.0
CC,0.2,0.0,2.0
CC,0.3,0.5,0.0
AA,0.5,0.5,1.0
GG,0.4,0.0,0.0

Output:

SNP,NN1,NN2,NN3
AG,0.0,0.0,1.0
CC,0.3,0.5,0.0
AA,0.5,0.5,1.0
• 0 views • link
written by Arup Ghosh Arup Ghosh

Oh my god, csvtk filter happens to do this:

$ cat data.csv | csvtk filter -f '2-4>0.5' --any 
SNP,NN1,NN2,NN3
AG,0.0,0.0,1.0
CC,0.2,0.0,2.0
AA,0.5,0.5,1.0
• 0 views • link
written by shenwei356 shenwei356

Log in to answer this question.

More posts like this