sort_index doesn't work after concat

[mwiebusch78]

The sort_index method does not seem to work properly if the dataframe was created with concat. See this example:

>>> df = pd.DataFrame(index=['a', 'b'])
>>> pd.concat([df, df], keys=[0.8, 0.5]).sort_index()
Empty DataFrame
Columns: []
Index: [(0.8, a), (0.8, b), (0.5, a), (0.5, b)]

The 0.5 tuples should come before the 0.8 ones. Everything works fine if I create the multi-index from a product:

>>> pd.DataFrame(index=pd.MultiIndex.from_product([[0.8, 0.5], ['a', 'b']]))
Empty DataFrame
Columns: []
Index: [(0.8, a), (0.8, b), (0.5, a), (0.5, b)]
>>> pd.DataFrame(index=pd.MultiIndex.from_product([[0.8, 0.5], ['a', 'b']])).sort_index()
Empty DataFrame
Columns: []
Index: [(0.5, a), (0.5, b), (0.8, a), (0.8, b)]

I'm on pandas version 0.18.1.

[chris-b1]

Thanks for the report. xref #14015

There are two issues here. The first, which is the linked issue above is that sort_index sorts by the ordering of the levels, not necessarily lexicographically. In other words:

df = pd.DataFrame(index=pd.MultiIndex(levels=[[0.8, 0.5], ['a', 'b']], labels=[[0, 1], [0, 1]]))

df.index
Out[105]: 
MultiIndex(levels=[[0.8, 0.5], ['a', 'b']],
           labels=[[0, 1], [0, 1]])


df.sort_index()
Out[106]: 
Empty DataFrame
Columns: []
Index: [(0.8, a), (0.5, b)]

The specific problem here (which could be changed) - is that concat doesn't lexographically sort levels which constructing the resulting MultiIndex. Most other methods of a constructing a mi do sort the levels, so I think that change would be consistent.

[mwiebusch78]

Is there another method which re-orders a level of a multi-index? I've tried sortlevel but that doesn't have an effect either.

>>> df = pd.DataFrame(index=pd.MultiIndex(levels=[[0.8, 0.5], ['a', 'b']], labels=[[0, 1], [0, 1]]))
>>> df.sortlevel(0).index
MultiIndex(levels=[[0.8, 0.5], ['a', 'b']],
           labels=[[0, 1], [0, 1]])

[chris-b1]

You can call .sort_values on the MultiIndex itself and pass to reindex.

df.reindex(index=df.index.sort_values())
Out[156]: 
Empty DataFrame
Columns: []
Index: [(0.5, b), (0.8, a)]