Segmenting and Clustering Neighborhoods in Toronto - first notebook.ipynb

{
    "nbformat_minor": 1, 
    "cells": [
        {
            "source": "## Segmenting and Clustering Neighborhoods in Toronto", 
            "cell_type": "markdown", 
            "metadata": {
                "collapsed": true
            }
        }, 
        {
            "execution_count": 1, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "import pandas as pd"
        }, 
        {
            "source": "First task is to parse data from Wikipedia:", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 2, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 2, 
                    "metadata": {}, 
                    "data": {
                        "text/html": "\n        <iframe\n            width=\"800\"\n            height=\"350\"\n            src=\"https://en.wikipedia.org/wiki/List_of_postal_codes_of_Canada:_M\"\n            frameborder=\"0\"\n            allowfullscreen\n        ></iframe>\n        ", 
                        "text/plain": "<IPython.lib.display.IFrame at 0x7f34bb93cac8>"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "from IPython.display import IFrame\nurl = \"https://en.wikipedia.org/wiki/List_of_postal_codes_of_Canada:_M\"\nIFrame(url, width=800, height=350)"
        }, 
        {
            "source": "Data can be parsed using `BeautifulSoap`, but it's more straightforward just to use `Pandas` and its function `read_html`:", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 3, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 3, 
                    "metadata": {}, 
                    "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>PostalCode</th>\n      <th>Borough</th>\n      <th>Neighborhood</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>M1A</td>\n      <td>Not assigned</td>\n      <td>Not assigned</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>M2A</td>\n      <td>Not assigned</td>\n      <td>Not assigned</td>\n    </tr>\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>Harbourfront</td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "  PostalCode           Borough      Neighborhood\n0        M1A      Not assigned      Not assigned\n1        M2A      Not assigned      Not assigned\n2        M3A        North York         Parkwoods\n3        M4A        North York  Victoria Village\n4        M5A  Downtown Toronto      Harbourfront"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "data, = pd.read_html(url, match=\"Postcode\", skiprows=1)\ndata.columns = [\"PostalCode\", \"Borough\", \"Neighborhood\"]\ndata.head()"
        }, 
        {
            "source": "Only process the cells that have an assigned borough. Ignore cells with a borough that is \"Not assigned\".", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 4, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 4, 
                    "metadata": {}, 
                    "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>PostalCode</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>Harbourfront</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>M5A</td>\n      <td>Downtown Toronto</td>\n      <td>Regent Park</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>M6A</td>\n      <td>North York</td>\n      <td>Lawrence Heights</td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "  PostalCode           Borough      Neighborhood\n2        M3A        North York         Parkwoods\n3        M4A        North York  Victoria Village\n4        M5A  Downtown Toronto      Harbourfront\n5        M5A  Downtown Toronto       Regent Park\n6        M6A        North York  Lawrence Heights"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "data = data[data[\"Borough\"] != \"Not assigned\"]\ndata.head()"
        }, 
        {
            "source": "More than one neighborhood can exist in one postal code area. For example, in the table on the Wikipedia page, you will notice that M5A is listed twice and has two neighborhoods: Harbourfront and Regent Park. These two rows will be combined into one row with the neighborhoods separated with a comma.\n\nSolution is to group data by `PostalCode` and aggregate columns. For borough, it's sufficient to pick first item from the resulting series and for neighbourhood, items are joined together using `\", \".join(s)`:", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 5, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 5, 
                    "metadata": {}, 
                    "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>Borough</th>\n      <th>Neighborhood</th>\n    </tr>\n    <tr>\n      <th>PostalCode</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>M1B</th>\n      <td>Scarborough</td>\n      <td>Rouge, Malvern</td>\n    </tr>\n    <tr>\n      <th>M1C</th>\n      <td>Scarborough</td>\n      <td>Highland Creek, Rouge Hill, Port Union</td>\n    </tr>\n    <tr>\n      <th>M1E</th>\n      <td>Scarborough</td>\n      <td>Guildwood, Morningside, West Hill</td>\n    </tr>\n    <tr>\n      <th>M1G</th>\n      <td>Scarborough</td>\n      <td>Woburn</td>\n    </tr>\n    <tr>\n      <th>M1H</th>\n      <td>Scarborough</td>\n      <td>Cedarbrae</td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "                Borough                            Neighborhood\nPostalCode                                                     \nM1B         Scarborough                          Rouge, Malvern\nM1C         Scarborough  Highland Creek, Rouge Hill, Port Union\nM1E         Scarborough       Guildwood, Morningside, West Hill\nM1G         Scarborough                                  Woburn\nM1H         Scarborough                               Cedarbrae"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "borough_func = lambda s: s.iloc[0]\nneighborhood_func = lambda s: \", \".join(s)\nagg_funcs = {\"Borough\": borough_func, \"Neighborhood\": neighborhood_func}\ndata_temp = data.groupby(by=\"PostalCode\").aggregate(agg_funcs)\ndata_temp.head()"
        }, 
        {
            "source": "Some postprocessing is needed; reset the index and add columns back to right order:", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 6, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 6, 
                    "metadata": {}, 
                    "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>PostalCode</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": "  PostalCode      Borough                            Neighborhood\n0        M1B  Scarborough                          Rouge, Malvern\n1        M1C  Scarborough  Highland Creek, Rouge Hill, Port Union\n2        M1E  Scarborough       Guildwood, Morningside, West Hill\n3        M1G  Scarborough                                  Woburn\n4        M1H  Scarborough                               Cedarbrae"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "data = data_temp.reset_index()[data.columns]\ndata.head()"
        }, 
        {
            "source": "If a cell has a borough but a \"Not assigned\" neighborhood, then the neighborhood will be the same as the borough. So for the 9th cell in the table on the Wikipedia page, the value of the Borough and the Neighborhood columns will be Queen's Park.", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 7, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 7, 
                    "metadata": {}, 
                    "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>PostalCode</th>\n      <th>Borough</th>\n      <th>Neighborhood</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>85</th>\n      <td>M7A</td>\n      <td>Queen's Park</td>\n      <td>Not assigned</td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "   PostalCode       Borough  Neighborhood\n85        M7A  Queen's Park  Not assigned"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "data[data[\"Neighborhood\"] == \"Not assigned\"]"
        }, 
        {
            "source": "We can, for example, iterate through table and replace the values:", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 8, 
            "cell_type": "code", 
            "metadata": {
                "scrolled": false
            }, 
            "outputs": [
                {
                    "output_type": "stream", 
                    "name": "stdout", 
                    "text": "Replace \"Not assigned\" => Queen's Park in row 85\n"
                }
            ], 
            "source": "for (j, row) in data.iterrows():\n    if row[\"Neighborhood\"] == \"Not assigned\":\n        borough = row[\"Borough\"]\n        print(\"Replace \\\"Not assigned\\\" => %s in row %i\" % (borough, j))\n        row[\"Neighborhood\"] = borough"
        }, 
        {
            "source": "To check data, examine row 85, which should be the only changed one:", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 9, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 9, 
                    "metadata": {}, 
                    "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>PostalCode</th>\n      <th>Borough</th>\n      <th>Neighborhood</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>83</th>\n      <td>M6R</td>\n      <td>West Toronto</td>\n      <td>Parkdale, Roncesvalles</td>\n    </tr>\n    <tr>\n      <th>84</th>\n      <td>M6S</td>\n      <td>West Toronto</td>\n      <td>Runnymede, Swansea</td>\n    </tr>\n    <tr>\n      <th>85</th>\n      <td>M7A</td>\n      <td>Queen's Park</td>\n      <td>Queen's Park</td>\n    </tr>\n    <tr>\n      <th>86</th>\n      <td>M7R</td>\n      <td>Mississauga</td>\n      <td>Canada Post Gateway Processing Centre</td>\n    </tr>\n    <tr>\n      <th>87</th>\n      <td>M7Y</td>\n      <td>East Toronto</td>\n      <td>Business Reply Mail Processing Centre 969 Eastern</td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "   PostalCode       Borough                                       Neighborhood\n83        M6R  West Toronto                             Parkdale, Roncesvalles\n84        M6S  West Toronto                                 Runnymede, Swansea\n85        M7A  Queen's Park                                       Queen's Park\n86        M7R   Mississauga              Canada Post Gateway Processing Centre\n87        M7Y  East Toronto  Business Reply Mail Processing Centre 969 Eastern"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "data.iloc[83:88]"
        }, 
        {
            "source": "The size of the data is now:", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 10, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 10, 
                    "metadata": {}, 
                    "data": {
                        "text/plain": "(103, 3)"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "data.shape"
        }
    ], 
    "metadata": {
        "kernelspec": {
            "display_name": "Python 3.5", 
            "name": "python3", 
            "language": "python"
        }, 
        "language_info": {
            "mimetype": "text/x-python", 
            "nbconvert_exporter": "python", 
            "version": "3.5.5", 
            "name": "python", 
            "file_extension": ".py", 
            "pygments_lexer": "ipython3", 
            "codemirror_mode": {
                "version": 3, 
                "name": "ipython"
            }
        }
    }, 
    "nbformat": 4
}