合并两个DataFrame：按ID匹配+日期筛选+优先主团队方案

需要合并两个DataFrame以判断服务发生时用户所属团队：

• Table_1包含ID、Team Assigned、Date Team Start、Date Team End，记录用户的团队分配及起止时间
• Table_2包含ID、Service Date、Current Primary Team，记录服务发生日期及当前主团队

核心规则：

1. 按ID匹配两个表的记录
2. 筛选Service Date处于Date Team Start与Date Team End之间的团队记录
3. 若存在多个符合日期条件的团队，优先选择与Current Primary Team一致的团队

输入示例 #

Table_1

Table_2

期望输出 #

现有问题 #

使用pd.merge(Table_1, Table_2, on=['ID'], how='outer')会产生大量重复行，无法精准筛选每个服务日期对应的正确团队，也无法统计各团队的服务日期数量。

步骤1：预处理日期格式 #

首先统一日期格式，将Table_1中的Current替换为远未来日期（确保覆盖所有服务日期），并将所有日期列转换为datetime类型，方便后续区间比较。

步骤2：交叉合并并筛选有效记录 #

按ID合并两个表后，筛选出Service Date落在团队起止日期范围内的记录。

步骤3：设置优先级并选择最优团队 #

给与Current Primary Team一致的团队设置更高优先级，按ID和Service Date分组后，选择优先级最高的团队作为最终结果。

完整代码 #

import pandas as pd

# 模拟输入数据（实际使用时替换为你的DataFrame）
Table_1 = pd.DataFrame({
    'ID': [23,23,23,14,14,07,07,07],
    'Team Assigned': ['Red','Blue','Green','Green','Purple','Blue','Red','Purple'],
    'Date Team Start': ['2022-09-01','2022-08-01','2022-09-27','2022-08-01','2022-08-15','2022-07-03','2022-07-03','2022-05-01'],
    'Date Team End': ['2022-09-29','2022-09-15','Current','2022-08-17','Current','Current','2022-07-05','2022-06-24']
})

Table_2 = pd.DataFrame({
    'ID': [07,07,23,23,14],
    'Service Date': ['2022-08-01','2022-05-03','2022-08-15','2022-09-27','2022-08-12'],
    'Current Primary Team': ['Blue','Blue','Green','Green','Purple']
})

# 预处理日期：替换Current为远未来日期，转换为datetime类型
Table_1['Date Team End'] = Table_1['Date Team End'].replace('Current', '9999-12-31')
for col in ['Date Team Start', 'Date Team End']:
    Table_1[col] = pd.to_datetime(Table_1[col])
Table_2['Service Date'] = pd.to_datetime(Table_2['Service Date'])

# 按ID交叉合并两个表
merged = pd.merge(Table_1, Table_2, on='ID', how='inner')

# 筛选服务日期在团队起止区间内的记录
filtered = merged[(merged['Service Date'] >= merged['Date Team Start']) & 
                  (merged['Service Date'] <= merged['Date Team End'])]

# 设置优先级：与Current Primary Team一致的团队优先级为1，否则为0
filtered['priority'] = filtered.apply(lambda row: 1 if row['Team Assigned'] == row['Current Primary Team'] else 0, axis=1)

# 按ID、Service Date分组，取优先级最高的团队（降序排序后取第一条）
final_result = (filtered.sort_values('priority', ascending=False)
                .groupby(['ID', 'Service Date', 'Current Primary Team'])
                .first()
                .reset_index())

# 整理输出列
final_result = final_result.rename(columns={'Team Assigned': 'Assumed Primary Team'})
final_result = final_result[['ID', 'Service Date', 'Current Primary Team', 'Assumed Primary Team']]

# 打印结果
print(final_result)

代码说明 #

1. 日期预处理：将Current替换为9999-12-31，确保所有当前有效的团队分配都能被正确匹配；转换日期类型是为了支持区间比较操作。
2. 交叉合并与筛选：通过inner合并确保只保留存在匹配ID的记录，再通过布尔索引筛选出符合日期条件的团队。
3. 优先级设置与分组选择：通过priority字段标记优先团队，排序后分组取第一条即可得到每个服务日期对应的最优团队。
4. 结果整理：重命名列并保留所需字段，最终结果与期望输出一致。

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