Count the number of rows with a string occurring n times in multiple columns

Question

I have several hundred text files consisting each of five tab delimited columns. The first column contains an index and the following four the count of occurrences. Now I would like to count the number of rows that contain 3 columns with 0 (i.e. 7 rows in the example below).

1   0   0   0   9
2   0   9   0   0
3   10  0   0   0
4   0   10  4   0
5   0   0   0   10
6   0   0   0   10
7   0   0   0   10
8   0   10  0   0
9   5   0   5   0

I can code this as a loop in R, but as the original files each contain 60+ million rows, I wonder if there is no workaround with awk or sed and wc -l.

Answer 1

Yes, you can do this in awk:

awk '{ 
       k=0; 
       for(i=2;i<=NF;i++){ 
         if($i == 0){
             k++
         }
       }
       if(k==3){
         tot++
       }
      }
      END{
          print tot
      }' file 

And also with (GNU) sed and wc:

$ sed -nE '/\b0\b.*\b0\b.*\b0\b/p' file | wc -l
7

But, personally, I would do in in perl instead:

$ perl -ale '$tot++ if (grep{$_ == 0 } @F) == 3 }{ print $tot' file 
7

Or, the slightly less condensed:

$ perl -ale 'if( (grep{$_ == 0 } @F) == 3 ){
                  $tot++ 
              }
              END{
                  print $tot
              }' file 
7

And the same thing, for the golfers among you:

$ perl -ale '(grep{$_==0}@F)==3&&$t++}{print$t' file
7

Explanation

• -ale: -a makes perl behave like awk. It will read each line of the input file and split it on whitespace into the array @F. The -l adds a \n to each call of print and removes trailing newlines from the input and the -e is the script that should be applied to each line of input.
• $tot++ if (grep{$_ == 0 } @F) == 3 : increment $tot by one, for every time where there are exactly 3 fields that are 0. Since the 1st field starts from 1, we know it will never be 0 so we don't need to exclude it.
• }{: this is just a shorthand way of writing END{}, of giving a block of code that will be executed after the file has been processed. So, }{ print $tot will print the total number of lines with exactly three fields with a value of 0.

Answer 2

With GNU grep or ripgrep

$ LC_ALL=C grep -c $'\t''0\b.*\b0\b.*\b0\b' ip.txt 
7

$ rg -c '\t0\b.*\b0\b.*\b0\b' ip.txt
7

where $'\t' will match tab character, thus working even if first column is 0.

Sample run with large file:

$ perl -0777 -ne 'print $_ x 1000000' ip.txt > f1
$ du -h f1
92M f1

$ time LC_ALL=C grep -c $'\t''0\b.*\b0\b.*\b0\b' f1 > f2
real    0m0.416s

$ time rg -c '\t0\b.*\b0\b.*\b0\b' f1 > f3  
real    0m1.271s

$ time LC_ALL=C awk 'gsub(/\t0/,"")==3{c++} END{print c+0}' f1 > f4
real    0m8.645s

$ time perl -ale '$tot++ if (grep{$_ == 0 } @F) == 3 }{ print $tot' f1 > f5
real    0m14.349s

$ time LC_ALL=C sed -n 's/\t0\>//4;t;s//&/3p' f1 | wc -l > f6
real    0m14.075s
$ time LC_ALL=C sed -n 's/\t0\>/&/3p' f1 | wc -l > f8    
real    0m6.772s

$ time LC_ALL=C awk '{ 
       k=0; 
       for(i=2;i<=NF;i++){ 
         if($i == 0){
             k++
         }
       }
       if(k==3){
         tot++
       }
      }
      END{
          print tot
      }' f1 > f7 
real    0m10.675s

Remove LC_ALL=C if file can contain non-ASCII characters. ripgrep is usually faster than GNU grep but in test run GNU grep was faster. As per ripgrep's author, (?-u:\b) can be used to avoid unicode word boundary, but that resulted in similar time for above case.

Answer 3

Using GNU sed:

sed -E 's/\t0\>/&/3;t;d' file  | wc -l

As pointed out by Isaac, if we want to count exact 3 then do this :

sed -n 's/\t0\>//4;t;s//&/3p' file | wc -l

Answer 4

$ awk 'gsub(/\t0/,"")==3{c++} END{print c+0}' file
7

Answer 5

Use Perl to count the number of lines which see a zero surrounded on the left by a TAB and to the right by a word boundary, three times. In the end print the line count of such lines.

perl -lne '$c += 3 == (() = /\t0\b/g)}{print $c' file
7

Another way to do it is by looking at the fields:

perl -F'\t' -lane '$c++ if 3 == grep ! $_, @F[1..$#F]}{print $c' file

Yet another way is to use the s/// command in a scalar context:

perl -lne '$c += s/\t0\b//g == 3}{print $c' file 

We use Gnu awk to do this:

awk -F'\t' '
  {
      gsub(FS, FS FS)
      $0 = $0 FS
      if ($0 != gensub(FS"0"FS, "", 3, $0))  ++c
  }
  END{print c}
' file

Gnu grep can also help you:

grep -cP '(.*\t0\b.*){3}' file

Answer 6

Another awksolution

awk '{x=$0; if(gsub(/\y0\y/, "", x) == 3) y++}; END{print y+0}' file

This assumes the index column never contains a 0. We set a variable x to $0, and then replace a 0 surrounded by word boundaries with an empty string via the gsub call. Since gsub returns the number of replacements made, we increment y if return value equals 3

Answer 7

It is possible to match exactly n zeros (not more) with (GNU) sed:

$ i=3;               # use the n value wanted.
$ sed -E 's/\<0\>/&/'"$i"';Ta;{s//&/'"$((i+1))"';T};:a;d' file | wc -l
7

Explanation

i=3                  # define the count of the regex to match.
sed -E               # use extended regexes. reduce the need of `\`.
/\<0\>/              # match a zero (0) surrounded by whitespace.
s/\<0\>/&/           # replace zeros with themselves (detect that they exist)
'"$i"'               # count how many zeros to change.
Ta                   # branch to label `a` (delete line) if
                     # there were less than `i` zeros
{s//&/'"$((i+1))"'   # replace again but now i+1 zeros
T                    # branch to print if there are no more zeros than `i`.
:a;d                 # label to delete line (and catch any other line).

The equivalent with (GNU) awk is even simpler (gsub counts exactly how many zeros were replaced).

$ awk -vn=3 'gsub(/\<0\>/,"&")==3{c++}END{print c+0}' file
7

Answer 8

$ grep -E '(\<0\>.*){3}' file | wc -l
       7

The pattern \<0\> will match a lone digit 0. The full pattern matches any line that has at least three lone zeros.

Te only count lines that contain exactly three zeros, not four, remove any line that contains four or more zeros:

grep -E '(\<0\>.*){3}' file | grep -v -E '(\<0\>.*){4,}' | wc -l