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()
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.
Comment From: jreback
ok will close this then pending what we do with #14672 / MI sorting in general.