Multiple SKU Time Series Forecasting Using Loop

Question

I was trying to recreate a code for Multiple Time Series Forecasting using Facebook Prophet (https://medium.com/grabngoinfo/3-ways-for-multiple-time-series-forecasting-using-prophet-in-python-7a0709a117f9), I was partially successful at this. But unfortunately the output they are generating is a time series which predicts forecast on a day to day basis but my time series input data is on month on month basis.

The Dataset I am using for this is:

{'Date': {0: '2019-01-01', 1: '2019-02-01', 2: '2019-03-01', 3: '2019-04-01', 4: '2019-05-01', 5: '2019-06-01', 6: '2019-07-01', 7: '2019-08-01', 8: '2019-09-01', 9: '2019-10-01', 10: '2019-11-01', 11: '2019-12-01', 12: '2020-01-01', 13: '2020-02-01', 14: '2020-03-01', 15: '2020-04-01', 16: '2020-05-01', 17: '2020-06-01', 18: '2020-07-01', 19: '2020-08-01', 20: '2020-09-01', 21: '2020-10-01', 22: '2020-11-01', 23: '2020-12-01', 24: '2021-01-01', 25: '2021-02-01', 26: '2021-03-01', 27: '2021-04-01', 28: '2021-05-01', 29: '2021-06-01', 30: '2021-07-01', 31: '2021-08-01', 32: '2021-09-01', 33: '2021-10-01', 34: '2021-11-01', 35: '2021-12-01', 36: '2022-01-01', 37: '2022-02-01', 38: '2022-03-01', 39: '2022-04-01', 40: '2022-05-01', 41: '2022-06-01', 42: '2022-07-01', 43: '2022-08-01', 44: '2022-09-01'}, 'XYZ|419': {0: 0, 1: 0, 2: 0, 3: 0, 4: 0, 5: 0, 6: 0, 7: 0, 8: 0, 9: 0, 10: 791, 11: 833, 12: 478, 13: 343, 14: 543, 15: 560, 16: 427, 17: 302, 18: 391, 19: 279, 20: 405, 21: 580, 22: 824, 23: 767, 24: 1102, 25: 1000, 26: 1032, 27: 668, 28: 540, 29: 477, 30: 353, 31: 427, 32: 28, 33: 2, 34: 914, 35: 718, 36: 44, 37: 0, 38: 0, 39: 0, 40: 0, 41: 0, 42: 0, 43: 0, 44: 0}, 'XYZ|426': {0: 0, 1: 0, 2: 0, 3: 0, 4: 0, 5: 0, 6: 0, 7: 0, 8: 0, 9: 0, 10: 0, 11: 0, 12: 0, 13: 0, 14: 0, 15: 0, 16: 0, 17: 29, 18: 374, 19: 330, 20: 402, 21: 1005, 22: 1533, 23: 1582, 24: 1824, 25: 1168, 26: 193, 27: 895, 28: 613, 29: 651, 30: 267, 31: 233, 32: 135, 33: 173, 34: 564, 35: 789, 36: 343, 37: 275, 38: 383, 39: 181, 40: 96, 41: 499, 42: 53, 43: 84, 44: 23}, 'XYZ|465': {0: 0, 1: 0, 2: 0, 3: 0, 4: 0, 5: 0, 6: 0, 7: 0, 8: 0, 9: 0, 10: 0, 11: 0, 12: 0, 13: 0, 14: 0, 15: 0, 16: 0, 17: 44, 18: 292, 19: 240, 20: 364, 21: 806, 22: 1110, 23: 1232, 24: 1207, 25: 753, 26: 571, 27: 731, 28: 0, 29: 174, 30: 0, 31: 23, 32: 86, 33: 31, 34: 559, 35: 857, 36: 316, 37: 217, 38: 182, 39: 93, 40: 50, 41: 323, 42: 42, 43: 48, 44: 23}, 'XYZ|489': {0: 481, 1: 179, 2: 295, 3: 187, 4: 180, 5: 78, 6: 535, 7: 164, 8: 172, 9: 340, 10: 495, 11: 445, 12: 469, 13: 230, 14: 163, 15: 187, 16: 222, 17: 147, 18: 154, 19: 140, 20: 194, 21: 379, 22: 402, 23: 533, 24: 659, 25: 545, 26: 269, 27: 277, 28: 187, 29: 4, 30: 80, 31: 149, 32: 129, 33: 192, 34: 396, 35: 446, 36: 0, 37: 0, 38: 0, 39: 0, 40: 0, 41: 0, 42: 0, 43: 0, 44: 0}, 'XYZ|457': {0: 181, 1: 80, 2: 74, 3: 150, 4: 665, 5: 187, 6: 335, 7: 238, 8: 149, 9: 281, 10: 696, 11: 440, 12: 619, 13: 349, 14: 310, 15: 396, 16: 251, 17: 202, 18: 165, 19: 176, 20: 166, 21: 249, 22: 167, 23: 364, 24: 411, 25: 327, 26: 326, 27: 396, 28: 6, 29: 107, 30: 177, 31: 136, 32: 6, 33: 0, 34: 0, 35: 0, 36: 0, 37: 0, 38: 0, 39: 0, 40: 0, 41: 0, 42: 0, 43: 0, 44: 0}}

I am unable to get the output frequency from a day to day basis to Month on Month basis and also the output is churning out -ve values, Can someone please help me out to point out what is it that I am doing wrong?

import pandas as pd
import numpy as np
from prophet import Prophet
import seaborn as sns
import matplotlib.pyplot as plt
from tqdm import tqdm
from time import time

df = pd.read_excel ('Sample_Data.xlsx')
print (df)
df = df.reset_index()

Dataframe = pd.melt(df,id_vars='Date',value_vars=['XYZ|419','XYZ|426','XYZ|465','XYZ|489','XYZ|457'])
SKU_List = ['XYZ|419','XYZ|426','XYZ|465','XYZ|489','XYZ|457']

Dataframe.columns = ['ds','SKU','y']
Dataframe.head()
Dataframe.info()
group_by_SKU = Dataframe.groupby('SKU')
type(group_by_SKU)
group_by_SKU.describe()
group_by_SKU.groups.keys()

def train_and_forecast(group):
    m=Prophet()
    
    m.fit(group)
    
    future=m.make_future_dataframe(periods=365)
    forecast=m.predict(future)[['ds','yhat','yhat_lower','yhat_upper']]
    forecast['SKU'] = group['SKU'].iloc[0]
    
    return forecast[['ds', 'SKU', 'yhat', 'yhat_upper', 'yhat_lower']]
    
start_time=time()

for_loop_forecast = pd.DataFrame()

for SKU in SKU_List:
    group = group_by_SKU.get_group(SKU)
    forecast = train_and_forecast(group)
    for_loop_forecast=pd.concat((for_loop_forecast,forecast))
    
print('The time used for the for-loop forecast is ', time()-start_time)
for_loop_forecast*

This is the output for after loading the excel

This is the output after melting the data frame

This is the output after DataFrame.info()

This is the final output after the model is fit, as you guys can see this output is predicted on a day to day basis and also the yhat is throwing "-ve" results.

GrabNGoInfo · Answer

make_future_dataframe creates a new dataframe called future for the forecasting. periods=15 means that we will forecast for 15 days of data because the default frequency is daily data.

To use a different frequency, we can specify the freq option. For example, future = m.make_future_dataframe(periods=15, freq='MS') means that we are forecasting for the next 15 months.

In your example, you are using 10 data points to predict 365 data points, which is not likely to give reasonable predictions. I suggest increasing the number of records for the model and decreasing the number of time periods to predict, for example, you can use 365 data points to build the model and predict 10 data points.

Hopefully this helps. Thank you for reading my tutorial.

Multiple SKU Time Series Forecasting Using Loop

1 Answers