# awk arrays comparing multiple columns across two files.

**URL:** <https://community.unix.com/t/awk-arrays-comparing-multiple-columns-across-two-files/338254>\
**Category:** Shell Programming and Scripting\
**Created:** [January 2, 2014, 11:39am UTC](https://community.unix.com/t/awk-arrays-comparing-multiple-columns-across-two-files/338254 "2014-01-02T11:39:39Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![hubleo](https://community.unix.com/letter_avatar/hubleo/32/5_5575768a8748004e209b776fc1b2916d.png) [@hubleo](https://community.unix.com/u/hubleo)\
**Post date:** [January 2, 2014, 11:39am UTC](https://community.unix.com/t/awk-arrays-comparing-multiple-columns-across-two-files/338254/1 "2014-01-02T11:39:39Z")

</div>

Hi, I'm trying to use awk arrays to compare values across two files based on multiple columns. I've attempted to load file 2 into an array and compare with values in file 1, but success has been absent. If anyone has any suggestions (and I'm not even sure if my script so far is on the right lines) it would be very much appreciated.

file1

```nohighlight
A 1 10 20
B 1 35 50
C 2 40 50
D 2 65 100
E 3 10 30
F 3 20 40
G 4 25 50
H 4 45 70

```

file2

```nohighlight
ID1 11 16 1
ID2 75 100 1
ID3 45 47 2
ID4 15 30 3
ID5 40 45 4
ID6 55 65 4

```

Essentially, if column 2 in file 1, and column 4 in file 2 match, continue.  
Then, if column 2 in file 2 is \>= column 3 in file 1  
and column 3 in file 2 is \<= column 4 in file 1  
print out matching line of file 1, and column 1,2 and 3 from file2.

Desired output

```nohighlight
A 1 10 20 ID1 11 16
C 2 40 50 ID3 45 47
E 3 10 30 ID4 15 30
G 4 25 50 ID5 40 45
H 4 45 70 ID6 55 65

```

Using a pseudo script based on similar problems online, I've got:

```nohighlight
awk 'FNR == NR
{
f2[$0]++
next
}
{
for (i in f2)
{
split(i,f2_split)
if ((f2_split[4] == $2) && (f2_split[2] >= $3) && (f2_split[3] <= $4))
{print $0, f2_split[1],f2_split[2],f2_split[3]
}
}
}' file2 file1

```

This does a fantastic job of printing out the contents of file 2. Alas it's not what I was after. Any help would be much appreciated.

---

<div class="post-metadata">

**Author:** ![bartus11](https://community.unix.com/user_avatar/community.unix.com/bartus11/32/1418_2.png) [@bartus11](https://community.unix.com/u/bartus11)\
**Post date:** [January 2, 2014, 11:54am UTC](https://community.unix.com/t/awk-arrays-comparing-multiple-columns-across-two-files/338254/2 "2014-01-02T11:54:45Z")

</div>

Try:

```nohighlight
awk 'NR==FNR{a[NR]=$0;next}{for (i in a){split(a,x," ");if (x[4]==$2&&x[2]>=$3&&x[3]<=$4)print $0,x[1],x[2],x[3]}}' file2 file1

```

---

<div class="post-metadata">

**Author:** ![hubleo](https://community.unix.com/letter_avatar/hubleo/32/5_5575768a8748004e209b776fc1b2916d.png) [@hubleo](https://community.unix.com/u/hubleo)\
**Post date:** [January 2, 2014, 12:13pm UTC](https://community.unix.com/t/awk-arrays-comparing-multiple-columns-across-two-files/338254/3 "2014-01-02T12:13:55Z")

</div>

Thanks, that worked a treat. Had a little play around with that code, and realised one of the issues with my initial code was the use of new lines in inappropriate places. If I put a line between the awk 'NR==FNR and the rest of your code, that just prints out file 2 as well.

```nohighlight
awk 'NR==FNR
{a[NR]=$0;next}{for (i in a){split(a,x," ");if (x[4]==$2&&x[2]>=$3&&x[3]<=$4)print $0,x[1],x[2],x[3]}}' file2 file1

```

A valuable lesson that I'd previously overlooked. Thanks for your help.

---

<div class="post-metadata">

**Author:** ![bartus11](https://community.unix.com/user_avatar/community.unix.com/bartus11/32/1418_2.png) [@bartus11](https://community.unix.com/u/bartus11)\
**Post date:** [January 2, 2014, 12:16pm UTC](https://community.unix.com/t/awk-arrays-comparing-multiple-columns-across-two-files/338254/4 "2014-01-02T12:16:25Z")

</div>

You can put a newline like this:

```nohighlight
awk 'NR==FNR{
a[NR]=$0;next}{for (i in a){split(a,x," ");if (x[4]==$2&&x[2]>=$3&&x[3]<=$4)print $0,x[1],x[2],x[3]}}' file2 file1

```

---

<div class="post-metadata">

**Author:** ![hubleo](https://community.unix.com/letter_avatar/hubleo/32/5_5575768a8748004e209b776fc1b2916d.png) [@hubleo](https://community.unix.com/u/hubleo)\
**Post date:** [January 2, 2014, 12:28pm UTC](https://community.unix.com/t/awk-arrays-comparing-multiple-columns-across-two-files/338254/5 "2014-01-02T12:28:13Z")

</div>

Awesome, that's really useful. A rookie mistake no doubt but one that I won't make again any time soon, hopefully! Thanks