如何在python中按总和和平均列分组?

本文介绍了如何在python中按总和和平均列分组?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

作为输入，我有一个带时间的CSV文件，每次都有一堆数字.

As input I have a CSV file with times and a bunch of numbers for each time.

Time,F1,F2,F3 8:11,5,2,4 9:25,9,8,2 9:39,7,3,2 9:53,6,5,1 10:07,4,6,7 10:21,7,3,1 10:35,5,6,7 11:49,1,2,1 12:03,3,3,1

我想输出按小时平均和总和分组的每小时表格:

I'd like to output the table for each hour grouped by column Avg and Sum:

Time,SUM F1,SUM F2,SUM F3,AVG F1,AVG F2,AVG F3 8:00,5,2,4,5,2,4 9:00,22,16,5,7.3,5.3,1.6 10:00,16,15,15,5.3,5,5 11:00,1,2,1,1,2,1 12:00,3,3,1,3,3,1

到目前为止，我一直在研究使用字典，其中小时是关键，值是计数和总和的列表，然后将总和除以计数即可得出平均值. 我敢肯定，必须有一种更清洁的方法来做到这一点.也许有些图书馆可以使用它.有什么建议吗?

So far I was looking at doing it with a dictionary where hour is a key and value is a list of count and sum, then dividing sum by count to get average. I'm sure there must be cleaner way to do it. Maybe some library can work with this. Any suggestions?

推荐答案

一种熊猫解决方案:

import pandas as pd df = pd.read_csv('f123.csv') df['Time'] = df['Time'].apply(lambda x: x.split(':')[0] + ':00') by_hour = df.groupby('Time') data = {} for name in ['F1', 'F2', 'F3']: data['SUM ' + name] = by_hour[name].sum() data['AVG ' + name] = by_hour[name].mean() res = pd.DataFrame(data) print(res)

打印:

AVG F1 AVG F2 AVG F3 SUM F1 SUM F2 SUM F3 Time 10:00 5.333333 5.000000 5.000000 16 15 15 11:00 1.000000 2.000000 1.000000 1 2 1 12:00 3.000000 3.000000 1.000000 3 3 1 8:00 5.000000 2.000000 4.000000 5 2 4 9:00 7.333333 5.333333 1.666667 22 16 5

另存为csv文件:

res.to_csv('res.csv')

这是res.csv的内容:

Time,AVG F1,AVG F2,AVG F3,SUM F1,SUM F2,SUM F3 10:00,5.333333333333333,5.0,5.0,16,15,15 11:00,1.0,2.0,1.0,1,2,1 12:00,3.0,3.0,1.0,3,3,1 8:00,5.0,2.0,4.0,5,2,4 9:00,7.333333333333333,5.333333333333333,1.6666666666666667,22,16,5