How to return top N percent of Pandas DataFrame by grouping?

user_3486773 0
Viewed 1358

I have a dataframe df that looks like this:

Customer   Revenue  Products  Cluster
Jon         100         5        A
Janet       225         12       C
Jim         75          2        B
Stacy       95          4        A
John        125         5        B
Bob         325         20       C
Megan       75          2        A
Stacy       95          4        A

and so on for 100k records.

I am trying to return the top 2 percent of Revenue Customers grouped by cluster. I know this doesn't work but I think it gives the overall idea of what I am trying to do:

df.groupby('cluster').len(df['cluster'])/100 <= .2

Trying to get back a dataframe of the top 2 percent of customers by revenue, by cluster.

pandas-groupby pandas dataframe group-by python
1 Answers

IIUC, it looks like you can use GroupBy.quantile and then filter on the result:

# "Cluster" is the grouper, and
# "Revenue" is the column to calculate the quantile on. 
df[df['Revenue'] >= df.groupby('Cluster')['Revenue'].transform('quantile', 0.98)]

  Customer  Revenue  Products Cluster
0      Jon      100         5       A
4     John      125         5       B
5      Bob      325        20       C
user_49090870
Related