如何为DataFrame分配销售代表，实现多列总和近似均分？

思路说明 #

要让DM Bookings、Revenue、AtL Opps三个指标在两位销售代表间近似平均分配，有两种简单易落地的方案，无需追求完全精确，能满足均衡需求：

方案一：加权综合评分分配 #

通过标准化指标消除量纲差异，结合业务权重计算每行综合得分，排序后交替分配，优先保证高价值行的均衡。

import pandas as pd
import numpy as np

# 构造目标DataFrame
data = {
    'Letter': ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H'],
    'DM Bookings': [6.0, 2.0, 1.0, 0.0, 1.0, 0.0, 1.0, 0.0],
    'Revenue': [42506.0, 21055.0, 6307.0, 8254.0, 29878.0, 6911.0, 6735.0, 0.0],
    'AtL Opps': [34, 41, 36, 14, 38, 10, 19, 80]
}
df = pd.DataFrame(data)

# 标准化指标（min-max缩放）
df['DM_norm'] = (df['DM Bookings'] - df['DM Bookings'].min()) / (df['DM Bookings'].max() - df['DM Bookings'].min())
df['Revenue_norm'] = (df['Revenue'] - df['Revenue'].min()) / (df['Revenue'].max() - df['Revenue'].min())
df['AtL_norm'] = (df['AtL Opps'] - df['AtL Opps'].min()) / (df['AtL Opps'].max() - df['AtL Opps'].min())

# 自定义权重（可根据业务调整，比如Revenue权重更高）
weights = {'DM_norm': 0.2, 'Revenue_norm': 0.5, 'AtL_norm': 0.3}
df['score'] = df['DM_norm'] * weights['DM_norm'] + df['Revenue_norm'] * weights['Revenue_norm'] + df['AtL_norm'] * weights['AtL_norm']

# 按得分降序排序，交替分配代表
df_sorted = df.sort_values('score', ascending=False).reset_index(drop=True)
df_sorted['Rep'] = np.where(df_sorted.index % 2 == 0, 'sales_rep_1', 'sales_rep_2')

# 合并回原DataFrame（保留原始顺序）
df = df.merge(df_sorted[['Letter', 'Rep']], on='Letter')

# 查看分配结果与指标总和
print("分配结果：")
print(df[['Letter', 'Rep']])
print("\n各代表指标总和：")
print(df.groupby('Rep')[['DM Bookings', 'Revenue', 'AtL Opps']].sum())

方案二：贪心动态分配 #

遍历高价值行，每次将当前行分配给能让「三个指标综合差距更小」的代表，动态平衡分配结果。

# 沿用上述构造的df与score列
df_sorted = df.sort_values('score', ascending=False).reset_index(drop=True)

# 初始化两个代表的指标总和
rep1_totals = {'DM Bookings': 0.0, 'Revenue': 0.0, 'AtL Opps': 0.0}
rep2_totals = {'DM Bookings': 0.0, 'Revenue': 0.0, 'AtL Opps': 0.0}
assignments = []

for _, row in df_sorted.iterrows():
    # 计算分配给rep1后的综合差距
    dm_diff1 = abs(rep1_totals['DM Bookings'] + row['DM Bookings'] - rep2_totals['DM Bookings'])
    rev_diff1 = abs(rep1_totals['Revenue'] + row['Revenue'] - rep2_totals['Revenue'])
    atl_diff1 = abs(rep1_totals['AtL Opps'] + row['AtL Opps'] - rep2_totals['AtL Opps'])
    total_diff1 = dm_diff1 + rev_diff1 + atl_diff1
    
    # 计算分配给rep2后的综合差距
    dm_diff2 = abs(rep1_totals['DM Bookings'] - (rep2_totals['DM Bookings'] + row['DM Bookings']))
    rev_diff2 = abs(rep1_totals['Revenue'] - (rep2_totals['Revenue'] + row['Revenue']))
    atl_diff2 = abs(rep1_totals['AtL Opps'] - (rep2_totals['AtL Opps'] + row['AtL Opps']))
    total_diff2 = dm_diff2 + rev_diff2 + atl_diff2
    
    # 分配给差距更小的代表
    if total_diff1 <= total_diff2:
        assignments.append('sales_rep_1')
        rep1_totals['DM Bookings'] += row['DM Bookings']
        rep1_totals['Revenue'] += row['Revenue']
        rep1_totals['AtL Opps'] += row['AtL Opps']
    else:
        assignments.append('sales_rep_2')
        rep2_totals['DM Bookings'] += row['DM Bookings']
        rep2_totals['Revenue'] += row['Revenue']
        rep2_totals['AtL Opps'] += row['AtL Opps']

df_sorted['Rep'] = assignments
df = df.merge(df_sorted[['Letter', 'Rep']], on='Letter')

# 查看结果
print("分配结果：")
print(df[['Letter', 'Rep']])
print("\n各代表指标总和：")
print(df.groupby('Rep')[['DM Bookings', 'Revenue', 'AtL Opps']].sum())

结果说明 #

两种方案都能实现三个指标的近似均衡，你可以根据业务优先级调整权重或贪心判断逻辑（比如重点保证Revenue的均衡时，可单独加大该指标的权重或差距占比）。

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