如何在Pandas中实现带条件的聚合列？对应SQL逻辑实现

你需要实现与以下SQL等价的Pandas逻辑：

SELECT day, 
    SUM(CASE WHEN category = "A" THEN sales ELSE NULL END) / SUM(sales) * 100 AS output_1,
    SUM(CASE WHEN category = "B" AND sex = "female" THEN sales ELSE NULL END) / SUM(sales) * 100 AS output_2
FROM table
GROUP BY 1

源数据表如下：

import pandas as pd

data = {
    'day': ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday', 'Monday'],
    'category': ['A', 'B', 'C', 'A', 'B', 'C', 'A', 'B'],
    'sex': ['male', 'female', 'male', 'female', 'male', 'female', 'male', 'female'],
    'sales': [100, 200, 150, 180, 220, 120, 140, 160]
}
df = pd.DataFrame(data)

预期输出（注：你提供的示例数值与源数据计算结果不符，以下为实际计算产出）：

day   output_1    output_2
0     Monday  38.461538   61.538462
1    Tuesday   0.000000  100.000000
2  Wednesday   0.000000    0.000000
3   Thursday  100.000000    0.000000
4     Friday   0.000000    0.000000
5   Saturday   0.000000    0.000000
6     Sunday  100.000000    0.000000

你尝试用.agg()方法实现，但尚未找到正确方案，当前代码框架：

result = (
    df.groupby('column_1')
    .agg(
        output_1(),
        output_2()
    )
    .reset_index()
)

解决方案 #

方法1：直接使用.agg()结合匿名函数 #

在.agg()中定义匿名函数，直接完成条件聚合与比例计算：

result = df.groupby('day').agg(
    output_1=lambda x: (x[x['category'] == 'A']['sales'].sum() / x['sales'].sum()) * 100,
    output_2=lambda x: (x[(x['category'] == 'B') & (x['sex'] == 'female')]['sales'].sum() / x['sales'].sum()) * 100
).reset_index()

方法2：先生成条件列再聚合 #

通过生成临时条件列，让逻辑更直观，适合复杂条件场景：

import numpy as np

# 生成条件匹配的销售额列，不满足条件则为0
df['sales_A'] = np.where(df['category'] == 'A', df['sales'], 0)
df['sales_B_female'] = np.where((df['category'] == 'B') & (df['sex'] == 'female'), df['sales'], 0)
# 获取分组总销售额
df['total_sales'] = df.groupby('day')['sales'].transform('sum')

# 分组计算比例
result = df.groupby('day').agg(
    output_1=lambda x: (x['sales_A'].sum() / x['total_sales'].iloc[0]) * 100,
    output_2=lambda x: (x['sales_B_female'].sum() / x['total_sales'].iloc[0]) * 100
).reset_index()

# 清理临时列（可选）
df.drop(['sales_A', 'sales_B_female', 'total_sales'], axis=1, inplace=True)

方法3：使用assign链式调用简化代码 #

用assign整合临时列生成与聚合，实现一行链式操作：

result = (
    df.assign(
        sales_A=np.where(df['category'] == 'A', df['sales'], 0),
        sales_B_female=np.where((df['category'] == 'B') & (df['sex'] == 'female'), df['sales'], 0),
        total_sales=lambda x: x.groupby('day')['sales'].transform('sum')
    )
    .groupby('day')
    .agg(
        output_1=lambda x: (x['sales_A'].sum() / x['total_sales'].iloc[0]) * 100,
        output_2=lambda x: (x['sales_B_female'].sum() / x['total_sales'].iloc[0]) * 100
    )
    .reset_index()
)

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