UseCases/Sensor_Data_Analytics/Sensor_Data_Analytics_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",
    "       Indoor Sensors\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>\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>People often think of GeoSpatial as related to latitude, longitude and occasionally altitude, but it can also be applied to locations within a space such as a building. When you apply a time dimension to location,  you get “4D Analytics”.  In this demo, we will be using data from 54 building sensor locations at the Intel Berkeley Research lab. They produce an average of 68,000 readings per day over the month used in this demo. We will not only analyze the temperature, humidity, and light but also the connectivity of the sensors to each other and voltage readings.</p> \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The complexity of the analysis is that the sensors produce information approximately every 31 seconds, but not all sensors report at exactly the same time and some sensors will have occasional outages during the day due to communications or voltage issues.  This type of irregular data presence happens in multiple industries. Examples: a retail POS system may be late in communicating due to store system issues or warehouse inventories may be taken at irregular intervals. For this complexity, we will use Vantage Time Series Analytics capabilities.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This sensor data is an example of high volume, IoT data that is best left on low cost cloud storage.  When running this demo in the ClearScape Analytics Experience, the Vantage Database running on Google Cloud is accessing IoT data that is stored on AWS S3.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will connect to Vantage and explore a sensor dataset provided by the <b>Intel Berkeley Research Lab</b>.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "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",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We start by importing the required libraries and connecting to the database.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide_input": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import getpass\n",
    "import networkx as nx\n",
    "\n",
    "\n",
    "from teradataml import *\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "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 use down arrow to go to next cell. Begin running steps with Shift + Enter keys.</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=Sensor_Data_Analytics_PY_SQL.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Getting Data for This Demo </b></p>\n",
    "\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>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call get_data('DEMO_IndoorSensor_cloud');\"\n",
    " # Takes about 20 seconds\n",
    "# %run -i ../run_procedure.py \"call get_data('DEMO_IndoorSensor_local');\" \n",
    "# Takes about 40 seconds"
   ]
  },
  {
   "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",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Check sensor locations</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have the lab sensor locations already loaded into Vantage, so load them into a teradataml DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mote_x_y = DataFrame(in_schema('DEMO_IndoorSensor', 'sensor_locations'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Plot Sensor Locations in 2D Floor Plan</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's make sure our data matches the Intel provided map of the sensor locations as below:</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b> Sensor Location Map</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"images/intel_lab_1.png\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note default plot origin(0,0) is bottom left, while in the Intel lab diagram, origin is top right. Therefore, we need to flip the origin when plotting.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''create multiset table sensor_new_locations as (select id, 41-x x, 32-y y from \"DEMO_IndoorSensor\".\"sensor_locations\") with data;'''\n",
    "\n",
    "\n",
    "try:\n",
    "    execute_sql(qry)\n",
    "except:\n",
    "    db_drop_table('sensor_new_locations')\n",
    "    execute_sql(qry)\n",
    "\n",
    "df=DataFrame.from_query('SELECT Top 5 * FROM sensor_new_locations;')\n",
    "df.sort(['x','y'], ascending= True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mote_x_y = DataFrame('sensor_new_locations')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "code_folding": [
     2
    ]
   },
   "outputs": [],
   "source": [
    "mote_x_y_pandas = mote_x_y.to_pandas() #.reset_index()   # Get a pandas DataFrame for plotting\n",
    "\n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "for x, y, z in zip(mote_x_y_pandas.x, mote_x_y_pandas.y, mote_x_y_pandas.id):\n",
    "    ax.annotate(z, (x, y))\n",
    "\n",
    "ax.scatter(mote_x_y_pandas.x, mote_x_y_pandas.y)\n",
    " \n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Looks good! Now let's explore the connectivity between the sensors...</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Check Sensor Communications</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have the connectivity between the sensors also loaded into Vantage:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connectivity = DataFrame(in_schema('DEMO_IndoorSensor', 'connectivity'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connectivity_pandas = connectivity.to_pandas().reset_index()\n",
    "connectivity_pandas.sort_values(by='reachprob', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Clean up and remove some of the null data</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connectivity_clean = connectivity_pandas.query('sendid != 0')\n",
    "len(connectivity_clean.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connectivity_clean = connectivity_clean.query('receiveid != 0')\n",
    "len(connectivity_clean.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connectivity_clean = connectivity_clean.query('reachprob != 0')\n",
    "len(connectivity_clean.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Visualize Sensor Communications</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Build connectivity network:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DG = nx.DiGraph()\n",
    "DG.add_weighted_edges_from(zip(connectivity_clean.sendid, connectivity_clean.receiveid, connectivity_clean.reachprob))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define node positions data structure (dict) for plotting, nodes positions shown as sensor locations.\n",
    "pos = mote_x_y_pandas.set_index('id').T.to_dict('list')\n",
    "\n",
    "# Preview of node_positions with a bit of hack (there is no head/slice method for dictionaries).\n",
    "dict(list(pos.items())[0:5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Use reachprob as weight, which is the probability of sensorA's msg sent to sensorB.</b></p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'> \n",
    "    <li>reachprob>0.5 shown in red</li>\n",
    "    <li>0.5>=reachprob>0.1 shown in blue</li>\n",
    "    <li>0.1>=reachprob shown in green</li> \n",
    "</ol>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Node size reflects the sensor's capability of sending/receiving messages from other sensors. Bigger node size implies better communication.</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Below code creates a graph where Nodes are Sensors and edges are assigned according to the strength of the signal(reachprob).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "code_folding": [
     8,
     12,
     14,
     16
    ],
    "hide_input": false
   },
   "outputs": [],
   "source": [
    "plt.figure(figsize=(16, 9))\n",
    "\n",
    "elarge = [(u, v) for (u, v, d) in DG.edges(data=True) if d['weight'] > 0.5]\n",
    "esmall1 = [(u, v) for (u, v, d) in DG.edges(data=True) if ((d['weight'] > 0.1) and (d['weight'] <= 0.5))]\n",
    "esmall2 = [(u, v) for (u, v, d) in DG.edges(data=True) if d['weight'] <= 0.1]\n",
    "\n",
    "# nodes\n",
    "d = dict(DG.degree(weight='weight'))\n",
    "nx.draw_networkx_nodes(DG, pos, nodelist=d.keys(), node_size=[ v*100 for v in d.values()], \n",
    "                       node_color='white', edgecolors='black')\n",
    "\n",
    "# edges\n",
    "nx.draw_networkx_edges(DG, pos, edgelist=elarge, arrowstyle=\"->\", arrowsize=1,width=0.5, edge_color='r')\n",
    "nx.draw_networkx_edges(DG, pos, edgelist=esmall1, arrowstyle=\"->\", arrowsize=1,\n",
    "                       width=0.2, alpha=0.5, edge_color='b', style='dashed')\n",
    "nx.draw_networkx_edges(DG, pos, edgelist=esmall2, arrowstyle=\"->\", arrowsize=1,\n",
    "                       width=0.05, alpha=0.5, edge_color='g', style='dashed')\n",
    "\n",
    "# labels\n",
    "nx.draw_networkx_labels(DG, pos, font_size=15, font_family='sans-serif')\n",
    "\n",
    "plt.axis('off')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Get Sensor Readings Data from the Object Store</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Sensors and other IOT devices generate <b>a lot</b> of data. In our case the sensor data is located in an object store because of the cost/performance tradeoff and resilience it provides.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> We will do the following steps to get data from Object Store</p>\n",
    "<li style = 'font-size:16px;font-family:Arial'>Define a schema providing the column names and the delimiter.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial'>Create a foreign table using the schema.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial'>Define a view over the foreign table  which defines the data types and formats of the columns in the CSV data.</li> \n",
    "<li style = 'font-size:16px;font-family:Arial'>Define a view that joins the view above to the sensor locations to eliminate data that is not in the lab being analyzed.</li> \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:14px;font-family:Arial'><b>Create the Foreign Table</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now create a Foreign Table to allow easy access to the raw sensor data:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query='''CREATE MULTISET FOREIGN TABLE sensors_table ,FALLBACK ,\n",
    "     MAP = TD_MAP1\n",
    "     (\n",
    "      Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,\n",
    "      sensdate DATE FORMAT 'YYYY-MM-DD',\n",
    "      senstime TIME(6),\n",
    "      epoch BIGINT,\n",
    "      moteid INTEGER,\n",
    "      temperature FLOAT,\n",
    "      humidity FLOAT,\n",
    "      light FLOAT,\n",
    "      voltage FLOAT,\n",
    "      sensdatetime TIMESTAMP(6))\n",
    "USING\n",
    "(\n",
    "      location  ('/s3/s3.amazonaws.com/clearscape-analytics-datasets/sensor_readings/')\n",
    "      MANIFEST  ('FALSE')\n",
    "      PATHPATTERN  ('$var1/$var2')\n",
    "      STOREDAS  ('PARQUET')\n",
    ")\n",
    ";'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('sensors_table')\n",
    "    execute_sql(query)                  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here is what the raw data looks like - note each row is coming back into a single 'Payload' column, we will address this in the next step...</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sensors_table = DataFrame('sensors_table');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Take a sample of a few rows (10):</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sensors_table.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Create a View for Easy Access</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Create a view on top of the raw data that puts structure and proper datatypes on the sensor data:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "execute_sql('''REPLACE VIEW sensor_readings\n",
    "  AS \n",
    "    (SELECT\n",
    "      CAST(\"sensdate\" AS DATE ) sensdate,\n",
    "      CAST(\"senstime\" AS TIME(6)) senstime,\n",
    "      CAST(epoch AS BIGINT) epoch,\n",
    "      CAST(moteid AS INTEGER) moteid,\n",
    "      CAST(temperature AS FLOAT) ( FORMAT '-ZZZ9.99') temperature,\n",
    "      CAST(humidity AS FLOAT) ( FORMAT '-ZZZ9.99') humidity,\n",
    "      CAST(light AS FLOAT) ( FORMAT '-ZZZ9.99') light,\n",
    "      CAST(voltage AS FLOAT) ( FORMAT '-ZZZ9.99') voltage,\n",
    "      cast(cast(\"sensdate\" as varchar(10)) || ' ' || cast(\"senstime\" as varchar(15)) AS TIMESTAMP(6)) sensdatetime\n",
    "  FROM sensors_table);''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now take a look at the formatted data:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sensor_readings = DataFrame('sensor_readings');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sensor_readings.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Final View - Clean Up Data and Join</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Clean up the data a little - limit the sensorid (moteid) to 54 because there are supposed to only be 54 sensors.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We also want to join with our local data table that gives us the location of each sensor:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "execute_sql('''REPLACE VIEW sensor_clean AS ( \n",
    "    SELECT * FROM sensor_readings sr \n",
    "    LEFT JOIN DEMO_IndoorSensor.sensor_locations sl\n",
    "    ON sr.moteid = sl.id\n",
    "    WHERE sr.moteid <= 54\n",
    ");''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>8. Time Series Analysis</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Our dataset is from a wireless sensor network composed of 54 sensors monitoring temperature, humidity, lighting conditions of the surrounding environment as well as voltage of each sensor. Each sensor monitors and submits a package containing the above information once every 31 s. Often times we will want to look at data in different time frames, this may be far more granular than we need for instance.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This is where the powerful Time Series functionality of Vantage comes in. Using the GROUP BY TIME we can easily group this into 1hr increments... But this could be by minute, day, whatever time interval you choose!</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "SELECT \n",
    "$TD_TIMECODE_RANGE AS T_RANGE \n",
    ", $TD_GROUP_BY_TIME AS T_GROUP \n",
    ", moteid\n",
    ", AVG(temperature) AS avg_temperature \n",
    ", AVG(humidity) AS avg_humidity\n",
    ", AVG(light) AS avg_light\n",
    ", AVG(voltage) AS avg_voltage\n",
    ", AVG(x) AS x\n",
    ", AVG(y) AS y\n",
    "FROM \n",
    "sensor_clean\n",
    "GROUP BY TIME (HOURS(1) AND moteid)                                          \n",
    "USING TIMECODE(sensdatetime)\n",
    "ORDER BY 1, 3;\n",
    "'''\n",
    "data1hr = pd.read_sql(qry, eng)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Vantage has generated the RANGE and GROUP columns for us to identify the timeslot the data is for. You can iterate and change the way the time series sensor data is aggregated. Try changing HOURS to DAYS and see the results. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now let's group the data again by Sensor id to see a summary of all 54 sensors:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data1hr_grp = data1hr.groupby('moteid')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# review first group\n",
    "data1hr_grp.first()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>9. Visualize the Hourly Sensor Readings</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now that we have used Vantage to bring in sensor data from an object store, clean it up, join it with additional data and change the time interval to hourly - let's take a look at it!</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Temperature is in degrees Celsius.</li>\n",
    "    <li>Humidity is temperature corrected relative humidity, ranging from 0-100%.</li>\n",
    "    <li>Light is in Lux (a value of 1 Lux corresponds to moonlight, 400 Lux to a bright office, and 100,000 Lux to full sunlight.)</li>\n",
    "    <li>Voltage is expressed in volts.</li>\n",
    "\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sens_type can be avg_temperature, avg_humidity, avg_light,  or avg_voltage.\n",
    "sens_type = \"avg_temperature\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Note that not all 54sensors have readings in every time range, \n",
    "# therefor need to get the list of keys from the group.\n",
    "fig, ax = plt.subplots(figsize=(16, 10))\n",
    "\n",
    "plt.ylabel(sens_type)\n",
    "\n",
    "for i in data1hr_grp.groups.keys():\n",
    "    plt.plot(data1hr_grp.get_group(i).T_GROUP, \n",
    "             data1hr_grp.get_group(i)[sens_type])\n",
    "    \n",
    "plt.xticks(rotation='vertical')\n",
    "#plt.legend(list(data1hr_grp.groups.keys()))\n",
    "plt.show;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Try changing the sensor reading from temperature to another sensor (humidity, light, voltage) and see the results. You can also change the time frame aggregation from an hour to something different and see the changes.</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As in the above plotted graph, average temperature is plotted, we can plot average humidity, average light or average voltage. In the cell where we are assigning sens_type</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'># sens_type can be avg_temperature, avg_humidity, avg_light,  or avg_voltage.\n",
    "    \n",
    "sens_type = \"avg_temperature\"\n",
    "</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Can be changed to\n",
    "    \n",
    "sens_type = \"avg_humidity\"</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> This will help plotting the above graph using average humidity, average light or average voltage.</p>  "
   ]
  },
  {
   "cell_type": "markdown",
   "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>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Cleanup work tables to prevent errors next time.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['sensor_new_locations', 'sensors_table']\n",
    "\n",
    "# Loop through the list of tables and execute the drop table command for each table\n",
    "for table in tables:\n",
    "    try:\n",
    "        db_drop_table(table_name = table)\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "execute_sql('DROP view sensor_readings;')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b>Databases and Tables </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code will clean up tables and databases created above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_IndoorSensor');\" \n",
    "#Takes 5 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>11. Dataset</b></p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The <b>sensor_readings</b> dataset contains 2.3 Million records - the output of 54 sensors between February 28th and April 5th, 2004:</p>\n",
    "\n",
    "\n",
    "- `date`: date of the sensor reading\n",
    "- `time`: time of the sensor reading\n",
    "- `epoch`: store identifier where the order was taken\n",
    "- `moteid`: unique sensor identifier\n",
    "- `temperature`: temperature\n",
    "- `humidity`: humidity\n",
    "- `light`: light\n",
    "- `voltage`: light\n",
    "\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<b>sensor_locations</b> contain xy coordinates of sensors in meters relative to the upper right corner of the lab\n",
    "\n",
    "- `id`: sensor identifier\n",
    "- `x`: X coordinate (M)\n",
    "- `y`: Y coordinate (M)\n",
    "</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<b>connectivity</b> the sensors are wirelessly interconnected and due to locations / environment the connections vary in strength \n",
    "\n",
    "- `sendid`: sensor id of sender\n",
    "- `receiveid`: sensor id of receiver\n",
    "- `reachprob`: probability of a message from a sender successfully reaching a receiver\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This data, the Intel Berkeley Research Lab data set, was collected through the hard work of: Peter Bodik, Wei Hong, Carlos Guestrin, Sam Madden, Mark Paskin, and Romain Thibaux. Mark aggregated the raw connectivity information over time and generated the beautiful network layout diagram. Sam and Wei wrote TinyDB and other software for logging data. Intel Berkeley provided hardware. The TinyOS team, in particular Joe Polastre and Rob Szewczyk, provided the software infrastructure and hardware designs that made this deployment possible.\n",
    "\n",
    "http://db.csail.mit.edu/labdata/labdata.html\n",
    "    </p>"
   ]
  },
  {
   "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"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "394.844px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}