Table of Contents
To find the closest value in a Pandas DataFrame, you can use the idxmin() or idxmax() methods. These methods take a column of numerical data, and return the index of the row that has the closest value to the provided value. For example, if you have a DataFrame of numbers and you want to find the closest value to 5, you can use idxmin() or idxmax() and set the argument to 5. This will return the index of the row that has the closest value to 5.
You can use the following basic syntax to find the row in a pandas DataFrame that contains the value closest to some specified value in a particular column:
#find row with closest value to 101 in points column df_closest = df.iloc[(df['points']-101).abs().argsort()[:1]]
The following example shows how to use this syntax in practice.
Example: Find Closest Value in Pandas DataFrame
Suppose we have the following pandas DataFrame that contains the number of points scored by various basketball teams:
import pandas as pd #create DataFrame df = pd.DataFrame({'team': ['Mavs', 'Nets', 'Hawks', 'Kings', 'Spurs', 'Cavs'], 'points': [99, 100, 96, 104, 89, 93]}) #view DataFrame print(df) team points 0 Mavs 99 1 Nets 100 2 Hawks 96 3 Kings 104 4 Spurs 89 5 Cavs 93
Now suppose we would like to select the row in the DataFrame that contains a value in the points column that is closest to 101.
We can use the following syntax to do so:
#find row with closest value to 101 in points column df_closest = df.iloc[(df['points']-101).abs().argsort()[:1]] #view results print(df_closest) team points 1 Nets 100
From the output we can see that the Nets have a value in the points column closest to 101.
Note that we could also use tolist() to only display the closest value itself instead of the entire row in the pandas DataFrame:
#display value closest to 101 in the points column df_closest['points'].tolist() [100]
Also note that we can change the value after the argsort() function to find several closest values.
For example, we can use the following syntax to find the rows in the DataFrame with the 2 closest values to 101 in the points column:
#find rows with two closest values to 101 in points column df_closest2 = df.iloc[(df['points']-101).abs().argsort()[:2]] #view results print(df_closest2) team points 1 Nets 100 0 Mavs 99
From the output we can see that the Nets have the closest value to 101 in the points column while the Mavs have the next closest value to 101 in the points column.