Identifying closest value in a column for each filter using Pandas
<p>I have a data frame with categories and values.  I need to find the value in each category closest to a value.  I think I'm close but I can't really get the right output when applying the results of argsort to the original dataframe.</p>

<p>For example, if the input was defined in the code below the output should have only <code>(a, 1, True)</code>, <code>(b, 2, True)</code>, <code>(c, 2, True)</code> and all other isClosest <code>Values</code> should be False.</p>

<p>If multiple values are closest then it should be the first value listed marked.</p>

<p>Here is the code I have which works but I can't get it to reapply to the dataframe correctly.  I would love some pointers.</p>

<pre><code>df = pd.DataFrame()
df['category'] = ['a', 'b', 'b', 'b', 'c', 'a', 'b', 'c', 'c', 'a']
df['values'] = [1, 2, 3, 4, 5, 4, 3, 2, 1, 0]
df['isClosest'] = False

uniqueCategories = df['category'].unique()
for c in uniqueCategories:
    filteredCategories = df[df['category']==c]    
    sortargs = (filteredCategories['value']-2.0).abs().argsort()
    #how to use sortargs so that we set column in df isClosest=True if its the closest value in each category to 2.0?
</code></pre>

<p>You can create a column of absolute differences:</p>

<pre><code>df['dif'] = (df['values'] - 2).abs()

df
Out: 
  category  values  dif
0        a       1    1
1        b       2    0
2        b       3    1
3        b       4    2
4        c       5    3
5        a       4    2
6        b       3    1
7        c       2    0
8        c       1    1
9        a       0    2
</code></pre>

<p>And then use <code>groupby.transform</code> to check whether the minimum value of each group is equal to the difference you calculated:</p>

<pre><code>df['is_closest'] = df.groupby('category')['dif'].transform('min') == df['dif']

df
Out: 
  category  values  dif is_closest
0        a       1    1       True
1        b       2    0       True
2        b       3    1      False
3        b       4    2      False
4        c       5    3      False
5        a       4    2      False
6        b       3    1      False
7        c       2    0       True
8        c       1    1      False
9        a       0    2      False
</code></pre>

<p><code>df.groupby('category')['dif'].idxmin()</code> would also give you the indices of the closest values for each category. You can use that for mapping too. </p>

<p>For selection:</p>

<pre><code>df.loc[df.groupby('category')['dif'].idxmin()]
Out: 
  category  values  dif
0        a       1    1
1        b       2    0
7        c       2    0
</code></pre>

<p>For assignment:</p>

<pre><code>df['is_closest'] = False
df.loc[df.groupby('category')['dif'].idxmin(), 'is_closest'] = True
df
Out: 
  category  values  dif is_closest
0        a       1    1       True
1        b       2    0       True
2        b       3    1      False
3        b       4    2      False
4        c       5    3      False
5        a       4    2      False
6        b       3    1      False
7        c       2    0       True
8        c       1    1      False
9        a       0    2      False
</code></pre>

<p>The difference between these approaches is that if you check equality against the difference, you would get True for all rows in case of ties. However, with <code>idxmin</code> it will return True for the first occurrence (only one for each group). </p>

python pandas group-by boolean closest