BUG: Join() Does NOT Preserve Calling DataFrame Index Orders with Multi-Index

[changxin-ge]

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

# First dataframe
idx1 = ['a', 'b', 'c']
df1 = pd.DataFrame([[1, 2, 3]], columns=idx1).set_index(idx1)

# Second dataframe
idx2 = ['b', 'c']
df2 = pd.DataFrame([[2, 3]], columns=idx2).set_index(idx2)

# Inner join
# Expect to have 'a', 'b', 'c' in df3 index
# Got 'b', 'c', 'a' instead which doesn't preserve calling
# DataFrame (df1) index order
df3 = df1.join(df2, how='inner')
df3.head(2)

Issue Description

Pandas.DataFrame.join stated that:

how{‘left’, ‘right’, ‘outer’, ‘inner’}, default ‘left’

• ...
• inner: form intersection of calling frame’s index (or column if on is specified) with other’s index, preserving the order of the calling’s one.

In cases, as provided in the example, if other (2nd) DataFrame has an index(indices) that is(are) not the first N indices in the calling DataFrame, the resulting DataFrame from the join will not preserve the index order of the calling DataFrame. Instead, the overlapping index(indices) will be moved to the front and the rest to the back in the multi-index names.

Expected Behavior

Expected Resulting DataFrame from the Join:

a	b	c
1	2	3

Got:

b	c	a
2	3	1

Installed Versions

INSTALLED VERSIONS

commit : 4bfe3d0
python : 3.9.12.final.0
python-bits : 64
OS : Darwin
OS-release : 21.4.0
Version : Darwin Kernel Version 21.4.0: Fri Mar 18 00:45:05 PDT 2022; root:xnu-8020.101.4~15/RELEASE_X86_64
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 1.4.2
numpy : 1.22.3
pytz : 2022.1
dateutil : 2.8.2
pip : 21.2.4
setuptools : 61.2.0
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : 3.0.3
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : 8.3.0
pandas_datareader: None
bs4 : 4.11.1
bottleneck : 1.3.4
brotli :
fastparquet : None
fsspec : 2022.3.0
gcsfs : None
markupsafe : 2.1.1
matplotlib : None
numba : None
numexpr : 2.8.1
odfpy : None
openpyxl : 3.0.9
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : None
snappy : None
sqlalchemy : 1.3.23
tables : None
tabulate : 0.8.9
xarray : None
xlrd : None
xlwt : None
zstandard : None

[simonjayhawkins]

Thanks @changxin-ge for the report.

• inner: form intersection of calling frame’s index (or column if on is specified) with other’s index, preserving the order of the calling’s one.

I think this refers to the rows and not the levels.

The code sample uses empty DataFrames and a single row. If we use more than one row and include column data, I think this shows why the order of the levels in the result is correct.

import pandas as pd

# First dataframe
idx1 = ["a", "b", "c", "d"]
df1 = pd.DataFrame([[2, 2, 3, 7], [1, 2, 3, 4]], columns=idx1).set_index(idx1[:-1])
print(df1)

# Second dataframe
idx2 = ["b", "c", "e"]
df2 = pd.DataFrame([[2, 3, 5]], columns=idx2).set_index(idx2[:-1])
print(df2)

# Inner join
# Expect to have 'a', 'b', 'c' in df3 index
# Got 'b', 'c', 'a' instead which doesn't preserve calling
# DataFrame (df1) index order
df3 = df1.join(df2, how="inner")
print(df3)

and we see that the order of the first DataFrame rows is persevered.

       d
a b c   
2 2 3  7
1 2 3  4
     e
b c   
2 3  5
       d  e
b c a      
2 3 2  7  5
    1  4  5

[changxin-ge]

@simonjayhawkins
Thank you for making the clarification on the documentation. Will close this issue, and have the following question and comment:

• Is there a plan for implementing index order preservation column-wise?
• It would be nice if pandas could make the documentation more clear on the join portion with this aspect.

Thank you!