I need to split and resample sales by shops between dates to monthly (calendar month).
| Offer | Start Date | End Date | Sales Shop 1 | Sales Shop 2 | Sales Shop 3 | Total Sales |
|---|---|---|---|---|---|---|
| Offer1 | 2018-01-01 | 2018-01-31 | 100 | 200 | 300 | 600 |
| Offer1 | 2018-03-14 | 2018-05-23 | 110 | 220 | 330 | 660 |
| Offer2 | 2018-08-01 | 2019-01-31 | 120 | 230 | 340 | 690 |
| Offer3 | 2018-08-21 | 2018-12-31 | 130 | 240 | 350 | 720 |
| Offer4 | 2020-12-31 | 2021-01-01 | 140 | 250 | 360 | 750 |
I require to have sales by shops by calendar month. The problem is I am unable to split the sales for the offers run more than a month. Output is required like the below.
| Calendar Month | Sales Shop 1 | Sales Shop 2 | Sales Shop 3 | Total Sales | Offers count that month |
|---|---|---|---|---|---|
| Jan-2018 | 100 | 200 | 300 | 600 | 1 |
| Feb-2018 | 0 | 0 | 0 | 0 | 0 |
| Mar-2018 | 110 / (17 offer active days of Mar) | 220 /(17 offer active days of Mar) | 330 /(17 offer active days of Mar) | 660 /(17 offer active days of Mar) | 1 |
| Apr-2018 | 110 /(30 offer active days of Apr) | 220/ (30 offer active days of Apr) | 330 /(30 offer active days of Apr) | 660/ (30 offer active days of Apr) | 1 |
| May-2018 | 110 /(23 offer active days of May) | 220 /(23 offer active days of May) | 330 /(23 offer active days of May) | 660/ (23 offer active days of May) | 1 |
and so on ...
I tried something like this but it's not solving the purpose.
df['Duration']= ((df['End Date']-df['Start Date']).dt.days)+1
df[numcols] = df[numcols].div(df['Duration'].values, axis=0)
df = df.join(
pd.Series(df.index, index=df.index)
.rename("expanded")
.dt.date.apply(lambda d: pd.date_range(d, freq="d", periods=1))
.explode()
).set_index("expanded")
please help and thanks in advance.
