如何将两个csv文件与公共列值组合，但两个文件的行数不同

Question

file1.csv contains 2 columns: c11;c12
file2.csv contains 2 columns: c21;c22
Common column: c11, c21

示例：

f1.csv格式

a;text_a            
b;text_b            
f;text_f            
x;text_x

二楼.csv

a;path_a
c;path_c
d;path_d
k;path_k
l;path_l
m:path_m

输出f1+f2：

a;text_a;path_a
b;text_b,''
c;'';path_c
d;'';path_d
f;text_f;''
k;'';path_k
l;'';path_l
m;'';path_m
x;text_x;''

如何用python实现？

Answer 1

对于基于一个或多个公共列合并多个文件（甚至是>；2），python中最有效的方法之一是使用“brewery”。您甚至可以指定需要考虑合并哪些字段以及需要保存哪些字段。

import brewery
from brewery
import ds
import sys

sources = [
    {"file": "grants_2008.csv",
     "fields": ["receiver", "amount", "date"]},
    {"file": "grants_2009.csv",
     "fields": ["id", "receiver", "amount", "contract_number", "date"]},
    {"file": "grants_2010.csv",
     "fields": ["receiver", "subject", "requested_amount", "amount", "date"]}
]

创建所有字段的列表并添加文件名以存储有关数据记录来源的信息。浏览源定义并收集字段：

for source in sources:
    for field in source["fields"]:
        if field not in all_fields:

out = ds.CSVDataTarget("merged.csv")
out.fields = brewery.FieldList(all_fields)
out.initialize()

for source in sources:

    path = source["file"]

# Initialize data source: skip reading of headers
# use XLSDataSource for XLS files
# We ignore the fields in the header, because we have set-up fields
# previously. We need to skip the header row.

    src = ds.CSVDataSource(path,read_header=False,skip_rows=1)

    src.fields = ds.FieldList(source["fields"])

    src.initialize()


    for record in src.records():

   # Add file reference into ouput - to know where the row comes from
    record["file"] = path

        out.append(record)

# Close the source stream

    src.finalize()


cat merged.csv | brewery pipe pretty_printer