Pandas DataFrame如何查询行列最接近低值及超范围返回NaN

核心逻辑 #

• 提前提取表中所有有效pH值和温度列名并排序，避免数据乱序影响匹配结果
• 输入pH值后，筛选所有小于等于输入值的有效pH，取最大值作为目标行的匹配依据，无符合条件的值直接返回np.nan
• 输入温度值后，筛选所有小于等于输入值的有效温度列名，取最大值作为目标列，无符合条件的值直接返回np.nan
• 匹配到目标行和列后直接返回对应单元格数值，不做任何插值计算

完整实现代码 #

import pandas as pd
import numpy as np

# 构造原始数据表
def table():
    df_table = pd.DataFrame()
    df_table['ph'] = [0.5, 0.8, 1.25, 1.75, 2.25, 2.75, 3.25, 3.75, 4.25, 4.75, 5.25, 5.75, 6.25, 6.80]
    df_table[38.0] = [25.37, 22.86, 10.16, 5.08, 2.54, 1.52, 1.02, 0.76, 0.51, 0.25, 0.18, 0.10, 0.08, 0.05]
    df_table[52.0] = [25.37, 25.37, 25.37, 17.78, 7.62, 3.30, 1.78, 1.27, 1.02, 0.76, 0.51, 0.38, 0.25, 0.13]
    df_table[79.0] = [25.37, 25.37, 25.37, 25.37, 10.16, 5.08, 2.54, 2.29, 1.78, 1.27, 0.76, 0.51, 0.38, 0.18]
    df_table[93.0] = [25.37, 25.37, 25.37, 25.37, 14.22, 7.11, 3.56, 3.18, 2.54, 1.78, 1.02, 0.76, 0.51, 0.25]
    return df_table

# 查询腐蚀速率函数
def get_corrosion_rate(input_ph, input_temp, df):
    # 匹配目标pH
    valid_ph = sorted(df['ph'].tolist())
    target_ph_list = [ph for ph in valid_ph if ph <= input_ph]
    if not target_ph_list:
        return np.nan
    target_ph = max(target_ph_list)
    
    # 匹配目标温度列
    valid_temp = sorted([col for col in df.columns if col != 'ph'])
    target_temp_list = [temp for temp in valid_temp if temp <= input_temp]
    if not target_temp_list:
        return np.nan
    target_temp = max(target_temp_list)
    
    # 返回目标值
    return float(df.loc[df['ph'] == target_ph, target_temp].iloc[0])

# 测试
if __name__ == "__main__":
    df = table()
    # 示例输入测试，输出22.86
    print(get_corrosion_rate(0.85, 45.0, df))
    # 超范围输入测试，输出nan
    print(get_corrosion_rate(0.85, 100.0, df))

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