Filter the rows of a file based on the number of columns that have =0.00000000

Question

I would like to filter the rows of a file (e.g. file.txt below) based on the number of columns, after column 5, that have =0.00000000.

The I/O below shows an example for filtering rows that have more than 1 column (after column 5) with zero values or =0.00000000 (in other words remove rows with two or more columns with zero value or remove rows with fewer than 6 columns (after column 5) with non-zero values).

Is there a way to do this in a flexible manner such that I can decide to filter rows that have more than 1 or 2 or 3 columns (after column 5) with =0.00000000

The real file has thousands of rows and 61 or 71 columns, although the first 5 are the same.

file.txt

MT 227 1 1.000 42.0 1:2=0.00036000 1:3=0.00000000 1:4=0.00004200 1:5=0.04300000 1:6=0.03400000 1:7=0.00000000 1:8=0.01204819
MT 233 1 1.000 60.0 1:2=0.10000000 1:3=0.00639386 1:4=0.00000000 1:5=0.00584795 1:6=0.20040000 1:7=0.10030000 1:8=0.02300000
MT 245 1 1.000 107.0 1:2=0.02000000 1:3=0.05600000 1:4=0.00000000 1:5=0.00000000 1:6=0.00000000 1:7=0.02922158 1:8=0.12631579
MT 251 1 1.000 136.0 1:2=0.13384412 1:3=0.01738004 1:4=0.10528891 1:5=0.00070562 1:6=0.01081160 1:7=0.00697347 1:8=0.00453430
MT 264 1 1.000 207.0 1:2=0.00000000 1:3=0.00000000 1:4=0.00000000 1:5=0.00413223 1:6=0.00000000 1:7=0.00192377 1:8=0.00000000
MT 286 1 1.000 300.0 1:2=0.00157816 1:3=0.00126087 1:4=0.00124224 1:5=0.00144928 1:6=0.00209524 1:7=0.00124224 1:8=0.00197719
MT 292 1 1.000 337.0 1:2=0.02000000 1:3=0.30000000 1:4=0.04000000 1:5=0.00050000 1:6=0.00148588 1:7=0.00000000 1:8=0.04000000
MT 293 1 1.000 326.0 1:2=0.00000000 1:3=0.00000000 1:4=0.00000000 1:5=0.00000000 1:6=0.00153610 1:7=0.00113162 1:8=0.00000000
MT 295 1 1.000 333.0 1:2=0.00084409 1:3=0.00125321 1:4=0.00117912 1:5=0.00067806 1:6=0.00041798 1:7=0.00108578 1:8=0.00183284
MT 296 1 1.000 343.0 1:2=0.00000000 1:3=0.00000000 1:4=0.00000000 1:5=0.00233645 1:6=0.00000000 1:7=0.00108070 1:8=0.00144300

out.txt

MT 233 1 1.000 60.0 1:2=0.10000000 1:3=0.00639386 1:4=0.00000000 1:5=0.00584795 1:6=0.20040000 1:7=0.10030000 1:8=0.02300000
MT 251 1 1.000 136.0 1:2=0.13384412 1:3=0.01738004 1:4=0.10528891 1:5=0.00070562 1:6=0.01081160 1:7=0.00697347 1:8=0.00453430
MT 286 1 1.000 300.0 1:2=0.00157816 1:3=0.00126087 1:4=0.00124224 1:5=0.00144928 1:6=0.00209524 1:7=0.00124224 1:8=0.00197719
MT 292 1 1.000 337.0 1:2=0.02000000 1:3=0.30000000 1:4=0.04000000 1:5=0.00050000 1:6=0.00148588 1:7=0.00000000 1:8=0.04000000
MT 295 1 1.000 333.0 1:2=0.00084409 1:3=0.00125321 1:4=0.00117912 1:5=0.00067806 1:6=0.00041798 1:7=0.00108578 1:8=0.00183284

It would be a lot easier to just remove any row with a column (after column 5) having =0.00000000 using grep -v "=0.00000000" but this throws away too much data. Any help is greatly appreciated!

Answer 1

The simplest solution I was able to find is (yes, that simple):

awk -F '=0\\.00000000'   'NF<=2'   file

---

There are several possible solutions to this.

1. grep is quite fast at finding text, it only needs the correct regex.

   grep -vE '^([^ ]* ){5}.*(=0\.00000000.*){2}' file
   

   • The ^([^ ]* ){5} part will match columns (not spaces) separated by spaces (5 ({5}) of them), from the start (^) of a line.
   • Then, .*(=0\.00000000.*){2} will match at least two =0\.00000000 on that line.
   • Finally, reverse the match (-v) and use extended (ERE) regexes (less \needed).

It will be strict on the number of 0s it will match.

2. Sed with a similar regex:

   sed '/^\([^ ]* \)\{5\}.*\(=0\.00000000.*\)\{2\}/d' file
   

   but it will print any line that fails to match the pattern (easy to fail positive).

Or

3. Awk treating the line as text.

   awk -F '=0\\.00000000' 'NF<=2' file
   

4. Awk, which could parse floating numbers and then check for 0 value.

   Use @GlennJackman answer, please.

Answer 2

Using space or = as the field separator, start counting for zero values from column 7: if there are more then one, go on to the next line, else print the line.

awk -F '[= ]+' '{
    z = 0
    for (c = 7; c <= NF; c += 2)
        if ($c == 0.0 && ++z > 1)
            next
    print
}' file

Answer 3

This is the easiest way to print the lines that don't have more than one instance of that string:

grep -v '=0\.00000000.*0\.00000000' file.txt

As your file only has that string appearing after column 5 and you only want to print the rows where it appears one time or not at all, the above will print the lines where it doesn't appear more than once. The pattern =0\.00000000.*0\.00000000 matches any two instances of =0.00000000 on one line no matter in what columns they appear and if there is a third, fourth, fifth, and so on anywhere on one line, it won't print the line. The command that you were trying prints any lines that don't contain any instances of that string so it doesn't print the second line which is what you didn't want.

If you want it to print the lines that don't contain more instances of that string, simply add another .*0.00000000. For example, to print lines that don't contain more than three:

grep -v '=0\.00000000.*0\.00000000.*0\.00000000' file.txt

That will include line three which contains three instances of that string.