如何根据另一DataFrame的数值范围为目标DataFrame分配对应值？

需要实现的需求：现有两个DataFrame（df1、df2），匹配Wegnummer字段，且当df1的Hectometer bord值处于df2的van km与tot km范围内时，将df2的Code systeemdeel值分配给df1对应行，需保留df1所有行（无匹配的行对应字段留空）。

此前尝试用merge cross+query的方法，但会过滤掉无匹配的行，不符合需求；目前用循环映射的方法能得到预期结果，但希望优化实现方案。

示例数据 #

df1 #

Omschrijving bouwdeelsoort  Wegnummer   Hectometer bord
6   Camera (beweegbaar)         0.0         41.5    
7   Kast                        1.0         29.5    
8   Kast                        1.0         40.4
20  Kast                        1.0         30.4    

df2 #

Code systeemdeel    van km  tot km  Wegnummer
1   000_0010_R          40.4    50.7    0.0
2   001_0040_R          28.885  39.5    1.0
4   001_0050_R          39.5    46.4    1.0

预期结果 #

Omschrijving bouwdeelsoort  Wegnummer   Hectometer bord  Code systeemdeel
6   Camera (beweegbaar)         0.0         41.5             000_0010_R    
7   Kast                        1.0         29.5             001_0040_R                 
8   Kast                        1.0         40.4             001_0050_R
20  Kast                        1.0         30.4             001_0040_R

之前的尝试 #

过滤无匹配行的无效代码 #

data_importeren["Specifieke omschrijving object"] = ""    
DF_out = data_importeren.merge(systeemdelen, how='cross').query('(Wegnummer_x == Wegnummer_y) & (Hectometer bord >= van km) & (Hectometer bord <= tot km)')

该方法会直接丢弃df1中无匹配的行，不符合需求。

可行但待优化的循环映射代码 #

wegnummer_range_mapping = {}
for index, row in systeemdelen.iterrows():
    wegnr = row["Wegnummer"]
    if wegnr not in wegnummer_range_mapping:
        wegnummer_range_mapping[wegnr] = []
    wegnummer_range_mapping[wegnr].append((row["van km"], row["tot km"], row["Code systeemdeel"]))

# 定义赋值函数
def assign_code(row):
    wegnr = row["Wegnummer"]
    if wegnr in wegnummer_range_mapping:
        for start, end, code in wegnummer_range_mapping[wegnr]:
            # 注：原代码中row["Locatie"]应为row["Hectometer bord"]，属于笔误
            if start <= row["Hectometer bord"] <= end:
                return code
    return None

# 应用函数生成新列
df1["Code systeemdeel"] = df1.apply(assign_code, axis=1)

该方法能得到正确结果，但显式循环在数据量较大时性能较差。

优化方案 #

方案1：左连接+分组筛选（简洁高效） #

利用pandas的左连接保留df1所有行，再通过分组筛选匹配的结果：

# 按Wegnummer左连接，保留df1所有行
merged = df1.merge(df2, on="Wegnummer", how="left")
# 筛选出Hectometer bord在范围区间内的行，或未匹配的行
mask = merged["Hectometer bord"].between(merged["van km"], merged["tot km"]) | merged["van km"].isna()
# 按原df1的索引分组，取第一个匹配的Code systeemdeel（无匹配则为NaN）
matched_codes = merged[mask].groupby(df1.index)["Code systeemdeel"].first().reset_index()
# 合并回原df1，得到最终结果
final_df = df1.merge(matched_codes, left_index=True, right_on="index", how="left").drop("index", axis=1)

方案2：IntervalIndex范围映射（高效查询） #

使用pandas的IntervalIndex专门处理范围匹配，查询效率更高：

# 按Wegnummer分组，为每个组创建区间索引与Code的映射
interval_map = {}
for wegnr, group in df2.groupby("Wegnummer"):
    # 创建闭区间索引（包含两端值）
    intervals = pd.IntervalIndex.from_arrays(group["van km"], group["tot km"], closed="both")
    interval_map[wegnr] = pd.Series(group["Code systeemdeel"].values, index=intervals)

# 定义高效赋值函数
def get_code(row):
    wegnr = row["Wegnummer"]
    hm = row["Hectometer bord"]
    if wegnr in interval_map:
        # 直接通过区间索引查找对应code
        return interval_map[wegnr].get(hm, None)
    return None

# 应用函数生成新列
df1["Code systeemdeel"] = df1.apply(get_code, axis=1)

方案3：numpy向量化操作（大数据场景最优） #

基于numpy的广播特性实现无循环的向量化匹配，适合数据量较大的场景：

import numpy as np

# 将df1和df2的Wegnummer转为numpy数组，用于广播匹配
df1_wegnr = df1["Wegnummer"].values[:, np.newaxis]
df2_wegnr = df2["Wegnummer"].values[np.newaxis, :]
# 生成Wegnummer匹配的掩码
wegnr_mask = df1_wegnr == df2_wegnr

# 生成Hectometer bord在van km与tot km之间的掩码
hm_values = df1["Hectometer bord"].values[:, np.newaxis]
van_km = df2["van km"].values[np.newaxis, :]
tot_km = df2["tot km"].values[np.newaxis, :]
range_mask = (hm_values >= van_km) & (hm_values <= tot_km)

# 合并两个掩码，得到最终匹配条件
combined_mask = wegnr_mask & range_mask

# 提取匹配的Code systeemdeel，无匹配则设为NaN
codes = df2["Code systeemdeel"].values[np.newaxis, :]
df1["Code systeemdeel"] = np.where(combined_mask.any(axis=1), codes[combined_mask].reshape(-1), np.nan)

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