Pandas动态选择列执行GroupBy分组报错求助

问题场景 #

需要实现用户可选若干列（1-6个），加上固定的category、score、mop三列作为分组依据，执行GroupBy聚合操作，但之前三种写法均报错。

错误代码及原因分析 #

代码1及报错 #

categorical_fields = []
card_name_match=input("Do you want to include card name first name matches passenger first name: y/n")
if card_name_match=="y":
    categorical_fields.append("name_match")
fare_class_1_cat=input("Do you want to include Fare Class 1 Category: y/n")
if fare_class_1_cat=="y":
    categorical_fields.append("FARECLASS1_cat")
fare_class_2_cat=input("Do you want to include Fare Class 2 Category: y/n")
if fare_class_2_cat=="y":
    categorical_fields.append("FARECLASS2_cat")
distance_cat=input("Do you want to include Distance: y/n")
if distance_cat=="y":
    categorical_fields.append("distance_category")
int_or_domestic=input("Do you want to include if flight was international or domestic: y/n")
if int_or_domestic=="y":
    categorical_fields.append("international_or_domestic")
journey_type=input("Do you want to include journey type of one way, round trip, or different 2nd arrival destination: y/n")
if journey_type=="y":
    categorical_fields.append("dep_to_arr")

# 执行语句
airline_score = airline.groupby([categorical_fields,'category','score','mop']).agg(count=('fs_sham','count'),dollars=('fs_dollars','sum')).reset_index()

报错信息：ValueError: Grouper and axis must be same length
错误原因：categorical_fields本身是列表，将其放入另一个列表后形成了嵌套结构（例如[[col1, col2], 'category', ...]），Pandas无法识别这种嵌套列表作为分组列。

代码2及报错 #

categorical_fields.extend(['category','score','mop'])
group_columns = airline.groupby(categorical_fields)
airline_score = airline.groupby(group_columns).agg(count=('fs_sham','count'),dollars=('fs_dollars','sum')).reset_index()

报错信息：ValueError: Grouper for '<class 'pandas.core.groupby.generic.DataFrameGroupBy'>' not 1-dimensional
错误原因：group_columns已经是GroupBy对象，groupby()方法只接受列名列表、列对象等合法分组依据，不能传入GroupBy实例。

代码3及报错 #

categorical_fields.extend(['category','score','mop'])
airline_score = airline.groupby(airline.columns.isin([categorical_fields])).agg(count=('fs_sham','count'),dollars=('fs_dollars','sum')).reset_index()

报错信息：ValueError: Grouper and axis must be same length
错误原因：airline.columns.isin([categorical_fields])传入了嵌套列表，返回的布尔数组长度等于DataFrame的列数，但分组时该数组长度与数据行长度不匹配，导致报错。

正确实现方法 #

核心是将用户选择的列列表与固定列列表合并为一维列名列表，直接传给groupby()方法：

categorical_fields = []
card_name_match=input("Do you want to include card name first name matches passenger first name: y/n")
if card_name_match=="y":
    categorical_fields.append("name_match")
fare_class_1_cat=input("Do you want to include Fare Class 1 Category: y/n")
if fare_class_1_cat=="y":
    categorical_fields.append("FARECLASS1_cat")
fare_class_2_cat=input("Do you want to include Fare Class 2 Category: y/n")
if fare_class_2_cat=="y":
    categorical_fields.append("FARECLASS2_cat")
distance_cat=input("Do you want to include Distance: y/n")
if distance_cat=="y":
    categorical_fields.append("distance_category")
int_or_domestic=input("Do you want to include if flight was international or domestic: y/n")
if int_or_domestic=="y":
    categorical_fields.append("international_or_domestic")
journey_type=input("Do you want to include journey type of one way, round trip, or different 2nd arrival destination: y/n")
if journey_type=="y":
    categorical_fields.append("dep_to_arr")

# 合并用户选择列与固定列，生成一维分组列列表
group_cols = categorical_fields + ['category', 'score', 'mop']

# 执行分组聚合
airline_score = airline.groupby(group_cols).agg(
    count=('fs_sham', 'count'),
    dollars=('fs_dollars', 'sum')
).reset_index()

解释：使用+号合并两个列表，得到的是一维的列名集合，Pandas可以直接识别并按这些列完成分组聚合操作。

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