Pandas Rolling Groupby Shift back 1, Trying to lag rolling sum

Question

I am trying to get a rolling sum of the past 3 rows for the same ID but lagging this by 1 row. My attempt looked like the below code and i is the column. There has to be a way to do this but this method doesnt seem to work.

for i in df.columns.values:
    df.groupby('Id', group_keys=False)[i].rolling(window=3, min_periods=2).mean().shift(1)


id    dollars  lag

1      6       nan
1      7       nan
1      6       6.5
3      7       nan
3      4       nan
3      4       5.5
3      3       5
5      6       nan
5      5       nan
5      6       5.5
5      12      5.67
5      7       8.3

check with apply something like `df.groupby('Id', group_keys=False)[i].apply(lambda x : x.rolling(window=3, min_periods=2).mean().shift(1))` — BENY, Jan 24 '19 at 20:19
did not work, TabError ; inconsistent use of tabs and spaces in indentation — William Bernard, Jan 24 '19 at 21:08
your title says "sum", but your code says "mean." Can you provide sample data and describe expected output? — C8H10N4O2, Jun 29 '20 at 20:29

score 3 · Answer 1 · answered Jun 30 '20 at 02:23

I am trying to get a rolling sum of the past 3 rows for the same ID but lagging this by 1 row.

You can create the lagged rolling sum by chaining DataFrame.groupby(ID), .shift(1) for the lag 1, .rolling(3) for the window 3, and .sum() for the sum.

Example: Let's say your dataset is:

import pandas as pd
# Reproducible datasets are your friend!
d = pd.DataFrame({'grp':pd.Series(['A']*4 + ['B']*5 + ['C']*6),
                  'x':pd.Series(range(15))})
print(d)

I think what you're asking for is this:

d['y'] = d.groupby('grp')['x'].shift(1).rolling(3).sum()
print(d)

 grp   x     y
   A   0   NaN
   A   1   NaN
   A   2   NaN
   A   3   3.0
   B   4   NaN
   B   5   NaN
   B   6   NaN
   B   7  15.0
   B   8  18.0
   C   9   NaN
   C  10   NaN
   C  11   NaN
   C  12  30.0
   C  13  33.0
   C  14  36.0

Pandas Rolling Groupby Shift back 1, Trying to lag rolling sum

1 Answers1