用 Python 为数据科学中的电子表格赋能

Excel，我的老朋友

我是那种只要能在电子表格里处理数据就会动手的人。上周，我的团队主管发来一份尚未更新项目清单的员工名单，我立刻打开文件，开始做表、按员工汇总统计，然后把按员工分解的汇总数据发给了他。这大概把他折腾坏了，但我也没办法；我热爱数据分析，也热爱 Excel。我从 2001 年起就一直使用 Excel，直到几个月前，它一直是我进行数据整理、分析和可视化的首选工具。它非常适合整理数据、执行计算与分析，甚至还能充当简单的数据库。我用它做过各种事情，从记录个人预算、支出、健身，到在军队里制作多页的人事力量分析报告。Excel 在那里的大多数情况下都能胜任，而且也是唯一的选择。由于安全原因，我无法使用 VBA、Python 或其他任何自动化方式。数据集的规模也足够小，它都能处理。

差不多一年前，我开始在 Cerner 的 Abilities Lab 担任系统工程师，负责软件性能测试。简单说，我的工作就是软件性能测试。我们会在新软件和更新版软件面向客户发布前进行测试，测试范围包括：Windows 与 Unix 的 CPU 和内存、Java 虚拟机性能、后端服务与用户界面的响应时间、数据库性能、对现有软件的影响、网络性能，以及几乎您能想到的一切。没有哪个测试只在单台机器上进行，大多数时候是 Windows 与 Unix（物理与虚拟）设备的组合，既有终端用户系统，也有服务器。在我参与的某个项目中，我们有超过 20 台系统需要收集数据。

根据项目持续时间不同，可能需要审核、分析与汇报的测试多达 50 个。所有这些数据都要汇总到一份最终的测试结果报告，并发布到内部的 Jira 项目页面，供软件开发团队查看。我们确实有一些内部定制工具能聚合大部分数据，但并非全部，而且这些工具生成的报告无法根据我所做的特定测试类型进行充分自定义。撰写和提交这些报告时，我的首选工具是 Excel——为什么不呢？我有扎实的 Excel 背景，而且当时我坚信我可以让它做任何事情。

Excel，我们遇到问题了……

我不打算详细描述将数据从内部工具导入 Excel 的繁琐流程，但即使对我这样非常熟练使用 Excel 的人来说，这也是一项要耗费数小时的工作。数据必须被复制、清洗、排序和筛选。无论我如何编写公式，它们几乎总要调整；随后还要根据项目中记录的测试数量、节点数量和各种响应时间来调整表格和图表。之后，还得通过查找替换确保测试名称正确，检查每个页面每一行的数字格式和高亮是否无误，以及做一般性的数据清理。我尝试过编写模板、宏和 VBA 脚本，但我所做的一切都不足以适配可变的测试数量、设备和性能指标——每次测试都要进行大量清理。我确实做出过一个能加速流程的模板，但要把一切弄到位，仍然需要半天时间。

回想起那些荒诞复杂的公式和 VBA 脚本，我还会忍不住发笑。情况在我们更新 Java 版本、内部工具无法正确处理日志时变得更糟。我们转用了一个名为 GC Easy 的外部工具，它能把日志解析成可加载到 Excel 的 JSON 文件。可以用 PowerQuery 加载这些 JSON 文件，但过程缓慢而繁琐；当您尝试解析 50 多个、每个超过百万行的 JSON 文件时，您很可能会频繁把 Excel 弄崩溃。再加上我需要一种能实现自动化的方式，却始终找不到好办法。即使能把数据加载进 Excel，图形可视化（比如随时间变化的堆大小堆叠面积图）也会卡死并引发更多崩溃。就算能解决这个问题，JSON 文件也只是开始。

手动复制、清洗和分析数据，必然会出错。我们都是人，都会疏漏、分心、误点。我的流程行不通，也不可能在不对整个团队进行深入 Excel 培训的情况下适用于所有人。也许是我的军旅背景，也许只是性格使然，我总在寻找改进流程、提升效率的方法，于是我开始琢磨如何精简整个流程。

当时我们的内部工具没有 API，也没有可用的导出功能。我无法不通过复制/粘贴就直接从中提取数据。我的下一个想法是直奔源头：测试中每台设备的日志文件。这意味着要搜索一个测试目录及其内部的多个子目录，从不同格式的多种文件类型中提取数据，完成所有计算，并以我需要的格式把结果写入 Excel，包括可视化和条件格式。

我需要解析的文件类型包括 .txt、.csv、.json（多种结构）、.xml（多种结构）、.dat、.html，还有部分数据需要从 Oracle 数据库中提取。我要考虑到目录结构的多样性、文件数量和结构的多样性、没有闭合标签的 .xml 文件，以及超过 10GB 的文件大小。流程还必须易于理解、尽可能自动化，并能处理数百万行数据。Excel 显然无法满足这些需求，所以我转向了流行的 BI 工具：PowerBI 和 Tableau。我不打算写 BI 工具测评，总之，它们完全不符合我的需求。

那么，有没有一种工具能做到 Excel 会做的一切，能在 Excel 中写报告，还能满足我列出的全部要求？

认识我的新朋友：Python 和 Pandas

如果您对 Excel 稍有了解，我相信您一定会喜欢 Python 的 pandas。pandas 将 Excel 的功能与 Python 语言的强大能力结合在一起。Excel 的列变成 pandas 的 Series，表格变成 DataFrame，复杂公式变成 Python 函数。pandas 能做 Excel 能做的一切：

读取数据

Excel 在读取平面文件方面表现不错，配合 PowerQuery，还能有限地查询数据库、读取部分 .xml 与 .json 文件。要是只看文档，感觉它几乎无所不能；但现实并非如此。界面笨拙、需要大量时间上手，没有好的自动化方式，数据清洗堪比看牙医一样“欢乐”。而 pandas 让读数变得轻而易举。

这个 Python 数据处理库可以原生读取多种来源的数据。完整列表请查看其文档页面。更棒的是，如果 pandas 不能原生读取，您很可能能找到某个 Python 包来读取，或者只用几行代码就能解决。想深入学习，请查看 DataCamp 的 Python 数据导入课程系列 等课程。

我用 pandas 读完了前面提到的每一种文件类型，包括那些没有闭合标签的 .xml 文件。借助一些有创意的自然语言处理，我还能从原始 .txt 文件中抓取所有测试元数据——Excel 做不到这点，因为这些文件并非为数据而格式化。我现在用的脚本版本不仅能读取我需要的所有文件类型，还能通过几行 Python 代码直接调用 API，从 GC Easy 自动获取 Java 垃圾回收的 .json 日志；这是 Excel 永远做不到的。和 Excel 比如何呢？使用 pandas，我可以搜索整个测试目录，只需几分钟就能把所有数据整整齐齐地取回到 DataFrame 中。它甚至能轻松处理超过 10GB 的日志文件。也许这不算太大，但用 Excel 读这种大小的文件，祝您好运。

我甚至已经开始从读取原始平面文件，逐步转向从多 TB 的 Oracle 数据库中提取数据。这个过程我没法和 Excel 比——除了短暂尝试过一次，我一直避免在 Excel 中做数据库查询。我记得那并不是想再经历一次的体验。而用 SQLAlchemy 通过 Python 连接则易如反掌。连接简单，查询速度把 Excel 甩出几条街。想了解入门内容，请查看 Python 关系型数据库入门。

清洗数据

抛开可视化不谈，我坚信数据应当呈现在干净、整洁、格式化且标注清晰的表格中。Pandas 提供了很多用于清洗数据和优雅呈现表格的好方法。官方 Pandas 网站上的 样式 页面是我最喜欢的 DataFrame 格式化指南。只需几行代码，您就能设置小数位数、格式化数字、添加行与条件格式，而且这个过程可以在无限多的文档中重复执行。能够在不受数据规模限制的情况下自动化数据清洗的部分环节，为我省下了无数时间。

公平地说，您当然也可以在 Excel 中清洗数据。它有大量内置工具就是为此而生，这也正是我每次（好不容易）把所需数据全部加载进去后不得不做的事。这主要是高度手工化的流程，鼠标点击过多，而且仍然错误频发、耗时很长。即便您对 Excel 的所有工具运用娴熟，错误也在所难免。确实有用公式、宏与 VBA 脚本来加速的方法（我稍后会谈），但这离完美差得很远。

分析数据

我就直说了：在数据分析方面，pandas 彻底碾压 Excel，两者无从公平比较。您可以在 Excel 中写公式、用一些内置函数做分析；我多年就是这么干的，也确实能做出结果。我还开过 Excel 数据分析课程，多次倡导使用它，并且上过以 Excel 为主要工具的统计课。我并不是说它不能用于分析。然而，把它和 pandas 相比，就像把纸笔和计算器相比；根本不是一个层级。

去年 8 月，Kaggle 报告称Python 在数据科学领域已超越 R；12 月，Quartz 称 pandas 为“数据科学中最重要的工具”。我想我不必在这个话题上再多说什么。

宏与 VBA

2012 年我还在现役，在得克萨斯州埃尔帕索的布利斯堡做例行牙科检查。刚坐上椅子，牙医说我需要拔智齿，正好他有时间，就当场只打了局麻给我做了手术。那是一次糟糕透顶的经历，让我疼了好几天。如果要在再经历一次这样的手术与使用 Excel 宏或 Visual Basic 之间二选一，我大概宁愿再拔几颗牙。

当然，有很多人在这些领域技艺高超，我也见过不少巧妙的 VBA 脚本成就的惊艳作品，我为此鼓掌。但我显然不是它的拥趸，我也不推荐任何人去用它，尤其是在您可以用 Python 的情况下。Python 作为 VBA 的替代方案如此受欢迎，以至于微软正考虑将Python 作为 Excel 的官方脚本语言。

但我还是需要用 Excel

尽管我很想把一切都用 Python 完成，但在我的场景下并不现实。就目前而言，我的分析结果仍需要以 Excel 的形式交付。幸运的是，有诸如 OpenPyXl 与 XlsxWriter 这样的包，正是为此而生。我每天都用 XlsxWriter 将 DataFrame 转换成非常专业的 Excel 报告，其中没有任何公式、宏或 VBA 脚本。它甚至还能做可视化。

说到数据可视化……

不再有无聊的图表

我从不是数据可视化的发烧友。我一直更喜欢在行与列中查看数据，对重点区域做些高亮，偶尔插个柱状图。Excel 在可视化方面提供的选项不少，但我掌握的也就是做堆叠面积图、柱状图和折线图。每次都是选中数据，点“插入图表”，找一个需要的类型。然后稍微做点筛选、加个标题，也就到此为止。

实话说，Excel 自定义图表的选项非常多，但流程很费劲，学习曲线也很陡。我至今不喜欢在 Excel 中做图，但用 Python 就完全不同了。

我现在坚信数据可视化的价值，并花了很多精力去设计有助于分析的优秀图形。当我在 DataCamp 上开始上课并接触到 Matplotlib 和 Bokeh 等包时，我开始理解数据可视化的真正威力和意义。借助 Matplotlib，可以直接在 pandas 中或通过导入 pyplot 来轻松创建可视化。pyplot 中可用的可视化类型多到难以尽述，而且图形的几乎所有方面都可以自定义。Python 数据可视化入门展示了如何用 Matplotlib 做出非常出色的图形。

尽管 Matplotlib 很强大，但我发现自己还需要更多的交互性。我希望能在一幅图中选择、缩放、悬停、排序和筛选数据，而且我当然不想去学 react.js 或 D3。学习 Python是一件让我至今仍然享受的事，但 JavaScript 并非我的强项。幸运的是，Python 再次出手相助，Bokeh 和 Dash 等包可以满足这些需求。

Bokeh 由 Anaconda 支持，结合了 D3.js 与 Python，能够制作完全交互的图形，并可导出为独立的 html 文档、嵌入网页，或在 Bokeh Server 上运行。虽然它的学习曲线比 Matplotlib 更陡，但我想您会同意，它生成的可视化非常值得投入；看看其图库就知道了。我现在在 Bokeh 上的功力还远未达到预期，但自从上了 用 Bokeh 进行交互式数据可视化的第一课起，它就成了我最喜欢的可视化包之一。如果 Bokeh 不合您意，Plotly 团队出品的 Dash 也是不错的选择。

Dash 是另一款免费的开源 Python 可视化工具，它采用 React.js 而不是 D3。我也是最近才发现 Dash，它同样能在无需学习一行 Javascript 的情况下，生成非常出色的交互式可视化。要比较 Dash 与 Bokeh 并不容易，因为两者都能做出很棒的图形。我目前对 Dash 唯一的顾虑是，目前我只能在 Dash 服务器上运行这些可视化，尚未能把它们嵌入独立文档、html 页面或 Jupyter 笔记本。尽管如此，它依然很有潜力，您可以在他们的图库中看到。

用调度与任务自动化节省时间

2014 年夏天，我所在的组织遇到了一个问题。预备役部队的运作由一组被称为“战备度”的数字所驱动。它表示某个单位中可以被召集部署的人员比例。这个数字由多类数据决定，包括医疗、牙科、教育、体能，以及一整套其他指标。我不能在这里给出具体数值，但我们的指标远达不到应有水平。

我的解决方案是一个多页的 Excel 报告，由若干表格和图表组成，信息来自多个人员数据库。内容包括组织层面的汇总数据、每位人员在各个衡量维度上的信息、以及基于本周正在进行的行动，对接下来三个月该指标走向的预测。这个方案的确非常成功，我们的“战备度”显著提升，并在很长一段时间里保持在较高水平。

不过，流程本身一团糟。我每周一早早到单位，手动从各个来源拉取信息，然后清洗数据并复制到本周的报告里。接着我至少要花一个小时检查报告是否有错并进一步清洗。之后还要把报告邮件发给行政人员，让他们核对数据，并补充那些尚未录入数据库的更新。平均下来，这份报告要花三天时间才能发出。人力成本既高又不必要。

只需写点 Python 脚本，我就能自动从各个来源拉取数据，把一切汇总进 pandas 的 DataFrame，整份报告写入 Excel，并自动发邮件给行政人员。最好的是，可以把它安排在周一凌晨零点零一分自动运行，省下海量工时。在 Linux 系统上，通过把任务加入 crontab，定时运行 Python 脚本易如反掌。在我目前的工作中，我们使用的系统太多了，crontab 是工作中不可或缺的一部分，掌握任务调度是一项必备技能。所以，如果您需要每晚零点拉取数据并写入报告，在 Linux 上用 crontab 就再合适不过。

我很喜欢 Linux，但我大部分工作是在 Windows 上完成的，它也是我偏好的操作系统。在 Windows 上，您有多种任务调度选项，比如 Windows 任务计划程序、Schtask.exe，或 Powershell。使用这些工具，您可以把脚本安排在系统资源最空闲的非高峰时段运行，或者设定在某个数据源更新后立即运行。这是我真心希望在几年前就能用上的选项。

我最喜欢的工具：Jupyter 笔记本

在用 Excel 多年之后，学习 Python 的感觉就像曾经只拿着一把钳子过日子，而某天忽然发现了一个装满闪亮新工具的完整工具箱。每个工具都很棒、各有用途，但若要选一个我用得最多的，那就是 Jupyter 笔记本。我用 Python 写的所有内容，要么直接写在 Jupyter 笔记本中供阅读，要么后来再转成独立的 Python 脚本。Jupyter 是编写函数、测试代码、进行探索性数据分析、甚至展示最终成果的绝佳工具。它甚至可以用来写博客（就像这篇）或配合 Github Pages。如果您浏览 DataCamp 的教程，会发现其中很多就是以同样的方式写成的。

为什么在讨论 Excel 时提到它？

Jupyter 笔记本可以充当您分析成果的呈现载体，替代 Excel。您可以在同一页里编写代码、展示 pandas DataFrame、可视化与结论，并且只需点一下按钮就能转换为 HTML。我的个人目标之一，是完全摆脱 Excel，改用 Jupyter 笔记本发送完成的报告。随着 JupyterLab 的发布，这一点变得更加可行。配合 widgets，您可以把 Jupyter 笔记本变成一个交互式数据分析应用。

结语

我仍然每天使用 Excel——它是一个很棒的工具，深入许多组织的日常工作，包括我所在的公司。几乎所有人都熟悉它，能用它来阅读信息并做简单的数据分析。但我认为，很多人并不知道，使用 Pandas 和 Python 进行同样的分析，不仅更高效，而且更容易。

我的目标是在今年年底前彻底告别 Excel，而我可以用本文提到的工具做到这一点。您不可能一夜之间成为专家，但 Excel 也同样如此。希望读完这篇文章，能激励您走出 Excel，尝试我提到的一些新工具。它们会让您更高效、更有成效，并最终加快您的工作流程。