BUG: alignment in operations with multi-index

[znicholls]

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

• 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

import pandas as pd
a = pd.DataFrame(
    {2010: [1, 2, 3], 2020: [3, 4, 5]},
    index=pd.MultiIndex.from_product(
        [["a"], ["b"], [0, 1, 2]], names=["scen", "mod", "id"]
    ),
)

# yes the example is just a scalar so this is overkill but
# it illustrates a bigger problem
b = pd.Series(
    [0.4],
    index=pd.MultiIndex.from_product(
        [["b"], ["a"]], names=["mod", "scen"]
    ),
)

a.add(b, axis=0)

# result (below) is full of nans
             2010  2020
scen mod id            
a    b   0    NaN   NaN
         1    NaN   NaN
         2    NaN   NaN

Problem description

The output is all nans even though these two objects can be sensibly aligned (see workaround below).

Expected Output

             2010  2020
mod scen id            
b   a    0    1.4   3.4
         1    2.4   4.4
         2    3.4   5.4

There is a simple workaround, simply align before doing the operation (rather than letting the operation align for you).

b_aligned, a_aligned = b.align(a)
a_aligned.add(b_aligned, axis=0)
# gives result above

Given that there's such a simple workaround, it could just be worth noting this in the docs (i.e. saying that alignment can be tricky and won't always work internally, in which case the user can align themselves before performing the operation) rather than changing the code. I'm happy to add such a line to the docs if helpful.

If code changes are a better route, then flipping the way alignment is done in

pandas/pandas/core/ops/__init__.py

Line 306 in e4a2196

left, right = left.align(

(i.e. doing right, left = right.align(left...) rather than the current left, right = left.align(right...) fixes the problem but fails other tests so the simplest solution doesn't seem to be an acceptable one in this case.

Output of pd.show_versions()

INSTALLED VERSIONS

commit : b577f0b4a6862dbb3329a23b5c2d6b6dc045996a
python : 3.8.5.final.0
python-bits : 64
OS : Darwin
OS-release : 19.6.0
Version : Darwin Kernel Version 19.6.0: Tue Jan 12 22:13:05 PST 2021; root:xnu-6153.141.16~1/RELEASE_X86_64
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : en_AU.UTF-8
LOCALE : en_AU.UTF-8

pandas : 1.4.0.dev0+538.gb577f0b4a6
numpy : 1.19.2
pytz : 2020.1
dateutil : 2.8.1
pip : 20.2.3
setuptools : 49.1.0.post20200704
Cython : 0.29.21
pytest : 6.1.1
hypothesis : 5.37.1
sphinx : 3.2.1
blosc : None
feather : None
xlsxwriter : 1.3.6
lxml.etree : 4.5.2
html5lib : 1.1
pymysql : None
psycopg2 : None
jinja2 : 2.11.2
IPython : 7.18.1
pandas_datareader: None
bs4 : 4.9.3
bottleneck : 1.3.2
fsspec : 0.8.3
fastparquet : 0.4.0
gcsfs : 0.7.1
matplotlib : 3.2.2
numexpr : 2.7.1
odfpy : None
openpyxl : 3.0.5
pandas_gbq : None
pyarrow : 0.17.1
pyxlsb : None
s3fs : 0.4.2
scipy : 1.5.1
sqlalchemy : 1.3.19
tables : 3.6.1
tabulate : 0.8.7
xarray : 0.15.1
xlrd : 1.2.0
xlwt : 1.3.0
numba : 0.48.0

[mroeschke]

Closed by #46058