BUG: Groupby column and min aggregation over period column yields NaT for all rows

[franzoni315]

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

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

---

Code Sample:

d = {
  'user_id': {
    0: 4628132781164409,
    1: 7075307556966343,
    2: 8013098674514720,
    3: 7690164883842520,
    4: 4985766983843363,
    5: 3038805367505476,
    6: 2541787162631756,
    7: 4985766983843363,
    8: 2541787162631756,
    9: 3558493739463398},
  'date': {
    0: '2020-02-12',
    1: '2020-02-12',
    2: '2020-02-12',
    3: '2020-02-12',
    4: '2020-02-12',
    5: '2020-02-12',
    6: '2020-02-12',
    7: '2020-02-12',
    8: '2020-02-12',
    9: '2020-02-12'
 }
}

df = pd.DataFrame(d).astype({'date': 'datetime64'})
df['week'] = df['date'].dt.to_period('W')
df['cohort'] = df.groupby('user_id')['week'].min()

Problem description

The above code returns NaT values for the cohort column, but it was expected to return the minimum values for week, which in this case would be 2020-02-10/2020-02-16 for all rows.

Expected Output

If we change the last line to:

df['cohort'] = df.groupby('user_id')['week'].transform('min')

We get the expected value for the cohort column, i.e., 2020-02-10/2020-02-16.

Output of pd.show_versions()

INSTALLED VERSIONS

commit : 67a3d42
python : 3.7.5.final.0
python-bits : 64
OS : Linux
OS-release : 5.4.0-1029-aws
Version : #30~18.04.1-Ubuntu SMP Tue Oct 20 11:09:25 UTC 2020
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : C.UTF-8
LOCALE : en_US.UTF-8

pandas : 1.1.4
numpy : 1.19.4
pytz : 2019.3
dateutil : 2.8.1
pip : 20.0.2
setuptools : 45.2.0
Cython : 0.29.15
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : 2.8.4 (dt dec pq3 ext lo64)
jinja2 : 2.11.2
IPython : 7.12.0
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : 3.1.3
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : 1.0.1
pytables : None
pyxlsb : None
s3fs : None
scipy : 1.4.1
sqlalchemy : 1.3.20
tables : None
tabulate : 0.8.7
xarray : None
xlrd : None
xlwt : None
numba : 0.52.0

[rhshadrach]

This is expected behavior. Look at the values from df.groupby('user_id')['week'].min() by itself, they will have index given by user_id. Then when you make the assignment todf['cohort'], pandas will try to align the integer index of df with the index of the groupby results. When pandas finds no match, the null value is appropriately used.

This is the reason transform exists, and is the appropriate method to use here.

[rhshadrach]

If I've missed something, please comment here and can reopen.