Pandas:使用 groupby 重新采样时间序列 [英] Pandas: resample timeseries with groupby

问题描述

鉴于下面的熊猫数据帧:

Given the below pandas DataFrame:

In [115]: times = pd.to_datetime(pd.Series(['2014-08-25 21:00:00','2014-08-25 21:04:00',
                                            '2014-08-25 22:07:00','2014-08-25 22:09:00']))
          locations = ['HK', 'LDN', 'LDN', 'LDN']
          event = ['foo', 'bar', 'baz', 'qux']
          df = pd.DataFrame({'Location': locations,
                             'Event': event}, index=times)
          df
Out[115]:
                               Event Location
          2014-08-25 21:00:00  foo   HK
          2014-08-25 21:04:00  bar   LDN
          2014-08-25 22:07:00  baz   LDN
          2014-08-25 22:09:00  qux   LDN

我想重新采样数据以按计数每小时聚合一次，同时按位置分组以生成如下所示的数据框:

I would like resample the data to aggregate it hourly by count while grouping by location to produce a data frame that looks like this:

Out[115]:
                               HK    LDN
          2014-08-25 21:00:00  1     1
          2014-08-25 22:00:00  0     2

我尝试了 resample() 和 groupby() 的各种组合，但没有成功.我该怎么办?

I've tried various combinations of resample() and groupby() but with no luck. How would I go about this?

推荐答案

在我原来的帖子中，我建议使用 pd.TimeGrouper.现在，使用 pd.Grouper 而不是 pd.TimeGrouper.语法基本相同，但 TimeGrouper 现在已弃用而支持 pd.石斑鱼.

In my original post, I suggested using pd.TimeGrouper. Nowadays, use pd.Grouper instead of pd.TimeGrouper. The syntax is largely the same, but TimeGrouper is now deprecated in favor of pd.Grouper.

此外，虽然pd.TimeGrouper 只能按DatetimeIndex 分组，但pd.Grouper 可以按日期时间列 分组，您可以通过key 参数.

Moreover, while pd.TimeGrouper could only group by DatetimeIndex, pd.Grouper can group by datetime columns which you can specify through the key parameter.

您可以使用 pd.Grouper 按小时对 DatetimeIndex 的 DataFrame 进行分组:

You could use a pd.Grouper to group the DatetimeIndex'ed DataFrame by hour:

grouper = df.groupby([pd.Grouper(freq='1H'), 'Location'])

使用count来统计每组事件的数量:

use count to count the number of events in each group:

grouper['Event'].count()
#                      Location
# 2014-08-25 21:00:00  HK          1
#                      LDN         1
# 2014-08-25 22:00:00  LDN         2
# Name: Event, dtype: int64

使用 unstack 将 Location 索引级别移动到列级别:

use unstack to move the Location index level to a column level:

grouper['Event'].count().unstack()
# Out[49]: 
# Location             HK  LDN
# 2014-08-25 21:00:00   1    1
# 2014-08-25 22:00:00 NaN    2

然后使用 fillna 将 NaN 更改为零.

and then use fillna to change the NaNs into zeros.

综合起来，

grouper = df.groupby([pd.Grouper(freq='1H'), 'Location'])
result = grouper['Event'].count().unstack('Location').fillna(0)

收益

Location             HK  LDN
2014-08-25 21:00:00   1    1
2014-08-25 22:00:00   0    2

这篇关于Pandas:使用 groupby 重新采样时间序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！