Python Pandas Moving Average Lag

Question

Consider the following Python program:

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

data = [["2017-05-25 22:00:00", 5],
["2017-05-25 22:05:00", 7],
["2017-05-25 22:10:00", 9],
["2017-05-25 22:15:00", 10],
["2017-05-25 22:20:00", 15],
["2017-05-25 22:25:00", 20],
["2017-05-25 22:30:00", 25],
["2017-05-25 22:35:00", 32]]

df = pd.DataFrame(data)
df.columns = ["date", "value"]
df["date2"] = pd.to_datetime(df["date"],format="%Y-%m-%d %H:%M:%S")

ts = pd.Series(df["value"].values, index=df["date2"])
mean_smoothed = ts.rolling(window=5).mean()
exp_smoothed = ts.ewm(alpha=0.5).mean()

h1 = ts.head(8)
h2 = mean_smoothed.head(8)
h3 = exp_smoothed.head(8)
k = pd.concat([h1, h2, h3], join='outer', axis=1)
k.columns = ["Actual", "Moving Average", "Exp Smoothing"]
print(k)

This prints

                     Actual  Moving Average  Exp Smoothing
date2                                                     
2017-05-25 22:00:00       5             NaN       5.000000
2017-05-25 22:05:00       7             NaN       6.333333
2017-05-25 22:10:00       9             NaN       7.857143
2017-05-25 22:15:00      10             NaN       9.000000
2017-05-25 22:20:00      15             9.2      12.096774
2017-05-25 22:25:00      20            12.2      16.111111
2017-05-25 22:30:00      25            15.8      20.590551
2017-05-25 22:35:00      32            20.4      26.317647

Drawing a graph

plt.figure(figsize=(16,5))
plt.plot(ts, label="Original")
plt.plot(mean_smoothed, label="Moving Average")
plt.plot(exp_smoothed, label="Exponentially Weighted Average")
plt.legend()
plt.show()

Both moving average (MA) and exponential smoothing (ES) introduce a lag: In the above example MA, needs 5 values to make a predication what the 6th value will be. If you look at the table, however, there are only 4 NaN values in the MA column, and the 5th value is already a non-NaN value (=the first prediction).

Question: How do I draw these values in a graph such that the lag is correctly preserved? Looking at ES, it is actually a bit more obvious: ES should start at t=2 but starts but starts immediatelly.

Answer 1

Interpolation should fix the issue.

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

data = [["2017-05-25 22:00:00", 5],
["2017-05-25 22:05:00", 7],
["2017-05-25 22:10:00", 9],
["2017-05-25 22:15:00", 10],
["2017-05-25 22:20:00", 15],
["2017-05-25 22:25:00", 20],
["2017-05-25 22:30:00", 25],
["2017-05-25 22:35:00", 32]]

df = pd.DataFrame(data)
df.columns = ["date", "value"]
df["date2"] = pd.to_datetime(df["date"],format="%Y-%m-%d %H:%M:%S")

ts = pd.Series(df["value"].values, index=df["date2"])
mean_smoothed = ts.rolling(window=5).mean()
###### NEW #########
mean_smoothed[0]=ts[0]
mean_smoothed.interpolate(inplace=True)
####################
exp_smoothed = ts.ewm(alpha=0.5).mean()

h1 = ts.head(8)
h2 = mean_smoothed.head(8)
h3 = exp_smoothed.head(8)
k = pd.concat([h1, h2, h3], join='outer', axis=1)
k.columns = ["Actual", "Moving Average", "Exp Smoothing"]
print(k)


plt.figure(figsize=(16,5))
plt.plot(ts, label="Original")
plt.plot(mean_smoothed, label="Moving Average")
plt.plot(exp_smoothed, label="Exponentially Weighted Average")
plt.legend()
plt.show()