BUG: Discrepency between documentation and output for outer merge on index when left and right indices match and are unique #55992
Description
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 left = pd.DataFrame({"key": [2, 3, 1]}) right = pd.DataFrame({"key": [2, 3, 1]}) lefti = left.set_index("key") righti = right.set_index("key") for sort in [False, True]: result_key = left.merge(right, on="key", how="outer", sort=sort) result_index = lefti.merge(righti, left_index=True, right_index=True, how="outer", sort=sort).reset_index() print(f"{sort=!s:<5} {result_key.equals(result_index)=!s:<5}") # sort=False result_key.equals(result_index)=False # sort=True result_key.equals(result_index)=FalseIssue Description
Since #54611, merging dataframes produces a result that almost always matches the documented behaviour. The only exception appears to be an outer merge on an index when the left and right indexes are equal and unique. In this case, irrespective of sort=True/False, the returned result has the order of the input index.
Expected Behavior
Since there isn't a carve-out in the documentation for this specific corner-case, I would expect that pd.merge(..., how="outer") always sorts the output lexicographically according to the join key(s).
Installed Versions
INSTALLED VERSIONS ------------------ commit : b864b8eb54605124827e6af84aa7fbc816cfc1e0 python : 3.10.12.final.0 python-bits : 64 OS : Linux OS-release : 6.2.0-36-generic Version : #37~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Mon Oct 9 15:34:04 UTC 2 machine : x86_64 processor : x86_64 byteorder : little LC_ALL : None LANG : en_GB.UTF-8 LOCALE : en_GB.UTF-8 pandas : 2.1.0.dev0+1414.gb864b8eb54 numpy : 1.24.4 pytz : 2023.3 dateutil : 2.8.2 setuptools : 67.7.2 pip : 23.2.1 Cython : 3.0.0 pytest : 7.4.0 hypothesis : 6.82.2 sphinx : 6.2.1 blosc : None feather : None xlsxwriter : 3.1.2 lxml.etree : 4.9.3 html5lib : 1.1 pymysql : 1.4.6 psycopg2 : 2.9.6 jinja2 : 3.1.2 IPython : 8.14.0 pandas_datareader : None bs4 : 4.12.2 bottleneck : 1.3.7 dataframe-api-compat: None fastparquet : 2023.7.0 fsspec : 2023.6.0 gcsfs : 2023.6.0 matplotlib : 3.7.2 numba : 0.57.1 numexpr : 2.8.4 odfpy : None openpyxl : 3.1.2 pandas_gbq : None pyarrow : 12.0.1 pyreadstat : 1.2.2 python-calamine : None pyxlsb : 1.0.10 s3fs : 2023.6.0 scipy : 1.11.1 sqlalchemy : 2.0.19 tables : 3.8.0 tabulate : 0.9.0 xarray : 2023.7.0 xlrd : 2.0.1 zstandard : 0.19.0 tzdata : 2023.3 qtpy : 2.4.0 pyqt5 : None