How to extract a range of number from two files?

Question

I'm trying to extract the number range from two files.

$ cat file1.txt

chr1 69509
chr1 69511
chr1 71000
chr1 358112
chr1 586874
chr1 744200
. . .

$cat file2.txt

chr1 69091 70005
chr1 358070 358183
chr1 586842 586955
chr1 744195 744343
. . .

Desired output :

69509 chr1 69091 70005
69511 chr1 69091 70005
71000 out of range
358112 chr1 358070 358183
586874 chr1 586842 586955
744200 chr1 744195 744343

Below is my awk code.

awk 'NR==FNR{x[$1] = $2; next;}{for (i in x) {if (x[i] > $2 && x[i] < $3)print x[i], $1,$2,$3;else{print x[i], "out of range"}}}' file1.txt file2.txt

However, the output returned by this code contains only the data of the last NR.

For example,

744200 out of range
744200 out of range
744200 out of range
744200 chr1 744195 744343

I guess it is related to NR and FNR but I couldn't figure out how to do it.

Answer 1

It is because you are keying your array x on $1 which is the same chr1 for all the lines in the first file. So on every line the array contents are indexed based on the same key and overwritten with the value in $2. So by the end of the file you have one entry in the array x['chr1']=744200

You need to store the line uniquely. Store the key as a tuple $1 FS $2 and modify your logic to

awk 'NR == FNR { x[$1 FS $2] = $2; next }{ 
     for (key in x) { 
       split(key, arr)
       if (arr[2] > $2 && arr[2] < $3) 
           print arr[2], $0 
       else {
           print arr[2], "out of range"
       }
     }
}' file1 file2

To improve the logic to do the range check only on lines where the first column is the same for both the files, add a condition to the if as

if ( (arr[2] > $2 && arr[2] < $3) && ( $1 == arr[1] ) ) 

Answer 2

Assuming the file2 ranges don't overlap, like in your example:

$ cat tst.awk
NF==3 { beg=$2; end=$3; all=$0; next }
{ print $2, ( ($2 >= beg) && ($2 <= end) ? all : "out of range" ) }

$ sort -sk2n file2 file1 | awk -f tst.awk
69509 chr1 69091 70005
69511 chr1 69091 70005
71000 out of range
358112 chr1 358070 358183
586874 chr1 586842 586955
744200 chr1 744195 744343

It uses GNU sort for -s ("stable sort") to ensure the file2 entry is printed before the file1 entry in the case of identical $2s.