BrandCultivation/utils/result_process.py

import os
import pandas as pd
from database import MySqlDao
from models.rank.data.config import ImportanceFeaturesMap, ProductConfig

dao = MySqlDao()
def filter_data(data, filter_dict):
    product_content = []
    for key, value in filter_dict.items():
        if key != 'product_code':
            product_content.append(f"{ImportanceFeaturesMap.PRODUCT_FEATRUES_MAP[key]}({value})")
    
    data = data[data['product_feat'].isin(product_content)]
    return data

def split_relation_subtable(data, filter_dict, save_dir):
    """拆分卷烟商户特征相关性子表"""
    data = filter_data(data, filter_dict).copy()
    data.to_csv(os.path.join(save_dir, "feats_interaction.csv"), index=False, encoding='utf-8-sig')
    data['group_key'] = data["product_feat"].str.extract(r'^([^(]+)')
    grouped = data.groupby('group_key')
    sub_tables = {
        name: group.drop(columns=['group_key']).sort_values('relation', ascending=False)
        for name, group in grouped
    }
    
    for name, sub_data in sub_tables.items():
        sub_data.to_csv(os.path.join(save_dir, f"{name}.csv"), index=False, encoding='utf-8-sig')
        
def generate_report(city_uuid, data, filter_dict, recommend_data, delivery_count, save_dir):
    """根据总表筛选结果"""
    # 1. 筛选商户相关性排序结果
    data = filter_data(data, filter_dict).copy()
    data.to_csv(os.path.join(save_dir, "feats_interaction.csv"), index=False, encoding='utf-8-sig')
    group_sums = data.groupby("cust_feat")["relation"].sum()
    # 筛选出总和非负的cust_feat
    valid_cust_feats = group_sums[group_sums > 0].index.tolist()
    cust_relation = data[data["cust_feat"].isin(valid_cust_feats)]
    cust_relation = cust_relation.reset_index(drop=True)
    cust_relation = cust_relation.rename(
        columns = {
            "product_feat": "卷烟特征",
            "cust_feat": "商户特征",
            "relation": "相关性"
        }
    )
    cust_relation.to_csv(os.path.join(save_dir, "品规商户特征关系表.csv"), index=False, encoding='utf-8-sig')
    
    
    # 2. 品规信息
    cust_relation[:20].to_csv(os.path.join(save_dir, "cust_relation.csv"), index=False, encoding='utf-8-sig')
    with open(os.path.join(save_dir, "卷烟信息表.csv"), "w", encoding='utf-8-sig') as f:
        for key, value in filter_dict.items():
            if key != 'product_code':
                f.write(f"{ImportanceFeaturesMap.PRODUCT_FEATRUES_MAP[key]}, {value}\n")
                
    # 3. 生成推荐报告
    recommend_report = generate_recommend_report(city_uuid, recommend_data, delivery_count)
    recommend_report.to_csv(os.path.join(save_dir, "商户售卖推荐表.csv"), index=False, encoding="utf-8-sig")
    
def generate_recommend_report(city_uuid, recommend_data, delivery_count):
    recommend_data = pd.DataFrame(recommend_data)
    
    recommend_list = recommend_data["cust_code"].to_list()
    recommend_cust_info = dao.get_cust_by_ids(city_uuid, recommend_list)
    
    cust_ids = recommend_cust_info.set_index("BB_RETAIL_CUSTOMER_CODE")
    recommend_data = recommend_data.join(cust_ids, on="cust_code", how="inner")
    recommend_data = recommend_data[["cust_code", "BB_RETAIL_CUSTOMER_NAME", "sale_qty", "recommend_score"]]
   # 1. 计算每个商户的理论应得数量（带小数）
    recommend_data["delivery_float"] = (
        recommend_data["recommend_score"] / recommend_data["recommend_score"].sum() * delivery_count
    )

    # 2. 向下取整得到基础配额
    recommend_data["delivery_count"] = recommend_data["delivery_float"].astype(int)

    # 3. 计算余数并排序
    recommend_data["remainder"] = recommend_data["delivery_float"] - recommend_data["delivery_count"]
    recommend_data = recommend_data.sort_values("remainder", ascending=False)

    # 4. 将剩余配额按余数从大到小分配
    remaining = delivery_count - recommend_data["delivery_count"].sum()
    recommend_data.iloc[:remaining, recommend_data.columns.get_loc("delivery_count")] += 1
    
    recommend_data = recommend_data.drop(columns=["delivery_float", "remainder"])
    recommend_data = recommend_data.reset_index()
    # 5. 按recommend_score从大到小重新排序
    recommend_data = recommend_data.sort_values("index")
    recommend_data["sale_qty"] = recommend_data["sale_qty"].round(0).astype(int) # 将月均销量四舍五入取整
    recommend_data = recommend_data.rename(
        columns={
            "index": "推荐序号", 
            "cust_code": "商户编号", 
            "BB_RETAIL_CUSTOMER_NAME": "商户名称", 
            "sale_qty": "历史月均销量", 
            "recommend_score": "推荐系数", 
            "delivery_count": "建议投放量（条）"
            }
        )
    recommend_data["推荐序号"] = recommend_data["推荐序号"] + 1
    
    return recommend_data
       
def get_cust_list_from_history_order(city_uuid, product_code):
    # 获取订单数据并处理
    order_data = dao.get_eval_order_by_product(city_uuid, product_code)
    order_data = order_data[["cust_code", "cust_name", "product_code", "product_name", "sale_qty", "sale_amt"]]
    
    # 确保cust_code是字符串类型
    order_data["cust_code"] = order_data["cust_code"].astype(str)
    
    order_data = order_data.groupby(["cust_code", "cust_name", "product_code", "product_name"])[["sale_qty", "sale_amt"]].mean().reset_index()
    order_data["sale_qty"] = order_data["sale_qty"].round(0).astype(int)
    order_data = order_data.sort_values("sale_qty", ascending=False)
    
    # 读取推荐数据
    recommend_data = pd.read_csv('./data/商户售卖推荐表.csv')
    # recommend_data = recommend_data.drop(columns=["sale_qty"])
    # 确保recommend_data中的cust_code也是字符串类型
    recommend_data["商户编号"] = recommend_data["商户编号"].astype(str)
    cust_ids = recommend_data.set_index("商户编号")
    
    # 执行合并操作
    merge_data = order_data.join(cust_ids, on="cust_code", how="left")
    merge_data = merge_data[["cust_code", "cust_name", "product_code", "product_name", "sale_qty", "推荐序号", "推荐系数", "历史月均销量"]]
    merge_data = merge_data.rename(
        columns={
            "cust_code": "商户编号", 
            "cust_name": "商户名称", 
            "product_code": "卷烟编码", 
            "product_name": "卷烟名称", 
            "sale_qty": "月均销量"
        }
    )
    return merge_data
        
if __name__ == "__main__":
    order_data = get_cust_list_from_history_order("00000000000000000000000011445301", "350355")
    order_data.to_csv("./data/eval.csv", index=False)