I want to match a list of numeric ids with two columns (each line has a different number/id) in file 1

>cat file1
1 23444
3 422255223
1 35541 

and print only those lines from a larger file (file2) for which the 1st column matches and the 2nd column id is between the id in file 2.

>cat file 2
1 10 30 XP2
1 31 50 XP34
1 23000 25000 XP56
2 19000 30000 Xp9J

Running the script, Ideally if match is found it would output just the IDs from file 1 and then any of the match columns after the between column match e.g:

 awk code file1 file2
 1 23444 XP56

There is already a solution close to this question posted here: Awk - Print row if number is between column 1 and column 2

But I am struggleing to modify that script to accomodate 2 columns for the match and to query a file (file1) line by line.

  • file1 in your example has two times a line with id 1. Should the second file be compared with both values 23444 and 35541 then? And what is if both values lie in the range (when I replace 35541 with 24123 for example), should both be printed? – chaos May 10 at 15:25
  • Yes the first column ID will be repeated many times (there are only 10 of them in my real file). The combination of the 2nd column and 1st column will be pretty unique (e.g. 1, 256663 should only occur once), however. Each row in file 1 should be compared with file 2 independently. It is possible that multiple rows in file 1 will match the same row in file 2 (if they have the same ID in column 1, e.g. 1, and if thier column 2 numbers both fall between the numbers in column 2, 3 of file 2). – user95146 May 10 at 15:59

This answer is GNU-awk specific: it uses arrays-of-arrays

gawk '
    NR == FNR { f1[$1][$2] = 1; next }
    $1 in f1 {
        for (val in f1[$1]) {
            if (0+$2 <= 0+val && 0+val <= 0+$3) {
                print $1, val, $4
' file1 file2

The 0+x business in the if condition ensures that your values are compared as numbers not strings. This is needed because with string comparision, "10" <= "23444" <= "30" is true.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.