Custom sorting of the level 1 index of a multiindex Pandas DataFrame according to the level 0 index

Question

I have a multindex DataFrame, df:

arrays = [['bar', 'bar', 'baz', 'baz', 'baz', 'baz', 'foo', 'foo'],
          ['one', 'two', 'one', 'two', 'three', 'four', 'one', 'two']]

df = pd.DataFrame(np.ones([8, 4]), index=arrays)

which looks like:

             0    1    2    3
bar one    1.0  1.0  1.0  1.0
    two    1.0  1.0  1.0  1.0
baz one    1.0  1.0  1.0  1.0
    two    1.0  1.0  1.0  1.0
    three  1.0  1.0  1.0  1.0
    four   1.0  1.0  1.0  1.0
foo one    1.0  1.0  1.0  1.0
    two    1.0  1.0  1.0  1.0

I now need to sort the 'baz' sub-level into a new order, to create something that looks like df_end:

arrays_end = [['bar', 'bar', 'baz', 'baz', 'baz', 'baz', 'foo', 'foo'],
              ['one', 'two', 'two', 'four', 'three', 'one', 'one', 'two']]

df_end = pd.DataFrame(np.ones([8, 4]), index=arrays_end)

which looks like:

             0    1    2    3
bar one    1.0  1.0  1.0  1.0
    two    1.0  1.0  1.0  1.0
baz two    1.0  1.0  1.0  1.0
    four   1.0  1.0  1.0  1.0
    three  1.0  1.0  1.0  1.0
    one    1.0  1.0  1.0  1.0
foo one    1.0  1.0  1.0  1.0
    two    1.0  1.0  1.0  1.0

I thought that I might be able to reindex the baz row:

new_index = ['two','four','three','one']

df.loc['baz'].reindex(new_index)

Which gives:

         0    1    2    3
two    1.0  1.0  1.0  1.0
four   1.0  1.0  1.0  1.0
three  1.0  1.0  1.0  1.0
one    1.0  1.0  1.0  1.0

...and insert these values back into the original DataFrame:

df.loc['baz'] = df.loc['baz'].reindex(new_index)

But the result is:

             0    1    2    3
bar one    1.0  1.0  1.0  1.0
    two    1.0  1.0  1.0  1.0
baz one    NaN  NaN  NaN  NaN
    two    NaN  NaN  NaN  NaN
    three  NaN  NaN  NaN  NaN
    four   NaN  NaN  NaN  NaN
foo one    1.0  1.0  1.0  1.0
    two    1.0  1.0  1.0  1.0

Which is not what I'm looking for! So my question is how I can use new_index to reorder the rows in the baz index. Any advice would be greatly appreciated.

This seems a better solution for me: stackoverflow.com/questions/43073254/… — Pengin
– Pengin, Commented Oct 1, 2021 at 10:56

Tai · Accepted Answer · 2018-01-29 18:47:07Z

3

Edit: (to fit the desired layout)

arrays = [['bar', 'bar', 'baz', 'baz', 'baz', 'baz', 'foo', 'foo'],
          ['one', 'two', 'one', 'two', 'three', 'four', 'one', 'two']]

df = pd.DataFrame(np.arange(32).reshape([8, 4]), index=arrays)
new_baz_index = [('baz', i) for i in ['two','four','three','one']]
index = df.index.values.copy()
index[df.index.get_loc('baz')] = new_baz_index
df.reindex(index)

df.index.get_loc('baz') will get the location of the baz part as a slice object and we replace the part there only.

edited Jan 29, 2018 at 18:47

answered Jan 29, 2018 at 18:15

Tai

8,0643 gold badges31 silver badges50 bronze badges

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

2 Comments

tomp Over a year ago

Thanks for your incredibly quick and helpful reply Tai. This is excellent :)

Tai Over a year ago

@tomp no problem. Glad it helps.

BENY · Accepted Answer · 2018-01-29 18:40:16Z

1

Update :-)

pd.concat([df[df.index.get_level_values(level=0)!='baz'],df.reindex(list(zip(['baz']*4,['two','four','three','one'])))])
Out[1156]: 
             0    1    2    3
bar one    1.0  1.0  1.0  1.0
    two    1.0  1.0  1.0  1.0
foo one    1.0  1.0  1.0  1.0
    two    1.0  1.0  1.0  1.0
baz two    1.0  1.0  1.0  1.0
    four   1.0  1.0  1.0  1.0
    three  1.0  1.0  1.0  1.0
    one    1.0  1.0  1.0  1.0

edited Jan 29, 2018 at 18:40

answered Jan 29, 2018 at 18:07

BENY

324k22 gold badges176 silver badges250 bronze badges

1 Comment

tomp Over a year ago

Hi again Wen, and thanks for looking at this! The df in your answer matches the input df in my question, rather than the output. Is there a missing step?

Collectives™ on Stack Overflow

Custom sorting of the level 1 index of a multiindex Pandas DataFrame according to the level 0 index

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related