BUG: drop_duplicates() inconsistent output when using/not using subset and having different types of NaNs

[user-jx]

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
df = pd.DataFrame({
'A':[1,1,1],
'B':[2,2,2],
'C':[3,3,3],
'D':[pd.NaT, pd.NaT, pd.NaT]
})
df.loc[0,'D'] = 0; df.loc[0,'D'] = float('nan')
df.loc[2,'D'] = 0; df.loc[2,'D'] = float('nan')

#%%
df.drop_duplicates(keep=False)

#%%
df.drop_duplicates(subset='D', keep=False)

Issue Description

Hi,
I have the following pandas DataFrame:

import pandas as pd
df = pd.DataFrame({
'A':[1,1,1],
'B':[2,2,2],
'C':[3,3,3],
'D':[pd.NaT, pd.NaT, pd.NaT]
})
df.loc[0,'D'] = 0; df.loc[0,'D'] = float('nan')
df.loc[2,'D'] = 0; df.loc[2,'D'] = float('nan')

   A  B  C  D  
0  1  2  3  NaN
1  1  2  3  NaT
2  1  2  3  NaN

With

df.drop_duplicates(keep=False)

I get:

Empty DataFrame
Columns: [A, B, C, D]
Index: []

But when using the parameter subset for the only column that has a difference,

df.drop_duplicates(subset='D', keep=False)

I get:

   A  B  C  D  
1  1  2  3  NaT

I have two questions:

1. Shouldn't the outcome be the same in both cases?
2. pandas.NaT and float('nan') are considered as different values by drop_duplicates()?

Thank you!

Expected Behavior

I expected that the outcome would be the same in both cases.

Installed Versions

INSTALLED VERSIONS

commit : a671b5a
python : 3.10.13.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.19045
machine : AMD64
processor : Intel64 Family 6 Model 167 Stepping 1, GenuineIntel
byteorder : little
LC_ALL : None
LANG : en
LOCALE : English_United States.1253

pandas : 2.1.4
numpy : 1.25.2
pytz : 2023.3.post1
dateutil : 2.8.2
setuptools : 68.2.2
pip : 23.3.1
Cython : None
pytest : None
hypothesis : None
sphinx : 5.0.2
blosc : None
feather : None
xlsxwriter : 3.1.9
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.3
IPython : 8.20.0
pandas_datareader : None
bs4 : 4.12.2
bottleneck : 1.3.7
dataframe-api-compat: None
fastparquet : None
fsspec : 2024.3.1
gcsfs : None
matplotlib : 3.8.4
numba : 0.59.0
numexpr : 2.8.7
odfpy : None
openpyxl : 3.0.10
pandas_gbq : None
pyarrow : 14.0.2
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : 1.9.3
sqlalchemy : None
tables : 3.9.2
tabulate : 0.9.0
xarray : 2024.2.0
xlrd : None
zstandard : None
tzdata : 2023.3
qtpy : 2.4.1
pyqt5 : None

[vkhodygo]

I'm surprised you don't get an error:

/tmp/ipykernel_9568/1664581211.py:1: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '0' has dtype incompatible with datetime64[ns], please explicitly cast to a compatible dtype first.
  df.loc[0,'D'] = 0; df.loc[0,'D'] = float('nan')

[user-jx]

I don't have the latest version of pandas but a slightly earlier one, this is probably why I don't get the error.
So, DataFrames can accept only one dtype in each column now?
If this is the case, then my questions are probably answered.
Thank you for your time.

[vkhodygo]

I'd ask for a second opinion, dealing with missing data in pandas is a whole different can of worms nowadays.

[rhshadrach]

Thanks for the report. When using multiple columns, pandas uses the groupby internals to determine what the duplicates are. groupby identifies all NA values as s single group. This is #48476. When using a single column pandas uses Series.duplicated which uses a hashmap for better performance, which does differentiate between NA values.

Marking this as needs discussion for now as we need to agree on which of the two behaviors we want for both operations.

[vkhodygo]

See #59891 and links therein.