Extracting Data From Canada.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# IMPORTING REQUIRED LIBRARIES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd #To load data from html tables\n",
    "import numpy as np #To replace Not assigned values with NaN"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Extracting data from wikipedia"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[    Postal Code           Borough  \\\n",
       " 0           M1A      Not assigned   \n",
       " 1           M2A      Not assigned   \n",
       " 2           M3A        North York   \n",
       " 3           M4A        North York   \n",
       " 4           M5A  Downtown Toronto   \n",
       " ..          ...               ...   \n",
       " 175         M5Z      Not assigned   \n",
       " 176         M6Z      Not assigned   \n",
       " 177         M7Z      Not assigned   \n",
       " 178         M8Z         Etobicoke   \n",
       " 179         M9Z      Not assigned   \n",
       " \n",
       "                                           Neighborhood  \n",
       " 0                                                  NaN  \n",
       " 1                                                  NaN  \n",
       " 2                                            Parkwoods  \n",
       " 3                                     Victoria Village  \n",
       " 4                            Regent Park, Harbourfront  \n",
       " ..                                                 ...  \n",
       " 175                                                NaN  \n",
       " 176                                                NaN  \n",
       " 177                                                NaN  \n",
       " 178  Mimico NW, The Queensway West, South of Bloor,...  \n",
       " 179                                                NaN  \n",
       " \n",
       " [180 rows x 3 columns],\n",
       "                                                   0   \\\n",
       " 0                                                NaN   \n",
       " 1  NL NS PE NB QC ON MB SK AB BC NU/NT YT A B C E...   \n",
       " 2                                                 NL   \n",
       " 3                                                  A   \n",
       " \n",
       "                                                   1   \\\n",
       " 0                              Canadian postal codes   \n",
       " 1  NL NS PE NB QC ON MB SK AB BC NU/NT YT A B C E...   \n",
       " 2                                                 NS   \n",
       " 3                                                  B   \n",
       " \n",
       "                                                   2    3    4    5    6    7   \\\n",
       " 0                                                NaN  NaN  NaN  NaN  NaN  NaN   \n",
       " 1  NL NS PE NB QC ON MB SK AB BC NU/NT YT A B C E...  NaN  NaN  NaN  NaN  NaN   \n",
       " 2                                                 PE   NB   QC   QC   QC   ON   \n",
       " 3                                                  C    E    G    H    J    K   \n",
       " \n",
       "     8    9    10   11   12   13   14   15     16   17  \n",
       " 0  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN    NaN  NaN  \n",
       " 1  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN    NaN  NaN  \n",
       " 2   ON   ON   ON   ON   MB   SK   AB   BC  NU/NT   YT  \n",
       " 3    L    M    N    P    R    S    T    V      X    Y  ,\n",
       "    0   1   2   3   4   5   6   7   8   9   10  11  12  13  14  15     16  17\n",
       " 0  NL  NS  PE  NB  QC  QC  QC  ON  ON  ON  ON  ON  MB  SK  AB  BC  NU/NT  YT\n",
       " 1   A   B   C   E   G   H   J   K   L   M   N   P   R   S   T   V      X   Y]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_url=\"https://en.wikipedia.org/wiki/List_of_postal_codes_of_Canada:_M\" #Link of webpage from which data neads to be scrapped\n",
    "#Now let's scrap the page using read_html function\n",
    "tables = pd.read_html(data_url)\n",
    "tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Here read_html function returns 3 lists but the required data is in first list only "
   ]
  },
  {
   "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",
       "      <td>0</td>\n",
       "      <td>M1A</td>\n",
       "      <td>Not assigned</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>M2A</td>\n",
       "      <td>Not assigned</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>M3A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Parkwoods</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>M4A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Victoria Village</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\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\n",
       "0         M1A      Not assigned                        NaN\n",
       "1         M2A      Not assigned                        NaN\n",
       "2         M3A        North York                  Parkwoods\n",
       "3         M4A        North York           Victoria Village\n",
       "4         M5A  Downtown Toronto  Regent Park, Harbourfront"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=tables[0]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### We have to remove the rows where Borough is not defined. \n",
    "#### Let's replace them with NaN"
   ]
  },
  {
   "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>Borough</th>\n",
       "      <th>Neighborhood</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>M1A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>M2A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>M3A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Parkwoods</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>M4A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Victoria Village</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\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\n",
       "0         M1A               NaN                        NaN\n",
       "1         M2A               NaN                        NaN\n",
       "2         M3A        North York                  Parkwoods\n",
       "3         M4A        North York           Victoria Village\n",
       "4         M5A  Downtown Toronto  Regent Park, Harbourfront"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.replace(\"Not assigned\", np.nan, inplace = True)\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    103\n",
       "True      77\n",
       "Name: Neighborhood, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Finding number of null values\n",
    "df.isnull()['Neighborhood'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Deleting rows with missing values"
   ]
  },
  {
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>M3A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Parkwoods</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>M4A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Victoria Village</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>M5A</td>\n",
       "      <td>Downtown Toronto</td>\n",
       "      <td>Regent Park, Harbourfront</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>M6A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Lawrence Manor, Lawrence Heights</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>M7A</td>\n",
       "      <td>Downtown Toronto</td>\n",
       "      <td>Queen's Park, Ontario Provincial Government</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>98</td>\n",
       "      <td>M8X</td>\n",
       "      <td>Etobicoke</td>\n",
       "      <td>The Kingsway, Montgomery Road, Old Mill North</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>99</td>\n",
       "      <td>M4Y</td>\n",
       "      <td>Downtown Toronto</td>\n",
       "      <td>Church and Wellesley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>100</td>\n",
       "      <td>M7Y</td>\n",
       "      <td>East Toronto</td>\n",
       "      <td>Business reply mail Processing Centre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>101</td>\n",
       "      <td>M8Y</td>\n",
       "      <td>Etobicoke</td>\n",
       "      <td>Old Mill South, King's Mill Park, Sunnylea, Hu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>102</td>\n",
       "      <td>M8Z</td>\n",
       "      <td>Etobicoke</td>\n",
       "      <td>Mimico NW, The Queensway West, South of Bloor,...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>103 rows × 3 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  \n",
       "0                                            Parkwoods  \n",
       "1                                     Victoria Village  \n",
       "2                            Regent Park, Harbourfront  \n",
       "3                     Lawrence Manor, Lawrence Heights  \n",
       "4          Queen's Park, Ontario Provincial Government  \n",
       "..                                                 ...  \n",
       "98       The Kingsway, Montgomery Road, Old Mill North  \n",
       "99                                Church and Wellesley  \n",
       "100              Business reply mail Processing Centre  \n",
       "101  Old Mill South, King's Mill Park, Sunnylea, Hu...  \n",
       "102  Mimico NW, The Queensway West, South of Bloor,...  \n",
       "\n",
       "[103 rows x 3 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# simply drop whole row with NaN in \"Borough\" column\n",
    "df.dropna(subset=[\"Borough\"], axis=0, inplace=True)\n",
    "# reset index, because we droped two rows\n",
    "df.reset_index(drop=True, inplace=True)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    103\n",
       "Name: Neighborhood, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Verifying if all null values are removed\n",
    "df.isnull()['Neighborhood'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "103"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Checking whether all postal codes are unique or Neighbourhood needs to be merged\n",
    "len(df['Postal Code'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(103, 3)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving data for future use"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"canada_neighbourhood.csv\")"
   ]
  }
 ],
 "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}