CODEWITHSUNDEEP
pythonpandas
J. Doe
J. Doe

Reputation: 21

Add rank field to pandas dataframe by unique groups and sorting by multiple columns

say I have this dataframe and I want every unique user ID to have its own rank value based on the date stamp:

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

So that's fine, but what if I wanted to add another field to serve as a tie-breaker when there are two of the same dates? I was hoping something would be as easy as:

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

but that doesn't work- any ideas?

ideal output:

    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

Edited to have real data
Looks like the top solution here doesn't handle zeros in the tie_breaker field correctly - any idea what's going on?

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

Upvotes: 2

Views: 639

Answers (1)

A.P.
A.P.

Reputation: 1159

With this dataframe:

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]})

One way to do it is:

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)

Output:

   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

Upvotes: 1

Related Questions