how to sort a multi-level pandas data-frame by a particular column?

Question

I want to sort a multi-index pandas dataframe by a column but don't want the entire dataframe to be sorted at once. But rather want to sort by one of the indices. Here is an example of what I mean: Below is an example of a multi-index dataframe.

first  second
bar    one       0.361041
       two       0.476720
baz    one       0.565781
       two       0.848519
foo    one       0.405524
       two       0.882497
qux    one       0.488229
       two       0.303862

And What I want is:

first  second
bar    two       0.476720
       one       0.361041
baz    two       0.848519
       one       0.565781
foo    two       0.882497
       one       0.405524
qux    two       0.488229
       one       0.303862

I want to sort the dataframe based on the third column i.e the numbers and keep the level 0 index constant. How can we do that?

i can't understand your question. could you clarify? – Atlas Bravoos Jun 05 '20 at 09:22 — Atlas Bravoos, Jun 05 '20 at 09:22

jezrael · Answer 1 · 2020-06-05T10:11:35.823

1

Use Series.reindex with sorted second level values:

a = s.reindex(sorted(s.index.levels[1], reverse=True), level=1)
print (a)
first  second
bar    two       0.476720
       one       0.361041
baz    two       0.848519
       one       0.565781
foo    two       0.882497
       one       0.405524
qux    two       0.303862
       one       0.488229
Name: a, dtype: float64

edited Jun 05 '20 at 10:11

answered Jun 05 '20 at 09:23

jezrael

822,522
95
1,334
1,252

`s.loc[('qux','three')]=1.03490` will reindexing add `three` to every group? – Ch3steR Jun 05 '20 at 09:59
@Ch3steR - You are right, edited answer for requirement – jezrael Jun 05 '20 at 10:02
`s.reindex(['two','three','one','four'], level=1)` I tried to reindex like this but `'three'` and `'four'` are not added to every group. It should add right and fill with NaNs right? – Ch3steR Jun 05 '20 at 10:08
@Ch3steR - hmm, for me working correct, `s.loc[('qux','three')]=1.03490` – jezrael Jun 05 '20 at 10:12
https://stackoverflow.com/questions/62214123/reindex-doesnt-fill-with-nan this is what i was mentioning. – Ch3steR Jun 05 '20 at 12:18

score 0 · Answer 2 · edited Jun 07 '20 at 09:59

You can use sort index to sort the index, and pass boolean of [True,False] to the ascending parameter

df.sort_index(level=[0,1],ascending=[True,False])

                Unnamed: 2
first   second  
bar      two    0.476720
         one    0.361041
baz      two    0.848519
         one    0.565781
foo      two    0.882497
         one    0.405524
qux      two    0.303862
         one    0.488229

how to sort a multi-level pandas data-frame by a particular column?

2 Answers2