使用pandas匹配两张表：满足列值一致且里程区间包含条件

存在的核心问题 #

• LN_PFX字段预处理错误：将去空格后的结果存储到了未使用的LN_PX字段，原LN_PFX字段仍保留空格，直接导致字符串匹配失效
• 区间筛选逻辑未生效：执行区间条件筛选后未将结果赋值回result变量，相当于区间判断完全没有执行
• 字段索引取值错误：删除table1第0列后字段顺序发生偏移，仍用索引位置取字段值会读取到错误内容，匹配逻辑完全错位
• 数值类型未校验：FromMP、ToMP、SEG_BGN_MP、SEG_END_MP如果为字符串类型，大小比较逻辑会出现异常
• 样例数据本身无匹配：你提供的测试数据中LineMax表DivisionNum均为3，第二张表OPER_MNT_DIV_CD均为4，天然不满足等值匹配条件，测试时可调整对应字段值验证逻辑

修正后代码 #

import pandas as pd
import numpy as np
import pyodbc
import math

x=pyodbc.connect("DSN=DBP1")
table1=pd.read_csv("LineMaxOrder.csv")
s2="select oper_mnt_div_cd, trk_cls_nbr, ln_pfx, seg_bgn_mp, seg_end_mp, ln_sfx, crvtr_mn, crvtr_deg, xstg_elev from dcmctrk.crv_seg where trk_cls_nbr = 1 order by oper_mnt_div_cd, ln_pfx,ln_sfx"
# 删除冗余列
table1=table1.drop(table1.columns[[0]],axis=1)
dChange=pd.read_sql_query(s2,x)

# 修正dChange预处理逻辑：LN_PFX同时做去空格和转大写，不要存储到无关字段
dChange["LN_PFX"]=dChange["LN_PFX"].str.strip().str.upper()
dChange["LN_SFX"]=dChange["LN_SFX"].str.strip().str.upper()
dChange["OPER_MNT_DIV_CD"]=dChange["OPER_MNT_DIV_CD"].astype(int)
# 数值字段统一转浮点型
dChange['SEG_BGN_MP'] = pd.to_numeric(dChange['SEG_BGN_MP'], errors='coerce')
dChange['SEG_END_MP'] = pd.to_numeric(dChange['SEG_END_MP'], errors='coerce')
# 剔除数值字段为空的无效行
dChange = dChange.dropna(subset=['SEG_BGN_MP', 'SEG_END_MP'])

# 处理LineMax表
dfObj2=table1.select_dtypes(["object"])
table1[dfObj2.columns] = dfObj2.apply(lambda x: x.str.strip().str.upper())
table1=table1.fillna('')
# 数值字段统一转浮点型
table1['FromMP'] = pd.to_numeric(table1['FromMP'], errors='coerce')
table1['ToMP'] = pd.to_numeric(table1['ToMP'], errors='coerce')
# 剔除数值字段为空的无效行
table1 = table1.dropna(subset=['FromMP', 'ToMP'])

w=[]
# 改用列名取字段，彻底避免索引偏移错误
for idx,row in table1.iterrows():
    a = row['DivisionNum']
    b = row['Prefix']
    c = row['Suffix']
    agu1 = row['FromMP']
    agu2 = row['ToMP']
    big = max(agu1,agu2)
    small = min(agu2,agu1)
    # 等值匹配
    result=dChange[(dChange["OPER_MNT_DIV_CD"]==a)&(dChange["LN_PFX"]==b)&(dChange["LN_SFX"]==c)]
    if result.empty:
        continue
    # 区间匹配，必须将筛选结果赋值回result才会生效
    result = result[(result["SEG_BGN_MP"]<=small)&(result["SEG_END_MP"]>=big)]
    if result.empty:
        continue
    print(result)
    w.append(result)

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