0

I'm trying to merge 2 files together using awk. I want to print fields 1,2 from file1 as they are and replace field 3 according to a mapping stored in file 2.

File1:

1,top,yel
2,dress,bl
3,jeans,bl

File2:

bl,blue
yel,yellow

Desired output:

1,top,yellow
2,dress,blue
3,jeans,blue

What I tried so far

Here's the awk command I tried so far:

gawk 'BEGIN {FS=OFS=","} NR==FNR {seen[$3]=$1 "," $2;next} ($1) in seen {print seen[$1],$2}' file1.csv file2.csv

However when "bl" appears twice in file 1, the output only includes one of the occurences. In the above example it's missing line "2" from file1.

3,jeans,blue
1,top,yellow

1 Answer 1

2

Based on your desired output, it sounds like what you actually want to do is read file2.csv first, creating a mapping between the fields, and then apply that to file1.csv

awk 'BEGIN{OFS=FS=","} NR==FNR{clr[$1]=$2; next} {$3=clr[$3]; print}' file2.csv file1.csv
5
  • thank you, can you explain what {$3=clr[$3] does?
    – John
    Commented Apr 15, 2020 at 19:57
  • @John it's a lookup, taking $3 as the key and returning the corresponding value from the (arbitrarily named) array clr. Commented Apr 15, 2020 at 20:00
  • does the first $3 relate to the field number? Also could you kindly explain NR==FNR{clr[$1]=$2; next} - Thanks
    – John
    Commented Apr 15, 2020 at 21:59
  • @John it's the same syntax as your original seen[$3]=$1 "," $2;next except the index is the 1st column (instead of the 3rd) and the assigned value is the 2nd column (instead of a concatenation of the 1st and 2nd) Commented Apr 16, 2020 at 0:06
  • I took the liberty to edit the question title and body to make the OP's intention more clear. If you want, you can remove the corresponding remark in your answer.
    – AdminBee
    Commented Apr 16, 2020 at 7:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .