BUG: rolling on time-based index wrongly

[jh-wu]

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 numpy as np
import pandas as pd

# Create sample data
dates = pd.date_range(start='2024-01-01', periods=5, freq='D')
ids = range(1, 3)

np.random.seed(42)
# Create all combinations of dates and ids
data = []
for date in dates:
    for id in ids:
        data.append({
            'date': date,
            'Id': id,
            'value': np.random.rand() * 100
        })

df_sample = pd.DataFrame(data)
print(df_sample)

def rolling_func(x):
    print(x)

    return x.iloc[-1]

df_sample.set_index('date').groupby('Id')['value'].rolling(5, min_periods=1).apply(rolling_func, raw=False)

Issue Description

    date          Id   value

2024-01-01 1 37.454012
2024-01-01 2 95.071431
2024-01-02 1 73.199394
2024-01-02 2 59.865848
2024-01-03 1 15.601864
2024-01-03 2 15.599452
2024-01-04 1 5.808361
2024-01-04 2 86.617615
2024-01-05 1 60.111501
2024-01-05 2 70.807258

For a sample dataframe like the above, the code returns the following results. I believe that the rolling results based on the group (by Id) and time-based index (by date) are wrong.

date
2024-01-01 37.454012
dtype: float64
date
2024-01-01 37.454012
2024-01-01 73.199394
dtype: float64
date
2024-01-01 37.454012
2024-01-01 73.199394
2024-01-02 15.601864
dtype: float64
date
2024-01-01 37.454012
2024-01-01 73.199394
2024-01-02 15.601864
2024-01-02 5.808361
dtype: float64
date
2024-01-01 37.454012
2024-01-01 73.199394
2024-01-02 15.601864
2024-01-02 5.808361
2024-01-03 60.111501
dtype: float64
date
2024-01-03 95.071431
dtype: float64
date
2024-01-03 95.071431
2024-01-04 59.865848
dtype: float64
date
2024-01-03 95.071431
2024-01-04 59.865848
2024-01-04 15.599452
dtype: float64
date
2024-01-03 95.071431
2024-01-04 59.865848
2024-01-04 15.599452
2024-01-05 86.617615
dtype: float64
date
2024-01-03 95.071431
2024-01-04 59.865848
2024-01-04 15.599452
2024-01-05 86.617615
2024-01-05 70.807258
dtype: float64

Expected Behavior

I expect the results shall be like the following:
date
2024-01-01 37.454012
dtype: float64
date
2024-01-01 37.454012
2024-01-02 73.199394 # Instead of "2024-01-01 73.199394"
dtype: float64
date
2024-01-01 37.454012
2024-01-02 73.199394 # Instead of "2024-01-01 73.199394"
2024-01-03 15.601864 # Instead of "2024-01-02 15.601864"
dtype: float64
date
2024-01-01 37.454012
2024-01-02 73.199394 # Instead of "2024-01-01 73.199394"
2024-01-03 15.601864 # Instead of "2024-01-02 15.601864"
2024-01-04 5.808361 # Instead of "2024-01-02 5.808361"

Installed Versions

Details

INSTALLED VERSIONS

commit : 9c8bc3e
python : 3.13.11
python-bits : 64
OS : Darwin
OS-release : 24.6.0
Version : Darwin Kernel Version 24.6.0: Wed Nov 5 21:28:03 PST 2025; root:xnu-11417.140.69.705.2~1/RELEASE_ARM64_T8122
machine : arm64
processor : arm
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.3.3
numpy : 2.2.4
pytz : 2025.2
dateutil : 2.9.0.post0
pip : 25.2
Cython : 3.1.2
sphinx : None
IPython : 9.1.0
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : 4.13.5
blosc : None
bottleneck : None
dataframe-api-compat : None
fastparquet : 2024.11.0
fsspec : 2025.5.1
html5lib : None
hypothesis : None
gcsfs : None
jinja2 : 3.1.6
lxml.etree : None
matplotlib : 3.10.7
numba : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
psycopg2 : None
pymysql : None
pyarrow : 19.0.1
pyreadstat : None
pytest : None
python-calamine : None
pyxlsb : None
s3fs : None
scipy : 1.16.1
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlsxwriter : None
zstandard : None
tzdata : 2025.2
qtpy : None
pyqt5 : None

[Moriyuki-S]

Hi @jh-wu, I took a look into this issue.

It appears that the issue relates to _generate_cython_apply_func in pandas/core/window/rolling.py (inside RollingAndExpandingMixin). It seems that while groupby reorders the values, self._on retains the original order, causing the mismatch.

I tried aligning the index as follows, and it seems to resolve the issue. Here is the diff of the potential fix:

# pandas/core/window/rolling.py

     def _generate_cython_apply_func(
         self,
         args: tuple[Any, ...],
         kwargs: dict[str, Any],
         raw: bool | np.bool_,
         function: Callable[..., Any],
     ) -> Callable[[np.ndarray, np.ndarray, np.ndarray, int], np.ndarray]:
         from pandas import Series
 
         window_func = partial(
             window_aggregations.roll_apply,
             args=args,
             kwargs=kwargs,
             raw=bool(raw),
             function=function,
         )
 
+        apply_index = self._on
+        if not raw and isinstance(self, BaseWindowGroupby):
+            # Groupby rolling reorders values by group; align the index to match.
+            groupby_indices = list(self._grouper.indices.values())
+            if groupby_indices:
+                groupby_order = np.concatenate(groupby_indices)
+                apply_index = self._on.take(groupby_order)
+            else:
+                apply_index = self._on[:0]

         def apply_func(values, begin, end, min_periods, raw=raw):
             if not raw:
                 # GH 45912
-                values = Series(values, index=self._on, copy=False)
+                values = Series(values, index=apply_index, copy=False)
             return window_func(values, begin, end, min_periods)
 
         return apply_func

If this approach looks correct, I would be happy to open a PR.

[sanrishi]

@Moriyuki-S thanks for the excellent investigation! You correctly identified the root cause in _generate_cython_apply_func.

I have concerns about relying on indices.values() order. As you mentioned, groupby(sort=True) (the default) processes groups in sorted order, but indices.values() follows dictionary insertion order.

For example, if your data has Id=2 before Id=1:

• indices.values() yields: [[Id=2 indices], [Id=1 indices]] (insertion order)
• But rolling processes: [Id=1 values, Id=2 values] (sorted order)

This would cause a new misalignment.

I've been working on a fix that aligns with self._grouper.result_index to handle the sort order correctly.

Either way, your diagnosis was spot-on and saved a lot of debugging time. 🙌

[Moriyuki-S]

@sanrishi
Thanks for the feedback! You make a valid point regarding the sorting order discrepancy between indices.values() and groupby(sort=True). Relying on insertion order is indeed risky there.

To address this, I've drafted a fix using self._grouper.result_ilocs to align the index correctly. I've submitted a PR just now in #63849 .

Of course, if you have a local branch that handles this better, feel free to supersede my PR!