Join two files with common and range identifiers

Shell Programming and Scripting
1

I have a problem joining two files. The first file abc.txt has 10k lines and has lots of fields but two fields fff1 and ppp1 to merge by. The second file xyz.txt is a master file with 1k lines and lots of fields but three fields to merge by fff1; rrr1 and qqq1.

The two files need to be merged by fff1 and whenever ppp1 lies between rrr1 and qqq1. So multiple lines from abc.txt with meet this criteria and data from xyz.txt will be copied whenever fff1 matches for the two files and ppp1 from abc.txt lies between rrr1 and qqq1 from xyz.txt.

I hope this is clear. I would welcome any suggestions and open to any script as long as it is efficient since the actual files are millions of lines. Thanks for your help.

2

Please post sample input and output so that someone can help you promptly and easily.

Simply writing in plain words will be a pain. Thanks

3

Here are two sample files

abc.txt

name fff1 ppp1
sam 1 12
pam 1 14
jen 2 8
trout 2 6
pat 3 12
don 3 16

xyz.txt

id fff1 qqq1 rrr1
234 1 10 15
456 2 4 7
324 2 8 10
334 3 10 20

The resulting file should be abcxyz.txt

name fff1 ppp1 id qqq1 rrrr1
sam 1 12 234 10 15
pam 1 14 234 10 15
jen 2 8 324 8 10
trout 2 6 456 4 7
pat 3 12 334 10 20
don 3 16 334 10 20

4

Hi,
welcome to unix.com!!!
Try this one,

awk 'FNR==NR{a[$2]=$3;v[$2]=$0;next;}{if(a[$2] &&a[$2]>=$3 && a[$2]<=$4){print v[$2],$0;}}' abc.txt xyz.txt

Cheers,
Ranga:-)

5

That solution won't work. Multiple records in abc.txt can have the same $2. Your assignments to a and v are both clobbering.

Regards,
Alister

6

Yes, i haven't noticed that.
Good catch. Thank you!!
But the requirement is

The two files need to be merged by fff1 and whenever ppp1 lies betweenrrr1 and qqq1.

right? The only man can answer for this. Its user.
Please confirm us.
Cheers,
Ranga:-)

7 
join -12 -22 -o1.1,1.2,1.3,2.1,2.3,2.4 abc.txt xyz.txt | awk '$3>=$5 && $3<=$6' > abcxyz.txt

That will work if both files are sorted on fff1 and if the header line at the top of the sample data files isn't present in the actual data.

Regards,
Alister

8

The solution didn't work. The second file xyz.txt is a master file and multiple records from abc.txt match at least one record from xyz.txt

9 
awk 'NR == FNR {
  k[NR] = $2
  b[NR] = $3
  e[NR] = $4
  m[NR] = $1 " " $3 " " $4
  next
}
{
  for (i = 1; i in m; ++i) {
    if ($2 == k && $3 >= b && $3 <= e)
      print $0, m
  }
}' xyz.txt abc.txt
10

In what way did it "not work"?