基于时间范围与机位匹配的DataFrame高效关联合并方案咨询

问题 #

我有一个包含机位stand和时间戳datetime字段的记录表（df_hvd），需要为该表补充acreg和flight信息。
df_hvd核心字段如下：

另有包含所有航班（到达/离港）及对应起降时间段的表（df_events），需根据机位匹配+时间范围包含的规则，获取对应acreg和flight信息，表结构如下：

规则：df_hvd的时间戳需落在df_events某条记录的start_datetime到end_datetime范围内，且机位（df_hvd.stand与df_events.position）匹配，完成关联合并补充字段。

尝试过循环实现，但效率极低且存在键错误：

for r in df_hvd.index: 
    for e in df_temp.index: 
        if df_hvd.at[r, "Positie"] == df_temp.at[r, "stand"]:
            if df_hvd.at[r, "datetime"] <= df_temp.at[e, "end_datetime"] and df_hvd.at[r, "datetime"] >= df_temp.at[e, "start_datetime"]: 
                print(df_temp.at[r, "acreg"])

另一种循环写法也未生效：

for e in df_temp.index: 
    stand = df_temp.at[e, "stand"]
    start = df_temp.at[e, "start_datetime"]
    end = df_temp.at[e, "end_datetime"]
    acreg = df_temp.at[e, "acreg"]
    
    df_hvd.loc[(df_hvd["datetime"] <= end) & (df_hvd["datetime"] >= start) & (df_hvd["Positie"] == stand), 'acreg'] = acreg

现寻求高效且正确的DataFrame关联合并方法。

高效解决方案 #

前置准备：统一时间格式 #

先确保两个表的时间字段为datetime类型，避免字符串匹配错误：

import pandas as pd

# 转换时间字段为datetime类型
df_hvd['datetime'] = pd.to_datetime(df_hvd['datetime'])
df_events['start_datetime'] = pd.to_datetime(df_events['start_datetime'])
df_events['end_datetime'] = pd.to_datetime(df_events['end_datetime'])

方法1：merge_asof（推荐，大数据量高效） #

merge_asof是pandas专为时间范围匹配设计的高效方法，需先对两表按机位+时间排序：

# 按机位、时间排序
df_events_sorted = df_events.sort_values(['position', 'start_datetime'])
df_hvd_sorted = df_hvd.sort_values(['stand', 'datetime'])

# 执行asof合并：匹配同机位，且df_hvd.datetime >= df_events.start_datetime，取最近的未超时记录
result = pd.merge_asof(
    df_hvd_sorted,
    df_events_sorted[['acreg', 'flight', 'position', 'start_datetime', 'end_datetime']],
    left_on='datetime',
    right_on='start_datetime',
    left_by='stand',
    right_by='position',
    direction='backward'
)

# 过滤掉时间超出end_datetime的无效匹配
result = result[result['datetime'] <= result['end_datetime']]

# 恢复原df_hvd的索引顺序（可选）
result = result.set_index(df_hvd_sorted.index).reindex(df_hvd.index)

方法2：区间索引匹配（中等数据量适用） #

将df_events的时间范围转为区间索引，通过批量匹配完成关联：

# 为df_events创建时间区间索引
df_events['time_interval'] = pd.IntervalIndex.from_arrays(
    df_events['start_datetime'], 
    df_events['end_datetime'], 
    closed='both'  # 包含边界时间
)

# 定义批量匹配函数
def match_flight(row):
    # 筛选同机位的航班记录
    same_stand_events = df_events[df_events['position'] == row['stand']]
    # 查找时间落在哪个区间内的航班
    matched = same_stand_events[same_stand_events['time_interval'].contains(row['datetime'])]
    if not matched.empty:
        return pd.Series([matched['acreg'].iloc[0], matched['flight'].iloc[0]])
    return pd.Series([None, None])

# 批量补充字段
df_hvd[['acreg', 'flight']] = df_hvd.apply(match_flight, axis=1)

方法3：交叉合并后过滤（小数据量快速实现） #

如果数据量较小，可先按机位交叉合并，再过滤时间范围：

# 先按机位关联两表
merged = pd.merge(df_hvd, df_events, left_on='stand', right_on='position', how='left')

# 过滤时间在有效范围内的记录
filtered = merged[(merged['datetime'] >= merged['start_datetime']) & (merged['datetime'] <= merged['end_datetime'])]

# 保留原df_hvd所有行，匹配不到的用NaN填充
result = df_hvd.merge(filtered[['datetime', 'stand', 'acreg', 'flight']], on=['datetime', 'stand'], how='left')

关键注意事项 #

• 检查字段名一致性：你的循环代码中出现了Positie，需确认是否为df_hvd中stand字段的拼写错误，必须保证机位字段名在两表中对应（df_hvd.stand ↔ df_events.position）
• 处理重复匹配：如果存在一个时间点对应多个航班的情况，需额外添加规则（如按movement_type筛选，或取最早/最晚航班）

内容的提问来源于stack exchange，提问作者Hans.nl