python之使用聚合值、多列解析 CSV 文件

我想修改此处的帖子 (Parse CSV file and aggregate the values) 以对多列求和，而不是仅求一列。

所以对于这些数据:

CITY,AMOUNT,AMOUNT2,AMOUNTn 
London,20,21,22 
Tokyo,45,46,47 
London,55,56,57 
New York,25,26,27 

我怎样才能得到这个:

CITY,AMOUNT,AMOUNT2,AMOUNTn 
London,75,77,79 
Tokyo,45,46,47 
New York,25,26,27 

我最终会有几千列，不幸的是我不能使用 pandas 包来完成这项任务。这是我刚刚将所有三个 AMOUNT 列聚合为一个的代码，这不是我想要的

from __future__ import division 
import csv 
from collections import defaultdict 
 
def default_factory(): 
    return [0, None, None, 0] 
 
reader = csv.DictReader(open('test_in.txt')) 
cities = defaultdict(default_factory) 
for row in reader: 
    headers = [r for r in row.keys()] 
    headers.remove('CITY') 
    for i in headers: 
        amount = int(row[i]) 
        cities[row["CITY"]][0] += amount 
        max = cities[row["CITY"]][1] 
        cities[row["CITY"]][1] = amount if max is None else amount if amount > max else max 
        min = cities[row["CITY"]][2] 
        cities[row["CITY"]][2] = amount if min is None else amount if amount < min else min 
        cities[row["CITY"]][3] += 1 
 
 
for city in cities: 
    cities[city][3] = cities[city][0]/cities[city][3] # calculate mean 
 
with open('test_out.txt', 'wb') as myfile: 
    writer = csv.writer(myfile, delimiter="\t") 
    writer.writerow(["CITY", "AMOUNT", "AMOUNT2", "AMOUNTn ,"max", "min", "mean"]) 
    writer.writerows([city] + cities[city] for city in cities) 

谢谢你的帮助

请您参考如下方法:

这是使用 itertools.groupby 的一种方法。

import StringIO 
import csv 
import itertools 
 
data = """CITY,AMOUNT,AMOUNT2,AMOUNTn 
London,20,21,22 
Tokyo,45,46,47 
London,55,56,57 
New York,25,26,27""" 
 
# I use StringIO to create a file like object for demo purposes 
f = StringIO.StringIO(data)  
fieldnames = f.readline().strip().split(',') 
key = lambda x: x[0] # the first column will be a grouping key 
# rows must be sorted by city before passing to itertools.groupby 
rows_sorted = sorted(csv.reader(f), key=key) 
outfile = StringIO.StringIO('') 
writer = csv.DictWriter(outfile, fieldnames=fieldnames, lineterminator='\n') 
writer.writeheader() 
for city, rows in itertools.groupby(rows_sorted, key=key): 
    # remove city column for aggregation, convert to ints 
    rows = [[int(x) for x in row[1:]] for row in rows]  
    agg = [sum(column) for column in zip(*rows)] 
    writer.writerow(dict(zip(fieldnames, [city] + agg))) 
 
print outfile.getvalue() 
 
# CITY,AMOUNT,AMOUNT2,AMOUNTn 
# London,75,77,79 
# New York,25,26,27 
# Tokyo,45,46,47