如何用Pandas按指定行（Revenue）逐列除全行并适配多列？

需求说明 #

• 将DataFrame中每一列的所有行除以该列内的Revenue行，得到各科目占Revenue的百分比
• 方案需适配任意数量的列

当前DataFrame #

import pandas as pd

data = {'202112 YTD': {'Gross Margin': 200000,
  'Other (Income) & Expense': -100000,
  'Revenue': 5000000,
  'SG&A Expense': 150000,
  'Segment EBITDA': 200000},
 '202212 YTD': {'Gross Margin': 2850000,
  'Other (Income) & Expense': -338000,
  'Revenue': 6000000,
  'SG&A Expense': 15000,
  'Segment EBITDA': 200000}}

df = pd.DataFrame.from_dict(data)
print(df)

期望输出 #

outdata = {'202112 YTD': {'Gross Margin': 0.040,
  'Other (Income) & Expense': -0.020,
  'Revenue': 1,
  'SG&A Expense': 0.030,
  'Segment EBITDA': 0.040},
 '202212 YTD': {'Gross Margin': 0.475,
  'Other (Income) & Expense': -0.056,
  'SG&A Expense': 0.003,
  'Segment EBITDA': 0.033}}

outdf = pd.DataFrame.from_dict(outdata)
print(outdf.round(3))

我的尝试（参考代码） #

import copy
import pandas as pd

original_table = [
    {'name': 'Alice', 'age': 25, 'gender': 'Female'},
    {'name': 'Bob', 'age': 32, 'gender': 'Male'},
    {'name': 'Charlie', 'age': 40, 'gender': 'Male'},
    {'name': 'Daisy', 'age': 22, 'gender': 'Female'},
    {'name': 'Eve', 'age': 18, 'gender': 'Female'},
]

# 复制表格
duplicate_table = copy.deepcopy(original_table)

# 指定用于除法的列和除数
column_name = 'age'
divisor_value = original_table[3][column_name]

# 遍历行进行除法操作
for i, row in enumerate(duplicate_table):
    if column_name in row:
        duplicate_table[i][column_name] = row[column_name] / divisor_value
    else:
        print(f"column: {column_name} not found in table")

# 转为DataFrame
duplicate_df = pd.DataFrame(duplicate_table)
print(duplicate_df)

正确解决方案 #

利用Pandas的广播机制，一行代码即可完成，自动适配任意列数：

# 计算每列各值占该列Revenue的比例，保留三位小数
result_df = df.div(df.loc['Revenue'], axis=1).round(3)
print(result_df)

代码说明 #

• df.loc['Revenue']提取所有列的Revenue值，返回一个索引为列名的Series
• df.div(..., axis=1)按列执行除法运算，每列的每个元素都会除以该列对应的Revenue值
• round(3)保留三位小数，与期望输出格式匹配

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