需求分析数据采集数据清洗数据分析模型结果
需求分析 数据采集 数据清洗 数据分析 模型结果1、需求分析
因为家住在合肥，希望通过分析下二手房的房价看看做个参考
2、数据采集
之前准备在安居客上爬二手房的数据，但是安居客的robots.txt文件禁止爬取内容，我之前试了不少次安居客都警告我不能爬了，所以我掉转转战链家，链家的网站是 link.，爬取了50页的数据，代码如下:
#链家合肥二手房数据爬取
from openpyxl import load_workbook,Workbook
import requests
import openpyxl as op
from bs4 import BeautifulSoup
import numpy as np
hp=[]
hl=[]
#爬取50页的数据
for i in range(1,51,1):
#伪装请求头
headers = {'User-Agent':'Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:95.0) Gecko/20100101 Firefox/95.0'}
res=requests.get('https://hf.lianjia.com/ershoufang/pg'+str(i)+'/',headers=headers)
house_info=BeautifulSoup(res.text,'html.parser')
#爬取房价（how much/m**2)
house_price=house_info.find_all('div',class_='unitPrice')
# house_basic=house_info.find_all('div',class_="houseInfo")
#爬取二手房地址
house_loc=house_info.find_all('div',class_="positionInfo")
for i in house_price:
hp.append(i.text)
for k in house_loc:
hl.append(k.text)
#写入已有的excel文件
def write(num_list,col_num):
bg = op.load_workbook(r"hefei_secondary_hand_house.xlsx")
sheet = bg.active
for i in range(1, len(num_list)+1):
sheet.cell(i,col_num,num_list[i - 1])
bg.save("hefei_secondary_hand_house.xlsx")
write(hl,8)
write(hp,9)
我们看看爬取的数据（部分截图）:
3、数据清洗
爬取的数据比较混乱，比如说有些房子只有结构的信息（比如板塔结合），而没有建造时间，而有些房子既有建造时间也有结构的信息，所以我们要将有些没有建造时间的数据进行删除，这样方便我们分析。对于几室几厅，我们可以对其进行相加；对于多少平米，我们去掉后面的平米并将数据化为int；朝向、状态和结构是分类变量，我们可以之后创建哑变量；对于楼层我们可以对低楼层进行总楼层/4向上取整作为变量的值，对于中楼层进行总楼层整除2作为变量的值，对于高楼层进行总楼层乘3向下取整再整除4作为变量的值；建造时间变量的值修改为（2022-建造时间）；房价均价取出整数部分；小区位置参考博客link.，计算小区位置与安徽省政府的距离作为变量的值。具体jupyter代码如下:
import pandas as pd
import math
data=pd.read_excel('hefei_secondary_hand_house.xlsx',engine='openpyxl',header=None,
names=['户型','面积','朝向','状态','楼层位置','建造时间','结构','小区位置','均价'])
#删除数据
need_del=[]
for i in range(len(data['建造时间'])):
if data['建造时间'][i][-2]!='建':
need_del.append(i)
data.drop(data.index[need_del],inplace=True)
data= data.reset_index(drop=True)
for i in range(len(data['户型'])):
data['户型'][i]=int(data['户型'][i][0])+int(data['户型'][i][2])
for i in range(len(data['面积'])):
data['面积'][i]=float(data['面积'][i][1:-3])
for i in range(len(data['朝向'])):
if len(data['朝向'][i])<=4:
data['朝向'][i]='单面'
elif len(data['朝向'][i])<=6:
data['朝向'][i]='双面'
else:
data['朝向'][i]='三面'
for i in range(len(data['状态'])):
data['状态'][i]=data['状态'][i].replace(" ","")
for i in range(len(data['楼层位置'])):
if data['楼层位置'][i][-2]=='层':
data['楼层位置'][i]=int(data['楼层位置'][i][1:-2])
elif data['楼层位置'][i][1]=='下':
data['楼层位置'][i]=2
else:
if data['楼层位置'][i][1]=='低':
data['楼层位置'][i]=math.ceil(int(data['楼层位置'][i][6:-3])/4)
elif data['楼层位置'][i][1]=='中':
data['楼层位置'][i]=int(data['楼层位置'][i][6:-3])//2
else:
data['楼层位置'][i]=(int(data['楼层位置'][i][6:-3])*3)//4
for i in range(len(data['建造时间'])):
data['建造时间'][i]=2022-int(data['建造时间'][i][1:-3])
for i in range(len(data['结构'])):
data['结构'][i]=data['结构'][i].replace(" ","")
#为了方便后面使用高德地图的地理编码API，把地址改成逐级递减的形式
for i in range(len(data['小区位置'])):
data['小区位置'][i]=data['小区位置'][i].replace(" ","")
for i in range(len(data['小区位置'])):
arr='合肥市'
for j in range(len(data['小区位置'][i].split('-'))-1,-1,-1):
arr+=data['小区位置'][i].split('-')[j]
data['小区位置'][i]=arr
arr=''
for i in range(len(data['均价'])):
data['均价'][i]=data['均价'][i].replace(" ","")
data['均价'][i]=data['均价'][i].replace(",","")
for i in range(len(data['均价'])):
data['均价'][i]=int(data['均价'][i][:-3])
#最后将数据写入csv文件
data.to_csv('data_stats.csv',header=True,index=None,encoding='utf-8')
查看部分数据:
接下来就是利用高德开放平台，网址link.，注册一个，注册好了点击控制台，点击应用管理，创建新应用，查看link.申请一个key，然后就可以得到每个地方的经纬度了。具体代码如下:
import requests
import json
from math import sin, asin, cos, radians, fabs, sqrt
import pandas as pd
EARTH_RADIUS = 6371 # 地球平均半径大约6371km
def hav(theta):
s = sin(theta / 2)
return s * s
def get_distance_hav(lat0,lng0,lat1,lng1):
# 用haversine公式计算球面两点间的距离
# 经纬度转换成弧度
lat0 = radians(lat0)
lat1 = radians(lat1)
lng0 = radians(lng0)
lng1 = radians(lng1)
dlng = fabs(lng0 - lng1)
dlat = fabs(lat0 - lat1)
h = hav(dlat) + cos(lat0) * cos(lat1) * hav(dlng)
distance = 2 * EARTH_RADIUS * asin(sqrt(h)) # km
return round(distance,2)
#这是安徽省政府的经纬度
base_lat_lng=[31.733195, 117.325963]
xq_location=pd.read_csv('data_stats.csv')
for j in range(len(xq_location['小区位置'])):
arr=[]
url = 'https://restapi.amap.com/v3/geocode/geo' # 输入API问号前固定不变的部分
params = {'key':'5957eabb12999886bf8dbdc76bdb7791', # 注册高德地图，创建应用获得的key，这是我自己创建的key
'address': xq_location['小区位置'][j]} # 将两个参数放入字典
res = requests.get(url, params)
results = json.loads(res.text)
# 打印结果
for i in range(len(results["geocodes"][0]["location"].split(','))-1,-1,-1):
arr.append(float(results["geocodes"][0]["location"].split(',')[i]))
xq_location['小区位置'][j]=get_distance_hav(base_lat_lng[0],base_lat_lng[1],arr[0],arr[1])
arr=[]
#将数据清洗后的结果写入csv文件
xq_location.to_csv('hefei_sechouse_data_stats.csv',header=True,index=None,encoding='utf-8')
4、数据分析与可视化
sklearn中的linear_model
#jupyter
import pandas as pd
import numpy as np
from sklearn.utils import shuffle
from sklearn import linear_model
from sklearn.metrics import mean_squared_error,r2_score
datasets=pd.read_csv('hefei_sechouse_data_new.csv')
data_array=np.array(datasets)
data,target=data_array[:,0:-1],np.log(data_array[:,-1])
X,y=shuffle(data,target,random_state=123)
offset=int(X.shape[0]*0.8)
X_train,y_train=X[:offset],y[:offset]
X_test,y_test=X[offset:],y[offset:]
y_train=y_train.reshape((-1,1))
y_test=y_test.reshape((-1,1))
# print("X_train's shape",X_train.shape)
# print("X_test's shape",X_test.shape)
# print("y_train's shape",y_train.shape)
# print("y_test's shape",y_test.shape)
X_train,y_train
------------------分隔线------------------
sk_lasso=linear_model.Lasso(alpha=0.1)
clf=linear_model.Ridge(alpha=0.01)
regr=linear_model.LinearRegression(fit_intercept=True,normalize=False)
sk_lasso.fit(X_train,y_train)
clf.fit(X_train,y_train)
regr.fit(X_train,y_train)
y_pred_lasso=sk_lasso.predict(X_test)
y_pred_ridge=clf.predict(X_test)
y_pred=regr.predict(X_test)
# print("mean squared error: %.2f"% mean_squared_error(y_test,y_pred))
print('R Square score: %.2f'% r2_score(y_test,y_pred))
print('LASSO R Square score: %.2f'% r2_score(y_test,y_pred_lasso))
print('Ridge R Square score: %.2f'% r2_score(y_test,y_pred_ridge))
# print(sk_lasso.coef_)
print(regr.coef_)
print(sk_lasso.coef_)
print(clf.coef_)
未引入哑变量:
R Square score: 0.25
LASSO R Square score: 0.26
Ridge R Square score: 0.25
[[ 0.01842696 -0.00057685 0.01486218 -0.01579192 0.00080404 0.00033422
0.01900491 -0.02674357]]
[ 0.00000000e+00 -3.23163258e-05 0.00000000e+00 -0.00000000e+00
0.00000000e+00 -0.00000000e+00 0.00000000e+00 -2.41732531e-02]
[[ 0.0184266 -0.00057684 0.0148615 -0.01579173 0.00080405 0.00033421
0.01900436 -0.02674356]]
如果引入哑变量，可以用stats模块来进行分析
代码如下:
from statsmodels.formula.api import ols
house=pd.read_csv('hefei_sechouse_data_stats.csv')
norminal_data1=house['朝向']
norminal_data2=house['状态']
norminal_data3=house['结构']
dummies1=pd.get_dummies(norminal_data1)
dummies1.drop(columns=['三面'],inplace=True)
dummies2=pd.get_dummies(norminal_data2)
dummies2.drop(columns=['其他'],inplace=True)
dummies3=pd.get_dummies(norminal_data3)
dummies3.drop(columns=['暂无数据'],inplace=True)
results=pd.concat(objs=[house,dummies1,dummies2,dummies3],axis=1)
results=results.drop(columns=['朝向','状态','结构'])
# results
tmp='+'.join(results.drop(columns=['均价']).columns)
lm=ols(formula='均价 ~ {}'.format(tmp),data=results).fit()
lm.summary()
我们可以观察到R_squared的值为0.182，很小，模型拟合的很不好，所以我们还是用R_squared值更高的上述模型进行预测:
stats_array=np.array(results)
data_new,target_new=np.hstack((stats_array[:,0:5],stats_array[:,6:])),np.log(stats_array[:,5])
X_new,y_new=shuffle(data_new,target_new,random_state=123)
offset=int(X_new.shape[0]*0.8)
X_train_,y_train_=X_new[:offset],y_new[:offset]
X_test_,y_test_=X_new[offset:],y_new[offset:]
y_train_=y_train_.reshape((-1,1))
y_test_=y_test_.reshape((-1,1))
# print("X_train's shape",X_train.shape)
# print("X_test's shape",X_test.shape)
# print("y_train's shape",y_train.shape)
# print("y_test's shape",y_test.shape)
#X_train_,y_train_
# from linear_regression import *
# loss_his,params,grads=linear_train(X_train,y_train,0.01,200000)
# print(params)
sk_lasso_=linear_model.Lasso(alpha=0.01)
clf_=linear_model.Ridge(alpha=1000)
regr_=linear_model.LinearRegression(fit_intercept=True,normalize=False)
sk_lasso_.fit(X_train_,y_train_)
clf_.fit(X_train_,y_train_)
regr_.fit(X_train_,y_train_)
y_pred_lasso_=sk_lasso_.predict(X_test_)
y_pred_ridge_=clf_.predict(X_test_)
y_pred_=regr_.predict(X_test_)
# # print("mean squared error: %.2f"% mean_squared_error(y_test,y_pred))
# print('R Square score: %.3f'% r2_score(y_test_,y_pred_))
# print('LASSO R Square score: %.3f'% r2_score(y_test_,y_pred_lasso_))
# print('Ridge R Square score: %.3f'% r2_score(y_test_,y_pred_ridge_))
# # print(sk_lasso_.coef_)
# print(regr_.coef_)
# print(sk_lasso_.coef_)
# print(clf_.coef_)
#选取一个小区的二手房，参数写明，进行预测
near_my_home=[5,95.35,8,4,16.8,1,0,1,0,0,0,0,1,0]
pre=np.array([near_my_home])
y_pre_lasso=sk_lasso_.predict(pre)
y_pre_ridge=clf_.predict(pre)
y_pre=regr_.predict(pre)
print(np.exp(y_pre))
print(np.exp(y_pre_lasso))
print(np.exp(y_pre_ridge))
均价:
[[17133.67911903]]
[16501.90102646]
[[16604.71670699]]
5、 模型结果
该房子在链家上挂出的价格为16419RMB，LASSO回归预测的结果最为接近。
