Pandas groupby rolling drops index column

Question

Not sure if I am doing something wrong (Pandas 1.2.5):

ids = pd.DataFrame(data=range(10), columns=['Id'])
dt = pd.DataFrame(pd.date_range('2021-01-01', '2021-01-10', freq='D'), columns=['Date'])
df = ids.merge(dt, how='cross')
df['Val'] = np.random.randint(1,10, size=len(df))
df.set_index(['Id', 'Date'], inplace=True)
df['Val'].groupby('Id').rolling(window=3).mean()

I would expect the result to include the Date column (otherwise why compute a rolling mean?) but Date is not there:

Id
0          NaN
0          NaN
0     2.333333
0     3.333333
0     3.666667
        ...   
9     5.000000
9     4.000000
9     5.000000
9     5.333333
9     6.000000
Name: Val, Length: 100, dtype: float64

What am I missing?

Also, df['Val'].reset_index('Id').groupby('Id').rolling(window=3).mean() seems to work somehow but returns Id as a data column as well as an index column even if as_index=False is passed in groupby. Very strange!

                Id  Val
Id  Date        
0   2021-01-01  NaN NaN
    2021-01-02  NaN NaN
    2021-01-03  0.0 7.000000
    2021-01-04  0.0 6.333333
    2021-01-05  0.0 4.666667
... ... ... ...

The data column there is `Val`. You only grouped by the `Id` variable, so that's the index. — ifly6, Jun 30 '21 at 18:28
What's the right way to do it? Also, I've added data for the "hacky" solution and Date is in the index despite groupby by Id only. I am very confused :( — iggy, Jun 30 '21 at 18:37

score 2 · Accepted Answer · answered Jun 30 '21 at 18:47

2

I think this is a little cleaner,

ids = pd.DataFrame(data=range(10), columns=['Id'])
dt = pd.DataFrame(pd.date_range('2021-01-01', '2021-01-10', freq='D'), columns=['Date'])
df = ids.merge(dt, how='cross')
df['Val'] = np.random.randint(1,10, size=len(df))
df.set_index(['Id'], inplace=True)
df.groupby(['Id']).rolling(window=3,on='Date').mean()#.head(60)

Only change was to not include 'Date' in the index, and roll on='Date'

answered Jun 30 '21 at 18:47

Clay Shwery

380
1
8

2

Thanks, I see now. The docs are not exactly clear: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.rolling.html but it looks like `on` is crucial here except we can't refer to an index column after a groupby so pulling `Date` out of the index is the way to do it `df['Val'].reset_index('Date').groupby('Id').rolling(window=3, on='Date').mean()` – iggy Jun 30 '21 at 22:54

Pandas groupby rolling drops index column

1 Answers1