链家广州二手房数据 2023

烨哥

还记得在2019年的夏天曾经用 R 爬过一份广州在 lianjia.com 放盘数据 (博客1，博客2，博客3)。翻看当时的记录：我稚嫩地惊叹着广州二手房放盘量已经超过50,000套了。尔后，疫情袭来，三年封锁。这个夏天当我用 Python 再次爬 lianjia.com 广州的放盘数据，却坦然地接受超120,000套巨量放盘数量。
我分别在5月初和6月初各爬了一次，方便比较二手房数据的变化。如果以后还有时间和精力会继续每月一更数据。
简单地清洗了数据，其他分析很还没做，就暂时分享一下 SQL 能看到的数字和趋势吧。

全市平均总价和均价

1. SELECT
2. strftime('%Y-%m', date) as Month
3. COUNT(total_price) as Count,
4. ROUND(AVG(total_price), 2) as Avg_Total_Price,
5. ROUND(AVG(unit_price), 2) as Avg_Unit_Price
6. FROM gz
7. GROUP BY date

复制代码

MonthCountAvg_Total_PriceAvg_Unit_Price2023-05119042335.2135232.992023-06121251336.6735278.07全市各区平均总价和均价

1. SELECT
2. strftime('%Y-%m', date) as Month,
3. district as District,
4. COUNT(total_price) as Count,
5. ROUND(AVG(total_price), 2) as Avg_Total_Price,
6. ROUND(AVG(unit_price), 2) as Avg_Unit_Price
7. FROM gz
8. GROUP BY district, date
9. 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热门区域平均总价和均价

1. SELECT
2. strftime('%Y-%m', date) as Month,
3. position as Location,
4. COUNT(total_price) as Count,
5. ROUND(AVG(total_price), 2) as Avg_Total_Price,
6. ROUND(AVG(unit_price), 2) as Avg_Unit_Price,
7. MIN(total_price) as Min_Total_Price,
8. MIN(unit_price) as Min_Unit_Price
9. FROM gz
10. WHERE position LIKE '珠江新城%'
11. GROUP BY position, date

复制代码

MonthLocationCountAvg_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热门小区平均总价和均价

1. SELECT
2. strftime('%Y-%m', date) as Month,
3. region as Region,
4. COUNT(total_price) as Count,
5. ROUND(AVG(total_price), 2) as Avg_Total_Price,
6. ROUND(AVG(unit_price), 2) as Avg_Unit_Price,
7. MIN(total_price) as Min_Total_Price,
8. MIN(unit_price) as Min_Unit_Price
9. FROM gz
10. WHERE region LIKE '中海花城湾%'
11. GROUP BY date

复制代码

MonthRegionCountAvg_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】 我们会及时删除侵权内容，谢谢合作！