Skip to main content
 首页 » 编程设计

python之填充缺失日期后在 pandas groupby 对象中填充值

2024年11月01日8sxdcgaq8080

已经问了很多类似的类似问题,这对我解决这个问题有很大帮助,我遵循了以下帮助: Fill in missing dates of groupby 和 Pandas- adding missing dates to DataFrame while keeping column/index values?

然而它仍然没有发挥作用。

我制作了一个玩具数据集来演示我面临的问题:

data = pd.DataFrame({'Date': ['2012-01-01', '2012-01-01','2012-01-01','2012-01-02','2012-01-02','2012-01-02','2012-01-03'], 'Id': ['21','21','22','21','22','23','21'], 'Quantity': ['5','1','4','4','2','1','4'], 'NetAmount': ['66','45','76','35','76','73','45']}) 
 
data['Quantity'] = data['Quantity'].astype('int') 
data['NetAmount'] = data['NetAmount'].astype('float') 

我按照下面的代码对数据集进行了分组:

data['Date'] =pd.to_datetime(data.Date) - pd.to_timedelta(7,unit = 'd') 
 
data =data.groupby(['Id',pd.Grouper(key='Date', freq='W-MON')])['Quantity', 'NetAmount'].sum().reset_index().sort_values('Date') 
data.reset_index() 
data1 = data.groupby(['Id','Date']).agg({'Quantity': sum, 'NetAmount': sum}).reset_index() 
 

然后我填写缺失的日期:

data2 = data1.set_index(['Date', 'Id','NetAmount']).Quantity.unstack(-3).\ 
    reindex(columns=pd.date_range(data1['Date'].min(), data1['Date'].max(),freq='W-MON'),fill_value=0).\ 
            stack(dropna=False).unstack().stack(dropna=False).\ 
                unstack('NetAmount').stack(dropna=False).fillna(0).reset_index() 

给出结果数据框:

   Id    level_1  NetAmount     0 
0   21 2011-12-26       45.0   0.0 
1   21 2011-12-26       73.0   0.0 
2   21 2011-12-26      146.0  10.0 
3   21 2011-12-26      152.0   0.0 
4   21 2012-01-02       45.0   4.0 
5   21 2012-01-02       73.0   0.0 
6   21 2012-01-02      146.0   0.0 
7   21 2012-01-02      152.0   0.0 
8   22 2011-12-26       45.0   0.0 
9   22 2011-12-26       73.0   0.0 
10  22 2011-12-26      146.0   0.0 
11  22 2011-12-26      152.0   6.0 
12  22 2012-01-02       45.0   0.0 
13  22 2012-01-02       73.0   0.0 
14  22 2012-01-02      146.0   0.0 
15  22 2012-01-02      152.0   0.0 
16  23 2011-12-26       45.0   0.0 
17  23 2011-12-26       73.0   1.0 
18  23 2011-12-26      146.0   0.0 
19  23 2011-12-26      152.0   0.0 
20  23 2012-01-02       45.0   0.0 
21  23 2012-01-02       73.0   0.0 
22  23 2012-01-02      146.0   0.0 
23  23 2012-01-02      152.0   0.0 
 

但实际上我希望得到:

0   21 2011-12-26       66.0   5.0 
1   21 2011-12-26       45.0   1.0 
2   21 2011-12-26       35.0   4.0 
3   21 2012-02-02       45.0   4.0 
4   22 2011-12-26       76.0   4.0 
5   22 2012-02-02       76.0   2.0 
6   23 2011-12-26        0.0   0.0 
7   23 2012-02-02       73.0   1.0 

填充有效,但是,我不明白结果数据框中到底发生了什么,例如在 netAmount 列中,结果是关闭的我是 unstack/stack 函数的新手,我在这个过程中遗漏了什么?感谢您的帮助!

更新:我尝试在添加“0”值后按 id 和数据重新分组:

data2 = pd.DataFrame(data2) 
data3 = data2.groupby(['Id','Date']).agg({'Quantity': sum, 'NetAmount': sum}).reset_index() 

但是我得到了这个错误

Traceback (most recent call last): 
  File "", line 48, in <module> 
    data3 = data2.groupby(['Id','Date']).agg({'Quantity': sum, 'NetAmount': sum}).reset_index() 
  File "", line 7632, in groupby 
    observed=observed, **kwargs) 
  File "", line 2110, in groupby 
    return klass(obj, by, **kwds) 
  File "", line 360, in __init__ 
    mutated=self.mutated) 
  File "", line 578, in _get_grouper 
    raise KeyError(gpr) 
KeyError: 'Date' 

请您参考如下方法:

您需要将列 Quantity 和 NetAmount 转换为数字

data['Quantity'] = data['Quantity'].astype('int') 
data['NetAmount'] = data['NetAmount'].astype('float') 

当列为字符串时,sum 函数按组连接所有字符串。

现在重新运行您的代码,它应该会按预期工作

#   Id  level_1   NetAmount 0 
#0  21  2011-12-26  45.0    0.0   
#1  21  2011-12-26  73.0    0.0 
#2  21  2011-12-26  146.0   10.0 
#3  21  2011-12-26  152.0   0.0 
#4  21  2012-01-02  45.0    4.0 
#5  21  2012-01-02  73.0    0.0 
#6  21  2012-01-02  146.0   0.0 
#7  21  2012-01-02  152.0   0.0 
#8  22  2011-12-26  45.0    0.0 
#9  22  2011-12-26  73.0    0.0 
#10 22  2011-12-26  146.0   0.0 
#11 22  2011-12-26  152.0   6.0 
#12 22  2012-01-02  45.0    0.0 
#13 22  2012-01-02  73.0    0.0 
#14 22  2012-01-02  146.0   0.0 
#15 22  2012-01-02  152.0   0.0 
#16 23  2011-12-26  45.0    0.0 
#17 23  2011-12-26  73.0    1.0 
#18 23  2011-12-26  146.0   0.0 
#19 23  2011-12-26  152.0   0.0 
#20 23  2012-01-02  45.0    0.0 
#21 23  2012-01-02  73.0    0.0 
#22 23  2012-01-02  146.0   0.0 
#23 23  2012-01-02  152.0   0.0