Calculating Rolling forward averages with pandas

Question

I need to calculate some rolling forward averages in a dataframe and really don't know where to start.

I know if I wanted to select a cell 10 days ahead say I would do df.shift(-10), but what I'm looking to do is calculate the average between 10 and 15 days ahead say.

So what I'm kind of thinking is df.rolling(-10,-15).mean(), if I was trying to calculate just a moving average going backing in time df.rolling(15, 10).mean() would work perfectly and I did think about just calculating the averages like that, and then somehow shifting the data.

Any help would be great

Many thanks

Ethology

Answer 1

You could calculate the rolling mean 5 days ahead, and then shift that for 10 more periods. Since negative values in rolling are not allowed, you can invert the axis, calculate backwards, and then invert again (see How to use Pandas rolling_* functions on a forward-looking basis):

df = pd.DataFrame(np.random.rand(100, 2))
df[::-1].rolling(5).mean()[::-1].shift(-10)

Assignable

Ahhh I can shift at the end! I didn't know this, yes that's a great answer, that's exactly what I'm after thanks mate – Ethology

Answer 2

The above answer doesn't look right. IMHO you musn't reverse and shift.

df = pd.DataFrame(np.random.randint(low=0, high=10, size=(100, 2))) # int easier to interpret
df[::-1].rolling(window=5, min_periods=1).mean()[::-1]

this also works but you lose the last 4 values:

df.rolling(window=5, min_periods=1).mean().shift(-5)

The more difficult problem of a rolling window that is arbitrarily shifted (offset) probably needs to use .shift() in some way.

Divisibility

Answer 3

There is a new method to deal with this. That said includes current row.

https://pandas.pydata.org/docs/reference/api/pandas.api.indexers.FixedForwardWindowIndexer.html

indexer = pd.api.indexers.FixedForwardWindowIndexer(window_size=2)

df.rolling(window=indexer, min_periods=1).sum()

Trapani