Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Add fields in different files only if some fields between them match

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Add fields in different files only if some fields between them match
# 1  
Old 06-05-2011
Data Add fields in different files only if some fields between them match
Hi everybody (first time posting here)

I have a file1 that looks like >

Code:
1,101,0.1,0.1
1,26,0.1,0.1
1,3,0.1,0.1
1,97,0.5,0.5
1,98,8.1,0.218919
1,99,6.2,0.248
2,101,0.1,0.1
2,24,3.1,0.147619
2,25,23.5,0.559524
2,26,34,0.723404

with 762 lines..

I have another 'similar' file2 >
Code:
 1,101,0.8,0.266667
1,3,0.8,0.1
1,96,83.8,13.9667
1,97,3.3,0.55
1,98,2.8,0.254545
1,99,4.4,0.22
2,101,0.1,0.1
2,24,1.3,0.108333
2,25,16.4,0.341667
2,26,30.4,0.608

with 738 lines

I want to add 'third field in file1 to third field in file2' and same thing for fourth field ...if and only if, first and second fields between them match ...
tried in bash something like..

Code:
awk -F "," '{print $1, $2}'file1 >a
awk -F "," '{print $1, $2}'file2 >b
cat a b > c
sort -u c > d
cat d | while read line
do
 !!!awk  
done

but I stopped at the !!!awk line...because I am not sure ...Smilie

any ideas? I know that awk is the key ...but I know just the basics of awk..Smilie
thanks in advance
Last edited by murpholinox; 06-05-2011 at 11:04 PM.. Reason: did not finish
# 2  
Old 06-05-2011
So you'd like to replace col 3 & 4 in file2, if there is match in file1 with col 1&2?

Code:
awk 'NR==FNR{a[$1 FS $2]=$3;b[$1 FS $2]=$4;next} 
     {$3=(a[$1 FS $2]=="")?$3:a[$1 FS $2]; $4=(b[$1 FS $2]=="")?$4:b[$1 FS $2]}1' FS=, OFS=, file1 file2

# 3  
Old 06-05-2011
ok I have something like this..
the problem is that I do not know how to tell if field one AND field two match $line...

Code:
awk -F "," '{print $1, $2}' file1 >a
awk -F "," '{print $1, $2}' file2 >b
cat a b > c
sort -u c > d
cat d | while read line
do
   awk '{if ($1, $2 == '$line') print $0 }' file1 > e 
   awk '{if ($1, $2 == '$line') print $0}' file2  > f

done

paste e f > g
awk '{$4 + $8}' g > h

---------- Post updated at 09:36 PM ---------- Previous update was at 09:34 PM ----------

not to replace...but sum > field3file1+field3file2

---------- Post updated at 09:44 PM ---------- Previous update was at 09:36 PM ----------

Quote:
Originally Posted by rdcwayx
So you'd like to replace col 3 & 4 in file2, if there is match in file1 with col 1&2?
not to replace, but to sum .. x+y

pardon me, not quite good English.
# 4  
Old 06-06-2011
Still don't understand, then you need provide the expect output from your souce files.
# 5  
Old 06-06-2011
ok
lets assume file1 and file2 are the lines shown on the first post (only first 10 lines of each file)
I want >
1,101,0.9,0.366667
1,3,0.9,0.2
1,97,3.8,1.05
1,98,10.9,0.473464
1,99,10.6,0.468
2,101,0.2,0.2
2,24,4.4,0.255952
2,25,39.9,0.901191
2,26,64.4,1.331404
Last edited by murpholinox; 06-06-2011 at 12:38 AM.. Reason: not good
# 6  
Old 06-06-2011
Code:
awk 'NR==FNR{a[$1 FS $2]=$3;b[$1 FS $2]=$4;next} 
    a[$1 FS $2]!=""  {$3=a[$1 FS $2]+$3; $4=b[$1 FS $2]+$4;print}' FS=, OFS=, file1 file2

This User Gave Thanks to rdcwayx For This Post:
murpholinox
# 7  
Old 06-06-2011
MySQL
thank you so much!
could u please, explain me this
'NR==FNR
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Is there a UNIX command that can compare fields of files with differing number of fields?

Hi, Below are the sample files. x.txt is from an Excel file that is a list of users from Windows and y.txt is a list of database account. $ head -500 x.txt y.txt ==> x.txt <== TEST01 APP_USER_PROFILE USER03 APP_USER_PROFILE TEST02 APP_USER_EXP_PROFILE TEST04 APP_USER_PROFILE USER01 ... (3 Replies)
Discussion started by: newbie_01
3 Replies

2. Shell Programming and Scripting

Matching two fields in two csv files, create new file and append match

I am trying to parse two csv files and make a match in one column then print the entire file to a new file and append an additional column that gives description from the match to the new file. If a match is not made, I would like to add "NA" to the end of the file Command that Ive been using... (6 Replies)
Discussion started by: dis0wned
6 Replies

3. UNIX for Beginners Questions & Answers

awk match two fields in two files

Hi, I have two TEST files t.xyz and a.xyz which have three columns each. a.xyz have more rows than t.xyz. I will like to output rows at which $1 and $2 of t.xyz match $1 and $2 of a.xyz. Total number of output rows should be equal to that of t.xyz. It works fine, but when I apply it to large... (6 Replies)
Discussion started by: geomarine
6 Replies

4. UNIX for Beginners Questions & Answers

Match Fields between two files, print portions of each file together when matched in ([g]awk)'

I've written an awk script to compare two fields in two different files and then print portions of each file on the same line when matched. It works reasonably well, but every now and again, I notice some errors and cannot seem to figure out what the issue may be and am turning to you for help. ... (2 Replies)
Discussion started by: jvoot
2 Replies

5. Shell Programming and Scripting

awk to print fields that match using conditions and a default value for non-matching in two files

Trying to use awk to match the contents of each line in file1 with $5 in file2. Both files are tab-delimited and there may be a space or special character in the name being matched in file2, for example in file1 the name is BRCA1 but in file2 the name is BRCA 1 or in file1 name is BCR but in file2... (6 Replies)
Discussion started by: cmccabe
6 Replies

6. Shell Programming and Scripting

awk partial string match and add specific fields

Trying to combine strings that are a partial match to another in $1 (usually below it). If a match is found than the $2 value is added to the $2 value of the match and the $3 value is added to the $3 value of the match. I am not sure how to do this and need some expert help. Thank you :). file ... (2 Replies)
Discussion started by: cmccabe
2 Replies

7. Shell Programming and Scripting

awk help: Match data fields from 2 files & output results from both into 1 file

I need to take 2 input files and create 1 output based on matches from each file. I am looking to match field #1 in both files (Userid) and create an output file that will be a combination of fields from both file1 and file2 if there are any differences in the fields 2,3,4,5,or 6. Below is an... (5 Replies)
Discussion started by: ambroze
5 Replies

8. Shell Programming and Scripting

how to match fields from different files in PERL

Howdy! I have multiple files with tab-separated data: File1_filtered.txt gnl|Amel_4.0|Group3.29 1 G R 42 42 60 15 ,.AAA.aa,aa.A.. hh00/f//hD/h/hh gnl|Amel_4.0|Group3.29 2 C Y 36 36 60 5 T.,T, LggJh gnl|Amel_4.0|Group3.29 3 A R 27 27 60 9 Gg,,.gg., B6hcc22_c File2_filtered.txt ... (3 Replies)
Discussion started by: sramirez
3 Replies

9. UNIX for Dummies Questions & Answers

Match values from 2 files and append certain fields

Hi, I need help on appending certain field in my file1.txt based on matched patterns in file2.txt using awk or sed. The blue color need to match with one of the data in field $2 in file2.txt. If match, BEGIN and FINISHED value in red will have a new value from field $3 and $4 accordingly. ... (1 Reply)
Discussion started by: redse171
1 Replies

10. Shell Programming and Scripting

Match two files and divide fields

I have two files that have the date field in common. I request your help with some script that divide each field value from file1 by the correspond field value of the file2 only when the field date is equal in both files. Thanks in advance ! This is a sample of the files file 1 12/16/2010,... (2 Replies)
Discussion started by: csierra
2 Replies
Login or Register to Ask a Question