商业智能BIPowerQuery玩转大数据

商务智能在统计工作中的应用——抓取合并网络数据表

2017-07-26  本文已影响140人  阿森纳里

实例下载：链接: https://pan.baidu.com/s/1kUWARJp 密码: fxkk

Power BI是微软公司推出的商务智能软件，由于其强大的数据处理和分析功能在业界享有盛誉，广泛应用于金融、财务、IT、统计等和数据打交道的领域，是一款功能强大的ETL分析工具。它的特色之一是对用户十分友好，上手简单，使对于编程不熟悉的用户也能很快上手，从而极大地提高工作效率。
以下以一个常见的应用场景来介绍PBI的相关功能。
在工作中我们有时候需要从网页中下载大量表格并进行合并分析，传统的方法是复制粘贴至一个sheet中，但是要处理的表格多的时候相当繁琐，我们可以用VBA编程解决，但有了PBI，我们就可以用最易理解的思维来解决问题了。

在本例中，我们需要从北京市统计局网站下载历史上各季度数据并进行数据整合，各季度数据存放在不同的网页中。

数据源：北京市GDP季度数据

一. 获取数据来源

数据来源表

首先我们得到一张[数据来源表]，列出数据表存放的网址，从2005年1季度至2017年2季度，共有50张表。

二. 分析表结构

通过分析可以看出，50张表的表结构大致相同，以最新一期为例，

2017年上半年北京市分产业GDP数据表

从行来看，这张表分为四个区域，分别是表名、表头、数据区域、说明；从列来看，共有三列：行业、增加值、增速。其中，表名和说明不属于有效数据，导入整合的时候应该去掉，只导入表头和数据区域，这二者合称表格区域。在表格区域中，我们需要整合的是数据区域的增加值列和增速列，也就是数字部分，其他文字部分每一期都差不多，可统一处理。

但是要注意，差不多并不是完全一样，分析历史时期可以看到，行业分类有过变化，从2014年4季度起，北京市分产业GDP开始实行新的2011版行业分类标准，这在该期数据表下方有说明：

注2：产业划分依据国家统计局2012年制定的《三次产业划分规定》，行业划分依据《国民经济行业分类》（GB/T4754-2011）。

新的2011版行业分类标准和旧版的2002版的不同之处是部分门类范围有所调整，顺序也有所改变，不过我们大致可以整理出这样的一张对照表：


新旧行业分类对照表

而相对来说，列结构没有变化，一直是行业、增加值、增速这三列。


宾栏结构

甲栏和宾栏

在统计工作中，我们把表头叫做“宾栏”，把表的前几列（一般是文字列）叫做“甲栏”，甲栏宾栏和数据区域之间以粗线隔开，数据区域的各行和各列之间可以以细线隔开，也可不隔，目的是使表格醒目以便于观看。

在表格的左上角（第二象限），通常是属性说明，很多时候我们也会空着不写——不知道该写些啥，也即没有总结出属性规律。

属性是数据库中的概念，任何一个数据都有属性名和属性值，该数据本身即属性值，该数据所在列的列名即属性名，比如图中的“指标1/指标2/指标3”。如果我们把这张表转置，那么其实“项目1/项目2/项目3”也是属性名。

二维表和四象限

一张典型的数据表结构

这是一张典型的二维表，我们平时见到的大多数表都是二维表，简单地说，二维表就是一张多行、多列的数据表，它通过指定第几行和第几列（即行列坐标）来确定唯一的数据，比如说在这张表中，我们说第一行第二列的数据是多少，或者说项目1的指标2是多少/第一产业的增速是多少，在这里，我们默认的前提是北京市、本期，也就是说，我们问的是北京市2017年2季度第一产业的增速是多少，这个默认的前提就是上下文“context”。

二维即平面，如果以平面直角坐标系来标注二维表的各区域，那么宾栏即第一象限，左上角的属性说明区域即第二象限，甲栏即第三象限，右下角的数据区域即第四象限。数据区域的第一列的各数据有一个共同的属性——它们都属于指标1，第一行的数据也有一个共同的属性——它们都属于项目1；而“指标1/指标2/指标3”有一个共同的属性——它们都是“指标”的内容，竖看“指标1”是属性名，它决定了我们取的是第一列数据，横看“指标1”又成了属性值，它由“指标”中的第一个元素所决定，因此，可以说“指标”和“项目”是属性名的属性名。这是复合函数的关系，或者说是一个俄罗斯套娃式关系，“指标”和“项目”属性分别是本数据表关系的“外码”(foreign key)，而它们组成的属性组就是本表数据关系的“主码”(primary key)。

“逆透视”预习

有时候我们第二象限留空不写，那是我们还木有总结出“属性名的属性名”，所以不知道该写什么。有时候我们只写上“项目”，那是我们忽略了各列数据之间的关系，也就是说我们没有意识到“指标1/指标2/指标3”也可以是属性值，它们可以是另一个数据关系的筛选项的（和“项目[项目1,项目2,项目3]”一样）。理解这一点对于随后逆透视操作的理解至关重要。
少数时候，我们在第二象限只写上“指标”。

表结构的两种形式

三. 合并数据表

有了以上预备知识，我们就可以开始整合数据了。
先上代码：

let

// 1.获取数据源
    源 = 数据来源表,
    选择相关列 = Table.SelectColumns(源,{"季度", "网址", "甲栏版本"}),

// 2.批处理表格
    批处理表格 = Table.TransformColumns(选择相关列,{"网址", each 
     let 获取表格 = Web.Page(Web.Contents(_)){0}[Data],
         数据区域 = Table.Skip(Table.RemoveColumns(获取表格, {"Column1"}),2),
          /* 第一列Column1是甲栏，前两行是宾栏(表头)，
             删除它们，保留第四象限的数据区域*/
         添加行号 = Table.AddIndexColumn(数据区域, "行号",1,1),
          // 给数据区域添加行号
         逆透视 = Table.UnpivotOtherColumns(添加行号,{"行号"},"列号","值")
          // 逆透视数据区域以便于数值处理
     in  逆透视}),
    展开表格 = Table.ExpandTableColumn(批处理表格, "网址", {"行号", "列号", "值"}, {"行号", "列号", "值"}),

// 3.规范甲栏、宾栏
    _规范甲栏_1合并查询 = Table.NestedJoin(
     展开表格,  {"甲栏版本", "行号"},
     甲栏规范表, {"甲栏版本", "行号"},
     "NewColumn",JoinKind.Inner),
      // 仅选择相关行，舍弃表末尾的说明行等无用信息
    _规范甲栏_2删除列 = Table.RemoveColumns(_规范甲栏_1合并查询,{"行号", "甲栏版本"}),
    _规范甲栏_3行业 = Table.ExpandTableColumn(_规范甲栏_2删除列, "NewColumn", {"行业"}, {"行业"}),
    _规范宾栏_1合并查询 = Table.NestedJoin(
     _规范甲栏_3行业, {"列号"},
     宾栏规范表,     {"列号"},
     "NewColumn",JoinKind.Inner),
      // 仅选择相关列
    _规范宾栏_2删除列 = Table.RemoveColumns(_规范宾栏_1合并查询,{"列号"}),
    _规范宾栏_3列名 = Table.ExpandTableColumn(_规范宾栏_2删除列, "NewColumn", {"列名"}, {"列名"}),

// 4.变换数据
    更正数据 = Table.TransformColumns(_规范宾栏_3列名, {"值", each 
      let n = Table.PositionOf(更正表,[原值=_],0,"原值")
      in  if n=-1 then _ else 更正表[修改为]{n}
     }),
    转化为数值 = Table.TransformColumns(更正数据, {"值", each 
     try Number.From(_) otherwise _}),

// 5.透视宾栏、导出
    透视宾栏 = Table.Pivot(转化为数值, List.Distinct(转化为数值[列名]), "列名", "值"),
    重排序 = Table.Sort(透视宾栏,{{"季度", Order.Ascending}, {"行业", Order.Ascending}})
in
    重排序

看似很难，但理解了数据处理的过程，其实就非常简单。数据处理大致分为以下几个过程：1.获取数据源；2.批处理表格；3.规范甲栏、宾栏；4.变换数据；5.透视宾栏、导出。
用易懂的话来说，就是：1.拿到50张表；2.分别剪下50张表的第四象限(数据区域)；3.把不同表的行列规范起来，比如2005年一季度的第4行是工业数据，但2014年四季度的第8行才是工业数据，于是我们把这两期的这两个行号统一替换为“ BCD.工业”；4.处理一些特殊情况，比如“持平”意思是增长率为0，“-”意思是“空/没有/null”；5.导出处理完成的数据表。

1. 获取数据源

这一步很简单，载入excel文件中存放的[数据来源表]，仅保留相关的三列：季度、网址、甲栏版本。

2. 批处理表格

这一步中间过程多一些，Table.TransformColumns是列变换，我们对于[数据来源表]的“网址”列做统一变换。

3. 规范甲栏、宾栏

_规范甲栏_1合并查询 = Table.NestedJoin( 展开表格, {"甲栏版本", "行号"}, 甲栏规范表, {"甲栏版本", "行号"}, "NewColumn",JoinKind.Inner), // 仅选择相关行，舍弃表末尾的说明行等无用信息 _规范甲栏_2删除列 = Table.RemoveColumns(_规范甲栏_1合并查询,{"行号", "甲栏版本"}), _规范甲栏_3行业 = Table.ExpandTableColumn(_规范甲栏_2删除列, "NewColumn", {"行业"}, {"行业"}),
在规范甲栏之前我们首先需要准备[甲栏规范表]，这是规范甲栏的依据。

甲栏规范表
把上一张展开之后的数据表和这张[甲栏规范表]进行“合并查询Table.NestedJoin”操作，凡是甲栏版本为1_2005S1的数据表，它的第1行代表“0.地区生产总值”，第2行代表“1.第一产业”……；凡是甲栏版本为2_2014S4的数据表，它的第1行代表“0.地区生产总值”，第3行代表“1.第一产业”……。

Table.NestedJoin类似于excel函数中的vlookup或者match-index，不过它的功能要强大得多，其查询依据可以是多个属性构成的属性组。JoinKind.Inner这个查询参数规定了查询结果仅呈现两个表格的交集，像2_2014S4版本的第2行、第6行等无数据，查询结果中就不再呈现，如此就过滤掉了无用的数据行。

同样道理，进行宾栏规范化操作，这就是前面要对原始数据表进行批量逆透视操作的原因。

规范化后得到的数据表如下：


规范化甲栏(行业)、宾栏(列名)

4. 变换数据

更正数据 = Table.TransformColumns(_规范宾栏_3列名, {"值", each let n = Table.PositionOf(更正表,[原值=_],0,"原值") in if n=-1 then _ else 更正表[修改为]{n} }),

更正表
这一步很简单，根据[更正表]中的内容，把数据中的“-”替换为null(空)，“持平”替换为0。所用到的方法可称之为“多重替换”，具体可参考我另一篇文章中的介绍：PQ多重替换操作再探。

转化为数值 = Table.TransformColumns(更正数据, {"值", each try Number.From(_) otherwise _}),
然后把“以文本形式存储的数字”转化为数字。

得到以下数据表：


变换数据

5. 透视宾栏、导出

透视宾栏 = Table.Pivot(转化为数值, List.Distinct(转化为数值[列名]), "列名", "值"), 重排序 = Table.Sort(透视宾栏,{{"季度", Order.Ascending}, {"行业", Order.Ascending}})
最后，为方便浏览数据，对宾栏(列名)再次进行透视，对甲栏(主键)进行排序，导出。

导出数据

以上。
.
.

上一篇下一篇

猜你喜欢

热点阅读