英文:
Keep date in pandas groupby rolling aggregation
问题
I'm running the code below for computing rolling statistics over date on a dataset.
import pandas as pddf = pd.DataFrame({'id': [1, 1, 1, 2, 2, 2],'date': ['2023-01-01', '2023-01-02', '2023-01-03', '2023-01-01', '2023-01-02', '2023-01-03'],'value': [5, 4, 7, 2, 7, 1]})df['date'] = pd.to_datetime(df['date'])df.groupby('id')['value'].rolling(2).agg({'sum': 'sum', 'mean': 'mean'})
The code does not keep date in the result. Ideally I would like to keep the date for each statistics over time, but I only get an index number.
英文:
I'm running the code below for computing rolling statistics over date on a dataset.
import pandas as pddf = pd.DataFrame({'id': [1, 1, 1, 2, 2, 2],'date': ['2023-01-01', '2023-01-02', '2023-01-03', '2023-01-01', '2023-01-02', '2023-01-03'],'value': [5, 4, 7, 2, 7, 1]})df['date'] = pd.to_datetime(df['date'])df.groupby('id')['value'].rolling(2).agg({'sum': 'sum', 'mean': 'mean'})
The code does not keep date in the result. Ideally I would like to keep the date for each statistics over time, but I only get an index number.
答案1
得分: 1
将date设置为索引以在聚合过程中保留它：
out = (df.set_index('date').groupby('id')['value'].rolling(2).agg(['sum', 'mean']).reset_index())print(out)
id date sum mean0 1 2023-01-01 NaN NaN1 1 2023-01-02 9.0 4.52 1 2023-01-03 11.0 5.53 2 2023-01-01 NaN NaN4 2 2023-01-02 9.0 4.55 2 2023-01-03 8.0 4.0
英文:
Set date as index to preserve it during aggregation:
out = (df.set_index('date').groupby('id')['value'].rolling(2).agg(['sum', 'mean']).reset_index())print(out)
id date sum mean0 1 2023-01-01 NaN NaN1 1 2023-01-02 9.0 4.52 1 2023-01-03 11.0 5.53 2 2023-01-01 NaN NaN4 2 2023-01-02 9.0 4.55 2 2023-01-03 8.0 4.0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论