Skip to main content
 首页 » 编程设计

python之通过唯一组将排名字段添加到 pandas 数据框并按多列排序

2025年05月04日16cyq1162

假设我有这个数据框,我希望每个唯一的用户 ID 都有自己的基于日期戳的排名值:

In [93]: 
df = pd.DataFrame({ 
'userid':['a', 'a', 'a', 'a', 'b', 'b', 'b', 'b'],  
'date_stamp':['2016-02-01', '2016-02-01', '2016-02-04', '2016-02-08', '2016-02-04', '2016-02-10', '2016-02-10', '2016-02-12'], 
'tie_breaker':[1,2,3,4,1,2,3,4]}) 
 
df['date_stamp'] = df['date_stamp'].map(lambda x: datetime.datetime.strptime(x, "%Y-%m-%d")) 
df['rank'] = df.groupby(['userid'])['date_stamp'].rank(ascending=True, method='min') 
df 
 
Out[93]: 
date_stamp  tie_breaker userid  rank 
0   2016-02-01  1   a   1 
1   2016-02-01  2   a   1 
2   2016-02-04  3   a   3 
3   2016-02-08  4   a   4 
4   2016-02-04  1   b   1 
5   2016-02-10  2   b   2 
6   2016-02-10  3   b   2 
7   2016-02-12  4   b   4 

这很好,但是如果我想在有两个相同的日期时添加另一个字段来作为决胜局怎么办?我希望事情会像这样简单:

df['rank'] = df.groupby(['userid'])[['date_stamp','tie_breaker']].rank(ascending=True, method='min') 

但这行不通 - 有什么想法吗?

理想输出:

    date_stamp  tie_breaker userid  rank 
0   2/1/16  1   a   1 
1   2/1/16  2   a   2 
2   2/4/16  3   a   3 
3   2/8/16  4   a   4 
4   2/4/16  1   b   1 
5   2/10/16 2   b   2 
6   2/10/16 3   b   3 
7   2/12/16 4   b   4 

经过编辑以获得真实数据
看起来这里的最佳解决方案没有正确处理 tie_breaker 字段中的零 - 知道发生了什么吗?

df = pd.DataFrame({ 
'userid':['10010012083198581013', '10010012083198581013', '10010012083198581013', '10010012083198581013','10010012083198581013'],  
'date_stamp':['2015-12-26 13:24:37', '2015-11-25 11:24:13', '2015-10-25 12:13:59', '2015-02-16 22:59:58','2015-08-17 11:43:43'], 
'tie_breaker':[460000156735858, 460000152444239, 460000147374709, 11083155016444116916,0]}) 
df['date_stamp'] = df['date_stamp'].map(lambda x: datetime.datetime.strptime(x, "%Y-%m-%d %H:%M:%S")) 
df['userid'] = df['userid'].astype(str) 
df['tie_breaker'] = df['tie_breaker'].astype(str) 
 
def myrank(g):  
    return pd.DataFrame(1 + np.lexsort((g['tie_breaker'].rank(), 
                                    g['date_stamp'].rank())), 
                    index=g.index) 
 
df['rank']=df.groupby(['userid']).apply(myrank) 
df.sort('date_stamp') 
 
Out[101]:   
date_stamp  tie_breaker userid  rank 
3   2015-02-16  11083155016444116916    10010012083198581013    2 
4   2015-08-17  0   10010012083198581013    1 
2   2015-10-25  460000147374709 10010012083198581013    3 
1   2015-11-25  460000152444239 10010012083198581013    5 
0   2015-12-26  460000156735858 10010012083198581013    4 

请您参考如下方法:

有了这个数据框:

df = pd.DataFrame({ 
'userid':['a', 'a', 'a', 'a', 'b', 'b', 'b', 'b'],  
'date_stamp':['2016-02-01', '2016-02-01', '2016-02-04', '2016-02-08', 
'2016-02-04', '2016-02-10', '2016-02-10', '2016-02-12'], 
'tie_breaker':[1,2,3,4,1,2,3,4]}) 

一种方法是:

def myrank(g):  
    return pd.DataFrame(1 + np.lexsort((g['tie_breaker'].rank(), 
                                        g['date_stamp'].rank())), 
                        index=g.index) 
 
 
df['rank']=df.groupby(['userid']).apply(myrank) 

输出:

   date_stamp  tie_breaker userid  rank 
0  2016-02-01            1      a     1 
1  2016-02-01            2      a     2 
2  2016-02-04            3      a     3 
3  2016-02-08            4      a     4 
4  2016-02-04            1      b     1 
5  2016-02-10            2      b     2 
6  2016-02-10            3      b     3 
7  2016-02-12            4      b     4