Pandas使用.where()方法时条件被忽略，快递员奖金计算不符合成功率阈值要求

问题根源 #

1. 原逻辑在明细订单行维度计算成功率，不符合业务规则：成功率是快递员（或单日+快递员维度）的聚合统计指标，不属于单个订单的属性。
2. 示例代码中df['Total Orders'] = df['DeliveryOnTime'].count()将全量数据集的总订单数赋值给所有行，导致单条合格订单的成功率计算错误，条件判断失效。

示例数据修复代码 #

import pandas as pd

data = {'ID': [1, 1, 1, 2, 2, 3, 4, 5, 5],
         'DeliveryOnTime': ["On-time", "Late", "Early", "On-time", "On-time", "Late", "Early", "Early", "Late"],
      }
df = pd.DataFrame(data)

# 按快递员ID聚合统计核心指标
courier_agg = df.groupby('ID').agg(
    Total_Orders=('DeliveryOnTime', 'count'),
    Eligible=('DeliveryOnTime', lambda x: x.isin(["On-time", "Early"]).sum())
).reset_index()

# 计算成功率与对应奖金
courier_agg['Success Rate'] = (courier_agg['Eligible'] / courier_agg['Total_Orders']) * 100
courier_agg['Bonus'] = courier_agg.apply(lambda x: round(x['Eligible'] * 1.2, 1) if x['Success Rate'] >= 95 else 0, axis=1)

# 输出结果
print(courier_agg[['ID', 'Eligible', 'Total_Orders', 'Success Rate', 'Bonus']])

运行后即可得到你预期的输出结果。

正式业务代码修复 #

对应包含日期、快递员维度的业务逻辑，直接在聚合后的统计层计算成功率和奖金即可：

from openpyxl import load_workbook
import pandas as pd

df = pd.read_excel(r'path\filename.xlsx')
df['DeliveredAt'] = pd.to_datetime(df['DeliveredAt'].astype(str))
df['Date'] = df['DeliveredAt'].dt.strftime('%d/%m/%y')
# 标记单条订单是否符合合格要求
df['Eligible'] = df['DeliveryOnTime'].isin(["On-time", "Early"])

# 按日期+快递员维度计算单日统计结果
per_day = df.groupby(['Date', 'Courier']).agg(
    Total_Orders=('OrderNumber', 'count'),
    Eligible=('Eligible', 'sum'),
    Incentive=('Incentive', 'sum')
).reset_index()
per_day['Success Rate'] = (per_day['Eligible'] / per_day['Total_Orders']) * 100
per_day['Bonus'] = per_day.apply(lambda x: x['Eligible'] * 1.2 if x['Success Rate'] >= 95 else 0, axis=1)

# 按快递员维度计算总统计结果
per_courier = df.groupby('Courier').agg(
    Total_Orders=('OrderNumber', 'count'),
    Eligible=('Eligible', 'sum'),
    Incentive=('Incentive', 'sum')
).reset_index()
per_courier['Success Rate'] = (per_courier['Eligible'] / per_courier['Total_Orders']) * 100
per_courier['Bonus'] = per_courier.apply(lambda x: x['Eligible'] * 1.2 if x['Success Rate'] >= 95 else 0, axis=1)

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