Home icon

/ 博客/

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

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

作者：Valerio Maggio

在这篇博文中，我们将探讨新的 Excel 中的 Python 功能如何为在 Excel 中处理时间序列数据带来全新的方式。 感谢其与 Anaconda 发行版 的内置集成，现在可以利用 Python 数据建模功能，使 Excel 中的时间序列分析成为一种全新的体验。

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

时间序列入门

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

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

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

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

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

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

使用 Excel 中的 Python 预测空气污染

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

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

数据集信息

我们要做的第一件事是描述我们的数据，方法是使用 Excel 中的 Python 将 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 对象。
然后，我们确保日期列被正确解析为 datetime 对象，并将该列设置为表格的索引。 这样，我们的数据将按相应的日期编制索引，这将使我们在考虑时间维度时更容易处理。 最后，我们使用 describe 方法收集一些数据统计信息。

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

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

我们的表格中可用的列是：

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

from matplotlib import pyplot as plt
plt.style.use('bmh')  # set the plotting style

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

这将溢出到连续的单元格中 air_pollution DataFrame 的所有列名。

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

plot_data_trends(xl("$J2"))

这将生成第一列的数据趋势。 现在，让我们将单元格内容拖动到以下 6 个连续行上，让 Excel 发挥其魔力，自动更新对列名的引用，并生成相应的图！ 完成后，相应地调整行高和列宽，以使图的可视化效果适合单元格内（例如，行高 = 250）。

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

Python output

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

Plot data trends

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

分解我们的时间序列

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

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

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

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

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

# Adjust the styling of the plot with 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 轴。

Pollution chart

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

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

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

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

让我们移动到 A2 单元格，并在 =PY 之后编写以下 Python 代码。 请注意，为了清楚起见，我们将在代码注释中引用这些方法。 另请注意，代码中引用的结果 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. Automatic decomposition trend
pm_ax.plot(result.trend)
pm_ax.set_title("Automatic decomposed trend")

# 2. Moving Average
mm = air_pollution.pollution_today.rolling(12).mean()
mv_ax.plot(mm)
mv_ax.set_title("Moving average 12 steps")

# 3. Linear Regression
X = np.arange(len(air_pollution.pollution_today))
X = X[:, np.newaxis]
y = air_pollution.pollution_today.values
model = LinearRegression()
model.fit(X, y)

# calculate trend and generate the plot
trend = model.predict(X)
fit_ax.plot(trend)
fit_ax.set_title("Trend fitted by linear regression")

plt.tight_layout()

这可能需要几秒钟才能完成，您应该看到如下图所示的图表，它们在一个独特的图中报告

Decomposed trends

我们可以看到我们的系列没有强烈的趋势，这是一个好消息，因为这意味着我们不需要做太多的数据清理。 来自自动分解和移动平均的结果看起来更像是季节性效应 + 随机噪声，而不是趋势。 线性回归图（从顶部数第三个）进一步证实了这一点，该图生成了一个较差的趋势。

时间序列建模

现在让我们继续在 Excel 中使用 Python 进行时间序列建模的最后一步，让我们探索如何在 Python 中使用 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"Data Partitioning: {len(df_training)} days of training data - {len(df_test)} days of testing data"

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

作为该单元格的输出，您应该得到以下结果：

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

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

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

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

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

from statsmodels.tsa.holtwinters import SimpleExpSmoothing

# let’s create a list holding generated predictions by the model
yhat = list()

# Iterate over the multiple time steps in test data (days, in this case)
for t in range(len(df_test.pollution_today)):
    # Compose the training sequence (temp_train) by adding to the whole
    # training data one single time step (t), i.e. day
    temp_train = air_pollution[:len(df_training)+t]
    # Instantiate the prediction model, SimpleExpSmoothing with temp_train
    model = SimpleExpSmoothing(temp_train.pollution_today)
    # fit the model, and generate predictions
    model_fit = model.fit()
    predictions = model_fit.predict(start=len(temp_train),                                     
                                    end=len(temp_train))
    # Store the generated predictions
    yhat = yhat + [predictions]

# Stack together all the generated predictions
yhat = pd.concat(yhat)
# Let’s finally generate the plot with test data, and generated predictions
fig = plt.figure()
plt.plot(df_test.pollution_today.values, label="Original")
plt.plot(yhat.values, color='red', label="ExpSmoothing predicted")
plt.legend()
fig

最终结果报告如下：

Output chart

现在是时候尝试我们的 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='Original')
plt.plot(yhat.values, color='red', label='ARIMA predicted')
plt.legend()
fig

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

ARIMA model

注意： 执行此单元格将需要一些时间，因为 ARIMA 模型更复杂，并且数据集也不小。 如果在您的计算机上花费的时间太长而无法完成，您可以减少预测中的步数，例如，测试集中考虑的天数。 换句话说，将外循环替换为以下行，仅考虑接下来的季度（90 天）：

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

结论

在这篇文章中，我们探讨了新的 Excel 中的 Python 集成在处理时间序列数据进行分析和预测时提供的巨大潜力。 感谢其与 Anaconda 发行版 的自然集成，新的集成允许用户直接在 Excel 工作簿中访问 numpy、scikit-learn 和 stats models 等包，从而使我们能够以全新的方式在 Excel 中处理时间数据。

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

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

个人简介

Valerio Maggio 是 Anaconda 的研究员和数据科学家倡导者。 他也是开源贡献者和 Python 社区的活跃成员。 在过去的 12 年中，他为许多国际会议和社区聚会（如 PyCon Italy、PyData、EuroPython 和 EuroSciPy）做出了贡献并担任志愿者。