我有以下 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
请您参考如下方法:
使用双 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')
