利用php爬虫分析南京房价
前些天看到csdn上的一篇文章,利用python写爬虫来分析上海房价的。感觉挺有意思的。正好最最近写snake后台也写到了文章采集,我也来用php的爬虫来分析一下南京的房价。说干就开始吧。
本次爬虫的依赖文件: 首先是ares333大神的CURL类。我用的是初期的版本,这是https://github.com/ares333/php-curlmulti大神的github项目地址,他写的curl确实很牛!
采集用的是phpQuery,不知道这个类的朋友,可以自行百度吧。
至于数据的来源,我选择安居客,数据量还是可以的,打开安居客选到南京的频道。开始分析他们的页面结构,至于怎么用phpQuery分析页面结构采集的方法,这里就不做详细的介绍了。分析好结构,好,开始建立数据表。首先建立区域表,房屋交易都是分版块的,版块表结构如下CREATE TABLE `area` (<br> `id` int(11) NOT NULL AUTO_INCREMENT,<br> `name` varchar(155) NOT NULL COMMENT '南京市区',<br> `url` varchar(155) NOT NULL COMMENT '房源区域连接',<br> `pid` int(2) NOT NULL COMMENT '分类',<br> PRIMARY KEY (`id`)<br>) ENGINE=MyISAM DEFAULT CHARSET=utf8;
我是首先自己添加的一些区服的数据,其实可以采集这些,因为就那几个区,地址有限,就直接添加了。添加了是14条数据:
初始数据准备好了,就可以开始采集所有的区域版块入口地址了。贴上代码
area.php
<?php // +---------------------------------------------------------------------- // | 采集区域脚本 // +---------------------------------------------------------------------- // | Author: NickBai <1902822973@qq.com> // +---------------------------------------------------------------------- set_time_limit(0); require 'init.php'; //根据大区信息前往抓取 $sql = "select * from `area`"; $area = $db->query( $sql )->fetchAll( PDO::FETCH_ASSOC ); foreach($area as $key=>$vo){ $url = $vo['url']; $result = $curl->read($url); $charset = preg_match("/<meta.+?charset=[^\w]?([-\w]+)/i", $result['content'], $temp) ? strtolower( $temp[1] ) : ""; phpQuery::$defaultCharset = $charset; //设置默认编码 $html = phpQuery::newDocumentHTML( $result['content'] ); $span = $html['.items .sub-items a']; $st = $db->prepare("insert into area(name,url,pid) values(?,?,?)"); foreach($span as $v){ $v = pq( $v ); //为方便分页抓取,先加入分页规则 $href = trim( $v->attr('href') ) . 'p*/#filtersort'; $st->execute([ trim( $v->text() ), $href, $vo['id']]); } }
采集出的单条数据如下:百家湖 http://nanjing.anjuke.com/sale/baijiahu/p*/#filtersort
数据地址都有了,而且页面地址我加了*,这样就可以替换了,打开程序就能开始采集每个模块下的其他分页的书据了。最重要的主程序就要开始了;
新建hdetail表来记录采集来的房屋数信息:
CREATE TABLE `hdetail` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pid` int(5) NOT NULL COMMENT '区域id', `square` int(10) DEFAULT NULL COMMENT '面积', `housetype` varchar(55) DEFAULT '' COMMENT '房屋类型', `price` int(10) DEFAULT '0' COMMENT '单价', `allprice` int(10) DEFAULT '0' COMMENT '总价', `name` varchar(155) DEFAULT '' COMMENT '小区名称', `addr` varchar(155) DEFAULT '' COMMENT '小区地址', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
数据库有了,那么主程序奉上。
house.php
<?php // +---------------------------------------------------------------------- // | 采集各区具体房源信息 // +---------------------------------------------------------------------- // | Author: NickBai <1902822973@qq.com> // +---------------------------------------------------------------------- set_time_limit(0); require 'init.php'; //查询各板块数据 $sql = "select * from `area` where id > 14"; $allarea = $db->query($sql)->fetchAll( PDO::FETCH_ASSOC ); //http://www.php.cn/页面不存在时,会跳转到首页 foreach($allarea as $key=>$vo){ $url = $vo['url']; $i = 1; while ( true ){ $urls = str_replace( "*" , $i, $url); $result = $curl->read( $urls ); if( "http://nanjing.anjuke.com/sale/" == $result['info']['url'] ){ break; } $charset = preg_match("/<meta.+?charset=[^\w]?([-\w]+)/i", $result['content'], $temp) ? strtolower( $temp[1] ) : ""; phpQuery::$defaultCharset = $charset; //设置默认编码 $html = phpQuery::newDocumentHTML( $result['content'] ); $p = $html['#houselist-mod li .house-details']; $isGet = count( $p->elements ); //未采集到内容跳出,视为结束 if( 0 == $isGet ){ break; } foreach($p as $v){ $sql = "insert into hdetail(pid,square,housetype,price,allprice,name,addr) "; $pid = $vo['id']; $square = rtrim( trim( pq($v)->find("p:eq(1) span:eq(0)")->text() ), "平方米"); $htype = trim( pq($v)->find("p:eq(1) span:eq(1)")->text() ); $price = rtrim ( trim( pq($v)->find("p:eq(1) span:eq(2)")->text() ), "元/m²"); $area = explode(" ", trim( pq($v)->find("p:eq(2) span")->text() ) ); $name = str_replace( chr(194) . chr(160), "", array_shift($area) ); //utf-8中的空格无法用trim去除,所以采用此方法 $addr = rtrim( ltrim (trim( array_pop($area) ) , "["), "]" ); $allprice = trim( pq($v)->siblings(".pro-price")->find("span strong")->text() ); $sql .= " value( ". $pid .",". $square .", '". $htype ."' ,". $price .",". $allprice .", '". $name ."' ,'". $addr ."' )"; $db->query($sql); } echo mb_convert_encoding($vo['name'], "gbk", "utf-8") . " PAGE : ". $i . PHP_EOL; $i++; } }
跳过前面的大区,逐个采集。建议用cmd模式运行这个脚本。因为时间较长,所以用浏览器会导致卡死现象。至于不知道怎么用cmd命令执行php的,自己百度吧。
如果觉得慢的话,你们可以复制几分house.php文件,修改
$sql = "select * from `area` where id > 14";
根据id进行截取,多打开几个cmd执行,就变成多进程模式了。
下面就是等待了,我是8.16号采集的,一共采集了311226条数据。好了现在数有了,就可以开始分析了。我分析的代码如下:
<?php require "init.php"; $data = unserialize( file_get_contents('./data/nj.data') ); if( empty( $data ) ){ //全南京 $sql = "select avg(price) price from hdetail"; $nanjing = intval( $db->query($sql)->fetch( PDO::FETCH_ASSOC )['price'] ); //其余数据 $data = [ $nanjing, getOtherPrice('2,3,4,5,6,7,8,10'), getOtherPrice('1'), getOtherPrice('2'), getOtherPrice('3'), getOtherPrice('4'), getOtherPrice('5'), getOtherPrice('6'), getOtherPrice('7'), getOtherPrice('8'), getOtherPrice('9'), getOtherPrice('10'), getOtherPrice('11'), getOtherPrice('12'), getOtherPrice('13') ]; //添加缓存 file_put_contents('./data/nj.data', serialize( $data )); } //均价最高TOP10 $sql = "select avg(price) price,name from hdetail GROUP BY name ORDER BY price desc limit 10"; $res = $db->query($sql)->fetchAll( PDO::FETCH_ASSOC ); $x = ""; $y = ""; foreach($res as $vo){ $x .= "'" . $vo['name'] . "',"; $y .= intval( $vo['price'] ). ","; } //均价最低TOP10 $sql = "select avg(price) price,name from hdetail GROUP BY name ORDER BY price asc limit 10"; $res = $db->query($sql)->fetchAll( PDO::FETCH_ASSOC ); $xl = ""; $yl = ""; foreach($res as $vo){ $xl .= "'" . $vo['name'] . "',"; $yl .= intval( $vo['price'] ). ","; } //交易房型数据 $sql = "select count(0) allnum, housetype from hdetail GROUP BY housetype order by allnum desc"; $res = $db->query($sql)->fetchAll( PDO::FETCH_ASSOC ); $htype = ""; foreach($res as $vo){ $htype .= "[ '" . $vo['housetype'] . "', " .$vo['allnum']. "],"; } $htype = rtrim($htype, ','); //交易的房屋面积数据 $square = ['50平米以下', '50-70平米', '70-90平米', '90-120平米', '120-150平米', '150-200平米', '200-300平米', '300平米以上']; $sql = "select count(0) allnum, square from hdetail GROUP BY square"; $squ = $db->query($sql)->fetchAll( PDO::FETCH_ASSOC ); $p50 = 0; $p70 = 0; $p90 = 0; $p120 = 0; $p150 = 0; $p200 = 0; $p250 = 0; $p300 = 0; foreach($squ as $key=>$vo){ if( $vo['square'] < 50 ){ $p50 += $vo['allnum']; } if( $vo['square'] >= 50 && $vo['square'] < 70 ){ $p70 += $vo['allnum']; } if( $vo['square'] >= 70 && $vo['square'] < 90 ){ $p90 += $vo['allnum']; } if( $vo['square'] >= 90 && $vo['square'] < 120 ){ $p120 += $vo['allnum']; } if( $vo['square'] >= 120 && $vo['square'] < 150 ){ $p150 += $vo['allnum']; } if( $vo['square'] >= 150 && $vo['square'] < 200 ){ $p200 += $vo['allnum']; } if( $vo['square'] >= 200 && $vo['square'] < 300 ){ $p250 += $vo['allnum']; } if( $vo['square'] >= 300 ){ $p300 += $vo['allnum']; } } $num = [ $p50, $p70, $p90, $p120, $p150, $p200, $p250, $p300 ]; $sqStr = ""; foreach($square as $key=>$vo){ $sqStr .= "[ '" . $vo . "', " .$num[$key]. "],"; } //根据获取ids字符串获取对应的均价信息 function getOtherPrice($str){ global $db; $sql = "select id from area where pid in(" . $str . ")"; $city = $db->query($sql)->fetchAll( PDO::FETCH_ASSOC ); $ids = ""; foreach($city as $v){ $ids .= $v['id'] . ","; } $sql = "select avg(price) price from hdetail where pid in (".rtrim($ids, ",").")"; $price = intval( $db->query($sql)->fetch( PDO::FETCH_ASSOC )['price'] ); return $price; } ?> <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>南京房价分析</title> <link rel="shortcut icon" href="favicon.ico"> <link href="css/bootstrap.min.css?v=3.3.6" rel="stylesheet"> <link href="css/font-awesome.min.css?v=4.4.0" rel="stylesheet"> <link href="css/animate.min.css" rel="stylesheet"> <link href="css/style.min.css?v=4.1.0" rel="stylesheet"> </head> <body class="gray-bg"> <p class="wrapper wrapper-content"> <p class="row"> <p class="col-sm-12"> <p class="row"> <p class="col-sm-12"> <p class="ibox float-e-margins"> <p class="ibox-title"> <h5>全南京以及各区二手房均价</h5> <p class="ibox-tools"> <a class="collapse-link"> <i class="fa fa-chevron-up"></i> </a> <a class="close-link"> <i class="fa fa-times"></i> </a> </p> </p> <p class="ibox-content"> <p id="container"></p> </p> </p> </p> </p> </p> </p> <p class="row"> <p class="col-sm-6"> <p class="row"> <p class="col-sm-12"> <p class="ibox float-e-margins"> <p class="ibox-title"> <h5>均价最高的小区TOP10</h5> <p class="ibox-tools"> <a class="collapse-link"> <i class="fa fa-chevron-up"></i> </a> <a class="close-link"> <i class="fa fa-times"></i> </a> </p> </p> <p class="ibox-content"> <p id="avgpriceh"></p> </p> </p> </p> </p> </p> <p class="col-sm-6"> <p class="row"> <p class="col-sm-12"> <p class="ibox float-e-margins"> <p class="ibox-title"> <h5>均价最低的小区TOP10</h5> <p class="ibox-tools"> <a class="collapse-link"> <i class="fa fa-chevron-up"></i> </a> <a class="close-link"> <i class="fa fa-times"></i> </a> </p> </p> <p class="ibox-content"> <p id="avgpricel"></p> </p> </p> </p> </p> </p> </p> <p class="row"> <p class="col-sm-6"> <p class="row"> <p class="col-sm-12"> <p class="ibox float-e-margins"> <p class="ibox-title"> <h5>交易房型比例</h5> <p class="ibox-tools"> <a class="collapse-link"> <i class="fa fa-chevron-up"></i> </a> <a class="close-link"> <i class="fa fa-times"></i> </a> </p> </p> <p class="ibox-content"> <p id="htype"></p> </p> </p> </p> </p> </p> <p class="col-sm-6"> <p class="row"> <p class="col-sm-12"> <p class="ibox float-e-margins"> <p class="ibox-title"> <h5>交易房屋面积比例</h5> <p class="ibox-tools"> <a class="collapse-link"> <i class="fa fa-chevron-up"></i> </a> <a class="close-link"> <i class="fa fa-times"></i> </a> </p> </p> <p class="ibox-content"> <p id="square"></p> </p> </p> </p> </p> </p> </p> </p> <script type="text/javascript" src="js/jquery.min.js?v=2.1.4"></script> <script type="text/javascript" src="js/bootstrap.min.js?v=3.3.6"></script> <script type="text/javascript" src="http://cdn.hcharts.cn/highcharts/highcharts.js"></script> <script type="text/javascript"> $(function () { $('#container').highcharts({ chart: { type: 'column' }, title: { text: '全南京以及各区二手房均价' }, subtitle: { text: '来源于安居客8.16的数据' }, xAxis: { categories: ['全南京','江南八区','江宁区','鼓楼区','白下区','玄武区','建邺区','秦淮区','下关区','雨花台区','浦口区','栖霞区','六合区', '溧水区','高淳区','大厂'], crosshair: true }, yAxis: { min: 0, title: { text: '元/m²' } }, tooltip: { headerFormat: '<span style="font-size:10px">{point.key}</span><table>', pointFormat: '<tr><td style="color:{series.color};padding:0">{series.name}: </td>' + '<td style="padding:0"><b>{point.y:.1f} 元/m²</b></td></tr>', footerFormat: '</table>', shared: true, useHTML: true }, plotOptions: { column: { pointPadding: 0.2, borderWidth: 0, dataLabels:{ enabled:true// dataLabels设为true } } }, series: [{ name: '平均房价', data: [<?php echo implode(',', $data); ?>] }] }); //均价最高top10 $('#avgpriceh').highcharts({ chart: { type: 'column' }, title: { text: '均价最高的小区TOP10' }, subtitle: { text: '来源于安居客8.16的数据' }, xAxis: { categories: [<?=$x; ?>], crosshair: true }, yAxis: { min: 0, title: { text: '元/m²' } }, tooltip: { headerFormat: '<span style="font-size:10px">{point.key}</span><table>', pointFormat: '<tr><td style="color:{series.color};padding:0">{series.name}: </td>' + '<td style="padding:0"><b>{point.y:.1f} 元/m²</b></td></tr>', footerFormat: '</table>', shared: true, useHTML: true }, plotOptions: { column: { pointPadding: 0.2, borderWidth: 0, dataLabels:{ enabled:true// dataLabels设为true } } }, series: [{ name: '平均房价', data: [<?=$y; ?>] }] }); //均价最低top10 $('#avgpricel').highcharts({ chart: { type: 'column' }, title: { text: '均价最低的小区TOP10' }, subtitle: { text: '来源于安居客8.16的数据' }, xAxis: { categories: [<?=$xl; ?>], crosshair: true }, yAxis: { min: 0, title: { text: '元/m²' } }, tooltip: { headerFormat: '<span style="font-size:10px">{point.key}</span><table>', pointFormat: '<tr><td style="color:{series.color};padding:0">{series.name}: </td>' + '<td style="padding:0"><b>{point.y:.1f} 元/m²</b></td></tr>', footerFormat: '</table>', shared: true, useHTML: true }, plotOptions: { column: { pointPadding: 0.2, borderWidth: 0, dataLabels:{ enabled:true// dataLabels设为true } } }, series: [{ name: '平均房价', data: [<?=$yl; ?>] }] }); // Radialize the colors Highcharts.getOptions().colors = Highcharts.map(Highcharts.getOptions().colors, function (color) { return { radialGradient: { cx: 0.5, cy: 0.3, r: 0.7 }, stops: [ [0, color], [1, Highcharts.Color(color).brighten(-0.3).get('rgb')] // darken ] }; }); //房型类型 $('#htype').highcharts({ chart: { plotBackgroundColor: null, plotBorderWidth: null, plotShadow: false }, title: { text: '交易的二手房型比例' }, tooltip: { pointFormat: '{series.name}: <b>{point.percentage:.1f}%</b>' }, plotOptions: { pie: { allowPointSelect: true, cursor: 'pointer', dataLabels: { enabled: true, format: '<b>{point.name}</b>: {point.percentage:.1f} %', style: { color: (Highcharts.theme && Highcharts.theme.contrastTextColor) || 'black' }, connectorColor: 'silver' } } }, series: [{ type: 'pie', name: 'Browser share', data: [ <?=$htype; ?> ] }] }); //房型面积类型 $('#square').highcharts({ chart: { plotBackgroundColor: null, plotBorderWidth: null, plotShadow: false }, title: { text: '交易的二手房面积比例' }, tooltip: { pointFormat: '{series.name}: <b>{point.percentage:.1f}%</b>' }, plotOptions: { pie: { allowPointSelect: true, cursor: 'pointer', dataLabels: { enabled: true, format: '<b>{point.name}</b>: {point.percentage:.1f} %', style: { color: (Highcharts.theme && Highcharts.theme.contrastTextColor) || 'black' }, connectorColor: 'silver' } } }, series: [{ type: 'pie', name: 'Browser share', data: [ <?=$sqStr; ?> ] }] }); }); </script> </body> </html>
页面效果如下:
哈哈,房价果然很是吓人,二手房都这个价钱了。还有什么有趣的信息,你自己可以去发掘。