Use php crawler to analyze Nanjing housing prices
Using php crawler to analyze Nanjing housing prices
A few days ago I saw an article on csdn, using Python to write a crawler to analyze Shanghai housing prices. It feels quite interesting. It just so happens that I recently wrote about article collection in the snake backend. I will also use the PHP crawler to analyze Nanjing's housing prices. Let’s get started.
The dependency files of this crawler: The first is the CURL class of the master ares333. I am using an early version. This is the github project address of https://github.com/ares333/php-curlmulti. The curl he wrote is really awesome!
The collection uses phpQuery. If you don’t know this category, you can search it on Baidu yourself.
As for the source of the data, I chose Anjuke. The amount of data is still acceptable. I opened the Anjuke channel to Nanjing. Start analyzing their page structure. As for how to use phpQuery to analyze the page structure collection method, I will not introduce it in detail here. After analyzing the structure, OK, start building the data table. First, create an area table. House transactions are divided into sections. The section table structure is as followsCREATE TABLE `area` (<br> `id` int(11) NOT NULL AUTO_INCREMENT,<br> `name` varchar(155) NOT NULL COMMENT 'Nanjing City District',<br> `url` varchar(155) NOT NULL COMMENT 'House area connection',<br> `pid` int(2) NOT NULL COMMENT 'Category',<br> PRIMARY KEY (`id`)<br>) ENGINE=MyISAM DEFAULT CHARSET=utf8; I first added some district server data myself. In fact, I can collect these, because there are only a few districts with limited addresses, so I added them directly. 14 pieces of data have been added: 
After the initial data is ready, you can start collecting all regional block entrance addresses. Paste the code
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']]);
}
}The single piece of data collected is as follows: Baijiahu http://nanjing.anjuke.com/sale/baijiahu/p*/#filtersort Data address I have them all, and I added * to the page address, so that it can be replaced. When you open the program, you can start collecting documents on other pages under each module. The most important main program is about to begin;
Create a new hdetail table to record the collected house number information:
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;
Now that the database is available, the main program is presented.
<?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++;
}
}
If you feel it is slow, you can copy a few parts of the house.php file, modify
$sql = "select * from `area` where id > 14";
to intercept according to the ID, open a few more cmds for execution, and it will become a multi-process mode.
Now it’s time to wait. I collected it on 8.16 and collected a total of 311,226 pieces of data. Okay, now that we have the numbers, we can start analyzing. The code I analyzed is as follows:
<?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>
The page effect is as follows:
Haha, the housing prices are really scary, second-hand houses are already at this price. If there is any interesting information, you can discover it yourself. 
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to solve win7 driver code 28
Dec 30, 2023 pm 11:55 PM
Some users encountered errors when installing the device, prompting error code 28. In fact, this is mainly due to the driver. We only need to solve the problem of win7 driver code 28. Let’s take a look at what should be done. Do it. What to do with win7 driver code 28: First, we need to click on the start menu in the lower left corner of the screen. Then, find and click the "Control Panel" option in the pop-up menu. This option is usually located at or near the bottom of the menu. After clicking, the system will automatically open the control panel interface. In the control panel, we can perform various system settings and management operations. This is the first step in the nostalgia cleaning level, I hope it helps. Then we need to proceed and enter the system and
What to do if the blue screen code 0x0000001 occurs
Feb 23, 2024 am 08:09 AM
What to do with blue screen code 0x0000001? The blue screen error is a warning mechanism when there is a problem with the computer system or hardware. Code 0x0000001 usually indicates a hardware or driver failure. When users suddenly encounter a blue screen error while using their computer, they may feel panicked and at a loss. Fortunately, most blue screen errors can be troubleshooted and dealt with with a few simple steps. This article will introduce readers to some methods to solve the blue screen error code 0x0000001. First, when encountering a blue screen error, we can try to restart
Solve the 'error: expected initializer before 'datatype'' problem in C++ code
Aug 25, 2023 pm 01:24 PM
Solve the "error:expectedinitializerbefore'datatype'" problem in C++ code. In C++ programming, sometimes we encounter some compilation errors when writing code. One of the common errors is "error:expectedinitializerbefore'datatype'". This error usually occurs in a variable declaration or function definition and may cause the program to fail to compile correctly or
The computer frequently blue screens and the code is different every time
Jan 06, 2024 pm 10:53 PM
The win10 system is a very excellent high-intelligence system. Its powerful intelligence can bring the best user experience to users. Under normal circumstances, users’ win10 system computers will not have any problems! However, it is inevitable that various faults will occur in excellent computers. Recently, friends have been reporting that their win10 systems have encountered frequent blue screens! Today, the editor will bring you solutions to different codes that cause frequent blue screens in Windows 10 computers. Let’s take a look. Solutions to frequent computer blue screens with different codes each time: causes of various fault codes and solution suggestions 1. Cause of 0×000000116 fault: It should be that the graphics card driver is incompatible. Solution: It is recommended to replace the original manufacturer's driver. 2,
GE universal remote codes program on any device
Mar 02, 2024 pm 01:58 PM
If you need to program any device remotely, this article will help you. We will share the top GE universal remote codes for programming any device. What is a GE remote control? GEUniversalRemote is a remote control that can be used to control multiple devices such as smart TVs, LG, Vizio, Sony, Blu-ray, DVD, DVR, Roku, AppleTV, streaming media players and more. GEUniversal remote controls come in various models with different features and functions. GEUniversalRemote can control up to four devices. Top Universal Remote Codes to Program on Any Device GE remotes come with a set of codes that allow them to work with different devices. you may
Resolve code 0xc000007b error
Feb 18, 2024 pm 07:34 PM
Termination Code 0xc000007b While using your computer, you sometimes encounter various problems and error codes. Among them, the termination code is the most disturbing, especially the termination code 0xc000007b. This code indicates that an application cannot start properly, causing inconvenience to the user. First, let’s understand the meaning of termination code 0xc000007b. This code is a Windows operating system error code that usually occurs when a 32-bit application tries to run on a 64-bit operating system. It means it should
Detailed explanation of the causes and solutions of 0x0000007f blue screen code
Dec 25, 2023 pm 02:19 PM
Blue screen is a problem we often encounter when using the system. Depending on the error code, there will be many different reasons and solutions. For example, when we encounter the problem of stop: 0x0000007f, it may be a hardware or software error. Let’s follow the editor to find out the solution. 0x000000c5 blue screen code reason: Answer: The memory, CPU, and graphics card are suddenly overclocked, or the software is running incorrectly. Solution 1: 1. Keep pressing F8 to enter when booting, select safe mode, and press Enter to enter. 2. After entering safe mode, press win+r to open the run window, enter cmd, and press Enter. 3. In the command prompt window, enter "chkdsk /f /r", press Enter, and then press the y key. 4.
How to use Copilot to generate code
Mar 23, 2024 am 10:41 AM
As a programmer, I get excited about tools that simplify the coding experience. With the help of artificial intelligence tools, we can generate demo code and make necessary modifications as per the requirement. The newly introduced Copilot tool in Visual Studio Code allows us to create AI-generated code with natural language chat interactions. By explaining functionality, we can better understand the meaning of existing code. How to use Copilot to generate code? To get started, we first need to get the latest PowerPlatformTools extension. To achieve this, you need to go to the extension page, search for "PowerPlatformTool" and click the Install button
