Pandas1.2.0及以上版本按对应列头查找值的实现指南

pandas.DataFrame.lookup自1.2.0版本弃用后，可通过索引对齐+花式索引的通用方案覆盖所有原使用场景，各场景实现方式如下：

1. 标准场景（默认范围索引/非连续范围索引、保留原索引） #

通用实现兼容连续、非连续、自定义类型的行索引，返回结果可保留原始查找的行索引：

import pandas as pd
import numpy as np

# 测试数据：非连续行索引示例
df = pd.DataFrame({
    'A': [1,2,3,4],
    'B': [5,6,7,8],
    'C': [9,10,11,12]
}, index=[0,2,3,5])

# 待查找的行列配对序列
row_labels = [0,3,5,2]
col_labels = ['A','C','B','A']

# 核心实现
idx, cols = pd.factorize(col_labels)
lookup_arr = df.reindex(cols, axis=1).to_numpy()[np.arange(len(row_labels)), idx]
# 转换为Series保留原始查找索引
result = pd.Series(lookup_arr, index=row_labels)

2. MultiIndex多层索引场景 #

针对行索引为多层MultiIndex的场景，仅需增加行维度的reindex操作即可：

# 多层索引测试数据
midx = pd.MultiIndex.from_tuples([('a','x'),('a','y'),('b','x'),('b','y')], names=['level1','level2'])
df_multi = pd.DataFrame({'col1':[10,20,30,40], 'col2':[50,60,70,80]}, index=midx)

# 待查找的行列配对序列
row_labels_multi = [('a','y'), ('b','x'), ('a','x')]
col_labels_multi = ['col2', 'col1', 'col2']

# 核心实现
idx, cols = pd.factorize(col_labels_multi)
lookup_arr = df_multi.reindex(row_labels_multi, axis=0).reindex(cols, axis=1).to_numpy()[np.arange(len(row_labels_multi)), idx]
result_multi = pd.Series(lookup_arr, index=pd.MultiIndex.from_tuples(row_labels_multi))

3. 未匹配值设置默认值 #

当查找的行/列标签不存在于DataFrame中时，可通过reindex的fill_value参数自定义默认返回值：

# 含缺失标签的查找序列：行索引10、列D不存在于测试df中
row_labels_missing = [0,3,10,2]
col_labels_missing = ['A','C','B','D']
default_val = -999 # 自定义未匹配默认值

# 核心实现
idx, cols = pd.factorize(col_labels_missing)
arr = df.reindex(index=row_labels_missing, columns=cols, fill_value=default_val).to_numpy()
lookup_arr = arr[np.arange(len(row_labels_missing)), idx]
result_with_default = pd.Series(lookup_arr, index=row_labels_missing)

4. 查找列存在NaN时返回NaN处理 #

该场景不需要额外配置，上述实现逻辑天然兼容：目标单元格本身为NaN时，返回结果会自动保留NaN值：

# 含NaN的测试数据
df_with_nan = pd.DataFrame({
    'A': [1,np.nan,3],
    'B': [4,5,np.nan]
})
row_labels = [0,1,2]
col_labels = ['A','A','B']

# 核心实现
idx, cols = pd.factorize(col_labels)
lookup_arr = df_with_nan.reindex(cols, axis=1).to_numpy()[np.arange(len(row_labels)), idx]
# 返回结果：array([ 1., nan, nan]) 符合需求

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