Air_Quality_Data_Visualization.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "This notebook is for the data visualization of different kinds of graphs air quality researchers are interested in. It's intended for air quality researchers to track the data collection of sensors and study the relationships between different air quality measurements. The data used is collected by sensors placed on different parts of Georgia Tech's campus and is stored in a database that this notebook queries to retrieve relevant data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using This Notebook\n",
    "* Copy this file and rename it because you'd want to save your graphs and graph inputs. There can't be two people using the same notebook file because one would have to discard the work done by the other person if they want to use the notebook file.\n",
    "* To open this notebook and run the cells in it, you have three options:\n",
    "  * Jupyter\n",
    "    * You can use it on your browser or locally\n",
    "    * You can connect it to your local file system\n",
    "    * For more information, check out the link <a href=\"https://jupyter.org/try\">here</a>\n",
    "  * Google Colab\n",
    "    * It's primarily used on the browser\n",
    "    * You can upload your notebook to Google Drive and open it from there by choosing \"Open with\" and selecting \"Google Colaboratory\"\n",
    "    * For more information, check out the link <a href=\"https://colab.research.google.com/\">here</a>.\n",
    "  * VS Code:\n",
    "    * Cannot be used on the browser, only locally\n",
    "    * Unlike the other two options, you'd have to set up the kernel manually to be able to run the cells (that's why I don't recommend it)\n",
    "* Currently, this notebook supports 3 graphs: sensor activity, diurnal, timeseries.\n",
    "* For each graph in this notebook, there's a 3-step process to visualize it:\n",
    "    * Step 1: Query the Database\n",
    "    * step 2: Transform the Data (some graphs don't have that)\n",
    "    * step 3: Create the Graph\n",
    "* For each graph, there are 4 (or 3 if there's no step 2) cells you need to run. The first 3 (or 2) are function definitions that implement each of the steps mentioned above. The last cell is where you specify the paramters of all the functions and call them.\n",
    "* Important considerations:\n",
    "  * The first step (querying the database) takes the longest. It could take up to 10 minutes to run depending on the volume of data you're querying. Therefore, I suggest having the function calls to the other 2 steps in their own cell so that you can modify the parameters and run them again quickly as many times as you want.\n",
    "  * For each graph, you have to specify the correct table that contains information about the sensor and columns you're interestd in. The implementation of the first step (querying the database) doesn't check if you chose the correct table. It's your responsibility to make sure you're selecting the correct table. For example, if you're interested in the `pm1` and `o3` data of the `SN000-037` sensor, you have to specify the table parameter to be `v100_final`. If you're not sure what the correct table is, check the \"Database Overview\" section.\n",
    "* To start using the notebook, first run the cell under \"Establish Connection\" to connect to the database. Then, run the cell under \"Imports\". Lastly, run all the cells that have function definitions. Once you've done that, you can start visualizing by playing with the last cell under each graph type and modifying the parameters to your liking.\n",
    "* If you ever get a \"Could not establish connection\" error or any kind of error that seems like it's related to the database connection, just rerun the cell under \"Establish Connection\"."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Database Overview\n",
    "Here are the tables:\n",
    "* `location`\n",
    "* `sensor`\n",
    "* `mod_pm_raw`\n",
    "* `v100_raw`\n",
    "* `v100_final`\n",
    "* `v200_raw`\n",
    "* `v200_final`\n",
    "\n",
    "Here are the sensors and their tables:\n",
    "* mod_pm: ['MOD-PM-00001', 'MOD-PM-00002', 'MOD-PM-00003', 'MOD-PM-00004', 'MOD-PM-00005', 'MOD-PM-00006', 'MOD-PM-00007', 'MOD-PM-00008', 'MOD-PM-00009', 'MOD-PM-00010', 'MOD-PM-00011', 'MOD-PM-00012', 'MOD-PM-00013', 'MOD-PM-00014', 'MOD-PM-00015', 'MOD-PM-00016', 'MOD-PM-00017', 'MOD-PM-00018', 'MOD-PM-00019', 'MOD-PM-00024', 'MOD-PM-00280', 'MOD-PM-00281', 'MOD-PM-00285', 'MOD-PM-00287', 'MOD-PM-00288', 'MOD-PM-00289', 'MOD-PM-00290', 'MOD-PM-00291', 'MOD-PM-00292', 'MOD-PM-00293', 'MOD-PM-00294', 'MOD-PM-00296', 'MOD-PM-00299']\n",
    "* v100: ['SN000-036', 'SN000-035', 'SN000-037', 'SN000-032']\n",
    "* v200: ['SN000-123', 'SN000-124', 'SN000-125', 'SN000-121', 'SN000-119', 'SN000-126', 'SN000-127', 'SN000-110', 'SN000-122']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Setup"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Establish Connection\n",
    "<u>Warning:</u> make sure you're connected via VPN before proceeding. <br>\n",
    "If you don't have the VPN client installed on your machine, visit <a href=\"https://vpn.gatech.edu\">vpn.gatech.edu</a> and log in. Then click on \"Download Windows/macOS VPN Client\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import relevant packages\n",
    "import sqlalchemy\n",
    "from sshtunnel import SSHTunnelForwarder\n",
    "\n",
    "# specify database credentials\n",
    "username = \"\"\n",
    "password = \"\"\n",
    "private_server_ip = \"\"\n",
    "ip = \"\"\n",
    "database = \"\"\n",
    "\n",
    "# ssh to database\n",
    "server = SSHTunnelForwarder(\n",
    "    ssh_address_or_host=(ip, 22),\n",
    "    ssh_username=username,\n",
    "    ssh_password=password,\n",
    "    remote_bind_address=(private_server_ip, 3306)\n",
    ")\n",
    "\n",
    "# start ssh server\n",
    "server.start()\n",
    "local_port = str(server.local_bind_port)\n",
    "engine = sqlalchemy.create_engine(\"mysql+pymysql://{user}:{pw}@{host}:{port}/{db}\".format(user=username, pw=password, host=private_server_ip, port=local_port, db=database))\n",
    "connection = engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# close the connection to the server/database\n",
    "server.stop() # only run this when you're done, right before closing the notebook (it's OK if you forgot, but it's good practice to close the connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from typing import List\n",
    "\n",
    "import pandas as pd\n",
    "import plotly.express as px"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Start Visualizing!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sensor Activity\n",
    "<u>Description:</u> visualize the change in datapoints collected per month over all the collected datapoints for the given sensor.<br>\n",
    "<u>Input:</u> sensor serial number. <br>\n",
    "<u>Output:</u> a histogram of how many datapoints were collected per month."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def query_database_sensor_activity(sensor: str, table: str, show_outputs: bool = True) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Queries the database for all the timestamps in which a datapoint was collected by the given sensor.\n",
    "    Parameters:\n",
    "        sensor (str): the sensor ID to query\n",
    "        table (str): the table that contains information about the given sensor in the database\n",
    "        show_outputs (bool): whether to print updates about the function\n",
    "    Returns:\n",
    "        pd.DataFrame: the query result\n",
    "    \"\"\"\n",
    "    sql_query = \"SELECT timestamp FROM iaq.{} WHERE sn = '{}'\".format(table, sensor)\n",
    "    print(f\"Executed SQL query:\\n{sql_query}\\n\") if show_outputs else None\n",
    "    tsdf = pd.read_sql(sql=sql_query, con=connection).sort_values(by='timestamp')\n",
    "    print(\"Shape of query result: {}\\n\".format(tsdf.shape)) if show_outputs else None\n",
    "    print(f\"Sample of query result:\\n{tsdf.sample(5)}\\n\") if show_outputs else None\n",
    "    return tsdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_graph_sensor_activity(df: pd.DataFrame, sensor: str) -> None:\n",
    "    \"\"\"\n",
    "    Creates the sensor activity graph using the data from the database query result.\n",
    "    Parameters:\n",
    "        df (pd.DataFrame): the dataframe containing the sensor activity data, which we got as the database query result\n",
    "        sensor (str): the sensor ID queried\n",
    "    Returns:\n",
    "        None\n",
    "    \"\"\"\n",
    "    fig = px.histogram(df['timestamp'].dt.date, x='timestamp')\n",
    "\n",
    "    fig.update_traces(\n",
    "        xbins=dict(size=\"M1\"))\n",
    "    \n",
    "    fig.update_layout(\n",
    "        title='Datapoint Counts per Month for Sensor {}'.format(sensor),\n",
    "        xaxis_title='Month',\n",
    "        yaxis_title='Count',\n",
    "        xaxis=dict(\n",
    "            dtick='M1'),\n",
    "        bargap=0.1)\n",
    "    \n",
    "    fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# specify the parameters\n",
    "sensor = \"SN000-124\"\n",
    "table = \"v200_final\"\n",
    "show_outputs = True\n",
    "\n",
    "# call the functions\n",
    "query_result = query_database_sensor_activity(sensor, table, show_outputs)\n",
    "create_graph_sensor_activity(query_result, sensor)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Diurnal\n",
    "<u>Description:</u> visualize the diurnal graph of the chosen columns (e.g. `pm1`, `pm10`, `o3`, etc.). <br>\n",
    "<u>Input:</u> sensor serial number, columns to visualize, start date and end date of period to visualize. <br>\n",
    "<u>Output:</u> line graph with each hour of the day (0-23) on the horizontal axis and the average of all values in that hour on the vertical axis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def query_database_diurnal(sensor: str, table: str, columns: List[str], start_date: str, end_date: str, show_outputs: bool = True) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Queries the database for the given columns and timestamp of each datapoint of the given sensor.\n",
    "    Parameters:\n",
    "        sensor (str): the sensor ID to query\n",
    "        table (str): the table that contains information about the given sensor in the database\n",
    "        columns (List[str]): the columns to query\n",
    "        start_date (str): the start date of the datapoints to query\n",
    "        end_date (str): the end date of the datapoints to query\n",
    "        show_outputs (bool): whether to print updates about the function\n",
    "    Returns:\n",
    "        pd.DataFrame: the query result\n",
    "    \"\"\"\n",
    "    sql_query = \"SELECT timestamp, {cols} FROM iaq.{table} WHERE sn = '{sn}' AND '{start}' <= timestamp AND timestamp <= '{end}'\".format(cols=', '.join(columns), table=table, sn=sensor, start=start_date, end=end_date)\n",
    "    print(f\"Executed SQL query:\\n{sql_query}\\n\") if show_outputs else None\n",
    "    didf = pd.read_sql(sql=sql_query, con=connection)\n",
    "    print(f\"Shape of query result: {didf.shape}\\n\") if show_outputs else None\n",
    "    print(f\"Sample of query result:\\n{didf.sample(5)}\\n\") if show_outputs else None\n",
    "    return didf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def transform_data_diurnal(df: pd.DataFrame, show_outputs: bool = True) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Transforms the query result into a dataframe that contains the mean of each column for each hour of the day.\n",
    "    Parameters:\n",
    "        df (pd.DataFrame): the dataframe containing the query result\n",
    "        show_outputs (bool): whether to print updates about the function\n",
    "    Returns:\n",
    "        pd.DataFrame: the transformed dataframe\n",
    "    \"\"\"\n",
    "    df['hour'] = df['timestamp'].dt.hour\n",
    "    df = df.groupby(pd.Grouper(key='hour')).mean().reset_index()\n",
    "    print(f\"Sample of dataframe after transformation:\\n{df.sample(5)}\\n\") if show_outputs else None\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_graph_diurnal(df: pd.DataFrame, columns: List[str], sensor: str, colors: List[str] = None, dx: int = 5, show_dots: bool = True) -> None:\n",
    "    \"\"\"\n",
    "    Creates the diurnal graph using the trasformed database query result.\n",
    "    Parameters:\n",
    "        df (pd.DataFrame): the dataframe containing the diurnal data, which we got by transforming the database query result\n",
    "        columns (List[str]): the columns to plot\n",
    "        sensor (str): the sensor ID queried\n",
    "        colors (List[str]): the colors to use for each column (in the same order as the columns).\n",
    "            Colors can be specified as names of colors or hex values. If None, the default colors will be used.\n",
    "        dx (int): the x-axis tick interval\n",
    "        show_dots (bool): whether to show dots on the graph\n",
    "    Returns:\n",
    "        None\n",
    "    \"\"\"\n",
    "    \n",
    "    fig = px.line(\n",
    "        df,\n",
    "        x='hour', \n",
    "        y=columns, \n",
    "        title=f\"Diurnal graph of {', '.join(columns)} for {sensor}\",\n",
    "        color_discrete_sequence=colors if colors else None,\n",
    "        markers=show_dots)\n",
    "\n",
    "    fig.update_layout(\n",
    "        xaxis=dict(dtick=dx))\n",
    "\n",
    "    fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# specify the parameters\n",
    "sensor = \"SN000-037\"\n",
    "table = 'v100_final'\n",
    "columns = ['pm1', 'pm10', 'pm25', 'o3']\n",
    "start_date = \"2022-08-01\"\n",
    "end_date = \"2022-08-03\"\n",
    "show_outputs = True\n",
    "colors = ['black', 'blue', 'red', 'brown']\n",
    "dx = 2 \n",
    "dots_on_datapoints = True\n",
    "\n",
    "# call the functions\n",
    "query_result = query_database_diurnal(sensor, table, columns, start_date, end_date, show_outputs)\n",
    "transformed_result = transform_data_diurnal(query_result, show_outputs)\n",
    "create_graph_diurnal(transformed_result, columns, sensor, colors, dx, dots_on_datapoints)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Timeseries\n",
    "<u>Description:</u> visualize a timeseries graph of the collected datapoints of the chosen columns by the given sensor. <br>\n",
    "<u>Input:</u> sensor serial number, columns to plot. <br>\n",
    "<u>Output:</u> a timeseries graph with one plot or more."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "def query_database_timeseries(sensor: str, table: str, columns: List[str], start_date: str, end_date: str, show_outputs: bool = True) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Queries the database for the given columns and timestamp of each datapoint of the given sensor.\n",
    "    Parameters:\n",
    "        sensor (str): the sensor ID to query\n",
    "        table (str): the table that contains information about the given sensor in the database\n",
    "        columns (List[str]): the columns to query\n",
    "        start_date (str): the start date of the datapoints to query\n",
    "        end_date (str): the end date of the datapoints to query\n",
    "        show_outputs (bool): whether to print updates about the function\n",
    "    Returns:\n",
    "        pd.DataFrame: the query result\n",
    "    \"\"\"\n",
    "    sql_query = \"SELECT timestamp, {cols} FROM iaq.{table} WHERE sn = '{sn}' AND '{start}' <= timestamp AND timestamp <= '{end}'\".format(cols=', '.join(columns), table=table, sn=sensor, start=start_date, end=end_date)\n",
    "    print(f\"Executed SQL query:\\n{sql_query}\\n\") if show_outputs else None\n",
    "    tsdf = pd.read_sql(sql=sql_query, con=connection)\n",
    "    print(f\"Shape of query result: {tsdf.shape}\\n\") if show_outputs else None\n",
    "    print(f\"Sample of query result:\\n{tsdf.sample(5)}\\n\") if show_outputs else None\n",
    "    return tsdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_graph_timeseries(df: pd.DataFrame, columns: List[str]):\n",
    "    \"\"\"\n",
    "    Creates the timeseries graph using the data from the database query result.\n",
    "    Parameters:\n",
    "        df (pd.DataFrame): the dataframe containing the timeseries data, which we got as the database query result\n",
    "        columns (List[str]): the columns to plot\n",
    "    Returns:\n",
    "        None\n",
    "    \"\"\"\n",
    "    fig = px.line(df, x='timestamp', y=columns)\n",
    "    fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# specify the parameters\n",
    "sensor = \"SN000-036\"\n",
    "table = \"v100_final\"\n",
    "columns = [\"pm1\", \"pm10\"]\n",
    "start_date = \"2022-08-01\"\n",
    "end_date = \"2022-08-03\"\n",
    "show_outputs = True\n",
    "\n",
    "# call the functions\n",
    "query_result = query_database_timeseries(sensor, table, columns, start_date, end_date, show_outputs)\n",
    "create_graph_timeseries(query_result, columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exporting Graphs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hover over to the top left side of the plot, you'll see a camera icon that if you hover over will say \"Download plot as a png\". Click on it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Release Notes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is the first version of this notebook, so there are no release notes. However, future version will have notes that list out all the changes made."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "ascent-aq",
   "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.11.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}