0

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!

3
  • Do you want the criteria to be more than 1 after column 5 with that string, more than 2 after column 5 with that string, or more than 3 after column 5 with that string? May 11, 2020 at 23:15
  • Do you mean fewer than one column or more than one column? None of them have fewer than one column as that would be zero. May 11, 2020 at 23:22
  • Sorry for the confusion. For any given row, I want the output file to have no more than 1 column (after column 5) with =0.00000000 . So for all rows 6/7 columns after column 5 should have a non-zero value like =0.30000000
    – Age87
    May 11, 2020 at 23:29

3 Answers 3

3

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.

  1. 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

  1. Awk treating the line as text.

    awk -F '=0\\.00000000' 'NF<=2' file
    
  2. Awk, which could parse floating numbers and then check for 0 value.

    Use @GlennJackman answer, please.

3
  • Amazing! Thank you, sir!
    – Age87
    May 12, 2020 at 4:51
  • I love #3. Very intuitive May 12, 2020 at 12:11
  • Thanks @glennjackman I appreciate your feedback.!
    – user232326
    May 12, 2020 at 14:16
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
1

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.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .