# -*- coding: utf-8 -*-^"""^Created on Thu Jan 9 11:36:00 2020^^@author: QIAOQICHAO258^"""^^^^^import pandas as pd^import numpy as np^import time^from tqdm import tqdm^import codecs^import csv^import os^from math import radians, cos, sin, asin, sqrt ^import math^from casevalue import CaseValue^from xgboost import XGBRegressor^from sklearn.model_selection import GridSearchCV^from sklearn.preprocessing import minmax_scale^pi = math.pi^EARTH_REDIUS = 6378.137^^# 經(jīng)度系數(shù) 1米所對應的經(jīng)度^LONGITUDE_COEF = 0.000011^# 緯度系數(shù) 1米所對應的度度^LATITUDE_COEF = 0.000009^^# 城市^city = '深圳市'^# poi數(shù)據(jù)文件^path_poi = '高德POI/%s.xlsx'%city^# 小區(qū)清單數(shù)據(jù)文件^path_community = '小區(qū)清單/小區(qū)清單_高德地址_%s.xlsx'%city^^^ ^^def rad(d):^ return d * pi / 180.0^^# 測算兩點經(jīng)緯度之間的距離^def getDistance1(lat1, lng1, lat2, lng2):^ radLat1 = rad(lat1)^ radLat2 = rad(lat2)^ a = radLat1 - radLat2^ b = rad(lng1) - rad(lng2)^ s = 2 * math.asin(math.sqrt(math.pow(math.sin(a/2), 2) + math.cos(radLat1) * math.cos(radLat2) * math.pow(math.sin(b/2), 2)))^ s = s * EARTH_REDIUS^ return s * 1000^^def getDistance(lon1, lat1, lon2, lat2):^ #計算距離 ^^ # 將十進制度數(shù)轉(zhuǎn)化為弧度 ^ lon1, lat1, lon2, lat2 = map(radians, [lon1, lat1, lon2, lat2]) ^ # haversine公式 ^ dlon = lon2 - lon1 ^ dlat = lat2 - lat1 ^ a = sin(dlat/2)**2 + cos(lat1) * cos(lat2) * sin(dlon/2)**2^ c = 2 * asin(sqrt(a)) ^ r = 6378.137 # 地球平均半徑，單位為公里 ^ return c * r * 1000^^# 獲取poi數(shù)據(jù)^def get_poi_data(poi_path):^ ^ read_poi_data = pd.read_excel(poi_path)^# read_poi_data = pd.read_csv(poi_path, encoding='utf-8', engine='python')^ ^ return read_poi_data^^^# 獲取小區(qū)清單的數(shù)據(jù) ^def get_community_data(comm_path):^ ^ read_community_data = pd.read_excel(comm_path)^^ return read_community_data^^# 案例掛牌數(shù)據(jù)^def get_shilian_data(path_data):^ ^ # 不能中文路徑^# shilian_data = pd.read_table(shilian_path, sep='$', error_bad_lines=False,encoding='utf-8', low_memory=False,quoting=csv.QUOTE_NONE)^ ^ # 可以中文路徑^ with codecs.open(path_data, 'r' ,encoding='utf-8', errors='ignore') as f:^ shilian_data = pd.read_csv(f, sep='$', error_bad_lines=False, low_memory=False, quoting=csv.QUOTE_NONE)^ # 讀取城市的映射表 ^ bazhuayu = pd.read_excel(r'D:\Users\QIAOQICHAO258\Desktop\合作小區(qū)畫像代碼\各類數(shù)據(jù)\新映射表\九月八爪魚案例-深圳交付.xlsx')^ # 案例名稱和ID^ dict_temp = dict(zip(bazhuayu['community_sources'], bazhuayu['匹配ID-匯總']))^ # 映射^ shilian_data['fang_case.community_id'] = shilian_data['bazhuayu_case.community_sources'].map(dict_temp)^ ^ return shilian_data^^^^def select_facter_poi(temp_comm_longitude, temp_comm_latitude, poi_data, len_coef):^ ^# start_T = time.time()^ ^ ^ ^ poi_data_spilt = poi_data[ (temp_comm_longitude + LONGITUDE_COEF * len_coef > poi_data['longitude'] ) & ^ (poi_data['longitude'] > temp_comm_longitude - LONGITUDE_COEF * len_coef) &^ (temp_comm_latitude + LONGITUDE_COEF * len_coef > poi_data['latitude'] ) &^ (poi_data['latitude'] > temp_comm_latitude - LONGITUDE_COEF * len_coef) ]^# print(time.time() - start_T)^# return poi_data[ (temp_comm_longitude + LONGITUDE_COEF * len_coef > poi_data['longitude'] ) & (poi_data['longitude'] > temp_comm_longitude - LONGITUDE_COEF * len_coef) &(temp_comm_latitude + LONGITUDE_COEF * len_coef > poi_data['latitude'] ) &(poi_data['latitude'] > temp_comm_latitude - LONGITUDE_COEF * len_coef) ]^ return poi_data_spilt^^^#體育館最近距離^def get_turth_distance(poi_data_all, read_community_data):^ dict_temp = {^ '體育館':["['綜合體育館']"],^ '幼兒園':["['幼兒園']"],^ '小學':["['小學']","['中學', '小學']", "['小學', '中學']"],^ '購物中心':["['購物中心']"],^ '三級甲等醫(yī)院':["['三級甲等醫(yī)院']"],^ '政府機關(guān)':["['區(qū)縣級政府及事業(yè)單位']","['省直轄市級政府及事業(yè)單位']"],^ '火車站':["['火車站']"],^ '景區(qū)':["['國家級景點']", "['省級景點']"],^ '公園':["['公園']"],^ '高等院校':["['高等院校']"],^ '地鐵站': ["['地鐵站']"],^ '飛機場':["['飛機場']"],^ '城市中心':["['城市中心']"],^ '城市廣場':["['城市廣場']"],^ }^ data1=dict_temp.items()^ community_data = read_community_data[['省份', '樓盤名稱', '高德經(jīng)度', '高德緯度']].values^ list_distance_min=[] ^ for key,value in data1:^ poi_data=poi_data_all.copy()^ if key=='政府機關(guān)':^ poi_data = poi_data[((poi_data['sub_category']=="['區(qū)縣級政府及事業(yè)單位']")|(poi_data['sub_category']=="['省直轄市級政府及事業(yè)單位']"))&(poi_data['name'].str.endswith('人民政府')) ]^ elif key=='小學':^ poi_data=poi_data[((poi_data['sub_category']=="['小學']")|(poi_data['sub_category']=="['中學', '小學']")|(poi_data['sub_category']=="['小學', '中學']"))]^ elif key=='景區(qū)':^ poi_data=poi_data[((poi_data['sub_category']=="['國家級景點']")|(poi_data['sub_category']=="['省級景點']"))]^ else:^ print(value)^ poi_data =poi_data[(poi_data['sub_category']==value[0])]^ # 小區(qū)清單列表^ list_distance_min=[] ^ print(value, 'poi數(shù)據(jù)：', len(poi_data), '小區(qū)清單列表：', len(read_community_data))^ ^ # 遍歷小區(qū)清單^ for temp_comm in tqdm(community_data): ^ # 經(jīng)度^ temp_comm_longitude = float(temp_comm[2]) ^ # 緯度^ temp_comm_latitude = float(temp_comm[3]) ^ list_distance_temp=[]^ # 過濾經(jīng)緯度 ^ temp_poi_data = poi_data[['mid_category', 'sub_category', 'longitude', 'latitude', 'name']].values^ ^ for temp_poi in temp_poi_data:^ ^ # 經(jīng)度^ temp_poi_longitude = float(temp_poi[2])^ # 緯度^ temp_poi_latitude = float(temp_poi[3])^ ^ # 距離^ distance = getDistance(temp_comm_longitude, temp_comm_latitude, temp_poi_longitude, temp_poi_latitude)^ list_distance_temp.append(distance)^ distance_min=min(list_distance_temp)if len(list_distance_temp)!=0 else 0^ list_distance_min.append(distance_min)^ read_community_data['%s'%(key)]=list_distance_min^ return read_community_data^^^# 購物中心標簽^def get_turth_number(poi_data_all, read_community_data):^ dict_temp = {^^ '商務(wù)寫字樓':["['商務(wù)寫字樓']"],^ '喪葬設(shè)施':["['喪葬設(shè)施']"],^ '商場':["['商場']"],^ '路口名':["['路口名']"],^ '公交車站相關(guān)':["['公交車站相關(guān)']"],^ '工廠':["['工廠']"],^ }^ data1=dict_temp.items()^ community_data = read_community_data[['省份', '樓盤名稱', '高德經(jīng)度', '高德緯度']].values^ for key,value in data1:^ poi_data=poi_data_all.copy()^ print(value)^ poi_data =poi_data[(poi_data['sub_category']==value[0])]^ # 小區(qū)清單列表^ list_number=[] ^ print(value, 'poi數(shù)據(jù)：', len(poi_data), '小區(qū)清單列表：', len(read_community_data))^ ^ # 遍歷小區(qū)清單^ for temp_comm in tqdm(community_data): ^ # 經(jīng)度^ temp_comm_longitude = float(temp_comm[2]) ^ # 緯度^ temp_comm_latitude = float(temp_comm[3]) ^ list_number_temp=[]^ # 過濾經(jīng)緯度^ poi_data_spilt = select_facter_poi(temp_comm_longitude, temp_comm_latitude, poi_data, 2000)^ temp_poi_data = poi_data_spilt[['mid_category', 'sub_category', 'longitude', 'latitude', 'name']].values^ ^ for temp_poi in temp_poi_data:^ ^ # 經(jīng)度^ temp_poi_longitude = float(temp_poi[2])^ # 緯度^ temp_poi_latitude = float(temp_poi[3])^^ # 距離^ distance = getDistance(temp_comm_longitude, temp_comm_latitude, temp_poi_longitude, temp_poi_latitude)^ if distance<=1000:^ list_number_temp.append(distance)^ number=len(list_number_temp)^ list_number.append(number)^ read_community_data['%s'%(key)]=list_number^ return read_community_data^^^^#處理填充^def community_data(df):^ # df=pd.read_excel(file_path)^ # print(df['樓盤ID'])^ # 填充房屋類型的空值^ housing_type_fill=str(df['房屋類型（小類）'].mode())^ df.fillna({'房屋類型（小類）':housing_type_fill})^ # 填充綠化率和容積率的空值踩寇，根據(jù)房屋類型分組填充^ cols=[col for col in df.columns if col in ['綠化率','容積率'] ]^ gp_col='房屋類型（小類）'^ df_na=df[cols].isna()^ df_mean=df.groupby(gp_col)[cols].mean()^ for col in cols:^ na_series=df_na[col]^ names=list(df.loc[na_series,gp_col]) ^ t=df_mean.loc[names,col]^ t.index=df.loc[na_series,col].index^ df.loc[na_series,col]=t^^ # 填充其他字段的空值^ build_date_fill=int(df['建成年份'].mode())^ green_rate_fill=df['綠化率'].mean()^ plot_rate_fill=df['容積率'].mean()^ floor_area_fill=df['占地面積'].mean()^ house_num_fill=df['總戶數(shù)'].mean()^ manage_type_fill=str(df['管理形式'].mode())^ ^ column_list=df.fillna({'管理形式':manage_type_fill,'占地面積':floor_area_fill,'總戶數(shù)':house_num_fill,'綠化率':green_rate_fill,^ '容積率':plot_rate_fill,'建成年份':build_date_fill,'地上車位':0,'地下車位':0})^ ^# result=column_list[['樓盤ID','樓盤名稱','行政區(qū)','綠化率','容積率','管理形式','地上車位','地下車位','占地面積','建成年份','總戶數(shù)']]^ column_list['車位比']=column_list.apply(lambda x:(x['地上車位']+x['地下車位'])/x['總戶數(shù)'],axis=1)^# print(result['車位比'])^# result.to_csv('result.csv',index=False,encoding='gbk')^# print(result['樓盤ID'])^ return column_list^^^def value2score(data):^ usecols = {^ '地鐵站': (1, '生活配套'),^ '建成年份': (0, '樓盤品質(zhì)'),^ '綠化率': (0, '樓盤品質(zhì)'),^ '容積率': (0, '樓盤品質(zhì)'),^ '體育館': (1, '宜居程度'),^ '幼兒園': (1, '宜居程度'),^ '總戶數(shù)': (0, '樓盤品質(zhì)'),^ '占地面積': (0, '樓盤品質(zhì)'),^ '小學': (1, '生活配套'),^ '購物中心': (1, '生活配套'),^ '三級甲等醫(yī)院': (1, '生活配套'),^ '政府機關(guān)': (0, '區(qū)位狀況'),^ '景區(qū)': (1, '宜居程度'),^ '公園': (1, '宜居程度'),^ '高等院校': (1, '區(qū)位狀況'),^ '城市中心': (1, '區(qū)位狀況'),^ '城市廣場': (0, '區(qū)位狀況'),^ '商務(wù)寫字樓': (0, '區(qū)位狀況'),^ '喪葬設(shè)施': (1, '不利因素'),^ '商場': (0, '生活配套'),^ '路口名': (0, '區(qū)位狀況'),^ '公交車站相關(guān)': (0, '區(qū)位狀況'),^ '工廠': (0, '不利因素'),^ '成交數(shù)量': (0, '活躍程度'),^ '掛牌數(shù)量': (0, '活躍程度'),^ '總樓層': (0, '樓盤品質(zhì)'),^ '抗跌率': (0, '活躍程度'),^ '車位比': (0, '樓盤品質(zhì)')}^ data['建成年份'] = pd.to_numeric(data['建成年份'],errors='coerce')^ data['建成年份'].fillna(data['建成年份'].quantile(0.5),inplace=True)^ for col_name, col in data.iteritems():^ if col_name in usecols.keys():^ cat = usecols[col_name][1]^^ t = usecols[col_name][0]^ data.loc[data[col_name] >= data[col_name].quantile(0.85), col_name + '分數(shù)'] = t*-4 + 5^ data.loc[(data[col_name] < data[col_name].quantile(0.85)) & (^ data[col_name] >= data[col_name].quantile(0.65)), col_name + '分數(shù)'] = t*-2 + 4^ data.loc[(data[col_name] < data[col_name].quantile(0.65)) & (^ data[col_name] >= data[col_name].quantile(0.45)), col_name + '分數(shù)'] = 3^ data.loc[(data[col_name] < data[col_name].quantile(0.45)) & (^ data[col_name] >= data[col_name].quantile(0.25)), col_name + '分數(shù)'] = t*2 + 2^ data.loc[data[col_name] < data[col_name].quantile(0.25), col_name + '分數(shù)'] = t*4 + 1^ if cat in data.columns:^ data[cat] += data[col_name + '分數(shù)']^ else:^ data[cat] = data[col_name + '分數(shù)']^ return data^^^def get_label(data):^ list_developers = pd.read_excel('百強物業(yè)開發(fā)商/百強開發(fā)商.xlsx')^ list_property = pd.read_excel('百強物業(yè)開發(fā)商/百強物業(yè).xlsx')^ developers = list_developers['公司名稱'].values^ propertys = list_property['公司名稱'].values^^ data_temp = data.copy()^^ data_temp.loc[data_temp['容積率'] >= data_temp['容積率'].quantile(0.85), '容積率標簽'] = '容積率高'^ data_temp.loc[data_temp['容積率'] <= data_temp['容積率'].quantile(0.15), '容積率標簽'] = '容積率低'^^ data_temp.loc[data_temp['綠化率'] >= data_temp['綠化率'].quantile(0.85), '綠化率標簽'] = '綠化率高'^ data_temp.loc[data_temp['綠化率'] <= data_temp['綠化率'].quantile(0.15), '綠化率標簽'] = '綠化率低'^^ data_temp.loc[data_temp['總戶數(shù)'] >= data_temp['總戶數(shù)'].quantile(0.85), '小區(qū)規(guī)模標簽'] = '大型社區(qū)'^ data_temp.loc[data_temp['總戶數(shù)'] <= data_temp['總戶數(shù)'].quantile(0.15), '小區(qū)規(guī)模標簽'] = '小型社區(qū)'^^ # data_temp.loc[data_temp['車位比']>=data_temp['車位比'].quantile(0.85),'停車位標簽'] = '停車位充裕'^ # data_temp.loc[data_temp['車位比']<=data_temp['車位比'].quantile(0.15),'停車位標簽'] = '停車位緊缺'^^ data_temp.loc[data_temp['建成年份'] >= 2015, '樓齡標簽'] = '次新房'^ data_temp.loc[(data_temp['建成年份'] >= 2010) & (data['建成年份'] < 2015), '樓齡標簽'] = '6-10年樓齡'^ data_temp.loc[(data_temp['建成年份'] >= 2005) & (data['建成年份'] < 2010), '樓齡標簽'] = '10-15年樓齡'^ data_temp.loc[(data_temp['建成年份'] >= 2000) & (data['建成年份'] < 2005), '樓齡標簽'] = '15-20年樓齡'^ data_temp.loc[data_temp['建成年份'] < 2000, '樓齡標簽'] = '老舊小區(qū)'^^ data_temp.loc[data_temp['開發(fā)商'].isin(developers), '百強開發(fā)商標簽'] = '百強開發(fā)商'^ data_temp.loc[data_temp['物業(yè)公司'].isin(propertys), '百強物業(yè)標簽'] = '百強物業(yè)'^^ data_temp.loc[data_temp['掛牌數(shù)量'] >= data_temp['掛牌數(shù)量'].quantile(0.85), '活躍度標簽'] = '掛盤活躍'^ data_temp.loc[data_temp['掛牌數(shù)量'] <= data_temp['掛牌數(shù)量'].quantile(0.15), '停車位標簽'] = '掛盤不活躍'^^ data_temp.loc[data_temp['購物中心'] < 1000, '購物中心標簽'] = '近購物中心'^ data_temp.loc[data_temp['三級甲等醫(yī)院'] < 1000, '三級甲等醫(yī)院標簽'] = '近三甲醫(yī)院'^ data_temp.loc[data_temp['政府機關(guān)'] < 1000, '政府機關(guān)標簽'] = '近政府機關(guān)'^ data_temp.loc[data_temp['火車站'] < 1000, '火車站標簽'] = '近火車站'^ data_temp.loc[data_temp['景區(qū)'] < 1000, '景區(qū)標簽'] = '近景區(qū)'^ # data_temp.loc[data_temp['公園']<1000, '公園標簽'] = '近公園'^ # data_temp.loc[data_temp['地鐵站']<1000, '地鐵標簽'] = '近地鐵站'^ data_temp.loc[data_temp['飛機場'] < 1000, '機場標簽'] = '距離機場過近'^ data_temp.loc[data_temp['喪葬設(shè)施'] > 5, '喪葬設(shè)施標簽'] = '距離喪葬設(shè)施過近'^ data_temp.loc[data_temp['工廠'] > 3, '工廠標簽'] = '距離工廠過近'^ data_temp.loc[data_temp['商務(wù)寫字樓'] >= data_temp['商務(wù)寫字樓'].quantile(0.85), '商務(wù)區(qū)標簽'] = '商務(wù)區(qū)'^^ show_cols = data_temp.columns[data_temp.columns.str.contains('標簽')]^ # 計算展示標簽^ for idx, row in data_temp.iterrows():^ labels = []^ for col in show_cols:^ if pd.isna(row[col]):^ pass^ else:^ labels.append(row[col])^^ data_temp.loc[idx, '展示標簽'] = ','.join(labels)^^ return data_temp^^^def train(data):^ use_cols = [ '容積率',^ '綠化率',^ '占地面積',^ '建成年份',^ '總樓層',^ '城市中心',^ '抗跌率',^ '購物中心',^ '三級甲等醫(yī)院',^ '高等院校',^ '商務(wù)寫字樓',^ '公交車站相關(guān)',^ '掛牌數(shù)量',^ '路口名',^ '商場',^ '工廠',^ '政府機關(guān)',^ '喪葬設(shè)施',^ '體育館',^ '公園',^ '地鐵站',^ '小學',^ '幼兒園',^ '火車站',^ '小區(qū)均價']^^ train_data = data[use_cols]^ X = train_data[train_data['小區(qū)均價'].notna()].iloc[:, :-1]^ y = train_data[train_data['小區(qū)均價'].notna()].iloc[:, -1]^ model = XGBRegressor(n_jobs=-1)^ param_grid = {'max_depth': np.arange(2, 8, 1),^ 'gamma': np.arange(0.5, 0.8, 0.1),^ 'colsample_bytree': np.arange(0.5, 0.8, 0.1)}^ gs = GridSearchCV(model, param_grid=param_grid, n_jobs=-1, verbose=1, cv=5, scoring='neg_mean_absolute_error')^ gs.fit(X, y)^ print(gs.best_params_)^ print(gs.best_score_)^ X_ = train_data.iloc[:, :-1]^ y_ = gs.predict(X_)^ data.sort_values('綜合評分',inplace=True)^ data['綜合評分'] = np.linspace(5, 10, data.shape[0])^^^if __name__=='__main__':^^ print('城市： ', city)^ start_time1 = time.time()^# # 獲取poi數(shù)據(jù)^ poi_data = get_poi_data(path_poi)^# # 獲取小區(qū)清單數(shù)據(jù)^ read_community_data = get_community_data(path_community)^ read_community_data.dropna(subset=['高德經(jīng)度'],inplace=True)^^# print('讀數(shù)時間：',time.time() - start_time)^^ start_time = time.time()^ read_community_data = get_turth_distance(poi_data, read_community_data)^ read_community_data = get_turth_number(poi_data, read_community_data)^ print('poi數(shù)據(jù)運行時間：',time.time() - start_time)^ ^ start_time = time.time()^ case_value = CaseValue(city)^ result = case_value.result^ read_community_data = pd.merge(read_community_data, result, on='樓盤ID', how='left')^ print('案例數(shù)據(jù)運行時間：',time.time() - start_time)^^ # TODO: 出標簽^ start_time = time.time()^ read_community_data = get_label(read_community_data)^ print('出標簽時間：',time.time() - start_time)^^ # TODO: 處理小區(qū)清單數(shù)據(jù)^ start_time = time.time()^ read_community_data=community_data(read_community_data)^ print('全部運行時間：',time.time() - start_time1)^^ # TODO: 原始值出分^ read_community_data = value2score(read_community_data)^ read_community_data.to_excel('訓練數(shù)據(jù)_%s.xlsx'%city)^^ # TODO: 訓練模型^ read_community_data = train(read_community_data)^ read_community_data.to_excel('小區(qū)畫像_%s.xlsx'%city, index=False)^ ^^^import pandas as pd^import numpy as np^from tqdm import tqdm^import os^^^^class CaseValue():^^ def __init__(self, city_name):^ self.city_name = city_name^ self.df = self.read_and_process_data()^ self.data = self.get_info(self.df)^ self.result = self.match_c()^ self.result = self.fill_nan(self.result)^^ def read_and_process_data(self):^ year, month = 2019, 1^ cols_dict = \^ {'bazhuayu_case.case_type': '案例類型',^ 'bazhuayu_case.case_source': '案例來源',^ 'bazhuayu_case.district': '區(qū)域',^ 'bazhuayu_case.community_sources': '案例源小區(qū)名稱',^ 'bazhuayu_case.flr_total_ind': '總樓層',^ 'bazhuayu_case.checked_time': '看房次數(shù)',^ 'bazhuayu_case.attention_time': '關(guān)注量',^ 'bazhuayu_case.browse_time': '瀏覽次數(shù)',^ 'bazhuayu_case.list_time': '掛牌時間',^ 'bazhuayu_case.transaction_time':'成交時間',^ 'bazhuayu_case.list_totalprice': '掛牌總價（萬元）',^ 'bazhuayu_case.list_unitprice': '掛牌單價（元/㎡）',^ 'bazhuayu_case.transaction_price': '成交總價（萬元）',^ 'bazhuayu_case.transaction_avg_price': '成交單價（元/㎡）',^ 'bazhuayu_case.community_price': '案例源小區(qū)均價（元/㎡）',^ 'bazhuayu_case.transaction_cycle': '成交周期',^ 'bazhuayu_case.price_adjustment_times': '調(diào)價次數(shù)',^ 'bazhuayu_case.longitude': '經(jīng)度',^ 'bazhuayu_case.latitude': '緯度'}^^ # TODO: 讀取數(shù)據(jù)^ txt_name = os.listdir('城市八爪魚數(shù)據(jù)/%s'%self.city_name)[0]^ df = pd.read_csv('城市八爪魚數(shù)據(jù)/%s/%s' %(self.city_name, txt_name), engine='python', encoding='utf-8',^ sep='$', error_bad_lines=False, usecols=cols_dict.keys())^ df.columns = df.columns.map(cols_dict)^^ # 逗號過濾^ df['案例源小區(qū)名稱'] = df['案例源小區(qū)名稱'].astype(str)^ df['案例源小區(qū)名稱'] = df['案例源小區(qū)名稱'].str.replace(',', '')^^ # TODO: 八爪魚小區(qū)名稱映射^ df_community = pd.read_excel('映射表/%s.xlsx'%self.city_name)^ df_community['映射字段'] = df_community['district'] + df_community['community_sources']^^ d = dict(zip(df_community['映射字段'], df_community['man_community_id']))^ df['映射字段'] = df['區(qū)域'] + df['案例源小區(qū)名稱']^ df['匹配ID'] = df['映射字段'].map(d)^ n = df.shape[0]^ n_nan = df[df['匹配ID'].isna()].shape[0]^ print('總數(shù)據(jù)量:%s, 可映射數(shù)據(jù)%s, 不可映射數(shù)據(jù)%s'%(n, n-n_nan, n_nan))^ df = df[df['匹配ID'].notna()]^^ def func(x):^ if pd.isna(x['掛牌時間']):^ if pd.isna(x['成交時間']):^ return np.nan^ else:^ return x['成交時間']^ else:^ return x['掛牌時間']^^ def func2(x):^ if pd.isna(x['成交單價（元/㎡）']) :^ return x['掛牌單價（元/㎡）'] * 0.95^ else:^ return x['成交單價（元/㎡）']^^ df['時間'] = df.apply(func, axis=1)^ df['時間'] = df['時間'].map(lambda x: np.nan if len(str(x)) > 10 else x)^ df['時間']= pd.to_datetime(df['時間'], errors='coerce')^ df = df[df['時間'].notna()]^ df = df[df['時間']>pd.to_datetime('%s-%s-01'%(year, month))]^ df['時間'] = df['時間'].map(lambda x: (x.year, x.month))^^ cols = ['案例源小區(qū)均價（元/㎡）', '掛牌總價（萬元）', '掛牌單價（元/㎡）', '成交總價（萬元）',^ '成交單價（元/㎡）', '總樓層']^ for col in cols:^ df[col] = pd.to_numeric(df[col], errors='coerce')^ df['價格'] = df[['掛牌單價（元/㎡）', '成交單價（元/㎡）']].apply(func2, axis=1)^ df = df[['匹配ID', '案例源小區(qū)均價（元/㎡）', '掛牌總價（萬元）', '掛牌單價（元/㎡）', '成交總價（萬元）',^ '成交單價（元/㎡）', '總樓層','時間','價格']]^ df.drop_duplicates(subset=['匹配ID', '價格'], inplace=True)^ return df^^ def get_info(self, df):^ data = []^ for c_id, group in tqdm(df.groupby(['匹配ID']), desc='掛牌數(shù)據(jù)計算中'):^^ n_chengjiao = group[group['成交單價（元/㎡）'].notna()].shape[0]^ n_guapai = group[group['掛牌單價（元/㎡）'].notna()].shape[0]^^ n_floor = group['總樓層'].median()^ avg_price = group['價格'].median()^ case_price = group['案例源小區(qū)均價（元/㎡）'].median()^ if pd.isna(case_price):^ price = avg_price^ else:^ price = case_price^ a = group.groupby('時間').mean()^ rate = (a['價格'].diff() / a['價格']).mean()^ data.append([c_id, n_chengjiao, n_guapai, n_floor, price, rate])^ data = pd.DataFrame(data, columns=['樓盤ID','成交數(shù)量','掛牌數(shù)量','總樓層','小區(qū)均價','抗跌率'])^ return data^^ def match_c(self):^ # TODO: 讀取小區(qū)清單^ c_list = pd.read_excel('小區(qū)清單/小區(qū)清單_高德地址_%s.xlsx'%self.city_name)^ result = pd.merge(c_list[['樓盤ID']], self.data,^ on='樓盤ID', how='left')^ return result^^ def fill_nan(self, result):^ result['成交數(shù)量'].fillna(0, inplace=True)^ result['掛牌數(shù)量'].fillna(0, inplace=True)^ result['總樓層'].fillna(result['總樓層'].quantile(0.4), inplace=True)^ result['抗跌率'].fillna(result['抗跌率'].quantile(0.4), inplace=True)^ return result^^if '__main__' == '__name__':^ model = CaseValue('深圳市')^ result = model.result^ ^ ^