Assignment 2 - Segmenting and Clustering Neighborhoods in Toronto - Part 1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Applied Data Science Capstone Assignment 2 :Segmenting and Clustering Neighborhoods in Toronto\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### For this assignment, you will be required to explore and cluster the neighborhoods in Toronto.\n",
    "\n",
    "1. Start by creating a new Notebook for this assignment.\n",
    "2. Use the Notebook to build the code to scrape the following Wikipedia page, https://en.wikipedia.org/wiki/List_of_postal_codes_of_Canada:_M, in order to obtain the data that is in the table of postal codes and to transform the data into a pandas dataframe like the one shown below:\n",
    "\n",
    "![alt text](https://d3c33hcgiwev3.cloudfront.net/imageAssetProxy.v1/7JXaz3NNEeiMwApe4i-fLg_40e690ae0e927abda2d4bde7d94ed133_Screen-Shot-2018-06-18-at-7.17.57-PM.png?expiry=1555718400000&hmac=hY_G211KTmDqSbQCViiORKXZxJe2iUqeTTbg1hVMtY0)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Link for scrapping a table from a website: https://towardsdatascience.com/web-scraping-html-tables-with-python-c9baba21059"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Import necessary libraries\n",
    "\n",
    "import requests\n",
    "import lxml.html as lh\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "url='https://en.wikipedia.org/wiki/List_of_postal_codes_of_Canada:_M'\n",
    "\n",
    "#Create a handle, page, to handle the contents of the website\n",
    "page = requests.get(url)\n",
    "\n",
    "#Store the contents of the website under doc\n",
    "doc = lh.fromstring(page.content)\n",
    "\n",
    "#Parse data that are stored between <tr>..</tr> of HTML\n",
    "tr_elements = doc.xpath('//tr')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check the length of the first 12 rows\n",
    "[len(T) for T in tr_elements[:12]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This means that there are 3 columns per row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1:\"Postcode\"\n",
      "2:\"Borough\"\n",
      "3:\"Neighbourhood\n",
      "\"\n"
     ]
    }
   ],
   "source": [
    "# Parse the first row as our header\n",
    "tr_elements = doc.xpath('//tr')\n",
    "\n",
    "#Create empty list\n",
    "col=[]\n",
    "i=0\n",
    "\n",
    "#For each row, store each first element (header) and an empty list\n",
    "for t in tr_elements[0]:\n",
    "    i+=1\n",
    "    name=t.text_content()\n",
    "    print ('%d:\"%s\"'%(i,name))\n",
    "    col.append((name,[]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Creating Pandas DataFrame\n",
    "\n",
    "Each header is appended to a tuple along with an empty list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Since out first row is the header, data is stored on the second row onwards\n",
    "for j in range(1,len(tr_elements)):\n",
    "    #T is our j'th row\n",
    "    T=tr_elements[j]\n",
    "    \n",
    "    #If row is not of size 3, the //tr data is not from our table \n",
    "    if len(T)!=3:\n",
    "        break\n",
    "    \n",
    "    #i is the index of our column\n",
    "    i=0\n",
    "    \n",
    "    #Iterate through each element of the row\n",
    "    for t in T.iterchildren():\n",
    "        data=t.text_content() \n",
    "        #Check if row is empty\n",
    "        if i>0:\n",
    "        #Convert any numerical value to integers\n",
    "            try:\n",
    "                data=int(data)\n",
    "            except:\n",
    "                pass\n",
    "        #Append the data to the empty list of the i'th column\n",
    "        col[i][1].append(data)\n",
    "        #Increment i for the next column\n",
    "        i+=1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[288, 288, 288]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the length of each column. Ideally, they should all be the same\n",
    "[len(C) for (title,C) in col]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This shows that each of the 3 columns has exactly 288 rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Creating the pandas data frame "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Dict={title:column for (title,column) in col}\n",
    "df=pd.DataFrame(Dict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.To create the above dataframe:\n",
    "\n",
    "-  The dataframe will consist of three columns: PostalCode, Borough, and Neighborhood\n",
    "\n",
    "- Only process the cells that have an assigned borough. Ignore cells with a borough that is Not assigned.\n",
    "\n",
    "- 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 as shown in row 11 in the above table.\n",
    "\n",
    "- 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.\n",
    "- Clean your Notebook and add Markdown cells to explain your work and any assumptions you are making.\n",
    "- In the last cell of your notebook, use the .shape method to print the number of rows of your dataframe.\n",
    "\n",
    "4.Submit a link to your Notebook on your Github repository. (10 marks)\n",
    "\n",
    "- Note: There are different website scraping libraries and packages in Python. One of the most common packages is  BeautifulSoup. Here is the package's main documentation page: http://beautiful-soup-4.readthedocs.io/en/latest/\n",
    "\n",
    "- The package is so popular that there is a plethora of tutorials and examples of how to use it. Here is a very good Youtube video on how to use the BeautifulSoup package: https://www.youtube.com/watch?v=ng2o98k983k\n",
    "\n",
    "- Use the BeautifulSoup package or any other way you are comfortable with to transform the data in the table on the Wikipedia page into the above pandas dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>Neighbourhood</th>\n",
       "      <th>Postcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Not assigned</td>\n",
       "      <td>Not assigned\\n</td>\n",
       "      <td>M1A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Not assigned</td>\n",
       "      <td>Not assigned\\n</td>\n",
       "      <td>M2A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>North York</td>\n",
       "      <td>Parkwoods\\n</td>\n",
       "      <td>M3A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>North York</td>\n",
       "      <td>Victoria Village\\n</td>\n",
       "      <td>M4A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Downtown Toronto</td>\n",
       "      <td>Harbourfront\\n</td>\n",
       "      <td>M5A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Borough     Neighbourhood\\n Postcode\n",
       "0      Not assigned      Not assigned\\n      M1A\n",
       "1      Not assigned      Not assigned\\n      M2A\n",
       "2        North York         Parkwoods\\n      M3A\n",
       "3        North York  Victoria Village\\n      M4A\n",
       "4  Downtown Toronto      Harbourfront\\n      M5A"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Access the top 5 rows of the data frame \n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Rearranging and renaming the columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Postcode</th>\n",
       "      <th>Borough</th>\n",
       "      <th>Neighbourhood</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\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>M2A</td>\n",
       "      <td>Not assigned</td>\n",
       "      <td>Not assigned\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M3A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Parkwoods\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>M4A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Victoria Village\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M5A</td>\n",
       "      <td>Downtown Toronto</td>\n",
       "      <td>Harbourfront\\n</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Postcode           Borough       Neighbourhood\n",
       "0      M1A      Not assigned      Not assigned\\n\n",
       "1      M2A      Not assigned      Not assigned\\n\n",
       "2      M3A        North York         Parkwoods\\n\n",
       "3      M4A        North York  Victoria Village\\n\n",
       "4      M5A  Downtown Toronto      Harbourfront\\n"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns = ['Borough', 'Neighbourhood','Postcode']\n",
    "\n",
    "cols = df.columns.tolist()\n",
    "cols\n",
    "\n",
    "cols = cols[-1:] + cols[:-1]\n",
    "\n",
    "df = df[cols]\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Cleaning the messy string in the Borough column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Postcode</th>\n",
       "      <th>Borough</th>\n",
       "      <th>Neighbourhood</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": [
       "  Postcode           Borough      Neighbourhood\n",
       "0      M1A      Not assigned      Not assigned \n",
       "1      M2A      Not assigned      Not assigned \n",
       "2      M3A        North York         Parkwoods \n",
       "3      M4A        North York  Victoria Village \n",
       "4      M5A  Downtown Toronto      Harbourfront "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.replace('\\n',' ', regex=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Dropping all cells with a borough that is Not assigned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Postcode</th>\n",
       "      <th>Borough</th>\n",
       "      <th>Neighbourhood</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>M4A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Victoria Village</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M5A</td>\n",
       "      <td>Downtown Toronto</td>\n",
       "      <td>Harbourfront</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>M5A</td>\n",
       "      <td>Downtown Toronto</td>\n",
       "      <td>Regent Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M6A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Lawrence Heights</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>M6A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Lawrence Manor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>M7A</td>\n",
       "      <td>Queen's Park</td>\n",
       "      <td>Not assigned</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>M9A</td>\n",
       "      <td>Etobicoke</td>\n",
       "      <td>Islington Avenue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>M1B</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Rouge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>M1B</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Malvern</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Postcode           Borough      Neighbourhood\n",
       "0      M3A        North York         Parkwoods \n",
       "1      M4A        North York  Victoria Village \n",
       "2      M5A  Downtown Toronto      Harbourfront \n",
       "3      M5A  Downtown Toronto       Regent Park \n",
       "4      M6A        North York  Lawrence Heights \n",
       "5      M6A        North York    Lawrence Manor \n",
       "6      M7A      Queen's Park      Not assigned \n",
       "7      M9A         Etobicoke  Islington Avenue \n",
       "8      M1B       Scarborough             Rouge \n",
       "9      M1B       Scarborough           Malvern "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.drop(df.index[df['Borough'] == 'Not assigned'], inplace = True)\n",
    "\n",
    "# Reset the index and dropping the previous index\n",
    "df = df.reset_index(drop=True)\n",
    "\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Combining Neighbourhoods based on similar Postcode and Borough  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Postcode</th>\n",
       "      <th>Borough</th>\n",
       "      <th>Neighbourhood</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",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>M1J</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Scarborough Village</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>M1L</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Clairlea ,Golden Mile ,Oakridge</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>M1N</td>\n",
       "      <td>Scarborough</td>\n",
       "      <td>Birch Cliff ,Cliffside West</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Postcode      Borough                                     Neighbourhood\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 \n",
       "5      M1J  Scarborough                              Scarborough Village \n",
       "6      M1K  Scarborough      East Birchmount Park ,Ionview ,Kennedy Park \n",
       "7      M1L  Scarborough                  Clairlea ,Golden Mile ,Oakridge \n",
       "8      M1M  Scarborough  Cliffcrest ,Cliffside ,Scarborough Village West \n",
       "9      M1N  Scarborough                      Birch Cliff ,Cliffside West "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.groupby(['Postcode', 'Borough'])['Neighbourhood'].apply(','.join).reset_index()\n",
    "df.columns = ['Postcode','Borough','Neighbourhood']\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Removing any space in the start of the string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['Neighbourhood'] = df['Neighbourhood'].str.strip()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Assigning Borough values to the Neignbourhood where vlaue is \"Not assigned\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.loc[df['Neighbourhood'] == 'Not assigned', 'Neighbourhood'] = df['Borough']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Postcode</th>\n",
       "      <th>Borough</th>\n",
       "      <th>Neighbourhood</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>Queen's Park</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Postcode       Borough Neighbourhood\n",
       "85      M7A  Queen's Park  Queen's Park"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check if the Neighbourhood for Queen's Park changed \n",
    "df[df['Borough'] == 'Queen\\'s Park']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(103, 3)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the shape of the data frame\n",
    "df.shape"
   ]
  }
 ],
 "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}