BUG: Incomplete join with categorical MultiIndex

[jdemaeyer]

• 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.

---

When we join two dataframes with similar MultiIndexes, where one level is a CategoricalDtype, some of the rows are not properly joined:

Code Sample

import pandas as pd

df = pd.DataFrame()
df['major'] = pd.Series(list('AAAAB'))
df['minor'] = pd.Series(
    list('YXYXX'), dtype=pd.CategoricalDtype(['Y', 'X']))
df['value'] = pd.Series([1, 2, 3, 4, 5])
df.set_index(['major', 'minor'], inplace=True)

df1 = df.iloc[:2]
df2 = df.iloc[2:]

df1.join(df2, lsuffix='_left', rsuffix='_right')

Problem description

The code sample above gives us df1:

             value
major minor       
A     Y          1
      X          2

and df2:

             value
major minor       
A     Y          3
      X          4
B     X          5

If we left-join the two dataframes index-on-index, we expect the first two rows of df2 to be matched against the two rows of df1 (see Expected Output below). Instead, we find that the second row of df2 is ignored:

             value_left  value_right
major minor                         
A     Y               1          3.0
      X               2          NaN

Clues

I am way out of my depth here, but these are some things I noticed while trying to find a minimal example:

• The issue goes away if we define the categories for the minor index level in alphabetical order (i.e. dtype=pd.CategoricalDtype(['X', 'Y']) instead of ['Y', 'X']).
• The ordered parameter of CategoricalDtype has no influence.
• The third row in df2, although dropped during the left-join, is crucial. Joining df1 and df2.iloc[:2] yields the expected output.
• The issue does not occur when we perform an outer join
• I was not able to reproduce this issue with a single-level index.
• I was able to reproduce this bug w/ v1.1.3 as well but have not checked with any earlier versions.
• (Unrelated: Boy was I excited to finally give something back to pandas - even if it's just a bug report - after all that it's done for me over the years!)

Expected Output

             value_left  value_right
major minor                         
A     Y               1            3
      X               2            4

Output of pd.show_versions()

INSTALLED VERSIONS

commit : b5958ee
python : 3.9.0.final.0
python-bits : 64
OS : Linux
OS-release : 5.9.13-arch1-1
Version : #1 SMP PREEMPT Tue, 08 Dec 2020 12:09:55 +0000
machine : x86_64
processor :
byteorder : little
LC_ALL : None
LANG : en_US.utf8
LOCALE : en_US.UTF-8

pandas : 1.1.5
numpy : 1.19.4
pytz : 2019.3
dateutil : 2.8.1
pip : 20.3.1
setuptools : 51.0.0
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : 1.3.7
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : 2.8.6 (dt dec pq3 ext lo64)
jinja2 : None
IPython : 7.19.0
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : 3.0.5
pandas_gbq : None
pyarrow : None
pytables : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
numba : None

[simonjayhawkins]

Thanks @jdemaeyer for the report.

appears to work back in 0.25.3 so will mark as regression pending further investigation.

>>> pd.__version__
'0.25.3'
>>> df1.join(df2, lsuffix="_left", rsuffix="_right")
             value_left  value_right
major minor
A     Y               1            3
      X               2            4
>>>