Identify overlapping ranges in AWK

Question

I have a file with rows of 3 columns (tab separated) eg:

2 45 100

And a second file with rows of 3 columns (tab separated) eg:

2 10 200

I want an awk command that matched the lines if $1 in both files matches and the range between $2-$3 in file one interstects at all with the range in $2-$3 in file 2. It can be within the range of values in file 2 or the range in file 2 can be within the range in file 1, or theer can just be a partial overlap. Any kind of intersect between the ranges would count as a match and then print the row in file 3.

My current code only matches if $1 and either $2 or $3 match, but doesn't work for when the ranges are within each other as in these cases the precise numbers don't match.

  awk '
        BEGIN {
            FS = "\t";
        }
        FILENAME == ARGV[1] {
            pair[ $1, $2, $3 ] = 1;
            next;
        }
        {
            if ( pair[ $1, $2, $3 ] == 1 ) {
                print $1 $2 $3;
            }
        }

Example Input:

File1:

File2:

Here line 1(file1) matches line 1(file2) because the first column matches AND range 10-15 overlaps between both ranges Line 2 (file1) matches line 3(file2) because first column matches and range of 30-50 is within range 10-100. Line 4(file1) matches line 4(file2) because first column matches and the range 22-24 overlaps in both. Therefore output would be lines 1,2 and 4 from file2 printed in a new output file.

Hope these examples help.

Your help is really appreciated.

Thank you in advance!

I've tried to help you with this problem before (stackoverflow.com/questions/12727108/…). I suggest you to provide an example of your input and the expected output. Otherwise you are forcing all us to guess and build our own tests that won't be as accurate of what you could provide us. It's time and effort loss for those who wish to help you. — Birei
thanks i didn't realise you'd updated the answer to deal with the overlapping ranges problem. Ill take a look and if it works Ill close this question. I added examples as requested above. — user964689
No. I didn't update it. But the other thread was more complex because you asked to go throught a folder looking for files and process each one of them. I had to create all those files with my own (guessed) data and was much more work than expected at first sight. — Birei
I see, well I figured out how to put the awk command into a loop 'for f in folder' etc so should be ok now as long as there is an awk command that works for a single file1 compared to file2 then I will integrate it into a loop. I appreciate your help, your first solution worked for many files. This additional question is actually for a slightly different dataset where the regions in file1 are much smaller so I miss overlaps where the range in file1 is within the range in file2 which never happened in the first dataset. So thanks your efforts have already helped. — user964689

arutaku arutaku · Accepted Answer · 2012-10-05T09:27:03

It is quite easy if you use join command to merge both files by its first field ($1):

If you only want the file2 lines as output:

join --nocheck-order <(sort -n file1) <(sort -n file2) | awk '{if ($2 >= $4 && $2 <= $5 || $3 >= $4 && $3 <= $5 || $4 >= $2 && $4 <= $3 || $5 >= $2 && $5 <= $3) {print $1" "$4" "$5;}}' -

Using your input files I got this output:

1 5 15
2 10 100
8 22 24

Identify overlapping ranges in AWK

1 Answers