Skip to main content
 首页 » 编程设计

python之如何在检查重复行标题和共同添加新数据时合并多个 csv 文件

2025年05月04日15wuhuacong

我进行了相当彻底的搜索,试图找到我的问题的答案,但没有成功。

为了更好地解释我的问题,我的任务是合并多个 .csv 文件,同时还做一些其他事情。例如,假设我有三个名为 run_1.csv、run_2.csv 和 run_3.csv 的文件,它们都位于一个名为/runs/的目录中

run_1.csv 看起来像:

Name, Mass (kg), run_1 
One, 1, 5.4 
Two, 2, 4.5 
Three, 3, 6.5 

run_2.csv 看起来像:

Name, Mass (kg), run_2 
One, 1, 5.7 
Two, 2, 6.7 

run_3.csv 看起来像:

Name, Mass (kg), run_3 
One, 1, 4.7 
Three, 3, 5.9 
Four, 4, 2.0 

我希望我的输出文件 (output.csv) 看起来像这样:(注意,行的顺序无关紧要)

Name, Mass (kg), run_1, run_2, run_3 
One, 1, 5.4, 5.7, 4.7 
Two, 2, 4.5, 6.7,  
Three, 3, 6.5, , 5.9  
Four, 4, , , 2.0 

目前,我在 csv 模块中工作,并做了这样的事情:

import os 
import csv 
 
fields = ['name', 'mass', 'run_1', 'run_2', 'run_3'] 
 
with open('output.csv', 'wb') as csvfile : 
writer = csv.writer(csvfile, delimiter=",") 
writer.writerow(fields) #write the header 
 
file_names= [] 
for file in os.listdir(/runs/): 
    file_names.append(file) 
 
    with open(/runs/+file_name+'.csv', 'rb') as infile: 
        reader = csv.reader(infile) 
        reader.next() #just skipping the first row, the header 
 
        entries = set() 
        for row in reader: 
            line = [] 
            key = row[0] 
            time = row[2] 
 
            if key not in entries: 
                row.remove(row[-1]) 
                line.extend(row) 
                for number in images_full: 
                    line.append('') 
 
                line.insert(fields.index(file_name.strip('.csv')), time) 
 
                writer.writerow(line) 
 
            elif key in entries: 
                row.remove(row[-1]) 
                line.extend(row) 
                for number in images_full: 
                    line.append('') 
 
                line.insert(fields.index(file_name.strip('.csv')), time) 
 
                writer.writerow(line) #BUT, I only want it too add this data into the missing spot, not overwrite the whole line! 

所以我很茫然,非常感谢任何帮助。输入的 csv 文件可以更改,但我相信有一种方法可以按原样完成此操作。

编辑:这是通过将原始 csv 读入字典,然后将它们写出来解决的,见下文:

counter = 0 
with open(/result+total_data_file_name, 'wb') as outfile: 
writer = csv.writer(outfile) 
writer.writerow(fields) 
fields.pop(0) 
 
for names in result.keys(): 
    line = [] 
    name = result.keys()[counter] 
    line.append(name) 
 
    for field_key in fields: 
        try: 
            line.append(result[name][field_key]) 
 
        except KeyError: 
            line.append('') 
    counter += 1 
 
    writer.writerow(line) 

请您参考如下方法:

这会将 header 下方的所有值放入字典中以去除重复项,您可以只写 header ,然后写键/值。

from collections import defaultdict 
new_data_dict = {} 
 
files = ["in.csv","in2.csv","in3.csv"] 
for f in files: 
    with open(f) as f: 
        f.next() 
        for row in f: 
            row = row.strip().split(",") 
            new_data_dict.setdefault(row[0],set()) 
            new_data_dict[row[0]].update(row[1:]) 
 
{'Four': set([' 2.0', ' 4']), 'Three': set([' 3', ' 6.5', ' 5.9']), 'Two': set([' 2', ' 6.7', ' 4.5']), 'One': set([' 5.7', ' 5.4', ' 1', ' 4.7'])} 

写入数据:

import csv 
new_data_dict = {} 
files = ["in.csv","in2.csv","in3.csv"] 
headers = set() 
for f in files: 
    with open(f) as f: 
        headers.update(f.next().rstrip().split(",")[2:]) 
        for row in f: 
            row = row.strip().split(",") 
            new_data_dict.setdefault(row[0],set()) 
            new_data_dict[row[0]].update(row[1:]) 
headers = ["Name","Mass (kg)"] + sorted(headers,key=lambda x: int(x.split("_")[-1])) 
 
with open("out.csv","w") as out: 
    writer = csv.writer(out) 
    writer.writerow(headers) 
    for k,v in new_data_dict.items(): 
        writer.writerow([k]+list(v)) 

保持秩序:

for f in files: 
    with open(f) as f: 
        headers.update(f.next().rstrip().split(",")[2:]) 
        for row in f: 
            row = row.strip().split(",") 
            new_data_dict.setdefault(row[0],[]) 
            new_data_dict[row[0]]+= row[1:] 
headers = ["Name","Mass (kg)"] + sorted(headers,key=lambda x: int(x.split("_")[-1])) 
 
with open("out.csv","w") as out: 
    writer = csv.writer(out) 
    writer.writerow(headers) 
    for k,v in new_data_dict.items(): 
        writer.writerow([k]+sorted(set(v),key=lambda  x: new_data_dict[k].index(x))) 
 
Name,Mass (kg), run_1, run_2, run_3 
Four, 4, 2.0 
Three, 3, 6.5, 5.9 
Two, 2, 4.5, 6.7 
One, 1, 5.4, 5.7, 4.7