如何按DataFrame的Topic1分组，用Val1值除以分组内的Final值

原始DataFrame #

Topic1        Topic2        Val1        Val2
Fruit         A               12        9
Fruit         B               10        7
Fruit         Final           16        9
Shirt         X               40        1
Shirt         Y               10        10
Shirt         A               30        3
Shirt         Final           100       20

实现方案 #

方法一：使用groupby.transform直接计算 #

通过分组变换，在每个Topic1组内，将Val1除以该组中Topic2为Final对应的Val1值：

import pandas as pd

# 构造原始数据
data = {
    'Topic1': ['Fruit', 'Fruit', 'Fruit', 'Shirt', 'Shirt', 'Shirt', 'Shirt'],
    'Topic2': ['A', 'B', 'Final', 'X', 'Y', 'A', 'Final'],
    'Val1': [12, 10, 16, 40, 10, 30, 100],
    'Val2': [9, 7, 9, 1, 10, 3, 20]
}
df = pd.DataFrame(data)

# 计算Calc列，保留两位小数
df['Calc'] = df.groupby('Topic1')['Val1'].transform(
    lambda group: group / group[group.index.isin(df[df['Topic2'] == 'Final'].index)]
).round(2)

print(df)

方法二：提取Final值后合并计算 #

先提取每组Final对应的Val1，再合并到原表进行除法运算：

import pandas as pd

# 构造原始数据
data = {
    'Topic1': ['Fruit', 'Fruit', 'Fruit', 'Shirt', 'Shirt', 'Shirt', 'Shirt'],
    'Topic2': ['A', 'B', 'Final', 'X', 'Y', 'A', 'Final'],
    'Val1': [12, 10, 16, 40, 10, 30, 100],
    'Val2': [9, 7, 9, 1, 10, 3, 20]
}
df = pd.DataFrame(data)

# 提取每组Final的Val1并改名
final_vals = df[df['Topic2'] == 'Final'][['Topic1', 'Val1']].rename(columns={'Val1': 'Final_Val1'})

# 合并数据
df = df.merge(final_vals, on='Topic1')

# 计算Calc列
df['Calc'] = (df['Val1'] / df['Final_Val1']).round(2)

# 可选：删除中间辅助列
df = df.drop('Final_Val1', axis=1)

print(df)

运行结果 #

Topic1 Topic2  Val1  Val2  Calc
0  Fruit      A    12     9  0.75
1  Fruit      B    10     7  0.62
2  Fruit  Final    16     9  1.00
3  Shirt      X    40     1  0.40
4  Shirt      Y    10    10  0.10
5  Shirt      A    30     3  0.30
6  Shirt  Final   100    20  1.00

注：原示例中Fruit组B的Calc值标注为0.44，实际计算10/16=0.625，保留两位小数应为0.62，推测是原示例的笔误。

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