python - Pandas 变换组中的位置/等级

Question

我有以下 DataFrame，其中包含两组动物以及它们每天吃多少食物，

df = pd.DataFrame({'animals': ['cat', 'cat', 'dog', 'dog', 'rat', 
                               'cat', 'rat', 'rat', 'dog', 'cat'],
                   'food': [1, 2, 2, 5, 3, 1, 4, 0, 6, 5]},
                  index=pd.MultiIndex.from_product([['group1'] + ['group2'],
                                                    list(range(5))])
                     ).rename_axis(['groups', 'day'])

df

            animals food
groups  day     
group1  0   cat     1
        1   cat     2
        2   dog     2
        3   dog     5
        4   rat     3
group2  0   cat     1
        1   rat     4
        2   rat     0
        3   dog     6
        4   cat     5

我可以将其“映射”/转换成一个新列，以查看每天应该为每只动物提供多少食物 daily_meal。

df['daily_meal'] = df.groupby(['animals', 'groups']).transform('mean')
df

            animals food    daily_meal
groups  day         
group1  0   cat     1       1.5
        1   cat     2       1.5
        2   dog     2       3.5
        3   dog     5       3.5
        4   rat     3       3.0
group2  0   cat     1       3.0
        1   rat     4       2.0
        2   rat     0       2.0
        3   dog     6       6.0
        4   cat     5       3.0

我现在想知道 daily_meal 在每个组中的排名，并将其“映射”/转换为名为 group_rank 的新列。我该怎么做？

例如

            animals food    daily_meal   group_rank
groups  day         
group1  0   cat     1       1.5          1
        1   cat     2       1.5          1
        2   dog     2       3.5          3
        3   dog     5       3.5          3
        4   rat     3       3.0          2

group2  0   cat     1       3.0          2
        1   rat     4       2.0          1
        2   rat     0       2.0          1
        3   dog     6       6.0          3
        4   cat     5       3.0          2

Answer 1

使用双 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

或者:

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

谢谢 Scott Boston改进解决方案:

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')