86

I have a time series object grouped of the type <pandas.core.groupby.SeriesGroupBy object at 0x03F1A9F0>. grouped.sum() gives the desired result but I cannot get rolling_sum to work with the groupby object. Is there any way to apply rolling functions to groupby objects? For example:

x = range(0, 6)
id = ['a', 'a', 'a', 'b', 'b', 'b']
df = DataFrame(zip(id, x), columns = ['id', 'x'])
df.groupby('id').sum()
id    x
a    3
b   12

However, I would like to have something like:

  id  x
0  a  0
1  a  1
2  a  3
3  b  3
4  b  7
5  b  12
smci
  • 32,567
  • 20
  • 113
  • 146

5 Answers5

141

For the Googlers who come upon this old question:

Regarding @kekert's comment on @Garrett's answer to use the new

df.groupby('id')['x'].rolling(2).mean()

rather than the now-deprecated 

df.groupby('id')['x'].apply(pd.rolling_mean, 2, min_periods=1)

curiously, it seems that the new .rolling().mean() approach returns a multi-indexed series, indexed by the group_by column first and then the index. Whereas, the old approach would simply return a series indexed singularly by the original df index, which perhaps makes less sense, but made it very convenient for adding that series as a new column into the original dataframe.

So I think I've figured out a solution that uses the new rolling() method and still works the same:

df.groupby('id')['x'].rolling(2).mean().reset_index(0,drop=True)

which should give you the series

0    0.0
1    0.5
2    1.5
3    3.0
4    3.5
5    4.5

which you can add as a column:

df['x'] = df.groupby('id')['x'].rolling(2).mean().reset_index(0,drop=True)
Kevin Wang
  • 2,673
  • 2
  • 10
  • 18
  • I think you can use `.transform` rather than reset_index? – TMrtSmith Nov 17 '17 at 15:16
  • 19
    This actually fails if you're grouping by multiple columns. Dropping the first argument (levels) solves this though as it removes all levels by default. So the line becomes `df['x'] = df.groupby('id')['x'].rolling(2).mean().reset_index(drop=True)` – Kartik Sreenivasan Jan 22 '18 at 08:21
  • 13
    As another maddening nuance, use `groupby(..., sort=False)` if your group variable is not already sorted. I was getting really bizarre results when adding this rolling mean as a new column because the order didn't match the original df. – Hendy Feb 23 '19 at 22:09
  • Very useful information. a) They should add this to their [pandas Cookbook](https://pandas.pydata.org/pandas-docs/stable/user_guide/cookbook.html) b) Can you raise some pandas bugs on the change in functionality? They should consider the consequences better before they deprecate. – smci Jun 29 '19 at 03:24
  • Could you elaborate on why we should put ```.rolling(2)```, i.e. why ```window=2``` here? Is it because there are 2 groups 'a' and 'b'? – uniquegino Feb 02 '20 at 20:35
  • @uniquegino it's been a while since I looked at this, but I think the 2 is just an example. The question asked for a rolling sum, and the example given was a rolling sum of 2. A rolling sum with a window of 2 just means that the rolling sum is a sum over 2 values (the current value and the previous value). So the rolling sum for the 5th row = the value of the 5th row + the value of the 4th row. (if window=3, then it would be 5th+4th+3rd). – Kevin Wang Feb 03 '20 at 22:12
  • @KevinWang yes, I am aware of the application of ```window``` but I feel the last row in the original question is the sum of all values (12=3+4+5) in group b, i.e. ```window=3```, which confused me the most - how ```.rolling(2).mean()``` magically served the cumsum needs in each group. I must be missing something basic but couldn't think through right not – uniquegino Feb 03 '20 at 22:30
  • @uniquegino oh gotcha. You're right, that original example was cumsum, but the question also asked about rolling functions (specifically, in the comments: `Yes, ideally cumsum and any rolling function(mean, sum, std).`), of which rolling(2) was an example. – Kevin Wang Feb 03 '20 at 22:45
82

cumulative sum

To answer the question directly, the cumsum method would produced the desired series:

In [17]: df
Out[17]:
  id  x
0  a  0
1  a  1
2  a  2
3  b  3
4  b  4
5  b  5

In [18]: df.groupby('id').x.cumsum()
Out[18]:
0     0
1     1
2     3
3     3
4     7
5    12
Name: x, dtype: int64

pandas rolling functions per group

More generally, any rolling function can be applied to each group as follows (using the new .rolling method as commented by @kekert). Note that the return type is a multi-indexed series, which is different from previous (deprecated) pd.rolling_* methods.

In [10]: df.groupby('id')['x'].rolling(2, min_periods=1).sum()
Out[10]:
id
a   0   0.00
    1   1.00
    2   3.00
b   3   3.00
    4   7.00
    5   9.00
Name: x, dtype: float64

To apply the per-group rolling function and receive result in original dataframe order, transform should be used instead:

In [16]: df.groupby('id')['x'].transform(lambda s: s.rolling(2, min_periods=1).sum())
Out[16]:
0    0
1    1
2    3
3    3
4    7
5    9
Name: x, dtype: int64

deprecated approach

For reference, here's how the now deprecated pandas.rolling_mean behaved:

In [16]: df.groupby('id')['x'].apply(pd.rolling_mean, 2, min_periods=1)
Out[16]: 
0    0.0
1    0.5
2    1.5
3    3.0
4    3.5
5    4.5
Garrett
  • 47,045
  • 6
  • 61
  • 50
  • 51
    pd.rolling_mean is now deprecated for Series and will be removed, use `df.groupby('id')['x'].rolling(2).mean()` instead – kekert Oct 12 '16 at 15:39
  • in case you need it sorted to the original index efficiently: ```df.reset_index().groupby('id', sort=False)['x'].rolling(2, min_periods=1).mean().sort_index(level=1).reset_index(drop=True)``` – nrcjea001 Jul 22 '22 at 09:23
  • if original index is already sorted then replace ```df.reset_index()``` with ```df``` – nrcjea001 Jul 22 '22 at 09:25
11

Here is another way that generalizes well and uses pandas' expanding method.

It is very efficient and also works perfectly for rolling window calculations with fixed windows, such as for time series. 

# Import pandas library
import pandas as pd

# Prepare columns
x = range(0, 6)
id = ['a', 'a', 'a', 'b', 'b', 'b']

# Create dataframe from columns above
df = pd.DataFrame({'id':id, 'x':x})

# Calculate rolling sum with infinite window size (i.e. all rows in group) using "expanding"
df['rolling_sum'] = df.groupby('id')['x'].transform(lambda x: x.expanding().sum())

# Output as desired by original poster
print(df)
  id  x  rolling_sum
0  a  0            0
1  a  1            1
2  a  2            3
3  b  3            3
4  b  4            7
5  b  5           12
Sean McCarthy
  • 4,838
  • 8
  • 39
  • 61
  • 1
    Do you have anything to back up that this is "very efficient"? Generally with pandas, doing any sort of iteration (eg. "transform", or "apply") is a major performance hit, compared to doing the same thing with vector operations (which the built-ins of ".sum", ".rolling", etc. will all be). I know Pandas does do some pre-inspection on the iteration loops to see if it can optimize it for you, but in general iteration should be avoided if performance is a concern. – bwest87 Dec 04 '19 at 21:50
  • 1
    I am sorry I can only give you one upvote, I'm considering creating new accounts to give more credit to this answer. It's the only one that worked for me grouping on multiple columns, thanks! – sousben Mar 22 '20 at 12:19
  • 1
    Cool. This can apply exponential moving average. ```q['exponential_ave'] = q.groupby('id')['x'].transform(lambda x: x.ewm(com=0.2).mean())``` – Darkhan Apr 19 '20 at 22:11
  • 1
    What's the difference between this using `expanding` vs using `rolling`? – liang Jun 25 '21 at 22:55
  • 1
    @liang [this article](https://www.mikulskibartosz.name/the-difference-between-the-expanding-and-rolling-window-in-pandas/) explains it better than I can. In rolling functions the window size remains constant whereas in expanding functions it changes. See [this answer](https://stackoverflow.com/a/59986646/3385948) as well. – Sean McCarthy Jun 25 '21 at 23:08
4

If you need to reassign the grouped-rolling-function back to the original Dataframe, while keeping order and groups you can use the transform function.

df.sort_values(by='date', inplace=True)
grpd = df.groupby('group_key')
#using center=false to assign values on window's last row
df['val_rolling_7_mean'] = grpd['val'].transform(lambda x: x.rolling(7, center=False).mean())
yoav_aaa
  • 367
  • 2
  • 11
3

I'm not sure of the mechanics, but this works. Note, the returned value is just an ndarray. I think you could apply any cumulative or "rolling" function in this manner and it should have the same result.

I have tested it with cumprod, cummax and cummin and they all returned an ndarray. I think pandas is smart enough to know that these functions return a series and so the function is applied as a transformation rather than an aggregation.

In [35]: df.groupby('id')['x'].cumsum()
Out[35]:
0     0
1     1
2     3
3     3
4     7
5    12

Edit: I found it curious that this syntax does return a Series:

In [54]: df.groupby('id')['x'].transform('cumsum')
Out[54]:
0     0
1     1
2     3
3     3
4     7
5    12
Name: x
Zelazny7
  • 39,946
  • 18
  • 70
  • 84