我进行了相当彻底的搜索,试图找到我的问题的答案,但没有成功。
为了更好地解释我的问题,我的任务是合并多个 .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
