df.stack() behaving differently between 0.19.2 and 0.20.1

[dpsugasa]

Code Sample, a copy-pastable example if possible

import pandas as pd
import numpy as np


index = pd.DatetimeIndex(['2017-05-04', '2017-05-05', '2017-05-08', '2017-05-09',
               '2017-05-10'],
              dtype='datetime64[ns]',name = 'date', freq='B')
columns = pd.MultiIndex(levels=[['HSBA LN Equity', 'UCG IM Equity', 'ISP IM Equity'], ['LAST PRICE', 'HIGH', 'LOW']],
           labels=[[0, 0, 0, 1, 1, 1, 2, 2, 2], [0, 1, 2, 0, 1, 2, 0, 1, 2]])
data = np.array([[ 663.8, 672.5, 661.1, 15.97, 16.02, 15.49, 2.76, 2.768, 2.694],
              [ 658.6, 663.9, 656.0, 16.22, 16.48, 15.77, 2.842, 2.868, 2.77 ],
              [ 660.6, 664.1, 658.9, 16.01, 16.49, 15.94, 2.852, 2.898, 2.826],
              [ 664.9, 669.2, 662.5, 15.90, 16.41, 15.90, 2.848, 2.898, 2.842],
              [ 670.9, 673.4, 663.8, 16.09, 16.15, 15.59, 2.85,  2.888, 2.802]])
df = pd.DataFrame(data, columns=columns, index = index)

Problem description

Since switching to 0.20.1, when using df.stack(0), the output looks like this:

                              HIGH  LAST PRICE      LOW
date                                                   
2017-05-04 HSBA LN Equity  672.500     663.800  661.100
           UCG IM Equity     2.768       2.760    2.694
           ISP IM Equity    16.020      15.970   15.490
2017-05-05 HSBA LN Equity  663.900     658.600  656.000
           UCG IM Equity     2.868       2.842    2.770
           ISP IM Equity    16.480      16.220   15.770
2017-05-08 HSBA LN Equity  664.100     660.600  658.900
           UCG IM Equity     2.898       2.852    2.826
           ISP IM Equity    16.490      16.010   15.940
2017-05-09 HSBA LN Equity  669.200     664.900  662.500
           UCG IM Equity     2.898       2.848    2.842
           ISP IM Equity    16.410      15.900   15.900
2017-05-10 HSBA LN Equity  673.400     670.900  663.800
           UCG IM Equity     2.888       2.850    2.802
           ISP IM Equity    16.150      16.090   15.590

The columns change order and the tickers no longer correspond to the correct prices.

` Expected Output

0.19.2 maintains the correct hierarchy.

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit: None python: 3.6.1.final.0 python-bits: 64 OS: Darwin OS-release: 16.5.0 machine: x86_64 processor: i386 byteorder: little LC_ALL: None LANG: None LOCALE: None.None

pandas: 0.20.1
pytest: 3.0.5
pip: 9.0.1
setuptools: 35.0.1
Cython: 0.25.2
numpy: 1.12.1
scipy: 0.19.0
xarray: None
IPython: 6.0.0
sphinx: 1.5.5
patsy: 0.4.1
dateutil: 2.6.0
pytz: 2017.2
blosc: None
bottleneck: 1.2.0
tables: 3.3.0
numexpr: 2.6.2
feather: None
matplotlib: 2.0.1
openpyxl: 2.4.1
xlrd: 1.0.0
xlwt: 1.2.0
xlsxwriter: 0.9.6
lxml: 3.7.2
bs4: 4.5.3
html5lib: 0.999999999
sqlalchemy: 1.1.5
pymysql: None
psycopg2: None
jinja2: 2.9.6
s3fs: None
pandas_gbq: None
pandas_datareader: 0.2.1

[dsm054]

Simpler case:

columns = pd.MultiIndex(levels=[['B', 'A'],
                                ['C', 'D']],
                        labels=[[0, 0, 1, 1], [0, 1, 0, 1]])
df = pd.DataFrame(columns=columns, data=[range(4)])
assert df.loc[0, ("A","C")] == df.stack(0).loc[(0, "A"), "C"]

passes under 0.19.2 for me and fails under 0.20.1.

[dsm054]

In 0.19.2, with the above dataframe, we have

In [8]: df.sortlevel(level=0, axis=1)
Out[8]: 
   B     A   
   C  D  C  D
0  0  1  2  3

In 0.20.1, we have

In [378]: df.sortlevel(level=0, axis=1)
/home/dsm/sys/miniconda3/envs/py36/bin/ipython:1: FutureWarning: sortlevel is deprecated, use sort_index(level= ...)
  #!/home/dsm/sys/miniconda3/envs/py36/bin/python
Out[378]: 
   A     B   
   C  D  C  D
0  2  3  0  1

or, to use the new syntax,

In [380]: df.sort_index(level=0, axis=1)
Out[380]: 
   A     B   
   C  D  C  D
0  2  3  0  1

That is, in 0.20.1, the sort actually affects the frame, and frame.columns and this.columns differ in _stack_multi_columns. As a result, I think this line:

    new_levels.append(frame.columns.levels[level_num])

is no longer justified, because the level order can have changed. I think that just using new_levels.append(level_vals) (taken from this after the sort) should suffice to repair it. Anyone else want to call dibs, or should I take a run at it?

[sinhrks]

Thanks for the report. It looks like a regression. I think @dsm054 's fix looks good.