Context
I want, for each team, the rows of the data frame that contains the top three scoring players.
In my head, it is a combination of Dataframe.nlargest()
and Dataframe.groupby()
but I don’t think this is supported.
My ideal solution is:
- performed directly on
df
without having to create other dataframes - legible, and
- relatively performant (real df shape is 7M rows and 5 col)
Input
import pandas as pd df = pd.read_json('{"team":{"0":"A","1":"A","2":"A","3":"A","4":"A","5":"B","6":"B","7":"B","8":"B","9":"B","10":"C","11":"C","12":"C","13":"C","14":"C"},"player":{"0":"Alice","1":"Becky","2":"Carmen","3":"Donna","4":"Elizabeth","5":"Fran","6":"Greta","7":"Heather","8":"Iris","9":"Jackie","10":"Kelly","11":"Lucy","12":"Molly","13":"Nina","14":"Ophelia"},"points":{"0":15,"1":11,"2":13,"3":8,"4":10,"5":28,"6":29,"7":18,"8":25,"9":9,"10":12,"11":23,"12":18,"13":10,"14":15}}')
| team | player | points | |------|-----------|--------| | A | Alice | 15 | | A | Becky | 11 | | A | Carmen | 13 | | A | Donna | 8 | | A | Elizabeth | 10 | | B | Fran | 28 | | B | Greta | 29 | | B | Heather | 18 | | B | Iris | 25 | | B | Jackie | 9 | | C | Kelly | 12 | | C | Lucy | 23 | | C | Molly | 18 | | C | Nina | 10 | | C | Ophelia | 15 |
Desired Output
df_output = pd.read_json('{"team":{"0":"A","1":"A","2":"A","3":"B","4":"B","5":"B","6":"C","7":"C","8":"C"},"player":{"0":"Alice","1":"Becky","2":"Carmen","3":"Fran","4":"Greta","5":"Iris","6":"Lucy","7":"Molly","8":"Ophelia"},"points":{"0":15,"1":11,"2":13,"3":28,"4":29,"5":25,"6":23,"7":18,"8":15}}') df_output
| team | player | points | |------|---------|--------| | A | Alice | 15 | | A | Becky | 11 | | A | Carmen | 13 | | B | Fran | 28 | | B | Greta | 29 | | B | Iris | 25 | | C | Lucy | 23 | | C | Molly | 18 | | C | Ophelia | 15 |
Advertisement
Answer
You can use df.groupby.rank
method:
In [1401]: df[df.groupby('team')['points'].rank(ascending=False) <= 3] Out[1401]: team player points 0 A Alice 15 1 A Becky 11 2 A Carmen 13 5 B Fran 28 6 B Greta 29 8 B Iris 25 11 C Lucy 23 12 C Molly 18 14 C Ophelia 15