API: Should groupby.rolling be treated as a transform

[rhshadrach]

I don't have a lot of familiarity of window ops, but thought this was an interesting question from our Reddit AMA:

# example DataFrame with 3 columns: date, id and a random value
dates = list(pd.date_range(start="2019-01-01", end="2019-12-01", freq="MS"))
length = len(dates)
n = 2
ids = sorted(list(range(n)) * length)
values = np.random.randint(low=0, high=10, size=length).tolist()
df = pd.DataFrame({"date": dates * n, "id": ids, "value": values * n})

print(df.head())
#         date  id  value
# 0 2019-01-01   0      8
# 1 2019-02-01   0      8
# 2 2019-03-01   0      7
# 3 2019-04-01   0      2
# 4 2019-05-01   0      1

print(df.rolling(3, on="date")["value"].max().head())
# 0    NaN
# 1    NaN
# 2    8.0
# 3    8.0
# 4    7.0
# Name: value, dtype: float64

print(df.groupby("id").rolling(3, on="date")["value"].max().head())
# id  date      
# 0   2019-01-01    NaN
#     2019-02-01    NaN
#     2019-03-01    8.0
#     2019-04-01    8.0
#     2019-05-01    7.0
# Name: value, dtype: float64

When doing DataFrame.rolling with a reduction, the operation is a transform. With this, should DataFrameGroupBy.rolling then also be treated like a transform; i.e. not include the groupers or on column in the result, but rather return the index of the original frame? Like other transforms, users can then use the original object if they want to recover the corresponding on values or grouping data.

cc @mroeschke

[jpweytjens]

A related discussion can be found at #38787.

[mroeschke]

This statement from the rolling on parameter docs might have a typo, but I would assume applies to groupby rolling too

Provided integer column is ignored and excluded from result since an integer index is not used to calculate the rolling window.

(I think integer column should be integer index?)

Personally, I find it nice that the result index has the corresponding labels associated with the values instead of the original index. If groupby rolling didn't include the groupers and on columns, they would have to possibly sort the grouping labels and do a take on the on column. (This is how we validate on is monotonic)?

    def _validate_datetimelike_monotonic(self):
        """
        Validate that each group in self._on is monotonic
        """
        # GH 46061
        if self._on.hasnans:
            self._raise_monotonic_error("values must not have NaT")
        for group_indices in self._grouper.indices.values():
            group_on = self._on.take(group_indices)
            if not (
                group_on.is_monotonic_increasing or group_on.is_monotonic_decreasing
            ):
                on = "index" if self.on is None else self.on
                raise ValueError(
                    f"Each group within {on} must be monotonic. "
                    f"Sort the values in {on} first."
                )

[rhshadrach]

If groupby rolling didn't include the groupers and on columns, they would have to possibly sort the grouping labels and do a take on the on column. (This is how we validate on is monotonic)?

Not quite following here, "they would have to" - I'm guessing "they" is the users, but I'm not sure why they would have to do this. In other words, they would have to do this in order to accomplish what?

[jpweytjens]

Why not implement a .transform() method on the groupby().rolling() object that returns a Series with the same index as the oringinal df? This would provide a workflow very similar to the one that .groupby().transform() already provides.

Currently, inserting a df.groupby(...).rolling(...) back into df is a bit cumbersome at the moment. I only know of these 2 methods.

df["rolling_max_per_id_v1"] = df.set_index("date").groupby("id", as_index=False)["value"].rolling(3).max()["value"]
df["rolling_max_per_id_v2"] = df.groupby("id").rolling(3, on="date")["value"].max().set_axis(df.index)

Compared to the groupby().transform()

df["max_per_id"] = df.groupby("id")["value"].transform("max")

Ideally, something like this would work

df["rolling_max_per_id_ideal"] = df.groupby("id)["value"].rolling(3, on="date").transform("max")

[mroeschke]

Not quite following here, "they would have to" - I'm guessing "they" is the users, but I'm not sure why they would have to do this. In other words, they would have to do this in order to accomplish what?

Sorry, yeah "they" is a user who would want an index equivalent to the current behavior if in the future the index is reset.

Why not implement a .transform() method on the groupby().rolling() object that returns a Series with the same index as the oringinal df?

I don't think this warrants a separate method entirely

[rhshadrach]

"they" is a user who would want an index equivalent to the current behavior if in the future the index is reset.

I see, thanks. I think what I'm missing here is an understanding of a use case where the current index is desirable, but I think that is just due to my lack of experience with using rolling in a groupby.

[jpweytjens]

Having used groupby without rolling, I was suprised to see a different index than when using groupby with a rolling. It's unclear to me why rolling would change this behaviour?

I don't immediately see a use case for the current behaviour, but my experience with time series is only limited at this point.

[mroeschke]

I guess the use case is the convenience of a "final" result - having a descriptive index pairing a rolling window value with the associated groupby and on label.

If it is inconvenient for further manipulation and isn't consistent with other groupby results I would be open to changing. And if the resulting index was changed it would be good to describe the users "do X to get the old result"

[Demetrio92]

I still do not understand what is the pandas way of getting a rolling average column by group. I also can only see a way of doing something similar to @jpweytjens and relying on indices.

This is dangerous. A simple .sort_values(ascending=False) breaks it. So does having a non-unique index.

[Demetrio92]

My current hack:

import pandas as pd
test_df = pd.concat([
    pd.DataFrame({
        'group': 'A',
        'date': pd.date_range('2023-01-01', '2023-01-11'),
        'value':
            [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11],
    }),
    pd.DataFrame({
        'group': 'B',
        'date': pd.date_range('2023-01-01', '2023-01-11'),
        'value':
            [11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21],
    }),
])

test_df.reset_index(drop=True, inplace=True)  # turn off to break it
assert test_df.index.is_unique  
test_df.sort_values(by=['group', 'date'], ascending=True, inplace=True)  # use ascending=False to break it
test_df['value_ra_3'] = test_df\
    .groupby('group', as_index=True)[['group', 'date', 'value']]\
    .rolling(window=3, min_periods=3, on='date')['value']\
    .agg('mean').reset_index(drop=True)