使用键值连接两个csv文件 [英] join two csv files with key value

问题描述

我有两个csv文件，我想使用键值，城市的列来加入他们。

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

一个csv文件，d01.csv有这种形式，

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

.csv，具有下一个结构，

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

我需要一个新的csv文件，其中有一列城市出现在2个csv文件中。

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

b $ b

我试着用

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?

推荐答案

以下是在bash中使用join的方法：

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  

注意，join只输出记录键存在于两个文件中。要获取所有这些文件，请指定要从两个文件中删除记录，指定所需的字段，并为缺少的字段指定默认值：

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

这篇关于使用键值连接两个csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！