Getting monthly data into daily/weekly data

General
,
1

Hi there,

I am facing the following issue:
I have a dataframe Actuals [months, products] which has forecasts on a monthly level.
Now I need to convert it into days and then calender weeks by the following formula:

(demand in month/days in month ) *days in week =demand CW

Since for some CWs the months are overlapping I have figured the following logic
month / p^1 /p^2 etc....
1 /100
2 /200
3/100
.....
I need so create a vector containing the days in month acordingly
monthly_divisor<- c(31,28,31,30,31,30,31,31,30,31,30,31,31,28,31,30,31,30)
and divide the data by the number of days in week.
Then I need to make a long list where the first month is replicated 31 times and Febrary 29 times and so on ...
Last but not least I summarize the Calender weeks accordingly.
The thing is that I do not know really how to start, as I am very new to R and I have a tight schedule for my Thesis.
Can anybody help me on this?
I read that the best way would be to write a function and run it with adply ()....

UPDATE:

See the approach below but I would still need help ....

2

I'd recommend using the lubridate package which you can learn more about at the links below:

If you can provide a reprex (see FAQ below), it'll be easier for people to help you with the specifics:

3

You might want to take a look at this paper, for some discussion of formal methods for time series disaggregation (might provide some ideas for alternative approaches, or at least context for the algorithm you have come up with):
https://journal.r-project.org/archive/2013/RJ-2013-028/index.html

Note that these methods are all limited to situations where one frequency is an integer multiple of the other — this is not the case for going from months to weeks or days, unless you introduce something like an artificial standard 30 day month.

For the monthly to weekly case, I guess you could also think about it like a weighted calculation, weighting according to the month’s contribution to a given week?

You’ve probably thought of this already, but maybe also consider how to communicate whether there are limitations to how meaningful your monthly forecasts are when re-expressed on a weekly or daily basis (I’m wondering if there are analogous issues to e.g., https://www.researchgate.net/publication/4747863_Temporal_Aggregation_and_Economic_Time_Series).

2 Likes
4

tibbletime might be useful here

2 Likes
5 
monthly_divisor<- c(31,28,31,30,31,30,31,31,30,31,30,31)
monthly_divisor <-as.numeric(monthly_divisor)
Productdemand1rstforecast<-as.data.frame.numeric(Productdemand1rstforecast)

Productdemand1rstforecast =cbind (Dates,Productdemand1rstforecast )
for (n in 2:ncol(Productdemand1rstforecast)){
 
  for ( i in 1:12){
    Productdemand1rstforecast [i,n]= round(Productdemand1rstforecast [i,n]/monthly_divisor [i])
 
  }
} 
 
dt_monthly<- data.table (Productdemand1rstforecast)

#replicate the data by days in mont
#add up 7 rows -> new data frame
#declaration of Calender weeks acordingly

Hey guys, this was my first step...
The data has the following structure
rows= months, columns = products.
Next step would be to do a replica of the first month 31 times then 28 and so on...
Then add up 7 rows at a time and rename the rows with the corresponding CWs of the year.
I mean it is not pretty but I think it will do the job. But I do not know how to continue.
Any ideas?

PS: cant use reprex- its confidential data

6

Anna,
I hope you have daily actuals for some period of time (13-18 months) - if not - ignore my answer...
The best way to allocate monthly numbers by days/weeks would be to produce daily forecasting model that takes into account calendar events for each month - holidays, days of week, number of days in month, etc. Depending on business line that you are in, 5 Fridays in a month could mean that monthly volume is different from 5 Mondays month (just an example). Once you have your calendar model forecast, you can create weights for each day within the month based on calendar factors (daily forecast divided by sum of daily for the month). After applying weight to monthly forecast number you will get daily expectations, and you can create weekly summaries.

2 Likes
7

Problem is with daily forecasting that you face the fact of complex seasonality. Do you think it makes sense, considering that some products do show intermittend demand to go with this approach.
I thought since I have that sompley seasonality, I chose bucket conversion for forcasting and disaggregate in a very stubborn way since it cancels out the complex seasonality factor.
Dont you agree?
(I have that daily data - even the order development data)

Do you know a model that kinda handles complex seasonality besides tbats and stlf

8

Anna,
I was not using automatic models because of complexity of 3-4 levels of different seasonality (monthly/weekly/bi-weekly/semi-monthly) and the need to account for holidays, special days, etc. I essentially used complex set of dummy variables to account for mix of different seasonalities. Email me at yan dot lyesin at gmail dot com - i will send you my calendar file and you will get an idea...

1 Like
9

sent you a mail :slight_smile: thanks for the help