如何基于区间合并两个Pandas DataFrame？

我有两个DataFrame：mp和sp：

mp数据结构与示例 #

mp包含州道（route，整数）、里程桩（milepost，浮点数）、经度（lon，浮点数）、纬度（lat，浮点数），示例数据如下：

route  milepost         lon         lat
2091        2      0.0              -122.189880 47.979063
2667        2      0.1              -122.188067 47.979760
2159        2      0.2              -122.186210 47.979560
1174        2      0.3              -122.184197 47.979098
1878        2      0.4              -122.182098 47.978718

sp数据结构与示例 #

sp包含州道（route，整数）、限速起始里程桩（begin，浮点数）、限速结束里程桩（end，浮点数）、限速值（limit，整数），示例数据如下：

route    begin       end          limit
3119  2        0.000000    0.130000     30
2787  2        0.130000    2.880000     55
3184  2        2.880000    12.080000    60
3035  2        12.080000   12.720000    55
2780  2        12.720000   14.220000    45
2777  2        14.220000   15.450000    35
2774  2        15.450000   21.500000    55

需求 #

需要将两个DataFrame合并，为mp中的每个里程桩匹配对应限速值：同一route下，根据milepost所在的[begin, end]区间匹配limit。例如：

• route 2中，milepost 0.0属于[0.0,0.13]，对应限速30；
• milepost 0.1属于[0.13,2.88]，对应限速55；
• milepost 2.8属于[2.88,12.08]，对应限速60，以此类推。

期望结果示例：

route  milepost         lon         lat       limit
2091        2      0.0              -122.189880 47.979063 30
2667        2      0.1              -122.188067 47.979760 55
2159        2      0.2              -122.186210 47.979560 55
...
2789        2      2.8              -122.134982 47.972241 60
1388        2      2.9              -122.133974 47.970995 60
1609        2      3.0              -122.132964 47.969750 60

已尝试的方法（均未成功） #

• 使用pandas的merge方法，丢失大量行；
• 尝试用fuzzypanda进行模糊连接，遇到fuzzypanda.matching不存在的错误；
• 尝试循环匹配，但代码未生效：

for route in mp['route']:
    for milepost in mp['milepost']:
        if (route == sp['route']) & (milepost >= sp['begin']) & (milepost <= sp['end']):
            mp.insert(1, 'limit', sp['limit'][0])

方法1：使用merge_asof（推荐） #

Pandas的merge_asof专门用于区间匹配场景，适合同一分组下的区间连接需求，效率较高。步骤如下：

1. 先对两个DataFrame按匹配键排序：

import pandas as pd

# 对sp按route和begin排序
sp_sorted = sp.sort_values(['route', 'begin'])
# 对mp按route和milepost排序
mp_sorted = mp.sort_values(['route', 'milepost'])

2. 执行区间匹配：

result = pd.merge_asof(
    mp_sorted,
    sp_sorted[['route', 'begin', 'end', 'limit']],
    by='route',
    left_on='milepost',
    right_on='begin',
    direction='backward'
)

3. 验证并清理结果：
   确保匹配的里程桩在对应区间内，然后删除冗余列：

# 筛选符合区间条件的行（排除超出所有区间的里程桩）
result = result[result['milepost'] <= result['end']]
# 删除不需要的中间列
result = result.drop(['begin', 'end'], axis=1)
# 恢复原mp的索引顺序（可选）
result = result.reindex(mp.index)

方法2：使用apply逐行匹配 #

如果数据量较小，可以直接逐行查找对应区间：

def get_matching_limit(row):
    # 筛选同一route下，里程桩在区间内的行
    match_mask = (sp['route'] == row['route']) & \
                 (sp['begin'] <= row['milepost']) & \
                 (sp['end'] >= row['milepost'])
    # 返回匹配到的限速值，无匹配则返回NaN
    return sp.loc[match_mask, 'limit'].iloc[0] if match_mask.any() else None

mp['limit'] = mp.apply(get_matching_limit, axis=1)

方法3：使用区间索引 #

为每个州道预构建区间索引，再进行匹配：

# 为每个route创建区间-限速的映射
route_limit_map = {}
for route in sp['route'].unique():
    route_data = sp[sp['route'] == route]
    # 创建闭区间索引
    intervals = pd.IntervalIndex.from_arrays(
        route_data['begin'], 
        route_data['end'], 
        closed='both'
    )
    route_limit_map[route] = pd.Series(route_data['limit'].values, index=intervals)

# 批量匹配限速
mp['limit'] = mp.apply(
    lambda row: route_limit_map[row['route']].get(row['milepost'], None),
    axis=1
)

内容的提问来源于stack exchange，提问作者J. Sizzler