# compare files and create new with awk

**URL:** <https://community.unix.com/t/compare-files-and-create-new-with-awk/179262>\
**Category:** Shell Programming and Scripting\
**Created:** [December 19, 2007, 4:21am UTC](https://community.unix.com/t/compare-files-and-create-new-with-awk/179262 "2007-12-19T04:21:44Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![system](https://community.unix.com/uploads/default/original/2X/c/c7e9e16a595259edafe88615719d5c5dcda035c2.png) [@system](https://community.unix.com/u/system)\
**Post date:** [December 19, 2007, 4:21am UTC](https://community.unix.com/t/compare-files-and-create-new-with-awk/179262/1 "2007-12-19T04:21:44Z")

</div>

hi everybody:

Anybody know how I can compare two files where the pattern would be the three firsts columns of each file to create another one. This is, I have two file:

file1 is like:

```nohighlight
20030601 14 05 498.985 0.436532
20030601 14 10 499.531 0.260819
20030601 14 15 499.427 0.508597
20030601 14 20 500.418 0.606761
20030601 14 25 496.877 0.233742
20030601 14 30 496.578 0.117021
20030601 14 35 497.189 0.487961
20030601 14 40 499.676 0.613496
....
...
..

```

and file2 is:

```nohighlight
20030601 14 05 71.96452 810 309.2 30.09 43.11 996.26 0.00 4.04 125.70 55.32
20030601 14 10 72.46647 802 307.3 29.98 43.02 996.27 0.00 3.74 117.70 59.46
20030601 14 15 72.98309 789 304.3 29.99 43.65 996.28 0.00 2.79 116.50 72.80
20030601 14 20 73.51406 780 303.2 30.27 42.71 996.24 0.00 3.31 103.00 67.17
20030601 14 25 74.05905 764 300 29.66 43.53 996.22 0.00 3.21 94.10 68.35
20030601 14 30 74.61774 754 299.8 29.73 42.94 996.18 0.00 4.05 132.40 57.06
20030601 14 35 75.18980 744 298.6 29.86 42.81 996.16 0.00 3.25 116.30 71.00
20030601 14 40 75.77492 746 306.8 30.30 41.84 996.16 0.00 2.46 111.50 61.00
...
...
..

```

In this case i would like that when two files have the same value at three first columns create another one where appear these three common values and the fourth column of file1 and the ninth of file2.

thanks in advance. :rolleyes:

---

<div class="post-metadata">

**Author:** ![ranjithpr](https://community.unix.com/letter_avatar/ranjithpr/32/6_22734026db12803ebb3e059622ac3534.png) [@ranjithpr](https://community.unix.com/u/ranjithpr)\
**Post date:** [December 19, 2007, 4:38am UTC](https://community.unix.com/t/compare-files-and-create-new-with-awk/179262/2 "2007-12-19T04:38:47Z")

</div>

join -j 1 -j 2 -j 3 -o 1.1,1.2,1.3,1.4,2.9 file1 file2

---

<div class="post-metadata">

**Author:** ![summer\_cherry](https://community.unix.com/letter_avatar/summer_cherry/32/6_22734026db12803ebb3e059622ac3534.png) [@summer\_cherry](https://community.unix.com/u/summer_cherry)\
**Post date:** [December 19, 2007, 4:40am UTC](https://community.unix.com/t/compare-files-and-create-new-with-awk/179262/3 "2007-12-19T04:40:10Z")

</div>

Hi,

Try follow code:

```nohighlight
nawk '{
if(NR==FNR)
	f[$3]=sprintf("%s %s %s %s",$1,$2,$3,$4)
if(NR!=FNR)
{
	if (f[$3]!="")
		n[$3]=sprintf("%s %s",f[$3],$9)
}
}
END{
for (i in n)
print n
}' a b | sort -n -k3

```

output:

```nohighlight
20030601 14 05 498.985 996.26
20030601 14 10 499.531 996.27
20030601 14 15 499.427 996.28
20030601 14 20 500.418 996.24
20030601 14 25 496.877 996.22
20030601 14 30 496.578 996.18
20030601 14 35 497.189 996.16
20030601 14 40 499.676 996.16

```

---

<div class="post-metadata">

**Author:** ![system](https://community.unix.com/uploads/default/original/2X/c/c7e9e16a595259edafe88615719d5c5dcda035c2.png) [@system](https://community.unix.com/u/system)\
**Post date:** [December 19, 2007, 5:41am UTC](https://community.unix.com/t/compare-files-and-create-new-with-awk/179262/4 "2007-12-19T05:41:28Z")

</div>

Thanks summer\_cherry:  
I have used your script, it works but I guess that at files where there are more than 100,00 lines only there are 12 common lines where the fields 1,2 and 3 were equals.

Thanks ranjithpr:  
I tried join command

```nohighlight
join -j 1 -j 2 -j 3 -o 1.1,1.2,1.3,1.4,2.9 a.txt b.txt > final.txt

```

but appears this message:

```auto
join: fields 1 and 2 are not compatibles

```

what I have to improve?. Thanks in advance again.

---

<div class="post-metadata">

**Author:** ![ranjithpr](https://community.unix.com/letter_avatar/ranjithpr/32/6_22734026db12803ebb3e059622ac3534.png) [@ranjithpr](https://community.unix.com/u/ranjithpr)\
**Post date:** [December 19, 2007, 8:35am UTC](https://community.unix.com/t/compare-files-and-create-new-with-awk/179262/5 "2007-12-19T08:35:51Z")

</div>

join is having some limitation. Files should be sorted and any of the field its matching it will join etc. you can try this script but I don't have any how fast it will be for bigger files

cat small\_file |while read key1 key2 key3 key4  
do  
key9=`grep "$key1 $key2 $key3" big_file |tr -s ' '|cut -d' ' -f9`  
if [$? -eq 0]  
then  
echo "$key1 $key2 $key3 $key4 $key9"  
fi  
done

---

<div class="post-metadata">

**Author:** ![Mohit623](https://community.unix.com/letter_avatar/mohit623/32/6_22734026db12803ebb3e059622ac3534.png) [@Mohit623](https://community.unix.com/u/Mohit623)\
**Post date:** [December 27, 2007, 7:28am UTC](https://community.unix.com/t/compare-files-and-create-new-with-awk/179262/6 "2007-12-27T07:28:20Z")

</div>

Hi Ranlithpr:  
can u please explain the above posted msg.  
Actually i started using unix a few days back only.  
So will you please help me.

---

<div class="post-metadata">

**Author:** ![Mohit623](https://community.unix.com/letter_avatar/mohit623/32/6_22734026db12803ebb3e059622ac3534.png) [@Mohit623](https://community.unix.com/u/Mohit623)\
**Post date:** [December 27, 2007, 7:31am UTC](https://community.unix.com/t/compare-files-and-create-new-with-awk/179262/7 "2007-12-27T07:31:47Z")

</div>

sorry your name was typed wrong by me.  
Ranjithpr

---

<div class="post-metadata">

**Author:** ![Franklin52](https://community.unix.com/user_avatar/community.unix.com/franklin52/32/500_2.png) [@Franklin52](https://community.unix.com/u/Franklin52)\
**Post date:** [December 27, 2007, 9:05am UTC](https://community.unix.com/t/compare-files-and-create-new-with-awk/179262/8 "2007-12-27T09:05:32Z")

</div>

Try this:

```nohighlight
awk '
FNR==NR{arr[$1$2$3]=$1$2$3;arr2[$1$2$3]=$4;next} 
arr[$1$2$3]{print $1,$2,$3,arr2[$1$2$3],$9}
' file1 file2

```

Regards