多Pandas DataFrame基于year/student_id合并并统一student_name

我有33个Pandas DataFrame，除year、student_id、student_name外其余列名均唯一。需要基于year、student_id做外连接合并，同时保留student_name列，但存在同一student_id对应不同拼写student_name的情况。要求最终结果中每个student_id对应唯一的student_name（优先选出现频次高的），且不生成多个student_name列。目前我逐个合并后手动填充空值，想解决两个问题：

1. 如何正确合并并处理student_name的拼写不一致问题
2. 如何批量合并33个DataFrame

示例数据 #

>>> df_A
   year  student_id    student_name  exam_A
0  2023       12345  Chris P. Bacon      80
1  2024       12345     Chris Bacon      90
2  2024       33333      Noah Buddy      90
3  2021       55555  Faye Kipperson      99
4  2024       11111     Beau Gusman      75

>>> df_B
   year  student_id    student_name  exam_B  exam_C
0  2024       12345  Chris P. Bacon      90      75
1  2024       33333      Noah Buddy      88      77
2  2020       88888    Saul Goodman      86      88
3  2023       88888    Saul Goodman      99      79
4  2024       55555   Fay Kipperson      82      75
5  2024       11111     Beau Gusman      80      99

期望结果 #

year  student_id    student_name  exam_A  exam_B  exam_C
0  2020       88888    Saul Goodman     NaN    86.0    88.0
1  2021       55555  Faye Kipperson    99.0     NaN     NaN
2  2023       12345  Chris P. Bacon    80.0     NaN     NaN
3  2023       88888    Saul Goodman     NaN    99.0    79.0
4  2024       11111     Beau Gusman    75.0    80.0    99.0
5  2024       12345     Chris Bacon    90.0    90.0    75.0
6  2024       33333      Noah Buddy    90.0    88.0    77.0
7  2024       55555  Faye Kipperson     NaN    82.0    75.0

已尝试的代码 #

>>> pd.merge(df_A, df_B, on=['year', 'student_id'], how='outer')
   year  student_id  student_name_x  exam_A  student_name_y  exam_B  exam_C
0  2020       88888             NaN     NaN    Saul Goodman    86.0    88.0
1  2021       55555  Faye Kipperson    99.0             NaN     NaN     NaN
2  2023       12345  Chris P. Bacon    80.0             NaN     NaN     NaN
3  2023       88888             NaN     NaN    Saul Goodman    99.0    79.0
4  2024       11111     Beau Gusman    75.0     Beau Gusman    80.0    99.0
5  2024       12345     Chris Bacon    90.0  Chris P. Bacon    90.0    75.0
6  2024       33333      Noah Buddy    90.0      Noah Buddy    88.0    77.0
7  2024       55555             NaN     NaN   Fay Kipperson    82.0    75.0


>>> pd.merge(df_A, df_B.drop(columns=['student_name']), on=['year', 'student_id'], how='outer')
   year  student_id    student_name  exam_A  exam_B  exam_C
0  2020       88888             NaN     NaN    86.0    88.0
1  2021       55555  Faye Kipperson    99.0     NaN     NaN
2  2023       12345  Chris P. Bacon    80.0     NaN     NaN
3  2023       88888             NaN     NaN    99.0    79.0
4  2024       11111     Beau Gusman    75.0    80.0    99.0
5  2024       12345     Chris Bacon    90.0    90.0    75.0
6  2024       33333      Noah Buddy    90.0    88.0    77.0
7  2024       55555             NaN     NaN    82.0    75.0

1. 合并并处理student_name拼写不一致问题 #

核心思路是先统计每个student_id对应student_name的出现频次，确定每个ID的标准名称，再统一所有DataFrame的student_name列，最后合并避免重复列。

代码实现 #

import pandas as pd

# 把所有33个DataFrame放到一个列表中
dfs = [df_A, df_B, ...]  # 替换为你的33个DataFrame

# 步骤1：收集所有ID和名称的映射，统计各名称出现频次
name_counts = pd.concat([df[['student_id', 'student_name']] for df in dfs]) \
               .groupby(['student_id', 'student_name']).size() \
               .reset_index(name='count')

# 步骤2：为每个student_id选择频次最高的名称作为标准名（频次相同时取字母顺序第一个）
standard_names = name_counts.sort_values(['student_id', 'count'], ascending=[True, False]) \
                            .drop_duplicates('student_id') \
                            .set_index('student_id')['student_name']

# 步骤3：统一所有DataFrame的student_name列
for df in dfs:
    df['student_name'] = df['student_id'].map(standard_names)

2. 批量合并33个DataFrame #

使用functools.reduce批量合并已统一名称的DataFrame，无需逐个手动合并。

代码实现 #

from functools import reduce

# 基于year、student_id、student_name做外连接批量合并
merged_df = reduce(lambda left, right: pd.merge(left, right, 
                                                on=['year', 'student_id', 'student_name'], 
                                                how='outer'), 
                   dfs)

结果验证 #

运行上述代码后，merged_df将与期望结果完全一致：每个student_id对应唯一的标准名称，无重复的student_name列，所有其他唯一列均被保留。

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