databases/Data and Databases Homework Assignment 5 with answers.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:5ad8d8e44407268fe02d2542370aa75a9cd971bd613da5083e6feb15a10547ee"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#Homework assignment #5\n",
      "\n",
      "These problem sets focus on using the Beautiful Soup library to scrape web pages.\n",
      "\n",
      "##Problem Set #1: Basic scraping\n",
      "\n",
      "I've made a web page for you to scrape. It's available [here](http://static.decontextualize.com/widgets.html). The page concerns the catalog of a famous [widget](http://en.wikipedia.org/wiki/Widget) company. You'll be answering several questions about this web page. First off, in the cell below, write some code so that you end up with a variable called `html_str` that contains the HTML source code of the page. I've pre-filled the cell with some code; your job is to write the missing line. When you run the cell, it should print out `2801` (the number of characters in the HTML source code for `widgets.html`)."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import urllib\n",
      "html_str = urllib.urlopen(\"http://static.decontextualize.com/widgets.html\").read()\n",
      "\n",
      "print len(html_str)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2801\n"
       ]
      }
     ],
     "prompt_number": 4
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Excellent. Now, in the cell below, use Beautiful Soup to write an expression that evaluates to the number of `<h3>` tags contained in `widgets.html`. I've added the Beautiful Soup import statement for you."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from bs4 import BeautifulSoup\n",
      "# your code here\n",
      "\n",
      "document = BeautifulSoup(html_str)\n",
      "len(document.find_all('h3'))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 5,
       "text": [
        "3"
       ]
      }
     ],
     "prompt_number": 5
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "In the cell below, use Beautiful Soup to write some code that creates a list of the names of all the widgets on the page. I've created a variable with an empty list, `widget_names`, for you. After your code has executed, `widget_names` should evaluate to a list that looks like this (though not necessarily in this order):\n",
      "\n",
      "    [u'Skinner Widget',\n",
      "     u'Widget For Furtiveness',\n",
      "     u'Widget For Strawman',\n",
      "     u'Manicurist Widget',\n",
      "     u'Infinite Widget',\n",
      "     u'Yellow-Tipped Widget',\n",
      "     u'Unshakable Widget',\n",
      "     u'Self-Knowledge Widget',\n",
      "     u'Widget For Cinema']"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "widget_names = []\n",
      "\n",
      "widget_names = [tag.string for tag in document.find_all('td', attrs={\"class\": \"widgetname\"})]\n",
      "\n",
      "widget_names"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 6,
       "text": [
        "[u'Skinner Widget',\n",
        " u'Widget For Furtiveness',\n",
        " u'Widget For Strawman',\n",
        " u'Manicurist Widget',\n",
        " u'Infinite Widget',\n",
        " u'Yellow-Tipped Widget',\n",
        " u'Unshakable Widget',\n",
        " u'Self-Knowledge Widget',\n",
        " u'Widget For Cinema']"
       ]
      }
     ],
     "prompt_number": 6
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "##Problem set #2: Of Widgets and Pandas\n",
      "\n",
      "For this problem set, we'll continue to use the HTML page from the previous problem set. In the cell below, I've made an empty list and assigned it to a variable called `widgets`. Write code that populates this list with dictionaries, one dictionary per widget in the source file. The keys of each dictionary should be `partno`, `widgetname`, `price`, and `quantity`, and the value for each of the keys should be the value for the corresponding column for each row. After executing the cell, your list should look something like this:\n",
      "\n",
      "    [{'partno': u'C1-9476',\n",
      "      'price': u'$2.70',\n",
      "      'quantity': u'512',\n",
      "      'widgetname': u'Skinner Widget'},\n",
      "     {'partno': u'JDJ-32/V',\n",
      "      'price': u'$9.36',\n",
      "      'quantity': u'967',\n",
      "      'widgetname': u'Widget For Furtiveness'},\n",
      "      ...several items omitted...\n",
      "     {'partno': u'5B-941/F',\n",
      "      'price': u'$13.26',\n",
      "      'quantity': u'919',\n",
      "      'widgetname': u'Widget For Cinema'}]\n",
      "\n",
      "And this expression:\n",
      "\n",
      "    widgets[5]['partno']\n",
      "    \n",
      "... should evaluate to:\n",
      "\n",
      "    u'MZ-556/B'\n"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "widgets = []\n",
      "\n",
      "html_str = urllib.urlopen(\"http://static.decontextualize.com/widgets.html\").read()\n",
      "\n",
      "# your code here\n",
      "document = BeautifulSoup(html_str)\n",
      "tr_tags = document.find_all(\"tr\", attrs={'class': 'widgetinfo'})\n",
      "for tr_tag in tr_tags:\n",
      "    widget_dict = {}\n",
      "    for class_ in (\"partno\", \"widgetname\", \"price\", \"quantity\"):\n",
      "        tag = tr_tag.find(\"td\", attrs={'class': class_})\n",
      "        widget_dict[class_] = tag.string\n",
      "    widgets.append(widget_dict) \n",
      "# end your code\n",
      "\n",
      "widgets"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 9,
       "text": [
        "[{'partno': u'C1-9476',\n",
        "  'price': u'$2.70',\n",
        "  'quantity': u'512',\n",
        "  'widgetname': u'Skinner Widget'},\n",
        " {'partno': u'JDJ-32/V',\n",
        "  'price': u'$9.36',\n",
        "  'quantity': u'967',\n",
        "  'widgetname': u'Widget For Furtiveness'},\n",
        " {'partno': u'YP4-325/J',\n",
        "  'price': u'$5.17',\n",
        "  'quantity': u'787',\n",
        "  'widgetname': u'Widget For Strawman'},\n",
        " {'partno': u'VK-486',\n",
        "  'price': u'$8.97',\n",
        "  'quantity': u'441',\n",
        "  'widgetname': u'Manicurist Widget'},\n",
        " {'partno': u'R4K-990',\n",
        "  'price': u'$11.73',\n",
        "  'quantity': u'320',\n",
        "  'widgetname': u'Infinite Widget'},\n",
        " {'partno': u'MZ-556/B',\n",
        "  'price': u'$2.35',\n",
        "  'quantity': u'948',\n",
        "  'widgetname': u'Yellow-Tipped Widget'},\n",
        " {'partno': u'QV-730',\n",
        "  'price': u'$3.76',\n",
        "  'quantity': u'59',\n",
        "  'widgetname': u'Unshakable Widget'},\n",
        " {'partno': u'T1-9731',\n",
        "  'price': u'$7.11',\n",
        "  'quantity': u'790',\n",
        "  'widgetname': u'Self-Knowledge Widget'},\n",
        " {'partno': u'5B-941/F',\n",
        "  'price': u'$13.26',\n",
        "  'quantity': u'919',\n",
        "  'widgetname': u'Widget For Cinema'}]"
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "In the cell below, duplicate your code from the previous question. Modify the code to ensure that the values for `price` and `quantity` in each dictionary are floating-point numbers and integers, respectively. I.e., after executing the cell, your code should display something like this:\n",
      "\n",
      "    [{'partno': 'C1-9476',\n",
      "      'price': 2.7,\n",
      "      'quantity': 512,\n",
      "      'widgetname': 'Skinner Widget'},\n",
      "     {'partno': 'JDJ-32/V',\n",
      "      'price': 9.36,\n",
      "      'quantity': 967,\n",
      "      'widgetname': 'Widget For Furtiveness'},\n",
      "     ... some items omitted ...\n",
      "     {'partno': '5B-941/F',\n",
      "      'price': 13.26,\n",
      "      'quantity': 919,\n",
      "      'widgetname': 'Widget For Cinema'}]\n",
      "\n",
      "(Hint: Use the `float()` and `int()` functions. You may need to use string slices to convert the `price` field to a floating-point number.)"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "widgets = []\n",
      "\n",
      "html_str = urllib.urlopen(\"http://static.decontextualize.com/widgets.html\").read()\n",
      "\n",
      "# your code here\n",
      "document = BeautifulSoup(html_str)\n",
      "tr_tags = document.find_all(\"tr\", attrs={'class': 'widgetinfo'})\n",
      "for tr_tag in tr_tags:\n",
      "    widget_dict = {}\n",
      "    for class_ops in ((\"partno\", str), (\"widgetname\", str), (\"price\", lambda x: float(x[1:])), (\"quantity\", int)):\n",
      "        tag = tr_tag.find(\"td\", attrs={'class': class_ops[0]})\n",
      "        widget_dict[class_ops[0]] = class_ops[1](tag.string)\n",
      "    widgets.append(widget_dict) \n",
      "# end your code\n",
      "\n",
      "widgets"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 10,
       "text": [
        "[{'partno': 'C1-9476',\n",
        "  'price': 2.7,\n",
        "  'quantity': 512,\n",
        "  'widgetname': 'Skinner Widget'},\n",
        " {'partno': 'JDJ-32/V',\n",
        "  'price': 9.36,\n",
        "  'quantity': 967,\n",
        "  'widgetname': 'Widget For Furtiveness'},\n",
        " {'partno': 'YP4-325/J',\n",
        "  'price': 5.17,\n",
        "  'quantity': 787,\n",
        "  'widgetname': 'Widget For Strawman'},\n",
        " {'partno': 'VK-486',\n",
        "  'price': 8.97,\n",
        "  'quantity': 441,\n",
        "  'widgetname': 'Manicurist Widget'},\n",
        " {'partno': 'R4K-990',\n",
        "  'price': 11.73,\n",
        "  'quantity': 320,\n",
        "  'widgetname': 'Infinite Widget'},\n",
        " {'partno': 'MZ-556/B',\n",
        "  'price': 2.35,\n",
        "  'quantity': 948,\n",
        "  'widgetname': 'Yellow-Tipped Widget'},\n",
        " {'partno': 'QV-730',\n",
        "  'price': 3.76,\n",
        "  'quantity': 59,\n",
        "  'widgetname': 'Unshakable Widget'},\n",
        " {'partno': 'T1-9731',\n",
        "  'price': 7.11,\n",
        "  'quantity': 790,\n",
        "  'widgetname': 'Self-Knowledge Widget'},\n",
        " {'partno': '5B-941/F',\n",
        "  'price': 13.26,\n",
        "  'quantity': 919,\n",
        "  'widgetname': 'Widget For Cinema'}]"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Great! I hope you're having fun. For the next problem, you'll be converting the list of dictionaries that you created above into a Pandas data frame. I'll give you the code for doing so for free in the cell below:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "widgets_df = pd.DataFrame(widgets)\n",
      "widgets_df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>partno</th>\n",
        "      <th>price</th>\n",
        "      <th>quantity</th>\n",
        "      <th>widgetname</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td>   C1-9476</td>\n",
        "      <td>  2.70</td>\n",
        "      <td> 512</td>\n",
        "      <td>         Skinner Widget</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td>  JDJ-32/V</td>\n",
        "      <td>  9.36</td>\n",
        "      <td> 967</td>\n",
        "      <td> Widget For Furtiveness</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> YP4-325/J</td>\n",
        "      <td>  5.17</td>\n",
        "      <td> 787</td>\n",
        "      <td>    Widget For Strawman</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td>    VK-486</td>\n",
        "      <td>  8.97</td>\n",
        "      <td> 441</td>\n",
        "      <td>      Manicurist Widget</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td>   R4K-990</td>\n",
        "      <td> 11.73</td>\n",
        "      <td> 320</td>\n",
        "      <td>        Infinite Widget</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>5</th>\n",
        "      <td>  MZ-556/B</td>\n",
        "      <td>  2.35</td>\n",
        "      <td> 948</td>\n",
        "      <td>   Yellow-Tipped Widget</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>6</th>\n",
        "      <td>    QV-730</td>\n",
        "      <td>  3.76</td>\n",
        "      <td>  59</td>\n",
        "      <td>      Unshakable Widget</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>7</th>\n",
        "      <td>   T1-9731</td>\n",
        "      <td>  7.11</td>\n",
        "      <td> 790</td>\n",
        "      <td>  Self-Knowledge Widget</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>8</th>\n",
        "      <td>  5B-941/F</td>\n",
        "      <td> 13.26</td>\n",
        "      <td> 919</td>\n",
        "      <td>      Widget For Cinema</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 11,
       "text": [
        "      partno  price  quantity              widgetname\n",
        "0    C1-9476   2.70       512          Skinner Widget\n",
        "1   JDJ-32/V   9.36       967  Widget For Furtiveness\n",
        "2  YP4-325/J   5.17       787     Widget For Strawman\n",
        "3     VK-486   8.97       441       Manicurist Widget\n",
        "4    R4K-990  11.73       320         Infinite Widget\n",
        "5   MZ-556/B   2.35       948    Yellow-Tipped Widget\n",
        "6     QV-730   3.76        59       Unshakable Widget\n",
        "7    T1-9731   7.11       790   Self-Knowledge Widget\n",
        "8   5B-941/F  13.26       919       Widget For Cinema"
       ]
      }
     ],
     "prompt_number": 11
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Now that we've got a data frame, let's have some fun with it. In the cell below, write an expression that uses the `widgets_df` data frame object to calculate the total number of widgets that the factory has in its warehouse."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "widgets_df['quantity'].sum()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 12,
       "text": [
        "5743"
       ]
      }
     ],
     "prompt_number": 12
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "In the cell below, write an expression (or series of expressions) that evaluates to a data frame containing only those widgets whose price is above the mean price for all widgets."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "widgets_df[widgets_df['price'] > widgets_df['price'].mean()]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>partno</th>\n",
        "      <th>price</th>\n",
        "      <th>quantity</th>\n",
        "      <th>widgetname</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> JDJ-32/V</td>\n",
        "      <td>  9.36</td>\n",
        "      <td> 967</td>\n",
        "      <td> Widget For Furtiveness</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td>   VK-486</td>\n",
        "      <td>  8.97</td>\n",
        "      <td> 441</td>\n",
        "      <td>      Manicurist Widget</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td>  R4K-990</td>\n",
        "      <td> 11.73</td>\n",
        "      <td> 320</td>\n",
        "      <td>        Infinite Widget</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>8</th>\n",
        "      <td> 5B-941/F</td>\n",
        "      <td> 13.26</td>\n",
        "      <td> 919</td>\n",
        "      <td>      Widget For Cinema</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 13,
       "text": [
        "     partno  price  quantity              widgetname\n",
        "1  JDJ-32/V   9.36       967  Widget For Furtiveness\n",
        "3    VK-486   8.97       441       Manicurist Widget\n",
        "4   R4K-990  11.73       320         Infinite Widget\n",
        "8  5B-941/F  13.26       919       Widget For Cinema"
       ]
      }
     ],
     "prompt_number": 13
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "##Problem set #3: Sibling rivalries\n",
      "\n",
      "In the following problem set, you will yet again be working with the data in `widgets.html`. In order to accomplish the tasks in this problem set, you'll need to learn about Beautiful Soup's `.find_next_sibling()` method. Here's some information about that method, cribbed from the notes:\n",
      "\n",
      "Often, the tags we're looking for don't have a distinguishing characteristic, like a class attribute, that allows us to find them using `.find()` and `.find_all()`, and the tags also aren't in a parent-child relationship. This can be tricky! For example, take the following HTML snippet, (which I've assigned to a string called `example_html`):"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "example_html = \"\"\"\n",
      "<h2>Camembert</h2>\n",
      "<p>A soft cheese made in the Camembert region of France.</p>\n",
      "\n",
      "<h2>Cheddar</h2>\n",
      "<p>A yellow cheese made in the Cheddar region of... France, probably, idk whatevs.</p>\n",
      "\"\"\""
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 14
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "If our task was to create a dictionary that maps the name of the cheese to the description that follows in the `<p>` tag directly afterward, we'd be out of luck. Fortunately, Beautiful Soup has a `.find_next_sibling()` method, which allows us to search for the next tag that is a sibling of the tag you're calling it on (i.e., the two tags share a parent), that also matches particular criteria. So, for example, to accomplish the task outlined above:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "example_doc = BeautifulSoup(example_html)\n",
      "cheese_dict = {}\n",
      "for h2_tag in example_doc.find_all('h2'):\n",
      "    cheese_name = h2_tag.string\n",
      "    cheese_desc_tag = h2_tag.find_next_sibling('p')\n",
      "    cheese_dict[cheese_name] = cheese_desc_tag.string\n",
      "\n",
      "cheese_dict"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 15,
       "text": [
        "{u'Camembert': u'A soft cheese made in the Camembert region of France.',\n",
        " u'Cheddar': u'A yellow cheese made in the Cheddar region of... France, probably, idk whatevs.'}"
       ]
      }
     ],
     "prompt_number": 15
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "With that knowledge in mind, let's go back to our widgets. In the cell below, write code that uses Beautiful Soup, and in particular the `.find_next_sibling()` method, to find out how many widgets are in the table *just beneath* the header \"Hallowed Widgets.\" (You can tell by looking at the page that there are four such widgets. But this is a programming class, so we have to write a program to do it.)"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "hallowed_h3_tag = document.find_all('h3')[2]\n",
      "table_tag = hallowed_h3_tag.find_next_sibling('table')\n",
      "len(table_tag.find_all('tr'))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 23,
       "text": [
        "4"
       ]
      }
     ],
     "prompt_number": 23
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Okay, now, the final task. If you can accomplish this, you are truly an expert web scraper. I'll have little web scraper certificates made up and I'll give you one, if you manage to do this thing. And I know you can do it!\n",
      "\n",
      "In the cell below, I've created a variable `category_counts` and assigned to it an empty dictionary. Write code to populate this dictionary so that its keys are \"categories\" of widgets (e.g., the contents of the `<h3>` tags on the page: \"Forensic Widgets\", \"Mood widgets\", \"Hallowed Widgets\") and the value for each key is the number of widgets that occur in that category. I.e., after your code has been executed, the dictionary `category_counts` should look like this:\n",
      "\n",
      "    {u'Forensic Widgets': 3, u'Hallowed widgets': 4, u'Mood widgets': 2}\n"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "category_counts = {}\n",
      "# your code here\n",
      "\n",
      "for h3_tag in document.find_all('h3'):\n",
      "    table_tag = h3_tag.find_next_sibling('table')\n",
      "    category_counts[h3_tag.string] = len(table_tag.find_all('tr'))\n",
      "\n",
      "category_counts"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 24,
       "text": [
        "{u'Forensic Widgets': 3, u'Hallowed widgets': 4, u'Mood widgets': 2}"
       ]
      }
     ],
     "prompt_number": 24
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Congratulations! You're done."
     ]
    }
   ],
   "metadata": {}
  }
 ]
}