pandas TimeGrouper在多索引 [英] Pandas TimeGrouper on multiindex

问题描述

我有一个multiIndex pandas数据框，其中第一级索引是一个组，第二级索引是时间. 我想做的是，在每个组中，以日内观测值的平均值重新采样为每日频率.

I have a multiIndex pandas dataframe, where the first level index is a group and the second level index is time. What I want to do is, within each group, to resample to daily frequency taking the average of intraday observations.

import pandas as pd
import numpy as np

data = pd.concat([pd.DataFrame([['A']*72, list(pd.date_range('1/1/2011', periods=72, freq='H')), list(np.random.rand(72))], index = ['Group', 'Time', 'Value']).T,
                  pd.DataFrame([['B']*72, list(pd.date_range('1/1/2011', periods=72, freq='H')), list(np.random.rand(72))], index = ['Group', 'Time', 'Value']).T,
                  pd.DataFrame([['C']*72, list(pd.date_range('1/1/2011', periods=72, freq='H')), list(np.random.rand(72))], index = ['Group', 'Time', 'Value']).T],
                  axis = 0).set_index(['Group', 'Time'])

这是我到目前为止尝试过的:

This is what I tried so far:

daily_counts = data.groupby(pd.TimeGrouper('D'), level = ['Time']).mean()

但是出现以下错误:

TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'MultiIndex'

有什么办法解决这个问题吗?

Any idea how to solve this?

推荐答案

您需要先将列强制转换为float，然后使用

You need first cast column to float and then use Grouper:

data['Value'] = data['Value'].astype(float)
daily_counts = data.groupby([pd.Grouper(freq='D', level='Time'), 
                             pd.Grouper(level='Group')])['Value'].mean()

print (daily_counts) 
Time        Group
2011-01-01  A        0.548358
            B        0.612878
            C        0.544822
2011-01-02  A        0.529880
            B        0.437062
            C        0.388626
2011-01-03  A        0.563854
            B        0.479299
            C        0.557190
Name: Value, dtype: float64

另一种解决方案:

data = data.reset_index(level='Group')
print (data.groupby('Group').resample('D')['Value'].mean())

这篇关于 pandas TimeGrouper在多索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！