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

[user-jx]

Pandas version checks

• [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.

• [X] 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 : a671b5a8bf5dd13fb19f0e88edc679bc9e15c673 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 https://github.com/pandas-dev/pandas/issues/59891 and links therein.