Cross-reference columns across files

Question

I'm sure there must be a way to do this, but I'm stuck, so would love your help. I have two files that look like:

File1:

8750    11.29592283
8800    7.500359335
14150   3.281781819
23850   3.668931975
23900   14.69918465
23950   5.259260427
27300   3.374571119

File2:

hypothetical protein CDS                7152            7346            TK0006
hypothetical protein CDS                7399            7614            TK0007
DNA methylase CDS               7655            8755            TK0008
hypothetical protein CDS                8760            10093           TK0009
hypothetical protein CDS                10095           10379           TK0010
hypothetical protein CDS                10385           10807           TK0011

File1 is genome coordinates and expression values; File2 is genome annotations. What I need is if the value in column1 file1 is between column2 file2 and column3 file3; return the full line of each file. For example, the first two lines of file1 would return an output of:

8750    11.29592283    DNA methylase CDS               7655            8755            TK0008
8800    7.500359335    hypothetical protein CDS                8760            10093           TK0009

Is such a process possible with a short awk command, or similar? Or am I biting off more than I can chew?

Thanks!!

Answer 1

awk solution:

awk -F'[[:space:]][[:space:]]+' 'NR==FNR{ a[$1]=$0; next }
     {  for(i in a) { 
            if (int(i) >= $2 && int(i) <= $3) { 
                print a[i],$0; delete a[i]; break 
            } 
        }
     }' File1 OFS='\t' File2

The output:

8750    11.29592283 DNA methylase CDS               7655            8755            TK0008
8800    7.500359335 hypothetical protein CDS                8760            10093           TK0009

---

• -F'[[:space:]][[:space:]]+' - pattern defining field sepatator

• a[$1]=$0 - capturing crucial values from File1

• if (int(i) >= $2 && int(i) <= $3) - check if some 1st field value from File1 is in range (between the 2nd and 3rd firld values from File2)

• delete a[i] - delete matched value

• break - breaking the loop to avoid redundant iterations