Data Extraction with OneMap API.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Extraction with OneMap API - HDB Block Coordinates and Travel Distances between HDB Blocks\n",
    "\n",
    "#### Author: Kenneth Leung (https://www.linkedin.com/in/kennethleung1/)\n",
    "##### Last Updated: 3 Feb 2020\n",
    "\n",
    "#### Background\n",
    "This project is part of a Operations Research term assignment to leverage the principles of linear programming (LP) to tackle a real-life problem. The objective was to develop a LP model that can suggest optimal locations to strategically place health wellness centers in the community to engage the most number of elderly individuals (i.e. maximizing coverage), given a set of pre-determined constraints and assumptions.\n",
    "\n",
    "The code displayed in this notebook shows some of the aspects of data acquisition performed, so as to generate data to feed into the LP model.\n",
    "\n",
    "\n",
    "#### References\n",
    "- SLA Github: https://github.com/sla-onemap/  \n",
    "- PYPI: https://pypi.org/project/python-onemapsg/ (pip install python-onemapsg)  \n",
    "- Official OneMap Documentation: https://docs.onemap.sg/  \n",
    "- OneMap SwaggerHub: https://developers.onemap.sg/swagger  \n",
    "- SingStats Data: https://www.singstat.gov.sg/find-data/search-by-theme/population/geographic-distribution/latest-data  \n",
    "- Information on public housing blocks developed by Housing Development Board (HDB):     https://en.wikipedia.org/wiki/Public_housing_in_Singapore  \n",
    "- Brief information on what a health wellness center does: https://www.ktph.com.sg/wellness-kampung\n",
    "\n",
    "Special thanks to the OneMap team for actively responding to my email queries\n",
    "\n",
    "#### Table of Contents\n",
    "(1) [Importing necessary libraries](#part1)  \n",
    "(2) [Setting up OneMap authentication](#part2)  \n",
    "(3) [Retrieving postal codes and lat-long coordinates](#part3)  \n",
    "(4) [Importing filtered Excel file of 101 selected HDB blocks](#part4)  \n",
    "(5) [Data Preprocessing](#part5)  \n",
    "(6) [Pairwise Travel (Walk) between HDB blocks](#part6)  \n",
    "(7) [Pairwise Travel (Public Transport) between HDB blocks](#part7)  \n",
    "(8) [Travel Time from HDB Block to Woodlands Health Campus (WHC)](#part8)  \n",
    "(9) [Generation of Random Elderly Population Numbers per HDB Block](#part9) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name=\"part1\"></a>\n",
    "### Importing necessary libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "import requests\n",
    "import os\n",
    "import http.client\n",
    "import json\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pprint import pprint\n",
    "from onemapsg import OneMap\n",
    "from itertools import product\n",
    "from itertools import cycle\n",
    "import random\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "C:\\Users\\klty0\\Dropbox\\NUS MSBA\\Semester 1\\BDC5101 - Deterministic Operations Research Models\\Term Project\\Code\n"
     ]
    }
   ],
   "source": [
    "# Set working directory\n",
    "os.chdir(r\"C:\\Users\\klty0\\Dropbox\\NUS MSBA\\Semester 1\\BDC5101 - Deterministic Operations Research Models\\Term Project\\Code\")\n",
    "print(os.getcwd())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name=\"part2\"></a>\n",
    "### Setting up OneMap Authentication\n",
    "\n",
    "The actual authentication credentials have been masked here. Please update the credentials in the code below before running"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Authentication with OneMap client (via HTTPS connection)\n",
    "conn = http.client.HTTPSConnection(\"developers.onemap.sg\")\n",
    "payload = \"------WebKitFormBoundary7MA4YWxkTrZu0gW\\r\\nContent-Disposition: form-data; name=\\\"email\\\"\\r\\n\\r\\nkennethleung@u.nus.edu\\r\\n------WebKitFormBoundary7MA4YWxkTrZu0gW\\r\\nContent-Disposition: form-data; name=\\\"password\\\"\\r\\n\\r\\nE66n67HtEjDu3Ra\\r\\n------WebKitFormBoundary7MA4YWxkTrZu0gW--\"\n",
    "headers = {\n",
    "'content-type': \"multipart/form-data; boundary=----WebKitFormBoundary7MA4YWxkTrZu0gW\",\n",
    "'cache-control': \"no-cache\" }\n",
    "\n",
    "onemap = OneMap(\"youremailhere@email.com\", \"yourpasswordhere\")\n",
    "\n",
    "conn.request(\"POST\", \"/privateapi/auth/post/getToken\", payload, headers)\n",
    "res = conn.getresponse()\n",
    "data = res.read()\n",
    "\n",
    "# Prints access token code\n",
    "print(data.decode(\"utf-8\"))\n",
    "\n",
    "# Setting endpoint as OneMap API search\n",
    "url = \"https://developers.onemap.sg/commonapi/search\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name=\"part3\"></a>\n",
    "### Retrieving postal codes and latitude-longitude (lat-long) coordinates\n",
    "\n",
    "Based on the Singapore URA Master Plan 2014, Singapore is segmented into various planning areas, and with multiple subzones within each planning area. GIS map can be found here: https://keylocation.sg/singapore/planning-areas OR https://www.singstat.gov.sg/-/media/files/publications/population/population2019-map1.pdf\n",
    "\n",
    "As we have chosen the <b>Woodlands</b> planning area to investigate (since the new Woodlands Health Campus will be located there in 2022), we want to extract and explore the entire dataset linked with the keyword 'Woodlands'."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"population_map_sg.jpg\" width=\"800\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "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>SEARCHVAL</th>\n",
       "      <th>BLK_NO</th>\n",
       "      <th>ROAD_NAME</th>\n",
       "      <th>BUILDING</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>POSTAL</th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "      <th>LONGTITUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>WOODLANDS PARK</td>\n",
       "      <td></td>\n",
       "      <td>NIL</td>\n",
       "      <td>WOODLANDS PARK</td>\n",
       "      <td>WOODLANDS PARK</td>\n",
       "      <td>NIL</td>\n",
       "      <td>19679.80071</td>\n",
       "      <td>44111.93008</td>\n",
       "      <td>1.415205915</td>\n",
       "      <td>103.7585543</td>\n",
       "      <td>103.7585543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WOODLANDS MEADOW</td>\n",
       "      <td>782</td>\n",
       "      <td>WOODLANDS CRESCENT</td>\n",
       "      <td>WOODLANDS MEADOW</td>\n",
       "      <td>782 WOODLANDS CRESCENT WOODLANDS MEADOW SINGAP...</td>\n",
       "      <td>730782</td>\n",
       "      <td>24690.53753</td>\n",
       "      <td>47763.051889999995</td>\n",
       "      <td>1.448226355</td>\n",
       "      <td>103.8035797</td>\n",
       "      <td>103.8035797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WOODLANDS PASTURE I</td>\n",
       "      <td>783</td>\n",
       "      <td>WOODLANDS RISE</td>\n",
       "      <td>WOODLANDS PASTURE I</td>\n",
       "      <td>783 WOODLANDS RISE WOODLANDS PASTURE I SINGAPO...</td>\n",
       "      <td>730783</td>\n",
       "      <td>24750.725290000002</td>\n",
       "      <td>47717.51462</td>\n",
       "      <td>1.44781454</td>\n",
       "      <td>103.80412059999999</td>\n",
       "      <td>103.80412059999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WOODLANDS DEW</td>\n",
       "      <td>780</td>\n",
       "      <td>WOODLANDS CRESCENT</td>\n",
       "      <td>WOODLANDS DEW</td>\n",
       "      <td>780 WOODLANDS CRESCENT WOODLANDS DEW SINGAPORE...</td>\n",
       "      <td>730780</td>\n",
       "      <td>24409.813</td>\n",
       "      <td>47775.793410000006</td>\n",
       "      <td>1.44834155</td>\n",
       "      <td>103.8010571</td>\n",
       "      <td>103.8010571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>WOODLANDS GLEN</td>\n",
       "      <td>574</td>\n",
       "      <td>WOODLANDS DRIVE 16</td>\n",
       "      <td>WOODLANDS GLEN</td>\n",
       "      <td>574 WOODLANDS DRIVE 16 WOODLANDS GLEN SINGAPOR...</td>\n",
       "      <td>730574</td>\n",
       "      <td>24120.18302</td>\n",
       "      <td>45810.80484</td>\n",
       "      <td>1.430570871</td>\n",
       "      <td>103.7984548</td>\n",
       "      <td>103.7984548</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             SEARCHVAL BLK_NO           ROAD_NAME             BUILDING  \\\n",
       "0       WOODLANDS PARK                        NIL       WOODLANDS PARK   \n",
       "1     WOODLANDS MEADOW    782  WOODLANDS CRESCENT     WOODLANDS MEADOW   \n",
       "2  WOODLANDS PASTURE I    783      WOODLANDS RISE  WOODLANDS PASTURE I   \n",
       "3        WOODLANDS DEW    780  WOODLANDS CRESCENT        WOODLANDS DEW   \n",
       "4       WOODLANDS GLEN    574  WOODLANDS DRIVE 16       WOODLANDS GLEN   \n",
       "\n",
       "                                             ADDRESS  POSTAL  \\\n",
       "0                                     WOODLANDS PARK     NIL   \n",
       "1  782 WOODLANDS CRESCENT WOODLANDS MEADOW SINGAP...  730782   \n",
       "2  783 WOODLANDS RISE WOODLANDS PASTURE I SINGAPO...  730783   \n",
       "3  780 WOODLANDS CRESCENT WOODLANDS DEW SINGAPORE...  730780   \n",
       "4  574 WOODLANDS DRIVE 16 WOODLANDS GLEN SINGAPOR...  730574   \n",
       "\n",
       "                    X                   Y     LATITUDE           LONGITUDE  \\\n",
       "0         19679.80071         44111.93008  1.415205915         103.7585543   \n",
       "1         24690.53753  47763.051889999995  1.448226355         103.8035797   \n",
       "2  24750.725290000002         47717.51462   1.44781454  103.80412059999999   \n",
       "3           24409.813  47775.793410000006   1.44834155         103.8010571   \n",
       "4         24120.18302         45810.80484  1.430570871         103.7984548   \n",
       "\n",
       "           LONGTITUDE  \n",
       "0         103.7585543  \n",
       "1         103.8035797  \n",
       "2  103.80412059999999  \n",
       "3         103.8010571  \n",
       "4         103.7984548  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating empty dictionary and list to hold GET requests data\n",
    "querystring = {}\n",
    "query_results = []\n",
    "df_list = []\n",
    "\n",
    "x = \"WOODLANDS\"\n",
    "\n",
    "# OneMap API swagger page (https://developers.onemap.sg/swagger) showed there were 232 pages of results with keyword Woodlands.\n",
    "# Therefore, using the upper limit of 233 as the upper limit to form the range for extracting\n",
    "for i in range(1,233):\n",
    "    querystring.update({\"searchVal\":x,\"returnGeom\":\"Y\",\"getAddrDetails\":\"Y\", \"pageNum\":i})\n",
    "    response = requests.request(\"GET\", url, data=payload, headers=headers, params=querystring)\n",
    "    query_results.append(response.json())\n",
    "\n",
    "for j in range(232):\n",
    "    df = pd.DataFrame.from_dict(query_results[j]['results'])\n",
    "    df_list.append(df)\n",
    "\n",
    "df_woodlands = pd.concat(df_list)\n",
    "\n",
    "# Previewing the raw dataset\n",
    "df_woodlands.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>BUILDING</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>POSTAL</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WOODLANDS MEADOW</td>\n",
       "      <td>782 WOODLANDS CRESCENT WOODLANDS MEADOW SINGAP...</td>\n",
       "      <td>730782</td>\n",
       "      <td>1.448226355</td>\n",
       "      <td>103.8035797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WOODLANDS PASTURE I</td>\n",
       "      <td>783 WOODLANDS RISE WOODLANDS PASTURE I SINGAPO...</td>\n",
       "      <td>730783</td>\n",
       "      <td>1.44781454</td>\n",
       "      <td>103.80412059999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WOODLANDS DEW</td>\n",
       "      <td>780 WOODLANDS CRESCENT WOODLANDS DEW SINGAPORE...</td>\n",
       "      <td>730780</td>\n",
       "      <td>1.44834155</td>\n",
       "      <td>103.8010571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>WOODLANDS GLEN</td>\n",
       "      <td>574 WOODLANDS DRIVE 16 WOODLANDS GLEN SINGAPOR...</td>\n",
       "      <td>730574</td>\n",
       "      <td>1.430570871</td>\n",
       "      <td>103.7984548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>WOODLANDS SPRING</td>\n",
       "      <td>799 WOODLANDS DRIVE 60 WOODLANDS SPRING SINGAP...</td>\n",
       "      <td>730799</td>\n",
       "      <td>1.446490887</td>\n",
       "      <td>103.7999175</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              BUILDING                                            ADDRESS  \\\n",
       "1     WOODLANDS MEADOW  782 WOODLANDS CRESCENT WOODLANDS MEADOW SINGAP...   \n",
       "2  WOODLANDS PASTURE I  783 WOODLANDS RISE WOODLANDS PASTURE I SINGAPO...   \n",
       "3        WOODLANDS DEW  780 WOODLANDS CRESCENT WOODLANDS DEW SINGAPORE...   \n",
       "4       WOODLANDS GLEN  574 WOODLANDS DRIVE 16 WOODLANDS GLEN SINGAPOR...   \n",
       "5     WOODLANDS SPRING  799 WOODLANDS DRIVE 60 WOODLANDS SPRING SINGAP...   \n",
       "\n",
       "   POSTAL     LATITUDE           LONGITUDE  \n",
       "1  730782  1.448226355         103.8035797  \n",
       "2  730783   1.44781454  103.80412059999999  \n",
       "3  730780   1.44834155         103.8010571  \n",
       "4  730574  1.430570871         103.7984548  \n",
       "5  730799  1.446490887         103.7999175  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Keeping only the necessary columns from the dataset\n",
    "df_woodlands = df_woodlands[['BUILDING', 'ADDRESS', 'POSTAL', 'LATITUDE', 'LONGITUDE' ]]\n",
    "\n",
    "# Dropping rows where POSTAL code is NIL\n",
    "df_woodlands['POSTAL'] = df_woodlands['POSTAL'].replace('NIL', np.nan)\n",
    "df_woodlands = df_woodlands.dropna(axis=0, subset=['POSTAL'])\n",
    "\n",
    "# Preview updated Woodlands dataset\n",
    "df_woodlands.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We decided to narrow down our focus to the Woodland South subzone of the Woodlands planning area. Image below courtesy of keylocation.sg"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"woodlands_south_gis.jpg\" width=\"330\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name=\"part4\"></a>\n",
    "### Importing filtered Excel file of 101 selected HDB blocks\n",
    "\n",
    "Based on our dataset and geospatial visualization, we decided to further narrow down to 101 blocks in a specific community within the Woodland South subzone. This was easily filtered manually in Excel.\n",
    "\n",
    "The reason why this was not done in Python is because there were many other locations which we want to exclude (e.g. Multi-storey carparks, retail malls etc). Given the relatively small dataset, I determined it was easier to do the exclusion manually on an Excel spreadsheet. The inclusion and exclusion criteria leaves us with 101 blocks which we will use for our LP modelling.\n",
    "\n",
    "Another reason for manual editing of the Excel sheet is the fact that there are some new HDB blocks which are not yet listed in the OneMap database. These HDB blocks are eventually manually added into our list for the purpose of our project.\n",
    "\n",
    "Note: One of our assumptions is that health wellness centers are to be located at HDB blocks for accessibility and practical reasons (easier to set up a health center within an existing building).\n",
    "\n",
    "Having the edited Excel file ready, we now import the xlsx file into our Jupyter notebook."
   ]
  },
  {
   "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>BUILDING</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>POSTAL</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HDB-WOODLANDS</td>\n",
       "      <td>501 WOODLANDS DRIVE 14 HDB-WOODLANDS SINGAPORE...</td>\n",
       "      <td>730501</td>\n",
       "      <td>1.432925</td>\n",
       "      <td>103.792007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>HDB-WOODLANDS</td>\n",
       "      <td>502 WOODLANDS DRIVE 14 HDB-WOODLANDS SINGAPORE...</td>\n",
       "      <td>730502</td>\n",
       "      <td>1.433180</td>\n",
       "      <td>103.791790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>HDB-WOODLANDS</td>\n",
       "      <td>502A WOODLANDS DRIVE 14 HDB-WOODLANDS SINGAPOR...</td>\n",
       "      <td>731502</td>\n",
       "      <td>1.433305</td>\n",
       "      <td>103.791314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>HDB-WOODLANDS</td>\n",
       "      <td>503 WOODLANDS DRIVE 14 HDB-WOODLANDS SINGAPORE...</td>\n",
       "      <td>730503</td>\n",
       "      <td>1.432500</td>\n",
       "      <td>103.791787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HDB-WOODLANDS</td>\n",
       "      <td>504 WOODLANDS DRIVE 14 HDB-WOODLANDS SINGAPORE...</td>\n",
       "      <td>730504</td>\n",
       "      <td>1.432982</td>\n",
       "      <td>103.791063</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        BUILDING                                            ADDRESS  POSTAL  \\\n",
       "0  HDB-WOODLANDS  501 WOODLANDS DRIVE 14 HDB-WOODLANDS SINGAPORE...  730501   \n",
       "1  HDB-WOODLANDS  502 WOODLANDS DRIVE 14 HDB-WOODLANDS SINGAPORE...  730502   \n",
       "2  HDB-WOODLANDS  502A WOODLANDS DRIVE 14 HDB-WOODLANDS SINGAPOR...  731502   \n",
       "3  HDB-WOODLANDS  503 WOODLANDS DRIVE 14 HDB-WOODLANDS SINGAPORE...  730503   \n",
       "4  HDB-WOODLANDS  504 WOODLANDS DRIVE 14 HDB-WOODLANDS SINGAPORE...  730504   \n",
       "\n",
       "   LATITUDE   LONGITUDE  \n",
       "0  1.432925  103.792007  \n",
       "1  1.433180  103.791790  \n",
       "2  1.433305  103.791314  \n",
       "3  1.432500  103.791787  \n",
       "4  1.432982  103.791063  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Importing post-edited dataset (Focus on Woodlands South HDBs) \n",
    "# Excel file is made available in GitHub repo. Please feel free to open up to have a look\n",
    "df_woodlands_south_hdb = pd.read_excel('df_woodlands_south_hdb.xlsx')\n",
    "df_woodlands_south_hdb.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(101, 5)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_woodlands_south_hdb.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Confirming that there are no duplicate postal codes\n",
    "df_woodlands_south_hdb.duplicated(subset=None, keep='first').any()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name=\"part5\"></a>\n",
    "### Data Preprocessing\n",
    "\n",
    "We now need to create a dataframe which allows for block-to-block travel time and distance calculation\n",
    "\n",
    "Source block refers to the origin HDB block, while Sink block refers to the destination HDB block"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "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>POSTAL</th>\n",
       "      <th>latlong</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>730502</td>\n",
       "      <td>1.43318008766272,103.79179043736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>731502</td>\n",
       "      <td>1.43330461383637,103.791313633199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>730503</td>\n",
       "      <td>1.43249995976889,103.791786711705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>730504</td>\n",
       "      <td>1.43298242263143,103.791063262859</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   POSTAL                            latlong\n",
       "0  730501  1.43292524188657,103.792006998018\n",
       "1  730502   1.43318008766272,103.79179043736\n",
       "2  731502  1.43330461383637,103.791313633199\n",
       "3  730503  1.43249995976889,103.791786711705\n",
       "4  730504  1.43298242263143,103.791063262859"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Silencing chained assignment warning\n",
    "pd.options.mode.chained_assignment = None\n",
    "\n",
    "# Cleaning up the dataframe (Keep only necessary columns)\n",
    "df_clean = df_woodlands_south_hdb[['POSTAL','LATITUDE', 'LONGITUDE']]\n",
    "\n",
    "# Create new lat-long concatenated string column (for input into routing). This is how OneMap API recognizes it for routing calculation\n",
    "df_clean['latlong'] = df_clean['LATITUDE'].astype(str) + ',' + df_clean['LONGITUDE'].astype(str)\n",
    "\n",
    "df_clean = df_clean[['POSTAL','latlong']]\n",
    "df_clean = pd.DataFrame(df_clean)\n",
    "\n",
    "df_clean.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Source_Postal</th>\n",
       "      <th>Source_Latlong</th>\n",
       "      <th>Sink_Postal</th>\n",
       "      <th>Sink_Latlong</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "      <td>730502</td>\n",
       "      <td>1.43318008766272,103.79179043736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "      <td>731502</td>\n",
       "      <td>1.43330461383637,103.791313633199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "      <td>730503</td>\n",
       "      <td>1.43249995976889,103.791786711705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "      <td>730504</td>\n",
       "      <td>1.43298242263143,103.791063262859</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Source_Postal                     Source_Latlong Sink_Postal  \\\n",
       "0        730501  1.43292524188657,103.792006998018      730501   \n",
       "1        730501  1.43292524188657,103.792006998018      730502   \n",
       "2        730501  1.43292524188657,103.792006998018      731502   \n",
       "3        730501  1.43292524188657,103.792006998018      730503   \n",
       "4        730501  1.43292524188657,103.792006998018      730504   \n",
       "\n",
       "                        Sink_Latlong  \n",
       "0  1.43292524188657,103.792006998018  \n",
       "1   1.43318008766272,103.79179043736  \n",
       "2  1.43330461383637,103.791313633199  \n",
       "3  1.43249995976889,103.791786711705  \n",
       "4  1.43298242263143,103.791063262859  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating combination table (full outer join with itself)\n",
    "df_combi = df_clean\n",
    "\n",
    "df_combi['postal_latlong_1'] = df_combi.POSTAL.astype(str) + ' ' + df_combi.latlong.astype(str)\n",
    "\n",
    "# Drop original postal code and latlong\n",
    "df_combi.drop(['POSTAL','latlong'],axis = 1, inplace = True)\n",
    "\n",
    "# Creating combination table\n",
    "df_combi = pd.DataFrame(list(product(df_combi['postal_latlong_1'], df_combi['postal_latlong_1'])))\n",
    "df_combi.columns = ['postal_latlong_1','postal_latlong_2']\n",
    "\n",
    "df_combi[['Source_Postal','Source_Latlong']] = df_combi.postal_latlong_1.str.split(\" \",expand=True) \n",
    "df_combi[['Sink_Postal','Sink_Latlong']] = df_combi.postal_latlong_2.str.split(\" \",expand=True) \n",
    "\n",
    "# Dropping previous unnecessary columns\n",
    "df_combi.drop(['postal_latlong_1','postal_latlong_2'],axis = 1, inplace = True)\n",
    "\n",
    "df_combi.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the preview above, we can see that we now have a dataframe that is primed for API request calls. We have a lat-long coordinate (in concatenated string form) for each source postal and sink postal. Every source block (of the list of 101 HDB blocks) is linked to every block in the same list (including itself). This means that we should have a total of (101 * 101 = <b>10201</b>) observations in the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10201, 4)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_combi.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name=\"part6\"></a>\n",
    "### Pairwise Travel (Walk) between HDB blocks\n",
    "\n",
    "We now call the OneMap API to extract the walking time and distance between every source and sink HDB block (i.e pairwise travel). This service calculates the distance and returns the drawn path between the specified start and end values depending on the routeType.\n",
    "\n",
    "Due to timeout error, the API does not allow me to query for the entire dataset at one go. To overcome this, I had to split it into smaller dataframes with a API call pause of 200 seconds between every dataframe. This pause value of 200 seconds was derived based on trial and error. Too short a pause will result in the similar timeout issue, while too long a pause will make the entire extraction process too tedious and slow.\n",
    "\n",
    "The following steps will take some time, so go for a coffee break after initiating the code :)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "35\n"
     ]
    }
   ],
   "source": [
    "# Split main dataframe into smaller dataframes of 300 rows each\n",
    "size = 300\n",
    "list_of_dfs = [df_combi.loc[i:i+size-1,:] for i in range(0, len(df_combi),size)]\n",
    "\n",
    "# Number of smaller dataframes to process\n",
    "print(len(list_of_dfs))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "full_list = []\n",
    "n = 0\n",
    "\n",
    "for j in list_of_dfs:\n",
    "    for i in range(len(j)):\n",
    "        route = onemap.route(j.iloc[i]['Source_Latlong'], j.iloc[i]['Sink_Latlong'], 'walk')\n",
    "        time_taken = route.to_dict()['route_summary']['total_time']\n",
    "        distance = route.to_dict()['route_summary']['total_distance']\n",
    "        source = j.iloc[i]['Source_Postal']\n",
    "        sink = j.iloc[i]['Sink_Postal']\n",
    "        sequence = []\n",
    "        sequence.extend([source, sink, time_taken, distance])\n",
    "        full_list.append(sequence)\n",
    "        print('Processing row number ',i+1,' of df ', n+1)\n",
    "    n += 1\n",
    "    print('Dataframe ',n, 'complete. Sleep begins now')\n",
    "    time.sleep(200)\n",
    "    print('Restart processing')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After the data extraction is complete, we merge the list of dataframes into a single dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>source_postal</th>\n",
       "      <th>sink_postal</th>\n",
       "      <th>total_time</th>\n",
       "      <th>total_distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730501</td>\n",
       "      <td>730501</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>730501</td>\n",
       "      <td>730502</td>\n",
       "      <td>27</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>730501</td>\n",
       "      <td>731502</td>\n",
       "      <td>71</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>730501</td>\n",
       "      <td>730503</td>\n",
       "      <td>56</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>730501</td>\n",
       "      <td>730504</td>\n",
       "      <td>98</td>\n",
       "      <td>135</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  source_postal sink_postal  total_time  total_distance\n",
       "0        730501      730501           0               0\n",
       "1        730501      730502          27              37\n",
       "2        730501      731502          71              99\n",
       "3        730501      730503          56              78\n",
       "4        730501      730504          98             135"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_complete = pd.DataFrame(full_list, columns = ['source_postal', 'sink_postal', 'total_time', 'total_distance'])\n",
    "df_complete.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To determine distance between every pair of source and sink blocks, we will be taking the average of the values (of both time and distance) from source to sink, and sink to source. This is because we are assuming that an individual will take the same route to and fro the health wellness center."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "734574    101\n",
       "734573    100\n",
       "733574     99\n",
       "733573     98\n",
       "733571     97\n",
       "         ... \n",
       "730505      5\n",
       "730504      4\n",
       "730503      3\n",
       "730502      2\n",
       "730501      1\n",
       "Name: postal_2, Length: 101, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating separate dataframe for manipulation of time and distance\n",
    "df_time_and_dist = df_complete\n",
    "\n",
    "# Creating a column that gives a unique identifier. Since we want the average of the to and fro travel routes,\n",
    "# we need to consolidate regardless of source or sink location. \n",
    "# To start, we create a id column (serving as an index) that is a list containing both locations\n",
    "df_time_and_dist['id'] = df_time_and_dist[['source_postal','sink_postal']].values.tolist()\n",
    "\n",
    "# Sorting the list such that the smaller number is always in front. This will help\n",
    "# create a unique identifier (directionless) that allows merging, and obtaining mean of routes\n",
    "df_time_and_dist['id'] = df_time_and_dist.id.sort_values().apply(lambda x: sorted(x))\n",
    "df_time_and_dist = df_time_and_dist.reset_index(drop=True)\n",
    "\n",
    "# Dropping unnecessary columns\n",
    "df_time_and_dist.drop(['source_postal','sink_postal'], axis = 1, inplace = True)\n",
    "\n",
    "# Converting id from list into string first (So that it can be used as key)\n",
    "df_time_and_dist['id'] = df_time_and_dist['id'].apply(' '.join)\n",
    "\n",
    "# Merging rows based on id, and getting the mean time (between 2 locations)\n",
    "df_time_and_dist = df_time_and_dist.groupby('id', as_index=False).mean()\n",
    "\n",
    "# Renaming time column (to reflect average time)\n",
    "df_time_and_dist.rename(columns={'total_time' : 'average_time_seconds',\n",
    "                                 'total_distance' : 'average_dist_metres'}, inplace = True)\n",
    "\n",
    "# Splitting list column into 2 columns\n",
    "postal_id = df_time_and_dist[\"id\"].str.split(\" \", n = 1, expand = True) \n",
    "df_time_and_dist[\"postal\"]= postal_id[0] \n",
    "df_time_and_dist[\"postal_2\"]= postal_id[1] \n",
    "\n",
    "# Dropping id column and rearranging columns\n",
    "df_time_and_dist.drop(['id'], axis = 1, inplace = True)\n",
    "cols = ['postal', 'postal_2', 'average_time_seconds', 'average_dist_metres']\n",
    "df_time_and_dist = df_time_and_dist.loc[:, cols]\n",
    "\n",
    "# Exporting datasets\n",
    "#df_time_and_dist.to_excel(\"Average Distance and Walking Time between 101 HDB.xlsx\", index=False, encoding='utf-8-sig')\n",
    "\n",
    "# Sanity check to see that all pairwise calculations are done\n",
    "df_time_and_dist['postal'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>postal</th>\n",
       "      <th>postal_2</th>\n",
       "      <th>average_time_seconds</th>\n",
       "      <th>average_dist_metres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730501</td>\n",
       "      <td>730501</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>730501</td>\n",
       "      <td>730502</td>\n",
       "      <td>27.0</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>730501</td>\n",
       "      <td>730503</td>\n",
       "      <td>56.0</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>730501</td>\n",
       "      <td>730504</td>\n",
       "      <td>98.0</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>730501</td>\n",
       "      <td>730505</td>\n",
       "      <td>117.0</td>\n",
       "      <td>163.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   postal postal_2  average_time_seconds  average_dist_metres\n",
       "0  730501   730501                   0.0                  0.0\n",
       "1  730501   730502                  27.0                 37.0\n",
       "2  730501   730503                  56.0                 78.0\n",
       "3  730501   730504                  98.0                135.0\n",
       "4  730501   730505                 117.0                163.0"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Displaying the final output dataframe\n",
    "df_time_and_dist.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name=\"part7\"></a>\n",
    "### Pairwise Travel (Public Transport) between HDB blocks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The concept in this section is the same as that of the Pairwise Travel (Walk) section above, just that some of the parameters in the API call are different for the Public Transport route type. This is because we have additional parameters to include for the Public Transport route i.e. date/time of travel, and mode (i.e. TRANSIT, BUS or RAIL).\n",
    "\n",
    "We first need to remove the rows where the source block exactly matches the sink block. This is because while the Walking Distance call gives a 0 value for every case where the source block is the same as the sink block, the Public Transport Distance call returns an error instead. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {
    "scrolled": true
   },
   "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>Source_Postal</th>\n",
       "      <th>Source_Latlong</th>\n",
       "      <th>Sink_Postal</th>\n",
       "      <th>Sink_Latlong</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "      <td>730502</td>\n",
       "      <td>1.43318008766272,103.79179043736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "      <td>731502</td>\n",
       "      <td>1.43330461383637,103.791313633199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "      <td>730503</td>\n",
       "      <td>1.43249995976889,103.791786711705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "      <td>730504</td>\n",
       "      <td>1.43298242263143,103.791063262859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "      <td>730505</td>\n",
       "      <td>1.4321945263483,103.791155512188</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Source_Postal                     Source_Latlong Sink_Postal  \\\n",
       "1        730501  1.43292524188657,103.792006998018      730502   \n",
       "2        730501  1.43292524188657,103.792006998018      731502   \n",
       "3        730501  1.43292524188657,103.792006998018      730503   \n",
       "4        730501  1.43292524188657,103.792006998018      730504   \n",
       "5        730501  1.43292524188657,103.792006998018      730505   \n",
       "\n",
       "                        Sink_Latlong  \n",
       "1   1.43318008766272,103.79179043736  \n",
       "2  1.43330461383637,103.791313633199  \n",
       "3  1.43249995976889,103.791786711705  \n",
       "4  1.43298242263143,103.791063262859  \n",
       "5   1.4321945263483,103.791155512188  "
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Removing rows where the source and sink postal codes are the same\n",
    "df_combi_no_self = df_combi\n",
    "df_combi_no_self = df_combi_no_self[df_combi_no_self['Source_Postal'] != df_combi_no_self['Sink_Postal']]\n",
    "\n",
    "df_combi_no_self.head()\n",
    "\n",
    "# The first row should already show that the source and sink are of different postal codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10100, 4)"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We expect this dataframe to have 101 less rows compared to the original (i.e. 10201 - 101 = 10100) since we are removing\n",
    "# rows where the source and sink postals match\n",
    "df_combi_no_self.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "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>Source_Postal</th>\n",
       "      <th>Source_Latlong</th>\n",
       "      <th>Sink_Postal</th>\n",
       "      <th>Sink_Latlong</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>730502</td>\n",
       "      <td>1.43318008766272,103.79179043736</td>\n",
       "      <td>730502</td>\n",
       "      <td>1.43318008766272,103.79179043736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>731502</td>\n",
       "      <td>1.43330461383637,103.791313633199</td>\n",
       "      <td>731502</td>\n",
       "      <td>1.43330461383637,103.791313633199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306</th>\n",
       "      <td>730503</td>\n",
       "      <td>1.43249995976889,103.791786711705</td>\n",
       "      <td>730503</td>\n",
       "      <td>1.43249995976889,103.791786711705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>408</th>\n",
       "      <td>730504</td>\n",
       "      <td>1.43298242263143,103.791063262859</td>\n",
       "      <td>730504</td>\n",
       "      <td>1.43298242263143,103.791063262859</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Source_Postal                     Source_Latlong Sink_Postal  \\\n",
       "0          730501  1.43292524188657,103.792006998018      730501   \n",
       "102        730502   1.43318008766272,103.79179043736      730502   \n",
       "204        731502  1.43330461383637,103.791313633199      731502   \n",
       "306        730503  1.43249995976889,103.791786711705      730503   \n",
       "408        730504  1.43298242263143,103.791063262859      730504   \n",
       "\n",
       "                          Sink_Latlong  \n",
       "0    1.43292524188657,103.792006998018  \n",
       "102   1.43318008766272,103.79179043736  \n",
       "204  1.43330461383637,103.791313633199  \n",
       "306  1.43249995976889,103.791786711705  \n",
       "408  1.43298242263143,103.791063262859  "
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Keeping the rows where source and sink matches (so that they can be appended later)\n",
    "df_combi_self = df_combi[df_combi['Source_Postal'] == df_combi['Sink_Postal']]\n",
    "df_combi_self.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(101, 4)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_combi_self.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we run the API calls on the entire dataset. If timeout occurs, do refer to the previous section on splitting the dataframe into smaller dataframes with inter-dataframe pauses to prevent timeout. Over here, we chose the mode of public transport to be <b>BUS</b>."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "full_list =[]\n",
    "\n",
    "for i in range(len(df_combi_no_self)):\n",
    "    route = onemap.route(df_combi_no_self.iloc[i]['Source_Latlong'], df_combi_no_self.iloc[i]['Sink_Latlong'], route_type = 'pt', public_transport_options = {\"date\": \"2019-11-20\", \n",
    "                                                                            \"time\":\"10:00:00\", \n",
    "                                                                            \"mode\": \"BUS\"})\n",
    "                                                                            \n",
    "    mode = route.to_dict()['request_parameters']['mode']\n",
    "    time_taken = route.to_dict()['plan']['itineraries'][0]['duration']      \n",
    "    walk_distance = round(route.to_dict()['plan']['itineraries'][0]['walkDistance'])\n",
    "    source = df_combi_no_self.iloc[i]['Source_Postal']\n",
    "    sink = df_combi_no_self.iloc[i]['Sink_Postal']\n",
    "    sequence = []\n",
    "    sequence.extend([source, sink, mode, time_taken, walk_distance])\n",
    "    full_list.append(sequence)\n",
    "    print('Processing row number ',i+1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Timeout error is common when you run the entire query at one go. The error message should look like   \n",
    "<b><font color = 'red'>BadRequest</font>: Your request has timed out.Please try again!</b>\n",
    "\n",
    "An alternate method I came up with to overcome the timeout error, is to perform manual iterative execution of the API request call based on the last point of cutoff due to timeout. This means that the API call will continue from where it last left off. Despite this being manual, it actually has the higher chance of successfully pulling all the required data. \n",
    "\n",
    "Since this is the alternate (less preferred) method, the best way is still to adopt the method used in the <b>Pairwise Travel (Walk) between HDB blocks</b> section where I split the dataframes into small ones, and introducing a 200 second pause before running the next dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10100\n"
     ]
    }
   ],
   "source": [
    "# RUN THIS SECTION ONLY THERE IS TIMEOUT ERROR ON THE PREVIOUS CHUNK!\n",
    "\n",
    "x = len(full_list)\n",
    "\n",
    "# This prints the last row number in which the API was called successfully, and will be the point where the API call will restart.\n",
    "print(x)\n",
    "    \n",
    "df_combi_cont = df_combi_no_self.iloc[x:,:]\n",
    "\n",
    "for i in range(len(df_combi_cont)):\n",
    "    route = onemap.route(df_combi_cont.iloc[i]['Source_Latlong'], df_combi_cont.iloc[i]['Sink_Latlong'], route_type = 'pt', public_transport_options = {\"date\": \"2019-11-20\", \n",
    "                                                                            \"time\":\"10:00:00\", \n",
    "                                                                            \"mode\": \"BUS\"})\n",
    "                                                                            \n",
    "    mode = route.to_dict()['request_parameters']['mode']\n",
    "    time_taken = route.to_dict()['plan']['itineraries'][0]['duration']      \n",
    "    walk_distance = round(route.to_dict()['plan']['itineraries'][0]['walkDistance'])\n",
    "    source = df_combi_cont.iloc[i]['Source_Postal']\n",
    "    sink = df_combi_cont.iloc[i]['Sink_Postal']\n",
    "    sequence = []\n",
    "    sequence.extend([source, sink, mode, time_taken, walk_distance])\n",
    "    full_list.append(sequence)\n",
    "    print('Processing row number ',i+1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Processing the data extracted for public transport route type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "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>source_postal</th>\n",
       "      <th>sink_postal</th>\n",
       "      <th>mode</th>\n",
       "      <th>total_time</th>\n",
       "      <th>walk_distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730501</td>\n",
       "      <td>730502</td>\n",
       "      <td>WALK</td>\n",
       "      <td>34</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>730501</td>\n",
       "      <td>731502</td>\n",
       "      <td>WALK</td>\n",
       "      <td>86</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>730501</td>\n",
       "      <td>730503</td>\n",
       "      <td>WALK</td>\n",
       "      <td>82</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>730501</td>\n",
       "      <td>730504</td>\n",
       "      <td>WALK</td>\n",
       "      <td>131</td>\n",
       "      <td>135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>730501</td>\n",
       "      <td>730505</td>\n",
       "      <td>WALK</td>\n",
       "      <td>159</td>\n",
       "      <td>162</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  source_postal sink_postal  mode  total_time  walk_distance\n",
       "0        730501      730502  WALK          34             37\n",
       "1        730501      731502  WALK          86             98\n",
       "2        730501      730503  WALK          82             78\n",
       "3        730501      730504  WALK         131            135\n",
       "4        730501      730505  WALK         159            162"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merging the list of dataframes into a single dataframe\n",
    "df_pt = pd.DataFrame(full_list, columns = ['source_postal', 'sink_postal', 'mode', \n",
    "                                           'total_time', 'walk_distance'])\n",
    "df_pt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10100, 5)"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pt.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "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>postal</th>\n",
       "      <th>postal_2</th>\n",
       "      <th>mode</th>\n",
       "      <th>average_time_seconds</th>\n",
       "      <th>average_walking_dist_metres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730501</td>\n",
       "      <td>730502</td>\n",
       "      <td>WALK</td>\n",
       "      <td>34.0</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>730501</td>\n",
       "      <td>730503</td>\n",
       "      <td>WALK</td>\n",
       "      <td>83.0</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>730501</td>\n",
       "      <td>730504</td>\n",
       "      <td>WALK</td>\n",
       "      <td>131.0</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>730501</td>\n",
       "      <td>730505</td>\n",
       "      <td>WALK</td>\n",
       "      <td>159.0</td>\n",
       "      <td>162.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>730501</td>\n",
       "      <td>730506</td>\n",
       "      <td>WALK</td>\n",
       "      <td>172.0</td>\n",
       "      <td>175.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   postal postal_2  mode  average_time_seconds  average_walking_dist_metres\n",
       "0  730501   730502  WALK                  34.0                         37.0\n",
       "1  730501   730503  WALK                  83.0                         78.0\n",
       "2  730501   730504  WALK                 131.0                        135.0\n",
       "3  730501   730505  WALK                 159.0                        162.0\n",
       "4  730501   730506  WALK                 172.0                        175.0"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Silencing chained assignment warning\n",
    "pd.options.mode.chained_assignment = None\n",
    "\n",
    "# Creating separate dataframe for manipulation of time and distance\n",
    "df_pt_time_and_dist = df_pt.copy()\n",
    "\n",
    "df_pt_time_and_dist['source_postal'] = df_pt_time_and_dist['source_postal'].astype(str)\n",
    "df_pt_time_and_dist['sink_postal'] = df_pt_time_and_dist['sink_postal'].astype(str)\n",
    "\n",
    "# Creating a column that gives a unique identifier\n",
    "# Since we want the average of the to and fro travel routes, we need to consolidate regardless of\n",
    "# source or sink location. To start, we create a id column that is a list containing both locations\n",
    "df_pt_time_and_dist['id'] = df_pt_time_and_dist[['source_postal','sink_postal']].values.tolist()\n",
    "\n",
    "# Sorting the list such that the smaller number is always in front. This will help\n",
    "# create a unique identifier (directionless) that allows merging and getting mean of routes\n",
    "df_pt_time_and_dist['id'] = df_pt_time_and_dist.id.sort_values().apply(lambda x: sorted(x))\n",
    "df_pt_time_and_dist = df_pt_time_and_dist.reset_index(drop=True)\n",
    "\n",
    "# Dropping unnecessary columns\n",
    "df_pt_time_and_dist.drop(['source_postal','sink_postal'], axis = 1, inplace = True)\n",
    "\n",
    "# Converting id from list into string first (So that it can be used as key)\n",
    "df_pt_time_and_dist['id'] = df_pt_time_and_dist['id'].apply(' '.join)\n",
    "\n",
    "# Separating mode\n",
    "df_pt_mode = df_pt_time_and_dist[['id','mode']]\n",
    "df_pt_mode.drop_duplicates(inplace=True)\n",
    "\n",
    "# Merging rows based on id, and getting the mean time (between 2 locations)\n",
    "df_pt_time_and_dist = df_pt_time_and_dist.groupby('id', as_index=False).mean()\n",
    "\n",
    "# Renaming time column (to reflect average time)\n",
    "df_pt_time_and_dist.rename(columns={'total_time' : 'average_time_seconds',\n",
    "                                 'walk_distance' : 'average_walking_dist_metres'}, inplace = True)\n",
    "\n",
    "# Remerging mode into dataframe\n",
    "df_pt_time_and_dist = pd.merge(df_pt_time_and_dist, df_pt_mode, on='id')\n",
    "\n",
    "# Splitting list column into 2 columns\n",
    "postal_id = df_pt_time_and_dist[\"id\"].str.split(\" \", n = 1, expand = True) \n",
    "df_pt_time_and_dist[\"postal\"]= postal_id[0] \n",
    "df_pt_time_and_dist[\"postal_2\"]= postal_id[1] \n",
    "\n",
    "# Dropping id column and rearranging columns\n",
    "df_pt_time_and_dist.drop(['id'], axis = 1, inplace = True)\n",
    "cols = ['postal', 'postal_2', 'mode','average_time_seconds', 'average_walking_dist_metres']\n",
    "df_pt_time_and_dist = df_pt_time_and_dist.loc[:, cols]\n",
    "\n",
    "df_pt_time_and_dist.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remember to need to add back the 101 self pairwise block combinations (e.g. 730501 - 730501). These rows were previously removed to prevent the API call from returning an error i.e. Append df_combi_self to df_pt_time_and_dist\n",
    "\n",
    "First we need to edit the df_combi_self dataframe before appending (to match the columns of df_pt_time_and_dist)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "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>Source_Postal</th>\n",
       "      <th>Source_Latlong</th>\n",
       "      <th>Sink_Postal</th>\n",
       "      <th>Sink_Latlong</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>730502</td>\n",
       "      <td>1.43318008766272,103.79179043736</td>\n",
       "      <td>730502</td>\n",
       "      <td>1.43318008766272,103.79179043736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>731502</td>\n",
       "      <td>1.43330461383637,103.791313633199</td>\n",
       "      <td>731502</td>\n",
       "      <td>1.43330461383637,103.791313633199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306</th>\n",
       "      <td>730503</td>\n",
       "      <td>1.43249995976889,103.791786711705</td>\n",
       "      <td>730503</td>\n",
       "      <td>1.43249995976889,103.791786711705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>408</th>\n",
       "      <td>730504</td>\n",
       "      <td>1.43298242263143,103.791063262859</td>\n",
       "      <td>730504</td>\n",
       "      <td>1.43298242263143,103.791063262859</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Source_Postal                     Source_Latlong Sink_Postal  \\\n",
       "0          730501  1.43292524188657,103.792006998018      730501   \n",
       "102        730502   1.43318008766272,103.79179043736      730502   \n",
       "204        731502  1.43330461383637,103.791313633199      731502   \n",
       "306        730503  1.43249995976889,103.791786711705      730503   \n",
       "408        730504  1.43298242263143,103.791063262859      730504   \n",
       "\n",
       "                          Sink_Latlong  \n",
       "0    1.43292524188657,103.792006998018  \n",
       "102   1.43318008766272,103.79179043736  \n",
       "204  1.43330461383637,103.791313633199  \n",
       "306  1.43249995976889,103.791786711705  \n",
       "408  1.43298242263143,103.791063262859  "
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's first have a recap as to what the dataframe looks like\n",
    "df_combi_self.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "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>Source_Postal</th>\n",
       "      <th>Sink_Postal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730501</td>\n",
       "      <td>730501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>730502</td>\n",
       "      <td>730502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>731502</td>\n",
       "      <td>731502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>730503</td>\n",
       "      <td>730503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>730504</td>\n",
       "      <td>730504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>730505</td>\n",
       "      <td>730505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>730506</td>\n",
       "      <td>730506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>730507</td>\n",
       "      <td>730507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>730508</td>\n",
       "      <td>730508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>730509</td>\n",
       "      <td>730509</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Source_Postal Sink_Postal\n",
       "0        730501      730501\n",
       "1        730502      730502\n",
       "2        731502      731502\n",
       "3        730503      730503\n",
       "4        730504      730504\n",
       "5        730505      730505\n",
       "6        730506      730506\n",
       "7        730507      730507\n",
       "8        730508      730508\n",
       "9        730509      730509"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We first reset index\n",
    "df_combi_self = df_combi_self.reset_index(drop=True)\n",
    "\n",
    "# Next we keep only the source_postal and Sink_Postal\n",
    "df_combi_self = df_combi_self[['Source_Postal', 'Sink_Postal']]\n",
    "\n",
    "df_combi_self.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {
    "scrolled": true
   },
   "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>postal</th>\n",
       "      <th>postal_2</th>\n",
       "      <th>mode</th>\n",
       "      <th>average_time_seconds</th>\n",
       "      <th>average_walking_dist_metres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730501</td>\n",
       "      <td>730501</td>\n",
       "      <td>WALK</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>730502</td>\n",
       "      <td>730502</td>\n",
       "      <td>WALK</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>731502</td>\n",
       "      <td>731502</td>\n",
       "      <td>WALK</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>730503</td>\n",
       "      <td>730503</td>\n",
       "      <td>WALK</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>730504</td>\n",
       "      <td>730504</td>\n",
       "      <td>WALK</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>730505</td>\n",
       "      <td>730505</td>\n",
       "      <td>WALK</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>730506</td>\n",
       "      <td>730506</td>\n",
       "      <td>WALK</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>730507</td>\n",
       "      <td>730507</td>\n",
       "      <td>WALK</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>730508</td>\n",
       "      <td>730508</td>\n",
       "      <td>WALK</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>730509</td>\n",
       "      <td>730509</td>\n",
       "      <td>WALK</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   postal postal_2  mode  average_time_seconds  average_walking_dist_metres\n",
       "0  730501   730501  WALK                     0                            0\n",
       "1  730502   730502  WALK                     0                            0\n",
       "2  731502   731502  WALK                     0                            0\n",
       "3  730503   730503  WALK                     0                            0\n",
       "4  730504   730504  WALK                     0                            0\n",
       "5  730505   730505  WALK                     0                            0\n",
       "6  730506   730506  WALK                     0                            0\n",
       "7  730507   730507  WALK                     0                            0\n",
       "8  730508   730508  WALK                     0                            0\n",
       "9  730509   730509  WALK                     0                            0"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Adding the additional 3 columns of mode, average_time_seconds, average_walking_dist_metres\n",
    "df_combi_self['mode'] = 'WALK'\n",
    "df_combi_self['average_time_seconds'] = 0\n",
    "df_combi_self['average_walking_dist_metres'] = 0\n",
    "\n",
    "# Renaming columns for matching\n",
    "df_combi_self.columns=df_pt_time_and_dist.columns.values\n",
    "\n",
    "df_combi_self.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {
    "scrolled": false
   },
   "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>postal</th>\n",
       "      <th>postal_2</th>\n",
       "      <th>mode</th>\n",
       "      <th>average_time_seconds</th>\n",
       "      <th>average_walking_dist_metres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730501</td>\n",
       "      <td>730502</td>\n",
       "      <td>WALK</td>\n",
       "      <td>34.0</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>730501</td>\n",
       "      <td>730503</td>\n",
       "      <td>WALK</td>\n",
       "      <td>83.0</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>730501</td>\n",
       "      <td>730504</td>\n",
       "      <td>WALK</td>\n",
       "      <td>131.0</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>730501</td>\n",
       "      <td>730505</td>\n",
       "      <td>WALK</td>\n",
       "      <td>159.0</td>\n",
       "      <td>162.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>730501</td>\n",
       "      <td>730506</td>\n",
       "      <td>WALK</td>\n",
       "      <td>172.0</td>\n",
       "      <td>175.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   postal postal_2  mode  average_time_seconds  average_walking_dist_metres\n",
       "0  730501   730502  WALK                  34.0                         37.0\n",
       "1  730501   730503  WALK                  83.0                         78.0\n",
       "2  730501   730504  WALK                 131.0                        135.0\n",
       "3  730501   730505  WALK                 159.0                        162.0\n",
       "4  730501   730506  WALK                 172.0                        175.0"
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Appending the 2 dataframes together to form the final output dataframe\n",
    "df_pt_final = pd.concat([df_pt_time_and_dist, df_combi_self], ignore_index=True, sort =False)\n",
    "\n",
    "df_pt_final.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5151, 5)"
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pt_final.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name=\"part8\"></a>\n",
    "### Travel Time from HDB Block to Woodlands Health Campus (WHC)\n",
    "\n",
    "One of our proposed constraints for our LP model is that the location of the health wellness center needs to be within 5 minutes of the nearest healthcare facility for the purpose of emergency evacuation. WHC is the closest healthcare facility which we have chosen, so we will now need to acquire data relating to the travel time (by driving) from every HDB block (in the list of 101 blocks) to the WHC location. As WHC does not yet have a postal code (site still under construction), we used Google Maps to identify the latitude-longitude coordinates of the future site."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"WHC_googlemap.jpg\" width=\"400\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>POSTAL</th>\n",
       "      <th>latlong</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>730502</td>\n",
       "      <td>1.43318008766272,103.79179043736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>731502</td>\n",
       "      <td>1.43330461383637,103.791313633199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>730503</td>\n",
       "      <td>1.43249995976889,103.791786711705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>730504</td>\n",
       "      <td>1.43298242263143,103.791063262859</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   POSTAL                            latlong\n",
       "0  730501  1.43292524188657,103.792006998018\n",
       "1  730502   1.43318008766272,103.79179043736\n",
       "2  731502  1.43330461383637,103.791313633199\n",
       "3  730503  1.43249995976889,103.791786711705\n",
       "4  730504  1.43298242263143,103.791063262859"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Initializing a new dataframe, copied over from previous dataframe\n",
    "df_whc_hdb = df_clean\n",
    "df_whc_hdb.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "scrolled": true
   },
   "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>POSTAL</th>\n",
       "      <th>postal_latlong</th>\n",
       "      <th>WHC_latlong</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657,103.792006998018</td>\n",
       "      <td>1.425088,103.794736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>730502</td>\n",
       "      <td>1.43318008766272,103.79179043736</td>\n",
       "      <td>1.425088,103.794736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>731502</td>\n",
       "      <td>1.43330461383637,103.791313633199</td>\n",
       "      <td>1.425088,103.794736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>730503</td>\n",
       "      <td>1.43249995976889,103.791786711705</td>\n",
       "      <td>1.425088,103.794736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>730504</td>\n",
       "      <td>1.43298242263143,103.791063262859</td>\n",
       "      <td>1.425088,103.794736</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   POSTAL                     postal_latlong          WHC_latlong\n",
       "0  730501  1.43292524188657,103.792006998018  1.425088,103.794736\n",
       "1  730502   1.43318008766272,103.79179043736  1.425088,103.794736\n",
       "2  731502  1.43330461383637,103.791313633199  1.425088,103.794736\n",
       "3  730503  1.43249995976889,103.791786711705  1.425088,103.794736\n",
       "4  730504  1.43298242263143,103.791063262859  1.425088,103.794736"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Specifying WHC coordinates\n",
    "df_whc_hdb['WHC_latlong'] = '1.425088,103.794736'\n",
    "df_whc_hdb.rename(columns={'latlong' : 'postal_latlong'}, inplace = True)\n",
    "\n",
    "df_whc_hdb.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>HDB_postal</th>\n",
       "      <th>WHC_latlong</th>\n",
       "      <th>total_time</th>\n",
       "      <th>total_distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730501</td>\n",
       "      <td>1.425088,103.794736</td>\n",
       "      <td>216</td>\n",
       "      <td>1207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>730502</td>\n",
       "      <td>1.425088,103.794736</td>\n",
       "      <td>228</td>\n",
       "      <td>1267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>731502</td>\n",
       "      <td>1.425088,103.794736</td>\n",
       "      <td>245</td>\n",
       "      <td>1474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>730503</td>\n",
       "      <td>1.425088,103.794736</td>\n",
       "      <td>199</td>\n",
       "      <td>1166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>730504</td>\n",
       "      <td>1.425088,103.794736</td>\n",
       "      <td>266</td>\n",
       "      <td>1547</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   HDB_postal          WHC_latlong  total_time  total_distance\n",
       "0      730501  1.425088,103.794736         216            1207\n",
       "1      730502  1.425088,103.794736         228            1267\n",
       "2      731502  1.425088,103.794736         245            1474\n",
       "3      730503  1.425088,103.794736         199            1166\n",
       "4      730504  1.425088,103.794736         266            1547"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_list = []\n",
    "\n",
    "for i in range(len(df_whc_hdb)):\n",
    "    route = onemap.route(df_whc_hdb.iloc[i]['postal_latlong'], df_whc_hdb.iloc[i]['WHC_latlong'],'drive')\n",
    "    time_taken = route.to_dict()['route_summary']['total_time']\n",
    "    distance = route.to_dict()['route_summary']['total_distance']\n",
    "    source = df_whc_hdb.iloc[i]['POSTAL']\n",
    "    sink = df_whc_hdb.iloc[i]['WHC_latlong']\n",
    "    sequence = []\n",
    "    sequence.extend([source, sink, time_taken, distance])\n",
    "    full_list.append(sequence)\n",
    "\n",
    "df_whc_hdb_drive = pd.DataFrame(full_list, columns = ['HDB_postal', 'WHC_latlong', 'total_time', 'total_distance'])\n",
    "\n",
    "# Export postal to WHC time distance\n",
    "#df_whc_hdb_drive.to_excel(\"Distance and Driving Time from HDB to WHC.xlsx\", index=False, encoding='utf-8-sig')\n",
    "\n",
    "df_whc_hdb_drive.head()\n",
    "\n",
    "# The time is in seconds, while distance is in metres"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name=\"part9\"></a>\n",
    "### Generation of Random Elderly Population Numbers per HDB Block\n",
    "\n",
    "Over here, we generate random numbers of elderly individuals (aged ≥ 65 years old) per HDB block. This is because we do not have granular number of elderly individuals per HDB block (data not publicly available), while we only have total number of elderly per subzone from SingStats, including Woodlands South. Therefore we will be doing a random (estimate) distribution based on the total sum. This simulated data is needed for optimization, where we want to maximize the number of elderly we can reach from the placement of our health wellness center locations. \n",
    "\n",
    "Based on the 2019 data from the 'Singapore Residents by Planning AreaSubzone, Age Group, Sex and Type of Dwelling, June 2000-2019' dataset (https://www.singstat.gov.sg/find-data/search-by-theme/population/geographic-distribution/latest-data), we determine the elderly population in the Woodlands South subzone to be 3,030. We will proceed to randomize the distribution of this value of 3,030 amongst the 101 HDB blocks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\klty0\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:4: FutureWarning: Columnar iteration over characters will be deprecated in future releases.\n",
      "  after removing the cwd from sys.path.\n"
     ]
    },
    {
     "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>POSTAL</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>elderly_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730501</td>\n",
       "      <td>1.43292524188657</td>\n",
       "      <td>103.792006998018</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>730502</td>\n",
       "      <td>1.43318008766272</td>\n",
       "      <td>103.79179043736</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>731502</td>\n",
       "      <td>1.43330461383637</td>\n",
       "      <td>103.791313633199</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>730503</td>\n",
       "      <td>1.43249995976889</td>\n",
       "      <td>103.791786711705</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>730504</td>\n",
       "      <td>1.43298242263143</td>\n",
       "      <td>103.791063262859</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   POSTAL          latitude         longitude  elderly_count\n",
       "0  730501  1.43292524188657  103.792006998018             38\n",
       "1  730502  1.43318008766272   103.79179043736             49\n",
       "2  731502  1.43330461383637  103.791313633199              1\n",
       "3  730503  1.43249995976889  103.791786711705              9\n",
       "4  730504  1.43298242263143  103.791063262859             16"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create random elderly population distribution numbers for dataset\n",
    "df_population = df_clean[[\"POSTAL\", \"postal_latlong\"]]\n",
    "\n",
    "df_population['latitude'], df_population['longitude'] = df_population['postal_latlong'].str.split(',', 1).str\n",
    "df_population.drop('postal_latlong', axis = 1, inplace = True)\n",
    "\n",
    "# Randomize the 3,030 elderly into the 101 HDB blocks\n",
    "# https://stackoverflow.com/questions/3589214/generate-multiple-random-numbers-to-equal-a-value-in-python\n",
    "def constrained_sum_sample_pos(n, total):\n",
    "    dividers = sorted(random.sample(range(1, total), n - 1))\n",
    "    return [a - b for a, b in zip(dividers + [total], [0] + dividers)]\n",
    "\n",
    "df_population['elderly_count'] = pd.Series(constrained_sum_sample_pos(101, 3030))\n",
    "\n",
    "# df_population.to_excel(\"Elderly Population by HDB Postal.xlsx\", index=False, encoding='utf-8-sig')\n",
    "\n",
    "df_population.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Thank you for reviewing! Would greatly appreciate your feedback and suggestions on how to further improve this notebook!"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}