0
$\begingroup$

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))
$\endgroup$
3
  • 1
    $\begingroup$ I have no idea what you are asking for, but if you are asking "how much volume occurred in may" and "how much volume occurred in june" there is simply not enough information from this alone to answer. Perhaps if you were to assume that within the recorded windows the volume was consistent day to day you could estimate this... but that is not a safe assumption. For instance, weekdays may have different rates than weekends... holidays could play a factor... there may be a greater trend of increase over a particular month... paycheck schedules could matter, etc... $\endgroup$
    – JMoravitz
    Commented Nov 15, 2023 at 15:10
  • $\begingroup$ The punchline is that the data shows what the data says it shows, and does not show anything else. $\endgroup$
    – JMoravitz
    Commented Nov 15, 2023 at 15:11
  • $\begingroup$ @JMoravitz, Maybe I can subtract the date with 7 so that I can get each month available in the data and then adjust it according. And then Calendar Adjustment = [Number of days in month] / [Number of Weeks in month] *7 Calendar Adjusted Vol = Calendar Adjustment * Volume $\endgroup$
    – noswear
    Commented Nov 15, 2023 at 15:27

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .