Hi,
I have a tabular text file with 105078 rows and 22 columns I want to remove all those lines if 50 percent of columns (columns 3 - 22) has empty fields.
I tried ISBLANK and IF function in excel, no luck. Could awk or sed help me?
Regards,
Waqas.
Edited: tags and Spelling mistake
•
written
by
waqasnayab
not tested:
awk '{W=0.0;for(i=3;i<=NF;i++) {if($i=="") W++;} if(W/(NF-3) <0.5) print; }'
Thanks Pierre,
Yeah, I run the following command:
awk '{W=0.0;for(i=3;i<=NF;i++) {if($i=="") W++;} if(W/(NF-3) <0.5) print; }' all_sample_nomismatch.feature
and got this error:
awk: cmd. line:1: (FILENAME=all_sample_nomismatch.feature FNR=1) fatal: division by zero attempted
My input file is without any headers, like this:
human_miRNA.mis_0+ hsa-miR-1237-5p 4 5 6 4 1 2 75 8 45 5 6 45 12 1 25
human_miRNA.mis_0+ hsa-miR-1246-5p 1
human_miRNA.mis_0+ hsa-miR-1249-5p 1
human_miRNA.mis_0+ hsa-miR-1273e-3p 1
human_miRNA.mis_0+ hsa-miR-1285-1-5p 1
human_miRNA.mis_0+ hsa-miR-151a-3p 1
human_miRNA.mis_0+ hsa-miR-1908-5p 2
human_miRNA.mis_0+ hsa-miR-191-5p 3 3 6 1 14 4
human_miRNA.mis_0+ hsa-miR-1973-3p 1
The output should be the first line as it has 50 percent columns filled between columns 3 - 22. Hope it might help you further. Will update if I got luck.
Regards,
Waqas.
This worked for me:
awk '(NF==12){print}' all_sample_nomismatch.feature
Now, I have all those columns (including columns 1 and 2 must, and then 3 to 22) where 3 to 22 fields are 50 percent filled. Hope I did correctly.
Regards,
Waqas.
Log in to answer this question.
From https://unix.stackexchange.com/questions/363059/awk-script-to-count-the-0-in-each-row-and-print-the-rows-that-have-less-than-20