链家广州二手房数据 2023

还记得在2019年的夏天曾经用 R 爬过一份广州在 lianjia.com 放盘数据 (博客1，博客2，博客3)。翻看当时的记录：我稚嫩地惊叹着广州二手房放盘量已经超过50,000套了。尔后，疫情袭来，三年封锁。这个夏天当我用 Python 再次爬 lianjia.com 广州的放盘数据，却坦然地接受超120,000套巨量放盘数量。

我分别在5月初和6月初各爬了一次，方便比较二手房数据的变化。如果以后还有时间和精力会继续每月一更数据。

简单地清洗了数据，其他分析很还没做，就暂时分享一下 SQL 能看到的数字和趋势吧。

---

全市平均总价和均价

SELECT 
strftime('%Y-%m', date) as Month
COUNT(total_price) as Count, 
ROUND(AVG(total_price), 2) as Avg_Total_Price, 
ROUND(AVG(unit_price), 2) as Avg_Unit_Price
FROM gz
GROUP BY date

Month	Count	Avg_Total_Price	Avg_Unit_Price
2023-05	119042	335.21	35232.99
2023-06	121251	336.67	35278.07

---

全市各区平均总价和均价

SELECT 
strftime('%Y-%m', date) as Month,
district as District,
COUNT(total_price) as Count, 
ROUND(AVG(total_price), 2) as Avg_Total_Price, 
ROUND(AVG(unit_price), 2) as Avg_Unit_Price
FROM gz
GROUP BY district, date
ORDER BY Avg_Unit_Price DESC;

Month	District	Count	Avg_Total_Price	Avg_Unit_Price
2023-06	天河	14218	614.78	64940.46
2023-05	天河	13794	610.66	64691.91
2023-06	越秀	9007	427.34	55810.87
2023-05	越秀	8845	422.72	55589.06
2023-05	海珠	12724	413.44	47381.29
2023-06	海珠	13088	413.68	47254.85
2023-05	荔湾	7278	330.14	39530.19
2023-06	荔湾	7425	331.93	39476.55
2023-05	白云	12436	323.99	34393.4
2023-06	白云	12631	322.83	34240.06
2023-05	黄埔	7130	308.32	33441.71
2023-06	黄埔	7326	308.66	33285.32
2023-05	番禺	22027	327.2	29453.26
2023-06	番禺	22351	328.3	29391.85
2023-05	南沙	6785	243.09	22459.74
2023-06	南沙	6857	241.56	22329.02
2023-05	增城	14397	189.51	17288.2
2023-06	增城	14571	188.8	17115.31
2023-05	花都	11176	173.18	15648.84
2023-06	花都	11253	171.31	15536.09
2023-05	从化	2450	134.86	11623.92
2023-06	从化	2524	134.94	11611.71

---

热门区域平均总价和均价

SELECT 
strftime('%Y-%m', date) as Month,
position as Location,
COUNT(total_price) as Count, 
ROUND(AVG(total_price), 2) as Avg_Total_Price,
ROUND(AVG(unit_price), 2) as Avg_Unit_Price,
MIN(total_price) as Min_Total_Price,
MIN(unit_price) as Min_Unit_Price
FROM gz 
WHERE position LIKE '珠江新城%' 
GROUP BY position, date

Month	Location	Count	Avg_Total_Price	Avg_Unit_Price	Min_Total_Price	Min_Unit_Price
2023-05	珠江新城东	602	1402.61	100603.31	68.0	24616
2023-06	珠江新城东	655	1409.19	100814.75	68.0	24616
2023-05	珠江新城中	474	1583.22	138480.37	255.0	50973
2023-06	珠江新城中	521	1536.53	137023.28	255.0	50973
2023-05	珠江新城西	720	787.99	83997.19	140.0	29717
2023-06	珠江新城西	729	785.41	83721.94	140.0	29717

---

热门小区平均总价和均价

SELECT 
strftime('%Y-%m', date) as Month,
region as Region,
COUNT(total_price) as Count, 
ROUND(AVG(total_price), 2) as Avg_Total_Price,
ROUND(AVG(unit_price), 2) as Avg_Unit_Price,
MIN(total_price) as Min_Total_Price,
MIN(unit_price) as Min_Unit_Price
FROM gz 
WHERE region LIKE '中海花城湾%'
GROUP BY date

Month	Region	Count	Avg_Total_Price	Avg_Unit_Price	Min_Total_Price	Min_Unit_Price
2023-05	中海花城湾	31	2322.81	190289.81	1055.0	158456
2023-06	中海花城湾	42	2065.17	186906.24	1038.0	155903

---

小结

广州各区的放盘量维持增长趋势。强势区（天河与越秀）二手房总体均价微涨，其余各区二手房价格呈下跌趋势。广州二手房风向标区域珠江新城放盘量增加但价格下跌。网红小区中海花城湾平均总价下降约250万。总之，6月的房子比5月更不好卖了。

如果有需要数据的可以自取。如果觉得有用的请星标。 GitHub Link