基于多条件高效合并两个DataFrame的技术实现咨询

我有两个规模适中的DataFrame，结构如下：

df_B #

id     start_time                   end_time                     side      cost 
1234   2021-01-01 16:00:00.100000   2021-01-01 16:02:00.100000   BUY       100
1564   2021-01-01 16:05:00.100000   2021-01-01 16:10:00.100000   BUY       111
7535   2021-01-01 16:40:00.100000   2021-01-01 16:55:00.100000   BUY       124
9999   2021-01-01 16:44:00.100000   2021-01-01 16:45:00.100000   BUY       128

df_S #

id     start_time                   end_time                     side      cost 
5366   2021-01-01 16:00:00.100000   2021-01-01 16:02:00.100000   SELL       100
4533   2021-01-01 16:05:00.100000   2021-01-01 16:08:00.100000   SELL       105
4532   2021-01-01 16:20:00.100000   2021-01-01 16:50:00.100000   SELL       122
5827   2021-01-01 16:30:00.100000   2021-01-01 16:35:00.100000   SELL       123

我需要创建一个新的DataFrame，规则是：遍历df_B的每条记录，当df_S.cost <= df_B.cost且df_S.start_time <= df_B.end_time时，将匹配的df_S记录合并到对应df_B记录后。

期望输出示例：

id     start_time                   end_time                     side      cost  id_S   start_time_S             end_time_S             side_S      cost_S 
1234   2021-01-01 16:00:00.100000   2021-01-01 16:02:00.100000   BUY       100   5366   2021-01-01 16:00:00.100000   2021-01-01 16:02:00.100000   SELL       100
1564   2021-01-01 16:05:00.100000   2021-01-01 16:10:00.100000   BUY       111   4533   2021-01-01 16:05:00.100000   2021-01-01 16:08:00.100000   SELL       105
7535   2021-01-01 16:40:00.100000   2021-01-01 16:55:00.100000   BUY       124
9999   2021-01-01 16:44:00.100000   2021-01-01 16:45:00.100000   BUY       128

针对大型DataFrame，如何高效实现这一需求？

针对大型DataFrame，绝对要避免逐行遍历——这种方法时间复杂度为O(n*m)，数据量大时会直接导致性能瓶颈。推荐以下两种基于矢量化操作或索引优化的方案：

方法1：条件交叉合并 + 筛选去重 #

通过merge生成笛卡尔积后，过滤符合条件的行，再处理多匹配场景。适合数据规模中等的情况：

import pandas as pd

# 先转换时间列为datetime类型（必须步骤）
df_B['start_time'] = pd.to_datetime(df_B['start_time'])
df_B['end_time'] = pd.to_datetime(df_B['end_time'])
df_S['start_time'] = pd.to_datetime(df_S['start_time'])
df_S['end_time'] = pd.to_datetime(df_S['end_time'])

# 给df_S列名加后缀，避免合并后重名冲突
df_S = df_S.add_suffix('_S')

# 交叉合并两个DataFrame
merged = df_B.merge(df_S, how='left')

# 过滤满足条件的记录
filtered = merged[(merged['cost_S'] <= merged['cost']) & (merged['start_time_S'] <= merged['end_time'])]

# 处理多匹配：若一个df_B行对应多个df_S，取第一个匹配（可按需调整为取最大cost_S等）
result = filtered.groupby(df_B.columns.tolist()).first().reset_index()

# 补全无匹配的df_B记录
result = df_B.merge(result, how='left')

方法2：IntervalIndex时间匹配 + 成本过滤 #

利用IntervalIndex实现时间范围的快速匹配，避免全量笛卡尔积，适合超大型DataFrame场景：

import pandas as pd

# 转换时间列为datetime类型
df_B['start_time'] = pd.to_datetime(df_B['start_time'])
df_B['end_time'] = pd.to_datetime(df_B['end_time'])
df_S['start_time'] = pd.to_datetime(df_S['start_time'])
df_S['end_time'] = pd.to_datetime(df_S['end_time'])

# 基于df_B的时间范围构建IntervalIndex
intervals = pd.IntervalIndex.from_arrays(df_B['start_time'], df_B['end_time'], closed='both')

# 找到每个df_S记录对应的df_B行索引（无匹配则返回-1）
df_S['b_idx'] = intervals.get_indexer(df_S['start_time'])

# 过滤出匹配成功且成本符合条件的df_S记录
matched_S = df_S[(df_S['b_idx'] != -1) & (df_S['cost'] <= df_B.loc[df_S['b_idx'], 'cost'].values)]

# 重命名df_S列并合并到df_B
matched_S = matched_S.add_suffix('_S').rename(columns={'b_idx_S': 'b_idx'})
result = df_B.reset_index().rename(columns={'index': 'b_idx'}).merge(matched_S, on='b_idx', how='left').drop('b_idx', axis=1)

关键注意事项 #

• 时间列类型转换：必须将时间列转为datetime类型，否则字符串比较会出错。
• 多匹配处理：若存在一个df_B对应多个df_S的情况，需明确需求（保留全部、取第一个、取最优），调整代码中的筛选/分组逻辑。
• 超大规模数据：如果数据量达到千万级，可考虑使用Dask进行分布式处理，避免内存溢出。

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