compare files and create new with awk

#1 (**permalink**) 12-19-2007

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:

Code:

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:

Code:

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.

ranjithpr · #2 (**permalink**) 12-19-2007

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

summer_cherry · #3 (**permalink**) 12-19-2007

Hi,

Try follow code:

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[i]
}' a b | sort -n -k3

output:

Code:

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 (**permalink**) 12-19-2007

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

Code:

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:

HTML Code:

join: fields 1 and 2  are not compatibles

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

ranjithpr · #5 (**permalink**) 12-19-2007

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

Mohit623 · #6 (**permalink**) 12-27-2007

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.

Mohit623 · #7 (**permalink**) 12-27-2007

sorry your name was typed wrong by me.
Ranjithpr

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
File Compare and Create New File with Diff	guiguy	UNIX for Advanced & Expert Users	7	02-28-2007 06:43 AM
compare two txt files	space13	Shell Programming and Scripting	8	09-22-2006 09:40 AM
How to compare several files and create a new one	alxkn	UNIX for Dummies Questions & Answers	1	07-17-2006 11:04 PM
help! need to compare files	danielsf	Shell Programming and Scripting	6	07-09-2003 05:09 PM
compare files	ingunix	UNIX for Dummies Questions & Answers	3	05-24-2001 11:44 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: