Skip to main content
 首页 » 编程设计

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

2025年02月15日8zhengyun_ustc

我有这样的数据:

                       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']  

得到如图所示的结果。

请您参考如下方法:

我认为你需要:

#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 
... 
...