154 lines
6.3 KiB
Python
154 lines
6.3 KiB
Python
"""
|
|
统计近三个月、近一年样本量及小区属性
|
|
"""
|
|
import logging
|
|
from goojia_utils import MAIN_MONGO_CLIENT, ElasticSearchClient, dateutils, commutils
|
|
import pandas as pd
|
|
import datetime
|
|
import sys
|
|
|
|
log = logging.getLogger(__name__)
|
|
|
|
|
|
# ================== 核心函数定义(供 main 调用) ==================
|
|
|
|
def get_sell(df, daysBefore=365):
|
|
cities = df[['城市']].drop_duplicates(keep='first')['城市'].values.tolist()
|
|
city_one_year_count = list()
|
|
for city in cities:
|
|
flag = True
|
|
for item in city_one_year_count:
|
|
if item['城市'] == city:
|
|
flag = False
|
|
break
|
|
if flag == False:
|
|
continue
|
|
seaweed_temp_df = seaweed_df[seaweed_df['city'] == city]
|
|
count = 0
|
|
total_num = 0
|
|
all_num = 0
|
|
for index, item in seaweed_temp_df.iterrows():
|
|
names = list()
|
|
region = item['region']
|
|
names.append(item['name'])
|
|
if type(item['alias']) == list:
|
|
for alias_item in item['alias']:
|
|
names.append(alias_item)
|
|
names_str = "','".join(names)
|
|
names_str = "'" + names_str + "'"
|
|
try:
|
|
num = commutils.get_sample(city, region, names_str, daysBefore=daysBefore)
|
|
if num > sell_num:
|
|
count += 1
|
|
total_num += num
|
|
all_num += num
|
|
except Exception as e:
|
|
print(item)
|
|
print(e)
|
|
city_one_year_count.append({'城市': city, '近一年有样本小区数量': count, '近一年的样本总量': total_num, '总数量': all_num})
|
|
print(city + "已处理完, 近一年有样本小区数量" + str(count) + " 近一年的样本总量" + str(total_num))
|
|
|
|
result_df = pd.DataFrame(city_one_year_count)
|
|
return result_df
|
|
|
|
|
|
def sell_stat():
|
|
"""
|
|
最近一年有样本小区占比 最近3月有样本小区占比
|
|
"""
|
|
df = pd.read_excel(aim_file_name)
|
|
result_df = get_sell(df, 365)
|
|
result_df = result_df[['城市', '近一年有样本小区数量', '近一年的样本总量']]
|
|
df = pd.merge(df, result_df, how='left', on=['城市'])
|
|
df['近一年有样本小区占比'] = round(df['近一年有样本小区数量'] * 100 / df['小区量'], 2)
|
|
|
|
result_df = get_sell(df, 93)
|
|
result_df = result_df.rename(columns={'近一年有样本小区数量': '近三月有样本小区数量'})
|
|
result_df = result_df[['城市', '近三月有样本小区数量']]
|
|
df = pd.merge(df, result_df, how='left', on=['城市'])
|
|
df['近三月有样本小区占比'] = round(df['近三月有样本小区数量'] * 100 / df['小区量'], 2)
|
|
|
|
df.to_excel(aim_file_name, index=False)
|
|
|
|
|
|
def property_stat():
|
|
"""
|
|
建筑年代覆盖率 物业类型覆盖率 建筑类型覆盖率
|
|
"""
|
|
df = pd.read_excel(aim_file_name)
|
|
df['建筑年代'] = None
|
|
df['物业类型'] = None
|
|
df['建筑类型'] = None
|
|
for index, item in df.iterrows():
|
|
city = item['城市']
|
|
print(city)
|
|
count = seaweed_conn.count(
|
|
{'cat': 'district', 'status': 0, 'city': city, 'finish_building_date': {'$ne': None}})
|
|
df.loc[index, '建筑年代'] = round(count * 100 / item['小区量'], 2)
|
|
|
|
estate_type2_count = seaweed_conn.count(
|
|
{'cat': 'district', 'status': 0, 'city': city, 'estate_type2': {'$ne': None}})
|
|
df.loc[index, '物业类型'] = round(estate_type2_count * 100 / item['小区量'], 2)
|
|
|
|
build_type_count = seaweed_conn.count(
|
|
{'cat': 'district', 'status': 0, 'city': city, 'build_type': {'$ne': None}})
|
|
df.loc[index, '建筑类型'] = round(build_type_count * 100 / item['小区量'], 2)
|
|
df.to_excel(aim_file_name, index=False)
|
|
|
|
|
|
def merge_aim():
|
|
df = pd.read_excel(aim_file_name)
|
|
need_df = pd.read_excel('/opt/airflow/dags/repo/dags/stat/evaluate/data/估值二期项目数据投标.xlsx')
|
|
df = pd.merge(df, need_df, how='left', on=['城市'])
|
|
df.to_excel(aim_file_name, index=False)
|
|
|
|
|
|
def up_to_nextcloud():
|
|
"""
|
|
上传统计信息至nextcloud
|
|
"""
|
|
from nextcloud import NextCloud
|
|
NEXTCLOUD_URL = "https://nextcloud.fangjia.com"
|
|
NEXTCLOUD_USERNAME = "shenjian@fangjia.com"
|
|
NEXTCLOUD_PASSWORD = "goojia123456"
|
|
aim_file_path = '共享/二期估值数据每周统计/估值数据统计结果' + year_month_day + '.xlsx'
|
|
nxc = NextCloud(endpoint=NEXTCLOUD_URL, user=NEXTCLOUD_USERNAME, password=NEXTCLOUD_PASSWORD, json_output=True)
|
|
nxc.upload_file(aim_file_name, aim_file_path)
|
|
|
|
|
|
# ================== 🛡️ 安全运行入口 ==================
|
|
|
|
if __name__ == "__main__":
|
|
# 1. 只有脚本运行时,才解析命令行参数并转为整数
|
|
sell_num_arg = sys.argv[1] if len(sys.argv) > 1 else "2"
|
|
try:
|
|
sell_num = int(sell_num_arg)
|
|
except ValueError:
|
|
log.warning(f"无法将参数 '{sell_num_arg}' 转换为整数,将使用默认值 2。")
|
|
sell_num = 2
|
|
log.info("输出参数" + str(sell_num))
|
|
|
|
# 2. 统计时间范围及存储文件名定义
|
|
year_month_day = dateutils.format_date(datetime.datetime.now())
|
|
aim_file_name = '/home/airflow/data/evaluate/估值数据统计结果' + year_month_day + '.xlsx'
|
|
start_time = dateutils.format_date(dateutils.add_months(dateutils.datetime.datetime.now(), days=-93))
|
|
one_year_time = dateutils.format_date(dateutils.add_months(dateutils.datetime.datetime.now(), days=-366))
|
|
|
|
# 3. 延迟数据库初始化(避开 Airflow 解析器扫描)
|
|
seaweed_conn = MAIN_MONGO_CLIENT.get_collection('fangjia', 'seaweed')
|
|
es206_client = ElasticSearchClient(host='192.168.0.206')
|
|
|
|
# 4. 加载基础数据
|
|
seaweed_df = pd.DataFrame(list(seaweed_conn.find({'cat': 'district', 'status': 0}, {'city': 1, 'region': 1, 'name': 1, 'mapping_alias': 1})
|
|
.batch_size(10000)))
|
|
seaweed_df = seaweed_df.rename(columns={'mapping_alias': 'alias'})
|
|
|
|
# 5. 开始核心统计与上传逻辑
|
|
log.info("开始统计统计近三个月、近一年样本量及小区属性")
|
|
sell_stat()
|
|
property_stat()
|
|
merge_aim()
|
|
log.info("完成统计统计近三个月、近一年样本量及小区属性")
|
|
log.info("开始上传至云盘")
|
|
up_to_nextcloud()
|
|
log.info("云盘上传完成") |