Pandas Transform Position/rank In Group

I have the following DataFrame with two groups of animals and how much food they eat each day, df = pd.DataFrame({'animals': ['cat', 'cat', 'dog', 'dog', 'rat',

Solution 1:

Use double transform:

df['daily_meal'] = df.groupby(['animals', 'groups'])['food'].transform('mean')
df['group_rank'] = df.groupby('groups')['daily_meal'].rank(method='dense')
print (df)
           animals  food  daily_meal  group_rank
groups day                                      
group1 0       cat     1         1.5         1.0
       1       cat     2         1.5         1.0
       2       dog     2         3.5         3.0
       3       dog     5         3.5         3.0
       4       rat     3         3.0         2.0
group2 0       cat     1         3.0         2.0
       1       rat     4         2.0         1.0
       2       rat     0         2.0         1.0
       3       dog     6         6.0         3.0
       4       cat     5         3.0         2.0

Or:

s = df.groupby(['animals', 'groups'])['food'].transform('mean')
df['group_rank'] = s.groupby('groups').transform(lambda x: x.rank(method='dense'))
print (df)
           animals  food  group_rank
groups day                          
group1 0       cat     1         1.0
       1       cat     2         1.0
       2       dog     2         3.0
       3       dog     5         3.0
       4       rat     3         2.0
group2 0       cat     1         2.0
       1       rat     4         1.0
       2       rat     0         1.0
       3       dog     6         3.0
       4       cat     5         2.0

Thanks Scott Boston for improving solution:

df['daily_meal'] = df.groupby(['animals', 'groups'])['food'].transform('mean')
df['group_rank'] = df.groupby('groups')['daily_meal'].rank(method='dense')

---

s = df.groupby(['animals', 'groups'])['food'].transform('mean')
df['group_rank'] = s.groupby('groups').rank(method='dense')

Solution 2:

Using get_level_values + transform + rank

df.groupby([df.index.get_level_values(level='groups')])['daily_meal '].apply(lambda x : x.rank(method='dense'))
Out[1068]: 
groupsday
group1  01.011.023.033.042.0
group2  02.011.021.033.042.0
Name: daily_meal , dtype: float64

After assign

df['group_rank']=df.groupby([df.index.get_level_values(level='groups')])['daily_meal '].apply(lambda x : x.rank(method ='dense'))
df
Out[1070]: 
           animals  food  daily_meal   group_rank
groups day                                       
group1 0       cat     1          1.5         1.0
       1       cat     2          1.5         1.0
       2       dog     2          3.5         3.0
       3       dog     5          3.5         3.0
       4       rat     3          3.0         2.0
group2 0       cat     1          3.0         2.0
       1       rat     4          2.0         1.0
       2       rat     0          2.0         1.0
       3       dog     6          6.0         3.0
       4       cat     5          3.0         2.0

Here is the method I get the daily_meal

df['daily_meal ']=df.groupby([df.index.get_level_values(level='groups'),df.animals])['food'].transform('mean')