BUG: dangerous inconsistency when setting values on slice of multi-index

[sbitzer]

• [ x ] I have checked that this issue has not already been reported.

• [ x ] I have confirmed this bug exists on the latest version of pandas.

• (optional) I have confirmed this bug exists on the master branch of pandas.

---

Note: Please read this guide detailing how to provide the necessary information for us to reproduce your bug.

Code Sample, a copy-pastable example

df = pd.DataFrame(
    [[1, 2], [3, 4], [5, 6], [7, 8]], 
    index=pd.MultiIndex.from_product([['a', 'b'], [0, 1]]))

In [137]: df
Out[137]: 
     0  1
a 0  1  2
  1  3  4
b 0  5  6
  1  7  8

df2 = pd.DataFrame([[9, 10], [11, 12]], index=[1, 0])

In [139]: df2
Out[139]: 
    0   1
1   9  10
0  11  12

df.loc[('a', df2.index), :] = df2.values

In [141]: df
Out[141]: 
      0   1
a 0   9  10
  1  11  12
b 0   5   6
  1   7   8

Problem description

When assigning to df in the last step, I use the index of df2 to correctly align the rows of the two DataFrames, i.e., to select the order of elements in df to assign to. It turns out that this order is ignored in MultiIndex indexing. I believe this is dangerous, because df2.values on the right hand side is the only way to pass the values in df2 to df as long as df2 hasn't got the same levels as df (cf. #10440). Unsuspecting users who believe that MultiIndex indexing will work as indexing on a normal Index (I haven't found a warning about this in the docs) will most likely miss that their values have been sorted unexpectedly.

Expected Output

In [141]: df
Out[141]: 
      0   1
a 0  11  12
  1   9  10
b 0   5   6
  1   7   8

Output of pd.show_versions()

INSTALLED VERSIONS

commit : None
python : 3.7.6.final.0
python-bits : 64
OS : Windows
OS-release : 7
machine : AMD64
processor : Intel64 Family 6 Model 42 Stepping 7, GenuineIntel
byteorder : little
LC_ALL : None
LANG : de_DE.UTF-8
LOCALE : None.None

pandas : 1.0.3
numpy : 1.18.1
pytz : 2020.1
dateutil : 2.8.1
pip : 20.0.2
setuptools : 46.1.3.post20200330
Cython : None
pytest : 5.3.4
hypothesis : None
sphinx : 3.0.3
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 2.11.2
IPython : 7.13.0
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : 3.1.3
numexpr : 2.7.1
odfpy : None
openpyxl : 3.0.3
pandas_gbq : None
pyarrow : None
pytables : None
pytest : 5.3.4
pyxlsb : None
s3fs : None
scipy : 1.4.1
sqlalchemy : 1.3.13
tables : 3.6.1
tabulate : None
xarray : None
xlrd : 1.2.0
xlwt : None
xlsxwriter : None
numba : None

[simonjayhawkins]

Thanks @sbitzer for the report. I think this could be a duplicate of #31330.

[sbitzer]

Thanks @sbitzer for the report. I think this could be a duplicate of #31330.

I'd say #31330 is the underlying problem and the use case presented here gives an example where the consequences are not purely cosmetic, but could lead to wrong results without the user noticing.