如何合并经Groupby拆分并处理后的DataFrames？

需求说明 #

为每个部门确定主题专家（SME）：若部门内80%员工向某管理者汇报，则该管理者为SME，否则对应字段设为NaN。已实现单部门的SME识别逻辑，但分组处理后无法合并所有子DataFrame，导出时仅能得到单个部门的数据。

当前代码 #

import pandas as pd
import openpyxl

# 读取数据
df = pd.read_excel('C:/Users/WorleyMG/OneDrive - Crowe LLP/Desktop/Test/Test1.xlsx')

# 按部门ID拆分数据
df_group = df.groupby('Department ID')
df_split = [df_group.get_group(x) for x in df_group.groups]

# 识别各部门SME
for df in df_split:
    df_count_mgr_id = df['Manager ID'].value_counts()
    df2 = pd.DataFrame(df_count_mgr_id)
    df2['percent'] = (df2['Manager ID'] / df2['Manager ID'].sum())
    df2 = df2.reset_index()
    df2 = df2.rename(columns={'index':'manager id','Manager ID':'count'})
    df2.loc[df2['percent'] >= .8, 'SME'] = 'This is the SME'
    df2 = df2[df2['SME'] == 'This is the SME']
    df = pd.concat((df2['manager id'],df), axis=0)
    df = df.rename(columns={0: 'manager id'})
    copy_down = df['manager id'].iloc[0]
    df['manager id']=copy_down
    df = df.dropna(subset=['ID'])
    print(df)

当前输出 #

ID     Name  Department ID  ...  3rd Manager ID 3rd Manager Name  manager id
0  7838.0   Batman        11640.0  ...             NaN              NaN      7838.0
1  8636.0    Jacob        11640.0  ...          5356.0          William      7838.0
2  7618.0   Jingle        11640.0  ...          5356.0          William      7838.0
3  7848.0   Heimer        11640.0  ...          5356.0          William      7838.0
4  7872.0  Schmidt        11640.0  ...          5356.0          William      7838.0
5  7532.0    Sarah        11640.0  ...          5356.0          William      7838.0
6  7878.0   Connor        11640.0  ...          5356.0          William      7838.0
7  8500.0   Austin        11640.0  ...          5356.0          William      7838.0
8  7926.0   Powers        11640.0  ...          5356.0          William      7838.0

[9 rows x 10 columns]
        ID    Name  Department ID  ...  3rd Manager ID 3rd Manager Name  manager id
9   8222.0  Freddy        12122.0  ...          7504.0            Chris         NaN
10  9102.0  Kruger        12122.0  ...          7504.0            Chris         NaN
11  6020.0    Tony        12122.0  ...          5356.0          William         NaN
12  7608.0   Stark        12122.0  ...          5356.0          William         NaN
13  7920.0   Bruce        12122.0  ...          5356.0          William         NaN
14  6636.0   Wayne        12122.0  ...          5356.0          William         NaN
    
[6 rows x 10 columns]

期望输出 #

ID     Name  Department ID  ...  3rd Manager ID 3rd Manager Name  manager id
0  7838.0   Batman        11640.0  ...             NaN              NaN      7838.0
1  8636.0    Jacob        11640.0  ...          5356.0          William      7838.0
2  7618.0   Jingle        11640.0  ...          5356.0          William      7838.0
3  7848.0   Heimer        11640.0  ...          5356.0          William      7838.0
4  7872.0  Schmidt        11640.0  ...          5356.0          William      7838.0
5  7532.0    Sarah        11640.0  ...          5356.0          William      7838.0
6  7878.0   Connor        11640.0  ...          5356.0          William      7838.0
7  8500.0   Austin        11640.0  ...          5356.0          William      7838.0
8  7926.0   Powers        11640.0  ...          5356.0          William      7838.0
9   8222.0  Freddy        12122.0  ...          7504.0            Chris         NaN
10  9102.0  Kruger        12122.0  ...          7504.0            Chris         NaN
11  6020.0    Tony        12122.0  ...          5356.0          William         NaN
12  7608.0   Stark        12122.0  ...          5356.0          William         NaN
13  7920.0   Bruce        12122.0  ...          5356.0          William         NaN
14  6636.0   Wayne        12122.0  ...          5356.0          William         NaN
    
[15 rows x 10 columns]

解决方案 #

原代码核心问题是：循环处理每个子DataFrame时，未将结果保存到统一容器，每次循环的df变量仅临时覆盖，最终无法保留所有部门数据。

修改方式：创建空列表存储每个处理后的子DataFrame，最后用pd.concat合并所有元素。

修改后的代码 #

import pandas as pd
import openpyxl

# 读取数据
df = pd.read_excel('C:/Users/WorleyMG/OneDrive - Crowe LLP/Desktop/Test/Test1.xlsx')

# 按部门ID拆分数据
df_group = df.groupby('Department ID')
df_split = [df_group.get_group(x) for x in df_group.groups]

# 创建空列表存储处理后的子DataFrame
processed_dfs = []

# 识别各部门SME并收集结果
for sub_df in df_split:
    df_count_mgr_id = sub_df['Manager ID'].value_counts()
    df2 = pd.DataFrame(df_count_mgr_id)
    df2['percent'] = (df2['Manager ID'] / df2['Manager ID'].sum())
    df2 = df2.reset_index()
    df2 = df2.rename(columns={'index':'manager id','Manager ID':'count'})
    df2.loc[df2['percent'] >= .8, 'SME'] = 'This is the SME'
    df2 = df2[df2['SME'] == 'This is the SME']
    
    # 处理当前部门的SME字段
    merged_df = pd.concat((df2['manager id'], sub_df), axis=0)
    merged_df = merged_df.rename(columns={0: 'manager id'})
    # 若有符合条件的SME则填充，否则留空
    if not merged_df['manager id'].isna().all():
        copy_down = merged_df['manager id'].iloc[0]
        merged_df['manager id'] = copy_down
    merged_df = merged_df.dropna(subset=['ID'])
    
    # 将处理后的子DataFrame加入列表
    processed_dfs.append(merged_df)

# 合并所有处理后的子DataFrame
final_df = pd.concat(processed_dfs, ignore_index=True)

# 打印结果并导出
print(final_df)
final_df.to_csv('your_output_path.csv', index=False)

关键修改点 #

1. 新增processed_dfs = []作为容器，存储每个部门处理后的结果
2. 循环变量名改为sub_df，避免和全局df混淆
3. 处理完每个部门后，用processed_dfs.append(merged_df)保存结果
4. 最后用pd.concat(processed_dfs, ignore_index=True)合并所有数据，ignore_index=True重置索引避免重复
5. 添加导出CSV的代码，确保完整数据被保存

内容的提问来源于stack exchange，提问作者Matt W.