Sorting MultiIndex DataFrame

Question

I've been looking for a way to sort each row-level 0, column-level 1 pair within a MultiIndex DataFrame by the values they contain, but so far I haven't had any luck. For example, if my DataFrame looks like

import numpy as np
import pandas as pd

np.random.seed(7)
tup = (('A', 'B'), np.arange(2))
index = pd.MultiIndex.from_product(tup, names=('row-lvl 0', 'row-lvl 1'))
tup = (('X', 'Y'), ('q', 'p'))
columns = pd.MultiIndex.from_product(tup, names=('col-lvl 0', 'col-lvl 1'))
data = np.random.rand(4, 4)
df = pd.DataFrame(data, index=index, columns=columns)
print(df)

col-lvl 0                   X                   Y          
col-lvl 1                   q         p         q         p
row-lvl 0 row-lvl 1                                        
A         0          0.076308  0.779919  0.438409  0.723465
          1          0.977990  0.538496  0.501120  0.072051
B         0          0.268439  0.499883  0.679230  0.803739
          1          0.380941  0.065936  0.288146  0.909594

I would like it to be sorted in ascending order to look like

col-lvl 0                   X                   Y          
col-lvl 1                   q         p         q         p
row-lvl 0 row-lvl 1                                        
A         0          0.076308  0.538496  0.438409  0.072051
          1          0.977990  0.779919  0.501120  0.723465
B         0          0.268439  0.065936  0.288146  0.803739
          1          0.380941  0.499883  0.679230  0.909594

I've read the pandas documentation for sort_values and sort_index but they didn't seem to be what I'm looking for. Any help with this would be greatly appreciated.

BENY · Accepted Answer · 2020-03-29 22:38:30Z

1

This is not sort_values , since you ignore the index impact only check the value

for x , y in df.groupby(level=0):
...     df.loc[x]=np.sort(y.values,0)
...     
df
col-lvl 0                   X                   Y          
col-lvl 1                   q         p         q         p
row-lvl 0 row-lvl 1                                        
A         0          0.076308  0.538496  0.438409  0.072051
          1          0.977990  0.779919  0.501120  0.723465
B         0          0.268439  0.065936  0.288146  0.803739
          1          0.380941  0.499883  0.679230  0.909594

answered Mar 29, 2020 at 22:38

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Over a year ago

Thank you for your help! This worked perfectly. Note that if A or B are tuples this will throw an error, so you may need to do some re-indexing.

Collectives™ on Stack Overflow

Sorting MultiIndex DataFrame

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related