Groupby showing other columns
I have a dataset like this:
name | $ | letter
adam, 34, c
beny, 45, e
adam, 55, a
beny, 87, t
I would like to extract the max $ donated by each name that matches the letter. So for Adam, I would get: adam, 55, a.
If I use:
df.groupby('name')[['$']].max()
which doesn't give me the corresponding letter.
If I use:
df.groupby('name')[['$','letter']].max()
I get max $ and the highest letter in the alphabet.
source to share
Use DataFrameGroupBy.idxmax
max values for indices and then select loc
:
print (df.groupby('name')['$'].idxmax())
name
adam 2
beny 3
Name: $, dtype: int64
df = df.loc[df.groupby('name')['$'].idxmax()]
print (df)
name $ letter
2 adam 55 a
3 beny 87 t
Another solution with sort_values
first and then use GroupBy.last
:
df = df.sort_values('$').groupby('name', as_index=False).last()
print (df)
name $ letter
0 adam 55 a
1 beny 87 t
The difference in solutions idxmax
allows the original indices to be last
reset to them.
source to share