UseCases/4D_Analytics_on_bike_sharing/4D_Analytics_on_bike_sharing_PY_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "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",
    "       Austin Bike Share\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",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Bike shares are becoming a popular alternative means of transportation. Suppose you had a transportation business servicing the public with various stations where they could access your transportation services. You must ensure you have equipment at the stations when the public needs them. You also know that the weather dramatically impacts the demand for your transportation services. This demonstration shows how to integrate historical trip information with weather information, leveraging Vantage Geospatial and time-series capability to improve your service and grow your business. \n",
    "<br>\n",
    "The City of Austin makes data available on >649k bike trips over 2013-2017.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Contents:</b></p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Connect to Vantage</li>\n",
    "    <li>Explore the data</li>\n",
    "    <li>Create and Explore Temporal, Geospatial and Time index data</li>\n",
    "    <li>Insights</li>\n",
    "    <li>Clean up</li>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<h1 style = 'font-size:28px;font-family:Arial;color:#00233c'><b>1. Connect to Vantage</b></h1>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "# '%%capture' suppresses the display of installation steps of the following packages\n",
    "# !pip install folium"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: </b><i>The above statements may need to be uncommented if you run the notebooks on a platform other than ClearScape Analytics Experience that does not have the libraries installed. If you uncomment those installs, be sure to restart the kernel after executing those lines to bring the installed libraries into memory. The simplest way to restart the Kernel is by typing zero zero: <b> 0 0</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we import the required libraries, set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries\n",
    "import warnings\n",
    "\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "warnings.simplefilter(action=\"ignore\", category=DeprecationWarning)\n",
    "warnings.simplefilter(action=\"ignore\", category=RuntimeWarning)\n",
    "warnings.simplefilter(action=\"ignore\", category=FutureWarning)\n",
    "warnings.simplefilter(action=\"ignore\", category=UserWarning)\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.ticker as mtick\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import random as rand\n",
    "import getpass\n",
    "import math\n",
    "\n",
    "from teradataml import *\n",
    "from teradataml.dataframe.dataframe import DataFrame\n",
    "from teradataml.dataframe.dataframe import in_schema\n",
    "from teradataml.context.context import create_context, remove_context, get_context\n",
    "from teradataml.dataframe.copy_to import copy_to_sql\n",
    "from teradataml.options.display import display\n",
    "\n",
    "display.max_rows = 5\n",
    "\n",
    "import plotly.express as px\n",
    "from plotly.subplots import make_subplots\n",
    "import plotly.graph_objects as go\n",
    "import seaborn as sns\n",
    "import folium\n",
    "from folium import Choropleth, Circle, Marker, CircleMarker, Circle\n",
    "from folium.plugins import HeatMap, MarkerCluster\n",
    "\n",
    "from sklearn.metrics import mean_absolute_error\n",
    "from sklearn.metrics import roc_auc_score\n",
    "from sklearn.metrics import roc_curve\n",
    "\n",
    "# pandas dataframe display settings\n",
    "pd.set_option(\"display.max_rows\", 50)\n",
    "pd.set_option(\"display.max_columns\", 50)\n",
    "pd.set_option(\"display.width\", 1000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press the Enter key, then <b>use down arrow</b> to go to next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=4D_Analytics_on_bike_sharing_PY_SQL.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<h2 style = 'font-size:20px;font-family:Arial;color:#00233c'> <b>1.1 Getting Data for This Demo</b></h2>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. You have the option of either running the demo using foreign tables to access the data without using any storage on your environment or downloading the data to local storage which may yield somewhat faster execution, but there could be considerations of available storage. There are two statements in the following cell, and one is commented out. You may switch which mode you choose by changing the comment string.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_AustinBikeShare_cloud');\"\n",
    " # takes about 30 seconds, estimated space: 0 MB\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_AustinBikeShare_local');\" \n",
    "# takes about 50 seconds, estimated space: 200 MB"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next is an optional step – if you want to see status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>2. Explore the data</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As a warm-up, let us look at the tables in our database TRNG_AustinBike.</p>       "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"SELECT \n",
    "    DatabaseName,\n",
    "    TableName\n",
    "FROM\n",
    "    DBC.Tables\n",
    "WHERE\n",
    "    DatabaseName = 'DEMO_AustinBikeShare'\n",
    "\"\"\"\n",
    "# pd.read_sql(query, eng)\n",
    "tbl_df = DataFrame.from_query(query)\n",
    "tbl_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see that we have three tables in our database. The Trips table contains data on the trips taken using the bikes, the stations table has locations of the bike stations, and the weather table has details about the weather.\n",
    "    <br>\n",
    "    <br>\n",
    "The query below shows the number of rows in each of the tables in the database.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT\n",
    "(\n",
    "    SELECT COUNT(*)\n",
    "    FROM DEMO_AustinBikeShare.trips\n",
    ") AS trips,\n",
    "(\n",
    "    SELECT COUNT(*)\n",
    "    FROM DEMO_AustinBikeShare.stations\n",
    ") AS stations,\n",
    "(\n",
    "    SELECT COUNT(*)\n",
    "    FROM DEMO_AustinBikeShare.weather\n",
    ") AS weather;\n",
    "\"\"\"\n",
    "\n",
    "# pd.read_sql(query, eng)\n",
    "cnt_df = DataFrame.from_query(query)\n",
    "cnt_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>2.1 Examine the trips table</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's look at the sample data in the trips table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT\n",
    "   TOP 5 *\n",
    "FROM\n",
    "    DEMO_AustinBikeShare.trips\n",
    ";\"\"\"\n",
    "\n",
    "# pd.read_sql(query, eng)\n",
    "trips_df = DataFrame.from_query(query)\n",
    "trips_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Which type of subscribers take most of the rides?</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT \n",
    "    top 5 count(trip_id) as ride_count, subscriber_type \n",
    "FROM DEMO_AustinBikeShare.trips \n",
    "GROUP BY subscriber_type \n",
    "ORDER BY 1 desc;\"\"\"\n",
    "\n",
    "# pd.read_sql(query, eng)\n",
    "tripcount_df = DataFrame.from_query(query)\n",
    "tripcount_df.sort(\"ride_count\", ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the above result we can say that <b>Walk Up</b> rides are <b>250%</b> more than second most popular subscription type.\n",
    "    <br><br>\n",
    "    From which station do highest number of trips start?</p>  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT\n",
    "    TOP 20\n",
    "    start_station_name,\n",
    "    COUNT(trip_id) AS trips\n",
    "FROM\n",
    "    DEMO_AustinBikeShare.trips\n",
    "GROUP BY 1\n",
    "ORDER BY 2 DESC;\n",
    "\"\"\"\n",
    "\n",
    "df_st_trips = DataFrame.from_query(query)\n",
    "df_st_trips.sort(\"trips\", ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_histogram(df, x, y, title, x_title, y_title, width=1200, height=500):\n",
    "    fig = px.histogram(df, x=x, y=y, title=title, nbins=df.shape[0])\n",
    "    fig.update_yaxes(title=y_title)\n",
    "    fig.update_xaxes(title=x_title)\n",
    "    fig.update_layout(\n",
    "        autosize=False,\n",
    "        width=width,\n",
    "        height=height,\n",
    "    )\n",
    "    return fig"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_st_trips = df_st_trips.to_pandas()\n",
    "get_histogram(\n",
    "    df_st_trips,\n",
    "    x=\"start_station_name\",\n",
    "    y=\"trips\",\n",
    "    title=\"Trips by station\",\n",
    "    x_title=\"start_station_name\",\n",
    "    y_title=\"trips\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We see that <b>Riverside @ S. Lamar</b> has the highest number of trips originating from here.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's see average number of trips originating per from a station.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT AVG(trips) as avg_trips FROM (\n",
    "    SELECT\n",
    "    start_station_name,\n",
    "    COUNT(1) AS trips\n",
    "    FROM\n",
    "        DEMO_AustinBikeShare.trips\n",
    "    GROUP BY 1\n",
    ") AS t;\n",
    "\"\"\"\n",
    "\n",
    "# pd.read_sql(query, eng)\n",
    "\n",
    "df_avg_trips = DataFrame.from_query(query)\n",
    "df_avg_trips"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We see that the top station <b>Riverside @ S. Lamar</b> has <b>4 times more trips</b> than the average.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now let's look at the pattern of bike usage over time. </p>    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT\n",
    "    TRUNC(start_time, 'Month') AS start_Month,\n",
    "    COUNT(1) AS trips\n",
    "FROM\n",
    "    DEMO_AustinBikeShare.trips\n",
    "GROUP BY 1\n",
    ";\n",
    "\"\"\"\n",
    "\n",
    "df_trips_day = DataFrame.from_query(query)\n",
    "df_trips_day.sort(\"start_Month\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_trips_day = df_trips_day.to_pandas()\n",
    "get_histogram(\n",
    "    df_trips_day,\n",
    "    x=\"start_Month\",\n",
    "    y=\"trips\",\n",
    "    title=\"Trips by day\",\n",
    "    x_title=\"start_Month\",\n",
    "    y_title=\"trips\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above chart we observe few things:</p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>There are two months where the data is nearly missing</li>\n",
    "    <li>The peak usage month is as much as 30k trips in a month</li>\n",
    "    <li>March and October are first and second busiest months across the data of 4 years.</li>\n",
    "</ol>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Can this be related to the weather? Is the weather in March and October favorable for biking? Let's see this in the next section.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>2.2 Examine the weather table</b></p>    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf = DataFrame(in_schema(\"DEMO_AustinBikeShare\", \"weather\"))\n",
    "tdf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The temperature data is reported hourly (the minutes and seconds are always zero). The temperature columns are in Kelvin, which few people use to decide if it is good bicycle weather, so we will create a view over the weather table to convert the temperature to Fahrenheit. We will also average the temperature for the day.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "query = '''\n",
    "REPLACE VIEW austin_weather AS\n",
    "    SELECT\n",
    "        TRUNC(dt, 'Month') AS dt, \n",
    "        ROUND(AVG((temp - 273.15) * 9/5 + 32) ,0) AS AveTemp,\n",
    "        SUM(CASE\n",
    "                WHEN weather_main in ('Rain', 'Mist') THEN 1\n",
    "                ELSE 0\n",
    "            END) AS Precip_hours\n",
    "    FROM DEMO_AustinBikeShare.weather\n",
    "    GROUP BY 1;'''\n",
    "\n",
    "execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT * FROM austin_weather;\"\"\"\n",
    "\n",
    "df_temp_month = DataFrame.from_query(query)\n",
    "df_temp_month.sort(\"dt\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_temp_month = df_temp_month.to_pandas()\n",
    "get_histogram(\n",
    "    df_temp_month,\n",
    "    x=\"dt\",\n",
    "    y=\"AveTemp\",\n",
    "    title=\"Average Temperature by Month\",\n",
    "    x_title=\"date\",\n",
    "    y_title=\"trips\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If we plot the data, we find we are missing some data, but we get an idea of the typical temperature ranges.  If we look at the hours each month when precipitation occurs, we see some patterns that could also be impacting the number of trips.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we can observe that for almost all of the March and October months, the temperature is around 70 degrees Fahrenheit. This is a favorable biking temperature as it is neither too cold nor too hot.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "get_histogram(\n",
    "    df_temp_month,\n",
    "    x=\"dt\",\n",
    "    y=\"Precip_hours\",\n",
    "    title=\"Average Precip Hours by Month\",\n",
    "    x_title=\"date\",\n",
    "    y_title=\"trips\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the above two charts, March and October have favorable conditions for biking, which reflects in the increased bike rides.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>2.3 Geospatial data</b></p>    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Geospatial columns have a type and one or more pairs of Latitude and Longitude. We included the Latitude and Longitude columns in the table so you could see how a simple geospatial feature (a POINT) is represented.\n",
    "    <br>\n",
    "For more geospatial datatype supported by Teradata, please click <a href = 'https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Geospatial-Data-Types/Geospatial-Data/Geometry-Types'>here</a>.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT * FROM DEMO_AustinBikeShare.stations;\"\"\"\n",
    "\n",
    "df_stations = DataFrame.from_query(query)\n",
    "df_stations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us analyze the info about the stations first. There are 72 stations in total, among them 56 - active, closed - 10, moved - 5 and 1 station which is active only during Austin City Limits Music Festival (ACL only). Let's plot them on the map.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_stations = df_stations.to_pandas()\n",
    "df_stations[\"status\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create folium map object\n",
    "def get_folium_map_obj(\n",
    "    location=[30.27186, -97.73997], zoom_start=10.5, height=700, tiles=\"OpenStreetMap\"\n",
    "):\n",
    "    return folium.Map(\n",
    "        location=location, zoom_start=zoom_start, height=height, tiles=tiles\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get color dictionary for statuses\n",
    "def get_color_dict():\n",
    "    return {\"active\": \"green\", \"ACLonly\": \"gray\", \"closed\": \"red\", \"moved\": \"purple\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating the map\n",
    "map_obj = get_folium_map_obj(zoom_start=11)\n",
    "clr_dict = get_color_dict()\n",
    "\n",
    "# Adding points to the map\n",
    "mc = MarkerCluster()\n",
    "for idx, row in df_stations.iterrows():\n",
    "    if not math.isnan(row[\"longitude\"]) and not math.isnan(row[\"latitude\"]):\n",
    "        cn = row[\"name\"]\n",
    "        sts = row[\"status\"]\n",
    "        clr = clr_dict[sts.replace(\" \", \"\")]\n",
    "        mc.add_child(\n",
    "            Marker(\n",
    "                [row[\"latitude\"], row[\"longitude\"]],\n",
    "                color=clr,\n",
    "                popup=\"<b> Name:\" + cn + \"</b> <i> Status:\" + sts + \"</i>\",\n",
    "                icon=folium.Icon(icon=\"bicycle\", prefix=\"fa\", color=clr),\n",
    "            )\n",
    "        ).add_to(map_obj)\n",
    "map_obj.add_child(mc)\n",
    "\n",
    "map_obj.add_child(folium.LatLngPopup())\n",
    "# view map\n",
    "map_obj"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A map with many of stations can be made simpler by using station clusters. When the map is zoomed out, nearby stations are clustered together. However, as the zoom level is increased, the clusters are broken up. Green signifies fewer than 10 stations, whereas the yellow cluster hue indicates more than 10 stations.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Click on any Station to view name of station and status like Active, Closed, Moved or ACL Only.<p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Colors of Station indicates below:</p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Green - Active</li>\n",
    "    <li>Red - Closed</li>\n",
    "    <li>Purple - Moved </li>\n",
    "    <li>Gray - ACL Only </li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To filter out particular statuses like Active or closed stations: click on the left-top side legend below the map.<p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating the map\n",
    "map_obj = get_folium_map_obj(zoom_start=12.5)\n",
    "clr_dict = get_color_dict()\n",
    "\n",
    "# define feature groups\n",
    "fg_act = folium.FeatureGroup(name=\"Active\", color=\"green\")\n",
    "fg_acl = folium.FeatureGroup(name=\"ACL only\", color=\"gray\")\n",
    "fg_cls = folium.FeatureGroup(name=\"Closed\", color=\"red\")\n",
    "fg_mv = folium.FeatureGroup(name=\"Moved\", color=\"purple\")\n",
    "\n",
    "for idx, row in df_stations.iterrows():\n",
    "    if not math.isnan(row[\"longitude\"]) and not math.isnan(row[\"latitude\"]):\n",
    "        cn = row[\"name\"]\n",
    "        sts = row[\"status\"]\n",
    "        clr = clr_dict[sts.replace(\" \", \"\")]\n",
    "        if clr == \"red\":\n",
    "            folium.Marker(\n",
    "                [row[\"latitude\"], row[\"longitude\"]],\n",
    "                popup=\"<b> Name:\" + cn + \"</b> <i> Status:\" + sts + \"</i>\",\n",
    "                icon=folium.Icon(icon=\"bicycle\", prefix=\"fa\", color=clr),\n",
    "            ).add_to(fg_cls)\n",
    "        elif clr == \"green\":\n",
    "            folium.Marker(\n",
    "                [row[\"latitude\"], row[\"longitude\"]],\n",
    "                popup=\"<b> Name:\" + cn + \"</b> <i> Status:\" + sts + \"</i>\",\n",
    "                icon=folium.Icon(icon=\"bicycle\", prefix=\"fa\", color=clr),\n",
    "            ).add_to(fg_act)\n",
    "        elif clr == \"gray\":\n",
    "            folium.Marker(\n",
    "                [row[\"latitude\"], row[\"longitude\"]],\n",
    "                popup=\"<b> Name:\" + cn + \"</b> <i> Status:\" + sts + \"</i>\",\n",
    "                icon=folium.Icon(icon=\"bicycle\", prefix=\"fa\", color=clr),\n",
    "            ).add_to(fg_acl)\n",
    "        elif clr == \"purple\":\n",
    "            folium.Marker(\n",
    "                [row[\"latitude\"], row[\"longitude\"]],\n",
    "                popup=\"<b> Name:\" + cn + \"</b> <i> Status:\" + sts + \"</i>\",\n",
    "                icon=folium.Icon(icon=\"bicycle\", prefix=\"fa\", color=clr),\n",
    "            ).add_to(fg_mv)\n",
    "\n",
    "# add feature groups to the map\n",
    "map_obj.add_child(fg_act)\n",
    "map_obj.add_child(fg_acl)\n",
    "map_obj.add_child(fg_cls)\n",
    "map_obj.add_child(fg_mv)\n",
    "\n",
    "map_obj.add_child(folium.LatLngPopup())\n",
    "folium.map.LayerControl(\"topleft\", collapsed=False).add_to(map_obj)\n",
    "# view map\n",
    "map_obj"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Numerous geospatial functions exist, but we can demonstrate the basics by finding the distance from the main office (station_id = 1001) to other stations.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "For more geospatial functions supported by Teradata, please click <a href = 'https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Geospatial-Data-Types'>here</a>.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT\n",
    "    station.station_id, station.name, \n",
    "    station.latitude, station.longitude,\n",
    "    office.latitude as ofc_lat, \n",
    "    office.longitude as ofc_lon, \n",
    "    ROUND(office.location.ST_SphericalDistance(station.location), 0) Distance_Meters\n",
    "FROM DEMO_AustinBikeShare.stations station, DEMO_AustinBikeShare.stations office\n",
    "WHERE office.station_id = 1001\n",
    ";\n",
    "\"\"\"\n",
    "\n",
    "df_dist_frm_stn = DataFrame.from_query(query)\n",
    "df_dist_frm_stn.sort(\"station_id\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the below map, we can visualize the distance from the main office (station_id = 1001) to other stations. The centre point denotes the main station, and the length of the lines shows distances between the main office and station.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To view more details, hover over the stations which are showing details like Station ID, Name, and distance in meters</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define figure\n",
    "df_dist_frm_stn = df_dist_frm_stn.to_pandas()\n",
    "fig = go.Figure()\n",
    "\n",
    "# create colors list\n",
    "colors = [\n",
    "    \"aliceblue\",\n",
    "    \"gold\",\n",
    "    \"goldenrod\",\n",
    "    \"black\",\n",
    "    \"blanchedalmond\",\n",
    "    \"hotpink\",\n",
    "    \"indianred\",\n",
    "    \"indigo\",\n",
    "    \"blue\",\n",
    "    \"blueviolet\",\n",
    "    \"brown\",\n",
    "    \"burlywood\",\n",
    "    \"cadetblue\",\n",
    "    \"darkred\",\n",
    "    \"darksalmon\",\n",
    "    \"darkseagreen\",\n",
    "]\n",
    "\n",
    "for i in range(1, df_dist_frm_stn.shape[0]):\n",
    "    df_sub = df_dist_frm_stn[i : i + 1]\n",
    "    lats = df_sub.latitude.tolist() + df_sub.ofc_lat.tolist()\n",
    "    lons = df_sub.longitude.tolist() + df_sub.ofc_lon.tolist()\n",
    "\n",
    "    fig.add_trace(\n",
    "        go.Scattermapbox(\n",
    "            name=str(df_sub.station_id.values[0]),\n",
    "            mode=\"markers+lines\",\n",
    "            lat=lats,\n",
    "            lon=lons,\n",
    "            hoverinfo=\"text\",\n",
    "            hovertemplate=[\n",
    "                \"<b>Station ID:</b>:\"\n",
    "                + str(df_sub.iloc[i, 0])\n",
    "                + \"<br><i>Name</i>:\"\n",
    "                + str(df_sub.iloc[i, 1])\n",
    "                + \"<br><i>Dist (mt)</i>:\"\n",
    "                + str(df_sub.iloc[i, 6])\n",
    "                for i in range(df_sub.shape[0])\n",
    "            ],\n",
    "            marker={\"color\": colors[rand.randint(1, len(colors) - 1)], \"size\": 10},\n",
    "            opacity=0.8,\n",
    "        )\n",
    "    )\n",
    "\n",
    "\n",
    "fig.update_layout(\n",
    "    margin={\"l\": 0, \"t\": 0, \"b\": 0, \"r\": 0},\n",
    "    mapbox={\n",
    "        \"center\": {\"lon\": -97.73997, \"lat\": 30.27186},\n",
    "        \"style\": \"open-street-map\",\n",
    "        \"zoom\": 11.5,\n",
    "    },\n",
    "    height=600,\n",
    ")\n",
    "# view map\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now Let's see the most frequent trip routes on the map.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The code below defines a view that one group by the location of the start and end points of the trip and populates the data with the most frequently taken route by the users. Taking the top 10 routes here.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "query = '''\n",
    "REPLACE  VIEW trips_cnt as\n",
    "SELECT TOP 10 \n",
    "    t1.start_station_id, \n",
    "    t1.end_station_id, \n",
    "    count(*) as cnt1 \n",
    "FROM\n",
    "    DEMO_AustinBikeShare.trips AS t1\n",
    "GROUP BY 1,2\n",
    "HAVING t1.start_station_id <> t1.end_station_id\n",
    "ORDER BY cnt1 DESC\n",
    "'''\n",
    "\n",
    "execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "select \n",
    "    t.start_station_id,\n",
    "    t.end_station_id,\n",
    "    st.latitude as st_lat,\n",
    "    st.longitude as st_lon,\n",
    "    ed.latitude as ed_lat,\n",
    "    ed.longitude as ed_lon,\n",
    "    st.name as st_name,\n",
    "    ed.name as ed_name,\n",
    "    ROUND(st.location.ST_SphericalDistance(ed.location), 0) Distance_Meters,\n",
    "    cnt1 as trip_counts_bw_stns\n",
    "from demo_user.trips_cnt as t\n",
    "LEFT JOIN DEMO_AustinBikeShare.stations AS st ON t.start_station_id = st.station_id\n",
    "LEFT JOIN DEMO_AustinBikeShare.stations AS ed ON t.end_station_id = ed.station_id\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "df_most_freq_routes = DataFrame.from_query(query)\n",
    "df_most_freq_routes.sort(\"trip_counts_bw_stns\", ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above data suggests that the most frequently taken routes by the users are from <b>5th & Bowie to 4th & Congress</b>. It also concludes that most of these trips are between 600 meters and 1500 meters.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, let's visualise the above routes on the map.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load map centred on average coordinates\n",
    "df_most_freq_routes = df_most_freq_routes.to_pandas()\n",
    "my_map = map_obj = get_folium_map_obj(location=[30.26476, -97.74678], zoom_start=14.5)\n",
    "\n",
    "colors = [\n",
    "    \"red\",\n",
    "    \"goldenrod\",\n",
    "    \"black\",\n",
    "    \"blue\",\n",
    "    \"blueviolet\",\n",
    "    \"green\",\n",
    "    \"purple\",\n",
    "    \"darkslateblue\",\n",
    "    \"darkslategray\",\n",
    "    \"darkslategrey\",\n",
    "    \"darkturquoise\",\n",
    "    \"darkviolet\",\n",
    "    \"deeppink\",\n",
    "    \"deepskyblue\",\n",
    "]\n",
    "\n",
    "for idx, row in df_most_freq_routes.iterrows():\n",
    "    points = []\n",
    "    if not math.isnan(row[\"st_lat\"]) and not math.isnan(row[\"st_lon\"]):\n",
    "        points.append([row[\"st_lat\"], row[\"st_lon\"]])\n",
    "        points.append([row[\"ed_lat\"], row[\"ed_lon\"]])\n",
    "        strt = row[\"st_name\"]\n",
    "        end = row[\"ed_name\"]\n",
    "        clr = colors[rand.randint(1, len(colors) - 1)]\n",
    "        folium.Marker(\n",
    "            [row[\"st_lat\"], row[\"st_lon\"]],\n",
    "            popup=\"<b> Start:\" + strt + \"</b> <br> <i> End:\" + end + \"</i>\",\n",
    "            icon=folium.Icon(icon=\"bicycle\", prefix=\"fa\", color=\"green\"),\n",
    "        ).add_to(my_map)\n",
    "        folium.Marker(\n",
    "            [row[\"ed_lat\"], row[\"ed_lon\"]],\n",
    "            popup=\"<b> Start:\" + strt + \"</b> <br> <i> End:\" + end + \"</i>\",\n",
    "            icon=folium.Icon(icon=\"bicycle\", prefix=\"fa\", color=\"green\"),\n",
    "        ).add_to(my_map)\n",
    "        folium.PolyLine(points, color=clr, opacity=0.8).add_to(my_map)\n",
    "\n",
    "# view map\n",
    "my_map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above visualization suggests that Station <b>5th & Bowie and City Hall / Lavaca & 2nd</b> has the highest accessed stations as starting  and ending point respectively. Even though only ten trips originate from the highest accessed stations, it still has trip counts of more than 1500.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>3. Create and Explore Temporal, Geospatial and Time index data</b>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.1 Create a temporal table with weather data</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Temporal tables store and maintain information concerning time. Using temporal tables, Vantage can process statements and queries that include time-based reasoning. Temporal tables have one or two special columns which store time information:\n",
    "<ul style='font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>A transaction-time column records and maintains the period Vantage was aware of the information in the row. Vantage automatically enters and maintains the transaction-time column data and consequently tracks such information's history.</li>\n",
    "    <li>A valid-time column models the real-world and stores information such as the time an insurance policy or product warranty is valid, the length of employment of an employee, or other information that is important to track and manipulate in a time-aware fashion. When you add a new row to this type of table, you use the valid-time column to specify the time period for which the row information is valid. This is the period of validity (PV) of the information in the row.</li>\n",
    "</ul>\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "query = '''\n",
    "CREATE MULTISET TABLE weather_temporal (\n",
    "    begin_dt      TIMESTAMP(6) NOT NULL,\n",
    "    end_dt        TIMESTAMP(6) NOT NULL,\n",
    "    temp          FLOAT,\n",
    "    temp_min      FLOAT,\n",
    "    temp_max      FLOAT,\n",
    "    pressure      INTEGER,\n",
    "    humidity      INTEGER,\n",
    "    wind_speed    INTEGER,\n",
    "    wind_deg      INTEGER,\n",
    "    rain_1h       FLOAT,\n",
    "    rain_3h       FLOAT,\n",
    "    clouds        INTEGER,\n",
    "    weather_id    INTEGER,\n",
    "    weather_main  VARCHAR(50),\n",
    "    weather_desc  VARCHAR(50),\n",
    "    weather_icon  VARCHAR(50),\n",
    "    PERIOD FOR Weather_Duration(begin_dt,end_dt) AS VALIDTIME\n",
    ")\n",
    "PRIMARY INDEX (weather_id);'''\n",
    "\n",
    "execute_sql(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we are converting temp, temp_min, and temp_max from Kelvin to Fahrenheit while inserting the data into the weather_temporal table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "query = '''\n",
    "INSERT INTO weather_temporal\n",
    "SELECT\n",
    "    dt,\n",
    "    dt + INTERVAL '59' MINUTE + INTERVAL '59' SECOND,\n",
    "    round( ((temp - 273.15) * 9/5 + 32 ) ,0),\n",
    "    round( ((temp_min - 273.15) * 9/5 + 32 ) ,0),\n",
    "    round( ((temp_max - 273.15) * 9/5 + 32 ) ,0),\n",
    "    pressure,\n",
    "    humidity,\n",
    "    wind_speed,\n",
    "    wind_deg,\n",
    "    rain_1h,\n",
    "    rain_3h,\n",
    "    clouds,\n",
    "    weather_id,\n",
    "    weather_main,\n",
    "    weather_desc,\n",
    "    weather_icon\n",
    "FROM \n",
    "    DEMO_AustinBikeShare.weather;'''\n",
    "    \n",
    "execute_sql(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Sequenced temporal queries allow the extraction of the past, current, or future sequence of states of a temporal table. A query that is sequenced in valid time spans those rows with a period of validity that overlaps the period of applicability of the query. Additional conditions can be specified on the valid-time column to further filter the rows as required.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "For more detials on Sequenced Valid-Time functions supported by Teradata, please click <a href = 'https://docs.teradata.com/search/all?query=Sequenced+Valid-Time+Queries&content-lang=en-US'>here</a>.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SEQUENCED VALIDTIME SELECT * FROM weather_temporal SAMPLE 10;\n",
    "\"\"\"\n",
    "\n",
    "df_weather = DataFrame.from_query(query)\n",
    "df_weather"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now we can efficiently answer time-based reasoning queries faster and efficiently with Temporal tables. For example, was the weather favorable to biking in March and October 2016?</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The valid-time period is also known as the period of validity (PV) of the row. Valid-time columns are defined by specifying AS VALIDTIME in the column definition, and have a period data type with an element type of DATE or TIMESTAMP(n) (optionally including WITH TIME ZONE).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT\n",
    "    COUNT(weather_main) AS weather_hours, weather_main\n",
    "FROM (\n",
    "    VALIDTIME PERIOD '(2016-03-01, 2016-03-31)'\n",
    "    SELECT * FROM weather_temporal\n",
    ") AS dt\n",
    "GROUP BY weather_main;\n",
    "\"\"\"\n",
    "\n",
    "df_dur_wather_type_march = DataFrame.from_query(query)\n",
    "df_dur_wather_type_march"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_dur_wather_type_march = df_dur_wather_type_march.to_pandas()\n",
    "get_histogram(\n",
    "    df_dur_wather_type_march,\n",
    "    x=\"weather_main\",\n",
    "    y=\"weather_hours\",\n",
    "    title=\"Duration(in hours) of weather by weather type(for March 2016)\",\n",
    "    x_title=\"weather_main\",\n",
    "    y_title=\"weather_hours\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graphs suggest that March 2016 had more days favourable for biking(clear, cloudy, mist), hence explaining the increased number of bike rides.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT\n",
    "    COUNT(weather_main) AS weather_hours, weather_main\n",
    "FROM (\n",
    "        VALIDTIME PERIOD '(2016-10-01, 2016-10-30)'\n",
    "        SELECT * FROM weather_temporal\n",
    "    ) AS dt\n",
    "GROUP BY weather_main;\n",
    "\"\"\"\n",
    "df_dur_wather_type_oct = DataFrame.from_query(query)\n",
    "df_dur_wather_type_oct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_dur_wather_type_oct = df_dur_wather_type_oct.to_pandas()\n",
    "get_histogram(\n",
    "    df_dur_wather_type_oct,\n",
    "    x=\"weather_main\",\n",
    "    y=\"weather_hours\",\n",
    "    title=\"Duration(in hours) of weather by weather type(for October 2016)\",\n",
    "    x_title=\"weather_main\",\n",
    "    y_title=\"weather_hours\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graphs suggest that October 2016 had mostly 3 main weather conditions (clear, rain, and clouds), and out of these 2 weather conditions (clear and clouds), there were more days favourable for biking, hence the increased number of bike rides.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.2 Create a view for all trips with start/end stations data and a GEOSEQUENCE with start/end lat/long/time</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The code below defines a view which enhances the trip data with a Geosequence field containing the location and time for the start and end points of the trip.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As there are 0.6 million records in the trips table and we are using left joins twice, it would take a little bit of time to execute the query.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "query = '''\n",
    "REPLACE VIEW trips_geo AS\n",
    "SELECT\n",
    "    t.bikeid,\n",
    "    t.trip_ID,\n",
    "    t.subscriber_type,\n",
    "    t.start_station_id,\n",
    "    COALESCE(t.start_station_name, st.NAME) AS start_station_name,\n",
    "    t.start_time,\n",
    "    st.status starting_station_status,\n",
    "    t.end_station_id,\n",
    "    COALESCE(t.end_station_name, ed.NAME) AS end_station_name,\n",
    "    t.start_time \n",
    "        + CAST(t.duration_minutes/60 AS INTERVAL HOUR(4)) \n",
    "        + CAST(t.duration_minutes MOD 60 AS INTERVAL MINUTE(4)) AS end_time,\n",
    "    ed.status AS End_station_status,\n",
    "    t.duration_minutes,\n",
    "    NEW ST_GEOMETRY('ST_POINT' ,st.Longitude, st.Latitude) AS start_location,\n",
    "    NEW ST_GEOMETRY('ST_POINT' ,ed.Longitude, ed.Latitude) AS end_location,\n",
    "    CAST('GEOSEQUENCE( ('\n",
    "        || COALESCE(st.Longitude,-98.272797)\n",
    "        || ' '\n",
    "        || COALESCE(st.Latitude,30.578245)\n",
    "        || ','\n",
    "        || COALESCE(ed.longitude,-98.272797)\n",
    "        || ' '\n",
    "        || COALESCE(ed.latitude,30.578245)\n",
    "        || '), ('\n",
    "        || CAST(CAST(t.start_time AS FORMAT 'yyyy-mm-ddbhh:mi:ss') AS VARCHAR(50))\n",
    "        || ','\n",
    "        || CAST(CAST(end_time AS FORMAT 'yyyy-mm-ddbhh:mi:ss') AS VARCHAR(50))\n",
    "        || '), ('\n",
    "        || '1,2), (0) )' AS ST_GEOMETRY) AS GEOM\n",
    "FROM\n",
    "    DEMO_AustinBikeShare.trips AS t\n",
    "    LEFT JOIN DEMO_AustinBikeShare.stations AS st ON t.start_station_id = st.station_id\n",
    "    LEFT JOIN DEMO_AustinBikeShare.stations AS ed ON t.end_station_id = ed.station_id;'''\n",
    "\n",
    "execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT TOP 5 * FROM trips_geo;\"\"\"\n",
    "\n",
    "df_trips_geo = DataFrame.from_query(query)\n",
    "df_trips_geo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.3 Create a Time Index table of the trips to accelerate time related analysis</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Vantage supports tables with a Primary Time Index (PTI), which is used to store and quickly look up data that arrives based on time. This time-aware index distributes data across the units of parallelism. Still, it allows the optimizer to build plans which go directly to the unit of parallelism where the data is stored based on the time constraint.<br><br>\n",
    "In this case, we will declare the index to have hourly granularity with a baseline time earlier than any date of data we have. Based on the primary index declaration, the database automatically creates the first column with the name TD_TIMECODE. When we insert data, we will use the start_time column as that value.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "query = '''\n",
    "CREATE MULTISET TABLE trips_geo_pti (\n",
    "    bikeid                    INTEGER,\n",
    "    trip_id                   BIGINT,\n",
    "    subscriber_type           VARCHAR(50),\n",
    "    start_station_id          INTEGER,\n",
    "    start_station_name        VARCHAR(100),\n",
    "    starting_station_status   VARCHAR(50),\n",
    "    end_station_id            INTEGER,\n",
    "    end_station_name          VARCHAR(100),\n",
    "    end_time                  TIMESTAMP(6),\n",
    "    end_station_status        VARCHAR(50),\n",
    "    duration_minutes          INTEGER,\n",
    "    geom                      SYSUDTLIB.ST_GEOMETRY(16776192) INLINE LENGTH 9920\n",
    ")\n",
    "PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2013-12-20', MINUTES(60));'''\n",
    "\n",
    "execute_sql(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We now populate the local table.  This could take a minute to get data from the cloud storage.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "query = '''\n",
    "INSERT INTO trips_geo_pti\n",
    "SELECT\n",
    "    start_time,\n",
    "    bikeid,\n",
    "    trip_id,\n",
    "    subscriber_type,\n",
    "    start_station_id,\n",
    "    start_station_name,\n",
    "    starting_station_status,\n",
    "    end_station_id,\n",
    "    end_station_name,\n",
    "    end_time,\n",
    "    End_station_status,\n",
    "    duration_minutes,\n",
    "    geom\n",
    "FROM\n",
    "    trips_geo;'''\n",
    "    \n",
    "execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_trips_geo_pti = DataFrame(in_schema(\"demo_user\", \"trips_geo_pti\"))\n",
    "tdf_trips_geo_pti"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we are creating a new field geom from the start and end station latitude and longitude, along with a timestamp. In the above result, we are also calculating the duration in minutes from the start of the trips to the end of the trips.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.4 Augment trips data with weather data and extract geospatial information</b></p> \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Finally, we bring the data together with the geosequenced trip information with the available weather data, where the weather report period contains the trip's start time (TD_TIMECODE).</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "For more geospatial functions supported by Teradata, please click <a href = 'https://https://docs.teradata.com/r/Geospatial-Data-Types/June-2020'>here</a>.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "query = '''\n",
    "CREATE MULTISET TABLE trips_and_weather AS (\n",
    "    SELECT \n",
    "        t.start_station_name,\n",
    "        t.end_station_name,\n",
    "        t.bikeid,\n",
    "        t.trip_id,\n",
    "        t.subscriber_type as subscriber_type,\n",
    "        t.geom.GetInitT() AS pickup_time,\n",
    "        t.geom.GetFinalT() AS dropoff_time,\n",
    "        t.geom.ST_POINTN(1).ST_SPHEROIDALDISTANCE(geom.ST_POINTN(2))/1000 AS total_distance,\n",
    "        t.geom.ST_POINTN(1).ST_X() AS pickup_location_lon,\n",
    "        t.geom.ST_POINTN(1).ST_Y() AS pickup_location_lat,\n",
    "        t.geom.ST_POINTN(2).ST_X() AS dropoff_location_lon,\n",
    "        t.geom.ST_POINTN(2).ST_Y() AS dropoff_location_lat,        \n",
    "        t.duration_minutes,\n",
    "        t.TD_TIMECODE as Trip_TIMECODE,\n",
    "        wt.*\n",
    "    FROM \n",
    "        trips_geo_pti AS t\n",
    "        INNER JOIN Weather_temporal AS wt ON wt.weather_duration CONTAINS t.TD_TIMECODE\n",
    "        AND pickup_time >= '2017-07-01 00:00:00'\n",
    ")\n",
    "WITH DATA primary index(trip_id);'''\n",
    "\n",
    "execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT TOP 5 * FROM trips_and_weather WHERE CAST(pickup_time AS DATE) BETWEEN '2017-07-01' AND '2017-07-31'\n",
    "\"\"\"\n",
    "df_trips_weather = DataFrame.from_query(query)\n",
    "df_trips_weather"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>4. Insights</b>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.1 Average distance traveled w.r.t start stations</b></p>   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT\n",
    "    start_station_name, AVG(total_distance) avg_tot_dist, COUNT(trip_id) cnt_trips\n",
    "FROM trips_and_weather\n",
    "GROUP BY start_station_name\n",
    ";\"\"\"\n",
    "\n",
    "df_avg_dist = DataFrame.from_query(query)\n",
    "df_avg_dist.sort(\"cnt_trips\", ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above visualization suggests that Main Office has the highest average distance people travel. Even though only ten trips originate from the main station, it still has the highest average distance traveled. These ten trips are very long.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.2 Effect of weather on distance travelled</b></p>   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT\n",
    "    TOP 5 SUM(total_distance) AS distance_km, subscriber_type, weather_main\n",
    "FROM trips_and_weather\n",
    "GROUP BY subscriber_type, weather_main\n",
    ";\"\"\"\n",
    "\n",
    "df_tot_dist = DataFrame.from_query(query)\n",
    "df_tot_dist.sort(\"distance_km\", ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Looking at the results above, walk-up, local365, and local30 subscribers traveled more distance when the weather was clear or cloudy.</p>\n",
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.3 Average trip duration w.r.t subscriber type and trip type</b></p>   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT\n",
    "    subscriber_type,\n",
    "    CASE\n",
    "        WHEN start_station_name = end_station_name THEN 'Round_Trip'\n",
    "        ELSE 'Point-to-Point'\n",
    "    END AS trip_type,\n",
    "    AVG(duration_minutes) AS time_mins\n",
    "FROM trips_and_weather\n",
    "GROUP BY subscriber_type, trip_type\n",
    ";\"\"\"\n",
    "\n",
    "df_avg_duration = DataFrame.from_query(query)\n",
    "df_avg_duration.sort(\"time_mins\", ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Looking at the results above, round trips have longer trips than point-to-point for the explorer, walk up and annual members.</p>\n",
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.4 Does the bike require maintenance?</b></p>   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT\n",
    "    bikeid, COUNT(*) AS num_trips, sum(total_distance) AS distance,\n",
    "    CASE\n",
    "        WHEN distance > 70 THEN 'Recommended'\n",
    "        ELSE 'Not Required'\n",
    "    END AS maintenance\n",
    "FROM trips_and_weather\n",
    "GROUP BY bikeid\n",
    "; \"\"\"\n",
    "\n",
    "df_maintenance = DataFrame.from_query(query)\n",
    "df_maintenance.sort(\"distance\", ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_maintenance = df_maintenance.to_pandas()\n",
    "get_histogram(\n",
    "    df_maintenance.groupby([\"maintenance\"]).size().reset_index(name=\"counts\"),\n",
    "    x=\"maintenance\",\n",
    "    y=\"counts\",\n",
    "    title=\"Maintenance Required?\",\n",
    "    x_title=\"maintenance\",\n",
    "    y_title=\"counts\",\n",
    "    width=600,\n",
    "    height=500,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Looking at the results above, 50 bikes require maintenance according to our assumption that we should do bike repairs after every 70 kms.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>5. Clean up</b>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>5.1 Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Cleanup work tables to prevent errors next time. This section drops all the tables created during the demonstration.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = [\"weather_temporal\", \"trips_geo_pti\", \"trips_and_weather\", \"trips_geo\"]\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, schema_name=\"demo_user\")\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>5.2 Databases and Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code will clean up tables and databases created above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_AustinBikeShare');\" "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233c'><b>Links:</b></p>\n",
    "<ul style='font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Information about Geospatial datatype can be found <a href = 'https://docs.teradata.com/search/all?query=geospatial&content-lang=en-US'>here</a></li>\n",
    "    <li>Information about Temporal datatype can be found <a href = 'https://docs.teradata.com/search/all?query=temporal&content-lang=en-US'>here</a></li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "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. 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"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}