# data[].attributes.body
i cant seem to get the script to run for qn 2.... waited for over an hour... how do i fix it?

# data[].attributes.body
Apologies for late submission, I'm busy with my dad who is still dealing with covid at this time. And I'm still having issue at the last questions.

# data[].attributes.attachment_link.readable_text
{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "NpPlK3f9VIJg"
   },
   "source": [
    "# Pandas: \n",
    "### - Calling APIs using Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "gN2RFGn3VIJi"
   },
   "source": [
    "<strong> <h2> Run The Following Code </h2> </strong>\n",
    "\n",
    "Hit SHIFT + ENTER when your cusor is inside the cell of code.<br/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "3fskFP6UVIJo"
   },
   "source": [
    "> Import the packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "id": "GChe2i8UVIJo"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calling API Example 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> 1. Open this url in your browser: https://data.gov.sg/dataset/list-of-government-markets-hawker-centres\n",
    "> 2. Click on the \"Data API\" button on top rigth corner to see resource_id for this dataset\n",
    "> 3. Use **requests** package to call this API and get all first 5 rows of data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Option A - Manually construct the request URL\n",
    "url_full = 'https://data.gov.sg/api/action/datastore_search?resource_id=8f6bba57-19fc-4f36-8dcf-c0bda382364d&limit=5'\n",
    "\n",
    "response = requests.get(url_full)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Option B - (Recommneded) passing a dictionary to .get() method in requests to construct the request url\n",
    "url_base = 'https://data.gov.sg/api/action/datastore_search'\n",
    "\n",
    "parameters = {\n",
    "    'resource_id' : '8f6bba57-19fc-4f36-8dcf-c0bda382364d',\n",
    "    'limit': '5'\n",
    "}\n",
    "response = requests.get(url_base, params=parameters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'https://data.gov.sg/api/action/datastore_search?resource_id=8f6bba57-19fc-4f36-8dcf-c0bda382364d&limit=5'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the url that sent to the API server\n",
    "response.url"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "200"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the status of the request\n",
    "response.status_code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'help': 'https://data.gov.sg/api/3/action/help_show?name=datastore_search',\n",
       " 'success': True,\n",
       " 'result': {'resource_id': '8f6bba57-19fc-4f36-8dcf-c0bda382364d',\n",
       "  'fields': [{'type': 'int4', 'id': '_id'},\n",
       "   {'type': 'text', 'id': 'name_of_centre'},\n",
       "   {'type': 'text', 'id': 'location_of_centre'},\n",
       "   {'type': 'text', 'id': 'type_of_centre'},\n",
       "   {'type': 'text', 'id': 'owner'},\n",
       "   {'type': 'numeric', 'id': 'no_of_stalls'},\n",
       "   {'type': 'numeric', 'id': 'no_of_cooked_food_stalls'},\n",
       "   {'type': 'numeric', 'id': 'no_of_mkt_produce_stalls'}],\n",
       "  'records': [{'location_of_centre': '2, Adam Road, S(289876)',\n",
       "    'no_of_cooked_food_stalls': '32',\n",
       "    'no_of_mkt_produce_stalls': '0',\n",
       "    'name_of_centre': 'Adam Road Food Centre',\n",
       "    'type_of_centre': 'HC',\n",
       "    'no_of_stalls': '32',\n",
       "    'owner': 'Government',\n",
       "    '_id': 1},\n",
       "   {'location_of_centre': 'National Development Building, Annex B, Telok Ayer Street, S(069111)',\n",
       "    'no_of_cooked_food_stalls': '134',\n",
       "    'no_of_mkt_produce_stalls': '1',\n",
       "    'name_of_centre': 'Amoy Street Food Centre',\n",
       "    'type_of_centre': 'HC',\n",
       "    'no_of_stalls': '135',\n",
       "    'owner': 'Government',\n",
       "    '_id': 2},\n",
       "   {'location_of_centre': '1, Bedok Road, S(469572)',\n",
       "    'no_of_cooked_food_stalls': '32',\n",
       "    'no_of_mkt_produce_stalls': '0',\n",
       "    'name_of_centre': 'Bedok Food Centre',\n",
       "    'type_of_centre': 'HC',\n",
       "    'no_of_stalls': '32',\n",
       "    'owner': 'Government',\n",
       "    '_id': 3},\n",
       "   {'location_of_centre': '38A, Beo Crescent, S(169982)',\n",
       "    'no_of_cooked_food_stalls': '32',\n",
       "    'no_of_mkt_produce_stalls': '62',\n",
       "    'name_of_centre': 'Beo Crescent Market',\n",
       "    'type_of_centre': 'MHC',\n",
       "    'no_of_stalls': '94',\n",
       "    'owner': 'Government',\n",
       "    '_id': 4},\n",
       "   {'location_of_centre': '166, Jalan Besar, S(208877)',\n",
       "    'no_of_cooked_food_stalls': '66',\n",
       "    'no_of_mkt_produce_stalls': '0',\n",
       "    'name_of_centre': 'Berseh Food Centre',\n",
       "    'type_of_centre': 'HC',\n",
       "    'no_of_stalls': '66',\n",
       "    'owner': 'Government',\n",
       "    '_id': 5}],\n",
       "  '_links': {'start': '/api/action/datastore_search?limit=5&resource_id=8f6bba57-19fc-4f36-8dcf-c0bda382364d',\n",
       "   'next': '/api/action/datastore_search?offset=5&limit=5&resource_id=8f6bba57-19fc-4f36-8dcf-c0bda382364d'},\n",
       "  'limit': 5,\n",
       "  'total': 107}}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# View the json text returned by the API server\n",
    "response_dict = response.json()\n",
    "response_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> The json text above is a nested-dictionary <br>\n",
    "> Picture below shows the structure in a visual form\n",
    "\n",
    "> ![](hawker_structure.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "107"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Traverse the dictionary to the level where the main records are located\n",
    "response_dict['result']['total']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'location_of_centre': '2, Adam Road, S(289876)',\n",
       "  'no_of_cooked_food_stalls': '32',\n",
       "  'no_of_mkt_produce_stalls': '0',\n",
       "  'name_of_centre': 'Adam Road Food Centre',\n",
       "  'type_of_centre': 'HC',\n",
       "  'no_of_stalls': '32',\n",
       "  'owner': 'Government',\n",
       "  '_id': 1},\n",
       " {'location_of_centre': 'National Development Building, Annex B, Telok Ayer Street, S(069111)',\n",
       "  'no_of_cooked_food_stalls': '134',\n",
       "  'no_of_mkt_produce_stalls': '1',\n",
       "  'name_of_centre': 'Amoy Street Food Centre',\n",
       "  'type_of_centre': 'HC',\n",
       "  'no_of_stalls': '135',\n",
       "  'owner': 'Government',\n",
       "  '_id': 2},\n",
       " {'location_of_centre': '1, Bedok Road, S(469572)',\n",
       "  'no_of_cooked_food_stalls': '32',\n",
       "  'no_of_mkt_produce_stalls': '0',\n",
       "  'name_of_centre': 'Bedok Food Centre',\n",
       "  'type_of_centre': 'HC',\n",
       "  'no_of_stalls': '32',\n",
       "  'owner': 'Government',\n",
       "  '_id': 3},\n",
       " {'location_of_centre': '38A, Beo Crescent, S(169982)',\n",
       "  'no_of_cooked_food_stalls': '32',\n",
       "  'no_of_mkt_produce_stalls': '62',\n",
       "  'name_of_centre': 'Beo Crescent Market',\n",
       "  'type_of_centre': 'MHC',\n",
       "  'no_of_stalls': '94',\n",
       "  'owner': 'Government',\n",
       "  '_id': 4},\n",
       " {'location_of_centre': '166, Jalan Besar, S(208877)',\n",
       "  'no_of_cooked_food_stalls': '66',\n",
       "  'no_of_mkt_produce_stalls': '0',\n",
       "  'name_of_centre': 'Berseh Food Centre',\n",
       "  'type_of_centre': 'HC',\n",
       "  'no_of_stalls': '66',\n",
       "  'owner': 'Government',\n",
       "  '_id': 5}]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Traverse the dictionary to the level where the main records are located\n",
    "records = response_dict['result']['records']\n",
    "records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the number of records\n",
    "len(records)"
   ]
  },
  {
   "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>location_of_centre</th>\n",
       "      <th>no_of_cooked_food_stalls</th>\n",
       "      <th>no_of_mkt_produce_stalls</th>\n",
       "      <th>name_of_centre</th>\n",
       "      <th>type_of_centre</th>\n",
       "      <th>no_of_stalls</th>\n",
       "      <th>owner</th>\n",
       "      <th>_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2, Adam Road, S(289876)</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>Adam Road Food Centre</td>\n",
       "      <td>HC</td>\n",
       "      <td>32</td>\n",
       "      <td>Government</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>National Development Building, Annex B, Telok ...</td>\n",
       "      <td>134</td>\n",
       "      <td>1</td>\n",
       "      <td>Amoy Street Food Centre</td>\n",
       "      <td>HC</td>\n",
       "      <td>135</td>\n",
       "      <td>Government</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1, Bedok Road, S(469572)</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>Bedok Food Centre</td>\n",
       "      <td>HC</td>\n",
       "      <td>32</td>\n",
       "      <td>Government</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>38A, Beo Crescent, S(169982)</td>\n",
       "      <td>32</td>\n",
       "      <td>62</td>\n",
       "      <td>Beo Crescent Market</td>\n",
       "      <td>MHC</td>\n",
       "      <td>94</td>\n",
       "      <td>Government</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>166, Jalan Besar, S(208877)</td>\n",
       "      <td>66</td>\n",
       "      <td>0</td>\n",
       "      <td>Berseh Food Centre</td>\n",
       "      <td>HC</td>\n",
       "      <td>66</td>\n",
       "      <td>Government</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  location_of_centre no_of_cooked_food_stalls  \\\n",
       "0                            2, Adam Road, S(289876)                       32   \n",
       "1  National Development Building, Annex B, Telok ...                      134   \n",
       "2                           1, Bedok Road, S(469572)                       32   \n",
       "3                       38A, Beo Crescent, S(169982)                       32   \n",
       "4                        166, Jalan Besar, S(208877)                       66   \n",
       "\n",
       "  no_of_mkt_produce_stalls           name_of_centre type_of_centre  \\\n",
       "0                        0    Adam Road Food Centre             HC   \n",
       "1                        1  Amoy Street Food Centre             HC   \n",
       "2                        0        Bedok Food Centre             HC   \n",
       "3                       62      Beo Crescent Market            MHC   \n",
       "4                        0       Berseh Food Centre             HC   \n",
       "\n",
       "  no_of_stalls       owner  _id  \n",
       "0           32  Government    1  \n",
       "1          135  Government    2  \n",
       "2           32  Government    3  \n",
       "3           94  Government    4  \n",
       "4           66  Government    5  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use .json_normalize from Pandas to convert the dictionary into a DataFrame\n",
    "pd.json_normalize(records)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use .json_normalize from Pandas to convert the dictionary into a DataFrame \n",
    "# Store into a variable called \"df_hawker\"\n",
    "df_hawker = pd.json_normalize(records)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calling API Example 2: with Parameters"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> 1. Open this url in your browser: https://www.onemap.gov.sg/docs/#onemap-rest-apis\n",
    "> 2. Understand the parameters (required and optional) of the \"search\" endpoint from OneMap API\n",
    "> 3. Use **requests** package to call this API and get all first 5 rows of data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> ![apiexp](onemap_search.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "400"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Try to call the API (Case-sensitive Parameters not match)\n",
    "\n",
    "url_base = 'https://developers.onemap.sg/commonapi/search'\n",
    "\n",
    "parameters = {\n",
    "    'searchval' : 'Adam Road Food Centre',\n",
    "    'returngeom': 'Y',\n",
    "    'getAddrDetails': 'Y'\n",
    "}\n",
    "response = requests.get(url_base, params=parameters)\n",
    "response.status_code\n",
    "# Outcome is 400 status code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "400"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Try to call the API (Required Parameters not provided)\n",
    "\n",
    "url_base = 'https://developers.onemap.sg/commonapi/search'\n",
    "\n",
    "parameters = {\n",
    "    'searchval' : 'Adam Road Food Centre',\n",
    "}\n",
    "response = requests.get(url_base, params=parameters)\n",
    "response.status_code\n",
    "# Outcome is 400 series status code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "200"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Try to call the API (Correctly)\n",
    "\n",
    "url_base = 'https://developers.onemap.sg/commonapi/search'\n",
    "\n",
    "parameters = {\n",
    "    'searchVal' : 'Adam Road Food Centre',\n",
    "    'returnGeom': 'Y',\n",
    "    'getAddrDetails': 'Y'\n",
    "}\n",
    "response = requests.get(url_base, params=parameters)\n",
    "response.status_code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'found': 1,\n",
       " 'totalNumPages': 1,\n",
       " 'pageNum': 1,\n",
       " 'results': [{'SEARCHVAL': 'ADAM ROAD FOOD CENTRE',\n",
       "   'BLK_NO': '2',\n",
       "   'ROAD_NAME': 'ADAM ROAD',\n",
       "   'BUILDING': 'ADAM ROAD FOOD CENTRE',\n",
       "   'ADDRESS': '2 ADAM ROAD ADAM ROAD FOOD CENTRE SINGAPORE 289876',\n",
       "   'POSTAL': '289876',\n",
       "   'X': '25870.3018411137',\n",
       "   'Y': '34035.8644314632',\n",
       "   'LATITUDE': '1.3240827139625',\n",
       "   'LONGITUDE': '103.814182099841',\n",
       "   'LONGTITUDE': '103.814182099841'}]}"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# View the json text returned by the server\n",
    "response.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>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>ADAM ROAD FOOD CENTRE</td>\n",
       "      <td>2</td>\n",
       "      <td>ADAM ROAD</td>\n",
       "      <td>ADAM ROAD FOOD CENTRE</td>\n",
       "      <td>2 ADAM ROAD ADAM ROAD FOOD CENTRE SINGAPORE 28...</td>\n",
       "      <td>289876</td>\n",
       "      <td>25870.3018411137</td>\n",
       "      <td>34035.8644314632</td>\n",
       "      <td>1.3240827139625</td>\n",
       "      <td>103.814182099841</td>\n",
       "      <td>103.814182099841</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               SEARCHVAL BLK_NO  ROAD_NAME               BUILDING  \\\n",
       "0  ADAM ROAD FOOD CENTRE      2  ADAM ROAD  ADAM ROAD FOOD CENTRE   \n",
       "\n",
       "                                             ADDRESS  POSTAL  \\\n",
       "0  2 ADAM ROAD ADAM ROAD FOOD CENTRE SINGAPORE 28...  289876   \n",
       "\n",
       "                  X                 Y         LATITUDE         LONGITUDE  \\\n",
       "0  25870.3018411137  34035.8644314632  1.3240827139625  103.814182099841   \n",
       "\n",
       "         LONGTITUDE  \n",
       "0  103.814182099841  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use .json_normalize from Pandas to convert the dictionary into a DataFrame \n",
    "pd.json_normalize(response.json()['results'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> 1. Loop through the **df_hawker** and find the geoinfo of each hawker from OneMap's SEARCH endpoint\n",
    "> 2. Store all the geoinfo as a new DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_hawker_geoinfo = []\n",
    "\n",
    "for row_index, row in df_hawker.iterrows():\n",
    "    # Create a dictionary of the parameters\n",
    "    parameters = {\n",
    "        'searchVal' : row['name_of_centre'],\n",
    "        'returnGeom': 'Y',\n",
    "        'getAddrDetails': 'Y'\n",
    "    }\n",
    "\n",
    "    # Send the request via .get() method\n",
    "    response = requests.get(url_base, params=parameters)\n",
    "\n",
    "    # Check if the request is successful\n",
    "    if response.status_code == 200:\n",
    "        response_dict = response.json()\n",
    "        # Check if there is at least 1 record\n",
    "        if len(response_dict['results']) > 0:\n",
    "            df_temp = pd.json_normalize(response_dict['results'])\n",
    "            df_hawker_geoinfo.append(df_temp)\n",
    "    else:\n",
    "        print('The request failed and returned status code:' + str(response.status_code))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>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>ADAM ROAD FOOD CENTRE</td>\n",
       "      <td>2</td>\n",
       "      <td>ADAM ROAD</td>\n",
       "      <td>ADAM ROAD FOOD CENTRE</td>\n",
       "      <td>2 ADAM ROAD ADAM ROAD FOOD CENTRE SINGAPORE 28...</td>\n",
       "      <td>289876</td>\n",
       "      <td>25870.3018411137</td>\n",
       "      <td>34035.8644314632</td>\n",
       "      <td>1.3240827139625</td>\n",
       "      <td>103.814182099841</td>\n",
       "      <td>103.814182099841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AMOY STREET FOOD CENTRE</td>\n",
       "      <td>7</td>\n",
       "      <td>MAXWELL ROAD</td>\n",
       "      <td>AMOY STREET FOOD CENTRE</td>\n",
       "      <td>7 MAXWELL ROAD AMOY STREET FOOD CENTRE SINGAPO...</td>\n",
       "      <td>069111</td>\n",
       "      <td>29483.955767219</td>\n",
       "      <td>29088.4261356071</td>\n",
       "      <td>1.2793398636571</td>\n",
       "      <td>103.846652482254</td>\n",
       "      <td>103.846652482254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BEDOK FOOD CENTRE</td>\n",
       "      <td>1</td>\n",
       "      <td>BEDOK ROAD</td>\n",
       "      <td>BEDOK FOOD CENTRE</td>\n",
       "      <td>1 BEDOK ROAD BEDOK FOOD CENTRE SINGAPORE 469572</td>\n",
       "      <td>469572</td>\n",
       "      <td>41595.4345044643</td>\n",
       "      <td>33623.132626934</td>\n",
       "      <td>1.32034716834128</td>\n",
       "      <td>103.955480570427</td>\n",
       "      <td>103.955480570427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BEO CRESCENT MARKET</td>\n",
       "      <td>38A</td>\n",
       "      <td>BEO CRESCENT</td>\n",
       "      <td>BEO CRESCENT MARKET</td>\n",
       "      <td>38A BEO CRESCENT BEO CRESCENT MARKET SINGAPORE...</td>\n",
       "      <td>169982</td>\n",
       "      <td>27336.1809281698</td>\n",
       "      <td>30137.891893005</td>\n",
       "      <td>1.28883089150258</td>\n",
       "      <td>103.827353892228</td>\n",
       "      <td>103.827353892228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BERSEH FOOD CENTRE</td>\n",
       "      <td>166</td>\n",
       "      <td>JALAN BESAR</td>\n",
       "      <td>BERSEH FOOD CENTRE</td>\n",
       "      <td>166 JALAN BESAR BERSEH FOOD CENTRE SINGAPORE 2...</td>\n",
       "      <td>208877</td>\n",
       "      <td>30623.1448179465</td>\n",
       "      <td>32184.9996254593</td>\n",
       "      <td>1.30734410946371</td>\n",
       "      <td>103.856888783769</td>\n",
       "      <td>103.856888783769</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 SEARCHVAL BLK_NO     ROAD_NAME                 BUILDING  \\\n",
       "0    ADAM ROAD FOOD CENTRE      2     ADAM ROAD    ADAM ROAD FOOD CENTRE   \n",
       "1  AMOY STREET FOOD CENTRE      7  MAXWELL ROAD  AMOY STREET FOOD CENTRE   \n",
       "2        BEDOK FOOD CENTRE      1    BEDOK ROAD        BEDOK FOOD CENTRE   \n",
       "3      BEO CRESCENT MARKET    38A  BEO CRESCENT      BEO CRESCENT MARKET   \n",
       "4       BERSEH FOOD CENTRE    166   JALAN BESAR       BERSEH FOOD CENTRE   \n",
       "\n",
       "                                             ADDRESS  POSTAL  \\\n",
       "0  2 ADAM ROAD ADAM ROAD FOOD CENTRE SINGAPORE 28...  289876   \n",
       "1  7 MAXWELL ROAD AMOY STREET FOOD CENTRE SINGAPO...  069111   \n",
       "2    1 BEDOK ROAD BEDOK FOOD CENTRE SINGAPORE 469572  469572   \n",
       "3  38A BEO CRESCENT BEO CRESCENT MARKET SINGAPORE...  169982   \n",
       "4  166 JALAN BESAR BERSEH FOOD CENTRE SINGAPORE 2...  208877   \n",
       "\n",
       "                  X                 Y          LATITUDE         LONGITUDE  \\\n",
       "0  25870.3018411137  34035.8644314632   1.3240827139625  103.814182099841   \n",
       "1   29483.955767219  29088.4261356071   1.2793398636571  103.846652482254   \n",
       "2  41595.4345044643   33623.132626934  1.32034716834128  103.955480570427   \n",
       "3  27336.1809281698   30137.891893005  1.28883089150258  103.827353892228   \n",
       "4  30623.1448179465  32184.9996254593  1.30734410946371  103.856888783769   \n",
       "\n",
       "         LONGTITUDE  \n",
       "0  103.814182099841  \n",
       "1  103.846652482254  \n",
       "2  103.955480570427  \n",
       "3  103.827353892228  \n",
       "4  103.856888783769  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Concatenate the records stored in multiple DataFrames into a single DataFrame\n",
    "df_hawker_geoinfo = pd.concat(df_hawker_geoinfo, axis=0, ignore_index=True)\n",
    "df_hawker_geoinfo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# !! Your Turn !!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Replace the code marked as <..> COMPLETELY with your own.\n",
    "- The placeholder <..> is meant to be guidance for your answer.\n",
    "- It should not be restricting your solutions for the questions.\n",
    "- Feel free to add more lines or use less lines,\n",
    "- One placeholder does not mean that you can only insert one line of codes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 1\n",
    "> - Continue from the **df_hawker** and **df_hawker_geoinfo**, produce a new DataFrame **df_hawker_center**, <br>\n",
    "> where each row is a unique hawker center with all the columns both **df_hawker** and **df_hawker_geoinfo**\n",
    "> - The four columns \"X\", \"Y\", \"LONGTITUDE\", and \"_id\" must be excluded in the **df_hawker_center**\n",
    "> - Change all the column names into lower case"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>location_of_centre</th>\n",
       "      <th>no_of_cooked_food_stalls</th>\n",
       "      <th>no_of_mkt_produce_stalls</th>\n",
       "      <th>name_of_centre</th>\n",
       "      <th>type_of_centre</th>\n",
       "      <th>no_of_stalls</th>\n",
       "      <th>owner</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>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2, Adam Road, S(289876)</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>Adam Road Food Centre</td>\n",
       "      <td>HC</td>\n",
       "      <td>32</td>\n",
       "      <td>Government</td>\n",
       "      <td>2</td>\n",
       "      <td>ADAM ROAD</td>\n",
       "      <td>ADAM ROAD FOOD CENTRE</td>\n",
       "      <td>2 ADAM ROAD ADAM ROAD FOOD CENTRE SINGAPORE 28...</td>\n",
       "      <td>289876</td>\n",
       "      <td>1.3240827139625</td>\n",
       "      <td>103.814182099841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>National Development Building, Annex B, Telok ...</td>\n",
       "      <td>134</td>\n",
       "      <td>1</td>\n",
       "      <td>Amoy Street Food Centre</td>\n",
       "      <td>HC</td>\n",
       "      <td>135</td>\n",
       "      <td>Government</td>\n",
       "      <td>7</td>\n",
       "      <td>MAXWELL ROAD</td>\n",
       "      <td>AMOY STREET FOOD CENTRE</td>\n",
       "      <td>7 MAXWELL ROAD AMOY STREET FOOD CENTRE SINGAPO...</td>\n",
       "      <td>069111</td>\n",
       "      <td>1.2793398636571</td>\n",
       "      <td>103.846652482254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1, Bedok Road, S(469572)</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>Bedok Food Centre</td>\n",
       "      <td>HC</td>\n",
       "      <td>32</td>\n",
       "      <td>Government</td>\n",
       "      <td>1</td>\n",
       "      <td>BEDOK ROAD</td>\n",
       "      <td>BEDOK FOOD CENTRE</td>\n",
       "      <td>1 BEDOK ROAD BEDOK FOOD CENTRE SINGAPORE 469572</td>\n",
       "      <td>469572</td>\n",
       "      <td>1.32034716834128</td>\n",
       "      <td>103.955480570427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>38A, Beo Crescent, S(169982)</td>\n",
       "      <td>32</td>\n",
       "      <td>62</td>\n",
       "      <td>Beo Crescent Market</td>\n",
       "      <td>MHC</td>\n",
       "      <td>94</td>\n",
       "      <td>Government</td>\n",
       "      <td>38A</td>\n",
       "      <td>BEO CRESCENT</td>\n",
       "      <td>BEO CRESCENT MARKET</td>\n",
       "      <td>38A BEO CRESCENT BEO CRESCENT MARKET SINGAPORE...</td>\n",
       "      <td>169982</td>\n",
       "      <td>1.28883089150258</td>\n",
       "      <td>103.827353892228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>166, Jalan Besar, S(208877)</td>\n",
       "      <td>66</td>\n",
       "      <td>0</td>\n",
       "      <td>Berseh Food Centre</td>\n",
       "      <td>HC</td>\n",
       "      <td>66</td>\n",
       "      <td>Government</td>\n",
       "      <td>166</td>\n",
       "      <td>JALAN BESAR</td>\n",
       "      <td>BERSEH FOOD CENTRE</td>\n",
       "      <td>166 JALAN BESAR BERSEH FOOD CENTRE SINGAPORE 2...</td>\n",
       "      <td>208877</td>\n",
       "      <td>1.30734410946371</td>\n",
       "      <td>103.856888783769</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  location_of_centre no_of_cooked_food_stalls  \\\n",
       "0                            2, Adam Road, S(289876)                       32   \n",
       "1  National Development Building, Annex B, Telok ...                      134   \n",
       "2                           1, Bedok Road, S(469572)                       32   \n",
       "3                       38A, Beo Crescent, S(169982)                       32   \n",
       "4                        166, Jalan Besar, S(208877)                       66   \n",
       "\n",
       "  no_of_mkt_produce_stalls           name_of_centre type_of_centre  \\\n",
       "0                        0    Adam Road Food Centre             HC   \n",
       "1                        1  Amoy Street Food Centre             HC   \n",
       "2                        0        Bedok Food Centre             HC   \n",
       "3                       62      Beo Crescent Market            MHC   \n",
       "4                        0       Berseh Food Centre             HC   \n",
       "\n",
       "  no_of_stalls       owner BLK_NO     ROAD_NAME                 BUILDING  \\\n",
       "0           32  Government      2     ADAM ROAD    ADAM ROAD FOOD CENTRE   \n",
       "1          135  Government      7  MAXWELL ROAD  AMOY STREET FOOD CENTRE   \n",
       "2           32  Government      1    BEDOK ROAD        BEDOK FOOD CENTRE   \n",
       "3           94  Government    38A  BEO CRESCENT      BEO CRESCENT MARKET   \n",
       "4           66  Government    166   JALAN BESAR       BERSEH FOOD CENTRE   \n",
       "\n",
       "                                             ADDRESS  POSTAL  \\\n",
       "0  2 ADAM ROAD ADAM ROAD FOOD CENTRE SINGAPORE 28...  289876   \n",
       "1  7 MAXWELL ROAD AMOY STREET FOOD CENTRE SINGAPO...  069111   \n",
       "2    1 BEDOK ROAD BEDOK FOOD CENTRE SINGAPORE 469572  469572   \n",
       "3  38A BEO CRESCENT BEO CRESCENT MARKET SINGAPORE...  169982   \n",
       "4  166 JALAN BESAR BERSEH FOOD CENTRE SINGAPORE 2...  208877   \n",
       "\n",
       "           LATITUDE         LONGITUDE  \n",
       "0   1.3240827139625  103.814182099841  \n",
       "1   1.2793398636571  103.846652482254  \n",
       "2  1.32034716834128  103.955480570427  \n",
       "3  1.28883089150258  103.827353892228  \n",
       "4  1.30734410946371  103.856888783769  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_hawker_geoinfo.rename (columns={'SEARCHVAL':'name_of_centre'},inplace=True)\n",
    "df_hawker_geoinfo['name_of_centre'] = df_hawker_geoinfo['name_of_centre'].str.title()\n",
    "df_hawker_centre = pd.merge(df_hawker,df_hawker_geoinfo,'left','name_of_centre')\n",
    "df_hawker_centre.drop (['X','Y','LONGTITUDE','_id'],axis=1, inplace=True)\n",
    "df_hawker_centre\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 2\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Part A) <br>\n",
    "> Store the first 100 records from the csv file located at **data > 0_raw_data > MRT.csv** into a DataFrame, called **df_mrt_subset**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>Type</th>\n",
       "      <th>postal</th>\n",
       "      <th>Station Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MRT</td>\n",
       "      <td>609690</td>\n",
       "      <td>Jurong East</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MRT</td>\n",
       "      <td>659958</td>\n",
       "      <td>Bukit Batok</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MRT</td>\n",
       "      <td>659083</td>\n",
       "      <td>Bukit Gombak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MRT</td>\n",
       "      <td>689810</td>\n",
       "      <td>Choa Chu Kang</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MRT</td>\n",
       "      <td>689715</td>\n",
       "      <td>Yew Tee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>MRT</td>\n",
       "      <td>278995</td>\n",
       "      <td>Holland Village</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>MRT</td>\n",
       "      <td>139345</td>\n",
       "      <td>Buona Vista</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>MRT</td>\n",
       "      <td>138647</td>\n",
       "      <td>one-north</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>MRT</td>\n",
       "      <td>118177</td>\n",
       "      <td>Kent Ridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>MRT</td>\n",
       "      <td>117396</td>\n",
       "      <td>Haw Par Villa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Type  postal     Station Name\n",
       "0   MRT  609690      Jurong East\n",
       "1   MRT  659958      Bukit Batok\n",
       "2   MRT  659083     Bukit Gombak\n",
       "3   MRT  689810    Choa Chu Kang\n",
       "4   MRT  689715          Yew Tee\n",
       "..  ...     ...              ...\n",
       "95  MRT  278995  Holland Village\n",
       "96  MRT  139345      Buona Vista\n",
       "97  MRT  138647        one-north\n",
       "98  MRT  118177       Kent Ridge\n",
       "99  MRT  117396    Haw Par Villa\n",
       "\n",
       "[100 rows x 3 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_mrt_subset = pd.read_csv('data/0_raw_data/MRT.csv', nrows=100)\n",
    "df_mrt_subset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Part B) <br>\n",
    "> - Retrieve the geoinfo using OneMap's API Endpoint 'https://developers.onemap.sg/commonapi/search', <br>\n",
    "> using the postal codes of the MRT stations\n",
    "> - Store the geoinfo for the MRT stations' postal code into DataFrame **df_mrt_geoinfo** <br>\n",
    "> 💡Hint: You might need to filter the df_mrt_geoinfo to only the MRT stations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>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>JURONG EAST MRT STATION (EW24 / NS1)</td>\n",
       "      <td>10</td>\n",
       "      <td>JURONG EAST STREET 12</td>\n",
       "      <td>JURONG EAST MRT STATION (EW24 / NS1)</td>\n",
       "      <td>10 JURONG EAST STREET 12 JURONG EAST MRT STATI...</td>\n",
       "      <td>609690</td>\n",
       "      <td>17869.0570516568</td>\n",
       "      <td>35038.9688695427</td>\n",
       "      <td>1.33315281585758</td>\n",
       "      <td>103.742286332403</td>\n",
       "      <td>103.742286332403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>BUKIT BATOK MRT STATION (NS2)</td>\n",
       "      <td>10</td>\n",
       "      <td>BUKIT BATOK CENTRAL</td>\n",
       "      <td>BUKIT BATOK MRT STATION (NS2)</td>\n",
       "      <td>10 BUKIT BATOK CENTRAL BUKIT BATOK MRT STATION...</td>\n",
       "      <td>659958</td>\n",
       "      <td>18679.3223191258</td>\n",
       "      <td>36794.9260214306</td>\n",
       "      <td>1.34903331201636</td>\n",
       "      <td>103.749566478309</td>\n",
       "      <td>103.749566478309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>BUKIT GOMBAK MRT STATION (NS3)</td>\n",
       "      <td>802</td>\n",
       "      <td>BUKIT BATOK WEST AVENUE 5</td>\n",
       "      <td>BUKIT GOMBAK MRT STATION (NS3)</td>\n",
       "      <td>802 BUKIT BATOK WEST AVENUE 5 BUKIT GOMBAK MRT...</td>\n",
       "      <td>659083</td>\n",
       "      <td>18926.9127006577</td>\n",
       "      <td>37854.0358848721</td>\n",
       "      <td>1.35861159094192</td>\n",
       "      <td>103.751790910733</td>\n",
       "      <td>103.751790910733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>CHOA CHU KANG MRT STATION (NS4)</td>\n",
       "      <td>10</td>\n",
       "      <td>CHOA CHU KANG AVENUE 4</td>\n",
       "      <td>CHOA CHU KANG MRT STATION (NS4)</td>\n",
       "      <td>10 CHOA CHU KANG AVENUE 4 CHOA CHU KANG MRT ST...</td>\n",
       "      <td>689810</td>\n",
       "      <td>18101.2472041463</td>\n",
       "      <td>40812.1216052417</td>\n",
       "      <td>1.38536316540225</td>\n",
       "      <td>103.744370779756</td>\n",
       "      <td>103.744370779756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>YEW TEE MRT STATION (NS5)</td>\n",
       "      <td>61</td>\n",
       "      <td>CHOA CHU KANG DRIVE</td>\n",
       "      <td>YEW TEE MRT STATION (NS5)</td>\n",
       "      <td>61 CHOA CHU KANG DRIVE YEW TEE MRT STATION (NS...</td>\n",
       "      <td>689715</td>\n",
       "      <td>18438.9831173802</td>\n",
       "      <td>42158.0181021616</td>\n",
       "      <td>1.39753506936297</td>\n",
       "      <td>103.747405150236</td>\n",
       "      <td>103.747405150236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>445</th>\n",
       "      <td>HOLLAND VILLAGE MRT STATION (CC21)</td>\n",
       "      <td>200</td>\n",
       "      <td>HOLLAND AVENUE</td>\n",
       "      <td>HOLLAND VILLAGE MRT STATION (CC21)</td>\n",
       "      <td>200 HOLLAND AVENUE HOLLAND VILLAGE MRT STATION...</td>\n",
       "      <td>278995</td>\n",
       "      <td>23891.2152273953</td>\n",
       "      <td>32726.4128267691</td>\n",
       "      <td>1.31224029631595</td>\n",
       "      <td>103.796399122407</td>\n",
       "      <td>103.796399122407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>451</th>\n",
       "      <td>BUONA VISTA MRT STATION (EW21)</td>\n",
       "      <td>100</td>\n",
       "      <td>NORTH BUONA VISTA ROAD</td>\n",
       "      <td>BUONA VISTA MRT STATION (EW21)</td>\n",
       "      <td>100 NORTH BUONA VISTA ROAD BUONA VISTA MRT STA...</td>\n",
       "      <td>139345</td>\n",
       "      <td>23207.2578019593</td>\n",
       "      <td>32171.7150982097</td>\n",
       "      <td>1.3072237082044</td>\n",
       "      <td>103.790253514502</td>\n",
       "      <td>103.790253514502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>456</th>\n",
       "      <td>ONE-NORTH MRT STATION (CC23)</td>\n",
       "      <td>9</td>\n",
       "      <td>AYER RAJAH AVENUE</td>\n",
       "      <td>ONE-NORTH MRT STATION (CC23)</td>\n",
       "      <td>9 AYER RAJAH AVENUE ONE-NORTH MRT STATION (CC2...</td>\n",
       "      <td>138647</td>\n",
       "      <td>22896.0728127016</td>\n",
       "      <td>31346.4080956969</td>\n",
       "      <td>1.29975987493555</td>\n",
       "      <td>103.78745750525</td>\n",
       "      <td>103.78745750525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>460</th>\n",
       "      <td>KENT RIDGE MRT STATION (CC24)</td>\n",
       "      <td>301</td>\n",
       "      <td>SOUTH BUONA VISTA ROAD</td>\n",
       "      <td>KENT RIDGE MRT STATION (CC24)</td>\n",
       "      <td>301 SOUTH BUONA VISTA ROAD KENT RIDGE MRT STAT...</td>\n",
       "      <td>118177</td>\n",
       "      <td>22575.0107144611</td>\n",
       "      <td>30657.9332947638</td>\n",
       "      <td>1.29353349887123</td>\n",
       "      <td>103.784572738173</td>\n",
       "      <td>103.784572738173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464</th>\n",
       "      <td>HAW PAR VILLA MRT STATION (CC25)</td>\n",
       "      <td>270</td>\n",
       "      <td>PASIR PANJANG ROAD</td>\n",
       "      <td>HAW PAR VILLA MRT STATION (CC25)</td>\n",
       "      <td>270 PASIR PANJANG ROAD HAW PAR VILLA MRT STATI...</td>\n",
       "      <td>117396</td>\n",
       "      <td>22261.1431040755</td>\n",
       "      <td>29445.802035436</td>\n",
       "      <td>1.28257135772488</td>\n",
       "      <td>103.781752725064</td>\n",
       "      <td>103.781752725064</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>93 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                SEARCHVAL BLK_NO                  ROAD_NAME  \\\n",
       "0    JURONG EAST MRT STATION (EW24 / NS1)     10      JURONG EAST STREET 12   \n",
       "5           BUKIT BATOK MRT STATION (NS2)     10        BUKIT BATOK CENTRAL   \n",
       "11         BUKIT GOMBAK MRT STATION (NS3)    802  BUKIT BATOK WEST AVENUE 5   \n",
       "16        CHOA CHU KANG MRT STATION (NS4)     10     CHOA CHU KANG AVENUE 4   \n",
       "20              YEW TEE MRT STATION (NS5)     61        CHOA CHU KANG DRIVE   \n",
       "..                                    ...    ...                        ...   \n",
       "445    HOLLAND VILLAGE MRT STATION (CC21)    200             HOLLAND AVENUE   \n",
       "451        BUONA VISTA MRT STATION (EW21)    100     NORTH BUONA VISTA ROAD   \n",
       "456          ONE-NORTH MRT STATION (CC23)      9          AYER RAJAH AVENUE   \n",
       "460         KENT RIDGE MRT STATION (CC24)    301     SOUTH BUONA VISTA ROAD   \n",
       "464      HAW PAR VILLA MRT STATION (CC25)    270         PASIR PANJANG ROAD   \n",
       "\n",
       "                                 BUILDING  \\\n",
       "0    JURONG EAST MRT STATION (EW24 / NS1)   \n",
       "5           BUKIT BATOK MRT STATION (NS2)   \n",
       "11         BUKIT GOMBAK MRT STATION (NS3)   \n",
       "16        CHOA CHU KANG MRT STATION (NS4)   \n",
       "20              YEW TEE MRT STATION (NS5)   \n",
       "..                                    ...   \n",
       "445    HOLLAND VILLAGE MRT STATION (CC21)   \n",
       "451        BUONA VISTA MRT STATION (EW21)   \n",
       "456          ONE-NORTH MRT STATION (CC23)   \n",
       "460         KENT RIDGE MRT STATION (CC24)   \n",
       "464      HAW PAR VILLA MRT STATION (CC25)   \n",
       "\n",
       "                                               ADDRESS  POSTAL  \\\n",
       "0    10 JURONG EAST STREET 12 JURONG EAST MRT STATI...  609690   \n",
       "5    10 BUKIT BATOK CENTRAL BUKIT BATOK MRT STATION...  659958   \n",
       "11   802 BUKIT BATOK WEST AVENUE 5 BUKIT GOMBAK MRT...  659083   \n",
       "16   10 CHOA CHU KANG AVENUE 4 CHOA CHU KANG MRT ST...  689810   \n",
       "20   61 CHOA CHU KANG DRIVE YEW TEE MRT STATION (NS...  689715   \n",
       "..                                                 ...     ...   \n",
       "445  200 HOLLAND AVENUE HOLLAND VILLAGE MRT STATION...  278995   \n",
       "451  100 NORTH BUONA VISTA ROAD BUONA VISTA MRT STA...  139345   \n",
       "456  9 AYER RAJAH AVENUE ONE-NORTH MRT STATION (CC2...  138647   \n",
       "460  301 SOUTH BUONA VISTA ROAD KENT RIDGE MRT STAT...  118177   \n",
       "464  270 PASIR PANJANG ROAD HAW PAR VILLA MRT STATI...  117396   \n",
       "\n",
       "                    X                 Y          LATITUDE         LONGITUDE  \\\n",
       "0    17869.0570516568  35038.9688695427  1.33315281585758  103.742286332403   \n",
       "5    18679.3223191258  36794.9260214306  1.34903331201636  103.749566478309   \n",
       "11   18926.9127006577  37854.0358848721  1.35861159094192  103.751790910733   \n",
       "16   18101.2472041463  40812.1216052417  1.38536316540225  103.744370779756   \n",
       "20   18438.9831173802  42158.0181021616  1.39753506936297  103.747405150236   \n",
       "..                ...               ...               ...               ...   \n",
       "445  23891.2152273953  32726.4128267691  1.31224029631595  103.796399122407   \n",
       "451  23207.2578019593  32171.7150982097   1.3072237082044  103.790253514502   \n",
       "456  22896.0728127016  31346.4080956969  1.29975987493555   103.78745750525   \n",
       "460  22575.0107144611  30657.9332947638  1.29353349887123  103.784572738173   \n",
       "464  22261.1431040755   29445.802035436  1.28257135772488  103.781752725064   \n",
       "\n",
       "           LONGTITUDE  \n",
       "0    103.742286332403  \n",
       "5    103.749566478309  \n",
       "11   103.751790910733  \n",
       "16   103.744370779756  \n",
       "20   103.747405150236  \n",
       "..                ...  \n",
       "445  103.796399122407  \n",
       "451  103.790253514502  \n",
       "456   103.78745750525  \n",
       "460  103.784572738173  \n",
       "464  103.781752725064  \n",
       "\n",
       "[93 rows x 11 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "url_base = 'https://developers.onemap.sg/commonapi/search'\n",
    "\n",
    "df_mrt_geoinfo = []\n",
    "\n",
    "for row_index, row in df_mrt_subset.iterrows():\n",
    "    parameters = {\n",
    "        'searchVal' : int(row['postal']),\n",
    "        'returnGeom': 'Y',\n",
    "        'getAddrDetails' : 'Y'\n",
    "    }\n",
    "    \n",
    "    response = requests.get(url_base, params=parameters)\n",
    "    \n",
    "    if response.status_code == 200:\n",
    "        response_dict = response.json()\n",
    "        \n",
    "        if len (response_dict['results']) > 0:\n",
    "            df_temp = pd.json_normalize(response_dict['results'])\n",
    "            df_mrt_geoinfo.append(df_temp)\n",
    "            \n",
    "    else:\n",
    "        print('The request failed and returned status code:' + str(response.status_code))\n",
    "        \n",
    "df_mrt_geoinfo = pd.concat(df_mrt_geoinfo, axis=0, ignore_index=True)\n",
    "df_mrt_geoinfo[df_mrt_geoinfo.BUILDING.str.contains('\\(')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Part A) <br>\n",
    "> - Download all the records for HDB carpark info from the API https://data.gov.sg/dataset/hdb-carpark-information\n",
    "> - Store the records into a DataFrame, called **df_carpark**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>short_term_parking</th>\n",
       "      <th>car_park_type</th>\n",
       "      <th>y_coord</th>\n",
       "      <th>x_coord</th>\n",
       "      <th>free_parking</th>\n",
       "      <th>gantry_height</th>\n",
       "      <th>car_park_basement</th>\n",
       "      <th>night_parking</th>\n",
       "      <th>address</th>\n",
       "      <th>car_park_decks</th>\n",
       "      <th>_id</th>\n",
       "      <th>car_park_no</th>\n",
       "      <th>type_of_parking_system</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>WHOLE DAY</td>\n",
       "      <td>BASEMENT CAR PARK</td>\n",
       "      <td>31490.4942</td>\n",
       "      <td>30314.7936</td>\n",
       "      <td>NO</td>\n",
       "      <td>1.80</td>\n",
       "      <td>Y</td>\n",
       "      <td>YES</td>\n",
       "      <td>BLK 270/271 ALBERT CENTRE BASEMENT CAR PARK</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>ACB</td>\n",
       "      <td>ELECTRONIC PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WHOLE DAY</td>\n",
       "      <td>MULTI-STOREY CAR PARK</td>\n",
       "      <td>33695.5198</td>\n",
       "      <td>33758.4143</td>\n",
       "      <td>SUN &amp; PH FR 7AM-10.30PM</td>\n",
       "      <td>2.10</td>\n",
       "      <td>N</td>\n",
       "      <td>YES</td>\n",
       "      <td>BLK 98A ALJUNIED CRESCENT</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>ACM</td>\n",
       "      <td>ELECTRONIC PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WHOLE DAY</td>\n",
       "      <td>SURFACE CAR PARK</td>\n",
       "      <td>34500.3599</td>\n",
       "      <td>29257.7203</td>\n",
       "      <td>SUN &amp; PH FR 7AM-10.30PM</td>\n",
       "      <td>0.00</td>\n",
       "      <td>N</td>\n",
       "      <td>YES</td>\n",
       "      <td>BLK 101 JALAN DUSUN</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>AH1</td>\n",
       "      <td>ELECTRONIC PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7AM-7PM</td>\n",
       "      <td>SURFACE CAR PARK</td>\n",
       "      <td>39012.6664</td>\n",
       "      <td>28185.4359</td>\n",
       "      <td>NO</td>\n",
       "      <td>0.00</td>\n",
       "      <td>N</td>\n",
       "      <td>NO</td>\n",
       "      <td>BLOCK 253 ANG MO KIO STREET 21</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>AK19</td>\n",
       "      <td>COUPON PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NO</td>\n",
       "      <td>SURFACE CAR PARK</td>\n",
       "      <td>38684.1754</td>\n",
       "      <td>29482.0290</td>\n",
       "      <td>NO</td>\n",
       "      <td>0.00</td>\n",
       "      <td>N</td>\n",
       "      <td>NO</td>\n",
       "      <td>BLK 302/348 ANG MO KIO ST 31</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>AK31</td>\n",
       "      <td>COUPON PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>WHOLE DAY</td>\n",
       "      <td>SURFACE CAR PARK</td>\n",
       "      <td>40303.0500</td>\n",
       "      <td>29136.4400</td>\n",
       "      <td>SUN &amp; PH FR 7AM-10.30PM</td>\n",
       "      <td>5.40</td>\n",
       "      <td>N</td>\n",
       "      <td>YES</td>\n",
       "      <td>BLK 633/640 ANG MO KIO ST 61</td>\n",
       "      <td>0</td>\n",
       "      <td>96</td>\n",
       "      <td>A72</td>\n",
       "      <td>ELECTRONIC PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>NO</td>\n",
       "      <td>SURFACE CAR PARK</td>\n",
       "      <td>33389.4366</td>\n",
       "      <td>40720.5785</td>\n",
       "      <td>NO</td>\n",
       "      <td>0.00</td>\n",
       "      <td>N</td>\n",
       "      <td>NO</td>\n",
       "      <td>BLK 152A BEDOK SOUTH ROAD</td>\n",
       "      <td>0</td>\n",
       "      <td>374</td>\n",
       "      <td>B65L</td>\n",
       "      <td>ELECTRONIC PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>WHOLE DAY</td>\n",
       "      <td>SURFACE CAR PARK</td>\n",
       "      <td>40091.5441</td>\n",
       "      <td>28835.3369</td>\n",
       "      <td>SUN &amp; PH FR 7AM-10.30PM</td>\n",
       "      <td>0.00</td>\n",
       "      <td>N</td>\n",
       "      <td>YES</td>\n",
       "      <td>BLK 641/645 ANG MO KIO ST 61</td>\n",
       "      <td>0</td>\n",
       "      <td>97</td>\n",
       "      <td>A73</td>\n",
       "      <td>ELECTRONIC PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>WHOLE DAY</td>\n",
       "      <td>SURFACE CAR PARK</td>\n",
       "      <td>40069.8164</td>\n",
       "      <td>29166.9700</td>\n",
       "      <td>SUN &amp; PH FR 7AM-10.30PM</td>\n",
       "      <td>0.00</td>\n",
       "      <td>N</td>\n",
       "      <td>YES</td>\n",
       "      <td>BLK 646/649 ANG MO KIO ST 61</td>\n",
       "      <td>0</td>\n",
       "      <td>98</td>\n",
       "      <td>A74</td>\n",
       "      <td>ELECTRONIC PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>WHOLE DAY</td>\n",
       "      <td>SURFACE CAR PARK</td>\n",
       "      <td>39798.9610</td>\n",
       "      <td>28597.1669</td>\n",
       "      <td>SUN &amp; PH FR 7AM-10.30PM</td>\n",
       "      <td>4.50</td>\n",
       "      <td>N</td>\n",
       "      <td>YES</td>\n",
       "      <td>BLK 177/182 ANG MO KIO AVE 4</td>\n",
       "      <td>0</td>\n",
       "      <td>99</td>\n",
       "      <td>A75</td>\n",
       "      <td>ELECTRONIC PARKING</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   short_term_parking          car_park_type     y_coord     x_coord  \\\n",
       "0           WHOLE DAY      BASEMENT CAR PARK  31490.4942  30314.7936   \n",
       "1           WHOLE DAY  MULTI-STOREY CAR PARK  33695.5198  33758.4143   \n",
       "2           WHOLE DAY       SURFACE CAR PARK  34500.3599  29257.7203   \n",
       "3             7AM-7PM       SURFACE CAR PARK  39012.6664  28185.4359   \n",
       "4                  NO       SURFACE CAR PARK  38684.1754  29482.0290   \n",
       "..                ...                    ...         ...         ...   \n",
       "95          WHOLE DAY       SURFACE CAR PARK  40303.0500  29136.4400   \n",
       "96                 NO       SURFACE CAR PARK  33389.4366  40720.5785   \n",
       "97          WHOLE DAY       SURFACE CAR PARK  40091.5441  28835.3369   \n",
       "98          WHOLE DAY       SURFACE CAR PARK  40069.8164  29166.9700   \n",
       "99          WHOLE DAY       SURFACE CAR PARK  39798.9610  28597.1669   \n",
       "\n",
       "               free_parking gantry_height car_park_basement night_parking  \\\n",
       "0                        NO          1.80                 Y           YES   \n",
       "1   SUN & PH FR 7AM-10.30PM          2.10                 N           YES   \n",
       "2   SUN & PH FR 7AM-10.30PM          0.00                 N           YES   \n",
       "3                        NO          0.00                 N            NO   \n",
       "4                        NO          0.00                 N            NO   \n",
       "..                      ...           ...               ...           ...   \n",
       "95  SUN & PH FR 7AM-10.30PM          5.40                 N           YES   \n",
       "96                       NO          0.00                 N            NO   \n",
       "97  SUN & PH FR 7AM-10.30PM          0.00                 N           YES   \n",
       "98  SUN & PH FR 7AM-10.30PM          0.00                 N           YES   \n",
       "99  SUN & PH FR 7AM-10.30PM          4.50                 N           YES   \n",
       "\n",
       "                                        address car_park_decks  _id  \\\n",
       "0   BLK 270/271 ALBERT CENTRE BASEMENT CAR PARK              1    1   \n",
       "1                     BLK 98A ALJUNIED CRESCENT              5    2   \n",
       "2                           BLK 101 JALAN DUSUN              0    3   \n",
       "3                BLOCK 253 ANG MO KIO STREET 21              0    4   \n",
       "4                  BLK 302/348 ANG MO KIO ST 31              0    5   \n",
       "..                                          ...            ...  ...   \n",
       "95                 BLK 633/640 ANG MO KIO ST 61              0   96   \n",
       "96                    BLK 152A BEDOK SOUTH ROAD              0  374   \n",
       "97                 BLK 641/645 ANG MO KIO ST 61              0   97   \n",
       "98                 BLK 646/649 ANG MO KIO ST 61              0   98   \n",
       "99                 BLK 177/182 ANG MO KIO AVE 4              0   99   \n",
       "\n",
       "   car_park_no type_of_parking_system  \n",
       "0          ACB     ELECTRONIC PARKING  \n",
       "1          ACM     ELECTRONIC PARKING  \n",
       "2          AH1     ELECTRONIC PARKING  \n",
       "3         AK19         COUPON PARKING  \n",
       "4         AK31         COUPON PARKING  \n",
       "..         ...                    ...  \n",
       "95         A72     ELECTRONIC PARKING  \n",
       "96        B65L     ELECTRONIC PARKING  \n",
       "97         A73     ELECTRONIC PARKING  \n",
       "98         A74     ELECTRONIC PARKING  \n",
       "99         A75     ELECTRONIC PARKING  \n",
       "\n",
       "[100 rows x 13 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "url_base = 'https://data.gov.sg/api/action/datastore_search'\n",
    "\n",
    "parameters = {\n",
    "    'resource_id' : '139a3035-e624-4f56-b63f-89ae28d4ae4c'\n",
    "}\n",
    "response = requests.get(url_base, params=parameters)\n",
    "\n",
    "response_dict = response.json()\n",
    "\n",
    "df_carpark = pd.json_normalize (response_dict['result']['records'])\n",
    "\n",
    "df_carpark"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Part B)** - Challenging Question [Optional] <br>\n",
    "> - Check the number of records in **df_carpark**\n",
    "> - You will realize the total records on data.gov.sg is 2,183 records, but **df_carpark**only has 100 records\n",
    "> - This is because it's common for API return a \"page\" of results per call. In this case, the API returns 100 records per page.\n",
    "> - If you check *response.json()['result']['_links']*, you will see there are two links, the \"next\" link shows how to retrieve the next 100 records\n",
    "> - Find a way to download all 2,183 records and store into the dataframe **df_carparks**\n",
    "\n",
    "> hint: you can use anything that you have learnt so far to achieve this"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'start': '/api/action/datastore_search?resource_id=139a3035-e624-4f56-b63f-89ae28d4ae4c',\n",
       " 'next': '/api/action/datastore_search?offset=100&resource_id=139a3035-e624-4f56-b63f-89ae28d4ae4c'}"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response.json()['result']['_links']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>short_term_parking</th>\n",
       "      <th>car_park_type</th>\n",
       "      <th>y_coord</th>\n",
       "      <th>x_coord</th>\n",
       "      <th>free_parking</th>\n",
       "      <th>gantry_height</th>\n",
       "      <th>car_park_basement</th>\n",
       "      <th>night_parking</th>\n",
       "      <th>address</th>\n",
       "      <th>car_park_decks</th>\n",
       "      <th>_id</th>\n",
       "      <th>car_park_no</th>\n",
       "      <th>type_of_parking_system</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>WHOLE DAY</td>\n",
       "      <td>BASEMENT CAR PARK</td>\n",
       "      <td>31490.4942</td>\n",
       "      <td>30314.7936</td>\n",
       "      <td>NO</td>\n",
       "      <td>1.80</td>\n",
       "      <td>Y</td>\n",
       "      <td>YES</td>\n",
       "      <td>BLK 270/271 ALBERT CENTRE BASEMENT CAR PARK</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>ACB</td>\n",
       "      <td>ELECTRONIC PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WHOLE DAY</td>\n",
       "      <td>MULTI-STOREY CAR PARK</td>\n",
       "      <td>33695.5198</td>\n",
       "      <td>33758.4143</td>\n",
       "      <td>SUN &amp; PH FR 7AM-10.30PM</td>\n",
       "      <td>2.10</td>\n",
       "      <td>N</td>\n",
       "      <td>YES</td>\n",
       "      <td>BLK 98A ALJUNIED CRESCENT</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>ACM</td>\n",
       "      <td>ELECTRONIC PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WHOLE DAY</td>\n",
       "      <td>SURFACE CAR PARK</td>\n",
       "      <td>34500.3599</td>\n",
       "      <td>29257.7203</td>\n",
       "      <td>SUN &amp; PH FR 7AM-10.30PM</td>\n",
       "      <td>0.00</td>\n",
       "      <td>N</td>\n",
       "      <td>YES</td>\n",
       "      <td>BLK 101 JALAN DUSUN</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>AH1</td>\n",
       "      <td>ELECTRONIC PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7AM-7PM</td>\n",
       "      <td>SURFACE CAR PARK</td>\n",
       "      <td>39012.6664</td>\n",
       "      <td>28185.4359</td>\n",
       "      <td>NO</td>\n",
       "      <td>0.00</td>\n",
       "      <td>N</td>\n",
       "      <td>NO</td>\n",
       "      <td>BLOCK 253 ANG MO KIO STREET 21</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>AK19</td>\n",
       "      <td>COUPON PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NO</td>\n",
       "      <td>SURFACE CAR PARK</td>\n",
       "      <td>38684.1754</td>\n",
       "      <td>29482.0290</td>\n",
       "      <td>NO</td>\n",
       "      <td>0.00</td>\n",
       "      <td>N</td>\n",
       "      <td>NO</td>\n",
       "      <td>BLK 302/348 ANG MO KIO ST 31</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>AK31</td>\n",
       "      <td>COUPON PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2179</th>\n",
       "      <td>WHOLE DAY</td>\n",
       "      <td>MULTI-STOREY CAR PARK</td>\n",
       "      <td>45576.0125</td>\n",
       "      <td>29850.1522</td>\n",
       "      <td>SUN &amp; PH FR 7AM-10.30PM</td>\n",
       "      <td>2.15</td>\n",
       "      <td>N</td>\n",
       "      <td>YES</td>\n",
       "      <td>BLK 461 YISHUN AVENUE 6</td>\n",
       "      <td>10</td>\n",
       "      <td>2180</td>\n",
       "      <td>Y77M</td>\n",
       "      <td>ELECTRONIC PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2180</th>\n",
       "      <td>WHOLE DAY</td>\n",
       "      <td>MULTI-STOREY CAR PARK</td>\n",
       "      <td>45166.4820</td>\n",
       "      <td>30057.2209</td>\n",
       "      <td>SUN &amp; PH FR 7AM-10.30PM</td>\n",
       "      <td>2.15</td>\n",
       "      <td>N</td>\n",
       "      <td>YES</td>\n",
       "      <td>BLK 468 YISHUN ST 43</td>\n",
       "      <td>4</td>\n",
       "      <td>2181</td>\n",
       "      <td>Y78M</td>\n",
       "      <td>ELECTRONIC PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2181</th>\n",
       "      <td>WHOLE DAY</td>\n",
       "      <td>SURFACE CAR PARK</td>\n",
       "      <td>45686.2734</td>\n",
       "      <td>27772.9219</td>\n",
       "      <td>SUN &amp; PH FR 7AM-10.30PM</td>\n",
       "      <td>4.50</td>\n",
       "      <td>N</td>\n",
       "      <td>YES</td>\n",
       "      <td>BLK 731/746 YISHUN STREET 71,72/AVENUE 5</td>\n",
       "      <td>0</td>\n",
       "      <td>2182</td>\n",
       "      <td>Y8</td>\n",
       "      <td>ELECTRONIC PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2182</th>\n",
       "      <td>WHOLE DAY</td>\n",
       "      <td>MULTI-STOREY CAR PARK</td>\n",
       "      <td>45679.7181</td>\n",
       "      <td>29935.5818</td>\n",
       "      <td>SUN &amp; PH FR 7AM-10.30PM</td>\n",
       "      <td>2.15</td>\n",
       "      <td>N</td>\n",
       "      <td>YES</td>\n",
       "      <td>BLK 478 YISHUN ST 42</td>\n",
       "      <td>11</td>\n",
       "      <td>2183</td>\n",
       "      <td>Y82M</td>\n",
       "      <td>ELECTRONIC PARKING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2183</th>\n",
       "      <td>WHOLE DAY</td>\n",
       "      <td>SURFACE CAR PARK</td>\n",
       "      <td>45507.8047</td>\n",
       "      <td>28077.2305</td>\n",
       "      <td>SUN &amp; PH FR 7AM-10.30PM</td>\n",
       "      <td>4.50</td>\n",
       "      <td>N</td>\n",
       "      <td>YES</td>\n",
       "      <td>BLK 747/752 YISHUN STREET 72</td>\n",
       "      <td>0</td>\n",
       "      <td>2184</td>\n",
       "      <td>Y9</td>\n",
       "      <td>ELECTRONIC PARKING</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2184 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     short_term_parking          car_park_type     y_coord     x_coord  \\\n",
       "0             WHOLE DAY      BASEMENT CAR PARK  31490.4942  30314.7936   \n",
       "1             WHOLE DAY  MULTI-STOREY CAR PARK  33695.5198  33758.4143   \n",
       "2             WHOLE DAY       SURFACE CAR PARK  34500.3599  29257.7203   \n",
       "3               7AM-7PM       SURFACE CAR PARK  39012.6664  28185.4359   \n",
       "4                    NO       SURFACE CAR PARK  38684.1754  29482.0290   \n",
       "...                 ...                    ...         ...         ...   \n",
       "2179          WHOLE DAY  MULTI-STOREY CAR PARK  45576.0125  29850.1522   \n",
       "2180          WHOLE DAY  MULTI-STOREY CAR PARK  45166.4820  30057.2209   \n",
       "2181          WHOLE DAY       SURFACE CAR PARK  45686.2734  27772.9219   \n",
       "2182          WHOLE DAY  MULTI-STOREY CAR PARK  45679.7181  29935.5818   \n",
       "2183          WHOLE DAY       SURFACE CAR PARK  45507.8047  28077.2305   \n",
       "\n",
       "                 free_parking gantry_height car_park_basement night_parking  \\\n",
       "0                          NO          1.80                 Y           YES   \n",
       "1     SUN & PH FR 7AM-10.30PM          2.10                 N           YES   \n",
       "2     SUN & PH FR 7AM-10.30PM          0.00                 N           YES   \n",
       "3                          NO          0.00                 N            NO   \n",
       "4                          NO          0.00                 N            NO   \n",
       "...                       ...           ...               ...           ...   \n",
       "2179  SUN & PH FR 7AM-10.30PM          2.15                 N           YES   \n",
       "2180  SUN & PH FR 7AM-10.30PM          2.15                 N           YES   \n",
       "2181  SUN & PH FR 7AM-10.30PM          4.50                 N           YES   \n",
       "2182  SUN & PH FR 7AM-10.30PM          2.15                 N           YES   \n",
       "2183  SUN & PH FR 7AM-10.30PM          4.50                 N           YES   \n",
       "\n",
       "                                          address car_park_decks   _id  \\\n",
       "0     BLK 270/271 ALBERT CENTRE BASEMENT CAR PARK              1     1   \n",
       "1                       BLK 98A ALJUNIED CRESCENT              5     2   \n",
       "2                             BLK 101 JALAN DUSUN              0     3   \n",
       "3                  BLOCK 253 ANG MO KIO STREET 21              0     4   \n",
       "4                    BLK 302/348 ANG MO KIO ST 31              0     5   \n",
       "...                                           ...            ...   ...   \n",
       "2179                      BLK 461 YISHUN AVENUE 6             10  2180   \n",
       "2180                         BLK 468 YISHUN ST 43              4  2181   \n",
       "2181     BLK 731/746 YISHUN STREET 71,72/AVENUE 5              0  2182   \n",
       "2182                         BLK 478 YISHUN ST 42             11  2183   \n",
       "2183                 BLK 747/752 YISHUN STREET 72              0  2184   \n",
       "\n",
       "     car_park_no type_of_parking_system  \n",
       "0            ACB     ELECTRONIC PARKING  \n",
       "1            ACM     ELECTRONIC PARKING  \n",
       "2            AH1     ELECTRONIC PARKING  \n",
       "3           AK19         COUPON PARKING  \n",
       "4           AK31         COUPON PARKING  \n",
       "...          ...                    ...  \n",
       "2179        Y77M     ELECTRONIC PARKING  \n",
       "2180        Y78M     ELECTRONIC PARKING  \n",
       "2181          Y8     ELECTRONIC PARKING  \n",
       "2182        Y82M     ELECTRONIC PARKING  \n",
       "2183          Y9     ELECTRONIC PARKING  \n",
       "\n",
       "[2184 rows x 13 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "url_base = 'https://data.gov.sg/api/action/datastore_search'\n",
    "\n",
    "df_carparks = []\n",
    "\n",
    "for i in range(0,2200,100):\n",
    "    # Create a dictionary of the parameters\n",
    "    parameters = {\n",
    "        'offset' : i,\n",
    "        'resource_id' : '139a3035-e624-4f56-b63f-89ae28d4ae4c'\n",
    "    }\n",
    "\n",
    "    # Send the request via .get() method\n",
    "    response = requests.get(url_base, params=parameters)\n",
    "\n",
    "    # Check if the request is successful\n",
    "    if response.status_code == 200:\n",
    "        response_dict = response.json()\n",
    "        # Check if there is at least 1 record\n",
    "        if len(response_dict['result']['records']) > 0:\n",
    "            df_temp = pd.json_normalize(response_dict['result']['records'])\n",
    "            df_carparks.append(df_temp)\n",
    "    else:\n",
    "        print('The request failed and returned status code:' + str(response.status_code))\n",
    "\n",
    "df_carparks = pd.concat(df_carparks, axis=0, ignore_index=True)\n",
    "df_carparks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2184"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_carparks)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "name": "DC Bootcamp Week 17.ipynb",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}

# data[].attributes.body
Week 10 Assignment

# data[].attributes.body
Need someone to enlighten on how the codes contributed by Jia Wei works!

# data[].attributes.attachment_link.title
DC_Bootcamp_Week_10_Questions-Poh Ban Hong dtd 21 Jun 2022

# data[].attributes.body
Could not run Q2b after numerous attempts.

# data[].attributes.body
Completed

# data[].attributes.body
gave up on Q2b. The API code took forever to run - 'https://developers.onemap.sg/commonapi/search'

# data[].attributes.attachment_link.title
DC_Bootcamp_Week_10_Questions - TPH - Done

# data[].attributes.body
Please assist to review my code and give your comments freely. Thanks.