UseCases/Shipping_Time_Prediction/Shipping_Time_Prediction_PY_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fc4b9f6c-0c0c-4180-8688-d09cdfbaa8dc",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Shipping Time Prediction Using Vantage InDB Analytic Functions\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d5706d3-8c1b-4d46-ac90-8eee3868279f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>This is a Beta Release of this notebook!</b></p>   \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>   \n",
    "This should be considered a Beta Release.  Every effort has been made to ensure it's functionality and completeness.  If you discover any issue with any part of this notebook, your suggestions will be reviewed and, if approved, merged in the next round of notebook commits.   \n",
    "<br>   \n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>How to submit a Review of this Notebook</b></p>   \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>   \n",
    "First, please read through and execute the notebook.   \n",
    "<br> \n",
    "If you think the notebook is fine as it is, great.  Please send us a quick email and let us know. When the notebook is taken out of beta, this paragraph will be removed and the Beta designation the index page will be removed.   \n",
    "<br> \n",
    "If you find something that you question or you think needs to be changed or if you think it's great as it is:   \n",
    "</p>       \n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>   \n",
    "  <li>Send us an email: <a href=\"mailto:ClearScapeAnalyticsNotebookReviews@Teradata.com?subject=Beta Notebook Review\">Click here.</a></li>   \n",
    "  <li>Please include the name of the notebook in email.</li>   \n",
    "  <li>Include a screen shot or a clear description of the section in the notebook you would like us to look at.</li>   \n",
    "  <li>Paste the screen shot or enter the description of the section into the email.</li>   \n",
    "  <li>Describe how you would change it.</li>   \n",
    "  <li>Send It!</li>   \n",
    "</ol>   \n",
    "</p>   \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If we have any follow-up questions, we'll reach out to you at the return email address. </p>   \n",
    "<hr style=\"height:4px;border:none;background-color:#00233C;\"> "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bfbcd9c9-a417-46c9-913f-4937369ecf47",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>eBay, as an online marketplace, faces the challenge of accurately estimating delivery dates for shipments from various sellers. The current estimation process, based on seller handling time and carrier transit time, often leads to inconsistent and inaccurate predictions. This results in customer dissatisfaction and potential erosion of trust in the platform. Customer satisfaction starts with the experience. However, in every customer experience there is risk of unknown or unexpected issues. Therefore, there is a need to develop a robust system that can reliably estimate delivery dates, accounting for handling time, transit time, and other relevant variables affecting the actual delivery timeframe. Luckily, Teradata Vantage and ClearScape Analytics provide the features to examine historical data, predictive modeling techniques, and machine learning algorithms to improve accuracy. The successful implementation of an improved delivery time estimation system will enhance customer satisfaction, increase buyer trust, boost sales, and improve seller engagement on the platform.</p>\n",
    "    \n",
    "   \n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Business Value</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Understand shipment process and what factors lead to inaccurate predictions.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Increase customer satisfaction.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Ensure timeliness and accurate scheduling.</li></p>\n",
    "    <p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Why Vantage?</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To build more effective ML and AI models, developers and data scientists need to look outside the box for data, tools, and techniques that can continuously enhance the accuracy, speed, and efficacy of their models. Unfortunately, most of the time, this creativity comes at a cost. Plus, combining different types of analytics and data into the development pipeline usually adds complexity, fragility, and difficulties with operationalizing the process.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Luckily, Teradata Vantage provides ClearScape Analytics functions which allow users to seamlessly combine a wide range of behavioral, text processing, statistical analysis, and advanced analytic functions with model training and deployment tools on the same platform.  This allows for rapid development, testing, and validation of new techniques at scale in near-real time so new, more accurate models can easily be deployed to production.<p>\n",
    "\n",
    "  \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> To address the problem in estimating delivery dates for eBay packages, we propose leveraging Teradata's in-database capabilities. By using Teradata's data cleaning and machine learning functionalities, we can develop a robust model to predict delivery dates. This involves collecting relevant data, cleaning it for accuracy, performing feature engineering, developing a predictive model,and validating and optimizing it. The implementation of this solution can lead to improved customer satisfaction, increased trust, higher sales, and enhanced seller engagement.</p>\n",
    "   \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5426d0ca-3471-4e50-9472-113ebf16dd19",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Connect to Vantage.</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eca611c2-906f-4adc-a7f0-dacf65576edb",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the section, we import the required libraries and set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24bbc783",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-31T07:42:10.698948Z",
     "start_time": "2023-07-31T07:42:06.990321Z"
    }
   },
   "outputs": [],
   "source": [
    "import getpass\n",
    "import time\n",
    "import pandas as pd\n",
    "import teradataml as tdml\n",
    "from teradataml import *\n",
    "\n",
    "import sqlalchemy\n",
    "from sqlalchemy import event\n",
    "import csv\n",
    "# from teradataml.dataframe.data_transfer import read_csv\n",
    "from teradatasqlalchemy.types import *\n",
    "import random\n",
    "from PIL import Image\n",
    "\n",
    "from teradataml import *\n",
    "configure.val_install_location = \"val\"\n",
    "\n",
    "import plotly.express as px\n",
    "import io\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "display.max_rows=5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9e793f3-bc05-4191-8b4a-ab4373eb95d1",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will be prompted to provide the password. We will enter the password, press the Enter key, and then use the down arrow to go to the next cell. Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65006e51-2f7d-4c88-a1fa-bb336b1a15bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../startup.ipynb\n",
    "eng = create_context(host = 'host.docker.internal', username='demo_user', password = password)\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b0e20f29-a223-4479-a4d6-f2b6ec349bb6",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Shipping_Time_Prediction_PY_SQL.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84961f42-47f7-4d55-b804-aae5cdd080dc",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. We have the option of either running the demo using foreign tables to access the data without using any storage on our environment or downloading the data to local storage, which may yield somewhat faster execution. However, we need to consider available storage. There are two statements in the following cell, and one is commented out. We may switch which mode we choose by changing the comment string.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a1319ca-0fe6-4a14-a59d-9020cf86c8a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call get_data('DEMO_ShippingTimePrediction_local');\"\n",
    " # Takes about 40 secs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a934c0a-ff68-4b45-8fa1-1106f91461e5",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Optional step – We should execute the below step only if we want to see the status of databases/tables created and space used.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ae82294-7180-446e-ba6c-33addf882f2d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de5541ee-0cf7-4b79-a4d7-95ad52b855b0",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Analyze the raw data set</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The dataset is shipping dataset with data containing 110,000 rows. A more detailed description of the features is already mentioned at the end.\n",
    "\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us start by creating a \"Virtual DataFrame\" that points directly to the dataset in Vantage. We then begin our analysis by checking the shape of the DataFrame and examining the data types of all its columns.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cfb465f6-e446-499d-bcb4-3e477520dca2",
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_df=DataFrame(in_schema('DEMO_ShipTimePred', 'Delivery_date_data'))\n",
    "raw_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2efa6801-c0d7-4ac3-9b7b-894029fd3ce0",
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24e21819-f53e-4c96-8331-dd4343b88c03",
   "metadata": {},
   "outputs": [],
   "source": [
    "conversions = raw_df.select(['record_number','b2c_c2c']).groupby('b2c_c2c').count()\n",
    "conversions=conversions.to_pandas(all_rows=True).reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82dcfcc8-bbc2-41ac-a107-170de97e680e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see that the aggregated data is available to us in teradataml dataframe. Let's visualize this data to better understand the Distribution values by the types of Channel. Vantage's Clearscape Analytics can easily integrate with 3rd party visualization tools like Tableau, PowerBI or many python modules available like plotly, seaborn etc. We can do all the calculations and pre-processing on Vantage and pass only the necessary information to visualization tools, this will not only make the calculation faster but also reduce the time due to less data movement between tools. We do the data transfer for this and the subsequent visualizations wherever necessary.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ec0146e-4b44-4093-b0b4-3856421f62e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Plotting Distribution by channel\n",
    "\n",
    "fig = px.bar(data_frame=conversions, x='b2c_c2c', y='count_record_number', color='b2c_c2c')\n",
    "\n",
    "fig.update_layout(title='Channel Distribution',\n",
    "                   xaxis_title='Channel',\n",
    "                   yaxis_title='Distributions')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6441badd-f69b-436b-9125-6f61a53b0c94",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above chart shows the distribution of the shipments based on the channel use B2C(Business to Customer) and C2C(Customer to Customer).\n",
    "\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can also try analyzing the shipments by Shipment Methods. Since the data is sample data for the purpose of this demo the shipment methods used are not specified and are using just numbers to categorize the shipment methods used and are depicted as shipment methods ids.\n",
    "</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27672f97-ffdf-40bf-b21e-a7a9d8a722ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Plotting Distribution by Shipment Method\n",
    "shipments=raw_df.select(['record_number','shipment_method_id']).groupby('shipment_method_id').count()\n",
    "figure = Figure(width=800, height=400, heading=\"Shipment Method Distribution\")\n",
    "\n",
    "plot = shipments.plot(\n",
    "    x=shipments.shipment_method_id,\n",
    "    y=shipments.count_record_number,\n",
    "    kind='bar',\n",
    "    xlabel='Shipment Method',\n",
    "    ylabel='Distributions',\n",
    "    color='blue',\n",
    "    figure=figure,\n",
    "    grid_linestyle='-',\n",
    "    grid_linewidth=0.5\n",
    ")\n",
    "\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a13f4ed-5113-4651-bb86-92060d62a924",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above chart shows the distribution of the shipments based on different shipment method. As seen in the chart most of the shipments are using the Shipment Method Id(0)\n",
    "\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can also try analyzing the shipments by Categories. Similar to the Shipment Methods the categories are not specified and are using just numbers to categorize the shipments. The categories are defined using numbers and are depicted as Category IDs. \n",
    "</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b98f5476-74f9-4c9a-a05b-72c75a70eba7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Plotting Distribution by category\n",
    "categories=raw_df.select(['record_number','category_id']).groupby('category_id').count()\n",
    "figure = Figure(width=800, height=400, heading=\"Category Distribution\")\n",
    "\n",
    "plot = categories.plot(\n",
    "    x=categories.category_id,\n",
    "    y=categories.count_record_number,\n",
    "    kind='bar',\n",
    "    xlabel='Category',\n",
    "    ylabel='Distributions',\n",
    "    color='blue',\n",
    "    figure=figure,\n",
    "    grid_linestyle='-',\n",
    "    grid_linewidth=0.5\n",
    ")\n",
    "\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c372cb1e-b524-4c58-a41c-8ed7e485b77f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above chart shows how the distribution of the shipments based on the categories. Most of the shipments are for categories with Category Ids between 0-5.\n",
    "\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Below we try to check the Shipment Fees for various shipments.\n",
    "</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "89e79c31-c1ac-4425-9e5f-7b834ac083e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.express as px\n",
    "# ShipFeesdf=df[['shipping_fee','package_size']].groupby('package_size').agg([\"min\", \"max\"])\n",
    "ShipFeesdf=raw_df.select(['shipping_fee','package_size']).groupby('package_size').max()\n",
    "ShipFeesdf_plot=ShipFeesdf.to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0527da7-41ce-4370-9072-07567dd32fa6",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = px.bar(ShipFeesdf_plot, x='package_size', y='max_shipping_fee')\n",
    "\n",
    "fig.update_layout(title='Shipping Fees Variation',\n",
    "                   xaxis_title='Package Size',\n",
    "                   yaxis_title='Shipping Fees')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4cef5692-e9a8-4138-8b6f-6c7a74dc68aa",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above chart shows how the maximum shipment fees based on the size of the package. As seen the fees is maximum for the largest package size.</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4adce4e8-56d6-4294-9259-2775e54795cc",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Data Preprocessing and Cleaning</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ebb3d03-7404-4736-b576-5da1122a3f51",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We create a smaller dataset to pre process and clean the data and use the same in predictions.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a20e4ae-993f-4ae1-8700-1ce275a69971",
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_df = raw_df.assign(days=raw_df.delivery_date -  raw_df.acceptance_scan_timestamp.cast(type_=DATE))\n",
    "raw_df=raw_df[raw_df.days >= 3] \n",
    "raw_df=raw_df[raw_df.days <= 6 ]\n",
    "window = raw_df.days.window(order_columns='record_number')\n",
    "raw_df = raw_df.assign(rn=window.row_number())\n",
    "raw_df = raw_df[raw_df.rn <= 5000]\n",
    "raw_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97a62675-2050-4f67-a56c-0fb80d4fa103",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Data Preprocessing:</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>New column 'distance' is added to the table which will store distance between the item location and the buyer location.</p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>The geospatial function <b>ST_SPHERICALDISTANCE</b> in Vantage is used to calculate the distance using the latitude and longitude columns of the item and buyer.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e31fa9c-c30e-4e10-b3cf-8ede881204d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "11efebdf-c690-423d-b694-ee124fad4d24",
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(raw_df,table_name = 'Delivery_Date_Data_new', schema_name = 'DEMO_ShipTimePred_db', if_exists= 'replace' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa87dcbd-3cd9-4ece-baca-ddc3ce889663",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-31T06:23:49.856645Z",
     "start_time": "2023-07-31T06:23:43.201506Z"
    }
   },
   "outputs": [],
   "source": [
    "qry='''ALTER TABLE DEMO_ShipTimePred_db.Delivery_Date_Data_new\n",
    "ADD distance FLOAT;'''\n",
    "\n",
    "execute_sql(qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72af992b-dae0-478f-94f8-780862922b81",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-31T06:24:06.581344Z",
     "start_time": "2023-07-31T06:23:49.858878Z"
    }
   },
   "outputs": [],
   "source": [
    "qry='''UPDATE DEMO_ShipTimePred_db.Delivery_Date_Data_new\n",
    "SET distance = NEW ST_Geometry('ST_Point', item_long, item_lat).ST_SPHERICALDISTANCE(NEW ST_Geometry('ST_Point', \n",
    "buyer_long, buyer_lat))/1000;'''\n",
    "\n",
    "execute_sql(qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4483cf6-abb6-4a64-8d6a-c1d96a25b887",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Checking and handling missing values:</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We create a new table with this available data so that we maintain the copy of the original data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42fad591-1d18-4295-9e6c-30cfb01d170b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-31T06:43:02.457075Z",
     "start_time": "2023-07-31T06:42:57.716270Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "qry='''CREATE multiset TABLE delivery_date_complete_dataset AS (\n",
    "        SELECT *\n",
    "        FROM DEMO_ShipTimePred_db.Delivery_Date_Data_new\n",
    "    ) WITH DATA PRIMARY INDEX (record_number);'''\n",
    "\n",
    "try:\n",
    "    execute_sql(qry)\n",
    "except:\n",
    "    db_drop_table('delivery_date_complete_dataset')\n",
    "    execute_sql(qry)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "baed0165-afd1-410c-9c10-2a0ffaf736ee",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Get Rows With Missing Values</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>TD_GetRowsWithMissingValues used on the table delivery_date_complete_dataset will select rows from the table where at least one of the first 19 columns has missing values.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "68d9e6cf-3c40-440a-ba11-bc0c8ffb9dbe",
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_dataset_df =  DataFrame('delivery_date_complete_dataset')\n",
    "obj = GetRowsWithMissingValues(data=complete_dataset_df,\n",
    "                                   target_columns='0:23')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4664607-e890-4abc-81d8-8c002f349b1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_dataset_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2875448-ff85-4688-8e10-3d186cc9040f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Replace Missing Values</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We create a reusable function to replace missing values for various columns which is used below to calculate missing values for declared_handling_days, weight, carrier_min_estimate and carrier_max_estimate. Below is the logic used for replacing missing values:</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>It calculates the average value (AvgVal) of a specified column (avgColumn) grouped by another column (groupCol) in the delivery_date_complete_dataset table. Only non-null values are considered, and the result is grouped by the specified column.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Updates the delivery_date_complete_dataset table by filling in missing values in the avgColumn with either the corresponding value from AverageData based on the matching groupCol, or with the overall average value if no match is found.</li>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7edd2430-eee4-446d-8bcc-507b5f8241fd",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-31T07:42:27.070406Z",
     "start_time": "2023-07-31T07:42:27.067048Z"
    }
   },
   "outputs": [],
   "source": [
    "def temp_col(col):\n",
    "    execute_sql(\"\"\"\n",
    "    ALTER TABLE delivery_date_complete_dataset\n",
    "    ADD \"{0}_varchar\" VARCHAR(50);\"\"\".format(col))\n",
    "    \n",
    "    execute_sql(\"\"\"\n",
    "    UPDATE delivery_date_complete_dataset\n",
    "    SET \"{0}_varchar\" = CAST({0} AS VARCHAR(50));\"\"\".format(col))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "596b8a92-c9c9-44fc-bc47-ff5c2613b468",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-31T07:42:27.064473Z",
     "start_time": "2023-07-31T07:42:27.059223Z"
    }
   },
   "outputs": [],
   "source": [
    "def handleMissingData(avgColumn, groupCol):\n",
    "    temp_col(avgColumn)\n",
    "    \n",
    "    try:\n",
    "        execute_sql(\"\"\"DROP TABLE AVERAGEDATA\"\"\")\n",
    "        print(\"DROPPING TABLE AVERAGEDATA\")\n",
    "    except:\n",
    "        print(\"[Teradata Database] [Info] Object 'AVERAGEDATA' does not exist.\")\n",
    "        \n",
    "    execute_sql(\"\"\"\n",
    "        CREATE VOLATILE TABLE AverageData AS (\n",
    "            SELECT DISTINCT AVG(\"{0}\") as AvgVal, \"{1}\" as \"{1}\"\n",
    "            FROM delivery_date_complete_dataset\n",
    "            WHERE \"{0}_varchar\" <> '**********************'\n",
    "            GROUP BY \"{1}\"\n",
    "        )\n",
    "        WITH DATA\n",
    "        ON COMMIT PRESERVE ROWS;\n",
    "    \"\"\".format(avgColumn, groupCol))\n",
    "    \n",
    "    execute_sql(\"\"\"\n",
    "        UPDATE delivery_date_complete_dataset AS E\n",
    "    SET \"{0}\" = \n",
    "        CASE\n",
    "            WHEN E.\"{0}_varchar\" = '**********************'\n",
    "                THEN COALESCE(\n",
    "                    (SELECT AvgVal FROM AverageData AS D WHERE E.\"{1}\" = D.\"{1}\"),\n",
    "                    (SELECT AVG(AvgVal) FROM AverageData)\n",
    "                )\n",
    "            ELSE \"{0}\"\n",
    "        END;\n",
    "    \"\"\".format(avgColumn, groupCol))\n",
    "    \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e042ed64-476a-4faa-a93f-c1eb56d0bbf7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above code is used to get the missing values for columns 'declared_handling_days', 'weight','carrier_min_estimate' and 'carrier_max_estimate' </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18bd389c-db07-49ff-ade6-0a253e1f9d3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "handleMissingData(\"declared_handling_days\", \"seller_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be892ac7-06ae-411f-9f2e-92e5d72dc2b0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-31T06:44:59.160770Z",
     "start_time": "2023-07-31T06:44:15.110157Z"
    }
   },
   "outputs": [],
   "source": [
    "handleMissingData(\"weight\", \"category_id\") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4ea854b-6a3f-46d1-9c77-66144429d907",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-31T06:45:32.796528Z",
     "start_time": "2023-07-31T06:44:59.163069Z"
    }
   },
   "outputs": [],
   "source": [
    "handleMissingData(\"carrier_min_estimate\", \"shipment_method_id\") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7d388844-6b29-4548-b377-d12e2cd118d5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-31T06:46:22.767415Z",
     "start_time": "2023-07-31T06:45:32.798934Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "handleMissingData(\"carrier_max_estimate\", \"shipment_method_id\") # Handle missing carrier_max_estimate"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4b27d2e-be8b-40bc-8388-ef4e536b1112",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The below code is used to get the missing values for column package size based on weight and average package size.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f5b7c2c-e164-41c7-8213-902f857f0c0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_dataset_df =  DataFrame('delivery_date_complete_dataset')\n",
    "pkg_averagedata = complete_dataset_df[complete_dataset_df.package_size != 'NONE']\n",
    "pkg_averagedata=pkg_averagedata.select(['package_size','weight']).groupby('package_size').mean()\n",
    "pkg_averagedata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4a3ca3a-d064-49c4-8afd-4d6e32771b14",
   "metadata": {},
   "outputs": [],
   "source": [
    "temp_df= complete_dataset_df[complete_dataset_df.package_size == 'NONE']\n",
    "temp_df = temp_df.merge(right=pkg_averagedata, how='cross', on = '1', lsuffix='t1', rsuffix = 't2')\n",
    "temp_df = temp_df.assign(difference=(temp_df.weight - temp_df.mean_weight).abs())\n",
    "window = temp_df.difference.window(partition_columns='record_number',\n",
    "                       order_columns='difference')\n",
    "\n",
    "temp_df = temp_df.assign(rn=window.row_number())\n",
    "temp_df = temp_df[temp_df.rn == 1]\n",
    "temp_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "919ad501-06e2-4bb4-a5d5-2a4835406f59",
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_dataset_df = complete_dataset_df.merge(right=temp_df, how='left',on=[\"record_number=record_number\"]\n",
    "                                                ,lsuffix='t3',rsuffix='t4')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c1ab7887-07f8-4606-a92b-563facbb84b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_dataset_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "08ba4f1e-e423-4143-9ff6-c8827fc58605",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will standardize round the values for handling days and distance and calculate the values for handling, shipping and delivery days.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60c51eaa-48df-4ab4-880b-b4f4056cf7c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_dataset_df = complete_dataset_df.assign(drop_columns=True,\n",
    "                                                 b2c_c2c = complete_dataset_df.t3_b2c_c2c,\n",
    "                                                 seller_id=complete_dataset_df.t3_seller_id,\n",
    "                                                 declared_handling_days= complete_dataset_df.t3_declared_handling_days.round(0),\n",
    "                                                 acceptance_scan_timestamp=complete_dataset_df.t3_acceptance_scan_timestamp,\n",
    "                                                 shipment_method_id=complete_dataset_df.t3_shipment_method_id,\n",
    "                                                 shipping_fee=complete_dataset_df.t3_shipping_fee,\n",
    "                                                 carrier_min_estimate=complete_dataset_df.t3_carrier_min_estimate,\n",
    "                                                 carrier_max_estimate=complete_dataset_df.t3_carrier_max_estimate,\n",
    "                                                 item_zip=complete_dataset_df.t3_item_zip,\n",
    "                                                 buyer_zip=complete_dataset_df.t3_buyer_zip,\n",
    "                                                 category_id=complete_dataset_df.t3_category_id,\n",
    "                                                 item_price=complete_dataset_df.t3_item_price,\n",
    "                                                 quantity=complete_dataset_df.t3_quantity,\n",
    "                                                 payment_datetime=complete_dataset_df.t3_payment_datetime,\n",
    "                                                 delivery_date=complete_dataset_df.t3_delivery_date,\n",
    "                                                 weight=complete_dataset_df.t3_weight,\n",
    "                                                 weight_units=complete_dataset_df.t3_weight_units,\n",
    "                                                 package_size=complete_dataset_df.t2_package_size,\n",
    "                                                 record_number=complete_dataset_df.t3_record_number,\n",
    "                                                 item_lat=complete_dataset_df.t3_item_lat,\n",
    "                                                 item_long=complete_dataset_df.t3_item_long,\n",
    "                                                 buyer_lat=complete_dataset_df.t3_buyer_lat,\n",
    "                                                 buyer_long=complete_dataset_df.t3_buyer_long,\n",
    "                                                 distance=complete_dataset_df.t3_distance.round(0),\n",
    "                                                 mean_weight=complete_dataset_df.mean_weight,\n",
    "                                                 difference=complete_dataset_df.difference,\n",
    "                                                 rn=complete_dataset_df.t4_rn\n",
    "                                                  )\n",
    "complete_dataset_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ffccaebe-4347-4baf-826c-1a278dd038f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_dataset_df = complete_dataset_df.assign(\n",
    "                                    handling_days = complete_dataset_df.acceptance_scan_timestamp.cast(type_=DATE)\n",
    "                                            - complete_dataset_df.payment_datetime.cast(type_=DATE),\n",
    "                                    shipping_days = complete_dataset_df.delivery_date.cast(type_=DATE)\n",
    "                                            - complete_dataset_df.acceptance_scan_timestamp.cast(type_=DATE),\n",
    "                                    delivery_days = complete_dataset_df.delivery_date.cast(type_=DATE)\n",
    "                                            - complete_dataset_df.payment_datetime.cast(type_=DATE))\n",
    "\n",
    "complete_dataset_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "140fdd71-667a-4838-997d-ab89730c7d09",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For our analysis we will delete rows where distance , weight or item price are zero and select data where the delivery days are between 3 and 6.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0916517a-b9df-4ae4-9ce3-32dd99c0fa62",
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_dataset_df=complete_dataset_df[complete_dataset_df.distance != 0.0] \n",
    "complete_dataset_df=complete_dataset_df[complete_dataset_df.weight != 0]\n",
    "complete_dataset_df=complete_dataset_df[complete_dataset_df.item_price != 0.0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f63e7283-8992-469b-a2dd-838c9579d473",
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_dataset_df=complete_dataset_df[complete_dataset_df.delivery_days >= 3] \n",
    "complete_dataset_df=complete_dataset_df[complete_dataset_df.delivery_days <= 6 ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bcfda3c2-05b8-413b-9b79-1f1fc621d779",
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_dataset_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69bc1058-675a-42fb-af89-175e351f7023",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_final=df_.select([\"b2c_c2c\",\"shipping_fee\",\"item_price\",\"quantity\", \"weight\",\"package_size\",\"record_number\"\n",
    "#                     ,\"distance\",\"shipment_method_id\",\"category_id\",])\n",
    "df_final=complete_dataset_df.drop([\"seller_id\",\"declared_handling_days\", \"carrier_min_estimate\", \"carrier_max_estimate\",\n",
    "                \"item_zip\",\"buyer_zip\", \"weight_units\", \"item_lat\",\"item_long\",\"buyer_lat\",\"buyer_long\",\n",
    "                   \"payment_datetime\", \"acceptance_scan_timestamp\", \"delivery_date\"], axis=1)\n",
    "df_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4cf08be-cf74-412c-ae57-67d6148f700f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45f8deb7-602f-41c7-b7eb-929287053e8f",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Creation of final analytic dataset </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have datasets in which different columns have different units . If we feed these features to the model as is, there is every chance that one feature will influence the result more due to its value than the others. But this doesn’t necessarily mean it is more important as a predictor. So, to give importance to all the features we need feature scaling.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we apply the Standard scale and transform functions which are ScaleFit and ScaleTransform functions in Vantage. ScaleFit() function outputs statistics to input to ScaleTransform() function, which scales specified input DataFrame columns.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3b2fd80-26f4-4dea-a96b-34aa1e7f0255",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ScaleFit , ScaleTransform\n",
    "scaler = ScaleFit(\n",
    "                    data=df_final,\n",
    "                    target_columns=[\"shipping_fee\",\"item_price\", \"quantity\", \"weight\", \"distance\"],\n",
    "                    scale_method=\"STD\",\n",
    "                    global_scale=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5db3ce9f-fc6e-4c48-bfc3-0c6f4e9f3dc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "ADS_scaled = ScaleTransform(data=df_final,\n",
    "                         object=scaler.output,\n",
    "                         accumulate=[\"record_number\",\"b2c_c2c\", \"package_size\", \"delivery_days\"\n",
    "                                     ,\"shipment_method_id\",\"category_id\"]\n",
    "                           ).result\n",
    "ADS_scaled"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ddfd965a-4d67-4b43-89ac-a8b36472755d",
   "metadata": {},
   "outputs": [],
   "source": [
    "ADS_scaled = ADS_scaled.assign(shipment_method_id = ADS_scaled.shipment_method_id.cast(type_=VARCHAR(5)),\n",
    "                               category_id = ADS_scaled.category_id.cast(type_=VARCHAR(5)))\n",
    "ADS_scaled"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbfadf9d-223e-4f47-b662-8195e392ead9",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>OneHotEncodingfit function records all the parameters required for OneHotEncodingTransform() function. Such as, target attributes and their categorical values to be encoded and other parameters.    Output of OneHotEncodingFit() function is used by OneHotEncodingTransform() function for encoding the input data. It supports inputs in both sparse and dense format.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6be10134-baee-40f7-883a-bb344fb012e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(ADS_scaled, table_name = 'delivery_date_dataset_final', if_exists='replace')\n",
    "ADS_scaled = DataFrame('delivery_date_dataset_final')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60ee289f-8d24-4a91-a7d8-08dfce37dee3",
   "metadata": {},
   "outputs": [],
   "source": [
    "fit_obj = OneHotEncodingFit(data = ADS_scaled,\n",
    "                                is_input_dense=True,\n",
    "                                target_column=['b2c_c2c','shipment_method_id','category_id','package_size'],\n",
    "                                category_counts=[2,23,33,6],\n",
    "                                approach='Auto',\n",
    "                                other_column=\"other\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df10f690-c0bf-4d30-83f7-c85e9430c6bc",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>OneHotEncodingTransform function encodes specified attributes and categorical values as one-hot numeric vectors,  using OneHotEncodingFit() function output.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55df4415-1d35-4bad-8724-ed0d0771e38c",
   "metadata": {},
   "outputs": [],
   "source": [
    "OneHotTrandform_df = OneHotEncodingTransform(data=ADS_scaled,\n",
    "                                  object=fit_obj.result,\n",
    "                                  is_input_dense=True)\n",
    "OneHotTrandform_df.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06703833-deb1-45b6-830f-39acff7e027d",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Creation of Train and Test data.</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The TrainTestSplit() function simulates how a model would perform on new data. The function divides the dataset into train and test subsets to evaluate machine learning algorithms and validate processes. The first subset is used to train the model. The second subset is used to make predictions and compare the predictions to actual values.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16902846-1a0b-451f-b6ac-7a7f55ffb81c",
   "metadata": {},
   "outputs": [],
   "source": [
    "TrainTestSplit_out = TrainTestSplit(data = OneHotTrandform_df.result,\n",
    "                                        id_column=\"record_number\",\n",
    "                                        train_size=0.75,\n",
    "                                        test_size=0.25,\n",
    "                                        seed=42)\n",
    "\n",
    "TrainTestSplit_df = TrainTestSplit_out.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0da2b13f-c70d-43c4-9570-3580f5cc89e5",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Creating Train and Test datasets.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35ab496a-9c02-403a-a235-238c5dd62ecd",
   "metadata": {},
   "outputs": [],
   "source": [
    "delivery_date_train_dataset = TrainTestSplit_df[TrainTestSplit_df.TD_IsTrainRow == 1]\n",
    "delivery_date_test_dataset = TrainTestSplit_df[TrainTestSplit_df.TD_IsTrainRow == 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc69fcb6-bf0e-428c-9bfc-aadeb7258d9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(delivery_date_train_dataset, table_name = 'delivery_date_train_dataset', if_exists='replace')\n",
    "delivery_train_dataset = DataFrame('delivery_date_train_dataset')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a627aea-099c-43ed-b614-913c97f865b3",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Feature Selection using Elastic Net Regularization.</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Feature selection is a crucial step in building predictive models as it helps identify the most relevant and informative features from a potentially large set of variables. In this context, elastic net regularization is a powerful technique that can be employed to effectively filter out features and improve model performance.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Elastic net regularization combines the L1 (Lasso) and L2 (Ridge) regularization techniques, offering a balanced approach to feature selection. It applies a penalty term to the model's objective function, encouraging sparsity in the coefficient estimates and promoting the selection of a subset of important features while shrinking the coefficients of less relevant or redundant features.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For more information on **Regularization**: <a href='https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Database-Analytic-Functions/Model-Training-Functions/TD_GLM/TD_GLM-Syntax-Elements'>[Link]</a></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1accc2f-f797-40d9-80e9-59407479f612",
   "metadata": {},
   "outputs": [],
   "source": [
    "GLM_out = GLM(input_columns= ['2:11','13:75'],\n",
    "                    response_column = \"delivery_days\",\n",
    "                    data = delivery_train_dataset,\n",
    "                    family='Gaussian',\n",
    "                    learning_rate = 'adaptive',\n",
    "                    batch_size=500,\n",
    "                    max_iter_num=100,\n",
    "                    alpha=0.3,\n",
    "                    lambda1=0.01,\n",
    "                    iter_num_no_change=70,\n",
    "                    tolerance=0.002,\n",
    "                    intercept=True,\n",
    "                    initial_eta=0.015,\n",
    "                    momentum = 0.8,\n",
    "                    local_sgd_iterations=10\n",
    "                    )\n",
    "GLM_out.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4958f03-cd03-4bc9-b1cd-1fc54f8faf16",
   "metadata": {},
   "outputs": [],
   "source": [
    "glm_fs_df = GLM_out.result\n",
    "copy_to_sql(glm_fs_df, table_name = 'td_glm_cal_ex', if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "11ee2aca-0bf4-4bd9-9d69-865dd345760d",
   "metadata": {},
   "outputs": [],
   "source": [
    "glm_fs_df = glm_fs_df[glm_fs_df.attribute> 0]\n",
    "glm_fs_df = glm_fs_df[glm_fs_df.estimate != 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a5f5758-3aa6-4e58-a587-958ba345402a",
   "metadata": {},
   "outputs": [],
   "source": [
    "val_list=glm_fs_df.select(['predictor']).get_values()\n",
    "final_list =  list(val_list[:,0]) + ['record_number', 'delivery_days']\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b63126e-dd43-4d9c-afdb-39a977341ab4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The output of the TD_GLM function provides attributes where the index of the predictors have positive values and the estimate column has the predictor weights. For feature selection we consider all columns which are the predictors and have weights >0 i.e. estimate > 0. </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> In the for loop we create a list of all such columns and create a table with only the columns which have weightage as predictors for the model.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce7fc924-186e-41b7-9154-9878177fc8b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "train_dataset = delivery_date_train_dataset[final_list]\n",
    "test_dataset = delivery_date_test_dataset[final_list]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26426ef8-311f-4685-aef4-c57b73a81d35",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We create the train and test datasets with only these features(columns) to be used in the model for predictions.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30bbd479-1402-45b4-8cf4-f6184b30b038",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>8. Generalized Linear Model (GLM) in Teradata </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The TD_GLM function is a generalized linear model (GLM) that performs regression and classification analysis on data sets, where the response follows an exponential family distribution and supports the following models:</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Regression (Gaussian family): The loss function is squared error.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Binary Classification (Binomial family): The loss function is logistic and implements logistic regression. The only response values are 0 or 1.</li>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The function uses the Minibatch Stochastic Gradient Descent (SGD) algorithm. The algorithm estimates the gradient of loss in minibatches, which is defined by the BatchSize argument and updates the model with a learning rate using the LearningRate argument.</p>\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we are using Regression</p>    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e9d5955d-a6b1-4096-b442-2a4611f39974",
   "metadata": {},
   "outputs": [],
   "source": [
    "GLM_df = GLM(input_columns= ['0:50'],\n",
    "                    response_column = \"delivery_days\",\n",
    "                    data = train_dataset,\n",
    "                    family='Gaussian',\n",
    "                    learning_rate = 'adaptive',\n",
    "                    batch_size=800,\n",
    "                    max_iter_num=300,\n",
    "                    alpha=0.2,\n",
    "                    lambda1=0.01,\n",
    "                    iter_num_no_change=200,\n",
    "                    tolerance=0.002,\n",
    "                    intercept=True,\n",
    "                    initial_eta=0.02,\n",
    "                    momentum = 0.8,\n",
    "                    local_sgd_iterations=20\n",
    "                    )\n",
    "GLM_df.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ddc6855f-56a5-49d7-a720-05f57d295458",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>TDGLMPredict </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The TDGLMPredict function predicts target values (regression) and class labels (classification) for test data using a GLM model of the TD_GLM function.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "28a706b9-c1b8-473c-bb59-a90e82c7b923",
   "metadata": {},
   "outputs": [],
   "source": [
    "TDGLMPredict_out = TDGLMPredict(object=GLM_df.result,\n",
    "                                    newdata=test_dataset,\n",
    "                                    accumulate=\"delivery_days\",\n",
    "                                    id_column=\"record_number\")\n",
    "df=TDGLMPredict_out.result\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b683c2d7-674a-4d56-89e7-2db364022299",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "# import matplotlib.patches as patches\n",
    "import seaborn as sns\n",
    "\n",
    "df_plot=df.to_pandas(all_rows=True).reset_index().head(200)\n",
    "x = df_plot['record_number']\n",
    "# Put array of years here\n",
    "y1 = df_plot['delivery_days']\n",
    "y2 = df_plot['prediction']\n",
    "plt.figure(figsize=(20,8))\n",
    "sns.lineplot(data= df_plot ,x=\"record_number\",y=\"delivery_days\",ci=None)\n",
    "sns.lineplot(data= df_plot ,x=\"record_number\",y=\"prediction\",ci=None)\n",
    "plt.grid()\n",
    "# plt.xticks(np.arange(1,60, step=1))\n",
    "plt.legend(['Actual Value', 'Predicted Value'], loc='best', fontsize=16)\n",
    "plt.title('Comparison of Actual vs Predicted Delivery Days', fontsize=20)\n",
    "plt.xlabel('Record Number', fontsize=16)\n",
    "plt.ylabel('Delivery Days', fontsize=16)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2fceccf6-7022-49f0-9525-3509f0b2d67c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>RegressionEvaluator</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The RegressionEvaluator function computes metrics to evaluate and compare multiple models and summarizes how close predictions are to their expected values.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For more information on **RegressionEvaluator**: <a href='https://docs.teradata.com/r/Lake/Teradata-Package-for-Python-Function-Reference-on-VantageCloud-Lake/teradataml-Analytic-Database-17.20.xx-Analytic-Functions/MODEL-EVALUATION-functions/RegressionEvaluator'> [Link] </a></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f6b3aba-5c27-4200-a04c-46b9a18c9f77",
   "metadata": {},
   "outputs": [],
   "source": [
    "RegressionEvaluator_out = RegressionEvaluator(data = df,\n",
    "                                                  observation_column = \"delivery_days\",\n",
    "                                                  prediction_column = \"prediction\",\n",
    "                                                  freedom_degrees = [5, 48],\n",
    "                                                  independent_features_num = 5,\n",
    "                                                  metrics = ['RMSE','R2','FSTAT'])\n",
    "RegressionEvaluator_out.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e33de01-8cb8-418f-b291-9709d4985707",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Metrics of the regression evaluator has the RMSE, R2 and the F-STAT metrics which are specified in the Metrics. The main values to observe are the P_VALUE and the F_CONCLUSION. Lesser the value of RMSE the more correct values will be predicted by the model. The P_VALUE should be less than 0.05 and the F_CONCLUSION should be Reject null hypothesis which means that the model has given expected outputs.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Root mean squared error (RMSE)The most common metric for evaluating linear regression model performance is called root mean squared error, or RMSE. Root means squared error (MSE) is the square root of the average of the squares of the errors between observed values and predicted values.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The coefficient of determination — more commonly known as R² — allows us to measure the strength of the relationship between the response and predictor variables in the model. R Squared (R2) is the proportion of the variation in the dependent variable that is predictable from the independent variable(s).</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The metrics specified in the Metrics syntax element are displayed. For FSTAT, the following columns are displayed:</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>F_score:- F_score value from the F-test.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>F_Critcialvalue:- F critical value from the F-test. (alpha, df1, df2, UPPER_TAILED) , alpha = 95%</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>p_value:- Probability value associated with the F_score value (F_score, df1, df2, UPPER_TAILED)</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>F_Conclusion:- F-test result, either 'reject null hypothesis' or 'fail to reject null hypothesis'. If F_score > F_Critcialvalue, then 'reject null hypothesis' Else 'fail to reject null hypothesis'.</li></p>\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2d67497-6bc3-425e-b3cd-d791b43dd5a2",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>9. Decision Forest </b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Decision Forest is a powerful method used for predicting outcomes in both classification and regression problems. It's an improvement on the technique of combining (or \"bagging\") multiple decision trees. Normally, building a decision tree involves assessing the importance of each feature in the data to determine how to divide the information. This method takes a unique approach by only considering a random subset of features at each division point in the tree. This forces each decision tree within the \"forest\" to be different from one another, which ultimately improves the accuracy of the predictions. The function relies on a training dataset to develop a prediction model. Then, the TD_DecisionForestPredict function uses the model built by the TD_DecisionForest function to make predictions. It supports regression, binary, and multi-class classification tasks.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Typically, constructing a decision tree involves evaluating the value for each input feature in the data to select a split point. The function reduces the features to a random subset (that can be considered at each split point); the algorithm can force each decision tree in the forest to be very different to improve prediction accuracy. The function uses a training dataset to create a predictive model. The TD_DecisionForestPredict function uses the model created by the TD_DecisionForest function for making predictions. The function supports regression, binary, and multi-class classification.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35ea3f4e-04fd-4261-a458-801f2b270e22",
   "metadata": {},
   "outputs": [],
   "source": [
    "train_dataset.to_sql(table_name='train_dataset' , if_exists='replace')\n",
    "test_dataset.to_sql(table_name='test_dataset' , if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72e11c3f-1fd3-48e3-8986-232730bffdd6",
   "metadata": {},
   "outputs": [],
   "source": [
    "DecisionForest_out = DecisionForest(data = DataFrame('train_dataset'), \n",
    "                            input_columns = ['0:52'], \n",
    "                            response_column = 'delivery_days', \n",
    "                            max_depth = 24, \n",
    "                            num_trees = 6, \n",
    "                            min_node_size = 1, \n",
    "                            mtry = -1, \n",
    "                            mtry_seed = 2,\n",
    "                            seed = 2, \n",
    "                            tree_type = 'REGRESSION')\n",
    "\n",
    "decision_df=DecisionForest_out.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48db575b-de19-4822-86cd-5a6c67762ebf",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>TDDecisionForestPredict</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>TDDecisionForestPredict function uses the model output by TD_DecisionForest function to analyze the input data and make predictions. This function outputs the probability that each observation is in the predicted class. Processing times are controlled by the number of trees in the model. When the number of trees is more than what can fit in memory, then the trees are cached in a local spool space.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16a9112c-00b1-4f44-91e9-312fcdb5936f",
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Predict_out = TDDecisionForestPredict(\n",
    "    newdata=DataFrame('test_dataset'),\n",
    "    object=DecisionForest_out.result,\n",
    "    id_column='record_number',\n",
    "    accumulate='delivery_days',\n",
    "    )\n",
    "\n",
    "DF_Predict_out.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad3b0157-5630-4eb8-934c-c13901d85581",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_result = DF_Predict_out.result\n",
    "df_result = df_result.assign(delivery_hours = df_result.delivery_days*24,\n",
    "                             prediction_hours = df_result.prediction * 24)\n",
    "df_result                             "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ff07f03-df45-4e84-9f1f-df80cf096516",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "# import matplotlib.patches as patches\n",
    "import seaborn as sns\n",
    "\n",
    "df_plot=df_result_pd=df_result.to_pandas(all_rows=True).reset_index()\n",
    "plt.figure(figsize=(20,8))\n",
    "sns.lineplot(data= df_plot[:200] ,x=\"record_number\",y=\"delivery_days\",ci=None)\n",
    "sns.lineplot(data= df_plot[:200] ,x=\"record_number\",y=\"prediction\",ci=None)\n",
    "plt.grid()\n",
    "# plt.xticks(np.arange(1,60, step=1))\n",
    "plt.legend(['Actual Value', 'Predicted Value'], loc='best', fontsize=16)\n",
    "plt.title('Comparison of Actual vs Predicted Delivery Days', fontsize=20)\n",
    "plt.xlabel('Record Number', fontsize=16)\n",
    "plt.ylabel('Delivery Days', fontsize=16)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c15cccde-d82f-4cdc-84e6-49915a0bf08f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>RegressionEvaluator</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The RegressionEvaluator function computes metrics to evaluate and compare multiple models and summarizes how close predictions are to their expected values.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For more information on **RegressionEvaluator**: <a href='https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Database-Analytic-Functions/Model-Evaluation-Functions/TD_RegressionEvaluator'> [Link] </a></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "427ed648-734d-48b4-8d03-31b1795b4e60",
   "metadata": {},
   "outputs": [],
   "source": [
    "RegressionEvaluator_dfout = RegressionEvaluator(data = df_result,\n",
    "                                                  observation_column = \"delivery_days\",\n",
    "                                                  prediction_column = \"prediction\",\n",
    "                                                  freedom_degrees = [5, 48],\n",
    "                                                  independent_features_num = 5,\n",
    "                                                  metrics = ['RMSE','R2','FSTAT'])\n",
    "RegressionEvaluator_dfout.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "969939d3-fadc-4404-a430-3cd45dc70b00",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Metrics of the regression evaluator has the RMSE, R2 and the F-STAT metrics which are specified in the Metrics. The main values to observe are the P_VALUE and the F_CONCLUSION. Lesser the value of RMSE the more correct values will be predicted by the model. The P_VALUE should be less than 0.05 and the F_CONCLUSION should be Reject null hypothesis which means that the model has given expected outputs.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Root mean squared error (RMSE)The most common metric for evaluating linear regression model performance is called root mean squared error, or RMSE. Root means squared error (MSE) is the square root of the average of the squares of the errors between observed values and predicted values.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The coefficient of determination — more commonly known as R² — allows us to measure the strength of the relationship between the response and predictor variables in the model. R Squared (R2) is the proportion of the variation in the dependent variable that is predictable from the independent variable(s).</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The metrics specified in the Metrics syntax element are displayed. For FSTAT, the following columns are displayed:</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>F_score:- F_score value from the F-test.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>F_Critcialvalue:- F critical value from the F-test. (alpha, df1, df2, UPPER_TAILED) , alpha = 95%</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>p_value:- Probability value associated with the F_score value (F_score, df1, df2, UPPER_TAILED)</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>F_Conclusion:- F-test result, either 'reject null hypothesis' or 'fail to reject null hypothesis'. If F_score > F_Critcialvalue, then 'reject null hypothesis' Else 'fail to reject null hypothesis'.</li></p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be0c90a7-cc95-4fd5-a633-b5ded163fd7c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b> Conclusion</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have seen an end-to-end exploration process for Shipping Time Predictions using ClearScape Analytics on Teradata Vantage. We have preprocessed data, created model using the InDB Analytic functions and compared the performance of the 2 models. The data we have used is sample data and so the results may not be accurate. Thanks to the in-database capabilities offered by Teradata Vantage with ClearScape Analytics, we were able to run this exploration with the smallest notebook instance.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d384c07-258a-4c93-be28-4b59f8a63010",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>11. Cleanup</b></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We need to clean up our work tables to prevent errors next time.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "752650cb-88ee-438f-ba12-56ebf331e617",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['temp_Ship','train_dataset','test_dataset','td_glm_cal_ex',\n",
    "          'delivery_date_dataset_final','delivery_date_train_dataset','delivery_date_test_dataset']\n",
    "\n",
    "# Loop through the list of tables and execute the drop table command for each table\n",
    "for table in tables:\n",
    "    try:\n",
    "        db_drop_table(table_name=table)\n",
    "    except:\n",
    "        pass\n",
    "      \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9658c8a2-8d14-4065-b6ce-a9736ef9e961",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_drop_table(table_name='Delivery_date_data_new', schema_name='DEMO_ShipTimePred_db') "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d26d42f5-5b53-4e7f-b6d1-92f45c6b8f2a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Databases and Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use the following code to clean up tables and databases created for this demonstration.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4f7c0a8-eca9-4b2e-8799-9a767aa51e12",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_ShippingTimePrediction');\" \n",
    "#Takes 45 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "922c3f95-9746-4ec0-9bae-cb283397e770",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "702943bb-a184-460b-ada7-0d75ee59d339",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Resources</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let’s look at the elements we have available for reference for this notebook:</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "The implemented functions are from the following documentation:</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'> <a href='https://www.docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20'>Advanced SQL Engine 17.20 Functions</a></li>       \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'> <a href= 'https://docs.teradata.com/r/Enterprise_IntelliFlex_Lake_VMware/Vantage-Analytics-Library-User-Guide/Welcome-to-Vantage-Analytics-Library'>Vantage Analytics Library</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'> <a href= 'https://docs.teradata.com/r/Teradata-VantageTM-Unbounded-Array-Framework-Time-Series-Reference'>UAF Time-Series 17.20 Functions</a></li>    \n",
    "<br> \n",
    "       \n",
    "    \n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Data</b></p>\n",
    "      \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The data was collected from open source <a href= 'https://www.kaggle.com/datasets/armanaanand/ebay-delivery-date-prediction'>Kaggle</a> with following description</p>\n",
    "    \n",
    "<img src='images/DataSet.png'>\n",
    "   \n",
    "   \n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Filters:</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Industry:</b> Transportation</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Functionality:</b> Machine Learning</li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Use Case:</b> Shipping Time Predictions</li></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Related Resources:</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href ='https://www.teradata.com/Blogs/Using-a-Lake-Centric-Modernization-Approach'>Using a Lake-Centric Modernization Approach to Clean Up a Data and Compute Mess</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href ='https://www.teradata.com/Blogs/Hyper-scale-time-series-forecasting-done-right'>Hyper-scale time series forecasting done right</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href ='https://www.teradata.com/Blogs/Data-Analytics-Keeps-the-Wheels-on-the-Bus'>Data & Analytics Keep the Wheels on the Bus!</a></li>\n",
    "  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "872c40f5-9b68-4d5b-a043-991a25fc56d3",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023, 2024. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "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.9.10"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "394px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}