Moving Average Pandas

Question

I would like to add a moving average calculation to my exchange time series.

Original data from Quandl

Exchange = Quandl.get("BUNDESBANK/BBEX3_D_SEK_USD_CA_AC_000",                       authtoken="xxxxxxx")  #               Value # Date                # 1989-01-02  6.10500 # 1989-01-03  6.07500 # 1989-01-04  6.10750 # 1989-01-05  6.15250 # 1989-01-09  6.25500 # 1989-01-10  6.24250 # 1989-01-11  6.26250 # 1989-01-12  6.23250 # 1989-01-13  6.27750 # 1989-01-16  6.31250  # Calculating Moving Avarage MovingAverage = pd.rolling_mean(Exchange,5)  #               Value # Date           # 1989-01-02      NaN # 1989-01-03      NaN # 1989-01-04      NaN # 1989-01-05      NaN # 1989-01-09  6.13900 # 1989-01-10  6.16650 # 1989-01-11  6.20400 # 1989-01-12  6.22900 # 1989-01-13  6.25400 # 1989-01-16  6.26550 

I would like to add the calculated Moving Average as a new column to the right after Value using the same index (Date). Preferably I would also like to rename the calculated moving average to MA.

Answer 1

A moving average can also be calculated and visualized directly in a line chart by using the following code:

Example using stock price data:

import pandas_datareader.data as web import matplotlib.pyplot as plt import datetime plt.style.use('ggplot')  # Input variables start = datetime.datetime(2016, 1, 01) end = datetime.datetime(2018, 3, 29) stock = 'WFC'  # Extrating data df = web.DataReader(stock,'morningstar', start, end) df = df['Close']  print df   plt.plot(df['WFC'],label= 'Close') plt.plot(df['WFC'].rolling(9).mean(),label= 'MA 9 days') plt.plot(df['WFC'].rolling(21).mean(),label= 'MA 21 days') plt.legend(loc='best') plt.title('Wells Fargo\nClose and Moving Averages') plt.show() 

Tutorial on how to do this: https://youtu.be/XWAPpyF62Vg

Answer 2

The rolling mean returns a Series you only have to add it as a new column of your DataFrame (MA) as described below.

For information, the rolling_mean function has been deprecated in pandas newer versions. I have used the new method in my example, see below a quote from the pandas documentation.

Warning Prior to version 0.18.0, pd.rolling_*, pd.expanding_*, and pd.ewm* were module level functions and are now deprecated. These are replaced by using the Rolling, Expanding and EWM. objects and a corresponding method call.

df['MA'] = df.rolling(window=5).mean()  print(df) #             Value    MA # Date                    # 1989-01-02   6.11   NaN # 1989-01-03   6.08   NaN # 1989-01-04   6.11   NaN # 1989-01-05   6.15   NaN # 1989-01-09   6.25  6.14 # 1989-01-10   6.24  6.17 # 1989-01-11   6.26  6.20 # 1989-01-12   6.23  6.23 # 1989-01-13   6.28  6.25 # 1989-01-16   6.31  6.27