《Using OpenRefine》翻译~12

上一篇：《Using OpenRefine》翻译~11

点4：单元格值转换

在第二章：分析和修改数据中，我们学习到OpenRefine可以自动修改一列的单元格内容，比如去除多余空格。上一点中，我们学习到聚类是另一种修改列单元格内容的方法。然而，以上方法都仅仅是单元格值转换通用方法的一部分。你可以通过不同的稍显复杂的方式修改单元格值。虽然这看起来像是EXCEL公式，但是你会惊讶于其功能的强大。

举个例子，假如你不喜欢使用管道符作为Categories列单元格值得分隔符，你想替换成为逗号后面加个空格作为分隔符。当然你可以先把多值单元格拆分然后再组合实现，但其实我们可以一步完成上述操作，点击Categories列下拉菜单选择Edit cells | Transform…. 会出现如下的转换对话框：

以上界面中最重要的部分是Expression（表达式）, 这里可以输入小脚本，用来修改值。Language 项可以让我们选择表达式的语言，现在支持的语言有General Refine Expression Language(GREL),Jython (Java环境的Python语言), 和Clojure（函数型语言，类似Lisp语言），如果你对后两种语言较熟悉，你会发现书写表达式十分简单。但是，GREL是设计特别用来作简单转换的，所以本书我们将使用GREL。

在Preview页，我们能够同时看到初始值和转换后的值，这可以让你实时的调试转换表达式，能够立即看到效果。History页保存了你曾经用过的表达式，这样你后续可以直接重用原来的公式。History页还可以对公式打上星标，这样你还可以在Starred页找到它们。最后Help页对大多数表达式做了简介。

对话框底部，我们可以确定如果表达式在某个特定单元格中运行结果出错时怎么办。你可以选择保持原值、置空、使用错误值。另外，你也可以选择将转换表达式对单元格进行重复应用的次数，这个很有用，可以对处理结果再进行处理。比如，如果你有一个表达式，用来将首字母大写的单词删除，那么就可以利用这个重复功能将单元格中所有大写单词均删除。

回到我们的任务：将管道符修改为逗号后面加个空格。表达式处默认值是value，就像你猜到的那样，这代表原始值。让我们做个小实验看看会发生什么：输入1234作为新单元格值 。预览界面会更新，显示所有单元格值变为1234。这当然没什么意义，但对于你理解功能肯定有用。我们真正要做的是对原值按照我们的意愿进行替换。在Help页,我们找到实现上面意图的GREL函数叫做replace。因为我们想把管道符替换成逗号，所以我们输入value.replace("|", ", ")。这里我们对字符加了双引号，因为它们是字符串而非数字。预览界面会刷新单元格值，我们发现就是我们想要的结果，所以点击OK。只需要很短的时间，OpenRefine就转换好了Categories列的单元格内容，所有的管道符已经被替换。

在修改分隔符时需要特别小心，因为有可能分隔符恰恰是值内容中的一部分。比如：分类Cup, saucer and plate sets中包含逗号，所以如果对这个字段进行替换则很有可能导致错误。但是，我们这里举例的时候还是会用逗号。

为了让我们更加熟练值转换操作，我们再尝试下，这次假设我们想把Provenance列中的分号修改为逗号。点击Provenance(Production)下拉菜单Edit cells, Transform…，然后输入表达式value.replace(";", ",")。表达式本身并没有错，但是我们在预览窗口中发现有许多错误提示。这刚刚可以让我们试着设置On error参数设定（我也建议你试试看），当然更重要的是我们要知道究竟出了什么问题。错误信息如下：Error: replace expects 3 strings, or 1 string, 1 regex, and 1 string。所以错误的原因是我们传递replace参数时出错了。如果我们检查下出现错误的单元格，我们发现是有些单元格为null所导致。确实，因为null并不是字符串，所以我们并不能对其中的字符进行操作。所以我们需要告诉OpenRefine只转换非空的单元格。点击Cancel,关闭对话框，再点击Provenance(Production)列下拉菜单选择Text filter.虽然我们可以选择Facet by blank功能，但是我们可以通过直接选择包含分号的单元格来节省时间，在文本过滤中输入”;”就能够筛选出我们需要的内容。现在再试着进行单元格转换，我们发现筛选出的单元格内容都进行了替换，并且没有任何出错提示。

最后，为了展示单元格值转换的强大功能。我们将做一些复杂点的操作。虽然管道符已经被替换成了逗号，但是Categories列还有一些其他问题存在。确实，有一些值内容中就包含重复内容，比如第14条记录：Didactic displays, Pearl hells, Buttons,Didactic displays。我们无法采用上面的重复项处理来解决，因为这里的重复是在一个单元格内的。幸运的是，我们可以使用GREL来解决。打开Categories列的值转换窗口，输入如下表达式：value.split(", ").uniques().join(", ")。这个表达式看起来有点复杂，但是分开解读还是比较容易理解的。首先第一个我们将值按照“，”分割（逗号后面跟一个空格），然后使用uniques函数去重，最后再把内容重新连接到一起。当你点击OK后，OpenRefine会完成操作并且提示你有多少单元格进行了该项操作。

掌握GREL是简单的

本小点介绍了一些例子，如果这里还无法满足你的要求，那么附录：正则表达式和GREL将完整的介绍GREL，学习后你将可以自己书写表达式。

 

 

点5：增加源列

有时候你可能希望在单元格值转换的时候保留原单元格值。当然你可以在操作错误的时候通过Undo / Redo页恢复原值。但是如果能够同时显示原值和转换后的值得话一定会更好。因此，OpenRefine提供了增加一列，并且该列是基于其他列创建的功能。

假设我们希望增加一列，这一列是对应分类单元格中分类计数。点击Categories列，选择Edit column-> Add column based on this column…会弹出一个类似单元格转换的对话框。不过，这次其提示需要一个列名。在New column name中输入Category Count。现在我们可以创建分类计数的表达式了。因为分类是被某个字符分隔的（管道符或者逗号，如果你学习过上一点的话），我们可以简单的将它们拆分然后计数。表达式如下：value.split(",").length() ，Preview面板会显示结果，如果发现没有问题，那么点击OK.确认。

OpenRefine增加了一列新列，这样我们就可以使用Category Count列来分析我们的数据。比如，我们可以对Category Count列进行文本透视：Facet|Text facet. 因为我们只是想对每个独立的计数数量进行分析，而不是分析其值域。所以这里使用文本透视比较简单，虽然这列数据是数字格式。左侧透视界面会显示究竟分类数如何分布，如下图所示：

我们发现有一点比较奇怪：有一栏的值是(blank)。如果我们点击下，我们发现所有的记录Categories字段为空，那么为什么Category Count不显示为0呢？答案是对于空值单元格，这个转换表达式会导致一个错误，因为没有值可以被分割。上一点的学习中，我们可能能够做到在转换前把这些行过滤掉了。不过我们这里尝试修复这些行。在透视界面中点击（blank），点击Category Count 下拉菜单： Edit cells|Transform…. 如果我们在表达式窗口中输入0并且点击OK,那么这些空值单元格就会变成0。那些行会消失，别奇怪，因为我们在透视中还选中着blank，点击过滤项 0（或者其他你像选择的），你就能 得到你想要的数据。

从数据分析角度考虑，我们对于每条记录中分类数计数中那些是最普遍的比较感兴趣。在Category Count透视界面中，选择Sort by:设置成 count.我们发现每条记录中分类数为2的最普遍，不过请注意只有这些值已经聚类和去重后才是比较准确的，就像上一点所介绍的。有一次，我们感受到了GREL的强大。

点6：拆分列

本章开始的时候，我们演示了如何将一个单元格中的多值内容拆分成多行。但是，有时候这并不能解决问题。现有例子中，单元格中的多个分类其实属于同一个属性：分类间比较相似，并且顺序可以互换。但是如果多值单元格中的内容属于不同的类别的话情况就不同了。比如，当一个clients表格包含电话字段但是没有email字段，而某一个客户却同时又上面两种信息。结果这个人的电话信息和email信息可能会被放在一起，用斜杠分隔。

在Powerhouse Museum数据集中，我们也在很多列种发现这种情况。比如，在Provenance字段，我们能够同时发现designers, makers等等信息。如果我们能够把它们拆分成不同的列的话，我们分析就会更加有意义。我们可以点击Provenance (Production)列下拉菜单： Edit column| Split into several columns….，我们能够看到如下的拆分对话框：

我们可以选择按照曾经介绍的by separator（按分隔符）分割, 还可以按照by field lengths.（按长度）分割。按长度分割在数据结构固定的时候特别有用，如1987 en-us,X/Y这样的类型，其没有固定的分隔符（或者根本没有）。当然，本例中我们使用分隔符。一个有趣的选项是设置一个限制，因为可能有分割成很多列，所以设置下限制比如5会比较明智。千万不要忘记设置一个分隔符，比如这里设置成“|”。你可以选择让OpenRefine猜测下分割后的单元格类型（比如分割后可能会出现数字），还可以让OpenRefine删除原来的列。

在你点击OK后，你会发现OpenRefine将原来叫做Provenance (Production) 的列被替换成为了很多叫做 Provenance(Production) 1, Provenance (Production) 2等等的列。这些列可以后续按照实际含义修改名称。并不是所有列都有内容，只有那些至少含有5项的单元格分割后才会所有单元格都有内容。虽然分割后不会超过你设置的最大列数目，但是还是会产生分割前的单元格分项较少，导致无法填满所有的列的情况。

我们再对Object Title列进行分割，我们发现该列中有些单元格是以数字开头的。如果把数字从中剥离出来应该十分有意义。首先，让我们先过滤出这些数字开头的项。点击Object Title 下拉菜单中的Text filter.我们这里写上以数字开头的正则表达式^\d.这个表达式告诉过滤器从开头(^)进行查找并且寻找数字(\d)。别忘了勾选regular expression 。否则 OpenRefine 会把正则表达式当成字符查找。现在我们就得到了那些以数字开头的项。

点击Object Title下拉菜单，然后选择：Edit column |Split into several columns…. 现在我们使用一个空格作为分隔符，设置我们最多要2列然后点击OK. 数字和标题被分割成了两列。

分割列比分割多值单元格功能更加强大，因为其有很多配置选项。你甚至可以使用正则表达式来定义分隔符，这样我们可以做到不同的内容应用不同的分隔符。另外，如果你对本小点中的正则表达式有些疑惑，别忘了参考附录：正则表达式和GREL，那里介绍的非常详细。

下一篇：《Using OpenRefine》翻译~13