DataFrame.to_xarray() is sorting dimensional coordinates

[ucyo]

Code Sample, a copy-pastable example if possible

import pandas as pd
import numpy as np

rd = np.zeros((4,4), dtype=np.float64)
lag = np.arange(4)[::-1]
# lag = np.random.randn(4) 
lev = np.arange(4)

iterables = [lag, lev]
index = pd.MultiIndex.from_product(iterables, names=['lag','lev'])
solution = pd.DataFrame(data=rd.reshape(rd.size, 1),index=index)
solution.columns = ['tmp']

solution.to_xarray()

Output:

<xarray.Dataset>
Dimensions:  (lag: 4, lev: 4)
Coordinates:
  * lag      (lag) int64 0 1 2 3
  * lev      (lev) int64 0 1 2 3
Data variables:
    tmp      (lag, lev) float64 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 ...

Problem description

The dimensions of the generated xarray.Dataset is sorted in ascending order for lag.
This is true for any entry (like the commented out np.random.randn(4) line)

Expected Output

<xarray.Dataset>
Dimensions:  (lag: 4, lev: 4)
Coordinates:
  * lag      (lag) int64 3 2 1 0
  * lev      (lev) int64 0 1 2 3
Data variables:
    tmp      (lag, lev) float64 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 ...

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit: None python: 3.4.5.final.0 python-bits: 64 OS: Linux OS-release: 3.10.0-327.22.2.el7.x86_64 machine: x86_64 processor: x86_64 byteorder: little LC_ALL: en_US.UTF-8 LANG: en_US.UTF-8 LOCALE: en_US.UTF-8

pandas: 0.19.1
nose: None
pip: 9.0.1
setuptools: 30.2.0
Cython: None
numpy: 1.11.2
scipy: 0.18.1
statsmodels: None
xarray: 0.8.2
IPython: 5.1.0
sphinx: None
patsy: None
dateutil: 2.6.0
pytz: 2016.10
blosc: None
bottleneck: None
tables: None
numexpr: None
matplotlib: 1.5.3
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
httplib2: None
apiclient: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.8
boto: None
pandas_datareader: None

[jreback]

cc @MaximilianR
@shoyer

[shoyer]

I agree that this is unintuitive, but unfortunately it's not obvious what the right fix is.

You can actually see the same sorting behavior with pandas's .unstack() method, for the same reason:

In [8]: solution.unstack()
Out[8]:
     tmp
lev    0    1    2    3
lag
0    0.0  0.0  0.0  0.0
1    0.0  0.0  0.0  0.0
2    0.0  0.0  0.0  0.0
3    0.0  0.0  0.0  0.0

The problem is that for unstacking (in pandas or with to_xarray()), we simply use the order of MultiIndex.labels, instead of order of appearance on the MultiIndex. See pydata/xarray#906 for an extensive discussion of the issue in the context of xarray's unstack() method.

One possible fix would be to change unstack() in both pandas and xarray to no longer use MultiIndex labels directly but instead always pull out in order of appearance, i.e.,

In [14]: solution.index.levels[0].take(pd.unique(solution.index.labels[0]))
Out[14]: Int64Index([3, 2, 1, 0], dtype='int64', name='lag')

This would be an API change but possibly would be a welcome development. The order of levels is mostly an implementation detail that users would rather not need to think about. I suppose there could also potentially be a keyword argument to switch this behavior on/off in unstack() (and possibly to_xarray()).

Alternatively, #14672 proposes a new keyword argument to MultiIndex.from_product to avoid sorting levels when constructing the MultiIndex.

[jreback]

ok will close this then pending what we do with #14672 / MI sorting in general.