CODEWITHSUNDEEP
pythonpandasdatetimedataframe
Eric Hansen
Eric Hansen

Reputation: 1809

Grouping dates by 5 minute periods irrespective of day

I have a DataFrame with data similar to the following 

import pandas as pd; import numpy as np; import datetime; from datetime import timedelta;

df = pd.DataFrame(index=pd.date_range(start='20160102', end='20170301', freq='5min'))
df['value'] = np.random.randn(df.index.size)
df.index += pd.Series([timedelta(seconds=np.random.randint(-60, 60)) 
                       for _ in range(df.index.size)])

which looks like this 

In[37]: df
Out[37]: 
                        value
2016-01-02 00:00:33  0.546675
2016-01-02 00:04:52  1.080558
2016-01-02 00:10:46 -1.551206
2016-01-02 00:15:52 -1.278845
2016-01-02 00:19:04 -1.672387
2016-01-02 00:25:36 -0.786985
2016-01-02 00:29:35  1.067132
2016-01-02 00:34:36 -0.575365
2016-01-02 00:39:33  0.570341
2016-01-02 00:44:56 -0.636312
                      ...
2017-02-28 23:14:57 -0.027981
2017-02-28 23:19:51  0.883150
2017-02-28 23:24:15 -0.706997
2017-02-28 23:30:09 -0.954630
2017-02-28 23:35:08 -1.184881
2017-02-28 23:40:20  0.104017
2017-02-28 23:44:10 -0.678742
2017-02-28 23:49:15 -0.959857
2017-02-28 23:54:36 -1.157165
2017-02-28 23:59:10  0.527642

Now, I'm aiming to get the mean per 5 minute period over the course of a 24 hour day - without considering what day those values actually come from.

How can I do this effectively? I would like to think I could somehow remove the actual dates from my index and then use something like pd.TimeGrouper, but I haven't figured out how to do so.

My not-so-great solution

My solution so far has been to use between_time in a loop like this, just using an arbitrary day. 

aggregates = []
start_time = datetime.datetime(1990, 1, 1, 0, 0, 0)

while start_time < datetime.datetime(1990, 1, 1, 23, 59, 0):
    aggregates.append(
        (
            start_time, 
            df.between_time(start_time.time(), 
                            (start_time + timedelta(minutes=5)).time(),
                            include_end=False).value.mean()
        )
    )
    start_time += timedelta(minutes=5)

result = pd.DataFrame(aggregates, columns=['time', 'value'])

which works as expected 

In[68]: result
Out[68]: 
                   time     value
0   1990-01-01 00:00:00  0.032667
1   1990-01-01 00:05:00  0.117288
2   1990-01-01 00:10:00 -0.052447
3   1990-01-01 00:15:00 -0.070428
4   1990-01-01 00:20:00  0.034584
5   1990-01-01 00:25:00  0.042414
6   1990-01-01 00:30:00  0.043388
7   1990-01-01 00:35:00  0.050371
8   1990-01-01 00:40:00  0.022209
9   1990-01-01 00:45:00 -0.035161
..                  ...       ...
278 1990-01-01 23:10:00  0.073753
279 1990-01-01 23:15:00 -0.005661
280 1990-01-01 23:20:00 -0.074529
281 1990-01-01 23:25:00 -0.083190
282 1990-01-01 23:30:00 -0.036636
283 1990-01-01 23:35:00  0.006767
284 1990-01-01 23:40:00  0.043436
285 1990-01-01 23:45:00  0.011117
286 1990-01-01 23:50:00  0.020737
287 1990-01-01 23:55:00  0.021030

[288 rows x 2 columns]

But this doesn't feel like a very Pandas-friendly solution.

Upvotes: 2

Views: 103

Answers (1)

EdChum
EdChum

Reputation: 394099

IIUC then the following should work:

In [62]:
df.groupby(df.index.floor('5min').time).mean()

Out[62]:
             value
00:00:00 -0.038002
00:05:00 -0.011646
00:10:00  0.010701
00:15:00  0.034699
00:20:00  0.041164
00:25:00  0.151187
00:30:00 -0.006149
00:35:00 -0.008256
00:40:00  0.021389
00:45:00  0.016851
00:50:00 -0.074825
00:55:00  0.012861
01:00:00  0.054048
01:05:00  0.041907
01:10:00 -0.004457
01:15:00  0.052428
01:20:00 -0.021518
01:25:00 -0.019010
01:30:00  0.030887
01:35:00 -0.085415
01:40:00  0.002386
01:45:00 -0.002189
01:50:00  0.049720
01:55:00  0.032292
02:00:00 -0.043642
02:05:00  0.067132
02:10:00 -0.029628
02:15:00  0.064098
02:20:00  0.042731
02:25:00 -0.031113
...            ...
21:30:00 -0.018391
21:35:00  0.032155
21:40:00  0.035014
21:45:00 -0.016979
21:50:00 -0.025248
21:55:00  0.027896
22:00:00 -0.117036
22:05:00 -0.017970
22:10:00 -0.008494
22:15:00 -0.065303
22:20:00 -0.014623
22:25:00  0.076994
22:30:00 -0.030935
22:35:00  0.030308
22:40:00 -0.124668
22:45:00  0.064853
22:50:00  0.057913
22:55:00  0.002309
23:00:00  0.083586
23:05:00 -0.031043
23:10:00 -0.049510
23:15:00  0.003520
23:20:00  0.037135
23:25:00 -0.002231
23:30:00 -0.029592
23:35:00  0.040335
23:40:00 -0.021513
23:45:00  0.104421
23:50:00 -0.022280
23:55:00 -0.021283

[288 rows x 1 columns]

Here I floor the index to '5 min' intervals and then group on the time attribute and aggregate the mean

Upvotes: 3

Related Questions