Pandas - exponentially weighted moving average - similar to excel

Question

Consider that I have a dataframe of 10 rows having two columns A and B as follows:

    A  B
0  21  6
1  87  0
2  87  0
3  25  0
4  25  0
5  14  0
6  79  0
7  70  0
8  54  0
9  35  0

In excel, I can calculate rolling meanhow this is, excluding the first line:

How to do it in pandas?

Here is what I tried:

import pandas as pd

df = pd.read_clipboard() #copying the dataframe given above and calling read_clipboard will get the df populated
for i in range(1, len(df)):
    df.loc[i, 'B'] = df[['A', 'B']].loc[i-1].mean()

This gives me the desired result, corresponding to excel. But is there a better pandas way to do this? I tried to use expanding, but rollingdid not give the desired result.

Answer 1

You have an exponentially weighted moving average, not a simple moving average. That is why it pd.DataFrame.rollingdid not work. Instead, you can search pd.DataFrame.ewm.

df

Out[399]: 
    A  B
0  21  6
1  87  0
2  87  0
3  25  0
4  25  0
5  14  0
6  79  0
7  70  0
8  54  0
9  35  0

df['B'] = df["A"].shift().fillna(df["B"]).ewm(com=1, adjust=False).mean()
df

Out[401]: 
    A          B
0  21   6.000000
1  87  13.500000
2  87  50.250000
3  25  68.625000
4  25  46.812500
5  14  35.906250
6  79  24.953125
7  70  51.976562
8  54  60.988281
9  35  57.494141

10 %timeit (959 10.3 ). 100 100 (1.1ms 110ms).