CODEWITHSUNDEEP
pythondatetimetimegroup-bypandas
joeb1415
joeb1415

Reputation: 547

Faster way to groupby time of day in pandas

I have a time series of several days of 1-minute data, and would like to average it across all days by time of day.

This is very slow:

from datetime import datetime
from pandas import date_range, Series
time_ind = date_range(datetime(2013, 1, 1), datetime(2013, 1, 10), freq='1min')
all_data = Series(randn(len(time_ind)), time_ind)
time_mean = all_data.groupby(lambda x: x.time()).mean()

Takes almost a minute to run!

While something like:

time_mean = all_data.groupby(lambda x: x.minute).mean()

takes only a fraction of a second.

Is there a faster way to group by time of day?

Any idea why this is so slow?

Upvotes: 5

Views: 8029

Answers (2)

Andy Hayden
Andy Hayden

Reputation: 375675

It's faster to groupby the hour/minute/.. attributes rather than .time. Here's Jeff's baseline:

In [11]: %timeit all_data.groupby(all_data.index.time).mean()
1 loops, best of 3: 202 ms per loop

and without time it's much faster (the fewer attributes the faster it is):

In [12]: %timeit all_data.groupby(all_data.index.hour).mean()
100 loops, best of 3: 5.53 ms per loop

In [13]: %timeit all_data.groupby([all_data.index.hour, all_data.index.minute, all_data.index.second, all_data.index.microsecond]).mean()
10 loops, best of 3: 20.8 ms per loop

Note: time objects don't accept a nanosecond (but that's DatetimeIndex's resolution).

We should probably convert the index to have time objects to make this comparison fair:

In [21]: res = all_data.groupby([all_data.index.hour, all_data.index.minute, all_data.index.second, all_data.index.microsecond]).mean()

In [22]: %timeit res.index.map(lambda t: datetime.time(*t))
1000 loops, best of 3: 1.39 ms per loop

In [23]: res.index = res.index.map(lambda t: datetime.time(*t))

So it's around 10 times faster for maximum resolution, and you can easily make it coarser (and faster) e.g. groupby just the hour and minute..

Upvotes: 2

bmu
bmu

Reputation: 36214

Both your "lambda-version" and the time property introduced in version 0.11 seems to be slow in version 0.11.0: 

In [4]: %timeit all_data.groupby(all_data.index.time).mean()
1 loops, best of 3: 11.8 s per loop

In [5]: %timeit all_data.groupby(lambda x: x.time()).mean()
Exception RuntimeError: 'maximum recursion depth exceeded while calling a Python object' in <type 'exceptions.RuntimeError'> ignored
Exception RuntimeError: 'maximum recursion depth exceeded while calling a Python object' in <type 'exceptions.RuntimeError'> ignored
Exception RuntimeError: 'maximum recursion depth exceeded while calling a Python object' in <type 'exceptions.RuntimeError'> ignored
1 loops, best of 3: 11.8 s per loop

With the current master both methods are considerably faster:

In [1]: pd.version.version
Out[1]: '0.11.1.dev-06cd915'

In [5]: %timeit all_data.groupby(lambda x: x.time()).mean()
1 loops, best of 3: 215 ms per loop

In [6]: %timeit all_data.groupby(all_data.index.time).mean()
10 loops, best of 3: 113 ms per loop
'0.11.1.dev-06cd915'

So you can either update to a master or wait for 0.11.1 which should be released this month.

Upvotes: 3

Related Questions