Column Placeholder Rank

Question

I want to create a column manager_rankthat ranks the manager with sumreturns. I came up with one solution, posted below, but was hoping that if someone has something more elegant.

import pandas as pd
df = pd.DataFrame([['2012', 'A', 1], ['2012', 'B', 4], ['2011', 'A', 5], ['2011', 'B', 4]],
                 columns=['year', 'manager', 'return'])

Desired Result:

   year manager  return  manager_rank
0  2012       A       1             2
1  2011       A       5             2
2  2012       B       4             1
3  2011       B       4             1

Answer 1

df['ranking'] = df.groupby('manager')['return'].transform(np.sum).rank(ascending=False, method='dense')

   year manager  return  ranking
0  2012       A       1        2
1  2012       B       4        1
2  2011       A       5        2
3  2011       B       4        1

Answer 2

Single line:

manager_rank = (df.groupby('manager')
                  .sum()
                  ['return']
                  .rank(ascending=False)
                  .to_frame(name='manager_rank')
                  .reset_index()
                )

df = pd.merge(df, manager_rank, on='manager')

Step by Step Details:

1. Group by Manager with sumas an aggregation function

In [8]: df.groupby('manager').sum()
Out[8]: 
         return
manager        
A             6
B             8

2. Use rank()rank assignment for managers

In [9]: df.groupby('manager').sum().rank()
Out[9]: 
         return
manager        
A             1
B             2

In [10]: df.groupby('manager').sum().rank(ascending=False)
Out[10]: 
         return
manager        
A             2
B             1

3. Pass this result to another column

In [13]: df.groupby('manager').sum().rank(ascending=False)['return'].to_frame(name='manager_rank')
Out[13]: 
         manager_rank
manager              
A                   2
B                   1

4. Attach the result of the above steps to the original data frame!

df = pd.merge(df, manager_rank, on='manager')

Answer 3

to_frame name reset_index:

manager_rank = (df.groupby('manager')
                  .sum()
                  ['return']
                  .rank(ascending=False)
                  .reset_index(name='manager_rank')
                )

df = pd.merge(df, manager_rank, on='manager')
print df

   year manager  return  manager_rank
0  2012       A       1             2
1  2011       A       5             2
2  2012       B       4             1
3  2011       B       4             1

Answer 4

, @Stefan, ( , ).

df['total_return'] = (df
                      .groupby('manager')['return']
                      .transform(lambda group: (1 + group / 100.).cumprod().iat[-1])) - 1
df['ranking'] = df.total_return.rank(ascending=False, method='dense')

>>> df
   year manager  return  ranking  total_return
0  2012       A       1        2        0.0605
1  2012       B       4        1        0.0816
2  2011       A       5        2        0.0605
3  2011       B       4        1        0.0816