How to select row with max value in column from pandas groupby() groups?

Question

I have a table like this:

import pandas as pd

df = pd.DataFrame(
        [
            ['john', 'rdgsdr', 2, 'A'],
            ['ann',  'dsdfds', 3, 'A'],
            ['john', 'jkfgdj', 1, 'B'],
            ['bob',  'xcxfcd', 5, 'A'],
            ['john', 'uityuu', 3, 'C'],
            ['ann',  'werwwe', 2, 'C'],
        ],
        columns=['name', 'stuff', 'orders', 'store']
    )

# df
#    name   stuff  orders store
# 0  john  rdgsdr       2     A
# 1   ann  dsdfds       3     A
# 2  john  jkfgdj       1     B
# 3   bob  xcxfcd       5     A
# 4  john  uityuu       3     C
# 5   ann  werwwe       2     C

I need to extract for each name the row with maximum number of orders; and also compute for that name the list of all the stores. Like this:

grouped = df.groupby('name')

for name, group in grouped:
    print('-'*5, name, '-'*5)
    print(group)

# ----- ann -----
#   name   stuff  orders store
# 1  ann  dsdfds       3     A  <- max(orders) for ann
# 5  ann  werwwe       2     C
# ----- bob -----
#   name   stuff  orders store
# 3  bob  xcxfcd       5     A  <- max(orders) for bob
# ----- john -----
#    name   stuff  orders store
# 0  john  rdgsdr       2     A
# 2  john  jkfgdj       1     B
# 4  john  uityuu       3     C  <- max(orders) for john

# ##########################
# This is what I want to get
# ##########################
>>> result
   name   stuff  max orders  all stores
1  ann   dsdfds           3         A,C
3  bob   xcxfcd           5           A
4  john  uityuu           3       A,B,C

I tried this:

result = grouped.agg(
        **{
            # 'stuff': 'stuff',
            'max orders': pd.NamedAgg('orders', max),
            'all stores': pd.NamedAgg('store', lambda s: s.str.join(',')),
        }
    )

But I don't know how to include the 'stuff' column in the result (in my real app I have many such additional columns, maybe dozens). And also, the join gives me lists instead of strings:

>>> result
   name  max orders all stores
0   ann           3     [A, C]
1   bob           5          A
2  john           3  [A, B, C]

BENY · Answer 1 · 2021-12-08T20:19:55.460

2

Try with first

out = df.set_index('stuff').groupby('name').agg(stuff = ('orders' , 'idxmax'),
                                          max_orders = ('orders' , 'max'),
                                          all_stores = ('store',','.join))#.reset_index()
Out[200]: 
       stuff  max_orders all_stores
name                               
ann   dsdfds           3        A,C
bob   xcxfcd           5          A
john  uityuu           3      A,B,C

edited Dec 08 '21 at 20:19

answered Dec 08 '21 at 20:00

BENY

317,841
20
164
234

It says ```TypeError: Must provide 'func' or tuples of '(column, aggfunc).``` – Amenhotep Dec 08 '21 at 20:05
Also, I think I need to tell it somehow that I need the `stuff` value corresponding to `max(orders)`, not just any (or the first) row in the group. – Amenhotep Dec 08 '21 at 20:07
@Amenhotep check the update – BENY Dec 08 '21 at 20:20
As I said, I have dozens of "stuff" columns, that one was just an example. – Amenhotep Dec 08 '21 at 21:01

score 1 · Accepted Answer · answered Dec 08 '21 at 20:20

You can do this by combining this answer with a groupby to get the list of stores they have worked at.

# Get stores that each person works at
stores_for_each_name = df.groupby('name')['store'].apply(','.join)

# Get row with largest order value for each name
df = df.sort_values('orders', ascending=False).drop_duplicates('name').rename({'orders': 'max_orders'}, axis=1)

# Replace store column with comma-separated list of stores they have worked at
df = df.drop('store', axis=1)
df = df.join(stores_for_each_name, on='name')

Output:

   name   stuff  max_orders  store
3   bob  xcxfcd           5      A
1   ann  dsdfds           3    A,C
4  john  uityuu           3  A,B,C

Also, I think your solution is very straight forward and efficient, since you don't recalculate things. Congratulations! — Amenhotep, Dec 09 '21 at 14:01

How to select row with max value in column from pandas groupby() groups?

2 Answers2