Segmentation and Clustering in Toronto 2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Assignment IBM\n",
    "Week 3 Assignment of Applied Data Science Capstone\n",
    "\n",
    "Task 2: Add the latitude and longitude coordinates to the dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Postal Code</th>\n",
       "      <th>Borough</th>\n",
       "      <th>Neighborhood</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>M1B</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Rouge, Malvern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>M1C</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Highland Creek, Rouge Hill, Port Union</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M1E</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Guildwood, Morningside, West Hill</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>M1G</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Woburn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M1H</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Cedarbrae</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Postal Code      Borough                            Neighborhood\n",
       "0         M1B  Scarborough                          Rouge, Malvern\n",
       "1         M1C  Scarborough  Highland Creek, Rouge Hill, Port Union\n",
       "2         M1E  Scarborough       Guildwood, Morningside, West Hill\n",
       "3         M1G  Scarborough                                  Woburn\n",
       "4         M1H  Scarborough                               Cedarbrae"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from bs4 import BeautifulSoup\n",
    "import requests\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "# download url data from internet\n",
    "url = \"https://en.wikipedia.org/wiki/List_of_postal_codes_of_Canada:_M\"\n",
    "source = requests.get(url).text\n",
    "Canada_data = BeautifulSoup(source, 'lxml')\n",
    "\n",
    "# creat a new Dataframe\n",
    "column_names = ['Postalcode','Borough','Neighborhood']\n",
    "toronto = pd.DataFrame(columns = column_names)\n",
    "content = Canada_data.find('div', class_='mw-parser-output')\n",
    "table = content.table.tbody\n",
    "postcode = 0\n",
    "borough = 0\n",
    "neighborhood = 0\n",
    "\n",
    "for tr in table.find_all('tr'):\n",
    "    i = 0\n",
    "    for td in tr.find_all('td'):\n",
    "        if i == 0:\n",
    "            postcode = td.text\n",
    "            i = i + 1\n",
    "        elif i == 1:\n",
    "            borough = td.text\n",
    "            i = i + 1\n",
    "        elif i == 2: \n",
    "            neighborhood = td.text.strip('\\n').replace(']','')\n",
    "    toronto = toronto.append({'Postalcode': postcode,'Borough': borough,'Neighborhood': neighborhood},ignore_index=True)\n",
    "\n",
    "# clean dataframe \n",
    "toronto = toronto[toronto.Borough!='Not assigned']\n",
    "toronto = toronto[toronto.Borough!= 0]\n",
    "toronto.reset_index(drop = True, inplace = True)\n",
    "i = 0\n",
    "for i in range(0,toronto.shape[0]):\n",
    "    if toronto.iloc[i][2] == 'Not assigned':\n",
    "        toronto.iloc[i][2] = toronto.iloc[i][1]\n",
    "        i = i+1\n",
    "                                 \n",
    "df = toronto.groupby(['Postalcode','Borough'])['Neighborhood'].apply(', '.join).reset_index()\n",
    "\n",
    "def neighborhood_list(grouped):    \n",
    "    return ', '.join(sorted(grouped['Neighborhood'].tolist()))\n",
    "                    \n",
    "grp = df.groupby(['Postalcode', 'Borough'])\n",
    "df2 = grp.apply(neighborhood_list).reset_index(name='Neighborhood')\n",
    "df2.rename(columns={'Postalcode':'Postal Code'}, inplace=True)\n",
    "df2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "Retrieve postcode coordinates.¶\n",
    "Now that we have built a dataframe of the postal code of each neighborhood along with the borough name and neighborhood name, in order to utilize the Foursquare location data, we need to get the latitude and the longitude coordinates of each neighborhood. We are supposed to use the Google Maps Geocoding API to get the latitude and the longitude coordinates of each neighborhood. However, this is a paid service API: http://geoawesomeness.com/developers-up-in-arms-over-google-maps-api-insane-price-hike/, so we will use the Geocoder Python package instead: https://geocoder.readthedocs.io/index.html.\n",
    "\n",
    "The problem with this Package is you have to be persistent sometimes in order to get the geographical coordinates of a given postal code. So you can make a call to get the latitude and longitude coordinates of a given postal code and the result would be None, and then make the call again and you would get the coordinates. So, in order to make sure that you get the coordinates for all of our neighborhoods, you can run a while loop for each postal code.\n",
    "\n",
    "For this task, we just use a prepared csv to retrieve the coordinates.\n",
    "\n",
    "Extract csv with Toronto geographical coordinates to dataframe.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Postal Code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>M1B</th>\n",
       "      <td>43.806686</td>\n",
       "      <td>-79.194353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M1C</th>\n",
       "      <td>43.784535</td>\n",
       "      <td>-79.160497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M1E</th>\n",
       "      <td>43.763573</td>\n",
       "      <td>-79.188711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M1G</th>\n",
       "      <td>43.770992</td>\n",
       "      <td>-79.216917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M1H</th>\n",
       "      <td>43.773136</td>\n",
       "      <td>-79.239476</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Latitude  Longitude\n",
       "Postal Code                      \n",
       "M1B          43.806686 -79.194353\n",
       "M1C          43.784535 -79.160497\n",
       "M1E          43.763573 -79.188711\n",
       "M1G          43.770992 -79.216917\n",
       "M1H          43.773136 -79.239476"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toronto_geocsv = 'https://cocl.us/Geospatial_data'\n",
    "!wget -q -O 'toronto_m.geospatial_data.csv' toronto_geocsv\n",
    "geocsv_data = pd.read_csv(toronto_geocsv).set_index(\"Postal Code\")\n",
    "geocsv_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now combine the two"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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",
       "      <th>Borough</th>\n",
       "      <th>Neighborhood</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",
       "      <td>Scarborough</td>\n",
       "      <td>Rouge, Malvern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>M1C</td>\n",
       "      <td>43.784535</td>\n",
       "      <td>-79.160497</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Highland Creek, Rouge Hill, Port Union</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M1E</td>\n",
       "      <td>43.763573</td>\n",
       "      <td>-79.188711</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Guildwood, Morningside, West Hill</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>M1G</td>\n",
       "      <td>43.770992</td>\n",
       "      <td>-79.216917</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Woburn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M1H</td>\n",
       "      <td>43.773136</td>\n",
       "      <td>-79.239476</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Cedarbrae</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Postal Code   Latitude  Longitude      Borough  \\\n",
       "0         M1B  43.806686 -79.194353  Scarborough   \n",
       "1         M1C  43.784535 -79.160497  Scarborough   \n",
       "2         M1E  43.763573 -79.188711  Scarborough   \n",
       "3         M1G  43.770992 -79.216917  Scarborough   \n",
       "4         M1H  43.773136 -79.239476  Scarborough   \n",
       "\n",
       "                             Neighborhood  \n",
       "0                          Rouge, Malvern  \n",
       "1  Highland Creek, Rouge Hill, Port Union  \n",
       "2       Guildwood, Morningside, West Hill  \n",
       "3                                  Woburn  \n",
       "4                               Cedarbrae  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.merge(geocsv_data, df2, on='Postal Code')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>M1B</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Rouge, Malvern</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>Scarborough</td>\n",
       "      <td>Highland Creek, Rouge Hill, Port Union</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>Scarborough</td>\n",
       "      <td>Guildwood, Morningside, West Hill</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>Scarborough</td>\n",
       "      <td>Woburn</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>Scarborough</td>\n",
       "      <td>Cedarbrae</td>\n",
       "      <td>43.773136</td>\n",
       "      <td>-79.239476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>M1J</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Scarborough Village</td>\n",
       "      <td>43.744734</td>\n",
       "      <td>-79.239476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>M1K</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>East Birchmount Park, Ionview, Kennedy Park</td>\n",
       "      <td>43.727929</td>\n",
       "      <td>-79.262029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>M1L</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Clairlea, Golden Mile, Oakridge</td>\n",
       "      <td>43.711112</td>\n",
       "      <td>-79.284577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>M1M</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Cliffcrest, Cliffside, Scarborough Village West</td>\n",
       "      <td>43.716316</td>\n",
       "      <td>-79.239476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>M1N</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Birch Cliff, Cliffside West</td>\n",
       "      <td>43.692657</td>\n",
       "      <td>-79.264848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>M1P</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Dorset Park, Scarborough Town Centre, Wexford ...</td>\n",
       "      <td>43.757410</td>\n",
       "      <td>-79.273304</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Postal Code      Borough  \\\n",
       "0          M1B  Scarborough   \n",
       "1          M1C  Scarborough   \n",
       "2          M1E  Scarborough   \n",
       "3          M1G  Scarborough   \n",
       "4          M1H  Scarborough   \n",
       "5          M1J  Scarborough   \n",
       "6          M1K  Scarborough   \n",
       "7          M1L  Scarborough   \n",
       "8          M1M  Scarborough   \n",
       "9          M1N  Scarborough   \n",
       "10         M1P  Scarborough   \n",
       "\n",
       "                                         Neighborhood   Latitude  Longitude  \n",
       "0                                      Rouge, Malvern  43.806686 -79.194353  \n",
       "1              Highland Creek, Rouge Hill, Port Union  43.784535 -79.160497  \n",
       "2                   Guildwood, Morningside, West Hill  43.763573 -79.188711  \n",
       "3                                              Woburn  43.770992 -79.216917  \n",
       "4                                           Cedarbrae  43.773136 -79.239476  \n",
       "5                                 Scarborough Village  43.744734 -79.239476  \n",
       "6         East Birchmount Park, Ionview, Kennedy Park  43.727929 -79.262029  \n",
       "7                     Clairlea, Golden Mile, Oakridge  43.711112 -79.284577  \n",
       "8     Cliffcrest, Cliffside, Scarborough Village West  43.716316 -79.239476  \n",
       "9                         Birch Cliff, Cliffside West  43.692657 -79.264848  \n",
       "10  Dorset Park, Scarborough Town Centre, Wexford ...  43.757410 -79.273304  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df[['Postal Code', 'Borough', 'Neighborhood', 'Latitude', 'Longitude']]\n",
    "df.head(11)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('Toronto.TASK_II_df.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(103, 5)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.6",
   "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}