awk - Merge two files by matching columns and append the columns values of second file to the line

Question

I have an unusual merge request in awk. Hoping you could help.

File1

pl1,prop1,20

pl1,prop2,30

pl1,prop3,40

pl2,prop1,70

pl2,prop2,80

pl2,prop3,90

pl3,prop1,120

pl3,prop2,130

pl3,prop3,140

File2

store1,pl1

store2,pl1

store3,pl2

store4,pl3

store5,pl2

store6,pl1

Output:

prop1, store1-20, store2-20, store3-70, store4-120, store5-70, store6-20

prop2, store1-30, store2-30, store3-80, store4-130, store5-80, store6-30

prop3, store1-40, store2-40, store3-90, store4-140, store5-90, store6-40

Rules

file1.column1 should match file2.column2
for all matching lines - file2.column1 should be concatenated with file1.currentLine.column3 should be appended

Many thanks,

possible duplicate of How to merge two files using AWK? (The use case is a little different, but working on second column instead of first one and changing the output order does not sounds too hard, have a try and then ask if you're stuck trying :) ) — Tensibai
My problem is very different from How to merge two files using AWK?. The output is driven by file1.column2 and this value should not be repeated. to add to this, I am still getting hang of Awk, so finding difficult to exploit all the options. — Arpan

glenn jackman glenn jackman · Accepted Answer · 2014-12-12T16:32:08

I'm assuming those blank lines are not actually in your input files.

Using GNU awk which has true arrays of arrays:

gawk -F, '
    NR==FNR { prop[$2][$1] = $3; next } 
    { pl[$2][$1] = 1 } 
    END {
        for (key in prop) {
            printf "%s", key; 
            for (subkey in prop[key]) {
                for (store in pl[subkey]) {
                    printf ", %s-%d", store, prop[key][subkey]
                }
            }
            print ""
        }
    }
' File1 File2

prop1, store1-20, store2-20, store6-20, store3-70, store5-70, store4-120
prop2, store1-30, store2-30, store6-30, store3-80, store5-80, store4-130
prop3, store1-40, store2-40, store6-40, store3-90, store5-90, store4-140

awk - Merge two files by matching columns and append the columns values of second file to the line

1 Answers