CODEWITHSUNDEEP
pythonpandasdataframe
tj judge
tj judge

Reputation: 616

Convert monthly data to weekly data - Python

I have a dataframe which looks like this:

ID Date     Volume Sales
1  2020-02   10     4
1  2020-03   8      6
2  2019-12   6      8
2  2019-10   4      10

Data here is monthly, and I would like to convert it to weekly.. dividing the volume and sales column by the number of weeks in the month.

I have tried to this but have only been successful doing this column by column.. is there an easy way to do this, that could also be handled on a large dataset.

Would it be a similar step in converting weekly back to monthly? Thanks!

Upvotes: 2

Views: 2751

Answers (1)

anky
anky

Reputation: 75080

From what I understand you can try creating an array of all weeks based on the Date column using pd.offset, then explode and groupby+transform to get count of each group for using it in division:

s = pd.to_datetime(df['Date'])
u = (df.assign(Weeks=[pd.date_range(*x,freq='W')
                  for x in zip(s,s.add(pd.offsets.MonthEnd()))]).explode("Weeks"))

u[['Volume','Sales']] = u[['Volume','Sales']].div(
                        u.groupby("Date")['Date'].transform("count"),axis=0)

print(u)

   ID     Date  Volume  Sales      Weeks
0   1  2020-02     2.5    1.0 2020-02-02
0   1  2020-02     2.5    1.0 2020-02-09
0   1  2020-02     2.5    1.0 2020-02-16
0   1  2020-02     2.5    1.0 2020-02-23
1   1  2020-03     1.6    1.2 2020-03-01
1   1  2020-03     1.6    1.2 2020-03-08
1   1  2020-03     1.6    1.2 2020-03-15
1   1  2020-03     1.6    1.2 2020-03-22
1   1  2020-03     1.6    1.2 2020-03-29
2   2  2019-12     1.2    1.6 2019-12-01
2   2  2019-12     1.2    1.6 2019-12-08
2   2  2019-12     1.2    1.6 2019-12-15
2   2  2019-12     1.2    1.6 2019-12-22
2   2  2019-12     1.2    1.6 2019-12-29
3   2  2019-10     1.0    2.5 2019-10-06
3   2  2019-10     1.0    2.5 2019-10-13
3   2  2019-10     1.0    2.5 2019-10-20
3   2  2019-10     1.0    2.5 2019-10-27

Upvotes: 1

Related Questions