BUG: Empty column name in group dataframe when using pyarrow types

[dbalabka]

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
data = {
    'id': [1, 1, 1, 2, 2, 2],
    'date': pd.to_datetime(['2023-01-01', '2023-01-04', '2023-01-05', '2023-01-01', '2023-01-04', '2023-01-05']),
    'metric': [1,1,1,1,1,1]
}
df = pd.DataFrame(data).astype({'id': 'int64', 'metric': 'int64', 'date': 'timestamp[ns][pyarrow]'})

print(
    df
    .groupby(by=['id'])
    .apply(lambda x: x.resample("D", on="date").sum(), include_groups=False)
)

Issue Description

Group DataFrame column date should not be empty:

                        metric
id                                           <----------------------- missing date column name
1  2023-01-01 00:00:00       1
   2023-01-02 00:00:00       0
   2023-01-03 00:00:00       0
   2023-01-04 00:00:00       1
   2023-01-05 00:00:00       1
2  2023-01-01 00:00:00       1
   2023-01-02 00:00:00       0
   2023-01-03 00:00:00       0
   2023-01-04 00:00:00       1
   2023-01-05 00:00:00       1

Expected Behavior

The following snippet generates the Grouped DataFrame with expected column names:

import pandas as pd
data = {
    'id': [1, 1, 1, 2, 2, 2],
    'date': pd.to_datetime(['2023-01-01', '2023-01-04', '2023-01-05', '2023-01-01', '2023-01-04', '2023-01-05']),
    'metric': [1,1,1,1,1,1]
}
df = pd.DataFrame(data)

print(
    df
    .groupby(by=['id'])
    .apply(lambda x: x.resample("D", on="date").sum(), include_groups=False)
)

               metric
id date              
1  2023-01-01       1
   2023-01-02       0
   2023-01-03       0
   2023-01-04       1
   2023-01-05       1
2  2023-01-01       1
   2023-01-02       0
   2023-01-03       0
   2023-01-04       1
   2023-01-05       1

Installed Versions

UserWarning: Setuptools is replacing distutils.

INSTALLED VERSIONS

commit : d9cdd2e
python : 3.10.12.final.0
python-bits : 64
OS : Linux
OS-release : 5.15.153.1-microsoft-standard-WSL2
Version : #1 SMP Fri Mar 29 23:14:13 UTC 2024
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : en_US.UTF-8
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.2.2
numpy : 1.26.3
pytz : 2023.4
dateutil : 2.8.2
setuptools : 69.0.3
pip : 24.0
Cython : None
pytest : 7.4.4
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.3
IPython : 8.20.0
pandas_datareader : None
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : 4.12.3
bottleneck : None
dataframe-api-compat : None
fastparquet : None
fsspec : 2023.12.2
gcsfs : 2023.12.2post1
matplotlib : 3.8.2
numba : 0.58.1
numexpr : None
odfpy : None
openpyxl : 3.1.2
pandas_gbq : None
pyarrow : 14.0.2
pyreadstat : None
python-calamine : None
pyxlsb : None
s3fs : 2023.12.2
scipy : 1.12.0
sqlalchemy : 2.0.29
tables : None
tabulate : 0.9.0
xarray : None
xlrd : None
zstandard : None
tzdata : 2023.4
qtpy : None
pyqt5 : None

[Stranger-Jie]

@dbalabka you can dtype as '<M8[ns]' or 'datetime64[ns]'.

df = pd.DataFrame(data).astype({'id': 'int64', 'metric': 'int64', 'date': '<M8[ns]'})
df = pd.DataFrame(data).astype({'id': 'int64', 'metric': 'int64', 'date': 'datetime64[ns]'})

[dbalabka]

@StrangersZero , thanks for the comment. I process the large dataset in Dask and adding type conversion might be an extra cluster work that I would like to avoid. In my case, I simply put the correct name after resampling.

Still, such inconsistent behaviour of DataFrame meta data for different types looks weird. It would be great to align the behaviour for any type.

[rhshadrach]

Confirmed on main. Further investigations and PRs to fix are welcome!

[dkcamargotz]

hey i'd like to take this one

[dkcamargotz]

@rhshadrach from what i could look i think the issue might be on the Resample not in the groupby nor the apply

[rhshadrach]

Thanks, I'll add Resample for now. If you can reproduce the issue with just resample, post an example here and we can remove the groupby/apply labels.

[dkcamargotz]

@rhshadrach Awesome i'll try to reproduce it only with the resample later today and i'll keep you guys posted

[dkcamargotz]

hey @rhshadrach i was able to reproduce the error only with the resample it was quite easily actually:

Reproducible Example:

import pandas as pd
data = {
    'id': [1, 1, 1, 2, 2, 2],
    'date': pd.to_datetime(['2023-01-01', '2023-01-04', '2023-01-05', '2023-01-01', '2023-01-04', '2023-01-05']),
    'metric': [1,1,1,1,1,1]
}
df = pd.DataFrame(data).astype({'id': 'int64', 'metric': 'int64', 'date': 'timestamp[ns][pyarrow]'})

print(
    df.resample("D", on="date").sum()
)

Issue Description

DataFrame column date should not be empty:

                     id  metric
2023-01-01 00:00:00   3       2
2023-01-02 00:00:00   0       0
2023-01-03 00:00:00   0       0
2023-01-04 00:00:00   3       2
2023-01-05 00:00:00   3       2

Expected Behavior

The following snippet generates the Grouped DataFrame with expected column names:

import pandas as pd
data = {
    'id': [1, 1, 1, 2, 2, 2],
    'date': pd.to_datetime(['2023-01-01', '2023-01-04', '2023-01-05', '2023-01-01', '2023-01-04', '2023-01-05']),
    'metric': [1,1,1,1,1,1]
}
df = pd.DataFrame(data).astype({'id': 'int64', 'metric': 'int64', 'date': 'timestamp[ns][pyarrow]'})

print(
    df.resample("D", on="date").sum()
)

            id  metric
date                   
2023-01-01  3       2
2023-01-02  0       0
2023-01-03  0       0
2023-01-04  3       2
2023-01-05  3       2

[dkcamargotz]

btw i also made sure to use diferent methods for the resample like mean and median and it also has the same issue

[rhshadrach]

Thanks @dkcamargotz!

[dkcamargotz]

Thanks @dkcamargotz!

Sure! Im trying to reproduce using upsample type of resample to track where the issue is. But im having trouble i'll keep looking for the bug on monday

[dbalabka]

@rhshadrach, the following problem might be triggered by #59888

[dbalabka]

I've checked it seems these are two separate problems. The workaround described in #59888 does not help:

import pandas as pd
import pyarrow as pa

data = {
    'id': [1, 1, 1, 2, 2, 2],
    'date': pd.to_datetime(['2023-01-01', '2023-01-04', '2023-01-05', '2023-01-01', '2023-01-04', '2023-01-05']),
    'metric': [1,1,1,1,1,1]
}
df = pd.DataFrame(data).astype({'id': 'int64', 'metric': 'int64', 'date': pd.ArrowDtype(pa.timestamp('ns'))})

print(
    df
    .groupby(by=['id'])
    .apply(lambda x: x.resample("D", on="date").sum(), include_groups=False)
)

Still getting empty column:

                        metric
id                            
1  2023-01-01 00:00:00       1
   2023-01-02 00:00:00       0
   2023-01-03 00:00:00       0
   2023-01-04 00:00:00       1
   2023-01-05 00:00:00       1
2  2023-01-01 00:00:00       1
   2023-01-02 00:00:00       0
   2023-01-03 00:00:00       0
   2023-01-04 00:00:00       1
   2023-01-05 00:00:00       1