在多个列上使用GroupBy创建新的滚动平均值列

2021-12-08 04:33:33 发布

您现在位置:Python中文网/ 问答频道 /正文

我有一个有11列的数据框架,其中date是一个索引。我正在尝试创建一个新列,其滚动平均值为total。但是,我得到了一个错误:TypeError:插入列的索引与框架索引不兼容

import pandas as pd

df = pd.DataFrame({
    'date':['2016-04-01','2016-05-01','2016-07-01','2016-08-01','2016-09-01',  '2019-04-01','2019-05-01','2019-06-01','2019-08-01','2019-09-01'],
    'Country':['USA', 'USA', 'USA', 'USA', 'USA','USA', 'USA', 'USA', 'USA', 'USA'],
    'Region':['Eastern','Eastern','Eastern','Eastern','Eastern','Eastern','Eastern','Eastern','Eastern','Eastern'],
    'State':['New York','New York','New York','New York','New York','New York','New York','New York','New York','New York'],
    'Supplier':['ABC','ABC','ABC','ABC','ABC','ABC','ABC','ABC','ABC','ABC'],
    'Location':['Bin-1', 'Bin-1', 'Bin-1', 'Bin-1', 'Bin-1','Bin-1', 'Bin-1', 'Bin-1', 'Bin-1', 'Bin-1'],
    'Year':[2016,2016,2016,2016,2016,2019,2019,2019,2019,2019],
    'Month':[4,5,7,8,9,4,5,6,8,9],
    'periodcode':[4,5,7,8,9,4,5,6,8,9],
    'Product':['bike','bike','bike','bike','bike','bike','bike','bike','bike','bike'],
    'total':[0,2000,1000,4000,0,2000,2000,1000,4000,600]})
df.set_index('date', inplace=True)

df['mean'] = df.groupby(['Country','Region','State','Supplier','Location','Product'], as_index=False)['total'].rolling(3).mean().reset_index(level=0,drop=True)
df.head(10)

但是,当我将year列包括到groupby时,即

df['mean'] = df.groupby(['Country','Region','State','Supplier','Location','Product','Year'], as_index=False)['total'].rolling(3).mean().reset_index(level=0,drop=True) 

我计算了滚动平均值。问题是,我希望分组排除Year

有什么想法吗