如何编译多个csv文件并检查重复行标题及合并新数据

Question

我搜索了很久，想找到我问题的答案，但没有成功。

为了更好地解释我的问题，我的任务是合并多个 .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)

Answer 1

我觉得你想要的是字典来存储键值对。还有，你需要先解析所有的文件，然后再写出任何东西。

补充：如果你需要在某些字段没有条目的情况下留空，可以使用字典的字典。

import os
import csv
import string

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= []

   # Use a dictionary to store result of all runs.
   # Each key is this dictionary is a string like 'One', 'Two', 'Three', etc.
   # The values are themselves dictionaries, with a key of the run index.
   runs = dict()

   # parse all the files first
   for file in os.listdir('runs/'):
      file_names.append(file)

      with open('runs/'+file, 'rb') as infile:
         reader = csv.reader(infile)
         reader.next() #just skipping the first row, the header

         # Get the run index for the sub-key
         temp = string.rstrip(file,'.csv')
         run_index = int(string.lstrip(temp,'runs_'))

         for row in reader:            
            key = row[0]
            index = row[1]
            time = row[2]

            # make the key a string like "Four 4"
            key = key + ' ' + index # use whitespace delimeter

            if key not in runs: 
               # create a new dict entry
               runs[key] = dict()
               runs[key][run_index] = time
            elif key in runs:   
               # add to the existing dict
               value = runs[key]                             
               value[run_index] = time
               runs[key] = value                           

   # find the run with max number of elements in its sub-dictionary
   max_entries = 0
   key_w_max_entries = -1
   for key in runs.keys():
      if len(runs[key].keys()) > max_entries:
         max_entries = len(runs[key].keys())
         key_w_max_entries = key

   # now write out the dictionary values             
   for key in runs.keys():
      line = []
      words = key.split() # split on whitespace
      for word in words:
         line.append(word)
      for i in runs[key_w_max_entries].keys():
         try:
            line.append(str(runs[key][i]))
         except:
            # if the key doesn't exist in the sub-dictionary, fill in a blank
            line.append(' ')
      writer.writerow(line) 

这样会给我一个像这样的文件：

name,mass,run_1,run_2,run_3
One,1, 5.4, 5.7, 4.7
Three,3, 6.5, , 5.9
Two,2, 4.5, 6.7, 
Four,4, , , 2.0

Answer 2

这段代码会把所有在标题下面的值放到一个字典里，并且去掉重复的值。你只需要写下你的标题，然后再写对应的键和值。

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