I have a data with the Date and Volume column like below, where I am trying to adjust the calendar, the dates are week-ending dates, hence some of the data is missed since May ended with a full week on 28 May and the next week was shared with June and the same for September. How do I distribute the data from the next month to the previous month?
| Date | Volume |
|---|---|
| 2022/05/28 4 W/E | 13190466.37 |
| 2022/07/02 5 W/E | 16679937.92 |
| 2022/07/30 4 W/E | 13195704.23 |
| 2022/08/27 4 W/E | 13388264.4 |
| 2022/10/01 5 W/E | 16674265.42 |
| 2022/10/29 4 W/E | 13178313.2 |
| 2022/11/26 4 W/E | 13307829.47 |
Things I have came up with is not that useful also I am still missing other months
df = df.withColumn('no_of_days',dayofmonth(last_day(col('date'))))
.withColumn('consumptionweeks',substring(col('date'),12,1) )
df = df.withColumn('calendar_adjustment',col('no_of_days')/(col('consumptionweeks').cast('int')*7))