数据清理指南（二）

于 2025-07-20 12:45:12 发布
阅读量617 收藏 4
点赞数 8
CC 4.0 BY-SA版权
分类专栏： 默认分类 文章标签： 默认分类
License CC BY-NC-SA 4.0 / 自豪地采用谷歌翻译
本文链接：https://blog.csdn.net/wizardforcel/article/details/149481424

原文：annas-archive.org/md5/9291c4c1871fca078a620d5e67720b6e

译者：飞龙

协议：CC BY-NC-SA 4.0

第五章：从网页收集和清理数据

最常见和有用的厨房工具之一是滤网，也叫筛子、漏网或中式过滤器，其目的是在烹饪过程中将固体与液体分开。在本章中，我们将为我们在网页上找到的数据构建滤网。我们将学习如何创建几种类型的程序，帮助我们找到并保留我们想要的数据，同时丢弃不需要的部分。

在本章中，我们将：

  • 理解两种想象 HTML 页面结构的方式，第一种是（a）作为我们可以查找模式的行集合，第二种是（b）作为包含节点的树形结构，我们可以识别并收集这些节点的值。

  • 尝试三种解析网页的方法，一种是使用逐行方法（基于正则表达式的 HTML 解析），另外两种使用树形结构方法（Python 的 BeautifulSoup 库和名为 Scraper 的 Chrome 浏览器工具）。

  • 在一些现实世界的数据上实现这三种技术；我们将练习从网页论坛中抓取日期和时间。

理解 HTML 页面结构

网页只是一个包含一些特殊标记元素（有时称为 HTML标签）的文本文件，目的是告诉网页浏览器页面应该如何在用户端显示。例如，如果我们想让某个特定的词语以强调的方式显示，我们可以将其用<em>标签包围，如下所示：

<em>非常重要</em>的是你必须遵循这些指令。

所有网页都有这些相同的特征；它们由文本组成，文本中可能包含标签。我们可以使用两种主要的思维模型从网页中提取数据。两种模型各有其有用的方面。在这一节中，我们将描述这两种结构模型，然后在下一节中，我们将使用三种不同的工具来提取数据。

逐行分隔符模型

在思考网页的最简单方式时，我们集中于这样一个事实：有很多 HTML 元素/标签被用来组织和显示网页内容。如果我们想从网页中提取有趣的数据，在这个简单的模型中，我们可以使用页面文本和嵌入的 HTML 元素本身作为分隔符。例如，在前面的例子中，我们可能决定收集所有<em>标签中的内容，或者我们可能想收集在<em>标签之前或</em>标签之后的所有内容。

在这个模型中，我们将网页视为一个大致无结构的文本集合，而 HTML 标签（或文本中的其他特征，如重复的词语）帮助提供结构，我们可以利用这些结构来界定我们想要的部分。一旦我们有了分隔符，就能从杂乱无章的内容中筛选出有趣的数据。

例如，下面是来自现实世界 HTML 页面的一段摘录，来自 Django IRC 频道的聊天日志。让我们考虑如何使用其 HTML 元素作为分隔符，提取有用的数据：

<div id="content">
<h2>Sep 13, 2014</h2>

<a href="/2014/sep/14/">← next day</a> Sep 13, 2014  <a href="/2014/sep/12/">previous day →</a>

<ul id="ll">
<li class="le" rel="petisnnake"><a href="#1574618" name="1574618">#</a> <span style="color:#b78a0f;8" class="username" rel="petisnnake">&lt;petisnnake&gt;</span> i didnt know that </li>
...
</ul>
...
</div>

以这个示例文本为例，我们可以使用<h2></h2>标签作为分隔符，提取出这个聊天记录的日期。我们可以使用<li></li>标签作为分隔符提取文本行，在该行内，我们可以看到rel=""可用于提取聊天者的用户名。最后，似乎从</span>结束到</li>开始的所有文本就是用户发送到聊天频道的实际消息行。

注意

这些聊天日志可以在线访问，网址是 Django IRC 日志网站，django-irc-logs.com。该网站还提供了一个关键词搜索接口，供用户查询日志。前述代码中的省略号（…）表示示例中已删除的部分文本。

从这段杂乱的文本中，我们可以使用分隔符的概念提取出三部分干净的数据（日志日期、用户和消息行）。

树形结构模型

另一种想象网页文本的方式是将其视为由 HTML 元素/标签组成的树形结构，每个元素与页面上的其他标签相互关联。每个标签都显示为一个节点，树形结构由页面中所有不同的节点组成。出现在另一个标签内的标签被视为子节点，而包围它的标签则是父节点。在之前的 IRC 聊天示例中，HTML 代码可以通过树形图表示，如下所示：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00273.jpeg

如果我们能够将 HTML 文本想象成树形结构，我们可以使用编程语言为我们构建树形结构。这使我们能够根据元素名称或元素在列表中的位置提取所需的文本值。例如：

  • 我们可能需要按名称获取标签的值（给我<h2>节点中的文本）

  • 我们可能需要某种特定类型的所有节点（给我所有在<div>中的<ul>里的<li>节点）

  • 我们可能需要某个元素的所有属性（给我所有<li>元素中的rel属性列表）

在本章的其余部分，我们将结合这两种思维模型——逐行处理和树形结构——并通过一些示例进行实践。我们将演示三种不同的方法，来提取和清理 HTML 页面中的数据。

方法一 – Python 和正则表达式

在本节中，我们将使用一种简单的方法，从 HTML 页面中提取我们想要的数据。这种方法基于识别页面中的分隔符，并通过正则表达式进行模式匹配来提取数据。

你可能还记得我们在第三章，清理数据的得力助手——电子表格和文本编辑器中，曾经尝试过一些正则表达式（regex），当时我们学习如何使用文本编辑器。在这一章中，某些概念将类似，不过这次我们将编写一个 Python 程序来找到匹配的文本并提取出来，而不是像在那一章中那样使用文本编辑器进行替换。

在我们开始示例之前，最后需要注意的是，虽然这种正则表达式（regex）方法相对容易理解，但根据你特定的项目，它也有一些限制，可能会影响效果。我们将在本节的最后详细描述这种方法的局限性。

第一步 —— 找到并保存一个用于实验的网页文件

在这个示例中，我们将从之前提到的 Django 项目中获取一个 IRC 聊天记录。这些文件是公开的，具有相当规则的结构，因此非常适合用于这个项目。前往 Django IRC 日志档案，django-irc-logs.com/，找到一个你感兴趣的日期。进入目标日期的页面并将其保存到你的工作目录。当你完成时，应该会得到一个 .html 文件。

第二步 —— 查看文件并决定值得提取的内容

由于我们在第二章，基础知识——格式、类型和编码中学到过，.html 文件实际上就是文本，而第三章，清理数据的得力助手——电子表格和文本编辑器，让我们非常熟悉如何在文本编辑器中查看文本文件，因此这一步应该很容易。只需在文本编辑器中打开 HTML 文件，查看它。有什么内容看起来适合提取？

当我查看文件时，我看到有几个我想要提取的内容。立刻我就看到，对于每条聊天评论，有行号、用户名和评论本身。我们计划从每一行聊天记录中提取这三个项目。

下图显示了我的文本编辑器中打开的 HTML 文件。由于某些行非常长，我已经启用了软换行（在 TextWrangler 中此选项位于菜单下的 查看 | 文本显示 | 软换行文本）。大约在第 29 行，我们看到了聊天记录列表的开始，每一行都包含我们感兴趣的三个项目：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00274.jpeg

我们的任务是找出每一行看起来相同的特征，以便我们可以预测性地从每一行聊天记录中提取出相同的三个项目。查看文本后，以下是我们可以遵循的一些可能规则，以准确地提取每个数据项并尽量减少调整：

  • 看起来我们要找的三个项目都位于<li>标签中，而这些<li>标签本身位于<ul id="ll">标签内。每个<li>表示一条聊天消息。

  • 在该消息中，行号出现在两个位置：它跟在字符串<a href="#后面，并且出现在name属性后的引号内。在示例文本中，第一个行号是1574618。

  • username属性出现在三个位置，首先是li class="le"的rel属性值。在span标签内，username属性再次作为rel属性的值出现，且它也出现在&lt;和&gt;符号之间。在示例文本中，第一个username是petisnnake。

  • 行消息出现在</span>标签后和</li>标签前。在示例中，第一个行消息是i didnt know that。

现在我们已经知道了如何找到数据项的规则，可以开始编写我们的程序了。

第三步——编写一个 Python 程序，提取有用的信息并将其保存到 CSV 文件中

下面是一个简短的代码，用于打开先前显示格式的 IRC 日志文件，解析出我们感兴趣的三个部分，并将它们打印到一个新的 CSV 文件中：

import re
import io

row = []

infile  = io.open('django13-sept-2014.html', 'r', encoding='utf8')
outfile = io.open('django13-sept-2014.csv', 'a+', encoding='utf8')
for line in infile:
    pattern = re.compile(ur'<li class=\"le\" rel=\"(.+?)\"><a href=\"#(.+?)\" name=\"(.+?)<\/span> (.+?)</li>', re.UNICODE)
    if pattern.search(line):
        username = pattern.search(line).group(1)
        linenum = pattern.search(line).group(2)
        message = pattern.search(line).group(4)
        row.append(linenum)
        row.append(username)
        row.append(message)
        outfile.write(', '.join(row))
        outfile.write(u'\n')
        row = []
infile.close()

这段代码中最棘手的部分是pattern这一行。该行构建了模式匹配，文件中的每一行都将与之进行比较。

提示

保持警惕。每当网站开发者更改页面中的 HTML 时，我们就有可能面临构造的正则表达式模式失效的风险。事实上，在编写这本书的几个月里，页面的 HTML 至少更改过一次！

每个匹配的目标组看起来像这样：.+?。总共有五个匹配组。其中三个是我们感兴趣的项目（username、linenum和message），而其他两个组只是无关的内容，我们可以丢弃。我们还会丢弃网页中其他部分的内容，因为它们根本不符合我们的模式。我们的程序就像一个筛子，只有三个有效的孔。好东西会通过这些孔流出，而无关的内容则被留下。

第四步——查看文件并确保其内容干净

当我们在文本编辑器中打开新的 CSV 文件时，可以看到前几行现在像这样：

1574618, petisnnake, i didnt know that 
1574619, dshap, FunkyBob: ahh, hmm, i wonder if there's a way to do it in my QuerySet subclass so i'm not creating a new manager subclass *only* for get_queryset to do the intiial filtering 
1574620, petisnnake, haven used Django since 1.5

这看起来是一个很好的结果。你可能注意到，第三列的文本没有被包围在任何分隔符中。这可能会成为一个问题，因为我们已经使用逗号作为分隔符。如果第三列中有逗号怎么办？如果你担心这个问题，可以为第三列添加引号，或者使用制表符（tab）作为列的分隔符。为此，将第一行outfile.write()中的连接字符从逗号改为\t（制表符）。你也可以通过ltrim()函数修剪消息中的空格，去除任何多余的字符。

使用正则表达式解析 HTML 的局限性

这个正则表达式方法一开始看起来很简单，但它也有一些局限性。首先，对于新的数据清理者来说，设计和完善正则表达式可能会非常麻烦。你肯定需要计划花费大量时间进行调试，并且写下大量文档。为了帮助生成正则表达式，我强烈建议使用正则表达式测试工具，如 Pythex.org，或者直接使用你喜欢的搜索引擎找到一个。如果你使用的是 Python 语言，请确保你选择的是 Python 正则表达式测试工具。

接下来，你应该提前知道，正则表达式完全依赖于网页结构在未来保持不变。因此，如果你计划定期从一个网站收集数据，你今天写的正则表达式可能明天就不管用了。只有在网页布局没有变化的情况下，它们才会有效。即使在两个标签之间添加一个空格，也会导致整个正则表达式失败，而且调试起来非常困难。还要记住，大多数时候你无法控制网站的变化，因为你通常不是自己的网站在收集数据！

最后，有许多情况几乎不可能准确地编写正则表达式来匹配给定的 HTML 结构。正则表达式很强大，但并不完美或无懈可击。对于这个问题的幽默解读，我推荐你去看那个在 Stack Overflow 上被点赞超过 4000 次的著名回答：stackoverflow.com/questions/1732348/。在这个回答中，作者幽默地表达了许多程序员的挫败感，他们一遍又一遍地尝试解释为什么正则表达式并不是解析不规则且不断变化的 HTML 的完美解决方案。

方法二 - Python 和 BeautifulSoup

由于正则表达式存在一些局限性，我们在数据清理工具包中肯定还需要更多工具。这里，我们介绍如何使用基于解析树的 Python 库 BeautifulSoup 从 HTML 页面中提取数据。

第一步 - 找到并保存一个用于实验的文件

对于这一步，我们将使用与方法 1 相同的文件：来自 Django IRC 频道的文件。我们将搜索相同的三个项目。这样做将使得这两种方法之间的比较更加容易。

第二步 - 安装 BeautifulSoup

BeautifulSoup 目前是 4 版本。这个版本可以在 Python 2.7 和 Python 3 中使用。

注意

如果你使用的是 Enthought Canopy Python 环境，只需在 Canopy 终端中运行 pip install beautifulsoup4。

第三步 - 编写一个 Python 程序来提取数据

我们感兴趣的三个项位于一组 li 标签中，具体来说是那些 class="le" 的标签。在这个特定文件中没有其他 li 标签，但为了以防万一，我们还是要具体说明。以下是我们需要的项及其在解析树中的位置：

  • 我们可以从 li 标签下的 rel 属性中提取用户名。

  • 我们可以从 a 标签的 name 属性中获取 linenum 值。a 标签也是 li 标签内容中的第一个项。

    注意

    请记住，数组是从零开始的，所以我们需要请求项 0。

    在 BeautifulSoup 中，标签的内容是该标签在解析树中的下级项。一些其他包会将这些称为 子项。

  • 我们可以从 li 标签的第四个内容项中提取消息（引用为数组项 [3]）。我们还注意到每条消息前面都有一个空格，因此我们需要在保存数据之前去除它。

这里是与我们在解析树中想要的内容相对应的 Python 代码：

from bs4 import BeautifulSoup
import io

infile  = io.open('django13-sept-2014.html', 'r', encoding='utf8')
outfile = io.open('django13-sept-2014.csv', 'a+', encoding='utf8')
soup = BeautifulSoup(infile)

row = []
allLines = soup.findAll("li","le")
for line in allLines:
    username = line['rel']
    linenum = line.contents[0]['name']
    message = line.contents[3].lstrip()
    row.append(linenum)
    row.append(username)
    row.append(message)
    outfile.write(', '.join(row))
    outfile.write(u'\n')
    row = []
infile.close()

第四步 – 查看文件并确保其清洁

当我们在文本编辑器中打开新的 CSV 文件时，可以看到前几行现在与方法 1 中的内容完全相同：

1574618, petisnnake, i didnt know that 
1574619, dshap, FunkyBob: ahh, hmm, i wonder if there's a way to do it in my QuerySet subclass so i'm not creating a new manager subclass *only* for get_queryset to do the intiial filtering 
1574620, petisnnake, haven used Django since 1.5

就像使用正则表达式方法一样，如果你担心最后一列中嵌入的逗号，你可以将该列的文本用引号括起来，或者直接使用制表符来分隔列。

方法三 – Chrome Scraper

如果你真的不想编写程序来解析数据，也有几个基于浏览器的工具使用树形结构来帮助你识别和提取感兴趣的数据。我认为使用起来最简单、工作量最少的工具是一个名为 Scraper 的 Chrome 扩展，由名为 mnmldave（真名：Dave Heaton）的开发者创建。

第一步 – 安装 Scraper Chrome 扩展

如果你还没有安装 Chrome 浏览器，请下载并安装。确保你获得正确的 Scraper 扩展；有几个扩展的名字非常相似。我建议使用开发者自己提供的 GitHub 网站来下载该产品，网址是 mnmldave.github.io/scraper/。这样你可以确保使用正确的抓取工具，而不是通过 Chrome 商店搜索。从 mmldave.github.io/scraper 网站，点击链接从 Google 商店安装扩展并重启浏览器。

第二步 – 从网站收集数据

将浏览器指向我们之前用来获取其他两个网页数据提取实验数据的相同网页 URL，其中一个是 Django IRC 日志。我在这里使用的是 2014 年 9 月 13 日的日志作为示例和截图，所以我将访问 django-irc-logs.com/2014/sep/13/。

在我编写本文时，浏览器中的该页面显示如下：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00275.jpeg

我们对这份 IRC 日志中的三个项目感兴趣：

  • 行号（我们从前两个实验中知道，这部分链接位于**#**符号下方）

  • 用户名（位于**<和>**符号之间）

  • 实际的行信息

Scraper 允许我们依次高亮显示这三项内容，并将其值导出到 Google 电子表格中，之后我们可以将它们重新组合成一个单一的表格，并导出为 CSV 格式（或根据需要进行其他操作）。以下是操作步骤：

  1. 使用鼠标高亮你想要抓取的项目。

  2. 右键点击并从菜单中选择抓取相似项…。在以下示例中，我选择了用户名petisnnake作为我要让 Scraper 使用的目标：https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00276.jpeg

  3. 在选择抓取相似项后，工具会显示一个新窗口，列出页面上所有相似项目。以下截图显示了 Scraper 找到的所有用户名列表：https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00277.jpeg

    Scraper 根据一个示例用户名找到所有相似的项目。

  4. 窗口底部有一个标有**导出到 Google 文档…**的按钮。请注意，根据您的设置，您可能需要点击同意以允许 Scraper 访问 Google 文档。

步骤三 – 数据列的最终清理

一旦我们从页面上提取了所有数据元素并将它们存储在独立的 Google 文档中，我们将需要将它们合并为一个文件，并进行最后的清理。以下是提取后的行号示例，但在清理之前的样子：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00278.jpeg

我们对列A完全不感兴趣，也不关心前导的**#**符号。用户名和行信息数据类似——我们需要大部分内容，但我们想要去除一些符号，并将所有内容合并到一个 Google 电子表格中。

使用我们在第三章中介绍的查找和替换技巧，清理数据的得力工具 – 电子表格和文本编辑器（即去除**#、<和>**符号，并将行粘贴到单一表格中），我们最终得到一个干净的单一数据集，如下所示：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00279.jpeg

Scraper 是一个从网页中提取少量数据的好工具。它有一个方便的 Google 电子表格界面，如果你不想写程序来完成这个工作，它可以是一个快捷的解决方案。在下一部分，我们将处理一个更大的项目。这个项目可能足够复杂，需要我们将本章的一些概念融入到一个综合解决方案中。

示例项目 – 从电子邮件和网络论坛中提取数据

Django IRC 日志项目相当简单。它的设计目的是向你展示三种常用于从 HTML 页面中提取干净数据的技术之间的差异。我们提取的数据包括行号、用户名和 IRC 聊天消息，所有这些都很容易找到，几乎不需要额外的清理。在这个新的示例项目中，我们将考虑一个概念上类似的案例，但这将要求我们将数据提取的概念从 HTML 扩展到 Web 上的另外两种半结构化文本：托管在 Web 上的电子邮件消息和基于 Web 的讨论论坛。

项目的背景

最近，我在进行一项关于社交媒体如何用于提供软件技术支持的研究。具体来说，我在尝试发现，某些开发 API 和框架的软件开发组织是否应该将开发者的技术支持转移到 Stack Overflow，还是应该继续使用旧的媒体，如电子邮件和网络论坛。为了完成这项研究，我比较了（其中之一）开发者通过 Stack Overflow 获得 API 问题答案的时间与通过旧社交媒体（如网络论坛和电子邮件组）获得答案的时间。

在这个项目中，我们将处理这个问题的一个小部分。我们将下载两种表示旧社交媒体的原始数据：来自网络论坛的 HTML 文件和来自 Google Groups 的电子邮件消息。我们将编写 Python 代码来提取这些两个支持论坛中发送消息的日期和时间。然后我们将找出哪些消息是对其他消息的回复，并计算一些关于每条消息收到回复所花时间的基本统计数据。

提示

如果你想知道为什么我们在这个示例项目中没有提取 Stack Overflow 部分的数据，稍等一下，直到第九章，Stack Overflow 项目。整章内容将致力于创建和清理一个 Stack Overflow 数据库。

这个项目将分为两部分。在第一部分，我们将从 Google Groups 上托管的项目的电子邮件存档中提取数据；在第二部分，我们将从另一个项目的 HTML 文件中提取数据。

第一部分 – 清理 Google Groups 电子邮件中的数据

许多软件公司传统上使用电子邮件邮件列表或混合的电子邮件-网络论坛来为其产品提供技术支持。Google Groups 是这种服务的一个流行选择。用户可以通过电子邮件发送消息到小组，或者在 Web 浏览器中阅读和搜索消息。然而，一些公司已经不再通过 Google Groups 为开发者提供技术支持（包括 Google 自己的产品），而是转而使用 Stack Overflow。Google BigQuery 这样的数据库产品就是现在使用 Stack Overflow 的一个例子。

第一步 – 收集 Google Groups 消息

为了研究 BigQuery Google Group 中问题的响应时间，我首先创建了该组中所有帖子 URL 的列表。你可以在我的 GitHub 网站上找到我的完整 URL 列表：github.com/megansquire/stackpaper2015/blob/master/BigQueryGGurls.txt。

一旦我们有了目标 URL 列表，就可以编写一个 Python 程序下载所有存储在这些 URL 中的电子邮件，并将它们保存到磁盘。在下面的程序中，我的 URL 列表已保存为名为 GGurls.txt 的文件。time 库已经包含，所以我们可以在请求 Google Groups 服务器之间使用短暂的 sleep() 方法：

import urllib2
import time

with open('GGurls.txt', 'r') as f:
    urls = []
    for url in f:
        urls.append(url.strip())

currentFileNum = 1
for url in urls:
    print("Downloading: {0} Number: {1}".format(url, currentFileNum))
    time.sleep(2)
    htmlFile = urllib2.urlopen(url)
    urlFile = open("msg%d.txt" %currentFileNum,'wb')
    urlFile.write(htmlFile.read())
    urlFile.close()
    currentFileNum = currentFileNum +1

该程序最终将 667 个文件写入磁盘。

第二步 - 从 Google Groups 消息中提取数据

现在我们有 667 封电子邮件消息存储在不同的文件中。我们的任务是编写一个程序，一次读取这些文件并使用本章中的某种技术提取我们需要的信息。如果我们查看其中一封电子邮件消息，我们会看到许多 头部，它们存储关于电子邮件的信息，或者说是其 元数据。我们可以迅速看到标识我们需要的元数据元素的三个头部：

In-Reply-To: <ab71b72a-ef9b-4484-b0cc-a72ecb2a3b85@r9g2000yqd.googlegroups.com>
Date: Mon, 30 Apr 2012 10:33:18 -0700
Message-ID: <CA+qSDkQ4JB+Cn7HNjmtLOqqkbJnyBu=Z1Ocs5-dTe5cN9UEPyA@mail.gmail.com>

所有消息都有 Message-ID 和 Date，但是 In-Reply-To 头部只有在消息是对另一条消息的回复时才会出现。In-Reply-To 的值必须是另一条消息的 Message-ID 值。

以下代码展示了基于正则表达式的解决方案，用于提取 Date、Message-ID 和 In-Reply-To（如果有）值，并创建一些原始消息和回复消息的列表。然后，代码尝试计算消息与其回复之间的时间差：

import os
import re
import email.utils
import time
import datetime
import numpy

originals = {}
replies = {}
timelist = []

for filename in os.listdir(os.getcwd()):
    if filename.endswith(".txt"):
        f=open(filename, 'r')
        i=''
        m=''
        d=''
        for line in f:
            irt = re.search('(In\-Reply\-To: <)(.+?)@', line)    
            mid = re.search('(Message\-ID: <)(.+?)@', line)
            dt = re.search('(Date: )(.+?)\r', line)
            if irt: 
                i= irt.group(2) 
            if mid:
                m= mid.group(2)
            if dt:
                d= dt.group(2)
        f.close()
        if i and d:
            replies[i] = d
        if m and d:
            originals[m] = d

for (messageid, origdate) in originals.items():
    try:
        if replies[messageid]:
            replydate = replies[messageid]                
            try:
                parseddate = email.utils.parsedate(origdate)
                parsedreply = email.utils.parsedate(replydate)
            except:
                pass
            try:
                # this still creates some malformed (error) times
                timeddate = time.mktime(parseddate)
                timedreply = time.mktime(parsedreply)
            except:
                pass
            try:
                dtdate = datetime.datetime.fromtimestamp(timeddate)
                dtreply = datetime.datetime.fromtimestamp(timedreply)
            except:
                pass
            try:
                difference = dtreply - dtdate
                totalseconds = difference.total_seconds()
                timeinhours =  (difference.days*86400+difference.seconds)/3600
                # this is a hack to take care of negative times
                # I should probably handle this with timezones but alas
                if timeinhours > 1:
                    #print timeinhours
                    timelist.append(timeinhours)
            except:
                pass
    except:
        pass

print numpy.mean(timelist)
print numpy.std(timelist)
print numpy.median(timelist)

在这段代码中，初始的 for 循环遍历每一条消息并提取我们感兴趣的三项数据。（该程序不会将这些数据存储到单独的文件或磁盘上，但如果你需要，可以添加这个功能。）这部分代码还创建了两个重要的列表：

  • originals[] 是原始消息的列表。我们假设这些主要是成员提问的问题。

  • replies[] 是回复消息的列表。我们假设这些主要是回答其他消息中问题的回复。

第二个 for 循环处理原始消息列表中的每一条消息，执行以下操作：如果原始消息有回复，尝试计算该回复发送所花费的时间。然后我们会记录下回复时间的列表。

提取代码

对于本章，我们主要关注代码中的清理和提取部分，因此让我们仔细看看这些代码行。在这里，我们处理每一行电子邮件文件，寻找三个电子邮件头部：In-Reply-To、Message-ID 和 Date。我们使用正则表达式搜索和分组，就像我们在本章第一部分的方法中做的那样，来限定这些头部并轻松提取其后的值：

for line in f:
    irt = re.search('(In\-Reply\-To: <)(.+?)@', line) 
    mid = re.search('(Message\-ID: <)(.+?)@', line)
    dt = re.search('(Date: )(.+?)\r', line)
    if irt: 
        i = irt.group(2) 
    if mid:
        m = mid.group(2)
    if dt:
        d = dt.group(2)

为什么我们决定在这里使用正则表达式而不是基于树的解析器？主要有两个原因：

  1. 由于我们下载的电子邮件不是 HTML 格式，因此它们不能轻松地描述为具有父节点和子节点的树状结构。因此，基于解析树的解决方案（如 BeautifulSoup）不是最好的选择。

  2. 由于电子邮件头部是结构化且非常可预测的（尤其是我们在这里寻找的三个头部），因此使用正则表达式解决方案是可以接受的。

程序输出

该程序的输出是打印出三个数字，估算该 Google Group 上消息的回复时间的均值、标准差和中位数（单位：小时）。当我运行此代码时，我得到的结果如下：

178.911877395
876.102630872
18.0

这意味着，发布到 BigQuery Google Group 上的消息的中位响应时间大约为 18 小时。现在，让我们考虑如何从另一种来源提取类似的数据：网络论坛。你认为在网络论坛上回答问题的速度会更快、更慢，还是与 Google Group 相当？

第二部分 – 清理来自网络论坛的数据

我们将在这个项目中研究的网络论坛来自一家名为DocuSign的公司。他们也将开发者支持转移到了 Stack Overflow，但他们仍然保留着一个旧版的基于网页的开发者论坛存档。我在他们的网站上四处寻找，直到发现了如何下载那些旧论坛中的一些消息。这里展示的过程比 Google Groups 示例要复杂一些，但你将学到很多关于如何自动收集数据的知识。

步骤一 – 收集一些指向 HTML 文件的 RSS

DocuSign 开发者论坛上有成千上万的消息。我们希望能够获得所有这些消息或讨论帖子的 URL 列表，以便我们编写代码自动下载它们，并高效提取回复时间。

为此，首先我们需要获取所有讨论的 URL 列表。我发现 DocuSign 旧版 Dev-Zone 开发者网站的存档位于community.docusign.com/t5/DevZone-Archives-Read-Only/ct-p/dev_zone。

该网站在浏览器中的显示如下：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00280.jpeg

我们绝对不希望点击进入每一个论坛，再进入每条消息并手动保存。这会花费很长时间，而且极其无聊。有没有更好的方法？

DocuSign网站的帮助页面表明，可以下载一个真正简单的聚合（RSS）文件，显示每个论坛中最新的讨论和消息。我们可以使用这些 RSS 文件自动收集网站上许多讨论的 URL。我们感兴趣的 RSS 文件仅与开发者支持论坛相关（而不是公告或销售论坛）。这些 RSS 文件可以通过以下网址获得：

在您的浏览器中访问列表中的每个 URL（如果时间紧迫，可以只访问一个）。该文件是 RSS 格式，类似于带标签的半结构化文本，类似 HTML。将 RSS 保存为本地系统中的文件，并给每个文件加上.rss扩展名。完成此过程后，您最多应拥有七个 RSS 文件，每个文件对应前面展示的一个 URL。

每个 RSS 文件中都有描述论坛中所有讨论主题的元数据，其中包括我们在此阶段真正需要的数据：每个特定讨论主题的 URL。用文本编辑器打开其中一个 RSS 文件，您将能够看到我们感兴趣的 URL 示例。它看起来像这样，并且在文件中，您会看到每个讨论主题都有一个这样的 URL：

<guid>http://community.docusign.com/t5/Misc-Dev-Archive-READ-ONLY/Re-Custom-CheckBox-Tabs-not-marked-when-setting-value-to-quot-X/m-p/28884#M1674</guid>

现在，我们可以编写一个程序，循环遍历每个 RSS 文件，查找这些 URL，访问它们，然后提取我们感兴趣的回复时间。接下来的部分将这些步骤拆解成一系列更小的步骤，并展示一个完成整个任务的程序。

第二步 – 从 RSS 中提取 URL；收集并解析 HTML

在这一步，我们将编写一个程序，执行以下操作：

  1. 打开我们在第 1 步中保存的每个 RSS 文件。

  2. 每次看到<guid>和</guid>标签对时，提取其中的 URL 并将其添加到列表中。

  3. 对列表中的每个 URL，下载该位置上的 HTML 文件。

  4. 阅读该 HTML 文件，提取每条消息的原始发帖时间和回复时间。

  5. 计算发送回复所需的时间，并计算平均值、中位数和标准差，就像我们在第一部分做的那样。

以下是一些 Python 代码，用于处理所有这些步骤。我们将在代码列出的末尾详细介绍提取部分：

import os
import re
import urllib2
import datetime
import numpy

alllinks = []
timelist = []
for filename in os.listdir(os.getcwd()):
    if filename.endswith('.rss'):
        f = open(filename, 'r')
        linktext = ''
        linkurl = ''
        for line in f:
            # find the URLs for discussion threads
            linktext = re.search('(<guid>)(.+?)(<\/guid>)', line)    

            if linktext:
                linkurl= linktext.group(2)
                alllinks.append(linkurl)
        f.close()

mainmessage = ''
reply = ''
maindateobj = datetime.datetime.today()
replydateobj = datetime.datetime.today()
for item in alllinks:
    print "==="
    print "working on thread\n" + item
    response = urllib2.urlopen(item)
    html = response.read() 
    # this is the regex needed to match the timestamp 
    tuples = re.findall('lia-message-posted-on\">\s+<span class=\"local-date\">\\xe2\\x80\\x8e(.*?)<\/span>\s+<span class=\"local-time\">([\w:\sAM|PM]+)<\/span>', html)	
    mainmessage = tuples[0]
    if len(tuples) > 1:
        reply = tuples[1]
    if mainmessage:
        print "main: "
        maindateasstr = mainmessage[0] + " " + mainmessage[1]
        print maindateasstr
        maindateobj = datetime.datetime.strptime(maindateasstr, '%m-%d-%Y %I:%M %p')
    if reply:
        print "reply: "
        replydateasstr = reply[0] + " " + reply[1]
        print replydateasstr
        replydateobj = datetime.datetime.strptime(replydateasstr, '%m-%d-%Y %I:%M %p')

        # only calculate difference if there was a reply 
        difference = replydateobj - maindateobj
        totalseconds = difference.total_seconds()
        timeinhours =  (difference.days*86400+difference.seconds)/3600
        if timeinhours > 1:
            print timeinhours
            timelist.append(timeinhours)

print "when all is said and done, in hours:"
print numpy.mean(timelist)
print numpy.std(timelist)
print numpy.median(timelist)
程序状态

程序运行时，会打印出状态消息，以便我们知道它正在处理什么。每找到一个 RSS 源中的 URL，就会有一个这样的状态消息，内容如下：

===
working on thread
http://community.docusign.com/t5/Misc-Dev-Archive-READ-ONLY/Can-you-disable-the-Echosign-notification-in-Adobe-Reader/m-p/21473#M1156
main: 
06-21-2013 08:09 AM
reply: 
06-24-2013 10:34 AM
74

在此展示中，74 表示从线程中第一条消息的发布时间到第一条回复之间的小时数（大约三天，再加上两小时）。

程序输出

在程序结束时，它会打印出平均值、标准差和中位数回复时间（以小时为单位），就像第一部分程序为 Google Groups 所做的那样：

when all is said and done, in hours:
695.009009009
2506.66701108
20.0

看起来 DocuSign 论坛的回复时间比 Google Groups 略慢。它报告了 20 小时，而 Google Groups 是 18 小时，但至少两个数字在同一大致范围内。你的结果可能会有所不同，因为新消息一直在添加。

提取代码

由于我们主要关注数据提取，让我们仔细看看代码中发生这一过程的部分。以下是最相关的一行代码：

tuples = re.findall('lia-message-posted-on\">\s+<span class=\"local-date\">\\xe2\\x80\\x8e(.*?)<\/span>\s+<span class=\"local-time\">([\w:\sAM|PM]+)<\/span>', html)

就像我们之前的一些示例一样，这段代码也依赖于正则表达式来完成工作。然而，这个正则表达式相当混乱。也许我们应该用 BeautifulSoup 来写？让我们看一下我们试图匹配的原始 HTML，以便更好地理解这段代码的目的，以及是否应该采取不同的方式。以下是页面在浏览器中的截图，感兴趣的时间已在截图上做了标注：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00281.jpeg

那么底层 HTML 是什么样的呢？这正是我们程序需要能够解析的部分。原始消息的日期在 HTML 页面中打印了多个地方，但日期和时间组合只打印了一次，原始消息和回复各一次。以下是 HTML 的展示，显示了这些内容是如何呈现的（HTML 已被压缩并去除换行，以便更易查看）：

<p class="lia-message-dates lia-message-post-date lia-component-post-date-last-edited" class="lia-message-dates lia-message-post-date">
<span class="DateTime lia-message-posted-on lia-component-common-widget-date" class="DateTime lia-message-posted-on">
<span class="local-date">‎06-18-2013</span>
<span class="local-time">08:21 AM</span>

<p class="lia-message-dates lia-message-post-date lia-component-post-date-last-edited" class="lia-message-dates lia-message-post-date">
<span class="DateTime lia-message-posted-on lia-component-common-widget-date" class="DateTime lia-message-posted-on">
<span class="local-date">‎06-25-2013</span>
<span class="local-time">12:11 AM</span>

这实际上是一个正则表达式可以轻松解决的问题，因为我们可以编写一个正则表达式，找到两种类型的消息中的所有实例。在代码中，我们声明第一个找到的实例是原始消息，下一个实例是回复，代码如下：

mainmessage = tuples[0]
if len(tuples) > 1:
    reply = tuples[1]

我们本可以使用基于解析树的解决方案，如 BeautifulSoup，但我们需要处理这样一个事实：span类的值在两组日期中是相同的，甚至父元素（<p>标签）也有相同的类。因此，这个解析树比章节中第二种方法所展示的要复杂得多。

如果你真的想尝试使用 BeautifulSoup 进行这种提取，我的建议是首先使用浏览器的开发者工具查看页面结构。例如，在 Chrome 浏览器中，你可以选择你感兴趣的元素——在这个案例中是日期和时间——右键点击它，然后选择检查元素。这将打开一个开发者工具面板，显示该数据在整个文档树中的位置。在每个 HTML 元素左侧的小箭头指示是否存在子节点。然后，你可以决定如何通过程序化地定位目标元素在解析树中的位置，并制定区分它与其他节点的计划。由于这个任务超出了本书的范围，我将留给读者作为练习。

总结

在本章中，我们发现了几种经过验证的技术，用于将有趣的数据与不需要的数据分离。当我们在厨师的厨房里做高汤时，我们使用筛子来过滤掉我们不需要的骨头和蔬菜残渣，同时让我们想要的美味液体通过筛网的孔流入容器中。当我们在数据科学的厨房里从网页中提取数据时，采用的也是同样的思路。我们需要设计一个清理计划，使我们能够提取所需的数据，同时把剩下的 HTML 丢弃。

在过程中，我们回顾了提取 HTML 数据时使用的两种主要思维模型，即逐行分隔符方法和解析树/节点模型。接着，我们探讨了三种可靠且经过验证的 HTML 解析方法，用于提取我们想要的数据：正则表达式、BeautifulSoup 和基于 Chrome 的点击式网页抓取工具。最后，我们完成了一个项目，收集并提取了来自现实世界电子邮件和 HTML 页面的有用数据。

诸如电子邮件和 HTML 之类的文本数据证明并不难清理，但那二进制文件呢？在下一章中，我们将探讨如何从一个更加复杂的目标——PDF 文件中提取干净的数据。

第六章：清理 PDF 文件中的数据

在上一章中，我们发现了从我们想要的数据中分离不需要的数据的不同方法。我们将数据清理过程想象成做鸡汤，我们的目标是保留汤底，去掉骨头。但是，如果我们想要的数据和不需要的数据难以区分，应该怎么办呢？

想象一瓶陈年好酒，酒中有大量沉淀物。乍一看，我们可能看不见悬浮在液体中的沉淀物。但当酒液在醒酒器中放置一段时间后，沉淀物会沉到底部，我们就能倒出更加清澈、更具香气的酒液。在这种情况下，普通的过滤器无法将酒与沉淀物分开——需要一个专用工具。

在本章中，我们将尝试使用几种数据醒酒器来提取隐藏在难以理解的 PDF 文件中的所有有用内容。我们将探索以下主题：

  • PDF 文件的用途是什么，为什么从中提取数据很困难

  • 如何从 PDF 文件中复制和粘贴内容，以及当这不起作用时该怎么办

  • 如何通过只保存我们需要的页面来缩小 PDF 文件的大小

  • 如何使用名为pdfMiner的 Python 包中的工具从 PDF 文件中提取文本和数字

  • 如何使用一种名为Tabula的基于浏览器的 Java 应用程序从 PDF 文件中提取表格数据

  • 如何使用完整的、付费版的 Adobe Acrobat 提取数据表格

为什么清理 PDF 文件很困难？

便携式文档格式（PDF）文件比我们之前在本书中查看的文本文件要复杂一些。PDF 是一种二进制格式，由 Adobe Systems 发明，后来发展成开放标准，以便多个应用程序能够创建文档的 PDF 版本。PDF 文件的目的是提供一种查看文档中文本和图形的方式，而不依赖于进行原始排版的软件。

在 1990 年代初期，桌面出版的全盛时期，每个图形设计软件包都有不同的专有格式，并且这些软件包非常昂贵。在那个时候，为了查看一个用 Word、Pagemaker 或 Quark 创建的文档，你必须使用创建该文档的相同软件打开它。这在 Web 初期尤为成问题，因为 HTML 中并没有很多技术可以创建复杂的布局，但人们还是想要相互分享文件。PDF 的设计目的是作为一种供应商中立的布局格式。Adobe 免费提供其 Acrobat Reader 软件，供任何人下载，随后 PDF 格式得到了广泛应用。

注意

这是关于 Acrobat Reader 早期的一些有趣事实。当你在 Google 搜索引擎中输入“click here”时，仍然会将Adobe 的 Acrobat PDF Reader 下载网站作为第一个结果，并且已经维持了多年。这是因为许多网站分发 PDF 文件时会附带类似“要查看此文件，您必须安装 Acrobat Reader。点击此处下载。”的信息。由于 Google 的搜索算法会利用链接文本来判断哪些网站与哪些关键词相关联，因此click here这一关键词现在与 Adobe Acrobat 的下载站点紧密相关。

PDF 仍然被用来制作供应商和应用程序中立的文件版本，这些文件的布局比纯文本能够实现的更为复杂。例如，在不同版本的 Microsoft Word 中查看同一文档时，包含大量嵌入表格、样式、图像、表单和字体的文档仍然可能会表现出不同的效果。这可能是由多个因素引起的，例如操作系统的差异或安装的 Word 软件版本不同。即使是旨在跨软件包或版本之间兼容的应用程序，细微的差异也可能导致不兼容问题。PDF 就是为了解决其中的一些问题而创建的。

我们可以立即看出，处理 PDF 文件要比文本文件更困难，因为它是二进制格式，而且还嵌入了字体、图像等。因此，我们可靠的数据清理工具箱中的大部分工具，比如文本编辑器和命令行工具（less），在处理 PDF 文件时基本无用。幸运的是，仍然有一些技巧可以帮助我们从 PDF 文件中提取数据。

首先尝试简单的解决方案——复制

假设在你准备倒出瓶中的美味红酒时，不小心把瓶子打翻了。你可能会认为这是一次彻底的灾难，认为需要更换整个地毯。但在你开始撕掉整个地板之前，或许值得尝试一个老酒吧招数：苏打水和湿布。在这一部分，我们概述了一些可以首先尝试的步骤，而不是直接投入昂贵的文件翻新项目。它们可能不起作用，但值得一试。

我们的实验文件

让我们通过使用一个实际的 PDF 文件来练习清理 PDF 数据。我们也不希望这个实验太简单，因此我们选择了一个非常复杂的文件。假设我们有兴趣从一个在 Pew Research Center 网站上找到的文件中提取数据，文件名为《Is College Worth It?》。这份 2011 年发布的 PDF 文件共有 159 页，包含了大量数据表格，展示了衡量美国大学教育是否值得投资的各种方法。我们希望找到一种方法，能够快速提取这些表格中的数据，以便进行进一步的统计分析。例如，下面是报告中某个表格的样子：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00282.jpeg

这个表格相当复杂。它只有六列和八行，但有几行占用了两行，并且标题行的文本仅在五列中显示。

提示

完整报告可以在 PewResearch 网站找到：www.pewsocialtrends.org/2011/05/15/is-college-worth-it/，我们使用的特定文件标注为《完整报告》：www.pewsocialtrends.org/files/2011/05/higher-ed-report.pdf。

第一步——尝试复制我们需要的数据

我们将在这个示例中使用的数据位于 PDF 文件的第 149 页（在他们的文档中标记为第 143 页）。如果我们在 PDF 查看器中打开文件，例如 Mac OSX 上的预览，并尝试仅选择表格中的数据，我们已经看到一些奇怪的事情发生了。例如，即使我们不打算选择页面编号（143），它仍然被选中了。这对我们的实验来说不是个好兆头，但我们还是继续吧。使用 Command-C 复制数据，或者选择编辑 | 复制。

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00283.jpeg

在预览中选择这个 PDF 中的文本时的显示效果

第二步——尝试将复制的数据粘贴到文本编辑器中

以下截图展示了复制的文本粘贴到我们的文本编辑器 Text Wrangler 中后的样子：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00284.jpeg

很明显，在复制并粘贴数据后，这些数据的顺序完全混乱。页面编号被包含在内，数字是横向排列的而非纵向，列标题也排列错乱。甚至有些数字被合并了；例如，最后一行包含了 4、4、3、2 这四个数字，但在粘贴后的版本中，这些数字变成了一个单一的数字4432。此时，手动清理这些数据可能需要比重新输入原始表格更长的时间。我们可以得出结论，在处理这个特定的 PDF 文件时，我们需要采取更强的措施来清理它。

提示

我们应当注意，此时 PDF 文件的其他部分清理得很好。例如，位于文件第 3 页的前言（纯文本部分），使用前述技巧复制出来也很顺利。在这个文件中，唯一的问题是实际的表格数据。你应该在决定提取技巧之前，先对 PDF 文件的所有部分——包括文本和表格数据——进行实验。

第三步——制作文件的简化版本

我们的复制粘贴操作没有成功，因此我们已经接受了需要采取更具侵入性措施的事实。也许如果我们不打算提取这个 PDF 文件的所有 159 页数据，我们可以仅识别出我们想要操作的 PDF 区域，并将该部分保存为一个单独的文件。

在 MacOSX 的 预览 中执行此操作，启动 文件 | 打印… 对话框。在 页面 区域，我们将输入实际想要复制的页面范围。为了进行这个实验，我们只对第 149 页感兴趣；因此，在 从： 和 到： 框中都输入 149，如以下截图所示。

然后在底部的 PDF 下拉框中，选择 在预览中打开 PDF。您将看到您的单页 PDF 出现在新窗口中。从这里，我们可以将其保存为新文件并给它一个新名称，如 report149.pdf 或类似名称。

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00285.jpeg

另一种可尝试的技术 – pdfMiner

现在我们有了一个较小的文件来进行实验，让我们尝试一些编程解决方案来提取文本，看看是否能有所改善。pdfMiner 是一个 Python 包，包含两个嵌入式工具，用于处理 PDF 文件。我们特别感兴趣的是尝试其中一个工具，一个名为 pdf2txt 的命令行程序，旨在从 PDF 文档中提取文本。也许它能够帮助我们正确提取文件中的数字表格。

第一步 – 安装 pdfMiner

启动 Canopy Python 环境。从 Canopy 终端窗口中，运行以下命令：

pip install pdfminer

这将安装整个 pdfMiner 包及其所有相关的命令行工具。

提示

pdfMiner 及其随附的两个工具 pdf2txt 和 dumpPDF 的文档可以在 www.unixuser.org/~euske/python/pdfminer/ 查阅。

第二步 – 从 PDF 文件中提取文本

我们可以使用名为 pdf2txt.py 的命令行工具从 PDF 文件中提取所有文本。为此，请使用 Canopy 终端并导航到文件所在目录。命令的基本格式是 pdf2txt.py <filename>。如果您的文件较大并包含多页（或者如果您尚未将 PDF 拆分成较小的文件），您还可以运行 pdf2txt.py –p149 <filename> 来指定只提取第 149 页。

与前面的复制粘贴实验一样，我们不仅会尝试在第 149 页的表格上使用此技术，还会尝试在第 3 页的序言上。为了仅提取第 3 页的文本，我们运行以下命令：

pdf2txt.py –p3 pewReport.pdf

运行此命令后，Pew Research 报告的提取序言将出现在我们的命令行窗口中：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00286.jpeg

要将此文本保存为名为 pewPreface.txt 的文件，我们只需在命令行中添加重定向，如下所示：

pdf2txt.py –p3 pewReport.pdf > pewPreface.txt

那么位于第 149 页的那些棘手的数据表格呢？当我们对它们使用pdf2txt时会发生什么呢？我们可以运行以下命令：

pdf2txt.py pewReport149.pdf

结果比复制粘贴稍微好一些，但差别不大。实际的数据输出部分如下截图所示。列头和数据混在一起，来自不同列的数据顺序错乱。

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00287.jpeg

我们必须宣告此次表格数据提取实验失败，尽管 pdfMiner 在逐行文本提取方面表现得相当不错。

注意

请记住，使用这些工具的成功率可能因人而异，很多时候取决于原始 PDF 文件的特定特性。

看起来我们为这个示例选择了一个非常棘手的 PDF 文件，但我们不必灰心丧气。相反，我们将转向另一个工具，看看它的表现如何。

第三种选择——Tabula

Tabula是一个基于 Java 的程序，用于提取 PDF 文件中表格内的数据。我们将下载 Tabula 软件，并让它在我们第 149 页的棘手表格上发挥作用。

步骤一——下载 Tabula

Tabula 可以从其官方网站下载：tabula.technology/。该网站提供了一些简单的下载说明。

提示

在 Mac OSX 10.10.1 版本上，我必须先下载旧版的 Java 6 应用程序，才能运行 Tabula。整个过程非常简单，只需按照屏幕上的说明操作即可。

步骤二——运行 Tabula

从下载的.zip压缩文件中启动 Tabula。在 Mac 系统上，Tabula 应用程序文件简单地被称为Tabula.app。如果你愿意，可以将其复制到Applications文件夹中。

当 Tabula 启动时，它会在默认的网页浏览器中打开一个标签页或窗口，地址是http://127.0.0.1:8080/。屏幕上的初始操作部分如下所示：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00288.jpeg

自动检测表格需要较长时间，这个警告是对的。对于包含三个表格的单页perResearch149.pdf文件，表格自动检测花费了整整两分钟，并且出现了一个关于 PDF 文件格式不正确的错误信息。

步骤三——指示 Tabula 提取数据

一旦 Tabula 读取了文件，就该指示它所在的位置在哪里了。使用鼠标光标，选择你感兴趣的表格。我在第一个表格的四周画了一个框。

Tabula 花了大约 30 秒钟读取表格，结果如下所示：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00289.jpeg

与通过复制粘贴和pdf2txt读取数据的方式相比，这些数据看起来非常不错。但是，如果你对 Tabula 读取表格的方式不满意，你可以通过清除选择并重新绘制矩形来重复此过程。

步骤四——提取数据

我们可以使用 Tabula 中的下载数据按钮将数据保存为更友好的文件格式，如 CSV 或 TSV。正如我们在前几章所学，这些格式如果需要，可以在电子表格或文本编辑器中清理。恰到好处，我们准备进行下一步了。

第五步——更多清理

在 Excel 或文本编辑器中打开 CSV 文件并查看它。在这个阶段，我们在提取 PDF 数据时已经遇到了很多失败，所以现在很容易就想放弃。然而，如果你已经读到数据清理的书籍中的这一部分，你可能会猜到这些数据还能进一步清理。这里有一些我们从前几章学到的简单数据清理任务：

  1. 我们可以将所有两行文本单元格合并成一个单元格。例如，在B列，许多短语占据了不止一行。让学生具备高效工作能力和成为劳动力成员应该放在一个单元格中，作为一个完整的短语。1行和2行的表头也是如此（4-year和Private应该放在一个单元格中）。要在 Excel 中清理这些内容，可以在B列和C列之间创建一个新列。使用concatenate()函数将 B3:B4、B5:B6 等进行合并。使用粘贴特殊将新合并的值添加到新列中。然后删除不再需要的两列。对1行和2行也执行相同的操作。

  2. 删除行之间的空白行。

    当这些操作完成后，数据看起来是这样的：

    https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00290.jpeg

提示

与直接剪切粘贴数据或运行简单命令行工具相比，Tabula 可能看起来要花费更多精力。这是真的，除非你的 PDF 文件像这个一样挑剔。记住，专业工具之所以存在是有原因的——但除非真的需要，否则不要使用它们。首先使用简单的解决方案，只有在真正需要时才使用更复杂的工具。

当一切都失败时——第四种技术

Adobe Systems 销售的付费商业版 Acrobat 软件比起仅仅允许你阅读 PDF 文件，还具有一些附加功能。使用完整版的 Acrobat，你可以创建复杂的 PDF 文件，并以各种方式操作现有文件。与此相关的一个功能是 Acrobat 中的**导出选定内容为…**选项。

要开始使用此功能，请启动 Acrobat，并使用文件打开对话框打开 PDF 文件。在文件内，导航到包含要导出的数据的表格。以下截图展示了如何选择我们操作过的 149 页 PDF 中的数据。使用鼠标选择数据，然后右键点击并选择导出选定内容为…

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00291.jpeg

到此为止，Acrobat 会询问你想如何导出数据。CSV 是其中的一个选择。如果你确定不想在文本编辑器中编辑文件，Excel 工作簿（.xlsx）也是一个不错的选择。由于我知道 Excel 也能打开 CSV 文件，我决定将文件保存为这种格式，以便在 Excel 和文本编辑器之间具有更大的灵活性。

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00292.jpeg

在选择文件格式后，我们会被提示输入文件名和保存位置。当我们在文本编辑器或 Excel 中打开生成的文件时，会发现它看起来和我们在前一节看到的 Tabula 版本很像。以下是 CSV 文件在 Excel 中打开后的样子：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00293.jpeg

到这个阶段，我们可以使用与 Tabula 数据清洗时相同的清理程序，合并 B2:B3 单元格到一个单元格中，然后删除空行。

摘要

本章的目标是学习如何从 PDF 文件中导出数据。就像美酒中的沉淀物，PDF 文件中的数据起初看起来非常难以分离。然而，与倒酒这一非常被动的过程不同，分离 PDF 数据需要大量的反复试探。我们学会了四种处理 PDF 文件清洗数据的方法：复制粘贴、pdfMiner、Tabula 和 Acrobat 导出。每种工具都有其优缺点：

  • 复制粘贴不需要任何成本，且几乎不需要工作，但对于复杂的表格来说，效果并不理想。

  • pdfMiner/Pdf2txt 也是免费的，作为一个命令行工具，它可以被自动化处理，且能处理大量数据。但和复制粘贴一样，它也容易被某些类型的表格所混淆。

  • Tabula 的设置工作量较大，而且由于它是一个正在开发中的产品，有时会出现奇怪的警告。它的处理速度也比其他选项慢一些。然而，它的输出非常干净，即便是复杂的表格。

  • Acrobat 的输出与 Tabula 类似，但几乎不需要设置，也几乎不费力气。它是一个付费产品。

到最后，我们得到了一个干净的数据集，准备进行分析或长期存储。

在下一章，我们将重点讨论已存储在关系型数据库管理系统（RDBMS）中的数据。我们将学习如何清洗这种方式存储的数据，了解一些常见的数据异常以及如何修复它们。

第七章：RDBMS 清理技术

家用冰箱通常配备架子，大多数还配有一两个蔬菜抽屉。但是，如果你曾经参观过家居整理商店或与专业的整理师交流过，你会发现还有许多额外的储存选项，包括蛋托、奶酪盒、饮料罐分配器、酒瓶架、剩菜标签系统以及各种尺寸的堆叠式、彩色编码的收纳盒。但我们真的需要这些额外的东西吗？要回答这个问题，你可以问自己以下几个问题：我常用的食物是否容易找到？食物是否占用了不应有的空间？剩菜是否清楚标注了内容和制作时间？如果我们的答案是否，整理专家表示，容器和标签可以帮助我们优化存储、减少浪费，并让生活更轻松。

这与我们的关系型数据库管理系统（RDBMS）是一样的。作为经典的长期数据存储解决方案，RDBMS 是现代数据科学工具包的标准部分。然而，我们常常犯的一个错误是，仅仅将数据存入数据库，却很少考虑细节。在本章中，我们将学习如何设计一个超越两层架子和一个抽屉的 RDBMS。我们将学习一些技术，确保我们的 RDBMS 能够优化存储、减少浪费，并使我们的生活更轻松。具体来说，我们将：

  • 学习如何发现我们 RDBMS 数据中的异常

  • 学习几种策略来清理不同类型的问题数据

  • 学习何时以及如何为清理过的数据创建新表，包括创建子表和查找表

  • 学习如何记录你所做更改的规则

准备工作

在本章的示例中，我们将使用一个流行的数据集——Sentiment140。该数据集的创建旨在帮助学习 Twitter 消息中的正面和负面情绪。我们在本书中并不专注于情感分析，但我们将使用这个数据集来练习在数据已导入关系型数据库后进行数据清理。

要开始使用 Sentiment140 数据集，你需要设置好 MySQL 服务器，和之前的 Enron 示例一样。

第一步——下载并检查 Sentiment140 数据集

我们想使用的 Sentiment140 数据版本是来自 Sentiment140 项目的原始文件集，直接可以从help.sentiment140.com/for-students获取。这份包含推文及其积极与消极情感（或情绪，评分为 0、2 或 4）的 ZIP 文件由斯坦福大学的研究生创建。自从这份文件公开发布后，其他网站也将原始的 Sentiment140 文件添加到其平台，并将其作为更大推文集合的一部分公开。对于本章内容，我们将使用原始的 Sentiment140 文本文件，可以通过前面提到的链接或直接访问cs.stanford.edu/people/alecmgo/trainingandtestdata.zip来获取。

下载 ZIP 文件，解压缩并使用文本编辑器查看其中的两个 CSV 文件。你会立刻注意到，一个文件比另一个文件的行数多得多，但这两个文件的列数是相同的。数据是逗号分隔的，并且每一列都被双引号括起来。每一列的描述可以在前一部分链接的for-students页面中找到。

第二步 – 清理以便数据库导入

对于我们的目的——学习如何清理数据——将这些文件中较小的一个加载到单个 MySQL 数据库表中就足够了。我们所需要做的所有学习，都可以通过较小的文件来完成，这个文件叫做testdata.manual.2009.06.14.csv。

在查看数据时，我们可能会注意到一些地方，如果我们直接将此文件导入 MySQL，可能会出现问题。其中一个问题出现在文件的第 28 行：

"4","46","Thu May 14 02:58:07 UTC 2009","""booz allen""",

你看到在booz关键字前和allen一词后有三重引号"""吗？同样的问题出现在第 41 行，在歌曲标题P.Y.T周围有双引号：

"4","131","Sun May 17 15:05:03 UTC 2009","Danny Gokey","VickyTigger","I'm listening to ""P.Y.T"" by Danny Gokey…"

这些额外的引号问题在于 MySQL 导入程序会使用引号来分隔列文本。这将导致错误，因为 MySQL 会认为这一行的列数比实际的多。

为了解决这个问题，在文本编辑器中，我们可以使用查找和替换功能，将所有的"""替换为"（双引号），并将所有的""替换为'（单引号）。

提示

这些""可能也可以完全移除，对这个清理工作几乎没有负面影响。为此，我们只需要搜索""并将其替换为空。但如果你希望尽量接近推文的原始意图，使用单引号（甚至像这样转义的双引号\"）作为替代字符是一个安全的选择。

将这个清理过的文件保存为新文件名，比如cleanedTestData.csv。现在我们准备将它导入到 MySQL 中。

第三步 – 将数据导入到 MySQL 的单一表中

为了将我们稍微清理过的数据文件加载到 MySQL 中，我们需要回顾一下第三章中导入电子表格数据到 MySQL部分的 CSV 到 SQL 技术：

  1. 从命令行，导航到保存你在第二步中创建的文件的目录。这就是我们将要导入到 MySQL 中的文件。

  2. 然后，启动你的 MySQL 客户端，并连接到你的数据库服务器：

    user@machine:~/sentiment140$ mysql -hlocalhost -umsquire -p
Enter password:

  3. 输入你的密码，登录后，在 MySQL 中创建一个数据库来存储表格，方法如下：

    mysql> CREATE DATABASE sentiment140;
mysql> USE sentiment140;

  4. 接下来，我们需要创建一个表格来存储数据。每一列的数据类型和长度应该尽可能匹配我们所拥有的数据。某些列将是 varchar 类型的，每列都需要指定长度。由于我们可能不知道这些长度应该是多少，我们可以使用清理工具来确定一个合适的范围。

  5. 如果我们在 Excel 中打开 CSV 文件（Google 电子表格同样可以很好地完成这项工作），我们可以运行一些简单的函数来找到某些文本字段的最大长度。例如，len()函数可以给出文本字符串的字符长度，max()函数则能告诉我们某个范围中的最大值。打开 CSV 文件后，我们可以应用这些函数来查看 MySQL 中 varchar 列的长度应是多少。

    以下截图展示了一种使用函数来解决这个问题的方法。它展示了length()函数应用于列G，并且max()函数应用于列H，但作用于列G。

    https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00294.jpeg

    列G和H展示了如何在 Excel 中获取文本列的长度，然后获取最大值。

  6. 为了更快速地计算这些最大长度，我们还可以使用 Excel 的快捷方式。以下数组公式可以快速将文本列的最大值和长度合并到一个单元格中——只需确保在输入此嵌套函数后按Ctrl + Shift + Enter，而不是仅按Enter：

    =max(len(f1:f498))

    这个嵌套函数可以应用于任何文本列，以获取该列中文本的最大长度，它只使用一个单元格来完成这一操作，而不需要任何中间的长度计算。

在我们运行这些函数之后，结果显示我们任何一个推文的最大长度是 144 个字符。

检测和清理异常

你可能会好奇，为什么这个数据集中一条推文的长度会是 144 个字符，而 Twitter 限制所有推文的最大长度为 140 个字符。结果发现，在 sentiment140 数据集中，&字符有时被翻译成 HTML 等效代码&amp，但并不是每次都这样。有时也使用了其他 HTML 代码，例如，<字符变成了&lt;，>变成了&gt;。所以，对于一些非常长的推文，增加的几个字符很容易使这条推文超过 140 个字符的长度限制。我们知道，这些 HTML 编码的字符并不是原始用户推文的内容，而且我们发现这些情况并不是每次都会发生，因此我们称之为数据异常。

要清理这些数据，我们有两种选择。我们可以选择直接将脏数据导入数据库并尝试在那里清理，或者先在 Excel 或文本编辑器中清理。为了展示这两种方法的不同，我们在这里会同时演示这两种做法。首先，我们将在电子表格或文本编辑器中使用查找和替换功能，尝试将下表中显示的字符转换。我们可以将 CSV 文件导入 Excel，看看能在 Excel 中清理多少：

HTML 代码替换为实例计数用来查找计数的 Excel 函数
&lt;<6=COUNTIF(F1:F498,"*&lt*")
&gt;>5=COUNTIF(F1:F498,"*&gt*")
&amp;&24=COUNTIF(F1:F498,"*&amp*")

前两个字符替换在 Excel 中的查找和替换功能中运行正常。&lt;和&gt;这些 HTML 编码字符已被替换。看看文本像这样：

I'm listening to 'P.Y.T' by Danny Gokey &lt;3 &lt;3 &lt;3 Aww, he's so amazing. I &lt;3 him so much :)

上述内容会变成如下文本：

I'm listening to 'P.Y.T' by Danny Gokey <3 <3 <3 Aww, he's so amazing. I <3 him so much :)

然而，当我们尝试在 Excel 中查找&amp;并将其替换为&时，可能会遇到一个错误，如下所示：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00295.jpeg

一些操作系统和 Excel 版本存在我们选择**&**字符作为替代符时的一个问题。如果遇到这个错误，我们可以采取几种不同的方法：

  • 我们可以使用自己喜欢的搜索引擎，尝试找到一个 Excel 解决方案来修复这个错误。

  • 我们可以将 CSV 文本数据移到文本编辑器中，并在那里执行查找和替换功能。

  • 我们也可以继续将数据导入数据库，即使其中包含了奇怪的&amp;字符，然后再尝试在数据库中清理这些数据。

通常，我会倾向于不将脏数据导入数据库，除非可以在数据库外清理干净。然而，既然这章是关于在数据库内清理数据的，那我们就将半清理的数据导入数据库，等数据进入表格后，再清理&amp;问题。

创建我们的表格

为了将我们半清理的数据导入数据库，我们首先需要编写CREATE语句，然后在 MySQL 数据库中运行它。CREATE语句如下所示：

mysql> CREATE TABLE sentiment140 (
    ->   polarity enum('0','2','4') DEFAULT NULL,
    ->   id int(11) PRIMARY KEY,
    ->   date_of_tweet varchar(28) DEFAULT NULL,
    ->   query_phrase varchar(10) DEFAULT NULL,
    ->   user varchar(10) DEFAULT NULL,
    ->   tweet_text varchar(144) DEFAULT NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

注意

该语句使用简单快速的 MyISAM 引擎，因为我们预计不会需要任何 InnoDB 功能，例如行级锁定或事务处理。关于 MyISAM 与 InnoDB 的区别，这里有一个关于何时使用每种存储引擎的讨论：stackoverflow.com/questions/20148/myisam-versus-innodb。

你可能会注意到，代码仍然要求tweet_text列的长度为 144。这是因为我们无法清理包含&amp;代码的这些列。然而，这对我影响不大，因为我知道 varchar 列不会使用额外的空间，除非它们需要。毕竟，这就是它们被称为 varchar（可变字符）列的原因。但是，如果这个额外的长度真的让你困扰，你可以稍后修改表格，只保留该列的 140 个字符。

接下来，我们将使用 MySQL 命令行从以下位置运行导入语句：

mysql> LOAD DATA LOCAL INFILE 'cleanedTestData.csv'
    ->   INTO TABLE sentiment140
    ->   FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\'
    ->   (polarity, id, date_of_tweet, query_phrase, user, tweet_text);

该命令将从我们清理过的 CSV 文件中加载数据到我们创建的新表中。成功消息将显示如下，表明所有 498 行数据已经成功加载到表中：

Query OK, 498 rows affected (0.00 sec)
Records: 498  Deleted: 0  Skipped: 0  Warnings: 0

提示

如果你可以访问浏览器界面的工具，如 phpMyAdmin（或者桌面应用程序如 MySQL Workbench 或 Toad for MySQL），所有这些 SQL 命令都可以在这些工具中轻松完成，而无需在命令行中输入。例如，在 phpMyAdmin 中，你可以使用导入标签并在那里上传 CSV 文件。只要确保数据文件按照第二步 – 为数据库导入清理中的步骤进行清理，否则你可能会遇到文件中列数过多的错误。这个错误是由于引号问题导致的。

第四步 – 清理&amp;字符

在最后一步，我们决定暂时不清理&amp;字符，因为 Excel 在处理时给出了一个奇怪的错误。现在我们已经完成了第三步 – 将数据导入到 MySQL 的单一表格，并且数据已经导入到 MySQL 中，我们可以非常轻松地使用UPDATE语句和replace()字符串函数来清理数据。以下是需要的 SQL 查询，用来将所有出现的&amp;替换为&：

UPDATE sentiment140 SET tweet_text = replace(tweet_text,'&amp;', '&');

replace()函数的工作方式就像在 Excel 或文本编辑器中的查找和替换一样。我们可以看到，推文 ID 594 曾经显示为#at&amp;t is complete fail，现在变成了#at&t is complete fail。

第五步 – 清理其他神秘字符

当我们浏览tweet_text列时，可能会注意到一些奇怪的推文，例如推文 ID 613 和 2086：

613, Talk is Cheap: Bing that, I?ll stick with Google
2086, Stanford University?s Facebook Profile

?字符是我们应该关注的重点。和我们之前看到的 HTML 编码字符一样，这个字符问题也很可能是字符集转换中的一个副作用。在这种情况下，原始推文中可能有某种高 ASCII 或 Unicode 的撇号（有时称为智能引号），但当数据转换为低级字符集，如纯 ASCII 时，那个特定的撇号就被简单地更改为?。

根据我们打算如何处理这些数据，我们可能不希望省略?字符。例如，如果我们进行词频统计或文本挖掘，可能非常重要的是将I?ll转换为I'll，并将University?s转换为University's。如果我们决定这很重要，那么我们的任务就是检测到发生错误的推文，然后制定策略将问号转换回单引号。当然，诀窍在于，我们不能仅仅把tweet_text列中的每个问号都替换成单引号，因为有些推文中的问号是应该保留的。

为了定位问题字符，我们可以运行一些 SQL 查询，尝试使用正则表达式来查找问题。我们关注的是出现在奇怪位置的问号，例如紧跟其后的是字母字符。以下是使用 MySQL REGEXP 功能的初步正则表达式。运行此查询将大致告诉我们问题问号可能所在的位置：

SELECT id, tweet_text
FROM sentiment140
WHERE tweet_text
REGEXP '\\?[[:alpha:]]+';

这个 SQL 正则表达式查找紧跟一个或多个字母字符的问号字符。SQL 查询返回了六行结果，其中四行结果的问号是异常的，另外两行是假阳性。假阳性是指匹配了我们模式但实际上不应更改的推文。两个假阳性是推文 ID 为234和2204的推文，它们包含的问号是合法的 URL 的一部分。推文139、224、613和2086是真阳性，也就是说，这些推文被正确地检测为异常，需要进行修改。所有结果如下图所示，来自 phpMyAdmin 的截图：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00296.jpeg

不过，139号推文有点奇怪。它在Obama这个词前面有一个问号，就像是在引用某篇新闻文章的标题，但在字符串末尾没有匹配的引号（或者是丢失的引号）。这应该是某个其他字符吗？这实际上也可能是一个假阳性，或者至少它的阳性不足以让我们真正去修复它。在仔细检查推文时，224号推文也在一个看起来不该出现问号的地方多了一个奇怪的问号。

如果我们要编写一个replace()函数，将问题问号替换为单引号，我们将需要编写一个正则表达式，仅匹配真正的问题，并且不匹配任何误报。然而，由于这个数据集很小，且只有四个真正的问题——如果我们认为139不需要清理的话，就是三个——那么我们完全可以手动清理这些问题。特别是因为我们对于其他可能存在的问题（例如推文224中的额外问号）还有一些疑问。

在这种情况下，由于我们只有三行问题数据，直接对数据运行三个小的UPDATE命令会比尝试构建完美的正则表达式更快捷。以下是处理推文224（仅第一个问题）、613和2086的 SQL 查询：

UPDATE sentiment140 SET tweet_text = 'Life''s a bitch? and so is Dick Cheney. #p2 #bipart #tlot #tcot #hhrs #GOP #DNC http://is.gd/DjyQ' WHERE id = 224;

UPDATE sentiment140 SET tweet_text = 'Talk is Cheap: Bing that, I''ll stick with Google. http://bit.ly/XC3C8' WHERE id = 613;

UPDATE sentiment140 SET tweet_text = 'Stanford University''s Facebook Profile is One of the Most Popular Official University Pages - http://tinyurl.com/p5b3fl' WHERE id = 2086;

注意

请注意，在这些更新语句中，我们必须对单引号进行转义。在 MySQL 中，转义字符可以是反斜杠或单引号本身。这些示例中使用了单引号作为转义字符。

第六步——清理日期

如果我们查看date_of_tweet列，会发现我们将其创建为一个简单的可变字符字段，varchar(30)。那有什么问题呢？好吧，假设我们想按时间顺序排列这些推文。现在，我们不能使用简单的 SQL ORDER BY语句来获取正确的日期顺序，因为我们得到的将是字母顺序。所有星期五都会排在任何星期一之前，五月总是在六月之后。我们可以用以下 SQL 查询来测试这一点：

SELECT id, date_of_tweet
FROM sentiment140
ORDER BY date_of_tweet;

最初的几行是按顺序排列的，但在第 28 行附近，我们开始看到问题：

2018  Fri May 15 06:45:54 UTC 2009
2544  Mon Jun 08 00:01:27 UTC 2009
…
3  Mon May 11 03:17:40 UTC 2009

5 月 11 日并不在5 月 15 日或6 月 8 日之后。为了解决这个问题，我们需要创建一个新列，清理这些日期字符串，并将它们转换为合适的 MySQL 日期时间数据类型。我们在第二章的数据类型转换部分中学到，MySQL 在日期和时间作为原生date、time或datetime类型存储时效果最好。插入 datetime 类型的格式如下：YYYY-MM-DD HH:MM:SS。但我们在date_of_tweet列中的数据并不是这种格式。

MySQL 有许多内置函数可以帮助我们将杂乱的日期字符串格式化为首选格式。通过这样做，我们可以利用 MySQL 在日期和时间上的数学运算能力，例如，找出两个日期或时间之间的差异，或者按日期或时间正确地排序项目。

为了将我们的字符串转换为 MySQL 友好的日期时间类型，我们将执行以下操作：

  1. 修改表格，增加一个新列，用于存储新的日期时间信息。我们可以将这个新列命名为date_of_tweet_new、date_clean，或者其他一个清晰区分于原date_of_tweet列的名称。执行此任务的 SQL 查询如下：

    ALTER TABLE sentiment140
ADD date_clean DATETIME NULL
AFTER date_of_tweet;

  2. 对每一行执行更新操作，将旧的日期字符串格式化为正确格式的日期时间类型，而不是字符串，并将新值添加到新创建的date_clean列中。执行此任务的 SQL 语句如下：

    UPDATE sentiment140
SET date_clean = str_to_date(date_of_tweet, '%a %b %d %H:%i:%s UTC %Y');

此时，我们已经有了一个新列，里面填充了清理后的日期时间。回想一下，原来的date_of_tweet列有问题，因为它没有正确地对日期进行排序。为了测试日期是否现在已经正确排序，我们可以按新列的顺序选择数据：

SELECT id, date_of_tweet 
FROM sentiment140
ORDER BY date_clean;

我们看到现在的行已经完美排序，5 月 11 日的日期排在最前面，且没有日期错乱。

我们是否应该删除旧的date列？这由你决定。如果你担心可能犯了错误，或者因为某些原因你可能需要原始数据，那么就保留它。但如果你觉得可以删除，直接删除该列，如下所示：

ALTER TABLE sentiment140 
DROP date_of_tweet;

你也可以创建一个 Sentiment140 表的副本，里面包含原始列作为备份。

第七步 – 分离用户提及、话题标签和 URL

目前这个数据的另一个问题是，tweet_text列中隐藏了很多有趣的信息，例如，考虑一个人使用@符号将推文指向另一个人的情况。这叫做 Twitter 上的提及。统计一个人被提及的次数，或者统计他们与特定关键词一起被提及的次数，可能会很有趣。另一个隐藏在部分推文中的有趣数据是话题标签；例如，ID 为 2165 的推文使用了#jobs和#sittercity话题标签讨论工作和保姆的概念。

这条推文还包含了一个外部的非 TwitterURL。我们可以提取每个提及、话题标签和 URL，并将它们单独保存到数据库中。

这个任务与我们清理日期时的操作类似，但有一个重要的区别。在日期的情况下，我们只有一个可能的修正版本，因此只需添加一个新列来存储清理后的日期版本。然而，对于提及、话题标签和 URL，我们在单个tweet_text值中可能会有零个或多个，例如我们之前查看的推文（ID 2165）包含了两个话题标签，这条推文（ID 2223）也是如此：

HTML 5 Demos! Lots of great stuff to come! Yes, I'm excited. :) http://htmlfive.appspot.com #io2009 #googleio

这条推文没有提及、一个 URL 和两个话题标签。推文 ID 为 13078 的推文包含了三个提及，但没有话题标签或 URL：

Monday already. Iran may implode. Kitchen is a disaster. @annagoss seems happy. @sebulous had a nice weekend and @goldpanda is great. whoop.

我们需要更改数据库结构，以便存储这些新的信息——话题标签、URLs 和用户提及——同时要记住，一条推文中可能包含许多这样的内容。

创建一些新表

根据关系数据库理论，我们应避免创建用于存储多值属性的列。例如，如果一条推文有三个话题标签，我们不应该将这三个话题标签都存入同一列。对我们来说，这条规则意味着我们不能直接复制用于日期清理问题的ALTER过程。

相反，我们需要创建三个新表：sentiment140_mentions、sentiment140_urls 和 sentiment140_hashtags。每个新表的主键将是一个合成 ID 列，每个表将包括另外两个列：tweet_id，它将该新表与原始sentiment140表联系起来，以及实际提取的标签、提及或 URL 文本。以下是创建这些表的三个CREATE语句：

CREATE TABLE IF NOT EXISTS sentiment140_mentions (
  id int(11) NOT NULL AUTO_INCREMENT,
  tweet_id int(11) NOT NULL,
  mention varchar(144) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS sentiment140_hashtags (
  id int(11) NOT NULL AUTO_INCREMENT,
  tweet_id int(11) NOT NULL,
  hashtag varchar(144) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS sentiment140_urls (
  id int(11) NOT NULL AUTO_INCREMENT,
  tweet_id int(11) NOT NULL,
  url varchar(144) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

注意

这些表不使用外键回到原始的sentiment140推文表。如果您想添加这些外键，这是完全可能的。但为了学习如何清理这个数据集，我们在此并不需要外键。

现在我们已经创建了表格，是时候将我们从tweet_text column中仔细提取的数据填充到这些表格中了。我们将分别处理每个提取的案例，从用户提及开始。

提取用户提及

为了设计一个能处理用户提及提取的程序，我们首先回顾一下我们已知的推文中关于提及的内容：

  • 用户提及总是以**@**符号开始

  • 用户提及是紧跟在**@**符号后的单词

  • 如果**@**后面有空格，则不是用户提及

  • 用户提及本身内部没有空格

  • 由于电子邮件地址也使用**@**符号，我们应该注意这一点

使用这些规则，我们可以构造一些有效的用户提及：

  • @foo

  • @foobar1

  • @1foobar

我们可以构造一些无效的用户提及示例：

  • @ foo（@后面的空格使其无效）

  • foo@bar.com（bar.com 未被识别）

  • @foo bar（只会识别@foo）

  • @foo.bar（只会识别@foo）

注意

在这个例子中，我们假设我们不关心常规的@mention和.@mention（有时称为点提及）之间的区别。这些是推文中在@`符号前有一个句点的推文，目的是将推文推送到所有用户的粉丝。

由于这个规则集比我们能在 SQL 中高效执行的要复杂，因此更倾向于编写一个简单的小脚本，利用正则表达式来清理这些推文。我们可以用任何能连接到数据库的语言来编写这种类型的脚本，比如 Python 或 PHP。由于我们在第二章中使用了 PHP 连接数据库，基础知识 - 格式、类型和编码，我们在这里也使用一个简单的 PHP 脚本。这个脚本连接到数据库，搜索 tweet_text 列中的用户提及，并将找到的提及移动到新的 sentiment140_mentions 表中：

<?php
// connect to db
$dbc = mysqli_connect('localhost', 'username', 'password', 'sentiment140')
    or die('Error connecting to database!' . mysqli_error());
$dbc->set_charset("utf8");

// pull out the tweets
$select_query = "SELECT id, tweet_text FROM sentiment140";
$select_result = mysqli_query($dbc, $select_query);

// die if the query failed
if (!$select_result)
    die ("SELECT failed! [$select_query]" .  mysqli_error());

// pull out the mentions, if any
$mentions = array();
while($row = mysqli_fetch_array($select_result))
{
    if (preg_match_all(
        "/(?<!\pL)@(\pL+)/iu",
        $row["tweet_text"],
        $mentions
    ))
    { 
        foreach ($mentions[0] as $name)
        {
            $insert_query = "INSERT into sentiment140_mentions (id, tweet_id, mention) VALUES (NULL," . $row["id"] . ",'$name')";
            echo "<br />$insert_query";
            $insert_result = mysqli_query($dbc, $insert_query);
            // die if the query failed
            if (!$insert_result)
                die ("INSERT failed! [$insert_query]" .  mysqli_error());
        }
    }
}
?>

在对 sentiment140 表运行这个小脚本之后，我们发现从原始的 498 条推文中提取了 124 个独特的用户提及。这个脚本的几个有趣之处包括，它可以处理用户名中的 Unicode 字符，即使这个数据集中没有这些字符。我们可以通过快速插入一行测试数据到 sentiment140 表的末尾来进行测试，例如：

INSERT INTO sentiment140 (id, tweet_text) VALUES(99999, "This is a @тест");

然后，再次运行脚本；你会看到在 sentiment140_mentions 表中添加了一行，并成功提取了 @тест 的 Unicode 用户提及。在下一节中，我们将构建一个类似的脚本来提取标签。

提取标签

标签有其自身的规则，这些规则与用户提及略有不同。以下是一些我们可以用来判断是否为标签的规则：

  • 标签以 # 符号开头

  • 标签是紧跟在 # 符号后面的单词

  • 标签可以包含下划线，但不能有空格和其他标点符号

用于提取标签的 PHP 代码与用户提及的代码几乎完全相同，唯一不同的是代码中间的正则表达式。我们只需将 $mentions 变量改为 $hashtags，然后调整正则表达式如下：

if (preg_match_all(
        "/(#\pL+)/iu",
        $row["tweet_text"],
        $hashtags
    ))

这个正则表达式表示我们对匹配大小写不敏感的 Unicode 字母字符感兴趣。然后，我们需要将 INSERT 行改为使用正确的表和列名，如下所示：

$insert_query = "INSERT INTO sentiment140_hashtags (id, tweet_id, hashtag) VALUES (NULL," . $row["id"] . ",'$name')";

当我们成功运行这个脚本时，我们看到 54 个标签已被添加到 sentiment140_hashtags 表中。更多的推文中包含了多个标签，甚至比包含多个用户提及的推文还多。例如，我们可以立即看到推文 174 和 224 都包含了多个嵌入的标签。

接下来，我们将使用这个相同的骨架脚本，并再次修改它来提取 URLs。

提取 URLs

从文本中提取 URL 可以像寻找任何以http://或https://开头的字符串一样简单，或者根据文本中包含的 URL 类型的不同，可能会变得更为复杂。例如，有些字符串可能包括file:// URL 或者磁力链接（如磁力链接），或者其他类型的特殊链接。在我们的 Twitter 数据中，情况相对简单，因为数据集中包含的所有 URL 都以 HTTP 开头。所以，我们可以偷懒，设计一个简单的正则表达式来提取任何以 http:// 或 https:// 开头的字符串。这个正则表达式看起来就是这样：

if (preg_match_all(
        "!https?://\S+!",
        $row["tweet_text"],
        $urls
    ))

然而，如果我们在喜欢的搜索引擎上稍作搜索，实际上我们可以轻松找到一些相当印象深刻且实用的通用 URL 匹配模式，这些模式可以处理更复杂的链接格式。这样做的好处在于，如果我们编写的 URL 提取程序能够处理这些更复杂的情况，那么即使未来我们的数据发生变化，它依然能够正常工作。

一个非常详细的 URL 匹配模式文档给出了daringfireball.net/2010/07/improved_regex_for_matching_urls网站。以下代码展示了如何修改我们的 PHP 代码，以便在 Sentiment140 数据集中使用该模式进行 URL 提取：

<?php
// connect to db
$dbc = mysqli_connect('localhost', 'username', 'password', 'sentiment140')
    or die('Error connecting to database!' . mysqli_error());
$dbc->set_charset("utf8");

// pull out the tweets
$select_query = "SELECT id, tweet_text FROM sentiment140";
$select_result = mysqli_query($dbc, $select_query);

// die if the query failed
if (!$select_result)
    die ("SELECT failed! [$select_query]" .  mysqli_error());

// pull out the URLS, if any
$urls = array();
$pattern  = '#\b(([\w-]+://?|www[.])[^\s()<>]+(?:\([\w\d]+\)|([^[:punct:]\s]|/)))#';

while($row = mysqli_fetch_array($select_result))
{
    echo "<br/>working on tweet id: " . $row["id"];
    if (preg_match_all(
        $pattern,
        $row["tweet_text"],
        $urls
    ))
    {
        foreach ($urls[0] as $name)
        {
            echo "<br/>----url: ".$name;
            $insert_query = "INSERT into sentiment140_urls (id, tweet_id, url)
                VALUES (NULL," . $row["id"] . ",'$name')";
            echo "<br />$insert_query";
            $insert_result = mysqli_query($dbc, $insert_query);
            // die if the query failed
            if (!$insert_result)
                die ("INSERT failed! [$insert_query]" .mysqli_error());
        }
    }
}
?>

这个程序几乎与我们之前编写的提及提取程序相同，只有两个不同点。首先，我们将正则表达式模式存储在一个名为 $pattern 的变量中，因为它较长且复杂。其次，我们对数据库的 INSERT 命令做了小的修改，就像我们在话题标签提取时做的那样。

正则表达式模式的逐行解释可以在原始网站上找到，但简短的解释是，所示的模式将匹配任何 URL 协议，如 http:// 或 file://，它还尝试匹配有效的域名模式以及几级深度的目录/文件模式。如果你想查看它匹配的多种模式以及一些肯定不会匹配的已知模式，源网站也提供了自己的测试数据集。

第八步——清理查找表

在第七步——分离用户提及、话题标签和 URL部分，我们创建了新的表格来存储提取的标签、用户提及和 URL，然后提供了一种方法，通过id列将每一行与原始表格关联起来。我们按照数据库规范化的规则，通过创建新的表格来表示推文与用户提及、推文与话题标签、推文与 URL 之间的多对一关系。在这一步中，我们将继续优化此表格的性能和效率。

我们现在关心的列是query_phrase列。查看该列数据，我们可以看到同样的短语反复出现。这些显然是最初用于定位和选择现在存在于数据集中的推文的搜索短语。在sentiment140表中的 498 条推文中，查询短语有多少次被反复使用？我们可以通过以下 SQL 来检测这一点：

SELECT count(DISTINCT query_phrase)
FROM sentiment140;

查询结果显示，只有 80 个不同的查询短语，但它们在 498 行数据中反复出现。

这在 498 行数据的表中可能看起来不算问题，但如果我们有一个非常大的表，比如包含数亿行的表，我们就需要关注这个列的两个问题。首先，重复这些字符串占用了数据库中不必要的空间；其次，查找不同的字符串值会非常慢。

为了解决这个问题，我们将创建一个查找表来存储查询值。每个查询字符串只会在这个新表中出现一次，我们还会为每个查询字符串创建一个 ID 号。接下来，我们将修改原始表，使用这些新的数字值，而不是目前使用的字符串值。我们的操作流程如下：

  1. 创建一个新的查找表：

    CREATE TABLE sentiment140_queries (
  query_id int(11) NOT NULL AUTO_INCREMENT,
  query_phrase varchar(25) NOT NULL,
  PRIMARY KEY (query_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

  2. 用不同的查询短语填充查找表，并自动为每个短语分配一个query_id编号：

    INSERT INTO sentiment140_queries (query_phrase)
SELECT DISTINCT query_phrase FROM sentiment140;

  3. 在原始表中创建一个新列，用于存储查询短语编号：

    ALTER TABLE sentiment140
ADD query_id INT NOT NULL AFTER query_phrase;

  4. 在下一步操作出错的情况下，备份sentiment140表。每次对表执行UPDATE操作时，最好先进行备份。我们可以使用像 phpMyAdmin 这样的工具轻松复制表（使用操作标签）。或者，我们可以重新创建一份表的副本，并将原始表中的行导入到副本中，如下所示的 SQL：

    CREATE TABLE sentiment140_backup(
  polarity int(1) DEFAULT NULL,
  id int(5)NOT NULL,
  date_of_tweet varchar(30) CHARACTER SET utf8 DEFAULT NULL ,
  date_clean datetime DEFAULT NULL COMMENT 'holds clean, formatted date_of_tweet',
  query_id int(11) NOT  NULL,
  user varchar(25) CHARACTER SET utf8 DEFAULT NULL,
  tweet_text varchar(144) CHARACTER SET utf8 DEFAULT NULL ,
  PRIMARY KEY (id)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
INSERT INTO sentiment140_backup SELECT * FROM sentiment140;

  5. 用正确的数字填充新列。为此，我们通过它们的文本列将两个表连接起来，然后从查找表中查找正确的数字值，并将其插入到sentiment140表中。在以下查询中，每个表都被赋予了别名s和sq：

    UPDATE sentiment140 s
INNER JOIN sentiment140_queries sq
ON s.query_phrase = sq.query_phrase
SET s.query_id = sq.query_id;

  6. 删除sentiment140表中的旧query_phrase列：

    ALTER TABLE sentiment140
DROP query_phrase;

到目前为止，我们已经找到了一种有效的方法来创建短语列表，具体如下。这些短语按字母顺序排列：

SELECT query_phrase
FROM sentiment140_queries
ORDER BY 1;

我们还可以通过对这两个表进行连接，查找包含给定短语（如baseball）的推文：

SELECT s.id, s.tweet_text, sq.query_phrase
FROM sentiment140 s
INNER JOIN sentiment140_queries sq
  ON s.query_id = sq.query_id
WHERE sq.query_phrase = 'baseball';

到此为止，我们已经清理了sentiment140表，并创建了四个新表来存储各种提取和清理后的值，包括话题标签、用户提及、网址和查询短语。我们的tweet_text和date_clean列已经清理干净，并且我们已经有了一个查询短语的查找表。

第九步 – 记录你所做的工作

由于有九个清理步骤，并且使用了多种语言和工具，毫无疑问我们在某个环节会犯错并需要重复某个步骤。如果我们需要向别人描述我们做了什么，我们几乎肯定会记不清楚确切的步骤以及每个步骤背后的原因。

为了避免过程中出现错误，我们必须保持清理步骤的日志记录。至少，日志应按照执行顺序包含以下内容：

  • 每个 SQL 语句

  • 每个 Excel 函数或文本编辑器的操作流程，包括必要时的截图

  • 每个脚本

  • 每一步的备注和评论，解释为什么做每一件事

另一个优秀的建议是，在每个阶段都创建表的备份，例如，我们在对sentiment140表执行UPDATE操作之前就创建了备份，我们还讨论了在创建新的date_clean列之后进行备份。备份操作很简单，如果以后决定不需要备份表，可以随时删除它。

总结

在这一章中，我们使用了一个示例数据集——一个名为 Sentiment140 的推文集合，学习如何在关系数据库管理系统中清理和处理数据。我们在 Excel 中进行了几项基础清理操作，然后我们回顾了如何将数据从 CSV 文件导入到数据库中。在此之后，剩余的清理操作是在 RDBMS 内部进行的。我们学习了如何将字符串转换为正确的日期格式，然后我们提取了推文文本中的三种数据，并最终将这些提取的值移至新的干净表格中。接下来，我们学习了如何创建一个查找表，存储当前效率低下的数值，这样我们就能用高效的数字查找值更新原表。最后，由于我们执行了很多步骤，并且总有可能出现错误或关于我们所做操作的沟通不清晰，我们回顾了一些记录清理过程的策略。

在下一章中，我们将改变视角，专注于为他人准备已清理的数据，而不是清理我们已经得到的数据。我们将学习一些最佳实践，创建需要他人最少清理的 数据集。

第八章 最佳实践：共享你的清洗数据

到目前为止，在本书中，我们已经学习了许多不同的方法来清洗和组织数据集。也许现在是时候考虑让别人使用我们清洗过的数据了。本章的目标是提供一些最佳实践，以便邀请朋友进入你的数据科学厨房。共享数据可能意味着将其提供给其他人、其他团队，甚至是你未来的某个版本。那么，如何将数据包装好，以供他人使用？你应该如何告诉别人你已经清洗好的数据？如何确保你所有的辛勤工作都归功于你？

在本章中，我们将学习：

  • 如何展示和包装你的清洗数据

  • 如何为你的数据提供清晰的文档说明

  • 如何通过授权你的清洗数据来保护并扩展你的辛勤工作

  • 如何找到并评估公开清洗数据的选项

在开始本章之前，我们应该明确指出，我们只能清洗并共享我们有权共享的数据。也许这听起来很显而易见，但值得重复一遍。本章假设你所清洗并随后共享的数据，实际上是你有权以这种方式处理的数据。如果你对此有疑问，请阅读本章中的设置数据的条款和许可部分，确保你遵循你希望你的用户遵循的相同准则。

准备一个清晰的数据包

在本节中，我们深入探讨了在发布数据包供大众使用之前需要回答的许多重要问题。

你希望人们如何访问你的数据？如果数据在数据库中，你希望用户能够登录并执行 SQL 命令吗？还是希望创建可以下载的纯文本文件供他们使用？你是否需要为数据创建 API？反正你到底有多少数据，是否希望对数据的不同部分设置不同的访问权限？

如何分享清洗后的数据的技术方面非常重要。一般来说，最好从简单的方式开始，必要时再采用更复杂的分发计划。以下是一些数据分发选项，按从最简单到最复杂的顺序排列。当然，随着复杂度的增加，也会带来更大的好处：

  • 压缩纯文本 – 这是一种非常低风险的分发方式。正如我们在第二章中学到的，基础知识 - 格式、类型和编码，纯文本可以被压缩成非常小的文件大小。一个简单的 CSV 或 JSON 文件是通用的，可以轻松转换为许多其他格式。需要考虑的事项包括：

    • 你将如何让用户下载文件？在网页上提供开放链接非常简单且方便，但它不允许你要求凭证（如用户名和密码）来访问文件。如果这对你很重要，那么你需要考虑其他分发文件的方法；例如，通过使用带有用户名和密码的 FTP 服务器，或通过使用你 web 服务器的访问控制。

    • 你的文件有多大？你预期的流量有多少？你的托管服务商允许多少流量，超出部分会开始收费？

  • 压缩 SQL 文件 — 分发 SQL 文件可以让你的用户在自己的系统上重建你的数据库结构和数据。需要考虑的一些因素包括：

    • 你的用户可能使用的数据库系统与你不同，因此他们无论如何都需要清理数据。直接给他们纯文本可能更高效。

    • 你的数据库系统可能与他们的服务器设置不同，因此你需要提前澄清这些自定义设置。

    • 你还需要提前规划是否你的数据集设计为随着时间增长，例如，决定你是否只提供 UPDATE 语句，还是始终提供足够的 CREATE 和 INSERT 语句来重新创建整个数据库。

  • 实时数据库访问 — 提供实时访问你的数据库是一种很好的方式，可以让用户在较低层次上与数据互动。需要考虑的一些因素包括：

    • 提供实时访问要求你为每个用户设置单独的用户名和密码，这意味着需要跟踪用户。

    • 因为你有可识别的用户，你需要提供一种方式来与他们沟通支持问题，包括丢失凭证以及如何使用系统。

    • 除非你已经为数据库构建了一个安全的前端，并采取了基本的预防措施，比如限制用户执行查询的次数和查询执行的时间，否则允许通用用户名和密码可能不是一个好主意。一个不当的 OUTER JOIN 查询，尤其是对一个有几千 TB 数据的表，可能会使你的数据库崩溃，影响其他用户。

    • 你是否希望用户能够构建访问你数据的程序，例如，通过 ODBC 或 JDBC 中间件层？如果是这样，你需要在规划访问权限和配置服务器时考虑到这一点。

  • API — 设计一个应用程序编程接口（API）来访问你的数据，将允许你的最终用户编写自己的程序，能够以可预测的方式访问数据并获取结果集。API 的优点是，它将在互联网上提供以已知的、受限的方式访问数据的途径，并且用户无需解析数据文件或处理将数据从一种格式转换到另一种格式的任务。需要考虑的一些因素包括：

    • 构建一个良好的 API 在前期投入上比其他选项更昂贵；然而，如果你有很多需要支持的用户且支持人员有限，那么从长远来看，构建 API 可能会帮你节省资金。

    • 使用 API 要求用户具备比其他方法更高的技术知识。你应该准备好详细的文档，并附带示例。

    • 你需要有一个认证和安全计划来追踪谁被允许访问你的数据以及他们可以对数据进行哪些操作。如果你计划设立多个访问层级，例如，将数据的不同层次进行货币化，那么诸如用户从一个层次转到另一个层次等问题需要提前进行清晰的规划。

    • 就像常规的数据库访问一样，用户滥用或误用 API 是始终可能发生的情况。你需要提前规划并采取预防措施，发现并移除那些可能通过故意或无意的误用，使得服务无法访问的恶意或不小心的用户。

选择分发方法会受到你的预算（包括资金和时间）以及用户期望的影响。我能给出的最好建议是：我在遵循开源软件座右铭“尽早发布，经常发布”时，取得了不错的成果。这对我有用，因为我有一个小的用户群，有限的预算，并且没有太多闲暇时间来应对那些可能有效也可能无效的复杂打包计划。

提醒一句——使用 GitHub 分发数据

GitHub 是一个基于云的文件存储库，旨在帮助软件开发人员协作开发软件并托管他们的代码以供他人下载。它的受欢迎程度急剧上升，目前托管了超过 1600 万个项目仓库。正因为如此，我与许多数据科学家交流时，他们都建议将数据存储在 GitHub 上。

不幸的是，GitHub 在存储非代码数据方面存在一些局限性，尽管它在技术人员中非常流行且易于使用，但你仍应注意其一些政策，这些政策可能会影响你的数据。相关政策已在帮助指南中列出，链接为help.github.com/articles/what-is-my-disk-quota，但我们已在这里总结了其中的重要内容：

  • 首先，GitHub 是对源代码控制系统 Git 的封装，而该系统并不适合存储 SQL。帮助指南中写道，“大型 SQL 文件与 Git 等版本控制系统兼容性差。”我不确定“兼容性差”具体指什么，但我确信在用户体验至关重要的情况下，我一定要避免遇到这种问题。

  • 其次，GitHub 对文件大小有一些严格的限制。每个项目（仓库）限制为 1GB，每个文件限制为 100MB。我发布的大多数数据文件作为个人文件时都小于这个限制，但由于我每年多次发布许多时间序列文件，我不得不为它们创建多个仓库。在这种方案下，每次我发布新文件时，都需要评估是否会超出文件大小限制。这很快就变成了一个大麻烦。

简而言之，GitHub 本身推荐使用一种网络托管解决方案来分发文件，特别是当文件较大或面向数据库时。如果你决定在 GitHub 上托管，务必小心发布包含用户凭据的文件。这包括发布你数据库系统的用户名和密码、Twitter 的认证密钥和秘密，或其他任何个人信息。由于 GitHub 本质上是一个 Git 仓库，这些错误将永远存在，除非整个仓库被删除。如果你发现自己犯了错误，将个人信息发布到了 GitHub，必须立即取消当前账户的认证，并重新创建所有密钥和密码。

文档化你的数据

一旦人们获得了数据，理想情况下，即使是在之前，他们也需要了解自己所得到的是什么。文档化数据可能对你来说只是事后想起来的事情，但对于你的用户来说极其重要，因为他们对数据和你做的所有处理并不熟悉。在本节中，我们将回顾一些可以添加到数据包中的内容，以帮助理解数据。

README 文件

简单的 README 文件在计算机领域有着悠久的历史。它只是一个与软件包一起分发的文本文件，或者位于包含其他文件的目录中，目的是让用户在开始使用其余的软件包或文件之前，先阅读 README 文件。README 文件会告诉用户有关软件包的重要信息，如作者和创作原因、安装说明、已知的错误和使用文件的其他基本说明。

如果你正在构建数据包，例如，包含文本或 SQL 文件的压缩文件，快速且简单的方法是在压缩前为文件包添加一个 README 文件。如果你正在为文件创建一个网站或在线目录，在显眼的位置添加一个 README 文件会非常有帮助。以下截图展示了我用来分发我参与的项目 FLOSSmole 文件的一个网站目录。我添加了一个 README 目录，以便将所有需要用户首先阅读的文件放入其中。我将该目录名以一个下划线开头，这样它将始终按字母顺序排在列表的顶部：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00297.jpeg

网站上显示 README 文件的文件目录，README 文件位于顶部。

在README.txt文件中，我给用户提供了关于文件的通用和具体说明。以下是我为这个目录中的数据提供的README文件示例：

README for http://flossdata.syr.edu/data directory 

What is this place?
This is a repository of flat files or data "dumps", from the FLOSSmole project.

What is FLOSSmole? 
Since 2004, FLOSSmole aims to:
    --freely provide data about free, libre, and open source software (FLOSS) projects in multiple formats for anyone to download;
    --integrate donated data & scripts from other research teams;
    --provide a community for researchers to discuss public data about FLOSS development.

FLOSSmole contains: Several terabytes (TB) of data covering the period 2004-now, and growing with data sets from nearly 10,000 web-based collection operations, and growing each month. This includes data for millions of open source projects and their developers.

If you use FLOSSmole data, please cite it accordingly:
Howison, J., Conklin, M., & Crowston, K. (2006). FLOSSmole: A collaborative repository for FLOSS research data and analyses. International Journal of Information Technology and Web Engineering, 1(3), 17–26.

What is included on this site? 
Flat files, date and time-stamped, from various software forges & projects. We have a lot of other data in our database that is not available here in flat files. For example, IRC logs and email from various projects. For those, see the following:

1\. Direct database access. Please use this link for direct access to our MySQL database: http://flossmole.org/content/direct-db-access-flossmole-collection-available

2\. FLOSSmole web site. Includes updates, visualizations, and examples. http://flossmole.org/

这个示例README文件适用于整个目录的文件，但你也可以为每个文件或不同的目录准备一个README文件。这完全取决于你。

文件头部

另一种有效的向用户传达信息的方式，特别是当你创建的是平面文本文件或 SQL 命令时，是在每个文件的顶部放置一个头部，解释其格式和使用方式。一种常见做法是为每一行头部加上某种类似注释的字符，如#或//。

文件头部中常包含的一些内容有：

  • 文件名和文件所在的包的名称

  • 参与创建此文件的人员姓名、他们的组织和所在地

  • 文件发布的日期

  • 文件的版本号，或者在哪里可以找到该文件的早期版本

  • 文件的目的

  • 数据的来源地，以及数据自那时以来所做的任何更改

  • 文件的格式及其组织方式，例如，列出字段及其含义

  • 文件的使用条款或许可

以下示例显示了我某个数据项目中分发的 TSV 文件的头部示例。在其中，我解释了数据的内容以及如何解释文件中的每一列。我还解释了如何引用数据以及如何分享数据的政策。我们将在本章后面讨论授权和分享的选项：

# Author: Squire, M. & Gazda, R.
# License: Open Database License 1.0
# This data 2012LTinsultsLKML.tsv.txt is made available under the 
# Open Database License: http://opendatacommons.org/licenses/ 
# odbl/1.0/. 
#
# filename: 2012LTinsultsLKML.tsv.txt
# explanation: This data set is part of a larger group of data 
# sets described in the paper below, and hosted on the 
# FLOSSmole.org site. Contains insults gleaned from messages sent 
# to the LKML mailing list by Linus Torvalds during the year 2012
#
# explanation of fields:
# date: this is the date the original email was sent
# markmail permalink: this is a permalink to the email on markmail 
# (for easy reading)
# type: this is our code for what type of insult this is
# mail excerpt: this is the fragment of the email containing the 
# insult(s). Ellipses (...) have been added where necessary.
#
# Please cite the paper and FLOSSmole as follows:
#
# Squire, M. & Gazda, R. (2015). FLOSS as a source for profanity 
# and insults: Collecting the data. In Proceedings of 48th 
# Hawai'i International Conference on System Sciences (HICSS-48).
# IEEE. Hawaii, USA. 5290-5298
#
# Howison, J., Conklin, M., & Crowston, K. (2006). FLOSSmole: A 
# collaborative repository for FLOSS research data and analyses. 
# International Journal of Information Technology and Web 
# Engineering, 1(3), 17–26.

如果你预期用户将定期收集你的数据文件，你应该在文件头部使用一致的注释字符。在前面的示例中，我使用了#字符。这样做的原因是，用户可能会编写程序来自动下载和解析你的数据，或许将其加载到数据库中，或在程序中使用。你一致使用注释字符将使用户能够跳过头部内容，不进行处理。

数据模型和图表

如果你分发的是用于构建数据库的 SQL 文件，或者你提供了用于查询的数据库的实时访问，可能会发现一个可视化图表，如实体-关系图（ERD），将极大地帮助用户。

在我的一些项目中，我喜欢同时提供表格的文字描述，例如前面描述的头部和README文件，同时也提供表格及其之间关系的可视化图表。因为我分发的数据库非常庞大，我还会对我的图表进行着色，并注释图表的每个部分，以指示该部分数据库中的内容。

以下截图展示了我的一个大型图表的高层次概览。它被缩小，以显示实体-关系图（ERD）的大小：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00298.jpeg

由于这个 ERD（实体关系图）有点复杂且难以阅读，即使是在大屏幕上，我已将数据库的每个独立部分进行了着色，并在需要的地方提供了注释。下面是从大图的左上角放大后的橙色部分截图：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00299.jpeg

数据库某一部分的特写图，包括描述表格目的的注释。

通过查看这个图表，用户能够清晰地了解数据库不同部分是如何组合在一起的。重要的是，高层次的注释直接显示在图表上，当用户需要关于某个特定字段的详细信息时，他们可以参考 README 文件或该文件中的头部部分。

要创建 ERD，你可以使用任何数据库管理系统（RDBMS）工具，包括 MySQL Workbench（这是我用来创建你所看到的着色版本的工具）。其他流行的工具包括 Microsoft Visio、Sparx Enterprise Architect 和 draw.io。许多这些工具允许你连接到 RDBMS 并从现有数据库逆向生成图表，或者从图纸中正向生成 SQL 语句。在任何一种情况下，ERD 都能帮助你的用户更好地理解数据模型。

文档维基或内容管理系统（CMS）

另一种组织项目文档的方法是将其发布到维基或内容管理系统（CMS）中。有数百种 CMS 和维基软件可供选择，常见的选项包括 MediaWiki、WordPress、Joomla!和 Drupal。GitHub 也为托管在其上的项目提供了维基服务，其他一些软件托管服务，如 Sourceforge 和 Bitbucket，也提供类似的服务。

你可以使用 CMS 或维基提供文件的下载链接，并可以通过 CMS 发布文档和解释。我在自己的工作中也使用了 CMS 来托管更新日志、展示示例图表的可视化内容、使用数据构建的图表，以及一个供用户使用的脚本库。

以下是大多数面向数据的项目在文档 CMS 或维基中包括的一些常见部分：

  • 关于项目 — 这一部分告诉用户数据项目的目的以及如何联系项目负责人。此部分还可能包括如何参与、如何加入邮件列表或讨论区，或如何联系项目负责人等信息。

  • 获取数据 — 这一部分解释了访问数据的不同方式。常见的选择包括直接数据库访问、文件下载或 API。这一部分还解释了任何特殊的注册或登录程序。

  • 使用数据 — 这包括启动查询、使用示例、基于数据构建的图形、图表和实体关系图（ERD）。它提供了其他人使用这些数据所做的事情的链接。本节还会再次说明，如果有必要的话，您对数据引用的期望和任何许可政策。

在本节中，我们讨论了记录数据的各种方式，包括 README 文件、文件头、实体关系图（ERD）和基于 Web 的解决方案。在这个讨论中，我们提到了许可数据的概念，并说明了您对数据引用和共享的期望。在下一节中，我们将深入探讨许可数据集的具体内容。

设置数据的条款和许可

数据分发计划的重要组成部分是确定您希望用户如何引用、共享或重混您的数据的期望。您希望用户使用数据的期望清单被称为数据的使用条款（ToU）。使用条款还可能赋予用户某些特定权利，例如修改或再分发数据的能力。您授予用户的这些权利集合被称为数据许可。用户可以根据是否同意遵守使用条款来选择是否使用您的数据。用户也可以根据他们想要对数据进行的操作是否被许可来决定是否使用数据。

在本节中，我们将概述一些您可以做出的选择，以设定用户与您的数据互动的期望。我们还将回顾一些您可能希望在使用条款中包含的常见事项，以及一些可以应用于您的数据集的常见预设许可。

常见的使用条款

不是每个人在共享数据时都有相同的目标。例如，我参与的一个项目的具体目标是为科学界收集、清理并重新分发数据。因为我是大学教授，我的部分工作责任是发表对他人有用的学术研究论文、软件和数据集。因此，对我来说，确保人们在使用我的论文和发布的数据集时引用它们非常重要。然而，我的另一位朋友并非学术界人士，他经常匿名发布数据集，并且不要求在使用这些数据时进行引用或通知。

下面是设置数据使用期望时的一些常见考虑事项：

  • 引用 — 您是否希望基于您的数据发布内容的人明确说明他们获得数据的来源？如果是，应该使用什么网址或引用？

  • 隐私——你是否有关于保护用户或其信息隐私的规则？你是否希望用户遵守任何特定的隐私指南或研究指南？例如，有些人要求用户遵循类似于他们自己所遵循的机构研究委员会（IRB）或其他研究伦理团体（例如，互联网研究者协会（AOIR））的程序。

  • 数据的适当使用——你是否怀疑你的数据集可能以某种方式被滥用？数据是否可能被断章取义？它的内容是否可能与其他数据集结合，从而造成伤害？对于某些项目，设定用户如何使用你提供的数据的期望将是一个非常好的主意。

  • 联系方式——你是否有特定的方式希望数据用户在使用数据时通知你？他们是否需要通知你？如果你预期用户可能对数据有疑问或关切，提供关于如何以及为什么联系你（作为数据集提供者）的指南会很有帮助。

正如我们在本章的文档化数据部分讨论的那样，数据集的使用条款（ToU）可以通过 README 文件、文件头或网站提供给潜在用户。如果你提供实时数据库访问，你也可以通知潜在用户，在接受数据库系统的用户名和密码时，他们即表示同意遵守你的条款。对于 API 访问，也可以使用类似的结构，用户通过主动使用身份验证令牌或访问凭证来表示同意你的 ToU。

当然，所有这些最佳实践都受到各个国际国家和组织的法律和政策的约束。如果没有一点帮助，尝试做到这一点可能非常复杂。为了帮助数据提供者设定用户期望，随着时间的推移，已经出现了一些通用的许可方案。我们现在将讨论其中的两种：

创意共享

创意共享（CC）许可证是预先打包的通用规则集，版权或可版权材料的提供者可以将其应用于他们的作品。这些许可证规定了作品使用者可以做什么。通过提前声明许可证，作品所有者可以避免需要单独授予每个希望修改或重新分发特定作品的人的许可证。

关于 CC 许可证的问题——这可能对你来说不是问题，取决于你打算如何使用它——是 CC 许可证旨在应用于具有版权的作品。你的数据库或数据集可否享有版权？你是否打算授权数据库的内容，还是整个数据库？为了帮助你回答这个问题，我们将引导你访问 Creative Commons 的 Wiki，这里比我们在此能做的更详细地解答了这个问题。该页面甚至有一个专门关于数据和数据库的常见问题部分：wiki.creativecommons.org/Data。

ODbL 和开放数据共同体

另一个不错的数据授权选择是开放数据库许可证（ODbL）。这是专为数据库设计的许可证。开放知识基金会（OKF）创建了一个两分钟的指南，帮助你决定如何开放数据，你可以在这里找到：OpenDataCommons.org/guide/。

如果你需要更多的选择，OpenDefinition.org 网站，作为 OKF 的一部分，提供了更多预打包许可证，你可以将它们应用于你的数据集。这些许可证涵盖从非常开放的公共领域式许可证，到要求署名和共享衍生作品的许可证。此外，他们还提供了一本《开放数据手册》，这本手册在帮助你理清数据库或数据集中的知识产权问题，以及你希望如何处理这些数据方面非常有帮助。你可以在这里下载或在线浏览《开放数据手册》：OpenDataHandbook.org。

宣传你的数据

一旦你拥有了完整的数据包，就该向全世界展示它了。宣传你的数据将确保尽可能多的人使用它。如果你已经有了特定的用户群体，宣传它可能只需要在邮件列表或特定的研究小组中发送一个 URL。但有时候，我们会创建一个数据集，认为它可能对更大、更不明确的群体感兴趣。

数据集列表

网络上有许多数据集列表，大多数是围绕某种主题组织的。这些元集合（集合的集合）的发布者通常非常乐意列出适合其细分领域的新数据来源。元集合的主题可以包括：

  • 与相同主题相关的数据集，例如，音乐数据、生物数据或关于新闻报道的文章集合

  • 解决同一类问题相关的数据集，例如，能够用于开发推荐系统或训练机器学习分类器的数据集

  • 与特定技术问题相关的数据集，例如，旨在基准测试或测试特定软件或硬件设计的数据集

  • 针对特定系统使用而设计的数据集，例如，针对学习编程语言（如 R）、数据可视化服务（如 Tableau）或基于云的平台（如 Amazon Web Services）优化的数据集。

  • 所有数据集都拥有相同类型的许可证，例如，仅列出公共领域数据集或仅列出已批准用于学术研究的数据集。

如果你发现你的数据集在这些列表中没有得到很好的展示，或者不符合现有的元集合要求，另一个选择是创建你自己的数据仓库。

Stack Exchange 上的开放数据

Stack Exchange 上的开放数据区，网址是opendata.stackexchange.com，是一个与开放数据集相关的问答集合。我曾在这里找到过许多有趣的数据集，有时候我也能向其他人展示如何使用我自己的数据集回答问题。这个问答网站也是一个很好的方式，帮助你了解人们有什么样的问题，以及他们希望使用的数据格式。

在 Stack Exchange 上将你的数据宣传为解决某个问题的方案之前，请确保你的访问方法、文档和许可证符合标准，参考我们在本章之前讨论的指南。这在 Stack Exchange 上尤其重要，因为无论是问题还是答案都可能被用户投下反对票。你最不想做的事情，就是用一堆断开的链接和混乱的文档来宣传你的数据。

黑客马拉松

让人们参与到你的数据中来，另一个有趣的方式是将其作为一个可用的数据集来宣传，适用于黑客马拉松。数据黑客马拉松通常是一天或多天的活动，程序员和数据科学家汇聚一堂，实践不同的技术，或者利用数据解决某类特定的问题。

一个简单的搜索引擎查询“数据黑客马拉松”可以帮助你了解当前黑客马拉松的关注重点。部分黑客马拉松由公司赞助，另一些则是为回应社会问题而举办的。大多数黑客马拉松都有维基或其他方法，允许你将你的网址和数据集的简要描述添加到可以在活动当天使用的数据集列表中。我不太推荐某一个具体的黑客马拉松，因为黑客马拉松本质上是一次性举办的活动，之后它们会发生变化，转变成其他形式。它们通常在不规则的时间举办，并由临时组织的团队进行安排。

如果你的数据集是为学术目的设计的，例如，如果它是一个研究数据集，你可以考虑在学术会议的工作坊或海报展示环节举办自己的黑客马拉松。这是让人们参与到数据操作中的一个绝佳方式，至少，你可以从会议上的人们那里获得一些有价值的反馈，了解如何改进你的数据，或者他们认为你应该下一个构建什么样的数据集。

总结

在本章中，我们探讨了多种分享我们清洗后数据的可能性。我们讨论了不同数据包装和分发方式的各种解决方案和权衡。我们还回顾了提供文档的基础知识，包括用户需要知道的最重要内容以及如何在文档文件中传达这些内容。我们注意到，许可证和使用条款几乎总是在文档中出现，但它们是什么意思，您应该如何为您的数据选择一个合适的许可证？我们回顾了一些数据项目的常见使用条款，以及最常见的许可方案：创作共用（Creative Commons）和开放数据库许可证（ODbL）。最后，我们集思广益，提出了几种宣传您数据的方式，包括数据元集合、开放数据堆栈交换网站和以数据为中心的黑客马拉松。

在本书的这一部分，您已经看到了数据清洗的完整的从头到尾的概述。接下来的两章将包含更长、更详细的项目，带给您更多关于数据清洗任务的实际体验，使用我们在本书前面部分学到的技能。

第九章 Stack Overflow 项目

这是两个完整的、章节级的项目中的第一个，我们将在其中实践所有关于数据清理的知识。我们可以把每个项目看作是一场晚宴，在这场晚宴上，我们展示自己在数据科学厨房中的最佳技能。要举办一场成功的晚宴，当然需要提前规划好菜单和嘉宾名单。然而，真正的专家标志是我们如何应对那些事情不完全按照计划进行的时刻。我们每个人都曾经历过这样的时刻：尽管我们精心准备了食谱和购物清单，但还是忘记购买一个重要的食材。我们能否调整计划，面对途中遇到的新挑战？

在本章中，我们将使用公开发布的 Stack Overflow 数据库转储进行一些数据清理。Stack Overflow 是 Stack Exchange 问答网站家族的一部分。在这些网站上，编写优秀的问题和答案可以为用户赢得积分和徽章，这些积分和徽章会随着时间积累。为了练习我们的数据清理技能，我们将使用我们在第一章中介绍的相同的六步方法，为什么你需要清洁数据？。

  • 决定我们要解决的是什么问题——我们为什么要看这些数据？

  • 收集和存储我们的数据，包括下载并提取由 Stack Overflow 提供的数据转储，创建一个 MySQL 数据库来存储数据，并编写脚本将数据导入 MySQL 数据库。由于 Stack Overflow 数据集庞大，我们还将创建一些较小的测试表，填充随机选择的行。

  • 在尝试清理整个数据集之前，先对测试表执行一些试验性的清理任务。

  • 分析数据。我们是否需要进行计算？我们是否应该编写一些聚合函数来计数或求和数据？我们是否需要以某种方式转换数据？

  • 如果可能的话，提供数据的可视化。

  • 解决我们最初要调查的问题。我们的过程是否有效？我们成功了吗？

这需要大量的工作，但我们提前准备得越充分，开始得越早，我们就越有可能将我们的数据科学晚宴称为成功。

第一步——提出关于 Stack Overflow 的问题

为了开始我们的项目，我们需要提出一个合理有趣的问题，需要一些简单的数据分析来回答。我们该从哪里开始？首先，让我们回顾一下我们对 Stack Overflow 的了解。我们知道它是一个程序员的问答网站，我们可以假设程序员在提问和回答时可能会使用大量的源代码、错误日志和配置文件。此外，我们知道，有时在像 Stack Overflow 这样的基于 Web 的平台上发布这些长文本转储会因为行长、格式和其他可读性问题而显得很尴尬。

看到那么多包含大量文本的提问和回答让我不禁想知道，Stack Overflow 上的程序员是否会通过外部粘贴站点（例如 www.Pastebin.com）链接到他们的代码或日志文件。Pastebin 是一个可以粘贴大量文本的网站，如源代码或日志文件，网站会返回一个短链接，供你与他人分享。大多数粘贴站点也支持源代码语法高亮，而 Stack Overflow 默认不支持这一功能。

粘贴站点在 IRC 和电子邮件中非常常见，但在 Stack Overflow 上呢？一方面，就像在 IRC 或电子邮件中一样，提供一个链接可以使问题或回答更简洁，从而使其余的内容更容易阅读。但另一方面，根据使用的粘贴站点，URL 不一定能永远有效。这意味着，随着时间的推移，问题或回答可能会因为链接腐烂而失去价值。

像 JSFiddle 这样的工具在某些方面使这个问题变得更加复杂。在一个互动粘贴站点（如 JSFiddle）上，你不仅可以粘贴源代码并获得一个 URL，还可以允许他人在浏览器中编辑并运行代码。这在 Stack Overflow 的问答场景中非常有帮助，尤其是在像 JavaScript 这样的基于浏览器的语言中。然而，链接腐烂的问题依然存在。此外，对于初学者来说，JSFiddle 比像 Pastebin 这样简单的代码粘贴站点要稍微复杂一些。

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00300.jpeg

JSFiddle 有四个窗口，分别用于 HTML、CSS、JavaScript 和结果展示。

注意

在 Stack Overflow 的社区讨论区里，关于是否应该使用粘贴站点，尤其是对于只包含粘贴站点链接而没有实际代码的提问或回答，展开了相当多的争论。总的来说，尽管人们普遍认为粘贴站点很有用，但他们也认识到保护 Stack Overflow 自身的长期性和实用性至关重要。社区决定避免发布仅包含链接而没有代码的提问或回答。如果你想回顾这场讨论，好的起点是这个链接：meta.stackexchange.com/questions/149890/。

对于我们在这里的讨论，我们不需要站队。在这个辩论中，我们可以提出一些简单的基于数据的问题，例如：

  1. 人们在 Stack Overflow 上使用像 Pastebin 和 JSFiddle（以及其他类似的粘贴站点）的频率有多高？

  2. 他们在提问中还是回答中更常使用粘贴站点？

  3. 引用粘贴站点 URL 的帖子通常会包含源代码吗？如果包含，通常是多少？

我们可以将这些问题作为动机，收集、存储和清理我们的 Stack Overflow 数据。即使结果是其中的一些问题太难或无法回答，记住我们的总体目标将有助于指导我们需要进行的清理类型。将问题牢记在心可以防止我们偏离轨道，避免执行最终会变得毫无意义或浪费时间的任务。

第二步——收集和存储 Stack Overflow 数据

写作时，Stack Exchange 提供了他们所有网站（包括 Stack Overflow）的数据——以 XML 文件的形式，任何人都可以免费下载。在本节中，我们将下载 Stack Overflow 文件，并将数据导入到我们 MySQL 服务器的数据库中。最后，我们将创建这些表的几个小版本进行测试。

下载 Stack Overflow 数据转储

Stack Exchange 上的所有数据可以从互联网档案馆下载。2014 年 9 月的转储是写作时最新的版本。每个 Stack Exchange 网站都有一个或多个与之相关的文件，每个文件都链接到该详细信息页面：archive.org/details/stackexchange。

我们只关心按字母顺序排列的八个 Stack Overflow 文件，具体如下所示：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00301.jpeg

Archive.org 列出显示我们感兴趣的八个 Stack Overflow 文件。

对于列表中的每个文件，右键点击链接，并指示你的浏览器将文件保存到磁盘。

解压文件

请注意，每个文件都具有 .7z 扩展名。这是一种压缩归档格式。可以使用匹配的 7-Zip 软件或其他兼容的软件包进行解压缩和解档。7-Zip 不是我们在第二章中讨论的最常见的文件归档工具，基础 - 格式、类型与编码，而且你可能电脑上还没有安装兼容的解压软件，因此我们可以把它当作第一个小问题，需要绕过。尝试双击文件以打开它，但如果你没有安装与 .7z 扩展名相关联的软件，你将需要安装一个适当的 7-Zip 解压工具。

  • 对于 Windows，你可以从他们的网站下载 7-Zip 软件：www.7-zip.org

  • 对于 Mac OS X，你可以下载并安装 The Unarchiver，这是一款免费的实用工具，下载地址：unarchiver.c3.cx

一旦安装了软件，逐个解压每个文件。解压后的文件相当大，所以请确保你有足够的磁盘空间来存放它们。

提示

在我目前的系统上，比较压缩和未压缩文件的大小显示，未压缩版本大约是压缩版本的十倍。这些文件解压时也需要几分钟时间，具体取决于你正在使用的系统规格，因此请为此步骤预留时间。

创建 MySQL 表并加载数据

我们现在有八个 .xml 文件，每个文件将映射到我们即将构建的数据库中的一个表。为了创建数据库和表，我们可以使用 phpMyAdmin 或其他图形工具通过点击的方式完成，或者我们可以运行 Georgios Gousios 编写并可在 gist.github.com/gousiosg/7600626 获得的以下简单 SQL 代码。此代码包含前六个表的 CREATE 和 LOAD INFILE 语句，但自从编写此脚本以来，数据库转储已经添加了两个额外的表。

为了构建新的表结构，我们可以在终端窗口或 shell 中运行 head 命令，以检查文件的前几行。在终端中，运行该命令在最小的 XML 文件 PostLinks.xml 上，方法如下：

head PostLinks.xml

结果中的前四行如下所示：

<?xml version="1.0" encoding="utf-8"?>
<postlinks>
 <row Id="19" CreationDate="2010-04-26T02:59:48.130" PostId="109" RelatedPostId="32412" LinkTypeId="1" />
 <row Id="37" CreationDate="2010-04-26T02:59:48.600" PostId="1970" RelatedPostId="617600" LinkTypeId="1" />

我们新数据库表中的每一行应对应 XML <row> 行中的一行，行中显示的每个属性表示数据库表中的一个列。我们可以对 Tags.xml 文件执行相同的 head 命令，查看它的列应该是什么。以下 SQL 代码将处理两个额外表的 CREATE 语句和 LOAD 语句：

CREATE TABLE post_links (
  Id INT NOT NULL PRIMARY KEY,
  CreationDate DATETIME DEFAULT NULL,
  PostId INT NOT NULL,
  RelatedPostId INT NOT NULL,
  LinkTypeId INT DEFAULT NULL
);

CREATE TABLE tags (
  Id INT NOT NULL PRIMARY KEY,
  TagName VARCHAR(50) DEFAULT NULL,
  Count INT DEFAULT NULL,
  ExcerptPostId INT DEFAULT NULL,
  WikiPostId INT DEFAULT NULL
);

LOAD XML LOCAL INFILE 'PostLinks.xml'
INTO TABLE post_links
ROWS IDENTIFIED BY '<row>';

LOAD XML LOCAL INFILE 'Tags.xml'
INTO TABLE tags
ROWS IDENTIFIED BY '<row>';

注意

注意，LOAD XML 语法略有变化，因此我们可以将文件保存在本地。如果你的.xml文件存储在本地计算机上而不是数据库服务器上，只需在LOAD XML语句中添加LOCAL字样，如前面的代码所示，然后可以引用文件的完整路径。

关于 MySQL LOAD XML 语法的更多信息，请参阅 MySQL 文档：dev.mysql.com/doc/refman/5.5/en/load-xml.html。

到此为止，我们已经拥有一个功能完整的 MySQL 数据库，包含八个表，每个表都填充了数据。然而，这些表非常大，只有八个表就有超过一亿九千万行。当我们开始清理数据并为分析做准备时，我们会注意到，如果在像posts、comments、votes或post_history这样的超大表上犯错，重建该表将需要很长时间。在下一步中，我们将学习如何创建测试表，以便如果程序或查询出现问题，我们能将损失控制在最小范围。

构建测试表

在本节中，我们将构建八个较小版本的原始表，每个表都随机填充来自原始表的数据。

我们的第一步是重新运行CREATE语句，但这次在每个表名之前加上test_前缀，如下所示：

DROP TABLE IF EXISTS test_post_links;
CREATE TABLE test_post_links (
  Id INT NOT NULL PRIMARY KEY,
  CreationDate INT,
  PostId INT,
  RelatedPostId INT,
  LinkTypeId INT
);

除了在表名之前添加test_外，这八个测试表将与我们之前创建的其他表完全相同。

接下来，我们需要向新的测试表中填充数据。我们可以简单地从每个表中选择前 1,000 行并加载到测试表中。然而，做这样做的缺点是这些行是根据它们插入 Stack Overflow 数据库的顺序排列的，因此如果我们仅请求前 1,000 行，我们的子集就不会有来自不同日期和时间的良好样本。我们希望选择的行有较为随机的分布。我们如何随机选择一组行？在本书中，我们之前并没有处理过这个问题，因此这是另一个需要我们准备尝试新方法的情况，以确保我们的数据科学晚宴顺利进行。

有几种选择随机行的方式，其中一些比其他方式更高效。效率对于我们这个项目来说非常重要，因为我们正在处理的表非常大。让我们随机选择行的一个小难点是，虽然我们的表有一个数字类型的主键作为Id列，但这些Id号码并不是连续的。例如，在post_links表中，Id列的前几个值分别是 19、37、42 和 48。

数据中的空缺是一个问题，因为简单的随机生成器是这样操作的：

  1. 构建一个 PHP 脚本，要求提供表中最低和最高的Id值，如下所示：

    SELECT min(Id) FROM post_links;
SELECT max(Id) FROM post_links;

  2. 然后，仍然在脚本中，生成一个介于min和max值之间的随机数，并请求该随机值对应的行：

    SELECT * FROM post_links WHERE Id = [random value];

  3. 根据需要重复步骤 2，直到获取所需的行数。

不幸的是，举个例子，在 Stack Overflow 数据库表中执行此操作，例如在我们的post_links表上，将导致许多查询失败，因为我们的数据在Id列中有很多空缺。例如，如果前面的示例中的步骤 2 生成了数字 38，怎么办？我们的post_links表中没有Id为 38 的记录。这意味着我们需要检测到这个错误，并尝试用一个新的随机值重新执行。

注意

到这一步，一个懂一些 SQL 的人 — 但不多 — 通常会建议我们只需让 MySQL 在包含Id的列上执行ORDER BY rand()，然后执行LIMIT命令来挑选我们想要的记录数。这个想法的问题是，即使我们排序的列是索引列，ORDER BY rand()仍然必须读取每一行来分配一个新的随机数。因此，在像 Stack Overflow 数据库中那样的大表上，这种方法完全不适用。我们将不得不等待ORDER BY rand()查询完成，等待时间会太长。ORDER BY rand()对于小表是可以接受的解决方案，但对于我们在这里处理的大表大小则不适用。

以下 PHP 脚本展示了我们的最终随机行选择过程是如何工作的，它将构建八个测试表，每个表恰好包含 1,000 行。每个表将通过尽可能随机地选择行值，并尽量减少努力，且不对这个简单问题进行过度设计来填充：

<?php //randomizer.php
// how many rows should be in each of the test tables?
$table_target_size = 1000;

// connect to db, set up query, run the query
$dbc = mysqli_connect('localhost','username','password','stackoverflow')
       or die('Error connecting to database!' . mysqli_error());
$dbc->set_charset("utf8");

$tables = array("badges",
    "comments",
    "posts",
    "post_history",
    "post_links",
    "tags",
    "users",
    "votes");

foreach ($tables as $table)
{
  echo "\n=== Now working on $table ===\n";
    $select_table_info = "SELECT count(Id) as c, min(Id) as mn, max(Id) as mx FROM $table";
    $table_info = mysqli_query($dbc, $select_table_info);
    $table_stuff = mysqli_fetch_object($table_info);
    $table_count = $table_stuff->c;
    $table_min = $table_stuff->mn;
    $table_max = $table_stuff->mx;

    // set up loop to grab a random row and insert into new table
    $i=0;
    while($i < $table_target_size)
    {
        $r = rand($table_min, $table_max);
        echo "\nIteration $i: $r";
        $insert_rowx = "INSERT IGNORE INTO test_$table (SELECT * FROM $table WHERE Id = $r)";
        $current_row = mysqli_query($dbc, $insert_rowx);

        $select_current_count = "SELECT count(*) as rc FROM test_$table";
        $current_count= mysqli_query($dbc, $select_current_count);
        $row_count = mysqli_fetch_object($current_count)->rc;
        $i = $row_count;
    }
}
?>

运行该代码后，我们可以看到如果需要的话，我们有一组八个测试表可以使用。使用这些较小的表进行测试能确保我们的清理工作顺利进行，且错误能够被控制。如果我们发现需要更多的行数在我们的随机表中，我们可以简单地提高$table_target_size命令并重新运行。

构建测试表是一个很好的习惯，一旦你知道如何以简单和有用的方式创建它们。

第三步 – 清理数据

记住我们的目标是开始分析在问题、回答和评论中某些 URL 被引用的频率，因此从 Stack Overflow 的posts和comments表中的文本开始是合乎逻辑的。然而，由于这些表非常大，我们将使用我们刚刚创建的test_posts和test_comments表来代替。然后，一旦我们确信查询完美无缺，我们可以在更大的表上重新运行它们。

这个清理任务与我们在第七章中提取推文中的 URL 存储方式非常相似，RDBMS 清理技术。然而，这个项目有自己的一套具体规则：

  • 由于帖子和评论本身就是不同的实体，我们应该为来自帖子（包括问题和回答）和来自评论的 URL 分别创建不同的表。

  • 每个问题、回答或评论可以包含多个 URL。我们应该存储所有的 URL，同时也应该追踪这些 URL 来自于哪个帖子或评论的唯一标识符。

  • 每个问题和回答也可以包含格式化的源代码。<code>标签用于在 Stack Overflow 的帖子中界定源代码。将代码与帖子分开将帮助我们回答有关粘贴站点 URL 和源代码共存的问题。通常有多少代码会与这样的链接一起出现，如果有的话？

    注意

    从技术上讲，帖子可以在没有<code>标签的情况下创建，但通常会有人很快编辑这些不规范的帖子，加入这些有用的标签，并因此获得 Stack Overflow 的积分。为了简洁起见，在本项目中，我们假设代码会被包含在<code>标签中。

  • 根据 Stack Overflow 数据库转储文档（可以在meta.stackexchange.com/questions/2677/查看），实际上有八种帖子类型，其中问题和答案只是两种类型。因此，我们需要将查询限制为postTypeId=1表示问题，postTypeId=2表示答案。

  • 为了确保我们只从评论中提取指向问题或答案的 URL，而不是其他类型的帖子，我们需要将查询连接回帖子表，并将结果限制为postTypeId=1或postTypeId=2。

创建新表

创建我们需要的数据库表来存储这些 URL 的 SQL 查询如下：

CREATE TABLE clean_comments_urls (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  commentId INT NOT NULL,
  url VARCHAR(255) NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS clean_posts_urls (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  postId INT NOT NULL,
  url VARCHAR(255) NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

我们还需要创建一个表来存储我们从帖子中剥离出的代码：

CREATE TABLE clean_posts_code (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  postId INT NOT NULL,
  code TEXT NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

到目前为止，我们有了三个新表，这些表将存储我们的清理后的 URL 和清理后的源代码。在接下来的部分中，我们将提取 URL 和代码，并填充这些新表。

提取 URL 并填充新表

我们可以修改我们之前在第七章中编写的脚本，RDBMS 清理技术，以在这个新的 Stack Overflow 环境中提取 URL，具体如下：

<?php // urlExtractor.php
// connect to db
$dbc = mysqli_connect('localhost', 'username', 'password', 'stackoverflow')
    or die('Error connecting to database!' . mysqli_error());
$dbc->set_charset("utf8");

// pull out the text for posts with
// postTypeId=1 (questions)
// or postTypeId=2 (answers)
$post_query = "SELECT Id, Body
    FROM test_posts
    WHERE postTypeId=1 OR postTypeId=2";

$comment_query = "SELECT tc.Id, tc.Text
    FROM test_comments tc
    INNER JOIN posts p ON tc.postId = p.Id
    WHERE p.postTypeId=1 OR p.postTypeId=2";

$post_result = mysqli_query($dbc, $post_query);
// die if the query failed
if (!$post_result)
    die ("post SELECT failed! [$post_query]" .  mysqli_error());

// pull out the URLS, if any
$urls = array();
$pattern  = '#\b(([\w]+://?|www[.])[^\s()<>]+(?:\([\w\d]+\)|([^[:punct:]\s]|/)))#';

while($row = mysqli_fetch_array($post_result))
{
    echo "\nworking on post: " . $row["id"];
    if (preg_match_all(
        $pattern,
        $row["Body"],
        $urls
    ))
    {
        foreach ($urls[0] as $url)
        {
          $url = mysqli_escape_string($dbc, $url);
            echo "\n----url: ".$url;
            $post_insert = "INSERT INTO clean_posts_urls (id, postid, url)
                VALUES (NULL," . $row["Id"] . ",'$url')";
            echo "\n$post_insert";
            $post_insert_result = mysqli_query($dbc, $post_insert);
        }
    }
}

$comment_result = mysqli_query($dbc, $comment_query);
// die if the query failed
if (!$comment_result)
    die ("comment SELECT failed! [$comment_query]" .  mysqli_error());

while($row = mysqli_fetch_array($comment_result))
{
    echo "\nworking on comment: " . $row["id"];
    if (preg_match_all(
        $pattern,
        $row["Text"],
        $urls
    ))
    {
        foreach ($urls[0] as $url)
        {
            echo "\n----url: ".$url;
            $comment_insert = "INSERT INTO clean_comments_urls (id, commentid, url)
                VALUES (NULL," . $row["Id"] . ",'$url')";
            echo "\n$comment_insert";
            $comment_insert_result = mysqli_query($dbc, $comment_insert);
        }
    }
}
?>

我们现在已经完全填充了clean_post_urls和clean_comment_urls表。对于我随机填充的测试表，运行这个脚本只会得到大约 100 个评论 URL 和 700 个帖子 URL。不过，这些数据足以在我们对整个数据集运行脚本之前，先测试一下我们的思路。

提取代码并填充新表

为了提取<code>标签中嵌入的文本并填充我们的新clean_posts_code表，我们可以运行以下脚本。这个过程类似于 URL 提取器，唯一不同的是它不需要搜索评论，因为评论中没有用<code>标签界定的代码。

在我随机选择的测试表中，初始的SELECT查询从test_post表中提取了约 800 行，表中总共有 1000 行。然而，每个帖子可能包含多个代码片段，因此最终的表格有超过 2000 行。以下 PHP 代码提取了<code>标签中嵌入的文本：

<?php // codeExtractor.php
// connect to db
$dbc = mysqli_connect('localhost', 'username, 'password', 'stackoverflow')
    or die('Error connecting to database!' . mysqli_error());
$dbc->set_charset("utf8");

// pull out the text for posts with
// postTypeId=1 (questions)
// or postTypeId=2 (answers)
$code_query = "SELECT Id, Body
    FROM test_posts
    WHERE postTypeId=1 OR postTypeId=2
    AND Body LIKE '%<code>%'";

$code_result = mysqli_query($dbc, $code_query);
// die if the query failed
if (!$code_result)
    die ("SELECT failed! [$code_query]" .  mysqli_error());

// pull out the code snippets from each post
$codesnippets = array();
$pattern  = '/<code>(.*?)<\/code>/';

while($row = mysqli_fetch_array($code_result))
{
    echo "\nworking on post: " . $row["Id"];
    if (preg_match_all(
        $pattern,
        $row["Body"],
        $codesnippets
    ))
    {
      $i=0;
        foreach ($codesnippets[0] as $code)
        {
          $code = mysqli_escape_string($dbc, $code);
            $code_insert = "INSERT INTO clean_posts_code (id, postid, code)
                VALUES (NULL," . $row["Id"] . ",'$code')";
            $code_insert_result = mysqli_query($dbc, $code_insert);
            if (!$code_insert_result)
                die ("INSERT failed! [$code_insert]" .  mysqli_error());
            $i++;
        }
        if($i>0)
        {
          echo "\n   Found $i snippets";
        }
    }
}
?>

我们现在有了一个包含每个帖子中打印出的所有代码的列表，并已将其存储在clean_post_code表中。

第四步 – 分析数据

在这一部分，我们编写一些代码来回答本章开始时的三个问题。我们感兴趣的是寻找：

  • 帖子和评论中提到的不同粘贴网站的 URL 数量

  • 比较问题和答案中粘贴网站 URL 的数量

  • 统计带有粘贴网站 URL 的帖子中<code>标签的普及率

哪些粘贴网站最受欢迎？

为了回答这个问题，我们将生成一个 JSON 表示，包含 paste 站点 URL 和计数，使用clean_posts_urls和clean_comments_urls表格。这项简单的分析将帮助我们找出哪些 pastebin 网站在这个 Stack Overflow 数据集中特别受欢迎。以下 PHP 查询从数据库中查询我们预先列出的$pastebins数组中的 paste 站点，并执行从帖子和评论中匹配 URL 的计数。它使用的是测试表格，因此这些数字要比实际表格中的数字小得多：

<?php // q1.php
// connect to db
$dbc = mysqli_connect('localhost', 'username', 'password', 'stackoverflow')
    or die('Error connecting to database!' . mysqli_error());
$dbc->set_charset("utf8");

// these are the web urls we want to look for and count
$pastebins = array("pastebin",
    "jsfiddle",
    "gists",
    "jsbin",
    "dpaste",
    "pastie");
$pastebin_counts = array();

foreach ($pastebins as $pastebin)
{
    $url_query = "SELECT count(id) AS cp,
          (SELECT count(id)
          FROM clean_comments_urls
          WHERE url LIKE '%$pastebin%') AS cc
        FROM clean_posts_urls
        WHERE url LIKE '%$pastebin%'";
    $query = mysqli_query($dbc, $url_query);
    if (!$query)
        die ("SELECT failed! [$url_query]" .  mysqli_error());
    $result = mysqli_fetch_object($query);
    $countp = $result->cp;
    $countc = $result->cc;
    $sum = $countp + $countc;

    array_push($pastebin_counts, array('bin' => $pastebin,
                                        'count' => $sum));
}
// sort the final list before json encoding it
// put them in order by count, high to low
foreach ($pastebin_counts as $key => $row)
{
    $first[$key]  = $row['bin'];
    $second[$key] = $row['count'];
}

array_multisort($second, SORT_DESC, $pastebin_counts);
echo json_encode($pastebin_counts);
?>

我们可以查看运行该脚本时从测试表格中得到的 JSON 输出，通过查看脚本的输出。我的随机行产生了以下计数：

[{"bin":"jsfiddle","count":44},{"bin":"jsbin","count":4},{"bin":"pastebin","count":3},{"bin":"dpaste","count":0},{"bin":"gists","count":0},{"bin":"pastie","count":0}]

注意

记住，由于你选择的随机 URL 集合不同，你的值可能会有所不同。

当我们进入本章的步骤 5 – 数据可视化部分时，我们将使用这个 JSON 代码来构建一个条形图。但首先，让我们先回答之前提出的另外两个问题。

哪些 paste 站点在问题中流行，哪些在回答中流行？

我们的第二个问题是，pastebin URL 在问题帖子中更常见，还是在回答帖子中更常见。为了开始解决这个问题，我们将运行一系列 SQL 查询。第一个查询仅仅是询问clean_posts_urls表中每种类型的帖子数，问题和回答：

SELECT tp.postTypeId, COUNT(cpu.id)
FROM test_posts tp
INNER JOIN clean_posts_urls cpu ON tp.Id = cpu.postid
GROUP BY 1;

结果显示，在我随机选择的测试集中，我有 237 个问题和 440 个回答：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00302.jpeg

phpMyAdmin 显示问题 URL 和回答 URL 的计数。

现在，我们想要知道这个问题的答案：在这 677 个 URL 中，按问题和回答分类，有多少个专门引用了六个 pastebin 网站中的某一个？我们可以运行以下 SQL 代码来找出答案：

SELECT  tp.postTypeId, count(cpu.id)
FROM test_posts tp
INNER JOIN clean_posts_urls cpu ON tp.Id = cpu.postId
WHERE cpu.url LIKE '%jsfiddle%'
OR cpu.url LIKE '%jsbin%'
OR cpu.url LIKE '%pastebin%'
OR cpu.url LIKE '%dpaste%'
OR cpu.url LIKE '%gist%'
OR cpu.url LIKE '%pastie%'
GROUP BY 1;

结果如下表所示。共有 18 个问题引用了某个 paste 站点，而 24 个回答引用了某个 paste 站点。

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00303.jpeg

phpMyAdmin 显示了引用 pastebin 的问答 URL 的计数。

需要注意的一点是，这些查询统计的是每个 URL 的出现次数。所以，如果某个postId引用了五个 URL，那么它们会被计数五次。如果我关心的是有多少帖子使用了某个 paste 站点 URL 一次或更多次，我需要修改两个查询的第一行，如下所示。这个查询统计了 URLs 表格中不同的帖子：

SELECT tp.postTypeId, COUNT(DISTINCT cpu.postId)
FROM test_posts tp
INNER JOIN clean_posts_urls cpu ON tp.Id = cpu.postId
GROUP BY 1;

以下截图显示了有多少问题和回答包含了一个 URL：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00304.jpeg

phpMyAdmin 显示了有多少问题和回答包含了任何 URL。

这个查询统计了在 URLs 表格中提到 paste 站点的特定帖子：

SELECT  tp.postTypeId, count(DISTINCT cpu.postId)
FROM test_posts tp
INNER JOIN clean_posts_urls cpu ON tp.Id = cpu.postId
WHERE cpu.url LIKE '%jsfiddle%'
OR cpu.url LIKE '%jsbin%'
OR cpu.url LIKE '%pastebin%'
OR cpu.url LIKE '%dpaste%'
OR cpu.url LIKE '%gist%'
OR cpu.url LIKE '%pastie%'
GROUP BY 1;

这个粘贴网站查询的结果如下，正如预期的那样，数字较小。在我们的测试集里，11 个问题使用了至少一个粘贴站 URL，16 个答案也如此。合计，37 个帖子至少引用了一个粘贴站 URL。

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00305.jpeg

PhpMyAdmin 显示了包含任何粘贴网站 URL 的问题和答案的数量。

尽管这些结果似乎显示人们在答案中引用粘贴网站 URL 的频率高于问题中，但我们需要从问题和答案的总体数量来进行比较。我们应该将结果值报告为该帖子类型（问题或答案）的总数的百分比。考虑到总数，我们现在可以说类似这样的话：“只考虑那些至少一次使用了某种 URL 的问题和答案，81 个问题中有 11 个使用了至少一个粘贴网站 URL（13.6%），222 个答案中有 16 个使用了至少一个粘贴网站 URL（7.2%）。” 综上所述，实际上问题在引用粘贴网站方面超过了答案，几乎是两倍。

在任何数据分析项目的这个阶段，你一定会有一大堆问题，比如：

  • 粘贴网站 URL 在问题和答案中的使用随时间发生了什么变化？

  • 带有粘贴网站 URL 的问题在投票和收藏中表现如何？

  • 发布带有粘贴网站 URL 的问题的用户有什么特点？

但由于这是一本关于数据清洗的书，而且我们仍然没有可视化这些数据，我会克制自己，暂时不回答这些问题。我们还有一个原始的三个问题没有回答，然后我们将继续可视化我们的一些结果。

帖子中是否同时包含粘贴站的 URL 和源代码？

回答我们第三个问题需要将 Stack Overflow 问题中的代码量与答案中的代码量进行比较，特别关注那些包含某种源代码（由 <code> 标签分隔）的帖子。在 第三步 - 清洗数据 部分中，我们从测试表中的帖子中提取了所有代码，并创建了一个新表来存放这些代码片段。现在，一个简单的查询来找出包含代码的帖子数量如下：

SELECT count(DISTINCT postid)
FROM clean_posts_code;

在我的样本集中，这产生了 664 个包含代码的帖子，来自 1,000 个测试帖子。换句话说：1,000 个帖子中有 664 个包含至少一个 <code> 标签。

要找出这些包含代码的帖子中有多少也包含了任何 URL，我们可以运行以下 SQL 查询：

SELECT count(DISTINCT cpc.postid)
FROM clean_posts_code cpc
INNER JOIN clean_posts_urls cpu
ON cpu.postId = cpc.postId;

我的样本集返回了 175 行数据。我们可以这样解释：原始测试集 1,000 个帖子中，17.5% 包含了代码和 URL。

现在，为了找出有多少包含代码的帖子也包含了粘贴站 URL，我们将进一步缩小 SQL 查询的范围：

SELECT count(DISTINCT cpc.postid)
FROM clean_posts_code cpc
INNER JOIN clean_posts_urls cpu
ON cpu.postId = cpc.postId
WHERE cpu.url LIKE '%jsfiddle%'
OR cpu.url LIKE '%jsbin%'
OR cpu.url LIKE '%pastebin%'
OR cpu.url LIKE '%dpaste%'
OR cpu.url LIKE '%gist%'
OR cpu.url LIKE '%pastie%';

从这些结果中，我们可以看到，只有 25 篇帖子同时包含源代码和粘贴站点 URL。从第二个问题中，我们知道 37 篇不同的帖子（包括问题和答案）至少使用过一次某种粘贴站点 URL。因此，25 比 37 大约是 68%。在更大的数据集上运行这些查询，看看这些值如何变化，将会很有趣。

与此同时，我们将对至少一个问题进行简单的可视化，以便完成数据科学六步法的一个完整回合。

第五步 – 可视化数据

可视化步骤有点像我们晚宴中的甜点环节。每个人都喜欢丰富的图形，它们看起来非常漂亮。然而，由于本书的重点在于数据清理而非分析与可视化，我们这里的图形将非常简单。在接下来的代码中，我们将使用 JavaScript D3 可视化库，以图形方式展示第一个问题的结果。这次可视化比我们在第四章中做的 D3 可视化要简单得多。你会记得，在那一章中，我们构建了一个相当复杂的网络图，但在这里，简单的条形图就足够了，因为我们只需要展示一些标签和计数。

以下是 HTML 和 JavaScript/D3 代码。该代码扩展了 Mike Bostock 的让我们制作一个条形图教程，教程地址为bl.ocks.org/mbostock/3885304。我扩展这段代码的方式之一是让它读取我们之前在q1.php脚本中生成的 JSON 文件。我们的 JSON 文件格式很漂亮，并且已经按从高到低排序，因此从中构建一个小条形图非常容易：

<!DOCTYPE html>
<meta charset="utf-8">
<!--
this code is modeled on mbostock's
"Let's Make a Bar Chart" D3 tutorial
available at http://bl.ocks.org/mbostock/3885304
My modifications:
* formatting for space
* colors
* y axis labels
* changed variable names to match our data
* loads data via JSON rather than .tsv file
-->

<style>
.bar {fill: lightgrey;}
.bar:hover {fill: lightblue;}
.axis {font: 10px sans-serif;}
.axis path, .axis line {
  fill: none;
  stroke: #000;
  shape-rendering: crispEdges;
}
.x.axis path {display: none;}
</style>
<body>
<script src="img/d3.min.js"></script>
<script>

var margin = {top: 20, right: 20, bottom: 30, left: 40},
    width = 960 - margin.left - margin.right,
    height = 500 - margin.top - margin.bottom;

var x = d3.scale.ordinal()
    .rangeRoundBands([0, width], .1);

var y = d3.scale.linear()
    .range([height, 0]);

var xAxis = d3.svg.axis()
    .scale(x)
    .orient("bottom");

var yAxis = d3.svg.axis()
    .scale(y)
    .orient("left");

var svg = d3.select("body").append("svg")
    .attr("width", width + margin.left + margin.right)
    .attr("height", height + margin.top + margin.bottom)
  .append("g")
    .attr("transform", "translate(" + margin.left + "," + margin.top + ")");

d3.json("bincounter.php", function(error, json)
{
    data = json;
    draw(data);
});

function draw(data)
{
  x.domain(data.map(function(d) { return d.bin; }));
  y.domain([0, d3.max(data, function(d) { return d.count; })]);

  svg.append("g")
      .attr("class", "x axis")
      .attr("transform", "translate(0," + height + ")")
      .call(xAxis);

  svg.append("g")
      .attr("class", "y axis")
      .call(yAxis)
    .append("text")
      .attr("transform", "rotate(-90)")
      .attr("y", 6)
      .attr("dy", ".71em")
      .style("text-anchor", "end")
      .text("Frequency");

  svg.selectAll(".bar")
      .data(data)
    .enter().append("rect")
      .attr("class", "bar")
      .attr("x", function(d) { return x(d.bin) ; })
      .attr("width", x.rangeBand())
      .attr("y", function(d) { return y(d.count); })
      .attr("height", function(d) { return height - y(d.count); });
}

</script>
</body>
</html>

我们可以将其保存为q1chart.html，并在浏览器中查看。该代码调用了我们的q1.php脚本，后者生成 JSON 文件，D3 则用它来构建这个图表，左侧部分如下所示：

https://github.com/OpenDocCN/freelearn-ds-pt5-zh/raw/master/docs/cln-dt/img/image00306.jpeg

D3 可视化展示从三个 URL 计数生成的 JSON 数据。

条形图显示指向 JSFiddle 的 URL 似乎是最常见的，至少在我版本的随机选择测试数据集中是这样。我们仅通过查看q1.php的 JSON 输出就知道了这一点，但看到图形化的展示仍然让人感觉很直观。接下来的部分，我们将总结结果和过程，并讨论下一步该如何推进这个项目。

第六步 – 问题解决

从我们在第四步 – 分析数据和第五步 – 可视化数据部分开发的查询和可视化中，我们现在可以尝试回答当初促使这个项目的三个问题。

在第一个问题中，我们希望查找在帖子和评论中按 URL 提到的不同粘贴站点的数量。我们创建的q1.php脚本和条形图可视化数据显示，至少在测试数据中，JSFiddle 是我们查看的六个粘贴站点 URL 中最常被提及的。

第二个问题是关于粘贴站点 URL 在问题和答案中是否更为普遍。我们的查询显示，粘贴站点 URL 出现在问题中的几率是出现在答案中的大约两倍，但无论是问题还是答案中的数量都很少，至少在我们的测试集中是这样。

对于第三个问题，我们希望查看人们是否真的听从了 Stack Overflow 的建议，在发布粘贴站点 URL 的同时也附上了源代码。在我们的测试集中，查询结果显示，37 条记录中有 25 条同时包含了粘贴站点 URL 和推荐的源代码。这意味着大约有 68%的合规率。

此时，我们可以提出并回答许多其他问题，也有很多激动人心的方法，可以将这个简单的研究扩展成更有趣的内容。但现在，我们将专注于存储和清理程序，以便将这个项目扩展到使用完整的数据集。

从测试表迁移到完整表

在这个项目的开始阶段，我们创建了一组测试表，以便在一个无压力的环境中开发项目，每个表只有 1,000 行数据。使用行数可控的小型表格非常重要，尤其是在我们不确定查询是否按预期工作的情况下，或者当我们需要尝试一些复杂的连接、子查询、奇怪的正则表达式等。此时，如果我们对已经编写的查询和脚本感到满意，就可以开始重写过程，使用完整大小的表格。

以下是我们将采取的步骤，将项目迁移到完整表：

  1. DROP测试表：

    DROP TABLE IF EXISTS test_badges;
DROP TABLE IF EXISTS test_comments;
DROP TABLE IF EXISTS test_posts;
DROP TABLE IF EXISTS test_post_history;
DROP TABLE IF EXISTS test_post_links;
DROP TABLE IF EXISTS test_tags;
DROP TABLE IF EXISTS test_users;
DROP TABLE IF EXISTS test_votes;

  2. 如下所示，清空cleaned_posts_code、cleaned_posts_urls和cleaned_comments_urls表：

    TRUNCATE TABLE cleaned_posts_code;
TRUNCATE TABLE cleaned_posts_urls;
TRUNCATE TABLE cleaned_comments_urls;

  3. 编辑urlExtractor.php和codeExtractor.php脚本，使其从posts表中SELECT而不是从test_posts表中选择。可以按如下方式编辑这些查询：

    SELECT Id, Body FROM posts

  4. 重新运行urlExtractor.php和codeExtractor.php脚本，以便它们重新填充之前清空（截断）的干净代码和 URL 表。

此时，我们已经准备好清理后的代码和 URL 表进行分析和可视化。在执行这些步骤时请耐心，了解许多查询和脚本可能需要很长时间才能完成。posts表非常大，且我们编写的许多查询都是针对使用通配符的文本列进行选择的。

摘要

在这个项目中，我们提出了几个关于 Stack Overflow 上 URL 普及的问题，特别是那些与粘贴网站相关的链接，如www.Pastebin.com和www.JSFiddle.net。为了开始回答这些问题，我们从 Stack Exchange 的公开文件发布中下载了 Stack Overflow 帖子（以及其他 Stack Overflow 数据）。我们建立了一个 MySQL 数据库，并创建了八个表来存储这些数据。然后，我们为测试目的创建了每个表的 1,000 行小版本，这些版本填充了随机选择的数据样本。通过这些测试表，我们提取了每个问题、答案和评论中提到的 URL，并将它们保存到一个新的干净表格中。我们还提取了问题和答案中的源代码，并将这些代码片段保存到一个新的表格中。最后，我们能够构建一些简单的查询和可视化工具，帮助我们回答最初提出的问题。

尽管结果相对简单，从数据清理的角度来看，我们的“晚宴”还是成功的。我们能够制定一个连贯的计划，并采取系统的步骤来执行计划，并在必要时调整。现在我们已经准备好迎接我们的最终项目，以及一个完全不同的晚宴菜单。

在下一章，我们将收集并清理我们自己版本的著名 Twitter 数据集。

数仓建模—DMBOK2 数据管理知识体系指南
06-13 4万+
DAMA-DMBOK2指南》为数据管理提供了系统的方法和框架，涵盖了从数据治理到数据质量管理的各个方面。它强调数据作为企业资产的重要性，并提供了详细的指导原则、活动、角色和技术工具，以确保数据管理的有效性和成功。环境要素为数据管理提供了必要的背景和支持，确保各知识领域的活动能够顺利进行并取得成功。。CDMP考试：CDMP（Certified Data Management Professional）认证是数据管理领域的权威性认证，由DAMA国际组织推出。
Kafka数据清理指南
WdzDevops的博客
09-20 4130
在本文中，我们将介绍如何使用Kafka进行数据清理。当我们在Kafka集群中处理大量的数据时，及时清理过期、无效或不再需要的数据是非常重要的。首先，我们需要了解Kafka中的数据保留策略。Kafka的数据保留策略决定了消息在主题中保留的时间。默认情况下，Kafka会根据时间来保留数据，也可以根据数据大小进行保留。根据您的需求和数据保留策略，选择适合您的场景的方法进行数据清理。这将将特定分区保留的时间设置为1秒，使消息在下一次清理时被删除。当主题中的数据大小超过指定的阈值时，旧的消息将被删除。
数据湖数据仓库数据集市数据清理以及DataOps
纸上得来终觉浅，绝知此事要躬行
10-08 2439
一提到大数据我们就知道是海量数据，但是我们并不了解需要从哪些维度去考虑这些数据的存储。比如 数据湖、数据仓库、数据集市，以及数据自动化应用DataOps有哪些实现方式和实际应用，这篇文章将浅显的做一次介绍。
最全Python数据清理终极指南
码农-Vax天道的时间宝藏
11-30 1722
一般来说，我们在拟合一个机器学习模型或是统计模型之前，总是要进行数据清理的工作。因为没有一个模型能用一些杂乱无章的数据来产生对项目有意义的结果。 数据清理或清除是指从一个记录集、表或是数据库中检测和修改（或删除）损坏或不准确的数据记录的过程，它用于识别数据中不完整的、不正确的、不准确的或者与项目本身不相关的部分，然后对这些无效的数据进行替换、修改或者删除等操作。 这是个很长的定义，不过描述的较为简单，容易理解。 为了简便起见，我们在Python中新创建了一个完整的、分步的指南，你将从中学习到如何进行数
Jenkins垃圾清理指南
但老师
02-25 1575
Jenkins是一款开源的持续集成与持续交付（CI/CD）工具，广泛用于自动化构建、测试和部署。在分布式构建环境中，Agent节点可能因内存不足导致任务失败。
苹果电脑离职前数据清理，隐私安全很重要
2401_88252730的博客
03-05 1639
苹果电脑的数据清理需结合系统擦除、多次覆盖及痕迹清除等多层防护，才能有效抵御数据恢复风险。对于非技术用户，可借助工具简化流程，但其功能仅限文件级清理，系统级擦除仍需手动操作。隐私安全无小事，尤其在职场变动时，一次彻底的清理既能保护个人权益，亦是对职业道德的尊重。
mysql 定期清理表数据_数据库操作指南：MySql定时清理某个时间之前的数据
weixin_39633252的博客
12-19 853
一个项目如果规划不合理，每天会产生很多条告警，导致查询速度变慢。同时由于数据量过大可能会导致磁盘空间不够的情况。一般可以通过程序代码定时清理的方式来进行。现在可以通过MySql的时间进行定时清理，让数据库自己去清理过期数据。创建事件现在解释一下第一行，表明了这个事件是由alex创建，事件名称为date_clean第二行，表示这个事件定期执行第三行，执行周期为每天一次第四行，事件默认状态为不启动，开...
大数据之数据清理的终极指南
yuhun001的博客
05-23 3494
我花了几个月的时间分析来自传感器、调查及日志等相关数据。无论我用多少图表，设计多么复杂的算法，结果总是会与预期不同。更糟糕的是，当你向首席执行官展示你的新发现时，他/她总会发现缺陷，你的发现与他们的理解完全不符- 毕竟，他们是比你更了解领域的专家，而你只是数据工程师或开发人员。 你为你的模型引入了大量脏数据，没有清理数据，你告诉你的公司用这些结果做事情，结果肯定是错的。数据不正确或不一致会导...
MySQL时间分区表数据清理：原理与实践指南
热门推荐
私聊前往站内信：https://i.csdn.net/#/msg/chat/weixin_44976692
04-26 1万+
分区表是将一个大表在物理上分割成多个小表（分区），而在逻辑上仍然表现为一个完整表的技术。每个分区可以独立存储在不同的物理位置，但查询时仍像操作单个表一样简单。MySQL时间分区表是管理大规模时间序列数据的强大工具。通过合理设计分区策略和自动化维护脚本，可以显著提高查询性能、简化数据维护工作并降低存储成本。本文介绍的技术和方法已在多个生产环境验证，希望读者能根据自身业务特点灵活运用，构建高效的数据生命周期管理体系。
CDH 集群定期清理指南
萌兔兔MMQ！！
08-15 924
可以在CM里启动（hdfs-balancer-示例-操作-重新平衡），也可以命令启动（hadoop balancer [-threshold ]）。解决方法一：主要清理cloudera-host-monitor 、cloudera-service-monitor。下面是我写的一个shell脚本，可以查看所有目录和文件的大小，将大的无用文件删除。例：subject_ts、ts_subject、ts等带有ts的目录（慎重啊）例：subject_ts、ts_subject、ts等带有ts的目录。...
浅析人力资源数据挖掘中的数据清理.pdf
07-14
然而，数据挖掘的质量在很大程度上取决于数据的质量，这就需要通过数据清理来保证。数据清理是数据挖掘的一个重要前期工作，它关注于消除数据中不完整、不一致、含噪声及偏差的问题，以提高数据的质量，从而提高挖掘...
Python数据清理终极指南（2020版）
02-24
一般来说，我们在拟合一个机器学习模型或是统计模型之前，总是要进行数据清理的工作。因为没有一个模型能用一些杂乱无章的数据来产生对项目有意义的结果。数据清理或清除是指从一个记录集、表或是数据库中检测和修改...
C盘爆满终极清理指南.pdf
04-17
内容概要：本文《C盘爆满终极清理指南》提供了从基础清理到深度优化的20个实用技巧，帮助用户解决C盘空间不足的问题。首先介绍了快速清理基础篇，如使用系统自带清理工具、清理微信/QQ文件、精简休眠文件和迁移虚拟...
K3账套数据清理工具
07-04
《K3账套数据清理工具详解与应用指南》 在企业管理中，财务系统扮演着至关重要的角色，其中金蝶K3WISE系统作为国内广泛应用的企业管理软件，为企业的财务管理提供了全面的解决方案。然而，随着企业运营时间的增长，...
spring-ai-autoconfigure-model-image-observation-1.0.0-M8.jar中文-英文对照文档.zip
07-19
1、压缩文件中包含： 中文-英文对照文档、jar包下载地址、Maven依赖、Gradle依赖、源代码下载地址。 2、使用方法： 解压最外层zip，再解压其中的zip包，双击 【index.html】 文件，即可用浏览器打开、进行查看。 3、特殊说明： （1）本文档为人性化翻译，精心制作，请放心使用； （2）只翻译了该翻译的内容，如：注释、说明、描述、用法讲解 等； （3）不该翻译的内容保持原样，如：类名、方法名、包名、类型、关键字、代码 等。 4、温馨提示： （1）为了防止解压后路径太长导致浏览器无法打开，推荐在解压时选择“解压到当前文件夹”（放心，自带文件夹，文件不会散落一地）； （2）有时，一套Java组件会有多个jar，所以在下载前，请仔细阅读本篇描述，以确保这就是你需要的文件。 5、本文件关键字： jar中文-英文对照文档.zip,java,jar包,Maven,第三方jar包,组件,开源组件,第三方组件,Gradle,中文API文档,手册,开发手册,使用手册,参考手册。
基因工程原理与技术(1).ppt
最新发布
07-19
基因工程原理与技术(1).ppt
spring-ai-autoconfigure-vector-store-opensearch-1.0.0-RC1.jar中文文档.zip
07-19
1、压缩文件中包含： 中文文档、jar包下载地址、Maven依赖、Gradle依赖、源代码下载地址。 2、使用方法： 解压最外层zip，再解压其中的zip包，双击 【index.html】 文件，即可用浏览器打开、进行查看。 3、特殊说明： （1）本文档为人性化翻译，精心制作，请放心使用； （2）只翻译了该翻译的内容，如：注释、说明、描述、用法讲解 等； （3）不该翻译的内容保持原样，如：类名、方法名、包名、类型、关键字、代码 等。 4、温馨提示： （1）为了防止解压后路径太长导致浏览器无法打开，推荐在解压时选择“解压到当前文件夹”（放心，自带文件夹，文件不会散落一地）； （2）有时，一套Java组件会有多个jar，所以在下载前，请仔细阅读本篇描述，以确保这就是你需要的文件。 5、本文件关键字： jar中文文档.zip,java,jar包,Maven,第三方jar包,组件,开源组件,第三方组件,Gradle,中文API文档,手册,开发手册,使用手册,参考手册。
采购计划审批单模板excel表格.xls
07-19
采购计划审批单模板excel表格.xls
spring-ai-autoconfigure-vector-store-mongodb-atlas-1.0.0-RC1.jar中文-英文对照文档.zip
07-19
1、压缩文件中包含： 中文-英文对照文档、jar包下载地址、Maven依赖、Gradle依赖、源代码下载地址。 2、使用方法： 解压最外层zip，再解压其中的zip包，双击 【index.html】 文件，即可用浏览器打开、进行查看。 3、特殊说明： （1）本文档为人性化翻译，精心制作，请放心使用； （2）只翻译了该翻译的内容，如：注释、说明、描述、用法讲解 等； （3）不该翻译的内容保持原样，如：类名、方法名、包名、类型、关键字、代码 等。 4、温馨提示： （1）为了防止解压后路径太长导致浏览器无法打开，推荐在解压时选择“解压到当前文件夹”（放心，自带文件夹，文件不会散落一地）； （2）有时，一套Java组件会有多个jar，所以在下载前，请仔细阅读本篇描述，以确保这就是你需要的文件。 5、本文件关键字： jar中文-英文对照文档.zip,java,jar包,Maven,第三方jar包,组件,开源组件,第三方组件,Gradle,中文API文档,手册,开发手册,使用手册,参考手册。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值