python - 使用 Python 字典在 Python 中合并 CSV 文件

Question

您好，我正在尝试根据公共(public)列或主键合并两个 CSV 文件中的特定字段来创建新的 CSV 文件。我曾尝试在 powershell 中做同样的事情并且它有效但完成过程非常缓慢 - 合并 5000 多个行文件超过 30 分钟所以在 Python 中尝试这个。我是新来的所以请放轻松。

所以两个文件是 infile.csv 和 checkfile.csv，创建的输出文件中的列将基于 infile.csv 中的列。该代码检查 checkfile.csv 中的值，创建 outfile.csv，从 infile.csv 中复制列，并需要根据 checkfile.com 中的相应值重写两个字段的值。详情如下

infile.csv -

"StockNumber","SKU","ChannelProfileID","CostPrice"
"10m_s-vid#APTIIAMZ","2VV-10",3746,0.33
"10m_s-vid#CSE","2VV-10",3746,0.98
"1RR-01#CSE","1RR-01",3746
"1RR-01#PCAWS","1RR-01",3746,
"1m_s-vid_ext#APTIIAMZ","2VV-101",3746,0.42

检查文件.csv

ProductCode, Description, Supplier, CostPrice, RRPPrice, Stock, Manufacturer, SupplierProductCode, ManuCode, LeadTime
2VV-03,3MTR BLACK SVHS M - M GOLD CABLE - B/Q 100,Cables Direct Ltd,0.43,,930,CDL,2VV-03,2VV-03,1
2VV-05,5MTR BLACK SVHS M - M GOLD CABLE - B/Q 100,Cables Direct Ltd,0.54,,1935,CDL,2VV-05,2VV-05,1
2VV-10,10MTR BLACK SVHS M - M GOLD CABLE - B/Q 50,Cables Direct Ltd,0.86,,1991,CDL,2VV-10,2VV-10,1

我得到的 outfile.csv 是 -

StockNumber,SKU,ChannelProfileID,CostPrice
10m_s-vid#APTIIAMZ,2VV-10,"(' ',)", 
10m_s-vid#CSE,2VV-10,"(' ',)", 
1RR-01#CSE,1RR-01,"(' ',)", 
1RR-01#PCAWS,1RR-01,"(' ',)", 
1m_s-vid_ext#APTIIAMZ,2VV-101,"(' ',)", 

但我需要的 outfile.csv 是 -

StockNumber,SKU,ChannelProfileID,CostPrice
10m_s-vid#APTIIAMZ,2VV-10,1991,0.86  
10m_s-vid#CSE,2VV-10,1991,0.86   
1RR-01#CSE,1RR-01
1RR-01#PCAWS,1RR-01          
1m_s-vid_ext#APTIIAMZ,2VV-101

最后是代码——

import csv

with open('checkfile.csv', 'rb') as checkfile:
    checkreader = csv.DictReader(checkfile)

    product_result = dict(
        ((v['ProductCode'], v[' Stock']), (v['ProductCode'], v[' CostPrice']))  for v in checkreader
    )

with open('infile.csv', 'rb') as infile:
    with open('outfile.csv', 'wb') as outfile:
        reader = csv.DictReader(infile)

        writer = csv.DictWriter(outfile, reader.fieldnames)
        writer.writeheader()

        for item in reader:
            result = product_result.get(item['SKU'], " ")

            item['ChannelProfileID'] = result,
            item['CostPrice'] = result

            writer.writerow(item)

Answer 1

你可以让它更简单一些:

import csv

with open('checkfile.csv', 'rb') as checkfile:
    product_result = {
        record['ProductCode']: record for record in csv.DictReader(checkfile)}

with open('infile.csv', 'rb') as infile:
    with open('outfile.csv', 'wb') as outfile:
        reader = csv.DictReader(infile)
        writer = csv.DictWriter(outfile, reader.fieldnames)
        writer.writeheader()
        for item in reader:
            record = product_result.get(item['SKU'], None)
            if record:
                item['ChannelProfileID'] = record[' Stock']  # ???
                item['CostPrice'] = record[' CostPrice']
            else:
                item['ChannelProfileID'] = None
                item['CostPrice'] = None
            writer.writerow(item)

我不确定我用 ??? 评论的行。

此外，如果您真的想生成损坏的 CSV，请随意省略 else 子句。

我用 StringIO 对象测试了它。它产生了您指定的结果，但带有尾随逗号，在检查文件中没有匹配项。

我使用了 Python 2.7 字典理解，因为你用 python-2.7 标记了你的问题。