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

Question

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.

go ahead and try... these might help: http://stackoverflow.com/questions/32481877/what-is-nr-fnr-in-awk , http://stackoverflow.com/documentation/awk/topics , http://stackoverflow.com/documentation/perl/topics — Sundeep, Jul 25 '16 at 15:53

score 3 · Accepted Answer · edited Jul 25 '16 at 16:11

3

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

edited Jul 25 '16 at 16:11

Borodin

126,100
9
70
144

answered Jul 25 '16 at 16:10

anubhava

761,203
64
569
643

score 2 · Answer 2 · answered Jul 25 '16 at 16:09

In perl, I'd probably do something like this:

#!/usr/bin/env perl
use strict;
use warnings 'all';
use Data::Dumper;

open ( my $grades_in, '<', "file1.txt" ) or die $!;
my @grade_lookup = map { [split] } <$grades_in>;
print Dumper \@grade_lookup;
close ( $grades_in ); 

open ( my $people, '<', "file2.txt" ) or die $!; 
while (<$people>) {
   chomp;
   my ( $person, $score ) = split;
   my ( $grade ) = grep { $_ -> [1] >= $score 
                     and $_ -> [2] <= $score } @grade_lookup;
   print join " ", $person, $score, $grade -> [0], $grade -> [3], "\n";
} 
close ( $people );

output:

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

score 2 · Answer 3 · answered Jul 25 '16 at 16:21

In Perl

use strict;
use warnings 'all';
use autodie;

use List::Util 'first';

my @grades = do {
    open my $fh, '<', 'file1.txt';
    map [ split ], <$fh>;
};

open my $fh, '<', 'file2.txt';

while ( <$fh>) {
    my ($name, $score) = split;
    my $grade = first { $_->[2] <= $score } @grades;
    print "$name $score @$grade[0,3]\n";
}

output

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

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

3 Answers3

output:

output