How to you do a rolling sum in Pandas where the index is descending?-CodePudding

I want to calculate a trailing rolling window on a dataframe that is sorted in descending order.

For example let

df = pd.DataFrame({'A': [1,2,3,4,5,6,7]}, index=[f'20{i}' for i in range(21, 14, -1)])

>>> df  
        A
2021    1
2020    2
2019    3
2018    4
2017    5
2016    6
2015    7

How can I neatly calculate the mean of the last two years? Ie. a neater version of:

ans = df.iloc[::-1].rolling(2).mean().iloc[::-1]
>>> ans
           A
2021    1.50
2020    2.50
2019    3.50
2018    4.50
2017    5.50
2016    6.50
2015    NaN

I implemented this for now:

def irolling(s:pd.Series, aggfunc, **kwargs)->pd.Series:
    return s.iloc[::-1].rolling(**kwargs).agg(aggfunc).iloc[::-1]

df.pipe(irolling, aggfunc='mean', window=2)

And I know I could register irolling to the pandas API as show here.

CodePudding user response：

You can use the rolling average going forward and then shift up the result by one row.

import pandas as pd
df = pd.DataFrame({'A': [1,2,3,4,5,6,7]}, index=[f'20{i}' for i in range(21, 14, -1)])
ans = df.rolling(2).mean().shift(-1)
ans

This returns the same as your attempt

        A
2021    1.5
2020    2.5
2019    3.5
2018    4.5
2017    5.5
2016    6.5
2015    NaN