如何高效匹配两个DataFrame并新增列？（优化循环方案）

现有两个DataFrame df1 和 df2，结构与数据如下：

df1 #

df2 #

需要生成目标DataFrame，新增DATA列，规则为：当df1与df2的LOT字段匹配，且df1的EVENT_TIME处于df2的IN_TIME与OUT_TIME区间内时，DATA列取值为df2对应的VALUE，目标结构如下：

目标DataFrame #

原代码使用嵌套for循环实现，但当df1和df2行数超过10万时，耗时超过10分钟：

DATA = pd.Series()
for idx_1, ser_1 in df1.iterrows():
    for idx_2, ser_2 in df2.iterrows():
        if (df1['LOT'] == df2['LOT'] ) and (df1['EVENT_TIME'] >= df2['IN_TIME']) and (df1['EVENT_TIME'] <= df2['OUT_TIME']):
            DATA[idx_1] = df2['VALUE']

df = pd.concat([df1, DATA],axis=1)

需要用更高效的方法优化代码。

方法1：merge + 布尔索引过滤 #

先按LOT字段合并两个DataFrame，再筛选时间符合条件的行，最后合并回原df1：

import pandas as pd

# 确保时间列转为datetime类型
df1['EVENT_TIME'] = pd.to_datetime(df1['EVENT_TIME'])
df2['IN_TIME'] = pd.to_datetime(df2['IN_TIME'])
df2['OUT_TIME'] = pd.to_datetime(df2['OUT_TIME'])

# 按LOT做左连接
merged = pd.merge(df1, df2, on='LOT', how='left')
# 筛选时间区间符合条件的记录
filtered = merged[(merged['EVENT_TIME'] >= merged['IN_TIME']) & (merged['EVENT_TIME'] <= merged['OUT_TIME'])]
# 合并回原df1并重命名列
result = df1.merge(filtered[['LOT', 'EVENT_TIME', 'VALUE']], on=['LOT', 'EVENT_TIME'], how='left').rename(columns={'VALUE': 'DATA'})

该方法利用pandas向量化操作替代Python循环，效率比嵌套循环提升显著，适合中等规模数据。

方法2：pd.merge_asof（大数据最优方案） #

merge_asof是pandas专门用于时间区间匹配的API，通过排序后线性扫描匹配，时间复杂度远低于嵌套循环：

import pandas as pd

# 转换时间列类型
df1['EVENT_TIME'] = pd.to_datetime(df1['EVENT_TIME'])
df2['IN_TIME'] = pd.to_datetime(df2['IN_TIME'])
df2['OUT_TIME'] = pd.to_datetime(df2['OUT_TIME'])

# 按LOT分组处理（支持多LOT场景）
def process_lot_group(group):
    df2_group = df2[df2['LOT'] == group.name].sort_values('IN_TIME')
    # 匹配EVENT_TIME >= IN_TIME的最近区间
    merged = pd.merge_asof(group.sort_values('EVENT_TIME'),
                          df2_group,
                          left_on='EVENT_TIME',
                          right_on='IN_TIME',
                          by='LOT',
                          direction='forward')
    # 过滤掉EVENT_TIME超出OUT_TIME的记录
    merged = merged[merged['EVENT_TIME'] <= merged['OUT_TIME']]
    return merged.drop(columns=['IN_TIME', 'OUT_TIME']).rename(columns={'VALUE': 'DATA'})

# 分组处理后合并结果，保留原索引顺序
result = df1.groupby('LOT', group_keys=False).apply(process_lot_group).sort_index()

十万级数据量下，该方法可将耗时压缩到秒级。

方法3：矢量化广播操作 #

利用numpy广播特性，通过矩阵运算替代循环：

import pandas as pd
import numpy as np

# 转换时间列类型
df1['EVENT_TIME'] = pd.to_datetime(df1['EVENT_TIME'])
df2['IN_TIME'] = pd.to_datetime(df2['IN_TIME'])
df2['OUT_TIME'] = pd.to_datetime(df2['OUT_TIME'])

result_list = []
for lot in df1['LOT'].unique():
    df1_lot = df1[df1['LOT'] == lot].copy()
    df2_lot = df2[df2['LOT'] == lot]
    
    # 生成布尔矩阵：每个EVENT_TIME是否落在对应IN-OUT区间内
    time_mask = (df1_lot['EVENT_TIME'].values[:, np.newaxis] >= df2_lot['IN_TIME'].values) & \
                (df1_lot['EVENT_TIME'].values[:, np.newaxis] <= df2_lot['OUT_TIME'].values)
    
    # 匹配对应区间的VALUE
    df1_lot['DATA'] = df2_lot['VALUE'].values[np.argmax(time_mask, axis=1)]
    result_list.append(df1_lot)

result = pd.concat(result_list).sort_index()

该方法避免了Python层面的循环开销，效率远高于嵌套循环。

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