每分钟平均值的csv文件

Question

我有一个很大的csv文件，这个文件每10秒记录一次时间和数值。这个csv文件的内容大概是这样的：

Datetime             Data  
2008-10-01 12:00:10, 34  
2008-10-01 12:00:20, 55  
2008-10-01 12:00:30, 46  
2008-10-01 12:00:40, 33  
2008-10-01 12:00:50, 55  
2008-10-01 12:01:00, 21  
2008-10-01 12:01:10, 2  
2008-10-01 12:01:20, 34  
2008-10-01 12:01:30, 521  
2008-10-01 12:01:40, 45  
2008-10-01 12:01:50, 32  
2008-10-01 12:02:00, 34  

我想写一个脚本，来计算每分钟的平均值，然后把结果写入一个新的csv文件，输出的格式应该是这样的：

Datetime             Data  
2008-10-01 12:00:00, 40.67  
2008-10-01 12:01:00, 111.33  

有没有人知道怎么做到这一点？或者有什么模块推荐给我，或者有没有相关的例子可以参考？

Answer 1

我觉得最简单的方法就是把时间当作字符串来处理，而不是当作时间，然后使用 itertools.groupby 这个工具。

from csv import reader
from itertools import groupby

lines = """Datetime             Data
2008-10-01 12:00:10, 34
2008-10-01 12:00:20, 55
2008-10-01 12:00:30, 46
2008-10-01 12:00:40, 33
2008-10-01 12:00:50, 55
2008-10-01 12:01:00, 21
2008-10-01 12:01:10, 2
2008-10-01 12:01:20, 34
2008-10-01 12:01:30, 521
2008-10-01 12:01:40, 45
2008-10-01 12:01:50, 32
2008-10-01 12:02:00, 34"""

lines = iter(lines.splitlines())

# above this is just for testing, really you'd do
# with open('filename', 'rb') as lines:
# and indent the rest

next(lines)

for minute, group in groupby(reader(lines), lambda row: row[0][:16]):
    group = list(group)
    print minute, sum(float(row[1]) for row in group) / len(group)

Answer 2

使用 csv.reader 来解析文件，然后用字典来整理结果。你可以用 str.rpartition 方法来分离出秒数。接着，使用 sum 和 len 来计算平均值：

data = '''\
2008-10-01 12:00:10, 34  
2008-10-01 12:00:20, 55  
2008-10-01 12:00:30, 46  
2008-10-01 12:00:40, 33  
2008-10-01 12:00:50, 55  
2008-10-01 12:01:00, 21  
2008-10-01 12:01:10, 2  
2008-10-01 12:01:20, 34  
2008-10-01 12:01:30, 521  
2008-10-01 12:01:40, 45  
2008-10-01 12:01:50, 32  
2008-10-01 12:02:00, 34
'''.splitlines()

import csv

d = {}
for timestamp, value in csv.reader(data):
    minute, colon, second = timestamp.rpartition(':')
    if minute not in d:
        d[minute] = [float(value)]
    else:
        d[minute].append(float(value))

for minute, values in sorted(d.items()):
    avg_value = sum(values) / len(values)
    print minute + ',' + str(avg_value)