# How do I arrange my data to predict 6 weeks of daily sales

I have a data.table base that has many variables to use them to forecasting sales for the next 6 weeks of daily sales. In fact, all the database is arranged by date as you can see here.Note that here I just show you some of variables.

> Data_train[order(Date)]
Store DayOfWeek       Date Sales Customers Open Promo StateHoliday SchoolHoliday
1:     1         2 2013-01-01     0         0    0     0            a             1
2:     2         2 2013-01-01     0         0    0     0            a             1
3:     3         2 2013-01-01     0         0    0     0            a             1
4:     4         2 2013-01-01     0         0    0     0            a             1
5:     5         2 2013-01-01     0         0    0     0            a             1
---
1017205:  1111         5 2015-07-31  5723       422    1     1            0             1
1017206:  1112         5 2015-07-31  9626       767    1     1            0             1
1017207:  1113         5 2015-07-31  7289       720    1     1            0             1
1017208:  1114         5 2015-07-31 27508      3745    1     1            0             1
1017209:  1115         5 2015-07-31  8680       538    1     1            0             1 .


My question is about the arrangement of the data according to the target goal. My problem is really about the Date variable. In fact, I suggest this path:

1. I sum all sales by each date (because I have many types of stores).
2. I order my database according to the Date 's ascending order.
3. I didn't need duplicated rows in the date variables so I delete them.

Just to show you the new base for considered variables.

> Data_train[,SumSaleseachDay:=sum(Sales),by=c('Date')][order(Date)][!duplicated(Date)][,-c('Sales','Customers'),with=FALSE]
Store DayOfWeek       Date Open Promo StateHoliday SchoolHoliday SumSaleseachDay
1:     1         2 2013-01-01    0     0            a             1           97235
2:     1         3 2013-01-02    1     0            0             1         6949829
3:     1         4 2013-01-03    1     0            0             1         6347820
4:     1         5 2013-01-04    1     0            0             1         6638954
5:     1         6 2013-01-05    1     0            0             1         5951593
---
938:     1         1 2015-07-27    1     1            0             1        10707292
939:     1         2 2015-07-28    1     1            0             1         9115073
940:     1         3 2015-07-29    1     1            0             1         8499962
941:     1         4 2015-07-30    1     1            0             1         8798854
942:     1         5 2015-07-31    1     1            0             1        10109742


ADDED INFORMATION: I have a database of 1017209 rows. And for each Store, I have its historic of Sales between 2013-01-01 and 2015-07-31. And I have also 17 variables included to build the model.

The steps above just lead to forecast by day.

If I want to forecast for each Store and by day, what should I do?

I guess you are trying to predict the future sales based on the data from two years that each store gathered. So I would suggest you to train model on each individual set for each store which will predict the forecast of the day for that store and feed the prediction to the separate model which will predict the overall forecast on all the stores based upon the individual store prediction, thus you can get individual predictions as well as overall forecast from the model.

-> Based on your comments Remembers it is a bit time consuming but i think it worth spending for good results you can skip it and learn from tutorials where i learned alot (not promoting)

They are not for your specific purpose but close to yours

You can learn the whole process of prediction in the following tutorials Tutorials

Here we go step by step what i had explained

-> First normalize all the data sets from each store

-> then Choose the algorithm you want to use

-> then Arrange the store data in a organized way because you may get bit confused while training

-> Initially Just pick one data set and tune the hyper parameters and understand working of those parameters carefully because your going to them a lot.

-> When you get the point Start from the top to bottom of store list and Store the trained model for each store with similar names

-> When your done with all the stores you will be getting predictions for each as you requested and then collect those prediction and average them and you would be getting approximate predication from all the stores

• thank you for your suggestion.I agree with you in training the model for each store. But I think that It will be more efficient if you edited it and give clear Steps. Remember that I have couple of constraints here, need a prediction of Sales each Store and each Day for the next 6 weeks. – Amir Feb 21 '18 at 15:12
• I think that your idea is good.But it would be very long if we have many types of Stores – Amir Feb 22 '18 at 10:27

I guess it depends on exactly what you want - do you want a forecast per store? If so you would need to leave the data set aggregated only to a store level. If not, and you want the overall sales by day, then you are OK to leave it aggregated.

Another consideration is whether there are many store-level features e.g. location, weather, local demographics. These features may be useful for the model to learn from and would lose resolution if store level data is aggregated, therefore leaving it un-aggregated would be better. The counter point to this is whether you have enough data for each store to learn representative trends! So it does get kind of circular and depends on the size of your data set.