如何将Pandas分组对象转为列表并添加自定义编号列合并为DataFrame

步骤1：预处理数据类型 #

原数据中cost列存在字符串值（如'33'）和空值，先统一转换为数值类型，避免聚合出错：

import pandas as pd

# 原DataFrame定义
list_of_customers =[
[202206,'patrick','lemon','fruit','citrus',10,'tesco'],
[202206,'paul','lemon','fruit','citrus',20,'tesco'],
[202206,'frank','lemon','fruit','citrus',10,'tesco'],
[202206,'jim','lemon','fruit','citrus',20,'tesco'], 
[202206,'wendy','watermelon','fruit','',39,'tesco'],
[202206,'greg','watermelon','fruit','',32,'sainsburys'],
[202209,'wilson','carrot','vegetable','',34,'sainsburys'],    
[202209,'maree','carrot','vegetable','',22,'aldi'],
[202209,'greg','','','','','aldi'], 
[202209,'wilmer','sprite','drink','',22,'aldi'],
[202209,'jed','lime','fruit','citrus',40,'tesco'],    
[202209,'michael','lime','fruit','citrus',12,'aldi'],
[202209,'andrew','','','','33','aldi'], 
[202209,'ahmed','lime','fruit','fruit',33,'aldi'] 
]

df = pd.DataFrame(list_of_customers,columns = ['date','customer','item','item_type','fruit_type','cost','store'])

# 转换cost列为数值类型，无法转换的设为NaN
df['cost'] = pd.to_numeric(df['cost'], errors='coerce')

步骤2：定义筛选条件与编号映射 #

把所有筛选规则和对应的variable_number用字典关联，方便批量处理：

condition_mapping = {
    '01': df['item_type'].isin(['fruit']),       # fruit_variable对应编号
    '02': df['item_type'].isin(['vegetable']),   # vegetable_variable对应编号
    '01a': df['fruit_type'].isin(['citrus']),    # citrus_variable对应编号
    '03': df['item_type'].isin(['poultry'])      # meat_variable对应编号
}

步骤3：批量聚合并合并结果 #

循环处理每个筛选条件，完成分组聚合、添加编号，最后合并所有结果（无匹配数据的变量不会报错，仅不会生成对应行）：

result_list = []

for var_num, condition in condition_mapping.items():
    # 筛选符合条件的数据
    filtered_data = df[condition].copy()
    # 按date和store分组，求和cost（自动忽略NaN）
    aggregated_data = filtered_data.groupby(['date', 'store'], as_index=False)['cost'].sum()
    # 添加variable_number列
    aggregated_data['variable_number'] = var_num
    # 加入结果列表
    result_list.append(aggregated_data)

# 合并所有子结果为最终DataFrame
final_result = pd.concat(result_list, ignore_index=True)

# 查看结果
print(final_result)

可选：保留无匹配数据的变量行 #

如果需要为无匹配结果的变量（如meat_variable）保留一行空数据（仅显示variable_number），可以修改循环逻辑：

result_list = []

for var_num, condition in condition_mapping.items():
    filtered_data = df[condition].copy()
    aggregated_data = filtered_data.groupby(['date', 'store'], as_index=False)['cost'].sum()
    
    if aggregated_data.empty:
        # 无匹配时生成仅含variable_number的空行
        aggregated_data = pd.DataFrame({'variable_number': [var_num]})
    else:
        aggregated_data['variable_number'] = var_num
    
    result_list.append(aggregated_data)

final_result = pd.concat(result_list, ignore_index=True)

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