Rick Rick - 4 months ago 12
Perl Question

Awk - Get rows from a file that contain values within ranges described in another file

I have 2 tab-delimited files formatted similar to this:

file 1

A 100 90 PASS
B 89 80 PASS
C 79 70 PASS
D 69 60 FAIL
F 59 0 FAIL


file 2

Randy 80
Denis 44
Earl 97


I want to take the values from column 2 in file 2 and compare them with the ranges given between columns 2 and 3 of file 1. Then I want to create a new file that combines this data, printing columns 1 and 2 from file 2 and columns 1 and 4 from file 1:

file 3

Randy 80 B PASS
Denis 44 F FAIL
Earl 97 A PASS


I want to implement this using awk or perl.

Answer

You can use this awk:

awk 'BEGIN{FS=OFS="\t"}
FNR==NR {
   a[$0] = $2
   next
}
{
   for (i in a)
      if ($2>=a[i] && $3<=a[i])
         print i, $1, $4
}' file2 file1
Earl    97  A   PASS
Randy   80  B   PASS
Denis   44  F   FAIL