Get the closest value to another value of a panda dataframe

July 8, 2022

I have a df like this

           SYMBOL        price   gainddS8  gainddS7_5   gainddS7  gainddS6_5  \
102  1000SHIBUSDT     0.016049 -32.899520  -30.866404 -28.833288  -26.800171    
9         ADAUSDT     0.572700 -15.371514  -2.5       -1.0        2.497339   
24       ALGOUSDT     0.391300 -1.117796    0.5104497  14.091197   16.077897

I want to get the closest value to "price" in all column for all symbols.
for example 1000SHIBUSDT, the cell gainddS6_5 is the closest to price.

Here is expected result

           SYMBOL        price  closestvalue
102  1000SHIBUSDT     0.016049 -26.800171
9         ADAUSDT     0.572700 -1.0
24       ALGOUSDT     0.391300  0.5104497

My code gave me the column name instead of the value
here is my code

gain = df.filter(like='gain').sub(1).abs().idxmin(axis=1)
df['closestvalue'] = gain
df = pd.DataFrame(df, columns=['SYMBOL', 'price','closestvalue'])
print(df)

>Solution :

Use numpy indexing wth position of minimal values by numpy.argmin:

df1 = df.filter(like='gain')
pos = df1.sub(df['price'], axis=0).abs().to_numpy().argmin(axis=1)
df['closestvalue'] = df1.to_numpy()[np.arange(len(df1)), pos]

print (df)
           SYMBOL     price   gainddS8  gainddS7_5   gainddS7  gainddS6_5  \
102  1000SHIBUSDT  0.016049 -32.899520  -30.866404 -28.833288  -26.800171   
9         ADAUSDT  0.572700 -15.371514   -2.500000  -1.000000    2.497339   
24       ALGOUSDT  0.391300  -1.117796    0.510450  14.091197   16.077897   

     closestvalue  
102    -26.800171  
9       -1.000000  
24       0.510450