问题描述
我想创建一个年度排名(所以在 2012 年,经理 B 为 1.在 2011 年,经理 B 再次为 1).我在 pandas rank 函数上苦苦挣扎了一段时间,不想诉诸 for 循环.
I would like to create a rank on year (so in year 2012, Manager B is 1. In 2011, Manager B is 1 again). I struggled with the pandas rank function for awhile and DO NOT want to resort to a for loop.
s = pd.DataFrame([['2012','A',3],['2012','B',8],['2011','A',20],['2011','B',30]], columns=['Year','Manager','Return']) Out[1]: Year Manager Return 0 2012 A 3 1 2012 B 8 2 2011 A 20 3 2011 B 30
<小时>
我遇到的问题是附加代码(之前认为这无关紧要):
The issue I'm having is with the additional code (didn't think this would be relevant before):
s = pd.DataFrame([['2012', 'A', 3], ['2012', 'B', 8], ['2011', 'A', 20], ['2011', 'B', 30]], columns=['Year', 'Manager', 'Return']) b = pd.DataFrame([['2012', 'A', 3], ['2012', 'B', 8], ['2011', 'A', 20], ['2011', 'B', 30]], columns=['Year', 'Manager', 'Return']) s = s.append(b) s['Rank'] = s.groupby(['Year'])['Return'].rank(ascending=False) raise Exception('Reindexing only valid with uniquely valued Index ' Exception: Reindexing only valid with uniquely valued Index objects
有什么想法吗?
这是我正在使用的真实数据结构.重新索引时遇到问题..
Any ideas?
This is the real data structure I am using.
Been having trouble re-indexing..
推荐答案
听起来你想按Year分组,然后按降序排列Returns.
It sounds like you want to group by the Year, then rank the Returns in descending order.
import pandas as pd s = pd.DataFrame([['2012', 'A', 3], ['2012', 'B', 8], ['2011', 'A', 20], ['2011', 'B', 30]], columns=['Year', 'Manager', 'Return']) s['Rank'] = s.groupby(['Year'])['Return'].rank(ascending=False) print(s)
产量
Year Manager Return Rank 0 2012 A 3 2 1 2012 B 8 1 2 2011 A 20 2 3 2011 B 30 1
<小时>
解决 OP 修改后的问题:错误消息
To address the OP's revised question: The error message
ValueError: cannot reindex from a duplicate axis
在尝试对索引中具有重复值的 DataFrame 进行 groupby/rank 时发生.您可以通过构造 s 在追加后具有唯一索引值来避免该问题:
occurs when trying to groupby/rank on a DataFrame with duplicate values in the index. You can avoid the problem by constructing s to have unique index values after appending:
s = pd.DataFrame([['2012', 'A', 3], ['2012', 'B', 8], ['2011', 'A', 20], ['2011', 'B', 30]], columns=['Year', 'Manager', 'Return']) b = pd.DataFrame([['2012', 'A', 3], ['2012', 'B', 8], ['2011', 'A', 20], ['2011', 'B', 30]], columns=['Year', 'Manager', 'Return']) s = s.append(b, ignore_index=True)
产量
Year Manager Return 0 2012 A 3 1 2012 B 8 2 2011 A 20 3 2011 B 30 4 2012 A 3 5 2012 B 8 6 2011 A 20 7 2011 B 30
<小时>
如果您已经使用
If you've already appended new rows using
s = s.append(b)
然后使用 reset_index 创建唯一索引:
then use reset_index to create a unique index:
s = s.reset_index(drop=True)