利用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>
页面效果如下:
哈哈，房价果然很是吓人，二手房都这个价钱了。还有什么有趣的信息，你自己可以去发掘。
Introduction à l'utilisation du code complet VBS
La différence entre vscode et vs
Comment insérer une vidéo en HTML
Introduction aux outils de développement logiciel
Qu'apprendre en cours de programmation
Comment définir une bordure pointillée CSS
Introduction aux touches de raccourci pour réduire Windows Windows
erreur MySQL 10060
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
