Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find row closest value to input

Tags:

python

pandas

I am struggling with this problem. I have an pandas array which looks like this

               delta_n    column_1   ...
0                10      10        ...
1                20       0
2                30       0

Now i have a number, lets say i search for delta_n=20.5 and I want to select the row closest to the number of delta_n.

My output should be:

1                20       0

I tried it with df.loc[20.5] but as it is not in the pd dataframe it doesn't work.

Thanks, R

like image 362
raffa_sa Avatar asked Oct 01 '18 08:10

raffa_sa


People also ask

How do you find the closest value in a data frame?

To find the row corresponding to a nearest value in an R data frame, we can use which. min function after getting the absolute difference between the value and the column along with single square brackets for subsetting the row. To understand how it works, check out the examples given below.

How do I find the closest value in Python?

We can find the nearest value in the list by using the min() function. Define a function that calculates the difference between a value in the list and the given value and returns the absolute value of the result. Then call the min() function which returns the closest value to the given value.

What is Idxmin?

The idxmin() method returns a Series with the index of the minimum value for each column. By specifying the column axis ( axis='columns' ), the idxmin() method returns a Series with the index of the minimum value for each row.


1 Answers

Subtract value by sub, get absolute values by abs, find index with minimal value by idxmin and last select by loc:

idx = df['delta_n'].sub(delta_n).abs().idxmin()

#added double [[]] for one row DataFrame
df1 = df.loc[[idx]]
print (df1)
   delta_n  column_1
1       20         0

#output Series with one []
s = df.loc[idx]
print (s)
delta_n     20
column_1     0
Name: 1, dtype: int64

Details:

print (df['delta_n'].sub(delta_n))
0   -10.5
1    -0.5
2     9.5
Name: delta_n, dtype: float64

print (df['delta_n'].sub(delta_n).abs())
0    10.5
1     0.5
2     9.5
Name: delta_n, dtype: float64

print (df['delta_n'].sub(delta_n).abs().idxmin())
1

Another numpy solution for positions by numpy.argmin and selecting by iloc:

pos = df['delta_n'].sub(delta_n).abs().values.argmin()
print (pos)
1

df1 = df.loc[[pos]]
print (df1)
   delta_n  column_1
1       20         0

s = df.loc[pos]
print (s)
delta_n     20
column_1     0
Name: 1, dtype: int64
like image 81
jezrael Avatar answered Oct 05 '22 19:10

jezrael