《常用数据清洗工具及基本操作》课件.pptx

1、第四章 常用数据清洗工具及基本操作4.1 Microsoft Excel数据清洗基本操作4.2 Kettle简介及基本操作4.3 OpenRefine简介及基本操作4.5 Hawk简介及基本操作 4.4 DataWrangler简介及基本操作4.6 上机练习与实训习题4.1 Microsoft Excel数据清洗基本操作第四章 常用数据清洗工具及基本操作 4.1.1Excel数据清洗概述Microsoft Excel是微软公司Microsoft Office系列办公软件的重要组件之一，是一个功能强大的电子表格程序，能将整齐而美观的表格呈现给用户，还可以将表格中的数据通过多种形式的图形、图表表现

2、出来，增强表格的表达力和感染力。Microsoft Excel也是一个复杂的数据管理和分析软件，能完成许多复杂的数据运算，帮助使用者做出最优的决策。利用Excel内嵌的各种函数可以方便地实现数据清洗的功能，并且可以借助过滤、排序、作图等工具看出数据的规律。另外，Excel还支持VBA编程，可以实现各种更加复杂的数据运算和清理。作为一款桌面型数据处理软件，Excel主要面向日常办公和中小型数据集的处理，但在面对海量数据的清洗任务时却是难以胜任的，即使是小型数据集在使用前也存在需要规范化的问题，因此，通过在Excel中进行数据清洗的实践操作，有助于帮助读者理解数据清洗的概念和知识，并掌握一定的操作

3、技巧，为后面进行大数据集的清洗打好基础。4.1 Microsoft Excel数据清洗基本操作第四章 常用数据清洗工具及基本操作主要针对与数据清洗密切相关的操作和注意事项做简要介绍1Excel数据清洗相关操作快速定位和快速填充12数据分列3Excel中的数据类型和数据格式第四章 常用数据清洗工具及基本操作存储 存储成本下降4.1 Microsoft Excel数据清洗基本操作 1数据分列在利用Excel进行数据处理过程中，常会遇到1列单元格中的数据是组合型的情况，即粒度过大，如“2017-03-25 Saturday 18:22”，包含日期、星期和时间3个部分，如图4-1所示。需要将之拆分为独

4、立的3列，这时就可以采用分列功能实现，操作步骤为：步骤1：选定要进行分列的数据，然后单击“数据”工具栏，选择“分列”，如图4-2所示。图4-1 组合型数据示例 图4-2 选择“分列”操作第四章 常用数据清洗工具及基本操作4.1 Microsoft Excel数据清洗基本操作步骤2：出现文本分列向导（本向导也可以在选中待分列区域后，按Alt+A+E快捷键快速打开），如图4-3所示，默认选中“分隔符号”，单击“下一步”按钮。图4-3 文本分列向导第1步步骤3：选择分隔符号，本例中为空格，所以选中“空格”复选框，选中后，在数据预览的区域里就会显示按照要求分隔后的格式，如图4-4所示，单击“下一步”按

5、钮。图4-4 文本分列向导第2步第四章 常用数据清洗工具及基本操作4.1 Microsoft Excel数据清洗基本操作步骤4：设置分列后各列的数据格式，根据实际情况而定，这里设为文本格式，选中“文本”单选按钮，如图4-5所示。步骤5：设置分列后，还可设置数据存放的区域，如图4-6所示，单击“完成”按钮。图4-5 文本分列向导第3步图4-6 设置数据存放区域存储 存储成本下降第四章 常用数据清洗工具及基本操作4.1 Microsoft Excel数据清洗基本操作可以看到，数据已被完美地分开，如图4-7所示。图4-7 完成数据分列 第四章 常用数据清洗工具及基本操作4.1 Microsoft E

6、xcel数据清洗基本操作2 快速定位和快速填充在日常的工作中经常会看到一些重复项合并的Excel表格，如月份、地区等，主要是为了方便查看，如图4-8所示A列的销售区。但这样的工作表，没有办法使用数据透视表功能进行统计、汇总和分析等。图4-8 重复项合并示例第八章 清洗RDBMS数据实例 第四章 常用数据清洗工具及基本操作4.1 Microsoft Excel数据清洗基本操作对此，可以使用Excel的“定位”功能来实现快速填充，步骤如下：步骤1：选中A列，单击“合并后居中”按钮，取消单元格合并，结果如图4-9所示。图4-9 取消单元格合并第八章 清洗RDBMS数据实例 第四章 常用数据清洗工具及

7、基本操作4.1 Microsoft Excel数据清洗基本操作步骤2：选中A列，然后依次单击“查找和选择”“定位条件”“空值”按钮（或按Ctrl+G快捷键弹出“定位”对话框后，单击“定位条件”按钮，如图4-10所示）。步骤3：在随后弹出的“定位条件”对话框中选中“空值”单选按钮，然后单击“确定”按钮，如图4-11所示。图4-10 “定位”对话框 图4-11 “定位条件”对话框第八章 清洗RDBMS数据实例 第四章 常用数据清洗工具及基本操作4.1 Microsoft Excel数据清洗基本操作步骤4：在定位的空值单元格中输入“=A3”（根据实际情况输入），如图4-12所示。步骤5：按Ctrl+

8、Enter快捷键完成填充，结果如图4-13所示。图4-12 输入定位条件 图4-13 完成定位填充第四章 常用数据清洗工具及基本操作4.1 Microsoft Excel数据清洗基本操作 3 Excel中的数据类型和数据格式在Excel中，数据类型只有3种，分别是文本型、数字型和逻辑型。所有单元格默认的类型为数字型；当输入内容是以单引号为先导符时为文本型，一般当单元格中的数据为文本型时，单元格的左上角会出现绿色的小三角型标记；逻辑型是指运算结果为TRUE或FALSE的二值型数据。3种类型分别可以用函数istext()、isnumber()和islogical()进行判断。3种数据类型的对应关系

9、如图4-14所示。数据格式是指Excel中各个数据类型的外在表现形式，同一数据类型有多种数据格式，在工具栏上单击“设置单元格格式”按钮（或在单元格中右击，在弹出的快捷菜单中选择选择“设置单元格”命令），出现设置数据格式对话框，如图4-15所示。图4-14 Excel的数据类型图4-15 设置单元格格式4.1 Microsoft Excel数据清洗基本操作第四章 常用数据清洗工具及基本操作关于数据类型和数据格式的关系主要有以下几点：1所有单元格默认的类型为数字型，单元格格式的改变不会改变数据类型本身，但单元格格式会影响新生成数据的类型。2 以文本形式存储的数字，在参与四则运算时会转变成为数字，结

10、果为数字型；在参与函数运算时会忽略不计，但运算结果仍为数字型。以上是Excel数据清洗的常用操作介绍，使用数据分列功能是为了使数据的粒度变小；定位填充功能是为了将原始数据中存在的合并居中现象取消，并实现快速的数据填充，实例中仅使用了定位条件中的“空值”，日常工作中可以根据实际需要，选取其他的条件；正确理解Excel中数据类型和数据格式的区别和联系，有利于在实际的数据操作中避免错误。4.1 Microsoft Excel数据清洗基本操作第四章 常用数据清洗工具及基本操作Excel的函数功能十分强大，同时也非常复杂，其中很多都可以直接用来进行初步的数据清洗操作，本节按照功能介绍10类函数，根据经验

11、，这些函数在实际的数据清洗工作中使用频率较高，应用面也较广，使用这些函数可以让工作事半功倍。2Excel数据清洗常用函数AVERAGE函数12SUM函数3COUNT函数4INT函数和ROUND函数5IF函数67NOW函数和TODAY函数8ISNUMBER函数、ISTEXT函数和ISLOGICAL函数9MAX函数和MIN函数10SUMIF函数和COUNTIF函数HLOOKUP函数和VLOOKUP函数154.1 Microsoft Excel数据清洗基本操作第四章 常用数据清洗工具及基本操作SUM函数用来承担数学的加法运算，其参数可以是单个数字或一组数字，因此它的加法运算功能十分强大。使用一个单元

12、格区域的语法结构：=SUM(A1:A12)使用多个单元格区域的语法结构：=SUM(A1:A12,B1:B12)AVERAGE函数是频繁使用的一个统计函数，用于计算数据集的平均值。其参数可以是数字，或者是单元格区域。使用一个单元格区域的语法结构：=AVERAGE(A1:A12)使用多个单元格区域的语法结构：=AVERAGE(A1:A12,B1:B12)16COUNT函数用于统计含有数字的单元格的个数。注意：COUNT函数不会将数字相加，而只是统计共有多少个数字。COUNT函数的参数可以是单元格、单元格引用或者数字本身。COUNT函数会忽略非数字单元格的值。例如，如果A1:A10是COUNT函数的

13、参数，但是其中只有两个单元格含有数字，那么COUNT函数返回的值是2。使用一个单元格区域的语法结构：=COUNT(A1:A12)使用多个单元格区域的语法结构：=COUNT(A1:A12,B1:B12)4.1 Microsoft Excel数据清洗基本操作第四章 常用数据清洗工具及基本操作1617INT函数和ROUND函数都是将一个数字的小数部分删除，两者的区别在于：INT函数是无条件地将小数部分删除，无须进行四舍五入。该函数只有一个参数，语法结构：=INT(number)需要注意的是，INT函数总是向下舍去小数部分。例如，INT(-5.1)和INT(-5.9)都是等于-6，而不是-5，因为-6

14、才是-5.1和-5.9向下舍入的数字。相反，ROUND函数是将一个数字的小数部分四舍五入。该函数有两个参数：需要计算的数字和需要四舍五入的小数位数，语法结构：=ROUND(number,小数位数)另外还有两个函数ROUNDUP和ROUNDDOWN，可以规定是向上舍入还是向下舍入。ROUNDUP和ROUNDDOWN的语法结构与ROUND相似：=ROUNDUP(number,小数位数)=ROUNDDOWN(number,小数位数)4.1 Microsoft Excel数据清洗基本操作第四章 常用数据清洗工具及基本操作1717184.1 Microsoft Excel数据清洗基本操作第四章 常用数据

15、清洗工具及基本操作1818IF函数的主要用途是执行逻辑判断，根据逻辑表达式的真假，返回不同的结果，从而执行数值或公式的条件检测任务。逻辑判断的结果是返回一个TRUE或FALSE的值，注意这里的TRUE或FALSE不是正确和错误的意思，而是逻辑上的真与假的意思。IF函数的语法结构：=IF(逻辑判断,为TRUE时的结果,为FALSE时的结果)例如，给出的条件是B25C30，如果实际情况是TRUE，那么IF函数就返回第二个参数的值；如果是FALSE，则返回第三个参数的值。IF函数常常用来检查数据的逻辑错误，如使用二分法的多选题录入时，出现了1和0以外的数字，可以通过如下设置，过程如图4-19所示：步

16、骤1：选中数值区域格式条件格式公式。步骤2：输入公式，设置格式。图4-19 数据逻辑错误检查19NOW函数根据计算机现在的系统时间返回相应的日期和时间。TODAY函数则只返回日期。NOW函数和TODAY函数都没有参数。语法结构如下：=NOW()=TODAY()TODAY函数常用来计算过去到“今天”总共有多少天的计算上。例如，项目到今天总共进行多少天了？在一个单元格上输入开始日期，另一个单元格输入公式减去TODAY得到的日期，得出的数字就是项目进行的天数。请注意可能需要更改单元格的格式，才能正确显示所需要的日期和时间格式。4.1 Microsoft Excel数据清洗基本操作第四章 常用数据清洗

17、工具及基本操作1919HLOOKUP函数和VLOOKUP函数都可以用来在表格中查找数据。所谓的表格是指用户预先定义的行和列区域。具体来说，HLOOKUP返回的值与需要查找的值在同一列上，而VLOOKUP返回的值与需要查找的值在同一行上。两个函数的语法结构是：=HLOOKUP(查找值,区域,第几行,匹配方式)=VLOOKUP(查找值,区域,第几列,匹配方式)这两个函数的第一个参数是需要查找的值，如果在表格中查找到这个值，则返回一个不同的值。204.1 Microsoft Excel数据清洗基本操作第四章 常用数据清洗工具及基本操作2020ISNUMBER函数、ISTEXT函数和ISLOGICAL

18、函数这3个函数的功能是判断Excel的数据类型，ISNUMBER函数判断单元格中的值是否是数字，ISTEXT函数判断单元格中的值是否是文本，ISLOGICAL函数判断单元格中的值是TRUE或FALSE，这3个函数的返回值均为TRUE或FALSE。语法结构是：=ISNUMBER(value)=ISTEXT(value)=ISLOGICAL(value)MAX函数和MIN函数是在单元格区域中找到最大和最小的数值。两个函数可以拥有30个参数，参数还可以是单元格区域。两个函数的语法结构是：=MAX(number1,number2,)=MIN(number1,number2,)使用一个单元格区域的语法结

19、构：=MAX(A1:A12)使用多个单元格区域的语法结构：=MAX(A1:A12,B1:B12)SUMIF函数有3个参数，其语法结构：=SUMIF(判断范围,判断要求,汇总的区域)第一个参数可以与第三个参数不同，即实际需要汇总的区域可以不是应用判断要求的区域。第三个参数可以忽略，忽略的情况下，第一个参数应用条件判断的单元格区域就会用来作为需要求和的区域。COUNTIF函数用来计算单元格区域内符合条件的单元格个数。COUNTIF函数只有两个参数，其语法结构：=COUNTIF(单元格区域,计算的条件)如果其中一个单元格的值符合条件，则不管单元格里面的值是多少，返回值是1。利用这一特性可以进行重复数

20、据的处理。例如：对图4-20中的数据进行处理，分别找出重复值和非重复值。214.1 Microsoft Excel数据清洗基本操作第四章 常用数据清洗工具及基本操作2121SUMIF函数和COUNTIF函数分别根据条件汇总或计算单元格个数，Excel的计算功能因此大大增强。图4-20 寻找重复值和非重复值B1=COUNTIF(A:A,A1)寻找重复值；C1=COUNTIF(A$1:A1,A1)筛选出所有非重复项（筛选出1即可）。221.1 Microsoft Excel数据清洗基本操作第四章 常用数据清洗工具及基本操作22223Excel数据清洗操作的注意事项 同一份数据清单中避免出现空行和空

21、列；数据清单中的数据尽可能细化，不要使用数据合并；构造单行表头结构的数据清单，不要有两行以上的复杂表头结构；单元格的开头和末尾避免输入空格或其他控制符号；在一个工作表中要避免建立多个数据清单，每个工作表仅使用一个数据清单；当工作表中有多个数据清单时，则数据清单之间应至少留出一个空列和一个空行，以便于检测和选定数据清单；关键数据应置于数据清单的顶部或底部；对原始工作表做好备份，在执行完所有的清洗操作并确认无误后再复制到原始表中。234.1 Microsoft Excel数据清洗基本操作第四章 常用数据清洗工具及基本操作2323 4.1.2Excel数据清洗现有一个企业招聘职位信息的数据集，约有5

22、000条数据，客户提出需要了解数据分析师岗位情况，包括岗位分布和特点、能力要求、工资和薪酬等。由于数据集没有经过处理，所以表中的数据还很不规范，含有大量数据重复、缺失、单列数据粒度过大等问题，因此，在进行数据分析前，需要进行数据清洗操作，以使数据规范化。下面介绍执行数据清洗的主要过程。244.1 Microsoft Excel数据清洗基本操作第四章 常用数据清洗工具及基本操作24241数据预览拿到数据后，不要急着动手处理，先对数据集做总体的观察。如图4-21所示，可以看到，数据集表头由城市、公司名称、公司编号、公司福利、公司规模、经营区域、经营范围、教育程度、职位编号、职位名称、薪水和工作年限

23、要求等属性组成。图4-21 招聘信息数据集254.1 Microsoft Excel数据清洗基本操作第四章 常用数据清洗工具及基本操作2525数据整体较为规整，但通过初步观察，该数据集主要存在如下问题：（1）数据缺失（2）数据不一致（3）存在“脏”数据（4）数据不规范264.1 Microsoft Excel数据清洗基本操作第四章 常用数据清洗工具及基本操作26262进行数据清洗（1）清洗薪水数据（2）分列操作（3）搜索替换不一致第四章 常用数据清洗工具及基本操作4.2 Kettle简介及基本操作4.1 Microsoft Excel数据清洗基本操作4.3 OpenRefine简介及基本操作4

24、.5 Hawk简介及基本操作 4.4 DataWrangler简介及基本操作4.6 上机练习与实训习题大数据应用人才培养系列教材284.2 Kettle简介及基本操作第四章 常用数据清洗工具及基本操作2828大数据技术中，数据清洗的前期过程可简单地认为就是ETL的过程。ETL（Extract-Transform-Load）负责将分散的、异构数据源中的数据如关系数据、平面数据文件等抽取到临时中间层，进行清洗、转换、集成，最后加载到数据仓库或数据集市中，作为联机分析处理、数据挖掘提供决策支持的数据。在整个数据仓库的构建中，ETL工作占整个工作的50%70%，是构建数据仓库的重要一环，用户从数据源抽

25、取所需的数据，经过数据清洗，最终按照预先定义好的数据仓库模型，将数据加载到数据仓库中。本节介绍一款开源的ETL工具Kettle。294.2 Kettle简介及基本操作第四章 常用数据清洗工具及基本操作29294.2.1 Kettle软件概述1Kettle简介Kettle是一款国外的开源ETL工具，也是世界上最流行的开源商务智能软件Pentaho的主要组件之一，中文名称叫水壶，主要用于数据库间的数据迁移，商业名称PDI，纯Java编写，可跨平台运行，主要作者为Matt。2005年12月，Kettle成为开源软件。Kettle使用图形界面进行可视化的ETL过程设置操作，以命令行形式执行，支持非常广

26、泛的数据库类型与文本格式输入和输出，支持定时和循环，实现了把各种数据放到一个壶中，并按用户的要求格式输出，具有可集成、可扩展、可复用、跨平台、高性能等优点，目前在国内外大数据项目上有广泛的应用。Kettle软件主要由4个组件组成：Spoon、Pan、Chef和Kitchen。Spoon是一个图形化界面，用于设计ETL转换过程（Trans formation）。Pan批量运行由Spoon设计的ETL转换，是一个后台执行的程序，没有图形界面。Chef用于创建任务（Job）。通过允许每个转换、任务、脚本等，进行自动化更新数据仓库的复杂工作。Kitchen也是一个后台运行的程序，功能是批量使用由Che

27、f设计的任务。304.2 Kettle简介及基本操作第四章 常用数据清洗工具及基本操作30302Kettle软件下载和安装Kettle软件的下载地址为http:/ Integration/4.40-syable/pdi-ce-4.4.0-stable.zip，本书主要介绍Kettle在Windows环境下的安装、配置和使用。由于软件基于Java环境运行，所以安装前先要配置Java运行环境，要注意Kettle版本和Java版本的匹配，这里需要安装的Java版本为1.7.0_79。解压下载的文件，在解压的文件夹里，可以看到Kettle的启动文件Kettle.exe或Spoon.bat。双击运行，就

28、可以看到Kettle的开始界面，显示软件相关版本信息与GNU相关协议说明，如图4-30所示。图4-30 Kettle开始界面314.2 Kettle简介及基本操作第四章 常用数据清洗工具及基本操作31313Kettle软件界面显示开始界面后，Kettle会弹出资源库连接（Repository Connection）对话框，可以输入特定资源库的用户名和密码完成登录，如图4-31所示。登录时单击Cancel按钮即可进入Kettle，此时所定义的转换和工作是以XML文件方式存储在本地磁盘上，以.ktr和.kjb作为后缀名。若使用资源库登录，则所有定义的转换和工作将会存储到资源库里，资源库即数据库，例

29、如SQL Server数据库，里面存储了Kettle定义的元素的相关元数据库。资源库创建完毕，其相关信息将存储在repositories.xml文件中，它位于默认home目录的隐藏目录.kettle中。如果是Windows系统，该路径为C:Documents and Settings.kettle。进入Kettle设计界面，弹出“Spoon提示信息”对话框，直接单击“关闭”按钮，如图4-32所示。图4-31 资源库连接对话框图4-32 Spoon提示信息324.2 Kettle简介及基本操作第四章 常用数据清洗工具及基本操作3232Kettle设计界面如图4-33所示，按数字标识顺序，各主要功

30、能为：Kettle软件的菜单栏；Kettle软件的快捷工具栏；透视图功能，包括数据集成、模型和可视化3个组件；在使用Kettle时所涉及使用到的对象；Kettle中所有的组件；根据选择或者显示相应的结果；Kettle设计界面。图4-33 Kettle设计界面334.2 Kettle简介及基本操作第四章 常用数据清洗工具及基本操作33334.2.2 Kettle基本操作Kettle的主要功能是用来转换或者抽取数据，即ETL。Kettle提供了资源库的方式来整合所有的工作，如图4-34所示为Kettle的概念模型。一个数据抽取过程主要包括创建一个作业，每个作业可以包括多个转换操作。转换主要是操作数

31、据库，由编写和执行SQL语句、配置数据库地址等一系列步骤构成。一个完整的作业包括开始、作业、成功3个节点，针对作业进行编辑，选择作业所调用的转换，在转换中可以配置查询操作、更新操作或者插入操作等。上述操作均可使用软件中的工具执行，也可以通过编写程序调用的方式来实现。图4-34 Kettle概念模型344.2 Kettle简介及基本操作第四章 常用数据清洗工具及基本操作34341转换（transformation）转换主要是针对数据的各种处理，其本质是一组图形化的数据转换配置的逻辑结构，一个转换由若干个步骤（Steps）和连接（Hops）构成，转换文件的扩展名是.ktr。如图4-35所示的转换例

32、子，是一个从文本文件中读取数据、过滤、排序，然后将数据加载到数据库的过程。图4-35 Kettle转换实例354.2 Kettle简介及基本操作第四章 常用数据清洗工具及基本操作3535（1）步骤转换的构建模块，如一个数据文件的输入或一个表的输出就是一个步骤。按不同的功能分类，Kettle中的步骤主要有输入类、输出类和脚本类等。每种步骤用于完成某种特定的功能，通过配置一系列的步骤就可以完成相关的数据转换任务。（2）连接数据的通道，用于连接两个步骤，实现将元数据从一个步骤传递到另一个步骤。构成一个转换的所有步骤，并非按顺序执行，节点的连接只是决定了贯穿在步骤之间的数据流，步骤之间的顺序并不是转换

33、执行的顺序。当执行一个转换时，每个步骤都以其自己的线程启动，并不断地接收和推送数据。在一个转换中，所有的步骤是同步开启并运行的，所以步骤的初始化顺序是不可知的。因此我们不能在第一个步骤中设置一个变量，并试图在后续的步骤中使用它。一个步骤可以有多个连接，数据流可以从一个步骤流到多个步骤。364.2 Kettle简介及基本操作第四章 常用数据清洗工具及基本操作36362作业（Jobs）作业是比转换更高一级的处理流程，基于工作流模型协调数据源、执行过程和相关依赖性的ETL活动，实现了功能性和实体过程的聚合，作业由作业节点连接、作业项（Job Entry）和作业设置组成，作业文件的扩展名是.kjb。一

34、个作业中展示的任务有从FTP获取文件、核查一个数据库表是否存在、执行一个转换、发送邮件通知一个转换中的错误等，最终的结果可能是数据仓库的更新等。374.2 Kettle简介及基本操作第四章 常用数据清洗工具及基本操作37374.2.3 Kettle数据清洗实例操作现有一个关于银华基金的基金名称和基金代码信息的数据集，如图4-43所示。由于原始数据是通过网络爬虫抓取获得的，所以数据集存在数据错误和重复的问题；另外，抓取的基金名称是字符串型数据，有可能会出现字符编码的乱码或者字符串后有换行符等问题，所以需要对该数据集做清洗操作，本节介绍利用Kettle实现数据清洗的过程。启动Kettle软件，新建

35、一个转换并保存，如图4-44所示。图4-44 新建一个转换并保存384.2 Kettle简介及基本操作第四章 常用数据清洗工具及基本操作3838从左侧“输入”列表中选择Data Grid（行静态数据网格）并拖放到转换设计区，双击打开设置窗口，引用要读取数据的网址，如图4-45和图4-46所示。图4-45 设置读取网址1图4-46 设置读取网址2394.2 Kettle简介及基本操作第四章 常用数据清洗工具及基本操作3939再拖入一个Http client，通过HTTP调用Web服务，如图4-47所示。选择Accept URL from field选项，并选择index作为URL的来源字段。注意

36、字符集的设置，避免后面获取的接口数据出现乱码。图4-47 获取网页源代码404.2 Kettle简介及基本操作第四章 常用数据清洗工具及基本操作4040再从“脚本”列表中拖入Modified Java Script Value，用于脚本值的改进，并改善界面和性能。在Java Script里写入正则表达式对通过Http client组件得来的源代码进行解析，如图4-48和图4-49所示。图4-48 Java script代码编辑窗口图4-49 解析代码414.2 Kettle简介及基本操作第四章 常用数据清洗工具及基本操作4141再从“转换”列表中拖入Split field to rows，用分

37、隔符分隔单个字符串字段，并为每个分割项创建一个新行，如图4-50和图4-51所示。图4-50 分割字符1图4-51 分割字符2424.2 Kettle简介及基本操作第四章 常用数据清洗工具及基本操作4242继续拖入“查询”列表中的“流查询”，从转换中的其他流里查询值并将其放入“简称”这个字段里，如图4-52所示。再拖入Flow列表中的“过滤记录”，定制过滤条件，用相等或者不相等的判断表达式来过滤数据，如图4-53所示。最后拖入“输出”列表中的Microsoft Excel Writer，使用Excel组件中的Microsoft Excel Writer组件将数据写入Excel，如图4-54所示

38、。图4-52 流查询设置图4-53 过滤记录设置图4-54 数据写入Excel设置434.2 Kettle简介及基本操作第四章 常用数据清洗工具及基本操作4343完成以上步骤之后，在菜单栏中选择Action“运行”命令即可，运行结果如图4-55所示。可以看到，在执行结果中显示了执行的步骤名称、读写次数、处理条目、处理时间和处理速度等信息。图4-55 数据清洗处理设置完成图最终清洗处理后的数据集如图4-56所示。图4-56 清洗处理后的数据集第四章 常用数据清洗工具及基本操作4.2 Kettle简介及基本操作4.1 Microsoft Excel数据清洗基本操作4.3 OpenRefine简介及

39、基本操作4.5 Hawk简介及基本操作 4.4 DataWrangler简介及基本操作4.6 上机练习与实训习题大数据应用人才培养系列教材454.3 OpenRefine简介及基本操作第四章 常用数据清洗工具及基本操作45454.3.1 OpenRefine软件概述OpenRefine最初叫作Freebase Gridworks，由一家名为Metaweb的公司开发，主要用于调试各种表格，以避免随着时间的推移出现错误，这对于任何数据库来说都是一个很大的问题。后来，该软件被谷歌收购，更名为Google Refine，并发布了第2版。2012年10月，Google Refine被社区接管，并以Ope

40、nRefine为名进行了开源。OpenRefine是典型的交互数据转换工具（Interactive Data Transformation tools，IDTs），可以观察和操纵数据，使用单个的集成接口，对大数据进行快速、高效的操作。它类似于传统的表格处理软件Excel，但是工作方式更像是数据库，以列和字段的方式工作，而不是以单元格的方式工作。1.OpenRefine简介OpenRefine的主要功能有以下几种：多种格式的数据源文件支持，如JSON、XML、Excel等，除此之外，还可以通过插件的方式为OpenRefine添加更多格式的数据源的支持。数据的探索与修正。OpenRefine支持对

41、数据的排序、分类浏览、查重、文本数据过滤等操作。还支持对单个列中的数据进行分割、将多个列的数据通过某种规则合并、对相似的数据进行聚类、基于已有数据生成新的数据列、行列转换等，而且这些操作都非常简单快捷。关联其他数据源。数据是相互联系的，OpenRefine支持将自己的数据与其他数据源进行关联，如将人员数据与Facebook数据进行关联。通过插件的方式，能够实现各种数据之间的关联。464.3 OpenRefine简介及基本操作第四章 常用数据清洗工具及基本操作4646OpenRefine基于Java环境运行，因此是跨平台的。OpenRefine 2.6版是它改名以来的第一个发行版本，目前最新版本

42、为2.7。本书将采用Google Refine 2.5版本进行介绍，所有OpenRefine的具体介绍和操作均是指Google Refine 2.5。最新版OpenRefine的下载地址为http:/openrefine.org/。Google Refine 2.5版的下载地址为https:/ Refine 2.5版下载地址下载zip包。解压到某个目录。双击google-refine.exe文件，启动OpenRefine，如图4-57所示。图4-57 OpenRefine主界面474.3 OpenRefine简介及基本操作第四章 常用数据清洗工具及基本操作47474.3.2 OpenRefin

43、e基本操作创建OpenRefine项目十分简单，只需要选择文件、预览数据内容、确认创建3个步骤。通过单击“创建项目”标签页、选择数据集、单击“下一步”按钮来创建新项目。在OpenRefine中加载数据后，将显示如图4-58所示的界面内容。图4-58 OpenRefine数据界面484.3 OpenRefine简介及基本操作第四章 常用数据清洗工具及基本操作4848在界面中，主要显示了数据集的总行数、显示选项、数据列名称和菜单、数据内容等信息。在开始剖析清理数据前，十分重要的一点是确保OpenRefine较好地载入并显示了数据：查看列名称是否被解析正确（数据显示较宽时请使用水平滑动条）、单元格类

44、型是否正确等。默认情况下，显示的数据数目为10条，可以单击Show选项中的数值改变显示条目数量，最大值为50。下面继续介绍几种OpenRefine数据清洗的常用操作。1排序（Sort）操作排序是观察数据的常用手段，因为排过序的数据更加容易理解和易于分析，在OpenRefine相关列名的下拉菜单中选择Sort，如图4-59所示，将打开排序操作窗口，如图4-60所示。图4-59 选择“排序”操作 图4-60 排序对话框494.3 OpenRefine简介及基本操作第四章 常用数据清洗工具及基本操作4949单元格值可以按照文本（区别大小写或者不区别）、数字、日期、布尔值排序，对每个类别有两种不单元格

45、值可以按照文本（区别大小写或者不区别）、数字、日期、布尔值排序，对每个类别有两种不同的排序方式：同的排序方式：q q TextText（文本）：（文本）：az排序或者排序或者za排序。排序。q q NumbersNumbers（数字）：升序或者降序。（数字）：升序或者降序。q q DatesDates（日期）：升序或者降序。（日期）：升序或者降序。q q BooleansBooleans（布尔值）：（布尔值）：FALSE值先于值先于TRUE值或值或TRUE值先于值先于FALSE值。值。还可以对错误值和空值指定排序顺序，比如错误值可以排在最前面（这样容易发现问题），空值排在最后（因为空值一般没有

46、意义），而有效值居中。504.3 OpenRefine简介及基本操作第四章 常用数据清洗工具及基本操作50502透视（Facet）操作透视操作是OpenRefine的主要工作方式之一，用于多方面查看数据集的变化范围，实现对数据的透视分类操作，包括文字、数字、时间线、散点图等多个选项，并支持用户自定义操作。透视操作并不改变数据，但由此可以获得数据集的有用信息，如图4-61所示。q q 文本透视（文本透视（Text facetText facet）：同电子表格）：同电子表格的过滤功能非常相似，将特定文本的值进行的过滤功能非常相似，将特定文本的值进行分组归类；分组归类；q q 数字透视（数字透视（N

47、umeric facetNumeric facet）：用于查）：用于查看一列数据值的分布范围；看一列数据值的分布范围；q q 时间线透视（时间线透视（Timeline facetTimeline facet）：使用）：使用时间轴来查看列内容的分布情况；时间轴来查看列内容的分布情况；q q 散点图透视（散点图透视（Scatterplot facetScatterplot facet）：）：用于数据列之间数值的相关性分析。用于数据列之间数值的相关性分析。图4-61 Facet操作选项514.3 OpenRefine简介及基本操作第四章 常用数据清洗工具及基本操作51513聚类（Cluster）操作

48、在OpenRefine中通过对数据集中相似的值进行聚类分析，便于找出一些如拼写有微小错误的“脏”数据。OpenRefine提供两种不同的聚类模式，即key collision和nearest neighbor，这两种模式的原理不同。对于key collision，使用键函数来影射某个键值，相同的聚类有相同的键值。比如，如果有一个移除空格功能的键函数，那么“A B C和AB C”“ABC”就会有相同的键值：ABC。事实上，键函数在构建上更加复杂和高效。而nearest neighbor使用的技术是，值与值之间使用distance function来衡量。比如，如果将每一次修改称为一个变化，那么B

49、oot和Bots的变化数是2：一次增加和一次修改。对于OpenRefine来说，其使用的distance function称为levenshtein。在实际应用中，很难确定究竟哪种模式和方法组合最好。因此，最好的方法是尝试不同的组合，每次都需要小心地确认聚类项是否真的可以合并。OpenRefine能够帮助我们进行有效组合：比如，先尝试key collision，然后尝试nearest neighbor。可以通过单击待操作列名的下拉菜单，选择Edit cellsCluster and edit命令，如图4-62所示。图4-62 选择聚类操作524.3 OpenRefine简介及基本操作第四章 常

50、用数据清洗工具及基本操作52524.3.3 OpenRefine数据清洗实例操作1更清晰地显示数据Text facet（文本透视）是OpenRefine的核心功能之一，在招聘信息数据集中包含的城市或者国家等名称的列，若想大致了解这个字段都有哪些值和这些值的统计次数有多少，那么就可以使用文本透视功能。在Categories列菜单中选择FacetText facet命令，结果会出现在屏幕左侧的Facet/Filter 页中，如图4-66所示。现在就可以在屏幕左侧的Facet/Filter页面中清楚地看到各个公司福利的归类，在每组的右下角还显示了这些数据的行数，如图4-67所示。图4-67 Face