CSV文件中特定列的总和

Question

这里有一个csv文件，假设叫做 A.csv，里面的内容是：

Place,Hotel,Food,Fare

Norway,Regal,NonVeg,5000    
Poland,Jenny,Italiano,6000    
Norway,Suzane,Vegeterian,4000   
Norway,Regal,NonVeg,5000

我需要解析这个csv文件，并通过命令提示符传递参数来获得输出。

例子1：

mycode.py Place

期望的输出是：

Place,Fare    
Norway,14000  
Poland,6000

例子2：

mycode.py Place Hotel

期望的输出是：

Place,Hotel,Fare
Norway,Regal,10000  
Poland,Jenny,6000  
Norway,Suzane,4000

从上面的例子可以看出，无论你传递什么参数，它都会给你相同项的 Fare 列的总和。

下面是我的代码，我可以传递参数并得到输出，但在计算 Fare 的总和时遇到了困难。有没有人能帮我解决这个问题。

import sys
import csv
import collections

d = collections.defaultdict(list)

Data = []
Result = []
Final = []
Argvs = []
argv_len = len(sys.argv)
index = 0
input = ''

file = open('A.csv', 'rb')
try:
    reader = csv.reader(file)
    for row in reader:
        Data.append(row)

    for x in range(1, argv_len):
        Argvs.append(sys.argv[x])
    Argvs.append('Fare')

    for input in Argvs:
        for y in range(0, len(Data[0])):
            if(input == Data[0][y]):
                for z in range(1, len(Data)):
                    Result.append(Data[z][y])                   
                break       
        Final.append(Result)
        Result = []

    New = []
    NewFinal = []
    for x in range(0, len(Final[0])):
        for y in range(0, len(Final)):
            New.append(Final[y][x])
        NewFinal.append(New)
        New = []
    out = {}
    for a in NewFinal:
        out.setdefault(a[0],[]).append(int(a[-1]))
    with open("output.csv", "wb") as csv_file:
        writer = csv.writer(csv_file,  dialect='excel', delimiter=',')
        writer.writerow(Argvs)
        for k,v in out.iteritems():
            writer.writerow((k,sum(v)))
except Exception,e:
    print str(e)
finally:
    file.close()

我修改了代码，尝试进行分组。现在我能得到 Fare 的汇总，但输出结果并不是我想要的。

所以当我传递：

mycode.py Place Hotel

而不是：

Place,Hotel,Fare  
Norway,Regal,10000  
Poland,Jenny,6000  
Norway,Suzane,4000  

我得到的是：

Place,Hotel,Fare  
Norway,14000  
Poland,6000  

Answer 1

最后我终于得到了我想要的结果。
下面我分享一下最终的代码。

import sys
import csv

Data = []
Result = []
Final = []
Argvs = []
argv_len = len(sys.argv)
index = 0
input = ''

file = open('A.csv', 'rb')
try:
    reader = csv.reader(file)
    for row in reader:
        Data.append(row)

    for x in range(1, argv_len):
        Argvs.append(sys.argv[x])
    Argvs.append('Fare')

    for input in Argvs:
        for y in range(0, len(Data[0])):
            if(input == Data[0][y]):
                for z in range(1, len(Data)):
                    Result.append(Data[z][y])                   
                break       
        Final.append(Result)
        Result = []

    New = []
    NewFinal = []
    for x in range(0, len(Final[0])):
        for y in range(0, len(Final)):
            New.append(Final[y][x])
        NewFinal.append(New)
        New = []
    out = {}
    for a in NewFinal:
        count_val = a[-1]
        del a[-1]
        key_val = ','.join(a)
        out.setdefault(key_val.strip('"'),[]).append(int(count_val))
    with open("output.csv", "wb") as csv_file:
        writer = csv.writer(csv_file, delimiter=',',quotechar=' ')
        writer.writerow(Argvs)
        for k,v in out.iteritems():
            writer.writerow((k,sum(v)))
except Exception,e:
    print str(e)
finally:
    file.close()