427 lines
18 KiB
Python
427 lines
18 KiB
Python
import time
|
||
|
||
import numpy as np
|
||
import pandas as pd
|
||
from datetime import datetime
|
||
from pymongo import UpdateOne
|
||
from goojia_utils import mongo
|
||
|
||
|
||
def get_base_year_price(price_df):
|
||
"""获取能被5整除年份价格的全年均价"""
|
||
# 从 'date' 列中提取出年份部分
|
||
price_df['year'] = price_df['date'] // 100
|
||
|
||
# 筛选出能被5整除的年份
|
||
divisible_years = price_df['year'] % 5 == 0
|
||
|
||
# 根据筛选出的年份,提取出对应的数据行
|
||
base_year_df = price_df[divisible_years]
|
||
# result = base_year_df.groupby(['region', 'year'])['avg_price'].mean().reset_index()
|
||
result = pd.pivot_table(base_year_df, values='avg_price', index=['year'], columns=['district_name'], aggfunc='mean')
|
||
return result
|
||
|
||
|
||
def get_fixed_base_price(price_data, base_year_df):
|
||
"""
|
||
城市区域下小区定基价格指数
|
||
:param price_data: 均价数据
|
||
:param base_year_df: 能被5整除年份全年均价数据
|
||
"""
|
||
|
||
col_list = ['date', 'base_year', 'district_name', 'base_price_exp']
|
||
base_result_pf = pd.DataFrame()
|
||
# for index, row in base_year_df.iteritems(): # pandas 1.x
|
||
for index, row in base_year_df.items():
|
||
row_dict = list(zip(list(row.values), list(row.index)))
|
||
|
||
seaweed_name = index
|
||
for _k, _v in row_dict:
|
||
start_y = _v
|
||
end_y = start_y + 5
|
||
seaweed_y_df = price_data[(price_data['district_name'] == seaweed_name) & (start_y < price_data['year']) & (
|
||
price_data['year'] <= end_y)]
|
||
seaweed_y_df = seaweed_y_df.assign(base_price_exp=seaweed_y_df['avg_price'] / _k)
|
||
seaweed_y_df = seaweed_y_df.assign(base_year=_v)
|
||
base_result_pf = pd.concat([base_result_pf, seaweed_y_df[col_list]], axis=0)
|
||
return base_result_pf
|
||
|
||
|
||
def custom_pct_change(x, periods=1):
|
||
if periods > 1:
|
||
pct = x.pct_change(periods=periods)
|
||
else:
|
||
pct = x.pct_change()
|
||
mask = x.isna() & x.shift().isna()
|
||
return pct.mask(mask, np.nan)
|
||
|
||
|
||
def control_price(chain, year2year, r_m_base_exp):
|
||
"""
|
||
价格涨幅控制
|
||
环比:[97, 103]
|
||
同比:[80, 130]
|
||
定基:[50, 200]
|
||
"""
|
||
chain_range = (97, 103, 102)
|
||
year2year_range = (80, 130, 129)
|
||
r_m_base_exp_range = (50, 200, 199)
|
||
|
||
if chain:
|
||
chain_decimal = round((chain - int(chain)), 1)
|
||
if chain < chain_range[0]:
|
||
chain = chain_range[0] + chain_decimal
|
||
elif chain > chain_range[1]:
|
||
chain = chain_range[2] + chain_decimal
|
||
|
||
if year2year:
|
||
year2year_decimal = round((year2year - int(year2year)), 1)
|
||
if year2year < year2year_range[0]:
|
||
year2year = year2year_range[0] + year2year_decimal
|
||
elif year2year > year2year_range[1]:
|
||
year2year = year2year_range[2] + year2year_decimal
|
||
|
||
if r_m_base_exp:
|
||
base_exp_decimal = round((r_m_base_exp - int(r_m_base_exp)), 1)
|
||
if r_m_base_exp < r_m_base_exp_range[0]:
|
||
r_m_base_exp = r_m_base_exp_range[0] + base_exp_decimal
|
||
elif r_m_base_exp > r_m_base_exp_range[1]:
|
||
r_m_base_exp = r_m_base_exp_range[2] + base_exp_decimal
|
||
return chain, year2year, r_m_base_exp
|
||
|
||
|
||
def control_price(chain, year2year, r_m_base_exp):
|
||
# 假设这个函数已经定义,如果需要也可以向量化
|
||
return chain, year2year, r_m_base_exp
|
||
|
||
|
||
class SeaweedPrice:
|
||
def __init__(self):
|
||
self.region_df = None
|
||
self.client = mongo.MAIN_MONGO_CLIENT
|
||
# self.target_client = mongo.DEV_MONGO_CLIENT
|
||
self.target_client = mongo.MAIN_MONGO_CLIENT
|
||
self.zone_db = "fangjia"
|
||
self.price_db = "fangjia_stat"
|
||
self.seaweed_price_tb = "district_sell_stat"
|
||
self.seaweed_tb = "seaweed"
|
||
self.region_tb = "region_block"
|
||
|
||
self.target_seaweed_price_tb = "property_stats_index"
|
||
self.region_df = self.load_region()
|
||
|
||
def get_region(self):
|
||
return self.region_df
|
||
|
||
def get_exists_region(self):
|
||
iter_d = self.client.get_collection(self.price_db, self.target_seaweed_price_tb).aggregate(
|
||
[
|
||
# {
|
||
# "$match": {
|
||
# "remark": "均价计算"
|
||
# }
|
||
# },
|
||
{
|
||
"$group": {
|
||
"_id": {
|
||
"city": "$city",
|
||
"district": "$district"
|
||
},
|
||
"count": {"$sum": 1}
|
||
}
|
||
}]
|
||
)
|
||
region_data = list(iter_d)
|
||
city_region_list = []
|
||
for item in region_data:
|
||
zone_ = item.get('_id', None)
|
||
city_region_list.append((zone_['city'], zone_['district']))
|
||
return city_region_list
|
||
|
||
def inert_one_seaweed_mongodb(self, exp_data):
|
||
self.target_client.get_collection(self.price_db, self.target_seaweed_price_tb).insert_one(exp_data)
|
||
|
||
def inert_many_seaweed_mongodb(self, exp_data):
|
||
self.target_client.get_collection(self.price_db, self.target_seaweed_price_tb).insert_many(exp_data)
|
||
|
||
def load_region(self):
|
||
query = {"category": "region"}
|
||
fields = {
|
||
"city": 1,
|
||
"name": 1
|
||
}
|
||
iter_d = self.client.get_collection(self.zone_db, self.region_tb).find(query, fields).batch_size(10000)
|
||
region_data = list(iter_d)
|
||
if region_data:
|
||
region_data = pd.DataFrame(region_data)
|
||
region_data = region_data[['city', 'name']]
|
||
return region_data
|
||
|
||
def load_seaweed(self, city=None, region=None):
|
||
query = {"cat": "district", "city": city, "region": region}
|
||
fields = {
|
||
"region": 1,
|
||
"name": 1
|
||
}
|
||
iter_d = self.client.get_collection(self.zone_db, self.seaweed_tb).find(query, fields).batch_size(10000)
|
||
seaweed_data = list(iter_d)
|
||
if len(seaweed_data) > 0:
|
||
seaweed_data = pd.DataFrame(seaweed_data)
|
||
seaweed_data = seaweed_data[['region', 'name']]
|
||
return seaweed_data
|
||
|
||
def write_price_exp2mongo(self, city, region, base_exp, chain_exponents, last_exponents, start_date=201601):
|
||
"""
|
||
对定基指数、同比指数、环比指数和其他字段写入mongodb
|
||
:param city: 城市名称
|
||
:param region: 区域名称
|
||
:param base_exp: 定基指数数据
|
||
:param chain_exponents: 同比指数数据
|
||
:param last_exponents: 环比指数数据
|
||
:param start_date: 开始年月
|
||
"""
|
||
seaweed_list = chain_exponents.columns.tolist()
|
||
month_list = chain_exponents.index.tolist()
|
||
|
||
result_list = []
|
||
for seaweed_ in seaweed_list:
|
||
for month in month_list:
|
||
month_int = int(month)
|
||
if month_int < start_date:
|
||
continue
|
||
seaweed_month_d = {"city": city, "district": region, "name": seaweed_, "remark": "均价计算",
|
||
"yearmonth": int(month)}
|
||
chain = chain_exponents.loc[month_int, seaweed_]
|
||
year2year = last_exponents.loc[month_int, seaweed_]
|
||
if (chain and str(chain) == 'nan') and (year2year and str(year2year) == 'nan'):
|
||
continue
|
||
|
||
r_m_base_exp = None
|
||
if not base_exp.empty:
|
||
rm_base_exp_df = base_exp[(base_exp['date'] == month_int) & (base_exp['district_name'] == seaweed_)]
|
||
if not rm_base_exp_df.empty:
|
||
r_m_base_exp = rm_base_exp_df['base_price_exp'].values[0]
|
||
if str(r_m_base_exp) != 'nan':
|
||
r_m_base_exp = round(r_m_base_exp * 100, 1)
|
||
else:
|
||
r_m_base_exp = None
|
||
if str(chain) == 'nan':
|
||
chain = None
|
||
else:
|
||
chain = round((chain * 100 + 100), 1)
|
||
if str(year2year) == 'nan':
|
||
year2year = None
|
||
else:
|
||
year2year = round((year2year * 100 + 100), 1)
|
||
chain, year2year, r_m_base_exp = control_price(chain, year2year, r_m_base_exp)
|
||
seaweed_month_d['chain'] = chain # 同比
|
||
seaweed_month_d['yeartoyear'] = year2year # 环比
|
||
seaweed_month_d['fixedbase'] = r_m_base_exp # 定基
|
||
|
||
now_time = datetime.now()
|
||
seaweed_month_d['c_date'] = now_time
|
||
seaweed_month_d['m_date'] = now_time
|
||
|
||
result_list.append(seaweed_month_d)
|
||
# self.inert_one_seaweed_mongodb(seaweed_month_d)
|
||
# if result_list:
|
||
# self.inert_many_seaweed_mongodb(result_list)
|
||
return result_list
|
||
|
||
def write_price_exp2mongo_2(self, city, region, base_exp, chain_exponents, last_exponents, now_date=202001):
|
||
chain_df = chain_exponents
|
||
last_df = last_exponents
|
||
|
||
if not base_exp.empty:
|
||
# 准备base_exp数据
|
||
base_exp_prepared = base_exp[['date', 'district_name', 'base_price_exp']].copy()
|
||
base_exp_prepared['fixedbase'] = np.where(
|
||
base_exp_prepared['base_price_exp'].notna(),
|
||
(base_exp_prepared['base_price_exp'] * 100).round(1),
|
||
None
|
||
)
|
||
base_exp_prepared = base_exp_prepared.drop('base_price_exp', axis=1)
|
||
base_exp_prepared = base_exp_prepared.drop('date', axis=1)
|
||
base_exp_prepared_df = base_exp_prepared.set_index('district_name')
|
||
|
||
# 合并base_exp数据
|
||
result_df = pd.DataFrame({'chain': chain_df, 'yeartoyear': last_df})
|
||
result_df["fixedbase"] = base_exp_prepared_df
|
||
result_df["fixedbase"] = result_df["fixedbase"].where(pd.notnull(result_df["fixedbase"]), None)
|
||
else:
|
||
# 合并chain和year-to-year数据
|
||
result_df = pd.DataFrame({'chain': chain_df, 'yeartoyear': last_df})
|
||
# 添加城市和区域信息
|
||
result_df['city'] = city
|
||
result_df['district'] = region
|
||
result_df['yearmonth'] = now_date
|
||
result_df['remark'] = "均价计算"
|
||
result_df = result_df.reset_index(drop=False) # 将索引作为新列 'name'
|
||
result_df = result_df.rename(columns={'district_name': 'name'})
|
||
|
||
# 处理NaN值
|
||
result_df['chain'] = np.where(result_df['chain'].notna(), (result_df['chain'] * 100 + 100).round(1), None)
|
||
result_df['yeartoyear'] = np.where(result_df['yeartoyear'].notna(),
|
||
(result_df['yeartoyear'] * 100 + 100).round(1), None)
|
||
|
||
# 应用control_price函数
|
||
result_df[['chain', 'yeartoyear', 'fixedbase']] = result_df.apply(
|
||
lambda row: control_price(row['chain'], row['yeartoyear'], row['fixedbase']),
|
||
axis=1,
|
||
result_type='expand'
|
||
)
|
||
|
||
# 添加时间戳
|
||
now_time = datetime.now()
|
||
result_df['c_date'] = now_time
|
||
result_df['m_date'] = now_time
|
||
|
||
# 删除chain和yeartoyear都为NaN的行
|
||
result_df = result_df.dropna(subset=['chain', 'yeartoyear'], how='all')
|
||
|
||
# 转换为字典列表
|
||
result_list = result_df.to_dict('records')
|
||
|
||
return result_list
|
||
|
||
def process_seaweed_city_price(self, city=None, region=None, now_date=202001):
|
||
"""
|
||
加载小区名称,小区价格,计算定基、环比、同比指数,写入目标库
|
||
"""
|
||
t_1 = time.time()
|
||
seaweed_df = self.load_seaweed(city, region)
|
||
if len(seaweed_df) == 0 or len(seaweed_df) <= 0:
|
||
return 0
|
||
seaweed_name_list = seaweed_df['name'].tolist()
|
||
t_2 = time.time()
|
||
print(f"小区:({len(seaweed_name_list)}):{(t_2 - t_1):.2f}", end=' ')
|
||
four_num = int(now_date) // 100
|
||
five_times_year = four_num % 5
|
||
if four_num % 5 == 0:
|
||
five_times_year = 5
|
||
start_date = (four_num - five_times_year)*100 + 1 # 最近5倍整除的年份开始月
|
||
yesteryear = (int(now_date) // 100 - 1)*100 + (int(now_date) % 100)
|
||
|
||
query = {
|
||
"city": city,
|
||
"region": region,
|
||
"date": {"$gte": start_date}
|
||
}
|
||
fields = {
|
||
"avg_price": 1,
|
||
"region": 1,
|
||
"district_name": 1,
|
||
"date": 1
|
||
}
|
||
region_seaweeds_result = []
|
||
# 小区价格数据
|
||
iter_d = self.client.get_collection(self.price_db, self.seaweed_price_tb).find(query, fields).batch_size(10000)
|
||
t_3 = time.time()
|
||
print(f"价格:{(t_3 - t_2):.2f}", end=' ')
|
||
price_data = list(iter_d)
|
||
price_data = pd.DataFrame(price_data)
|
||
t_4 = time.time()
|
||
print(f"转换:({len(price_data)}):{(t_4 - t_3):.2f}", end=' ')
|
||
|
||
if len(price_data) > 0:
|
||
price_data = price_data.drop('_id', axis=1)
|
||
price_data = price_data[price_data['district_name'].isin(seaweed_name_list)]
|
||
base_year_df = get_base_year_price(price_data)
|
||
fixed_base_exponents = get_fixed_base_price(price_data, base_year_df) # 基准指数
|
||
|
||
price_data = price_data[price_data['date'] >= yesteryear] # 保留去年同月开始的数据
|
||
t_5 = time.time()
|
||
print(f"定基:{(t_5 - t_4):.2f}", end=' ')
|
||
|
||
if not price_data.empty:
|
||
price_seaweed_all_region_df = pd.DataFrame()
|
||
for seaweed_name in seaweed_name_list:
|
||
price_data_seaweed_df = price_data[price_data['district_name'] == seaweed_name]
|
||
if not price_data_seaweed_df.empty:
|
||
price_data_seaweed_df = price_data_seaweed_df.set_index('date')
|
||
price_data_seaweed_df = pd.pivot_table(price_data_seaweed_df, values='avg_price',
|
||
index=price_data_seaweed_df.index,
|
||
columns='district_name')
|
||
if price_data_seaweed_df.shape[1] != 1:
|
||
print(f"error: {seaweed_name} 应该只有一列,但实际有 {price_data_seaweed_df.shape[1]} 列")
|
||
continue
|
||
if price_seaweed_all_region_df.empty:
|
||
price_seaweed_all_region_df = price_data_seaweed_df
|
||
else:
|
||
price_seaweed_all_region_df = pd.concat(
|
||
[price_seaweed_all_region_df, price_data_seaweed_df], axis=1, join='outer')
|
||
t_51 = time.time()
|
||
print(f"合并:{(t_51 - t_5):.2f}", end=' ')
|
||
chain_exponents = custom_pct_change(price_seaweed_all_region_df)
|
||
last_exponents = custom_pct_change(price_seaweed_all_region_df, periods=12)
|
||
t_6 = time.time()
|
||
print(f"指数:{(t_6 - t_51):.2f}", end=' ')
|
||
|
||
fixed_base_exponents = fixed_base_exponents[fixed_base_exponents['date'] == int(now_date)]
|
||
chain_exponents = chain_exponents.loc[now_date, :]
|
||
last_exponents = last_exponents.loc[now_date, :]
|
||
region_seaweeds_result = self.write_price_exp2mongo_2(city, region, fixed_base_exponents,
|
||
chain_exponents, last_exponents, now_date)
|
||
t_60 = time.time()
|
||
print(f"封装:{(t_60 - t_6):.2f}", end=' ')
|
||
t_61 = time.time()
|
||
if region_seaweeds_result:
|
||
for i in range(0, len(region_seaweeds_result), 10000):
|
||
batch_seaweeds = region_seaweeds_result[i:i + 10000]
|
||
self.inert_many_seaweed_mongodb(batch_seaweeds)
|
||
t_7 = time.time()
|
||
print(f"写入:{(t_7 - t_61):.2f}", end=' ')
|
||
print(f"{city}-{region}===:{len(price_data)} in {len(seaweed_name_list)}个小区")
|
||
return len(price_data)
|
||
return 0
|
||
|
||
def process_seaweed(self):
|
||
print(self.target_client)
|
||
current_time = time.localtime()
|
||
current_year = current_time.tm_year
|
||
current_month = current_time.tm_mon
|
||
if current_month == 1:
|
||
last_month = 12
|
||
last_year = current_year - 1
|
||
else:
|
||
last_month = current_month - 1
|
||
last_year = current_year
|
||
now_date = last_year * 100 + last_month
|
||
region_data = self.get_region()
|
||
region_list = []
|
||
sum_num = 0
|
||
f_last_f = 0
|
||
city_region_list = self.get_exists_region()
|
||
in_num = 0
|
||
not_in_num = 0
|
||
print(f"共{len(region_data)}个小区,已经存在{len(city_region_list)}条,开始处理...")
|
||
for index, row in region_data.iterrows():
|
||
city_, region_ = row["city"], row["name"]
|
||
if (city_, region_) in city_region_list:
|
||
in_num += 1
|
||
continue
|
||
else:
|
||
not_in_num += 1
|
||
if (city_, region_) not in region_list:
|
||
region_list.append((city_, region_))
|
||
|
||
print(city_, f"index:【{index}】", end=',')
|
||
t_a = time.time()
|
||
num_ = self.process_seaweed_city_price(city_, region_, now_date)
|
||
t_b = time.time()
|
||
sum_num += num_
|
||
print(f"<{t_b - t_a} s>", num_)
|
||
print(f"insert {self.price_db}.{self.target_seaweed_price_tb} sum:{sum_num}")
|
||
print(f"in num:{in_num}, not in num:{not_in_num}")
|
||
|
||
|
||
if __name__ == '__main__':
|
||
t1 = time.time()
|
||
obj = SeaweedPrice()
|
||
t2 = time.time()
|
||
# db1 = obj.process_seaweed_city_price("上海", "长宁", 202411) # 杭州 文山州
|
||
obj.process_seaweed()
|
||
# obj.get_more_data()
|
||
t3 = time.time()
|
||
print(f"init: {t2 - t1}, search:{t3 - t2}")
|