603 lines
30 KiB
Python
603 lines
30 KiB
Python
import argparse
|
||
import logging
|
||
import os
|
||
import re
|
||
from datetime import datetime
|
||
|
||
import numpy as np
|
||
import pandas as pd
|
||
from goojia_utils import ElasticSearchClient
|
||
from goojia_utils import MAIN_MONGO_CLIENT, MongoDataFrameUpdater
|
||
from goojia_utils import areautils
|
||
from goojia_utils import dateutils
|
||
from sklearn.mixture import GaussianMixture
|
||
from sklearn.neighbors import BallTree
|
||
from sklearn.preprocessing import MinMaxScaler
|
||
from sklearn.preprocessing import StandardScaler
|
||
from sqlalchemy import create_engine
|
||
|
||
os.environ["NLS_LANG"] = "AMERICAN_AMERICA.AL32UTF8"
|
||
engine236 = create_engine("postgresql+psycopg2://postgres:Goojia123456@192.168.0.235:5433/fangjia", echo=False)
|
||
|
||
|
||
|
||
logger = logging.getLogger(__name__)
|
||
seaweed_client = MAIN_MONGO_CLIENT.get_collection("fangjia", "seaweed")
|
||
stat_client = MAIN_MONGO_CLIENT.get_collection("fangjia_stat", "district_sell_stat")
|
||
city_sell_stat_client = MAIN_MONGO_CLIENT.get_collection("fangjia_stat", "city_sell_stat")
|
||
sqmetre_rent_client = MAIN_MONGO_CLIENT.get_collection("fangjia", "sqmetre_rent")
|
||
community_rate_client = MAIN_MONGO_CLIENT.get_collection("fangjia_stat", "community_rate")
|
||
elastic_client = ElasticSearchClient(host="192.168.0.89", username='elastic', password='elasticsearch')
|
||
administrative_mapping_client = MAIN_MONGO_CLIENT.get_collection("business", "administrative_mapping")
|
||
# 定义楼盘评级级别数量
|
||
COMM_RATING_LEVEL_COUNT = 5
|
||
COMM_RATING_LEVEL_NAMES = {1: "S", 2: "A", 3: "B", 4: "C", 5: "D"}
|
||
|
||
|
||
# 定义数据预处理函数
|
||
def extract_years(text, validate=True):
|
||
text = str(text)
|
||
# 匹配四位数且前后无其他数字
|
||
matches = re.findall(r'(?<!\d)\d{4}(?!\d)', text)
|
||
if validate:
|
||
current_year = datetime.now().year + 10
|
||
valid_years = [year for year in matches if 1800 <= int(year) <= current_year]
|
||
if len(valid_years) > 0:
|
||
return int(valid_years[0])
|
||
return None
|
||
|
||
|
||
def fill_missing_by_around(df, field):
|
||
"""
|
||
填充缺失的建筑年份:
|
||
1. 使用球树(BallTree)加速空间查询
|
||
2. 计算1公里范围内的有效建筑年份均值
|
||
3. 若无有效数据则保留NaN
|
||
"""
|
||
# 转换坐标系为弧度(Haversine距离要求)
|
||
coords = np.radians(df[['lat', 'lng']].values)
|
||
|
||
# 构建球树索引(使用Haversine距离公式)
|
||
tree = BallTree(coords, metric='haversine')
|
||
|
||
# 定义地球半径(公里)
|
||
EARTH_RADIUS_KM = 6371
|
||
|
||
# 遍历每一行
|
||
for idx, row in df.iterrows():
|
||
if pd.isna(row[field]):
|
||
# 查询半径(将1公里转换为弧度)
|
||
radius_radians = 1 / EARTH_RADIUS_KM
|
||
|
||
# 执行范围查询(排除自身)
|
||
neighbors = tree.query_radius(
|
||
[coords[idx]],
|
||
r=radius_radians,
|
||
return_distance=False
|
||
)[0]
|
||
neighbors = [n for n in neighbors if n != idx] # 排除当前点
|
||
# 获取有效建筑年份
|
||
valid_years = df.iloc[neighbors][field].dropna()
|
||
# 填充均值(若存在有效值)
|
||
if not valid_years.empty:
|
||
df.at[idx, field] = valid_years.mean()
|
||
return df
|
||
|
||
|
||
def str2number(text):
|
||
try:
|
||
return float(text)
|
||
except ValueError:
|
||
return None
|
||
except TypeError:
|
||
return None
|
||
|
||
|
||
def normalize_to_01(array):
|
||
"""
|
||
将一维数组归一化到 [0, 1] 区间
|
||
参数:
|
||
- array: 输入的一维数组(列表、NumPy数组、Pandas Series)
|
||
返回:
|
||
- 归一化后的NumPy数组(dtype=float64)
|
||
示例:
|
||
>>> normalize_to_01([10, 20, 30]) --> array([0. , 0.5, 1. ])
|
||
"""
|
||
# 转换为NumPy数组并确保是一维
|
||
arr = np.array(array).reshape(-1, 1)
|
||
# 处理全相同值的情况
|
||
if np.all(arr == arr[0]) or np.all(np.isnan(arr)):
|
||
return np.zeros_like(arr).flatten()
|
||
# 使用MinMaxScaler
|
||
x_z = StandardScaler().fit_transform(arr)
|
||
scaler = MinMaxScaler()
|
||
normalized = scaler.fit_transform(x_z)
|
||
return normalized.flatten() # 转回一维
|
||
|
||
|
||
def safe_qcut_rating(values, n_bins, ascending=False):
|
||
"""
|
||
安全的分位数评级函数,处理各种边界情况
|
||
|
||
Args:
|
||
values: 待分级的数值序列
|
||
n_bins: 期望的分箱数量
|
||
ascending: True表示数值越大评级越高,False相反
|
||
|
||
Returns:
|
||
评级序列(从1开始)
|
||
"""
|
||
if len(values) == 0:
|
||
return pd.Series(dtype=int)
|
||
|
||
unique_count = values.nunique()
|
||
|
||
# 如果唯一值数量小于等于分箱数,直接使用排名
|
||
if unique_count <= n_bins:
|
||
if ascending:
|
||
ratings = values.rank(method='dense', ascending=True)
|
||
else:
|
||
ratings = values.rank(method='dense', ascending=False)
|
||
return ratings.astype(int)
|
||
|
||
# 尝试使用qcut
|
||
try:
|
||
# 使用labels=False返回数字索引,然后+1使评级从1开始
|
||
if ascending:
|
||
# 升序:最小值得到最小评级
|
||
ratings = pd.qcut(values, q=n_bins, labels=False, duplicates='drop') + 1
|
||
else:
|
||
# 降序:最大值得到最小评级(等级1)
|
||
ratings = pd.qcut(values, q=n_bins, labels=False, duplicates='drop')
|
||
ratings = n_bins - ratings # 反转评级顺序
|
||
return ratings.astype(int)
|
||
|
||
except ValueError as e:
|
||
logging.error(f"qcut失败: {e},使用排名方法替代")
|
||
# 回退到排名方法
|
||
if ascending:
|
||
ratings = values.rank(method='dense', ascending=True)
|
||
else:
|
||
ratings = values.rank(method='dense', ascending=False)
|
||
return ratings.astype(int)
|
||
|
||
|
||
def exponential_decay_weights(distances):
|
||
"""
|
||
根据指数衰减方法计算权重。
|
||
|
||
参数:
|
||
distances (array-like): 数据点与目标点之间的距离数组。
|
||
decay_coefficient (float): 衰减系数,控制权重衰减速度。
|
||
|
||
返回:
|
||
numpy.ndarray: 归一化后的权重数组。
|
||
"""
|
||
distances = np.array(distances)
|
||
weights = 1 - np.log(distances + 1) / np.log(max(distances) + 1)
|
||
return weights
|
||
|
||
|
||
def gmm_train(total_rate, data, source, target):
|
||
data = data.copy()
|
||
source_values = data[source].dropna()
|
||
unique_values = source_values.nunique()
|
||
actual_components = min(total_rate, unique_values)
|
||
if unique_values <= 2:
|
||
data[target] = safe_qcut_rating(source_values, actual_components, ascending=False)
|
||
return data
|
||
|
||
# 高斯混合模型
|
||
gmm = GaussianMixture(
|
||
n_components=actual_components,
|
||
covariance_type='full',
|
||
max_iter=200,
|
||
n_init=10,
|
||
reg_covar=1e-6,
|
||
random_state=42
|
||
).fit(data[[source]])
|
||
data = data.copy()
|
||
data.loc[:, 'cluster'] = gmm.predict(data[[source]])
|
||
data.loc[:, 'price_mean_cluster'] = data.groupby('cluster')[source].transform('mean')
|
||
data.loc[:, target] = data['price_mean_cluster'].rank(method='dense', ascending=False).astype(int)
|
||
data = data.drop(columns=['cluster', 'price_mean_cluster'], axis=1)
|
||
return data.copy()
|
||
|
||
|
||
# ## 定义小区加载函数
|
||
|
||
def load_comms(city):
|
||
query = {"cat": "district", "status": 0, "city": city}
|
||
fields = {"city": 1, "region": 1, "name": 1, "address": 1, "lng": 1, "lat": 1, "estate_type2": 1, "create_year": 1,
|
||
"cubage_percent": 1, "virescence_percent": 1, "estate_charge": 1, "business_quarter": 1}
|
||
seaweed = pd.DataFrame(list(seaweed_client.find(query, fields)))
|
||
seaweed = seaweed.dropna(subset=['lng', 'lat'])
|
||
seaweed['_id'] = seaweed['_id'].apply(lambda x: str(x))
|
||
seaweed['lng'] = seaweed['lng'] / 10000000000
|
||
seaweed['lat'] = seaweed['lat'] / 10000000000
|
||
seaweed = seaweed.rename(columns={"create_year": "build_year", "estate_type2": "estate_type", "business_quarter": "block", "_id": "comm_id"})
|
||
year = datetime.now().year
|
||
seaweed['build_year'] = seaweed['build_year'].apply(lambda x: extract_years(x))
|
||
seaweed['org_build_year'] = seaweed['build_year']
|
||
seaweed['age'] = seaweed['build_year'].apply(lambda x: (year - x) if pd.notna(x) else None)
|
||
seaweed = seaweed.reset_index()
|
||
for field in ['age', "cubage_percent", "virescence_percent", "estate_charge"]:
|
||
seaweed[field] = seaweed[field].apply(lambda x: str2number(x))
|
||
seaweed[f"org_{field}"] = seaweed[field]
|
||
seaweed = fill_missing_by_around(seaweed, field)
|
||
seaweed[field] = seaweed[field].fillna(seaweed[field].mean())
|
||
if field == 'age':
|
||
seaweed['age'] = seaweed['age'].apply(lambda x: int(x))
|
||
else:
|
||
seaweed[field] = seaweed[field].apply(lambda x: round(x, 2))
|
||
return seaweed
|
||
|
||
|
||
# ## 加载小区价格数据
|
||
|
||
def load_comm_stat(city, calc_year_month):
|
||
one_year_ago = dateutils.add_months(dateutils.parse_date(str(calc_year_month),"%Y%m"), years=-1)
|
||
half_on_year = dateutils.add_months(dateutils.parse_date(str(calc_year_month),"%Y%m"), months=-6)
|
||
one_year_ago_year_month = int(dateutils.format_date(one_year_ago, "%Y%m"))
|
||
half_on_year_year_month = int(dateutils.format_date(half_on_year, "%Y%m"))
|
||
|
||
stat = pd.DataFrame(list(stat_client.find({"date": calc_year_month, "city": city}, {"city": 1, "region": 1, "district_name": 1, "avg_price": 1, "_id": 0})))
|
||
stat = stat.rename(columns={"district_name": "name"})
|
||
stat['org_avg_price'] = stat["avg_price"]
|
||
one_year_before_stat = pd.DataFrame(list(stat_client.find({"date": one_year_ago_year_month, "city": city}, {"city": 1, "region": 1, "district_name": 1, "avg_price": 1, "_id": 0})))
|
||
one_year_before_stat = one_year_before_stat.rename(columns={"district_name": "name", "avg_price": "one_year_before_avg_price"})
|
||
stat = stat.merge(one_year_before_stat, on=['city', 'region', 'name'], how="left")
|
||
stat['yoy'] = (stat['avg_price'] - stat['one_year_before_avg_price']) * 100 / stat['one_year_before_avg_price']
|
||
stat['org_yoy'] = stat["yoy"]
|
||
stat['yoy'] = stat['yoy'].fillna(stat['yoy'].mean())
|
||
stat['resistance_coef'] = normalize_to_01(stat['yoy']) * 95
|
||
stat['resistance_coef'] = stat['resistance_coef'] + 2
|
||
stat['yoy'] = stat['yoy'].apply(lambda x: round(x, 2))
|
||
del stat['one_year_before_avg_price']
|
||
half_on_year_stat = pd.DataFrame(list(stat_client.find({"date": half_on_year_year_month, "city": city}, {"city": 1, "region": 1, "district_name": 1, "avg_price": 1, "_id": 0})))
|
||
half_on_year_stat = half_on_year_stat.rename(columns={"district_name": "name", "avg_price": "half_on_year_avg_price"})
|
||
if half_on_year_stat.empty:
|
||
return pd.DataFrame()
|
||
stat = stat.merge(half_on_year_stat, on=['city', 'region', 'name'], how="left")
|
||
stat['half_on_year'] = (stat['avg_price'] - stat['half_on_year_avg_price']) * 100 / stat['half_on_year_avg_price']
|
||
stat['half_on_year'] = stat['yoy'].fillna(0)
|
||
stat['half_on_year'] = stat['yoy'].apply(lambda x: round(x, 2))
|
||
del stat['half_on_year_avg_price']
|
||
sqmetre_rent = pd.DataFrame(list(sqmetre_rent_client.find({"s_date": calc_year_month, "cat": "district", "city": city},{"city": 1, "region": 1, "district_name": 1, "avg_price": 1, "_id": 0})))
|
||
sqmetre_rent = sqmetre_rent.rename(columns={"district_name": "name", "avg_price": "sqmetre_rent"})
|
||
stat = stat.merge(sqmetre_rent, on=['city', 'region', 'name'], how='left')
|
||
stat['roi'] = round(stat['sqmetre_rent'] * 12 / stat['avg_price'] * 100, 2)
|
||
stat['rent_to_price_ratio'] =stat['sqmetre_rent'] /stat['avg_price']
|
||
stat["org_sqmetre_rent"] = stat['sqmetre_rent']
|
||
stat["org_rent_to_price_ratio"] = stat['rent_to_price_ratio']
|
||
stat['rent_to_price_ratio'] = stat['rent_to_price_ratio'].fillna(stat['rent_to_price_ratio'].mean())
|
||
return stat
|
||
|
||
|
||
# ## 加载小区配套数据
|
||
|
||
def load_comms_summary(city):
|
||
all_community_summary = []
|
||
for doc in elastic_client.scan_by_sql(f"select * from community_summary where city = '{city}'"):
|
||
source = doc['_source']
|
||
community_summary = {"comm_id": source["id"]}
|
||
for key in source.keys():
|
||
if (key.endswith("num") or key.endswith("_distance")) and key not in ["half_on_year_sell_num"]:
|
||
community_summary[key] = source[key]
|
||
community_summary[f"org_{key}"] = source[key]
|
||
all_community_summary.append(community_summary)
|
||
all_community_summary = pd.DataFrame(all_community_summary)
|
||
return all_community_summary
|
||
|
||
|
||
# ## 定义分类数据和连续性数据打分规则
|
||
|
||
def category_score(data, field, score_map):
|
||
data[f"{field}_score"] = data[field].apply(lambda x: score_map[x])
|
||
return data
|
||
|
||
|
||
def numbic_score(data, field):
|
||
data[f"{field}_score"] = normalize_to_01(data[field])
|
||
|
||
|
||
estate_type_source_map = {"普通住宅": 0.5, "商住": 0, "别墅": 1}
|
||
|
||
# ## 定义楼盘评级因子权重
|
||
|
||
factor_weights = {
|
||
'estate_type_score': 0.02,
|
||
'age_score': 0.02,
|
||
'cubage_percent_score': 0.02,
|
||
'virescence_percent_score': 0.02,
|
||
'estate_charge_score': 0.02,
|
||
'avg_price_score': 0.1,
|
||
'sqmetre_rent_score': 0.05,
|
||
'yoy_score': 0.15,
|
||
'nearest_business_district_distance_score': 0.03,
|
||
'nearest_subway_station_distance_score': 0.07,
|
||
'nearest_bus_station_distance_score': 0.05,
|
||
'rent_to_price_ratio_score': 0.1,
|
||
'volume_price_ratio_score': 0.1,
|
||
'supermarket_num_score': 0.02,
|
||
'park_num_score': 0.02,
|
||
'catering_num_score': 0.02,
|
||
'mall_num_score': 0.04,
|
||
'kindergarten_num_score': 0.01,
|
||
'key_primary_school_num_score': 0.06,
|
||
'ordinary_primary_school_num_score': 0.04,
|
||
'key_middle_school_num_score': 0.02,
|
||
'ordinary_middle_school_num_score': 0.02,
|
||
'top_three_hospital_num_score': 0.03,
|
||
'non_3a_hospital_num_score': 0.02
|
||
}
|
||
|
||
|
||
# ## 楼盘评级打分
|
||
def comms_rating(data):
|
||
number_fields = ["age", "cubage_percent", "virescence_percent", "estate_charge", "avg_price", "sqmetre_rent",
|
||
"yoy", "supermarket_num", "park_num", "catering_num", "mall_num", "kindergarten_num",
|
||
"key_primary_school_num", "ordinary_primary_school_num", "key_middle_school_num",
|
||
"ordinary_middle_school_num", "top_three_hospital_num", "non_3a_hospital_num",
|
||
"nearest_business_district_distance", "nearest_subway_station_distance",
|
||
"nearest_bus_station_distance", "rent_to_price_ratio", "volume_price_ratio"]
|
||
for number_field in number_fields:
|
||
threshold_95 = data[number_field].quantile(0.99)
|
||
threshold_5 = data[number_field].quantile(0.01)
|
||
# 2. 修正:将低于95分位的数据替换为 95分位值
|
||
data[number_field] = data[number_field].apply(lambda x: threshold_95 if x >= threshold_95 else x)
|
||
data[number_field] = data[number_field].apply(lambda x: threshold_5 if x <= threshold_5 else x)
|
||
data[f'{number_field}_score'] = normalize_to_01(data[number_field])
|
||
data['age'] = data['age'].apply(lambda x: int(x) if pd.notna(x) else x)
|
||
data['age'] = data['age'].astype(int)
|
||
for category_field in ['estate_type']:
|
||
category_score(data, category_field, estate_type_source_map)
|
||
data['total_score'] = 0
|
||
factors = ["estate_type", "age", "cubage_percent", "virescence_percent", "estate_charge", "avg_price",
|
||
"sqmetre_rent", "yoy", "nearest_business_district_distance", "nearest_subway_station_distance",
|
||
"nearest_bus_station_distance", "rent_to_price_ratio", "supermarket_num", "park_num", "catering_num",
|
||
"mall_num", "kindergarten_num", "key_primary_school_num", "ordinary_primary_school_num",
|
||
"key_middle_school_num", "ordinary_middle_school_num", "top_three_hospital_num", "non_3a_hospital_num"]
|
||
for factor in factors:
|
||
data['total_score'] = data[f"total_score"] + data[f"{factor}_score"] * factor_weights[f"{factor}_score"]
|
||
data['total_score'] = normalize_to_01(data['total_score'])
|
||
# 小区品质打分
|
||
community_quality_factors = ["estate_type", "age", "cubage_percent", "virescence_percent", "estate_charge"]
|
||
data['community_quality_score'] = 0
|
||
for factor in community_quality_factors:
|
||
data['community_quality_score'] = data[f"community_quality_score"] + data[f"{factor}_score"] * factor_weights[
|
||
f"{factor}_score"]
|
||
data['community_quality_score'] = normalize_to_01(data['community_quality_score'])
|
||
# 小区价格打分
|
||
community_price_factors = ["avg_price", "sqmetre_rent", "yoy"]
|
||
data['community_price_score'] = 0
|
||
for factor in community_price_factors:
|
||
data['community_price_score'] = data[f"community_price_score"] + data[f"{factor}_score"] * factor_weights[
|
||
f"{factor}_score"]
|
||
data['community_price_score'] = normalize_to_01(data['community_price_score'])
|
||
# 区位打分
|
||
location_advantage_factors = ["nearest_business_district_distance", "nearest_subway_station_distance",
|
||
"nearest_bus_station_distance"]
|
||
data['location_advantage_score'] = 0
|
||
for factor in location_advantage_factors:
|
||
data['location_advantage_score'] = data[f"location_advantage_score"] + data[f"{factor}_score"] * factor_weights[
|
||
f"{factor}_score"]
|
||
data['location_advantage_score'] = normalize_to_01(data['location_advantage_score'])
|
||
# 流动性打分
|
||
flowability_factors = ["rent_to_price_ratio", "volume_price_ratio"]
|
||
data['flowability_score'] = 0
|
||
for factor in flowability_factors:
|
||
data['flowability_score'] = data[f"flowability_score"] + data[f"{factor}_score"] * factor_weights[
|
||
f"{factor}_score"]
|
||
data['flowability_score'] = normalize_to_01(data['flowability_score'])
|
||
# 流动性生活配套打分
|
||
living_facilities_factors = ["supermarket_num", "park_num", "catering_num", "mall_num"]
|
||
data['living_facilities_score'] = 0
|
||
for factor in living_facilities_factors:
|
||
data['living_facilities_score'] = data[f"living_facilities_score"] + data[f"{factor}_score"] * factor_weights[
|
||
f"{factor}_score"]
|
||
data['living_facilities_score'] = normalize_to_01(data['living_facilities_score'])
|
||
# 教育资源打分
|
||
education_factors = ["kindergarten_num", "key_primary_school_num", "ordinary_primary_school_num",
|
||
"key_middle_school_num", "ordinary_middle_school_num"]
|
||
data['education_score'] = 0
|
||
for factor in education_factors:
|
||
data['education_score'] = data[f"education_score"] + data[f"{factor}_score"] * factor_weights[f"{factor}_score"]
|
||
data['education_score'] = normalize_to_01(data['education_score'])
|
||
# 医疗资源打分
|
||
hospital_factors = ["top_three_hospital_num", "non_3a_hospital_num"]
|
||
data['hospital_score'] = 0
|
||
for factor in hospital_factors:
|
||
data['hospital_score'] = data[f"hospital_score"] + data[f"{factor}_score"] * factor_weights[f"{factor}_score"]
|
||
data['hospital_score'] = normalize_to_01(data['hospital_score'])
|
||
data = data.dropna(subset=['total_score'])
|
||
for score_factor in ['total_score', 'community_quality_score', 'community_price_score', 'location_advantage_score',
|
||
'flowability_score', 'living_facilities_score', 'education_score', 'hospital_score']:
|
||
factor = score_factor.replace("_score", "")
|
||
if score_factor == 'total_score':
|
||
data = gmm_train(COMM_RATING_LEVEL_COUNT, data, score_factor, f'rating')
|
||
data[f'rating'] = data[f'rating'].map(COMM_RATING_LEVEL_NAMES)
|
||
else:
|
||
data = gmm_train(COMM_RATING_LEVEL_COUNT, data, score_factor, f'{factor}_rating')
|
||
data[f'{factor}_rating'] = data[f'{factor}_rating'].map(COMM_RATING_LEVEL_NAMES)
|
||
|
||
buffer = []
|
||
data_null = data[data['region'].isna()]
|
||
data_not_null = data[data['region'].notna()]
|
||
for item in data_not_null['region'].unique():
|
||
group = data_not_null[data_not_null['region'] == item]
|
||
group = gmm_train(COMM_RATING_LEVEL_COUNT, group, 'total_score', f'region_rating')
|
||
group[f'region_rating'] = group[f'region_rating'].map(COMM_RATING_LEVEL_NAMES)
|
||
buffer.append(group)
|
||
buffer.append(data_null)
|
||
data = pd.concat(buffer)
|
||
if "block" in data.columns:
|
||
buffer = []
|
||
data_null = data[data['block'].isna()].copy()
|
||
data_not_null = data[data['block'].notna()].copy()
|
||
buffer.append(data_null)
|
||
if len(data_not_null) > 0:
|
||
data_not_null['region_block'] = data_not_null['region'] + "_" + data_not_null['block']
|
||
for item in data_not_null['region_block'].unique():
|
||
group = data_not_null[(data_not_null['region'] == str(item).split("_")[0]) & (data_not_null['block'] == str(item).split("_")[1])]
|
||
group = gmm_train(COMM_RATING_LEVEL_COUNT, group, 'total_score', 'block_rating')
|
||
group['block_rating'] = group['block_rating'].map(COMM_RATING_LEVEL_NAMES)
|
||
buffer.append(group)
|
||
data = pd.concat(buffer)
|
||
else:
|
||
data['block_rating'] = None
|
||
data['block'] = None
|
||
for score_factor in ['total_score', 'community_quality_score', 'community_price_score', 'location_advantage_score',
|
||
'flowability_score', 'living_facilities_score', 'education_score', 'hospital_score']:
|
||
data[f'{score_factor}'] = data[f'{score_factor}'].apply(lambda x: round(x * 100, 6))
|
||
return data
|
||
|
||
city_code_map = {}
|
||
for doc in administrative_mapping_client.find({"type":"城市"}, {"city_short" :1, "mzb_city_code":1}):
|
||
city_code_map[doc['city_short']] = doc['mzb_city_code']
|
||
|
||
def load_house_stat(city, calc_year_month):
|
||
end_year_month = int(dateutils.format_date(dateutils.add_months(dateutils.parse_date(str(calc_year_month), "%Y%m"), months=1), "%Y%m"))
|
||
half_on_year = dateutils.add_months(dateutils.parse_date(str(calc_year_month), "%Y%m"), months=-6)
|
||
half_on_year_year_month = int(dateutils.format_date(half_on_year, "%Y%m"))
|
||
sql = f"""
|
||
select gujia_comm_id as comm_id, count(*) as half_on_year_sell_num from base.bigdata_forsale where urbn_id = '{city_code_map[city]}'
|
||
and data_crt_tm >= to_date('{half_on_year_year_month}', 'YYYYMM') and data_crt_tm < to_date('{end_year_month}', 'YYYYMM')
|
||
group by gujia_comm_id ;
|
||
"""
|
||
house_stat = pd.read_sql(sql, con=engine236)
|
||
house_stat['org_half_on_year_sell_num'] = house_stat['half_on_year_sell_num']
|
||
return pd.read_sql(sql, con=engine236)
|
||
# ## 按照城市加载楼盘评级数据宽表
|
||
|
||
def load_data_by_city(city, calc_year_month):
|
||
comms = load_comms(city=city)
|
||
stat = load_comm_stat(city, calc_year_month)
|
||
comms_summary = load_comms_summary(city=city)
|
||
house_stat = load_house_stat(city, calc_year_month)
|
||
comms_summary = comms_summary.merge(house_stat, on='comm_id', how='left')
|
||
data = comms.merge(stat, how="left", on=['city', 'region', 'name'])
|
||
data = data.merge(comms_summary, how="left", on=['comm_id'])
|
||
num_fields = ["supermarket_num", "park_num", "catering_num", "mall_num", "kindergarten_num",
|
||
"key_primary_school_num", "ordinary_primary_school_num", "key_middle_school_num",
|
||
"ordinary_middle_school_num", "top_three_hospital_num", "non_3a_hospital_num",
|
||
"avg_price"]
|
||
for num_field in num_fields:
|
||
data[num_field] = data[num_field].fillna(0)
|
||
distance_fields = ["nearest_business_district_distance", "nearest_subway_station_distance",
|
||
"nearest_bus_station_distance"]
|
||
for distance_field in distance_fields:
|
||
if distance_field not in data:
|
||
data[distance_field] = 0
|
||
else:
|
||
data[distance_field] = data[distance_field].fillna(data[distance_field].max())
|
||
data[distance_field] = exponential_decay_weights(data[distance_field])
|
||
data[distance_field] = data[distance_field].apply(lambda x: round(x, 6))
|
||
data['volume_price_ratio'] = data['avg_price'] / data['half_on_year'].replace(0, 1e-6)
|
||
data['volume_price_ratio'] = data['volume_price_ratio'].fillna(data['volume_price_ratio'].mean())
|
||
return data
|
||
|
||
import multiprocessing
|
||
|
||
def process_city(args):
|
||
city = args['city']
|
||
calc_year_month = args['calc_year_month']
|
||
try:
|
||
logger.info(f"开始计算楼盘评级:{city} 年月:{calc_year_month}")
|
||
data = load_data_by_city(city, calc_year_month)
|
||
data = comms_rating(data)
|
||
db_fields = ["comm_id", "city", "region", "name", 'block', 'block_rating', "address", "sqmetre_rent", "yoy",
|
||
"rent_to_price_ratio", "roi", "avg_price", "total_score", "community_quality_score",
|
||
"community_price_score", "location_advantage_score", "flowability_score", "living_facilities_score",
|
||
"education_score", "hospital_score", "rating", "community_quality_rating", "community_price_rating",
|
||
"location_advantage_rating", "flowability_rating", "living_facilities_rating", "education_rating",
|
||
"hospital_rating", "supermarket_num", "park_num", "catering_num", "mall_num", "kindergarten_num",
|
||
"key_primary_school_num", "ordinary_primary_school_num", "key_middle_school_num",
|
||
"ordinary_middle_school_num", "top_three_hospital_num", "non_3a_hospital_num", "build_year", "age",
|
||
"cubage_percent", "virescence_percent", "estate_charge", 'estate_type', 'resistance_coef', "region_rating"]
|
||
# 恢复经过转换,填充的数据
|
||
for field in data.columns:
|
||
if field.startswith("org_"):
|
||
data[f"{field.replace('org_', '')}"] = data[field]
|
||
del data[field]
|
||
df_simple = data[db_fields].copy()
|
||
if "block" not in df_simple.columns:
|
||
df_simple['block'] = None
|
||
if "resistance_coef" not in df_simple.columns:
|
||
df_simple['resistance_coef'] = None
|
||
for factor_score_field in ['total_score', 'community_quality_score', 'community_price_score',
|
||
'location_advantage_score', 'flowability_score', 'living_facilities_score',
|
||
'education_score', 'hospital_score']:
|
||
key = "" if factor_score_field == "total_score" else f"_{factor_score_field.replace('_score', '')}"
|
||
|
||
df_simple[f'city{key}_rank'] = df_simple[factor_score_field].rank(method='min', ascending=False).astype(int)
|
||
df_simple[f'city{key}_total_rank'] = df_simple[f'city{key}_rank'].max()
|
||
|
||
df_simple[f'region{key}_rank'] = df_simple.groupby(['city', 'region'])[factor_score_field].rank(method='min', ascending=False).astype(int)
|
||
df_simple[f'region{key}_total_rank'] = df_simple[f'region{key}_rank'].max()
|
||
|
||
df_simple[f'block{key}_rank'] = None
|
||
df_simple[f'block{key}_total_rank'] = None
|
||
if df_simple['block'].notna().any():
|
||
buffer = []
|
||
data_null = df_simple[df_simple['block'].isna()].copy()
|
||
data_not_null = df_simple[df_simple['block'].notna()].copy()
|
||
buffer.append(data_null)
|
||
if len(data_not_null) > 0:
|
||
data_not_null['region_block'] = data_not_null['region'] + "_" + data_not_null['block']
|
||
for item in data_not_null['region_block'].unique():
|
||
group = data_not_null[(data_not_null['region'] == str(item).split("_")[0])&(data_not_null['block'] == str(item).split("_")[1])].copy()
|
||
group[f'block{key}_rank'] = group[factor_score_field].rank(method='min', ascending=False).astype(int)
|
||
group[f'block{key}_total_rank'] = group[f'block{key}_rank'].max()
|
||
buffer.append(group)
|
||
df_simple = pd.concat(buffer)
|
||
if "region_block" in df_simple.columns:
|
||
del df_simple['region_block']
|
||
for score_factor in ['total_score', 'community_quality_score', 'community_price_score','location_advantage_score', 'flowability_score', 'living_facilities_score','education_score', 'hospital_score']:
|
||
df_simple[score_factor] = df_simple[score_factor].apply(lambda x: round(x, 2))
|
||
df_simple['rent_to_price_ratio'] = df_simple['rent_to_price_ratio'].apply(lambda x: round(x, 4))
|
||
df_simple['yoy'] = df_simple['yoy'].apply(lambda x: round(x, 2))
|
||
updater = MongoDataFrameUpdater(community_rate_client)
|
||
dtype = {"avg_price": int,"build_year": int, 'resistance_coef': int}
|
||
for field in db_fields:
|
||
if "_rank" in field or "_num" in field:
|
||
dtype[field] = int
|
||
for field in df_simple.columns:
|
||
if "_rank" in field or "_num" in field:
|
||
dtype[field] = int
|
||
df_simple['date'] = calc_year_month
|
||
updater.bulk_insert_or_update_by_id(df_simple, id_fields=['comm_id', 'date'], dtype=dtype)
|
||
logger.info(f"楼盘评级计算结束:{city}")
|
||
except Exception as e:
|
||
import traceback
|
||
traceback.print_exc()
|
||
logging.error(f"{city}楼盘评级计算失败;{e}")
|
||
|
||
|
||
if __name__ == '__main__':
|
||
parser = argparse.ArgumentParser()
|
||
parser.add_argument('--city', type=str, default='all')
|
||
parser.add_argument('--month', type=str, default='')
|
||
args = parser.parse_args()
|
||
if args.city == 'all':
|
||
cities = areautils.get_all_cities()
|
||
else:
|
||
cities = [city.strip() for city in args.city.split(',') if city.strip()]
|
||
if args.month == '':
|
||
calc_year_months = [int(dateutils.format_date(dateutils.add_months(datetime.now(), months=-1), "%Y%m"))]
|
||
else:
|
||
calc_year_months = [int(calc_year_month.strip()) for calc_year_month in args.month.split(',') if calc_year_month.strip()]
|
||
# for city in cities:
|
||
# args_list = [{"city": city, "calc_year_month": calc_year_month} for calc_year_month in calc_year_months]
|
||
# for args in args_list:
|
||
# process_city(args)
|
||
with multiprocessing.Pool(processes=min(8, len(calc_year_months))) as pool:
|
||
# for calc_year_month in calc_year_months:
|
||
# args_list = [{"calc_year_month": calc_year_month, "city": city} for city in cities]
|
||
# pool.map(process_city, args_list)
|
||
# for calc_year_month in calc_year_months:
|
||
# args_list = [{"calc_year_month": calc_year_month, "city": city} for city in cities]
|
||
# pool.map(process_city, args_list)
|
||
for city in cities:
|
||
args_list = [{"city": city, "calc_year_month": calc_year_month} for calc_year_month in calc_year_months]
|
||
pool.map(process_city, args_list) |