CODEWITHSUNDEEP
bashcsvawk
Enric Agud Pique
Enric Agud Pique

Reputation: 1105

join two csv files with key value

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?

Upvotes: 16

Views: 16048

Answers (3)

Ondra Žižka
Ondra Žižka

Reputation: 46796

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

Example:

crunch \
   -in d01.csv -in d02.csv  \
   -out joined.csv \
   -sql "SELECT d01.*, d02.* FROM d01 LEFT OUTER JOIN d02 USING City"

The tool needs Java 8 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.

Upvotes: 2

glenn jackman
glenn jackman

Reputation: 246774

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,?,?,?

Upvotes: 18

Jotne
Jotne

Reputation: 41456

This awk may 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

Upvotes: 0

Related Questions