使用 Python 在 Excel 中分析时间序列数据

在这篇博文中，我们将探讨新的 **Python in Excel** 功能如何为在 Excel 中处理时间序列数据提供一种全新的方式。得益于其与 **Anaconda Distribution** 的内置集成，现在可以利用 Python 数据建模功能，使 Excel 中的时间序列分析成为全新的体验。

**注意**：要重现本文中的示例，请 安装Python in Excel 试用版。

时间序列入门

如果您想预测时间数据的趋势，**时间** 是在许多类型的分析中必须考虑的重要因素。这种分析的典型例子可以在许多领域找到。例如，在金融领域，股票交易员可以使用时间序列分析来更好地了解各种股票价格。类似地，在医疗保健领域，时间数据（通常也称为纵向数据）用于表示患者的健康轨迹，目的是预测疾病或康复进展。时间序列分析的另一个例子可以在气象学中找到，其中时间数据用于许多用例，例如温度预测或空气质量控制。在这篇博文中，我们将考虑一个空气污染预测的例子，探讨 Python 的新集成如何为 Excel 提供前所未有的时间序列分析功能。

简单来说，**时间序列** 可以定义为按时间顺序排列的一系列数据点。最常见的是，时间序列包含一系列连续等间距的时间点（例如，每 5 分钟或每小时），因此本质上我们通常处理的是离散的时间数据序列。

在时间序列中，时间是**自变量**，数据分析的目标通常是预测未来。但是，在处理时间数据时，还应该考虑其他重要方面：（1）自相关性，（2）季节性，（3）平稳性。

非正式地说，自相关性是指多个观察值在一段时间内（即 **时间滞后**）的相似程度。另一方面，季节性试图捕捉数据中周期性波动的概念。例如，“商品的价格在节假日期间较高”。如果时间序列的统计特性不随时间变化，则称该时间序列为平稳的。换句话说，它具有恒定的均值和方差，并且其协方差与时间无关。

有很多方法可以对时间序列进行建模以进行预测。最流行的方法是：移动平均、指数平滑 和 ARIMA。在这篇文章中，我们将在我们的实验中考虑后者，因为它是最强大的模型之一，并且可以更好地展示 Python in Excel 引入的新功能。如果您有兴趣了解有关 Python 中时间序列建模的更多信息，我建议您查看 Francesca Mazzeri（微软机器学习）在 GitHub 上的 存储库，其中包含使用 Anaconda Distribution 和 Azure 云的示例。

直观地说，ARIMA（自回归积分移动平均）定义为由三个简单模型组合而成的模型，旨在处理表现出非平稳特性的单变量时间序列。它结合了自回归 (AR) 和移动平均 (MA) 模型，以及序列的差分预处理步骤，以使序列平稳，称为积分 (I)。有关 ARIMA 模型的更多信息，请参阅相应的 维基百科文章。

使用 Python in Excel 预测空气污染

首先，让我们下载 包含 air_pollution.xlsx 数据的 Excel 工作簿。

在这个例子中，我们将考虑一个包含（每日）空气污染数据的数据集。数据集中的每个条目都以其日期、当天污染水平、前一天污染水平以及当天天气条件的其他信息（例如，是否降雨、风、雪、气压和温度）为特征。

数据集信息

我们将要做的第一件事是描述我们的数据，方法是使用 Python in Excel 将 Excel 表加载到 pandas.DataFrame 中。这样，我们也将熟悉新的集成。

让我们创建一个新的工作表，并将其命名为数据信息。

现在，让我们将 A1 单元格转换为 Python 单元格。我们可以使用 =PY 或使用键盘快捷键 Ctrl + Shift + Alt + P 来完成此操作。在公式编辑器中，让我们编写以下几行 Python 代码将 Excel 表转换为 pandas 数据帧

import pandas as pd

air_pollution = xl("Table1[#All]", headers=True)
air_pollution["date"] = pd.to_datetime(air_pollution["date"])
air_pollution.set_index("date", inplace=True)

air_pollution.describe()

首先，我们依赖于新的 xl() 函数来自动跨越整个表的范围，该范围会自动转换为 Python 中的 DataFrame 对象。
然后，我们确保日期列被正确地解析为日期时间对象，并将该列设置为我们表的索引。这样，我们的数据将按相应的日期索引，这将使我们在考虑时间维度时更容易操作。最后，我们使用 describe 方法收集一些数据的统计信息。

这些统计信息本身确实很有参考价值，但是，可视化每列的趋势是可视化时间数据的更好方法。这也将使我们有机会了解 Python 绘图功能如何在 Excel 中工作。

让我们首先定义一个简单的 Python 函数，该函数将接受列名并生成相应的绘图。

我们表中可用的列是：

pollution_today, dew, temp, press, wind_speed, snow, rain, pollution_yesterday

让我们继续操作 J1 单元格，我们将将其转换为 Python 单元格

from matplotlib import pyplot as plt
plt.style.use('bmh')  # 设置绘图样式

values = air_pollution.values

def plot_data_trends(column_name)
    fig = plt.figure()
    idx = air_pollution.columns.to_list().index(column_name)
    plt.plot(values[:, idx])
    plt.title(column_name, y=1, loc="right")
    return fig
   
“绘图函数定义”

现在，让我们对不同的列名多次调用此函数并生成绘图。为此，让我们首先将所有列名列在一个新的 Python 单元格中（例如，J2）的连续 Excel 单元格中

air_pollution.columns

这将把所有列名在连续的单元格中展开。

现在，让我们在 B11（例如）中编写以下代码，然后将其转换为 Python 单元格

plot_data_trends(xl("$J2"))

这将生成第一列的数据趋势。现在，让我们将单元格内容拖动到接下来的 6 行中，让 Excel 自动执行它的魔法，自动更新对列名的引用，并生成相应的绘图！完成之后，相应地调整**行高**和**列宽**，以使绘图在单元格内显示（例如，行高 = 250）。

在继续之前，关于 Python 单元格输出的快速说明。在撰写本文时，Python 单元格的默认**输出模式**为Python 对象（见下图）。此模式将生成的图像显示在单元格中为“图像对象”。

但是，在这种情况下，我们更感兴趣的是实际查看图像。为此，让我们选择所有七个单元格，并将它们的输出模式同时切换到“Excel 值”。这将显示生成的绘图，如以下图片所示。从现在开始，我们将假定在 Excel 中生成新绘图时切换到“Excel 值”。

有关 Python 单元格输出及其与 Excel 集成的更多信息，我建议您查看这篇博文文章，使用 Python in Excel 的 5 个快速技巧。

分解我们的时间序列

时间序列最常见的分析之一是将其**分解为多个部分**。

我们可以将时间序列分解为的几个部分是水平、趋势、季节性和噪声。所有序列都包含水平和噪声，但并非所有序列都存在季节性和趋势。
这 4 部分可以加法或乘法组合到时间序列中。

识别时间序列的不同部分，以及它们在数据中的行为，可能会以不同的方式影响您的模型。Python 的 statsmodel 提供了一个 seasonal_compose() 函数，可以在指定用于组合数据的模型是加法还是乘法后，自动分解时间序列。

让我们创建一个名为“时间序列组件”的新工作表。接下来，让我们转到左上角的单元格 A1，并使用 statsmodel.seasonal_decompose 编写以下 Python 代码。

from statsmodels.tsa.seasonal import seasonal_decompose
import matplotlib as mpl

# 使用 Matplotlib 调整绘图的样式
mpl.rcParams['axes.labelsize'] = 14
mpl.rcParams['xtick.labelsize'] = 12
mpl.rcParams['ytick.labelsize'] = 12
mpl.rcParams['text.color'] = 'k'
mpl.rcParams['figure.figsize'] = 18, 8

series = air_pollution.pollution_today[:365]
result = seasonal_decompose(series, model='multiplicative')
result.plot()

在导入必要的包之后，代码段中的前几行仅用于为使用 matplotlib 生成的绘图设置一些参数。重要的行是最后三行：我们收集了 2010 年（第一年，前 365 天）的 pollution_data 值，然后将时间序列分解为三个部分：趋势、季节性和残差。这些部分分别在下面生成的图表中表示，如每个绘图的 y 轴上的图例所示。

让我们快速看一下我们数据中的趋势，以进一步探索使用 Python 在 Excel 中工作的潜力。

当在时间序列中观察到一个增加或减少的斜率时，我们的数据中就会出现趋势。通常，我们希望消除数据中的趋势，因为这将使我们能够隔离数据中存在的任何季节性，从而提取出时间序列的周期性内容。

现在是时候进一步扩展我们在 Excel 中的 Python 技能，并尝试一些方法来检查我们系列中的趋势。在几行 Python 代码中，我们将尝试

1. 自动分解
2. 移动平均线
3. 拟合线性回归模型以识别趋势

让我们转到 A2 单元格，并在 =PY 后编写以下 Python 代码。请注意，我们将参考代码注释中的这些方法以提高清晰度。还要注意，代码中引用的 result Python 变量与之前单元格中定义的变量相同，可以通过 Excel 中 Python 的共享全局命名空间访问。

import numpy as np
from sklearn.linear_model import LinearRegression

fig = plt.figure(figsize=(15, 7))
layout = (3, 2)
pm_ax = plt.subplot2grid(layout, (0, 0), colspan=2)
mv_ax = plt.subplot2grid(layout, (1, 0), colspan=2)
fit_ax = plt.subplot2grid(layout, (2, 0), colspan=2)

# 1. 自动分解趋势
pm_ax.plot(result.trend)
pm_ax.set_title("自动分解的趋势")

# 2. 移动平均线
mm = air_pollution.pollution_today.rolling(12).mean()
mv_ax.plot(mm)
mv_ax.set_title("移动平均线 12 步")

# 3. 线性回归
X = np.arange(len(air_pollution.pollution_today))
X = X[:, np.newaxis]
y = air_pollution.pollution_today.values
model = LinearRegression()
model.fit(X, y)

# 计算趋势并生成绘图
trend = model.predict(X)
fit_ax.plot(trend)
fit_ax.set_title("线性回归拟合的趋势")

plt.tight_layout()

这可能需要几秒钟才能完成，您应该在唯一的绘图中看到如下所示的图表。

我们可以看到我们的序列没有很强的趋势，这是一个好消息，因为这意味着我们不需要进行很多数据清理。来自自动分解和移动平均线的結果更像是季节性效应 + 随机噪声，而不是趋势。线性回归图（从上到下第三个）进一步证实了这一点，它产生了很差的趋势。

时间序列建模

现在让我们继续使用 Python 在 Excel 中进行时间序列建模的最后一步，并探索如何使用 Python 在 Excel 中使用 ARIMA 为我们的数据建立和运行时间序列预测。

让我们首先创建一个名为“建模”的新建模工作表。

首先，我们必须通过创建训练和测试数据分区来准备我们的数据以进行建模。这两个分区（即两个不相交的子集）将分别用于训练我们的预测模型，并检查模型在对看不见的（测试）数据的未来预测中是如何执行的。

在 A1 Python 单元格中，编写以下 Python 代码。

RANDOM_SEED = 12345
np.random.seed(RANDOM_SEED)

split_date = '2014-01-01'
df_training = air_pollution.loc[air_pollution.index <= split_date]
df_test = air_pollution.loc[air_pollution.index > split_date]
f"数据分区：{len(df_training)} 天的训练数据 - {len(df_test)} 天的测试数据"

由于我们正在处理时间数据，因此数据分区将基于时间中的参考日期应用，在本例中为 2014-01-01。该日期之前的所有数据将用于训练我们的模型。此后所有日期将用于测试模型的预测能力。

作为该单元格的输出，您应该获得以下内容： 

数据分区：1461 天的训练数据 – 364 天的测试数据 

现在让我们在我们的数据上尝试两个预测模型，SimpleExpSmoothing 和 ARIMA，使用 Statsmodel，它是 Excel 中 Python 的 核心库之一。

Statsmodel 是 Python 中时间序列建模的标准，但是，它的 API 对初学者来说可能很棘手。因此，我已经包含了一个工作代码示例作为起点，以帮助您更快地入门。

好消息是，相同的代码可用于这两种考虑的方法（仅调用所选方法的单行代码将更改）。我在以下代码中添加了注释，以便您可以更好地理解。第二个代码示例将简单地用 ARIMA 替换调用 SimpleExpSmoothing 的行。

在单元格 A2 中，让我们编写以下 Python 代码来预测使用简单指数平滑的空气污染。分析计划是遍历测试数据，训练模型，并预测 1 天后，然后对测试样本中的所有天重复该过程。请查看代码中的注释以了解各个步骤。

from statsmodels.tsa.holtwinters import SimpleExpSmoothing

# 让我们创建一个包含模型生成的预测的列表
yhat = list()

# 迭代测试数据中的多个时间步长（在本例中为天）
for t in range(len(df_test.pollution_today)):
    # 通过将整个训练数据加上一个时间步长 (t)（即天）来组成训练序列 (temp_train)
    temp_train = air_pollution[:len(df_training)+t]
    # 实例化预测模型，使用 temp_train 的 SimpleExpSmoothing
    model = SimpleExpSmoothing(temp_train.pollution_today)
    # 拟合模型，并生成预测
    model_fit = model.fit()
    predictions = model_fit.predict(start=len(temp_train),                                   
                                    end=len(temp_train))
    # 存储生成的预测
    yhat = yhat + [predictions]


# 将所有生成的预测堆叠在一起
yhat = pd.concat(yhat)
# 最后让我们使用测试数据和生成的预测来生成绘图
fig = plt.figure()
plt.plot(df_test.pollution_today.values, label="原始数据")
plt.plot(yhat.values, color='red', label="ExpSmoothing 预测")
plt.legend()
fig

最终结果如下所示： 

现在是时候尝试我们的 ARIMA 模型了。在另一个单元格中，例如 B2，让我们编写以下代码。请注意，代码中仅有少数差异以粗体突出显示： 

from statsmodels.tsa.arima.model import ARIMA

yhat = list()
for t in range(0, len(df_test.pollution_today))
    temp_train = air_pollution[:len(df_training)+t]
    model = ARIMA(temp_train.pollution_today, order=(1, 0, 0))
    model_fit = model.fit()
    predictions = model_fit.predict(start=len(temp_train),        
                                    end=len(temp_train),
                                    dynamic=False)
    yhat = yhat + [predictions]

yhat = pd.concat(yhat)
fig = plt.figure(figsize=(14, 8))
plt.plot(df_test.pollution_today.values, label='原始数据')
plt.plot(yhat.values, color='red', label='ARIMA 预测')
plt.legend()
fig

此代码使用 ARIMA 模型生成以下结果。 

注意：执行此单元格将花费一些时间，因为 ARIMA 模型比较复杂，数据集也不小。如果它在您的计算机上花费太多时间才能完成，您可以减少预测中的步骤数，例如，测试集中考虑的天数。换句话说，用以下行替换外部循环，仅考虑下一季度（90 天）： 

for t in range(0, len(df_test.pollution_today[:90]))

结论

在这篇文章中，我们探讨了在处理时间序列数据（用于分析和预测）时，新的Python 在 Excel 中集成所提供的巨大潜力。由于其与Anaconda Distribution的自然集成，新的集成允许用户直接在 Excel 工作簿中访问 numpy、scikit-learn 和 stats model 等包，这为我们提供了一种全新的方式在 Excel 中处理时间数据。

air_pollution_with_python.xlsx 工作簿的最终版本可以从 这里公开下载。

免责声明：截至本文发表之时，Microsoft Excel 中的 Python 集成处于测试版。功能和函数可能会发生变化。如果您在此页面上发现任何错误，请随时联系我们。 

个人资料

Valerio Maggio 是 Anaconda 的研究员和数据科学倡导者。他也是开源贡献者，并积极参与 Python 社区。在过去的 12 年中，他为许多国际会议和社区聚会做出了贡献并自愿参与其中，例如 PyCon Italy、PyData、EuroPython 和 EuroSciPy。