了解大数据分析?试试看从VBA爬虫+SQL查询开始

回头想想,自己跟数据打交道也不少时日了。所谓大数据,其实是服务于战略决策、解决实际问题的数据集合的推演过程。数据采集、清洗(规整化、处理无效数据、GBK转码)、存储、分析、输出&可视化等都是“大数据”概念的组件。那天开了个会,主题好像是“智能财务”,感觉深受启发,尤其是“数据湖”“数据集市”“智能分录”“模型工厂”等一众崭新理念令我等耳目一新,获益良多(只要你敢想,估值百倍不是梦~)。

虽然(大量)数据积累是个吃力不讨好的活儿。枯燥、易错、缓慢且不被重视。但如果积累了一定量的高质量数据,却能成为有力的战略武器,为许多决策提供极高的参考价值。

其实很多大型网站积累的数据能为我们的分析、建模、决策提供了很好的素材。而“爬虫”程序,是我们批量采集,搭建基础库的一种有效工具,具有较高的技术价值。(“爬虫工程师”你们了解一下~)。

上回,我们通过“批量爬取小黄图”一文,接触到了爬虫程序常用的VBA函数。批量图片爬取,主要是识别图片地址常用的“src”起头&.jpg,.png后缀等固定字符串结尾,截取指定的图片地址信息,再用VBA-downloadtofile函数完成批量下载(最新研究发现,VBA可以直接调用迅雷,完成高速批量下载)

这次,我们以【携程】为例,了解爬虫工具中各种VBA字符串函数的使用方法&循环爬取策略。目的是将携程网上深圳全部的酒店信息批量摘下来。建立一个数据库。

还是一样,爬虫思路的构建是从分析目标网站的HTML代码开始的。

列表中第一个酒店是“南山citiGO”,我们直接在HTML码页面搜索(Ctrl+F)去定位(这样可以跳过大段的无关HTML代码)。于是,我们在514行发现了citogo的实际列表信息(酒店名称),以及“地铁站”“地址”“评价分数”“最低价格”等关键信息。我们的目的就是快速、批量获取&整理成数据库。

同时,网页分出了291个页面,也需要我们通过VBA-循环语句进行批量访问,提取每一页的酒店信息。

这个分页器的HTML代码其实蕴含着解决批量访问的关键!它可以为我们使用VBA-循环找到很好的路径。我们发现,该页面地址是根据shenzhen30/PXX(XX为页码)的规律排列的,尾页是291。哈哈哈,这个好办,VBA-循环就可以解决了!

通过分析,我们发现,页面虽大,HTML代码虽多,但酒店清单其实开始于514行,而开头的标志是:"hotel_list",分页器意味着结尾。也就是说,返回每页的这一段,就可以作为提取关键信息(字段)的文本依据了。

VBA代码:【建立msxml2.xmlhttp对象——发行请求——返回HTML文本——截取酒店清单列表——放入A1单元格】

结果返回

根据一开始的思路,我们要截取514行开始的酒店清单,也就是识别头部“hotel_list”以及尾部“”(位于543行)。这里我们用VBA-split函数,先把文本从hotel_list分开取第二段,然后再从分开取第一段。VBA代码如下:

单元格A1得到酒店清单信息,已经开始可以在头几行看到CITIGO酒店的名称,说明HTML 514行之前的内容被截掉了。

现在,我们要做的是:提取清单中每个酒店的名称,地址,评价分数,房价等信息。他要求我们,使用VBA进行字符串的操作,截取例如“深圳南山citiGO酒店”这样的文字,放入excel单元格。从页面上看,每张页面25个酒店。截取25个“酒店名称”相当于提取一个含25个元素的数组。每个数组元素表示一个酒店名称。

这个可以通过酒店名称的"hotel_num"作为前置标识符,分成25个数组,再通过""作为后置标识符截取。

vba代码如下:

我们可以看到结果很接近最后的目的了:

最后用vba-replace函数把这些替换掉,调整截取的初识位置,返回干净的酒店名称即可

返回结果

用类似方法,VBA函数的分列split,截取mid+instr,替换replace,调整初始位置等字符串操作,我们还能提取酒店地址,评价,价格等具体信息。代码如下:

携程深圳第一页的酒店清单相关信息,我们就全部清理出来了

接下来就是循环所有网页~通过shenzhen30/p &页码的格式进行循环。另外要注意,每循环一次是25个酒店信息,所以循环一页是25×页码(k=1 to 291)+i行(i=1 to 25)

于是,在2018-6-22晚上九点,我们爬取了携程在深圳的5261家酒店,耗时大概180秒

我们再来总价一下爬虫程序批量获取信息的方法论:

①分析网页HTML代码,找到关键信息的位置。观察关键信息前后的字符串特征。

②建立VBA object对象,请求返回HTML代码,同时观察HTML地址的特征,建立批量访问的循环语句

使用VBA函数,包括split, mid,instr,replace 等处理关键信息字符串,循环填充到excel单元格

接下来。。。。既然,有了这么多酒店信息,我们是否可以为将来开房做些有参考价值的数据挖掘呢?哈哈哈,当然是可以的。这里,我们着重介绍在excel中,将excel worksheet作为一个数据库,使用sql的分析操作。

sql是standard query language(标准查询语言)的缩写,它主要用于查询、分析数据库中的数据,例如一句比较标准的例句:select * from table_1 where price>500, 意思是,从table_1这张数据表中返回所有字段的信息,其中满足price字段下的所有数值>500。虽然基于excel的数据分析中,数据透视&取数公式是主流,但在大量数据分析中,sql的优势是快速,标准化,查询结果的可移植。

在excel调用sql,主要的方法是:使用VBA建立一个数据库联结对象,将爬虫返回的excel worksheet作为一个数据库,再建立一个数据集对象,将worksheet数据库的表定义成数据集,使用vba-execute函数,用sql查询想要获得的信息,最后将查询结果复制黏贴到一个新的worksheet.

代码如下:

【查询目的:返回深圳酒店的价格分布,即分段返回价格在0-300,400-600,700-1000&1000以上的酒店家数】

查询结果如下:

然后画个图:

我们发现深圳的酒店价格大部分集中在300以下,而300-700的中等价位仍有一定占比。700-1000&1000以上价格偏高酒店总体数量不多。

当然,这只是个简易的分析实例,我们可以用sql或者其他方法对数据进一步挖掘,获得更有价值的信息,为战略决策服务。

就这样,我们通过简单但深入本质的组合工具“爬虫+HTML+VBA+SQL”,对所谓“大数据”这个概念有了一次初步但具体认识。(以后也不会这么简单就被忽悠了有没有。。。)。谢谢各位的耐心,我们下次再聊~

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20180623G1PS7G00?refer=cp_1026
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

扫码关注腾讯云开发者

领取腾讯云代金券