如何在Pandas按Region和Year GroupBy后添加Imputed统计列？

现有如下结构的DataFrame，包含Region、Country、Imputed、Year、Price列，示例数据代码如下：

data1 = {'Region': ['Africa','Africa','Africa','Africa','Africa','Africa','Africa','Africa','Africa','Africa','Africa','Africa'],
         'Country': ['South Africa','South Africa','South Africa','South Africa','Nigeria','Nigeria','Nigeria','Nigeria','Kenya','Kenya','Kenya','Kenya'],
         'Imputed': ['No','No','Yes','No','Yes','Yes','No','No','Yes','No','No','No'],
         'Year': [2016, 2017, 2018, 2019,2016, 2017, 2018, 2019,2016, 2017, 2018, 2019],
         'Price': [500, 400, 432,450,750,780,816,890,212,376,415,430]}
df = pd.DataFrame(data1)

需要按Region和Year执行groupby操作，计算各年度的区域平均价格，目前已编写代码：

df = df.groupby(['Region','Year'])['Price'].mean()

同时希望添加一列，统计该分组内Imputed值为Yes的条目数占总条目数的比例，格式如“2/3 Components Imputed”，请问如何实现？

可以使用groupby结合agg方法，一次性完成平均价格计算和自定义统计列的生成，具体代码如下：

import pandas as pd

data1 = {'Region': ['Africa','Africa','Africa','Africa','Africa','Africa','Africa','Africa','Africa','Africa','Africa','Africa'],
         'Country': ['South Africa','South Africa','South Africa','South Africa','Nigeria','Nigeria','Nigeria','Nigeria','Kenya','Kenya','Kenya','Kenya'],
         'Imputed': ['No','No','Yes','No','Yes','Yes','No','No','Yes','No','No','No'],
         'Year': [2016, 2017, 2018, 2019,2016, 2017, 2018, 2019,2016, 2017, 2018, 2019],
         'Price': [500, 400, 432,450,750,780,816,890,212,376,415,430]}
df = pd.DataFrame(data1)

# 分组聚合，同时计算平均价格和自定义统计列
result = df.groupby(['Region', 'Year']).agg(
    Avg_Price=('Price', 'mean'),
    Imputed_Ratio=(
        'Imputed',
        lambda x: f"{sum(x == 'Yes')}/{len(x)} Components Imputed"
    )
).reset_index()

print(result)

代码说明 #

1. 用groupby(['Region', 'Year'])按区域和年份完成分组；
2. agg方法支持多列多操作的聚合：
   • Avg_Price=('Price', 'mean')：计算分组内的平均价格，并命名为Avg_Price；
   • Imputed_Ratio列通过lambda函数实现：统计分组内Imputed为Yes的条目数，除以分组总条目数，再拼接成要求的字符串格式；
3. reset_index()将分组的索引（Region和Year）转为普通列，方便后续查看与处理。

输出结果 #

执行代码后会得到如下结果：

Region  Year  Avg_Price          Imputed_Ratio
0  Africa  2016    487.333  2/3 Components Imputed
1  Africa  2017    518.667  1/3 Components Imputed
2  Africa  2018    554.333  1/3 Components Imputed
3  Africa  2019    590.000  0/3 Components Imputed

内容的提问来源于stack exchange，提问作者A.N.