I have file1 as follows:


and file2 as follows:


I would like to join the two files on fields 1 and 3 of file1 and fields 1 and 2 of file2 (e.g. 2010 and 0 for the first record in both files) and the output needs to look like this:


In other words, I need to append field 3 of file2 to file1 for matching rows. When I try the following code, it gives me the data from file1 but not field 3 of file2:

awk -F, 'NR==FNR {a[$1,$2]=$3; next} {print $0,a[$1,$2]}' OFS=, file2 file1

I get, incorrectly:


Notice the commas at the end but no data. I figured that it is probably because the matching fields are not adjacent to each other in file1. If I reorder the fields in file1 as follows,

awk -F, '{print $1,$3,$2,$4}' OFS=, file1 > file1_mod

then the join code works. My question is: How do I join without reordering the fields?

Please note that the native Bash 'join' command may be used with some tricks to enable it to join on more than one field, but I am not interested in that solution. I want to use awk.

  • 4
    Based on your description, you'd want print $0,a[$1,$3] not print $0,a[$1,$2] Commented Sep 3, 2020 at 19:44
  • It worked. Could you post it as an answer so that I can mark it as accepted? Commented Sep 3, 2020 at 20:10
  • It seems that using print $0,a[$1,$2] is a typo of the correct print $0,a[$1,$3]. I believe that the question could be closed.
    – user232326
    Commented Sep 8, 2020 at 16:50
  • 1
    @Isaac, it was not a typo. It was a misunderstanding on my part regarding what the code was doing. While the question can indeed be closed since steeldriver answered my question, it will perhaps be better if he posts it as an answer and not as a comment and I can then mark it as accepted. Commented Sep 9, 2020 at 12:19


You must log in to answer this question.

Browse other questions tagged .