数据处理
加载数据
上一篇文章中,已经介绍过BI了,它生成炫酷的可视化报告,其第一步数据获取上几乎可以从任何来源、任何结构、任何形式上获取数据。最基础的就是excel文件、csv文件,或者包含他们的的文件夹,如下图:
当然,这种数据加载会使得实时性没那么高,毕竟要人为的处理excel、csv文件等,高效的还是直接获取数据库的数据,如下图:
但是连接数据库就会遇到如下的问题,需要下载插件才能连接相应的数据库,可以直接了解详情进行相关插件的下载,下载之后配置后就可以使用。
我这个人有懒得搞插件,所以我选择另外一种数据加载的方式----python脚本。它的方便在于,一是不需要下载插件就可以访问数据库,另外python可以提前对源数据进行处理,当然后面也会讲BI的处理,但个人还是习惯Python的处理,这里读者自行决定哈,当然大数据还是相应的脚本处理的快些。
关于数据的加载,就讲这么些,读者根据自己的需求选择对应的加载方式即可,应该都没啥问题。
PP和PQ
PQ即Power Query,官网介绍如下:
Power Query是微软的数据连接和数据准备技术,使业务用户能够无缝访问存储在数百个数据源中的数据,并通过易于使用、引人入胜且无代码的用户体验来对其进行调整以适应他们的需求。
说的简单些,PQ就是清晰数据的神器,适合各种数据转换、处理,可以极大的提升工作效果,而且和脚本一样,只要逻辑一样,写一次清洗流程,后续的数据都按这个流程来。当然这里的PQ和EXCEL中的PQ完全一致。
PP即Power Pivot,官网介绍如下:
PowerPivot是一种数据建模技术,用于创建数据模型,建立关系,以及创建计算。 可使用 PowerPivot 处理大型数据集,构建广泛的关系,以及创建复杂(或简单)的计算,这些操作全部在高性能环境中和所你熟悉的 Excel 内执行
说的简单些,PP相当于excel中的透视图,但功能更加的强大些。当然这里的PP和EXCEL中的PP几乎一致。
常见处理
由上述介绍得知,这里讲解的数据处理都是基于PQ进行的。PQ的主界面如下图:
1. 追加、合并
合并使用的场景是:当需要映射另一个表中的信息时,就可以使用合并功能,类似excel中的vlookup、xlookup等功能。合并的主要选项如下:
操作说明:
1.选择需要合并的两个表
2.点选两个表的映射的依据列,可选多个列
3.选择连接种类:连接方式有左联(保留左表所有数据,右表映射到的数据),右联(保留右表所有数据,左表映射到的数据),内联(两表相同的数据),外联(保留所有数据),还有两个不常用的左反和右反。
4.*它还可以进行模糊匹配,但用的不多
追加使用的场景是:当需要把多个表纵向合并时,就可以使用追加功能,追加的主要选项如下:
操作说明:
1.选择需要追加的多个表
2.直接确定就可以
3.有个需要注意的地方就是列名要一样
2.标题行和数据格式
标题行
主要有两种,一种是把原本的标题变成数据的第一行;另一种时把数据的第一行用作标题;具体根据自己的数据应用。
数据格式
PQ中的数据格式和excel中的一样,只是没有了excel中的自定义格式。修改格式有两种方法:一种是选择相应的列,在数据类型中选择相应的数据类型;第二种时直接在列名旁边的小字处修改。
3.增
增多指增加列数据,在PQ的添加列选项卡中。大约来说有6种,分别是自定义列、条件列、索引列、合并列和重复列、快捷列,相关说明如下:
- 自定义列:通过PQ中的函数构造的列,和调用自定义函数列差不多,主要是应用函数。常用于其他简单添加无法实现时使用
- 条件列:基于某一个列,给一个基本的条件(大于、包含等)构造的列
- 索引列:生成一个由行数字组成的列,可以指定数字从0开始还是从1开始
- 重复列:构建一个列的复制结果
- 合并列:多个列合并构建的列,可以指定分隔符
- 快捷列:是PQ内部已经构建好的逻辑,如提取构建列、舍入、正余弦等
4.删
删除操作可以是行也可以是列,在主页面选项卡中的管理列和减少行实现,选中相应的列或者行,子啊上面选项卡选择删除的类型(前几行、后几行、间隔行、空行等)即可。
5.改
修改操作是在PQ的转换选项卡中实现。这里面有很多非常有用的操作,具体如下:
1.转置和反转行
转置就是行列变换----行变成列,列变成行,这里的转置和excel中的转置没得区别。非的说区别的话,就是转置完后需要注意是否需要提升第一行作标题。
反转行,顾名思义,就是把行前后调换顺序,其他不变。
2.替换值和填充
替换有两个应用,其一是替换相应的字符成另一个字符,其二则为替换错误值。一类似于excel中的replace和substitute等函数,二则是类似于iferror函数,虽然其实PQ中也存在iferror函数。
填充,常用于合并单元格的补齐,当数据透视之后,就会有空值(null)存在,这时候使用向下或者向上填充,这些空值就会被上面、下面最近的值填充。
4. 透视与逆透视
讲解之前,先看看什么是一维表,什么是二维表:
二维表:
一维表:
通过上面两个表,总结来说,维度指的是数值的列数。我们在处理的过程中有时候一维数据方便,有时候二维数据方便,所以,维度变换很有必要。
经过上述的介绍,你就应该会明白,数据透视就是增加维度的,把原本一列的数据变成多列,透视功能和excel表中的透视表差不多,可以选择透视的列和聚合的方式,直接使用excel的效果展示。上面一维表透视如下:
同样的道理,逆透视就是把多维的数据变成一维,即只要一列值,如下图,逆透视可以有三种,根据需要处理即可。
结果:
PQ处理实际应用(excel不好实现)
1.多表合并
我们在excel中合并表时,通过复制粘贴实现,但当表很多时,效率明显不行,所以PQ的使用就很方便,这里使用的是PQ的追加操作。具体如下:
- 加载多个数据或者加载文件夹,勾选合并并转换数据
- 只保留Content列,删除其他列数据
- 自定义添加一列,输入=Excel.Workbook([Content],true)即可完成
上述操作,在最新的版本中,不需要手动操作,会直接生成结果,不过你也可以通过右边的步骤进行自定义修改。
2.模糊匹配
在Excel中,映射vlookup、xlookup等都有模糊匹配的选项,但是很多时候并无法实现我们的需求,而PQ中的模糊映射可以,具体操作如下: