如何基于IP地址与时间戳条件合并两个Pandas DataFrame？

需要将DataFrame1（df1）中的serial_no和module字段关联到DataFrame2（df2）中，关联依据为IPaddress列。基础的merge方法无法满足需求，还需结合测试发生的时间戳（datetime）进行匹配，具体场景如下：

• CASE1：IP地址192.178.1.92在df1中仅有一个serial_no（8212），因此df2中该IP的所有测试记录均需匹配此serial_no；
• CASE2：IP地址192.178.1.90在df1中有两个serial_no，其中8227生效至2022年7月4日15:45:34，8228自2022年7月4日15:45:35起生效，故df2中Test2需匹配8227，Test3需匹配8228；
• CASE3：IP地址192.178.1.94在df1中有三个serial_no，df2中Test4、Test5、Test6需分别匹配对应生效时间的8197、8198、8199。

数据示例 #

import pandas as pd

# df1 设备信息表
df1 = pd.DataFrame({
    'module': ['ABC', 'PQR', 'PQR', 'XYZ', 'XYZ', 'XYZ'],
    'serial_no': [8212, 8227, 8228, 8197, 8198, 8199],
    'IPaddress': ['192.178.1.92', '192.178.1.90', '192.178.1.90', '192.178.1.94', '192.178.1.94', '192.178.1.94'],
    'datetime': ['7/4/2022 15:44:11', '7/4/2022 13:10:20', '7/4/2022 15:45:35', '7/4/2022 13:09:09', '7/4/2022 15:45:53', '7/4/2022 17:33:42']
})

# df2 测试记录表
df2 = pd.DataFrame({
    'Test': ['Test1', 'Test2', 'Test3', 'Test4', 'Test5', 'Test6'],
    'IPaddress': ['192.178.1.92', '192.178.1.90', '192.178.1.90', '192.178.1.94', '192.178.1.94', '192.178.1.94'],
    'datetime': ['7/4/2022 21:44:11', '7/4/2022 14:25:18', '7/4/2022 15:45:35', '7/4/2022 13:09:09', '7/4/2022 15:45:53', '7/4/2022 17:33:42']
})

期望输出 #

Test    IPaddress        datetime           SLNO  Module
Test1   192.178.1.92    7/4/2022 21:44:11   8212    ABC
Test2   192.178.1.90    7/4/2022 14:25:18   8227    PQR
Test3   192.178.1.90    7/4/2022 15:45:35   8228    PQR
Test4   192.178.1.94    7/4/2022 13:09:09   8197    XYZ
Test5   192.178.1.94    7/4/2022 15:45:53   8198    XYZ
Test6   192.178.1.94    7/4/2022 17:33:42   8199    XYZ

解决方案 #

使用Pandas的merge_asof方法可实现按IP地址匹配，同时基于时间戳找到对应生效的设备信息，步骤如下：

1. 转换时间列类型：将两个DataFrame的datetime列转换为Pandas的datetime类型，确保可进行时间比较
2. 排序数据：merge_asof要求两个DataFrame按关联键（IPaddress）和时间列（datetime）排序
3. 执行关联匹配：使用merge_asof按IP匹配，同时匹配测试时间大于等于设备生效时间的最近记录

# 转换datetime列为datetime类型
df1['datetime'] = pd.to_datetime(df1['datetime'])
df2['datetime'] = pd.to_datetime(df2['datetime'])

# 按IPaddress和datetime排序
df1_sorted = df1.sort_values(['IPaddress', 'datetime'])
df2_sorted = df2.sort_values(['IPaddress', 'datetime'])

# 使用merge_asof进行关联
result = pd.merge_asof(
    df2_sorted,
    df1_sorted[['IPaddress', 'datetime', 'serial_no', 'module']],
    on='datetime',
    by='IPaddress',
    direction='backward'  # 取测试时间之前或等于的最近生效记录
)

# 重命名列并调整顺序，匹配期望输出
result = result.rename(columns={'serial_no': 'SLNO', 'module': 'Module'})
result = result[['Test', 'IPaddress', 'datetime', 'SLNO', 'Module']]

# 格式化时间列输出（可选，匹配示例格式）
result['datetime'] = result['datetime'].dt.strftime('%m/%d/%Y %H:%M:%S')

print(result)

运行上述代码后，输出结果将与期望输出一致。

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