Moving Averages in pandas

Let's calculate SMA for a window size of 3, which means you will consider three values each time to calculate the moving average, and for every new value, the oldest value will be ignored.

To implement this, you will use pandas iloc function, since the demand column is what you need, you will fix the position of that in the iloc function while the row will be a variable i which you will keep iterating until you reach the end of the dataframe.

for i in range(0,df.shape[0]-2):
    df.loc[df.index[i+2],'SMA_3'] = np.round(((df.iloc[i,1]+ df.iloc[i+1,1] +df.iloc[i+2,1])/3),1)

For a sanity check, let's also use the pandas in-built rolling function and see if it matches with our custom python based simple moving average.

df['pandas_SMA_3'] = df.iloc[:,1].rolling(window=3).mean()

df.head()

Cool, so as you can see, the custom and pandas moving averages match exactly, which means your implementation of SMA was correct.

Let's also quickly calculate the simple moving average for a window_size of 4.

for i in range(0,df.shape[0]-3):
    df.loc[df.index[i+3],'SMA_4'] = np.round(((df.iloc[i,1]+ df.iloc[i+1,1] +df.iloc[i+2,1]+df.iloc[i+3,1])/4),1)

df.head()

df['pandas_SMA_4'] = df.iloc[:,1].rolling(window=4).mean()

df.head()

Now, you will plot the data of the moving averages that you calculated.

import matplotlib.pyplot as plt
%matplotlib inline

plt.figure(figsize=[15,10])
plt.grid(True)
plt.plot(df['demand'],label='data')
plt.plot(df['SMA_3'],label='SMA 3 Months')
plt.plot(df['SMA_4'],label='SMA 4 Months')
plt.legend(loc=2)

<matplotlib.legend.Legend at 0x11fe15080>

Cumulative Moving Average

I think we are now ready to move to a real dataset.

For cumulative moving average, let's use an air quality dataset which can be downloaded from this link.

df = pd.read_csv("AirQualityUCI/AirQualityUCI.csv", sep = ";", decimal = ",")
df = df.iloc[ : , 0:14]

df.head()

Preprocessing is an essential step whenever you are working with data. For numerical data one of the most common preprocessing steps is to check for NaN (Null) values. If there are any NaN values, you can replace them with either 0 or average or preceding or succeeding values or even drop them. Though replacing is normally a better choice over dropping them, since this dataset has few NULL values, dropping them will not affect the continuity of the series.

df.isna().sum()

Date             114
Time             114
CO(GT)           114
PT08.S1(CO)      114
NMHC(GT)         114
C6H6(GT)         114
PT08.S2(NMHC)    114
NOx(GT)          114
PT08.S3(NOx)     114
NO2(GT)          114
PT08.S4(NO2)     114
PT08.S5(O3)      114
T                114
RH               114
dtype: int64

From the above output, you can observe that there are around 114 NaN values across all columns, however you will figure out that they are all at the end of the time-series, so let's quickly drop them.

df.dropna(inplace=True)

df.isna().sum()

Date             0
Time             0
CO(GT)           0
PT08.S1(CO)      0
NMHC(GT)         0
C6H6(GT)         0
PT08.S2(NMHC)    0
NOx(GT)          0
PT08.S3(NOx)     0
NO2(GT)          0
PT08.S4(NO2)     0
PT08.S5(O3)      0
T                0
RH               0
dtype: int64

You will be applying cumulative moving average on the Temperature column (T), so let's quickly separate that column out from the complete data.

df_T = pd.DataFrame(df.iloc[:,-2])

df_T.head()

Now, you will use the pandas expanding method fo find the cumulative average of the above data. If you recall from the introduction, unlike the simple moving average, the cumulative moving average considers all of the preceding values when calculating the average.

df_T['CMA_4'] = df_T.expanding(min_periods=4).mean()

df_T.head(10)

Time series data is plotted with respect to the time, so let's combine the date and time column and convert it into a datetime object. To achieve this, you will use the datetime module from python (Source: Time Series Tutorial).

import datetime

df['DateTime'] = (df.Date) + ' ' + (df.Time)
df.DateTime = df.DateTime.apply(lambda x: datetime.datetime.strptime(x, '%d/%m/%Y %H.%M.%S'))

Let's change the index of the temperature dataframe with datetime.

df_T.index = df.DateTime

Let's now plot the actual temperature and the cumulative moving average wrt. time.

plt.figure(figsize=[15,10])
plt.grid(True)
plt.plot(df_T['T'],label='temperature')
plt.plot(df_T['CMA_4'],label='CMA_4')
plt.legend(loc=2)

<matplotlib.legend.Legend at 0x1210a2d30>

Exponential Moving Average

df_T['EMA'] = df_T.iloc[:,0].ewm(span=40,adjust=False).mean()

df_T.head()

plt.figure(figsize=[15,10])
plt.grid(True)
plt.plot(df_T['T'],label='temperature')
plt.plot(df_T['CMA_4'],label='CMA_4')
plt.plot(df_T['EMA'],label='EMA')
plt.legend(loc=2)

<matplotlib.legend.Legend at 0x14b2a41d0>

Wow! So as you can observe from the graph above, that the Exponential Moving Average (EMA) does a superb job in capturing the pattern of the data while the Cumulative Moving Average (CMA) lacks by a considerable margin.

Go Further!

Congratulations on finishing the tutorial.

This tutorial was a good starting point on how you can calculate the moving averages of your data and make sense of it.

Try writing the cumulative and exponential moving average python code without using the pandas library. That will give you much more in-depth knowledge about how they are calculated and in what ways are they different from each other.

There is still a lot to experiment. Try calculating the partial auto-correlation between the input data and the moving average, and try to find some relation between the two.

If you would like to learn more about DataFrames in pandas, take DataCamp's pandas Foundations interactive course.

References:

• Time Series Tutorial

Please feel free to ask any questions related to this tutorial in the comments section below.