Techiee Techiee - 11 days ago 6
Bash Question

How to merge two files with a common key in awk?

I have two files k1 and k2 and I am trying to merge both files based on the common key 'Name of Book'.

k1

John | Dreaming of Day | IEEEJournal
Akon | Dreaming of Night | ACMJournal


k2

Dreaming of Day | Fiction
Dreaming of Night | Non-Fiction


kout: Should merge based on Name of Book ie
Dreaming of Day
and
Dreaming of Night


John | Fiction | IEEEJournal
Akon | Non-Fiction | ACMJournal


I have written below awk script:

awk -F"|" 'BEGIN{OFS="|"}FNR==NR{a[$1$2]=$3;next}($1$2 in a && $3=$3"|"a[$1$2])' k1 k2


But it doesn't seem to work.

Answer

A gawk solution using array of arrays

gawk 'BEGIN{FS=" \\| "; OFS=" | "}
    FNR==NR{d[$2][1] = $1; d[$2][3] = $3; next}
    $1 in d{print d[$1][1], $2, d[$1][3]}
    ' k1 k2

It isn't awk solution, using sed and join

join <(sed 's/ | /|/g' k1) <(sed 's/ | /|/g' k2) -1 2 -2 1 -t'|' -o '1.1 2.2 1.3' | sed 's/|/ | /g'

you get

John | Fiction | IEEEJournal
Akon | Non-Fiction | ACMJournal

EDIT AWK VERSION

awk 'BEGIN{FS=" [|] "; OFS=" | "}
    FNR==NR{d1[$2] = $1; d3[$2] = $3; next}
    $1 in d1{print d1[$1], $2, d3[$1]}
    ' k1 k2