如何基于时间范围合并两个Pandas DataFrame并匹配事件？

问题描述 #

需要合并两个Pandas DataFrame：一个包含时间范围列（start、end）与事件列（event），另一个包含时间戳列（timestamp）与测量值列（measured value），目标是将事件信息匹配到对应时间戳的测量值行中。

数据示例 #

import pandas as pd

df_1 = pd.DataFrame(
    columns=["timestamp", "measured value"],
    data=[
        (pd.to_datetime("2012-07-16 23:23:50"), 2.1),
        (pd.to_datetime("2012-08-16 02:23:50"), 4),
        (pd.to_datetime("2015-07-16 12:23:50"), 2),
        (pd.to_datetime("2018-08-16 20:23:50"), 1.2),
    ],
)
df_2 = pd.DataFrame(
    columns=["start", "end", "event"],
    data=[
        (
            pd.to_datetime("2015-06-16 12:23:50"),
            pd.to_datetime("2015-08-16 12:23:50"),
            True,
        ),
    ],
)

尝试过的方法 #

df_2.index = pd.IntervalIndex.from_arrays(df_2["start"], df_2["end"], closed="both")
df_1.assign(events = df_2['event'])

执行结果：

timestamp  measured value events
0 2012-07-16 23:23:50    2.1    NaN
1 2012-08-16 02:23:50    4.0    NaN
2 2015-07-16 12:23:50    2.0    NaN
3 2018-08-16 20:23:50    1.2    NaN

此方法仅为df_2设置了IntervalIndex，但未执行时间戳与时间范围的匹配逻辑，导致所有events值均为NaN。

期望输出 #

timestamp  measured value event
0 2012-07-16 23:23:50    2.1   NaN
1 2012-08-16 02:23:50    4.0   NaN
2 2015-07-16 12:23:50    2.0  True
3 2018-08-16 20:23:50    1.2   NaN

解决方案 #

方法1：逐行匹配时间范围 #

通过创建IntervalIndex索引后，直接用时间戳查询对应事件值：

# 为df_2创建IntervalIndex索引
interval_idx = pd.IntervalIndex.from_arrays(df_2["start"], df_2["end"], closed="both")
df_2 = df_2.set_index(interval_idx)

# 为df_1添加匹配的event列
df_1["event"] = df_1["timestamp"].apply(
    lambda ts: df_2.loc[ts, "event"] if ts in df_2.index else pd.NA
)

方法2：高效批量匹配（适合大数据集） #

使用get_indexer批量获取匹配索引，避免逐行apply的性能损耗：

interval_idx = pd.IntervalIndex.from_arrays(df_2["start"], df_2["end"], closed="both")
# 获取每个timestamp对应的interval索引，未匹配到则返回-1
match_indices = interval_idx.get_indexer(df_1["timestamp"])
# 匹配event值，未匹配的设为NA
df_1["event"] = df_2["event"].iloc[match_indices].where(match_indices != -1, pd.NA)

两种方法均可得到期望输出，方法2在处理大规模数据时性能更优。

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