Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

compare files and create new with awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting compare files and create new with awk
# 1  
Old 12-19-2007
Question compare files and create new with awk
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. Smilie
tonet
# 2  
Old 12-19-2007
join of to files
join -j 1 -j 2 -j 3 -o 1.1,1.2,1.3,1.4,2.9 file1 file2
# 3  
Old 12-19-2007
awk
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  
Old 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.
tonet
# 5  
Old 12-19-2007
Limitations of join
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  
Old 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.
# 7  
Old 12-27-2007
sorry your name was typed wrong by me.
Ranjithpr
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

[awk] Compare two files

HI!! I am trying to compare two files using AWK but I have some problems. I need to count how many times letters are used in two texts. This is my script { long=length($0) for (i=1;i<=long;i++) { aux=substr($0,i,1) if ( aux != " " && aux != "" ) ... (7 Replies)
Discussion started by: ettore8888
7 Replies

2. Shell Programming and Scripting

awk compare files

I have a below requirement and trying to compare the files using awk File 1 - Already stored on a prev day id | text | email id --------------------------------- 89564|this is line 1 | xyz@sample.txt 985384|this is line 2 | abc@sample.txt 657342|this is line 3 |... (3 Replies)
Discussion started by: rakesh_411
3 Replies

3. Shell Programming and Scripting

Compare 2 files, awk maybe?

I have 2 files, file1: alfa numbers numbers vita numbers numbers gama numbers numbers delta numbers numbers epsilon numbers numbers zita numbers numbers ... file2: 'zita' keepnumbers keepnumbers keepnumbers 'gama' keepnumbers keepnumbers keepnumbers 'misc' ... (11 Replies)
Discussion started by: phaethon
11 Replies

4. Shell Programming and Scripting

How to create a script to compare 2 files?

I have two files File1 and File2 will contains same number of fields. The rows in File1 can be found anywhere in File2. If any rows from File1 doesn't match to File2 then write those records to a File1 log file. If any rows from File2 doesn't match to File1 then write those records to a File2... (6 Replies)
Discussion started by: ranjanp
6 Replies

5. HP-UX

Awk compare two files

Hi guys, I have 2 files: File1 ABC|2203|115.50 ABC|2288|328.12 ABC|2289|611.09 ABC|2290|698 DEF|1513|721.3 DEF|1514|40 DEF|1515|5 File2 ABC|2288|328.12 ABC|2289|666.08 ABC|2290|698.00 DEF|1513|721.30 (3 Replies)
Discussion started by: Eduardo Aceves
3 Replies

6. Shell Programming and Scripting

awk command to compare a file with set of files in a directory using 'awk'

Hi, I have a situation to compare one file, say file1.txt with a set of files in directory.The directory contains more than 100 files. To be more precise, the requirement is to compare the first field of file1.txt with the first field in all the files in the directory.The files in the... (10 Replies)
Discussion started by: anandek
10 Replies

7. Shell Programming and Scripting

compare two files using awk

Hi, I want to compare two files using awk and write an output based on if the records matched. Both the files are space delimitted. File A: 8351 00000000000636 2009044 -00001.000 8351 00000000000637 2009044 -00002.000 8351 00000000000638 2009044 -00001.000 8351 00000000000640... (7 Replies)
Discussion started by: gpaulose
7 Replies

8. Shell Programming and Scripting

Compare two files using awk

Hi. I'm new to awk and have searched for a solution to my problem, but haven't found the right answer yet. I have two files that look like this: file1 Delete,3105551234 Delete,3105551236 Delete,5625559876 Delete,5625556789 Delete,5625553456 Delete,5625551234 Delete,5625556956... (8 Replies)
Discussion started by: paul.o
8 Replies

9. Shell Programming and Scripting

Compare two csv files by two colums and create third file combining data from them.

I've got two large csv text table files with different number of columns each. I have to compare them based on first two columns and create resulting file that would in case of matched first two columns include all values from first one and all values (except first two colums) from second one. I... (5 Replies)
Discussion started by: agb2008
5 Replies

10. UNIX for Dummies Questions & Answers

How to compare several files and create a new one

Hello, I have a few files with simple data on them. All of the files may contain the same and different data. I need to create a new file that contains data that is on all files at the same time. Let say if word "ffffffff" is on all files it must be in the new file. If word "kkkkkkk" is not on... (1 Reply)
Discussion started by: alxkn
1 Replies
Login or Register to Ask a Question