How would I groupby and rank in Pandas while excluding one value of the group?

Question

Say I have this dataframe pictured in the 1st screenshot below, I want to rank the scores by Day without considering the "Total" value for each group.

The 2nd screenshot below is what I would like the output to look it.

What's an efficient way to do this?

data = pd.DataFrame({'Day':['Monday','Monday','Monday','Monday','Tuesday','Tuesday','Tuesday','Tuesday'],
                 'City':['NY','Chicago','Miami','Total','NY','Chicago','Miami','Total'],
                 'Score':[100,90,70,260,120,80,90,290]})

Answer 1

Take a view of the non-Total rows and perform the rank then assign it to a new 'Rank' column. Since the indices are still preserved, the Cities will receive the proper rank but the Total rows will default to NaN.

data.loc[data['City'] != 'Total', 'Rank'] = data.loc[data['City'] != 'Total'].groupby('Day')['Score'].rank(ascending = False)

Answer 2

Try using rank

First filter out the rows that contain total and assign the rank using .loc

data.loc[data['City'] != 'Total', 'Rank'] = data.loc[data['City'] != 'Total'].groupby('Day')['Score'].rank(method='max', ascending=False)


       Day     City  Score  Rank
0   Monday       NY    100   1.0
1   Monday  Chicago     90   2.0
2   Monday    Miami     70   3.0
3   Monday    Total    260   NaN
4  Tuesday       NY    120   1.0
5  Tuesday  Chicago     80   3.0
6  Tuesday    Miami     90   2.0
7  Tuesday    Total    290   NaN

Answer 3

Another possible solution, which uses mask to set Score of Total to NaN before applying the rank function:

data['Rank'] = (
    data.assign(
        Score = data['Score'].mask(data['City'].eq('Total')))
    .groupby('Day')['Score']
    .rank(method='dense', ascending=False)
    )

Output:

       Day     City  Score  Rank
0   Monday       NY    100   1.0
1   Monday  Chicago     90   2.0
2   Monday    Miami     70   3.0
3   Monday    Total    260   NaN
4  Tuesday       NY    120   1.0
5  Tuesday  Chicago     80   3.0
6  Tuesday    Miami     90   2.0
7  Tuesday    Total    290   NaN