如何用Pandas高效计算三维交叉表中的各类百分比？

问题背景 #

现有一组调查数据，受访者分为A、B、C三组，回答是/否问题，涵盖2023年8-10月数据，需要计算三类百分比：

• 组内垂直占比：同一组同一月份内，是/否回答的占比总和为100%
• 跨组垂直占比：同一月份内，所有组的是/否回答占比总和为100%
• 水平占比：同一组同一回答下，各月份的占比总和为100%

目标是生成包含group、answer、month、count及三类百分比的DataFrame。

现有实现方式 #

通过三次创建crosstab，堆叠索引后多次合并的方式实现，代码如下：

import pandas as pd
import numpy as np

agg_counts=[
    {"group":'A','answer':'yes','month':'Aug 2023','count':12},
    {"group":'A','answer':'yes','month':'Sep 2023','count':5},
    {"group":'A','answer':'yes','month':'Oct 2023','count':4},
    {"group":'A','answer':'no','month':'Aug 2023','count':9},
    {"group":'A','answer':'no','month':'Sep 2023','count':10},
    {"group":'A','answer':'no','month':'Oct 2023','count':3},
    {"group":'B','answer':'yes','month':'Aug 2023','count':21},
    {"group":'B','answer':'yes','month':'Sep 2023','count':3},
    {"group":'B','answer':'yes','month':'Oct 2023','count':6},
    {"group":'B','answer':'no','month':'Aug 2023','count':8},
    {"group":'B','answer':'no','month':'Sep 2023','count':9},
    {"group":'B','answer':'no','month':'Oct 2023','count':2},
    {"group":'C','answer':'yes','month':'Aug 2023','count':11},
    {"group":'C','answer':'yes','month':'Sep 2023','count':4},
    {"group":'C','answer':'yes','month':'Oct 2023','count':9},
    {"group":'C','answer':'no','month':'Aug 2023','count':8},
    {"group":'C','answer':'no','month':'Sep 2023','count':1},
    {"group":'C','answer':'no','month':'Oct 2023','count':13}
]

df=pd.DataFrame(agg_counts)

vp_it=pd.crosstab(
    columns=[df['group'],df['month']],
    index=[df['answer']],
    values=df['count'],
    aggfunc=np.sum,
    normalize='columns'
    )
df_vp_it=vp_it.stack([0,1]).reset_index().rename(columns={0:"vp_inner"})

vp_ot=pd.crosstab(
    columns=[df['month']],
    index=[df['group'],df['answer']],
    values=df['count'],
    aggfunc=np.sum,
    normalize='columns'
    )
df_vp_ot=vp_ot.stack(0).reset_index().rename(columns={0:"vp_outer"})

hp=pd.crosstab(
    columns=[df['month']],
    index=[df['group'],df['answer']],
    values=df['count'],
    aggfunc=np.sum,
    normalize='index'
    )
df_hp=hp.stack([0]).reset_index().rename(columns={0:"hp"})

merge_columns=['group','answer','month']
df_merged=df.merge(df_vp_it,on=merge_columns).merge(df_vp_ot,on=merge_columns).merge(df_hp,on=merge_columns)

更高效的实现方式 #

利用pandas的groupby+transform方法，直接在原DataFrame上计算各类百分比，无需多次创建交叉表和合并操作，代码更简洁且性能更优：

import pandas as pd

agg_counts=[
    {"group":'A','answer':'yes','month':'Aug 2023','count':12},
    {"group":'A','answer':'yes','month':'Sep 2023','count':5},
    {"group":'A','answer':'yes','month':'Oct 2023','count':4},
    {"group":'A','answer':'no','month':'Aug 2023','count':9},
    {"group":'A','answer':'no','month':'Sep 2023','count':10},
    {"group":'A','answer':'no','month':'Oct 2023','count':3},
    {"group":'B','answer':'yes','month':'Aug 2023','count':21},
    {"group":'B','answer':'yes','month':'Sep 2023','count':3},
    {"group":'B','answer':'yes','month':'Oct 2023','count':6},
    {"group":'B','answer':'no','month':'Aug 2023','count':8},
    {"group":'B','answer':'no','month':'Sep 2023','count':9},
    {"group":'B','answer':'no','month':'Oct 2023','count':2},
    {"group":'C','answer':'yes','month':'Aug 2023','count':11},
    {"group":'C','answer':'yes','month':'Sep 2023','count':4},
    {"group":'C','answer':'yes','month':'Oct 2023','count':9},
    {"group":'C','answer':'no','month':'Aug 2023','count':8},
    {"group":'C','answer':'no','month':'Sep 2023','count':1},
    {"group":'C','answer':'no','month':'Oct 2023','count':13}
]

df = pd.DataFrame(agg_counts)

# 计算组内垂直占比：按group和month分组，count占该组该月总和的比例
df['vp_inner'] = df['count'] / df.groupby(['group', 'month'])['count'].transform('sum')

# 计算跨组垂直占比：按month分组，count占该月总和的比例
df['vp_outer'] = df['count'] / df.groupby(['month'])['count'].transform('sum')

# 计算水平占比：按group和answer分组，count占该组该回答总和的比例
df['hp'] = df['count'] / df.groupby(['group', 'answer'])['count'].transform('sum')

# 可选：将百分比保留四位小数
df[['vp_inner', 'vp_outer', 'hp']] = df[['vp_inner', 'vp_outer', 'hp']].round(4)

优势说明 #

• 代码更简洁：无需多次创建交叉表、堆叠索引和合并DataFrame，直接在原数据上完成所有计算
• 性能更优：减少了多次数据转换和合并的开销，数据量越大，性能提升越明显
• 可读性更强：分组逻辑直接对应百分比的计算规则，便于理解和后期维护

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