compare files and create new with awk

Shell Programming and Scripting
1

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:

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:

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:

2

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

3

Hi,

Try follow code:

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:

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
4

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

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:

join: fields 1 and 2  are not compatibles

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

5

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

6

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.

7

sorry your name was typed wrong by me.
Ranjithpr

8

Try this:

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