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

Question

嗨，我正在尝试创建一个新的CSV文件从合并的特定字段在两个CSV文件基于一个公共列或主键。我尝试过在powershell中做同样的事情，它很有效，但是完成过程非常慢-合并5000多行文件需要30多分钟，所以在Python中尝试了这个过程。我是新来的，所以请对我从容不迫。在

所以有两个文件填充.csv以及检查文件.csv在输出文件中创建的列将基于中的列填充.csv. 代码检查中的值检查文件.csv，创建输出文件.csv，从中复制列填充.csv需要根据中的相应值重写两个字段的值checkfile.com网站. 以下是细节

在填充.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在

^{pr2}$

在输出文件.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,"(' ',)", 

但是输出文件.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对象测试了它。它产生了您指定的结果，但是后面有逗号，checkfile中没有匹配项。在

我使用了python2.7dict理解，因为你用Python-2.7标记了问题。在

Answer 2

import re
import csv
import collections
import glob

# Variables

total_record = []
headerCount = 0

for file in glob.glob("*.csv"):
    print(file)

    with open(file, 'r') as f:
        reader = csv.reader(f)
        list_record = list(reader)
        if headerCount == 0:
            headerCount = 1
            total_record.extend(list_record)
        else:
            list_record.pop(0)
            total_record.extend(list_record)

with open('combine.csv', 'w') as csvFile:
    writer = csv.writer(csvFile)
    writer.writerows(total_record)

Answer 3

import csv

product_result = {}

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

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

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'])
            if result:
               item['ChannelProfileID'], item['CostPrice'] = result
            else:
               item['ChannelProfileID'] = item['CostPrice'] = None

            writer.writerow(item)