Extract and clean the data using BeautifulSoup in Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Retrieving data from the web\n",
    "\n",
    "# requests\n",
    "\n",
    "The first task is to retrieve some data from the Internet. Python has many built-in libraries that were developed over the years to do exactly that (e.g. urllib, urllib2, urllib3).\n",
    "\n",
    "However, these libraries are very low-level and somewhat hard to use. They become especially cumbersome when you need to issue POST requests or authenticate against a web service.\n",
    "\n",
    "Luckly, as with most tasks in Python, someone has developed a library that simplifies these tasks. In reality, the requests made both on this assignment are fairly simple, and could easily be done using one of the built-in libraries. However, it is better to get acquainted to requests as soon as possible, since you will probably need it in the future."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that the requests library was imported into our namespace, we can use the functions offered by it.\n",
    "\n",
    "In this case we'll use the appropriately named get function to issue a GET request. This is equivalent to typing a URL into your browser and hitting enter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "req = requests.get(\"https://en.wikipedia.org/wiki/University_of_Cambridge\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another very nifty Python function is dir. You can use it to list all the properties of an object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['__attrs__',\n",
       " '__bool__',\n",
       " '__class__',\n",
       " '__delattr__',\n",
       " '__dict__',\n",
       " '__dir__',\n",
       " '__doc__',\n",
       " '__enter__',\n",
       " '__eq__',\n",
       " '__exit__',\n",
       " '__format__',\n",
       " '__ge__',\n",
       " '__getattribute__',\n",
       " '__getstate__',\n",
       " '__gt__',\n",
       " '__hash__',\n",
       " '__init__',\n",
       " '__init_subclass__',\n",
       " '__iter__',\n",
       " '__le__',\n",
       " '__lt__',\n",
       " '__module__',\n",
       " '__ne__',\n",
       " '__new__',\n",
       " '__nonzero__',\n",
       " '__reduce__',\n",
       " '__reduce_ex__',\n",
       " '__repr__',\n",
       " '__setattr__',\n",
       " '__setstate__',\n",
       " '__sizeof__',\n",
       " '__str__',\n",
       " '__subclasshook__',\n",
       " '__weakref__',\n",
       " '_content',\n",
       " '_content_consumed',\n",
       " '_next',\n",
       " 'apparent_encoding',\n",
       " 'close',\n",
       " 'connection',\n",
       " 'content',\n",
       " 'cookies',\n",
       " 'elapsed',\n",
       " 'encoding',\n",
       " 'headers',\n",
       " 'history',\n",
       " 'is_permanent_redirect',\n",
       " 'is_redirect',\n",
       " 'iter_content',\n",
       " 'iter_lines',\n",
       " 'json',\n",
       " 'links',\n",
       " 'next',\n",
       " 'ok',\n",
       " 'raise_for_status',\n",
       " 'raw',\n",
       " 'reason',\n",
       " 'request',\n",
       " 'status_code',\n",
       " 'text',\n",
       " 'url']"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dir(req)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "page = req.text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "str"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(page)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# BeautifulSoup\n",
    "Parsing data would be a breeze if we could always use well formatted data sources, such as CSV, JSON, or XML; but some formats such as HTML are at the same time a very popular and a pain to parse.\n",
    "\n",
    "One of the problems with HTML is that over the years browsers have evolved to be very forgiving of \"malformed\" syntax. Your browser is smart enough to detect some common problems, such as open tags, and correct them on the fly.\n",
    "\n",
    "Unfortunately, we do not have the time or patience to implement all the different corner cases, so we'll let BeautifulSoup do that for us."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "from bs4 import BeautifulSoup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "BeautifulSoup can deal with HTML or XML data, so the next line parser the contents of the page variable using its HTML parser, and assigns the result of that to the soup variable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "soup = BeautifulSoup(page,'html.parser')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "bs4.BeautifulSoup"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(soup)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Display the title of the webpage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<title>University of Cambridge - Wikipedia</title>"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "soup.title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'University of Cambridge - Wikipedia'"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "soup.title.get_text()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Display all p tags from the webpage\n",
    "\n",
    "You may use find_all method!\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "x = soup.findAll('p')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# No of P tags are present"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "155"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(soup.findAll('p'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you look at the Wikipedia page on your browser, you'll notice that it has a couple of tables in it. We will be working with the \"UCAS Admission Statistics\" table, but first we need to find it.\n",
    "\n",
    "One of the HTML attributes that will be very useful to us is the \"class\" attribute.\n",
    "\n",
    "Getting the class of a single element is easy.."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['infobox', 'vcard']"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "soup.table['class']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create a nested list containing classes of all the table tags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[['infobox', 'vcard'], ['wikitable', 'floatright'], ['infobox'], ['mbox-small', 'plainlinks', 'sistersitebox'], ['mbox-small', 'plainlinks', 'sistersitebox'], ['nowraplinks', 'hlist', 'mw-collapsible', 'autocollapse', 'navbox-inner'], ['nowraplinks', 'navbox-subgroup'], ['nowraplinks', 'mw-collapsible', 'mw-collapsed', 'navbox-inner'], ['nowraplinks', 'hlist', 'mw-collapsible', 'mw-collapsed', 'navbox-inner'], ['nowraplinks', 'navbox-subgroup'], ['nowraplinks', 'navbox-subgroup'], ['nowraplinks', 'mw-collapsible', 'autocollapse', 'navbox-inner'], ['nowraplinks', 'mw-collapsible', 'mw-collapsed', 'navbox-inner'], ['nowraplinks', 'mw-collapsible', 'autocollapse', 'navbox-inner'], ['nowraplinks', 'mw-collapsible', 'autocollapse', 'navbox-inner'], ['nowraplinks', 'hlist', 'navbox-inner']]\n"
     ]
    }
   ],
   "source": [
    "new_table = []\n",
    "for table in soup.findAll('table',class_= True):\n",
    "    new_table.append(table['class'])\n",
    "\n",
    "print(new_table)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As I mentioned, we will be using the UCAS Admission Statistics table for this lab. The next cell contains the HTML elements of said table. We will render it in different parts of the notebook to make it easier to follow along the parsing steps.\n",
    "\n",
    "# Check the classes and find the UCAS Admission Statistics Table\n",
    "\n",
    "Use find method to find the table using the correct class , convert it into string format and store it in table_html also stored the original form in html_soup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_html = str(soup.find_all('table', class_=\"wikitable floatright\")[0])\n",
    "html_soup = soup.find_all('table', class_=\"wikitable floatright\")[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'<table class=\"wikitable floatright\" style=\"font-size:85%; text-align:center;\">\\n<caption>UCAS Admission Statistics\\n</caption>\\n<tbody><tr>\\n<th>\\n</th>\\n<th>2017\\n</th>\\n<th>2016\\n</th>\\n<th>2015\\n</th>\\n<th>2014\\n</th>\\n<th>2013\\n</th></tr>\\n<tr>\\n<td><b>Applications</b><sup class=\"reference\" id=\"cite_ref-104\"><a href=\"#cite_note-104\">[104]</a></sup>\\n</td>\\n<td>17,235\\n</td>\\n<td>16,795\\n</td>\\n<td>16,505\\n</td>\\n<td>16,970\\n</td>\\n<td>16,330\\n</td></tr>\\n<tr>\\n<td><b>Offer Rate (%)</b><sup class=\"reference\" id=\"cite_ref-105\"><a href=\"#cite_note-105\">[105]</a></sup>\\n</td>\\n<td>31.2\\n</td>\\n<td>33.8\\n</td>\\n<td>33.5\\n</td>\\n<td>32.5\\n</td>\\n<td>32.2\\n</td></tr>\\n<tr>\\n<td><b>Enrols</b><sup class=\"reference\" id=\"cite_ref-106\"><a href=\"#cite_note-106\">[106]</a></sup>\\n</td>\\n<td>3,480\\n</td>\\n<td>3,440\\n</td>\\n<td>3,430\\n</td>\\n<td>3,425\\n</td>\\n<td>3,355\\n</td></tr>\\n<tr>\\n<td><a href=\"/wiki/Yield_(college_admissions)\" title=\"Yield (college admissions)\"><b>Yield (%)</b></a>\\n</td>\\n<td>64.7\\n</td>\\n<td>60.6\\n</td>\\n<td>62.0\\n</td>\\n<td>62.1\\n</td>\\n<td>63.8\\n</td></tr>\\n<tr>\\n<td><b>Applicant/Enrolled Ratio</b>\\n</td>\\n<td>4.95\\n</td>\\n<td>4.88\\n</td>\\n<td>4.81\\n</td>\\n<td>4.95\\n</td>\\n<td>4.87\\n</td></tr>\\n<tr>\\n<td><b><a href=\"/wiki/UCAS_Tariff\" title=\"UCAS Tariff\">Average Entry Tariff</a></b><sup class=\"reference\" id=\"cite_ref-Complete_League_Table_2019_107-0\"><a href=\"#cite_note-Complete_League_Table_2019-107\">[107]</a></sup><sup class=\"reference\" id=\"cite_ref-108\"><a href=\"#cite_note-108\">[note 1]</a></sup>\\n</td>\\n<td>n/a\\n</td>\\n<td>226\\n</td>\\n<td>592\\n</td>\\n<td>600\\n</td>\\n<td>601\\n</td></tr></tbody></table>'"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table_html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"wikitable floatright\" style=\"font-size:85%; text-align:center;\">\n",
       "<caption>UCAS Admission Statistics\n",
       "</caption>\n",
       "<tbody><tr>\n",
       "<th>\n",
       "</th>\n",
       "<th>2017\n",
       "</th>\n",
       "<th>2016\n",
       "</th>\n",
       "<th>2015\n",
       "</th>\n",
       "<th>2014\n",
       "</th>\n",
       "<th>2013\n",
       "</th></tr>\n",
       "<tr>\n",
       "<td><b>Applications</b><sup class=\"reference\" id=\"cite_ref-104\"><a href=\"#cite_note-104\">[104]</a></sup>\n",
       "</td>\n",
       "<td>17,235\n",
       "</td>\n",
       "<td>16,795\n",
       "</td>\n",
       "<td>16,505\n",
       "</td>\n",
       "<td>16,970\n",
       "</td>\n",
       "<td>16,330\n",
       "</td></tr>\n",
       "<tr>\n",
       "<td><b>Offer Rate (%)</b><sup class=\"reference\" id=\"cite_ref-105\"><a href=\"#cite_note-105\">[105]</a></sup>\n",
       "</td>\n",
       "<td>31.2\n",
       "</td>\n",
       "<td>33.8\n",
       "</td>\n",
       "<td>33.5\n",
       "</td>\n",
       "<td>32.5\n",
       "</td>\n",
       "<td>32.2\n",
       "</td></tr>\n",
       "<tr>\n",
       "<td><b>Enrols</b><sup class=\"reference\" id=\"cite_ref-106\"><a href=\"#cite_note-106\">[106]</a></sup>\n",
       "</td>\n",
       "<td>3,480\n",
       "</td>\n",
       "<td>3,440\n",
       "</td>\n",
       "<td>3,430\n",
       "</td>\n",
       "<td>3,425\n",
       "</td>\n",
       "<td>3,355\n",
       "</td></tr>\n",
       "<tr>\n",
       "<td><a href=\"/wiki/Yield_(college_admissions)\" title=\"Yield (college admissions)\"><b>Yield (%)</b></a>\n",
       "</td>\n",
       "<td>64.7\n",
       "</td>\n",
       "<td>60.6\n",
       "</td>\n",
       "<td>62.0\n",
       "</td>\n",
       "<td>62.1\n",
       "</td>\n",
       "<td>63.8\n",
       "</td></tr>\n",
       "<tr>\n",
       "<td><b>Applicant/Enrolled Ratio</b>\n",
       "</td>\n",
       "<td>4.95\n",
       "</td>\n",
       "<td>4.88\n",
       "</td>\n",
       "<td>4.81\n",
       "</td>\n",
       "<td>4.95\n",
       "</td>\n",
       "<td>4.87\n",
       "</td></tr>\n",
       "<tr>\n",
       "<td><b><a href=\"/wiki/UCAS_Tariff\" title=\"UCAS Tariff\">Average Entry Tariff</a></b><sup class=\"reference\" id=\"cite_ref-Complete_League_Table_2019_107-0\"><a href=\"#cite_note-Complete_League_Table_2019-107\">[107]</a></sup><sup class=\"reference\" id=\"cite_ref-108\"><a href=\"#cite_note-108\">[note 1]</a></sup>\n",
       "</td>\n",
       "<td>n/a\n",
       "</td>\n",
       "<td>226\n",
       "</td>\n",
       "<td>592\n",
       "</td>\n",
       "<td>600\n",
       "</td>\n",
       "<td>601\n",
       "</td></tr></tbody></table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from IPython.core.display import HTML\n",
    "\n",
    "HTML(table_html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Extract the rows from the UCAS Admission Statistics table and store it in rows variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[<tr>\n",
      "<th>\n",
      "</th>\n",
      "<th>2017\n",
      "</th>\n",
      "<th>2016\n",
      "</th>\n",
      "<th>2015\n",
      "</th>\n",
      "<th>2014\n",
      "</th>\n",
      "<th>2013\n",
      "</th></tr>, <tr>\n",
      "<td><b>Applications</b><sup class=\"reference\" id=\"cite_ref-104\"><a href=\"#cite_note-104\">[104]</a></sup>\n",
      "</td>\n",
      "<td>17,235\n",
      "</td>\n",
      "<td>16,795\n",
      "</td>\n",
      "<td>16,505\n",
      "</td>\n",
      "<td>16,970\n",
      "</td>\n",
      "<td>16,330\n",
      "</td></tr>, <tr>\n",
      "<td><b>Offer Rate (%)</b><sup class=\"reference\" id=\"cite_ref-105\"><a href=\"#cite_note-105\">[105]</a></sup>\n",
      "</td>\n",
      "<td>31.2\n",
      "</td>\n",
      "<td>33.8\n",
      "</td>\n",
      "<td>33.5\n",
      "</td>\n",
      "<td>32.5\n",
      "</td>\n",
      "<td>32.2\n",
      "</td></tr>, <tr>\n",
      "<td><b>Enrols</b><sup class=\"reference\" id=\"cite_ref-106\"><a href=\"#cite_note-106\">[106]</a></sup>\n",
      "</td>\n",
      "<td>3,480\n",
      "</td>\n",
      "<td>3,440\n",
      "</td>\n",
      "<td>3,430\n",
      "</td>\n",
      "<td>3,425\n",
      "</td>\n",
      "<td>3,355\n",
      "</td></tr>, <tr>\n",
      "<td><a href=\"/wiki/Yield_(college_admissions)\" title=\"Yield (college admissions)\"><b>Yield (%)</b></a>\n",
      "</td>\n",
      "<td>64.7\n",
      "</td>\n",
      "<td>60.6\n",
      "</td>\n",
      "<td>62.0\n",
      "</td>\n",
      "<td>62.1\n",
      "</td>\n",
      "<td>63.8\n",
      "</td></tr>, <tr>\n",
      "<td><b>Applicant/Enrolled Ratio</b>\n",
      "</td>\n",
      "<td>4.95\n",
      "</td>\n",
      "<td>4.88\n",
      "</td>\n",
      "<td>4.81\n",
      "</td>\n",
      "<td>4.95\n",
      "</td>\n",
      "<td>4.87\n",
      "</td></tr>, <tr>\n",
      "<td><b><a href=\"/wiki/UCAS_Tariff\" title=\"UCAS Tariff\">Average Entry Tariff</a></b><sup class=\"reference\" id=\"cite_ref-Complete_League_Table_2019_107-0\"><a href=\"#cite_note-Complete_League_Table_2019-107\">[107]</a></sup><sup class=\"reference\" id=\"cite_ref-108\"><a href=\"#cite_note-108\">[note 1]</a></sup>\n",
      "</td>\n",
      "<td>n/a\n",
      "</td>\n",
      "<td>226\n",
      "</td>\n",
      "<td>592\n",
      "</td>\n",
      "<td>600\n",
      "</td>\n",
      "<td>601\n",
      "</td></tr>]\n"
     ]
    }
   ],
   "source": [
    "rows = html_soup.find_all('tr')\n",
    "print(rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# lambda expressions\n",
    "\n",
    "We will then use a lambda expression to replace new line characters with spaces. Lambda expressions are to functions what list comprehensions are to lists: namely a more concise way to achieve the same thing.\n",
    "\n",
    "In reality, both lambda expressions and list comprehensions are a little different from their function and loop counterparts. But for the purposes of this class we can ignore those differences"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "rem_nl = lambda s: s.replace(\"\\n\", \" \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Extract the columns from the UCAS Admission Statistics table and store it in columns variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['2017', '2016', '2015', '2014', '2013']\n"
     ]
    }
   ],
   "source": [
    "th = html_soup.find_all('th')\n",
    "\n",
    "column = []\n",
    "columns = []\n",
    "\n",
    "for x in th:\n",
    "    column.append(rem_nl(x.get_text()))\n",
    "    \n",
    "column.remove(' ')\n",
    "\n",
    "for p in column:\n",
    "    columns.append(p.strip())\n",
    "        \n",
    "print(columns)\n",
    "        \n",
    "    \n",
    "    \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Extract the indexes from the rows variable\n",
    "\n",
    "Store it in a variable named indexes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Applications', 'Offer Rate (%)', 'Enrols', 'Yield (%)', 'Applicant/Enrolled Ratio', 'Average Entry Tariff']\n"
     ]
    }
   ],
   "source": [
    "index = html_soup.findAll('b')\n",
    "\n",
    "indexes = []\n",
    "for b in index:\n",
    "    indexes.append(b.get_text())\n",
    "    \n",
    "print(indexes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"wikitable floatright\" style=\"font-size:85%; text-align:center;\">\n",
       "<caption>UCAS Admission Statistics\n",
       "</caption>\n",
       "<tbody><tr>\n",
       "<th>\n",
       "</th>\n",
       "<th>2017\n",
       "</th>\n",
       "<th>2016\n",
       "</th>\n",
       "<th>2015\n",
       "</th>\n",
       "<th>2014\n",
       "</th>\n",
       "<th>2013\n",
       "</th></tr>\n",
       "<tr>\n",
       "<td><b>Applications</b><sup class=\"reference\" id=\"cite_ref-104\"><a href=\"#cite_note-104\">[104]</a></sup>\n",
       "</td>\n",
       "<td>17,235\n",
       "</td>\n",
       "<td>16,795\n",
       "</td>\n",
       "<td>16,505\n",
       "</td>\n",
       "<td>16,970\n",
       "</td>\n",
       "<td>16,330\n",
       "</td></tr>\n",
       "<tr>\n",
       "<td><b>Offer Rate (%)</b><sup class=\"reference\" id=\"cite_ref-105\"><a href=\"#cite_note-105\">[105]</a></sup>\n",
       "</td>\n",
       "<td>31.2\n",
       "</td>\n",
       "<td>33.8\n",
       "</td>\n",
       "<td>33.5\n",
       "</td>\n",
       "<td>32.5\n",
       "</td>\n",
       "<td>32.2\n",
       "</td></tr>\n",
       "<tr>\n",
       "<td><b>Enrols</b><sup class=\"reference\" id=\"cite_ref-106\"><a href=\"#cite_note-106\">[106]</a></sup>\n",
       "</td>\n",
       "<td>3,480\n",
       "</td>\n",
       "<td>3,440\n",
       "</td>\n",
       "<td>3,430\n",
       "</td>\n",
       "<td>3,425\n",
       "</td>\n",
       "<td>3,355\n",
       "</td></tr>\n",
       "<tr>\n",
       "<td><a href=\"/wiki/Yield_(college_admissions)\" title=\"Yield (college admissions)\"><b>Yield (%)</b></a>\n",
       "</td>\n",
       "<td>64.7\n",
       "</td>\n",
       "<td>60.6\n",
       "</td>\n",
       "<td>62.0\n",
       "</td>\n",
       "<td>62.1\n",
       "</td>\n",
       "<td>63.8\n",
       "</td></tr>\n",
       "<tr>\n",
       "<td><b>Applicant/Enrolled Ratio</b>\n",
       "</td>\n",
       "<td>4.95\n",
       "</td>\n",
       "<td>4.88\n",
       "</td>\n",
       "<td>4.81\n",
       "</td>\n",
       "<td>4.95\n",
       "</td>\n",
       "<td>4.87\n",
       "</td></tr>\n",
       "<tr>\n",
       "<td><b><a href=\"/wiki/UCAS_Tariff\" title=\"UCAS Tariff\">Average Entry Tariff</a></b><sup class=\"reference\" id=\"cite_ref-Complete_League_Table_2019_107-0\"><a href=\"#cite_note-Complete_League_Table_2019-107\">[107]</a></sup><sup class=\"reference\" id=\"cite_ref-108\"><a href=\"#cite_note-108\">[note 1]</a></sup>\n",
       "</td>\n",
       "<td>n/a\n",
       "</td>\n",
       "<td>226\n",
       "</td>\n",
       "<td>592\n",
       "</td>\n",
       "<td>600\n",
       "</td>\n",
       "<td>601\n",
       "</td></tr></tbody></table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "HTML(table_html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[17235.0, 16795.0, 16505.0, 16970.0, 16330.0, 31.2, 33.8, 33.5, 32.5, 32.2, 3480.0, 3440.0, 3430.0, 3425.0, 3355.0, 64.7, 60.6, 62.0, 62.1, 63.8, 4.95, 4.88, 4.81, 4.95, 4.87, None, 226.0, 592.0, 600.0, 601.0]\n"
     ]
    }
   ],
   "source": [
    "td = html_soup.findAll('td')\n",
    "\n",
    "value = []\n",
    "i = []\n",
    "values = []\n",
    "\n",
    "for x in td:\n",
    "    value.append(rem_nl(x.get_text()))\n",
    "\n",
    "unwanted = [0,6,12,18,24,30]\n",
    "\n",
    "for y in sorted(unwanted, reverse = True):\n",
    "    del value[y] \n",
    "    \n",
    "for z in value:\n",
    "    i.append(z.strip(\" \"))\n",
    "    \n",
    "for w in i:\n",
    "    if (w == 'n/a'):\n",
    "        values.append(None)\n",
    "    else:\n",
    "        values.append(float(w.replace(',','')))\n",
    "    \n",
    "\n",
    "    \n",
    "print(values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The problem with the list above is that the values lost their grouping.\n",
    "\n",
    "The zip function is used to combine two sequences element wise. So zip([1,2,3], [4,5,6]) would return [(1, 4), (2, 5), (3, 6)].\n",
    "\n",
    "This is the first time we see a container bounded by parenthesis. This is a tuple, which you can think of as an immutable list (meaning you can't add, remove, or change elements from it). Otherwise they work just like lists and can be indexed, sliced, etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<zip object at 0x00000215FD652D88>\n"
     ]
    }
   ],
   "source": [
    "stacked_values = zip(*[values[i::5] for i in range(len(columns))])\n",
    "print(stacked_values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---------\n",
    "\n",
    "# Pandas data structures\n",
    "\n",
    "## DataFrames\n",
    "\n",
    "To recap, we now have three data structures holding our column names, our row (index) names, and our values grouped by index.\n",
    "\n",
    "We will now load this data into a Pandas DataFrame. The loading process is pretty straightforward, and all we need to do is tell Pandas which container goes where."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create the DataFrame\n",
    "### Use stacked_values, columns and indexes to create the Demographics DataFrame\n",
    "#### Name the DataFrame df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "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>2017</th>\n",
       "      <th>2016</th>\n",
       "      <th>2015</th>\n",
       "      <th>2014</th>\n",
       "      <th>2013</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Applications</th>\n",
       "      <td>17235.00</td>\n",
       "      <td>16795.00</td>\n",
       "      <td>16505.00</td>\n",
       "      <td>16970.00</td>\n",
       "      <td>16330.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Offer Rate (%)</th>\n",
       "      <td>31.20</td>\n",
       "      <td>33.80</td>\n",
       "      <td>33.50</td>\n",
       "      <td>32.50</td>\n",
       "      <td>32.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Enrols</th>\n",
       "      <td>3480.00</td>\n",
       "      <td>3440.00</td>\n",
       "      <td>3430.00</td>\n",
       "      <td>3425.00</td>\n",
       "      <td>3355.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yield (%)</th>\n",
       "      <td>64.70</td>\n",
       "      <td>60.60</td>\n",
       "      <td>62.00</td>\n",
       "      <td>62.10</td>\n",
       "      <td>63.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Applicant/Enrolled Ratio</th>\n",
       "      <td>4.95</td>\n",
       "      <td>4.88</td>\n",
       "      <td>4.81</td>\n",
       "      <td>4.95</td>\n",
       "      <td>4.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Average Entry Tariff</th>\n",
       "      <td>NaN</td>\n",
       "      <td>226.00</td>\n",
       "      <td>592.00</td>\n",
       "      <td>600.00</td>\n",
       "      <td>601.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              2017      2016      2015      2014      2013\n",
       "Applications              17235.00  16795.00  16505.00  16970.00  16330.00\n",
       "Offer Rate (%)               31.20     33.80     33.50     32.50     32.20\n",
       "Enrols                     3480.00   3440.00   3430.00   3425.00   3355.00\n",
       "Yield (%)                    64.70     60.60     62.00     62.10     63.80\n",
       "Applicant/Enrolled Ratio      4.95      4.88      4.81      4.95      4.87\n",
       "Average Entry Tariff           NaN    226.00    592.00    600.00    601.00"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(stacked_values,indexes,columns)\n",
    "df.head(6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2017    float64\n",
       "2016    float64\n",
       "2015    float64\n",
       "2014    float64\n",
       "2013    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Drop the row containing NaN value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "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>2017</th>\n",
       "      <th>2016</th>\n",
       "      <th>2015</th>\n",
       "      <th>2014</th>\n",
       "      <th>2013</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Applications</th>\n",
       "      <td>17235.00</td>\n",
       "      <td>16795.00</td>\n",
       "      <td>16505.00</td>\n",
       "      <td>16970.00</td>\n",
       "      <td>16330.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Offer Rate (%)</th>\n",
       "      <td>31.20</td>\n",
       "      <td>33.80</td>\n",
       "      <td>33.50</td>\n",
       "      <td>32.50</td>\n",
       "      <td>32.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Enrols</th>\n",
       "      <td>3480.00</td>\n",
       "      <td>3440.00</td>\n",
       "      <td>3430.00</td>\n",
       "      <td>3425.00</td>\n",
       "      <td>3355.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yield (%)</th>\n",
       "      <td>64.70</td>\n",
       "      <td>60.60</td>\n",
       "      <td>62.00</td>\n",
       "      <td>62.10</td>\n",
       "      <td>63.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Applicant/Enrolled Ratio</th>\n",
       "      <td>4.95</td>\n",
       "      <td>4.88</td>\n",
       "      <td>4.81</td>\n",
       "      <td>4.95</td>\n",
       "      <td>4.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Average Entry Tariff</th>\n",
       "      <td>0.00</td>\n",
       "      <td>226.00</td>\n",
       "      <td>592.00</td>\n",
       "      <td>600.00</td>\n",
       "      <td>601.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              2017      2016      2015      2014      2013\n",
       "Applications              17235.00  16795.00  16505.00  16970.00  16330.00\n",
       "Offer Rate (%)               31.20     33.80     33.50     32.50     32.20\n",
       "Enrols                     3480.00   3440.00   3430.00   3425.00   3355.00\n",
       "Yield (%)                    64.70     60.60     62.00     62.10     63.80\n",
       "Applicant/Enrolled Ratio      4.95      4.88      4.81      4.95      4.87\n",
       "Average Entry Tariff          0.00    226.00    592.00    600.00    601.00"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean = df.fillna(value = 0)\n",
    "df_clean"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now our table looks good!\n",
    "\n",
    "---\n",
    "\n",
    "### NumPy\n",
    "\n",
    "Pandas is awesome, but it is built on top of another library the we will use extensively during the course. NumPy implements new data types and vectorized functions.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[1.7235e+04, 1.6795e+04, 1.6505e+04, 1.6970e+04, 1.6330e+04],\n",
       "       [3.1200e+01, 3.3800e+01, 3.3500e+01, 3.2500e+01, 3.2200e+01],\n",
       "       [3.4800e+03, 3.4400e+03, 3.4300e+03, 3.4250e+03, 3.3550e+03],\n",
       "       [6.4700e+01, 6.0600e+01, 6.2000e+01, 6.2100e+01, 6.3800e+01],\n",
       "       [4.9500e+00, 4.8800e+00, 4.8100e+00, 4.9500e+00, 4.8700e+00],\n",
       "       [0.0000e+00, 2.2600e+02, 5.9200e+02, 6.0000e+02, 6.0100e+02]])"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "numpy.ndarray"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(df_clean.values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3469.308333333334"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mean = df_clean['2017'].mean()\n",
    "mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2017    6883.955362\n",
       "2016    6685.915854\n",
       "2015    6536.376041\n",
       "2014    6721.802172\n",
       "2013    6465.609210\n",
       "dtype: float64"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SD = df_clean.std()\n",
    "SD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}