join two csv files with key value

Question

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

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 one, d02.csv, has the next 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 column of cities which appear in the 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 that 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 the 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 join only outputs records where the key exists in both files. To get all of them, specify that you want missing records from both files, specify the fields you want, and give a 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

I suggest the CSV Cruncher which takes CSV files as SQL tables and then allows SQL queries, resulting in another CSV file.

Example:

crunch input.csv output.csv \
   "SELECT AVG(duration) AS durAvg FROM (SELECT * FROM indata ORDER BY duration LIMIT 2 OFFSET 6)"

The tool needs Java 5 or later.

Some of the advantages:

• You really get CSV support, not just "let's assume the data is correct".
• You can join on multiple keys.
• Easier to use and understand than join-based solutions.
• You can combine more than 2 CSV files.
• You can join by SQL expressions - the values don't have to be the same.

Disclaimer: I wrote that tool. Unknown project state - Google Code was closed and I didn't transfer it soon enough. I might have a look at it if someone is insterested.