groupby nunique() with dates vs datetimes in presence of NaTs · Issue #27904 · pandas-dev/pandas (original) (raw)

Code Sample

import numpy as np import pandas as pd import datetime

df = pd.DataFrame({'a': ['x','x','x','x','x'], 'b': [datetime.date(2019,1,1), pd.NaT, datetime.date(2019,1,1), pd.NaT, datetime.date(2019,1,1)]}) print(df)

unique() returns only 1 value (plus the NaT) as expected

print('unique() with dates') print(df.groupby(['a'])['b'].unique())

nunique() returns 3 instead of the expected 1 (NaTs are dropped). Seems to be due to the NaTs

print('nunique() with dates') print(df.groupby(['a'])['b'].nunique())

convert date column to datetime

df['b'] = df.b.astype(np.datetime64)

with datetime, unique() still returns 1 value (plus the NaT) as expected

print('unique() with datetimes') print(df.groupby(['a'])['b'].unique())

with datetime, nunique() returns 1 as expected

print('nunique() with datetimes') print(df.groupby(['a'])['b'].nunique())

Problem description

Series.groupby().nunique() seems to have a different behavior with dates vs datetimes when NaTs are in the Series. The behavior with date seems to return more distinct values than there are.
This is not the same issue as #14149

Expected Output

For nunique() with dates the expected output is:

Output of pd.show_versions()

INSTALLED VERSIONS

commit : None
python : 3.7.3.final.0
python-bits : 64
OS : Linux
OS-release : 4.18.0-25-generic
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 0.25.0
numpy : 1.16.4
pytz : 2019.2
dateutil : 2.8.0
pip : 19.2.2
setuptools : 41.0.1
Cython : 0.29.13
pytest : 5.0.1
hypothesis : None
sphinx : 2.1.2
blosc : None
feather : None
xlsxwriter : 1.1.8
lxml.etree : 4.4.1
html5lib : 1.0.1
pymysql : None
psycopg2 : None
jinja2 : 2.10.1
IPython : 7.7.0
pandas_datareader: None
bs4 : 4.8.0
bottleneck : 1.2.1
fastparquet : None
gcsfs : None
lxml.etree : 4.4.1
matplotlib : 3.1.1
numexpr : 2.6.9
odfpy : None
openpyxl : 2.6.2
pandas_gbq : None
pyarrow : None
pytables : None
s3fs : None
scipy : 1.3.1
sqlalchemy : 1.3.6
tables : 3.5.2
xarray : None
xlrd : 1.2.0
xlwt : 1.3.0
xlsxwriter : 1.1.8