BUG: `df.combine_first` duplicates rows for nan index values

[ssche]

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

• I have confirmed this bug exists on the latest version of pandas.
  (the issue sounds familiar, but I couldn't find it anywhere...)

• (optional) I have confirmed this bug exists on the master branch of pandas.

---

Note: Please read this guide detailing how to provide the necessary information for us to reproduce your bug.

Code Sample, a copy-pastable example

>>> df1 = pd.DataFrame({'a': [1, 2, 3], 'b': [np.nan, 5, 6], 'x': [9, 10, 11]})
>>> df1_ = df1.set_index(['a', 'b'])
>>> df1_
        x
a b      
1 NaN   9
2 5.0  10
3 6.0  11


>>> df2 = pd.DataFrame({'a': [1, 2, 4], 'b': [np.nan, 5, 7], 'y': [12, 13, 14]})
>>> df2_ = df2.set_index(['a', 'b'])
>>> df2_
        y
a b      
1 NaN  12
2 5.0  13
4 7.0  14


>>> df1_.combine_first(df2_)
          x     y
a b              
1 NaN   9.0  12.0
  NaN   9.0  12.0   <--- duplicated row
2 5.0  10.0  13.0
3 6.0  11.0   NaN
4 7.0   NaN  14.0

Problem description

When combining two DataFrames with overlapping indices, the indices, which contain nan values but are otherwise identical, are duplicated.

The problem doesn't occur when the nan values are substituted with say -1.

Expected Output

>>> df1_.combine_first(df2_)
          x     y
a b              
1 NaN   9.0  12.0
2 5.0  10.0  13.0
3 6.0  11.0   NaN
4 7.0   NaN  14.0

It is expected that combine_first detects that the index [1, NaN] exists in both dataframes and thus does not duplicate this row in the resulting dataframe.

Output of pd.show_versions()

INSTALLED VERSIONS

commit : 7d32926
python : 3.8.7.final.0
python-bits : 64
OS : Darwin
OS-release : 19.6.0
Version : Darwin Kernel Version 19.6.0: Tue Jan 12 22:13:05 PST 2021; root:xnu-6153.141.16~1/RELEASE_X86_64
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : en_AU.UTF-8
LOCALE : en_AU.UTF-8

pandas : 1.2.2
numpy : 1.19.4
pytz : 2020.4
dateutil : 2.8.1
pip : 20.2.3
setuptools : 46.4.0
Cython : 0.29.21
pytest : 5.1.1
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : 0.9.6
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : 2.8.3 (dt dec pq3 ext lo64)
jinja2 : 2.11.2
IPython : 7.16.1
pandas_datareader: None
bs4 : None
bottleneck : 1.3.1
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : 3.3.0
numexpr : 2.7.1
odfpy : None
openpyxl : 3.0.5
pandas_gbq : None
pyarrow : 1.0.1
pyxlsb : None
s3fs : None
scipy : 1.4.1
sqlalchemy : 1.3.12
tables : 3.6.1
tabulate : None
xarray : None
xlrd : 1.2.0
xlwt : None
numba : None

[ssche]

patching def fast_unique_multiple(list arrays, sort: bool = True) to explicitly iterate over the already collected items (in table) instead of relying on in seems to resolve the issue, but that may not be particularly fast any more...

https://github.com/pandas-dev/pandas/blob/master/pandas/_libs/lib.pyx#L300

[ssche]

Is anyone able to reproduce this?

[phofl]

Would be fixed with #38977, but has a performance penalty so will see

[ssche]

Thanks @phofl. I wasn’t able to solve it without a performance hit either...

[mroeschke]

Though this was fixed with #38977, could use an extra regression test