如何基于范围值高效合并两个DataFrame？

问题背景 #

现有两个DataFrame：
section_headers：

start_sect_  end_sect_
0            0         50
1          121        139
2          221        270

sentences：

start_sent_  end_sent_
0             0         50
1            56         76
2            77         85
3            88        111
4           114        120
5           121        139
6           221        270

需将sentences中的条目匹配到对应section：当start_sent_大于等于某section的start_sect_且小于下一个section的start_sect_时，归属于当前section。期望输出：

start_sent_  end_sent_  start_sect_
0            0         50            0
1           56         76            0
2           77         85            0
3           88        111            0
4          114        120            0
5          121        139          121
6          221        270          221

原遍历方法在数据量大时效率极低，现提供两种无需遍历的高效矢量化方案。

方案一：使用numpy.digitize #

利用numpy的区间划分功能，直接定位每个sentence对应的section索引，性能最优，适合超大数据量：

import numpy as np
import pandas as pd

# 初始化示例数据
section_headers = pd.DataFrame({
    'start_sect_': [0, 121, 221],
    'end_sect_': [50, 139, 270]
})

sentences = pd.DataFrame({
    'start_sent_': [0, 56, 77, 88, 114, 121, 221],
    'end_sent_': [50, 76, 85, 111, 120, 139, 270]
})

# 提取section起始点，添加无穷大作为最后一个区间的右边界
sect_starts = section_headers['start_sect_'].values
bins = np.append(sect_starts, np.inf)

# 用digitize匹配区间，right=False对应[左边界, 右边界)的规则
section_indices = np.digitize(sentences['start_sent_'], bins, right=False) - 1

# 映射对应的start_sect_到sentences中
sentences['start_sect_'] = section_headers['start_sect_'].iloc[section_indices].values

print(sentences)

方案二：使用pandas.merge_asof #

pandas内置的按顺序匹配合并方法，逻辑更直观，适合需要保留更多section字段的场景：

import pandas as pd

# 初始化示例数据
section_headers = pd.DataFrame({
    'start_sect_': [0, 121, 221],
    'end_sect_': [50, 139, 270]
})

sentences = pd.DataFrame({
    'start_sent_': [0, 56, 77, 88, 114, 121, 221],
    'end_sent_': [50, 76, 85, 111, 120, 139, 270]
})

# 确保两个DataFrame按匹配键排序（merge_asof要求）
section_sorted = section_headers.sort_values('start_sect_')
sentences_sorted = sentences.sort_values('start_sent_').reset_index(drop=True)

# 执行前向匹配：找到小于等于当前start_sent_的最大start_sect_
merged = pd.merge_asof(
    sentences_sorted,
    section_sorted[['start_sect_']],
    left_on='start_sent_',
    right_on='start_sect_',
    direction='backward'
)

# 恢复原sentences的顺序
sentences['start_sect_'] = merged['start_sect_']

print(sentences)

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