3

I have a large comma separated file. I need to filter out rows that contain x amount of columns containing zeroes (excluding the first row). For simplicity, let's say I want to filter out rows with more than 4 zeroes:

    gene,v1,v2,v3,v4,v5,v6,v7
    gene1,0,1,5,0,0,4,100
    gene2,1,0,0,0,5,210,2
    gene3,0,0,0,0,6,0,0

Would return:

    gene,v1,v2,v3,v4,v5,v6,v7
    gene1,0,1,5,0,0,4,100
    gene2,1,0,0,0,5,210,2

Filtering out "gene3".

Here's what I've tried (attempting and failing to use ',0' as a delimiter):

awk -F',0' 'NF<4 {print}' file.csv
2

KISS approach, with awk

awk -F, '{c = 0; for(i=1; i<=NF; i++) {c += $i == "0" ? 1 : 0}} c <= 3' file.csv
    gene,v1,v2,v3,v4,v5,v6,v7
    gene1,0,1,5,0,0,4,100
    gene2,1,0,0,0,5,210,2

With perl

perl -F, -ne 'print unless (grep { $_ eq "0" } @F) > 3' file.csv
    gene,v1,v2,v3,v4,v5,v6,v7
    gene1,0,1,5,0,0,4,100
    gene2,1,0,0,0,5,210,2
3

With awk -F',0', three copies of ,0 will be taken as three separators, giving four fields in total. So if you use awk -F',0' 'NF<5 {print}' instead, you should see the correct lines in the output.

,0 will also match strings like 213,0123, which you may or may not want to take as zero separators.

So, you could also use , as the field separator and count the fields that have just only that one zero in them:

awk -F, '{z=0; for (i = 1 ; i <= NF ; i++) if ($i == 0) z++} z <= 4' file.csv
3

You can also solve your problem using regular expressions and grep.

grep -Ev '(,0(,[^0,]+)*){4,}' file.csv

I tested it on this file:

gene,v1,v2,v3,v4,v5,v6,v7
gene1,0,1,5,0,0,4,100
gene2,1,0,0,0,5,210,2
gene3,0,0,0,0,6,0,0
gene4,0,0,0,4,6,0,0
gene5,0,1,0,4,6,0,0

There are some assumptions:

  • no non-zero number starts with a zero,
  • zero numbers contain only one zero,
  • all numbers are integers.

The regular expression could be extended to address such cases should you need it.

1

Surely the answer is simply

awk -F,0 'NF<5' file.csv

Use a delimiter of ",0" and where number of fields is less than 5, take the default action which is to print.

I tested it on this file

gene,v1,v2,v3,v4,v5,v6,v7
gene1,0,1,5,0,0,4,100
gene2,1,0,0,0,5,210,2
gene3,0,0,0,0,6,0,0
gene4,0,0,0,4,6,0,0
gene5,0,1,0,4,6,0,0

Which yielded this result

gene,v1,v2,v3,v4,v5,v6,v7
gene1,0,1,5,0,0,4,100
gene2,1,0,0,0,5,210,2

Try it online!

  • as mentioned in ilkkachu's answer that would match something like 213,0123 – αғsнιη Jun 2 '18 at 10:53
  • Yep. Unsure whether that's a problem here, with the OP's dataset. – steve Jun 2 '18 at 13:12
1

If all numbers are integer, then using GNU awk which supports word boundaries \<...\>, you could do

gawk 'gsub(/\<0\>/, "0") <5' infile
  • +1, Given that \> is only valid for GNU awk (you may make a note about that) you can simplify to awk 'gsub(/\<0\>/,"0")<=4' infile. – Isaac Jun 2 '18 at 15:53
  • @isaac, note that busybox awk also supports \<. Also \b, though you'd need to write it gsub("\\b0\\b", "0"). – Stéphane Chazelas Jun 2 '18 at 16:34
  • @StéphaneChazelas Yes, busybox awk also have \<, \> and \b (as gawk). But please note: … 1. That \b means Backspace. in gawk, not "word boundary". …2. That "word boundary" is coded as \y in gawk. … 3. That /…/ leads to simpler regexes (no double \` required), … 4. that in both busybox awk` and gawk this: awk 'gsub(/\<0\>/, "0") <5' infile work correctly, and …5. that \b only works in busybox awk as a string (not as a regex inside /…/). So: /…/ is shorter and \< \> are more portable. – Isaac Jun 2 '18 at 17:00
0

This can be done with the following:

¶ split records on a comma

  perl -F'/,(?=0,|0$)/' -lane 'print if $#F < 4' csv.file 

° split on those commas to the right of whom we see either a 0, or a 0 at the end.

° the array formed by splitting up the record ($_) is (@F) and whose last index ($#) will have how many such commas were there.

¶ sed based

 sed -ne '
     h;1b print
     s/,/,,/g;s/$/,/;t reset
     :reset;s/,0,/&/4;t
     :print;g;p
 '  csv.file

°  we double the commas as this involves overlapping matches. Also provide a comma at the end for uniform matching. 
 ° a dummy t command is run first to clear the test flag, OTW the actual t command that follows misfires.
° a s/// command is run to do a fourth substitution. If it succeeds => there are at least four pure zero fields. We don't want this so the labelless t command shall take the conrol to end of any further processing. The -n sed option will prevent it from being printed.
° now when the substitution failed => there were three or less such pure zero fields and we want such lines.
° before making changes we had stored the original unmodified line in hold space so we get it back and print it.

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.