This is a test version of Biostars. For the public version, visit https://www.biostars.org.
filtering out lines (rows) based on repeated zero values with awk in linux

I want to filter out lines having zero values in more than 70% of the columns. Imagine I have the following “test_awk.txt” file

id sample1 sample2 sample3 sample4 sample5 sample6 sample7 sample8 sample9 sample10
gene1 1       2       3       4       5       6       0       0       0       0
gene2 0       0       0       0       0       0       0       0       0       0
gene3 0       0       0       0       1       0       0       0       0       0
gene4 0       0       0       10      0       10      0       0       0       0
gene5 0       0       0       0       0       0       0       0       0       0
gene6 10      10      9       9       9       9       9       9       9       9
gene7 8       8       8       8       8       8       8       8       8       8
gene8 0       0       0       0       1       1       1       0       0       0
gene9 0       0       0       0       0       1       1       1       1       1

I would like to remove lines like “gene2”, “gene3”, “gene4”, “gene5”, and “gene8” because they have zero values in more than 7 coulmns out of 10. My reallife file is to big to run it in R, so I’m trying to use "awk" but I’m getting stack Any help would be much appreciated

Ramon

filter out awk zero values
• 1,764 views • link
updated by shenwei356 shenwei356 • written by rgescudero • rgescudero

To make sure that 0 in gene names is not counted, I added gene10 entry copying gene 9 values and changing gene9 to gene 10.

$ awk 'NR==1 {print}; NR !=1 {print $1, $1="", $0, gsub(/0/, "")}' file.txt | awk -v OFS="\t" 'NR==1 {print};NR!=1 && $NF<7 {NF--;print}'

id  sample1 sample2 sample3 sample4 sample5 sample6 sample7 sample8 sample9 sample10
gene1   1   2   3   4   5   6   0   0   0   0
gene6   10  10  9   9   9   9   9   9   9   9
gene7   8   8   8   8   8   8   8   8   8   8
gene9   0   0   0   0   0   1   1   1   1   1
gene10  0   0   0   0   0   1   1   1   1   1
• 0 views • link
written by cpad0112 cpad0112

1 answer

not tested.

awk '{N=0.0;for(i=2;i<=NF;i++) if($i=="0") N++; if(N/(NF-1)<0.7) print;}' input.txt
• 0 views • link
written by Pierre Lindenbaum Pierre Lindenbaum

Thanks a lot Pierre Lindenbaum: it is working. Great!!! Many thanks!!!

• 0 views • link
written by rgescudero • rgescudero

mark the solution as answered please. Green tick on the left.

• 0 views • link
written by Pierre Lindenbaum Pierre Lindenbaum

Log in to answer this question.

More posts like this