ralph_sql_read.ipynb

{
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# Jupyter Notebook for Counting Building Occupancy from Polaris Traffic Simulation Data\r\n",
                "\r\n",
                "This notebook will load a Polaris SQLlite data file into a Pandas data frame using sqlite3 libraries and count the average number of people in each building in each hour of the simulation.\r\n",
                "\r\n",
                "For help with Jupyter notebooks\r\n",
                "\r\n",
                "For help on using sql with Pandas see\r\n",
                "http://www.pererikstrandberg.se/blog/index.cgi?page=PythonDataAnalysisWithSqliteAndPandas\r\n",
                "\r\n",
                "For help  on data analysis with Pandas see\r\n",
                "http://nbviewer.jupyter.org/github/jakevdp/PythonDataScienceHandbook/blob/master/notebooks/Index.ipynb\r\n"
            ],
            "metadata": {}
        },
        {
            "cell_type": "markdown",
            "source": [
                "This notebook will load a Polaris SQLlite data file into a Pandas data frame using sqlite3 libraries and count the average number of people in each building in each hour of the simulation."
            ],
            "metadata": {}
        },
        {
            "cell_type": "markdown",
            "source": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 2,
            "source": [
                "import sqlite3\r\n",
                "import numpy as np\r\n",
                "import pandas as pd\r\n",
                "import matplotlib.pyplot as plt"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 4,
            "source": [
                "# Create your connection.  Assumes data is in a parallel subdirectory to this one\r\n",
                "cnx = sqlite3.connect('.\\data\\detroit2-Demand.sqlite')"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 5,
            "source": [
                "# exract all the beginning locations of the building simulation\r\n",
                "beginning_location = pd.read_sql_query(\"SELECT * FROM Beginning_Location_All\", cnx)"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 6,
            "source": [
                "trips = pd.read_sql_query(\"SELECT start, end, origin, destination, person FROM Trip\", cnx)\r\n",
                "trips[\"start_hr\"] = trips.start // 3600\r\n",
                "trips[\"end_hr\"] = trips.end // 3600"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 7,
            "source": [
                "trips = pd.read_sql_query(\"SELECT start, end, origin, destination, person FROM Trip\", cnx)\r\n",
                "trips[\"start_hr\"] = trips.start // 3600\r\n",
                "trips[\"end_hr\"] = trips.end // 3600"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 9,
            "source": [
                "# create the data frames that have the counts of things grouped by start hr & origin and end hr & destination\r\n",
                "departs = trips.groupby(['start_hr','origin']).size().reset_index(name='countleave')\r\n",
                "arrives = trips.groupby(['end_hr','destination']).size().reset_index(name='countarrive')"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 10,
            "source": [
                "departm = {}\r\n",
                "arrivem = {}\r\n",
                "for i in range(24):\r\n",
                "    departm[i] = pd.merge(\r\n",
                "        beginning_location, departs[departs.start_hr == i],\r\n",
                "        left_on='location', right_on='origin', how='left'\r\n",
                "    ).fillna(0)\r\n",
                "    arrivem[i] = pd.merge(\r\n",
                "        beginning_location, arrives[arrives.end_hr == i],\r\n",
                "        left_on='location', right_on='destination', how='left'\r\n",
                "    ).fillna(0)"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 31,
            "source": [
                "occm = {}\r\n",
                "occm[0] = departm[0].occupants + arrivem[0].countarrive - departm[0].countleave\r\n",
                "for i in range(1, 24):\r\n",
                "    occm[i] = occm[i-1] + arrivem[i].countarrive - departm[i].countleave"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 32,
            "source": [
                "occupancy = pd.DataFrame(occm)"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 36,
            "source": [
                "occupancy.head()"
            ],
            "outputs": [
                {
                    "output_type": "execute_result",
                    "data": {
                        "text/plain": [
                            "    0    1    2    3    4    5    6    7    8    9   ...   14   15   16   17  \\\n",
                            "0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...  0.0  0.0  0.0  0.0   \n",
                            "1  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...  0.0  0.0  0.0  0.0   \n",
                            "2  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...  0.0  0.0  0.0  0.0   \n",
                            "3  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...  0.0  0.0  0.0  0.0   \n",
                            "4  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...  0.0  0.0  0.0  0.0   \n",
                            "\n",
                            "    18   19   20   21   22   23  \n",
                            "0  0.0  0.0  0.0  0.0  0.0  0.0  \n",
                            "1  0.0  0.0  0.0  0.0  0.0  0.0  \n",
                            "2  0.0  0.0  0.0  0.0  0.0  0.0  \n",
                            "3  0.0  0.0  0.0  0.0  0.0  0.0  \n",
                            "4  0.0  0.0  0.0  0.0  0.0  0.0  \n",
                            "\n",
                            "[5 rows x 24 columns]"
                        ],
                        "text/html": [
                            "<div>\n",
                            "<style scoped>\n",
                            "    .dataframe tbody tr th:only-of-type {\n",
                            "        vertical-align: middle;\n",
                            "    }\n",
                            "\n",
                            "    .dataframe tbody tr th {\n",
                            "        vertical-align: top;\n",
                            "    }\n",
                            "\n",
                            "    .dataframe thead th {\n",
                            "        text-align: right;\n",
                            "    }\n",
                            "</style>\n",
                            "<table border=\"1\" class=\"dataframe\">\n",
                            "  <thead>\n",
                            "    <tr style=\"text-align: right;\">\n",
                            "      <th></th>\n",
                            "      <th>0</th>\n",
                            "      <th>1</th>\n",
                            "      <th>2</th>\n",
                            "      <th>3</th>\n",
                            "      <th>4</th>\n",
                            "      <th>5</th>\n",
                            "      <th>6</th>\n",
                            "      <th>7</th>\n",
                            "      <th>8</th>\n",
                            "      <th>9</th>\n",
                            "      <th>...</th>\n",
                            "      <th>14</th>\n",
                            "      <th>15</th>\n",
                            "      <th>16</th>\n",
                            "      <th>17</th>\n",
                            "      <th>18</th>\n",
                            "      <th>19</th>\n",
                            "      <th>20</th>\n",
                            "      <th>21</th>\n",
                            "      <th>22</th>\n",
                            "      <th>23</th>\n",
                            "    </tr>\n",
                            "  </thead>\n",
                            "  <tbody>\n",
                            "    <tr>\n",
                            "      <th>0</th>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>1</th>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2</th>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>3</th>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>4</th>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "<p>5 rows × 24 columns</p>\n",
                            "</div>"
                        ]
                    },
                    "metadata": {},
                    "execution_count": 36
                }
            ],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 37,
            "source": [
                "occupancy[\"location\"]=beginning_location.location\r\n",
                "occupancy['land_use']=beginning_location.land_use"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 38,
            "source": [
                "occupancy"
            ],
            "outputs": [
                {
                    "output_type": "execute_result",
                    "data": {
                        "text/plain": [
                            "          0    1    2    3    4    5    6    7    8    9  ...   16   17   18  \\\n",
                            "0       0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...  0.0  0.0  0.0   \n",
                            "1       0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...  0.0  0.0  0.0   \n",
                            "2       0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...  0.0  0.0  0.0   \n",
                            "3       0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...  0.0  0.0  0.0   \n",
                            "4       0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...  0.0  0.0  0.0   \n",
                            "...     ...  ...  ...  ...  ...  ...  ...  ...  ...  ...  ...  ...  ...  ...   \n",
                            "145446  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  1.0  1.0  ...  0.0  0.0  0.0   \n",
                            "145447  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...  0.0  0.0  0.0   \n",
                            "145448  0.0  0.0  0.0  0.0  0.0  0.0  0.0  1.0  1.0  1.0  ...  0.0  0.0  0.0   \n",
                            "145449  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...  0.0  0.0  0.0   \n",
                            "145450  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...  0.0  0.0  0.0   \n",
                            "\n",
                            "         19   20   21   22   23  location     land_use  \n",
                            "0       0.0  0.0  0.0  0.0  0.0         1  SPECIAL_GEN  \n",
                            "1       0.0  0.0  0.0  0.0  0.0         2     BUSINESS  \n",
                            "2       0.0  0.0  0.0  0.0  0.0         3     BUSINESS  \n",
                            "3       0.0  0.0  0.0  0.0  0.0         4     BUSINESS  \n",
                            "4       0.0  0.0  0.0  0.0  0.0         5     BUSINESS  \n",
                            "...     ...  ...  ...  ...  ...       ...          ...  \n",
                            "145446  0.0  0.0  0.0  0.0  0.0    182657          ALL  \n",
                            "145447  0.0  0.0  0.0  0.0  0.0    182658          ALL  \n",
                            "145448  0.0  0.0  0.0  0.0  0.0    182659          ALL  \n",
                            "145449  0.0  0.0  0.0  0.0  0.0    182660          ALL  \n",
                            "145450  0.0  0.0  0.0  0.0  0.0    182661          ALL  \n",
                            "\n",
                            "[145451 rows x 26 columns]"
                        ],
                        "text/html": [
                            "<div>\n",
                            "<style scoped>\n",
                            "    .dataframe tbody tr th:only-of-type {\n",
                            "        vertical-align: middle;\n",
                            "    }\n",
                            "\n",
                            "    .dataframe tbody tr th {\n",
                            "        vertical-align: top;\n",
                            "    }\n",
                            "\n",
                            "    .dataframe thead th {\n",
                            "        text-align: right;\n",
                            "    }\n",
                            "</style>\n",
                            "<table border=\"1\" class=\"dataframe\">\n",
                            "  <thead>\n",
                            "    <tr style=\"text-align: right;\">\n",
                            "      <th></th>\n",
                            "      <th>0</th>\n",
                            "      <th>1</th>\n",
                            "      <th>2</th>\n",
                            "      <th>3</th>\n",
                            "      <th>4</th>\n",
                            "      <th>5</th>\n",
                            "      <th>6</th>\n",
                            "      <th>7</th>\n",
                            "      <th>8</th>\n",
                            "      <th>9</th>\n",
                            "      <th>...</th>\n",
                            "      <th>16</th>\n",
                            "      <th>17</th>\n",
                            "      <th>18</th>\n",
                            "      <th>19</th>\n",
                            "      <th>20</th>\n",
                            "      <th>21</th>\n",
                            "      <th>22</th>\n",
                            "      <th>23</th>\n",
                            "      <th>location</th>\n",
                            "      <th>land_use</th>\n",
                            "    </tr>\n",
                            "  </thead>\n",
                            "  <tbody>\n",
                            "    <tr>\n",
                            "      <th>0</th>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>1</td>\n",
                            "      <td>SPECIAL_GEN</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>1</th>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>2</td>\n",
                            "      <td>BUSINESS</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2</th>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>3</td>\n",
                            "      <td>BUSINESS</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>3</th>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>4</td>\n",
                            "      <td>BUSINESS</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>4</th>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>5</td>\n",
                            "      <td>BUSINESS</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>...</th>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>145446</th>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>1.0</td>\n",
                            "      <td>1.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>182657</td>\n",
                            "      <td>ALL</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>145447</th>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>182658</td>\n",
                            "      <td>ALL</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>145448</th>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>1.0</td>\n",
                            "      <td>1.0</td>\n",
                            "      <td>1.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>182659</td>\n",
                            "      <td>ALL</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>145449</th>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>182660</td>\n",
                            "      <td>ALL</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>145450</th>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>182661</td>\n",
                            "      <td>ALL</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "<p>145451 rows × 26 columns</p>\n",
                            "</div>"
                        ]
                    },
                    "metadata": {},
                    "execution_count": 38
                }
            ],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 28,
            "source": [
                "cols=['location', 'land_use']+list(range(23))"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 40,
            "source": [
                "# reorder the columns putting location and land use at the beginning\r\n",
                "cols=['location', 'land_use'] + list(range(0,24))\r\n",
                "occupancy = occupancy[cols]"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 44,
            "source": [
                "occupancy.tail()"
            ],
            "outputs": [
                {
                    "output_type": "execute_result",
                    "data": {
                        "text/plain": [
                            "        location land_use    0    1    2    3    4    5    6    7  ...   14  \\\n",
                            "145446    182657      ALL  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...  1.0   \n",
                            "145447    182658      ALL  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...  0.0   \n",
                            "145448    182659      ALL  0.0  0.0  0.0  0.0  0.0  0.0  0.0  1.0  ...  0.0   \n",
                            "145449    182660      ALL  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...  0.0   \n",
                            "145450    182661      ALL  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...  0.0   \n",
                            "\n",
                            "         15   16   17   18   19   20   21   22   23  \n",
                            "145446  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  \n",
                            "145447  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  \n",
                            "145448  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  \n",
                            "145449  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  \n",
                            "145450  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  \n",
                            "\n",
                            "[5 rows x 26 columns]"
                        ],
                        "text/html": [
                            "<div>\n",
                            "<style scoped>\n",
                            "    .dataframe tbody tr th:only-of-type {\n",
                            "        vertical-align: middle;\n",
                            "    }\n",
                            "\n",
                            "    .dataframe tbody tr th {\n",
                            "        vertical-align: top;\n",
                            "    }\n",
                            "\n",
                            "    .dataframe thead th {\n",
                            "        text-align: right;\n",
                            "    }\n",
                            "</style>\n",
                            "<table border=\"1\" class=\"dataframe\">\n",
                            "  <thead>\n",
                            "    <tr style=\"text-align: right;\">\n",
                            "      <th></th>\n",
                            "      <th>location</th>\n",
                            "      <th>land_use</th>\n",
                            "      <th>0</th>\n",
                            "      <th>1</th>\n",
                            "      <th>2</th>\n",
                            "      <th>3</th>\n",
                            "      <th>4</th>\n",
                            "      <th>5</th>\n",
                            "      <th>6</th>\n",
                            "      <th>7</th>\n",
                            "      <th>...</th>\n",
                            "      <th>14</th>\n",
                            "      <th>15</th>\n",
                            "      <th>16</th>\n",
                            "      <th>17</th>\n",
                            "      <th>18</th>\n",
                            "      <th>19</th>\n",
                            "      <th>20</th>\n",
                            "      <th>21</th>\n",
                            "      <th>22</th>\n",
                            "      <th>23</th>\n",
                            "    </tr>\n",
                            "  </thead>\n",
                            "  <tbody>\n",
                            "    <tr>\n",
                            "      <th>145446</th>\n",
                            "      <td>182657</td>\n",
                            "      <td>ALL</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>1.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>145447</th>\n",
                            "      <td>182658</td>\n",
                            "      <td>ALL</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>145448</th>\n",
                            "      <td>182659</td>\n",
                            "      <td>ALL</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>1.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>145449</th>\n",
                            "      <td>182660</td>\n",
                            "      <td>ALL</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>145450</th>\n",
                            "      <td>182661</td>\n",
                            "      <td>ALL</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>...</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "<p>5 rows × 26 columns</p>\n",
                            "</div>"
                        ]
                    },
                    "metadata": {},
                    "execution_count": 44
                }
            ],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 45,
            "source": [
                "# Remove locations with no occupancy activity\r\n",
                "occupancy_clean = occupancy[occupancy.iloc[:, 2:].abs().sum(axis=1) != 0]"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 46,
            "source": [
                "# Group locations by land use type\r\n",
                "land_uses = occupancy_clean.land_use.unique()\r\n",
                "occu_profiles = {}\r\n",
                "for land in land_uses:\r\n",
                "    profiles = occupancy_clean[occupancy_clean.land_use == land].drop('land_use', axis=1)\r\n",
                "    occu_profiles[land] = profiles.set_index('location')"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 47,
            "source": [
                "hr = range(24)"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 60,
            "source": [
                "# Occupancy schedules by land use type\r\n",
                "fig, axs = plt.subplots(5, 2, figsize=(16, 20))\r\n",
                "axs_flat = [x for y in axs for x in y]\r\n",
                "for i, ax in enumerate(axs_flat[:-1]):\r\n",
                "    land_type = list(occu_profiles.keys())[i]\r\n",
                "    df = occu_profiles[land_type]\r\n",
                "    ax.step(hr, df.mean(axis=0), color=[0, 0.45, 0.7], label='average')\r\n",
                "    ax.fill_between(\r\n",
                "        hr, df.min(axis=0), df.max(axis=0),\r\n",
                "        step='pre', color=[0, 0.45, 0.7], alpha=0.5, label='range'\r\n",
                "    )\r\n",
                "    ax.set_xticks(range(0, 24, 2))\r\n",
                "    ax.tick_params(labelsize=14)\r\n",
                "    ax.set_xlabel('hour', fontsize=14)\r\n",
                "    ax.set_ylabel('occupancy', fontsize=14)\r\n",
                "    ax.legend(fontsize=14)\r\n",
                "    ax.set_xlim([0,23])\r\n",
                "    ax.set_ylim([df.min().min() - 1,df.max().max() + 1])\r\n",
                "    ax.set_title(land_type, fontsize=18)\r\n",
                "fig.tight_layout()\r\n",
                "fig.savefig('occupancy_by_land_use_type.png', bbox_inches='tight', dpi=600)\r\n",
                "plt.close()"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 59,
            "source": [
                "# Histogram of maximum occupancy by land use type\r\n",
                "fig, axs = plt.subplots(5, 2, figsize=(12, 20))\r\n",
                "axs_flat = [x for y in axs for x in y]\r\n",
                "for i, ax in enumerate(axs_flat[:-1]):\r\n",
                "    land_type = list(occu_profiles.keys())[i]\r\n",
                "    df = occu_profiles[land_type]\r\n",
                "    ax.hist(\r\n",
                "        x=df.max(axis=1).values, color=[0, 0.45, 0.7],\r\n",
                "        alpha=0.5, label='all buildings'\r\n",
                "    )\r\n",
                "    ax.axvline(\r\n",
                "        x=df.max(axis=1).mean(), color=[0, 0.45, 0.7],\r\n",
                "        linewidth=2, label='average'\r\n",
                "    )\r\n",
                "    # ax.set_xticks(range(0, 24, 1))\r\n",
                "    ax.tick_params(labelsize=14)\r\n",
                "    ax.set_xlabel('Maximum occpuancy', fontsize=14)\r\n",
                "    ax.set_ylabel('Counts', fontsize=14)\r\n",
                "    ax.legend(fontsize=14)\r\n",
                "    ax.set_title(land_type, fontsize=18)\r\n",
                "fig.tight_layout()\r\n",
                "fig.savefig('maximum_occupancy_by_land_use_type.png', bbox_inches='tight', dpi=600)\r\n",
                "plt.close()"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 53,
            "source": [
                "# Export occupancy profile data\r\n",
                "for key, value in occu_profiles.items():\r\n",
                "    csv_out = 'Detroit_occupancy_{}.csv'.format(key.lower())\r\n",
                "    value.to_csv(csv_out)"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 54,
            "source": [
                "# Extract schedules with 4+ maximum occupancy and 0+ minimum occupancy\r\n",
                "occu_profiles_clean = {}\r\n",
                "for key, value in occu_profiles.items():\r\n",
                "    occu_profiles_clean[key] = value[(value.max(axis=1) >= 4) & (value.min(axis=1) >= 0)]"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 55,
            "source": [
                "# Normalize by maximum occupancy\r\n",
                "occu_profiles_clean_norm = {}\r\n",
                "for key, value in occu_profiles_clean.items():\r\n",
                "    occu_profiles_clean_norm[key] = value.div(value.max(axis=1), axis=0)"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 56,
            "source": [
                "land_types = ['BUSINESS', 'RESIDENTIAL-MULTI']"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 61,
            "source": [
                "# Cleaned occupancy schedules by land use type\r\n",
                "for land_type in land_types:\r\n",
                "    fig = plt.figure(figsize=(12, 5))\r\n",
                "    ax = fig.add_subplot(111)\r\n",
                "    df = occu_profiles_clean[land_type]\r\n",
                "    ax.step(hr, df.mean(axis=0), color=[0, 0.45, 0.7], label='average')\r\n",
                "    ax.fill_between(\r\n",
                "        hr, df.min(axis=0), df.max(axis=0),\r\n",
                "        step='pre', color=[0, 0.45, 0.7], alpha=0.5, label='range'\r\n",
                "    )\r\n",
                "    ax.set_xticks(range(0, 24, 2))\r\n",
                "    ax.tick_params(labelsize=14)\r\n",
                "    ax.set_xlabel('hour', fontsize=14)\r\n",
                "    ax.set_ylabel('occupancy', fontsize=14)\r\n",
                "    ax.legend(fontsize=14)\r\n",
                "    ax.set_xlim([0,23])\r\n",
                "    ax.set_ylim([df.min().min() - 1,df.max().max() + 1])\r\n",
                "    ax.set_title(land_type, fontsize=18)\r\n",
                "    fig.savefig('occupancy_clean_{}.png'.format(land_type.lower()), bbox_inches='tight', dpi=600)\r\n",
                "    plt.close()"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 63,
            "source": [
                "# Plot average occupancy profiles with 95% range\r\n",
                "for land_type in land_types:\r\n",
                "    fig = plt.figure(figsize=(12, 5))\r\n",
                "    ax = fig.add_subplot(111)\r\n",
                "    df = occu_profiles_clean_norm[land_type]\r\n",
                "    ax.step(hr, df.mean(axis=0), color=[0, 0.45, 0.7], label='average')\r\n",
                "    ax.fill_between(\r\n",
                "        hr, df.quantile(0.025, axis=0), df.quantile(0.975, axis=0),\r\n",
                "        step='pre', color=[0, 0.45, 0.7], alpha=0.5, label='95% range'\r\n",
                "    )\r\n",
                "    ax.set_xticks(range(0, 24, 2))\r\n",
                "    ax.tick_params(labelsize=14)\r\n",
                "    ax.set_xlabel('hour', fontsize=14)\r\n",
                "    ax.set_ylabel('occupancy percentage', fontsize=14)\r\n",
                "    ax.legend(fontsize=14)\r\n",
                "    ax.set_xlim([0,23])\r\n",
                "    ax.set_title(land_type, fontsize=18)\r\n",
                "    fig.savefig('occupancy_clean_norm_{}.png'.format(land_type.lower()), bbox_inches='tight', dpi=600)\r\n",
                "    plt.close()"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 65,
            "source": [
                "# Export cleaned and normalized data\r\n",
                "for land_type in land_types:\r\n",
                "    csv_out_clean = '.Detroit_occupancy_clean_{}.csv'.format(land_type.lower())\r\n",
                "    csv_out_clean_norm = 'Detroit_occupancy_clean_norm_{}.csv'.format(land_type.lower())\r\n",
                "    occu_profiles_clean[land_type].to_csv(csv_out_clean)\r\n",
                "    occu_profiles_clean_norm[land_type].to_csv(csv_out_clean_norm)"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 66,
            "source": [
                "occu_profiles_clean['BUSINESS'].max(axis=1).value_counts()"
            ],
            "outputs": [
                {
                    "output_type": "execute_result",
                    "data": {
                        "text/plain": [
                            "4.0     107\n",
                            "5.0      41\n",
                            "6.0      20\n",
                            "7.0       9\n",
                            "10.0      3\n",
                            "12.0      3\n",
                            "8.0       2\n",
                            "11.0      2\n",
                            "15.0      1\n",
                            "9.0       1\n",
                            "14.0      1\n",
                            "13.0      1\n",
                            "dtype: int64"
                        ]
                    },
                    "metadata": {},
                    "execution_count": 66
                }
            ],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 67,
            "source": [
                "# Reference schedules from prototype building models\r\n",
                "ref_sches = {\r\n",
                "    'occupancy': {\r\n",
                "            'BUSINESS': [\r\n",
                "                0, 0, 0, 0, 0, 0, 0.11, 0.21, 1, 1, 1, 1,\r\n",
                "                0.53, 1, 1, 1, 1, 0.32, 0.11, 0.11, 0.11, 0.11, 0.05, 0\r\n",
                "            ],\r\n",
                "            'RESIDENTIAL-MULTI': [\r\n",
                "                1, 1, 1, 1, 1, 1, 1, 0.85, 0.39, 0.25, 0.25, 0.25,\r\n",
                "                0.25, 0.25, 0.25, 0.25, 0.30, 0.52, 0.87, 0.87, 0.87, 1, 1, 1\r\n",
                "            ]\r\n",
                "    },\r\n",
                "    'light': {\r\n",
                "            'BUSINESS': [\r\n",
                "                0.18, 0.18, 0.18, 0.18, 0.18, 0.23, 0.23, 0.42, 0.9, 0.9, 0.9, 0.9,\r\n",
                "                0.8, 0.9, 0.9, 0.9, 0.9, 0.61, 0.42, 0.42, 0.32, 0.32, 0.23, 0.18\r\n",
                "            ],\r\n",
                "            'RESIDENTIAL-MULTI': [\r\n",
                "                0.011, 0.011, 0.011, 0.011, 0.034, 0.074, 0.079, 0.074, 0.034, 0.023, 0.023, 0.023,\r\n",
                "                0.023, 0.023, 0.023, 0.040, 0.079, 0.113, 0.153, 0.181, 0.181, 0.124, 0.068, 0.028\r\n",
                "                \r\n",
                "            ]\r\n",
                "    },\r\n",
                "    'plug': {\r\n",
                "            'BUSINESS': [\r\n",
                "                0.5, 0.5, 0.5, 0.5, 0.5, 1, 1, 1, 1, 1, 1, 1,\r\n",
                "                0.94, 1, 1, 1, 1, 0.5, 0.2, 0.2, 0.2, 0.2, 0.2, 0.2\r\n",
                "            ],\r\n",
                "            'RESIDENTIAL-MULTI': [\r\n",
                "                0.45, 0.41, 0.39, 0.38, 0.38, 0.43, 0.54, 0.65, 0.66, 0.67, 0.69, 0.70,\r\n",
                "                0.69, 0.66, 0.65, 0.68, 0.80, 1.00, 1.00, 0.93, 0.89, 0.85, 0.71, 0.58\r\n",
                "            ]\r\n",
                "    },\r\n",
                "}"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 68,
            "source": [
                "# Adjust lighting and plug load schedules using KNN regression\r\n",
                "def adjust_sche(elec_ref, occu_ref, occu, n=3):\r\n",
                "    hours_adjusted = np.asarray(range(1, 25)) / 24 * np.pi\r\n",
                "    occu_ref_array = np.asarray(occu_ref)\r\n",
                "    elec_ref_array = np.asarray(elec_ref)\r\n",
                "    elec = []\r\n",
                "    for i in range(24):\r\n",
                "        dist = np.sqrt(\r\n",
                "            np.sin(abs(hours_adjusted - (i+1) / 24 * np.pi)) ** 2 + (occu_ref_array - occu[i]) ** 2\r\n",
                "        )\r\n",
                "        try:\r\n",
                "            elec.append(elec_ref_array[dist == 0][0])\r\n",
                "        except IndexError:\r\n",
                "            idxs = np.argsort(dist)[:n]\r\n",
                "            elec.append(np.average(elec_ref_array[idxs], weights=1/dist[idxs]).round(3))\r\n",
                "    return elec"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 69,
            "source": [
                "# Lighting\r\n",
                "light_profiles_clean_norm = {}\r\n",
                "for land_type in land_types:\r\n",
                "    light_sche = occu_profiles_clean_norm[land_type].copy()\r\n",
                "    for i in range(occu_profiles_clean_norm[land_type].shape[0]):\r\n",
                "        light_sche.iloc[i, :] = adjust_sche(\r\n",
                "            ref_sches['light'][land_type],\r\n",
                "            ref_sches['occupancy'][land_type],\r\n",
                "            occu_profiles_clean_norm[land_type].iloc[i, :]\r\n",
                "        )\r\n",
                "    light_profiles_clean_norm[land_type] = light_sche"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 70,
            "source": [
                "# Plug\r\n",
                "plug_profiles_clean_norm = {}\r\n",
                "for land_type in land_types:\r\n",
                "    plug_sche = occu_profiles_clean_norm[land_type].copy()\r\n",
                "    for i in range(occu_profiles_clean_norm[land_type].shape[0]):\r\n",
                "        plug_sche.iloc[i, :] = adjust_sche(\r\n",
                "            ref_sches['plug'][land_type],\r\n",
                "            ref_sches['occupancy'][land_type],\r\n",
                "            occu_profiles_clean_norm[land_type].iloc[i, :]\r\n",
                "        )\r\n",
                "    plug_profiles_clean_norm[land_type] = plug_sche"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 71,
            "source": [
                "# Plot average occupancy profiles with 90% range and reference schedules\r\n",
                "for land_type in land_types:\r\n",
                "    fig = plt.figure(figsize=(7, 5))\r\n",
                "    ax = fig.add_subplot(111)\r\n",
                "    df = occu_profiles_clean_norm[land_type]\r\n",
                "    ax.step(hr, df.mean(axis=0), color=[0, 0.45, 0.7], label='average')\r\n",
                "    ax.fill_between(\r\n",
                "        hr, df.quantile(0.05, axis=0), df.quantile(0.95, axis=0),\r\n",
                "        step='pre', color=[0, 0.45, 0.7], alpha=0.3, label='90% range'\r\n",
                "    )\r\n",
                "    ax.step(hr, ref_sches['occupancy'][land_type], color='k', linestyle='--', label='DOE REF')\r\n",
                "    ax.set_xticks(range(0, 24, 2))\r\n",
                "    ax.tick_params(labelsize=14)\r\n",
                "    ax.set_xlabel('hour', fontsize=14)\r\n",
                "    ax.set_ylabel('occupancy percentage', fontsize=14)\r\n",
                "    ax.legend(fontsize=12)\r\n",
                "    ax.set_xlim([0,23])\r\n",
                "    ax.set_title(land_type, fontsize=18)\r\n",
                "    fig.savefig('occupancy_clean_norm_with_ref_{}.png'.format(land_type.lower()), bbox_inches='tight', dpi=600)\r\n",
                "    plt.close()"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 72,
            "source": [
                "# Plot average lighting profiles with 90% range and reference schedules\r\n",
                "for land_type in land_types:\r\n",
                "    fig = plt.figure(figsize=(7, 5))\r\n",
                "    ax = fig.add_subplot(111)\r\n",
                "    df = light_profiles_clean_norm[land_type]\r\n",
                "    ax.step(hr, df.mean(axis=0), color=[0, 0.45, 0.7], label='average')\r\n",
                "    ax.fill_between(\r\n",
                "        hr, df.quantile(0.05, axis=0), df.quantile(0.95, axis=0),\r\n",
                "        step='pre', color=[0, 0.45, 0.7], alpha=0.3, label='90% range'\r\n",
                "    )\r\n",
                "    ax.step(hr, ref_sches['light'][land_type], color='k', linestyle='--', label='DOE REF')\r\n",
                "    ax.set_xticks(range(0, 24, 2))\r\n",
                "    ax.tick_params(labelsize=14)\r\n",
                "    ax.set_xlabel('hour', fontsize=14)\r\n",
                "    ax.set_ylabel('lighting percentage', fontsize=14)\r\n",
                "    ax.legend(fontsize=12)\r\n",
                "    ax.set_xlim([0,23])\r\n",
                "    ax.set_title(land_type, fontsize=18)\r\n",
                "    fig.savefig('lighting_clean_norm_with_ref_{}.png'.format(land_type.lower()), bbox_inches='tight', dpi=600)\r\n",
                "    plt.close()"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": 73,
            "source": [
                "# Plot average plug load profiles with 90% range and reference schedules\r\n",
                "for land_type in land_types:\r\n",
                "    fig = plt.figure(figsize=(7, 5))\r\n",
                "    ax = fig.add_subplot(111)\r\n",
                "    df = plug_profiles_clean_norm[land_type]\r\n",
                "    ax.step(hr, df.mean(axis=0), color=[0, 0.45, 0.7], label='average')\r\n",
                "    ax.fill_between(\r\n",
                "        hr, df.quantile(0.05, axis=0), df.quantile(0.95, axis=0),\r\n",
                "        step='pre', color=[0, 0.45, 0.7], alpha=0.3, label='90% range'\r\n",
                "    )\r\n",
                "    ax.step(hr, ref_sches['plug'][land_type], color='k', linestyle='--', label='DOE REF')\r\n",
                "    ax.set_xticks(range(0, 24, 2))\r\n",
                "    ax.tick_params(labelsize=14)\r\n",
                "    ax.set_xlabel('hour', fontsize=14)\r\n",
                "    ax.set_ylabel('plug load percentage', fontsize=14)\r\n",
                "    ax.legend(fontsize=12)\r\n",
                "    ax.set_xlim([0,23])\r\n",
                "    ax.set_title(land_type, fontsize=18)\r\n",
                "    fig.savefig('plugload_clean_norm_with_ref_{}.png'.format(land_type.lower()), bbox_inches='tight', dpi=600)\r\n",
                "    plt.close()"
            ],
            "outputs": [],
            "metadata": {}
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "source": [],
            "outputs": [],
            "metadata": {}
        }
    ],
    "metadata": {
        "orig_nbformat": 4,
        "language_info": {
            "name": "python",
            "version": "3.7.10",
            "mimetype": "text/x-python",
            "codemirror_mode": {
                "name": "ipython",
                "version": 3
            },
            "pygments_lexer": "ipython3",
            "nbconvert_exporter": "python",
            "file_extension": ".py"
        },
        "kernelspec": {
            "name": "python3",
            "display_name": "Python 3.7.10 64-bit ('base': conda)"
        },
        "interpreter": {
            "hash": "17d820ce7147816d2063c5052dbf10120f107a9a4f06387eb6d5dd9d2b00117c"
        }
    },
    "nbformat": 4,
    "nbformat_minor": 2
}