Segmenting and Clustering Neighborhoods in Toronto - Add geodata.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Segmenting and Clustering Neighborhoods in Toronto - Add geodata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Scrapping Wikipedia page with beautifulsoup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Prepare the dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Postal Code</th>\n",
       "      <th>Borough</th>\n",
       "      <th>Neighborhood</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M3A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Parkwoods</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>M4A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Victoria Village</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M5A</td>\n",
       "      <td>Downtown Toronto</td>\n",
       "      <td>Regent Park,Harbourfront</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>M6A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Lawrence Manor,Lawrence Heights</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>M7A</td>\n",
       "      <td>Downtown Toronto</td>\n",
       "      <td>Queen's Park,Ontario Provincial Government</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Postal Code           Borough                                Neighborhood\n",
       "2         M3A        North York                                   Parkwoods\n",
       "3         M4A        North York                            Victoria Village\n",
       "4         M5A  Downtown Toronto                    Regent Park,Harbourfront\n",
       "5         M6A        North York             Lawrence Manor,Lawrence Heights\n",
       "6         M7A  Downtown Toronto  Queen's Park,Ontario Provincial Government"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from bs4 import BeautifulSoup\n",
    "import urllib.request\n",
    "import re\n",
    "POSTAL_CODES_URL = \"https://en.wikipedia.org/wiki/List_of_postal_codes_of_Canada:_M\"\n",
    "contents = urllib.request.urlopen(POSTAL_CODES_URL).read()\n",
    "soup = BeautifulSoup(contents, 'html.parser')\n",
    "table = soup.find(\"table\",{\"class\":\"wikitable\"})\n",
    "df = pd.read_html(str(table))[0]\n",
    "\n",
    "# Process dataframe\n",
    "df = df[df.Borough != 'Not assigned']\n",
    "df['Neighborhood'] = df['Neighborhood'].apply(lambda x: ','.join(y.strip() for y in x.split('/')))\n",
    "df.rename(columns={\"Postal code\": \"Postal Code\"}, inplace=True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the geographical coordinates from the csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Postal Code</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>M1B</td>\n",
       "      <td>43.806686</td>\n",
       "      <td>-79.194353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>M1C</td>\n",
       "      <td>43.784535</td>\n",
       "      <td>-79.160497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M1E</td>\n",
       "      <td>43.763573</td>\n",
       "      <td>-79.188711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>M1G</td>\n",
       "      <td>43.770992</td>\n",
       "      <td>-79.216917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M1H</td>\n",
       "      <td>43.773136</td>\n",
       "      <td>-79.239476</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Postal Code   Latitude  Longitude\n",
       "0         M1B  43.806686 -79.194353\n",
       "1         M1C  43.784535 -79.160497\n",
       "2         M1E  43.763573 -79.188711\n",
       "3         M1G  43.770992 -79.216917\n",
       "4         M1H  43.773136 -79.239476"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geo_df = pd.read_csv(\"Geospatial_Coordinates.csv\") \n",
    "geo_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge this dataframes by the column `Postal Code`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Postal Code</th>\n",
       "      <th>Borough</th>\n",
       "      <th>Neighborhood</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>M3A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Parkwoods</td>\n",
       "      <td>43.753259</td>\n",
       "      <td>-79.329656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>M4A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Victoria Village</td>\n",
       "      <td>43.725882</td>\n",
       "      <td>-79.315572</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M5A</td>\n",
       "      <td>Downtown Toronto</td>\n",
       "      <td>Regent Park,Harbourfront</td>\n",
       "      <td>43.654260</td>\n",
       "      <td>-79.360636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>M6A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Lawrence Manor,Lawrence Heights</td>\n",
       "      <td>43.718518</td>\n",
       "      <td>-79.464763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M7A</td>\n",
       "      <td>Downtown Toronto</td>\n",
       "      <td>Queen's Park,Ontario Provincial Government</td>\n",
       "      <td>43.662301</td>\n",
       "      <td>-79.389494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>M8X</td>\n",
       "      <td>Etobicoke</td>\n",
       "      <td>The Kingsway,Montgomery Road,Old Mill North</td>\n",
       "      <td>43.653654</td>\n",
       "      <td>-79.506944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>M4Y</td>\n",
       "      <td>Downtown Toronto</td>\n",
       "      <td>Church and Wellesley</td>\n",
       "      <td>43.665860</td>\n",
       "      <td>-79.383160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>M7Y</td>\n",
       "      <td>East Toronto</td>\n",
       "      <td>Business reply mail Processing CentrE</td>\n",
       "      <td>43.662744</td>\n",
       "      <td>-79.321558</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>M8Y</td>\n",
       "      <td>Etobicoke</td>\n",
       "      <td>Old Mill South,King's Mill Park,Sunnylea,Humbe...</td>\n",
       "      <td>43.636258</td>\n",
       "      <td>-79.498509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>M8Z</td>\n",
       "      <td>Etobicoke</td>\n",
       "      <td>Mimico NW,The Queensway West,South of Bloor,Ki...</td>\n",
       "      <td>43.628841</td>\n",
       "      <td>-79.520999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>103 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Postal Code           Borough  \\\n",
       "0           M3A        North York   \n",
       "1           M4A        North York   \n",
       "2           M5A  Downtown Toronto   \n",
       "3           M6A        North York   \n",
       "4           M7A  Downtown Toronto   \n",
       "..          ...               ...   \n",
       "98          M8X         Etobicoke   \n",
       "99          M4Y  Downtown Toronto   \n",
       "100         M7Y      East Toronto   \n",
       "101         M8Y         Etobicoke   \n",
       "102         M8Z         Etobicoke   \n",
       "\n",
       "                                          Neighborhood   Latitude  Longitude  \n",
       "0                                            Parkwoods  43.753259 -79.329656  \n",
       "1                                     Victoria Village  43.725882 -79.315572  \n",
       "2                             Regent Park,Harbourfront  43.654260 -79.360636  \n",
       "3                      Lawrence Manor,Lawrence Heights  43.718518 -79.464763  \n",
       "4           Queen's Park,Ontario Provincial Government  43.662301 -79.389494  \n",
       "..                                                 ...        ...        ...  \n",
       "98         The Kingsway,Montgomery Road,Old Mill North  43.653654 -79.506944  \n",
       "99                                Church and Wellesley  43.665860 -79.383160  \n",
       "100              Business reply mail Processing CentrE  43.662744 -79.321558  \n",
       "101  Old Mill South,King's Mill Park,Sunnylea,Humbe...  43.636258 -79.498509  \n",
       "102  Mimico NW,The Queensway West,South of Bloor,Ki...  43.628841 -79.520999  \n",
       "\n",
       "[103 rows x 5 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df = pd.merge(df, geo_df, on='Postal Code')\n",
    "result_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}