Compare two numerical ranges in two distincts files with awk and print ALL lines from file1 and the matching ones from file2

user_16425907 0
Viewed 38

This new question is a followup from a recent question : Compare two numerical ranges in two distincts files with awk. The proposed solution that perfectly worked was not practical for downstream analysis (misconception of my question, not on the solution that worked).

I have a file1 with 3 columns. Columns 2 and 3 define a numerical range. Data are sorted from the smaller to the bigger value in column 2. Numerical ranges never overlap.

file1

S   24     96
S   126    352
S   385    465
S   548    600
S   621    707
S   724    736

I have a second file2 (test) structured similarly.

file2

S   27     93
S   123    348
S   542    584
S   726    740
S   1014   2540
S   12652  12987

Desired output: Print ALL lines from file1 and next to them, lines of file2 for which numerical ranges overlap (including partially) the ones of file1. If no ranges from file2 overlap to a range of file1, print zero next to the range of file 1.

S   24    96     S   27    93       * 27-93 overlaps with 24-96
S   126   352    S   123   355      * 123-355 overlaps with 126-352
S   385   465    0                  * nothing in file2 overlaps with this range
S   548   600    S   542   584      * 542-584 overlaps with 548-600
S   621   707    0                  * nothing in file2 overlaps with this range
S   724   736    S   726   740      * 726-740 overlaps with 724-736

Based on the answer of the previous question from @EdMorton I modified the print command of the tst.awk script to add these new features. In addition I also changed the order file1/file2 to file2/file1 to have all the lines from file1 printed (whether or not there is a match in the second file)

'NR == FNR {
begs2ends[$2] = $3
next
}
{
for (beg in begs2ends) {
    end = begs2ends[beg] + 0
    beg += 0
    if (    ( ($2 >= beg) && ($2 <= end) ) ||
            ( ($3 >= beg) && ($3 <= end) ) ||
            ( ($2 <= beg) && ($3 >= end) )  ) {
        print $0,"\t",$1,"\t",beg,"\t",end
    else 
        print $0,"\t","0"
        next
    }
}
}

Note: $1 is identical in file1 and file2. This is why I used print ... $1 to make it appear. No idea how to print it from file2 and not file1 (if I understand correctly this $1 refers to file1.

And I launch the analysis with awk -f tst.awk file2 file1

The script is not accepting the else argument and I dont understand why? I assuming that it is linked to the looping but I tried several changes without any success. Thanks if you can help me with this.

awk
3 Answers

Assumptions:

  • a range from file1 can only overlap with one range from file2

The current code is almost correct, just need some work with the placement of the braces (using some consistent indentation helps):

awk '
BEGIN     { OFS="\t" }                                 # output field delimiter is "\t"
NR == FNR { begs2ends[$2] = $3; next } 
          {
            # $1=$1                                    # uncomment to have current line ($0) reformatted with "\t" delimiters during print
            for (beg in begs2ends) {
                end = begs2ends[beg] + 0
                beg += 0
               if ( ( ($2 >= beg) && ($2 <= end) ) ||
                    ( ($3 >= beg) && ($3 <= end) ) ||
                    ( ($2 <= beg) && ($3 >= end) ) ) {
                  print $0,$1,beg,end                  # spacing within $0 unchanged, 3 new fields prefaced with "\t"
                  next
               }
            }

            # if we get this far it is because we have exhausted the "for" loop
            # (ie, found no overlaps) so print current line + "0"

            print $0,"0"                               # spacing within $0 unchanged, 1 new field prefaced with "\t"
          }
' file2 file1

This generates:

S   24     96   S       27      93
S   126    352  S       123     348
S   385    465  0
S   548    600  S       542     584
S   621    707  0
S   724    736  S       726     740

With the $1=$1 line uncommented the output becomes:

S       24      96      S       27      93
S       126     352     S       123     348
S       385     465     0
S       548     600     S       542     584
S       621     707     0
S       724     736     S       726     740
S       900     1000    S       901     905
user_73661000

A slight variation on @markp-fuso's answer

Works with GNU awk: saved as overlaps.awk

BEGIN { PROCINFO["sorted_in"] = "@ind_num_asc" }
function in_range(val, min, max) { return min <= val && val <= max }
NR == FNR {
    line[FNR] = $0
    lo[FNR] = $2
    hi[FNR] = $3
    next
}
{
    overlap = "0"
    for (i in line) {
        if (in_range(lo[i], $2, $3) || in_range(hi[i], $2, $3)) {
            overlap = line[i]
            delete line[i]
            break
        }
    }
    print $0, overlap
}

Then

gawk -f overlaps.awk file2 file1 | column -t

outputs

S  24   96   S  27   93
S  126  352  S  123  348
S  385  465  0
S  548  600  S  542  584
S  621  707  0
S  724  736  S  726  740
user_75520 
$ cat tst.awk
BEGIN { OFS="\t" }
NR == FNR {
    ranges[++numRanges] = $0
    next
}
{
    overlapped = 0
    for ( i=1; i<=numRanges; i++ ) {
        range = ranges[i]
        split(range,vals)
        beg = vals[2]+0
        end = vals[3]+0
        if (    ( ($2 >= beg) && ($2 <= end) ) ||
                ( ($3 >= beg) && ($3 <= end) ) ||
                ( ($2 <= beg) && ($3 >= end) )  ) {
            overlapped = 1
            break
        }
    }

    if ( overlapped ) {
        print $0, range, sprintf("* %d-%d overlaps with %d-%d", beg, end, $2, $3)
    }
    else {
        print $0, 0, sprintf("* nothing in %s overlaps with this range", ARGV[1])
    }
}


$ awk -f tst.awk file2 file1 | column -s$'\t' -t
S   24     96   S   27     93   * 27-93 overlaps with 24-96
S   126    352  S   123    348  * 123-348 overlaps with 126-352
S   385    465  0               * nothing in file2 overlaps with this range
S   548    600  S   542    584  * 542-584 overlaps with 548-600
S   621    707  0               * nothing in file2 overlaps with this range
S   724    736  S   726    740  * 726-740 overlaps with 724-736
user_17450010
Related