Files
airflow/dags/price_exponents/seaweed_price_month.py

427 lines
18 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
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}")