How to perform rolling sum on pandas dataframe with group by for last 365 days only

Question

Trying to calculate a rolling sum on p_id for last 365 days only, creating a new column that contains this rolling sum. The dataframe with new column should look like this:

Date        p_id    points          roll_sum
                                
2016-07-29  57        11            11
2016-08-01  57        9             20
2017-01-12  57        5             25
2017-10-23  57        18            23
2018-03-03  57        0             18
2018-03-06  57        4             22
2019-03-16  57        3             3
1997-04-07  12        50            50
1997-04-09  12        32            82
1998-02-11  12        3             85
1998-05-12  12        0             3
1999-05-22  12        0             3
1999-05-29  12        15            18
2000-07-20  12        2             2
2002-10-27  12        17            19

I am getting error "Window must be an integer" when using this:

df.groupby(['Date', 'p_id'])['points'].rolling('365D', min_periods=1).sum()

or this:

df.reset_index(level=0).set_index('Date').groupby('p_id').points.rolling('365D').sum()

Tried searching on SO, got an answer similar to mine but it used redundant commands for python 2.x

Data frame can be recreated using code:

dates = ['2016-07-29',
'2016-08-01',
'2017-01-12',
'2017-10-23',
'2018-03-03',
'2018-03-06',
'2019-03-16',
'1997-04-07',
'1997-04-09',
'1998-02-11',
'1998-05-12',
'1999-05-22',
'1999-05-29',
'2000-07-20',
'2002-10-27']


pid = [57,57,57,57,57,57,57,12,12,12,12,12,12,12,12]

points = [11,9 ,5 ,18,0 ,4 ,3 ,50,32,3 ,0 ,0 ,15,2 ,17]

roll_sum = [11,20,25,23,18,22,3 ,50,82,85,3 ,3 ,18,2 ,19]

df = pd.DataFrame({'Date': dates,
            'p_id': pid,
            'points':points,
            'roll_sum':roll_sum})

Answer 1

you can add it as a series if the index of the dataframe and roll_sum match; here the index includes "p_id", "Date"

df["Date"] = df.Date.astype("datetime64")
roll_calc = df.groupby("p_id").rolling('365D', on="Date")["points"].sum()
df = df.set_index(["p_id", "Date"])
df["roll_sum_calc"] = roll_calc

output:

                 points  roll_sum  roll_sum_calc
p_id Date
57   2016-07-29      11        11           11.0
     2016-08-01       9        20           20.0
     2017-01-12       5        25           25.0
     2017-10-23      18        23           23.0
     2018-03-03       0        18           18.0
     2018-03-06       4        22           22.0
     2019-03-16       3         3            3.0
12   1997-04-07      50        50           50.0
     1997-04-09      32        82           82.0
     1998-02-11       3        85           85.0
     1998-05-12       0         3            3.0
     1999-05-22       0         3            0.0
     1999-05-29      15        18           15.0
     2000-07-20       2         2            2.0
     2002-10-27      17        19           17.0

Answer 2

Use set_index on the matching columns and join back to the rolling result:

s = df.set_index("Date").groupby('p_id')['points'].rolling('365D', min_periods=1).sum()

print (df.set_index(["p_id","Date"]).join(s, rsuffix="_rolling"))

                 points  roll_sum  points_rolling
p_id Date                                        
57   2016-07-29      11        11            11.0
     2016-08-01       9        20            20.0
     2017-01-12       5        25            25.0
     2017-10-23      18        23            23.0
     2018-03-03       0        18            18.0
     2018-03-06       4        22            22.0
     2019-03-16       3         3             3.0
12   1997-04-07      50        50            50.0
     1997-04-09      32        82            82.0
     1998-02-11       3        85            85.0
     1998-05-12       0         3             3.0
     1999-05-22       0         3             0.0
     1999-05-29      15        18            15.0
     2000-07-20       2         2             2.0
     2002-10-27      17        19            17.0