如何在Pandas中实现类似SQL的基于区间条件的不等值两表关联

Pandas没有原生支持任意条件的join语法，要实现和题目中SQL等价的区间左连接，常用三种方案，可根据数据量和区间特性选择：

方案1：交叉连接后条件过滤（逻辑最贴合SQL，适合小数据量） #

逻辑和SQL完全一致，先做笛卡尔积，再筛选符合区间条件的行，保留左表所有数据。

import pandas as pd

# 假设df_a对应表A，df_b对应表B
df_a = pd.DataFrame({'Entry number': [1000, 2000, 3000], 'Amount': [100, 200, 300]})
df_b = pd.DataFrame({'From Entry number': [900, 1900, 3500], 'To Entry Number': [1100, 2100, 4000], 'Created Date': ['2024-01-01', '2024-01-02', '2024-01-03']})

# 1. 给两个表加临时公共键，用于交叉连接
df_a['tmp_key'] = 0
df_b['tmp_key'] = 0

# 2. 左连接实现笛卡尔积
result = df_a.merge(df_b, on='tmp_key', how='left')

# 3. 筛选符合区间条件的行，删除临时键
result = result[(result['Entry number'] >= result['From Entry number']) & 
                (result['Entry number'] <= result['To Entry Number'])].drop('tmp_key', axis=1)

# 4. 补充未匹配到的左表行，恢复左连接特性
result = df_a.drop('tmp_key', axis=1).merge(result, on=['Entry number', 'Amount'], how='left')

• 优点：逻辑和SQL完全对齐，支持重叠区间、一对多匹配的场景
• 缺点：数据量较大时，笛卡尔积会占用极高内存，性能差

方案2：merge_asof实现（性能最优，适合非重叠区间场景） #

如果表B的区间无重叠，推荐用pd.merge_asof，底层做过优化，性能远高于交叉连接。
注意：使用前需要对两个表的匹配字段排序。

import pandas as pd

df_a = pd.DataFrame({'Entry number': [1000, 2000, 3000], 'Amount': [100, 200, 300]}).sort_values('Entry number')
df_b = pd.DataFrame({'From Entry number': [900, 1900, 3500], 'To Entry Number': [1100, 2100, 4000], 'Created Date': ['2024-01-01', '2024-01-02', '2024-01-03']}).sort_values('From Entry number')

# merge_asof默认左连接，匹配小于等于左表Entry number的最大的From Entry number
result = pd.merge_asof(df_a, df_b, left_on='Entry number', right_on='From Entry number')

# 过滤掉超出To Entry Number范围的匹配项
result = result[result['Entry number'] <= result['To Entry Number']]

# 恢复未匹配的左表行
result = df_a.merge(result, on=['Entry number', 'Amount'], how='left')

方案3：IntervalIndex匹配（适合中大数据量、重叠区间场景） #

利用Pandas的IntervalIndex直接做包含匹配，性能比交叉连接好。

import pandas as pd

df_a = pd.DataFrame({'Entry number': [1000, 2000, 3000], 'Amount': [100, 200, 300]})
df_b = pd.DataFrame({'From Entry number': [900, 1900, 3500], 'To Entry Number': [1100, 2100, 4000], 'Created Date': ['2024-01-01', '2024-01-02', '2024-01-03']})

# 给表B创建闭区间索引
df_b = df_b.set_index(pd.IntervalIndex.from_arrays(df_b['From Entry number'], df_b['To Entry Number'], closed='both'))

# 对表A每个Entry number匹配所有包含它的表B行
df_a['matches'] = df_a['Entry number'].apply(lambda x: df_b.loc[x].values.tolist() if x in df_b.index else [])

# 展开匹配结果，实现一对多关联
result = df_a.explode('matches').reset_index(drop=True)

# 拆分匹配的字段
result[['From Entry number', 'To Entry Number', 'Created Date']] = pd.DataFrame(result['matches'].tolist(), index=result.index)
result = result.drop('matches', axis=1)

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