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 ?

Improve this question

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you! This saved me a lot of time!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.