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

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]

Continue reading...