python - Pandas 将时间序列重新采样为 24 小时

Question

我有这样的数据:

                       OwnerUserId  Score
CreationDate        
2015-01-01 00:16:46.963 1491895.0   0.0
2015-01-01 00:23:35.983 1491895.0   1.0
2015-01-01 00:30:55.683 1491895.0   1.0
2015-01-01 01:10:43.830 2141635.0   0.0
2015-01-01 01:11:08.927 1491895.0   1.0
2015-01-01 01:12:34.273 3297613.0   1.0
..........

这是一整年的不同用户评分的数据，希望得到这样的数据:

OwnerUserId   1491895.0  1491895.0  1491895.0  2141635.0 1491895.0
00:00       0.0       3.0          0.0       3.0      5.8
00:01       5.0       3.0          0.0       3.0      5.8
00:02       3.0       33.0         20.0      3.0      5.8
 ......
23:40       12.0      33.0         10.0      3.0      5.8
23:41       32.0      33.0         20.0      3.0      5.8
23:42       12.0      13.0         10.0      3.0      5.8

dataframe 的元素是得分(均值或总和)。我一直在尝试如下:

pd.pivot_table(data_series.reset_index(),index=['CreationDate'],columns=['OwnerUserId'],
               fill_value=0).resample('W').sum()['Score'] 

得到如图所示的结果。

Answer 1

我认为你需要:

#remove `[]` and add parameter values for remove MultiIndex in columns
df = pd.pivot_table(data_series.reset_index(),
                    index='CreationDate',
                    columns='OwnerUserId',
                    values='Score',
                    fill_value=0) 

#truncate seconds and convert to timedeltaindex
df.index = pd.to_timedelta(df.index.floor('T').strftime('%H:%M:%S'))
#or round to minutes
#df.index = pd.to_timedelta(df.index.round('T').strftime('%H:%M:%S'))
print (df)
OwnerUserId  1491895.0  2141635.0  3297613.0
00:16:00             0          0          0
00:23:00             1          0          0
00:30:00             1          0          0
01:10:00             0          0          0
01:11:00             1          0          0
01:12:00             0          0          1

idx = pd.timedelta_range('00:00:00', '23:59:00', freq='T')
#resample by minutes, aggregate sum, for add missing rows use reindex
df = df.resample('T').sum().fillna(0).reindex(idx, fill_value=0)
print (df)
OwnerUserId  1491895.0  2141635.0  3297613.0
00:00:00           0.0        0.0        0.0
00:01:00           0.0        0.0        0.0
00:02:00           0.0        0.0        0.0
00:03:00           0.0        0.0        0.0
00:04:00           0.0        0.0        0.0
00:05:00           0.0        0.0        0.0
00:06:00           0.0        0.0        0.0
...
...