UNIX for Dummies Questions & Answers

Hi there,

I've got two files:

file1
file 2
I need to add column 4 of file1 as an additional column to file2 if the number in column2 file2 is in the range of columns 2 and 3 of file1 and column1 in file 2 matches column1 in file2. Foe example, take the first line in both files - the first columns are matching (both are 1), then column 2 of file2 (1235) is in the range of columns2 and 3 of file1 (1235 is between 1234 and 1240), thus, column 4 of file1 needs to be added to file2, line1. The result (for that one line) would be:

file3
1 1235 klp ABC

Similarly, there will be nothing for line2 in file2; and line 3 of file2 will get IGN from file1.

The final file3 should look like this:

Many thanks for your help!

Loose thinking: you are combining two files and making a third, which is what "join" does.

I think join will put two files together according to the identical fields, but I need also to kind of match the second field of file2 to fields 2 and 3 of file3. Once the 1st fields are matching, then the value of second column of file2 needs to be in the range of columns 2 and 3 of file1, and only then the 4th column of file1 should be added to file2. I am sorry for the confusion.

---------- Post updated at 01:04 PM ---------- Previous update was at 01:03 PM ----------

sorry, I mistyped: I need to kind of match the second column of file2 to the 2nd and 3rd columns of file1 (not 3 as I wrote before).

Ah, the ranging bit. Well, not heavy duty but in shell you can read f1 f2 f3 f4 and compare fields, (( $a2 >= $b2 && $a2 <= $b3 )) for numeric comparison. You can use one big while loop, reading new a fields when a is short and new b fields when b is short. Just prime your key variables before the loop so they both read the first line, like with -9999, -9998, -9997. The first line for file a because the old a vlaue is low, the first line for file b because the old b vlaue is too old, and then print the join if they are in range. Make use of the fact that one side is 0/1 to 1/many. If it is many to many, then you have to scan one file for every line in the other.

Many thanks for your suggestion. I am afraid my unix knowledge is not that great to completely understand what you meant. Also, the file is pretty big (~2G in size), so I assume I will scan each line in turn. If it is not too much to ask, could you please write an example script? Many thanks for your time. It is really appreciated.

Try this:

Code:

awk 'NR==FNR{a[$1]=$2; b[$1]=$3; c[$1]=$4; next} 
a[$1] && $2 >= a[$1] && $2 <= b[$1] {$(NF+1)=c[$1]}
1' file1 file2

Nice, but needs cooperative data: