Searching for interpolated data from a pandas multi-index dataframe

Question

I have established a multi-index pandas dataframe as per below:

import pandas as pd
GEN_power_vs_PF_index = pd.MultiIndex.from_tuples([('GEN PF', '0.85'),
                                                   ('GEN PF', '0.9'),
                                                   ('GEN PF', '0.95'),
                                                   ('GEN PF', '1.0')])

GEN_power_vs_PF_columns = pd.MultiIndex.from_tuples([('Generator power at terminal, (kW)', 201875),
                                                     ('Generator power at terminal, (kW)', 403750),
                                                     ('Generator power at terminal, (kW)', 605625),
                                                     ('Generator power at terminal, (kW)', 807500),
                                                     ('Generator power at terminal, (kW)', 1009375)])

GEN_power_vs_PF = pd.DataFrame([(3217.6, 4259.1, 5847, 8024.1, 11059.1),
                                (3133.2, 4023, 5390.5, 7242.6, 9678.4),
                                (3045.4, 3791.7, 4954.4, 6538.8, 8564),
                                (2891.6, 3439.5, 4346.6, 5609.6, 7228.9)],
                               index=GEN_power_vs_PF_index,
                               columns=GEN_power_vs_PF_columns)

How do I search for an interpolated value from the dataframe, e.g. for a GEN PF value of 0.9769 and Generator power at terminal, (kW) of 736381.3?

I'm fairly new to pandas, and so far I've tried using the df.loc method, which is only good for looking up readily available data (without interpolation), and even then I wasn't able to perform a 2D search. I kept getting a Key Error.

Related to [this question](https://stackoverflow.com/questions/43772218/fastest-way-to-use-numpy-interp-on-a-2-d-array), pandas dataframe can be converted to numpy array. — Quang Hoang, Aug 18 '23 at 02:42
When you search, what do want the output to be? The index value and column name for the closest cell? Or the interpolated cell value? — Reinderien, Aug 18 '23 at 11:13

score 0 · Answer 1 · answered Aug 18 '23 at 05:50

You can modify this solution:

from scipy import interpolate
x = GEN_power_vs_PF.index.get_level_values(1).astype(float)
y = GEN_power_vs_PF.columns.get_level_values(1)
z = GEN_power_vs_PF.to_numpy()
# you have to set kx and ky small for this small example dataset
# 3 is more usual and is the default
# s=0 will ensure this interpolates.  s>0 will smooth the data
# you can also specify a bounding box outside the data limits
# if you want to extrapolate
sp = interpolate.RectBivariateSpline(x, y, z, kx=2, ky=2, s=0)

print (sp([0.9769], [736381.3]))
[[5528.38273829]]

Searching for interpolated data from a pandas multi-index dataframe

1 Answers1