如何在Pandas分组后按条件计算计数比值？

先把你的数据整理成标准DataFrame结构更直观：

import pandas as pd

data = [
    ["Director-Level", "Meeting Requested", "EmailSend", 490],
    ["Manager-Level", "Meeting Requested", "EmailSend", 305],
    ["Non-Managerial", "Meeting Requested", "EmailSend", 272],
    ["Top Executive; C-Level", "Meeting Requested", "EmailSend", 226],
    ["VP-Level", "Meeting Requested", "EmailSend", 185],
    ["Director-Level", "Meeting Requested", "FormSubmit", 131],
    ["Manager-Level", "Meeting Requested", "FormSubmit", 74],
    ["Top Executive; C-Level", "Meeting Requested", "FormSubmit", 61],
    ["VP-Level", "Meeting Requested", "FormSubmit", 53],
    ["Non-Managerial", "Meeting Requested", "FormSubmit", 52],
    ["Other", "Meeting Requested", "EmailSend", 20],
    ["Other", "Meeting Requested", "FormSubmit", 2]
]
mr_jr = pd.DataFrame(data, columns=["JOB_ROLE", "COMMENTS", "ACTIVITY_TYPE", "COUNTS"])

你之前的代码没得到预期结果，核心问题是：在apply的lambda里，x[x['ACTIVITY_TYPE']=='FormSubmit'].COUNTS返回的是Series对象，两个Series直接相除会保留索引结构，最终得到的ratios是多层索引的Series，而非每个分组对应单个比值的结果。

这里给你两种简单可靠的解决方法：

方法一：数据透视表（最直观易维护） #

先把数据按JOB_ROLE分组，将ACTIVITY_TYPE转为列，直接对两列做除法运算：

# 生成透视表：行=JOB_ROLE，列=ACTIVITY_TYPE，值=COUNTS
pivot_df = mr_jr.pivot(index='JOB_ROLE', columns='ACTIVITY_TYPE', values='COUNTS')
# 计算比值并保留两位小数
ratios = (pivot_df['FormSubmit'] / pivot_df['EmailSend']).round(2)
# 转为列表得到预期输出
print(ratios.tolist())  # 输出: [0.27, 0.24, 0.19, 0.1, 0.27, 0.28]

如果需要和原始数据中JOB_ROLE的出现顺序一致，只需给pivot加上sort=False参数即可。

方法二：改进groupby的apply逻辑 #

在lambda里明确取出每个条件下的单个数值（每个JOB_ROLE对应两种ACTIVITY_TYPE，所以每个分组里每种类型只有一个值），用.iloc[0]提取Series的第一个元素：

ratios = mr_jr.groupby('JOB_ROLE').apply(
    lambda x: x[x['ACTIVITY_TYPE'] == 'FormSubmit']['COUNTS'].iloc[0] / 
              x[x['ACTIVITY_TYPE'] == 'EmailSend']['COUNTS'].iloc[0]
)
# 保留两位小数并转列表
print(ratios.round(2).tolist())

两种方法都能得到你想要的结果，其中透视表的写法更清晰，后续维护成本更低，推荐优先使用。

内容的提问来源于stack exchange，提问作者Krishnang K Dalal