How can I get grouped windows in pandas? I.e. like WINDOW OVER ... PARTITION BY ... from SQL

Question

Pandas window functions i.e. rolling work great. However coming from SQL I know, that windows can also be PARTITIONED BY some group.

How can I get grouped windows in pandas? A:

df.groupby(['group']).rolling('10s').mean()

fails with:

TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'MultiIndex'

And if df.rolling('10s', on='group') is used, it only works in case 'group' is int i.e. in SQL some concrete number of preceding/following rows. How can I preserve the window by time?

edit minimal sample:

import random
groups = ['A', 'B']
df = pd.DataFrame({'value': range(60), 'group': [random.choice(groups) for i in range(60)]},index=pd.DatetimeIndex(pd.date_range(start='20160101', end='20160229')))
df.head()

The following works, but does not consider the groups:

df[['value']].rolling('2d').mean().head()

The following does not work for time windows:

df[['group','value']].rolling(3, on='group').mean().head()

and

df.rolling('2D', on='group').mean().head()

fails with: window must be an integer when trying to use a time window.

Answer 1

Drop the index so you can group by. I think the issue with the index column.

df.groupby(['col2','col3'], as_index=False)