Getting_Started/Charting_and_Visualization/InDB_Visualizations_using_teradataml.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "9768d9c1",
   "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",
    "       InDB Visualizations using teradataml\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": "8509538e",
   "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'>\n",
    "Visualization is the process of representing data or information in graphical or visual formats such as charts, graphs, maps, and dashboards. The goal of visualization is to present complex data in a way that is easy to understand, allowing viewers to quickly grasp insights, patterns, and trends.<br>Visualizations leverage the human brain's ability to process visual information more efficiently than textual or numerical data alone. By encoding data into visual elements such as points, lines, bars, colors, and shapes, visualizations enable users to explore and interpret data intuitively.</p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Business Values</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Explore data patterns intuitively.</li>\n",
    "    <li>Communicate complex information effectively.</li>\n",
    "    <li>Facilitate decision-making processes.</li>\n",
    "    <li>Provide insights into trends and outliers.</li>\n",
    "    <li>Engage stakeholders in data-driven discussions.</li>\n",
    " </ul>\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'>\n",
    "Enterprises often grapple with vast volumes of data, which can pose challenges when it comes to scaling up data visualization efforts. These challenges often manifest in issues like slow performance and difficulties in processing and interpreting large datasets. ClearScape Analytics's specialized methods \"td_plot\" offer solutions tailored to address these specific challenges.\n",
    "<br>The \"td_plot\" method, streamlines large-scale visualization tasks by providing users with efficient tools to create visualizations directly within the Vantage platform. By eliminating the need for data movement, \"td_plot\" enhances efficiency and effectively tackles the hurdles associated with handling extensive datasets. This approach not only simplifies the visualization process but also ensures that insights can be gleaned swiftly without compromising on speed or performance. <br>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "In this functional demonstration, we will see all the visualizations possible in Clearscape Analytics.\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Simple Plot</b>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>    \n",
    "    <li>line plot</li>\n",
    "    <li>bar plot</li>\n",
    "    <li>scatter plot</li>\n",
    "    <li>geometry plot</li>\n",
    "    <li>correlation plot</li>\n",
    "    <li>wiggle plot</li>\n",
    "    <li>mesh plot</li>\n",
    "    </ul>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Combine multiple plots</b>    \n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'> \n",
    "    <li>Composite plot</li>\n",
    "    <li>Subplot</li>\n",
    "    </ul>\n",
    "</p>    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "135be242",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Connect to Vantage</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b1ed3d2-7504-472f-8617-038a45b0f7da",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the section, we import the required libraries.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "80d5cc3b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import.\n",
    "import os\n",
    "import getpass\n",
    "\n",
    "import warnings\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",
    "\n",
    "from teradataml import *"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6efdb50-e960-4a9a-84f5-2cb570af6f63",
   "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.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c1e2fd1c-7caa-4c4d-9063-2e82bc81aced",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ~/JupyterLabRoot/UseCases/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": "2a22596b-3e87-4a46-98fc-f3a84ec59176",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=InDB_Visualizations_using_teradataml.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "822951b1-95ff-4d83-b0c2-d9b07408f3d3",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b>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>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5dfa9f4d-685c-46a5-9ac3-2911e72c315a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ~/JupyterLabRoot/UseCases/run_procedure.py \"call get_data('DEMO_Plot_local');\"\n",
    "# takes about 50 seconds, estimated space: 2 MB\n",
    "# %run -i ~/JupyterLabRoot/UseCases/run_procedure.py \"call get_data('DEMO_Plot_cloud');\"\n",
    "# takes about 30 seconds, estimated space: 0 MB"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4aaf099-9777-44a7-bd68-3520b7a76491",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Optional step – We should execute the below step only if we want to see the status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62f46bc9-35a2-4a8a-ac1c-e4377de4c68e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ~/JupyterLabRoot/UseCases/run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b6566a8",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Line Plot</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A line plot is a visualization that connects data points with straight lines, commonly used to depict trends or relationships between two continuous variables. It's particularly useful for showing changes over time or across ordered categories, providing a clear and intuitive representation of the data's behavior.<br>Let us take a look how we can create a line plot in Vantage. We will visualize a company's stock price over the period.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ba40746-7af0-4fb6-b37c-9cc8c5836d0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = DataFrame(in_schema(\"DEMO_Plot\", \"Stock_data\"))\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8809ad2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "plot = df1.plot(x=df1.period,\n",
    "                y=df1.stockprice,\n",
    "                title=\"Stock price over the period\",)\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d683887",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Bar Plot</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A bar plot is a visualization that represents categorical data with rectangular bars, where the length or height of each bar corresponds to the frequency, count, or proportion of the category it represents. It's commonly used to compare values between different categories, making it easy to identify patterns, trends, and differences within the data.<br>Let us create a bar plot to visualize the rate of change in inflation for a country over 10 years.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13272449-ebe6-4e9c-8f59-871128689957",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = DataFrame(in_schema(\"DEMO_Plot\", \"Inflation\"))\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b7f59e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df2[df2.countryid==1]\n",
    "df2.plot(x=df2.year_recorded, \n",
    "        y=df2.inflation_rate, \n",
    "        kind=\"bar\",\n",
    "        title=\"Change in inflation over 10 years\",\n",
    "        color = \"orange\",\n",
    "        xlabel=\"Year\",\n",
    "        ylabel=\"Inflation Rate\",\n",
    "        grid_linestyle=\"-\",\n",
    "        grid_linewidth= 0.5 \n",
    "        )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "390f648b",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Scatter Plot</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A scatter plot is a visualization that displays individual data points as dots on a two-dimensional coordinate system, with one variable plotted on the x-axis and another variable plotted on the y-axis. It is commonly used to show the relationship or correlation between two continuous variables, allowing for the identification of patterns, trends, clusters, or outliers within the data. Scatter plots are valuable for visualizing the distribution and association between variables.<br>Let us create a scatter plot to visualize the Blood Pressure for different ages.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a29f44e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = DataFrame(in_schema(\"DEMO_Plot\", \"AgeandPressure\"))\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b5237e87",
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.plot(x=df3.age, \n",
    "        y=df3.blood_pressure, \n",
    "        kind=\"scatter\",\n",
    "        color=\"red\", \n",
    "        grid_color='grey',\n",
    "        xlabel='Age', \n",
    "        ylabel='Blood Pressure',\n",
    "        grid_linestyle=\"-\",\n",
    "        grid_linewidth= 0.5, \n",
    "        marker=\"o\",\n",
    "        markersize=7,\n",
    "        title=\"Blood Pressure for different Ages\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d39a14fa",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Geometry Plot</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Geometry plot is a plot generated on GeoSpatial data or Geometry data, which is the geometry column in teradataml GeoDataFrame. Only the columns with ST_GEOMETRY type are allowed for generating geometry plot.<br>Let us create a geometry plot to visualize the population density for all the states across US in year 1990.<br>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>Data source:\n",
    "    <li>Shapes of US states are generated from Free Blank United States Map in SVG - Resources | Simplemaps.com</li>\n",
    "    <li>Population data is accessed from Historical Population Change Data (1910-2020) (census.gov)</li>\n",
    "    </ul></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a19cf8d9-9dc0-4bc2-a9c4-f8b8659abf6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "us_population = DataFrame(in_schema(\"DEMO_Plot\", \"US_Population\"))\n",
    "us_population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e02efad8",
   "metadata": {},
   "outputs": [],
   "source": [
    "us_states_shapes = GeoDataFrame(in_schema(\"DEMO_Plot\", \"US_States_Shapes\"))\n",
    "us_states_shapes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "306f3b4d-f5a7-4eb6-a29d-13d27097c5c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "us_states_shapes.tdtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64d1359f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join shapes with population and filter only 1990 data.\n",
    "population_data = us_states_shapes.join(us_population,\n",
    "                                        on=us_population.state_name == us_states_shapes.state_name,\n",
    "                                        lprefix=\"us\",\n",
    "                                        rprefix=\"t2\")\n",
    "population_data = population_data.select([\"us_state_name\", \"state_shape\", \"population_year\", \"population\"])\n",
    "df4 = population_data[population_data.population_year == 1990]\n",
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3766fd69",
   "metadata": {},
   "outputs": [],
   "source": [
    "figure = Figure(width=1550, height=860)\n",
    "# Set heading for Figure.\n",
    "figure.heading = \"Geometry Plot\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "87e1c551",
   "metadata": {},
   "outputs": [],
   "source": [
    "plot_1990 = df4.plot(y=(df4.population, df4.state_shape),\n",
    "                       cmap='rainbow',\n",
    "                       figure=figure,\n",
    "                       reverse_yaxis=True,\n",
    "                       title=\"Population Density in US for the year 1990\",\n",
    "                       xlabel=\"\",\n",
    "                       ylabel=\"\")\n",
    "plot_1990.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7873a11",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Correlation Plot</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A correlation plot visualizes the pairwise relationships between variables in a dataset, typically displayed as a matrix of correlation coefficients. It helps identify patterns of association, showing the strength and direction of linear relationships between variables at a glance.<br>Let us create a correlation plot to visualize the correlation of one variable on the output value.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4041222f-80cc-4638-b926-4119240400d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df5 = DataFrame(in_schema(\"DEMO_Plot\", \"ACF\"))\n",
    "df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56f18c9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df5.plot(x=df5.ROW_I, \n",
    "        y=(df5.OUT_v, df5.CONF_OFF_v),\n",
    "        kind='corr', \n",
    "        color=\"orange\",\n",
    "        xlabel=\"Row_Id\", \n",
    "        ylabel=\"computed_autocorelation_confidence_bands\",\n",
    "        title=\"ACF Plot\"\n",
    "        )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "461db1a9",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Wiggle Plot</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A wiggle plot is a type of visualization used in geology and seismic exploration to represent subsurface structures and seismic data. It displays seismic waveforms vertically, with each trace shifted horizontally relative to the previous one, giving a \"wiggling\" appearance. This technique helps geologists interpret subsurface features and identify geological formations, faults, and other structural elements.<br>Let us create a wiggle plot on sample wavelet data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e42d01aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "df6 = DataFrame(in_schema(\"DEMO_Plot\", \"Wavelet\"))\n",
    "df6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "473b527f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df6.plot(x=df6.x, y=df6.y, scale=df6.c, kind='wiggle')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd604cd6",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Mesh Plot</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A Mesh plot in Vantage can be used to display matrix in an image form.<br>Let us create a mesh plot on sample wavelet data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7bb8f3d-d05c-4041-97bd-6050df01c671",
   "metadata": {},
   "outputs": [],
   "source": [
    "df6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6d1e136-6727-45a4-972b-47716cd8194c",
   "metadata": {},
   "outputs": [],
   "source": [
    "plot = df6.plot(x=df6.x,\n",
    "               y=df6.y,\n",
    "               scale=df6.c,\n",
    "               kind='mesh',\n",
    "               cmap='matter',\n",
    "               vmin=-0.5,\n",
    "               vmax=0.5)\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "119a88a1",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>8. Combining multiple Plots - Composite plot</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>With ClearScape Analytic's plot method we can combine multiple plot in a single image. A composite plot is a visualization that combines multiple individual plots or charts into a single cohesive display. It allows for the simultaneous presentation of different types of data or multiple perspectives on the same dataset.<br>Let us create a composite plot for comparing domestic passengers vs international passengers for an airline company.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "07d13e2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df7 = DataFrame(in_schema(\"DEMO_Plot\", \"US_Air_Pass\"))\n",
    "df7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d9c29203",
   "metadata": {},
   "outputs": [],
   "source": [
    "df7.plot(x=df7.TD_TIMECODE, \n",
    "         y=[df7.international, df7.domestic],\n",
    "         title=\"Domestic passengers vs International passengers\",\n",
    "         xlabel=\"year-month\",\n",
    "         ylabel=\"passenger count in million\"         \n",
    "        )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85d169b3",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>9. Combining multiple Plots - Subplot</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A subplot is a smaller plot or chart that is embedded within a larger plot or visualization, typically organized in a grid-like layout. Subplots allow for the simultaneous display of multiple views or aspects of the data within the same figure. They are commonly used to compare different datasets, highlight specific features, or present related information side by side, enhancing the overall clarity and comprehensiveness of the visualization.<br>Let us create subplots for changes in population density in US across four decades.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2020d4a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import subplots\n",
    "fig, axis = subplots(2, 2)\n",
    "fig.height = 1200\n",
    "fig.heading = \"Change in population density in US across four decades.\"\n",
    "axis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e3cb8fe6",
   "metadata": {},
   "outputs": [],
   "source": [
    "us_population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "533d1bc6",
   "metadata": {},
   "outputs": [],
   "source": [
    "us_states_shapes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f724f75a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join shapes with population and filter only 1990 data.\n",
    "population_data = us_states_shapes.join(us_population,\n",
    "                                        on=[\"state_name\"],\n",
    "                                        lprefix=\"us\",\n",
    "                                        rprefix=\"t2\")\n",
    "population_data = population_data.select([\"us_state_name\", \"state_shape\", \"population_year\", \"population\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24e08afb-b8eb-4c99-a2f2-bbaf131355e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "population_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "54841628",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find out the minimum and maximum population. This helps in coloring the plot.\n",
    "population_data.assign(min_population=population_data.population.min(), max_population=population_data.population.max(), drop_columns=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "98a66ba8",
   "metadata": {},
   "outputs": [],
   "source": [
    "population_data_2020 = population_data[population_data.population_year == 2020]\n",
    "population_data_2010 = population_data[population_data.population_year == 2010]\n",
    "population_data_2000 = population_data[population_data.population_year == 2000]\n",
    "population_data_1990 = population_data[population_data.population_year == 1990]\n",
    "\n",
    "# Generate subplot.\n",
    "# Plot population_data_1990 on first axis.\n",
    "plot_1990 = population_data_1990.plot(y=(population_data_1990.population, population_data_1990.state_shape),\n",
    "                                      cmap='rainbow',\n",
    "                                      figure=fig,\n",
    "                                      ax=axis[0],\n",
    "                                      reverse_yaxis=True,\n",
    "                                      vmin=55036.0,\n",
    "                                      vmax=39538223.0,\n",
    "                                      title=\"US 1990 Population\",\n",
    "                                      xlabel=\"\",\n",
    "                                      ylabel=\"\")\n",
    "\n",
    "# Plot population_data_2000 on second axis.\n",
    "plot_2000 = population_data_2000.plot(y=(population_data_2000.population, population_data_2000.state_shape),\n",
    "                                      cmap='rainbow',\n",
    "                                      figure=fig,\n",
    "                                      ax=axis[1],\n",
    "                                      reverse_yaxis=True,\n",
    "                                      vmin=55036.0,\n",
    "                                      vmax=39538223.0,\n",
    "                                      title=\"US 2000 Population\",\n",
    "                                      xlabel=\"\",\n",
    "                                      ylabel=\"\")\n",
    "\n",
    "# Plot population_data_2010 on third axis.\n",
    "plot_2010 = population_data_2010.plot(x=population_data_2010.population_year,\n",
    "                                      y=(population_data_2010.population, population_data_2010.state_shape),\n",
    "                                      cmap='rainbow',\n",
    "                                      figure=fig,\n",
    "                                      ax=axis[2],\n",
    "                                      reverse_yaxis=True,\n",
    "                                      vmin=55036.0,\n",
    "                                      vmax=39538223.0,\n",
    "                                      title=\"US 2010 Population\",\n",
    "                                      xlabel=\"\",\n",
    "                                      ylabel=\"\",\n",
    "                                      xtick_values_format=\"\")\n",
    "\n",
    "# Plot population_data_2020 on fourth axis.\n",
    "plot = population_data_2020.plot(x=population_data_2020.population_year,\n",
    "                                 y=(population_data_2020.population, population_data_2020.state_shape),\n",
    "                                 cmap='rainbow',\n",
    "                                 figure=fig,\n",
    "                                 ax=axis[3],\n",
    "                                 reverse_yaxis=True,\n",
    "                                 vmin=55036.0,\n",
    "                                 vmax=39538223.0,\n",
    "                                 title=\"US 2020 Population\",\n",
    "                                 xlabel=\"\",\n",
    "                                 ylabel=\"\",\n",
    "                                 xtick_values_format=\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3d65e22",
   "metadata": {},
   "outputs": [],
   "source": [
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52d1812e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us look at one more example to create subplots showcasing the the company performance across different quarters and years.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a588fa1-8e9e-4163-831c-ca64a39eb60c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df8 = DataFrame(in_schema(\"DEMO_Plot\", \"Finance_Data\"))\n",
    "df8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a0b3213",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradatasqlalchemy import DATE\n",
    "from teradataml.dataframe.sql import case_when\n",
    "c = case_when((df8.period.right(2).expression == 'q1', df8.period.left(4).expression+\"-01-01\"), \n",
    "              (df8.period.right(2).expression == 'q2', df8.period.left(4).expression+\"-04-01\"),\n",
    "              (df8.period.right(2).expression == 'q3', df8.period.left(4).expression+\"-07-01\"),\n",
    "              (df8.period.right(2).expression == 'q4', df8.period.left(4).expression+\"-10-01\"))\n",
    "df8.assign(investment_date=c.cast(DATE()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "207875b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import func\n",
    "df8=df8.assign(investment_date=c.cast(DATE()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "addb3283",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1980 = df8[(df8.id==3) & (func.to_char(df8.investment_date.expression, 'YYYY') == '1980')].select([\"investment_date\", \"investment\", \"expenditure\", \"income\"])\n",
    "df_1981 = df8[(df8.id==3) & (func.to_char(df8.investment_date.expression, 'YYYY') == '1981')].select([\"investment_date\", \"investment\", \"expenditure\", \"income\"])\n",
    "df_1982 = df8[(df8.id==3) & (func.to_char(df8.investment_date.expression, 'YYYY') == '1982')].select([\"investment_date\", \"investment\", \"expenditure\", \"income\"])\n",
    "df_all = df8[(df8.id==3) & ((func.to_char(df8.investment_date.expression, 'YYYY') == '1980') | \n",
    "                         (func.to_char(df8.investment_date.expression, 'YYYY') == '1981') | \n",
    "                         (func.to_char(df8.investment_date.expression, 'YYYY') == '1982'))].select([\"investment_date\", \"investment\", \"expenditure\", \"income\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a820ba6",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, axes = subplots(grid={(1, 1): (1,1), (1, 2): (1, 1), (1, 3): (1, 1), (2, 1): (1, 3)})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37dd4175",
   "metadata": {},
   "outputs": [],
   "source": [
    "axes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d14a399",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot 1980 data at first Axis.\n",
    "plot = df_1980.plot(x=df_1980.investment_date, \n",
    "                    y=[df_1980.investment, df_1980.expenditure, df_1980.income],\n",
    "                    kind=\"bar\",\n",
    "                    title=\"Financial overview of the company for all quarters in year 1980\",\n",
    "                    legend=[\"Investment\", \"Expenditure\", \"Income\"],\n",
    "                    xlabel=\"investment_month\",\n",
    "                    xtick_format='MM',\n",
    "                    figure=fig,\n",
    "                    ax=axes[0])\n",
    "\n",
    "# Plot 1981 data at second Axis.\n",
    "plot = df_1981.plot(x=df_1981.investment_date, \n",
    "                    y=[df_1981.investment, df_1981.expenditure, df_1981.income],\n",
    "                    kind=\"bar\",\n",
    "                    title=\"Financial overview of the company for all quarters in year 1981\",\n",
    "                    legend=[\"Investment\", \"Expenditure\", \"Income\"],\n",
    "                    xlabel=\"investment_month\",\n",
    "                    xtick_format='MM',\n",
    "                    figure=fig,\n",
    "                    ax=axes[1])\n",
    "\n",
    "# Plot 1982 data at third Axis.\n",
    "plot = df_1982.plot(x=df_1982.investment_date, \n",
    "                    y=[df_1982.investment, df_1982.expenditure, df_1982.income],\n",
    "                       kind=\"bar\",\n",
    "                       title=\"Financial overview of the company for all quarters in year 1982\",\n",
    "                       legend=[\"Investment\", \"Expenditure\", \"Income\"],\n",
    "                       xlabel=\"investment_month\",\n",
    "                       xtick_format='MM',\n",
    "                       figure=fig,\n",
    "                       ax=axes[2])\n",
    "\n",
    "# Plot all 3 years of data at third Axis.\n",
    "plot = df_all.plot(x=df_all.investment_date, \n",
    "                   y=[df_all.investment, df_all.expenditure, df_all.income],\n",
    "                   kind=\"line\",\n",
    "                   title=\"Financial overview of the company for all 3 years\",\n",
    "                   legend=[\"Investment\", \"Expenditure\", \"Income\"],\n",
    "                   figure=fig,\n",
    "                   ax=axes[3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b40563eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "plot.figure.heading = \"Financial overview of a company\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8760795c",
   "metadata": {},
   "outputs": [],
   "source": [
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3fbe5690-e180-4ba3-ac18-f42b1c1a1fd0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Conclusion</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this functional demo we have seen how we plot various plots InDB in Vantage using ClearScape Analytics without moving the data outside of the database. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8f76960",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>10. Cleanup</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6338a844-9a2c-46b7-9498-88458a5096ba",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C;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": "4093bc73-f107-4a11-a89b-14a8e1a57303",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ~/JupyterLabRoot/UseCases/run_procedure.py \"call remove_data('DEMO_Plot');\" \n",
    "#Takes 10 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb4a0196-5acf-43e3-9d4c-e960261a11ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77352127-6183-4756-8dce-1587c6d09a13",
   "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 - 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"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}