Data-prep.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a59b88a4-eab7-467d-ab8a-3bee6dba1254",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sdv.metadata import SingleTableMetadata\n",
    "from sdv.single_table import CTGANSynthesizer\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "import numpy as np\n",
    "import osmnx as ox"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a39701e6-a167-43a7-805a-333c8bce942e",
   "metadata": {},
   "source": [
    "## Download Pedestrian Collision Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "49f4a76c-6ecf-44c0-a825-4f844a1a5248",
   "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>INCIDENT INFO</th>\n",
       "      <th>DESCRIPTION</th>\n",
       "      <th>START_DT</th>\n",
       "      <th>MODIFIED_DT</th>\n",
       "      <th>QUADRANT</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Count</th>\n",
       "      <th>id</th>\n",
       "      <th>Point</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Westbound Memorial Drive and 9 Street NW</td>\n",
       "      <td>Traffic incident. Blocking the right lane</td>\n",
       "      <td>2023/11/27 08:55:49 AM</td>\n",
       "      <td>2023/11/27 08:57:14 AM</td>\n",
       "      <td>NW</td>\n",
       "      <td>-114.081269</td>\n",
       "      <td>51.053693</td>\n",
       "      <td>1</td>\n",
       "      <td>2023-11-27T08:55:4951.053693443345296-114.0812...</td>\n",
       "      <td>POINT (-114.08126874525114 51.053693443345296)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Eastbound Glenmore Trail and Crowchild Trail SW</td>\n",
       "      <td>Traffic incident. Blocking the right lane</td>\n",
       "      <td>2023/11/27 07:44:17 AM</td>\n",
       "      <td>2023/11/27 07:45:31 AM</td>\n",
       "      <td>SW</td>\n",
       "      <td>-114.122814</td>\n",
       "      <td>51.001318</td>\n",
       "      <td>1</td>\n",
       "      <td>2023-11-27T07:44:1751.00131795970452-114.12281...</td>\n",
       "      <td>POINT (-114.12281385934625 51.00131795970452)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Eastbound 17 Avenue east of Deerfoot Trail SE</td>\n",
       "      <td>Traffic incident. Blocking the right lane</td>\n",
       "      <td>2023/11/27 07:06:55 AM</td>\n",
       "      <td>2023/11/27 07:08:46 AM</td>\n",
       "      <td>SE</td>\n",
       "      <td>-114.011517</td>\n",
       "      <td>51.037175</td>\n",
       "      <td>1</td>\n",
       "      <td>2023-11-27T07:06:5551.03717516674427-114.01151...</td>\n",
       "      <td>POINT (-114.01151686241202 51.03717516674427)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Southbound 36 Street at 8 Avenue NE</td>\n",
       "      <td>Two vehicle incident. Blocking the left lanes</td>\n",
       "      <td>2023/11/26 05:47:39 PM</td>\n",
       "      <td>2023/11/26 06:08:18 PM</td>\n",
       "      <td>NE</td>\n",
       "      <td>-113.981869</td>\n",
       "      <td>51.060388</td>\n",
       "      <td>1</td>\n",
       "      <td>2023-11-26T17:47:3951.06038813992116-113.98186...</td>\n",
       "      <td>POINT (-113.98186891621388 51.06038813992116)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tsuut'ina Trail and 130 Avenue SW</td>\n",
       "      <td>Traffic incident.</td>\n",
       "      <td>2023/11/26 05:07:18 PM</td>\n",
       "      <td>2023/11/26 05:50:25 PM</td>\n",
       "      <td>SW</td>\n",
       "      <td>-114.138711</td>\n",
       "      <td>50.936205</td>\n",
       "      <td>1</td>\n",
       "      <td>2023-11-26T17:07:1850.93620462055178-114.13871...</td>\n",
       "      <td>POINT (-114.13871128738727 50.93620462055178)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       INCIDENT INFO  \\\n",
       "0          Westbound Memorial Drive and 9 Street NW    \n",
       "1   Eastbound Glenmore Trail and Crowchild Trail SW    \n",
       "2     Eastbound 17 Avenue east of Deerfoot Trail SE    \n",
       "3               Southbound 36 Street at 8 Avenue NE    \n",
       "4                 Tsuut'ina Trail and 130 Avenue SW    \n",
       "\n",
       "                                     DESCRIPTION                START_DT  \\\n",
       "0      Traffic incident. Blocking the right lane  2023/11/27 08:55:49 AM   \n",
       "1      Traffic incident. Blocking the right lane  2023/11/27 07:44:17 AM   \n",
       "2      Traffic incident. Blocking the right lane  2023/11/27 07:06:55 AM   \n",
       "3  Two vehicle incident. Blocking the left lanes  2023/11/26 05:47:39 PM   \n",
       "4                              Traffic incident.  2023/11/26 05:07:18 PM   \n",
       "\n",
       "              MODIFIED_DT QUADRANT   Longitude   Latitude  Count  \\\n",
       "0  2023/11/27 08:57:14 AM       NW -114.081269  51.053693      1   \n",
       "1  2023/11/27 07:45:31 AM       SW -114.122814  51.001318      1   \n",
       "2  2023/11/27 07:08:46 AM       SE -114.011517  51.037175      1   \n",
       "3  2023/11/26 06:08:18 PM       NE -113.981869  51.060388      1   \n",
       "4  2023/11/26 05:50:25 PM       SW -114.138711  50.936205      1   \n",
       "\n",
       "                                                  id  \\\n",
       "0  2023-11-27T08:55:4951.053693443345296-114.0812...   \n",
       "1  2023-11-27T07:44:1751.00131795970452-114.12281...   \n",
       "2  2023-11-27T07:06:5551.03717516674427-114.01151...   \n",
       "3  2023-11-26T17:47:3951.06038813992116-113.98186...   \n",
       "4  2023-11-26T17:07:1850.93620462055178-114.13871...   \n",
       "\n",
       "                                            Point  \n",
       "0  POINT (-114.08126874525114 51.053693443345296)  \n",
       "1   POINT (-114.12281385934625 51.00131795970452)  \n",
       "2   POINT (-114.01151686241202 51.03717516674427)  \n",
       "3   POINT (-113.98186891621388 51.06038813992116)  \n",
       "4   POINT (-114.13871128738727 50.93620462055178)  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Traffic incidents open data from https://data.calgary.ca/Transportation-Transit/Traffic-Incidents/35ra-9556\n",
    "\n",
    "data = pd.read_csv('Traffic_Incidents.csv')\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ff43048-0f34-44d4-80a4-edb9ec4a44c7",
   "metadata": {},
   "source": [
    "## Download Climate Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ebadd2e2-cea0-4418-871c-44253e9d7154",
   "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>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>MIN_TEMPERATURE</th>\n",
       "      <th>PRECIPITATION</th>\n",
       "      <th>SNOW_ON_GROUND</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>12.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>10.7</td>\n",
       "      <td>25.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>13.4</td>\n",
       "      <td>3.8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>12.5</td>\n",
       "      <td>0.4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  Month  Day  MIN_TEMPERATURE  PRECIPITATION  SNOW_ON_GROUND\n",
       "0  2016      8    4              NaN            NaN             NaN\n",
       "1  2016      8    5             12.5            0.0             NaN\n",
       "2  2016      8    6             10.7           25.5             NaN\n",
       "3  2016      8    7             13.4            3.8             NaN\n",
       "4  2016      8    8             12.5            0.4             NaN"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "climate = pd.read_csv('climate-daily.csv')\n",
    "climate = climate[[\"LOCAL_YEAR\",\"LOCAL_MONTH\",\"LOCAL_DAY\",\"MIN_TEMPERATURE\",\"TOTAL_PRECIPITATION\",\"SNOW_ON_GROUND\"]]\n",
    "climate = climate.rename(columns={\"LOCAL_YEAR\": \"Year\", \"LOCAL_MONTH\": \"Month\",\"LOCAL_DAY\":\"Day\",\"TOTAL_PRECIPITATION\":\"PRECIPITATION\" })\n",
    "climate.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bdd4231-3fd2-4d5a-95ff-af910d819631",
   "metadata": {},
   "source": [
    "## Data Cleanup and Feature Engineering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "63e047cf-d7c9-48b2-9712-2f02d572bd90",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/cd/p8vfp4vx48ddsfxtyzycyblm0000gn/T/ipykernel_6416/4030159769.py:8: UserWarning: Could not infer format, so each element will be parsed individually, falling back to `dateutil`. To ensure parsing is consistent and as-expected, please specify a format.\n",
      "  data['START_DT'] = pd.to_datetime(data['START_DT'])\n"
     ]
    }
   ],
   "source": [
    "# Drop NA values\n",
    "data = data.dropna(subset=['DESCRIPTION', 'START_DT',\"Longitude\",'Latitude'])\n",
    "\n",
    "# Filter only pedestrian collisions\n",
    "data= data[data[\"DESCRIPTION\"].str.contains(\"ped\", case=False)]\n",
    "\n",
    "# Convert column to datetime\n",
    "data['START_DT'] = pd.to_datetime(data['START_DT'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8b0ad88b-1c79-47cc-9490-18885854bf07",
   "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>Longitude</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Month</th>\n",
       "      <th>DayofWeek</th>\n",
       "      <th>Hour</th>\n",
       "      <th>Year</th>\n",
       "      <th>Day</th>\n",
       "      <th>Season</th>\n",
       "      <th>Workday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-114.068607</td>\n",
       "      <td>51.002510</td>\n",
       "      <td>11</td>\n",
       "      <td>6</td>\n",
       "      <td>16</td>\n",
       "      <td>2023</td>\n",
       "      <td>26</td>\n",
       "      <td>Winter</td>\n",
       "      <td>Weekend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>-114.169710</td>\n",
       "      <td>51.018934</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>18</td>\n",
       "      <td>2023</td>\n",
       "      <td>24</td>\n",
       "      <td>Winter</td>\n",
       "      <td>Weekday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>-114.161009</td>\n",
       "      <td>51.021726</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>2023</td>\n",
       "      <td>24</td>\n",
       "      <td>Winter</td>\n",
       "      <td>Weekday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>-114.062535</td>\n",
       "      <td>51.059772</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>2023</td>\n",
       "      <td>23</td>\n",
       "      <td>Winter</td>\n",
       "      <td>Weekday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>-114.111903</td>\n",
       "      <td>51.005006</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>2023</td>\n",
       "      <td>23</td>\n",
       "      <td>Winter</td>\n",
       "      <td>Weekday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Longitude   Latitude  Month  DayofWeek  Hour  Year  Day  Season  Workday\n",
       "5  -114.068607  51.002510     11          6    16  2023   26  Winter  Weekend\n",
       "40 -114.169710  51.018934     11          4    18  2023   24  Winter  Weekday\n",
       "41 -114.161009  51.021726     11          4    17  2023   24  Winter  Weekday\n",
       "63 -114.062535  51.059772     11          3    20  2023   23  Winter  Weekday\n",
       "64 -114.111903  51.005006     11          3    20  2023   23  Winter  Weekday"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = data[[\"START_DT\",\"Longitude\",\"Latitude\"]].copy()\n",
    "\n",
    "df['Month'] = df['START_DT'].dt.month\n",
    "df['DayofWeek'] = df['START_DT'].dt.dayofweek\n",
    "df['Hour'] = df['START_DT'].dt.hour\n",
    "df['Year'] = df['START_DT'].dt.year\n",
    "df['Day'] = df['START_DT'].dt.day\n",
    "\n",
    "# Categorical variables \n",
    "df['Season'] = pd.cut(df['Month'], bins=[-1, 5, 10, 13], labels=['Winter', 'Summer', 'Winter'],ordered=False).astype(str)\n",
    "df['Workday'] = pd.cut(df['DayofWeek'], bins=[-1, 5, 7], labels=['Weekday', 'Weekend'],ordered=False).astype(str)\n",
    "\n",
    "df = df.drop([\"START_DT\"], axis=1)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "895292a9-55ad-4362-bbd1-117879fa2e50",
   "metadata": {},
   "source": [
    "### Merge Climate and Collision Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3fd341f6-bdba-45e7-8c9a-974b2388eaf8",
   "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>Longitude</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Month</th>\n",
       "      <th>Season</th>\n",
       "      <th>DayofWeek</th>\n",
       "      <th>Workday</th>\n",
       "      <th>Hour</th>\n",
       "      <th>PRECIPITATION</th>\n",
       "      <th>MIN_TEMPERATURE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-114.169710</td>\n",
       "      <td>51.018934</td>\n",
       "      <td>11</td>\n",
       "      <td>Winter</td>\n",
       "      <td>4</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>-11.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-114.161009</td>\n",
       "      <td>51.021726</td>\n",
       "      <td>11</td>\n",
       "      <td>Winter</td>\n",
       "      <td>4</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>-11.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-114.062535</td>\n",
       "      <td>51.059772</td>\n",
       "      <td>11</td>\n",
       "      <td>Winter</td>\n",
       "      <td>3</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>-6.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-114.111903</td>\n",
       "      <td>51.005006</td>\n",
       "      <td>11</td>\n",
       "      <td>Winter</td>\n",
       "      <td>3</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>-6.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-114.091637</td>\n",
       "      <td>51.038428</td>\n",
       "      <td>11</td>\n",
       "      <td>Winter</td>\n",
       "      <td>2</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>-3.2</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1504</th>\n",
       "      <td>-114.014025</td>\n",
       "      <td>51.067037</td>\n",
       "      <td>12</td>\n",
       "      <td>Winter</td>\n",
       "      <td>0</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>-20.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1505</th>\n",
       "      <td>-113.937759</td>\n",
       "      <td>50.914367</td>\n",
       "      <td>12</td>\n",
       "      <td>Winter</td>\n",
       "      <td>3</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>-26.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1506</th>\n",
       "      <td>-114.090045</td>\n",
       "      <td>51.037818</td>\n",
       "      <td>12</td>\n",
       "      <td>Winter</td>\n",
       "      <td>3</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>-26.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1507</th>\n",
       "      <td>-114.076439</td>\n",
       "      <td>51.002463</td>\n",
       "      <td>12</td>\n",
       "      <td>Winter</td>\n",
       "      <td>2</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>-25.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1508</th>\n",
       "      <td>-114.094684</td>\n",
       "      <td>51.031445</td>\n",
       "      <td>12</td>\n",
       "      <td>Winter</td>\n",
       "      <td>2</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>-25.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1509 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Longitude   Latitude  Month  Season  DayofWeek  Workday  Hour  \\\n",
       "0    -114.169710  51.018934     11  Winter          4  Weekday    18   \n",
       "1    -114.161009  51.021726     11  Winter          4  Weekday    17   \n",
       "2    -114.062535  51.059772     11  Winter          3  Weekday    20   \n",
       "3    -114.111903  51.005006     11  Winter          3  Weekday    20   \n",
       "4    -114.091637  51.038428     11  Winter          2  Weekday    18   \n",
       "...          ...        ...    ...     ...        ...      ...   ...   \n",
       "1504 -114.014025  51.067037     12  Winter          0  Weekday    12   \n",
       "1505 -113.937759  50.914367     12  Winter          3  Weekday    20   \n",
       "1506 -114.090045  51.037818     12  Winter          3  Weekday     9   \n",
       "1507 -114.076439  51.002463     12  Winter          2  Weekday    18   \n",
       "1508 -114.094684  51.031445     12  Winter          2  Weekday    14   \n",
       "\n",
       "      PRECIPITATION  MIN_TEMPERATURE  \n",
       "0                 0            -11.3  \n",
       "1                 0            -11.3  \n",
       "2                 1             -6.3  \n",
       "3                 1             -6.3  \n",
       "4                 1             -3.2  \n",
       "...             ...              ...  \n",
       "1504              0            -20.3  \n",
       "1505              0            -26.3  \n",
       "1506              0            -26.3  \n",
       "1507              0            -25.2  \n",
       "1508              0            -25.2  \n",
       "\n",
       "[1509 rows x 9 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final = pd.merge(df, climate, on=[\"Year\",\"Month\",\"Day\"])\n",
    "df_final = df_final[[\"Longitude\",\"Latitude\",\"Month\",\"Season\",\"DayofWeek\",\"Workday\",\"Hour\",\"PRECIPITATION\",\"MIN_TEMPERATURE\"]]\n",
    "df_final['PRECIPITATION'] = df_final['PRECIPITATION'].apply(lambda x: 1 if x>0 else 0)\n",
    "df_final"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5a8548f-66fe-4d1e-849f-52663e27d509",
   "metadata": {},
   "source": [
    "## Negative Synthetic Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31a3655e-e4dd-4c48-bdfb-0a0e319feafd",
   "metadata": {},
   "source": [
    "The pedestrian collision data above represents the positive labels in our classifier. We therfore need to generate negative labels as well. I'll use the `sdv` python library to train a GAN to generate synthetic data and `osmnx` to generate a random sample of points along the road network"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b3d1237-a2f2-4fe4-9f64-4098e86b5a83",
   "metadata": {},
   "source": [
    "### Using `osmnx` to sample negative lable locations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "fe698248-afd4-41de-a18d-8c2c50e7086e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# G = ox.graph_from_place('Calgary, AB, Canada', network_type='drive')\n",
    "# ox.save_graphml(G, 'calgary.graphml')\n",
    "G = ox.load_graphml('calgary.graphml')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "38bb01fe-07ff-4648-8803-068a1662b82f",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/cd/p8vfp4vx48ddsfxtyzycyblm0000gn/T/ipykernel_5088/519044614.py:1: UserWarning: graph should be undirected to not oversample bidirectional edges\n",
      "  points = ox.utils_geo.sample_points(G, len(df))\n",
      "/Users/saadiqmohiuddin/.pyenv/versions/3.10.8/envs/srai/lib/python3.10/site-packages/osmnx/utils_geo.py:50: UserWarning: Geometry is in a geographic CRS. Results from 'interpolate' are likely incorrect. Use 'GeoSeries.to_crs()' to re-project geometries to a projected CRS before this operation.\n",
      "\n",
      "  return lines.interpolate(np.random.rand(n), normalized=True)\n"
     ]
    }
   ],
   "source": [
    "points = ox.utils_geo.sample_points(G, len(df))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89ef1f90-fa01-4b83-9bc9-6932d109871e",
   "metadata": {},
   "source": [
    "### Using `sdv` to generate synthetic tabular data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1f510485-88f2-40ec-b57a-dd66f6a49c06",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{\n",
       "    \"METADATA_SPEC_VERSION\": \"SINGLE_TABLE_V1\",\n",
       "    \"columns\": {\n",
       "        \"Month\": {\n",
       "            \"sdtype\": \"numerical\"\n",
       "        },\n",
       "        \"Season\": {\n",
       "            \"sdtype\": \"categorical\"\n",
       "        },\n",
       "        \"DayofWeek\": {\n",
       "            \"sdtype\": \"categorical\"\n",
       "        },\n",
       "        \"Workday\": {\n",
       "            \"sdtype\": \"categorical\"\n",
       "        },\n",
       "        \"Hour\": {\n",
       "            \"sdtype\": \"numerical\"\n",
       "        },\n",
       "        \"PRECIPITATION\": {\n",
       "            \"sdtype\": \"categorical\"\n",
       "        },\n",
       "        \"MIN_TEMPERATURE\": {\n",
       "            \"sdtype\": \"numerical\"\n",
       "        }\n",
       "    }\n",
       "}"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define table metadata excluding lat lon. Since we don't want the negative locations \n",
    "# to have the same distribution as the positive locations\n",
    "\n",
    "metadata = SingleTableMetadata()\n",
    "metadata.detect_from_dataframe(data=df_final.drop([\"Longitude\",\"Latitude\"], axis=1))\n",
    "metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "026dedf5-b870-431f-821a-3fadfbd61c41",
   "metadata": {},
   "outputs": [],
   "source": [
    "synthesizer = CTGANSynthesizer(\n",
    "    metadata, \n",
    "    enforce_min_max_values=True,\n",
    "    enforce_rounding=True,\n",
    "    epochs=500,\n",
    "    verbose=True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "348074a6-7cb3-40e8-a8fa-c78edb821162",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Gen. (-0.55) | Discrim. (-0.16): 100%|████████| 500/500 [00:30<00:00, 16.31it/s]\n"
     ]
    }
   ],
   "source": [
    "synthesizer.fit(df_final.drop([\"Longitude\",\"Latitude\"], axis=1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "51db2319-834f-4a54-8993-ef2986945615",
   "metadata": {},
   "outputs": [],
   "source": [
    "synthetic_data = synthesizer.sample(\n",
    "    num_rows=df.shape[0]\n",
    ")\n",
    "\n",
    "df_final[\"Target\"]=1\n",
    "synthetic_data[\"Target\"]=0\n",
    "\n",
    "long_lat = list(zip(points.geometry.x.to_list(),points.geometry.y.to_list()))\n",
    "synthetic_data = pd.concat([pd.DataFrame(long_lat, columns=['Longitude','Latitude']),synthetic_data], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c96f3474-f290-425c-8ea6-8feeb28b1efe",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all = pd.concat([synthetic_data, df_final], axis=0).sample(frac=1)\n",
    "df_all = df_all.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "9da70cf9-6d87-4460-8ccd-6e4d695153c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all.to_csv(\"pedestrian-collisions-final.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51697fdf-2008-4a3b-8811-2e2cbc49e8c2",
   "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": "3.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}