Excel2019-机器学习实用指南-全-

Excel2019 机器学习实用指南（全）

原文：annas-archive.org/md5/73bc5f955f7f7c5e532b15cca8cd2d75

译者：飞龙

协议：CC BY-NC-SA 4.0

前言

智能机器一直是人类长久以来的梦想。即使我们离开发通用人工智能还有很长的路要走，但我们已经在教会计算机执行困难任务方面取得了巨大进步，尤其是那些对人类来说重复且耗时的工作。

机器学习模型可以帮助任何企业理解可用数据，从而优化流程，降低成本，并通常帮助企业提前规划。各个能力水平的 Excel 用户可能会感到在这波创新浪潮中被落下。每个人都谈论 R 和 Python 作为实现这些任务的唯一相关工具。事实是，开发和使用机器学习模型所需的大量工作可以在 Excel 中完成。

本书首先对机器学习进行一般介绍，使相关概念清晰易懂。它展示了机器学习项目的每个步骤，从数据收集和读取不同数据源，到开发模型和可视化结果。在每一章中，都有几个示例和动手练习，展示了读者如何结合 Excel 函数、插件和数据库及云服务连接，以达到我们的目标：构建完整的数据分析流程。不同的机器学习模型被演示并针对要分析的数据类型进行了定制。

在本书的结尾，读者将接触到一些高级工具，如 Azure 云和自动化机器学习，这些工具简化了分析任务，代表了机器学习的未来。

本书面向的对象

本书旨在帮助那些将 Excel 作为日常工具的数据分析师，他们需要超越 Power Pivot，使用插件和其他高级工具。希望扩展知识以利用 Excel 和 Azure 之间新连接可能性的 Excel 专家，以及需要测试机器学习模型而不编写代码的项目经理也将从中受益。

人们普遍认为，为了进行数据科学，从数据清洗到可视化以及机器学习模型，你需要成为一名 Python 或 R 程序员。然而，现在并非如此，而且总体趋势似乎正朝着无代码数据科学的方向发展。读者需要了解，还有其他选择，通过避免代码将 Excel 提升到下一个层次，并使其成为专业数据分析和可视化的平台。

本书涵盖的内容

第一章，实现机器学习算法，涵盖了基本的机器学习算法及其实现方法。

第二章，机器学习模型的实践示例，增加了一些算法及其用例的示例。

第三章，从不同数据源导入数据到 Excel，介绍了如何将数据从不同来源读取到 Excel 中。

第四章，数据清洗和初步数据分析，描述了数据预处理以准备数据用于机器学习模型。

第五章，相关性与变量的重要性，涵盖了特征工程，这涉及到识别冗余变量和变量间有用的关系。

第六章，Excel 中的数据挖掘模型实战示例，描述了在解决如市场篮子分析和客户群体分析等商业问题中最常用的算法示例。

第七章，实现时间序列，涵盖了时间序列分析和预测。

第八章，在图表、直方图和地图中可视化数据，描述了 Excel 中可用的不同图表及其用途。

第九章，人工神经网络，涵盖了以人工神经网络和深度学习形式的人工智能学习进展。

第十章，Azure 和 Excel - 云端机器学习，涵盖了在云端构建和使用机器学习模型，并将它们连接到 Excel。

第十一章，机器学习的未来，涵盖了数据分析与预测模型的自动化。

为了充分利用本书

您需要掌握 Excel 的工作知识，包括如何进行单元格计算、输入基本函数和制作图表。对于第十章，Azure 和 Excel - 云端机器学习，您需要一个 Microsoft 账户。

下载示例代码文件

您可以从www.packt.com的账户下载本书的示例代码文件。如果您在其他地方购买了本书，您可以访问www.packt.com/support并注册，以便将文件直接通过电子邮件发送给您。

您可以通过以下步骤下载代码文件：

1. 在www.packt.com登录或注册。

2. 选择 SUPPORT 标签。

3. 点击代码下载与勘误。

4. 在搜索框中输入书籍名称，并遵循屏幕上的说明。

下载文件后，请确保使用最新版本的软件解压缩或提取文件夹：

• Windows 版的 WinRAR/7-Zip

• Mac 版的 Zipeg/iZip/UnRarX

• Linux 版的 7-Zip/PeaZip

本书的相关代码包也托管在 GitHub 上，网址为 github.com/PacktPublishing/Hands-On-Machine-Learning-with-Microsoft-Excel-2019。如果代码有更新，它将在现有的 GitHub 仓库中更新。

我们还有其他来自我们丰富图书和视频目录的代码包，可在 https://github.com/PacktPublishing/ 找到。查看它们吧！

下载彩色图像

我们还提供包含本书中使用的截图/图表的彩色图像的 PDF 文件。您可以从这里下载：www.packtpub.com/sites/default/files/downloads/9781789345377_ColorImages.pdf。

使用的约定

本书使用了多种文本约定。

CodeInText：表示文本中的代码单词、数据库表名、文件夹名、文件名、文件扩展名、路径名、虚拟 URL、用户输入和 Twitter 昵称。以下是一个示例：“导航到文件的存储位置并打开homes.csv文件。”

粗体：表示新术语、重要单词或屏幕上看到的单词。例如，菜单或对话框中的单词在文本中显示如下。以下是一个示例：“选择包含表格的完整单元格范围，点击“插入”菜单，然后选择“图表”。”

警告或重要提示看起来像这样。

小贴士和技巧看起来像这样。

联系我们

我们欢迎读者的反馈。

一般反馈：如果您对本书的任何方面有疑问，请在邮件主题中提及书名，并将邮件发送至 customercare@packtpub.com。

勘误：尽管我们已经尽最大努力确保内容的准确性，但错误仍然可能发生。如果您在这本书中发现了错误，我们将不胜感激，如果您能向我们报告，我们将不胜感激。请访问 www.packt.com/submit-errata，选择您的书籍，点击勘误提交表单链接，并输入详细信息。

盗版：如果您在互联网上遇到我们作品的任何形式的非法副本，我们将不胜感激，如果您能提供位置地址或网站名称，我们将不胜感激。请通过链接至材料的方式与我们联系 copyright@packt.com。

如果您有兴趣成为作者：如果您在某个领域有专业知识，并且您有兴趣撰写或为书籍做出贡献，请访问 authors.packtpub.com。

评论

请留下评论。一旦您阅读并使用了这本书，为什么不在您购买它的网站上留下评论呢？潜在读者可以查看并使用您的客观意见来做出购买决定，Packt 公司可以了解您对我们产品的看法，我们的作者也可以看到他们对书籍的反馈。谢谢！

如需了解 Packt 的更多信息，请访问 packt.com.

第一部分：机器学习基础

第一部分的目标是向读者介绍机器学习以及所使用的不同类型模型。它将涵盖监督学习和无监督学习，这是机器学习中的主要分支。在这些方面，将演示回归（连续目标变量）和分类（离散目标变量）之间的区别。所有这些点将通过实践示例进行解释。

本节包含以下章节：

• 第一章，机器学习算法的实现

• 第二章，机器学习模型的实践示例

第一章：实现机器学习算法

学习多年来一直是研究的课题。人类如何获取新知识，从基本的生存技能到高级的抽象主题，在计算机世界中难以理解并复制。机器通过比较示例并找出它们之间的相似性来学习。

机器（以及人类）学习最简单的方法是简化需要解决的问题。现实的一个简化版本，称为模型，对这个任务很有用。需要研究的相关问题包括样本的最小数量、欠拟合和过拟合、相关特征以及模型能够学习得多好。不同类型的目标变量需要不同的算法。

在本章中，将涵盖以下主题：

• 理解学习和模型

• 关注模型特征

• 实践中研究机器学习模型

• 评估模型

技术要求

本章没有技术要求，因为它是一个入门章节。为了能够跟随示例，章节中显示的数据应输入到 Excel 电子表格中。

理解学习和模型

人类的学习方式已经被研究了几十年。有一些心理学理论试图解释我们如何获取知识，如何使用它，以及如何将其推广以将我们所知应用于全新的场景。退一步来说，我们可以问自己：学习意味着什么？我们可以这样说，一旦我们学会了某样东西，我们就能以或多或少详细的方式重复它。实际上，学习远不止是复制一种行为或记住一首诗。事实上，我们理解我们所学的，并且能够概括这种知识，这有助于我们正确地应对新的人、新的地方和新的情况。

需要创建一种能够以某种方式模仿我们人类行为和智能的机器，这个需求已经存在了很长时间。几百年前，国王们对下棋的机器、不需要人类演奏者的乐器以及能够回答各种问题的神秘盒子感到惊讶。这些许多时候是虚假的发明表明，人类最大的梦想之一就是创造一个智能体，它能够复制人们通常执行的任务，即使智能是一个难以捉摸且不易定义的东西。

多年过去了，技术已经发展到我们可以现在创造出能够“思考”的机器，或者至少看起来是这样。事实上，我们称之为“智能”的大多数系统只是能够执行重复性任务或根据我们所展示的例子对外部输入做出反应。随着我们进入本章，我们将看到人类学习和智能的一些定义特征已经成为了现代机器学习系统的一部分，而一些则仍然是科幻小说的主题。

根据定义，机器学习意味着教会机器或算法执行任务。我们已经在做这件事很多年了——这被称为编程。我们给计算机一组指令，它们应该被执行的顺序，以及如何对有限数量的输入做出反应的几种选择。如果输入未知，或者如果我们要求计算机执行程序中不包含的操作，那么它将失败，显示错误。这种传统范式与机器学习之间的区别在于，我们永远不会告诉计算机确切要做什么。我们要么让它发现模式，要么展示我们想要的样本。我们当然会使用编程，但只是为了定义那些学习的算法，正如之前所描述的那样。从找到更好地代表一组点的直线到驾驶汽车，机器能做的每一件事都是通过这种方式学习的。

作为婴儿，我们开始探索我们周围的世界。由于我们太小，无法理解词语或例子，我们基本上是通过我们的感官来体验世界的。我们学会区分硬和软、粗糙和平滑、热和冷。当我们需要某样东西时，我们可以呼唤注意，甚至可以理解我们父母和宠物的耐心程度。在大多数情况下，没有人坐在我们旁边来解释我们看到、听到、感觉到、尝到和闻到的东西。这就是我们所说的无监督学习的例子。

在无监督学习中，训练数据是“未标记的”。在没有我们的帮助或干预的情况下，算法/（或程序/）将在数据中找到所需的联系或未预料到的模式，并学习数据集的细节和属性。

随着我们长大，我们开始理解词语并开始命名事物。当我们看到狗或猫时，我们的父母会告诉我们，我们学会了自己的名字和他们的名字，我们学会从其他孩子的玩具中识别出自己的玩具（并且为它们而争斗）。在不经意间，我们将物体、动物和人的某些特征与它们的名称联系起来。这些都是我们所说的监督学习的例子。在计算机的情况下，算法被展示为一组代表问题属性的变量集合，然后它学习这些特征如何与标签的名称相关联。

科学已经向我们展示了我们所处世界的巨大复杂性。科学知识的每一个分支都需要高级数学计算，甚至完全新的看待数据的方式。然而，我们所能解释的绝大多数只是真实世界的一小部分。每当描述一个物理现象、经济或金融事件，或者试图理解个人和群体的行为时，我们依赖于对真实问题的简化版本。这些被称为模型，它们使我们能够构建我们试图解释的任何事物的心理表征。如果模型足够准确，我们就能预测某些未来的事件，或者为某种结果得到一个近似值。正如你现在应该意识到的，这是非常强大的。例如，如果一个炮兵能够精确计算出炮弹将落在哪里，那么他的军队在战斗中将明显优于敌人。模型是现实的简化版本，用于理解问题并最终做出预测。理解你的对手忽视的东西始终是一种优势。

通过实例学习——线性回归模型

想象一下你和一位朋友拥有一家小型冰淇淋店。你们正在讨论每天应该生产多少千克的冰淇淋，并且你们都同意天气越热，售出的冰淇淋就越多。你补充说，这并不是唯一需要考虑的因素，还有其他变量也可能影响销售数量。作为理性的人和优秀的分析师，你们决定通过记录商店营业时间内的平均温度和售出的冰淇淋数量来进行一个小实验。夏天特别多雨，温度变化大，这有助于你得到一个良好的变量范围。最终的数据库看起来如下表：

平均温度 (°C)	售出的冰淇淋 (kg)
26	45
23	42.5
29	53.5
23	35.5
15	32.5
19	34.5
21	33.5
18	35
15	32.5
25	40.5
25	39.5
16	32
23	44.5
23	39.5
20	33
17	26.5
21	37.5
29	49.5
25	40.5
24	44

你的模型表明售出的冰淇淋数量与平均温度成正比。为了测试这个假设，我们可以绘制收集到的数据的散点图：

1. 选择包含表格的全部单元格范围，点击插入菜单，然后选择图表：

1. 现在，点击散点图，如下所示：

在写下坐标轴标题后，你应该得到一个类似于以下图表的图表：

我们可以看到确实存在线性相关性，并且它是正相关的（温度值越大，你卖出的冰淇淋就越多）。然后我们可以使用线性方程来表示这个模型，如下所示：

IC = a * T + b (1)

在这里，IC 是卖出的冰淇淋数量，T 是平均温度，而 a 和 b 是需要通过线性回归计算出的常数。

要获得 a 和 b 的值，我们可以使用 Excel 的分析工具包数据分析插件*。如果你还没有启用它，请参考链接 support.office.com/en-ie/article/use-the-analysis-toolpak-to-perform-complex-data-analysis-6c67ccf0-f4a9-487c-8dec-bdb5a2cefab6 了解如何操作。

1. 在你的工作表中选择数据范围，然后在主菜单中选择数据，接着选择数据分析：

1. 在弹出菜单中选择回归，然后点击确定：

1. 确保 x 和 y 的范围是正确的（x 是温度，y 是冰淇淋数量）。选择线形拟合图，在新图表中查看数据点上的回归线：

观察输出结果，我们看到最佳拟合数据线可以表示如下：

IC = 1.5 T + 6* (2)

a 的标准误差为 ±0.2，b 的标准误差为 ±4。R² 值为 0.78，这意味着拟合不是很好，只有 78% 的冰淇淋销售变化可以通过平均温度来解释。所以，你和你的朋友都是对的！

下面的图表显示了拟合线：

很明显，这条线很好地代表了数据，但有些点稍微偏离了一些，这表明在预测冰淇淋消费时需要考虑其他因素。无论如何，给定某一天的预测平均温度，你可以使用方程 (2) 来大致估计需要生产多少冰淇淋来满足可能的消费需求。

保留线性回归的其他结果，因为我们将在接下来的章节中使用其中的一些。

关注模型特征

作为对现实的简化表示，模型还包括一组变量，这些变量包含描述我们代表问题不同部分的相关信息。这些变量可以是像我们之前例子中看到的那样具体，比如 1 公斤的冰淇淋，或者像文本文档中两个单词意义相似度的数值表示那样抽象。

在特定的机器学习模型情况下，这些变量被称为特征。选择提供有关我们试图解释或预测的现象的相关信息的显著特征至关重要。如果我们考虑无监督学习，那么相关的特征是那些更好地表示数据集中信息聚类或关联的特征。对于监督学习，最重要的特征是与目标变量高度相关的特征——即我们想要预测或解释的值。

从机器学习模型中可以获得的洞察力的质量取决于输入到模型中的特征。特征选择和特征工程是常用的技术，用于提高模型的输入。特征选择是选择相关特征子集的过程，用于任何已识别的模型构建。它也可以称为变量选择或属性选择。在构建任何机器学习模型时，特征选择和数据清洗应该是第一步和最重要的步骤。特征工程被定义为使用已识别数据的领域知识来创建特征的过程，这些特征使机器学习算法能够工作。如果这样做正确，那么它将通过从输入到该模型或系统的新的数据中创建特征来增加机器学习算法的预测能力。

在我们之前的例子中，模型特征是平均温度和销售的冰淇淋数量。由于我们已经证明还有更多变量参与其中，我们可以添加一些额外的特征来更好地解释每日冰淇淋的消费。例如，我们可以考虑我们正在记录数据的那一天，并将这一信息作为另一个特征。此外，任何其他相关信息都可以或多或少地表示为一个特征。在监督学习中，通常将输入变量称为特征，将目标或预测变量称为标签。

特征可以是数值的（例如我们之前的例子中的温度），也可以是分类的（例如一周中的某一天）。由于计算机中的所有东西都是以数值数据表示的，因此分类数据应该通过将类别分配给数字来转换为数值形式。独热编码是一种将分类变量转换为数值形式（或编码）的过程，以便它们可以被输入到机器学习算法中。

按照我们的例子，我们可以将星期转换为星期数，如下所示：

星期	星期数
星期一	1
星期二	2
星期三	3
星期四	4
星期五	5
星期六	6
星期日	7

这种编码反映了星期的顺序，并为周末保留了最高的值。

假设你想更具体地预测你销售的每种口味的冰淇淋数量。为了方便起见，让我们说我们生产四种不同的口味：巧克力、草莓、柠檬和香草。你能否为每种口味分配一个数字，就像你在星期几编码中所做的那样？答案，正如我们将看到的，是否定的。让我们试一试，看看哪里出了问题：

口味	口味编号
巧克力	1
草莓	2
柠檬	3
香草	4

通过使用这种编码，我们隐含地表示巧克力比香草更接近草莓（1 个单位与 3 个单位），这不是口味的一个真实属性。将属性转换为数字的正确方式是创建二进制变量。这种方法被称为独热编码，如下表所示：

口味	它是巧克力吗？	它是草莓吗？	它是柠檬吗？	它是香草吗？
巧克力	1	0	0	0
草莓	0	1	0	0
柠檬	0	0	1	0
香草	0	0	0	1

这种方法产生了一些开销，因为它通过为原始变量的每个可能值创建一个二进制变量来增加特征的数量。从积极的一面来看，它正确地计算了特征的性质。我们将在下一章中看到一些例子。

根据目标变量的类型，我们可以将它们分类为回归模型（即连续目标变量）或分类模型（即离散目标变量）。例如，如果我们想预测一个实数或整数，我们使用回归，而如果我们试图预测一个具有有限选项的标签，我们使用分类。

实践中研究机器学习模型

我们已经看到了一个非常简单的例子，并使用它来解释一些基本概念。在下一章中，我们将探索更复杂的模型。为了清晰起见，并开始我们的机器学习之旅，我们只限制在一个非常小的数据集上。当使用机器学习模型解决实际问题时应注意一些一般性考虑：

• 数据量通常非常大。实际上，更大的数据集有助于获得更精确的模型和更可靠的预测。通常称为大数据的极大数据集可能会带来存储和处理挑战。

• 数据永远不会干净且可直接使用，因此数据清洗非常重要，并且需要花费大量时间。

• 正确表示现实生活问题所需的特征数量通常很大。之前提到的特征工程技术无法手工完成，因此必须设计并应用自动方法。

• 评估一组输入特征的预测能力远比评估每个单独特征的显著性重要。第五章（0da64bd8-0bc9-491b-875c-7ec7c35c6165.xhtml）中给出了如何选择特征的简单示例，相关性与变量的重要性

• 我们不太可能第一次应用模型就能得到非常好的结果。测试和评估许多不同的机器学习模型意味着要重复相同的步骤多次，通常还需要自动化。

• 数据集应该足够大，以便可以使用其中的一部分数据进行训练（通常为 80%），其余部分用于测试。仅基于训练数据评估模型的准确性是误导性的。一个模型可能在解释和预测训练数据集时非常精确，但当面对新的、之前未见过的数据值时，可能无法泛化并给出错误的结果。

• 训练数据和测试数据应从同一完整数据集中随机选择。试图基于远离训练范围的数据进行预测不太可能得到好的结果。

监督机器学习模型通常使用输入数据的一部分进行训练，并在剩余部分上进行测试。然后，该模型可以用来预测当输入新的和未知特征值时的结果，如下面的图所示：

一个典型的监督机器学习项目包括以下步骤：

1. 获取数据并合并不同的数据源（关于此内容的更多信息请参阅第三章，从不同数据源导入 Excel 数据)

2. 清洗数据（您可以参考第四章，数据清洗和初步数据分析)

3. 初步分析和特征工程（您可以参考第五章，相关性与变量的重要性)

4. 尝试不同的模型及其参数，并使用完整数据集的一部分进行训练，其余部分用于测试

5. 将模型部署以便在连续的分析流程中使用，而不仅仅是进行小规模的、孤立的测试

6. 预测新输入数据的值

在下一章的示例中，此过程将变得清晰。

比较欠拟合和过拟合

在前面的列表中，步骤 4 意味着一个迭代过程，我们尝试模型、参数和特征，直到我们得到最佳结果。现在让我们考虑一个分类问题，我们想要将正方形与圆形分开，如图所示。在过程的开始，我们可能会处于与第一张图（左侧）类似的情况。模型未能有效地分离这两种形状，两侧都是正方形和圆形的混合。这被称为 欠拟合，指的是一个无法表示数据集特征的模型：

随着我们继续调整参数并将模型调整到训练数据集，我们可能会发现自己处于与第三张图（右侧）类似的情况。模型准确地分割了数据集，使得边界线两侧只留下一个形状。即使这看起来是正确的，但它完全缺乏泛化能力。结果调整得太适合训练数据，以至于当我们用不同的数据集对其进行测试时，它将完全错误。这个问题被称为 过拟合。

为了解决我们模型中的过拟合问题，我们需要增加其适应性。然而，使其过于灵活也可能使其在预测方面表现不佳。为了避免这种情况，通常的解决方案是使用 正则化 技术。在专门的文献中可以找到许多类似的技术，但它们超出了本书的范围。

中心图表显示了一个更灵活的模型；它代表数据集，但足够泛化，可以处理新的、之前未见过的数据。这通常很耗时，并且很难找到正确的平衡，以构建一个好的机器学习模型。

评估模型

每当我们获得一个结果时，它只与表示真实问题的模型一样准确。因此，了解哪些方法可以用来评估我们模型的性能至关重要。

当处理 分类 模型 时，我们可以使用以下方法。

分析分类准确率

这是正确预测数（CP）与样本总数之比：

在这里，CP 代表准确或正确的预测数量，而 TP 代表所有已做预测的总数。

构建混淆矩阵

现在让我们考虑一个二元分类问题。我们有一组属于两个类别的样本：YES 或 NO。我们可以构建一个机器学习模型，该模型为每个输入变量集输出一个类别。通过在 200 个样本上测试我们的模型，我们将得到以下结果：

N=200	预测 NO	预测 YES
实际 NO	60	15
实际 YES	25	100

混淆矩阵有四个要素：

• 真阳性（TP）：模型预测为 YES 且实际值为 YES 的次数。在我们的例子中，这是 100 次。

• 真阴性（TN）：模型预测为 NO 且实际值为 NO 的次数。在我们的例子中，这是 60 次。

• 假阳性（FP）：模型预测为 YES 且实际值为 NO 的次数。在我们的例子中，这是 15 次。

• 假阴性（FN）：模型预测为 NO 且实际值为 YES 的次数。在这个例子中，这是 25 次。

然后，我们按照以下方程计算混淆矩阵：

计算曲线下面积（AUC）

分类模型的 AUC 定义为模型将随机正例排在随机负例之上的概率。

使用混淆矩阵，我们可以定义其他量如下：

真阳性率（TPR）或灵敏度是正确预测为正的数据点与具有真实值为YES的所有数据点的比率：

假阳性率（FPR）或特异性是错误地将NO数据点预测为YES的比率，与所有NO数据点的比率。

这两个量都在[0, 1]范围内。FPR 和 TPR 在不同的阈值值下计算，并构建一个图表。这条曲线被称为接受者操作特征（ROC）；AUC 是该曲线下的面积，如图所示：

如果我们想评估回归模型，可以使用以下技术。

计算平均绝对误差（MAE）

MAE 是真实值（y[j]）和预测值（ŷ[j]）之间绝对差值的平均值。它不能告诉我们错误的方向，这意味着预测可能高于或低于真实值。如果我们有总共N个数据点，我们可以这样计算 MAE：

计算平均平方误差（MSE）

MSE 取实际值和预测值之间差异的平方的平均值：

无论我们选择哪种评估方法，考虑问题的业务部分都极其重要。最佳解决方案不一定是拥有最精确的模型，而是更好地满足您的业务需求的模型。可能的情况是，一个可以快速构建的不是很精确的模型比一个需要一年时间才能完成的完美模型更好。考虑到数据集不平衡和业务需求，对模型进行微调以改进混淆矩阵值是很重要的：

另一个需要考虑的重要因素是，在分类问题的情况下，我们是否有一个平衡的数据集。一个占主导地位的类别将导致一个每次预测结果几乎相同的模型。例如，一个 99% YES 标签的数据集在训练后会产生一个机器学习模型，该模型对 99%的输入预测为 YES（并且它是正确的！）有许多已知的技巧用于平衡数据集并找出我们数据中的问题。

摘要

在本章中，我们简要讨论了机器的学习过程，这在某种程度上模仿了人类的学习过程。我们描述了如何使用一个模型，它是我们想要解决的问题的简化表示，来应用机器学习以找到解决方案。

使用线性回归模型，我们构建了一个简单的监督预测模型，并解释了如何使用它。然后我们讨论了回归和分类之间的区别，并展示了输入变量和特征的性质。

在训练机器学习模型时，欠拟合和过拟合是两个主要问题。我们解释了它们是什么，并提出了避免它们的方法。

最后，不同类型的目标变量需要不同的算法和评估方法来测试模型的质量——我们详细讨论了这一点在最后几节。

在下一章中，我们将使用机器学习来解决一些实际问题，并探讨一些监督学习和无监督模型是如何构建的。

问题

1. 经典计算机编程与机器学习的主要区别是什么？

2. 考虑到目标变量的类型，模型是如何分类的？

3. 根据它们的学习方式，有哪些不同类型的模型？

4. 创建和使用机器学习模型的主要步骤是什么？

5. 在 Excel 中执行的回归分析的结果包含关于残差的信息。它们是什么，它们与 MAE 和 MSE 有何关系？

6. 解释欠拟合和过拟合。

7. 如何使用分类特征来为机器学习模型提供数据？

进一步阅读

• 机器学习入门指南：towardsdatascience.com/machine-learning-for-beginners-d247a9420dab

• 机器学习基础 — 这是你的茶杯里的茶：hackernoon.com/machine-learning-basics-its-your-cup-of-tea-af4baf060ace

第二章：机器学习模型的实战示例

监督学习是教授模型关于世界如何看的最简单方式。展示给定输入变量的组合如何导致某个输出，即使用标记数据，使得计算机能够预测另一个类似数据集的输出，即使它从未见过这个数据集。无监督学习涉及从非标记数据中寻找模式和有用的见解。

我们将研究不同类型的机器学习模型，试图理解其细节并实际执行必要的计算，以便这些模型的内部工作原理清晰且可重复。

在本章中，将涵盖以下主题：

• 使用多元线性回归理解监督学习

• 使用决策树理解监督学习

• 使用聚类理解无监督学习

技术要求

本章没有技术要求。我们只需要在 Excel 表中输入每个部分表中显示的值，以便紧密跟随解释。

使用多元线性回归理解监督学习

在上一章中，我们通过使用两个变量的线性回归示例。有趣的是，我们可以看到如何将回归应用于超过两个变量（称为多元线性回归）并从结果中提取有用信息。

假设你被要求测试一家公司是否存在性别歧视的隐藏政策。你可能是在一家领导该公司诉讼案的事务所工作，他们需要基于数据的证据来支持他们的主张。

你可以从抽取公司的工资单样本开始，包括描述每位员工及其最近一次工资增长额的几个变量。以下截图显示了在 Excel 工作表中输入这些值后的值集：

数据集中有四个数值特征：

• ID：员工识别码，与我们的分析无关

• Score：最后一名员工的绩效评估结果

• Years in company：员工在公司工作的年数

• Salary increase：最近一次工资增长的金额（美元）

剩下的两个是分类的：

• Gender：男性（M）或女性（F）

• Division：员工在公司工作的部门

分类值在使用模型之前需要编码。最终的数据表如下：

通过应用标准的 Excel 函数，可以轻松获得独热编码。假设B2是包含性别分类的第一个单元格，我们可以在B21单元格中输入=IF(B2="F";1;0)，并将此值复制到B37单元格以下的所有单元格。

根据 Windows 列表分隔符选项中定义的字符，你应在公式中使用逗号（，）或分号（；）。

为了对员工部门进行编码，我们使用独热编码（有关详细说明，请参阅第一章，实现机器学习算法）并创建三个新变量：IsProduction?、IsResearch?和IsSales?。如果E2是包含Division数据的第一个行，那么我们可以使用单元格E21、F21和G21中的函数=IF(E2="Production";1;0)、=IF(E2="Research";1;0)和=IF(E2="Sales";1;0)，然后将它们按列复制到单元格E37、F37和G37。

在尝试对整个数据集进行回归之前，我们可以尝试一些特征工程。让我们看看我们能够根据每个员工所在的哪个部门来预测薪资增长的效果。这将给我们一个关于“薪资增长”目标变量与“部门”之间相关性的概念（关于变量之间相关性的更多细节将在第五章，相关性与变量的重要性中介绍）。

让我们遵循一些简单的步骤来使用内置的回归工具：

1. 导航到数据。

2. 点击数据分析，如下面的截图所示：

1. 选择回归，如下面的截图所示：

1. 作为输入 Y 范围，选择“薪资”数据，作为输入 X 范围，选择三个“部门”列：

结果显示 R² = 0.1，这意味着只有 10%的薪资增长与员工属于某个特定部门有关或可以由此解释。因此，我们可以丢弃这些列作为输入，并专注于其余部分。

我们重复进行回归，这次选择 X 值为列“性别”、“得分”和“在公司工作年限”。

结果现在大不相同，R²接近 0.85，这意味着 85%的薪资增长值可以通过所选变量来解释。

“性别”的重要性如何？通过查看 Excel 给出的 P 值系数，如下表所示，我们可以看到，根据与输入变量相关的 P 值，最重要的是性别，其次是得分和公司工作年限。因此，很明显，性别在决定薪资增长时起着重要作用，我们有证据证明公司政策并非性别中立：

	系数	P 值
截距	141.72775	0.083481944
性别	-221.9209346	6.47796E-05
得分	2.697512241	0.004201513
在公司工作年限	8.118352407	0.332588988

回归分析的结果告诉我们我们能够多好地解释数据样本，但不能给我们一个准确的度量，即模型将如何预测薪资增长。为了探索这一点，我们应该做以下事情：

• 获取工资单的不同样本（在我们的例子中，我们可以手动生成新数据）

• 使用前表中列出的系数构建一个表达式，并计算给定输入变量的预测薪资增长

• 使用如第一章中所述的均方根误差来比较预测值和实际值，实现机器学习算法

让我们看看你是否能完成这个练习；我希望能提供的基本信息帮助你完成这项任务已经理解。

我们已经展示了如何在数据中执行多重线性回归以从中提取有趣的见解。让我们继续探讨另一个重要的机器学习模型：决策树。

通过决策树理解监督学习

决策树算法使用决策的树形模型。其名称来源于分割记录的级联过程的图形表示。算法选择更好的输入变量来将数据集分割成更纯的子集，从目标变量的角度来看，理想情况下是一个只包含该变量一个值的子集。决策树是一些最广泛使用且易于理解的分类算法。

树算法计算的结果是一组简单的规则，这些规则解释了哪些输入值的值或区间可以更好地分割原始数据。结果和得到这些结果所遵循的路径可以清晰地展示出来，这使得决策树在与其他算法相比时具有优势。"可解释性"是某些机器学习和人工智能系统的一个严重问题——这些系统大多被用作黑盒——并且本身就是一个研究课题。

在复杂问题中，我们需要决定何时停止树的发展。大量的特征可能导致一个非常大且复杂的树，因此树的分支数量和长度通常由用户限制。

熵是决策树中一个非常重要的概念，以及量化每个子样本纯度的方法。它衡量树中每个叶子的信息量。熵越低，信息量越大。零熵意味着一个子集只包含目标变量的一个值，而值为一表示一个子集包含相同数量的两个值。这个概念将在后面的例子中解释。

熵是衡量你的数据混乱程度的一个指标。

使用在每一步计算的熵，算法选择最佳变量来分割数据，并递归地重复相同的程序。用户可以决定何时停止计算，要么当所有子集的熵为零，要么没有更多特征可以分割，或者达到一个最小熵水平。

在决策树中使用最适合的特征是分类特征。在连续的数值变量情况下，应首先将其转换为类别，通过将其分为范围来实现；例如，A > 0.5 将是 A1，A ≤ 0.5 将是 A2。

让我们来看一个解释决策树算法概念的例子。

根据天气决定是否在户外训练

假设我们有一组关于经验足球教练关于是否带球队在户外（健身房外）训练的历史数据，包括在做出决策的日子上的天气条件。

一个典型的数据集可能如下所示：

该数据集是专门为这个例子创建的，当然可能不代表任何真实的决策。

在这个例子中，目标变量是Train outside，其余变量是模型特征。

根据数据表，可能的一个决策树如下所示：

我们选择根据展望特征的值来开始分割数据。我们可以看到，如果值是多云，那么训练在户外的决定总是是，并且不依赖于其他特征的值。晴天和雨天可以进一步分割以得到答案。

我们如何决定首先使用哪个特征以及如何继续？我们将使用熵值来衡量考虑不同输入特征时其值的变化程度。

目标变量的熵

当查看单个属性时，熵的定义如下：

在这里，c是特征f的可能值的总数，p[i]是每个值的概率，log[2](p[i])是相同概率的以 2 为底的对数。计算细节如下：

1. 我们需要在数据集中计算是和否决策的数量。在我们的简单例子中，它们可以手动计算，但如果数据集更大，我们可以使用 Excel 函数：

COUNTIF(F2:F15;"Yes") 和 COUNTIF(F2:F15;"No")

然后，我们得到计算结果，是 = 9 和 否 = 5。

1. 当将熵公式应用于目标变量时，我们得到以下结果：

在这里，概率是计算为 是 (9) 或 否 (5) 占总数 (14) 的比例。

此计算也可以很容易地在 Excel 表中使用 I3/(I3+J3)LOG(I3/(I3+J3);2)-J3/(I3+J3)LOG(J3/(I3+J3);2) 来执行，其中 I3=9 和 J3=5。

各个特征相对于目标变量的熵

两个变量f[1]和f[2]的熵定义为以下：

在这里，v代表f[2]的每个可能值，P(v)是每个值的概率，S(v)在之前的方程中定义。

频率表

让我们构建一个频率表，这是计算变量之间组合总数通常的方法。在我们的例子中，我们用它来决定哪个变量选择会导致熵的更大减少：

1. 计算特征值的各种组合，将每个特征与Train outside目标变量进行比较。在这个特定例子中，您可以手动计数，但如果我们处理更大的数据集，有一个一般的方法来做这件事是有用的。

2. 要计算特征组合的数量，我们首先将数据表中的值成对连接。例如，CONCATENATE(B2;"_";F2) 给我们 Hot_No。

3. 如果我们将公式向下复制以完成总行数，我们将得到Temperature和Train outside变量的所有可能组合。

4. 如果我们用其余的特征重复相同的计算，结果将如下所示：

1. 创建交叉表来计算每列中唯一值的数量，即唯一组合的数量。这可以通过选择列中的整个范围，在所选区域的任何位置右键单击，然后左键单击快速分析来完成。以下对话框将弹出：

1. 选择 表格 | 交叉表创建如下表格：

1. 对所有列重复相同的程序，构建所有频率表和双变量熵。结果表和熵计算在以下小节中展示。

熵计算

Outlook-户外训练组合的频率表如下：

晴朗	户外训练
	是
晴朗	3
阴天	4
雨天	2

使用这些值，我们得到两个变量的熵，如下所示：

p(Sunny).S(Sunny)+p(Overcast).S(Overcast)+p(Rainy)S(Rainy)=*

5/14(-3/5log2(3/5)-2/5log2(2/5)) +*

4/14(-4/4log2(4/4)-0/4log2(0/4))+*

5/14(-2/5log2(2/5)-3/5log2(3/5))=*

0.693

在这里，p(Sunny) = (#Yes+#No)/Total entries = (2+3)/14, p(Overcast) = (#Yes+#No)/Total entries = (4+0)/14, p(Rainy) = (#Yes+#No)/Total entries = (2+3)/14. 熵值 S(v) 是使用相应的概率计算的，即 #Yes 或 #No 除以总 #Yes+#No。

温度-户外训练组合的频率表如下：

温度		户外训练
	是	否
热	2	2
温和	4	2
凉爽	3	1

使用这些值和类似的计算，熵的详细情况如下所示：

p(Hot).S(Hot)+p(Mild).S(Mild)+p(Cool)S(Cool)=*

4/14(-2/4log2-2/4log2) +*

6/14(-4/6log2-2/6log2)+*

4/14(-3/4log2-1/4log2) =*

0,911

组合 Humidity-Train outside 的频率表如下：

Humidity		Train Outside
Yes	No
High	3	4
Normal	6	1

使用这些值，我们得到以下熵：

p(High).S(High)+p(Normal).S(Normal)=

7/14(-3/7log2-4/7log2) +*

7/14(-6/7log2-1/7log2)=*

0,788

Windy-Train outside 组合的频率表如下：

Windy		Train Outside
	Yes	No
TRUE	6	2
FALSE	3	3

使用这些值，我们得到以下熵：

p(True).S(True)+p(False).S(False)=

8/14(-6/8log2-2/8log2) +*

6/14(-3/6log2-3/6log2)*

=0,892

比较熵差（信息增益）

要知道选择哪个变量作为第一次分割，我们计算从原始数据到相应子集的信息增益G，即熵值之差：

这里，S(f[1])是目标变量的熵，而S(f[1],f2)是每个特征相对于目标变量的熵。熵值已在之前的子节中计算，因此我们在此使用它们：

• 如果我们选择Outlook作为第一次分割树的变量，信息增益如下：

G(Train outside,Outlook) = S(Train outside) - S(Train outside,Outlook) *                                                 = 0.94-0.693=0.247*

• 如果我们选择Temperature，信息增益如下：

G(Train outside,Temperature) = S(Train outside) - S(Train outside,Temperature) *                                                           = 0.94-0.911=0.029*

• 如果我们选择Humidity，信息增益如下：

G(Train outside,Humidity) = S(Train outside) - S(Train outside,Humidity) *                                                     = 0.94-0.788=0.152*

• 最后，选择Windy给出以下信息增益：

G(Train outside,Windy) = S(Train outside) - S(Train outside,Windy)

= 0.94-0.892=0.048*

所有这些计算都可以使用 Excel 公式在电子表格中轻松完成。

树第一次分割时选择的变量是显示最大信息增益的变量，即Outlook。如果我们这样做，我们会注意到分割后产生的其中一个子集具有零熵，因此我们不需要进一步分割它。

要继续按照类似程序构建树，需要采取的步骤如下：

1. 计算S(Sunny), S(Sunny,Temperature), S(Sunny,Humidity), 和 S(Sunny,Windy)。

2. 计算G**(Sunny,Temperature), G(Sunny,Humidity), 和 G(Sunny,Windy)。

3. 较大的值将告诉我们使用哪个特征来分割Sunny。

4. 使用S(Rainy), S(Rainy,Temperature), S(Rainy,Humidity), 和 S(Rainy,Windy)来计算其他增益。

5. 较大的值将告诉我们使用哪个特征来分割Rainy。

6. 继续迭代，直到没有可用的特征为止。

正如我们将在本书后面看到的那样，树永远不会手工构建。理解它们的工作原理和涉及的计算非常重要。使用 Excel，可以轻松地跟随整个流程和每一步。遵循同样的原则，我们将在下一节中通过一个无监督学习示例进行操作。

通过聚类理解无监督学习

聚类是一种统计方法，试图根据距离度量将数据集中的点分组，通常是欧几里得距离，它计算一对点坐标之间平方差的平方根。简单来说，那些被分类在同一聚类内的点，在定义的距离意义上彼此更近，比属于其他聚类的点更近。同时，两个聚类之间的距离越大，我们就能更好地区分它们。这类似于说，我们试图构建成员之间更相似、与其他群体成员差异更大的群体。

很明显，聚类算法最重要的部分是定义和计算两个给定点之间的距离，并迭代地将点分配到定义的聚类中，直到聚类组成没有变化。

在尝试聚类分析之前，有几个要点需要考虑。并非每种类型的数据都适合聚类。例如，我们不能使用二进制数据，因为无法定义距离。值要么是1，要么是0，中间没有值。这排除了由 one-hot 编码生成的那种类型的数据。只有显示某种顺序或尺度的数据对聚类有用。即使数据值是真实的（例如，例如客户的支出金额或年收入），最好将它们分组在范围尺度上。

聚类用例的几个例子如下：

• 自动分组 IT 警报以分配优先级并相应地解决它们

• 通过不同渠道分析客户沟通（按时间段细分）

• 犯罪画像

• 城市流动性分析

• 欺诈检测（寻找异常值）

• 运动员表现分析

• 地理犯罪分析

• 配送物流

• 文档分类

现在，让我们通过一些例子来解释聚类算法的概念。

按月购买金额分组客户

现在，我们将跟随从客户数据生成聚类的全部计算和分析。这是一个典型的聚类算法的简化版本，展示了所有步骤但减少了迭代次数以便理解。聚类通常自动进行，但理解计算背后的逻辑很重要。

要使用的数据集包含 20 个不同客户在一个网店中每月花费的总金额，对应于给定年份的五月、六月和七月。一旦在 Excel 表中输入，数据看起来是这样的：

对于每个月，我们可以计算描述数据的几个主要参数：最小值、最大值、中位数和平均值：

	五月	六月	七月
最小值	316.89	500.66	185.63
最大值	11889.66	12214.41	11982.64
中位数	8388.63	8156.16	7708.27
平均值	6182.20	6229.24	6227.81

我们简单地使用 Excel 内置函数 MIN(), MAX(), MEDIAN(), 和 AVERAGE()，包括每个列的完整范围。

在聚类分析中，对数据集进行归一化是有用的，也就是说，将所有值转换为落在区间 [0,1] 内。这有助于我们处理那些与大多数点值差异很大的异常值，这些值可能会影响簇的定义。归一化后，这些点与其他点之间的距离就不那么远了，并且可以很容易地分组。显然，如果聚类分析的目标是找到这些异常值，那么保持数据集原样并突出异常值与数据集其他部分之间的差异是一个更好的主意。

归一化数据的最简单方法是将每个值除以对应列的最大值。为此，请按照以下步骤操作：

1. 在单元格 G2 中，输入 =B2/$B$24。 我们假设 B2 是五月列的第一个值，并且最大值在 B24。

2. 将此公式复制到整个列中。记住，在 Excel 中，在单元格 ID 前添加 $ 可以在复制内容到另一个单元格时固定该值。归一化后的表格如下所示：

让我们花点时间可视化数据并更深入地理解它。如果我们成对地考虑列，那么可以生成散点图，并按照以下步骤尝试通过视觉方式找到簇：

1. 选择五月和六月数据。

2. 点击 插入 | 散点图。

生成的图表如下所示：

可以识别出三个簇，并在前面的屏幕截图中被圈出。它们对应于每月花费相似金额的客户群体。

1. 用相同的方法处理五月和七月，我们得到以下图表：

在这种情况下，我们可以说有两个大簇，或者其中一个簇可以进一步分为两个。分离并不那么清晰，选择将取决于其他变量（记住，最好的模型总是最适合业务需求的模型）。

1. 最后，我们绘制了六月和七月的数据：

在这里，簇的划分似乎更加清晰，我们可以圈出三组点。

如果我们想同时考虑三个月怎么办？有一个迭代过程可以完成这个任务，这是称为K-means的聚类算法的基础。让我们详细遵循这个算法的步骤：

1. 决定你想要将数据分成多少个簇。这通常不是一个容易的决定。它将强烈依赖于数据集，在某些情况下，可能需要测试不同的值，直到你得到一个能够对数据提供有用见解的簇数。

2. 考虑到之前的视觉分析，我们决定选择三个作为簇的数量。

3. 以任意三个点作为簇的中心。起始点的选择并不重要，因为我们将会重复整个过程，直到结果簇成员没有变化。然后我们选择列表中的前三个点，如下表所示：

	May	June	July
Random1	0.055568104	0.043735522	0.15581034
Random2	0.07079235	0.067065974	0.079319396
Random3	0.026652635	0.040988882	0.171590079

1. 找到离它们更近的点，计算所有其他点到这些簇中心的距离。两点之间的欧几里得距离，P[1] =(x[1],y[1]**,z[1]) 和 P[2] = (x[2],y[2]**,z[2]), 定义如下：

使用 Excel 的内置 SUMXMY2([array1];array2}) 函数计算每个点到簇中心的 (DE)²。

1. 对于每个数据点，你将得到三个距离值。选择最小的一个来决定点属于哪个簇。例如，对于客户 ID = 4，我们得到以下信息：

D1	D2	D3	Cluster
0.019689391	0.004847815	0.025218271	2

在这里，D1，D1，和 D3 是点到相应簇中心的距离。最小的距离告诉我们这个点属于第二个簇。例如，对于客户 ID = 4 的 D1 计算如下 =SUMXMY2(B5:D5;$B\(23:\)D\(23)*，假设 Random1 的`五月`和 Random1 的`六月`分别位于单元格*\)B\(23*和*\)D$23。

1. 完整的结果数据表如下：

![可以通过在第一行输入以下公式然后向下复制来创建最后一列：=IF(E2=MIN(E2:G2);1;IF(F2=MIN(E2:G2);2;3))。根据表格，我们的第一个结果确实不平衡。大多数点落在簇一，少数在簇二，只有一个在簇三。我们需要继续计算，看看结果如何演变。遵循以下步骤：1. 我们现在将使用我们获得的簇的平均值而不是选择随机点。1. 按簇编号对表格进行排序。结果表格现在略有不同，如下所示：

1. 使用 MEAN() 函数计算每个月每个聚类的平均值。你应该得到以下表格中显示的相同结果：

	五月	六月	七月
Mean1	0.618762809	0.605805489	0.618056642
Mean2	0.157477363	0.155314048	0.111411008
Mean3	0.026652635	0.040988882	0.171590079

例如，对应于 五月 的 Mean1 是通过 AVERAGE(B2:B17) 计算得出的。

1. 使用与之前相同的公式，并计算所有其他点到平均值之间的距离，你会得到一个类似于这个表格的表格：

在第二次迭代后，一些点被移动到从聚类一移开的位置，现在它们属于聚类二和三。

1. 再进行一次计算。根据前面的表格，新的平均值如下：

	五月	六月	七月
Mean1	0.843481911	0.832289469	0.810799822
Mean2	0.292624962	0.280240044	0.310753303
Mean3	0.052180197	0.048870976	0.105552835

1. 可以将包含距离和聚类编号的表格表示如下：

在第三次迭代后，只有一个点改变了聚类，从二变为三；因此，我们正在接近最终结果。你应该能够再进行一次迭代，按照相同的步骤，证明它不会改变聚类标签，这意味着计算收敛到了一个稳定的聚类数量。

现实生活中的数据集可能不会那么快收敛。我们展示的是一个简化的例子，足以展示迭代的每一步，理解它们，并得到一个合理的结果。聚类通常不是手动计算的，而是通过预构建的算法执行。

在下一章中，你将学习如何将数据从不同的来源导入 Excel，这样你就不需要手动输入值。这将为你分析真实数据提供一个起点，通常比本章中展示的例子包含更多的变量和值。

摘要

在本章中，我们描述了将监督学习和无监督机器学习模型应用于解决问题的实际例子。我们涵盖了多元回归、决策树和聚类。我们还展示了如何选择和转换模型要摄入的输入变量或特征。

本章仅展示了每个算法的基本原理。在现实数据分析和预测中使用机器学习时，模型已经编程，可以用作黑盒。因此，了解每个模型的基本原理并知道我们是否正确使用它非常重要。

在接下来的章节中，我们将关注如何从不同的来源提取数据，根据我们的需求对其进行转换，并使用先前构建的模型进行分析。

问题

1. 为什么对分类特征进行编码很重要？

2. 有哪些不同的方法可以停止决策树计算？

3. 在示例中，Temperature_hot 的熵值为一。为什么？

4. 按照第 Understanding supervised learning with decision trees 节开始处的决策树图，决定是否进行外部训练的路径是什么？可以考虑使用 IF 语句。

5. 如果我们选择不同的起始点，聚类分布会改变吗？你可以阅读推荐的文章了解这一点。

6. 通过迭代分析获得的聚类与通过视觉确定的聚类是否相同？为什么？

进一步阅读

• 如何解释回归分析结果：P 值和系数：blog.minitab.com/blog/adventures-in-statistics-2/how-to-interpret-regression-analysis-results-p-values-and-coefficients

• 使用 Microsoft Excel 教学决策树分类，发表于《INFORMS Transactions on Education》第 11 卷第 3 期，第 123–131 页，作者：Kaan Ataman, George Kulick, Thaddeus Sim: pubsonline.informs.org/doi/10.1287/ited.1100.0060

• K 均值算法综述，发表于《International Journal of Engineering Trends and Technology (IJETT)》第 4 卷第 7 期，2013 年 7 月：www.ijettjournal.org/volume-4/issue-7/IJETT-V4I7P139.pdf

第二部分：数据收集和准备

一旦完成这些，读者应该对如何收集和清洗数据有非常清晰的理解，认识到存在不同的数据源、格式和数量。同时也会清楚在应用任何机器学习模型之前理解数据和问题是必要的。

本节包括以下章节：

• 第三章，从不同数据源导入数据到 Excel

• 第四章，数据清洗和初步数据分析

• 第五章，相关性与变量的重要性

第三章：从不同的数据源将数据导入 Excel

严肃的数据分析和机器学习不能仅通过手工输入数据来完成。数据源有不同的风味和大小，Excel 可以处理其中许多。本章将讨论如何从不同的来源导入数据，这是任何分析的第一步。

Get & Transform（在 Excel 2016 之前的版本中称为Power Query）是一个强大的工具，您可以使用它从不同的来源加载数据并进行转换。这些转换是必要的，以便您有一个干净的数据样本，然后可以使用它来训练和测试任何机器学习模型。

如果您正在运行 Excel 2010 SP1 或 Excel 2013，您需要下载并安装 Power Query。有关安装说明，请参阅链接www.microsoft.com/en-us/download/details.aspx?id=39379。

在本章中，我们将涵盖以下主题：

• 从文本文件导入数据

• 从另一个 Excel 工作簿导入数据

• 从网页导入数据

• 从 Facebook 导入数据

• 从 JSON 文件导入数据

• 从数据库导入数据

技术要求

您需要从本书的 GitHub 仓库下载homes.csv、homes.txt、titanic.xls和azure_text_analytics.json文件，该仓库地址为github.com/PacktPublishing/Hands-On-Machine-Learning-with-Microsoft-Excel-2019/tree/master/Chapter03。

从文本文件导入数据

最常用的数据文本文件是逗号分隔值（CSV）。正如其名所示，值按行写入文件，并且对于每一行，逗号分隔了属于每一列的值。打开一个新的工作簿，按照以下步骤操作：

1. 点击“数据”。

2. 导航到“获取数据”|“从文件”|“从文本/CSV”：

1. 导航到文件的存储位置并打开homes.csv文件。

2. 将弹出一个窗口，显示文件内容的预览，如下面的截图所示：

我们可以看到，Excel 通过使用正确的分隔符（逗号）正确地识别了不同的列。它还尝试自动检测数据类型。不过，这里有一个小问题。这个文件不是纯 CSV 格式，开头多了一行，显示了它最初是从哪里下载的。这对于给原作者致谢是好的，但它会稍微混淆加载过程。幸运的是，这种情况已经被考虑到了。要解决这个问题，请点击“编辑”。

1. 将弹出一个窗口，包含许多处理这些文件格式差异的选项：

1. 导航到“删除行”|“删除顶部行”。

2. 你将看到指定要跳过多少行的选项。在这个文件中，我们需要跳过2行，如下面的截图所示：

在移除行之后，我们只保留感兴趣的数据。你会注意到文件标题尚未用作列名，这将是理想的。为了纠正这一点，点击使用第一行作为标题。

1. 如下截图所示，这是结果：

注意，步骤列表已经应用于输入数据，位于表格的右侧。这允许你撤销任何操作，并且有助于跟踪你所做的一切，尤其是当你进行更复杂的转换时，比如我们在下一章将要展示给你的那些。

过程几乎准备好了，除了每列的数据类型。最初，它被自动设置为文本，因为我们跳过的前两行后来被评估为不包含数值数据。有两种类型的列：Acres显示十进制数，而其余的显示整数或整个数字。为了正确定义数据类型，执行以下步骤：

1. 选择Acres。

2. 导航到转换菜单中的数据类型。

3. 将类型更改为十进制数。

4. 选择其余的列，并将类型更改为整数以修复其他列。

5. 最后，点击关闭并加载。你会看到以下数据表：

这个表格看起来像任何 Excel 表格，除了我们总是可以双击查询（称为homes）并执行更多转换，甚至撤销我们已做的转换。

如果文件不是 CSV 文件，而是一个使用不同分隔符的文本文件，我们仍然可以使用类似的程序来加载它。我们只需重复用于导入 CSV 文件的步骤：

1. 点击数据。

2. 导航到“获取数据”|“从文件”|“从文本/CSV”。

3. 导航到文件的存储位置并打开homes.txt。你会看到以下预览：

在这种情况下，开头没有多余的行，但我们可以看到 Excel 未能识别列分隔符，即|字符。为了修复这个问题，从下拉菜单中选择自定义。

1. 输入|。

2. 结果是数据现在已经被正确地分隔成列。列自动使用文件标题进行标记，并且数据类型被正确猜测，如下面的截图所示：

最终结果是表格与我们从homes.csv加载的表格相同，但获得它所需的步骤更少，因为文件结构更简单。

在本节中，我们学习了如何将文本文件导入 Excel，这是最简单的情况。让我们继续一些更复杂的例子。

从另一个 Excel 工作簿导入数据

为什么我们还要从 Excel 工作簿导入数据，如果我们可以直接打开它呢？主要原因是想利用 Get & Transform 可以进行的转换。我们将通过使用包含泰坦尼克号乘客真实数据的文件来展示这一点，这个文件常被用来测试机器学习分类模型，并预测某个乘客是否在悲剧中幸存。

让我们遵循一些简单的步骤来加载数据并进行转换。在新的工作簿中，请按照以下步骤操作：

1. 点击“数据”。

2. 导航到“获取数据 | 从文件 | 从工作簿”，如下面的截图所示：

预览窗口与我们打开 CSV 文件时看到的略有不同，如下面的截图所示：

在预览的左侧，你可以看到一个名为 titanic.xls 的文件夹，它代表文件，下面是包含的工作表。Dictionary 只是变量的简短描述；数据包含在 Passenger data 工作表中。

当选择此项目时，我们将看到一个包含数据的表格。我们可以点击“加载”并将数据放入我们的工作表中，但在尝试我们将在下一章详细讨论的转换之前，我们将先尝试一下。为此，点击“编辑”并进入查询编辑器，如下面的截图所示：

列被正确识别，并且从列名中提取的名称被放置在输入文件中。你可以看到其中一个列，cabin，包含缺失值，标记为 null。这意味着对于这些乘客，没有关于他们在船上哪个船舱的信息。一些机器学习模型不接受空值，因此我们需要通过选择该列，如前一个截图所示来修复这个问题。

1. 在转换菜单中点击“替换值”。

2. 你将得到一个弹出对话框，你可以告诉 Excel 将 null 替换为 Unknown，如下面的截图所示：

结果如以下截图所示，所有 null 值都被替换为 Unknown。这对于机器学习模型来说既更优雅也更容易管理：

使用查询编辑器可以对数据进行多种转换。我们将在下一章中展示其他示例。你应该尝试测试不同的选项。

从网页导入数据

如果一个网页包含表格形式的数据，那么 Excel 能够自动导入这些表格。作为一个例子，我们将从关于 Excel 的维基百科页面导入一个表格。以下是我们的操作步骤：

1. 点击“数据”，然后导航到“获取数据 | 从其他来源 | 从网页”，如下面的截图所示：

1. 在弹出的对话框中，我们输入所提及网页的 URL 并点击确定，如下截图所示：

1. 现在，我们将能够看到可用表格的列表。选择显示 Excel 发布历史的表格。右侧显示了表格预览，如下截图所示：

1. 就像我们之前做的那样，我们只需加载数据或编辑它，根据我们的需求转换值。最终结果是 Excel 表格。

我们还可以选择切换到网页视图，就像在 Internet Explorer 中查看目标网页一样，这有助于我们确定哪些信息对我们的分析有用。

我们已经看到从网页导入数据是多么容易。现在，让我们探索从最受欢迎的网站之一：Facebook 导入数据。

从 Facebook 导入数据

可以直接从 Facebook 个人资料或页面导入数据。如果我们想记录新帖子出现的时间和日期，例如，我们可以按照以下步骤操作：

1. 点击“数据”。

2. 导航到“获取数据”|“从在线服务”|“从 Facebook”，如下截图所示：

1. 您需要指定用户或页面名称，这是页面 URL 的最后部分。例如，我将使用我的 Facebook 页面，这是默认选项。第一次连接时，您将需要使用用户名和密码登录（要从 Excel 连接到 Facebook，您需要一个 Facebook 个人资料）。以下对话框将出现：

1. 我们将选择获取帖子信息。点击确定后，我们得到以下屏幕（我将只展示部分，以保护我的隐私）：

1. 如我们所见，帖子的时间和日期被合并在一个单独的列中。要拆分这个列，点击编辑。

2. 选择列，然后在查询编辑器中选择“拆分列”。

3. 在对话框中，指定您想要通过字符T拆分列，如下截图所示：

1. 因此，你将得到两个新的列，一个用于日期，另一个用于时间，如下截图所示：

您已成功从您的 Facebook 页面提取信息，您现在可以使用这些信息来分析您发布频率，发布的内容类型以及其他有用的见解。这对于查看商业个人资料尤其有用，您可能希望调整内容以提高您的服务或产品的营销。

从 JSON 文件导入数据

JSON 是一种标准的数据共享格式，因为它使用人类可读的文本字段。它被大多数网络应用程序用于数据输入和输出。

在我们的示例中，我们将使用 Azure 文本分析 API。给定一个句子，这项服务可以识别文本情感、语言并提取关键词等。

输入句子为我在西雅图度过了一次美妙的旅行，并享受了看到太空针塔的乐趣！。API 正确识别语言为英语，提取了主要关键词，并告诉我们情感是积极的（分配的值大于 0.5）。所有这些信息都以 JSON 格式显示在窗口的右侧，并且可以在我们即将导入的文件中找到。以下截图是从 Azure 文本分析演示页面提取的：

要加载输入的 JSON 文件，请按照以下步骤操作：

1. 点击“数据”。

2. 导航到获取数据 | 从文件 | 从 JSON，如下截图所示：

1. 您将获得一个预览，显示 JSON 结构中的主要字段，如下截图所示：

1. 点击“转换为表格”将条目转换为常规 Excel 表格，如下截图所示：

值列是一个包含嵌套值的特殊表列。要导航到所需的变量，我们需要点击符号，并在随后出现的列中重复此操作。通过这样做，例如，我们可以获取情感值，它是 0.97。一旦没有更多的列可以展开，我们就可以加载数据并创建一个包含 JSON 结构中所有值的常规 Excel 表格，这将用于进一步分析。

从数据库导入数据

可用的数据库种类繁多，Excel 可以连接到其中大多数。对于所有这些数据库，连接过程都是相似的。我们将以一个为例：免费的 MS SQL Server Express 数据库，可以在任何计算机上下载。它有一些限制，但对于学习和测试少量数据来说非常实用。假设您的计算机中有一个本地数据库，请按照以下步骤连接数据库：

1. 点击“数据”。

2. 导航到获取数据 | 从数据库 | 从 SQL 服务器数据库，如下截图所示：

1. 弹出对话框将请求数据库服务器的名称。在这种情况下，它是本地计算机名称和 SQL Express 服务器。可选地，我们可以添加数据库名称，但如果我们留空，我们将能够看到服务器中所有数据库的列表。以下截图显示了这些细节：

1. 使用您的凭证连接到数据库。您可能可以使用 Windows 用户名和密码登录（如下面的截图所示）或者您可能有一个用于数据库的特殊用户名和密码对：

1. 如果连接成功，您将看到服务器上所有数据库及其包含的表的列表。

2. 选择一个表格，并获取通常的预览，如下面的截图所示：

与前述案例一样，您可以选择编辑数据或加载数据。最终结果总是一个 Excel 表格。

现在，您已经知道如何将 Excel 表格连接到数据库，这是最常用的数据存储方式。现在我们有了数据，下一步是准备它以进行分析。这将在下一章中详细展示。

摘要

在本章中，我们描述了将信息输入到 Excel 工作表中的不同方法，这超出了手动输入数据的范围。通过使用 Power Query 和查询编辑器，可以从 Excel 内部分析各种文件类型、网络数据源和数据库，以提取、转换和加载数据。我鼓励您探索其他数据源，因为加载过程非常相似。

到目前为止，我们已经看到了一些非常简单的数据转换在数据加载之前被应用。在下一章中，我们将讨论更多用于数据清洗的高级技术。

问题

1. 在文本文件中可以使用哪些字符作为分隔符？

2. 为什么在加载数据之前预处理数据如此重要？

3. 打开 Excel 文件和导入它之间有什么区别？

4. 可以从网页导入哪些类型的信息？

5. JSON 是用于交换信息的结构化格式之一。还有哪些格式存在？

6. 进行一些在线研究，了解使用数据库而不是单个数据文件的优势。

进一步阅读

• Power Query 文档：docs.microsoft.com/en-us/power-query/

• 查询编辑器（Power Query）简介：support.office.com/en-us/article/introduction-to-the-query-editor-power-query-1d6cdb63-bf70-4ae8-a7d5-6ae9547004d9

• 介绍 JSON：www.json.org/

第四章：数据清洗和初步数据分析

在获取到正确数据后，最困难且耗时的工作是将其准备好进行分析。在可以使用任何模型之前，了解给定数据集可以做什么和不能做什么是第一步。本章演示了如何使用 Excel 函数搜索和替换模式，以及如何找到错误的数据类型和缺失数据。它还包含一些有用的图表，以便我们可以从数据中获得洞察力并理解不同的变量。

在本章中，我们将涵盖以下主题：

• 数据清洗

• 可视化数据以进行初步分析

• 理解不平衡数据集

技术要求

您需要从 GitHub 仓库github.com/PacktPublishing/Hands-On-Machine-Learning-with-Microsoft-Excel-2019/tree/master/Chapter04下载titanic.xls文件。

数据清洗

数据永远不会干净——它总是包含缺失值、错误、不正确的格式和其他问题，这些问题使得在没有预处理的情况下无法将其提供给机器学习模型。这就是数据清洗的全部内容——在开始真正的分析之前纠正所有这些问题。

作为如何清洗数据集的示例，我们将使用泰坦尼克号乘客数据集。我们将重复上一章中“从另一个 Excel 工作簿导入数据”部分所描述的程序，以从 Excel 工作簿中导入数据。我们将使用泰坦尼克号乘客的实时数据，并演示如何为分析准备它。

要清洗数据集，执行以下必要步骤：

1. 导航到数据 | 从文件 | 从工作簿，如下面的截图所示：

1. 在选择titanic.xlsx文件和Passenger data工作表后，我们预览了文件的内容，如下面的截图所示：

1. 点击“编辑”并开始数据清洗过程。我们首先注意到我们不需要包含乘客姓名的列；它对我们的分析没有提供任何有用的信息。实际上，在大多数情况下，我们会被要求从我们的数据中删除个人信息，这是由于隐私政策的要求。

2. 选择name列。

3. 点击“移除列；”后的表格将如下所示：

1. 将cabin列中的所有nulls替换为unknown：

还有两列包含缺失值：boat 和 body。根据数据字典，它们告诉我们乘客所在的救生艇（如果他们幸存），以及他们死亡时分配给其身体的 ID。存在缺失值，但也有一些情况下我们无法获得值；显然，已故乘客没有使用救生艇，幸存者的身体 ID 未识别。我们将使用一些函数来处理这些选项。

1. 在查询编辑器中，选择“添加列”选项卡。

2. 选择自定义列。

3. 对话框显示我们可以命名新列并定义其内容。在文本框中输入 boat_corrected。

4. 定义一个函数来计算列的内容，如下所示：

if [survived]=1 and [boat] = null then "unknown" else [boat]

这意味着如果乘客幸存且船名缺失，我们将值设置为 unknown。否则，我们只需将值复制到原始列中，如下面的截图所示：

1. 添加另一个新列，以纠正 body 中的值，并为该列定义不同的值：

if [survived]=0 and [body] = null then "not recovered" else [body]

在这种情况下，我们想说的是，如果乘客在船难中没有幸存，并且没有身体 ID，那么他们可能没有被从水中打捞上来。

重新排序列后的结果是以下内容：

新列中仍有 null 值；它们对应于前面的情况（即没有救生艇的已故乘客，或没有身体 ID 的幸存者）。将这些值替换为 N/A；结果表格如下所示：

最后要修改的列是 age。我们将通过将乘客分组到年龄范围来简化这一点，并用缺失值替换。

1. 将所有缺失值（null）替换为 -1。我们可以通过选择列并点击“替换值”来完成此操作。

2. 导航到“添加列”选项卡。

3. 点击“条件列”并定义几个组，具体取决于年龄范围，如下面的截图所示：

结果是一个新的列（Age group），它包含不同的年龄组而不是年龄值，并用 unknown 替换 null 值。结果表格如下：

我们的数据集现在已清理完毕，准备进行一些初步分析，我们将在下一节中展示。

可视化数据以进行初步分析

在清理数据集之后，总是建议对其进行可视化。这有助于我们了解不同的变量，它们值的分布情况以及它们之间的相关性（我们将在下一章中更详细地探讨相关性）。我们可以确定哪些变量对我们的分析很重要，哪些变量提供了更多信息，以及哪些变量因为冗余可以被舍弃。

我们将首先查看几个条形图，我们将要么计数每个值的出现次数（使用直方图），要么显示每个值相对于总数的百分比（使用条形图）。为了实现这一点，请执行以下步骤：

1. 在表格中的任何单元格上右键单击以访问快速分析选项：

1. 在弹出窗口中，我们可以选择图表类型。选择簇状柱形图，如图所示：

默认情况下，Excel 创建了一个显示一些变量的数据透视表；我们需要更改变量和分组操作，以反映我们的需求。

1. 在右下角，我们将看到一个标记为Σ的窗口。在里面，我们可以点击变量并显示菜单，如图所示：

1. 点击值字段设置；您将看到一个弹出窗口，类似于以下截图，您可以在其中将总和更改为计数，因为我们想计数值，然后计算它们的总和：

我们将Age变量转换为Age group，因此这是我们现在想要使用的变量。也就是说，我们想要计算给定年龄组中有多少乘客。

1. 将数据透视表字段的选择更改为Age group。

2. 现在，更改图表标题并移动它，使其看起来类似于以下截图：

我们可以看到，列表中的大多数乘客都是成年人。我们对列表中的许多乘客有缺失信息，其余的群体共享少量乘客。图表中使用的表格可以在左上角看到。

一个有趣的问题是要问，不同年龄组的生存概率是否有差异？ 为了回答这个问题，我们需要将survived变量添加到轴（类别）窗口中，该窗口位于工作表的右下角。我们通过从数据透视表字段窗口拖动变量并将其放入轴来实现这一点。生成的图表如下：

这个图表有一个显著的问题——我们无法轻易地比较年龄组，因为它们的成员数量差异很大。解决方案是将所有内容都参照每个年龄组的总乘客数，并显示百分比。查看之前的图表，我们可以看到数据首先按年龄组分组，然后按生还情况分组。第一个是父变量。重复我们之前描述的步骤，导航到值字段设置菜单；你将看到一个类似于以下截图中的弹出窗口：

1. 点击显示值方式标签页，如前述截图所示。

2. 选择父总百分比选项，并将年龄组字段作为父变量；结果如下截图所示：

百分比是相对于每个年龄组内乘客总数的，现在比较起来更容易。老年组的大多数人没有在沉船中幸存，大多数成年人遭遇了同样的命运，儿童和青少年的数字似乎很均匀。很明显，大多数婴儿都幸存了，可能是因为他们在登船时被优先考虑。

重复相同的步骤，我们可以假设头等舱的乘客比其他乘客更有可能生还；考虑以下图表：

是的，如果你是头等舱乘客，比在三等舱更容易生还。这是泰坦尼克号悲剧的一个已知事实，我们可以在数据中看到它的反映。不同舱位的旅行条件以及安全措施都大不相同。

那么，关于性别呢？乘客是男性还是女性有关系吗？让我们构建一个男性和女性生还人数的直方图，并使用它来回答我们的问题。结果图表如下截图所示：

如你所见，性别显然很重要。女性生还者的概率比男性高，至少在一般意义上是这样。这其中的原因可能是因为女性在登船时被优先考虑，而男性，尤其是年轻人，被延迟以帮助其他乘客，因此他们没有及时到达救生艇。

我们结合了我们对数据集的先前知识和数据信息，以更好地理解我们可以和不能做什么。我们鼓励你为其他变量和组合创建图表，并尝试理解结果。为了理解我们从机器学习模型中获得的结果是否合理，理解数据集的基本细节至关重要。

理解不平衡数据集

为了能够比较不同变量的结果，我们需要考虑每个类别的样本数量不同。假设我们想要训练一个机器学习模型来预测给定乘客是否会幸存，基于年龄组、性别和舱位。如果我们绘制survived变量值的分布，我们会看到以下情况：

从前面的图表和表格中可以清楚地看出，非幸存者的人数几乎是幸存者的两倍。如果我们直接使用这个数据集，我们会在数据集中引入一个偏差，这将影响结果。预测生存变量为0的概率将大约是预测为1的两倍。这个陈述的例外是决策树及其相关的预测模型（如随机森林和 XGBoost），它们可以正确处理不平衡的数据集。其他模型，尤其是神经网络，对不均匀的数据集非常敏感。算法中的种族、性别和其他偏差引发了关于人工智能（AI）在所有级别上广泛应用的担忧。当将 AI 应用于现实生活时，这是一个严重的问题，防止这种情况的可能的解决方案仍在研究中。

给定足够的数据条目，平衡数据集的一个简单方法是从多数类别随机选择与少数类别数量相等的条目。在这种情况下，我们可能从显示survivor为0的行中选择 500 行。让我们使用以下步骤来完成：

1. 按照以下截图所示筛选条目：

1. 复制条目并将它们粘贴到一个新的工作表中。

2. 在开头插入一个新列，命名为ID。

3. 将数据转换成表格（插入 | 表格，保留第一行作为标题）。

4. 在第一个单元格中输入以下公式，并将其复制到该列的其余部分：

=RAND()

1. 确保自动计算已关闭。为此，导航到公式 | 计算选项并勾选手动。这将防止随机数自动更改：

1. 按 ID 排序数据（你可以选择升序或降序，这没有关系）。

2. 选择前 500 行作为你的随机样本。

3. 将这些行复制到新工作表中。

4. 添加 500 行，其中survived为1。

现在，你有一个包含 1,000 个条目的完美平衡数据集，你可以用它来训练你的机器学习模型。

摘要

在本章中，我们探讨了处理缺失数据的不同方法，并学习了如何对其进行分组或汇总。我们向您展示了在数据清洗后可视化数据的重要性，以便能够理解和解释从基本到更高级模型预测的结果。这是任何特征工程的开端，因为我们根据特征值进行转换和/或丢弃特征。过多的缺失值将意味着我们无法使用该变量（或特征），或者高度相关性将意味着我们可以丢弃相关变量中的一个。我们将在下一章中更深入地探讨相关性，向您展示如何使用不同的方法进行定量测量。

初步数据可视化对于理解数据属性和解释我们获得的结果至关重要，即使在应用机器学习模型之后。

问题

1. 回顾上一章所解释的内容，使用class（类别）、gender（性别）和Age group（年龄组）作为特征，survived（幸存）作为目标变量，构建一个决策树。你应该能够为乘客生存定义一些条件。

2. 你认为数据集中哪些变量高度相关？

3. 假设数据集包含一个只有少数缺失值的数值变量。是否可以用数值替换这些缺失值？你会使用什么值？

4. 解释偏差的含义以及为什么需要避免它。

5. 还有哪些类型的图表可以用于初步数据分析？在给定的数据集中尝试一些。

进一步阅读

• 《数据清洗最佳实践：收集数据前后的完整指南》，第一版，作者 Jason W. Osborn

• 《使用 Microsoft Excel 2013 和基于 Web 的工具的数据可视化技术入门》，作者 Tufts Data Lab

• 《分析多类别不平衡数据集的分类：预处理和成本敏感学习的二值化技术和临时方法》，作者 A. Fernández, V. López, M. Galar, M.J. del Jesus 和 F. Herrera

第五章：变量之间的相关性及其重要性

变量之间的相关性通常意味着一个变量的变化会反映在另一个变量上。但这并不意味着一个变量的变化是由相关变量的变化引起的。例如，产品的销售价格与制造成本相关，但价格的增加并不完全由它引起，因为还有其他因素如运输和通货膨胀需要考虑。

并非数据集中每个变量或特征都对我们的分析有用，有时其中许多是冗余的。变量对之间的强相关性告诉我们哪些可以被丢弃，哪些对于预测或解释目标变量是重要的。

在 Excel 中可以执行不同的相关性计算，并用于确定输入特征的相对重要性。我们将在本章中展示其中的一些，以及图形方法。

本章所使用的数据集是从 Carnegie Mellon 大学维护的 StatLib 库中获取的，它将汽车的不同变量与其燃油消耗相关联。

本章我们将涵盖以下主题：

• 建立散点图

• 计算协方差

• 计算皮尔逊相关系数

• 研究斯皮尔曼相关系数

• 理解最小二乘法

• 专注于特征选择

技术要求

您需要从 GitHub 仓库github.com/PacktPublishing/Hands-On-Machine-Learning-with-Microsoft-Excel-2019/tree/master/Chapter05下载auto-mpg.xlsx文件[.]。

建立散点图

首先，加载auto-mpg.xlsx文件。我们将使用其中的数据来展示本章的不同方面。变量的含义在 Excel 文件及其参考中描述。

评估变量之间相关性的最简单方法是创建一个散点图，将所有特征成对展示。例如，如果我们把Cylinders变量放在x轴上，与y轴上的Displacement变量作图，我们会看到一个正相关（即，气缸数量越多，排量值越高）。这是可以预料的，因为发动机排量的计算（这里以立方英寸表示）与气缸数量呈线性相关。

散点图可以在以下图表中看到：

如果我们观察燃油消耗与汽车重量之间的关系，图表将类似于以下：

在这种情况下，汽车的重量和每加仑燃油行驶的英里数之间存在负相关性（也就是说，汽车越重，每加仑燃油能行驶的英里数就越少）。我们还注意到，这种相关性是非线性的，这意味着直线不能描述这些变量之间的关系。

那么，如果我们绘制两个不相关的变量会怎样呢？例如，发动机的气缸数和汽车制造的年份之间是否存在任何相关性？让我们看一下下面的图表：

在这里，我们注意到，在分析的时间段内，3或5气缸的汽车并不常见，因为它们的例子很少。78到80年似乎是5气缸发动机的时期，但除了这些事实之外，还有4、6和8气缸发动机在数据集中的每一年都在生产。这两个变量之间没有明显的相关性，其中一个变量不能给我们关于另一个变量的任何信息。

如果我们只有几个变量，这种在散点图中寻找相关性的方法是好的，但图表的数量需要快速增加。事实上，如果变量的数量是N[v]，那么查看所有相关性的组合数量如下：

N[v ] (N[v ]-1)*

即使像我们这样的小数据集，要包含 8 个数值变量，我们也需要 28 个图表来覆盖所有可能的组合。如果我们有数百个变量，那么通过肉眼找到相关变量的任务将变得简单不可能。在下一节中，我们将描述自动计算相关性的方法，使其能够处理大数据集和大量特征。

计算协方差

我们需要定义一种统计方法，定量地衡量两个特征之间的关联程度。两个变量的协方差正是这样做的，那么让我们看看它是如何计算的。如果有两个变量，x和y，我们首先将它们的值围绕它们的平均值进行中心化，和；然后，我们乘以新的值并取乘积的平均值：

这种定义意味着如果两个变量同时增加或减少，那么协方差是正的，而如果它们朝相反的方向移动，那么协方差是负的。如果没有相关性，协方差值将很小，即接近零。

从定义中也很清楚，由于变量保持了它们的尺度，比较具有非常不同平均值的特征很困难，比较两个协方差也是不可能的。

在 Excel 中，通过使用数据分析插件（我们将在附录中解释如何激活它）可以轻松计算协方差。

要计算协方差，请执行以下步骤：

1. 打开数据文件。

2. 导航到数据 | 数据分析。

3. 在弹出窗口中，选择协方差，如图下所示：

1. 选择数据范围；在这种情况下，它是除了最后一列之外的全部表格，最后一列包含汽车名称，是非数值的：

结果如下表所示：

我们可以看到位移和汽缸数之间存在正协方差值，而重量和mpg之间存在负值。由于我们之前解释过，比较这些值是不可能的，所以我们不能说更多。这里的大变化是我们同时计算了所有这些值，所以我们不需要逐个查看图表。矩阵是对称的，所以只显示了其中一半。

有一种方法可以量化相关性并比较它们，这是卡尔·皮尔逊在 19 世纪 80 年代开发的。让我们在下一节中更详细地探讨它。

计算皮尔逊相关系数

皮尔逊系数在比较两个变量时最常用，它通过测量它们之间的线性关系来工作。皮尔逊给出的原始定义如下：

分子与协方差成正比，分母是中心变量的标准差（σ）的乘积。这种归一化确保了ρ可能值的范围在-1和1之间。

我们可以通过重复在计算协方差部分中概述的步骤，通过在弹出窗口中选择相关性来在 Excel 中计算皮尔逊相关系数。

结果表如下所示：

包含值为1的单元格表示自身与每个变量之间的线性关系。负相关性再次表明一个变量增加而另一个变量减少，而正相关性表明两个变量在同一方向上变化。

皮尔逊系数非常适合比较特征关系。例如，我们可以看到汽缸数和位移比重量和mpg更线性相关（根据位移的定义，实际上），即使第二对中的那些相关。

皮尔逊系数的另一个定义如下：

在这里，b是最佳拟合线性回归的斜率，该回归拟合x与y，而σ[i]是x和y的标准差。这个定义清楚地表明，系数衡量关系的线性程度，同时衡量两个特征可以变化的程度。

那么，如果关系不是线性的会怎样？在下一节中，我们可以讨论另一个系数，它将帮助我们计算非线性相关性。

研究 Spearman 相关系数

要计算 Spearman 系数，我们首先需要为每个变量的值进行排名，即从最高到最低排序时的值顺序。一旦我们有了新的表格，我们将在其上计算皮尔逊的ρ。

在一个新的工作表中，我们在一个单元格中定义以下公式：

=RANK.AVG(Data!A2;auto_mpg[mpg])

在这里，我们要求 Excel 在单元格中写入与我们数据表中mpg列的第一个单元格相对应的排名，考虑到该列的全范围。我们将公式复制到右侧的单元格中，直到完成数据表列数（8 列）。即使你复制公式到额外的单元格，也不会影响结果——你只会得到一个错误信息，因为你超出了数据表的范围。以类似的方式，我们可以将公式复制到剩余的行中，直到我们到达第399行（数据表的垂直范围）。我们甚至可以使用以下公式为新列添加标题：

=CONCAT("Rank_";auto_mpg[[#Headers];[mpg]])

然后，我们将它复制到第一行的所有单元格中。

我们获得表格的一个样本如下：

因为horsepower缺失一些值，它们无法排名，因此显示为#N/A. 由于这些值很少，我们可以手动删除它们。这将避免在计算下一步的皮尔逊系数时出错，正如我们之前所做的那样；结果是如下：

我们得到与皮尔逊系数相似的结果，但在非线性但相关性强的情形下，它们会略高一些。

当相关系数单调递增或递减时，Spearman 系数才接近1。这可以在以下屏幕截图中更好地展示：

在第一个图表中，皮尔逊系数较高，因为关系可以通过直线调整，即使它不是最佳拟合。Spearman 系数为1，因为存在关系且它是单调递增的。第二个图表显示了具有突然变化的关系，两个系数都给出了较小的值。第三个图表显示了变量之间的二次关系，它既不是线性的也不是单调的。通过观察这三个例子，我们可以理解系数并不总是提供关于变量之间相关性的所有必要信息，但它们对于获得一个总体概念是有用的。

理解最小二乘法

在某些情况下，我们可能想要证明两个变量之间存在函数关系，因此只需在我们的模型中使用其中一个变量——因为另一个可以通过一个表达式轻松近似。在这种情况下，使用最小二乘法是有用的。给定一组点 (x[i],y[i]) 和一个函数，如 y'[i] = f(x[i])，这种方法最小化 y'[i] 和 y[i] 之间的差的平方。我们正在计算的优化的一般表达式如下：

我们将使用数据表中的两列，即“重量”和mpg：

1. 在新工作表中创建一个新的表格。

2. 复制“重量”和mpg列的值。

3. 按照重量值对行进行排序；结果表格如下：

1. 插入一个折线图来查看函数关系看起来像什么，如下所示：

假设我们假设 mpg = Aweight^(-b)* 并尝试找到常数 a 和 b。

1. 使用以下公式创建一个新的列，名为“预测”：

=$H\(2*POWER([@weight];\)H$3)

结果表格如下：

1. 为了填充表格，我们选择 a = 60（在单元格 H2 中）和 b = -0.5（在单元格 H3 中）的初始值。这些将是最小二乘法的起点。

2. 要最小化的量是误差平方和。为了计算它，我们创建一个新的列，名为“平方误差”，使用以下公式：

=([@mpg]-[@prediction])²

1. 然后，使用以下公式在一个单元格中对该列的所有值进行求和：

=SUM(Table9[Squared error])

1. 导航到“数据”|“求解器”；如果您看不到此选项，请参阅附录中如何激活求解器的说明。您将在屏幕上看到一个以下窗口弹出：

1. “设置目标”选项填写了我们计算平方误差和的单元格 ID，而“通过更改变量单元格”选项填写了包含 a 和 b 值的两个单元格的 ID。我们可以将其他参数保留为默认值设置。

2. 点击“求解”；如果回归收敛，你将看到以下窗口：

1. 选择“保持求解器解决方案”以用计算出的值替换 a 和 b，并获取所有预测的新值。

如果我们将实际值和预测值放在同一个图表中，你应该会看到以下截图类似的内容：

因此，数据点的调整函数大约为 mpg = 68564/重量。

由于 y 变量的大分散性，这种调整并不精确，但考虑到汽车重量，它可以作为燃油消耗的快速估算。

我们已经探索了多种寻找相关变量的方法。这有助于理解哪些是相关的，哪些是冗余的。下一节将解释如何使用这些知识来简化机器学习模型的输入。

专注于特征选择

如我们之前提到的，这些描述的方法本身都不能精确地告诉我们如何选择输入特征。确实，在某些特定情况下，如果相关性足够强，我们可以丢弃一个或多个特征，只保留那些通过相关性代表它们的特征。通常，特征工程是一个漫长且耗时的工作，在机器学习领域几乎成为了一个独立的研究领域。

存在自动技术来执行特征工程，这是通常被称为自动机器学习（AutoML）的一部分。该方法包括让计算机尝试不同的特征集，包括它们的组合，并测试结果，直到找到最佳集。尽管如此，没有通用的特征选择配方，每个问题都需要分析——特别是找到导致更好的模型训练和预测能力的特征集。

摘要

在本章中，我们描述了建立变量之间相关性的最广泛使用的方法，这些方法将后来用作机器学习模型中的特征。这是一个漫长且困难的任务，但它是良好预测模型的基础。

没有一种方法可以单独用来确定哪些特征是重要的，哪些可以被丢弃。结合方法以及对该数据集的深入了解是完成此任务的基础。

在下一章中，我们将结束初步任务，开始关注机器学习模型的实际应用案例。

问题

1. 哪种方法更适合寻找数值变量和分类变量之间的相关性？

2. 在一对变量之间构建一些其他图表，并研究它们之间的相关性及其背后的逻辑。

3. 负的皮尔逊系数值是否意味着其中一个变量具有负值？

4. 皮尔逊相关系数表可以通过着色或添加条形来更好地比较不同值。在快速分析 | 格式化中探索这些选项。

5. 最小二乘回归的质量通常通过 R²值来衡量。计算调整后的mpg列与weight数据值对应的函数的此值（提示：你只需要计算一个额外的值总和 - 参考文献以获取更多信息）。

6. 在上一个问题中计算出的值应该接近 0.7，这不足以证明函数很好地复制了数据。尝试不同的函数并查看结果。

进一步阅读

• 《统计学：温和的介绍》，由 Frederick L. Coolidge 撰写（参考第六章及其参考文献）

第三部分：分析和机器学习模型

模型是机器学习的核心。它们可以解释、预测、建议该做什么，以及学习困难任务或时间依赖行为。

本节包含以下章节：

• 第六章，Excel 中的数据挖掘模型实战示例

• 第七章，实现时间序列

第六章：Excel 中的数据挖掘模型实战示例

数据挖掘是关于在数据中寻找隐藏的模式和关联。如果数据量小，那么在合理的时间内只能由人类完成的大量分析，计算机可以在极短的时间内完成。在 Excel 2016 之前，可以安装一个名为数据挖掘的插件，它包含了许多可以用于获取洞察力和发现任何数据集中信息的方法和模型，通常作为黑盒使用。随后，微软改变了其政策，开始将这项功能迁移到云端，首先是 Office 365，最近则是 Azure。在 Excel 中，还可以使用内置函数甚至通过在Visual Basic 应用程序（VBA）中编写的宏来计算许多事情。在这里，我们将展示两个案例，说明数据挖掘如何帮助我们找到数据中隐藏的重要信息。

在本章中，我们将涵盖以下主题：

• 通过示例学习：市场篮子分析

• 通过示例学习：客户群体分析

市场篮子分析研究哪些产品/服务的组合经常被客户一起购买。客户群体分析分析在给定时间后仍然购买产品/服务的客户，以及哪些客户流失。

技术要求

为了完成本节，读者需要从 GitHub 仓库github.com/PacktPublishing/Hands-On-Machine-Learning-with-Microsoft-Excel-2019/tree/master/Chapter06下载transactions_by_dept.csv和cohort_input_data.csv文件。

通过示例学习 – 市场篮子分析

我们几乎在每一个在线商店都读过这句话：“购买此产品的顾客还购买了...”。这一切始于 20 世纪 90 年代的亚马逊，如今已经非常普遍。同样的原则甚至正在实体店中进行测试，顾客可以根据他们的购物历史和与其他产品的相似性获得个性化的购买建议。这些技术通常被称为交叉销售，它们很有用，因为通常向现有客户销售附加产品比获取新客户更容易。

在本节中，我们将展示市场篮子分析的简化示例，这是这些推荐系统背后的数据挖掘技术类型。结果可能不如更先进的方法准确，但仍然有助于解释该方法并教授如何提升它。

我们将使用从data.world/下载的商业交易列表。首先，按照常规方式加载transactions_by_dept.csv文件。导航到数据 | 从文本/CSV，并选择该文件。

您需要编辑 Power Query 中的表格，将第一列的数据类型更改为文本，因为它包含交易 ID，Excel 可能会将其解释为数字并截断。

结果表看起来像这样：

在这个特定的例子中，我们将只关注前两列。POS Txn包含交易 ID，而Dept显示交易发生的部门描述。我们的目标是找出同一个客户在一次交易中在不同部门购物的频率。

在我们的发现中，我们将看到明显的、无趣的组合；可以解释和利用的意外组合（例如，在相关部门购物时提供该部门销售的产品），以及我们无法解释的组合（可能偶然发生或需要进一步调查）。

为了开始我们的分析，我们需要做以下事情：

1. 按交易编号分组所有交易。

2. 构建一个包含每次特定购买中访问的所有部门的列表。

要执行这些任务，我们将使用 Power Query 功能，按照以下步骤进行：

1. 导航到数据 | 从表/范围。

2. 在继续之前，请确保POS Txn列的数据类型设置为文本。

3. 点击“分组”. 您将看到以下弹出窗口：

1. 选择按POS Txn分组，将新列的名称改为有意义的名称，例如Concat_dept（因为我们正在尝试将一次购买中访问的所有部门连接成一个字符串），并选择任何操作. 点击窗口右下角的“确定”按钮，类似于前面截图所示。

2. 手动更改计算公式以获取我们想要的数据转换。导航到视图并确保公式栏被选中。如果没有，请选中它，您将看到以下公式：

= Table.Group(#"Changed Type", {"POS Txn"}, {{"Concat_dept", each Table.RowCount(_), type number}})

以下截图显示了前面的公式：

1. 必须将公式替换为以下内容：

= Table.Group(#"Changed Type", {"POS Txn"}, {{"Concat_dept", each Text.Combine([Dept]," | "), type text}})

不同之处在于，我们不是在表中计数行（Table.RowCount），而是使用"|"作为分隔符进行连接（Text.Combine）。我们还更改了输出类型为text。输出应类似于以下内容：

对于每个交易 ID，我们现在有一个表示涉及部门的字符串列表。我们将使用这个表来计算部门的组合，但首先，我们将它转换为范围：

1. 在表格中的任何单元格上右键单击，然后转到 表格 | 转换为范围。

2. 将工作表重命名为Concat depts（使用相同的名称，以确保未来函数中的引用正确）。

在实际生活中，我们可能需要限制我们分析的时间段以减少所需的计算量并清理数据，排除异常交易（异常值）。在我们的案例中，我们将限制研究的组合数量。我们可以按对、三元组或更大的数字来选择部门。问题是组合的数量会随着部门数量的增加而迅速增加。实际上，这个数字可以按以下方式计算：

在这里，*m*是元素的总数，*n*是每个组合中的元素数量。这个计算考虑到了元素不能重复（与自己比较没有意义），并且顺序无关紧要。

我们将根据交易数量选出前 10 个部门，并使用 Excel 函数计算组合。为了做到这一点，请执行以下步骤：

1. 创建一个如图所示的 PivotTable：

1. 按交易数量排序行以获取前 10 名：

0982:SPIRITS
0973:CANDY
0962:BEVERAGES
0597:HEALTH AIDS
0983:WINE
0991:TOBACCO
0836:HOUSEHOLD CLEANING
0604:PERSONAL CARE
0603:BEAUTY CARE
0984:BEER

我们如何轻松构建两个部门的所有可能组合？如果我们正确定义单元格内容并使用 Excel 内置的复制功能，这很简单。

假设有 10 个部门列在单元格A1:A10中。然后按照以下步骤操作：

1. 创建两个新的列，你可以将其标记为X和Y。

2. 将列X的第一个单元格定义为*=$A$1*，将列Y的第一个单元格定义为*=A2.*

3. 选择这两个单元格并将它们复制到Y列显示空值之前。记住，*$*符号在复制时固定单元格值。

4. 你将得到一个如图所示的列表，包含所有可能的部门X-Y对：

1. 在此列表中的最后一个单元格之后，将X定义为*= $A$2*，将Y定义为*= A3*。

2. 重复复制操作。如果你继续复制，直到将X定义为*= $A$9*和Y定义为*= A10*，你将得到完整的组合列表。

3. 元素的总数应如下所示：

返回到我们的Concat depts工作表，我们需要计算每个对在相同交易（X和Y）中出现的交易数量以及每个部门出现的次数。我们将定义两个函数：

1. 在这里，我们假设连接后的部门名称位于Concat depts工作表的列B中（这就是为什么需要这样命名的原因；如果你理解了函数，你可以更改名称），列C和D分别包含X和Y列表。两个COUNTIF函数考虑到了部门名称可能以不同的顺序出现，如下面的公式所示：

X&Y = =COUNTIF('Concat depts'!$B\(2:\)B\(2065;"*"&C2&"*"&D2&"*")+COUNTIF('Concat depts'!\)B\(2:\)B$2065;""&D2&""&C2&"")*

1. 我们将复制以下公式，直到达到最后一个元素，即数字45：

X = COUNTIF('Concat depts'!$B\(2:\)B$2065;""&C2&"")

1. 我们将计算以下内容：

支持 = X&Y/N

自信 = X&Y/X

在这里，N是交易的总数。支持表示规则（部门组合）在数据中的频率，而置信度是对涉及X和Y部门同时进行的交易的条件概率的估计。将这两个列相加，我们得到以下截图所示的表格，显示了计算结果：

我们刚刚进行的计算是 Apriori 算法的基础，该算法由 R. Agrawal 和 R. Srikant 于 1994 年发表，用于在数据集中寻找频繁项集的布尔关联规则，之所以命名为 Apriori 算法，是因为它使用了频繁项集属性的先验知识。Apriori 算法在挖掘频繁项集和相关的关联规则方面也非常有用。通常，这个算法用于操作包含大量交易的数据库，例如顾客在超市购买的商品。它帮助顾客轻松地进行购买，并提高了商店的销售业绩。重要的是要注意，商品之间的相关性并不一定意味着因果关系；例如，购买一个商品会导致某人购买另一个商品。

在现实场景中，我们会选择支持度和置信度超过一定阈值的组合，并研究这些见解。除了明显的规则，例如在同一笔购买中在酒水和葡萄酒部门购物的规则之外，我们还会发现我们没有预料到的其他组合。主要目的是找到可操作的见解；也就是说，我们可以采取行动的见解。我们可以提供新产品，将它们组合在促销活动中，并在不同的地方进行广告。

有大量的可能性。重要的是，机器已经学会了隐藏的关联，并为我们提供了关于我们自己的业务的有用信息。极其重要的是，不仅机器学习专家是这些分析的一部分，而且参与业务的人也应该提供他们的看法，并利用他们的经验和知识将此信息转化为经济利益。

是否可以在 Excel 中完成全面分析？如果我们无法访问更高级的工具，我们应该限制自己使用玩具模型吗？当然不是！Excel 功能极其强大，能够用于高级分析，但我们需要知道如何在宏中编写一点 VBA 代码。

现在应该已经清楚市场篮子分析的概念了，我们可以继续到另一个客户行为分析的例子。

通过实例学习 - 客户群体分析

对一个公司的客户及其行为获得洞察的一个极好方式是进行分段分析。这些分段是具有相同特征的客户组，通常被称为 队列。它们的定义在很大程度上取决于我们正在处理的企业类型。

我们将分析的数据集包含客户 ID 列表、他们首次从我们这里购买东西的日期、他们离开我们的日期以及每月平均花费金额。在这个上下文中，“离开我们”是什么意思？这主要是一个定义问题。例如，如果我们指的是信用卡，我们可能会认为客户在取消卡片时离开，或者我们可能想更进一步，如果客户连续两三个月没有花费任何钱，我们认为他们已经离开了。

然后，我们的分组将基于开始日期。我们将研究在给定时间段内有多少客户 流失，或者放弃了我们。

客户终身价值（LTV）通常指的是客户在我们这里整个期间产生的毛利润。这两个分析一起帮助我们决定哪些客户我们必须保留，以及哪些行动在获取更多有价值客户（就每月支出和忠诚度而言）方面更有效。

分析包括以下步骤：

1. 读取 cohort_input_data.csv 输入文件，导航到数据 | 从文本/CSV，并选择它。

2. 如果我们选择 month-year 格式，两个日期列 Date in 和 Date out 会看起来更好，也更易于理解。选择这些列，右键单击它们，然后点击格式单元格。会出现一个弹出窗口，如下面的截图所示：

1. 添加两列：Cohort，在这种情况下等于 Date in，以及 Active months，我们可以按以下方式计算：

=DATEDIF(B2;C2;"m")

这假设列 B 包含 Data in 值，列 C 包含 Data out 值。

1. 将此公式复制到剩余的单元格中。按 Date in 排序的结果表格如下：

1. 将此工作表重命名为 Customer data 以便将来参考。

首先，我们将研究在给定队列的生命周期内我们保留客户的情况如何。每个组都会告诉我们哪些行动需要重复，哪些需要避免以防止客户流失。我们将使用 COUNTIFS Excel 函数来计算每个队列每月活跃客户的数量：

1. 创建一个包含 48 行的矩阵，从零到我们数据集中月份的最大差异（48 个月），以及每月一列，从 Feb-15 到 Jan-19。以下截图展示了此矩阵的一个示例：

1. 在表格末尾添加一行，使用以下公式计算每个队列中客户总数：

=COUNTIF('Customer data'!$E\(2:\)E$751;"="&C$1)

这是因为Customer data工作表的C列包含队列列表，而矩阵的第一行包含我们的矩阵中的队列列表。

1. 将单元格内容复制到表格右侧的所有单元格中，由于行是固定的，因此每个列的正确值都会被计算出来，如下截图所示：

1. 在矩阵的第一个单元格中，输入以下公式：

=COUNTIFS('Customer data'!$E\(2:\)E$751;"="&C\(1;'Customer data'!\)C\(2:\)C$751;">"&EOMONTH(C\(1;\)B1))/C$52

让我们详细看看这意味着什么：

• • 我们只计算如果C1（Feb-15，当前队列）的内容是Customer data工作表C列中队列列表的一部分。

  • 我们计算Date out（Customer data工作表的C列）是否大于当前队列加上B列中的月份数。我们使用EOMONTH函数来处理可能作为输入的完整日期，而不仅仅是月份和年份。请在 Excel 帮助中查看此函数的定义。

  • 我们最后将这个值除以客户总数，得到一个百分比。

1. 将公式复制到整个矩阵。

2. 将单元格格式化为百分比。

3. 使用三色刻度条件格式化单元格。结果如下截图所示：

例如，我们可以看到在 20 个月后，Feb-15获得的客户中只有 19%仍然存在。颜色刻度还显示，某些队列的流失速度比其他队列快。现在，业务专家需要评估在每个时间段采取了哪些行动，并重复或更改它们。

我们现在知道我们能够保留多少客户，但他们的价值是多少？他们的消费金额并不相同，因此我们需要将这个变量包含在分析中。按照以下步骤操作：

1. 创建一个类似的矩阵，包含月份和队列，但现在我们将使用不同的公式：

=SUMIFS('Customer data'!$D\(2:\)D\(751;'Customer data'!\)E\(2:\)E$751;"="&C\(1;'Customer data'!\)C\(2:\)C$751;">"&EOMONTH(C\(1;\)B1))

这个公式意味着，只有当以下两个条件都满足时，我们才会在Customer data工作表的D列上对值进行求和：

• • 如果总和值对应于C1队列

  • 如果Date out大于当前队列，即客户仍然在我们这里，那么：

1. 将单元格值复制到整个矩阵。

2. 以与之前类似的方式格式化表格，以得到一个彩色矩阵，如下截图所示：

在这种情况下，我们可以看到每个队列随时间变化的消费金额，这与原始客户数量略有不同。

摘要

在本章中，我们学习了两种数据挖掘技术：市场篮子分析和顾客群体分析。第一种技术基于顾客行为告诉我们店铺部门或产品之间的隐藏关系。第二种技术显示了顾客数量的时间演变，揭示了不同顾客细分市场或群体之间的差异。

有大量数据挖掘分析可以帮助我们深入挖掘数据集，找到隐藏信息，这对于我们理解业务和做出正确决策至关重要。

在下一章中，我们将详细研究一种特殊的数据集：时间序列。我们将看到它需要不同类型的建模和分析。

问题

1. 在市场篮子分析中，如何避免无法解释的随机关联？

2. 如果我们在某个店铺中找到两种产品或两个部门之间的相关性，这种相关性能否推广到其他地方？

3. 群体矩阵中的一些列从某个特定日期开始显示零。这在当前数据集的背景下意味着什么？

4. 群体矩阵的最后几行持续显示零。这在当前数据集的背景下意味着什么？

5. 随着时间的推移，你如何最大化顾客的消费金额？

进一步阅读

• 《数据库营销：分析和管理顾客》由 Robert C. Blattberg，Byung-Do Kim，Scott A Neslin 著

• 《数据科学商业应用：你需要了解的数据挖掘和数据分析思维，第 1 版》，由 Foster Provost，Tom Fawcett 著

第七章：实现时间序列

时间演变的现象对许多学科至关重要。了解发生了什么，预测不同变量将如何演变，是做出正确、明智决策的关键知识。

时间序列分析是一个广泛的领域，有许多不同的方法来检测模式、预测行为，并将时间演变分解为已知和先前研究过的形状。我们将讨论其中的一些，重点关注那些易于使用 Excel 解决的问题。通常的想法是，我们的机器学习数据中的细节，并从中提取关于过去和可能未来发展的有用知识。

在本章中，我们将涵盖以下主题：

• 建模和可视化时间序列

• 在 Excel 中自动预测时间序列

• 研究时间序列的平稳性

技术要求

为了完成本章，读者需要从 GitHub 仓库github.com/PacktPublishing/Hands-On-Machine-Learning-with-Microsoft-Excel-2019/tree/master/Chapter07下载AirPassengers_modified.csv文件。

建模和可视化时间序列

我们已经看到，进行初步数据分析和可视化数据集是任何机器学习项目的第一步。时间序列也不例外。因此，我们将首先探索时间序列，并了解其不同的特征。

在时间序列的情况下，初步分析意味着对其进行建模；也就是说，了解它是否具有周期性，是否显示出某种趋势（随时间增加或减少），或者它是否是平稳的（值的均值和方差随时间不变），以及其他一些指标。可视化在这个分析中起着基本的作用，因为许多时间序列的特征可以通过数据点的图形表示来推断，即使有数值方法可以计算它们。

让我们使用一个流行的数据集来说明时间序列的建模和可视化。AirPassengers_modified.csv文件是一个非常流行的数据集的简化版本，通常在教授时间序列分析时作为示例（来源：Box, G. E. P., Jenkins, G. M. and Reinsel, G. C. (1976) 时间序列分析、预测与控制。第三版。Holden-Day. Series G）。我们的版本包含 1949 年至 1961 年间通过飞机旅行的国际乘客数量（以千为单位），按月分组。通过常规方式（数据 | 从文本/CSV）加载文件后，我们可以在以下图表中可视化时间序列，其中我们可以看到乘客数量作为时间的函数：

我们首先注意到乘客数量随时间增加。然后，如果我们仔细观察序列中的峰值，似乎有一个 12 个月的重复模式。让我们用数据来证明这个观察结果。

获取趋势的最简单方法是用 Excel 内置的趋势计算功能。要使用它，请按照以下步骤操作：

1. 点击图表区域。

2. 勾选“趋势线”复选框。

3. 点击“更多选项...”，如以下截图所示：

第一个选择将显示图表中的线条，然后，在选择了“更多选项...”之后，我们可以勾选复选框以在图表上查看线条方程表达式。请注意，线性并不是趋势线的唯一选项，更复杂的回归分析也是可用的，如以下截图所示：

如图表上所示的结果方程如下：

乘客数 = 0.0873旅行日期 - 1472*

观察生成的图表，我们看到一条直线并不能正确地跟随序列的时间演变。一些部分大部分在直线上方，而其他部分在直线下方，如以下图表所示：

我们如何对数据集的一般行为进行建模？我们可以先意识到，我们可以将序列视为由三部分组成：

• 一个周期性部分，它每 12 个月重复一次（我们猜测这是由于峰值的周期性，我们将这个 12 个月周期称为季节）

• 一个增长部分，我们可以通过回归或平均序列来获得

• 一个噪声部分，我们基本上将其定义为一旦我们隔离前两部分后剩下的值

这个模型可以写成以下形式：

让我们通过使用移动平均来改进增长部分的计算。这可以通过 Excel 自动计算，但我们将手动进行，因为它很简单，我们可以确切地了解它是如何工作的：

1. 首先计算前 12 个月的平均乘客数：

=AVERAGE(B2:B13)

1. 在右侧的单元格中，计算标准差（我们将在下一节中使用它，当我们测试序列的平稳性时）：

=STDEV.S(B2:B13)

1. 将这两个计算复制到表格的末尾。

然后，我们就有前 12 个月的平均值和标准差，在每个单元格对中。在实践中，我们正在定义一个移动的 12 个月窗口，并滑动它通过数据。生成的表格看起来类似于以下截图：

前十二位显然是空的，因为我们至少需要 12 个值来开始平均。如果我们把所有内容都放在同一个图表中，我们会看到一个类似于以下图表的图形：

移动平均更好地遵循序列的细节，我们将用它作为increasing(TravelDate)的良好近似。注意，移动标准差也随时间增加，我们将在下一节中使用这个结果。

回到我们为时间序列建立的模式，我们可以写出：

这个比率是在Passengers列和之前计算的移动平均之间。由于平均值需要从某个地方开始，所以你会缺少前 12 个点，但这没关系。对于其余的行，你可以计算Passengers/Moving Average，这近似于Passengers/increasing(TravelDate)。

1. 添加一个额外的列来计算这个比率。

2. 建立一个新的图表来显示计算。你会看到以下类似的图表：

我们清楚地提取了增长部分，但我们仍然有振荡和噪声的混合。让我们通过重复前 12 个值来按顺序模拟periodic(TravelDate)。

1. 在新列中，根据需要复制粘贴前 12 个值以填充相同数量的单元格。这些值将给出以下图表：

最后，我们将计算noise(TravelDate)。我们再次将从我们的模型中计算这个值：

1. 在另一列中，使用前面的计算创建一个新的图表：

现在我们有了时间序列的完整模型，我们可以用它来预测未来的值！让我们一步一步来做：

1. 打开一个新的工作表。

2. 复制序列值并扩展时间范围至Dec-62。

3. 在C列中，复制periodic(TravelDate)值（两个 12 个值的相等序列中的 24 个值）。

4. 在D列中，复制对应于最后两年的噪声值。

5. 在E列的第 146 个单元格中，使用趋势线公式(=0.0873A146 - 1472)计算increasing(TravelDate)*。

6. 将公式向下复制到表格末尾。然后单元格B1如下：

=C146D146E146

然后将相同的计算向下复制。得到的表格如下：

1. 插入一个新的图表，包括原始和预测的两个数据序列。

2. 在以下图表中，你可以看到预测相当准确！（或者至少它遵循了历史数据；预测质量的唯一真正衡量标准是将它与那些日期的真实数据进行比较）：

我们已经遵循了必要的步骤，根据其特征对时间序列进行建模，并广泛使用计算和可视化。我们将在下一节中展示这也可以通过使用 Excel 的内置功能自动实现。

在 Excel 中自动预测时间序列

现在我们已经艰难地预测了时间序列，理解了每一步，我们可以用简单的方法来做。我们将使用 Excel 的内置函数来预测未来乘客数量。执行以下步骤：

1. 选择两个列，TravelDate和Passengers，对应于时间和乘客数量。

2. 在主菜单中导航到“数据”：

3. 选择预测工作表（参考以下截图）：

1. 将弹出一个窗口，显示预测预览，并允许我们通过点击“选项”来更改一些参数：

• • 预测结束：我们可以选择预测期的结束。默认情况下，Excel 预测三个季节之后（更多内容将在后面介绍）。

  • 预测开始：我们可以对预测期的开始做同样的处理。默认是我们时间序列的最后一点时间。

  • 置信区间：这定义为围绕每个预测值的范围，其中 95%的预测点将落在其中（假设预测点的正态分布）。

  • 季节性：季节是时间序列重复其模式（如果它是周期性的，当然）的时期。它可以手动添加或自动检测。

以下图表显示了包含可用选项的弹出窗口：

1. 时间范围和值范围是选定的列（在我们的案例中是TravelDate和Passenger）。

2. 点击“创建”，我们获得三个新列：Forecast、Lower Confidence Bounds (Passengers)和Upper Confidence Bounds (Passengers)：

我们还将看到以下图表，显示时间序列加上预测值：

如果我们在弹出窗口中选择“包含预测统计信息”，我们将得到以下表格：

这些值对应于 Excel 内置的FORECAST.ETS.STAT函数，该函数是预测算法内部使用的。这些值的含义如下：

• Alpha：这衡量了赋予数据点的权重。值越高意味着我们给予最近的数据点更高的权重。

• Beta：这衡量了赋予趋势的权重。值越高意味着我们给予最近趋势更高的权重。

• Gamma：这衡量了赋予季节的权重。值越高意味着我们给予最近季节周期更高的权重。

• 平均绝对缩放误差 (MASE)：这衡量了预测的准确性。

• 对称平均绝对百分比误差 (SMAPE)：这基于百分比误差来衡量准确性。

• 平均绝对百分比误差 (MAPE)：这基于百分比误差来衡量准确性。

• 均方根误差 (RMSE)：这衡量预测值和观察值之间的差异。

我们已经解释了如何使用 Excel 的内置功能来分析和预测时间序列。在下一节中，我们将关注时间序列平稳性的重要性。

研究时间序列的平稳性

大多数时间序列预测方法都依赖于序列是平稳的这一事实。这是有道理的，因为这增加了在未来重复某种行为的概率，并使预测变得更容易。

我们如何知道给定的时间序列是否平稳？有正式的统计方法来衡量这一点，但我们也可以查看序列的一些属性。在实践中，有三个主要的平稳性检查：

• 均值是恒定的（不依赖于时间）。

• 方差是恒定的。

• 元素 i 和 i+m 的协方差是恒定的。

在我们之前的例子中，在 建模和可视化时间序列 部分中，我们绘制了移动平均（均值）和方差。如果您重新访问该图表，您会看到它们都不是随时间恒定的，因此序列是非平稳的，我们必须对其进行建模才能预测值。

更正式的统计测试是 Dickey-Fuller 测试，这超出了本书的范围。这个测试不是由 Excel 自动完成的，但有许多插件可以执行它。手动执行它没有意义。

有两种方法可以去除季节性和趋势：

• 将序列分解为噪声、周期性和增长项的技术。

• 差分——即通过取值 i 和 (i+m) 之间的差值来创建一个新的序列。位置差， m 被称为滞后。

您现在已经看到了基于对时间序列特征详细理解的不同预测方法。当我们讨论在 第十章 中使用 Azure 机器学习模型时，我们将展示更多高级预测方法，例如 ARIMA。

可视化也是本章展示的分析过程中的一个关键要素。我们将在下一章中看到不同的可视化技术，重点关注它们的特定用例。

摘要

我们已经看到了分解时间序列并预测其未来值的逐步方法。这至少在一般术语上可以帮助我们预测不同过程的结局。时间序列可以图形化和数值化地研究，提取其特征，并使用它们来理解它们在未来的行为。我们还看到，这可以在 Excel 中自动完成，但存在将其作为黑盒使用而不理解完整预测方法的风险。存在更高级的技术，我们将在未来的章节中讨论它们。

下一章将向您展示如何在 Excel 中构建一些基本图表以及如何使用它们来深入了解您的数据集。

问题

1. 在我们的预测中，我们使用 Excel 生成的趋势函数来模拟时间序列的增长部分。我们也可以使用移动平均值。这该如何操作？尝试一下，并比较结果。

2. 修改季节性和置信区间的值，并研究预测图和参数如何变化。

3. 你如何计算时间序列中两个值的协方差？

4. 使方差与时间无关的可能方法是对时间序列值取对数。在航空乘客数据序列中尝试这种方法，并检查方差值。

进一步阅读

• 《时间序列分析与应用》，作者 R.H. Shumway 和 D.S. Stoffer

• 《时间序列建模与预测导论研究》，作者 Ratnadip Adhikari R. K. Agrawal

第四部分：数据可视化和高级机器学习

有许多类型的可视化方式是广为人知的，并且能够提供关于我们的数据集和结果的重要信息。每种类型的数据和分析都有其对应的可视化方式。

许多数据分析和机器学习项目从未通过概念验证阶段。将它们带入一个分析持续的生产环境是很重要的，从数据提取到可视化。

本节包括以下章节：

• 第八章，在图表、直方图和地图中可视化数据

• 第九章，人工神经网络

• 第十章，Azure 和 Excel - 云端机器学习

• 第十一章，机器学习的未来

第八章：在图表、直方图和地图中可视化数据

如果我们在谈论机器学习，为什么我们应该关心可视化？答案很简单：如果你不能向没有任何技术知识的人展示你所分析的内容以及你模型的输出结果，那么你就无法展示任何附加值。我们已经展示了数据可视化对于理解数据集和决定哪些特征将对我们训练模型最有用的重要性。我们现在将要研究哪种类型的图表最适合讲述我们的数据以及我们从其中获得的新信息的故事。

本章将涵盖以下主题：

• 展示变量之间基本比较和关系

• 使用直方图构建数据分布

• 在地图上表示数据的地理分布

• 展示随时间变化的数据

技术要求

为了完成本章，读者需要从 GitHub 仓库github.com/PacktPublishing/Hands-On-Machine-Learning-with-Microsoft-Excel-2019/tree/master/Chapter08下载1976USpresident.xlsx和subte.xlsx文件。

展示变量之间基本比较和关系

在数据分析和机器学习的背景下，数据可视化极其重要。以下是一些原因：

• 讲述你的数据故事，并帮助决策者完成他们的工作。

• 预测某些变量未来的演变。

• 在数据中找到隐藏的趋势和模式。

• 找到异常值，即数据中的异常。

• 理解分布、组成和关系。

• 构建组和类别。

我们将展示用于展示不同类型数据的各种图表类型。示例图表中使用的数据如下：

Year	Sales	Cost	Profit	ROI
2015	23455	18294.9	5160.1	28.21%
2016	19226	12881.42	6344.58	49.25%
2017	34557	24881.04	9675.96	38.89%
2018	20134	14697.82	5436.18	36.99%
2019	22314	14057.82	8256.18	58.73%

还应考虑以下数据：

<td">20134

Year	SalesA	CostA	ProfitA	SalesB	CostB	ProfitB
2015	23455	18294.9	5160.1	23455	18294.9	5160.1
2016	19226	12881.42	6344.58	19226	12881.42	6344.58
2017	34557	24881.04	9675.96	34557	24881.04	9675.96
2018	20134	14697.82	5436.18	14697.82	5436.18
2019	22314	14057.82	8256.18	22314	14057.82	8256.18

将此数据输入 Excel 工作表，以便您可以在以下章节中创建图表。

Excel 图表的基本部分

Excel 图表有不同的部分，了解它们的名称很重要，这样我们就可以随意修改图表。以下图表详细展示了它们：

x 轴表示数据类别，y 轴表示数据系列。

在以下子节中展示的所有图表都可以通过选择数据范围并导航到插入 | 推荐图表来创建。在那里，我们可以看到预览并选择一个更好地讲述我们数据故事的图表。

柱状图

当你想在同一个图表中展示两个到四个不同的数据系列时，可以使用簇状柱状图。仅绘制一个系列没有意义，而超过四个可能会显得杂乱。看看以下示例：

在这种情况下，如果我们拆分数据，例如，显示更小的年份组并切换时间序列和类别（见以下图表），这肯定看起来更好：

柱状图中的系列应使用相同的单位表示，否则它们无法比较，或者比较可能会误导。

组合图表

如果我们需要展示用不同单位表示的数据系列，我们可以使用组合图表。基本区别是这些图表有两个具有不同测量单位的垂直轴。以下图表展示了此类示例：

在这种情况下，主要垂直轴（左侧）表示货币单位，次要垂直轴表示百分比。

堆积图表

当需要强调数据系列中相对值的重要性时，会使用此类图表。例如，成本 + 利润 = 销售额。我们可以看到，正如以下图表所示，一眼就能比较值。

此图表可以通过首先包括成本和利润变量，然后添加销售额来创建。

饼图和柱状图

饼图在比较多个系列时很有用。作为一个例子，我们将使用以下表格中列出的 2016 年美国俄勒冈州总统选举的结果：

候选人	选票
克林顿，希拉里	1,002,106
特朗普，唐纳德·J.	782,403
约翰逊，加里	94,231
其他	72,594
斯坦，吉尔	50,002

结果的饼图如下：

同样的结果可以用柱状图展示，而且比较选票数量的差异更容易。

通过在图表的任何部分右键单击，将图表类型更改为柱状图。

柱状图将看起来类似于以下：

我们现在知道如何为每种类型的数据选择最佳的图表。在下一节中，我们将研究在预览数据时使用直方图时最常用且最重要的图表之一。

使用直方图构建数据分布

我们在第五章“相关性和变量的重要性”中使用了直方图，但没有正式介绍它们。这种图表显示了数值或分类值的计数。为了显示数值数据，我们可以构建类别，就像我们对泰坦尼克号乘客的年龄所做的那样：

或者，我们也可以将年龄变量作为数字使用，并将值分布在箱子（落在相同数值范围内的数据点组）中：

上述直方图是按照以下步骤创建的：

1. 导航到插入 | 直方图。

2. 双击x轴以设置箱子的数量为15。

我们可以立即看到第一个箱子中有大量条目，对应于缺失的年龄值，我们将其定义为-1以方便识别。我们还注意到，大多数乘客年龄在 20 到 26 岁之间，分布并不对称；它似乎显示出向较老年龄的指数衰减和向较年轻年龄的更快下降。这可以通过考虑这样一个事实来解释，即当时在跨大西洋的船上旅行的小孩子并不多（至少在泰坦尼克号沉没的时候是这样）。

您还会注意到，直方图是发现异常值的一个极好的图形方法；也就是说，这些数据点不遵循与其他数据点相同的分布。在我们的当前例子中，缺失值也是异常值。

直方图的另一个有用应用是在比较大量项目时比较值。假设我们使用机器学习模型进行了预测。我们正在预测一个数值，我们希望将其与实际值进行比较以测试我们的模型。然后，对于每一组特征值，我们可以绘制实际值和预测值之间的差异。如果我们的模型是一个好的预测器，我们应该看到以下图表：

分布以0为中心，大部分是对称的，向右有一些较大的值。显然，这些都是不良的预测。

我们已经看到直方图是识别数据集不同特征的有价值工具。现在让我们集中讨论更复杂的数据表示。

在地图上表示数据的地理分布

在地图上定位信息对于理解空间维度上的数据非常有用，这通常通过其他方法很难实现。Excel 提供了不同的选项，我们将展示其中的一些。我们将首先使用包含地理坐标的数据，即纬度和经度。如今 GPS 系统的广泛应用使得获取此类信息变得容易。无论如何，如果此类精确信息不可用，我们将看到 Excel 内置的信息仍然可以使其简单绘制地图并显示有用信息。

在我们的第一个例子中，我们将使用关于 1976 年美国总统选举的麻省理工学院选举数据与科学实验室的数据。1976USpresident.xlsx文件包含其他信息，其中包括美国各州的列表以及每位总统候选人的得票数。我们希望将这些信息放入地图中，类似于新闻中展示的，共和党州用红色表示，民主党州用蓝色表示。

输入数据表如下：

从这张表中，我们需要提取每个州的获胜政党，即获得更多选票的那个政党。我们将使用 Power Query 及其分组功能。

1. 导航到“数据”|“来自表/范围”。

2. 打开 Power Query 窗口。你应该会看到以下截图类似的内容：

1. 选择“分组”|“高级”，然后选择“州”作为分组选项。

2. 然后，我们将添加一个名为“获胜者”的新列，其中我们将对 party 的值求和。这将产生一个错误，但将为我们获取获胜政党名称的基函数。

3. 我们将添加的第二列命名为“投票数”，其中我们选择 candidatevotes 的最大值。这将显示每个州的较大投票数。

以下截图显示了选择详细选项后窗口应该看起来像什么：

点击确定后，生成的表如下：

1. 为了修复错误并获取“获胜者”列中的政党名称，我们替换了以下函数：

= Table.Group(#"Changed Type", {"state"}, {{"Winner", each List.Sum([party]), type text}, {"Votes", each List.Max([candidatevotes]), type number}})

我们将替换前面的函数为以下内容：

= Table.Group(#"Changed Type", {"state"}, {{"Winner", each List.First([party]), type text}, {"Votes", each List.Max([candidatevotes]), type number}})

这将显示获胜政党，如下表所示：

1. 点击“关闭并加载”。

2. 使用生成的表创建地图。选择表中的任何单元格，然后导航到“插入”|“推荐图表”。第一个建议将是我们要的地图类型。

3. 点击确定。

4. 通过双击图例更改标题和颜色。生成的地图如下：

我们的第二个例子将使用来自阿根廷政府公共数据库（datos.gob.ar）的数据。表中列出了布宜诺斯艾利斯所有地下车站的经纬度、名称以及它们所属的线路。

布宜诺斯艾利斯地铁的昵称是Subte，这是subterráneo（地下）这个词的缩写，因此文件以此命名。

地理坐标在常规 Excel 地图中不接受作为输入，因此我们将展示 3D 地图功能，它还有其他优点。

执行以下步骤：

1. 加载 subte.xlsx 文件，你将看到以下表格（部分显示）：

1. 选择完整的数据范围。

2. 导航到插入 | 3D 地图。你应该会看到以下截图类似的内容：

如果图标变灰，请参考链接 support.office.com/en-ie/article/get-started-with-3d-maps-6b56a50d-3c3e-4a9e-a527-eea62a387030 以获取激活 3D 地图的逐步说明。

1. 点击如下截图所示的新游：

3D 地图窗口将打开，显示地球的默认视图，如下截图所示：

1. 在位置窗口中添加两个字段：Long 和 Lat。它们应该自动分配给相应的变量。如果不这样，从名称右侧的菜单中选择它们（见以下截图）。

2. 现在，地图应该以布宜诺斯艾利斯市为中心，但缩放可能仍然太远。

3. 通过鼠标滚动或使用 + 按钮放大。

4. 在类别字段中，添加线路，因为我们想区分不同的车站组。

5. 地图看起来将类似于以下截图：

让我们稍作改进。我们不希望用代表每个车站的大列，我们希望用小符号。为了实现这一点，请执行以下步骤：

1. 点击图层选项。

2. 将高度设置为 0%，并将厚度设置为 50% 到 70% 之间。

3. 你也可以为每条线（类别）更改颜色。在许多其他城市中，Subte 线路通过颜色来识别。A 是浅蓝色，B 是红色，C 是蓝色，D 是绿色，E 是紫色，H 是黄色（F 和 G 呢？它们，还有我，都还没有建成）。最终结果如下截图所示：

我们还可以做的最后一件事，因为 3D 地图可以交互使用，就是向数据卡（或通常在可视化中称为工具提示）添加有用的信息。为此，请执行以下步骤：

1. 点击自定义按钮（位于数据卡下方），你可以看到，默认情况下，迄今为止包含的三个变量都在那里（见以下示例截图）：

1. 通过点击红色 X 删除 Lat 和 Long。

2. 添加车站。

之后，如果我们把鼠标移到代表地下车站的任何符号上，我们会看到它的名称和它所属的线路，如下截图所示：

我们现在知道如何使用 Excel 使用地名或 GPS 坐标有效地表示地理数据。

在我们最后的例子中，我们将重新审视时间序列，展示不同的表示方式。

显示随时间变化的数据

在前面的章节中，我们详细分析了时间序列。我们主要使用折线图来表示数据的演变。时间演变能否以不同的方式表示？根据经验，当我们想要比较两个值时，线条可能会误导，而其他方法更好。

回到美国选举数据，假设我们想要比较一个州（例如，民主党）在几年内两个主要政党（民主党与共和党）的时间演变。执行以下步骤：

1. 在 Excel 中加载1976_2016USpresident.xlsx文件，你将看到与上一节中使用的表格相同的信息，但现在我们可以看到从 1976 年到 2016 年每个州每届选举的结果。

2. 随机选择一个州（以我们的案例为例，是加利福尼亚州）并尝试比较每个政党投票数随时间的变化。

3. 导航到数据 | 从表/范围。

4. 在 Power Query 窗口中，点击“选择列”。

5. 选择我们感兴趣的列：year、state、party、candidatevotes和totalvotes，如下截图所示：

1. 通过点击“添加列”|“自定义列”添加新列，计算按政党划分的投票百分比，即[candidatevotes]/[totalvotes]。

2. 将该列的类型更改为百分比（在“开始”|“数据类型”中）。结果类似于以下截图所示：

1. 筛选行，只留下state为California和party为republican或democrat。

我们需要以这种方式格式化表格，以便我们可以有意义地比较两个时间序列。为此，执行以下步骤：

1. 选择party列。

2. 导航到转换。

3. 点击“Pivot Column”。在点击“确定”之前，弹出窗口应该如下截图所示：

结果如下表所示：

我们现在将重复之前使用的一些步骤，所以我会快速解释一下：

1. 选择两个%列。

2. 导航到转换 | 替换值。

3. 将所有nulls转换为零。

4. 使用“开始”|“按组分组”按州分组。

5. 选择求和作为聚合。

6. 最后一步是创建另一列来计算民主党与共和党投票百分比与总数的差异：

100%- %republican -%democrat

这将给出投给其他政党的投票百分比。我们将使用以下表格创建图表：

1. 点击图表中的任何单元格。

2. 导航到插入 | 推荐图表。

3. 选择堆叠柱状图。

结果将类似于以下图表：

很明显，这种图表更适合比较各政党获得的投票数量和其他数据细节。例如，我们立即可以看到，在 1992 年，两个政党，尤其是共和党，都失去了对其他非传统政党的选票。

提示：在谷歌中搜索罗斯·佩罗特。

我们已经展示了除了折线图之外，还有其他方式来展示时间序列中包含的信息。现在你可以根据需要显示的信息类型尝试其他类型的图表。

摘要

我们已经讨论了 Excel 中不同类型的图表，可以用来比较变量并以有意义的方式展示数据，帮助我们从结果中提取价值。

我们现在可以回到纯机器学习模型，并跃进到高级神经网络的世界。

问题

1. 哪些类型的数据可以用图表表示？列出并考虑每种情况下最适合的图表。

2. 当你尝试使用饼图来展示五个或六个以上的数据系列时会发生什么？

3. 哪种类型的图表可以作为堆叠柱状图的良好替代品？

4. 尝试使用其他地理数据（例如，街道地址）来创建图表。

5. 你能使用美国总统选举数据来预测 2020 年的结果吗？原则上，应该可以预测时间序列的值。试一试，并思考预测的准确性以及可能的解释。

进一步阅读

• 《定量信息的视觉展示》，作者：爱德华·R·图费

• 《用数据讲故事：商业专业人士的数据可视化指南》，作者：科尔·努斯鲍默·克纳弗利克

第九章：人工神经网络

人工神经网络试图模仿人脑的工作方式。它们被用于解决一系列难题，例如理解书面或口头语言、识别图像中的物体，或驾驶汽车。

您将学习人工神经网络的基本工作原理，查看训练它所需的步骤和数学计算，并对复杂神经网络有一个总体了解。

本章节将涵盖以下主题：

• 介绍感知器——最简单的神经网络类型

• 构建深度网络

• 理解反向传播算法

技术要求

为了完成本章，您需要从 GitHub 仓库github.com/PacktPublishing/Hands-On-Machine-Learning-with-Microsoft-Excel-2019/tree/master/Chapter09下载transfusion.xlsx文件.

介绍感知器——最简单的神经网络类型

神经网络受到人脑的启发，更具体地说，是构成它的神经元细胞。实际上，自从第一个人工神经元被设计以来，神经科学已经取得了重大进展，因此最好说它们是受到几年前对大脑所知内容的启发。

感知器是构建人工神经网络的第一次尝试（弗兰克·罗森布拉特，1959 年）。它实际上是一个单神经元的模型，具有多个输入和一个输出。输出值是输入的加权总和，这些权重会迭代调整。这种简单的实现有许多缺点和限制，因此后来被多层感知器所取代。这种人工神经网络的最基本模型在以下图中显示：

输入层和输出层来自感知器，但现在增加了一个隐藏层节点。实际上，这一层的每个节点都充当一个神经元。为了理解每个神经元的输入和输出是如何工作的，以及信息是如何通过网络传递的，我们需要知道每个神经元构建的细节。人工神经元的示意图可以表示如下：

组合函数计算加权输入的总和作为结果输入，其中 w[i]是权重。激活函数使用这个输入来计算输出。输出范围通常限制在[0;1]之间，使用不同的函数。通常情况下，只有当输入值高于某个特定阈值时，神经元才会传递信号。

人工神经网络是如何学习的？使用一个训练数据集，其中输出是已知的。将输入值输入网络，将预测输出与实际输出进行比较，并在每一步迭代中调整 w[i] 权重。这意味着神经网络是一个监督学习模型。

问题越复杂，所需的训练样本数量就越大，以调整权重。我们还将看到，隐藏层和神经元的数量也会根据问题进行调整。调整这些参数是一个复杂的问题，几乎可以成为一个研究领域。

人工神经网络很有用，因为它们可以模拟任何数学函数。因此，即使输入值之间的关系未知，我们也可以使用网络来重现它并做出预测。

由于训练过程可能很复杂，并且在训练时调整的参数数量很大，因此通常很难理解为什么人工神经网络能够正确预测某个值。基于神经网络的智能模型的可解释性也是一个广泛研究的问题。

神经网络的某些应用如下：

• 图像分析——面部、物体、颜色、表情和手势

• 声音分析——声音、语音转文本和情感

• 文本分类——电子邮件垃圾邮件、文档内容中的欺诈和情感

• 硬件故障——预测性和/或诊断性

• 健康风险和/或诊断

• 客户或员工流失

让我们通过一个例子来看看实际中的训练是如何进行的。

训练神经网络

我们将使用来自台湾新竹市血液输血服务中心的公共数据集（由 Yeh, I-Cheng, Yang, King-Jang 和 Ting, Tao-Ming 编写的《使用伯努利序列在 RFM 模型上的知识发现》，发表于 2008 年的《Expert Systems with Applications》）。该数据集包含有关献血者的信息，总结为五个变量：

• R（最近性 - 自上次捐赠以来的月数）

• F（频率 - 总捐赠次数）

• M（货币 - 总捐赠血液量，单位为 cc）

• T（时间 - 自首次捐赠以来的月数）

• 一个二元变量，表示他们是否在 2007 年 3 月捐赠了血液（1 表示捐赠血液；0 表示未捐赠血液）

我们想证明人工神经网络如何从前面的四个特征中学习，并预测目标变量五。按照以下步骤重现并了解 transfusion.xlsx 文件中已显示的计算：

1. 将 transfusion.xlsx 文件加载到 Excel 中。

2. 在名为 transfusion* 的表格中，您将找到输入数据。它应该看起来像以下截图：

1. 由于数据没有按照任何特定的顺序呈现，我们可以使用前 500 个条目来训练神经网络。打开一个新的工作表并将其重命名为 training1（记住，我们正在重复创建文件中已经存在的工作表，以便你可以比较你的结果）。

2. 创建一组变量，就像你在下面的屏幕截图中所看到的那样。如果你使用相同的单元格，将更容易跟随下一步：

如果我们构建一个具有四个输入（输入数据中的四个特征）和一个包含两个神经元的隐藏层的艺术神经网络，我们需要八个权重参数：w[11]、w[12]、w[13] 和 w[14] 用于隐藏神经元一，以及 w[2][1]、w[21]、w[23] 和 w[24] 用于隐藏神经元二。其余参数将在稍后解释。

1. 从名为 transfusion 的电子表格中复制前 500 行数据（不包括标题）。

2. 在 training1 工作表中点击 B22 单元格。

3. 粘贴复制的单元格。

4. 你现在有一个包含输入值、称为 x[1]、x[2]、x[3]、[和] x[4]，以及输出二进制值 y 的表格。表格中的 # 列仅显示行号。

隐藏神经元 j 的组合函数是输入的加权和，如下公式所示：

在我们的例子中，N=4，这给我们带来了以下两个表达式：

1. 考虑到这些表达式，在 G22 单元格中写下以下公式：

=$E\(3*B22+\)E\(4*C22+\)E\(5*D22+\)E$6E22*

在 H22 单元格中写下以下内容：

=$E\(7*B22+\)E\(8*C22+\)E\(9*D22+\)E$10E22*

1. 将这些表达式复制到 G 列和 H 列的其余单元格中。最简单且最常用的激活函数是以下 Sigmoid 函数：

在我们的例子中，x 是针对每个隐藏神经元和每个用于训练的条目计算的组合函数：

1. 定义 I22 单元格为 =1/(1+EXP(-G22))，并将 J22 单元格定义为 =1/(1+EXP(-H22)).

2. 将这些公式复制到 I 列和 J 列的其余行中。最后的计算是神经网络输出，它是隐藏神经元输出的加权和，加上一个作为阈值的常数；如果总输入小于此值，输出为零且网络不激活。这可以用以下公式表示：

1. 然后，你可以在 K22 单元格中写下以下内容：

=$E\(11+\)E\(12*I22+\)E$13J22*

1. 将公式复制到 K 列的其余单元格。由于 E11、E12 和 E13 分别是我们为 theta 1、theta 2 和 theta 3 保存的单元格。我们在计算中使用了所有定义的权重和参数，但它们没有值。训练神经网络意味着找到这些参数的值，使得输出尽可能接近目标值，例如，对于 x[1]、x**[2]、x[3] 和 x[4] 的每一种组合，Output 的值与 y 的值之间的差异应该是可能的最小值。我们需要计算三个值：输出误差（Output-y）、平方误差（Error²）和平方误差的总和，这是需要最小化的值。

我们要最小化的函数，即平方误差的总和，只是可能损失函数之一。还有其他函数用于比较神经网络输出与训练值。在更高级的机器学习书籍中可以看到何时应用每个函数。

1. 将单元格 L22 定义为 =K22-F22.

2. 将公式复制到 L 列的其余行。

3. 将单元格 M22 定义为 =L22²。

4. 将公式复制到 M 列的其余行。

5. 将单元格 E15 定义为 =SUM(M22:M521)。这是平方误差的总和。

我们现在可以使用 Excel 的“求解器”设置 w[11]、w[12]、w[13]、w[14]、w[2][1]、w[21]、w[23]、w[24]、θ[o]、θ[1] 和 θ[2] 的值，同时最小化平方误差的总和：

1. 导航到“数据”。

2. 点击“求解”。

3. 填写以下截图所示的详细信息：

目标是 E15，其中我们存储平方误差的总和，以及变量单元格 E3 到 E13。

1. 点击“求解”。

2. 以下表格显示了最佳结果：

参数	值
w11	-3.915205816
w12	0.055009315
w13	0.016855755
w14	-0.301397506
w21	-0.016701972
w22	0.451221978
w23	-0.001645853
w24	-0.011395209
theta0	-0.349977457
theta1	0.247932886
theta2	1.256803829
平方误差	77.02669809

结果可能因求解器中使用的回归类型和初始值而异。梯度下降算法搜索（在 理解反向传播算法 部分中解释）可能会陷入一个局部最小值，其值大于全局最小值。

1. 将单元格 N22 定义为 =round(K22) 以将神经网络的输出转换为二进制值。

2. 比较预测值和线性值，你可以构建混淆矩阵：

		实际值
		1	0
预测值	1	32	22
	0	86	360

使用混淆矩阵来衡量神经网络训练的准确性。

测试神经网络

一旦你对训练结果满意，你可以使用获得的参数值来预测剩余数据的 y 值（这些数据在训练中从未使用过，然后可以用来测试网络输出）。

按照以下步骤使用测试数据集预测目标变量：

1. 复制名为 training1 的工作表。将新工作表命名为 test1。

2. 删除 B22:F521 的单元格范围。

3. 将名为 transfusion 的工作表中的最后 248 行复制到新的工作表中，从 B22 单元格开始。

4. 所有计算都应该正常工作，你应该能够看到使用测试数据作为输入的结果。

我们现在开发了一个简单的练习，展示了人工神经网络如何从输入数据中学习。我们进行的计算是反向传播算法的基础，该算法在本章的最后部分进行了详细解释。

构建深度网络

我们的简单人工神经网络示例非常简单，只包含一个隐藏层。我们能否添加更多层？当然可以！复杂性的下一步可能类似于以下图示：

我们添加了一个包含两个神经元的新的隐藏层，但我们也可以添加更多层，每层更多的神经元。网络的架构取决于我们赋予它的特定用途。多层人工神经网络通常被称为深度神经网络。

深度网络的输出计算类似于单层网络，考虑了每个神经元的所有输入、激活函数以及所有输入到输出神经元的加和。从先前的图中可以看出，网络中的每一层都受到前一层的影响。通常情况下，为了解决复杂问题，每一层都会学习一组特定的特征。例如，在识别图像时，第一层可能训练颜色，第二层训练形状，第三层训练物体，等等，随着我们向输出方向前进，复杂性逐渐增加。

随着我们在网络中添加更多的神经元，我们需要调整的参数也更多。在实践中如何做到这一点将在以下部分中变得清晰，其中将描述反向传播算法。

理解反向传播算法

深度神经网络训练过程中有两个阶段：前向传播和反向传播。我们已经详细了解了前向传播阶段：

1. 计算输入的加权总和：

1. 将激活函数应用于结果：

在章节末尾的建议阅读材料中找到不同的激活函数。Sigmoid 函数是最常见的，且易于使用，但并非唯一的选择。

1. 通过将最后一层的所有结果（N 个神经元）相加来计算输出：

在前向传播阶段之后，我们计算误差为输出和已知目标值之间的差异：误差 = (输出-y)²。

所有权重在正向传播阶段开始时都分配了随机值。

输出以及因此产生的误差是权重 w[i] 和 θ[i] 的函数。这意味着我们可以从误差出发，查看每个权重的微小变化如何影响结果。这在数学上表示为导数或梯度：

这个方程衡量了每次我们通过微小地改变 w[1] 而产生的误差变化。实际上，我们在每个神经元内部应用一个激活函数，因此误差的变化转化为以下方程（称为链式法则）：

我们希望改变所有权重值的方向，以减少误差。这就是为什么优化方法被称为梯度下降。如果我们想象误差是两个权重（当然，实际上不止两个，但我们人类很难想象超过三维的情况！）的函数，我们可以这样想象这个优化：

权重何时进行调整？有三种方法：

• 在线: 每次新的训练样本，所有权重都会重新计算。这非常耗时，如果数据集有太多异常值，可能会导致问题。

• 批量: 对整个训练数据集计算权重，计算累积误差并用来纠正它们。

• 随机: 使用批量模式，取训练数据的小样本。这加快了整个过程，并使方法对局部最优值更加稳健。

我们现在熟悉了人工神经网络是如何构建的以及它们的输出是如何计算的。随着网络规模的增大，通常不切实际进行这些计算，这在所有实际和有用的实现中经常发生。

摘要

我们已经研究了人工神经网络构建的基本原理以及它们如何从输入数据中学习。即使实际使用神经网络的方法与我们的示例不同，我们的方法对于理解细节和超越神经网络是神秘的黑盒子、神奇地解决问题的想法是有用的。

在下一章中，我们将看到如何使用 Azure 中可用的预构建机器学习模型，将它们连接到 Excel 来解决我们迄今为止提出的问题。

问题

1. 使用感知器测试的结果，构建混淆矩阵并评估预测的质量。

2. 在我们用人工神经网络解决的二分类问题中，遗漏了一个重要的步骤，如果我们实现它可能会改善结果。我们遗漏了什么？提示：构建一个表示 2007 年 3 月是否有献血行为的二进制变量的直方图。

进一步阅读

• 《神经网络简明介绍》 by David Kriesel*，可在www.dkriesel.com/_media/science/neuronalenetze-en-zeta2-1col-dkrieselcom.pdf在线获取

• 《神经网络与深度学习》 by Michael A. Nielsen*，可在neuralnetworksanddeeplearning.com/在线获取

• 《深度学习：利用算法让机器思考》，opensourceforu.com/2017/12/deep-learning-using-algorithms-to-make-machines-think/

第十章：Azure 和 Excel - 云端机器学习

当今的明显趋势是将所有分析、存储和可视化活动迁移到云端。在本章中，您将找到有关如何使用 Azure 服务和免费订阅以测试它们的信息。深度学习似乎是实现通用人工智能的途径，即能够像人类一样思考的机器。我们甚至还没有接近那个阶段，但人工神经网络被用于计算机视觉、文本和语音分析以及许多其他高级应用。Azure 中内置了许多人工智能用例，可以通过构建实验来使用，本章将详细说明。

本章将涵盖以下主题：

• 介绍 Azure 云

• 使用 Azure 机器学习工作室免费 - 步骤指南

• 将您的数据加载到 Azure 机器学习工作室

• 在 Azure 机器学习工作室中创建和运行实验

技术要求

要完成本章，您需要一个 Microsoft 账户。如果您没有，您可以在signup.live.com/免费获取一个。

您还需要从 GitHub 仓库github.com/PacktPublishing/Hands-On-Machine-Learning-with-Microsoft-Excel-2019/tree/master/Chapter10下载titanic_small.csv文件。

介绍 Azure 云

云计算是计算机系统资源（尤其是数据存储和计算能力）的按需可用性，用户无需直接主动管理。该术语通常用于描述通过互联网向许多用户开放的数据中心。以下是使用云而不是本地计算机的一些优点：

• 成本: 与购买和维护昂贵的硬件和软件相比，云模式仅按使用量付费。

• 速度: 通常只需在网站上配置一些设置，就可以在几分钟内获得大量资源。

• 全球规模: 资源的大小和位置可以根据用户需求动态更改。

• 生产力: IT 人员可以节省时间，专注于有助于业务增长的任务，而不是进行本地设备的实际维护。

• 性能: 云服务器经常升级，提供最新的可用技术。

• 安全: 尽管将敏感数据存储和传输到云端的担忧很常见，但大多数提供商都有足够的政策来保护用户数据。

在撰写本书时，最重要的云提供商是 AWS、MS-Azure 和 GCP。我们将重点关注 Azure，并描述一些与使用机器学习进行数据分析相关的服务。

就像所有云服务提供商一样，微软将所有类型的服务都包含在 Azure 中。我们的兴趣将主要在于Azure 机器学习工作室（AMLS），它旨在轻松创建和测试机器学习项目和实验。

使用 AMLS 免费版 – 步骤指南

微软 AMLS 是一个提供拖放界面的工具，用于构建、测试和部署机器学习模型和分析解决方案。可以将模型发布为 Web 服务，以便从 Excel（以及其他工具）中消费。

我们将首先在 AMLS 主页上注册，使用您的微软账户：

1. 打开studio.azureml.net/。您将看到以下首页：

1. 点击此处注册并进入下一页：

1. 选择第二个选项（即免费工作区），这要求您拥有微软账户。优点是此选项免费，并且您可以无限制地使用它。一旦您点击免费工作区选项，您将被带到以下登录界面：

1. 输入您创建微软账户时选择的用户名并点击下一步。然后您将看到密码输入界面：

1. 输入您的微软账户密码并点击登录。

2. 您将被带到 AMLS 主页：

现在您已准备好开始，我们将介绍如何将您的数据集加载到 ALMS 中。

将您的数据加载到 AMLS 中

没有数据就没有机器学习项目，因此我们分析的第一步是将输入文件（titanic_small.csv）加载到 AMLS 中。这是泰坦尼克数据集的简化版本，包含三个特征和一个目标变量：

• 特征：

  • 乘客等级：乘客所乘坐的等级（值 1、2 或 3 分别对应头等舱、二等舱和三等舱）

  • 性别：乘客的性别（女性或男性）

  • 年龄组：婴儿、儿童、青少年、成人、老年或未知

• 目标变量：

  • 幸存：如果乘客在船难中幸存，则为 1，如果没有，则为 0。

要加载文件，请按照以下步骤操作：

1. 从主页点击 DATASETS。您将看到一个空的数据集列表：

1. 点击+NEW 获取上传本地数据文件的链接：

1. 点击从本地文件，您将看到以下对话框：

1. 点击选择文件，并在您的计算机中导航到输入文件（titanic_small.csv）的位置。

2. 在相应字段中输入您想要的数据集名称。

3. 选择文件类型，在本例中为带有标题的通用 CSV 文件 (.csv)。

4. 可选地，描述数据集的内容以供将来参考。

5. 点击确定。

6. 您将在列表中看到您最近上传的数据集：

你现在拥有开始构建你的第一个 AMLS 实验所需的一切。我们将在下一节中一起完成。

在 AMLS 中创建和运行实验

AMLS 的基本组件是实验。它们是通过将预定义的模块拖放到工作区中构建的。每个模块都有一些定义的任务，一些在运行时可以选择的参数数量，以及定义的输入和输出节点数量。以下是 AMLS 模块的截图：

它们可以连接起来构建分析工作流程，从数据输入和转换到机器学习模型训练和结果。我们将逐步进行，创建一个机器学习实验，训练一个决策树来预测泰坦尼克号乘客的生存情况。

创建新实验

按以下步骤创建新环境：

1. 从主页选择“实验”。你会看到一个空的实验列表：

1. 点击+NEW 创建一个新的实验。你会看到可以从 Azure 目录加载的可用实验列表：

1. 点击“空白实验”创建一个空的。以下截图显示了在添加模块之前实验的外观：

左边是模块目录，按功能分组。中间（灰色）区域是工作区，我们将在这里放置模块，右边显示了实验的摘要。在工作区的左上角，你可以看到实验名称。你可以点击它并编辑文本。在我们的例子中，文本被编辑以显示“泰坦尼克号 - 模型训练”。

一旦你熟悉了 AMLS，就可以探索目录中的实验。它们是 AMLS 能提供的能力和你可以用它解决的问题类型的良好示例。

这个第一个实验将仅使用输入数据来训练决策树，并使其准备好在另一个实验中使用，该实验将预测乘客的生存情况。我们将实验分为两部分，因为我们只想训练一次树，然后用于预测。我们将在下一节中逐步展示如何训练决策树模型。

训练决策树模型

1. 在模块目录中，转到“已保存数据集 | 我的数据集”，将“titanic_small.csv”拖放到包含我们的输入数据的工作区右侧模块中。它包含从文件中读取的数据。在放置数据模块后，你应该看到类似于以下截图的内容：

在工作区上放置模块后点击它，将在屏幕的右侧显示有关它的信息。

1. 拖放另外两个模块，它们代表我们将要训练的决策树和一个通用训练模块。第一个位于“机器学习 | 初始化模型 | 分类 | 双类增强决策树”。第二个位于“机器学习 | 训练 | 训练模型”。

2. 一旦所有模块都拖放到工作区中，您需要将它们连接起来以构建数据流。要连接两个模块，请执行以下操作：

   1. 点击第一个模块的输出节点。

   2. 按住鼠标按钮，将指针移动到第二个模块的输入节点。

   3. 释放鼠标按钮。您应该看到连接两个模块的曲线线。

3. 最终连接的模块应类似于以下截图：

您需要在标记为“训练模型”的模块中选择目标变量。为此，请执行以下步骤：

1. 点击模块。

2. 点击右侧面板上的“启动列选择器”。

3. 在弹出的窗口中，点击“按名称”。

4. 从可用的列列表中，点击“survived”。

5. 点击右箭头（>）。这将把“survived”变量移动到右边。您应该看到类似于以下截图的内容：

1. 在底部菜单中，点击“运行”：

1. 等待运行完成。应该只需几分钟您就会看到所有模块上出现绿色的波浪线。

您可以可视化您训练的模型。在这种情况下，默认情况下训练 100 个决策树。要详细了解它们，请按照以下步骤操作：

1. 右键单击“训练模型”模块。

2. 导航到“训练模型 | 可视化”：

1. 选择左侧以缩略图形式显示的任何树，以查看详细信息。您可以看到在每个节点上如何做出决策，并跟随分支值到目标变量的最终值。以下截图说明了这一点：

1. 点击标记为“双类增强决策树”的模块，将在右侧面板上显示一系列模型参数。这些参数会影响决策树的训练。您可以更改这些参数并再次运行实验：

1. 通过在底部菜单中点击“保存”来保存实验。

最后，我们将保存模型以供将来使用，无论何时我们想要预测给定乘客的生存机会。为此，请按照以下步骤操作：

1. 右键单击“训练模型”模块。

2. 导航到“训练模型 |另存为训练模型”：

现在，模型已使用输入数据集训练完毕，并准备好在新实验中使用。我们将在下一个子节中构建这样一个实验，并展示如何从 Excel 工作簿中使用它。

使用模型从 Excel 进行预测

现在我们将使用训练好的决策树创建一个 Web 服务。Web 服务用于允许不同应用程序和不同源之间的通信，不依赖于操作系统或特定的编程语言。然后我们将使用 Excel 与这个 Web 服务通信，向它发送输入数据，并接收带有预测生存值的输出数据。

如果你仔细遵循了创建实验的说明，你现在应该能够自己完成，以下是一些一般性的指导：

1. 创建一个新的实验，并将其命名为“Titanic - Web 服务”。

2. 将五个模块拖放到工作区：

   • 已保存的数据集 | 我的数据集 | titanic_small.csv：我们加载并用于训练决策树的数据集。

   • 训练好的模型 | Titanic 模型 - 已训练：我们为将来使用而保存的训练好的模型。

   • 机器学习 | 分数 | 分数模型：将根据模型训练预测目标变量。

   • Web 服务 | Web 服务输入和 Web 服务输出：这将处理从 Excel 到 Web 服务的通信。

3. 将模块连接起来，使实验看起来像以下截图：

1. 保存实验并运行它。

输入数据实际上并没有在实验中使用，因为预测的数据最终将来自使用 Web 服务的应用程序。尽管如此，输入数据模块需要包含作为数据格式（变量名称和总列数）的参考。

1. 一旦运行成功，点击底部菜单中的“部署 Web 服务”。你将被带到以下屏幕：

Web 服务现在已创建，AMLS 让你可以下载一个已经连接到它的 Excel 文件。

1. 从屏幕最后一行的链接（批量执行，Excel 2013 或更高版本工作簿）下载文件。

2. 在你的电脑上打开 Excel 文件。你应该在工作簿右侧看到一个菜单：

这意味着文件已经连接到了我们创建的 Web 服务。

1. 点击“Titanic – Web 服务”。你将看到右侧的数据输入菜单：

1. 点击“使用示例数据”以获取几行数据用于预测。你现在有一个类似于以下表格的表格：

1. 在输入对话框中，写下A1:D6。

2. 在输出对话框中，写下你想要输出数据开始的单元格坐标。在这个例子中，我们使用了G1：

1. 点击“预测”。原始表格加上预测值将出现在工作表中：

由于“存活”列中的数据是目标变量，因此它不会被用于预测。它必须存在，以符合训练数据格式。

注意，在“评分标签”列中，五个预测值中有四个与用于训练的值一致。预测永远不会完美，这就是为什么精细调整模型参数和仔细研究预测误差如此重要的原因。

现在，你已经知道如何在 AMLS 中构建实验并与它从 Excel 中通信，发送和接收数据。所以，当你听到“云计算”这个词时，你就知道它是关于什么的。

摘要

我们已经完成了在 AMLS 中开设账户的所有必要步骤，AMLS 是微软 Azure 云的一部分，帮助我们构建简单的数据和数据分析流程。我们还构建了两个实验：其中一个训练决策树，另一个预测目标变量。然后我们学习了如何创建网络服务并将 Excel 连接到它，发送和接收数据。

在下一章中，我们将展示机器学习的当前状态，它几乎完全将操作转移到云端，使数据流完全自动化，并使用自动化来精细调整预测模型。

问题

1. 使用云计算的主要优势是什么？

2. 云计算只对机器学习有用吗？

3. 什么是网络服务，为什么它有用？

4. 如果模型已经训练好，为什么我们还需要在用于预测的分析流程中包含输入数据模型？

5. 为什么我们要将训练和预测分成两个不同的流程？

进一步阅读

查看以下资源，了解更多关于本章涵盖主题的信息：

• 理解云计算的基本知识：www.lucidchart.com/blog/cloud-computing-basics

• Azure Machine Learning Studio 文档：docs.microsoft.com/en-us/azure/machine-learning/studio/

• 简单易懂的决策树解释：medium.com/@chiragsehra42/decision-trees-explained-easily-28f23241248

第十一章：机器学习的未来

将数据分析迁移到云只是过去几年中机器学习项目变化的一部分。由于将自动化、人工智能（AI）和机器学习添加到许多不同的业务运营部分的好处现在已清晰，并且不需要进一步的证明，因此公司现在专注于更持久的解决方案。事实上，自然的后续步骤是考虑可以完成整个数据周期（从数据收集到可视化）的成品。

有许多创建数据分析流程的方法，这些流程可以在数据创建时消费数据，并在应用机器学习模型后返回结果和可视化。云服务使这项任务变得更加容易和高效。

自动机器学习是数据分析领域的当前趋势，其中可以在同一数据集上自动测试多个机器学习模型。模型参数被优化，直到找到最佳模型。这允许“公民数据科学家”这一概念的出现，这是一个利用大数据工具和技术分析数据并为公司创建数据和商业模型的角色。公民数据科学家不一定是数据科学或商业智能专家。这个角色被赋予那些能够使用大数据工具和技术创建数据模型的组织员工。

本章将涵盖以下主题：

• 自动数据分析流程

• 机器学习模型的再训练

• 自动机器学习

• 我们可以期待未来什么？

自动数据分析流程

在本书撰写几年之前，企业在接触机器学习时心中想着的是“让我们看看这东西能做什么……”。现在情况已不再如此。使用分析、机器学习模型、人工智能和高级可视化来理解、简化并预测许多不同情况的价值是显而易见的。这种价值可以用节省的钱、时间和努力来衡量，这有助于做出更好、更快的商业决策。

作为总结本书所学内容，我们可以列出数据分析流程的不同部分：

• 数据收集，通常来自多个不同的来源

• 数据清洗和准备，包括探索性可视化

• 选择适合我们数据的机器学习模型

• 使用历史数据训练模型（如果我们谈论监督学习）

• 挖掘数据中的隐藏或未知模式（如果我们谈论无监督学习）

• 测试模型预测的准确性

• 如果结果不满意，微调模型参数或更改模型

• 可视化结果

• 定期使用新数据重新训练模型

以下图示说明了这些步骤：

在以下小节中，我们将详细讨论每个步骤是如何自动化的。

数据收集

一旦确定了不同的数据源（本地文件和数据库），数据就可以定期上传到云存储服务。这通常是通过定期运行的过程自动完成的，需要最小程度的人工干预。主要云服务提供商提供了许多不同的存储选项。

这个概念在以下图中得到了说明：

下一步是为将数据输入模型做准备，即数据准备。

数据准备

完整的数据周期在以下图中展示：

在将数据导入云服务后，数据周期步骤如下：

1. 将数据集合并到一个单一表或一组连接的表中。这可能需要一些数据处理以将其转换为合适的格式。

2. 重建缺失数据，通过替换缺失值或决定丢弃不完整的条目。

3. 标准化数据的度量单位、小数精度和其他特征。

4. 标准化数据，特别是如果机器学习模型需要这样做的话。

5. 去重，即删除冗余条目。

6. 使用预定义的标准验证数据质量，并通过添加计算来丰富数据。

7. 将数据导出到下一个分析阶段，通常是机器学习模型。

所有云服务提供商都提供内置或第三方数据准备解决方案。一些替代方案包括 Amazon QuickSight、Azure 机器学习数据准备 SDK、Google Cloud 中的 Cloud Dataprep 以及许多其他选项。

模型训练

监督机器学习模型的训练周期可以总结如下：

准备好的数据被分为训练集和测试集。测试结果提供反馈以修改模型超参数和模型类型，并给出关于获得良好准确率所需数据量的提示。

最后一步是将模型部署以使其可用（例如，作为 Web 服务），同时也注册了特定训练运行的所有特征。模型的类型、所有超参数，甚至使用的数据都应作为元数据保存。

在云中提供了许多预构建的模型可供使用，以及注册和管理不同模型的方法。

无监督学习

每当我们不确定数据中会发现什么，或者需要处理大量无法手动管理的条目时，我们使用无监督机器学习。一个通用图示可能如下所示：

经过模型处理后，数据被排序并显示出可能对做出明智决策有用的模式。我们可以应用云中可用的所有模型，然后选择更适合我们知识和理解需求的模型。

可视化

数据流中的最后一步是可视化。在向非技术受众展示我们的结果时，强调我们分析的好处对于展示我们工作的价值至关重要。交互式仪表板是通常的做法，使用诸如Tableau、Power BI或QlikView等高级工具。一些示例可以在以下 URL 找到：www.clearpointstrategy.com/executive-dashboard-examples/。

每个仪表板工具都可以在线使用或连接到云端以展示分析结果。

机器学习模型的再训练

由于新数据始终可用且业务条件不断变化，机器学习模型需要定期再训练。云服务提供了一种最小干预的方法来做这件事，无需重建数据流中的任何部分。您只需加载新数据并指定您不是在构建一个新模型，而是在再训练一个现有模型。完成后，模型将像往常一样可供使用。

在本节中，我们展示了一个可以完全托管在云中的完整数据分析流程。本节也作为本书内容的详细总结很有用。下一节概述了机器学习的真正未来，届时编码和手动工作将减少到绝对最小。

自动化机器学习

在将机器学习应用于解决特定业务问题时，有几个任务对于模型的成功至关重要，例如：

• 数据预处理

• 特征工程

• 模型选择

• 模型超参数优化

• 模型结果的分析

这些任务通常由该领域的专家或多或少手动完成。近年来，对民主化机器学习的兴趣日益增长，允许非专家（有时被称为公民 数据 科学家）使用、改进并将机器学习应用于具体问题。自动化机器学习（AutoML）针对这一特定需求。

通常，新模型的构建过程可以描述如下图所示：

以下是构建新模型的过程：

• 输入数据经过预处理并用于构建最佳模型特征

• 根据用户完成的一些配置，构建并测试一组给定的模型

• 模型根据某些标准进行评估和测试

在开发、微调和评估模型的过程中，大部分工作都是自动完成的。结果是，根据输入数据和用户给出的条件，得到的是最佳可能的模型。

亚马逊、谷歌和微软都拥有 AutoML 功能，无论是使用专有代码还是外部包。存在其他解决方案，例如TPOT和H2O.ai。

机器学习的未来已经到来。无需编写代码，只需对工具集有最少了解，任何人都可以构建完整的数据分析流程。这既是优势也是风险：熟练的分析师可以更快地测试不同的选项并优化他们的工作；不熟练的人可能会使用如黑盒等解决方案，而不完全了解它们的工作原理，可能误解结果并做出错误决策。最终的责任属于任何商业工具的有兴趣的用户。

摘要

书的最后一章既被视为所有章节的总结，也视为一个窗口，展示了在 Excel 之外和未来的可能性。自动数据流和机器学习模型生成简化了分析师的工作并加快了决策过程。

希望你现在对机器学习有了广泛的了解，如何将其应用于每个业务线，以及这本书阅读之前所知的最高级替代方案。

问题

1. 监督学习和无监督学习在分析流程的哪个部分是不同的？

2. 为什么数据清洗是一个持续的过程？

3. 简要解释模型超参数是什么。

4. 哪些步骤可以通过 AutoML 自动执行？

进一步阅读

• Azure 机器学习服务文档: docs.microsoft.com/en-us/azure/machine-learning/service/

• AWS 上的机器学习: aws.amazon.com/machine-learning/

• 人工智能和机器学习产品: cloud.google.com/products/ai/

• 宣布 Azure 机器学习中的自动机器学习功能: azure.microsoft.com/en-us/blog/announcing-automated-ml-capability-in-azure-machine-learning/

• H2O.ai: www.h2o.ai/

• Cloud AutoMLB: cloud.google.com/automl/

• AUTOML: www.ml4aad.org/automl/

第十二章：评估

第一章，实现机器学习算法

1. 在传统的编程中，在计算机上开发和运行的代码是一系列逐步的指令，告诉计算机做什么以及如何处理不同的选项。机器学习是向计算机展示数据示例，要么通过示例教会它做什么，要么让它学习数据中隐藏的信息。

2. 机器学习模型可以是回归（如果目标变量是数值且连续）或分类（如果目标变量是分类或离散）。

3. 通过示例学习，在标记数据上训练的模型被称为监督机器学习模型。相比之下，那些在未标记数据中寻找信息的模型被称为无监督机器学习模型。

4. 创建和使用机器学习模型时需要遵循的主要步骤如下：

   1. 获取数据并合并不同的数据源

   2. 清洗数据

   3. 初步分析和特征工程

   4. 尝试不同的模型和每个模型的参数，使用完整数据集的一部分进行训练，并使用剩余部分进行测试

   5. 将模型部署，以便可以在连续的分析流程中使用，而不仅仅是进行小规模的、孤立的测试

   6. 预测新输入数据的值

5. R[j]残差是数据点 j 的 y 值与为该点拟合的回归线的值之间的差异：

平均绝对误差（MAE）定义为以下：

均方误差（MSE）定义为以下：

1. 欠拟合是指一个无法表示数据集特征的模型。即使这看起来是正确的，它完全缺乏泛化能力。过拟合是指一个与训练数据拟合得太好的模型，它缺乏泛化能力，并且在对不同的数据集进行测试时是错误的。

2. 需要将它们首先编码为二进制变量。

第二章，机器学习模型的实战示例

1. 编码将准备分类特征，以便将它们输入到机器学习模型中，并且不假设编码值之间有任何先前的相关性。

2. 通过设置树长度的限制或定义一个最小的熵值。

3. Temperature_hot均匀分布；两个值以Train_outside = yes 结尾，两个值以Train_outside = no 结尾。这代表了最大熵值，其中没有关于温度热时应该做什么的明确信息。

4. 在决定是否在户外训练时，会考虑以下IF语句：

   • 如果天气晴朗且无风，那么可以在户外训练。

   • 如果天气是晴朗且风大，那么不要在户外训练。

   • 如果天气是阴天，那么在户外训练。

   • 如果天气是雨天且湿度高，那么不要在户外训练。

   • 如果天气是雨天且湿度正常，那么就在外面训练。

应该清楚，这个图表只是一个示例，并不涵盖从数据表中可以得出的所有可能性。

1. 聚类的分布将取决于初始质心的选择。有更高级的构建聚类的方法，以避免这个问题。

2. 视觉聚类基于二维图表，它只显示两个变量之间的关系。数值分析考虑了数据中的所有维度。

第四章，从不同数据源导入数据到 Excel

1. 任何不会与文件内容混淆的字符。

2. 机器学习模型的输出将受到缺失或不正确数据条目的影响，并且应使用正确的格式。

3. 导入 Excel 文件将打开 Power Query 界面以预处理数据。

4. 以表格形式呈现的数据。

5. 可以在gist.github.com/gelisam/13d04ac5a54b577b2492785c1084281f找到详尽的列表。

6. 可以在stackoverflow.com/questions/38120895/database-vs-file-system-storage找到示例。

第四章，数据清洗和初步数据分析

1. 与手动构建决策树相比，深入研究 Azure Machine Learning Studio 中内置的示例可能会更有趣，该示例在第十章，Azure 和 Excel - 云端机器学习中展示过。

2. cabin和fare，pclass和fare，home.dest和fare是一些例子。

3. 缺失值可以用变量的平均值来替换。

4. 数据集中的任何不平衡都被称为偏差。这会影响任何机器学习模型的结果，因为模型将找到更多给定类别的示例或对特定目标值的某种倾向。

5. 例如，你可以尝试使用散点图查看变量之间的相关性。

第五章，相关性及变量的重要性

1. 例如，你可以在x轴上构建一个包含分类值的图表，在y轴上构建一个包含数值的图表；任何相关性都会从这个图表中清晰可见。

2. 应该很容易让读者构建图表并理解变量之间的关系。

3. 不。这意味着当一个变量增加时，另一个变量会减少。

4. 这种格式在第六章，Excel 中的数据挖掘模型实战示例中使用过。

5. 我们计算了平方误差（SSE）为([@mpg]-[@prediction])²。我们还需要计算的另一个和是SST = ([@mpg]-average([@prediction]))²。然后，我们计算R² = 1-SSE/SST。

6. 你可以尝试使用指数函数（EXP())或形状相似的其他函数。由于数据分散度非常高，R²值可能仍然远低于 1。

第六章，Excel 中的数据挖掘模型实战示例

1. 使用之前对业务的了解来排除这些关联。

2. 不一定。这类分析通常依赖于业务领域，甚至是我们执行它们的特定地点。这意味着某些结果可以推广，但通常并非所有结果都可以。

3. 这意味着没有客户在列出的时间开始购买产品，并且在行所示的时间段后继续购买。

4. 没有这样的老客户（从客户时间来看）。

5. 例如，关注那些停止购买的用户，并针对他们进行广告活动。

第七章，实现时间序列

1. 通过将increasing(TravelDate)设置为计算中的移动平均值，并遵循相同的步骤。

2. 如果季节性与数据中的真实值差异太大，那么预测的准确性将降低。如果我们增加置信区间，那么误差也会增加。

3. 使用 Excel 中的COVARIANCE.P函数。

4. 应用对数变换后，时间序列图将看起来像以下截图：

趋势仍在上升，但标准差看起来很平坦，并且不依赖于时间。

第八章，在图表、直方图和地图中可视化数据

1. 很难区分不同的饼图部分。

2. 多行图表。

3. 您可以从openaddresses.io/获取数据，并遵循本文中的说明：www.roguegeographer.com/create-your-own-maps-using-excel-3d-maps/。

4. 虽然可以这样做并得到结果，但准确性会很差。选举的结果主要取决于数据未考虑到的外部因素，而不是过去选举的历史结果。

第九章，人工神经网络

1. 结果将取决于人工神经网络训练。您可以在第一章的评估模型子节中找到逐步说明，实现机器学习算法。

2. 数据集不平衡，这会影响结果。

第十章，Azure 和 Excel - 云端机器学习

1. 成本、速度、全球规模、生产力、性能和安全。

2. 云计算对许多不同的应用程序都很有用，实际上可以取代所有在本地构建的东西，从数据库到可视化。

3. 网络服务是托管在互联网上的应用程序，可以通过预定义的协议和数据格式与其他应用程序进行通信。使用网络服务的优势在于它们易于共享，并且独立于操作系统和编程语言。

4. Azure 机器学习工作室需要输入数据格式，这来自输入数据模块。

5. 使用训练流程来训练模型并保存它。然后，在单独的流程中使用相同的模型进行预测，无需每次使用时都重新训练模型。

第十一章，机器学习的未来

1. 模型训练和测试被数据挖掘所取代，它通过尝试从数据中获取有用信息来工作。

2. 新数据持续包含到数据流中，并且在将其输入到机器学习模型之前必须完成整个循环。

3. 在开始学习过程之前设置超参数值，并定义模型的某些特性（例如，人工神经网络训练模型中的周期数）。

4. 以下步骤可以通过 AutoML 自动执行：

   • 数据预处理

   • 特征工程

   • 模型选择

   • 模型超参数优化

   • 模型结果分析