0

I have a pandas DataFrame that looks like this

Date         item   count
2019-01-01   i1     25
2019-01-01   i2     46
2019-01-02   i1     97
2019-01-02   i2     23
2019-01-02   i3     26
2019-01-03   i4     237
2019-01-04   i2     25
2019-01-10   i2     59

Date is of type datetime

I am looking for a way to get the sum of each of the items grouped into weekly bins. The resulting dataframe should look like this:

Week                       i1      i2     i3    i4
(2019-01-01, 2019-01-08]   122     94     26    237
(2019-01-08, 2019-01-15]   0       59     0     0

I have looked at Pandas pd.cut() - binning datetime column / series to create date bins, but I don't want the count of items grouped by the bins, rather the sum of the count of items grouped by the bins. So far, the only working solution I have is to use pandas.iterrows() and iterate over each row. Is there a better way of doing this ?

Hariharan Muralidharan
  • 17
  • 5

1 Answers1

1

We have unstack after groupby 

s=df.groupby([df.Date.dt.week,df.item])['count'].sum().unstack(fill_value=0)
Out[204]: 
item   i1  i2  i3   i4
Date                  
1     122  94  26  237
2       0  59   0    0
BENY
  • 317,841
  • 20
  • 164
  • 234