PowerQuery 中使用 M 语言创建自定义函数爬取天气后报网页

0.12字数 1746阅读 254

209190509更新
更新原因:实际使用下来有 bug,Power Query 对数据格式非常严格,月份输入不是文本格式会报错。
更新内容:使用 Text.From 函数将月份先转换为文本然后做判断长度和补足两位的操作,修正了 bug;新加步骤转换气温为数字格式;
因为20190508中央气象台出现了0512最高温39度的异常预报,为了证明是错误值,就简单做了一个上海月极值天气的透视表,可以忽略。
某盘文件下载2 4dif

关于月份补足到两位其实 M 语言中有现成的函数可以用,可以参考 Text.PadStart/End 函数简介,但是我懒得改代码了,就依然是原有的 If Else 条件判断。

原文


说明:
excel 2016 最早期的 Power Query 有部分函数不支持,可能无法运行。建议升级到最新。
2010 和 2013 版本的 Power Query 没有测试。
作者有一点儿 Power Query 使用经验,M 语言只学了一天,做了这个模板,所以用这个代码的注意检查数据条数。
懒得写的特别详细,所以不针对一点儿不懂 Power Query 的人。
某盘文件下载 f665

前期思路和准备

要爬的网页信息:天气后报

URL 举例:http://www.tianqihoubao.com/lishi/beijing/month/201904.html

拼音 beijing 用来控制是什么城市,201904 是年月。

这样要爬的 URL 要设置三个变量,城市、年份、月份。理论上年份和月份可以是固定的 2011-2019 ,月份也可以是固定的 01-12 月。 考虑到每次都爬 9 年 12 个月的数据(多城市数据就更多)比较费时间,大部分使用场景可能也不需要看这么久时间的天气数据,就设置可以自定义查询。自定义输入是通过引用 excel 的表实现的。

测试 Excel 表中直接输入年份月份会默认为数字、月份即使输入01,表中也是默认为 1。所以需要通过月份的位数判断是不是要在前面加一位 0 。然后转换年和月为文本格式

测试Excel 表中直接删除数据会形成空行,要求使用者右键删除表行又多了好几步操作,所以需要删选三个表中的空行

城市列表、年份、月份表格样式

上述三个表名称分别为:城市清单、年份、月份。


全部代码

上面三个自定义参数的表设置后,直接复制以下代码到 PowerQuery 的高级编辑器中可以直接保存运行。

let    
    tianqi = (city,year,month)=>Table.PromoteHeaders(Web.Page(Web.Contents("http://www.tianqihoubao.com/lishi/"&city&"/month/"&year&month&".html")){0}[Data], [PromoteAllScalars=true]),
    source = Excel.CurrentWorkbook(){[Name="城市清单"]}[Content],
    添加年份 = Table.AddColumn(source, "yea", each Excel.CurrentWorkbook(){[Name="年份"]}[Content]),
    添加月份 = Table.AddColumn(添加年份, "mont", each Excel.CurrentWorkbook(){[Name="月份"]}[Content]),
    展开年份 = Table.ExpandTableColumn(添加月份, "yea", {"年份"}, {"年份"}),
    展开月份 = Table.ExpandTableColumn(展开年份, "mont", {"月份"}, {"月份"}),
    筛选拼音城市年月列不为空 = Table.SelectRows(展开月份, each [cit] <> null and [年份] <> null and [月份] <> null),
    判断月份是否要加0 = Table.AddColumn(筛选拼音城市年月列不为空, "月份2", each if Text.Length([月份]) = 1 then "0"&[月份] else [月份]),
    更改年月列为文本 = Table.TransformColumnTypes(判断月份是否要加0,{{"年份", type text}, {"月份2", type text}}),
    添加网页表格 = Table.AddColumn(更改年月列为文本, "data", each tianqi([cit],[年份],[月份2])),
    展开网页表格 = Table.ExpandTableColumn(添加网页表格, "data", {"日期", "天气状况", "气温", "风力风向"}, {"日期", "天气状况", "气温", "风力风向"}),
    拆分天气列 = Table.SplitColumn(展开网页表格, "天气状况", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"白天天气", "夜间天气"}),
    拆分气温列 = Table.SplitColumn(拆分天气列, "气温", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"最高气温", "最低气温"}),
    拆分风向列 = Table.SplitColumn(拆分气温列, "风力风向", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"白天风向", "夜晚风向"}),
    删除摄氏度单位 = Table.ReplaceValue(拆分风向列,"℃"," ",Replacer.ReplaceText,{"最高气温", "最低气温"}),
    日期列变更为日期格式 = Table.TransformColumnTypes(删除摄氏度单位,{{"日期", type date}}),
    筛选日期为小于等于当前日期 = Table.SelectRows(日期列变更为日期格式, each [日期] <= DateTime.Date(DateTime.LocalNow())),
    删除的列 = Table.RemoveColumns(筛选日期为小于等于当前日期,{"cit", "年份", "月份", "月份2"})
in
    删除的列

代码分步骤解释

  1.    tianqi = (city,year,month)=>Table.PromoteHeaders(Web.Page(Web.Contents("http://www.tianqihoubao.com/lishi/"&city&"/month/"&year&month&".html")){0}[Data], [PromoteAllScalars=true]),
    

这里在开头创建了一个自定义函数 tianqi,参数有三个 city,year, month 。使用 Web.Page 来获得网页表格,Table.PromoteHeaders 用来将第一行用作标题。
网页表格,抓起来一般是没有标题的,column1、column2 的类似标题,如果爬出来之后再提升标题,前面已经设置好标题的列会因为标题提升失去固定的标题。

  1.  source = Excel.CurrentWorkbook(){[Name="城市清单"]}[Content],
     添加年份 = Table.AddColumn(source, "yea", each Excel.CurrentWorkbook(){[Name="年份"]}[Content]),
     添加月份 = Table.AddColumn(添加年份, "mont", each Excel.CurrentWorkbook(){[Name="月份"]}[Content]),
     展开年份 = Table.ExpandTableColumn(添加月份, "yea", {"年份"}, {"年份"}),
     展开月份 = Table.ExpandTableColumn(展开年份, "mont", {"月份"}, {"月份"}),
    

按照三个 excel 表名称添加展开三个自定义参数的表。这几个步骤运行完的结果:


添加城市、年、月之后
  1.  筛选拼音城市年月列不为空 = Table.SelectRows(展开月份, each [cit] <> null and [年份] <> null and [月份] <> null),
     判断月份是否要加0 = Table.AddColumn(筛选拼音城市年月列不为空, "月份2", each if Text.Length([月份]) = 1 then "0"&[月份] else [月份]),
     更改年月列为文本 = Table.TransformColumnTypes(判断月份是否要加0,{{"年份", type text}, {"月份2", type text}}),
    

如步骤名称,是开头提过的要对三个参数源表的处理。运行结果:


参数处理
  1.  添加网页表格 = Table.AddColumn(更改年月列为文本, "data", each tianqi([cit],[年份],[月份2])),
     展开网页表格 = Table.ExpandTableColumn(添加网页表格, "data", {"日期", "天气状况", "气温", "风力风向"}, {"日期", "天气状况", "气温", "风力风向"}),
    

使用开头声明的自定义函数添加一列,然后展开列中表格包含的每一列。


初步数据抓取

到这一步,抓取数据的部分就做完了,接下来是正常使用 Power Query 处理数据的部分。

  1.  拆分天气列 = Table.SplitColumn(展开网页表格, "天气状况", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"白天天气", "夜间天气"}),
     拆分气温列 = Table.SplitColumn(拆分天气列, "气温", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"最高气温", "最低气温"}),
     拆分风向列 = Table.SplitColumn(拆分气温列, "风力风向", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"白天风向", "夜晚风向"}),
    

这里是拆分原表的列,Power Query 的内建函数 Table.SplitColumn 似乎没办法一次拆分多个列,原默认代码中的拆分后列名为 天气状况.1 和 天气状况.2,这里就是一个重命名的设置,为了减少步骤,这里我自定义了拆分后列名。

拆分后
  1.  删除摄氏度单位 = Table.ReplaceValue(拆分风向列,"℃"," ",Replacer.ReplaceText,{"最高气温", "最低气温"}),
    

拆分后的气温包含摄氏度单位,这里使用替换为 null 的方式删除符号。


温度符号处理
  1.  日期列变更为日期格式 = Table.TransformColumnTypes(删除摄氏度单位,{{"日期", type date}}),
     筛选日期为小于等于当前日期 = Table.SelectRows(日期列变更为日期格式, each [日期] <= DateTime.Date(DateTime.LocalNow())),
    

原网页中的数据如果是超过当前日期也会抓出来空白的内容,所以这列做了一个条件筛选,筛选日期小于等于查询运行当天日期的行。原抓出来的日期列为文本,这里先转换为日期格式,然后做筛选。

M 语言中没有工作表函数 now() 来表示当前时间,表示当前日期(不含时间)是 DateTime.Date(DateTime.LocalNow())

日期筛选前

  1.  删除的列 = Table.RemoveColumns(筛选日期为小于等于当前日期,{"cit", "年份", "月份", "月份2"})
    

删除无用的列。

删除前

删除后

加载到 excel 工作表的数据

最后结果

参考:
1.excelhome 上网友分享教程
2.pqfans 自定义函数文章