Print Column if values is between two other values

Question

I am fairly new to data analysis and I have a problem with filtering some of my values. I have data arranged in a file with four columns like this:

A   1       10     5
B   10      100    120
C   100     1000   1200 
D   1000    10000
E   10000   100000
F   100000  1000000
G   1000000 10000000

What I want to do now is to print only lines where the values in column 4 are in between the values in columns 2 and 3. So something like this:

A 1 10 
C 100 1000
D 1000 10000

I tried with grep and awk, e.g. awk '$4 >= $2 && $4 <= $3 {print $1,2,3}'. Though the problem is that awk searches only within the same line and not for all lines where it matches this criteria. I guess it's rather simple but I couldn't figure out how to do it.

Answer 1

This will do what I think you are asking for. It collects all values from the 4th column and then, for all lines in the file and all values seen on the fourth column, it will print the line if any of the 4th values are between the values on the line.

perl -lane '$want{$F[$#F]}++; foreach $wanted (keys(%want)){
               if($wanted > $F[1] && $wanted < $F[2]){print "@F[0..2]"} 
             }' file
A 1 10
C 100 1000
D 1000 10000

However, as you can see above, this does not produce the output you show. It is printing line C because 100 < 120 < 1000 and it is not printing line E because none of the values of the 4th column are between 10000 and 100000.

---

Explanation

The -a switch to perl makes it automatically split the input file into fields on whitespace and saves the fields as the array @F. So, I save the last field ($F[$#F]) in the %wanted hash, and for each line, I go through the keys of the hash I have so far and print the 1st through 3rd fields (@F[0..2]) of the line if any of them are between the values.

Note that this will not work if your file is not ordered, if one of the 4th column numbers can appear after a line it would satisfy. If that can happen, you need to read the file twice, something like this:

 perl -le 'open(A,"$ARGV[0]"); while(<A>){@F=split(/\s+/); $want{$F[$#F]}++}; 
           open(A,"$ARGV[0]"); while(<A>){@F=split(/\s+/);
            foreach $wanted (keys(%want)){
                if($wanted > $F[1] && $wanted < $F[2]){print "@F[0..2]"}
            }} ' file

Or, you can use @Stephane's answer which also reads the file twice.

Answer 2

awk 'NR == FNR {if (NF >= 4) a[$4]; next}
     {for (i in a) if (+i >= $2 && +i <=$3) {print $1, $2, $3; next}}' file file

Answer 3

something like this?

awk '$4 <= $3 && $4 >= $2' data
A 1 10 5

without the 4th column:

awk '($4 <= $3 && $4 >= $2){print $1,$2,$3}' data
A   1   10