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.

  • Your data do not match your description. You only have three lines with 4 columns, is that how your file actually is? Also, your output only shows 3 columns, only one of which seems to match your input. Please edit your post to clarify what you actually need. In any case, your awk snippet does exactly what you seem to be asking for, what do you mean "all lines"? – terdon Feb 10 '14 at 15:09
  • @terdon: Yes thats how it looks like, it has four columns, but column four is of smaller size than columns 1,2,3. So what it should print, as in the example, is all lines where values from column four lie in between values of columns 2 and 3. If I do it like I posted with awk it will check #5 in row one only, #120 in row 2 only and #1200 in row three. What I need is that it checks #5 in all lines A to G, and then prints the line where it matches. So #1200 is not in between the values in row C, but it is in row E, thats why E should be printed. Sorry for the confusion – Thomas Feb 10 '14 at 15:24
  • OK, are the numbers ordered? I mean, is it possible to have something like 100 1000 300 on the 3d line and 200 1000 on the second? Or can I safely assume that the numbers are always incrementing and I don't need to read the whole file twice to get them all? Also, according to your description, you should also want column C since 100 < 120 < 1000 and I don't see why you're printing column E in your example. – terdon Feb 10 '14 at 15:47
  • @terdon: They are in ascending order. Yes you are right with column C, ACD would be printed. – Thomas Feb 10 '14 at 16:46

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.


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.

| improve this answer | |
  • That was exactly what I was looking for. Thanks a million for this and sorry again for the confusion before, due to my wrong example. – Thomas Feb 10 '14 at 16:52
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
| improve this answer | |
  • Works equally good for me as the answer by terdon above. Thank you very much for your help and time. – Thomas Feb 10 '14 at 16:57

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
| improve this answer | |
  • That's pretty much exactly what the OP has in the question. By the way, you don't need the " " between the printed fields, just write {print $1,$2,$3}. – terdon Feb 10 '14 at 15:10
  • @terdon, thanks a lot, I am not an expert in awk. – user55518 Feb 10 '14 at 15:11

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.