BUG: Adding DataFrames with misaligned MultiIndex produces NaN despite fill_value=0

[michaelpradel]

Pandas version checks

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

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

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd

# Create two DataFrames with MultiIndex
index1 = pd.MultiIndex.from_tuples([('A', 'one'), ('A', 'two')])
index2 = pd.MultiIndex.from_tuples([('B', 'one'), ('B', 'two')])

df1 = pd.DataFrame([[1, 2]], columns=index1)
df2 = pd.DataFrame([[3, 4]], columns=index2)

# Adding DataFrames with different MultiIndex
result = df1.add(df2, fill_value=0)
print(result)

Issue Description

I have two data frames with unaligned multi-indices. When adding the two data frames, with fill_value=0, I'd expect the missing values to be replaced with zero before performing the addition operation, as described in the documentation of DataFrame.add. However, the above example produces this output:

    A       B    
  one two one two
0 NaN NaN NaN NaN

The problem affects the current main branch (tested with commit e557039). No released version is affected (yet). The behavior was introduced by #60538. Before this PR, the code was producing the expected output.

Expected Behavior

I'd expect this output:

A B
one two one two
0 1.0 2.0 3.0 4.0

Installed Versions

INSTALLED VERSIONS ------------------ commit : 72fd708 python : 3.10.8 python-bits : 64 OS : Linux OS-release : 5.15.0-130-generic Version : #140-Ubuntu SMP Wed Dec 18 17:59:53 UTC 2024 machine : x86_64 processor : byteorder : little LC_ALL : None LANG : C.UTF-8 LOCALE : en_US.UTF-8

pandas : 3.0.0.dev0+1839.g72fd708761
numpy : 1.26.4
dateutil : 2.9.0.post0
pip : 24.2
Cython : 3.0.11
sphinx : 8.1.3
IPython : 8.30.0
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : 4.12.3
blosc : None
bottleneck : 1.4.2
fastparquet : 2024.11.0
fsspec : 2024.10.0
html5lib : 1.1
hypothesis : 6.122.4
gcsfs : 2024.10.0
jinja2 : 3.1.4
lxml.etree : 5.3.0
matplotlib : 3.10.0
numba : 0.60.0
numexpr : 2.10.2
odfpy : None
openpyxl : 3.1.5
psycopg2 : 2.9.10
pymysql : 1.4.6
pyarrow : 18.1.0
pyreadstat : 1.2.8
pytest : 8.3.4
python-calamine : None
pytz : 2024.2
pyxlsb : 1.0.10
s3fs : 2024.10.0
scipy : 1.14.1
sqlalchemy : 2.0.36
tables : 3.10.1
tabulate : 0.9.0
xarray : 2024.9.0
xlrd : 2.0.1
xlsxwriter : 3.2.0
zstandard : 0.23.0
tzdata : 2024.2
qtpy : None
pyqt5 : None

[asishm]

Thanks for the report and for the bisect @michaelpradel
cc @snitish

[snitish]

take

[snitish]

Thanks for the report. Raised a PR with a fix.