EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

   在我们的日常工作中,数据清洗通常是一个非常复杂和繁琐的过程,特别在EXCEL里进行数据清洗会显得格外痛苦,例如对数据进行简单的合并、去重、分列都需要花费不少的时间和功夫。虽然微软推出了power query这个厉害的清洗组件,但是M语言的门槛相对较高,一般人不易掌握。因此选择一个简洁的ETL工具去对数据进行清洗是一个非常好的选择。除了可以节省大量的时间,还能以可视化的界面进行操作,无需任何代码便可以快速对数据进行清洗。下面利用智分析的ETL工具,对EXCEL常见的几种清洗方法进行讲解。

1、 合并表格
我们平时工作中总会遇到需要对两个EXCEL表格进行合并的情况,例如对多个门店或者分公司的数据进行整合,如果列数都一样的时候,用复制粘贴的方式就可以对两个表格进行合并,但是如果表格的列数存在不一样的情况时,我们合并表格的难度会增加,例如以下两个表格,订单明细表B比订单明细表A多了一列,如果要合并这两个表格,则需要在A表里插入一列或者把B表里多出的这一列删除了,非常麻烦。但是用ETL工具处理就会非常迅速,以下为我用智分析的自助ETL功能对表格进行合并的方法。
EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

▲订单明细表A
EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

▲订单明细表B
第一步要做的就是先把两份数据导进智分析系统里,在数据连接的界面里选择EXCEL导入,分别把订单明细表A和订单明细表B导进来,并选择保存的路径。
EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

接下来点击数据准备里的自动ETL,进入到ETL的界面后把关系数据源拖拽到中间的展示区里,并在右边的参数里找到两份表格,然后鼠标右键点击关系数据源里的“执行到此处”。

EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

由于订单明细表B有多余的字段,我们先对这个数据源进行列选择,把数据源预处理里的‘“列选择”拖拽过去,并与订单明细表B进行相连,在输出列的界面里,把多余的字段剔除掉,这样便可以得到与A表字段相同的数据源。
EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境
EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

最后把“合并行”这个组件拖拽到展示区里,并把订单明细表A和加工过的订单明细表B进行相连,在参数选择里选择所有字段,合并方式选择并集,这样便可以把两个表格合并在一起了。

EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

我们看一下输出的结果,两份数据源完美地结合在一起了。如果对字段更多、更复杂的数据源进行合并,这个工具可以发挥出给力的效果。
EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

2、 删除重复项
数据源有重复项也是经常会遇到的问题,在EXCEL里通常可以用删除重复项的功能去对数据进行去重处理,但是利用ETL一样也可以对数据进行去重。还是用上面的数据源,我们把“去除重复值”这个组件拖拽到展示区里,并与上面合并好的数据源进行相连。
EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

连接好之后,在右边的参数选择项里选择要去重的字段,然后点击执行,我们来看一下输出的效果,两个表格里的重复值便剔除掉了,剩下的表格都是唯一值了。
EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境
EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

3、 分列
在用EXCEL处理数据时,经常要对一些数据字段进行分列,例如上图的数据源中,我们发现了数据源中销售人员这一列有多余的信息,多了销售代表这几个字,我们应该怎样把后面名字给提取出来呢?在EXCEL中可以用LEFT、RIGHT函数或者分列的方式对文本字段进行提取,但是在ETL里一样也可以实现。首先把“分列”这个组件拉拽到展示区里,并与上面的数据源进行相连。
EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

在右边的参数设置区里,我们先把需要分列的数据字段选择好,然后在分隔符选项里选择分列字段里所携带的分隔符,这里我们选择“-”。
EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

我们执行看一下执行的效果,销售人员这个字段被分成了两列,并生成了新的两个字段,完美地实现了分列的效果。

EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

4、 派生字段
如果要在ETL里增加新的数据字段也是没问题的,例如上图数据源里的订单日期包含了日期、时分秒这些信息,但是如果要把里面的年份提取出来,应该怎么做呢?这时我们把“派生字段”拉拽到展示区里,并与上面的数据进行相连。
EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

在参数的设置界面里,点击年份提取的函数,并选择订单日期这个字段,点击确定。
EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

我们看一下输出的效果,数据源最后的一列生成了新的一列,仅有年份的信息。如果要生成其他的字段也是可以实现的,只需编写表达式即可。
EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

5、 元数据编辑
ETL还可以对数据的类型进行更改,例如把字段调整为浮点型、字符串、整型或者是其他的类型。同样的方法,我们把“元数据编辑”拉拽到展示区里,并与上面的数据源进行相连。
EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

在元数据编辑的参数设置里,可以对数据字段的别名或者数据类型进行设置,这个对于数据库来说非常有用。

EXCEL数据缺失、混乱、重复怎么办?我用ETL带你走出困境

 以上为用ETL工具对EXCEL进行数据清洗的5个常用方法,后面还会对其他清洗方法进行讲解,敬请留意。
上一篇:CS 234 Spring 2019 — Assignment


下一篇:ggplot2|详解八大基本绘图要素