For each line in a file, print fields from specific column to NF if those values are less than value in another field

Question

I have a file with the following format where the number of fields per row is variable:

NC_000001.11_NM_001005484.2 69270   234 69037   65565   69037
NC_000001.11_NM_001005484.2 69511   475 69037   65565   69037
NC_000001.11_NM_001005484.2 69761   725 69037   65565   69037
NC_000001.11_NM_001385640.1 942155  20  942136  924432  925922  930155  931039  935772 939040   939272  941144  942136  942410  942559  943253  943698  943908  

For each line, I would like to print the first four fields. For the remaining fields ($5 to NF), I want to print the field if the values in those fields is less than the value in $4.

Example output:

NC_000001.11_NM_001005484.2 69270   234 69037   65565   
NC_000001.11_NM_001005484.2 69511   475 69037   65565   
NC_000001.11_NM_001005484.2 69761   725 69037   65565   
NC_000001.11_NM_001385640.1 942155  20  942136  924432  925922  930155  931039  935772 939040   939272  941144  

I've tried to wrap my head around a number of different awk options and have failed miserably. New to awk and would appreciate any help with this.

Answer 1

If you don't care about the white space in your output then all you need is:

$ cat tst.awk
{
    out = $1 OFS $2 OFS $3 OFS $4
    for (i=5; i<=NF; i++) {
        if ( $i < $4 ) {
            out = out OFS $i
        }
    }
    print out
}

$ awk -f tst.awk file
NC_000001.11_NM_001005484.2 69270 234 69037 65565
NC_000001.11_NM_001005484.2 69511 475 69037 65565
NC_000001.11_NM_001005484.2 69761 725 69037 65565
NC_000001.11_NM_001385640.1 942155 20 942136 924432 925922 930155 931039 935772 939040 939272 941144

which you can pipe to column to visually align if you like:

$ awk -f tst.awk file | column -t
NC_000001.11_NM_001005484.2  69270   234  69037   65565
NC_000001.11_NM_001005484.2  69511   475  69037   65565
NC_000001.11_NM_001005484.2  69761   725  69037   65565
NC_000001.11_NM_001385640.1  942155  20   942136  924432  925922  930155  931039  935772  939040  939272  941144

Otherwise if you want the spacing in the output to look like the spacing in the input (i.e. what looks like 1 or more blanks for the first 4 fields and 2 or more for the rest of the fields) and assuming some lines might only have 4 or less fields then using any POSIX awk (for character classes and regexp intervals):

$ cat tst.awk
BEGIN { OFS="\t" }
match($0,/([^[:space:]]+[[:space:]]+){3}[^[:space:]]+/) {
    out = substr($0,RSTART,RLENGTH)
    for (i=5; i<=NF; i++) {
        if ( $i < $4 ) {
            out = out OFS $i
        }
    }
    $0 = out
}
{ print }

If the fields after $4 should be tab-separated:

$ awk -f tst.awk file
NC_000001.11_NM_001005484.2 69270   234 69037   65565
NC_000001.11_NM_001005484.2 69511   475 69037   65565
NC_000001.11_NM_001005484.2 69761   725 69037   65565
NC_000001.11_NM_001385640.1 942155  20  942136  924432  925922  930155  931039  935772  939040  939272  941144

or if they should be separated by blanks:

$ awk -f tst.awk file | column -s$'\t' -t
NC_000001.11_NM_001005484.2 69270   234 69037   65565
NC_000001.11_NM_001005484.2 69511   475 69037   65565
NC_000001.11_NM_001005484.2 69761   725 69037   65565
NC_000001.11_NM_001385640.1 942155  20  942136  924432  925922  930155  931039  935772  939040  939272  941144

The above retains the white space between the first 4 fields so that'll just be whatever combination of tabs and/or blanks you have in your input, and then prints a tab before every 5th and subsequent field which you can use column to change to equivalent blanks if you like, both of which look like the input and output in your question.

I'm building a new string named out in the loops above and assigning it to $0 once after the loops rather than modifying $0 or $i within the loops because each time you change $i awk has to re-build $0 from it's fields, and each time you change $0 awk has to resplit $0 into fields so both are inefficient and can lead to unexpected errors depending on the contents of the fields and therefore you should not modify $0 or $i within a loop unless you have a very specific purpose in mind that requires you to do so.

Answer 2

This has been tested with GNU Awk 5.1.0, API: 3.0 Because of using 4th argument in split this solution might not work on other versions not compatible with syntax used here.

awk '{n=split($0, a, " ", b); line=""; for (i = 1; i <= n; i++) { if (i < 5 || a[i] < $4) line=(line a[i] b[i])}; print line; }' file.txt

Explanation:

• n=split($0, a, " ", b); - this splits entire line ($0) into values (stored in a) and spaces (stored in b) so we can try to preserve formatting of original file. Value stored in n gives us number of fields to process each line. split arrays a and b indexes starts with 1.
• line="" - start with line as empty string
• for (i = 1; i <= n; i++) - lets iterate over each field, split starts with index 1 so do our loop. <= part ensures last (nth) field is also processed
• if (i < 5 || a[i] < $4) - condition is true for first 4 fields or whenever field value is less than fourth field (your required condition)
• line=(line a[i] b[i]) - join actual field and spaces with previous ones that have met requirements of "if" condition
• print line - prints line variable that contains our desired output

Answer 3

This iterates over the fields from the end of the line to the beginning (i.e. in reverse order) and deletes the field if the field number (NF) is greater than 4 AND the value of that field is greater than the value of field 4 ($4).

$ awk '{
    for (i=NF; i>=1; i--) {
      if ((i > 4) && ($i >= $4)) {
        $i=""
      }
    };
    print
    }' input.txt
NC_000001.11_NM_001005484.2 69270 234 69037 65565 
NC_000001.11_NM_001005484.2 69511 475 69037 65565 
NC_000001.11_NM_001005484.2 69761 725 69037 65565 
NC_000001.11_NM_001385640.1 942155 20 942136 924432 925922 930155 931039 935772 939040 939272 941144 

BTW, it's not clear whether your input is space or tab separated. if you want tab-separated output (rather than a single space between each field), then add -v OFS='\t' to the awk command immediately before the single-quote starting the script. e.g.

awk -v OFS='\t' '...awk script here...' input.txt

BTW, awk will leave a lot of extra field separators in the output line, one wherever a field used to be before it was deleted. If you want to get rid of those, add the following line immediately before the print statement:

    $0=$0; $1=$1;

This will effectively remove any empty fields, by forcing awk to re-evaluate the input line and split it into fields again (splitting on FS, the field-separator, which defaults to any amount of white-space). It's a bit of a hack because awk doesn't have any way to actually delete a field from a line, so you have to force it to do that after the line has been modified.