join 2 files based on 1st & 2nd column of file AND 3rd & 4th column of second file

user_16423872 0
Viewed 96

file1

rs12345  G  C  
rs78901  A  T

file2

3  22745180  rs12345  G  C,G  
12 67182999  rs78901  A  G,T

desired output

3  22745180  rs12345  G  C  
12 67182999  rs78901  A  T

I tried awk 'NR==FNR {h[$1] = $3; next} {print $1,$2,$3,h[$2]}' file1 file2

output generated

3  22745180  rs12345

print first 4 columns of file2 and 3rd column of file1 as 5th col in output

paste join awk
2 Answers

You may use this awk:

awk 'FNR == NR {map[$1,$2] = $3; next} ($3,$4) in map {$NF = map[$3,$4]} 1' f1 f2 | column -t

3   22745180  rs12345  G  C
12  67182999  rs78901  A  T

A more readable version:

awk '
FNR == NR {
   map[$1,$2] = $3
   next
}
($3,$4) in map {
   $NF = map[$3,$4]
}
1' file1 file2 | column -t

Used column -t for tabular output only.

user_5482250

In the case you presented (rows in both files are matched) this will work

paste file1 file2 | awk '{print $4,$5,$6,$7,$3}'
user_116485570
Related