使用 bash/python 合并两个 CSV 文件

Question

我有两个 CSV 文件需要帮助映射/合并：

CSV 文件 1：

"ID","Name","Flavor"

"45fc754d-6a9b-4bde-b7ad-be91ae60f582","test1","m1.medium"

"83dbc739-e436-4c9f-a561-c5b40a3a6da5","test2","m1.tiny"

"ef68fcf3-f624-416d-a59b-bb8f1aa2a769","test3","m1.medium"

CSV 文件 2：

"Name","RAM","Disk","VCPUs"

"m1.medium",4096,40,2

"m1.xlarge",16384,160,8

"m1.tiny",128,1,1

理想的输出是：

"ID","Name","Flavor","RAM","Disk","VCPUs"

"45fc754d-6a9b-4bde-b7ad-be91ae60f582","test1","m1.medium",4096,40,2

"83dbc739-e436-4c9f-a561-c5b40a3a6da5","test2","m1.tiny",128,1,1

"ef68fcf3-f624-416d-a59b-bb8f1aa2a769","test3","m1.medium",4096,40,2

请注意，Flavor在 CSV 文件 1 中和Name在 CSV 文件 2 中是相同的。名称上的差异是用于提取信息的不同工具的结果。

另请注意，CSV File2 有一个flavor/name m1.xlarge. 如上所述，如果m1.xlarge flavor/name在 CSV File1 中未找到 ，则应将其从合并输出中丢弃。

我整天都在做这件事，结果好坏参半。任何想法，将不胜感激。

Answer 1

你可以使用这个awk：awk -v hdr='"ID","Name","Flavor","RAM","Disk","VCPUs"' 'BEGIN {   FS=OFS=","   print hdr}NR == FNR {   a[$1] = $2 FS $3 FS $4   next}$3 in a {   print $0, a[$3]}' file2.csv file1.csv"ID","Name","Flavor","RAM","Disk","VCPUs""45fc754d-6a9b-4bde-b7ad-be91ae60f582","test1","m1.medium",4096,40,2"83dbc739-e436-4c9f-a561-c5b40a3a6da5","test2","m1.tiny",128,1,1"ef68fcf3-f624-416d-a59b-bb8f1aa2a769","test3","m1.medium",4096,40,2

Answer 2

像这样的东西，但你必须玩转报价选项才能看到你喜欢什么。#!/usr/bin/env python3import csvby_name = {}with open('b.csv') as b:    for row in csv.DictReader(b):        name = row.pop('Name')        by_name[name] = rowwith open('c.csv', 'w') as c:    w = csv.DictWriter(c, ['ID', 'Name', 'Flavor', 'RAM', 'Disk', 'VCPUs'])    w.writeheader()    with open('a.csv') as a:        for row in csv.DictReader(a):            try:                match = by_name[row['Flavor']]            except KeyError:                continue            row.update(match)            w.writerow(row)输出：ID,Name,Flavor,RAM,Disk,VCPUs45fc754d-6a9b-4bde-b7ad-be91ae60f582,test1,m1.medium,4096,40,283dbc739-e436-4c9f-a561-c5b40a3a6da5,test2,m1.tiny,128,1,1ef68fcf3-f624-416d-a59b-bb8f1aa2a769,test3,m1.medium,4096,40,2

Answer 3

如果我正确理解了您的问题，并且您想根据列中的字符串将第一个文件中的行与第二个 csvFlavor中的列中具有该值的行进行匹配Name，那么这很容易做到xsv（您'可能需要先安装）：$ xsv join "Flavor" file1.csv "Name" file2.csvID,Name,Flavor,Name,RAM,Disk,VCPUs45fc754d-6a9b-4bde-b7ad-be91ae60f582,test1,m1.medium,m1.medium,4096,40,283dbc739-e436-4c9f-a561-c5b40a3a6da5,test2,m1.tiny,m1.tiny,128,1,1ef68fcf3-f624-416d-a59b-bb8f1aa2a769,test3,m1.medium,m1.medium,4096,40,2您还必须删除重复的Name列，您可以xsv再次使用它：$ xsv join "Flavor" file1.csv "Name" file2.csv | xsv select ID,Name,Flavor,RAM,Disk,VCPUsID,Name,Flavor,RAM,Disk,VCPUs45fc754d-6a9b-4bde-b7ad-be91ae60f582,test1,m1.medium,4096,40,283dbc739-e436-4c9f-a561-c5b40a3a6da5,test2,m1.tiny,128,1,1ef68fcf3-f624-416d-a59b-bb8f1aa2a769,test3,m1.medium,4096,40,2