CODEWITHSUNDEEP
pythonpandasdataframetime-series
ileadall42
ileadall42

Reputation: 651

Pandas resample timeseries in to 24hours

I have the data like this:

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

This is a whole year data with different user's score ,I hope to get the data like:

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

The element of dataframe is the score(mean or sum). I have been try like follow:

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

Get the result like the image. enter image description here

Upvotes: 2

Views: 910

Answers (1)

jezrael
jezrael

Reputation: 863246

I think you need:

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

Upvotes: 1

Related Questions