Compare columns from two CSVs and merge on matches

Question

I have two CSV files and am trying to merge them based on the first column in the first file matching the third column in the second file. They lines are not sorted.

file1.csv:

android,1,2
osx,2,5

file2.csv:

Converting,:Developer::|[E],android,Exact,,,,8,31
Converting,:Developer::|[E],osx,Exact,,,,8,31
Converting,:Developer::|[E],windows,Exact,,,,8,31

and would desire the following output.csv:

Converting,:Developer::|[E],android,Exact,,,,8,31,1,2
Converting,:Developer::|[E],osx,Exact,,,,8,31,2,5
Converting,:Developer::|[E],windows,Exact,,,,8,31,,

I have tried every example of

awk -F',' 'FNR==NR.....

that I could find on here but just cant seem to get it right.

Answer 1

You could use join for this

join -1 1 -2 3 -t ',' -a 2 -o 2.{1..9} 1.{2..3}  <(sort file1.csv) <(sort file2.csv)

-1 and -2 specifies which field from the files to compare
-t specifies the seperator to use for the fields
-a 2 says to print lines in <file2> that do not match
-o configures the output based on <file>.<field>

Answer 2

The best tool for the job is probably join but since you mentioned awk, here's another approach:

$ awk -F',' -vOFS="," 'FNR==NR{a[$1]=$2;b[$1]=$3; next}{print $0,a[$3],b[$3]}' file1 file2
Converting,:Developer::|[E],android,Exact,,,,8,31,1,2
Converting,:Developer::|[E],osx,Exact,,,,8,31,2,5
Converting,:Developer::|[E],windows,Exact,,,,8,31,,

It uses two arrays, a and b, which have the 1st field of file1 as keys and the 2nd and 3rd as values, respectively. The FNR==NR{...next} ensures that only the 1st file is saved in the arrays. Then, when processing the second file, we print the file's line ($0), followed by the values corresponding to its first field from arrays a and b. The -vOFS="," sets the output field separator to a comma so we get the desired output format.

Alternatively, slightly more cryptically:

$ awk -F',' -vOFS="," 'FNR==NR{a[$1]=$2","$3;next}{print $0,(a[$3]?a[$3]:",")}' file1 file2
Converting,:Developer::|[E],android,Exact,,,,8,31,1,2
Converting,:Developer::|[E],osx,Exact,,,,8,31,2,5
Converting,:Developer::|[E],windows,Exact,,,,8,31,,