如何基于Pandas datetime列±30分钟范围匹配合并DataFrame

需求：现有两个DataFrame，df1包含Reported Date（datetime类型）和order列，df2包含lastoccurrence（datetime类型）和orders列。需要筛选出df2中满足以下条件的记录：

• orders与df1的order值相等
• lastoccurrence处于对应Reported Date的±30分钟范围内

输入示例 #

df1 #

Reported Date            order          
12/14/2022  6:10:32 PM        A
9/15/2022  2:45:57 AM         B
9/15/2022  11:08:26 AM        C

df2 #

lastoccurrence          orders
12/14/2022  6:15:35 PM        A
12/14/2022  6:00:35 PM        A
12/14/2022  5:40:35 PM        A
12/14/2022  6:40:35 PM        A
12/14/2022  6:10:32 PM        B
9/15/2022  11:20:26 AM        C
9/15/2022  11:08:26 AM        A

期望输出 #

df3 #

lastoccurrence          orders
12/14/2022  6:15:35 PM        A
12/14/2022  6:00:35 PM        A
12/14/2022  5:40:35 PM        A
12/14/2022  6:40:35 PM        A
9/15/2022  11:20:26 AM        C

实现代码 #

import pandas as pd

# 若列不是datetime类型，先转换（如果已经是则跳过）
# df1['Reported Date'] = pd.to_datetime(df1['Reported Date'])
# df2['lastoccurrence'] = pd.to_datetime(df2['lastoccurrence'])

# 按订单号关联两个表
merged_df = pd.merge(df2, df1, left_on='orders', right_on='order', how='inner')

# 计算时间差绝对值，筛选出在±30分钟内的记录
time_diff = abs(merged_df['lastoccurrence'] - merged_df['Reported Date'])
df3 = merged_df[time_diff <= pd.Timedelta(minutes=30)][['lastoccurrence', 'orders']]

# 重置索引（可选）
df3 = df3.reset_index(drop=True)

print(df3)

代码说明 #

1. 先用merge把df2和df1按订单号（orders和order）做内连接，确保订单号匹配的记录被关联；
2. 计算关联后两条时间列的差值绝对值，筛选出时间差不超过30分钟的行；
3. 提取df2的lastoccurrence和orders列，得到最终结果。

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