3

I have a dataset as follows:

name | $ | letter
adam, 34,  c
beny, 45,  e
adam, 55,  a
beny, 87,  t

I'd like to extract the max $ donated by each name, with the respective letter. So for Adam, I would get: adam,55,a.

If I use:

df.groupby('name')[['$']].max()

that doesn't give me the respective letter.

If I use:

df.groupby('name')[['$','letter']].max()

I get the max $ and the highest letter in the alphabet.

Adam Schroeder
  • 748
  • 2
  • 9
  • 23

1 Answers1

4

Use DataFrameGroupBy.idxmax for indexes of max values and then select by 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

Difference in solutions is idxmax let original indexes, last reset them.

jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252