CODEWITHSUNDEEP
pythonpandassorting
Dickster
Dickster

Reputation: 3009

Sort by column within multi index level in pandas

I have a sorting request per example below.

Do i need to reset_index(), then sort() and then set_index() or is there a slick way to do this?

l = [[1,'A',99],[1,'B',102],[1,'C',105],[1,'D',97],[2,'A',19],[2,'B',14],[2,'C',10],[2,'D',17]]
df = pd.DataFrame(l,columns = ['idx1','idx2','col1'])
df.set_index(['idx1','idx2'],inplace=True)

# assume data has been received like this...
print df

           col1
idx1 idx2      
1    A       99
     B      102
     C      105
     D       97
2    A       19
     B       14
     C       10
     D       17

# I'd like to sort descending on col1, partitioning within index level = 'idx2'

           col1
idx1 idx2      
1    C      105
     B      102
     A       99
     D       97

2    A       19
     D       17
     B       14
     C       10

Thank you for the answer Note I change the data slightly:

l = [[1,'A',99],[1,'B',11],[1,'C',105],[1,'D',97],[2,'A',19],[2,'B',14],[2,'C',10],[2,'D',17]]
df = pd.DataFrame(l,columns = ['idx1','idx2','col1'])
df.set_index(['idx1','idx2'],inplace=True)
df = df.sort_index(by='col1', ascending=False)

however the output is 

idx1 idx2      
1    C      105
     A       99
     D       97
2    A       19
     D       17
     B       14
1    B       11
2    C       10

i would have wanted it to be 

idx1 idx2      
1    C      105
     A       99
     D       97
     B       11

2    A       19
     D       17
     B       14
     C       10

Upvotes: 30

Views: 21641

Answers (4)

JAB
JAB

Reputation: 12801

you can use sort_index:

 df.sort_index(level='col1', ascending=False)

Before version 0.17 level was by:

df.sort_index(by='col1', ascending=False)

This outputs:

             col1
idx1    idx2    
1       C    105
        B    102
        A    99
        D    97
2       A    19
        D    17
        B    14
        C    10

Upvotes: 15

Ashish Gulati
Ashish Gulati

Reputation: 335

Another way with a groupby (the already existing indexes) and an apply:

df.groupby(level=[0]).apply(lambda x:x.groupby(level=[1]).sum().sort_values('col1',ascending=False))

Output:

           col1
idx1 idx2      
1    C      105
     B      102
     A       99
     D       97
2    A       19
     D       17
     B       14
     C       10

Upvotes: 1

jezrael
jezrael

Reputation: 863166

You need DataFrame.reset_index, DataFrame.sort_values and DataFrame.set_index::

l = [[1,'A',99],[1,'B',11],[1,'C',105],[1,'D',97],
     [2,'A',19],[2,'B',14],[2,'C',10],[2,'D',17]]
df = pd.DataFrame(l,columns = ['idx1','idx2','col1'])
df.set_index(['idx1','idx2'],inplace=True)
print (df)
           col1
idx1 idx2      
1    A       99
     B       11
     C      105
     D       97
2    A       19
     B       14
     C       10
     D       17

df = df.reset_index() \
       .sort_values(['idx1','col1'], ascending=[True,False]) \
       .set_index(['idx1','idx2'])
print (df)
           col1
idx1 idx2      
1    C      105
     A       99
     D       97
     B       11
2    A       19
     D       17
     B       14
     C       10

EDIT:

For version 0.23.0 is possible use columns and index levels together (but buggy now if use ascending=[True, False], so maybe in newer versions):

df = df.sort_values(['idx1','col1'], ascending=[True,False])
print (df)

           col1
idx1 idx2      
1    C      105
     A       99
     D       97
     B       11
2    A       19
     D       17
     B       14
     C       10

Upvotes: 22

Kyle
Kyle

Reputation: 2894

This first sorts by the desired column, the resorts on the idx1 MultiIndex level only and works in up to date pandas versions that deprecate the by kwarg.

df.sort_values('col1', ascending=False).sort_index(level='idx1', sort_remaining=False)

Output:

             col1
idx1    idx2    
1       C    105
        B    102
        A    99
        D    97
2       A    19
        D    17
        B    14
        C    10

Upvotes: 9

Related Questions