pandas aggregate data while carrying a column unchanged

Question

I have a data frame, a:

a=pd.DataFrame({'ID': [1,1,2,2,3,4], 'B': [1,5,3,2,4,1], 'C': [1,4,3,6,1,1]})

   ID  B  C
0   1  1  1
1   1  5  4
2   2  3  3
3   2  2  6
4   3  4  1
5   4  1  1

And I want to aggregate it so that the resulting new data frame will be grouped by ID and return the row corresponding to min of B (so apply min() on B and carry C as is.

So the resulting data frame should be:

   ID  B  C
0   1  1  1
1   2  2  6
2   3  4  1
3   4  1  1

How can I do this programmatically using pandas.groupby(), or is there another way to do it?

Answer 1

You can use groupby and transform to filter rows

a.loc[a['B'] == a.groupby('ID').B.transform('min')]

    B   C   ID
0   1   1   1
3   2   6   2
4   4   1   3
5   1   1   4