Pandas按小时分组，按天汇总

Question

我有一个一周的时间序列，我想对每小时分组的天数进行求和，我已经尝试利用groupby和重采样方法，但没有成功。你知道吗

df.resample('h').sum().plot()

以及

times   = pd.DatetimeIndex(df.index)
grouped = df.groupby([times.hour, times.day])

时间序列示例

在这里编辑我绘制的示例数据：

end,Count
2019-11-21 00:00:00,323
2019-11-21 01:00:00,210
2019-11-21 02:00:00,207
2019-11-21 03:00:00,181
2019-11-21 04:00:00,231
2019-11-21 05:00:00,334
2019-11-21 06:00:00,574
2019-11-21 07:00:00,1176
2019-11-21 08:00:00,2297
2019-11-21 09:00:00,3599
2019-11-21 10:00:00,4084
2019-11-21 11:00:00,3866
2019-11-21 12:00:00,4207
2019-11-21 13:00:00,4673
2019-11-21 14:00:00,4925
2019-11-21 15:00:00,5238
2019-11-21 16:00:00,4972
2019-11-21 17:00:00,5481
2019-11-21 18:00:00,4574
2019-11-21 19:00:00,3756
2019-11-21 20:00:00,2668
2019-11-21 21:00:00,926
2019-11-21 22:00:00,594
2019-11-21 23:00:00,497
2019-11-22 00:00:00,337
2019-11-22 01:00:00,277
2019-11-22 02:00:00,242
2019-11-22 03:00:00,243
2019-11-22 04:00:00,328
2019-11-22 05:00:00,388
2019-11-22 06:00:00,560
2019-11-22 07:00:00,1264
2019-11-22 08:00:00,2674
2019-11-22 09:00:00,4274
2019-11-22 10:00:00,5226
2019-11-22 11:00:00,5180
2019-11-22 12:00:00,5170
2019-11-22 13:00:00,4979
2019-11-22 14:00:00,5320
2019-11-22 15:00:00,5330
2019-11-22 16:00:00,5506
2019-11-22 17:00:00,5586
2019-11-22 18:00:00,4886
2019-11-22 19:00:00,4034
2019-11-22 20:00:00,3733
2019-11-22 21:00:00,2790
2019-11-22 22:00:00,1505
2019-11-22 23:00:00,751
2019-11-23 00:00:00,416
2019-11-23 01:00:00,273
2019-11-23 02:00:00,195
2019-11-23 03:00:00,220
2019-11-23 04:00:00,269
2019-11-23 05:00:00,292
2019-11-23 06:00:00,403
2019-11-23 07:00:00,957
2019-11-23 08:00:00,2771
2019-11-23 09:00:00,5316
2019-11-23 10:00:00,7753
2019-11-23 11:00:00,7822
2019-11-23 12:00:00,8060
2019-11-23 13:00:00,8455
2019-11-23 14:00:00,8878
2019-11-23 15:00:00,10267
2019-11-23 16:00:00,10725
2019-11-23 17:00:00,8563
2019-11-23 18:00:00,5721
2019-11-23 19:00:00,3743
2019-11-23 20:00:00,2677
2019-11-23 21:00:00,2098
2019-11-23 22:00:00,975
2019-11-23 23:00:00,661
2019-11-24 00:00:00,469
2019-11-24 01:00:00,207
2019-11-24 02:00:00,191
2019-11-24 03:00:00,176
2019-11-24 04:00:00,226
2019-11-24 05:00:00,168
2019-11-24 06:00:00,238
2019-11-24 07:00:00,558
2019-11-24 08:00:00,1727
2019-11-24 09:00:00,3751
2019-11-24 10:00:00,5092
2019-11-24 11:00:00,5653
2019-11-24 12:00:00,4931
2019-11-24 13:00:00,4926
2019-11-24 14:00:00,5861
2019-11-24 15:00:00,6462
2019-11-24 16:00:00,7111
2019-11-24 17:00:00,5697
2019-11-24 18:00:00,3976
2019-11-24 19:00:00,2602
2019-11-24 20:00:00,1349
2019-11-24 21:00:00,682
2019-11-24 22:00:00,388
2019-11-24 23:00:00,324
2019-11-25 00:00:00,174
2019-11-25 01:00:00,143
2019-11-25 02:00:00,104
2019-11-25 03:00:00,76
2019-11-25 04:00:00,143
2019-11-25 05:00:00,238
2019-11-25 06:00:00,321
2019-11-25 07:00:00,530
2019-11-25 08:00:00,882
2019-11-25 09:00:00,1739
2019-11-25 10:00:00,2648
2019-11-25 11:00:00,2788
2019-11-25 12:00:00,2797
2019-11-25 13:00:00,2653
2019-11-25 14:00:00,2814
2019-11-25 15:00:00,3128
2019-11-25 16:00:00,3034
2019-11-25 17:00:00,2761
2019-11-25 18:00:00,2420
2019-11-25 19:00:00,1846
2019-11-25 20:00:00,1321
2019-11-25 21:00:00,516
2019-11-25 22:00:00,389
2019-11-25 23:00:00,270
2019-11-26 00:00:00,179
2019-11-26 01:00:00,113
2019-11-26 02:00:00,85
2019-11-26 03:00:00,108
2019-11-26 04:00:00,137
2019-11-26 05:00:00,212
2019-11-26 06:00:00,392
2019-11-26 07:00:00,458
2019-11-26 08:00:00,629
2019-11-26 09:00:00,1224
2019-11-26 10:00:00,1527
2019-11-26 11:00:00,1548
2019-11-26 12:00:00,1625
2019-11-26 13:00:00,1502
2019-11-26 14:00:00,1492
2019-11-26 15:00:00,1477
2019-11-26 16:00:00,1498
2019-11-26 17:00:00,1480
2019-11-26 18:00:00,1098
2019-11-26 19:00:00,1000
2019-11-26 20:00:00,595
2019-11-26 21:00:00,244
2019-11-26 22:00:00,175
2019-11-26 23:00:00,116
2019-11-27 00:00:00,26
2019-11-27 01:00:00,25
2019-11-27 02:00:00,72
2019-11-27 09:00:00,1756
2019-11-27 10:00:00,4008
2019-11-27 11:00:00,3803
2019-11-27 12:00:00,4139
2019-11-27 13:00:00,4607
2019-11-27 14:00:00,5072
2019-11-27 15:00:00,4908
2019-11-27 16:00:00,5401
2019-11-27 17:00:00,5329
2019-11-27 18:00:00,4340
2019-11-27 19:00:00,4048
2019-11-27 20:00:00,2747
2019-11-27 21:00:00,890
2019-11-27 22:00:00,549
2019-11-27 23:00:00,396

有什么想法吗？我不想用循环进行迭代

我想得到一个单独的曲线图，它表示像这样的其他日子的时间间隔的总和 （这只是一天的情节）

样本数据：

print (df)
                     rand  Count
end                             
2019-11-21 00:00:00     1      8
2019-11-21 01:00:00     1      5
2019-11-25 12:00:00     1      7
2019-11-25 13:00:00     1      2
2019-11-25 13:00:00     0      3
2019-11-25 22:00:00     0      5
2019-11-26 04:00:00     1      7
2019-11-26 11:00:00     0      5
2019-11-27 01:00:00     1      8
2019-11-27 01:00:00     0     25
2019-11-27 02:00:00     1     11

Answer 1

我相信你需要^{}和 ^{}与聚合sum：

df1 = df.groupby([df.index.hour.rename('h'), 
                  df.index.day.rename('d')])['Count'].sum().reset_index()
print (df1)

Answer 2

添加另外两个使用pandas.Series.dt.hour和pandas.Series.dt.date生成的列 然后按他们分组。你知道吗