基于客户价值的销售员工最优分配及Pandas列赋值需求

需求概述 #

需基于给定的Pandas DataFrame实现销售员工的客户最优分配优化，此前尝试的方案无效，现明确需求细节如下：

import pandas as pd

df = pd.DataFrame({
"center": ['0060','0060','0060','0060','0060','0060','0060','0060','0060','0060','0070','0070','0070','0070','0070','0070','0070','0070','0080','0080','0080','0080','0080','0080','0080','0080','0080','0080','0080','0080','0080'],
"client": ['C00001','C00002','C00003','C00004','C00005','C00006','C00007','C00008','C00009','C00010','C00011','C00012','C00013','C00014','C00015','C00016','C00017','C00018','C00019','C00020','C00021','C00022','C00023','C00024','C00025','C00026','C00027','C00028','C00029','C00030','C00031'],
"user": ['A','A','A','A','A','B','B','B','NaN','NaN','C','C','C','C','C','D','D','D','E','E','E','E','E','F','F','F','G','G','NaN','NaN','NaN'],
"value": [5,5,3,5,2,5,2,2,2,3,5,4,4,1,1,3,3,3,5,3,2,2,5,5,2,2,5,3,1,2,3], 
"assigned_user": ['NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN']
})

字段说明 #

• center：销售部门编号
• client：客户编号
• user：当前负责员工（值为NaN表示未分配的新客户）
• value：客户价值
• assigned_user：待填充的最终分配员工列

核心分配规则 #

按部门分组执行分配，需同时满足两个要求：

1. 各负责员工的总客户价值尽可能接近该部门总价值的均值
2. 尽可能保留员工原有客户分配（仅在必要时调整已有归属的客户）

示例说明 #

示例1：部门0060 #

• 部门员工：A、B
• 部门总客户价值：34，均值17
• 原有分配：A总价值20，B总价值9
• 优化方案：将A名下价值3的客户转至B，同时让B接收未分配的新客户，最终双方总价值接近17

示例2：部门0080 #

• 部门员工：E、F、G
• 部门总客户价值：40，均值约13.3
• 原有分配：E总价值17，F总价值9，G总价值8，另有总价值6的未分配新客户
• 优化方案：将E名下部分客户转出，与新客户一同分配给F、G，保留F、G原有客户，最终三者总价值尽可能接近均值

实现思路与代码 #

实现逻辑 #

1. 按center分组处理每个部门的数据
2. 计算部门总价值及员工均值目标
3. 统计现有员工的当前总价值，区分超额和缺口员工
4. 从超额员工中优先筛选小价值客户作为转移对象，同时将新客户分配给缺口员工，直到各员工总价值尽可能接近均值
5. 填充assigned_user字段，原有保留的客户直接沿用原user值，调整或新分配的客户填入目标员工

代码实现 #

import pandas as pd
import numpy as np

def optimize_client_assignment(df):
    # 复制原数据避免修改源数据
    df = df.copy()
    # 将字符串类型的'NaN'转为真实NaN值
    df['user'] = df['user'].replace('NaN', np.nan)
    df['assigned_user'] = df['assigned_user'].replace('NaN', np.nan)

    for center, group in df.groupby('center'):
        total_value = group['value'].sum()
        # 获取部门内有效员工列表
        employees = group['user'].dropna().unique().tolist()
        num_employees = len(employees)
        if num_employees == 0:
            continue
        target_mean = total_value / num_employees

        # 初始化员工当前总价值，原有客户先保留分配
        user_current = group[group['user'].notna()].groupby('user')['value'].sum().to_dict()
        df.loc[group.index, 'assigned_user'] = df.loc[group.index, 'user']

        # 收集未分配的新客户
        new_clients = group[group['user'].isna()]
        new_client_list = list(zip(new_clients.index, new_clients['value']))

        # 收集可转移的客户：从超额员工中按价值升序筛选
        transfer_list = []
        for user in employees:
            current_sum = user_current[user]
            if current_sum > target_mean:
                user_clients = group[(group['user'] == user)].sort_values('value')
                for idx, row in user_clients.iterrows():
                    if user_current[user] > target_mean:
                        transfer_list.append((idx, user, row['value']))
                        user_current[user] -= row['value']
                    else:
                        break

        # 按缺口大小排序员工，缺口大的优先分配
        def get_employee_gaps():
            return sorted([(u, target_mean - user_current[u]) for u in employees], key=lambda x: x[1], reverse=True)
        
        employee_gaps = get_employee_gaps()

        # 分配转移客户
        for idx, from_user, val in transfer_list:
            for user, gap in employee_gaps:
                if gap > 0:
                    df.loc[idx, 'assigned_user'] = user
                    user_current[user] += val
                    employee_gaps = get_employee_gaps()
                    break

        # 分配新客户
        for idx, val in new_client_list:
            for user, gap in employee_gaps:
                if gap > 0:
                    df.loc[idx, 'assigned_user'] = user
                    user_current[user] += val
                    employee_gaps = get_employee_gaps()
                    break

    return df

# 执行优化并查看结果
optimized_df = optimize_client_assignment(df)
print(optimized_df.groupby(['center', 'assigned_user'])['value'].sum())

内容的提问来源于stack exchange，提问作者joss