BUG: joining dataframes with multi-index and None index label results in AssertionError

[nemausus]

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
df1 = pd.DataFrame({"A": [1]}, index=pd.MultiIndex.from_tuples([(3, 3)], names=["X", None]))
df2 = pd.DataFrame({"B": [1]}, index=pd.MultiIndex.from_tuples([(3, 3)], names=[None, "X"]))
df1.join(df2)

Issue Description

Fails with error: AssertionError: Length of order must be same as number of levels (2), got 1

Expected Behavior

Should not result in AssertionError

Installed Versions

INSTALLED VERSIONS

commit : bdc79c1
python : 3.9.18.final.0
python-bits : 64
OS : Darwin
OS-release : 23.4.0
Version : Darwin Kernel Version 23.4.0: Fri Mar 15 00:10:42 PDT 2024; root:xnu-10063.101.17~1/RELEASE_ARM64_T6000
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.2.1
numpy : 1.26.4
pytz : 2023.3.post1
dateutil : 2.8.2
setuptools : 68.2.2
pip : 23.3.1
Cython : None
pytest : 7.4.4
hypothesis : None
sphinx : 5.0.2
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.3
IPython : 8.15.0
pandas_datareader : None
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : 4.12.2
bottleneck : None
dataframe-api-compat : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : 15.0.1
pyreadstat : None
python-calamine : None
pyxlsb : None
s3fs : None
scipy : 1.13.0
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2024.1
qtpy : 2.4.1
pyqt5 : None

[SiddheshBangar]

Hey @nemausus, I just looked into your issue and the problem that i found is that the join method is trying to align the MultiIndex levels of the two DataFrames, but the names of the index levels are not aligned correctly.

>>> df1
     A
X     
3 3  1
>>> df2
     B
  X   
3 3  1

• df1 has a MultiIndex with names ["X", None]
• df2 has a MultiIndex with names [None, "X"]

So it gets confused because the index levels names do not match, even though the structure of the indices is the same.

So if the Data frames have the same names for their multiindex levels this issue can be fixed is what I think, as I tried to test it this is what I found:

>>> df2 = pd.DataFrame({"B": [1]}, index=pd.MultiIndex.from_tuples([(3, 3)], names=[None, "X"]))
>>> df1 = pd.DataFrame({"A": [1]}, index=pd.MultiIndex.from_tuples([(3, 3)], names=[None, "X"]))
>>> df1.join(df2)
     A  B
  X      
3 3  1  1

Let me know if this solves your issue or I can look more further into it, thanks.

[rhshadrach]

@SiddheshBangar - this raises an AssertionError - those shouldn't be exposed to the user (only things like ValueError, TypeError, etc). So this definitely looks like a bug. Also, if you replace None with "Y", then the operation is successful.

[SiddheshBangar]

Hey @rhshadrach, so you mean to say that the error message should not be prompt or either error message can be fixed somehow and replace it with assertion error message.

[rhshadrach]

At a glance, it seems to me that this should not raise an error. If implementation difficulties make that difficult, then perhaps we can look into other possibilities. Further investigations are welcome!

[SiddheshBangar]

Okay, cool thanks @rhshadrach this looks a bit confusing to me at this point but I will try to figure out what the best can I do, if you have any extra information to share feel free to drop down that can help me to resolve this issue, Thanks