Ranking in the pandas category in groups

Question

I cannot figure out how to write a function to execute a grouped percentile. I have all the teams from 1985 to 2012 in a data frame; the first 10 are shown below: they are currently sorted by year. I wanted to give a percentile for LgRnk , grouped by Year . So, for example, 23 LgRank (worst team) for 1985 will be 100 percentiles, and 1 LgRank (best team) for 1985 will be 1 percentile. 30 LgRank (worst team) for 2010 will be 100 percent, etc. It must be grouped by year b / c with a different number of LgRnk s.

  Team WLPer Year LgRnk WL 19 Sacramento Kings 0.378 1985 18 31 51 0 Atlanta Hawks 0.415 1985 17 34 48 17 Phoenix Suns 0.439 1985 16 36 46 4 Cleveland Cavaliers 0.439 1985 15 36 46 13 Milwaukee Bucks 0.720 1985 3 59 23 3 Chicago Bulls 0.463 1985 14 38 44 16 Philadelphia 76ers 0.707 1985 4 58 24 22 Washington Wizards 0.488 1985 13 40 42 20 San Antonio Spurs 0.500 1985 12 41 41 21 Utah Jazz 0.500 1985 11 41 41 

I tried to create a function using: scipy.stats.percentileofscore and I cannot get it.

Answer 1

You can apply in the LgRnk column:

 # just for me to normalize this, so my numbers will go from 0 to 1 in this example In [11]: df['LgRnk'] = g.LgRnk.rank() In [12]: g = df.groupby('Year') In [13]: g.LgRnk.apply(lambda x: x / len(x)) Out[13]: 19 1.0 0 0.9 17 0.8 4 0.7 13 0.1 3 0.6 16 0.2 22 0.5 20 0.4 21 0.3 Name: 1985, dtype: float64 

The groupby rank series (which simply applies Series.rank ) takes a pct argument to do just that:

 In [21]: g.LgRnk.rank(pct=True) Out[21]: 19 1.0 0 0.9 17 0.8 4 0.7 13 0.1 3 0.6 16 0.2 22 0.5 20 0.4 21 0.3 Name: 1985, dtype: float64 

and directly on the WLPer column (although this is slightly different due to a draw):

 In [22]: g.WLPer.rank(pct=True, ascending=False) Out[22]: 19 1.00 0 0.90 17 0.75 4 0.75 13 0.10 3 0.60 16 0.20 22 0.50 20 0.35 21 0.35 Name: 1985, dtype: float64 

Note. I changed the numbers in the first line, so that you get different ratings in your full frame.