只需4步，微软数据科学家教你用OpenRefine搞定数据清洗

导读：本文将使用OpenRefine清理我们的数据集；它很擅长数据的读取、清理以及转换数据。

作者：托马兹·卓巴斯（Tomasz Drabas）

如需转载请联系大数据（ID：hzdashuju）

本文的源代码与数据集都可在Github上获取。如果要复制代码库，打开你的终端（Windows环境下的命令行、Cygwin或Git Bash，Linux/Mac环境下的Terminal），键入下面这条命令：

git clone https://github.com/drabastomek/practicalDataAnalysisCookbook.git

注意，你的机器得装好Git了。安装指南参见：

https://git-scm.com/book/en/v2/Getting-Started-Installing-Git

本文将使用一个数据集，包含985项真实的房产交易。这些交易是连续5天内在Sacramento发生的。数据下载自：

https://support.spatialkey.com/spatialkey-sample-csv-data/

精确地说，来自：

http://samplecsvs.s3.amazonaws.com/Sacramentorealestatetransactions.csv

数据已转成多种格式，放在GitHub代码库的Data/Chapter01文件夹中。

01 使用OpenRefine打开并转换数据

OpenRefine诞生时被称作GoogleRefine。Google后来开放了源代码。这是个优秀的工具，可用于快速筛选数据、清理数据、排重、分析时间维度上的分布与趋势等。

在接下来的技巧中，我们将处理Data/Chapter1文件夹下的readEstate_trans_dirty.csv文件。这个文件有些问题，我们会看到解决办法。

首先，从文本文件中读取数据时，OpenRefine默认转为文本类型；本技巧将进行数据类型转换。否则没法针对性地处理数字列。

其次，数据中有重复（下文“排重”部分会处理这个问题）。

再次，city_state_zip列，顾名思义，是市、州、邮编的混合体。我们还是希望拆分它们，在下文“用正则表达式与GREL清理数据”中，我们将看到如何提取这些信息。交易价格也有缺失—我们将在“估算缺失值”中估算这些价格。

1. 准备

要完成这些例子，你得在你的计算机上安装OpenRefine并能正常运行。OpenRefine可从这里下载：

http://openrefine.org/download.html

安装指导在：

https://github.com/OpenRefine/OpenRefine/wiki/Installation-Instructions

OpenRefine在浏览器中运行，所以你的计算机中得有一个浏览器。我在Chrome和Safari上测试了，没发现问题。

Mac OS X Yosemite预装了Java 8。但OpenRefine不支持。你需要安装Java 6或7—参考：

https://support.apple.com/kb/DL1572?locale=en_US

然而，即便安装了Java的历史版本，我依然在Mac OS X Yosemite和El Capitan系统中遇到了2.5版OpenRefine的问题。使用beta版（2.6），虽然还在开发中，却能正常使用。

2. 怎么做

首先启用OpenRefine，打开浏览器，输入：

http://localhost:3333

会打开类似下图的窗口：

你要做的第一件事就是创建一个工程。单击Choose files，进入Data/Chapter1，选中realEstate_trans_dirty.csv。单击OK，然后Next，最后Create Project。数据就打开了，你会看到类似这个的界面：

注意beds、baths、sq__ft、price、latitude以及longitude数据都被当成文本处理，sale_date也是如此。用OpenRefine，转换前面那些字段容易，转换sale_date可就没那么容易了：

举个例子，如果文本数据的格式类似2008-05-21这样，我们只需调用GREL（Google Refine Expression Language）的.toDate()方法，OpenRefine会替我们转换好。本例中正确转换日期需要一些小技巧。首先选中Transform选项，如下图所示：

在打开的窗口中，使用GREL转换日期：

这里的value变量代表选中列（sale_date）中每个单元格的值。表达式的第一部分从值中提取出月份和天数，也就是说，我们截取第4个到第10个字符的子字符串，得到May 21。

第二个substring(...)方法从字符串中提取出年份。使用...+','+...表达式将两块以逗号分隔。最后得到May 21, 2008这样的格式。这就方便OpenRefine处理了。也就是说，我们用括号包装两个substring方法，并使用了.toDate()方法，以正确转换日期。右边的Preview标签页会展示表达式的效果。

3. 参考

Ruben Verborgh和Max De Wilde合著的《Using OpenRefine》从各方面介绍了OpenRefine，深入浅出，娓娓道来：

https://www.packtpub.com/big-data-and-business-intelligence/using-openrefine

02 使用OpenRefine探索数据

理解数据是建立成功模型的前提。对数据做不到了如指掌，你建立的模型就可能在纸面上很美，却在生产环境中大错特错。探索数据集是检测数据是否有问题的一个好办法。

1. 准备

要学习本技巧，你需要在计算机上装好OpenRefine以及一个浏览器。至于如何安装OpenRefine，参阅本文01部分的准备部分。

我们假设你使用了前一技巧，所以你的数据已经加载到OpenRefine，且数据类型代表着列中的数据。

2. 怎么做

有了Facets，用OpenRefine探索数据就简单了。一个OpenRefine Facet可以理解成一个过滤器：它让你快速地选择某些行，或直接探索数据。每一列都可以创建一个facet—只消单击列旁边的下拉箭头，菜单中选Facet组。

OpenRefine中有四种基本的facet：文本、数字、时间线以及分布图。

你可以自行定制facet，或者使用OpenRefine工具库中复杂一些的facet，比如词或文本的长度。

文本facet可以让你快速地对数据集中文本列的分布有一个感觉。比如，我们可以找到数据集中，2008年5月15日到5月21日之间销售额最高的是哪个城市。

聪明的你一定猜到了，既然我们一直在分析Sacramento的数据，那估计就是Sacramento了吧，的确是这样，其后是Elk Grove、Lincoln、Roseville，如下图所示：

这让你对数据是否合理有一个直观的感受；可以充分判定提供的数据是否符合假设。

数字facet可以让你粗略了解数字型数据的分布。比如，我们可以检查数据集中价格的分布，如下图所示：

价格的分布大体上如我们所料：左倾的分布是合理的，落在右端的交易较少，因为那部分是有意愿也有能力购置大庄园的买家。

这个facet也发现了我们数据集的一个不足：在价格列缺少89份数值。本文后面第05节中将解决这个问题。

在已知拿到7天（2008年5月15日至5月21日）数据的情况下，检查交易的时间线是否有空白也是个好办法：

我们的数据横跨了7天，但我们发现两天没有交易。翻下日历就会发现，5月17号、18号是周末，这里没啥问题。时间线facet允许你使用左右两边的滑动条过滤数据：这里我们过滤出2008年5月16日之后的数据。

散布图facet能分析数据集中数字型变量间的相互作用：

单击某一行和列可以详细地分析相互作用：

03 排重

我们应该默认待处理的数据是有瑕疵的（除非能证明没有）。检查数据是否都整理好了是一个好习惯。我首先检查的总是重复行。

1. 准备

要学本技巧，你需要在计算机上装好OpenRefine以及一个浏览器。

我们假设你应用了前一项技巧，所以你的数据已经加载到OpenRefine，且数据类型与列中的数据相符。

2. 怎么做

我们先假设7天的房产交易中，出现同样的地址就意味着有重复的行。这么短的时间周期内，同一套房子不太可能被卖两回。所以，我们在重复的数据上Blank down：

这样做的效果就是保留了数据的第一次出现，而将重复出现的置为空白（截图中第四列）：

Fill down选项效果相反—它会用上一行的数据填补空白，直到出现新的数据。

现在创建一个关于空白的Facet，这样我们可以快速选中空白行：

创建这样的facet可以快速选中并移除空白行：

现在数据集中没有重复记录了。

04 使用正则表达式与GREL清理数据

清理并准备使用数据时，可能需要从文本字段中提取一些信息。有些时候，我们只需要用些分隔符将文本字段拆开。但当数据符合一些模式，并不是简单地拆分文本就能做到时，我们就需要求助于正则表达式了。

1. 准备

要学本技巧，你需要在计算机上装好OpenRefine以及一个浏览器。

我们假设你应用了前一项技巧，所以你的数据已经加载到OpenRefine，且数据类型与列中的数据相符。此外没有要求了。

2. 怎么做

我们先看下city_state_zip列中的模式。顾名思义，第一个元素是城市名，然后是州名，最后是5位数邮编。可以用空格作为分隔符拆分这个字段。

这对很多记录（例如Sacramento）都能起作用，而且其被解析成城市、州和邮编。不过这个方法有个问题—有些地名不止一个词（例如Elk Grove）。这种情况下，我们就需要做些改变。

这就轮到正则表达式展示身手了。你可以在OpenRefine中使用它转换数据。现在要将city_state_zip拆成三列：city、state和zip。单击列名旁边的向下按钮，出来的菜单中，根据情况选择Edit column或Add column。会如下图所示，出现一个窗口：

和以前一样，值指的是每个单元格的值。.match(...)方法应用到单元格的值上。它以一个正则表达式作为参数，返回的是匹配模式的一列值。正则表达式被封装在/.../之间。我们一步步解释这个正则表达式。

我们知道city_state_zip列的模式：首先是城市名（可能不止一个词），然后是州名的两字母缩写，最后是5位数字的邮编。下面给出了描述这个模式的正则表达式：

(.*) (..) (\d{5})

从后往前理解这个表达式要容易些。首先用(\d{5})提取出邮编。\d表示任何数字（等价于([0-9]{5})），{5}从字符串尾部开始选取5个数字。然后是(..)┐，用两个点来提取州名的两字母缩写。注意我们为了阅读方便，用┐替代空格符。

这个表达式提取两个字符以及一个空格—不多，不少。最后（从右往左读）是(.*)，这可理解为：（如果有的话）提取出未被另两个表达式匹配的所有字符。

总体上，这个正则表达式用普通话来表述就是：提取字符串（即使是空的）中州名的两字母缩写（前面有一个空格），后面跟有一个空格和五位表示邮编的数字。

.match(...)方法生成一个列表。本例中得到的是包含三个元素的列表。要得到城市名，可以使用下标[0]获取列表的第一个元素。要得到州名和邮编，可以分别使用下标[1]和下标[2]。

现在拆完city_state_zip列了，可以将工程导出成一个文件。在工具的右上角，你会看到Export按钮；选择Comma separated value。文件默认下载到Downloads文件夹。

3. 参考

强烈推荐Felix Lopez和Victor Romero合著的《Mastering Python Regular expressions》一书：

https://www.packtpub.com/application-development/mastering-python-regular-expressions

关于作者：托马兹·卓巴斯（Tomasz Drabas），微软数据科学家，致力于解决高维特征空间的问题。他有超过13年的数据分析和数据科学经验：在欧洲、澳大利亚和北美洲三大洲期间，工作领域遍及高新技术、航空、电信、金融和咨询。