CODEWITHSUNDEEP
bashjoinsedawk
user2761031
user2761031

Reputation: 41

awk match and merge two files on basis of key values

I have two files in which $3,$4 = $3,$2.

file1:

1211,A2,ittp,1,IPSG,a2,PA,3000,3000  
1311,A4,iztv,1,IPSG,a4,PA,240,250  
1411,B4,iztq,0,IPSG,b4,PA,230,250

file2:

TP,0,nttp,0.865556,0.866667  
TP,1,ittp,50.7956,50.65  
TP,1,iztv,5.42444,13.8467  
TP,0,iztq,645.194,490.609

I want to merge these files and print a new file like if file1 $3,$4 = file2 $3,$2 then print merged file like 

TP,1211,A2,ittp,1,IPSG,a2,PA,3000,3000,0.865556,0.866667  
TP,1311,A4,iztv,1,IPSG,a4,PA,240,250,50.7956,50.65  
TP,1411,B4,iztq,0,IPSG,b4,PA,230,250,5.42444,13.8467

BOTH THE FILES ARE CSV FILES.

I tried using awk but I'm not getting the desired output. It's printing only file1. 

$ awk -F, 'NR==FNR{a[$3,$4]=$3$2;next}{print $1, $2, $3, $4, $5, $6, $7, $8, $9, $10 a[$1] }' OFS=, 1.csv 2.csv

Upvotes: 1

Views: 2205

Answers (3)

cotarmanach
cotarmanach

Reputation: 116

Using Join If i1 and i2 are the input files

cat i1.txt | awk -F',' '{print $3 "-" $4 "," $1 "," $2 "," $5 "," $6 "," $7 "," $8 "," $9}' | sort > s1.txt
cat i2.txt | awk -F',' '{print $3 "-" $2 "," $1 "," $4 "," $5 }' | sort > s2.txt
join -t',' s1.txt s2.txt | tr '-' ',' > t12.txt
cat t12.txt | awk -F ',' '{print $10 "," $3 "," $4 "," $1 "," $2 "," $5 "," $6 "," $7 "," $8 "," $9 "," $11 "," $12 }'

Upvotes: 0

Chris Seymour
Chris Seymour

Reputation: 85795

One way with awk:

awk 'NR==FNR{a[$4,$3]=$0;next}($2,$3) in a{print $1,a[$2,$3],$4,$5}' FS=, OFS=, f1 f2
TP,1211,A2,ittp,1,IPSG,a2,PA,3000,3000,50.7956,50.65
TP,1311,A4,iztv,1,IPSG,a4,PA,240,250,5.42444,13.8467
TP,1411,B4,iztq,0,IPSG,b4,PA,230,250,645.194,490.609

Upvotes: 0

Barmar
Barmar

Reputation: 781058

awk -F, 'BEGIN {OFS=",";}
         NR == FNR {a[$3,$4] = $0;}
         NR != FNR && a[$3,$2] {print $1, a[$3,$2], $4, $5;}' 1.csv 2.csv

Upvotes: 1

Related Questions