pandas - pandas 聚合包括所有组

Question

我对 groupby 聚合有以下问题，即添加未在数据框中显示但应基于所需输出的组。一个例子：

import pandas as pd
from pandas.compat import StringIO

csvdata = StringIO("""day,sale
1,1
2,4
2,10
4,7
5,2.3
7,4.4
2,3.4""") 
#day 3,6 are intentionally not included here but I'd like to have it in output

df = pd.read_csv(csvdata, sep=",")
df1=df.groupby(['day'])['sale'].agg('sum').reset_index().rename(columns={'sale':'dailysale'})

df1

我怎样才能得到以下内容？谢谢！

1   1.0
2   17.4
3   0.0
4   7.0
5   2.3
6   0.0
7   4.4

标签： pandaspandas-groupby

Answer 1

您可以在聚合后添加Series.reindex指定的：rangesum

df1 = (df.groupby(['day'])['sale']
         .sum()
         .reindex(range(1, 8), fill_value=0)
         .reset_index(name='dailysale'))
print (df1)

   day  dailysale
0    1        1.0
1    2       17.4
2    3        0.0
3    4        7.0
4    5        2.3
5    6        0.0
6    7        4.4

另一个想法是使用ordered categorical，所以聚合sum添加缺失的行：

df['day'] = pd.Categorical(df['day'], categories=range(1, 8), ordered=True)
df1 = df.groupby(['day'])['sale'].sum().reset_index(name='dailysale')
print (df1)
  day  dailysale
0   1        1.0
1   2       17.4
2   3        0.0
3   4        7.0
4   5        2.3
5   6        0.0
6   7        4.4