Pandas GroupBy and select rows with the minimum value in a specific column

Question

I am grouping my dataset by column A and then would like to take the minimum value in column B and the corresponding value in column C.

data = pd.DataFrame({'A': [1, 2], 'B':[ 2, 4], 'C':[10, 4]}) data       A   B   C 0   1   4   3 1   1   5   4 2   1   2   10 3   2   7   2 4   2   4   4 5   2   6   6   

and I would like to get :

    A   B   C 0   1   2   10 1   2   4   4 

For the moment I am grouping by A, and creating a value that indicates me the rows I will keep in my dataset:

a = data.groupby('A').min() a['A'] = a.index to_keep = [str(x[0]) + str(x[1]) for x in a[['A', 'B']].values] data['id'] = data['A'].astype(str) + data['B'].astype('str') data[data['id'].isin(to_keep)] 

I am sure that there is a much more straight forward way to do this. I have seen many answers here that use multi-indexing but I would like to do this without adding multi-index to my dataframe. Thank you for your help.

Answer 1

I feel like you're overthinking this. Just use groupby and idxmin:

df.loc[df.groupby('A').B.idxmin()]     A  B   C 2  1  2  10 4  2  4   4 

---

df.loc[df.groupby('A').B.idxmin()].reset_index(drop=True)     A  B   C 0  1  2  10 1  2  4   4 

Answer 2

Had a similar situation but with a more complex column heading (e.g. "B val") in which case this is needed:

df.loc[df.groupby('A')['B val'].idxmin()]