如何按班级及入职日期匹配规则合并两个Pandas DataFrame？

问题描述 #

现有两个DataFrame（df1和df2），需按班级匹配，并根据入职日期规则合并得到指定格式结果：

输入数据 #

df1（教师信息）：

class      teacher  age instructor_joining_date
   A        mark    50  2024-01-20 07:18:29.599
   A        john    45  2024-05-08 05:31:21.379

df2（学生班级统计信息）：

class   count   student_joining_date
A          1    2024-05-17 01:05:58.072
A         50    2024-04-10 10:39:06.608
A         75    2024-04-05 09:49:07.246

合并规则 #

将df2与df1按班级匹配，为每个学生行匹配同班级中入职日期最晚且不晚于学生入职日期的教师信息，最终输出格式如下：

class   count   student_joining_date      teacher   age
A         1    2024-05-17 01:05:58.072    john       45
A        50    2024-04-10 10:39:06.608    mark       50
A        75    2024-04-05 09:49:07.246    mark       50

解决方案 #

使用Pandas完成合并，代码如下：

import pandas as pd

# 构造输入DataFrame
df1 = pd.DataFrame({
    'class': ['A', 'A'],
    'teacher': ['mark', 'john'],
    'age': [50, 45],
    'instructor_joining_date': ['2024-01-20 07:18:29.599', '2024-05-08 05:31:21.379']
})

df2 = pd.DataFrame({
    'class': ['A', 'A', 'A'],
    'count': [1, 50, 75],
    'student_joining_date': ['2024-05-17 01:05:58.072', '2024-04-10 10:39:06.608', '2024-04-05 09:49:07.246']
})

# 转换日期列为datetime类型（必须步骤，否则无法正确比较日期）
df1['instructor_joining_date'] = pd.to_datetime(df1['instructor_joining_date'])
df2['student_joining_date'] = pd.to_datetime(df2['student_joining_date'])

# 按班级交叉连接两个DataFrame，得到所有同班级的学生-教师组合
merged = df2.merge(df1, on='class', how='inner')

# 筛选出学生入职日期晚于/等于教师入职日期的有效组合
filtered = merged[merged['student_joining_date'] >= merged['instructor_joining_date']]

# 对每个学生行，保留入职日期最晚的教师信息
result = filtered.sort_values('instructor_joining_date') \
                 .groupby(['class', 'count', 'student_joining_date']) \
                 .last() \
                 .reset_index()

# 调整列顺序以匹配目标输出
result = result[['class', 'count', 'student_joining_date', 'teacher', 'age']]

print(result)

代码解释 #

• 日期类型转换：将字符串格式的日期转为datetime类型，确保可以进行大小比较。
• 交叉连接：通过merge按class关联，得到同班级下所有学生与教师的组合。
• 筛选有效组合：只保留学生入职日期不早于教师入职日期的行。
• 分组取最优教师：按学生的唯一标识（班级、count、学生入职日期）分组，排序后取最后一行（即入职日期最晚的符合条件的教师）。
• 列顺序调整：将结果列调整为目标输出的顺序。

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