How to use pandas Grouper with 7d frequency and fill missing days with 0?

Question

I have the following sample dataset

df = pd.DataFrame({
    'names': ['joe', 'joe', 'joe'],
    'dates': [dt.datetime(2019,6,1), dt.datetime(2019,6,5), dt.datetime(2019,7,1)],
    'values': [5,2,13]
})

and I want to group by names and by weeks or 7 days, which I can achieve with

df_grouped = df.groupby(['names', pd.Grouper(key='dates', freq='7d')]).sum()

                  values
names dates             
joe   2019-06-01       7
      2019-06-29      13

But what I would be looking for is something like this, with all the explicit dates

                  values
names dates             
joe   2019-06-01       7
      2019-06-08       0
      2019-06-15       0
      2019-06-22       0
      2019-06-29      13

And by doing df_grouped.index.levels[1] I see that all those intermediate dates are actually in the index, so maybe that's something I can leverage.

Any ideas on how to achieve this?

Thanks

Answer 1

Use DataFrameGroupBy.resample with DatetimeIndex:

df_grouped = df.set_index('dates').groupby('names').resample('7D').sum()
print (df_grouped)
                  values
names dates             
joe   2019-06-01       7
      2019-06-08       0
      2019-06-15       0
      2019-06-22       0
      2019-06-29      13