CODEWITHSUNDEEP
pythonpython-3.xpandas
adri
adri

Reputation: 161

Rolling windows excluding current rows

Here is a dataframe example:

days = ['2019-07-04 17:02:03', '2019-07-04 17:03:03',
        '2019-07-04 18:04:03', '2019-07-04 19:05:03',
        '2019-07-04 21:06:03', '2019-07-04 21:36:03',
        '2019-07-04 21:50:03', '2019-07-04 22:10:03']
ddf = pd.DataFrame({'Val': [0, 1, 2, 1, 4,1,3,1],'Cat':["A","A","A","A","B","B","B","B"]},
                  index= days)
ddf.index = pd.to_datetime(ddf.index)

                     Val Cat
2019-07-04 17:02:03    0   A
2019-07-04 17:03:03    1   A
2019-07-04 18:04:03    2   A
2019-07-04 19:05:03    1   A
2019-07-04 21:06:03    4   B
2019-07-04 21:36:03    1   B
2019-07-04 21:50:03    3   B
2019-07-04 22:10:03    1   B

If I apply rolling sum with 1 hour windows I get this:

ddf.groupby("Cat")["Val"].rolling("1h").sum().rename('sum_last_hour')

Cat                     
A    2019-07-04 17:02:03    0.0
     2019-07-04 17:03:03    1.0
     2019-07-04 18:04:03    2.0
     2019-07-04 19:05:03    1.0
B    2019-07-04 21:06:03    4.0
     2019-07-04 21:36:03    5.0
     2019-07-04 21:50:03    8.0
     2019-07-04 22:10:03    5.0
Name: sum_last_hour, dtype: float64
Name: sum_last_hour, dtype: float64

But I want to obtain this:

Cat                     
A    2019-07-04 17:02:03    NaN
     2019-07-04 17:03:03    0.0
     2019-07-04 18:04:03    NaN
     2019-07-04 19:05:03    NaN
B    2019-07-04 21:06:03    NaN
     2019-07-04 21:36:03    4.0
     2019-07-04 21:50:03    5.0
     2019-07-04 22:10:03    4.0
Name: sum_last_hour, dtype: float64

So I basically want to exclude the current row from the rolling sum if that makes sense... I tried using shift() but without success for now. Thanks for your help!

Upvotes: 6

Views: 3694

Answers (1)

adri
adri

Reputation: 161

Actually I just found out about it. You need to use the closed parameter in the rolling() function and set it to left. Something like this gives me the good result:

ddf.groupby("Cat").rolling("1h", closed= "left")["Val"].sum().rename('sum_last_hour')

Upvotes: 10

Related Questions