基于参考表的间接双线性插值：电压转温度批量处理技术问询

Let's break down how to implement this indirect bilinear interpolation workflow using pandas DataFrames, so you can process large datasets efficiently without manual single-step handling.

First, Define the Reference Table Structure #

First, let's assume your reference table has two key parts:

• A dedicated mapping for Internal Temp. (Ch1) that links voltage values to internal temperatures (your y list from the example)
• A grid where rows represent external temperature ranges (e.g., 200-250℃, 250-300℃) and columns represent internal temperature ranges (e.g., 125-140℃, 140-150℃), with cells storing the corresponding T2 voltage values.

Here's a simulated reference table as a pandas DataFrame to work with:

import pandas as pd
import numpy as np

# Simulate the core grid of T2 voltages (rows = external temp ranges, cols = internal temp ranges)
ref_grid = pd.DataFrame({
    "100-125℃": [3.201, 3.502, 3.803],
    "125-140℃": [3.608, 4.005, 4.462],
    "140-150℃": [3.616, 4.012, 4.468],
}, index=["200-250℃", "250-300℃", "300-350℃"])

# Separate series for Internal Temp. (Ch1) voltage-to-temperature mapping
internal_volt_to_temp = pd.Series(
    [100, 125, 140, 150],  # Temperatures
    index=[1.500, 1.721, 1.950, 2.100],  # Corresponding voltages
    name="Internal Temp. (Ch1)"
)

Step 1: Batch Convert T1 Voltages to Internal Temperatures #

Instead of looping through each value, we can vectorize this with np.interp for fast processing on large datasets:

# Simulate your raw input data with T1 and T2 voltages
raw_data = pd.DataFrame({
    "T1 Intern": [1.721, 1.835, 1.980, 1.650],
    "T2": [4.025, 3.700, 4.470, 3.300]
})

# Batch compute internal temperatures for all T1 values
raw_data["Internal Temp"] = np.interp(
    x=raw_data["T1 Intern"],
    xp=internal_volt_to_temp.index.values,  # Voltage reference points
    fp=internal_volt_to_temp.values         # Corresponding internal temperatures
)

Steps 2 & 3: Indirect Bilinear Interpolation for T2 to External Temperature #

Now we need to handle the two-stage interpolation for each row in raw_data. Here's a reusable function to execute the logic, which we can apply across the entire dataset:

def indirect_bilinear_interp(row, ref_grid):
    internal_temp = row["Internal Temp"]
    t2_volt = row["T2"]
    
    # 1. Find the internal temperature column interval
    col_intervals = []
    for col in ref_grid.columns:
        min_col_temp, max_col_temp = map(float, col.split("-"))
        col_intervals.append((min_col_temp, max_col_temp, col))
    
    # Match our internal temp to the correct column interval
    for min_col, max_col, col_name in col_intervals:
        if min_col <= internal_temp < max_col:
            # Grab the adjacent column for interpolation
            col_idx = ref_grid.columns.get_loc(col_name)
            adjacent_col = ref_grid.columns[col_idx + 1] if col_idx < len(ref_grid.columns)-1 else ref_grid.columns[col_idx -1]
            break
    
    # 2. Find the external temperature row interval that fits the T2 voltage
    row_intervals = []
    for row_name in ref_grid.index:
        min_row_temp, max_row_temp = map(float, row_name.split("-"))
        # Get T2 voltages for this row across our target columns
        volt_left = ref_grid.loc[row_name, col_name]
        volt_right = ref_grid.loc[row_name, adjacent_col]
        row_intervals.append((min_row_temp, max_row_temp, row_name, volt_left, volt_right))
    
    # Match T2 voltage to the correct row interval
    for min_row, max_row, row_name, v_left, v_right in row_intervals:
        if min(v_left, v_right) <= t2_volt <= max(v_left, v_right):
            # Grab adjacent row for interpolation
            row_idx = ref_grid.index.get_loc(row_name)
            adjacent_row = ref_grid.index[row_idx +1] if row_idx < len(ref_grid.index)-1 else ref_grid.index[row_idx -1]
            # Extract the four grid points needed for interpolation
            v_top_left = ref_grid.loc[row_name, col_name]
            v_top_right = ref_grid.loc[row_name, adjacent_col]
            v_bottom_left = ref_grid.loc[adjacent_row, col_name]
            v_bottom_right = ref_grid.loc[adjacent_row, adjacent_col]
            break
    
    # 3. Interpolate along columns to get two voltage points for our internal temp
    col_interp_factor = (internal_temp - min_col) / (max_col - min_col)
    v_row_top = v_top_left + col_interp_factor * (v_top_right - v_top_left)
    v_row_bottom = v_bottom_left + col_interp_factor * (v_bottom_right - v_bottom_left)
    
    # 4. Interpolate along rows to get final external temperature
    row_interp_factor = (t2_volt - min(v_row_top, v_row_bottom)) / (max(v_row_top, v_row_bottom) - min(v_row_top, v_row_bottom))
    final_temp = min_row + row_interp_factor * (max_row - min_row)
    
    return round(final_temp, 3)

# Apply the function to every row in raw_data
raw_data["External Temp"] = raw_data.apply(indirect_bilinear_interp, axis=1, ref_grid=ref_grid)

print(raw_data)

Key Adaptation Tips #

• Reference Table Parsing: If your actual reference table uses explicit min/max columns instead of range strings, adjust the interval extraction logic to match your structure.
• Edge Cases: The code includes basic handling for values at interval boundaries—expand this based on your specific requirements (e.g., clamping values to min/max if they fall outside all intervals).
• Performance: For extremely large datasets, consider replacing apply with vectorized operations. Use pd.cut to batch assign intervals first, then perform group-wise interpolation to speed things up.

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