用Excel/Power Query能爬取网页表格数据 方法一
用Excel/Power Query能爬取网页表格数据 方法一
- 开始
-
- 需求
- 准备数据到Excel
- 使用Power Query进行数据清洗整理
开始
本方法是本人借助网上的方法然后自己实操了一遍，记录一下。
如果网页中表格数据较少，需要的体力活不多，可以直接进行保存，不需要进行过多的技术操作。
但如果网页中表格数据分页太多或数据量太大，需要体力活太多，那您可以试下下列方法。
需求
爬取网页上的表格数据，保存到本地excel中。
准备数据到Excel
准备爬取下列链接中的表格数据
链接: 2019年财富世界500强排行榜
网页表格预览如下：
-
打开需要爬取的网页，在网页上 点击鼠标邮件 选择 查看网页源码
也可以直接快捷键Ctrl+U(Microsoft Edge)
如下图：
-
复制下图中内容到office 365中的Excel中，注意标签完整性以达到复制的数据完整性：
-
在Excel中按快捷键
Ctrl+T,进行表格创建，如下图：
-
鼠标点击 确定 按钮后，数据就进入到Excel中，下一步开始进行数据整理。
使用Power Query进行数据清洗整理
选中刚复制的内容，点击 数据 -> 来自表格/区域 ，将数据导入到 Power Query 中，如下图
删除 tbody 该列，该列没有我们要分析的数据。
在剩余列中，数据按照一定的规律进行排布。通过分析发现，在 1行 ， 8行 ， 15行 等等不包含我们要的数据，通过 删除行 中的 删除间隔行 可以进行删除。首先设置 开始行为1 ， 删除行数为1 ， 保留行数为6 。这样就可以将没用的 ****行全部去除。
现在每行数据中都包含了一些html源码中的 标签代码 ，通过 替换值 功能，将不需要的 标签代码 替换为空。这里将 ****** ************ ** ****替换为空。
经过上述替换后，大部分行只保留了我们想要的数据内容，但是 行4 以及类似的行中还包含有 链接标签 。由于链接地址不同，因此不能用简单的替换值功能。这时候我们需要将类似行进行拆分，将链接标签和数据拆分出来。进过对源数据行分析，发现可以按照 **>**字符对涉及到的行进行 按照分隔符 拆分。
经过上一步的拆分后，有链接标签的行已经被拆分为链接和数据，但是其他的行也被拆分了，导致我们需要的数据不在同一列上。这时需要进行合并列的操作，但是需要判断我们合并的列为数据。经分析发现特殊的行为有链接标签的行，通过添加 条件列 功能增加一个辅助列，按照判断是否含有链接标签标志<字符，如果有该字符，我们新添加的列的数据为1或者0，没有该字符的列数据为0或者1。这样就将有链接标签和没有链接标签的列区分开了。
之后通过添加 自定义列 功能实现合并列，其中添加 自定义列 的表达式逻辑为 通过判断辅助列的值去判断该提取哪一列的值到本列中 。完成表达式后，点击 确定 ，power query会自动按照表达式为我们生成新列，该列的数据正是我们需要的数据。
删除其他列，留下只有数据的列。
通过爬取的网页中，发现数据是按照 **-排名-上年排名-公司名称-营业收入-利润-国家-**的字段进行表格填充。现在我们得到的数据为一维表，要想变为网页上的格式，需要进行 透视 操作。进行透视前，需要添加一些辅助列，首先添加 索引列 ，按照 从0开始，1递增 的属性进行添加。
然后通过 添加列 功能选项卡下的 标准 中的 除整数 功能插入新的索引列。但该索引列的功能是将数据列按照 6行一组6行一组 进行递增索引。因此整数对象为6。这样数据就会按照 000000111111222222333333444444 的顺序逐行填充。该索引列为后续进行透视时后提供索引功能。
需要再添加一个 辅助列 ，该辅助列的功能就是进行后续的透视列的对象。透视后就变为网页中的列标题。该列需要通过添加自定义列功能实现。由于数据列中的数据是按照一定的规律循环的排列各项数据。因此自定义列的表达式中的大概逻辑为 对步骤9中添加的辅助索引列值进行除6取余后分别进行赋值为排名，上年排名，公司名称，营业收入，利润，国家的文本内容 。
选中步骤11中添加的列，点击转换选项卡中 透视列 按钮，值选为 数据列 ，高级中选择 不聚合 ，然后 确认 。就生成了我们想要的格式的数据。