Deepak Ram Deepak Ram - 4 years ago 198
Python Question

python pandas conditional ranking

A1 1
A2 2
A3 1.5
B1 1
B2 -2
B3 2
B4 -1.5


Now I want a output of rank within each regex A*, B* etc.,

A1 1 3
A2 2 1
A3 1.5 2
B1 1 2
B2 -2 4
B3 2 1
B4 -1.5 3


How can I achieve this directly using pandas inbuilt functionalities as .rank gives only rank in entire set.

Answer Source

You can use groupby by Series created by extract with rank:

print (df)
    a    b
0  A1  1.0
1  A2  2.0
2  A3  1.5
3  B1  1.0
4  B2 -2.0
5  B3  2.0
6  B4 -1.5

a = df.a.str.extract('([a-zA-Z])', expand=False)
print (a)
0    A
1    A
2    A
3    B
4    B
5    B
6    B
Name: a, dtype: object

df['c'] = df.groupby(a).b.rank(method='dense', ascending=False).astype(int)
print (df)
    a    b  c
0  A1  1.0  3
1  A2  2.0  1
2  A3  1.5  2
3  B1  1.0  2
4  B2 -2.0  4
5  B3  2.0  1
6  B4 -1.5  3
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download