如何为DataFrame添加基于多列计算的加权平均列？

我有如下DataFrame：

Name      Gender    Rate    Hours    Amount3 
Mike      Male      20      30       3,000.00 
Nancy     Female    10      50       1,500.00 
Bob       Male      30      40       6,000.00 
Terrance  Male      40      60       12,000.00 
Sara      Female    35      32       3,360.00 
Myo       Male      15      80       6,000.00 

我已有一段计算简单占比的代码：

final_df['Weighted Average'] = final_df.groupby('Gender')['Amount3'].transform(lambda x: x/x.sum() if x.sum() > 0 else 0 )

现在想要添加一个加权平均列，计算逻辑为(Rate * Hours) * (Amount3/groupby.sum())，请问该如何实现？

步骤1：处理数据格式（若需要） #

首先确保Amount3是数值类型，因为当前显示带逗号的字符串，需要先转换：

final_df['Amount3'] = final_df['Amount3'].str.replace(',', '').astype(float)

步骤2：实现加权平均计算 #

通过groupby.transform获取每个性别分组的Amount3总和，再逐行套用公式即可：

# 获取每个Gender分组的Amount3总和，保持与原DataFrame行数一致
gender_amount3_sum = final_df.groupby('Gender')['Amount3'].transform('sum')

# 计算目标加权平均列
final_df['Weighted Average'] = (final_df['Rate'] * final_df['Hours']) * (final_df['Amount3'] / gender_amount3_sum)

步骤3：处理边界情况（可选） #

如果存在分组Amount3总和为0的情况，为避免除以0错误，可添加判断：

final_df['Weighted Average'] = (final_df['Rate'] * final_df['Hours']) * (
    final_df['Amount3'] / gender_amount3_sum.where(gender_amount3_sum > 0, 1)
)

简化写法 #

也可以直接合并为一行代码，复用你原有逻辑的结构：

final_df['Weighted Average'] = (final_df['Rate'] * final_df['Hours']) * final_df.groupby('Gender')['Amount3'].transform(lambda x: x/x.sum() if x.sum()>0 else 0)

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