schemaVersion: 1
meta:
  sourceVersionId: 21e74f73-6609-44f6-aeab-1e9330d4f747 # DO NOT CHANGE - Hex uses this to match up project versions when reimporting the file
  description: null
  projectId: 4ce7c5ec-0de9-45c7-b039-6fe4d48ac189 # DO NOT CHANGE - Unique ID of the project from which this file was generated
  title: Forecasting Hourly Traffic
  timezone: null
  appTheme: SYS_PREF
  codeLanguage: PYTHON
  status: null
  categories: []
  castDecimalsDefault: true
projectAssets:
  dataConnections: []
  envVars: []
  secrets: []
sharedAssets:
  secrets: []
  vcsPackages: []
  dataConnections:
    - dataConnectionId: 0194571e-bf12-46de-8b6e-b2f77918febd # Snowflake (snowflake)
cells:
  - cellType: MARKDOWN
    cellId: 8c1e28c3-6ec3-4bea-b22f-5d96a0f9b17f # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        # Setup
        Install required packages and make sure all the libraries we need have been imported.
  - cellType: INPUT
    cellId: e6374490-65a5-418d-ac74-17cef3aa33cc # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      inputType: BUTTON
      name: install
      outputType: BOOLEAN
      options:
        intent: primary
        icon: play
        text: Install packages (required)
      defaultValue: null
  - cellType: CODE
    cellId: 335a8b19-266b-48d1-952f-9f22d3d4fca4 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        if install:
            !pip install mockseries
            !pip install --upgrade xgboost
        else:
            print("Click the button to install the required packages")
  - cellType: CODE
    cellId: 217f5e6f-3970-4649-a076-acc27331a318 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        # Mock Data
        import warnings
        warnings.filterwarnings('ignore')
        from mockseries.trend import LinearTrend
        from mockseries.seasonality import SinusoidalSeasonality
        from mockseries.noise import RedNoise
        from mockseries.utils import plot_timeseries, write_csv
        from datetime import datetime, date, timedelta
        from mockseries.utils import datetime_range
        from pandas.tseries.holiday import USFederalHolidayCalendar
        import numpy as np
        import random

        ## Snowpark 
        import snowflake.snowpark
        from snowflake.snowpark.session import Session
        from snowflake.snowpark import functions as F
        from snowflake.snowpark.functions import col
        from snowflake.snowpark.types import StringType, DateType, TimestampType, IntegerType, StructType, StructField, FloatType
        from snowflake.snowpark.functions import dateadd, current_date
        from snowflake.snowpark.functions import udtf

        # Model Deployment
        from time import time
        import sys, string, io, os, math
        import zipfile, json, pickle
        import pandas as pd
        import xgboost as xgb
        from sklearn.metrics import mean_squared_error, mean_absolute_error
  - cellType: MARKDOWN
    cellId: b0bd33a7-de2a-40e6-a885-d87a455fa4ad # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        ## Establish Secure Connection to Snowflake
        Add the schema to use throughout this project to the snowpark session connection. This ensures that data is always written to the correct location.
  - cellType: TEXT
    cellId: ce1586fe-d74f-44c8-823e-b2f5018d910f # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      richText:
        - type: h1
          children:
            - text: Creating the data
  - cellType: CODE
    cellId: 436422e1-4270-4052-84ec-a7bb1e5c85c9 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        class Dataset:
            ''' Used to generate the data used for forecasting '''
            def __init__(self):
                trend = LinearTrend(coefficient=0.025, time_unit=timedelta(days=4), flat_base=100)
                seasonality = SinusoidalSeasonality(
                    amplitude=20, period=timedelta(days=7)
                ) + SinusoidalSeasonality(amplitude=4, period=timedelta(days=1))
                noise = RedNoise(mean=0, std=3, correlation=0.5)
                timeseries = trend + seasonality + noise

                self.time_points = datetime_range(
                granularity=timedelta(hours=1),
                start_time=datetime(2018, 6, 16),
                end_time=datetime.today())
                self.ts_values = timeseries.generate(time_points=self.time_points)
                self.data = None
                self.dataset = None
                self.calendar = None

            def plot(self):
                plot_timeseries(self.time_points, self.ts_values)

            def _create_dataframe(self):
                df = pd.DataFrame({"time_points":self.time_points,"ts_values":self.ts_values})
                df['hour'] = df['time_points'].dt.hour
                df['date'] = df['time_points'].dt.date
                df['dayofweek'] = df['time_points'].dt.weekday

                df = df[df['hour'].between(7,22)]

                df['weekday'] = np.where((df['dayofweek']>= 1) & (df['dayofweek']<= 4), 1, 0)
                df['weekend'] = np.where((df['dayofweek']>= 5) & (df['dayofweek']<= 6), 1, 0)
                df['sunday'] = np.where(df['dayofweek']== 0 , 1, 0)
                df['breakfast'] = np.where((df['hour']>= 7) & (df['hour']<= 10), 1, 0)
                df['lunch'] = np.where((df['hour']>= 11) & (df['hour']<= 13), 1, 0)
                df['break'] = np.where((df['hour']>= 14) & (df['hour']<= 15), 1, 0)
                df['dinner'] = np.where((df['hour']>= 16) & (df['hour']<= 20), 1, 0)
                df['close'] = np.where(df['hour']>= 21, 1, 0)
                self.data = df

            def _create_date_table(self, start='2018-01-01', end='2025-12-31'):
                df = pd.DataFrame({"CALENDAR_DATE": pd.date_range(start, end)})
                df["CALENDAR_WEEK_DAY_NBR"] = df.CALENDAR_DATE.dt.dayofweek
                df["CALENDAR_MTH_DAY_NBR"] = df.CALENDAR_DATE.dt.day
                df["CALENDAR_MTH"] = df.CALENDAR_DATE.dt.month
                df["CALENDAR_YEAR"] = df.CALENDAR_DATE.dt.year
                return df
            
            def _random_traffic(self, r):
                if r["close"] == 1:
                    return random.uniform(0,.1)
                elif (r["weekday"] == 1 and r["break"] == 1) or (r["weekday"] == 1 and r["breakfast"] == 1):
                    return random.uniform(.15,.2)
                elif (r["weekday"] == 1 and r["dinner"] == 1) or (r["weekend"] == 1 and r["break"] == 1):
                    return random.uniform(.25,.35)
                elif (r["sunday"] == 1) and (r["dinner"] == 1):
                    return random.uniform(.4,.5)
                elif (r["weekend"] == 1 and r["breakfast"] == 1) or (r["weekend"] == 1 and r["dinner"] == 1) or (r["weekday"] == 1 and r["lunch"] == 1) or (r["sunday"] == 1 and r["break"] == 1):
                    return random.uniform(.52,.65)
                elif (r["sunday"] == 1 and r["breakfast"] == 1) or (r["weekend"] == 1 and r["lunch"] == 1):
                    return random.uniform(.70,.8)
                elif (r["sunday"] == 1 and r["lunch"] == 1):
                    return random.uniform(.95,1)
                else:
                    return 0

            def create_traffic_table(self):
                ''' Run this function to simulate the hourly traffic which is returned as a snowflake dataframe'''

                print('Creating Initial Dataframe...', end = " ")
                self._create_dataframe()
                print("Complete!")

                print('Simulating restaurant traffic... (about a 3 min wait)', end = ' ')
                dfs = []
                # takes abt 3 min to run
                for i in range (1,201):
                    _ = self.data.copy()
                    _['store_id'] = i
                    _['college_town'] = np.random.randint(0,2)
                    _["rest_shift"] = self.data.apply(self._random_traffic, axis = 1)
                    dfs.append(_)

                self.data = pd.concat(dfs)
                print("Complete!")
            
                print("Adding US calendar Holidays...", end = " ")
                calendar = USFederalHolidayCalendar()
                holiday_df = (
                    pd.DataFrame(
                        calendar.holidays(start=min(self.data["date"]), end=max(self.data["date"]), return_name=True)
                    )
                    .reset_index()
                    .rename(columns={"index": "date", 0: "holiday_name"})
                )

                holiday_df['date'] = holiday_df['date'].dt.date
                self.data = self.data.merge(holiday_df, on = 'date', how = 'left')
                self.data['hourly_traffic'] = self.data.ts_values * self.data.rest_shift
                print("Complete!")

                print("Creating master dataset...", end = " ")
                final = self.data[['time_points', 'hourly_traffic','holiday_name','store_id','college_town']]
                final['hourly_traffic'] = pd.to_numeric(final['hourly_traffic'])
                final['hourly_traffic'] = final['hourly_traffic'].astype(float)
                final['hourly_traffic'] = final['hourly_traffic'].round()
                final['hourly_traffic'] = final['hourly_traffic'].astype(int)
                final = final.rename(columns={"time_points": "TIME_POINTS", "hourly_traffic": "HOURLY_TRAFFIC","holiday_name": "HOLIDAY_NAME","store_id": "STORE_ID","college_town":"COLLEGE_TOWN"})
                self.dataset = final  
                print("Master dataset created!\nWrite this dataframe back into your Snowflake database.")
                return self.dataset

            def create_calendar_table(self, session):
                print("Creating calendar...", end = " ")
                calendar_df = self._create_date_table()

                calendar = USFederalHolidayCalendar()
                holiday_df = (
                    pd.DataFrame(
                        calendar.holidays(start='2018-01-01', end='2025-12-31', return_name=True)
                    )
                    .reset_index()
                    .rename(columns={"index": "date", 0: "holiday_name"})
                )

                holiday_df['date'] = holiday_df['date'].dt.date
                calendar_df['CALENDAR_DATE'] = calendar_df['CALENDAR_DATE'].dt.date
                calendar_final = calendar_df.merge(holiday_df, left_on='CALENDAR_DATE', right_on='date', how = 'left')
                calendar_final = calendar_final.rename(columns={"holiday_name":"HOLIDAY_NAME"})
                calendar_final_snow_df = session.create_dataframe(calendar_final).select('CALENDAR_DATE','CALENDAR_WEEK_DAY_NBR','CALENDAR_MTH_DAY_NBR','CALENDAR_MTH','CALENDAR_YEAR','HOLIDAY_NAME')
                calendar_final_snow_df = calendar_final_snow_df.select(
                    col("CALENDAR_DATE"),
                    col("CALENDAR_WEEK_DAY_NBR").cast(StringType()).alias("CALENDAR_WEEK_DAY_NBR"),
                    col("CALENDAR_MTH_DAY_NBR").cast(StringType()).alias("CALENDAR_MTH_DAY_NBR"),
                    col("CALENDAR_MTH").cast(StringType()).alias("CALENDAR_MTH"),
                    col("CALENDAR_YEAR").cast(StringType()).alias("CALENDAR_YEAR"),
                    col("HOLIDAY_NAME"),
                )
                self.calendar = calendar_final_snow_df.toPandas()
                print("Complete!")

                return self.calendar
  - cellType: CODE
    cellId: e997513e-b6c9-49d1-94ce-95fefee0f3db # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        # create an instance of the dataset class so that we can get our hourly traffic dataset
        dataset = Dataset()
  - cellType: CODE
    cellId: 7d795c83-94a8-40ba-b428-b7e7669d4b19 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: Code 9
    config:
      source: |-
        # We can get a pandas dataframe by calling this function, which generates the data for us
        traffic = dataset.create_traffic_table()
  - cellType: CODE
    cellId: 1867e54f-e04a-44fa-b919-cb2cf0dbcb70 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: cal = dataset.create_calendar_table(session)
  - cellType: TEXT
    cellId: 2612402f-92f1-40e2-89e4-2742c8057969 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      richText:
        - type: h2
          children:
            - text: Write data back to database
  - cellType: CODE
    cellId: dca4b3d9-18f0-46f2-82af-21baa198b25d # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        #Add stage for UDFs and Stored Procs
        session.sql('''
        create stage if not exists pymodels
        ''').collect()
  - cellType: MARKDOWN
    cellId: b58a2e94-3a6c-42a1-8839-de66baba3853 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        # Explore Historical Data

        Let's look at the historical **HOURLY_TRAFFIC** and **CALENDAR_INFO** tables from the stores.
  - cellType: CODE
    cellId: 04b09b5a-b1ff-490e-bc45-215467a73ea6 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        store_hourly_info_df = session.table('HOURLY_TRAFFIC')
        store_hourly_info_df.limit(10).toPandas()
  - cellType: CODE
    cellId: 6b4c64db-b1cf-4418-b6c0-f1d0a9bc5aff # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        store_calendar_info_df = session.table('CALENDAR_INFO')
        store_calendar_info_df.limit(10).toPandas()
  - cellType: MARKDOWN
    cellId: fc4bccb9-4017-48d9-be83-f74ea6d969b7 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        # Feature Engineering and Data Pre-Processing Tasks
        We're going to create a feature table with past historical data and future data to pass in to our XGBoost model.
  - cellType: MARKDOWN
    cellId: 880a38f1-5608-45b3-8e81-d22312eb15df # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: "### Create a dataframe with relevant historical data"
  - cellType: CODE
    cellId: ff8be39e-399f-465a-8ff9-064c082e143d # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        ## Extract date and hour from the time stamp in Hourly traffic
        past = store_hourly_info_df.select(
            "TIME_POINTS",
            col("TIME_POINTS").cast(DateType()).alias("DATE"),
            F.hour(col("TIME_POINTS")).alias("HOUR"),
            "STORE_ID",
            "COLLEGE_TOWN",
            "HOURLY_TRAFFIC",
        )


        ## Join the Calendar info table to the Hourly traffic table
        ## Filter hour between 7 and 22 since the restaraunts are only open from 7am -> 10pm
        past_final = (
            past.join(
                store_calendar_info_df,
                (store_calendar_info_df.col("CALENDAR_DATE") == past.col("DATE")),
                "left",
            )
            .select(
                col("TIME_POINTS"),
                col("HOUR"),
                "STORE_ID",
                "COLLEGE_TOWN",
                "CALENDAR_WEEK_DAY_NBR",
                "CALENDAR_MTH_DAY_NBR",
                "CALENDAR_MTH",
                "CALENDAR_YEAR",
                "HOLIDAY_NAME",
                "HOURLY_TRAFFIC",
            )
            .filter(col("HOUR").between(7, 22))
            .na.fill({"HOLIDAY_NAME": "No Holiday"})
        )

        past_final.limit(5).toPandas()
  - cellType: MARKDOWN
    cellId: fc95f91d-66a0-4fd9-8cba-62eab0e5f1c1 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: "### Generate store info with a empty hourly traffic for the next four weeks for forecasting "
  - cellType: CODE
    cellId: 83e78355-99a5-43a8-b928-2988fcb869a4 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        ## Create a column that has the next 672 hours (28 days) in date time format.
        df_date = session.range(672).select(dateadd("HOUR", "ID", current_date()).as_("DATE"))

        df_date = df_date.with_column("HOUR",F.hour(df_date["DATE"]))

        df_date = df_date.select(F.to_date(df_date["DATE"]).as_("DATE"),'HOUR').filter(col('HOUR').between(7,22))

         ## Cross join to make sure each store gets a value for the next 4 weeks
        df_store = session.table('HOURLY_TRAFFIC').select(col('STORE_ID').cast("string").alias("STORE_ID"),col('COLLEGE_TOWN').cast("string").alias("COLLEGE_TOWN")).distinct()
        stores = df_date.cross_join(df_store)
  - cellType: CODE
    cellId: e47aa85a-6354-4c5a-bd42-6c9ed9c9961b # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        ## Add in Calendar Information to create the final future table
        future_cal = session.table('CALENDAR_INFO')\
            .select('CALENDAR_DATE',\
                    'CALENDAR_WEEK_DAY_NBR',\
                    'CALENDAR_MTH_DAY_NBR',\
                    'CALENDAR_MTH',\
                    'CALENDAR_YEAR',\
                    'HOLIDAY_NAME').\
            filter((F.col('CALENDAR_DATE') >= F.current_date())\
                                                  & (F.col('CALENDAR_DATE')  <= F.current_date()+28))

        future_cal = future_cal.na.fill({"HOLIDAY_NAME": 'No Holiday'})

        ## Join store info and calendar data
        future_df = stores.join(future_cal, stores.col("DATE") == future_cal.col("CALENDAR_DATE"),"right")
        future_df = future_df.drop('CALENDAR_DATE')

        future_df = future_df.withColumn("DATE_HOUR", F.to_timestamp(F.dateadd("hour",col("HOUR"),col("DATE"))))
        future_df = future_df.drop('DATE')

        future_df = future_df.withColumn('HOURLY_TRAFFIC', F.lit(0))

        future_df = future_df.select('DATE_HOUR',\
                        'HOUR',\
                        'STORE_ID',\
                        'COLLEGE_TOWN',\
                        'CALENDAR_WEEK_DAY_NBR',\
                        'CALENDAR_MTH_DAY_NBR',\
                        'CALENDAR_MTH',\
                        'CALENDAR_YEAR',\
                        'HOLIDAY_NAME',\
                        'HOURLY_TRAFFIC')

        future_df.limit(5).toPandas()
  - cellType: MARKDOWN
    cellId: 4181865b-7185-435e-9a48-1da087b18b42 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: "### Save final features to a new table "
  - cellType: CODE
    cellId: 87a068fb-1ad0-49ee-baa1-b62a43dd27f6 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: Uniondf
    config:
      source: |2-
         ## Union the historical and future tables together
        unionDF = past_final.union(future_df)
            
        ## Write the final features table to Snowflake 
        unionDF.write.saveAsTable('MODEL_FEATURES', mode='overwrite', create_temp_table=False)
  - cellType: MARKDOWN
    cellId: 87a3369c-dbc9-4cd6-8f4c-229336e735bf # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: "## Training & batch forecasts using a UDTFs. "
  - cellType: CODE
    cellId: 39b2eb13-8400-4374-9ec8-6abb9826c3c9 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        schema = StructType([
             StructField("DATE", DateType()),
            StructField("HOUR_OF_DAY", IntegerType()),
            StructField("HOURLY_FORECAST", FloatType())  
        ])
  - cellType: CODE
    cellId: 844e379f-3c90-4c7d-ad3e-7c4da9bde50c # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        @udtf(output_schema = schema,
             input_types = [TimestampType(), IntegerType(),IntegerType(),FloatType(),StringType(),StringType(),StringType(),StringType()],
             name = "store_forecast", is_permanent=True, stage_location="@pymodels",
             packages=["pandas","xgboost == 1.5.0"], replace=True, session=session)
        class forecast:
            def __init__(self):
                self.date_hour=[]
                self.from_hour=[]
                self.COLLEGE_TOWN=[]
                self.DAYOFWEEK=[]
                self.MONTH=[]
                self.YEAR=[]
                self.HOLIDAY_NAME=[]
                self.HOURLY_TRAFFIC=[]
            
            def process(self, date_hour, HOURLY_TRAFFIC, from_hour, COLLEGE_TOWN, DAYOFWEEK, MONTH, YEAR, HOLIDAY_NAME):
                self.date_hour.append(date_hour)
                self.HOURLY_TRAFFIC.append(HOURLY_TRAFFIC)
                self.from_hour.append(from_hour)
                self.COLLEGE_TOWN.append(COLLEGE_TOWN)
                self.DAYOFWEEK.append(DAYOFWEEK)
                self.MONTH.append(MONTH)
                self.YEAR.append(YEAR)
                self.HOLIDAY_NAME.append(HOLIDAY_NAME)
            
            def end_partition(self):
                df = pd.DataFrame(zip(self.date_hour, 
                                      self.HOURLY_TRAFFIC, 
                                      self.from_hour, 
                                      self.COLLEGE_TOWN, 
                                      self.DAYOFWEEK, 
                                      self.MONTH, 
                                      self.YEAR, 
                                      self.HOLIDAY_NAME), 
                                  columns = ['DATE_HOUR','HOURLY_TRAFFIC','HOUR','COLLEGE_TOWN','CALENDAR_WEEK_DAY_NBR',
                                             'CALENDAR_MTH','CALENDAR_YEAR','HOLIDAY_NAME'])
                
                # set the time column as our index 
                df2 = df.set_index('DATE_HOUR') 
                df2.index = pd.to_datetime(df2.index)

                 # Converting features to categories for get_dummies
                df2['CALENDAR_WEEK_DAY_NBR'] = df2['CALENDAR_WEEK_DAY_NBR'].astype("category")
                df2['CALENDAR_MTH'] = df2['CALENDAR_MTH'].astype("category")
                df2['CALENDAR_YEAR'] = df2['CALENDAR_YEAR'].astype("category")
                df2['HOUR'] = df2['HOUR'].astype("category")
                df2['HOLIDAY_NAME'] = df2['HOLIDAY_NAME'].astype("category")
                df2['COLLEGE_TOWN'] = df2['COLLEGE_TOWN'].astype("category")

                #Use get_dummies for categorical features
                final = pd.get_dummies(data=df2, columns=['HOLIDAY_NAME', 
                                                          'COLLEGE_TOWN','CALENDAR_WEEK_DAY_NBR','CALENDAR_MTH','CALENDAR_YEAR','HOUR'])
               
                #do the train & forecast split
                today = date.today()
                yesterday = today - timedelta(days = 1)
                fourweek = today + timedelta(days = 28)
                tomorrow = today + timedelta(days = 1)

                train = final[(final.index >= pd.to_datetime('16-Jun-2018')) & (final.index <= pd.to_datetime(yesterday))]
                forecast = final[(final.index >= pd.to_datetime(tomorrow)) & (final.index <=pd.to_datetime(fourweek))]

                X_train = train.drop('HOURLY_TRAFFIC', axis = 1)
                y_train = train['HOURLY_TRAFFIC']

                X_forecast = forecast.drop('HOURLY_TRAFFIC', axis = 1)
                
                #Use XGBoost regressor model
                model = xgb.XGBRegressor(n_estimators=200,n_jobs=1)
                model.fit(X_train, y_train,
                        verbose=False) 
                
                forecast['PREDICTION'] = model.predict(X_forecast)

                hours = forecast.index.hour
                forecast = pd.concat([forecast, pd.DataFrame(hours, index=forecast.index)], axis = 1)
                forecast = forecast[["DATE_HOUR","PREDICTION"]]
                forecast = forecast.sort_index()
                forecast.loc[forecast['PREDICTION'] < 0,'PREDICTION']=0
                forecast['DATE'] = forecast.index.date
                
                # output prediction
                for idx, row in forecast.iterrows():
                    DATE = row['DATE']
                    DATE_HOUR = row['DATE_HOUR']
                    PREDICTION = row['PREDICTION']
                    yield DATE, DATE_HOUR, PREDICTION
  - cellType: MARKDOWN
    cellId: 12649a0a-68b8-46bf-a17b-464402601b30 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: "## Call the UDTF on Snowpark Optimized WH to run models in prallel and get forecast"
  - cellType: CODE
    cellId: da6be9a9-e7a0-48e9-9238-ba02cebfc250 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        df = session.table("MODEL_FEATURES")
        store_forecast = F.table_function("store_forecast")
  - cellType: CODE
    cellId: def90cc9-2153-431b-ba66-618c7a5c01aa # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        forecast = df.select(
            df["STORE_ID"],
            (
                store_forecast(
                    df["TIME_POINTS"],
                    df["HOURLY_TRAFFIC"],
                    df["HOUR"],
                    df["COLLEGE_TOWN"].cast(FloatType()),#.alias('COLLEGE_TOWN'),
                    df["CALENDAR_WEEK_DAY_NBR"],
                    df["CALENDAR_MTH_DAY_NBR"],
                    df["CALENDAR_YEAR"],
                    df["HOLIDAY_NAME"],
                ).over(partition_by=df["STORE_ID"])
            ),
        )
  - cellType: CODE
    cellId: a85a69b1-16ae-4e6b-ac8c-755a8366caca # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: forecast.limit(10).show()
  - cellType: CODE
    cellId: 4e5361a2-8d23-4967-a30a-1e8a64adc0f2 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        # takes abt 7 minutes (on an x-small warehouse)
        forecast.write.saveAsTable('FOUR_WEEK_FORECAST', mode='overwrite', create_temp_table=False)
  - cellType: SQL
    cellId: 7eee2a68-c1dd-4e71-ac8d-3224c27bbd91 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: Cast
    config:
      source: select * from "PC_HEX_DB"."PUBLIC"."FOUR_WEEK_FORECAST"
      dataFrameCell: false
      dataConnectionId: null
      resultVariableName: cast
      enableCache: false
      runOnLoad: false
      runOnSchedule: false
      useRichDisplay: true
      sqlCellOutputType: PANDAS
      useQueryMode: false
      castDecimals: true
      useNativeDates: false
      tableDisplayConfig:
        pageSize: 12
        hideIcons: false
        hideIndex: false
        defaultSortColumn: HOUR_OF_DAY
        defaultSortDirection: DESC
        conditionalFormatting: null
        filters: null
        columnProperties:
          - originalName: STORE_ID
            renameTo: null
            size: 150
            wrapText: null
            displayFormat: null
          - originalName: DATE
            renameTo: null
            size: 156
            wrapText: null
            displayFormat: null
          - originalName: HOUR_OF_DAY
            renameTo: null
            size: 152
            wrapText: null
            displayFormat: null
          - originalName: HOURLY_FORECAST
            renameTo: null
            size: 180
            wrapText: null
            displayFormat: null
        columnOrdering: null
  - cellType: CODE
    cellId: 3dbf3169-a36c-4626-bb7f-be26343da7a7 # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: null
    config:
      source: |-
        date_hour = np.array(list(zip(cast['DATE'].astype('str'), cast["HOUR_OF_DAY"].astype('str'))))
        full_time = []
        for date, hour in date_hour:
            full_time.append(pd.to_datetime(f"{date} {hour}:00"))

        cast['time'] = full_time
  - cellType: CHART
    cellId: c0519ce0-ae14-4a96-bb5c-1f1b7371c52c # DO NOT CHANGE - Hex uses this to match up cells when reimporting the file, and detect any changes to existing cells
    cellLabel: Predicted hourly forecast
    config:
      height: null
      vegaSpec:
        $schema: https://vega.github.io/schema/vega-lite/v5.json
        layer:
          - data:
              name: layer00
            mark:
              type: line
              clip: true
              tooltip: true
            encoding:
              x:
                field: time
                type: temporal
                timeUnit: yearmonthdatehours
              y:
                field: HOURLY_FORECAST
                type: quantitative
        resolve:
          scale: {}
        datasets:
          layer00:
            - name: dummy
              value: 0
      selectedLayerIndex: 0
      metadata:
        byLayer:
          - selectedDataFrameVariableName: cast
      defaultInputTimezone: UTC
appLayout:
  fullWidth: false
  visibleMetadataFields:
    - NAME
    - DESCRIPTION
    - AUTHOR
    - LAST_EDITED
    - LAST_RUN
    - CATEGORIES
    - STATUS
    - TABLE_OF_CONTENTS
  rows:
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: false
              hideOutput: false
              type: CELL
              cellId: 8c1e28c3-6ec3-4bea-b22f-5d96a0f9b17f
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 33
          elements:
            - showSource: false
              hideOutput: false
              type: CELL
              cellId: e6374490-65a5-418d-ac74-17cef3aa33cc
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: false
              hideOutput: false
              type: CELL
              cellId: 335a8b19-266b-48d1-952f-9f22d3d4fca4
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: true
              hideOutput: true
              type: CELL
              cellId: 217f5e6f-3970-4649-a076-acc27331a318
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: false
              hideOutput: false
              type: CELL
              cellId: b0bd33a7-de2a-40e6-a885-d87a455fa4ad
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: false
              hideOutput: false
              type: CELL
              cellId: ce1586fe-d74f-44c8-823e-b2f5018d910f
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: true
              hideOutput: true
              type: CELL
              cellId: e997513e-b6c9-49d1-94ce-95fefee0f3db
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: true
              hideOutput: true
              type: CELL
              cellId: 7d795c83-94a8-40ba-b428-b7e7669d4b19
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: false
              hideOutput: false
              type: CELL
              cellId: 2612402f-92f1-40e2-89e4-2742c8057969
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: false
              hideOutput: false
              type: CELL
              cellId: b58a2e94-3a6c-42a1-8839-de66baba3853
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: true
              hideOutput: false
              type: CELL
              cellId: 04b09b5a-b1ff-490e-bc45-215467a73ea6
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: true
              hideOutput: false
              type: CELL
              cellId: 6b4c64db-b1cf-4418-b6c0-f1d0a9bc5aff
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: false
              hideOutput: false
              type: CELL
              cellId: fc4bccb9-4017-48d9-be83-f74ea6d969b7
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: false
              hideOutput: false
              type: CELL
              cellId: 880a38f1-5608-45b3-8e81-d22312eb15df
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: true
              hideOutput: false
              type: CELL
              cellId: ff8be39e-399f-465a-8ff9-064c082e143d
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: false
              hideOutput: false
              type: CELL
              cellId: fc95f91d-66a0-4fd9-8cba-62eab0e5f1c1
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: true
              hideOutput: true
              type: CELL
              cellId: 83e78355-99a5-43a8-b928-2988fcb869a4
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: true
              hideOutput: false
              type: CELL
              cellId: e47aa85a-6354-4c5a-bd42-6c9ed9c9961b
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: false
              hideOutput: false
              type: CELL
              cellId: 4181865b-7185-435e-9a48-1da087b18b42
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: true
              hideOutput: true
              type: CELL
              cellId: 87a068fb-1ad0-49ee-baa1-b62a43dd27f6
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: false
              hideOutput: false
              type: CELL
              cellId: 87a3369c-dbc9-4cd6-8f4c-229336e735bf
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: true
              hideOutput: false
              type: CELL
              cellId: 844e379f-3c90-4c7d-ad3e-7c4da9bde50c
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: false
              hideOutput: false
              type: CELL
              cellId: 12649a0a-68b8-46bf-a17b-464402601b30
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: true
              hideOutput: true
              type: CELL
              cellId: da6be9a9-e7a0-48e9-9238-ba02cebfc250
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: true
              hideOutput: true
              type: CELL
              cellId: def90cc9-2153-431b-ba66-618c7a5c01aa
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: true
              hideOutput: false
              type: CELL
              cellId: a85a69b1-16ae-4e6b-ac8c-755a8366caca
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: true
              hideOutput: true
              type: CELL
              cellId: 4e5361a2-8d23-4967-a30a-1e8a64adc0f2
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: true
              hideOutput: false
              type: CELL
              cellId: 7eee2a68-c1dd-4e71-ac8d-3224c27bbd91
              height: null
              showLabel: true
    - columns:
        - start: 0
          end: 120
          elements:
            - showSource: false
              hideOutput: false
              type: CELL
              cellId: c0519ce0-ae14-4a96-bb5c-1f1b7371c52c
              height: null
              showLabel: true
