How do I get the maximum value for every group and rank with all other groups?-CodePudding

I want to find the max value for every team and rank the team ascending. This is the dataframe:

TEAM   |  GROUP  |   SCORE  
1      |    A    |    5
1      |    B    |    5
1      |    C    |    5
2      |    D    |    6
2      |    A    |    6
3      |    D    |    5
3      |    A    |    5

No team should have the same rank so in case the score is similar who shows up first gets the first rank - others will adjust accordingly. So the output for this is:

TEAM   |  GROUP  |   SCORE   |  RANK
1      |    A    |    5      |   1
1      |    B    |    5      |   1
1      |    C    |    5      |   1
2      |    D    |    6      |   3
2      |    A    |    6      |   3
3      |    D    |    5      |   2
3      |    A    |    5      |   2

I'm not very familiar with some python syntax but here's what I have so far:

team = df.groupby(['TEAM'])

for x in team:
    df['Rank'] = x.groupby(['TEAM'])['SCORE'].max().rank()

CodePudding user response：

Please try the below which uses sorting on score and team, then gets the changes and does a cumulative sum for rank:

s = df[['TEAM','SCORE']].sort_values(['SCORE','TEAM'])
df['RANK']  = s['TEAM'].ne(s['TEAM'].shift()).cumsum()

print(df)

   TEAM GROUP  SCORE  RANK
0     1     A      5     1
1     1     B      5     1
2     1     C      5     1
3     2     D      6     3
4     2     A      6     3
5     3     D      5     2
6     3     A      5     2