0

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

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

Many thanks,

Avinash Raj
  • 172,303
  • 28
  • 230
  • 274
Arpan
  • 3
  • 1
  • 2
    possible duplicate of [How to merge two files using AWK?](http://stackoverflow.com/questions/5467690/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 Dec 12 '14 at 15:18
  • thanks Tensibai, will try and let you know – Arpan Dec 12 '14 at 16:14
  • My problem is very different from [How to merge two files using AWK?](http://stackoverflow.com/questions/5467690/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 Dec 12 '14 at 16:20

1 Answers1

0

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
glenn jackman
  • 238,783
  • 38
  • 220
  • 352