pandas (original) (raw)

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 master branch of pandas.

Reproducible Example

import pandas as pd

left = pd.DataFrame( {"C1": "Hi"}, index=pd.Index(["A"], name="abc") ) right = pd.DataFrame( {"C1": "Bye"}, index=pd.Index(["A"], name="ABC"))

result = pd.merge(left, right, how="left", left_on="abc", right_on="ABC")

Issue Description

I get a KeyError when I attempt to merge using indices of different names.

Exception has occurred: KeyError
"None of ['abc'] are in the columns"
  File "C:\Users\<redacted>\pd-merge-bug.py", line 11, in <module>
    result = pd.merge(left, right, left_on="abc", right_on="ABC")

This holds for left.merge(right, how="left", left_on="abc", right_on="ABC") as well.

This is appears to only an issue when the join key is an index. Although using left_index=True and right_index=True would be a workaround for this simple case, it is not feasible in MultiIndex cases with different numbers of levels or matching on a subset of levels. Furthermore, the documentation does not state any limitations on using right_on when the left_on references an index.

When the join keys are columns instead of indices, merge works by retaining both abc and ABC as columns. Therefore, I would guess this issue is related to the ambiguity about picking a name for the index in the resulting DataFrame.

Expected Behavior

I would expect that when using how="left" or left.merge(right, ...), the index name from the left DataFrame would take precedence in the result.

If the intended behavior is in fact a KeyError, then a note in the documentation should be added.

Installed Versions

INSTALLED VERSIONS

commit : 66e3805
python : 3.9.7.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.19043
machine : AMD64
processor : Intel64 Family 6 Model 142 Stepping 12, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : English_United States.1252

pandas : 1.3.5
numpy : 1.21.5
pytz : 2021.3
dateutil : 2.8.2
pip : 21.2.4
setuptools : 58.0.4
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
numba : None