按公共列组合 2 个 .csv 文件 [英] Combining 2 .csv files by common column

问题描述

我有两个 .csv 文件，其中文件 1 的第一行是:

I have two .csv files where the first line in file 1 is:

MPID,Title,Description,Model,Category ID,Category Description,Subcategory ID,Subcategory Description,Manufacturer ID,Manufacturer Description,URL,Manufacturer (Brand) URL,Image URL,AR Price,Price,Ship Price,Stock,Condition

文件 2 的第一行:

Regular Price,Sale Price,Manufacturer Name,Model Number,Retailer Category,Buy URL,Product Name,Availability,Shipping Cost,Condition,MPID,Image URL,UPC,Description

然后每个文件的其余部分都填充了信息.

and then rest of every file is filled with info.

如您所见，两个文件都有一个名为 MPID 的公共字段(文件 1:col 1，文件 2:col 9，其中第一个 col 是 col 1).

As you can see, both files have a common field called MPID (file 1: col 1, file 2: col 9, where the first col is col 1).

我想创建一个新文件，通过查看此列来合并这两个文件(例如:如果两个文件中都有一个 MPID，那么在新文件中，该 MPID 将与其行一起出现来自文件 1 及其来自文件 2 的行).如果一个 MPID 只出现在一个文件中，那么它也应该进入这个组合文件.

I would like to create a new file which will combine these two files by looking at this column (as in: if there is an MPID that is in both files, then in the new file this MPID will appear with both its row from file 1 and its row from file 2). IF one MPID appears only in one file then it should also go into this combined file.

文件没有以任何方式排序.

The files are not sorted in any way.

如何在带有 shell 脚本或 python 的 debian 机器上执行此操作?

How do I do this on a debian machine with either a shell script or python?

谢谢.

除了分隔字段的逗号之外，两个文件都没有逗号.

Both files dont have commas other than the ones separating the fields.

推荐答案

sort -t , -k index1 file1 > sorted1
sort -t , -k index2 file2 > sorted2
join -t , -1 index1 -2 index2 -a 1 -a 2 sorted1 sorted2

这篇关于按公共列组合 2 个 .csv 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！