ForCustomer/End-to-End Data Science using Snowpark - Intermediate Path.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# End-to-End Data Science using Snowflake Hands-on-Lab\n",
    "\n",
    "<img src=\"assets/End-to-end_demo.png\" width=\"800\" />\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Background Information\n",
    "\n",
    "Tasty Bytes is one of the largest food truck networks in the world with localized menu options spread across 30 major cities in 15 countries. **Tasty Bytes is aiming to achieve 25% YoY sales growth over 5 years.**\n",
    "\n",
    "As Tasty Bytes Data Scientists, we have been asked to support this goal by helping our food truck drivers more intelligently pick where to park for shifts. \n",
    "\n",
    "**We want to direct our trucks to locations that are expected to have the highest sales on a given shift.\n",
    "This will maximize our daily revenue across our fleet of trucks.**\n",
    "\n",
    "To provide this insight, we will use historical shift sales at each location to build a model. This data has been made available to us in Snowflake.Our model will provide the predicted sales at each location for the upcoming shift.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"assets/problem_overview.png\" width=\"800\" />\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"assets/snowpark_101.png\" width=\"800\" />\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import Packages\n",
    "\n",
    "Just like the Python packages we are importing, we will import the Snowpark modules that we need.\n",
    "**Value**: Snowflake modules provide efficient ways to work with data and functions in Snowflake.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import Packages\n",
    "import pandas as pd\n",
    "import plotly.express as px\n",
    "import plotly.io as pio\n",
    "import sys\n",
    "import cachetools\n",
    "\n",
    "# Streamlit\n",
    "import streamlit as st\n",
    "\n",
    "# Import Snowflake modules\n",
    "from snowflake.snowpark import Session\n",
    "import snowflake.snowpark.functions as F\n",
    "import snowflake.snowpark.types as T\n",
    "from snowflake.snowpark import Window"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Need to update snowflake-ml-python package to version 1.2.2 to reflect PuPr Model Registry syntax\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect to Snowflake\n",
    "\n",
    "Our assigned HOL Snowflake role, `<ROLEXXX>`, can access the data in your assigned `<SCHEMAXXX>` of the HOL database. We will use your assigned `<WHXXX>` warehouse that has been created as a dedicated compute for data science workloads.We will use these parameters and our Snowflake account credentials to connect to Snowflake and create a Snowpark session. \n",
    "\n",
    "**Value:** Secure and governed.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create your Snowflake data connection called \"HOL`xxx`\"\n",
    "\n",
    "Set your HOL connection variables. For instance, if you were assigned number 1, your data connection parameters would include: {\"user\":\"USER0\",\"role\":\"ROLE1\",\"schema\": \"SCHEMA1\", \"warehouse\": \"WH1\"}.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set your account, password, and user values\n",
    "account = \"XXX\"\n",
    "password = \"XXX\"\n",
    "user = \"USER0\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "number = user[4:]\n",
    "\n",
    "# Concatenating the number with the required connection parameters\n",
    "role = \"ROLE\" + number\n",
    "warehouse = \"WH\" + number\n",
    "schema = \"SCHEMA\" + number\n",
    "\n",
    "connection_parameters = {\n",
    "    \"account\": account,\n",
    "    \"user\": user,\n",
    "    \"password\": password,\n",
    "    \"role\": role,\n",
    "    \"warehouse\": warehouse,\n",
    "    \"database\": \"HOL\",\n",
    "    \"schema\": schema,\n",
    "}\n",
    "\n",
    "# Create Snowpark session\n",
    "session = Session.builder.configs(connection_parameters).create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Checking environment setting and versions associated with Snowpark connection\n",
    "from snowflake.snowpark.version import VERSION\n",
    "print(\"----------------------------------------\")\n",
    "snowflake_environment = session.sql('select current_warehouse(), current_database(), current_schema(), current_version()').collect()\n",
    "snowpark_version = VERSION\n",
    "print('Warehouse                   : {}'.format(snowflake_environment[0][0]))\n",
    "print('Database                    : {}'.format(snowflake_environment[0][1]))\n",
    "print('Schema                      : {}'.format(snowflake_environment[0][2]))\n",
    "print('Snowflake version           : {}'.format(snowflake_environment[0][3]))\n",
    "print('Snowpark for Python version : {}.{}.{}'.format(snowpark_version[0],snowpark_version[1],snowpark_version[2]))\n",
    "print(\"----------------------------------------\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 1 - Use Snowpark to access and prepare data for modeling\n",
    "\n",
    "<img src=\"assets/Part1.png\" width=\"800\" />\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Snowpark ML is the Python library and underlying infrastructure for end-to-end ML workflows in Snowflake, including components for model development and operations. With Snowpark ML, you can use familiar Python frameworks for preprocessing, feature engineering, and training. You can deploy and manage models entirely in Snowflake without any data movement, silos, or governance tradeoffs.\n",
    "\n",
    "<img src=\"assets/snowpark_ml.png\" width=\"800\" />\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Snowpark DataFrame\n",
    "\n",
    "Let's create a Snowpark DataFrame containing our shift sales data from the **shift_sales_v** view in our Snowflake account using the Snowpark session.table function. A DataFrame is a data structure that contains rows and columns, similar to a SQL table.\n",
    "\n",
    "**Value:** Familiar representation of data for Python users.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YOUR TURN\n",
    "\n",
    "Create a Snowflake dataframe called \"snowpark_df\" and then use the show command to see top 10 values\n",
    "Table name is your [database name].analytics.shift_sales_v\n",
    "\n",
    "TIP: Use the following link to find required code https://docs.snowflake.com/developer-guide/snowpark/reference/python/latest/index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert code to create Snowpark dataframe here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preview the Data\n",
    "\n",
    "With our Snowpark DataFrame defined, let’s use the .show() function to take a look at the first 10 rows.\n",
    "**Value:** Instant access to data.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Preview the data\n",
    "# Use the .show() function to look at the first 10 rows.\n",
    "# VALUE: instant access to data\n",
    "snowpark_df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Select, Filter, Sort\n",
    "\n",
    "Notice the Null values for \"shift_sales\". Let's look at a single location.To do this, we will make another Snowpark DataFrame, location_df, from the above DataFrame and we will:\n",
    "\n",
    "1. Select columns\n",
    "2. Filter to a single location ID\n",
    "3. Sort by date\n",
    "\n",
    "**Value**: Efficient transformation pipelines using Python syntax and chained logic.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YOUR TURN\n",
    "\n",
    "Create a Snowflake dataframe called location_df that only includes: date, shift, shift_sales, location_id, and city\n",
    "Then filter the dataframe for location_id = 1135\n",
    "Then order the dataframe by date and shift\n",
    "Visualize the first 10 records\n",
    "\n",
    "TIP: Use the following link to find required code https://docs.snowflake.com/developer-guide/snowpark/reference/python/latest/index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select\n",
    "location_df = ????\n",
    "\n",
    "# Filter\n",
    "location_df = ????\n",
    "\n",
    "# Sort\n",
    "location_df = ????\n",
    "\n",
    "# Display\n",
    "location_df.show(n=??)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that shift sales are populated 8 days prior to the latest date in the data. The **missing values** represent future dates that do not have shift sales yet.\n",
    "\n",
    "## Snowpark works in two main ways:\n",
    "\n",
    "1. Snowpark code translated and executed as SQL on Snowflake\n",
    "2. Python functions deployed in a secure sandbox in Snowflake\n",
    "\n",
    "<img src=\"assets/Snowpark_ML_API.png\" width=\"800\" />\n",
    "\n",
    "\n",
    "Here's the value of using Snowpark:\n",
    "\n",
    "<img src=\"assets/SnowparkValue.png\" width=\"800\" />\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explain the Query\n",
    "\n",
    "Let's look at what was executed in Snowflake to create our location_df DataFrame.\n",
    "\n",
    "The translated SQL query can be seen in the Snowsight interface under _Activity_ in the _Query History_ or directly in our notebook by using the explain() function. **Value:** Transparent execution and compute usage.\n",
    "\n",
    "\n",
    "\n",
    "<img src=\"assets/query_history.png\" width=\"800\" />\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "location_df.explain()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"assets/data_exploration.png\" width=\"800\" />\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Compare DataFrame Size\n",
    "\n",
    "Let's bring a sample of our Snowflake dataset to our Python environment in a pandas DataFrame using the to_pandas() function. We will compare how much memory is used for the pandas DataFrame compared to the Snowpark DataFrame. As we will see, no memory is used for the Snowpark DataFrame in our Python environment. All data in the Snowpark DataFrame remains on Snowflake.\n",
    "**Value:** No copies or movement of data when working with Snowpark DataFrames.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Bring 10,000 rows from Snowflake to pandas\n",
    "pandas_df = snowpark_df.limit(10000).to_pandas()\n",
    "\n",
    "# Get Snowpark DataFrame size\n",
    "snowpark_size = sys.getsizeof(snowpark_df) / (1024*1024)\n",
    "print(f\"Snowpark DataFrame Size (snowpark_df): {snowpark_size:.2f} MB\")\n",
    "\n",
    "# Get pandas DataFrame size\n",
    "pandas_size = sys.getsizeof(pandas_df) / (1024*1024)\n",
    "print(f\"Pandas DataFrame Size (pandas_df): {pandas_size:.2f} MB\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Exploration\n",
    "\n",
    "Here, we will use Snowpark to explore our data. A common pattern for exploration is to use Snowpark to manipulate our data and then bring an aggregate table to our Python environment for visualization.\n",
    "**Value:** - Native Snowflake performance and scale for aggregating large datasets. - Easy transfer of aggregate data to the client-side environment for visualization.\n",
    "As we explore our data, we will highlight what is being done in Snowflake and what we are transferring to our client-side environment (Python notebook environment) for visualization.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How many rows are in our data?\n",
    "\n",
    "This will give us an idea of how we might need to approach working with this data. Do we have enough data to build a meaningful model? What compute might be required? Will we need to sample the data?\n",
    "**What's happening where?:** Rows counted in Snowflake. No data transfer.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Use the .count() function\n",
    "snowpark_df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's calculate some descriptive statistics.\n",
    "\n",
    "We use the Snowpark describe() function to calculate summary statistics and then bring the aggregate results into a pandas DataFrame to visualize in a formatted table.\n",
    "**What's happening where?:** Summary statistics calculated in Snowflake. Transfer aggregate summary statistics for client-side visualization.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YOUR TURN\n",
    "\n",
    "Use the built-in Snowpark function to generate descriptive statistics for the Snowpark dataframe called \"snowpark_df\"\n",
    "\n",
    "TIP: Use the following link to find required code https://docs.snowflake.com/developer-guide/snowpark/reference/python/latest/index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use the Snowpark DataFrame .describe function. You need to need to visualize from a pandas DataFrame\n",
    "\n",
    "# insert code to use Snowpark .describe function here and visualize using pandas format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# What are the numeric columns?\n",
    "# Define Snowflake numeric types\n",
    "numeric_types = [T.DecimalType, T.DoubleType, T.FloatType, T.IntegerType, T.LongType]\n",
    "\n",
    "# Get numeric columns\n",
    "numeric_columns = [col.name for col in snowpark_df.schema.fields if type(col.datatype) in numeric_types]\n",
    "numeric_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# What are the categorical columns?\n",
    "# Define Snowflake categorical types\n",
    "categorical_types = [T.StringType]\n",
    "\n",
    "# Get categorical columns\n",
    "categorical_columns = [col.name for col in snowpark_df.schema.fields if type(col.datatype) in categorical_types]\n",
    "categorical_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# What are the average shift sales (USD) by city?\n",
    "# Group by city and average shift sales\n",
    "analysis_df = snowpark_df.group_by(\"city\").agg(F.mean(\"shift_sales\").alias(\"avg_shift_sales\"))\n",
    "\n",
    "# Sort by average shift sales\n",
    "analysis_df = analysis_df.sort(\"avg_shift_sales\", ascending=True)\n",
    "\n",
    "# Pull to pandas and plot\n",
    "analysis_df.to_pandas().plot.barh(x=\"CITY\", y=\"AVG_SHIFT_SALES\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"assets/feature_engineering.png\" width=\"800\" />\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Feature Engineering\n",
    "\n",
    "Now let's keep revelant columns and transform columns to create features needed for our prediction model.To make some of our features more useful, we will normalize them using standard preprocessing techniques, such as One-Hot Encoding and MinMaxScaling. With SnowparkML, you can use a standard sklearn-style API to execute fully distributed feature engineering preprocessing tasks on Snowflake compute, with zero data movement. Let's fit a scaler and encoder to our data, then use it to transform the data, producing new feature columns.\n",
    "\n",
    "\n",
    "**Value:** The Snowpark syntax makes pipelines easy to implement and understand. The syntax also allows for easy migration of Spark pipelines to Snowflake.\n",
    "\n",
    "\n",
    "**All transformations for feature engineering in this notebook will be executed on Snowflake compute.**\n",
    "\n",
    "Notice what we haven't had to do? No tuning, maintenance, or operational overhead. We just need a role, warehouse, and access to the data.\n",
    "**Value**: Near-zero maintenance. Focus on the work that brings value.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a Rolling Average Feature\n",
    "\n",
    "We will use a Snowflake window function to get a **rolling shift average by location** over time. Window functions allow us to aggregate on a \"moving\" group of rows.\n",
    "\n",
    "#### **Step 1. Create a Window**\n",
    "\n",
    "Our window will partition the data by location and shift. It will order rows by date. It will include all rows prior to the current date of the observation it is aggregating for.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YOUR TURN\n",
    "\n",
    "Use a rolling average in two steps. First, create a window_by_location_all_days by location_id and shift which is ordered by date. Then create an aggregate average location shift sales.\n",
    "\n",
    "TIP: Use the following link to find required code https://docs.snowflake.com/developer-guide/snowpark/reference/python/latest/index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "window_by_location_all_days = (\n",
    "    Window.partition_by(???, ???)\n",
    "    .order_by(???)\n",
    "    .rows_between(Window.UNBOUNDED_PRECEDING, Window.CURRENT_ROW - 1)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Step 2. Aggregate across the Window**\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "snowpark_df = snowpark_df.with_column(\n",
    "    ???, \n",
    "    F.avg(???).over(window_by_location_all_days)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Impute Missing Values\n",
    "\n",
    "The rolling average feature we just created is missing if there are no prior shift sales at that location. We will replace those missing values with 0.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "snowpark_df = snowpark_df.fillna(value=0, subset=[\"avg_location_shift_sales\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Leverage Snowpark ML Modeling API to create features\n",
    "\n",
    "Snowpark ML provides APIs to support each stage of an end-to-end machine learning development and deployment process and includes two key components: [Snowpark ML Modeling](https://docs.snowflake.com/en/developer-guide/snowpark-ml/snowpark-ml-modeling) and [Snowpark ML Ops](https://docs.snowflake.com/en/developer-guide/snowpark-ml/snowpark-ml-mlops-model-registry).\n",
    "\n",
    "[Snowpark ML Modeling](https://docs.snowflake.com/en/developer-guide/snowpark-ml/snowpark-ml-modeling) supports data preprocessing, feature engineering, and model training in Snowflake using popular machine learning frameworks, such as scikit-learn, xgboost, and lightgbm. This API also includes a preprocessing module that can use compute resources provided by a Snowpark-optimized warehouse to provide scalable data transformations.\n",
    "\n",
    "Snowpark ML Operations (MLOps), featuring the [Snowpark ML Model Registry](https://docs.snowflake.com/en/developer-guide/snowpark-ml/snowpark-ml-mlops-model-registry), complements the Snowpark ML Development API. The model registry allows secure deployment and management of models in Snowflake, and supports models trained both inside and outside of Snowflake.\n",
    "\n",
    "\n",
    "\n",
    "Here are a Snowpark Features commonly used for Data Engineering tasks:\n",
    "\n",
    "<img src=\"assets/Snowpark4DE.png\" width=\"800\" />\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YOUR TURN\n",
    "\n",
    "Scale up your assigned Snowflake compute warehouse.  Enter your warehouse name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert code to scale up Snowflake compute warehouse to size Large"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YOUR TURN \n",
    "\n",
    "**Use Snowpark ML API to normalize the data using the fit_scaler function on numeric columns**\n",
    "\n",
    "TIP: use help(snowmlpp.MinMaxScaler)\n",
    "\n",
    "Snowpark ML scaler (MinMaxScaler) is used to shrink data within the given range, usually of 0 to 1. It transforms data by scaling features to a given range. It scales the values to a specific value range without changing the shape of the original distribution. For the Tasty_Bytes data, use MinMaxScaler to normalize \"CITY_POPULATION\" into \"CITY_POPULATION_NORM\" with values between 0 and 1.\n",
    "\n",
    "\n",
    "**Use Snowpark ML API to perform one-hot encoding function on categorical columns**\n",
    "\n",
    "TIP: use help(snowmlpp.OrdinalEncoder)\n",
    "\n",
    "Snowpark ML ordinal encoding (OE) is used to improve model performance by providing more information to the model about categorical variables. # It can help to avoid the problem of ordinality, which can occur when a categorical variable has a natural ordering (e.g. “small”, “medium”, “large”).# For the Tasty_Bytes data, use OE to change \"SHIFT\" which is currently AM or PM into and integer representation of \"SHIFT_OHE\" is 1.0 or 0.0. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import Snowpark ML: Machine Learning Toolkit for Snowflake\n",
    "import snowflake.ml.modeling.preprocessing as snowmlpp\n",
    "\n",
    "# Snowpark ML scaler (MinMaxScaler) is used to shrink data within the given range, usually of 0 to 1. \n",
    "\n",
    "def fit_scaler(session, df):\n",
    "    mm_target_columns = [???]\n",
    "    mm_target_cols_out = [???]\n",
    "    snowml_mms = snowmlpp.MinMaxScaler(input_cols=mm_target_columns, output_cols=mm_target_cols_out)\n",
    "    snowml_mms.fit(df)\n",
    "    return snowml_mms\n",
    "\n",
    "# Snowpark ML ordinal encoding (OE) is used to improve model performance by providing more information to the model about categorical variables. \n",
    "\n",
    "def fit_oe(session, df):\n",
    "    oe_target_cols = [???]\n",
    "    oe_output_cols = [???]\n",
    "    snowml_oe = snowmlpp.OrdinalEncoder(input_cols=oe_target_cols, output_cols=oe_output_cols)\n",
    "    snowml_oe.fit(df)\n",
    "    return snowml_oe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run Snowpark ML preprocessing functions against our feature data\n",
    "\n",
    "# For the Tasty_Bytes data, use MinMaxScaler to normalize \"CITY_POPULATION\" into \"CITY_POPULATION_NORM\" with values between 0 and 1.\n",
    "snowml_mms = fit_scaler(session, snowpark_df)\n",
    "normed_df = snowml_mms.transform(snowpark_df)\n",
    "\n",
    "# For the Tasty_Bytes data, use OneHotEncoder to change \"SHIFT\" which is currently AM or PM into and integer representation of \"SHIFT_OHE_AM\" is 1 or 0 and \"SHIFT_OHE_PM\" is 1 or 0. \n",
    "snowml_oe = fit_oe(session, normed_df)\n",
    "oe_df = snowml_oe.transform(normed_df)\n",
    "oe_df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter to Historical Data\n",
    "\n",
    "Our data includes placeholders for future data with missing shift sales. The future data represents the next 7 days of shifts for all locations. The historical data has shift sales for all locations where a food truck parked during a shift. We will only use historical data when training our model and will filter out the dates where the shift_sales column is missing.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data Science best practice: Always perform data quality on your training set e.g. remove nulls or invalid cells as they are the biggest problem in a training set as they output high false positives\n",
    "\n",
    "# Specifically for Tasty_Bytes data, dates where \"shift_sales\" are null values reflect future dates where sales need to be predicted.\n",
    "# Filter out these future dates so these records will not be used in model training. \n",
    "historical_df = oe_df.filter(F.col(\"shift_sales\").is_not_null())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Persist Transformations\n",
    "\n",
    "If we want to save the changes we can either save it as a table, meaning the SQL generated by the DataFrame is executed and the result is stored in a table or as a view where the DataFrame SQL will be the definition of the view.\n",
    "**save_as_table** saves the result in a table, if **mode='overwrite'** then it will also replace the data that is in it.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YOUR TURN\n",
    "\n",
    "Specify a table name to save your Snowpark DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's select \n",
    "# enter your code to save historical.df as INPUT_DATA in database HOL and your assigned schema"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 2 - Use Snowpark to train a model\n",
    "\n",
    "# <img src=\"assets/Part2.png\" width=\"800\" />\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"assets/model_training.png\" width=\"600\" />\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Drop Columns\n",
    "\n",
    "Let's return to the original prepared table, with all cities listed, and drop columns that will not be used in the model.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "prepared_df = historical_df.drop(\"location_id\", \"city_population\", \"shift\", \"city\", \"date\")\n",
    "prepared_df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Build a simple XGBoost Regression Model on Snowflake\n",
    "\n",
    "We will now use our training data to train a linear regression model on Snowflake.Recall from above, the two main ways that Snowpark works:\n",
    "\n",
    "1. Snowpark code translated and executed as SQL on Snowflake\n",
    "2. Python functions deployed in a secure sandbox in Snowflake\n",
    "\n",
    "We will be leveraging the deployment of Python functions into Snowflake for training and model deployment.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YOUR TURN\n",
    "\n",
    "Define relevant LABEL_COLUMNS and OUTPUT_COLUMNS for your XGBoost Regressor model\n",
    "\n",
    "TIP: Look up code in https://docs.snowflake.com/en/developer-guide/snowpark-ml/snowpark-ml-modeling#constructing-a-model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Retrieve column names needed in the next code block to populate feature_column_names\n",
    "prepared_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's define relevant features needed for the prediction model.\n",
    "LABEL_COLUMNS = [???]\n",
    "OUTPUT_COLUMNS = [???]\n",
    "FEATURE_COLUMN_NAMES = [\"SHIFT_OE\", \"CITY_POPULATION_NORM\", \"MONTH\", \"DAY_OF_WEEK\",\"LATITUDE\",\"LONGITUDE\",\"AVG_LOCATION_SHIFT_SALES\"]\n",
    "\n",
    "input_df = prepared_df.select(*LABEL_COLUMNS, *FEATURE_COLUMN_NAMES)\n",
    "input_df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SnowparkML also includes metric calculations such as correlations, and more. We will use the SnowparkML correlation method on our input dataframe to identify any linearly correlated features to the output. We'll also use matplotlib to plot the resulting matrix. Notice that all of the correlation calculations are pushed down to Snowflake!\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from snowflake.ml.modeling.metrics.correlation import correlation\n",
    "corr_df = correlation(df=input_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Return only the correlation summary results to generate this heatmap\n",
    "sns.heatmap(corr_df, cmap=\"YlGnBu\", annot=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What's great about this, is that we are using a lot of Snowpark components under the hood- the dataframe API, SQL, Python stored procedures and more. But with the new SnowparkML API, data scientists can take advantage of all that Snowpark affords them, while using common, familiar APIs that match how they do their work today.\n",
    "\n",
    "Now that we have our feature data, let's actually fit an XGBoost model to our features to attempt to predict future sales. We'll fit several different models with different hyperparameters, and then show how we can use the Snowpark Model Registry to select our best-fit model.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Split the data into train and test sets\n",
    "train_df, test_df = input_df.random_split(weights=[0.9, 0.1], seed=98)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What's happening when you leverage Snowpark ML Modeling API?\n",
    "\n",
    "Let's run our training job using the SnowparkML Modeling API- this will push down our model training to run on Snowflake, and you'll notice that the type of the model object returend is a SnowparkML XGBClassifier- this has some benefits, but also is fully compatible with the standard sklearn/xgboost model objects.\n",
    "\n",
    "- The model.fit() function actually creates a temporary stored procedure in the background. This also means that the model training is a single-node operation. Be sure to use a Snowpark Optimized Warehouse if you need more memory. We are just using an XS Standard Virtual Warehouse here, which we created at the beginning of this quickstart.\n",
    "- The model.predict() function actually creates a temporary vectorized UDF in the background, which means the input DataFrame is batched as Pandas DataFrames and inference is parallelized across the batches of data. You can check the query history once you execute the following cell to check.\n",
    "\n",
    "The Snowpark ML Modeling API enables the use of popular Python ML frameworks, such as scikit-learn and XGBoost, for feature engineering and model training without the need to move data out of Snowflake. \n",
    "\n",
    "Benefits of Snowpark ML Modeling: \n",
    "- **Feature engineering and preprocessing:** Improve performance and scalability with distributed execution for common scikit-learn preprocessing functions. \n",
    "- **Model training:** Accelerate model training for scikit-learn, XGBoost and LightGBM models without the need to manually create stored procedures or user-defined functions (UDFs), and leverage distributed hyperparameter optimization (now in public preview).\n",
    "\n",
    "Behind the scenes, Snowpark ML parallelizes data processing operations by taking advantage of Snowflake’s scalable computing platform.\n",
    "\n",
    "<img src=\"assets/DistributedDataProcessing.png\" width=\"800\" />\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YOUR TURN\n",
    "\n",
    "Import and define all the relevant details to train an XGBoost Regressor model\n",
    "\n",
    "TIP: Look up code in https://docs.snowflake.com/en/developer-guide/snowpark-ml/snowpark-ml-modeling#constructing-a-model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from snowflake.ml.modeling.??? import ???\n",
    "# Define the XGBRegressor\n",
    "regressor = ???(\n",
    "    label_cols = ???,\n",
    "    input_cols = ???,\n",
    "    output_cols = ???\n",
    ")\n",
    "\n",
    "# Train\n",
    "regressor.fit(train_df)\n",
    "\n",
    "# Predict\n",
    "result = regressor.predict(test_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Just to illustrate, we can also pass in a Pandas DataFrame to Snowpark ML's model.predict()\n",
    "regressor.predict(test_df.to_pandas())\n",
    "# try \n",
    "regressor.predict(test_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's analyze the results using Snowpark ML's MAPE.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YOUR TURN\n",
    "\n",
    "Complete and run the mse (mean square error) for your XGBoost Regressor model results\n",
    "\n",
    "TIP: Look up code in https://docs.snowflake.com/en/developer-guide/snowpark-ml/snowpark-ml-modeling#constructing-a-model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use Snowpark ML metrics to calculate\n",
    "from snowflake.ml.modeling.metrics import mean_absolute_percentage_error, mean_squared_error\n",
    "\n",
    "# Predict\n",
    "results = regressor.predict(test_df)\n",
    "\n",
    "# Calculate MAPE\n",
    "mape = mean_absolute_percentage_error(df=results, y_true_col_names=LABEL_COLUMNS, y_pred_col_names=OUTPUT_COLUMNS)\n",
    "\n",
    "# Calculate MSE\n",
    "mse = ???\n",
    "\n",
    "results.select([*LABEL_COLUMNS, *OUTPUT_COLUMNS]).show()\n",
    "print(f'''Mean absolute percentage error: {mape}''')\n",
    "print(f'''Mean squared error: {mse}''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot actual vs predicted \n",
    "g = sns.relplot(data=results[\"SHIFT_SALES\", \"PRED_SHIFT_SALES\"].to_pandas().astype(\"float64\"), x=\"SHIFT_SALES\", y=\"PRED_SHIFT_SALES\", kind=\"scatter\")\n",
    "g.ax.axline((0,0), slope=1, color=\"r\")\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Snowpark ML's GridSearchCV()\n",
    "\n",
    "Now, let's use Snowpark ML's GridSearchCV() function to find optimal model parameters.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from snowflake.ml.modeling.model_selection import GridSearchCV\n",
    "\n",
    "grid_search = GridSearchCV(\n",
    "estimator=XGBRegressor(),\n",
    "param_grid={\n",
    "\"n_estimators\":[25, 50],\n",
    "\"learning_rate\":[0.4, 0.5],\n",
    "},\n",
    "n_jobs = -1,\n",
    "scoring=\"neg_mean_absolute_percentage_error\",\n",
    "input_cols=FEATURE_COLUMN_NAMES,\n",
    "label_cols=LABEL_COLUMNS,\n",
    "output_cols=OUTPUT_COLUMNS\n",
    ")\n",
    "\n",
    "\n",
    "# Train\n",
    "grid_search.fit(train_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's analyze the grid_search results\n",
    "gs_results = grid_search.to_sklearn().cv_results_\n",
    "n_estimators_val = []\n",
    "learning_rate_val = []\n",
    "for param_dict in gs_results[\"params\"]:\n",
    "    n_estimators_val.append(param_dict[\"n_estimators\"])\n",
    "    learning_rate_val.append(param_dict[\"learning_rate\"])\n",
    "mape_val = gs_results[\"mean_test_score\"]*-1\n",
    "\n",
    "gs_results_df = pd.DataFrame(data={\n",
    "    \"n_estimators\":n_estimators_val,\n",
    "    \"learning_rate\":learning_rate_val,\n",
    "    \"mape\":mape_val})\n",
    "\n",
    "sns.relplot(data=gs_results_df, x=\"learning_rate\", y=\"mape\", hue=\"n_estimators\", kind=\"line\")\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's save our optimal model and its metadata:\n",
    "optimal_model = grid_search.to_sklearn().best_estimator_\n",
    "optimal_n_estimators = grid_search.to_sklearn().best_estimator_.n_estimators\n",
    "optimal_learning_rate = grid_search.to_sklearn().best_estimator_.learning_rate\n",
    "\n",
    "\n",
    "optimal_mape = gs_results_df.loc[(gs_results_df['n_estimators']==optimal_n_estimators) &\n",
    "                        (gs_results_df['learning_rate']==optimal_learning_rate),'mape'].values[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Scale down your assigned Snowflake compute warehouse.**\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YOUR TURN\n",
    "\n",
    "Scale down your assigned Snowflake compute warehouse to XSmall."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Increase size of Snowflake compute warehouse to XSMALL\n",
    "# insert your code to scale down your assigned Snowflake compute warehouse to XSmall"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 3 - Use Snowpark for MLOps\n",
    "\n",
    "# <img src=\"assets/Part3.png\" width=\"800\" />\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now let's use Snowflake Model Registry (Snowpark ML Ops)\n",
    "\n",
    "Model Registry was created to support model management operations including model registration, versioning, metadata and audit trails. Integrated deployment infrastructure for batch inference is a critical ease-of-use feature. Users can deploy ML models for batch inference from the registry directly into a Snowflake Warehouse as a vectorized UDF, or as a service to a customer-specified Compute Pool in Snowpark Container Services.\n",
    "\n",
    "Snowflake's Model Registry supports SciKitLearn, XGBoost, Pytorch, Tensorflow and MLFlow (via the pyfunc interface) models.\n",
    "\n",
    "# <img src=\"assets/ModelRegistry.png\" width=\"800\" />\n",
    "\n",
    "\n",
    "Model Registry allows easy deployment of pre-trained open-source models from providers such as HuggingFace. See this [blog](https://www.snowflake.com/blog/accelerate-ml-workflow-python-snowpark-ml/) for more detailsor the [Snowflake Model Registry documentation](https://docs.snowflake.com/en/developer-guide/snowpark-ml/snowpark-ml-mlops-model-registry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create the Model Registry and register your model\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from snowflake.ml.registry import Registry\n",
    "\n",
    "native_registry = Registry(session, database_name=\"HOL\", schema_name=schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YOUR TURN\n",
    "\n",
    "Define your model to be registered into the Snowflake Model Registry. Suggest using \"SHIFT_SALES_PREDICTION\" and version \"VO\". "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get sample input data to pass into the registry logging function\n",
    "X = train_df.select(FEATURE_COLUMN_NAMES).limit(100)\n",
    "\n",
    "# Define model name\n",
    "model_name = ???\n",
    "\n",
    "# Let's first log the very first model we trained\n",
    "model_ver = native_registry.log_model(\n",
    "    model_name=model_name,\n",
    "    version_name=???,\n",
    "    model=regressor,\n",
    "    sample_input_data=X, # to provide the feature schema\n",
    ")\n",
    "# Add a description\n",
    "model_ver.comment = \"This is the initial model of the Shift Sales Price Prediction model.\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YOUR TURN\n",
    "\n",
    "Register the optimal model from the GridSearchCV output. Suggest using version \"V2\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now, let's log the optimal model from GridSearchCV\n",
    "model_ver2 = native_registry.log_model(\n",
    "    model_name=model_name,\n",
    "    version_name=???,\n",
    "    model=???,\n",
    "    sample_input_data=X, # to provide the feature schema\n",
    ")\n",
    "\n",
    "# Add evaluation metric\n",
    "model_ver2.set_metric(metric_name=???, value=optimal_mape)\n",
    "\n",
    "# Add a description\n",
    "model_ver2.comment = \"???\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's confirm model(s) that were added\n",
    "native_registry.get_model(model_name).show_versions()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can see what the default model is when we have multiple versions with the same model name:\n",
    "native_registry.get_model(model_name).default.version_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now we can use the default version model to perform inference.\n",
    "model_ver = native_registry.get_model(model_name).version('V0')\n",
    "result_sdf = model_ver.run(test_df, function_name=\"predict\")\n",
    "result_sdf.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## View Registered Models\n",
    "\n",
    "Let's look at the Snowflake Model Registry UI. It is a unified place in Snowsight UI where all models registered in the Model Registry can be found and explored. \n",
    "\n",
    "To navigate to the Model Registry, click on “AI & ML” in the left-nav, and select “Models”. This will display all available models in the Model Registry across all DBs/Schemas that your role has access to.\n",
    "\n",
    "Currently, Model Registry will display user-created models from Snowpark ML, Snowpark, or models sourced from external ML platforms that are registered in Snowflake. In the future, Model Registry will also support other model types such as Cortex ML, Doc AI, Cortex Fine Tuning etc. \n",
    "\n",
    "<img src=\"assets/ModelRegistered.png\" width=\"800\" />\n",
    "\n",
    "To explore the details of a model, click on the corresponding row in the listing. This will open the details page for the selected model as shown below. \n",
    "\n",
    "Key model details such as description, any tags that are applied to the model, and a table of all versions of the model are displayed. \n",
    "\n",
    "Model Version metadata such as metrics can be found here. This page also shows the available model methods for invocation along with the inputs and outputs of those functions.\n",
    "\n",
    "<img src=\"assets/Model.png\" width=\"800\" />\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that our model is built and deployed, let's see it in action! We will find the best place to park in Vancouver for tomorrow morning's shift.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YOUR TURN\n",
    "\n",
    "Create a Snowflake DataFrame with only SHIFT_SALES and SHIFT_OE with future values (.isNull) for the city \"Vancouver\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "date_tomorrow_df = oe_df.filter(\n",
    "    (F.col(???).isNull())\n",
    "    & (F.col(\"shift_oe\") == 1)\n",
    "    & (F.col(\"city\") == \"???\")\n",
    ")\n",
    "date_tomorrow_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result_sdf = regressor.predict(date_tomorrow_df)\n",
    "result_sdf.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Visualize on a Map\n",
    "\n",
    "The red and yellow areas indicate higher predicted sales locations and the green zones indicate lower predicted sales. We will use this insight to ensure that our drivers are parking at the high-value locations. Value: Updated predictions readily available to drive towards our corporate goals."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import streamlit as st\n",
    "import pandas as pd\n",
    "\n",
    "# Pull location predictions into a pandas DataFrame\n",
    "predictions_df = result_sdf.to_pandas()\n",
    "\n",
    "# Define thresholds for high, moderate, and low predicted sales\n",
    "high_threshold = 250\n",
    "moderate_threshold = 200\n",
    "\n",
    "# Create separate DataFrames for different predicted sales categories\n",
    "high_sales_df = predictions_df[predictions_df['PRED_SHIFT_SALES'] >= high_threshold]\n",
    "moderate_sales_df = predictions_df[(predictions_df['PRED_SHIFT_SALES'] < high_threshold) & (predictions_df['PRED_SHIFT_SALES'] >= moderate_threshold)]\n",
    "low_sales_df = predictions_df[predictions_df['PRED_SHIFT_SALES'] < moderate_threshold]\n",
    "\n",
    "# Visualize on a map\n",
    "st.map(high_sales_df)\n",
    "st.map(moderate_sales_df)\n",
    "st.map(low_sales_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 4 - Create a SiS application to use predicted outputs\n",
    "\n",
    "<img src=\"assets/part4.png\" width=\"800\" />\n",
    "\n",
    "Create a SiS application for local managers to identify where to place daily food trucks. \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create new SiS app. \n",
    "\n",
    "### Replace existing code with SiS_application.py code listed below.\n",
    "\n",
    "### Update schema variable in line 29. \n",
    "\n",
    "### Include required pydeck and snowflake-ml-python package. \n",
    "\n",
    "### Run the SiS application.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import Python packages\n",
    "\n",
    "import streamlit as st\n",
    "\n",
    "import pydeck as pdk\n",
    "\n",
    "import numpy as np\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Import Snowflake modules\n",
    "\n",
    "from snowflake.snowpark import Session\n",
    "\n",
    "import snowflake.snowpark.functions as F\n",
    "\n",
    "from snowflake.snowpark import Window\n",
    "\n",
    "from snowflake.snowpark.context import get_active_session\n",
    "\n",
    "from snowflake.ml.registry import Registry\n",
    "\n",
    "import snowflake.ml.modeling.preprocessing as snowmlpp\n",
    "\n",
    "\n",
    "# Set your schema variable\n",
    "schema = \"SCHEMA0\"\n",
    "\n",
    "\n",
    "# Set Streamlit page config\n",
    "\n",
    "st.set_page_config(\n",
    "\n",
    "    page_title=\"Streamlit App: Snowpark 101\", \n",
    "\n",
    "    page_icon=\":truck:\",\n",
    "\n",
    "    layout=\"wide\",\n",
    "\n",
    ")\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Add header and a subheader\n",
    "\n",
    "st.header(\"Predicted Shift Sales by Location\")\n",
    "\n",
    "st.subheader(\"Data-driven recommendations for food truck drivers.\")\n",
    "\n",
    " \n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Connect to Snowflake\n",
    "\n",
    "# session = init_connection()\n",
    "\n",
    "session = get_active_session()\n",
    "\n",
    " \n",
    "\n",
    "# Create input widgets for cities and shift\n",
    "\n",
    "with st.container():\n",
    "\n",
    "    col1, col2 = st.columns(2)\n",
    "\n",
    "    with col1:\n",
    "\n",
    "        # Drop down to select city\n",
    "\n",
    "        city = st.selectbox(\n",
    "\n",
    "            \"City:\",\n",
    "\n",
    "            session.table(\"HOL.\"+schema+\".SHIFT_SALES_V\")\n",
    "\n",
    "            .select(\"city\")\n",
    "\n",
    "            .distinct()\n",
    "\n",
    "            .sort(\"city\"),\n",
    "\n",
    "        )\n",
    "\n",
    " \n",
    "\n",
    "    with col2:\n",
    "\n",
    "        # Select AM/PM Shift\n",
    "\n",
    "        shift = st.radio(\"Shift:\", (\"AM\", \"PM\"), horizontal=True)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "    n_trucks = st.selectbox('How many food trucks would you like to schedule today?', np.arange(1,10))\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "    if n_trucks > 1:\n",
    "\n",
    "        range = st.slider('What is the minimum distance in kilometers between food trucks?', 0, 20, 1)\n",
    "\n",
    "        st.write('You are requesting a minimum distance of ', range, 'km')\n",
    "\n",
    "        st.write('Click **:blue[Update]** to get the ', n_trucks, ' highest predicted Shift_Sales food truck locations.')\n",
    "\n",
    "    else:\n",
    "\n",
    "        st.write('Click **:blue[Update]** to get one food truck location predicted to have the Shift_Sales')\n",
    "\n",
    "        \n",
    "\n",
    "# Get predictions for city and shift time\n",
    "\n",
    "def get_predictions(city, shift):\n",
    "\n",
    "    # Get data and filter by city and shift\n",
    "\n",
    "    snowpark_df = session.table(\n",
    "\n",
    "        \"HOL.\"+schema+\".SHIFT_SALES_V\"\n",
    "\n",
    "    ).filter((F.col(\"shift\") == shift) & (F.col(\"city\") == city))\n",
    "\n",
    " \n",
    "\n",
    "    # Get rolling average\n",
    "\n",
    "    window_by_location_all_days = (\n",
    "\n",
    "        Window.partition_by(\"location_id\")\n",
    "\n",
    "        .order_by(\"date\")\n",
    "\n",
    "        .rows_between(Window.UNBOUNDED_PRECEDING, Window.CURRENT_ROW - 1)\n",
    "\n",
    "    )\n",
    "\n",
    " \n",
    "\n",
    "    snowpark_df = snowpark_df.with_column(\n",
    "\n",
    "        \"avg_location_shift_sales\",\n",
    "\n",
    "        F.avg(\"shift_sales\").over(window_by_location_all_days),\n",
    "\n",
    "    ).cache_result()\n",
    "\n",
    " \n",
    "\n",
    "    # Get tomorrow's date\n",
    "\n",
    "    date_tomorrow = (\n",
    "\n",
    "        snowpark_df.filter(F.col(\"shift_sales\").is_null())\n",
    "\n",
    "        .select(F.min(\"date\"))\n",
    "\n",
    "        .collect()[0][0]\n",
    "\n",
    "    )\n",
    "\n",
    " \n",
    "\n",
    "    # Filter to tomorrow's date\n",
    "\n",
    "    snowpark_df = snowpark_df.filter(F.col(\"date\") == date_tomorrow)\n",
    "\n",
    " \n",
    "\n",
    "    # Impute\n",
    "\n",
    "    snowpark_df = snowpark_df.fillna(value=0, subset=[\"avg_location_shift_sales\"])\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "    for colname in snowpark_df.columns:\n",
    "\n",
    "        new_colname = str.upper(colname)\n",
    "\n",
    "        snowpark_df = snowpark_df.with_column_renamed(colname, new_colname)\n",
    "\n",
    " \n",
    "\n",
    "    # Encode\n",
    "\n",
    "    snowpark_df = snowpark_df.with_column(\"shift_oe\", F.iff(F.col(\"shift\") == \"AM\", 0, 1))\\\n",
    "    .with_column(\"shift_oe\", F.iff(F.col(\"shift\") == \"PM\", 1, 0))\n",
    "\n",
    "\n",
    "\n",
    "    # Scale\n",
    "\n",
    "    mm_target_columns = [\"CITY_POPULATION\"]\n",
    "\n",
    "    mm_target_cols_out = [\"CITY_POPULATION_NORM\"]\n",
    "\n",
    "    snowml_mms = snowmlpp.MinMaxScaler(input_cols=mm_target_columns, \n",
    "\n",
    "                                       output_cols=mm_target_cols_out)\n",
    "\n",
    "    snowml_mms.fit(snowpark_df)\n",
    "\n",
    "    snowpark_df = snowml_mms.transform(snowpark_df)\n",
    "\n",
    "    \n",
    "\n",
    "    # Get all features\n",
    "\n",
    "    feature_cols = [\"SHIFT_OE\", \n",
    "\n",
    "                    \"CITY_POPULATION_NORM\", \n",
    "\n",
    "                    \"MONTH\", \n",
    "\n",
    "                    \"DAY_OF_WEEK\",\n",
    "\n",
    "                    \"LATITUDE\",\n",
    "\n",
    "                    \"LONGITUDE\",\n",
    "\n",
    "                    \"AVG_LOCATION_SHIFT_SALES\",\n",
    "\n",
    "                    \"LOCATION_ID\"]\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "    snowpark_df = snowpark_df.select(feature_cols)\n",
    "\n",
    "\n",
    "\n",
    "    native_registry = Registry(session=session, database_name=\"HOL\", schema_name=schema)\n",
    "\n",
    "    model_ver = native_registry.get_model(\"SHIFT_SALES_PREDICTION\").version('v0')\n",
    "\n",
    "    result_sdf = model_ver.run(snowpark_df, function_name=\"predict\")\n",
    "\n",
    "    return result_sdf\n",
    "\n",
    "\n",
    "\n",
    "# Update predictions and plot when the \"Update\" button is clicked\n",
    "\n",
    "if st.button(\":blue[Update]\"):\n",
    "\n",
    "    # Get predictions\n",
    "\n",
    "    with st.spinner(\"Getting predictions...\"):\n",
    "\n",
    "        predictions_sdf = get_predictions(city, shift)\n",
    "\n",
    "        predictions = predictions_sdf.to_pandas()\n",
    "\n",
    " \n",
    "\n",
    "    # Plot on a map\n",
    "\n",
    "    st.subheader(\"Predicted Shift Sales for position\")\n",
    "\n",
    "    predictions[\"PRED_SHIFT_SALES\"].clip(0, inplace=True)\n",
    "\n",
    "    st.pydeck_chart(\n",
    "\n",
    "        pdk.Deck(\n",
    "\n",
    "            map_style=None,\n",
    "\n",
    "            initial_view_state=pdk.ViewState(\n",
    "\n",
    "                latitude=predictions[\"LATITUDE\"][0],\n",
    "\n",
    "                longitude=predictions[\"LONGITUDE\"][0],\n",
    "\n",
    "                zoom=11,\n",
    "\n",
    "                pitch=50,\n",
    "\n",
    "            ),\n",
    "\n",
    "            layers=[\n",
    "\n",
    "                pdk.Layer(\n",
    "\n",
    "                    \"HexagonLayer\",\n",
    "\n",
    "                    data=predictions,\n",
    "\n",
    "                    get_position=\"[LONGITUDE, LATITUDE]\",\n",
    "\n",
    "                    radius=200,\n",
    "\n",
    "                    elevation_scale=4,\n",
    "\n",
    "                    elevation_range=[0, 1000],\n",
    "\n",
    "                    pickable=True,\n",
    "\n",
    "                    extruded=True,\n",
    "\n",
    "                ),\n",
    "\n",
    "                pdk.Layer(\n",
    "\n",
    "                    \"ScatterplotLayer\",\n",
    "\n",
    "                    data=predictions,\n",
    "\n",
    "                    get_position=\"[LONGITUDE, LATITUDE]\",\n",
    "\n",
    "                    get_color=\"[200, 30, 0, 160]\",\n",
    "\n",
    "                    get_radius=200,\n",
    "\n",
    "                ),\n",
    "\n",
    "            ],\n",
    "\n",
    "        )\n",
    "\n",
    "    )\n",
    "\n",
    "    \n",
    "\n",
    "    max_x = predictions.loc[predictions[\"PRED_SHIFT_SALES\"].idxmax()]\n",
    "\n",
    "    st.write(\"Maximum Predicted Sales are expected at the following location:\", max_x)\n",
    "\n",
    "    #st.dataframe(predictions_sdf)\n",
    "\n",
    "    \n",
    "\n",
    "    location_id = max_x[\"LOCATION_ID\"]\n",
    "\n",
    "    lat = max_x[\"LATITUDE\"]\n",
    "\n",
    "    long = max_x[\"LONGITUDE\"]\n",
    "\n",
    "\n",
    "\n",
    "    st.subheader(\"The following chart is generated using the st_point and st_distance Snowflake Geospatial features\")\n",
    "\n",
    "\n",
    "\n",
    "    if n_trucks == 1:\n",
    "\n",
    "        st.write(\"Have your only food truck positioned at Location ID \", location_id, \" to maximize SHIFT_SALES\")\n",
    "\n",
    "    elif n_trucks > 1:\n",
    "\n",
    "        best_locations = [location_id]\n",
    "\n",
    "        available_locations_sdf = predictions_sdf\n",
    "\n",
    "    \n",
    "\n",
    "        st_distance = F.function('st_distance')\n",
    "\n",
    "        st_point = F.function('st_point')\n",
    "\n",
    "    \n",
    "\n",
    "        for truck_n in np.arange(0,n_trucks - 1):\n",
    "\n",
    "            available_locations_sdf = available_locations_sdf.with_column(\"DISTANCE_TO_TRUCK\", \n",
    "\n",
    "                                        st_distance(\n",
    "\n",
    "                                            st_point(F.lit(float(long)), F.lit(float(lat))),\n",
    "\n",
    "                                            st_point(F.col(\"LONGITUDE\"), F.col(\"LATITUDE\"))\n",
    "\n",
    "                                        )/1609\n",
    "\n",
    "                                       ).filter(F.col(\"DISTANCE_TO_TRUCK\") >= range/1.609).order_by(\"PRED_SHIFT_SALES\", ascending=False)\n",
    "\n",
    "            max_x = available_locations_sdf.limit(1).to_pandas()\n",
    "\n",
    "            try:\n",
    "\n",
    "                location_id = max_x[\"LOCATION_ID\"].iloc[0]\n",
    "\n",
    "                lat = max_x[\"LATITUDE\"].iloc[0]\n",
    "\n",
    "                long = max_x[\"LONGITUDE\"].iloc[0]\n",
    "\n",
    "            except:\n",
    "\n",
    "                break\n",
    "\n",
    "            best_locations.append(location_id)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "        selected_locations = predictions[predictions[\"LOCATION_ID\"].isin(best_locations)]\n",
    "\n",
    "        st.map(selected_locations)\n",
    "\n",
    "        st.dataframe(selected_locations)\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "hex_info": {
   "author": "Diana Shaw",
   "exported_date": "Thu Feb 29 2024 20:20:21 GMT+0000 (Coordinated Universal Time)",
   "project_id": "b4861126-fb2c-4f2e-a1db-f725fca1baba",
   "version": "draft"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}