CODEWITHSUNDEEP
pandas
user2111827
user2111827

Reputation: 79

Trouble with groupss and aggregation

I'm trying to use pandas to select a single result from a group of results, where some column has a minimum value. An example table representing my data frame is:

ID   q  A B C D
---------------
1   10  1 2 3 4
1    5  5 6 7 8
2    1  9 1 2 3
2    2  8 7 6 5

I would like to group by ID and then select the row that has the smallest q for each group. So, the second row corresponding to ID=1 and the first row corresponding to ID=2 to be selected.

I can only select the lowest values of each column, which is not what I need. Thanks a lot to anybody who can offer some guidance.

Upvotes: 0

Views: 54

Answers (1)

Zelazny7
Zelazny7

Reputation: 40648

This should do what you're asking:

In [10]: df.groupby('ID').apply(lambda x: x.ix[x['q'].idxmin()])
Out[10]:
    ID  q  A  B  C  D
ID
1    1  5  5  6  7  8
2    2  1  9  1  2  3

Apply a function that returns the group row that has the index of the minimum 'q' value.

Upvotes: 1

Related Questions