270 lines
10 KiB
Python
270 lines
10 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
|
|
# import logging
|
|
|
|
|
|
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=['region'], aggfunc='mean')
|
|
return result
|
|
|
|
|
|
def get_fixed_base_price(price_data, base_year_df):
|
|
"""
|
|
城市下区域定基价格指数
|
|
:param price_data: 均价数据
|
|
:param base_year_df: 能被5整除年份全年均价数据
|
|
"""
|
|
base_result_pf = pd.DataFrame()
|
|
for index, row in base_year_df.iteritems():
|
|
# print(type(row))
|
|
row_dict = list(zip(list(row.values), list(row.index)))
|
|
|
|
region = index
|
|
for _k, _v in row_dict:
|
|
# print(_k, _v)
|
|
start_y = _v
|
|
end_y = start_y + 5
|
|
base_col = 'base_price_exp'
|
|
region_ = price_data[(price_data['region'] == region) & (start_y < price_data['year']) & (price_data['year'] <= end_y)]
|
|
region_ = region_.assign(base_price_exp=region_['avg_price'] / _k)
|
|
region_ = region_.assign(base_year=_v)
|
|
base_result_pf = pd.concat([base_result_pf, region_[['date', 'base_year', 'region', base_col]]], axis=0)
|
|
return base_result_pf
|
|
|
|
|
|
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)
|
|
|
|
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 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)
|
|
|
|
|
|
class RegionPrice:
|
|
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.region_price_tb = "region_sell_stat"
|
|
self.region_tb = "region_block"
|
|
|
|
self.target_region_price_tb = "stats_index"
|
|
self.region_df = self.load_region()
|
|
|
|
def inert_one_region_mongodb(self, exp_data):
|
|
self.target_client.get_collection(self.price_db, self.target_region_price_tb).insert_one(exp_data)
|
|
|
|
def inert_many_region_mongodb(self, exp_data):
|
|
self.target_client.get_collection(self.price_db, self.target_region_price_tb).insert_many(exp_data)
|
|
|
|
def write_price_exp2mongo(self, city, base_exp, chain_exponents, last_exponents, now_date=201601):
|
|
"""
|
|
对定基指数、同比指数、环比指数和其他字段写入mongodb
|
|
:param city: 城市名称
|
|
:param base_exp: 定基指数数据
|
|
:param chain_exponents: 同比指数数据
|
|
:param last_exponents: 环比指数数据
|
|
:param now_date: 当前年月
|
|
"""
|
|
region_list = chain_exponents.index
|
|
|
|
result_list = []
|
|
for region in region_list:
|
|
region_month_d = {"city": city, "district": region, "cat": "district", "remark": "均价计算", "source": 2,
|
|
"yearmonth": int(now_date), "type": "区域"}
|
|
# chain yeartoyear fixedbase 同比 环比 定基
|
|
chain = chain_exponents.loc[region]
|
|
year2year = last_exponents.loc[region]
|
|
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'] == now_date) & (base_exp['region'] == region)]
|
|
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
|
|
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)
|
|
region_month_d['chain'] = chain
|
|
region_month_d['yeartoyear'] = year2year
|
|
region_month_d['fixedbase'] = r_m_base_exp
|
|
|
|
now_time = datetime.now()
|
|
# now_time = now_time.strftime("%Y-%m-%d %H:%M:%S") # 字符串不利于查询
|
|
region_month_d['c_date'] = now_time
|
|
region_month_d['m_date'] = now_time
|
|
|
|
result_list.append(region_month_d)
|
|
# self.inert_one_region_mongodb(region_month_d)
|
|
if result_list:
|
|
self.inert_many_region_mongodb(result_list)
|
|
|
|
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)
|
|
region_data = pd.DataFrame(region_data)
|
|
region_data = region_data[['city', 'name']]
|
|
return region_data
|
|
|
|
def get_region(self):
|
|
return self.region_df
|
|
|
|
def process_city_price(self, city=None, now_date=202001):
|
|
"""
|
|
加载区域价格,计算定基、环比、同比指数,写入目标库
|
|
:param city: 指定城市
|
|
:param now_date: 当前日期:年月
|
|
"""
|
|
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,
|
|
"date": {"$gte": start_date}
|
|
}
|
|
fields = {
|
|
"avg_price": 1,
|
|
"region": 1,
|
|
"date": 1
|
|
}
|
|
iter_d = self.client.get_collection(self.price_db, self.region_price_tb).find(query, fields).batch_size(10000)
|
|
|
|
price_data = list(iter_d)
|
|
price_data = pd.DataFrame(price_data)
|
|
# zone_name = city+'-'+region if city else '*-'+region
|
|
if len(price_data) > 0:
|
|
price_data = price_data.drop('_id', axis=1)
|
|
base_year_df = get_base_year_price(price_data)
|
|
fixed_base_exponents = get_fixed_base_price(price_data, base_year_df) # 5年定基指数
|
|
|
|
price_data = price_data[price_data['date'] >= yesteryear] # 保留去年同月开始的数据
|
|
price_data = price_data.set_index('date')
|
|
price_data = pd.pivot_table(price_data, values='avg_price', index=price_data.index, columns='region')
|
|
# chain_exponents = price_data.pct_change()
|
|
# last_exponents = price_data.pct_change(periods=12)
|
|
chain_exponents = custom_pct_change(price_data) # 环比指数
|
|
last_exponents = custom_pct_change(price_data, periods=12) # 同比指数
|
|
|
|
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, :]
|
|
self.write_price_exp2mongo(city, fixed_base_exponents, chain_exponents, last_exponents, now_date)
|
|
return len(price_data)
|
|
|
|
return 0
|
|
|
|
def process_region(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()
|
|
city_list = []
|
|
sum_num = 0
|
|
ten_num = 0
|
|
for index, row in region_data.iterrows():
|
|
city_, region_ = row["city"], row["name"]
|
|
if city_ not in city_list:
|
|
city_list.append(city_)
|
|
num_ = self.process_city_price(city_, now_date)
|
|
sum_num += num_
|
|
# print(num_)
|
|
|
|
print(city_, end=',')
|
|
ten_num += 1
|
|
if ten_num % 10 == 0:
|
|
print("*" * 50, f"{sum_num}")
|
|
print(f"insert {self.price_db}.{self.target_region_price_tb} sum:{sum_num}")
|
|
|
|
|
|
if __name__ == '__main__':
|
|
t1 = time.time()
|
|
obj = RegionPrice()
|
|
t2 = time.time()
|
|
|
|
# obj.transform_date()
|
|
# db1 = obj.process_city_price("宝鸡", 202411) # 杭州 文山州
|
|
obj.process_region() # 全部区 按城市遍历
|
|
t3 = time.time()
|
|
print(f"init: {t2-t1}, search:{t3-t2}")
|
|
# db1 = obj.get_region()
|
|
# print(f"------------------------------{len(db1)}")
|
|
# print(db1)
|