如何用Pandas GroupBy按国家计算(同意-反对)/总受访者占比？

你可以用以下几种简洁的Pandas方法实现需求：

方法一：分组后自定义聚合函数 #

通过groupby按国家分组，再用apply调用自定义函数计算目标比值：

import pandas as pd

# 构造示例数据
country=['Country A','Country A','Country A','Country B','Country B','Country B']
responses=['Agree','Neutral','Disagree','Agree','Neutral','Disagree']
num_respondents=[10,50,30,58,24,23]
example_df = pd.DataFrame({"Country": country, "Response": responses, "Count": num_respondents})

def net_agree_ratio(group):
    agree = group.loc[group['Response'] == 'Agree', 'Count'].iloc[0]
    disagree = group.loc[group['Response'] == 'Disagree', 'Count'].iloc[0]
    total = group['Count'].sum()
    return (agree - disagree) / total

result = example_df.groupby('Country').apply(net_agree_ratio).reset_index(name='Net_Agree_Ratio')
print(result)

方法二：透视表重塑后直接计算 #

用pivot_table把数据转成宽表，直接通过列运算完成计算，可读性更强：

pivot_df = example_df.pivot_table(
    index='Country',
    columns='Response',
    values='Count',
    fill_value=0
)

pivot_df['Net_Agree_Ratio'] = (pivot_df['Agree'] - pivot_df['Disagree']) / pivot_df.sum(axis=1)
result = pivot_df[['Net_Agree_Ratio']].reset_index()
print(result)

方法三：加权求和法（最简洁高效） #

给不同回复赋予权重（Agree=1，Disagree=-1，Neutral=0），加权求和后直接除以总数：

weight_map = {'Agree': 1, 'Disagree': -1, 'Neutral': 0}
example_df['Weighted'] = example_df['Response'].map(weight_map) * example_df['Count']

result = example_df.groupby('Country').agg(
    weighted_sum=('Weighted', 'sum'),
    total=('Count', 'sum')
).assign(Net_Agree_Ratio=lambda x: x['weighted_sum'] / x['total'])[['Net_Agree_Ratio']].reset_index()

print(result)

三种方法的输出结果一致：

Country  Net_Agree_Ratio
0  Country A         -0.200000
1  Country B          0.315789

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