如何在python中按总和和平均列分组? [英] How group by sum and average column in 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

这篇关于如何在python中按总和和平均列分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！