CODEWITHSUNDEEP
python-2.7pandasgroup-by
SBad
SBad

Reputation: 1345

double ranking in python after a groupby

I have done a groupby which resulted in a dataframe similar to the below example. 

df = pd.DataFrame({'a': ['A', 'A','A', 'B', 'B','B'], 'b': ['A1', 'A2','A3' ,'B1', 'B2','B3'], 'c': ['2','3','4','5','6','1'] })

>>> df
   a   b  c
0  A  A1  2
1  A  A2  3
2  A  A3  4
3  B  B1  5
4  B  B2  6
5  B  B3  1

desired output

>>> df
       a   b  c
    4  B  B2  6
    3  B  B1  5
    5  B  B3  1       
    2  A  A3  4
    1  A  A2  3
    0  A  A1  2

As you can see it is a double ranking based on column a then column b. We first start with the highest which is B and within B we also start with the highest which is B2.

how i can do that in python please

Upvotes: 4

Views: 178

Answers (3)

jezrael
jezrael

Reputation: 862661

I think need first get max values by aggregating, then create ordered Categorical by ordering by max indices and last sort_values working as you need:

c = df.groupby('a')['c'].max().sort_values(ascending=False)
print (c)
a
B    6
A    4
Name: c, dtype: object

df['a'] = pd.Categorical(df['a'], categories=c.index, ordered=True)
df = df.sort_values(by=['a', 'c'], ascending=[True, False])
print (df)
   a   b  c
4  B  B2  6
3  B  B1  5
5  B  B3  1
2  A  A3  4
1  A  A2  3
0  A  A1  2

Upvotes: 2

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210842

you can first find maxima in each group and sort your DF descending by this local maxima and column c:

In [49]: (df.assign(x=df.groupby('a')['c'].transform('max'))
            .sort_values(['x','c'], ascending=[0,0])
            .drop('x',1))
Out[49]:
   a   b  c
4  B  B2  6
3  B  B1  5
5  B  B3  1
2  A  A3  4
1  A  A2  3
0  A  A1  2

Upvotes: 2

Zero
Zero

Reputation: 76917

Use

In [1072]: df.sort_values(by=['a', 'c'], ascending=[False, False])
Out[1072]:
   a   b  c
4  B  B2  6
3  B  B1  5
5  B  B3  1
2  A  A3  4
1  A  A2  3
0  A  A1  2

Upvotes: 2

Related Questions