BUG: multi-indexing sorting on axis=1 on >0 levels

[WindJunkie]

In a DataFrame with MultiIndex, sorting on the level with date values does not do anything (order remains unchanged). This happens for both row and column indexes. In my case I am starting with a string index, converting that to datetime index, have not tried with datetime values at the start.

Code Sample, a copy-pastable example if possible

df = pd.DataFrame([[1, 2], [6, 7]])
df.columns = pd.MultiIndex.from_tuples([(0, '8/11/2016 12:00:00 AM'), (0, '8/9/2016 12:00:00 AM')], names=['l1', 'Date'])
df.columns.set_levels(df.columns.levels[1].to_datetime(), level=1, inplace=True)
df.sort_index(axis=1, level=1)

Expected Output

Columns ordered by date is the expected output.

Date    2016-08-09 00:00:00 2016-08-11 00:00:00
0   2   1
1   7   6

Actual output are columns in the original sort order (not even lexicographically sorted).

output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.5.1.final.0
python-bits: 64
OS: Windows
OS-release: 10
machine: AMD64
processor: Intel64 Family 6 Model 60 Stepping 3, GenuineIntel
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8

pandas: 0.18.1
nose: 1.3.7
pip: 8.1.1
setuptools: 20.3
Cython: 0.23.4
numpy: 1.11.0
scipy: 0.17.1
statsmodels: 0.6.1
xarray: None
IPython: 4.1.2
sphinx: 1.3.1
patsy: 0.4.0
dateutil: 2.5.1
pytz: 2016.2
blosc: None
bottleneck: 1.0.0
tables: 3.2.2
numexpr: 2.6.0
matplotlib: 1.5.1
openpyxl: 2.3.2
xlrd: 0.9.4
xlwt: 1.0.0
xlsxwriter: 0.8.4
lxml: 3.6.0
bs4: 4.4.1
html5lib: None
httplib2: None
apiclient: None
sqlalchemy: 1.0.12
pymysql: None
psycopg2: None
jinja2: 2.8
boto: 2.39.0
pandas_datareader: None

[jreback]

xref #13431

[jreback]

I seem to remember this exact issue, but can't find ATM.

[chris-b1]

As discussed in some of those issues, MultiIndex sorting is based on the ordering in the levels. The levels are sorted on construction, but not on re-assignment. So a couple workarounds would be to construct the mi with the converted levels, or the index also has a sort_values() method that sorts by values.

In [22]: df.reindex(columns = df.columns.sort_values())
Out[22]: 
l1            0           
Date 2016-08-09 2016-08-11
0             2          1
1             7          6

[jorisvandenbossche]

MultiIndex sorting is based on the ordering in the levels.

Is this something we could consider changing?
In my opinion, this behaviour does not make much sense from a user perspective. If you want such behaviour, you can now use explicitly a CategoricalIndex. For most users of MultiIndex, the fact that it is implemented with label/levels (codes/categories) is only an implementation detail.

[shoyer]

Rather than basing MultiIndex sorting on something other than sorted levels, what about requiring that each level always be sorted? I believe this is already done by default in every case where pandas constructs the MultiIndex levels, so this would only breaks cases where levels are provided explicitly in the MultiIndex constructor or set using set_levels.

[chris-b1]

That seems fairly reasonable, although the sorting behavior is documented, and I'm sure it would break somebody's code, though probably not too painful to detect/deprecate in 0.19, fix in 0.20 / 1.0?

[shoyer]

Yes, assuredly someone relies on the existing behavior, but we could probably deprecate it. In my opinion the fact that levels and labels can be sorted differently is a major source of confusion.

One option with set_level would be to automatically factorize new levels and change the underlying integer codes, too. That's probably not a good idea if someone explicitly wrote MultiIndex(levels, labels), though.

[toobaz]

@chris-b1 : are you referring to the sentence "the present implementation of MultiIndex requires that the labels be sorted for some of the slicing"? Actually, I think most people (including me, until few minutes ago) interpret "the labels be sorted" as "the MultiIndex be sorted", not "the initialization arrays for .levels be sorted". And if instead they give the second interpretation, well then they should already be following the design proposed by @shoyer .

So if I'm not missing anything, it would be possible and great to have already in 0.19 the following behaviour: for each component of .levels passed at initialization, if it is not sorted, sort it, rearranging the correspondent component of .labels.

By the way: another case in which bad things currently happen is in a .join() of unsorted dataframes:

In [1]: import pandas as pd
   ...: labels = ['c', 'b']
   ...: comp = pd.DataFrame(index=pd.MultiIndex.from_product([labels, labels],
   ...:                                names =['uid', 'oth']))
   ...: idists = pd.Series(0, index=labels, name='Charles')
   ...: idists.index.name = 'uid'
   ...: cc = comp.join(idists, how='inner').sort_index()
   ...: len(cc.index.levels), cc.index.lexsort_depth, cc.index.is_monotonic
   ...: 
Out[1]: (2, 0, True)