如何在Pandas命名聚合中实现带条件的聚合计算？

现有如下Pandas DataFrame：

director_master_id  company_master_id        designation date_cessation appointment_original_date  some_col            director_name appt_chng_desig_date        t_designation t_dir_category
0             2601721            2465280           Director            NaT                2020-08-21  0000000              VINEET                  NaT             director       promoter
1             1111111            2465280           Director            NaT                2021-09-30  7129633              VIJAY                    NaT  additional director   professional
2             2222222            2465280           Director            NaT                2022-03-06  9500698              SACHDEV                 NaT  additional director   professional
3             3333333            2465280           Director            NaT                2023-01-03  9748791              SHUVI                   NaT  additional director   professional
4              444444            2465280           Director            NaT                2022-09-28  1469375            CHAKRABORTY               NaT             director    independent
5              933052            2465280           Director            NaT                2023-02-18  3565167               ANUP                   NaT                  NaN            NaN
6             2911635            2465280  Managing Director            NaT                2020-08-21  7767248              KUMAR                    NaT    managing director       promoter
7              779440            2465280           Director            NaT                2021-09-30  7298703            TYLER                      NaT  additional director   professional
8              804512            2465280           Director            NaT                2021-09-30  3559152            KARTIK                     NaT  additional director   professional
9               90320            2465280           Director            NaT                2021-09-30   177699            GOPAL                      NaT  additional director   professional

需要在单个Pandas groupby命名聚合代码块中完成以下计算：

• 统计总董事数量num_of_directors
• 统计t_dir_category等于promoter的董事数量num_of_promoter_directors（预期结果：2）
• 收集t_designation为managing director的董事姓名列表managing_directors（预期结果：[KUMAR]）

当前代码运行时出现KeyError: 't_dir_category'错误：

directors_info = director_history_details.groupby('company_master_id').agg(
                num_of_directors=('director_master_id', 'count'),
                num_of_promoter_directors=('director_master_id', lambda x: x[x['t_dir_category'] == 'promoter'].count()),
                managing_directors=('director_name',
                                    lambda x: x[x['t_designation'] == 'managing director']['director_name'].unique())
)

希望在单个聚合代码块中实现，预期输出：

company_master_id  num_of_directors  num_of_promoter_directors managing_directors
0            2465280                10                          2  [KUMAR]

你之前的代码报错是因为：当在agg中指定单个列（比如('director_master_id', lambda x: ...)）时，lambda参数x是该列的Series对象，而非整个分组的DataFrame，因此无法通过x['t_dir_category']访问其他列，触发KeyError。

以下两种方式都能在单个代码块中完成所有计算：

方法1：直接针对分组DataFrame编写聚合逻辑 #

directors_info = director_history_details.groupby('company_master_id').agg(
    num_of_directors=('director_master_id', 'count'),
    num_of_promoter_directors=lambda x: x[x['t_dir_category'] == 'promoter']['director_master_id'].count(),
    managing_directors=lambda x: x[x['t_designation'] == 'managing director']['director_name'].tolist()
).reset_index()

方法2：使用apply构造结果Series #

directors_info = director_history_details.groupby('company_master_id').apply(
    lambda x: pd.Series({
        'num_of_directors': x['director_master_id'].count(),
        'num_of_promoter_directors': x[x['t_dir_category'] == 'promoter'].shape[0],
        'managing_directors': x[x['t_designation'] == 'managing director']['director_name'].tolist()
    })
).reset_index()

• 两种方法均无需拆分计算后合并，直接在单个代码块完成需求
• 方法1中，未指定列的聚合项会默认传入分组后的整个DataFrame，可自由访问所有列
• 方法2通过apply直接处理分组DataFrame，返回的Series会自动转为聚合后的列
• 调用reset_index()可将company_master_id从索引转为普通列，匹配预期输出格式

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