BUG: Regression in version 1.3.0: outer/right merge converts Int64 column to object if side-specific values are present

[kpflugshaupt]

• [x ] I have checked that this issue has not already been reported.

• [x ] 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 merging data frames on an Int64 column, the resulting dtype differs between pandas 1.2.5 and 1.3.0.

• If all values are present on the left and right side, the Int64 dtype is retained successfully both in 1.2.5 and 1.3.0, for all merge methods (inner, left, right, outer)
• If there are specific values on both sides, dtype is converted depending on merge method:
  • 1.2.5 keeps Int64 for inner and left merges, converts to int64 for outer and right merges
  • 1.3.0 keeps Int64 for inner and left merges, converts to object for outer and right merges

Code Sample, a copy-pastable example

ALL VALUES IDENTICAL: Correct behaviour

# pandas 1.2.5
df1 = pd.DataFrame(dict(nr=[1,2,3,4])).astype({"nr": "Int64"})
df2 = pd.DataFrame(dict(nr=[1,2,3,4])).astype({"nr": "Int64"})
pd.merge(df1, df2, how="outer", on="nr").dtypes
# Out[9]: 
# nr    Int64

# pandas 1.3.0
df1 = pd.DataFrame(dict(nr=[1,2,3,4])).astype({"nr": "Int64"})
df2 = pd.DataFrame(dict(nr=[1,2,3,4])).astype({"nr": "Int64"})
pd.merge(df1, df2, how="outer", on="nr").dtypes
# Out[9]: 
# nr    Int64

SIDE-SPECIFIC VALUES PRESENT: Differing behaviour, regression on 1.3.0

# pandas 1.2.5
df1 = pd.DataFrame(dict(nr=[1,2,3  ])).astype({"nr": "Int64"})
df2 = pd.DataFrame(dict(nr=[  2,3,4])).astype({"nr": "Int64"})
pd.merge(df1, df2, how="outer", on="nr").dtypes
# Out[1]: 
# nr    int64
pd.merge(df1, df2, how="right", on="nr").dtypes
# Out[2]: 
# nr    int64
pd.merge(df1, df2, how="left", on="nr").dtypes
# Out[3]: 
# nr    Int64
pd.merge(df1, df2, how="inner", on="nr").dtypes
# Out[4]: 
# nr    Int64

# pandas 1.3.0
df1 = pd.DataFrame(dict(nr=[1,2,3  ])).astype({"nr": "Int64"})
df2 = pd.DataFrame(dict(nr=[  2,3,4])).astype({"nr": "Int64"})
pd.merge(df1, df2, how="outer", on="nr").dtypes
# Out[1]: 
# nr    object
pd.merge(df1, df2, how="right", on="nr").dtypes
# Out[2]: 
# nr    object
pd.merge(df1, df2, how="left", on="nr").dtypes
# Out[3]: 
# nr    Int64
pd.merge(df1, df2, how="inner", on="nr").dtypes
# Out[4]: 
# nr    Int64

Problem description

When merging on an Int64 column, the Int64 dtype is lost in certain circumstances (outer and right joins, side-specific values present).

• On pandas 1.2.5, the column drops to int64 (mostly equivalent to Int64, except for NULLs)
• On pandas 1.3.0, the column turns into an object (string) column (not equivalent to Int64 at all)

I found this when matches of a merged Int64 key against other Int64 columns silently stopped matching after the update to 1.3.0.
On 1.2.5, there was no problem, as int64 columns match correctly against Int64 ones.

Expected Output

• Ideally, the Int64 dtype should be retained in all the above cases
• If this is currently not possible, the 1.2.5 behaviour of converting to int64 is preferred, as the column still mostly works as expected

Output of pd.show_versions()

INSTALLED VERSIONS

commit : f00ed8f
python : 3.8.10.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.19042
machine : AMD64
processor : Intel64 Family 6 Model 158 Stepping 10, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : German_Switzerland.1252
pandas : 1.3.0
numpy : 1.20.2
pytz : 2021.1
dateutil : 2.8.1
pip : 21.1.3
setuptools : 51.0.0
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : 2.8.6 (dt dec pq3 ext lo64)
jinja2 : None
IPython : 7.25.0
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : 3.4.2
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : 4.0.0
pyxlsb : None
s3fs : None
scipy : 1.6.3
sqlalchemy : 1.4.0b1
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
numba : None

[mzeitlin11]

Thanks for reporting this @kpflugshaupt! I think this falls under #40073, so tentatively marking as a duplicate

[kpflugshaupt]

Hi @mzeitlin11,

I agree that #40073 describes the same problem. Sorry for overlooking that one!

I'll close this one as duplicate.

[kpflugshaupt]

Duplicate of #40073