Toranto Exporer.ipynb

{
    "cells": [
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "   ## Toranto Neighbourhood Explorer"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "<p> Import the required pandas package\nand initialize the 2 url variable for required data.</p>"
        },
        {
            "cell_type": "code",
            "execution_count": 53,
            "metadata": {},
            "outputs": [],
            "source": "import pandas as pd\nurl='https://en.wikipedia.org/wiki/List_of_postal_codes_of_Canada:_M'\ngeo_coordinate_url='https://cocl.us/Geospatial_data'"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "<p> Read the html table presnt in wikipedia website, with link present in 'url' variable and display the top 5 rows </p>"
        },
        {
            "cell_type": "code",
            "execution_count": 54,
            "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>M1A</td>\n      <td>Not assigned</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>M2A</td>\n      <td>Not assigned</td>\n      <td>NaN</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>Regent Park / Harbourfront</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
                        "text/plain": "  Postal code           Borough                Neighborhood\n0         M1A      Not assigned                         NaN\n1         M2A      Not assigned                         NaN\n2         M3A        North York                   Parkwoods\n3         M4A        North York            Victoria Village\n4         M5A  Downtown Toronto  Regent Park / Harbourfront"
                    },
                    "execution_count": 54,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "tables = pd.read_html(url)[0]\ntables.head()"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "<p> Filter the dataframe, with loc, primarily label based. Here it is filtering the Borough columns **other than** 'Not assigned' values.</p>"
        },
        {
            "cell_type": "code",
            "execution_count": 55,
            "metadata": {},
            "outputs": [],
            "source": "df=tables.loc[tables.Borough != 'Not assigned',:]"
        },
        {
            "cell_type": "code",
            "execution_count": 56,
            "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  </tbody>\n</table>\n</div>",
                        "text/plain": "  Postal code     Borough      Neighborhood\n2         M3A  North York         Parkwoods\n3         M4A  North York  Victoria Village"
                    },
                    "execution_count": 56,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "df.head(2)"
        },
        {
            "cell_type": "code",
            "execution_count": 57,
            "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>160</th>\n      <td>M8X</td>\n      <td>Etobicoke</td>\n      <td>The Kingsway / Montgomery Road / Old Mill North</td>\n    </tr>\n    <tr>\n      <th>165</th>\n      <td>M4Y</td>\n      <td>Downtown Toronto</td>\n      <td>Church and Wellesley</td>\n    </tr>\n    <tr>\n      <th>168</th>\n      <td>M7Y</td>\n      <td>East Toronto</td>\n      <td>Business reply mail Processing CentrE</td>\n    </tr>\n    <tr>\n      <th>169</th>\n      <td>M8Y</td>\n      <td>Etobicoke</td>\n      <td>Old Mill South / King's Mill Park / Sunnylea /...</td>\n    </tr>\n    <tr>\n      <th>178</th>\n      <td>M8Z</td>\n      <td>Etobicoke</td>\n      <td>Mimico NW / The Queensway West / South of Bloo...</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
                        "text/plain": "    Postal code           Borough  \\\n160         M8X         Etobicoke   \n165         M4Y  Downtown Toronto   \n168         M7Y      East Toronto   \n169         M8Y         Etobicoke   \n178         M8Z         Etobicoke   \n\n                                          Neighborhood  \n160    The Kingsway / Montgomery Road / Old Mill North  \n165                               Church and Wellesley  \n168              Business reply mail Processing CentrE  \n169  Old Mill South / King's Mill Park / Sunnylea /...  \n178  Mimico NW / The Queensway West / South of Bloo...  "
                    },
                    "execution_count": 57,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "df.groupby('Postal code')['Neighborhood'].apply(' / '.join).reset_index()\ndf.loc[df.Neighborhood=='Not assigned',:]\ndf.tail()"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "<p>Getting the dataframe shape by .shape attribute.</p>"
        },
        {
            "cell_type": "code",
            "execution_count": 58,
            "metadata": {},
            "outputs": [
                {
                    "data": {
                        "text/plain": "(103, 3)"
                    },
                    "execution_count": 58,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "df.shape"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "#### Read geographical coordinates of each postal code data from .csv file"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "<p> Read using pandas read_csv() funtion, store in geo_cor dataframe varaible and display the top 2 row.</p>"
        },
        {
            "cell_type": "code",
            "execution_count": 60,
            "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  </tbody>\n</table>\n</div>",
                        "text/plain": "  Postal Code   Latitude  Longitude\n0         M1B  43.806686 -79.194353\n1         M1C  43.784535 -79.160497"
                    },
                    "execution_count": 60,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "geo_cor=pd.read_csv(geo_coordinate_url)\ngeo_cor.head(2)"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "<p> Rename the Postal Code column, because the first dataframe have c in lower case and geographical coordinates have upper case. and made changes to the  existing dataframe by **inplace=True** </p>"
        },
        {
            "cell_type": "code",
            "execution_count": 61,
            "metadata": {},
            "outputs": [],
            "source": "geo_cor.rename(columns={'Postal Code': 'Postal code'}, inplace=True)"
        },
        {
            "cell_type": "code",
            "execution_count": 62,
            "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  </tbody>\n</table>\n</div>",
                        "text/plain": "  Postal code   Latitude  Longitude\n0         M1B  43.806686 -79.194353\n1         M1C  43.784535 -79.160497"
                    },
                    "execution_count": 62,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "geo_cor.head(2)"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "<p>For merge to work between 2 tables, we need to set the index of both the tables i.e works line primary key column.</p>"
        },
        {
            "cell_type": "code",
            "execution_count": 63,
            "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>Malvern / Rouge</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>Rouge Hill / Port Union / Highland Creek</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  \\\n0         M1B  43.806686 -79.194353  Scarborough   \n1         M1C  43.784535 -79.160497  Scarborough   \n2         M1E  43.763573 -79.188711  Scarborough   \n3         M1G  43.770992 -79.216917  Scarborough   \n4         M1H  43.773136 -79.239476  Scarborough   \n\n                               Neighborhood  \n0                           Malvern / Rouge  \n1  Rouge Hill / Port Union / Highland Creek  \n2       Guildwood / Morningside / West Hill  \n3                                    Woburn  \n4                                 Cedarbrae  "
                    },
                    "execution_count": 63,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "df.set_index(\"Postal code\")\ngeo_cor.set_index(\"Postal code\")\n\n#Merging both the tables in one\ntoronto_data=pd.merge(geo_cor, df)\ntoronto_data.head()"
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": ""
        }
    ],
    "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.9"
        }
    },
    "nbformat": 4,
    "nbformat_minor": 1
}