requests_mwiti.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime, timedelta\n",
    "import json\n",
    "import requests\n",
    "import dotenv\n",
    "import os\n",
    "\n",
    "dotenv.load_dotenv(\".env\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>date</th>\n",
       "      <th>temperature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-01-01 00:00:45</td>\n",
       "      <td>21.035860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-01-01 00:01:50</td>\n",
       "      <td>21.020624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-01-01 00:02:54</td>\n",
       "      <td>21.000704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-01-01 00:03:59</td>\n",
       "      <td>20.974922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-01-01 00:05:04</td>\n",
       "      <td>20.947382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>384999</th>\n",
       "      <td>2022-12-19 20:26:38</td>\n",
       "      <td>22.543673</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>385000</th>\n",
       "      <td>2022-12-19 20:27:43</td>\n",
       "      <td>22.540352</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>385001</th>\n",
       "      <td>2022-12-19 20:28:48</td>\n",
       "      <td>22.541523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>385002</th>\n",
       "      <td>2022-12-19 20:29:54</td>\n",
       "      <td>22.536640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>385003</th>\n",
       "      <td>2022-12-19 20:30:59</td>\n",
       "      <td>22.531563</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>385004 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       date  temperature\n",
       "0       2022-01-01 00:00:45    21.035860\n",
       "1       2022-01-01 00:01:50    21.020624\n",
       "2       2022-01-01 00:02:54    21.000704\n",
       "3       2022-01-01 00:03:59    20.974922\n",
       "4       2022-01-01 00:05:04    20.947382\n",
       "...                     ...          ...\n",
       "384999  2022-12-19 20:26:38    22.543673\n",
       "385000  2022-12-19 20:27:43    22.540352\n",
       "385001  2022-12-19 20:28:48    22.541523\n",
       "385002  2022-12-19 20:29:54    22.536640\n",
       "385003  2022-12-19 20:30:59    22.531563\n",
       "\n",
       "[385004 rows x 2 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_temp = pd.read_csv('data/weather/temperature.csv')\n",
    "df_temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "ename": "",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31mThe Kernel crashed while executing code in the current cell or a previous cell. \n",
      "\u001b[1;31mPlease review the code in the cell(s) to identify a possible cause of the failure. \n",
      "\u001b[1;31mClick <a href='https://aka.ms/vscodeJupyterKernelCrash'>here</a> for more info. \n",
      "\u001b[1;31mView Jupyter <a href='command:jupyter.viewOutput'>log</a> for further details."
     ]
    }
   ],
   "source": [
    "def date_time_getter(combined_date):\n",
    "  date = combined_date.split(' ')[0]\n",
    "  time = combined_date.split(' ')[1]\n",
    "  return date, time\n",
    "\n",
    "df_temp[['actual_date', 'actual_time']] = df_temp['date'].apply(lambda x: pd.Series(date_time_getter(x)))\n",
    "# df_temp['actual_date'] = pd.to_datetime(df_temp['actual_date'])\n",
    "\n",
    "df_temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>date</th>\n",
       "      <th>time</th>\n",
       "      <th>temperature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>00:00:45</td>\n",
       "      <td>21.035860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>00:01:50</td>\n",
       "      <td>21.020624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>00:02:54</td>\n",
       "      <td>21.000704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>00:03:59</td>\n",
       "      <td>20.974922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>00:05:04</td>\n",
       "      <td>20.947382</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date      time  temperature\n",
       "0  2022-01-01  00:00:45    21.035860\n",
       "1  2022-01-01  00:01:50    21.020624\n",
       "2  2022-01-01  00:02:54    21.000704\n",
       "3  2022-01-01  00:03:59    20.974922\n",
       "4  2022-01-01  00:05:04    20.947382"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df_temp.copy()\n",
    "\n",
    "df.drop(columns='date',inplace=True)\n",
    "df = df[['actual_date','actual_time','temperature']] \n",
    "df.rename(columns = {'actual_date':'date','actual_time':'time'}, inplace = True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 385004 entries, 0 to 385003\n",
      "Data columns (total 3 columns):\n",
      " #   Column       Non-Null Count   Dtype  \n",
      "---  ------       --------------   -----  \n",
      " 0   date         385004 non-null  object \n",
      " 1   time         385004 non-null  object \n",
      " 2   temperature  385004 non-null  float64\n",
      "dtypes: float64(1), object(2)\n",
      "memory usage: 8.8+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>date</th>\n",
       "      <th>hour</th>\n",
       "      <th>temperature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>20.640966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>20.200706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>19.873540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>3</td>\n",
       "      <td>19.522044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>4</td>\n",
       "      <td>20.097169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7515</th>\n",
       "      <td>2022-12-19</td>\n",
       "      <td>16</td>\n",
       "      <td>23.728903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7516</th>\n",
       "      <td>2022-12-19</td>\n",
       "      <td>17</td>\n",
       "      <td>23.288150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7517</th>\n",
       "      <td>2022-12-19</td>\n",
       "      <td>18</td>\n",
       "      <td>22.806882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7518</th>\n",
       "      <td>2022-12-19</td>\n",
       "      <td>19</td>\n",
       "      <td>22.649602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7519</th>\n",
       "      <td>2022-12-19</td>\n",
       "      <td>20</td>\n",
       "      <td>22.570625</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7520 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            date  hour  temperature\n",
       "0     2022-01-01     0    20.640966\n",
       "1     2022-01-01     1    20.200706\n",
       "2     2022-01-01     2    19.873540\n",
       "3     2022-01-01     3    19.522044\n",
       "4     2022-01-01     4    20.097169\n",
       "...          ...   ...          ...\n",
       "7515  2022-12-19    16    23.728903\n",
       "7516  2022-12-19    17    23.288150\n",
       "7517  2022-12-19    18    22.806882\n",
       "7518  2022-12-19    19    22.649602\n",
       "7519  2022-12-19    20    22.570625\n",
       "\n",
       "[7520 rows x 3 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def aggregate_temperatures_per_hour(df):\n",
    "    # Convert 'time' column to datetime object\n",
    "    df['time'] = pd.to_datetime(df['time'],format=\"%H:%M:%S\")\n",
    "    \n",
    "    # Extract hour component from 'time' column\n",
    "    df['hour'] = df['time'].dt.hour\n",
    "    \n",
    "    # Group by 'date' and 'hour' and calculate mean temperature\n",
    "    aggregated_df = df.groupby(['date', 'hour'])['temperature'].mean().reset_index()\n",
    "    \n",
    "    return aggregated_df\n",
    "  \n",
    "df_aggregate = aggregate_temperatures_per_hour(df)\n",
    "df_aggregate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>facility_id</th>\n",
       "      <th>CarParkName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Tallawong Station Car Park (historical only)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>Warriewood Car Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>Narrabeen Car Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12</td>\n",
       "      <td>Mona Vale Car Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13</td>\n",
       "      <td>Dee Why Car Park</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   facility_id                                   CarParkName\n",
       "0            1  Tallawong Station Car Park (historical only)\n",
       "1           10                           Warriewood Car Park\n",
       "2           11                            Narrabeen Car Park\n",
       "3           12                            Mona Vale Car Park\n",
       "4           13                              Dee Why Car Park"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cp = pd.read_json('data/carparks_original.json')\n",
    "cp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>facility_id</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>-33.756009</td>\n",
       "      <td>151.154528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>-34.672518</td>\n",
       "      <td>150.854695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>-33.423883</td>\n",
       "      <td>151.341711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>-33.952460</td>\n",
       "      <td>151.014838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>-33.697777</td>\n",
       "      <td>151.300667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   facility_id  longitude    latitude\n",
       "0            6 -33.756009  151.154528\n",
       "1            7 -34.672518  150.854695\n",
       "2            8 -33.423883  151.341711\n",
       "3            9 -33.952460  151.014838\n",
       "4           10 -33.697777  151.300667"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "coords_df = pd.read_json('data/coords.json')\n",
    "coords_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>facility_id</th>\n",
       "      <th>CarParkName</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Tallawong Station Car Park (historical only)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2</td>\n",
       "      <td>Kellyville Station Car Park (historical only)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>3</td>\n",
       "      <td>Bella Vista Station Car Park (historical only)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>4</td>\n",
       "      <td>Hills Showground Station Car Park (historical ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>5</td>\n",
       "      <td>Cherrybrook Station Car Park (historical only)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>6</td>\n",
       "      <td>Gordon Henry St North Car Park</td>\n",
       "      <td>-33.756009</td>\n",
       "      <td>151.154528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>7</td>\n",
       "      <td>Kiama Car Park</td>\n",
       "      <td>-34.672518</td>\n",
       "      <td>150.854695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>8</td>\n",
       "      <td>Gosford Car Park</td>\n",
       "      <td>-33.423883</td>\n",
       "      <td>151.341711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>9</td>\n",
       "      <td>Revesby Car Park</td>\n",
       "      <td>-33.952460</td>\n",
       "      <td>151.014838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>Warriewood Car Park</td>\n",
       "      <td>-33.697777</td>\n",
       "      <td>151.300667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>Narrabeen Car Park</td>\n",
       "      <td>-33.713514</td>\n",
       "      <td>151.297315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12</td>\n",
       "      <td>Mona Vale Car Park</td>\n",
       "      <td>-33.677276</td>\n",
       "      <td>151.305146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13</td>\n",
       "      <td>Dee Why Car Park</td>\n",
       "      <td>-33.752797</td>\n",
       "      <td>151.286485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>14</td>\n",
       "      <td>West Ryde Car Park</td>\n",
       "      <td>-33.807172</td>\n",
       "      <td>151.090229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>15</td>\n",
       "      <td>Sutherland East Parade Car Park</td>\n",
       "      <td>-34.031787</td>\n",
       "      <td>151.057190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>16</td>\n",
       "      <td>Leppington Car Park</td>\n",
       "      <td>-33.954400</td>\n",
       "      <td>150.808100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>17</td>\n",
       "      <td>Edmondson Park South Car Park</td>\n",
       "      <td>-33.969300</td>\n",
       "      <td>150.858700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>18</td>\n",
       "      <td>St Marys Car Park</td>\n",
       "      <td>-33.762256</td>\n",
       "      <td>150.776029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>19</td>\n",
       "      <td>Campbelltown Farrow Rd North Car Park</td>\n",
       "      <td>-34.063835</td>\n",
       "      <td>150.813929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>20</td>\n",
       "      <td>Campbelltown Hurley Street South Car Park</td>\n",
       "      <td>-34.063835</td>\n",
       "      <td>150.813929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>21</td>\n",
       "      <td>Penrith Combewood At-Grade Car Park</td>\n",
       "      <td>-33.750055</td>\n",
       "      <td>150.696135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>22</td>\n",
       "      <td>Penrith Combewood Multi-Level Car Park</td>\n",
       "      <td>-33.750055</td>\n",
       "      <td>150.696135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>23</td>\n",
       "      <td>Warwick Farm Car Park</td>\n",
       "      <td>-33.913450</td>\n",
       "      <td>150.935036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>24</td>\n",
       "      <td>Schofields Car Park</td>\n",
       "      <td>-33.704477</td>\n",
       "      <td>150.873817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>25</td>\n",
       "      <td>Hornsby Jersey St Car Park</td>\n",
       "      <td>-33.702801</td>\n",
       "      <td>151.098494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>26</td>\n",
       "      <td>Tallawong P1 Car Park</td>\n",
       "      <td>-33.691630</td>\n",
       "      <td>150.906022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>27</td>\n",
       "      <td>Tallawong P2 Car Park</td>\n",
       "      <td>-33.691630</td>\n",
       "      <td>150.906022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>28</td>\n",
       "      <td>Tallawong P3 Car Park</td>\n",
       "      <td>-33.691630</td>\n",
       "      <td>150.906022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>29</td>\n",
       "      <td>Kellyville North Car Park</td>\n",
       "      <td>-33.713514</td>\n",
       "      <td>150.935304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>30</td>\n",
       "      <td>Kellyville South Car Park</td>\n",
       "      <td>-33.713514</td>\n",
       "      <td>150.935304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>31</td>\n",
       "      <td>Bella Vista Car Park</td>\n",
       "      <td>-33.730592</td>\n",
       "      <td>150.944024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>32</td>\n",
       "      <td>Hills Showground Car Park</td>\n",
       "      <td>-33.727820</td>\n",
       "      <td>150.987345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>33</td>\n",
       "      <td>Cherrybrook Car Park</td>\n",
       "      <td>-33.736703</td>\n",
       "      <td>151.031977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>486</td>\n",
       "      <td>Ashfield Car Park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>487</td>\n",
       "      <td>Kogarah Car Park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>488</td>\n",
       "      <td>Seven Hills Car Park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>489</td>\n",
       "      <td>Manly Vale Car Park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>490</td>\n",
       "      <td>Brookvale Car Park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    facility_id                                        CarParkName  longitude  \\\n",
       "0             1       Tallawong Station Car Park (historical only)        NaN   \n",
       "11            2      Kellyville Station Car Park (historical only)        NaN   \n",
       "22            3     Bella Vista Station Car Park (historical only)        NaN   \n",
       "27            4  Hills Showground Station Car Park (historical ...        NaN   \n",
       "33            5     Cherrybrook Station Car Park (historical only)        NaN   \n",
       "34            6                     Gordon Henry St North Car Park -33.756009   \n",
       "35            7                                     Kiama Car Park -34.672518   \n",
       "36            8                                   Gosford Car Park -33.423883   \n",
       "37            9                                   Revesby Car Park -33.952460   \n",
       "1            10                                Warriewood Car Park -33.697777   \n",
       "2            11                                 Narrabeen Car Park -33.713514   \n",
       "3            12                                 Mona Vale Car Park -33.677276   \n",
       "4            13                                   Dee Why Car Park -33.752797   \n",
       "5            14                                 West Ryde Car Park -33.807172   \n",
       "6            15                    Sutherland East Parade Car Park -34.031787   \n",
       "7            16                                Leppington Car Park -33.954400   \n",
       "8            17                      Edmondson Park South Car Park -33.969300   \n",
       "9            18                                  St Marys Car Park -33.762256   \n",
       "10           19              Campbelltown Farrow Rd North Car Park -34.063835   \n",
       "12           20          Campbelltown Hurley Street South Car Park -34.063835   \n",
       "13           21                Penrith Combewood At-Grade Car Park -33.750055   \n",
       "14           22             Penrith Combewood Multi-Level Car Park -33.750055   \n",
       "15           23                              Warwick Farm Car Park -33.913450   \n",
       "16           24                                Schofields Car Park -33.704477   \n",
       "17           25                         Hornsby Jersey St Car Park -33.702801   \n",
       "18           26                              Tallawong P1 Car Park -33.691630   \n",
       "19           27                              Tallawong P2 Car Park -33.691630   \n",
       "20           28                              Tallawong P3 Car Park -33.691630   \n",
       "21           29                          Kellyville North Car Park -33.713514   \n",
       "23           30                          Kellyville South Car Park -33.713514   \n",
       "24           31                               Bella Vista Car Park -33.730592   \n",
       "25           32                          Hills Showground Car Park -33.727820   \n",
       "26           33                               Cherrybrook Car Park -33.736703   \n",
       "28          486                                  Ashfield Car Park        NaN   \n",
       "29          487                                   Kogarah Car Park        NaN   \n",
       "30          488                               Seven Hills Car Park        NaN   \n",
       "31          489                                Manly Vale Car Park        NaN   \n",
       "32          490                                 Brookvale Car Park        NaN   \n",
       "\n",
       "      latitude  \n",
       "0          NaN  \n",
       "11         NaN  \n",
       "22         NaN  \n",
       "27         NaN  \n",
       "33         NaN  \n",
       "34  151.154528  \n",
       "35  150.854695  \n",
       "36  151.341711  \n",
       "37  151.014838  \n",
       "1   151.300667  \n",
       "2   151.297315  \n",
       "3   151.305146  \n",
       "4   151.286485  \n",
       "5   151.090229  \n",
       "6   151.057190  \n",
       "7   150.808100  \n",
       "8   150.858700  \n",
       "9   150.776029  \n",
       "10  150.813929  \n",
       "12  150.813929  \n",
       "13  150.696135  \n",
       "14  150.696135  \n",
       "15  150.935036  \n",
       "16  150.873817  \n",
       "17  151.098494  \n",
       "18  150.906022  \n",
       "19  150.906022  \n",
       "20  150.906022  \n",
       "21  150.935304  \n",
       "23  150.935304  \n",
       "24  150.944024  \n",
       "25  150.987345  \n",
       "26  151.031977  \n",
       "28         NaN  \n",
       "29         NaN  \n",
       "30         NaN  \n",
       "31         NaN  \n",
       "32         NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df = cp.copy()\n",
    "\n",
    "final_df = final_df.merge(coords_df,on='facility_id',how='left')\n",
    "\n",
    "final_df.sort_values(by='facility_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>tsn</th>\n",
       "      <th>time</th>\n",
       "      <th>spots</th>\n",
       "      <th>zones</th>\n",
       "      <th>ParkID</th>\n",
       "      <th>occupancy</th>\n",
       "      <th>MessageDate</th>\n",
       "      <th>facility_id</th>\n",
       "      <th>facility_name</th>\n",
       "      <th>tfnsw_facility_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2101131</td>\n",
       "      <td>749399962</td>\n",
       "      <td>244</td>\n",
       "      <td>[{'occupancy': {'loop': None, 'monthlies': Non...</td>\n",
       "      <td>1</td>\n",
       "      <td>{'loop': None, 'monthlies': None, 'open_gate':...</td>\n",
       "      <td>2023-10-01T00:39:22</td>\n",
       "      <td>10</td>\n",
       "      <td>Warriewood Car Park</td>\n",
       "      <td>2101131TPR001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2101131</td>\n",
       "      <td>749422317</td>\n",
       "      <td>244</td>\n",
       "      <td>[{'occupancy': {'loop': None, 'monthlies': Non...</td>\n",
       "      <td>1</td>\n",
       "      <td>{'loop': None, 'monthlies': None, 'open_gate':...</td>\n",
       "      <td>2023-10-01T07:51:57</td>\n",
       "      <td>10</td>\n",
       "      <td>Warriewood Car Park</td>\n",
       "      <td>2101131TPR001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2101131</td>\n",
       "      <td>749431023</td>\n",
       "      <td>244</td>\n",
       "      <td>[{'occupancy': {'loop': None, 'monthlies': Non...</td>\n",
       "      <td>1</td>\n",
       "      <td>{'loop': None, 'monthlies': None, 'open_gate':...</td>\n",
       "      <td>2023-10-01T10:17:03</td>\n",
       "      <td>10</td>\n",
       "      <td>Warriewood Car Park</td>\n",
       "      <td>2101131TPR001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2101131</td>\n",
       "      <td>749431177</td>\n",
       "      <td>244</td>\n",
       "      <td>[{'occupancy': {'loop': None, 'monthlies': Non...</td>\n",
       "      <td>1</td>\n",
       "      <td>{'loop': None, 'monthlies': None, 'open_gate':...</td>\n",
       "      <td>2023-10-01T10:19:37</td>\n",
       "      <td>10</td>\n",
       "      <td>Warriewood Car Park</td>\n",
       "      <td>2101131TPR001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2101131</td>\n",
       "      <td>749431421</td>\n",
       "      <td>244</td>\n",
       "      <td>[{'occupancy': {'loop': None, 'monthlies': Non...</td>\n",
       "      <td>1</td>\n",
       "      <td>{'loop': None, 'monthlies': None, 'open_gate':...</td>\n",
       "      <td>2023-10-01T10:23:41</td>\n",
       "      <td>10</td>\n",
       "      <td>Warriewood Car Park</td>\n",
       "      <td>2101131TPR001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2101131</td>\n",
       "      <td>749431470</td>\n",
       "      <td>244</td>\n",
       "      <td>[{'occupancy': {'loop': None, 'monthlies': Non...</td>\n",
       "      <td>1</td>\n",
       "      <td>{'loop': None, 'monthlies': None, 'open_gate':...</td>\n",
       "      <td>2023-10-01T10:24:30</td>\n",
       "      <td>10</td>\n",
       "      <td>Warriewood Car Park</td>\n",
       "      <td>2101131TPR001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2101131</td>\n",
       "      <td>749432420</td>\n",
       "      <td>244</td>\n",
       "      <td>[{'occupancy': {'loop': None, 'monthlies': Non...</td>\n",
       "      <td>1</td>\n",
       "      <td>{'loop': None, 'monthlies': None, 'open_gate':...</td>\n",
       "      <td>2023-10-01T10:40:20</td>\n",
       "      <td>10</td>\n",
       "      <td>Warriewood Car Park</td>\n",
       "      <td>2101131TPR001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2101131</td>\n",
       "      <td>749435227</td>\n",
       "      <td>244</td>\n",
       "      <td>[{'occupancy': {'loop': None, 'monthlies': Non...</td>\n",
       "      <td>1</td>\n",
       "      <td>{'loop': None, 'monthlies': None, 'open_gate':...</td>\n",
       "      <td>2023-10-01T11:27:07</td>\n",
       "      <td>10</td>\n",
       "      <td>Warriewood Car Park</td>\n",
       "      <td>2101131TPR001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2101131</td>\n",
       "      <td>749444612</td>\n",
       "      <td>244</td>\n",
       "      <td>[{'occupancy': {'loop': None, 'monthlies': Non...</td>\n",
       "      <td>1</td>\n",
       "      <td>{'loop': None, 'monthlies': None, 'open_gate':...</td>\n",
       "      <td>2023-10-01T14:03:32</td>\n",
       "      <td>10</td>\n",
       "      <td>Warriewood Car Park</td>\n",
       "      <td>2101131TPR001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2101131</td>\n",
       "      <td>749445426</td>\n",
       "      <td>244</td>\n",
       "      <td>[{'occupancy': {'loop': None, 'monthlies': Non...</td>\n",
       "      <td>1</td>\n",
       "      <td>{'loop': None, 'monthlies': None, 'open_gate':...</td>\n",
       "      <td>2023-10-01T14:17:06</td>\n",
       "      <td>10</td>\n",
       "      <td>Warriewood Car Park</td>\n",
       "      <td>2101131TPR001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       tsn       time spots  \\\n",
       "0  2101131  749399962   244   \n",
       "1  2101131  749422317   244   \n",
       "2  2101131  749431023   244   \n",
       "3  2101131  749431177   244   \n",
       "4  2101131  749431421   244   \n",
       "5  2101131  749431470   244   \n",
       "6  2101131  749432420   244   \n",
       "7  2101131  749435227   244   \n",
       "8  2101131  749444612   244   \n",
       "9  2101131  749445426   244   \n",
       "\n",
       "                                               zones ParkID  \\\n",
       "0  [{'occupancy': {'loop': None, 'monthlies': Non...      1   \n",
       "1  [{'occupancy': {'loop': None, 'monthlies': Non...      1   \n",
       "2  [{'occupancy': {'loop': None, 'monthlies': Non...      1   \n",
       "3  [{'occupancy': {'loop': None, 'monthlies': Non...      1   \n",
       "4  [{'occupancy': {'loop': None, 'monthlies': Non...      1   \n",
       "5  [{'occupancy': {'loop': None, 'monthlies': Non...      1   \n",
       "6  [{'occupancy': {'loop': None, 'monthlies': Non...      1   \n",
       "7  [{'occupancy': {'loop': None, 'monthlies': Non...      1   \n",
       "8  [{'occupancy': {'loop': None, 'monthlies': Non...      1   \n",
       "9  [{'occupancy': {'loop': None, 'monthlies': Non...      1   \n",
       "\n",
       "                                           occupancy          MessageDate  \\\n",
       "0  {'loop': None, 'monthlies': None, 'open_gate':...  2023-10-01T00:39:22   \n",
       "1  {'loop': None, 'monthlies': None, 'open_gate':...  2023-10-01T07:51:57   \n",
       "2  {'loop': None, 'monthlies': None, 'open_gate':...  2023-10-01T10:17:03   \n",
       "3  {'loop': None, 'monthlies': None, 'open_gate':...  2023-10-01T10:19:37   \n",
       "4  {'loop': None, 'monthlies': None, 'open_gate':...  2023-10-01T10:23:41   \n",
       "5  {'loop': None, 'monthlies': None, 'open_gate':...  2023-10-01T10:24:30   \n",
       "6  {'loop': None, 'monthlies': None, 'open_gate':...  2023-10-01T10:40:20   \n",
       "7  {'loop': None, 'monthlies': None, 'open_gate':...  2023-10-01T11:27:07   \n",
       "8  {'loop': None, 'monthlies': None, 'open_gate':...  2023-10-01T14:03:32   \n",
       "9  {'loop': None, 'monthlies': None, 'open_gate':...  2023-10-01T14:17:06   \n",
       "\n",
       "  facility_id        facility_name tfnsw_facility_id  \n",
       "0          10  Warriewood Car Park     2101131TPR001  \n",
       "1          10  Warriewood Car Park     2101131TPR001  \n",
       "2          10  Warriewood Car Park     2101131TPR001  \n",
       "3          10  Warriewood Car Park     2101131TPR001  \n",
       "4          10  Warriewood Car Park     2101131TPR001  \n",
       "5          10  Warriewood Car Park     2101131TPR001  \n",
       "6          10  Warriewood Car Park     2101131TPR001  \n",
       "7          10  Warriewood Car Park     2101131TPR001  \n",
       "8          10  Warriewood Car Park     2101131TPR001  \n",
       "9          10  Warriewood Car Park     2101131TPR001  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final = pd.read_parquet('data/carpark_history_3_months_combined.parquet')\n",
    "df_final.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1691049 entries, 0 to 1691048\n",
      "Data columns (total 10 columns):\n",
      " #   Column             Dtype \n",
      "---  ------             ----- \n",
      " 0   tsn                object\n",
      " 1   time               object\n",
      " 2   spots              object\n",
      " 3   zones              object\n",
      " 4   ParkID             object\n",
      " 5   occupancy          object\n",
      " 6   MessageDate        object\n",
      " 7   facility_id        object\n",
      " 8   facility_name      object\n",
      " 9   tfnsw_facility_id  object\n",
      "dtypes: object(10)\n",
      "memory usage: 129.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df_final.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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": "0.0.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}