pandas-dev / pandas

Flexible and powerful data analysis / manipulation library for Python, providing labeled data structures similar to R data.frame objects, statistical functions, and much more
https://pandas.pydata.org
BSD 3-Clause "New" or "Revised" License
43.43k stars 17.85k forks source link

BUG: inconsistent indices in `GroupByRolling` when selecting or not selecting subset of columns #59567

Open inigohidalgo opened 1 month ago

inigohidalgo commented 1 month ago

Pandas version checks

Reproducible Example

import pandas as pd
import numpy as np
datetime_column = "datetime"
datetime_series = pd.date_range(start="2020-01-01", periods=10, freq="D")
datetime_series = datetime_series.append(datetime_series)

predictions = pd.DataFrame(
    {
        datetime_column: datetime_series,
        "prediction": np.random.rand(len(datetime_series)),
        "id": np.repeat(["A", "B"], 10),
        "area": np.repeat(["fr", "fr", "de", "de", "fr"], 4),
    }
)
print(predictions.groupby(["id", "area"]).rolling("7d", on="datetime").max())
             datetime  prediction
id area                          
A  de   8  2020-01-09    0.768346
        9  2020-01-10    0.768346
   fr   0  2020-01-01    0.159567
        1  2020-01-02    0.722039
        2  2020-01-03    0.722039
        3  2020-01-04    0.922641
        4  2020-01-05    0.922641
        5  2020-01-06    0.922641
        6  2020-01-07    0.922641
        7  2020-01-08    0.922641
B  de   10 2020-01-01    0.158251
        11 2020-01-02    0.814331
        12 2020-01-03    0.814331
        13 2020-01-04    0.814331
        14 2020-01-05    0.814331
        15 2020-01-06    0.943016
   fr   16 2020-01-07    0.975385
        17 2020-01-08    0.975385
        18 2020-01-09    0.975385
        19 2020-01-10    0.975385
print(predictions.groupby(["id", "area"]).rolling("7d", on="datetime")[["prediction"]].max())
                    prediction
id area datetime              
A  de   2020-01-09    0.768346
        2020-01-10    0.768346
   fr   2020-01-01    0.159567
        2020-01-02    0.722039
        2020-01-03    0.722039
        2020-01-04    0.922641
        2020-01-05    0.922641
        2020-01-06    0.922641
        2020-01-07    0.922641
        2020-01-08    0.922641
B  de   2020-01-01    0.158251
        2020-01-02    0.814331
        2020-01-03    0.814331
        2020-01-04    0.814331
        2020-01-05    0.814331
        2020-01-06    0.943016
   fr   2020-01-07    0.975385
        2020-01-08    0.975385
        2020-01-09    0.975385
        2020-01-10    0.975385

Issue Description

The only difference is that in the second case I am explicitly selecting a single column [[predictions]] whereas in the first example I am calling it on the full dataframe. This shouldn't make a difference as the dataframe only contains the predictions column outside of the columns used to group and roll on.

This difference causes two issues in the dataframe where I don't select a subset of the columns:

  1. The old index is appended as an additional unnamed level
  2. The datetime column is kept as a column instead of as an index level

Expected Behavior

I would expect both cases to behave the way the second example does, with id, area, datetime as the index levels.

Installed Versions

INSTALLED VERSIONS ------------------ commit : d9cdd2ee5a58015ef6f4d15c7226110c9aab8140 python : 3.10.13.final.0 python-bits : 64 OS : Linux OS-release : 5.15.0-1066-azure Version : #75-Ubuntu SMP Thu May 30 14:29:45 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 : 2.1.0 pytz : 2024.1 dateutil : 2.9.0.post0 setuptools : None pip : None Cython : None pytest : None hypothesis : None sphinx : None blosc : None feather : None xlsxwriter : None lxml.etree : None html5lib : None pymysql : None psycopg2 : None jinja2 : None IPython : 8.26.0 pandas_datareader : None adbc-driver-postgresql: None adbc-driver-sqlite : None bs4 : None bottleneck : None dataframe-api-compat : None fastparquet : None fsspec : None gcsfs : None matplotlib : None numba : None numexpr : None odfpy : None openpyxl : None pandas_gbq : None pyarrow : None pyreadstat : None python-calamine : None pyxlsb : None s3fs : None scipy : None sqlalchemy : None tables : None tabulate : None xarray : None xlrd : None zstandard : None tzdata : 2024.1 qtpy : None pyqt5 : None
rhshadrach commented 1 month ago

Thanks for the report - confirmed on main. Further investigations and PRs to fix are welcome!