Group by week and rank to pick top 5

user_17212995 0
Viewed 128

I have a DataFrame with one month's data:

initiated_date      | specialist_id  |  rating
21/10/2020 05:00:01 |   ab12         |    8.1
21/10/2020 12:20:01 |   gc35         |    7.3         
22/10/2020 04:30:01 |   ad32         |    6.4
22/10/2020 03:40:01 |   fe45         |    9.2
22/10/2020 01:50:01 |   ad32         |    8.2
23/10/2020 02:10:01 |   iu99         |    8.7
23/10/2020 11:30:01 |   iu99         |    7.9

I want to breakdown per week for the top 5 specialists with respect to average rating. I am trying to replicate the rank partition by function of sql here.

What I am looking for is:

Week  |  specialist_id |  avg.rating   |  ranking 
22    |   ab12         |    9.8        |    1
22    |   gb22         |    9.1        |    2
22    |   po90         |    8.8        |    3
22    |   nh78         |    8.3        |    4
22    |   hj89         |    8.1        |    5
23    |   bg77         |    9.7        |    1
23    |   ab12         |    9.5        |    2

This is what I am trying to do

df.groupby([df['initiated_date'].dt.week])['rating'].mean().reset_index()

I am not sure how to go further.

pandas-groupby pandas dataframe python
2 Answers

Let us do in steps:

  • groupby week and specialist_id and calculate avg_rating
  • groupby week and assign the numerical rank for avg_rating per week
  • (Optional) sort the values by week and rank
w = df.initiated_date.dt.isocalendar().week
df1 = df.groupby([w, 'specialist_id'])['rating'].mean().reset_index(name='avg_rating')
df1['rank'] = df1.groupby('week', as_index=False)['avg_rating'].rank(method='dense', ascending=False)
df1 = df1.sort_values(['week', 'rank'])

print(df1) 
   week specialist_id  avg_rating  rank
2    43          fe45         9.2   1.0
4    43          iu99         8.3   2.0
0    43          ab12         8.1   3.0
1    43          ad32         7.3   4.0
...
user_128331660

You need a double groupby, first to compute the mean per week+specialist, then to rank the specialists per week. Finally, select the ranks up to your target. Read carefully the rank documentation, there are many methods depending on how you want to handle identical values.

(df
 .assign(week=pd.to_datetime(df['initiated_date']).dt.isocalendar().week)
 .groupby(['week', 'specialist_id'])
 .agg(avg_rating=('rating', 'mean'))
 .assign(rank=lambda d: d.groupby('week')['avg_rating'].rank(method='dense', ascending=False))
 .query('rank <= 5')
)

Output:

                    avg_rating  rank
week specialist_id                  
43   ab12                  8.1   3.0
     ad32                  7.3   4.0
     fe45                  9.2   1.0
     gc35                  7.3   4.0
     iu99                  8.3   2.0
user_163434640
Related