Files

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)