Pandas DataFrame.to_xarray() is sorting dimensional coordinates

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

Comment From: jreback

cc @MaximilianR @shoyer

Comment From: 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 https://github.com/pydata/xarray/issues/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, https://github.com/pandas-dev/pandas/issues/14672 proposes a new keyword argument to MultiIndex.from_product to avoid sorting levels when constructing the MultiIndex.