链家广州二手房数据 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 dateMonthCountAvg_Total_PriceAvg_Unit_Price2023-05119042335.2135232.992023-06121251336.6735278.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;MonthDistrictCountAvg_Total_PriceAvg_Unit_Price2023-06天河14218614.7864940.462023-05天河13794610.6664691.912023-06越秀9007427.3455810.872023-05越秀8845422.7255589.062023-05海珠12724413.4447381.292023-06海珠13088413.6847254.852023-05荔湾7278330.1439530.192023-06荔湾7425331.9339476.552023-05白云12436323.9934393.42023-06白云12631322.8334240.062023-05黄埔7130308.3233441.712023-06黄埔7326308.6633285.322023-05番禺22027327.229453.262023-06番禺22351328.329391.852023-05南沙6785243.0922459.742023-06南沙6857241.5622329.022023-05增城14397189.5117288.22023-06增城14571188.817115.312023-05花都11176173.1815648.842023-06花都11253171.3115536.092023-05从化2450134.8611623.922023-06从化2524134.9411611.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, dateMonthLocationCountAvg_Total_PriceAvg_Unit_PriceMin_Total_PriceMin_Unit_Price2023-05珠江新城东6021402.61100603.3168.0246162023-06珠江新城东6551409.19100814.7568.0246162023-05珠江新城中4741583.22138480.37255.0509732023-06珠江新城中5211536.53137023.28255.0509732023-05珠江新城西720787.9983997.19140.0297172023-06珠江新城西729785.4183721.94140.029717热门小区平均总价和均价
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 dateMonthRegionCountAvg_Total_PriceAvg_Unit_PriceMin_Total_PriceMin_Unit_Price2023-05中海花城湾312322.81190289.811055.01584562023-06中海花城湾422065.17186906.241038.0155903小结
广州各区的放盘量维持增长趋势。强势区（天河与越秀）二手房总体均价微涨，其余各区二手房价格呈下跌趋势。广州二手房风向标区域珠江新城放盘量增加但价格下跌。网红小区中海花城湾平均总价下降约250万。总之，6月的房子比5月更不好卖了。
如果有需要数据的可以自取。如果觉得有用的请星标。 GitHub Link
出处：https://www.cnblogs.com/yukiwu/ 本文版权归作者和博客园所有，欢迎转载，转载请标明出处（附上博客链接）。 如果您觉得本篇博文对您有所收获，请点击右下角的 [推荐]，谢谢！
关注我的公众号,不定期更新学习心得
来源:https://www.cnblogs.com/yukiwu/p/17463801.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！
页:
[1]