Pandas如何从存储数值范围的DataFrame中匹配值并获取对应行列名称

实现步骤 #

1. 预处理df1，将各区间列的逗号分隔字符串转换为(start, end)数值元组，空值直接忽略
2. 定义匹配函数，输入单个product值，遍历预处理后的df1收集所有符合条件的「ID-列名」组合，拼接为字符串
3. 将匹配函数应用到df2的product列，生成所需的Source列

完整代码 #

import pandas as pd
import numpy as np

# --------------------- 构造示例数据 ---------------------
df1 = pd.DataFrame({
    'ID': ['a', 'b', '6c', '2d'],
    'Y_b1': ['1, 5', np.nan, '1211, 1215', '89, 95'],
    'c1_t': [np.nan, '88761, 88776', np.nan, '3, 19'],
    'rd1_hd': ['654, 678', np.nan, '38, 47', np.nan],
    'bd_n9': ['11, 15', np.nan, np.nan, '1567, 1578']
})

df2 = pd.DataFrame({
    'Owner': ['Abi', 'John', 'Lan', 'Smith', 'Tim', 'Don'],
    'product': [1, 678, 13, 90, 670, 4]
})

# --------------------- 预处理df1 ---------------------
# 将ID设为索引方便取值，区间字符串转数值元组
df1_processed = df1.set_index('ID')
for col in df1_processed.columns:
    def parse_range(s):
        if pd.isna(s):
            return np.nan
        start, end = map(int, s.strip().split(','))
        return (start, end)
    df1_processed[col] = df1_processed[col].apply(parse_range)

# --------------------- 匹配生成Source列 ---------------------
def get_source(product_val):
    match_list = []
    for row_id, row_data in df1_processed.iterrows():
        for col_name, range_val in row_data.items():
            if pd.isna(range_val):
                continue
            s, e = range_val
            if s <= product_val <= e:
                match_list.append(f"{row_id}-{col_name}")
    return ', '.join(match_list)

df2['Source'] = df2['product'].apply(get_source)
df3 = df2.copy()
print(df3)

性能优化提示 #

如果数据量较大，可提前把所有区间展开为一张映射表（每行存储start、end、source_str），再用区间匹配逻辑批量匹配，避免对每个product值都全量遍历df1。

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