Join two csv files with key value

Question

I have two csv files, I want to join them using the key value, city column.

One csv file, d01.csv has this form,

Barcelona, 19.5, 29.5 Tarragona, 20.4, 31.5 Girona, 17.2, 32.5 Lleida, 16.5, 33.5 Vic, 17.5, 31.4 

The other, d02.csv, has the following structure,

 City, Data, TMax, TMin Barcelona, 20140916, 19.9, 28.5 Tarragona, 20140916, 21.4, 30.5 Lleida, 20140916, 17.5, 32.5 Tortosa, 20140916, 20.5, 30.4 

I need a new csv file with a city column that appears in 2 csv files.

 City, Tmin, Tmax, Date, Tmin1, Tmax1 Barcelona, 19.5, 29.5, 20140916, 19.9, 28.5 Tarragona, 20.4, 31.5, 20140916, 21.4, 30.5 Girona, 17.2, 32.5, 20140916, 17.5, 32.5 Lleida, 16.5, 33.5, 20140916, 20.5, 30.4 

I tried to do this with

 join -j 2 -t ',' d01.csv d02.csv | awk -F "," '{print $1, $2, $3, $4, $5} > d03.csv 

but it is not complete ... how can I order a key value?

Answer 1

Here's how to use join in bash:

 { echo "City, Tmin, Tmax, Date, Tmin1, Tmax1" join -t, <(sort d01.csv) <(sed 1d d02.csv | sort) } > d03.csv cat d03.csv 

 City, Tmin, Tmax, Date, Tmin1, Tmax1 Barcelona, 19.5, 29.5, 20140916, 19.9, 28.5 Lleida, 16.5, 33.5 , 20140916, 17.5, 32.5 Tarragona, 20.4, 31.5 , 20140916, 21.4, 30.5 

Note that the connection only displays records where the key exists in both files. To get all of them, indicate that you need the missing entries from both files, specify the required fields and specify the default value for the missing fields:

 join -t, -a1 -a2 -o 0,1.2,1.3,2.2,2.3,2.4 -e '?' <(sort d01.csv) <(sed 1d d02.csv | sort) 

 Barcelona, 19.5, 29.5, 20140916, 19.9, 28.5 Girona, 17.2, 32.5,?,?,? Lleida, 16.5, 33.5 , 20140916, 17.5, 32.5 Tarragona, 20.4, 31.5 , 20140916, 21.4, 30.5 Tortosa,?,?, 20140916, 20.5, 30.4 Vic, 17.5, 31.4,?,?,? 

Answer 2

This awk can do:

 awk 'FNR==NR {a[$1]=$2FS$3FS$4;next} $1 in a {print $0,a[$1]}' OFS=", " d02,csv d01csv Barcelona, 19.5, 29.5, 20140916, 19.9, 28.5 Tarragona, 20.4, 31.5 , 20140916, 21.4, 30.5 Lleida, 16.5, 33.5 , 20140916, 17.5, 32.5