Lookup closest value in Pandas DataFrame

Question

I have a DataFrame that contains the data shown below:

   soc [%]  r0 [ohm]     tau1 [s]   tau2 [s]  r1 [ohm]  r2 [ohm]     c1 [farad]    c2 [farad]
0       90  0.001539  1725.035378  54.339882  0.001726  0.001614  999309.883552  33667.261120
1       80  0.001385   389.753276  69.807148  0.001314  0.001656  296728.345634  42164.808208
2       70  0.001539   492.320311  53.697439  0.001139  0.001347  432184.454388  39865.959637
3       60  0.001539   656.942558  63.233445  0.000990  0.001515  663400.436465  41727.472274
4       50  0.001539   296.080424  53.948112  0.000918  0.001535  322490.860387  35139.878909
5       40  0.001539   501.978979  72.015509  0.001361  0.001890  368919.408585  38100.665763
6       30  0.001539   585.297624  76.972464  0.001080  0.001872  542060.285388  41114.220492
7       20  0.001385  1308.176576  60.541172  0.001426  0.001799  917348.863136  33659.124096
8       10  0.001539  1194.993755  57.078336  0.002747  0.001851  435028.073957  30839.130201

Given a value z, I want to select a row in the data frame where soc [%] is closest to z. The code below demonstrates my current approach.

import pandas as pd
import time


def rc_params(df, z):

    if z > 90:
        params = df.loc[0]
    elif 80 < z <= 90:
        params = df.loc[0]
    elif 70 < z <= 80:
        params = df.loc[1]
    elif 60 < z <= 70:
        params = df.loc[2]
    elif 50 < z <= 60:
        params = df.loc[3]
    elif 40 < z <= 50:
        params = df.loc[4]
    elif 30 < z <= 40:
        params = df.loc[5]
    elif 20 < z <= 30:
        params = df.loc[6]
    elif 10 < z <= 20:
        params = df.loc[7]
    else:
        params = df.loc[8]

    r0 = params['r0 [ohm]']
    tau1 = params['tau1 [s]']
    tau2 = params['tau2 [s]']
    r1 = params['r1 [ohm]']
    r2 = params['r2 [ohm]']

    return r0, tau1, tau2, r1, r2


start = time.time()

z = 20

df = pd.read_csv('results/soc_rc.csv')

r0, tau1, tau2, r1, r2 = rc_params(df, z)

end = time.time()

print(f"""
z = {z}
r0 = {r0:.4f}
tau1 = {tau1:.4f}
tau2 = {tau2:.4f}
r1 = {r1:.4f}
r2 = {r2:.4f}
run time = {end - start:.4g} s
""")

Results from the above code give:

z = 20
r0 = 0.0014
tau1 = 1308.1766
tau2 = 60.5412
r1 = 0.0014
r2 = 0.0018
run time = 0.002264 s

My approach works fine but is there a better (faster) way to lookup the values in the data frame? There is a lookup function in Pandas but it finds exact values, so if a value doesn't exist then nothing is returned.

Answer 1

Not sure if this will help, but I'm using this to find nearest in a sorted column: (time series stuff)

result_index = df['col_to_search'].sub(search_value).abs().idxmin()

.sub(search_value) subtracts search_value from the df[col_to_search] to make the nearest value almost-zero,
.abs() makes the almost-zero the minimum of the column,
.idxmin() yields the df.index of the minimum value, or the closest match to search_value.

I got this approach from a similar search, but didn't note the original author or the site I found it.

Answer 2

Adapting from here would be a cleaner way to do what you want.

params = df.iloc[(df['soc [%]']-z).abs().argsort()[:1]]

There might be faster ways if your soc [%] column is fixed with those values.

Also, you should consider not measuring the time for pd.read_csv as that isn't what you are wanting to know the execution for.