challenge_excercise_solution.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "d519415f-6a1e-4d04-918f-6f0eb0c96399",
   "metadata": {
    "collapsed": false,
    "name": "_0_HEADLINE"
   },
   "source": [
    "# Show your Datascience Skills! :brain:\n",
    "\n",
    "Use your datascience expertise to:\n",
    "* Explore the existing Data\n",
    "* Develop and Register new Features in the **Feature Store**\n",
    "* Train and Register a Machine Learning Model in the **Model Registry**\n",
    "* Create Scores for passengers with unknown survival status\n",
    "\n",
    "This notebook comes with a range of incomplete code-cells.  \n",
    "Whenever there is something to do for you, you will see this marker:\n",
    "\n",
    "## :book: EXCERCISE\n",
    "\n",
    "This means the next - **and only the next** - code-cell needs your expertise.  ____\n",
    "\n",
    "To make it even easier, every cell that needs to be changed can be found in navigation on the right.  \n",
    "They start with **___ EXCERCISE ___** followed by a number."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1409aa3d-3e66-43e9-b832-61253bcc14ee",
   "metadata": {
    "collapsed": false,
    "name": "_1_IMPORTS1"
   },
   "source": [
    "# Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4049a079-6d27-4d75-bb4d-ea763ae20052",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "_1_IMPORTS2"
   },
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'plotly'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[2], line 20\u001b[0m\n\u001b[1;32m     18\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01mjson\u001b[39;00m\n\u001b[1;32m     19\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01mstreamlit\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m \u001b[38;5;21;01mst\u001b[39;00m\n\u001b[0;32m---> 20\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01mplotly\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mexpress\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m \u001b[38;5;21;01mpx\u001b[39;00m\n\u001b[1;32m     21\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01mpandas\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m \u001b[38;5;21;01mpd\u001b[39;00m\n\u001b[1;32m     22\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01mnumpy\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m \u001b[38;5;21;01mnp\u001b[39;00m\n",
      "\u001b[0;31mModuleNotFoundError\u001b[0m: No module named 'plotly'"
     ]
    }
   ],
   "source": [
    "# Snowpark Imports\n",
    "from snowflake.snowpark.context import get_active_session\n",
    "import snowflake.snowpark.functions as F\n",
    "from snowflake.snowpark.functions import col, lit, when\n",
    "\n",
    "# Snowpark ML\n",
    "from snowflake.ml.modeling.impute import SimpleImputer\n",
    "from snowflake.ml.modeling.preprocessing import OrdinalEncoder, OneHotEncoder, Normalizer\n",
    "from snowflake.ml.modeling.pipeline import Pipeline\n",
    "from snowflake.ml.modeling.xgboost import XGBClassifier\n",
    "from snowflake.ml.modeling.model_selection import GridSearchCV\n",
    "from snowflake.ml.registry import Registry\n",
    "from snowflake.ml.feature_store import FeatureStore, FeatureView, CreationMode\n",
    "from snowflake.cortex import Complete\n",
    "\n",
    "# Other Imports\n",
    "import matplotlib.pyplot as plt\n",
    "import json\n",
    "import streamlit as st\n",
    "import plotly.express as px\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "333c75c1-0fa8-4bf0-a954-5c21237cd5d4",
   "metadata": {
    "collapsed": false,
    "name": "_2_SETUP1"
   },
   "source": [
    "# 2 - Set Up Environment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "496c9046-0510-44d5-96ef-03c354504ee2",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "_2_SETUP2"
   },
   "outputs": [],
   "source": [
    "# Retrieve the Session\n",
    "session = get_active_session()\n",
    "\n",
    "# Set context\n",
    "session.use_schema('CC_KAGGLE_TITANIC_CHALLENGE.DEVELOPMENT')\n",
    "\n",
    "# Create reference to Feature Store\n",
    "fs = FeatureStore(\n",
    "    session=session, \n",
    "    database=\"CC_KAGGLE_TITANIC_CHALLENGE\", \n",
    "    name=\"DEVELOPMENT\", \n",
    "    default_warehouse=\"COMPUTE_WH\",\n",
    "    creation_mode=CreationMode.CREATE_IF_NOT_EXIST\n",
    ")\n",
    "\n",
    "# Create reference to Model Registry\n",
    "model_registry = Registry(\n",
    "    session=session, \n",
    "    database_name=session.get_current_database(), \n",
    "    schema_name=session.get_current_schema()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "058c878f-f2eb-43bf-8a82-961a6b9a744e",
   "metadata": {
    "collapsed": false,
    "name": "_3_EXPLORATION1"
   },
   "source": [
    "# 3 - Data Exploration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06890236-8044-4d9a-9fc8-fa70252203a5",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "_3_VIEW_EXISTING_FEATURES"
   },
   "outputs": [],
   "source": [
    "entity = fs.get_entity(name=\"PASSENGER\")\n",
    "kaggle_fv = fs.get_feature_view('PASSENGER_KAGGLE_FEATURES','V1')\n",
    "custom_fv = fs.get_feature_view('PASSENGER_CUSTOM_FEATURES','V1')\n",
    "\n",
    "# Retrieve existing features for your data\n",
    "titanic_df = fs.retrieve_feature_values(session.table('PASSENGER'), [kaggle_fv,custom_fv])\n",
    "titanic_df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be6bc797-460c-4b15-a151-70616fdc8304",
   "metadata": {
    "collapsed": false,
    "name": "_3_AGE1"
   },
   "source": [
    "## :book: EXCERCISE  \n",
    "**Feature Name:**  \n",
    "AGE\n",
    "\n",
    "**Description:**  \n",
    "Visualize the AGE Variable in relation to SURVIVED in a meaningful way.\n",
    "\n",
    "**Tips:**\n",
    "* *A [Distribution Plot](https://matplotlib.org/stable/api/_as_gen/matplotlib.pyplot.hist.html) could be useful.*\n",
    "* *For your convenience I already provided code to generate the required datasets*\n",
    "    * *passenger_age_survived*\n",
    "    * *passenger_age_died*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "02466cd8-0cb5-4231-be5b-32677ff40bc6",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "___EXCERCISE___1"
   },
   "outputs": [],
   "source": [
    "# Retrieve age values for passengers who survived or died\n",
    "passenger_age_survived = titanic_df.filter(col('SURVIVED')==1).dropna().select('AGE').to_pandas()['AGE'].tolist()\n",
    "passenger_age_died = titanic_df.filter(col('SURVIVED')==0).dropna().select('AGE').to_pandas()['AGE'].tolist()\n",
    "\n",
    "\n",
    "# Plotting the histogram\n",
    "plt.figure(figsize=(6, 2))\n",
    "plt.hist([passenger_age_survived, passenger_age_died], \n",
    "         bins=10, stacked=True, color=['g', 'r'], label=['SURVIVED', 'DEAD'])\n",
    "plt.title('Age Histogram by Survival')\n",
    "plt.xlabel('Age (Years)')\n",
    "plt.ylabel('# of Passengers')\n",
    "plt.legend()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84adf20b-219c-48a0-b476-227da4d5e0a8",
   "metadata": {
    "collapsed": false,
    "name": "_4_FEATURE_ENGINEERING"
   },
   "source": [
    "# 4 - Feature Engineering"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "def09a65-e8d6-4592-96d7-625845db8461",
   "metadata": {
    "collapsed": false,
    "name": "_4_IS_ALONE1"
   },
   "source": [
    "## :book: EXCERCISE \n",
    "**Feature Name:**  \n",
    "IS_ALONE\n",
    "\n",
    "**Description:**  \n",
    "Add a new binary feature IS_ALONE that is either 1 or 0.\n",
    "\n",
    "**Tips:**\n",
    "* *The feature FAM_SIZE tells you the number of people in the family (1 means the person is alone).*\n",
    "* *You can use the [iff-function](https://docs.snowflake.com/en/developer-guide/snowpark/reference/python/latest/snowpark/api/snowflake.snowpark.functions.iff) for this.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "01f5c39a-549e-4630-940e-24047186f419",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "___EXCERCISE___2"
   },
   "outputs": [],
   "source": [
    "titanic_df = titanic_df.with_column('IS_ALONE', F.iff(col('FAM_SIZE')==1,1,0))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "406e5e0d-59cd-4604-b473-aa29b5c4adb6",
   "metadata": {
    "collapsed": false,
    "name": "_4_AGE_GROUP1"
   },
   "source": [
    "## :book: EXCERCISE \n",
    "**Feature Name:**  \n",
    "AGE_GROUP\n",
    "\n",
    "**Description:**  \n",
    "Add a new variable to assign passengers to different age groups.  \n",
    "If the AGE of a passenger is missing, fill it with the most frequent category.\n",
    "\n",
    "**Tips:**\n",
    "* *Use the insight from the average AGE to identify which category to use when the AGE value is missing*\n",
    "* *You can use the [when-function](https://docs.snowflake.com/en/developer-guide/snowpark/reference/python/latest/snowpark/api/snowflake.snowpark.functions.when) for this.*\n",
    "* *For your convenience I already provide the conditions that you can utilize*\n",
    "* *use **.otherwise()** to fill values when AGE is missing* "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dcfc9623-e8e8-4689-871d-6b756943b285",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "___EXCERCISE___3"
   },
   "outputs": [],
   "source": [
    "print('Average Age of a passenger is:',titanic_df.select(F.round(F.avg('AGE'),1).as_('AGE')).collect()[0]['AGE'])\n",
    "\n",
    "condition_child    = (col('AGE') >  0) & (col('AGE') <= 12)\n",
    "condition_teenager = (col('AGE') > 12) & (col('AGE') <= 18)\n",
    "condition_adult    = (col('AGE') > 18) & (col('AGE') <= 50)\n",
    "condition_senior   = (col('AGE') > 50)\n",
    "condition_missing  = (col('AGE').is_null())\n",
    "\n",
    "titanic_df = titanic_df.with_column(\n",
    "    'AGE_GROUP', \n",
    "    when(condition_child,\"CHILD\")\n",
    "    .when(condition_teenager,\"TEENAGER\")\n",
    "    .when(condition_adult,\"ADULT\")\n",
    "    .when(condition_senior,\"SENIOR\")\n",
    "    .when(condition_missing, 'ADULT'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63df870b-74cd-48d5-873d-5f6a0aa5d4d5",
   "metadata": {
    "collapsed": false,
    "name": "_4_FEAT_DESC"
   },
   "source": [
    "## :book: EXCERCISE \n",
    "**Description:**  \n",
    "We are lazy again and don't want to write our own feature descriptions.  \n",
    "Adjust the prompt to generate a feature description for the columns **IS_ALONE** and **AGE_GROUP** that you created earlier.\n",
    "\n",
    "**Tips:**\n",
    "* *Keep it simple! The prompt in general works if the columns are in the dataframe.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a37112e-c8a3-4a73-b17d-1b837f50f64e",
   "metadata": {
    "codeCollapsed": false,
    "collapsed": false,
    "language": "python",
    "name": "___EXCERCISE___4"
   },
   "outputs": [],
   "source": [
    "llm = 'llama3-70b'\n",
    "\n",
    "prompt = f\"\"\"\n",
    "You are provided with a SQL Query that derives features from existing columns.\n",
    "Describe the features IS_ALONE and AGE_GROUP.\n",
    "The descriptions will be stored in a feature store, so make sure to return a JSON where the feature name is the key and the description is the value.\n",
    "{titanic_df.queries['queries'][0]}\n",
    "\"\"\"\n",
    "\n",
    "llm_response = Complete(llm, prompt)\n",
    "feature_descriptions = json.loads(llm_response.split('```')[1])\n",
    "for key in feature_descriptions:\n",
    "    feature_descriptions[key] = feature_descriptions[key].replace(\"'\", '')\n",
    "feature_descriptions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98693f3f-35aa-4e3e-b652-35cd3ad9833f",
   "metadata": {
    "collapsed": false,
    "name": "_4_FEAT_VIEW"
   },
   "source": [
    "## :book: EXCERCISE \n",
    "**Feature View Name:**  \n",
    "PASSENGER_CUSTOM_FEATURES_2\n",
    "\n",
    "**Description:**  \n",
    "Add a new feature view to the Feature Store called PASSENGER_CUSTOM_FEATURES_2\n",
    "\n",
    "**Tips:**\n",
    "* *You already have access to the **entity** variable.*\n",
    "* *Make sure to only add columns to this feature view that **you** created.*\n",
    "* *Make sure to include the **PASSENGER_ID** variable*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "533e251d-0007-46ba-9e47-acacacc4ff5c",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "___EXCERCISE___5"
   },
   "outputs": [],
   "source": [
    "# Create Feature View with Custom Features\n",
    "participant_fv = FeatureView(\n",
    "    name=\"PASSENGER_CUSTOM_FEATURES_PARTICIPANT\", \n",
    "    entities=[entity],\n",
    "    feature_df=titanic_df['PASSENGER_ID','AGE_GROUP','IS_ALONE'],\n",
    "    refresh_freq=\"1 minute\",\n",
    "    desc=\"My awesome new features\")\n",
    "\n",
    "# Add descriptions for some features\n",
    "participant_fv = participant_fv.attach_feature_desc(feature_descriptions)\n",
    "\n",
    "participant_fv = fs.register_feature_view(\n",
    "    feature_view=participant_fv, \n",
    "    version=\"V1\", \n",
    "    block=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "659269fa-ba59-470d-aba2-79048d42c558",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "_4_TRAIN_TEST_SPLIT"
   },
   "outputs": [],
   "source": [
    "spine_df = session.table('PASSENGER')\n",
    "\n",
    "spine_df_train = spine_df.filter(col('SURVIVED').is_not_null())\n",
    "print(f'Train dataset has {spine_df_train.count()} passengers.')\n",
    "spine_df_train.show(3)\n",
    "\n",
    "spine_df_test = spine_df.filter(col('SURVIVED').is_null())\n",
    "print(f'Test dataset has {spine_df_test.count()} passengers.')\n",
    "spine_df_test.show(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd6b5927-cef9-469e-bfef-dbedb8cbcb39",
   "metadata": {
    "collapsed": false,
    "name": "_4_GENERATE_TRAIN_DATASET"
   },
   "source": [
    "## :book: EXCERCISE \n",
    "**Description:**  \n",
    "Generate your Training Dataset.  \n",
    "\n",
    "**Tips:**\n",
    "* *You already have access to the **entity** variable.*\n",
    "* *Don't forget to utilize all feature views: kaggle_fv, custom_fv and participant_fv*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bfd3a110-54eb-4aa0-8bbf-281239ed919d",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "___EXCERCISE___6"
   },
   "outputs": [],
   "source": [
    "# Generate the training dataset by retrieving the features\n",
    "training_dataset = fs.generate_dataset(\n",
    "    name=\"TITANIC_TRAINING_DATASET\",\n",
    "    spine_df=spine_df_train,\n",
    "    features=[kaggle_fv,custom_fv,participant_fv],\n",
    "    spine_label_cols=[\"SURVIVED\"],\n",
    "    desc=\"Training Data to train model to predict whether a passenger survived.\"\n",
    ")\n",
    "\n",
    "# Retrieve a Snowpark DataFrame from the registered Dataset\n",
    "training_dataset_df = training_dataset.read.to_snowpark_dataframe().cache_result()\n",
    "training_dataset_df.show(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "137ff3dc-0429-47a9-a420-d6d9f8f45d6f",
   "metadata": {
    "collapsed": false,
    "name": "_5_MODELLING"
   },
   "source": [
    "# 5 - Modelling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "38a42bb9-ce39-4efa-a0e0-50891b7050af",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "_5_SWITCH_COMPUTE1"
   },
   "outputs": [],
   "source": [
    "# Switch to a larger warehouse to speed up training\n",
    "session.use_warehouse('TRAIN_WH')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d68e868-f779-4285-9eea-95f9d41f8ccd",
   "metadata": {
    "collapsed": false,
    "name": "___EXCERCISE___7"
   },
   "source": [
    "## :book: EXCERCISE \n",
    "**Description:**  \n",
    "Adjust the preprocessing pipeline to include your newly created columns IS_ALONE and AGE_GROUP.\n",
    "\n",
    "**Tips:**\n",
    "* *AGE_GROUP is an ordinal variable. Make sure you configure an [OrdinalEncoder](https://docs.snowflake.com/en/developer-guide/snowpark-ml/reference/latest/api/modeling/snowflake.ml.modeling.preprocessing.OrdinalEncoder).*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49e7a1bb-c356-42df-aad2-92eccdf81d03",
   "metadata": {
    "language": "python",
    "name": "_5_TRAIN_MODEL"
   },
   "outputs": [],
   "source": [
    "# DROP unused variables\n",
    "# PASSENGER_ID -> just an artificial ID with no predictive value\n",
    "# NAME -> people won't survive just because of their name and we extracted the title already\n",
    "# CABIN -> too many missing values\n",
    "# TICKET -> doesn't contain valuable information in its current form since it's a unique value per customer\n",
    "training_dataset_df = training_dataset_df.drop(['PASSENGER_ID','NAME','CABIN','TICKET'])\n",
    "\n",
    "# Impute Age by mean\n",
    "si_age =  SimpleImputer(\n",
    "    input_cols=['AGE','FARE'], \n",
    "    output_cols=['AGE_IMP','FARE_IMP'],\n",
    "    strategy='mean',\n",
    "    drop_input_cols=True\n",
    ")\n",
    "\n",
    "# ***************************************\n",
    "# ** DEFINE YOUR ORDINAL-ENCODER here  **\n",
    "# ** vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv  **\n",
    "# ***************************************\n",
    "# Define categories for the OrdinalEncoder\n",
    "categories = {\n",
    "        \"AGE_GROUP\": np.array([\"CHILD\", \"TEENAGER\", \"ADULT\", \"SENIOR\"]),\n",
    "    }\n",
    "\n",
    "oe = OrdinalEncoder(\n",
    "    input_cols=[\"AGE_GROUP\"], \n",
    "    output_cols=[\"AGE_GROUP_OE\"], \n",
    "    categories=categories,\n",
    "    drop_input_cols=True\n",
    ")\n",
    "# ***************************************\n",
    "# ** ɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅɅ  **\n",
    "# ** DEFINE YOUR ORDINAL-ENCODER here  **\n",
    "# ***************************************\n",
    "\n",
    "# Normalize Fare and Age\n",
    "norm = Normalizer(\n",
    "    input_cols=['AGE_IMP','FARE_IMP'],\n",
    "    output_cols=['AGE_IMP_NORM','FARE_IMP_NORM'],\n",
    "    drop_input_cols=True\n",
    ")\n",
    "\n",
    "# One-Hot-Encoding of categorical features\n",
    "ohe = OneHotEncoder(\n",
    "    input_cols=['SEX','EMBARKED','TITLE','FAM_SIZE_CATEGORY'], \n",
    "    output_cols=['SEX','EMBARKED','TITLE','FAM_SIZE_CATEGORY'],\n",
    "    drop_input_cols=True\n",
    ")\n",
    "\n",
    "# Define the XGBoost model (incl. Hyperparameter Tuning)\n",
    "label_cols = ['SURVIVED']\n",
    "output_cols = ['SURVIVED_PREDICTION']\n",
    "\n",
    "grid_search = GridSearchCV(\n",
    "    estimator=XGBClassifier(random_state=42),\n",
    "    param_grid={\n",
    "        'n_estimators':[10, 50, 100],\n",
    "        'max_depth': [2,4,8],\n",
    "        'learning_rate':[.01, .03, .1],\n",
    "    },\n",
    "    n_jobs = -1,\n",
    "    scoring=\"accuracy\",\n",
    "    label_cols=label_cols,\n",
    "    output_cols=output_cols\n",
    ")\n",
    "\n",
    "# Build the pipeline\n",
    "model_pipeline = Pipeline(\n",
    "    steps=[\n",
    "        (\"IMPUTE\",si_age),\n",
    "        (\"ORDINAL_ENCODE\",oe),\n",
    "        (\"NORMALIZE\",norm),\n",
    "        (\"ONE_HOT_ENCODE\",ohe),\n",
    "        (\"GRIDSEARCH_XGBOOST\",grid_search)\n",
    "    ]\n",
    ")\n",
    "\n",
    "# Fit the pipeline to the training data\n",
    "fitted_pipeline = model_pipeline.fit(training_dataset_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5bd8209-6e75-4ecd-aa73-39a9cbdda16e",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "_5_SWITCH_COMPUTE2"
   },
   "outputs": [],
   "source": [
    "# Switch back to smaller warehouse to save ressources\n",
    "session.use_warehouse('COMPUTE_WH')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cabd2960-a911-47f2-9a3c-28ca5a29d477",
   "metadata": {
    "collapsed": false,
    "name": "_6_MODEL_EVALUATION"
   },
   "source": [
    "# 6 - Model Evaluation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67fb64d9-bf79-4c8e-b28a-ad6d820461d3",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "_6_FEATURE_IMPORTANCE"
   },
   "outputs": [],
   "source": [
    "model_object = fitted_pipeline.to_sklearn().named_steps['GRIDSEARCH_XGBOOST']\n",
    "\n",
    "# Get the Feature Importance\n",
    "feature_importance = pd.DataFrame(\n",
    "    zip(model_object.best_estimator_.feature_names_in_, model_object.best_estimator_.feature_importances_),\n",
    "    columns=['FEATURE_NAME','IMPORTANCE']\n",
    ").sort_values('IMPORTANCE', ascending=False)\n",
    "\n",
    "fig = px.bar(feature_importance, x='FEATURE_NAME', y='IMPORTANCE')\n",
    "st.plotly_chart(fig)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da094c3b-796a-4cf5-adb1-a1e653c29aed",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "_6_PLOT_PARAMETERS"
   },
   "outputs": [],
   "source": [
    "# Build a dataframe for the gridsearch results\n",
    "gs_results = model_object.cv_results_\n",
    "n_estimators_val = []\n",
    "learning_rate_val = []\n",
    "max_depth_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",
    "    max_depth_val.append(param_dict[\"max_depth\"])\n",
    "accuracy_val = gs_results[\"mean_test_score\"]\n",
    "\n",
    "gs_results_df = pd.DataFrame(data={\n",
    "    \"n_estimators\":n_estimators_val,\n",
    "    \"learning_rate\":learning_rate_val,\n",
    "    \"max_depth\":max_depth_val,\n",
    "    \"accuracy\":accuracy_val})\n",
    "\n",
    "print(f'Number of Models: {len(gs_results_df)}')\n",
    "print('Best Parameter Configuration:')\n",
    "model_object.best_params_\n",
    "print(f'Accuracy of best Model: {model_object.best_score_}')\n",
    "\n",
    "# Create a 3D scatter plot to visualize impact of parameters on accuracy\n",
    "fig = px.scatter_3d(gs_results_df, x='learning_rate', y='n_estimators', z='max_depth', color='accuracy',\n",
    "                    labels={'accuracy': 'accuracy'})\n",
    "\n",
    "# Update the layout to increase the size of the chart\n",
    "fig.update_layout(\n",
    "    width=1000,  # Set the desired width\n",
    "    height=800   # Set the desired height\n",
    ")\n",
    "\n",
    "# Display the chart\n",
    "st.plotly_chart(fig)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e949e216-35c8-4a12-8ce5-8f984ef2ca28",
   "metadata": {
    "collapsed": false,
    "name": "_7_REGISTER_MODEL1"
   },
   "source": [
    "# 7 - Register Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "158ffce2-3916-4505-bf4b-691e76ecee70",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "_7_REGISTER_MODEL2"
   },
   "outputs": [],
   "source": [
    "# Register new model version\n",
    "registered_model = model_registry.log_model(\n",
    "    fitted_pipeline,\n",
    "    model_name=\"TITANIC_SURVIVAL_MODEL\",\n",
    "    comment=\"Model trained using GridsearchCV in Snowpark to predict survival of Titanic passengers.\",\n",
    "    metrics={\"accuracy\": model_object.best_score_},\n",
    "    conda_dependencies=['xgboost'],\n",
    "    version_name='PARTICIPANT_VERSION'\n",
    ")\n",
    "\n",
    "# View available models\n",
    "model_registry.show_models()\n",
    "\n",
    "# View available versions\n",
    "model_registry.get_model('TITANIC_SURVIVAL_MODEL').show_versions()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afc03526-019b-4a74-b68c-b5f00a7ba22f",
   "metadata": {
    "collapsed": false,
    "name": "_8_SCORING"
   },
   "source": [
    "# 8 - Scoring Passengers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a745ee4-66f7-45cb-b852-550d36df421e",
   "metadata": {
    "collapsed": false,
    "name": "___EXCERCISE___8"
   },
   "source": [
    "## :book: EXCERCISE \n",
    "**Description:**  \n",
    "Generate your Test Dataset.  \n",
    "\n",
    "**Tips:**\n",
    "* *You already have access to the **entity** variable.*\n",
    "* *Don't forget to utilize all feature views: kaggle_fv, custom_fv and participant_fv*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4582065-7f15-4fd8-ba87-f5a17536f8dd",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "_8_GENERATE_TEST_DATA_W_FEATURES"
   },
   "outputs": [],
   "source": [
    "# Retrieving features for the test data\n",
    "test_dataset_df = fs.retrieve_feature_values(\n",
    "    spine_df=spine_df_test, \n",
    "    features=[kaggle_fv,custom_fv,participant_fv], \n",
    "    exclude_columns=['SURVIVED']\n",
    ")\n",
    "test_dataset_df.show(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78df827e-6a04-47ea-b6e7-a23a6d81eebf",
   "metadata": {
    "codeCollapsed": false,
    "collapsed": false,
    "language": "python",
    "name": "_8_PREDICT_SURVIVAL"
   },
   "outputs": [],
   "source": [
    "# Create and persist predictions from registered model given the retrieved features\n",
    "predictions = registered_model.run(test_dataset_df, function_name='predict')\n",
    "predictions.write.save_as_table('TITANIC_TEST_PREDICTIONS', mode='overwrite')\n",
    "\n",
    "# \n",
    "predictions = session.table('TITANIC_TEST_PREDICTIONS')\n",
    "predictions.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74532147-63cc-4b40-8d3b-0c32df4a42ca",
   "metadata": {
    "collapsed": false,
    "name": "_9_LEADERBOARD"
   },
   "source": [
    "# 9 - Score your results!\n",
    "Kaggle expects a CSV file with two columns:  \n",
    "* PassengerId\n",
    "* Survived\n",
    "\n",
    "After offloading the data as CSV-file, you can download it via Snowflake's UI.\n",
    "\n",
    "If you don't have a Kaggle Account, you can also evaluate your performance with the following call:  \n",
    "**session.call('calculate_challenge_score', <path-to-your-csv-file>)**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "db3b27df-b23e-40b0-a093-d712a0af4c3e",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "_9_EXPORT_CSV"
   },
   "outputs": [],
   "source": [
    "# Transform data into format expected by Kaggle\n",
    "kaggle_submission = predictions.select('PASSENGER_ID','SURVIVED_PREDICTION')\n",
    "kaggle_submission = kaggle_submission.with_column_renamed(col('PASSENGER_ID'),'\"PassengerId\"')\n",
    "kaggle_submission = kaggle_submission.with_column_renamed(col('SURVIVED_PREDICTION'),'\"Survived\"')\n",
    "kaggle_submission.show()\n",
    "\n",
    "# Export Predictions to submission.csv\n",
    "kaggle_submission.write.csv(\n",
    "    '@KAGGLE_SUBMISSION/submission_participant.csv', \n",
    "    header=True, \n",
    "    single=True, \n",
    "    format_type_options={\"COMPRESSION\": \"NONE\"},\n",
    "    overwrite=True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf9a6cc4-f2e0-424e-9684-82e8532b0628",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "_9_CALCULATE_SCORE"
   },
   "outputs": [],
   "source": [
    "session.call('calculate_challenge_score', '@KAGGLE_SUBMISSION/submission_participant.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab5d352d-8ee3-471f-bb61-fbea730b4edd",
   "metadata": {
    "collapsed": false,
    "name": "_9_COMPARE_OLD_VS_NEW_MODEL"
   },
   "source": [
    "## :book: EXCERCISE \n",
    "**Description:**  \n",
    "Finally compare your model with the model from the demo.  \n",
    "Were you able to improve the model accuracy?\n",
    "\n",
    "**Tips:**\n",
    "* *You can easily retrieve saved metrics from your models by using **get_metric('accuracy')**.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9dc1c7bb-f193-4038-a59c-05219dd7f4c4",
   "metadata": {
    "language": "python",
    "name": "___EXCERCISE___9"
   },
   "outputs": [],
   "source": [
    "demo_version_accuracy = model_registry.get_model('TITANIC_SURVIVAL_MODEL').version('DEMO_VERSION').get_metric('accuracy')\n",
    "participant_version_accuracy = model_registry.get_model('TITANIC_SURVIVAL_MODEL').version('PARTICIPANT_VERSION').get_metric('accuracy')\n",
    "\n",
    "print(f'Demo Version Accuracy: {demo_version_accuracy}')\n",
    "print(f'Participant Version Accuracy: {participant_version_accuracy}')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py38_env",
   "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.8.17"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}