03_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data management\n",
    "\n",
    "In this notebook, we will work with the following:\n",
    "\n",
    "- Reading and cleaning data with `pandas`.\n",
    "- Web scraping.\n",
    "- API access.\n",
    "- Merging and querying with `pandas`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import urllib\n",
    "\n",
    "import pandas as pd\n",
    "from bs4 import BeautifulSoup\n",
    "from nytimesarticle import articleAPI"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading and cleaning data\n",
    "\n",
    "`pandas` supports a number of formats that we often find ourselves using.\n",
    "For example, I often use data in the Stata `dta` and SAS `sas7bdat` formats.\n",
    "In particular, if you find yourself putting full datasets from WRDS (especially the ones that are not accessible with web forms), you will end up using the SAS format.\n",
    "\n",
    "`pandas` also handles formats like Excel `xlsx`, comma separated values `csv` (and, indeed, nearly any delimited file), and fixed width data.\n",
    "The acquisition database, SDC Platinum, has a somewhat unreliable Excel output feature, and the `pandas` fixed width format reader takes nearly all of the pain out of reading in data exported that way.\n",
    "\n",
    "Note: `pandas` can also write many of the formats that it can read.\n",
    "A notable exception is `sas7bdat` because it is proprietary and undocumented.\n",
    "The reader was written with some clever reverse engineering, but writing a valid file is difficult and probably not coming in the future (see [Github issue](https://github.com/pandas-dev/pandas/issues/13031)).\n",
    "An easy workaround is using the SAS open format `xpt` or `csv`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>count_of_employees</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>74</td>\n",
       "      <td>Google</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  count_of_employees       name  year\n",
       "0                114  Microsoft  2016\n",
       "1                124  Microsoft  2017\n",
       "2                131  Microsoft  2018\n",
       "3                 72     Google  2016\n",
       "4                 74     Google  2017"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Stata data\n",
    "firmyear = pd.read_stata('./data/firmyear.dta')\n",
    "firmyear.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You are likely familiar with a number of data cleaning issues.\n",
    "However, you may not yet know how to map on what you know in another program to Python.\n",
    "The pandas documentation has a number of comparison references, including [R](https://pandas.pydata.org/pandas-docs/stable/comparison_with_r.html), [Stata](https://pandas.pydata.org/pandas-docs/stable/comparison_with_stata.html) and [SAS](https://pandas.pydata.org/pandas-docs/stable/comparison_with_sas.html).\n",
    "\n",
    "Some brief examples are below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count_of_employees    object\n",
       "name                  object\n",
       "year                  object\n",
       "dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firmyear.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "firmyear['year'] = firmyear['year'].astype('int')\n",
    "firmyear['count_of_employees'] = firmyear['count_of_employees'].astype('int')\n",
    "\n",
    "# Note, a more general version would be:\n",
    "# cols = firmyear.columns.drop('name')\n",
    "# firmyear[cols] = firmyear[cols].apply(pd.to_numeric, errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>count_of_employees</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>74</td>\n",
       "      <td>Google</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>76</td>\n",
       "      <td>Google</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_of_employees       name  year\n",
       "0                 114  Microsoft  2016\n",
       "1                 124  Microsoft  2017\n",
       "2                 131  Microsoft  2018\n",
       "3                  72     Google  2016\n",
       "4                  74     Google  2017\n",
       "5                  76     Google  2018"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firmyear"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count_of_employees     int64\n",
       "name                  object\n",
       "year                   int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firmyear.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# An example of using dictionaries.\n",
    "_COLUMNS = {\n",
    "    'count_of_employees': 'size_emp'\n",
    "}\n",
    "\n",
    "firmyear = firmyear.rename(columns=_COLUMNS)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>size_emp</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>74</td>\n",
       "      <td>Google</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>76</td>\n",
       "      <td>Google</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   size_emp       name  year\n",
       "0       114  Microsoft  2016\n",
       "1       124  Microsoft  2017\n",
       "2       131  Microsoft  2018\n",
       "3        72     Google  2016\n",
       "4        74     Google  2017\n",
       "5        76     Google  2018"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firmyear"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can do per-group things like calculating differences.\n",
    "firmyear['size_emp_change'] = firmyear.groupby(firmyear['name'])['size_emp'].diff()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>size_emp</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "      <th>size_emp_change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>74</td>\n",
       "      <td>Google</td>\n",
       "      <td>2017</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>76</td>\n",
       "      <td>Google</td>\n",
       "      <td>2018</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   size_emp       name  year  size_emp_change\n",
       "0       114  Microsoft  2016              NaN\n",
       "1       124  Microsoft  2017             10.0\n",
       "2       131  Microsoft  2018              7.0\n",
       "3        72     Google  2016              NaN\n",
       "4        74     Google  2017              2.0\n",
       "5        76     Google  2018              2.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firmyear"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Web scraping\n",
    "\n",
    "One helpful way of gathering text data is web scraping.\n",
    "We usually do this in three steps:\n",
    "\n",
    "1. Retrieve the pages with information we want.\n",
    "1. Extract the data from the pages.\n",
    "1. Clean and save the resulting data.\n",
    "\n",
    "Let's walk through an example of getting press releases from the [Microsoft website](https://news.microsoft.com/category/press-releases/).\n",
    "\n",
    "I often prefer to work out of order as follows:\n",
    "\n",
    "1. Figure out how to extract data from one page that has the data.\n",
    "1. Then, figure out how to automate getting the pages of interest.\n",
    "1. Run those pages through the procedure in step 1.\n",
    "1. Clean and save.\n",
    "\n",
    "This has the benefit of solving what is usually the hardest problem first."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Important note\n",
    "\n",
    "As you'll see, the difficulty ramps up a lot here.\n",
    "Web scraping is easily a full day topic on its own.\n",
    "Hence, I have two main goals for you:\n",
    "\n",
    "1. Get a sense of the logic and the process in solving the problem. This is a good start if you want to learn it yourself.\n",
    "1. Understand what is feasible and achievable. This helps whether you do it yourself or farm it out (and there's a ready talent pool for this)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read one page\n",
    "\n",
    "This is the hardest part."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "pr_url_1 = 'https://news.microsoft.com/2018/10/04/redline-communications-and-microsoft-announce-partnership-to-lower-the-cost-of-tv-white-space-solutions/'\n",
    "pr_req_1 = urllib.request.Request(pr_url_1)\n",
    "pr_page_1 = urllib.request.urlopen(pr_req_1).read()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "pr_soup_1 = BeautifulSoup(pr_page_1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<meta content=\"2018-10-04T13:00:35+00:00\" property=\"article:published_time\"/>"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pr_soup_1.find('meta', property='article:published_time')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'article:published_time'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pr_soup_1.find('meta', property='article:published_time')['property']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2018-10-04T13:00:35+00:00'"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pr_soup_1.find('meta', property='article:published_time')['content']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# List of meta tags to get.\n",
    "# Note: when in doubt, get everything you might possibly use.\n",
    "#       It's easier to drop stuff than to re-scrape everything.\n",
    "\n",
    "_METAS = [\n",
    "    'article:published_time',\n",
    "    'article:modified_time',\n",
    "    'og:title',\n",
    "    'og:description',\n",
    "    'og:updated_time',\n",
    "    'og:url',\n",
    "    'article:section'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "pr_data_1 = {}\n",
    "for meta in _METAS:\n",
    "    prop = pr_soup_1.find('meta', property=meta)['property']\n",
    "    content = pr_soup_1.find('meta', property=meta)['content']\n",
    "    pr_data_1.update({prop: content})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'article:published_time': '2018-10-04T13:00:35+00:00',\n",
       " 'article:modified_time': '2018-10-04T14:43:59+00:00',\n",
       " 'og:title': 'Redline Communications and Microsoft announce partnership to lower the cost of TV White Space solutions - Stories',\n",
       " 'og:description': 'The partnership will help make broadband more affordable and accessible for unserved communities in rural areas of the U.S. and globally REDMOND, Wash. — Oct. 4, 2018 — On Thursday, Redline Communications (TSX:RDL) and Microsoft Corp. announced a new partnership that will help address the rural broadband gap using TV White Space technology. Redline, a […ellipsis]',\n",
       " 'og:updated_time': '2018-10-04T14:43:59+00:00',\n",
       " 'og:url': 'https://news.microsoft.com/2018/10/04/redline-communications-and-microsoft-announce-partnership-to-lower-the-cost-of-tv-white-space-solutions/',\n",
       " 'article:section': 'Press Releases'}"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pr_data_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'The partnership will help make broadband more affordable and accessible for unserved communities in rural areas of the U.S. and globally'"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pr_soup_1.find('div', {'class': 'entry-content m-blog-content'}).find('h3').string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "pr_data_1['h3'] = pr_soup_1.find('div', \n",
    "                                 {'class': 'entry-content m-blog-content'}\n",
    "                                ).find('h3').string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'article:published_time': '2018-10-04T13:00:35+00:00',\n",
       " 'article:modified_time': '2018-10-04T14:43:59+00:00',\n",
       " 'og:title': 'Redline Communications and Microsoft announce partnership to lower the cost of TV White Space solutions - Stories',\n",
       " 'og:description': 'The partnership will help make broadband more affordable and accessible for unserved communities in rural areas of the U.S. and globally REDMOND, Wash. — Oct. 4, 2018 — On Thursday, Redline Communications (TSX:RDL) and Microsoft Corp. announced a new partnership that will help address the rural broadband gap using TV White Space technology. Redline, a […ellipsis]',\n",
       " 'og:updated_time': '2018-10-04T14:43:59+00:00',\n",
       " 'og:url': 'https://news.microsoft.com/2018/10/04/redline-communications-and-microsoft-announce-partnership-to-lower-the-cost-of-tv-white-space-solutions/',\n",
       " 'article:section': 'Press Releases',\n",
       " 'h3': 'The partnership will help make broadband more affordable and accessible for unserved communities in rural areas of the U.S. and globally'}"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pr_data_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<p><strong>REDMOND, Wash. </strong><strong>—</strong> <strong>Oct. 4, 2018</strong> <strong>—</strong> On Thursday, <a href=\"https://rdlcom.com/\">Redline Communications</a> (TSX:RDL) and <a href=\"https://www.microsoft.com/en-us/\">Microsoft Corp.</a> announced a new partnership that will help address the rural broadband gap using TV White Space technology. Redline, a leader in private wireless networks, will provide its Virtual Fiber™ radio technology in the TV White Space band to Microsoft Airband Initiative partners. Together, Redline and Microsoft’s partnership will help make broadband internet more affordable and accessible to unserved and underserved customers in rural areas in the United States and globally.</p>,\n",
       " <p>New cloud services and other technologies make broadband connectivity a necessity to start and grow a small business and to take advantage of advances in agriculture, telemedicine and education. It is a vital part of 21st century infrastructure. Yet, more than 19.4 million Americans living in rural areas lack access to broadband and miss out on the opportunities that connectivity provides. According to a study from <a href=\"https://blogs.microsoft.com/uploads/2017/07/Rural-Broadband-Strategy-Microsoft-Whitepaper-FINAL-7-10-17.pdf\">Boston Consulting Group</a>, a connectivity model that uses a combination of technologies, including TV White Space, can reduce the cost of extending broadband coverage in rural communities. TV White Space is an important part of the solution, creating broadband connections in UHF bands and enabling communication in challenging rural terrains and highly vegetated areas, all while protecting broadcasters and other licensees from harmful interference.</p>,\n",
       " <p>This partnership is part of Microsoft’s <a href=\"https://news.microsoft.com/rural-broadband/\">Airband Initiative</a>, which aims to close the broadband gap by extending access to 2 million unserved people in rural America by 2022, using a mix of technology solutions, including TV White Space.</p>,\n",
       " <p>“Our work with Redline will increase the availability of competitively priced TV White Space technology, enabling internet service providers (ISPs) to provide access to customers at an affordable price point,” said Paul Garnett, senior director of the Microsoft Airband Initiative. “This availability and utilization of TV White Space is absolutely critical to closing the broadband gap. This partnership will bring rapid evolution to the technology, making a real impact on real lives.”</p>,\n",
       " <p>“With its Virtual Fiber™ technology, Redline has been leading the TV White Space market and has been active in digital divide projects for almost a decade,” said Rob Williams, CEO at Redline. “In discussions with Microsoft, we realized that we shared the same vision for approaching the rural broadband gap, and we each possessed critical components to the solution. This partnership with Microsoft will help us address the digital divide more effectively in the U.S. and around the world.”</p>,\n",
       " <p>In addition to the partnerships with companies like Redline, Microsoft’s <a href=\"https://news.microsoft.com/rural-broadband/\">Airband Initiative</a> invests in partnerships with ISPs and other telecommunications companies, introduces innovative solutions for rural connectivity, and provides digital skills training for people in newly connected communities.</p>,\n",
       " <p><strong>About Redline Communications</strong></p>,\n",
       " <p>Redline Communications (TSX:RDL) designs and manufactures powerful wide-area wireless networks for mission-critical applications in challenging locations. Redline networks are used by Oil &amp; Gas companies onshore and offshore, Mining companies on surface and underground operations, by municipalities to remotely monitor infrastructure, and by specialized telecom service providers to deliver premium services. Thousands of businesses worldwide rely on Redline to engineer, plan and deliver ruggedized, secure and reliable networks for their IoT, voice, data and video communications needs. For more information visit <a href=\"http://www.rdlcom.com/\">www.rdlcom.com</a>.</p>,\n",
       " <p><strong>About Microsoft</strong></p>,\n",
       " <p>Microsoft (Nasdaq “MSFT” @microsoft) enables digital transformation for the era of an intelligent cloud and an intelligent edge. Its mission is to empower every person and every organization on the planet to achieve more.</p>,\n",
       " <p><strong>For more information, press only:<br/>\n",
       " </strong>Microsoft Media Relations, WE Communications for Microsoft, (425) 638-7777,</p>,\n",
       " <p>rrt@we-worldwide.com</p>,\n",
       " <p>Louis Lambert, Senior VP Business Development and Marketing for Redline,</p>,\n",
       " <p>+1 (905) 479-8344, <a href=\"mailto:llambert@rdlcom.com\">llambert@rdlcom.com</a></p>,\n",
       " <p>Cory Pala, Investor Relations for Redline, +1 (416) 657-2400, <a href=\"mailto:cpala@rdlcom.com\">cpala@rdlcom.com</a></p>,\n",
       " <p><em>Note to editors</em><em>:</em> For more information, news and perspectives from Microsoft, please visit the Microsoft News Center at <a href=\"http://news.microsoft.com/\">http://news.microsoft.com</a>. Web links, telephone numbers and titles were correct at time of publication, but may have changed. For additional assistance, journalists and analysts may contact Microsoft’s Rapid Response Team or other appropriate contacts listed at <a href=\"http://news.microsoft.com/microsoft-public-relations-contacts/\">http://news.microsoft.com/microsoft-public-relations-contacts</a>.</p>,\n",
       " <p> </p>]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pr_soup_1.find('div', \n",
    "               {'class': 'entry-content m-blog-content'}\n",
    "              ).find_all('p')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is a little gnarly.\n",
    "pr_data_1['body'] = '\\n\\n'.join(\n",
    "                        [i.text for i in pr_soup_1.find(\n",
    "                            'div', \n",
    "                            {'class': 'entry-content m-blog-content'}\n",
    "                            ).find_all('p')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'article:published_time': '2018-10-04T13:00:35+00:00',\n",
       " 'article:modified_time': '2018-10-04T14:43:59+00:00',\n",
       " 'og:title': 'Redline Communications and Microsoft announce partnership to lower the cost of TV White Space solutions - Stories',\n",
       " 'og:description': 'The partnership will help make broadband more affordable and accessible for unserved communities in rural areas of the U.S. and globally REDMOND, Wash. — Oct. 4, 2018 — On Thursday, Redline Communications (TSX:RDL) and Microsoft Corp. announced a new partnership that will help address the rural broadband gap using TV White Space technology. Redline, a […ellipsis]',\n",
       " 'og:updated_time': '2018-10-04T14:43:59+00:00',\n",
       " 'og:url': 'https://news.microsoft.com/2018/10/04/redline-communications-and-microsoft-announce-partnership-to-lower-the-cost-of-tv-white-space-solutions/',\n",
       " 'article:section': 'Press Releases',\n",
       " 'h3': 'The partnership will help make broadband more affordable and accessible for unserved communities in rural areas of the U.S. and globally',\n",
       " 'body': 'REDMOND, Wash. — Oct. 4, 2018 — On Thursday, Redline Communications (TSX:RDL) and Microsoft Corp. announced a new partnership that will help address the rural broadband gap using TV White Space technology. Redline, a leader in private wireless networks, will provide its Virtual Fiber™ radio technology in the TV White Space band to Microsoft Airband Initiative partners. Together, Redline and Microsoft’s partnership will help make broadband internet more affordable and accessible to unserved and underserved customers in rural areas in the United States and globally.\\n\\nNew cloud services and other technologies make broadband connectivity a necessity to start and grow a small business and to take advantage of advances in agriculture, telemedicine and education. It is a vital part of 21st century infrastructure. Yet, more than 19.4 million Americans living in rural areas lack access to broadband and miss out on the opportunities that connectivity provides. According to a study from Boston Consulting Group, a connectivity model that uses a combination of technologies, including TV White Space, can reduce the cost of extending broadband coverage in rural communities. TV White Space is an important part of the solution, creating broadband connections in UHF bands and enabling communication in challenging rural terrains and highly vegetated areas, all while protecting broadcasters and other licensees from harmful interference.\\n\\nThis partnership is part of Microsoft’s Airband Initiative, which aims to close the broadband gap by extending access to 2 million unserved people in rural America by 2022, using a mix of technology solutions, including TV White Space.\\n\\n“Our work with Redline will increase the availability of competitively priced TV White Space technology, enabling internet service providers (ISPs) to provide access to customers at an affordable price point,” said Paul Garnett, senior director of the Microsoft Airband Initiative. “This availability and utilization of TV White Space is absolutely critical to closing the broadband gap. This partnership will bring rapid evolution to the technology, making a real impact on real lives.”\\n\\n“With its Virtual Fiber™ technology, Redline has been leading the TV White Space market and has been active in digital divide projects for almost a decade,” said Rob Williams, CEO at Redline.\\xa0“In discussions with Microsoft, we realized that we shared the same vision for approaching the rural broadband gap, and we each possessed critical components to the solution. This partnership with Microsoft will help us address the digital divide more effectively in the U.S. and around the world.”\\n\\nIn addition to the partnerships with companies like Redline, Microsoft’s\\xa0Airband Initiative\\xa0invests in partnerships with ISPs and other telecommunications companies, introduces innovative solutions for rural connectivity, and provides digital skills training for people in newly connected communities.\\n\\nAbout Redline Communications\\n\\nRedline Communications (TSX:RDL) designs and manufactures powerful wide-area wireless networks for mission-critical applications in challenging locations. Redline networks are used by Oil & Gas companies onshore and offshore, Mining companies on surface and underground operations, by municipalities to remotely monitor infrastructure, and by specialized telecom service providers to deliver premium services. Thousands of businesses worldwide rely on Redline to engineer, plan and deliver ruggedized, secure and reliable networks for their IoT, voice, data and video communications needs. For more information visit\\xa0www.rdlcom.com.\\n\\nAbout Microsoft\\n\\nMicrosoft (Nasdaq “MSFT” @microsoft) enables digital transformation for the era of an intelligent cloud and an intelligent edge. Its mission is to empower every person and every organization on the planet to achieve more.\\n\\nFor more information, press only:\\nMicrosoft Media Relations, WE Communications for Microsoft, (425) 638-7777,\\n\\nrrt@we-worldwide.com\\n\\nLouis Lambert, Senior VP\\u202fBusiness Development\\u202fand Marketing for Redline,\\n\\n+1 (905) 479-8344, llambert@rdlcom.com\\n\\nCory Pala, Investor Relations for Redline, +1 (416) 657-2400, cpala@rdlcom.com\\n\\nNote to editors: For more information, news and perspectives from Microsoft, please visit the Microsoft News Center at http://news.microsoft.com. Web links, telephone numbers and titles were correct at time of publication, but may have changed. For additional assistance, journalists and analysts may contact Microsoft’s Rapid Response Team or other appropriate contacts listed at http://news.microsoft.com/microsoft-public-relations-contacts.\\n\\n\\xa0'}"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pr_data_1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Automate our one page work.\n",
    "\n",
    "This is fairly easy. We have the code for it already.\n",
    "We just need to wrap it in a function.\n",
    "\n",
    "**Note:** I'm using an `if` statement to check whether these properties exist, and guarding against the case where they don't.\n",
    "I did this iteratively while building this content, because I noticed (from errors) that many press releases do noe have modification dates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_data_from_soup(soup):\n",
    "    data = {}\n",
    "    for meta in _METAS:\n",
    "        if soup.find('meta', property=meta) is not None:\n",
    "            prop = soup.find('meta', property=meta)['property']\n",
    "        if soup.find('meta', property=meta) is not None:\n",
    "            content = soup.find('meta', property=meta)['content']\n",
    "        if prop is not None and content is not None:\n",
    "            data.update({prop: content})\n",
    "    try:\n",
    "        data['h3'] = soup.find('div', \n",
    "                               {'class': 'entry-content m-blog-content'}\n",
    "                              ).find('h3').string\n",
    "    except AttributeError:\n",
    "        data['h3'] = ''\n",
    "    \n",
    "    data['body'] = '\\n\\n'.join(\n",
    "                        [i.text for i in soup.find(\n",
    "                            'div', \n",
    "                            {'class': 'entry-content m-blog-content'}\n",
    "                            ).find_all('p')])\n",
    "    \n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'article:published_time': '2018-10-04T13:00:35+00:00',\n",
       " 'article:modified_time': '2018-10-04T14:43:59+00:00',\n",
       " 'og:title': 'Redline Communications and Microsoft announce partnership to lower the cost of TV White Space solutions - Stories',\n",
       " 'og:description': 'The partnership will help make broadband more affordable and accessible for unserved communities in rural areas of the U.S. and globally REDMOND, Wash. — Oct. 4, 2018 — On Thursday, Redline Communications (TSX:RDL) and Microsoft Corp. announced a new partnership that will help address the rural broadband gap using TV White Space technology. Redline, a […ellipsis]',\n",
       " 'og:updated_time': '2018-10-04T14:43:59+00:00',\n",
       " 'og:url': 'https://news.microsoft.com/2018/10/04/redline-communications-and-microsoft-announce-partnership-to-lower-the-cost-of-tv-white-space-solutions/',\n",
       " 'article:section': 'Press Releases',\n",
       " 'h3': 'The partnership will help make broadband more affordable and accessible for unserved communities in rural areas of the U.S. and globally',\n",
       " 'body': 'REDMOND, Wash. — Oct. 4, 2018 — On Thursday, Redline Communications (TSX:RDL) and Microsoft Corp. announced a new partnership that will help address the rural broadband gap using TV White Space technology. Redline, a leader in private wireless networks, will provide its Virtual Fiber™ radio technology in the TV White Space band to Microsoft Airband Initiative partners. Together, Redline and Microsoft’s partnership will help make broadband internet more affordable and accessible to unserved and underserved customers in rural areas in the United States and globally.\\n\\nNew cloud services and other technologies make broadband connectivity a necessity to start and grow a small business and to take advantage of advances in agriculture, telemedicine and education. It is a vital part of 21st century infrastructure. Yet, more than 19.4 million Americans living in rural areas lack access to broadband and miss out on the opportunities that connectivity provides. According to a study from Boston Consulting Group, a connectivity model that uses a combination of technologies, including TV White Space, can reduce the cost of extending broadband coverage in rural communities. TV White Space is an important part of the solution, creating broadband connections in UHF bands and enabling communication in challenging rural terrains and highly vegetated areas, all while protecting broadcasters and other licensees from harmful interference.\\n\\nThis partnership is part of Microsoft’s Airband Initiative, which aims to close the broadband gap by extending access to 2 million unserved people in rural America by 2022, using a mix of technology solutions, including TV White Space.\\n\\n“Our work with Redline will increase the availability of competitively priced TV White Space technology, enabling internet service providers (ISPs) to provide access to customers at an affordable price point,” said Paul Garnett, senior director of the Microsoft Airband Initiative. “This availability and utilization of TV White Space is absolutely critical to closing the broadband gap. This partnership will bring rapid evolution to the technology, making a real impact on real lives.”\\n\\n“With its Virtual Fiber™ technology, Redline has been leading the TV White Space market and has been active in digital divide projects for almost a decade,” said Rob Williams, CEO at Redline.\\xa0“In discussions with Microsoft, we realized that we shared the same vision for approaching the rural broadband gap, and we each possessed critical components to the solution. This partnership with Microsoft will help us address the digital divide more effectively in the U.S. and around the world.”\\n\\nIn addition to the partnerships with companies like Redline, Microsoft’s\\xa0Airband Initiative\\xa0invests in partnerships with ISPs and other telecommunications companies, introduces innovative solutions for rural connectivity, and provides digital skills training for people in newly connected communities.\\n\\nAbout Redline Communications\\n\\nRedline Communications (TSX:RDL) designs and manufactures powerful wide-area wireless networks for mission-critical applications in challenging locations. Redline networks are used by Oil & Gas companies onshore and offshore, Mining companies on surface and underground operations, by municipalities to remotely monitor infrastructure, and by specialized telecom service providers to deliver premium services. Thousands of businesses worldwide rely on Redline to engineer, plan and deliver ruggedized, secure and reliable networks for their IoT, voice, data and video communications needs. For more information visit\\xa0www.rdlcom.com.\\n\\nAbout Microsoft\\n\\nMicrosoft (Nasdaq “MSFT” @microsoft) enables digital transformation for the era of an intelligent cloud and an intelligent edge. Its mission is to empower every person and every organization on the planet to achieve more.\\n\\nFor more information, press only:\\nMicrosoft Media Relations, WE Communications for Microsoft, (425) 638-7777,\\n\\nrrt@we-worldwide.com\\n\\nLouis Lambert, Senior VP\\u202fBusiness Development\\u202fand Marketing for Redline,\\n\\n+1 (905) 479-8344, llambert@rdlcom.com\\n\\nCory Pala, Investor Relations for Redline, +1 (416) 657-2400, cpala@rdlcom.com\\n\\nNote to editors: For more information, news and perspectives from Microsoft, please visit the Microsoft News Center at http://news.microsoft.com. Web links, telephone numbers and titles were correct at time of publication, but may have changed. For additional assistance, journalists and analysts may contact Microsoft’s Rapid Response Team or other appropriate contacts listed at http://news.microsoft.com/microsoft-public-relations-contacts.\\n\\n\\xa0'}"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Notice how easy this is once we make a function.\n",
    "get_data_from_soup(pr_soup_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read many pages\n",
    "\n",
    "Now we need to get the URLs for all of the pages we want."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "many_pr_url_1 = 'https://news.microsoft.com/category/press-releases/'\n",
    "many_pr_req_1 = urllib.request.Request(many_pr_url_1)\n",
    "many_pr_page_1 = urllib.request.urlopen(many_pr_req_1).read()\n",
    "many_pr_soup_1 = BeautifulSoup(many_pr_page_1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<a class=\"f-post-link c-heading-6 m-chevron\" href=\"https://news.microsoft.com/2018/10/11/microsoft-announces-quarterly-earnings-release-date-37/\" ms.title=\"Microsoft announces quarterly earnings release date\" rel=\"bookmark\">\n",
       " \t\tMicrosoft announces quarterly earnings release date\t</a>,\n",
       " <a class=\"f-post-link c-heading-6 m-chevron\" href=\"https://news.microsoft.com/2018/10/11/indiana-governor-eric-j-holcomb-the-markle-foundation-and-microsoft-announce-skillful-indiana-bringing-new-investment-partners-and-innovation-to-indianas-next-level-workforce-agenda/\" ms.title=\"Indiana Governor Eric J. Holcomb, the Markle Foundation and Microsoft announce Skillful Indiana: Bringing new investment, partners and innovation to Indiana’s next level workforce agenda\" rel=\"bookmark\">\n",
       " \t\tIndiana Governor Eric J. Holcomb, the Markle Foundation and Microsoft announce Skillful Indiana: Bringing new investment, partners and innovation to Indiana’s next level workforce agenda\t</a>,\n",
       " <a class=\"f-post-link c-heading-6 m-chevron\" href=\"https://news.microsoft.com/2018/10/11/anil-kumbles-microsoft-ai-enabled-power-bat-partners-with-star-india-to-revolutionize-cricketing-experience-for-players-and-fans/\" ms.title=\"Anil Kumble’s Microsoft AI-enabled Power Bat partners with Star India to revolutionize cricketing experience for players and fans\" rel=\"bookmark\">\n",
       " \t\tAnil Kumble’s Microsoft AI-enabled Power Bat partners with Star India to revolutionize cricketing experience for players and fans\t</a>,\n",
       " <a class=\"f-post-link c-heading-6 m-chevron\" href=\"https://news.microsoft.com/2018/10/10/microsoft-and-lake-country-satellite-roll-out-free-wi-fi-in-boydton-virginia/\" ms.title=\"Microsoft and Lake Country Satellite roll out free Wi-Fi in Boydton, Virginia\" rel=\"bookmark\">\n",
       " \t\tMicrosoft and Lake Country Satellite roll out free Wi-Fi in Boydton, Virginia\t</a>,\n",
       " <a class=\"f-post-link c-heading-6 m-chevron\" href=\"https://news.microsoft.com/2018/10/10/blackbaud-and-microsoft-expand-partnership-with-an-integrated-cloud-initiative-for-nonprofits/\" ms.title=\"Blackbaud and Microsoft expand partnership with an integrated cloud initiative for nonprofits\" rel=\"bookmark\">\n",
       " \t\tBlackbaud and Microsoft expand partnership with an integrated cloud initiative for nonprofits\t</a>,\n",
       " <a class=\"f-post-link c-heading-6 m-chevron\" href=\"https://news.microsoft.com/2018/10/08/grab-forges-strategic-cloud-partnership-with-microsoft-to-drive-innovation-and-adoption-of-digital-services-across-southeast-asia/\" ms.title=\"Grab forges strategic cloud partnership with Microsoft to drive innovation and adoption of digital services across Southeast Asia\" rel=\"bookmark\">\n",
       " \t\tGrab forges strategic cloud partnership with Microsoft to drive innovation and adoption of digital services across Southeast Asia\t</a>,\n",
       " <a class=\"f-post-link c-heading-6 m-chevron\" href=\"https://news.microsoft.com/2018/10/04/redline-communications-and-microsoft-announce-partnership-to-lower-the-cost-of-tv-white-space-solutions/\" ms.title=\"Redline Communications and Microsoft announce partnership to lower the cost of TV White Space solutions\" rel=\"bookmark\">\n",
       " \t\tRedline Communications and Microsoft announce partnership to lower the cost of TV White Space solutions\t</a>,\n",
       " <a class=\"f-post-link c-heading-6 m-chevron\" href=\"https://news.microsoft.com/2018/09/24/adobe-microsoft-and-sap-announce-the-open-data-initiative-to-empower-a-new-generation-of-customer-experiences/\" ms.title=\"Adobe, Microsoft and SAP announce the Open Data Initiative to empower a new generation of customer experiences\" rel=\"bookmark\">\n",
       " \t\tAdobe, Microsoft and SAP announce the Open Data Initiative to empower a new generation of customer experiences\t</a>,\n",
       " <a class=\"f-post-link c-heading-6 m-chevron\" href=\"https://news.microsoft.com/2018/09/24/microsoft-fortifies-security-and-brings-ai-to-the-masses-at-ignite-2018/\" ms.title=\"Microsoft fortifies security and brings AI to the masses at Ignite 2018\" rel=\"bookmark\">\n",
       " \t\tMicrosoft fortifies security and brings AI to the masses at Ignite 2018\t</a>,\n",
       " <a class=\"f-post-link c-heading-6 m-chevron\" href=\"https://news.microsoft.com/2018/09/20/shell-expands-strategic-collaboration-with-microsoft-to-drive-industry-transformation-and-innovation/\" ms.title=\"Shell expands strategic collaboration with Microsoft to drive industry transformation and innovation\" rel=\"bookmark\">\n",
       " \t\tShell expands strategic collaboration with Microsoft to drive industry transformation and innovation\t</a>,\n",
       " <a class=\"f-post-link c-heading-6 m-chevron\" href=\"https://news.microsoft.com/2018/09/18/microsoft-announces-quarterly-dividend-increase/\" ms.title=\"Microsoft announces quarterly dividend increase\" rel=\"bookmark\">\n",
       " \t\tMicrosoft announces quarterly dividend increase\t</a>,\n",
       " <a class=\"f-post-link c-heading-6 m-chevron\" href=\"https://news.microsoft.com/2018/09/13/network-business-systems-and-microsoft-announce-agreement-to-deliver-broadband-internet-to-rural-communities-in-illinois-iowa-and-south-dakota/\" ms.title=\"Network Business Systems and Microsoft announce agreement to deliver broadband internet to rural communities in Illinois, Iowa and South Dakota\" rel=\"bookmark\">\n",
       " \t\tNetwork Business Systems and Microsoft announce agreement to deliver broadband internet to rural communities in Illinois, Iowa and South Dakota\t</a>,\n",
       " <a aria-label=\"Go to page 2 of results\" href=\"https://news.microsoft.com/category/press-releases/page/2/\" ms.title=\"2\">2</a>,\n",
       " <a aria-label=\"Go to page 3 of results\" href=\"https://news.microsoft.com/category/press-releases/page/3/\" ms.title=\"3\">3</a>,\n",
       " <a aria-label=\"Go to page 835 of results\" href=\"https://news.microsoft.com/category/press-releases/page/835/\" ms.title=\"835\">835</a>,\n",
       " <a aria-label=\"Go to next page\" class=\"c-glyph\" href=\"/category/press-releases/?paged=2\" ms.title=\"Next Page\">Next</a>]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Almost, but note the ones at the bottom.\n",
    "many_pr_soup_1.find('section', id='primary').find_all('a')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['https://news.microsoft.com/2018/10/11/microsoft-announces-quarterly-earnings-release-date-37/',\n",
       " 'https://news.microsoft.com/2018/10/11/indiana-governor-eric-j-holcomb-the-markle-foundation-and-microsoft-announce-skillful-indiana-bringing-new-investment-partners-and-innovation-to-indianas-next-level-workforce-agenda/',\n",
       " 'https://news.microsoft.com/2018/10/11/anil-kumbles-microsoft-ai-enabled-power-bat-partners-with-star-india-to-revolutionize-cricketing-experience-for-players-and-fans/',\n",
       " 'https://news.microsoft.com/2018/10/10/microsoft-and-lake-country-satellite-roll-out-free-wi-fi-in-boydton-virginia/',\n",
       " 'https://news.microsoft.com/2018/10/10/blackbaud-and-microsoft-expand-partnership-with-an-integrated-cloud-initiative-for-nonprofits/',\n",
       " 'https://news.microsoft.com/2018/10/08/grab-forges-strategic-cloud-partnership-with-microsoft-to-drive-innovation-and-adoption-of-digital-services-across-southeast-asia/',\n",
       " 'https://news.microsoft.com/2018/10/04/redline-communications-and-microsoft-announce-partnership-to-lower-the-cost-of-tv-white-space-solutions/',\n",
       " 'https://news.microsoft.com/2018/09/24/adobe-microsoft-and-sap-announce-the-open-data-initiative-to-empower-a-new-generation-of-customer-experiences/',\n",
       " 'https://news.microsoft.com/2018/09/24/microsoft-fortifies-security-and-brings-ai-to-the-masses-at-ignite-2018/',\n",
       " 'https://news.microsoft.com/2018/09/20/shell-expands-strategic-collaboration-with-microsoft-to-drive-industry-transformation-and-innovation/',\n",
       " 'https://news.microsoft.com/2018/09/18/microsoft-announces-quarterly-dividend-increase/',\n",
       " 'https://news.microsoft.com/2018/09/13/network-business-systems-and-microsoft-announce-agreement-to-deliver-broadband-internet-to-rural-communities-in-illinois-iowa-and-south-dakota/']"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Almost, but note the ones at the bottom.\n",
    "articles = many_pr_soup_1.find('section', id='primary').find_all('article')\n",
    "links = [i.find('a')['href'] for i in articles]\n",
    "links"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "many_pr_links_1 = links.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Automate getting links and data from each"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We need to turn links into soup objects a lot, so let's make a function.\n",
    "def link_to_soup(link):\n",
    "    req = urllib.request.Request(link)\n",
    "    page = urllib.request.urlopen(req).read()\n",
    "    soup = BeautifulSoup(page)\n",
    "    return soup\n",
    "    \n",
    "def get_links_from_link_page(link_page):\n",
    "    soup = link_to_soup(link_page)\n",
    "    articles = soup.find('section', id='primary').find_all('article')\n",
    "    links = [i.find('a')['href'] for i in articles]\n",
    "    return links\n",
    "\n",
    "def get_data_from_links(links):\n",
    "    data_list = []\n",
    "    for link in links:\n",
    "        soup = link_to_soup(link)\n",
    "        data_list.append(get_data_from_soup(soup))\n",
    "        \n",
    "    return data_list\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>article:modified_time</th>\n",
       "      <th>article:published_time</th>\n",
       "      <th>article:section</th>\n",
       "      <th>body</th>\n",
       "      <th>h3</th>\n",
       "      <th>og:description</th>\n",
       "      <th>og:title</th>\n",
       "      <th>og:updated_time</th>\n",
       "      <th>og:url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-10-12T00:36:34+00:00</td>\n",
       "      <td>2018-10-11T23:16:52+00:00</td>\n",
       "      <td>Press Releases</td>\n",
       "      <td>REDMOND, Wash. — Oct. 11, 2018 — Microsoft Cor...</td>\n",
       "      <td>Microsoft to host earnings conference call web...</td>\n",
       "      <td>Microsoft to host earnings conference call web...</td>\n",
       "      <td>Microsoft announces quarterly earnings release...</td>\n",
       "      <td>2018-10-12T00:36:34+00:00</td>\n",
       "      <td>https://news.microsoft.com/2018/10/11/microsof...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-10-11T16:36:30+00:00</td>\n",
       "      <td>2018-10-11T14:00:25+00:00</td>\n",
       "      <td>Press Releases</td>\n",
       "      <td>INDIANAPOLIS – Oct. 11, 2018 – Today, Eric J. ...</td>\n",
       "      <td>With support provided by Walmart, and in partn...</td>\n",
       "      <td>With support provided by Walmart, and in partn...</td>\n",
       "      <td>Indiana Governor Eric J. Holcomb, the Markle F...</td>\n",
       "      <td>2018-10-11T16:36:30+00:00</td>\n",
       "      <td>https://news.microsoft.com/2018/10/11/indiana-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-10-11T07:05:46+00:00</td>\n",
       "      <td>2018-10-11T07:04:28+00:00</td>\n",
       "      <td>Press Releases</td>\n",
       "      <td>MUMBAI, India — Oct. 11, 2018 — Microsoft Corp...</td>\n",
       "      <td>None</td>\n",
       "      <td>Microsoft, Star India and Anil Kumble redefine...</td>\n",
       "      <td>Anil Kumble’s Microsoft AI-enabled Power Bat p...</td>\n",
       "      <td>2018-10-11T07:05:46+00:00</td>\n",
       "      <td>https://news.microsoft.com/2018/10/11/anil-kum...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-10-11T20:29:31+00:00</td>\n",
       "      <td>2018-10-10T18:00:22+00:00</td>\n",
       "      <td>Press Releases</td>\n",
       "      <td>BOYDTON, Va. — Oct. 10, 2018 — Microsoft Corp....</td>\n",
       "      <td></td>\n",
       "      <td>BOYDTON, Va. — Oct. 10, 2018 — Microsoft Corp....</td>\n",
       "      <td>Microsoft and Lake Country Satellite roll out ...</td>\n",
       "      <td>2018-10-11T20:29:31+00:00</td>\n",
       "      <td>https://news.microsoft.com/2018/10/10/microsof...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-10-10T15:15:58+00:00</td>\n",
       "      <td>Press Releases</td>\n",
       "      <td>Charleston, S.C. (October 10, 2018) — Blackbau...</td>\n",
       "      <td>Introduce first phase of initiative with joint...</td>\n",
       "      <td>Introduce first phase of initiative with joint...</td>\n",
       "      <td>Blackbaud and Microsoft expand partnership wit...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://news.microsoft.com/2018/10/10/blackbau...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       article:modified_time     article:published_time article:section  \\\n",
       "0  2018-10-12T00:36:34+00:00  2018-10-11T23:16:52+00:00  Press Releases   \n",
       "1  2018-10-11T16:36:30+00:00  2018-10-11T14:00:25+00:00  Press Releases   \n",
       "2  2018-10-11T07:05:46+00:00  2018-10-11T07:04:28+00:00  Press Releases   \n",
       "3  2018-10-11T20:29:31+00:00  2018-10-10T18:00:22+00:00  Press Releases   \n",
       "4                        NaN  2018-10-10T15:15:58+00:00  Press Releases   \n",
       "\n",
       "                                                body  \\\n",
       "0  REDMOND, Wash. — Oct. 11, 2018 — Microsoft Cor...   \n",
       "1  INDIANAPOLIS – Oct. 11, 2018 – Today, Eric J. ...   \n",
       "2  MUMBAI, India — Oct. 11, 2018 — Microsoft Corp...   \n",
       "3  BOYDTON, Va. — Oct. 10, 2018 — Microsoft Corp....   \n",
       "4  Charleston, S.C. (October 10, 2018) — Blackbau...   \n",
       "\n",
       "                                                  h3  \\\n",
       "0  Microsoft to host earnings conference call web...   \n",
       "1  With support provided by Walmart, and in partn...   \n",
       "2                                               None   \n",
       "3                                                      \n",
       "4  Introduce first phase of initiative with joint...   \n",
       "\n",
       "                                      og:description  \\\n",
       "0  Microsoft to host earnings conference call web...   \n",
       "1  With support provided by Walmart, and in partn...   \n",
       "2  Microsoft, Star India and Anil Kumble redefine...   \n",
       "3  BOYDTON, Va. — Oct. 10, 2018 — Microsoft Corp....   \n",
       "4  Introduce first phase of initiative with joint...   \n",
       "\n",
       "                                            og:title  \\\n",
       "0  Microsoft announces quarterly earnings release...   \n",
       "1  Indiana Governor Eric J. Holcomb, the Markle F...   \n",
       "2  Anil Kumble’s Microsoft AI-enabled Power Bat p...   \n",
       "3  Microsoft and Lake Country Satellite roll out ...   \n",
       "4  Blackbaud and Microsoft expand partnership wit...   \n",
       "\n",
       "             og:updated_time  \\\n",
       "0  2018-10-12T00:36:34+00:00   \n",
       "1  2018-10-11T16:36:30+00:00   \n",
       "2  2018-10-11T07:05:46+00:00   \n",
       "3  2018-10-11T20:29:31+00:00   \n",
       "4                        NaN   \n",
       "\n",
       "                                              og:url  \n",
       "0  https://news.microsoft.com/2018/10/11/microsof...  \n",
       "1  https://news.microsoft.com/2018/10/11/indiana-...  \n",
       "2  https://news.microsoft.com/2018/10/11/anil-kum...  \n",
       "3  https://news.microsoft.com/2018/10/10/microsof...  \n",
       "4  https://news.microsoft.com/2018/10/10/blackbau...  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "msft_prs = pd.DataFrame(get_data_from_links(many_pr_links_1))\n",
    "msft_prs.to_csv('./data/msft_prs.csv', index=False)\n",
    "msft_prs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note**: for running time reasons, we're not going to make a multi-links-page version, but note that there's a next page link at the bottom of those pages that can be extracted to build that:\n",
    "\n",
    "```html\n",
    "<a href=\"/category/press-releases/page/2/?paged=3\" \n",
    "   class=\"c-glyph x-hidden-focus\" \n",
    "   aria-label=\"Go to next page\" ms.title=\"Next Page\">\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# APIs\n",
    "\n",
    "As we're about to see, it's really nice when sites help us out.\n",
    "\n",
    "We'll be using the New York Times API, and you'll need a key to use it. You can get one [here](https://developer.nytimes.com/signup), and it will show up by email (very quickly)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# You will replace REDACTED with the key you got in email.\n",
    "# It's probably obvious, but don't share it.\n",
    "nyt_api = articleAPI('REDACTED')\n",
    "\n",
    "msft_articles = nyt_api.search(q='Microsoft', begin_date=20180801)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>_id</th>\n",
       "      <th>byline.organization</th>\n",
       "      <th>byline.original</th>\n",
       "      <th>byline.person</th>\n",
       "      <th>document_type</th>\n",
       "      <th>headline.content_kicker</th>\n",
       "      <th>headline.kicker</th>\n",
       "      <th>headline.main</th>\n",
       "      <th>headline.name</th>\n",
       "      <th>headline.print_headline</th>\n",
       "      <th>...</th>\n",
       "      <th>print_page</th>\n",
       "      <th>pub_date</th>\n",
       "      <th>score</th>\n",
       "      <th>section_name</th>\n",
       "      <th>snippet</th>\n",
       "      <th>source</th>\n",
       "      <th>type_of_material</th>\n",
       "      <th>uri</th>\n",
       "      <th>web_url</th>\n",
       "      <th>word_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5bbb75cb068401528a2df0f5</td>\n",
       "      <td>REUTERS</td>\n",
       "      <td>By REUTERS</td>\n",
       "      <td>[]</td>\n",
       "      <td>article</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Microsoft Testing New Game-Streaming Service</td>\n",
       "      <td>None</td>\n",
       "      <td>Microsoft Testing New Game-Streaming Service</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-10-08T15:20:41+0000</td>\n",
       "      <td>297.65900</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft Corp is testing a new game streaming...</td>\n",
       "      <td>Reuters</td>\n",
       "      <td>News</td>\n",
       "      <td>nyt://article/b5c2521b-0946-5436-adb1-62d71a73...</td>\n",
       "      <td>https://www.nytimes.com/reuters/2018/10/08/tec...</td>\n",
       "      <td>141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5bbbe59b068401528a2df1ef</td>\n",
       "      <td>REUTERS</td>\n",
       "      <td>By REUTERS</td>\n",
       "      <td>[]</td>\n",
       "      <td>article</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Microsoft to Invest in Southeast Asian Ride-Ha...</td>\n",
       "      <td>None</td>\n",
       "      <td>Microsoft to Invest in Southeast Asian Ride-Ha...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-10-08T23:17:43+0000</td>\n",
       "      <td>276.20792</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft Corp is investing in Southeast Asian...</td>\n",
       "      <td>Reuters</td>\n",
       "      <td>News</td>\n",
       "      <td>nyt://article/f5290bfb-217c-5308-b763-2207b405...</td>\n",
       "      <td>https://www.nytimes.com/reuters/2018/10/08/bus...</td>\n",
       "      <td>327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5bbb4bb5068401528a2df0a8</td>\n",
       "      <td>REUTERS</td>\n",
       "      <td>By REUTERS</td>\n",
       "      <td>[]</td>\n",
       "      <td>article</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Exclusive: Microsoft's $7.5 Billion GitHub Dea...</td>\n",
       "      <td>None</td>\n",
       "      <td>Exclusive: Microsoft's $7.5 Billion GitHub Dea...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-10-08T12:21:08+0000</td>\n",
       "      <td>271.93472</td>\n",
       "      <td>NaN</td>\n",
       "      <td>U.S. software giant Microsoft is set to win un...</td>\n",
       "      <td>Reuters</td>\n",
       "      <td>News</td>\n",
       "      <td>nyt://article/928a254e-c6eb-5eca-896c-3556a5fa...</td>\n",
       "      <td>https://www.nytimes.com/reuters/2018/10/08/bus...</td>\n",
       "      <td>174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5bbcabf1068401528a2df379</td>\n",
       "      <td>REUTERS</td>\n",
       "      <td>By REUTERS</td>\n",
       "      <td>[]</td>\n",
       "      <td>article</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Microsoft Expands Cloud Service in Push for $1...</td>\n",
       "      <td>None</td>\n",
       "      <td>Microsoft Expands Cloud Service in Push for $1...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-10-09T13:23:59+0000</td>\n",
       "      <td>270.83615</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft Corp said on Tuesday its expanded Az...</td>\n",
       "      <td>Reuters</td>\n",
       "      <td>News</td>\n",
       "      <td>nyt://article/94179203-ee24-5ce5-bb96-641ee251...</td>\n",
       "      <td>https://www.nytimes.com/reuters/2018/10/09/bus...</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5bb295c6068401528a2ddeeb</td>\n",
       "      <td>REUTERS</td>\n",
       "      <td>By REUTERS</td>\n",
       "      <td>[]</td>\n",
       "      <td>article</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Microsoft Co-Founder Paul Allen Treated Again ...</td>\n",
       "      <td>None</td>\n",
       "      <td>Microsoft Co-Founder Paul Allen Treated Again ...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-10-01T21:46:45+0000</td>\n",
       "      <td>257.48560</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft Corp co-founder Paul Allen said on M...</td>\n",
       "      <td>Reuters</td>\n",
       "      <td>News</td>\n",
       "      <td>nyt://article/2f19d5d8-3def-5d8d-9ba8-519385d2...</td>\n",
       "      <td>https://www.nytimes.com/reuters/2018/10/01/us/...</td>\n",
       "      <td>273</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id byline.organization byline.original byline.person  \\\n",
       "0  5bbb75cb068401528a2df0f5             REUTERS      By REUTERS            []   \n",
       "1  5bbbe59b068401528a2df1ef             REUTERS      By REUTERS            []   \n",
       "2  5bbb4bb5068401528a2df0a8             REUTERS      By REUTERS            []   \n",
       "3  5bbcabf1068401528a2df379             REUTERS      By REUTERS            []   \n",
       "4  5bb295c6068401528a2ddeeb             REUTERS      By REUTERS            []   \n",
       "\n",
       "  document_type headline.content_kicker headline.kicker  \\\n",
       "0       article                    None            None   \n",
       "1       article                    None            None   \n",
       "2       article                    None            None   \n",
       "3       article                    None            None   \n",
       "4       article                    None            None   \n",
       "\n",
       "                                       headline.main headline.name  \\\n",
       "0       Microsoft Testing New Game-Streaming Service          None   \n",
       "1  Microsoft to Invest in Southeast Asian Ride-Ha...          None   \n",
       "2  Exclusive: Microsoft's $7.5 Billion GitHub Dea...          None   \n",
       "3  Microsoft Expands Cloud Service in Push for $1...          None   \n",
       "4  Microsoft Co-Founder Paul Allen Treated Again ...          None   \n",
       "\n",
       "                             headline.print_headline    ...     print_page  \\\n",
       "0       Microsoft Testing New Game-Streaming Service    ...            NaN   \n",
       "1  Microsoft to Invest in Southeast Asian Ride-Ha...    ...            NaN   \n",
       "2  Exclusive: Microsoft's $7.5 Billion GitHub Dea...    ...            NaN   \n",
       "3  Microsoft Expands Cloud Service in Push for $1...    ...            NaN   \n",
       "4  Microsoft Co-Founder Paul Allen Treated Again ...    ...            NaN   \n",
       "\n",
       "                   pub_date      score section_name  \\\n",
       "0  2018-10-08T15:20:41+0000  297.65900          NaN   \n",
       "1  2018-10-08T23:17:43+0000  276.20792          NaN   \n",
       "2  2018-10-08T12:21:08+0000  271.93472          NaN   \n",
       "3  2018-10-09T13:23:59+0000  270.83615          NaN   \n",
       "4  2018-10-01T21:46:45+0000  257.48560          NaN   \n",
       "\n",
       "                                             snippet   source  \\\n",
       "0  Microsoft Corp is testing a new game streaming...  Reuters   \n",
       "1  Microsoft Corp is investing in Southeast Asian...  Reuters   \n",
       "2  U.S. software giant Microsoft is set to win un...  Reuters   \n",
       "3  Microsoft Corp said on Tuesday its expanded Az...  Reuters   \n",
       "4  Microsoft Corp co-founder Paul Allen said on M...  Reuters   \n",
       "\n",
       "  type_of_material                                                uri  \\\n",
       "0             News  nyt://article/b5c2521b-0946-5436-adb1-62d71a73...   \n",
       "1             News  nyt://article/f5290bfb-217c-5308-b763-2207b405...   \n",
       "2             News  nyt://article/928a254e-c6eb-5eca-896c-3556a5fa...   \n",
       "3             News  nyt://article/94179203-ee24-5ce5-bb96-641ee251...   \n",
       "4             News  nyt://article/2f19d5d8-3def-5d8d-9ba8-519385d2...   \n",
       "\n",
       "                                             web_url word_count  \n",
       "0  https://www.nytimes.com/reuters/2018/10/08/tec...        141  \n",
       "1  https://www.nytimes.com/reuters/2018/10/08/bus...        327  \n",
       "2  https://www.nytimes.com/reuters/2018/10/08/bus...        174  \n",
       "3  https://www.nytimes.com/reuters/2018/10/09/bus...        158  \n",
       "4  https://www.nytimes.com/reuters/2018/10/01/us/...        273  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Trust me that the JSON for even a short period is very long,\n",
    "# thus the immediate dataframe processing.\n",
    "msft_nyt = pd.DataFrame(pd.io.json.json_normalize(msft_articles['response']['docs']))\n",
    "msft_nyt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# While we're at it, let's add Microsoft's ticker.\n",
    "# We'd usually add an identifier when getting query results.\n",
    "msft_nyt['id_ticker'] = 'msft'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "str"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We should also clean up the publication date.\n",
    "type(msft_nyt['pub_date'][0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas._libs.tslibs.timestamps.Timestamp"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "msft_nyt['pub_date'] = pd.to_datetime(msft_nyt['pub_date'])\n",
    "type(msft_nyt['pub_date'][0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We're going to look at this a bit later, so let's save it.\n",
    "msft_nyt.to_csv('./data/msft_nyt.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Commercial databases\n",
    "\n",
    "This is a more difficult topic to cover in a hands-on workshop (especially one with  openly-distributed materials) because of copyright.\n",
    "They tend to sort out into a couple of types:\n",
    "\n",
    "1. Those that come in tabular formats and simply require cleaning.\n",
    "2. Others (like LexisNexis and Factiva) that come in semi-structured form and require extensive parsing. \n",
    "\n",
    "For the second type, it's best to either recruit one of the few coauthors with this skillset (and perhaps even a written or partially written implementation) or hiring a programmer or student who can write one.\n",
    "It's worth noting that it's not all that hard to get a reasonable implementation written, but going from 90 percent parsing accuracy to 99 percent to 99.9+ percent is difficult, painstaking work.\n",
    "\n",
    "With that in mind, feel free to ask questions about this topic during the workshop.\n",
    "If time permits, I'll demonstrate some non-sharable stuff."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merging\n",
    "\n",
    "Like other software, `pandas` is great and merging data, and it as some conveniences not found in most other software.\n",
    "\n",
    "Let's work through a simple example to see it in action."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>size_emp</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "      <th>size_emp_change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>74</td>\n",
       "      <td>Google</td>\n",
       "      <td>2017</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   size_emp       name  year  size_emp_change\n",
       "0       114  Microsoft  2016              NaN\n",
       "1       124  Microsoft  2017             10.0\n",
       "2       131  Microsoft  2018              7.0\n",
       "3        72     Google  2016              NaN\n",
       "4        74     Google  2017              2.0"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remember our firm year data.\n",
    "firmyear.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>price</th>\n",
       "      <th>tic</th>\n",
       "      <th>yr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>86.13</td>\n",
       "      <td>msft</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>62.79</td>\n",
       "      <td>msft</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>54.32</td>\n",
       "      <td>msft</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   price   tic    yr\n",
       "0  86.13  msft  2018\n",
       "1  62.79  msft  2017\n",
       "2  54.32  msft  2016"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stock = pd.read_csv('./data/stock.csv')\n",
    "stock.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What we'd like to do is merge in those Microsoft stock prices from the beginning of those years.\n",
    "It's a bit contrived for an example, but it mirrors a lot of real world work.\n",
    "\n",
    "While we know that Microsoft's ticker is MSFT, there's no way for `pandas` to know that without help.\n",
    "So, to help, we'll make a lookup table using a dictionary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "lookup = {\n",
    "    'Microsoft': 'MSFT',\n",
    "    'Google': 'GOOG'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>size_emp</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "      <th>size_emp_change</th>\n",
       "      <th>id_ticker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MSFT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "      <td>10.0</td>\n",
       "      <td>MSFT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "      <td>7.0</td>\n",
       "      <td>MSFT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GOOG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>74</td>\n",
       "      <td>Google</td>\n",
       "      <td>2017</td>\n",
       "      <td>2.0</td>\n",
       "      <td>GOOG</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   size_emp       name  year  size_emp_change id_ticker\n",
       "0       114  Microsoft  2016              NaN      MSFT\n",
       "1       124  Microsoft  2017             10.0      MSFT\n",
       "2       131  Microsoft  2018              7.0      MSFT\n",
       "3        72     Google  2016              NaN      GOOG\n",
       "4        74     Google  2017              2.0      GOOG"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firmyear['id_ticker'] = firmyear['name'].map(lookup)\n",
    "firmyear.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>size_emp</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "      <th>size_emp_change</th>\n",
       "      <th>id_ticker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>msft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "      <td>10.0</td>\n",
       "      <td>msft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "      <td>7.0</td>\n",
       "      <td>msft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>goog</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>74</td>\n",
       "      <td>Google</td>\n",
       "      <td>2017</td>\n",
       "      <td>2.0</td>\n",
       "      <td>goog</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   size_emp       name  year  size_emp_change id_ticker\n",
       "0       114  Microsoft  2016              NaN      msft\n",
       "1       124  Microsoft  2017             10.0      msft\n",
       "2       131  Microsoft  2018              7.0      msft\n",
       "3        72     Google  2016              NaN      goog\n",
       "4        74     Google  2017              2.0      goog"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's make that lowercase.\n",
    "firmyear['id_ticker'] = firmyear['id_ticker'].str.lower()\n",
    "firmyear.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In Stata, we would have another problem, namely that our column names for merging do not match.\n",
    "With `pandas`, that's not a problem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "firmyear = firmyear.merge(stock, how='left', \n",
    "                          left_on=['id_ticker', 'year'],\n",
    "                          right_on=['tic', 'yr'],\n",
    "                          validate='1:1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>size_emp</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "      <th>size_emp_change</th>\n",
       "      <th>id_ticker</th>\n",
       "      <th>price</th>\n",
       "      <th>tic</th>\n",
       "      <th>yr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>msft</td>\n",
       "      <td>54.32</td>\n",
       "      <td>msft</td>\n",
       "      <td>2016.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "      <td>10.0</td>\n",
       "      <td>msft</td>\n",
       "      <td>62.79</td>\n",
       "      <td>msft</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "      <td>7.0</td>\n",
       "      <td>msft</td>\n",
       "      <td>86.13</td>\n",
       "      <td>msft</td>\n",
       "      <td>2018.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>goog</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>74</td>\n",
       "      <td>Google</td>\n",
       "      <td>2017</td>\n",
       "      <td>2.0</td>\n",
       "      <td>goog</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   size_emp       name  year  size_emp_change id_ticker  price   tic      yr\n",
       "0       114  Microsoft  2016              NaN      msft  54.32  msft  2016.0\n",
       "1       124  Microsoft  2017             10.0      msft  62.79  msft  2017.0\n",
       "2       131  Microsoft  2018              7.0      msft  86.13  msft  2018.0\n",
       "3        72     Google  2016              NaN      goog    NaN   NaN     NaN\n",
       "4        74     Google  2017              2.0      goog    NaN   NaN     NaN"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firmyear.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Querying\n",
    "\n",
    "When working with content data, we often need to do some sort of a query to aggregate data that is interesting to us.\n",
    "\n",
    "For example, let's add the average word count of articles from our NYT data to our firmyear data.\n",
    "We're only going to have results for 2018, as that's all the data we pulled."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "_AGG = {\n",
    "    'word_count': ['mean', 'sum']\n",
    "}\n",
    "\n",
    "def query_docs(data, ticker, year):\n",
    "    summary = (data[(data['id_ticker'] == ticker) & \n",
    "                    (data['pub_date'].dt.year == year)].agg(_AGG)\n",
    "                     .T.reset_index(drop=True))\n",
    "    summary['id_ticker'] = ticker\n",
    "    summary['year'] = year\n",
    "    summary = summary.rename(columns={'mean': 'wc_mean', 'sum': 'wc_sum'})\n",
    "    return summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = pd.DataFrame()\n",
    "for index, row in firmyear.loc[:, ['id_ticker', 'year']].iterrows():\n",
    "    results = results.append(query_docs(msft_nyt, row['id_ticker'], row['year']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>wc_mean</th>\n",
       "      <th>wc_sum</th>\n",
       "      <th>id_ticker</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>msft</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>msft</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>519.0</td>\n",
       "      <td>5190.0</td>\n",
       "      <td>msft</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>goog</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>goog</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>goog</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   wc_mean  wc_sum id_ticker  year\n",
       "0      NaN     0.0      msft  2016\n",
       "0      NaN     0.0      msft  2017\n",
       "0    519.0  5190.0      msft  2018\n",
       "0      NaN     0.0      goog  2016\n",
       "0      NaN     0.0      goog  2017\n",
       "0      NaN     0.0      goog  2018"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>size_emp</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "      <th>size_emp_change</th>\n",
       "      <th>id_ticker</th>\n",
       "      <th>price</th>\n",
       "      <th>tic</th>\n",
       "      <th>yr</th>\n",
       "      <th>wc_mean</th>\n",
       "      <th>wc_sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>msft</td>\n",
       "      <td>54.32</td>\n",
       "      <td>msft</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "      <td>10.0</td>\n",
       "      <td>msft</td>\n",
       "      <td>62.79</td>\n",
       "      <td>msft</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "      <td>7.0</td>\n",
       "      <td>msft</td>\n",
       "      <td>86.13</td>\n",
       "      <td>msft</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>519.0</td>\n",
       "      <td>5190.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>goog</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>74</td>\n",
       "      <td>Google</td>\n",
       "      <td>2017</td>\n",
       "      <td>2.0</td>\n",
       "      <td>goog</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   size_emp       name  year  size_emp_change id_ticker  price   tic      yr  \\\n",
       "0       114  Microsoft  2016              NaN      msft  54.32  msft  2016.0   \n",
       "1       124  Microsoft  2017             10.0      msft  62.79  msft  2017.0   \n",
       "2       131  Microsoft  2018              7.0      msft  86.13  msft  2018.0   \n",
       "3        72     Google  2016              NaN      goog    NaN   NaN     NaN   \n",
       "4        74     Google  2017              2.0      goog    NaN   NaN     NaN   \n",
       "\n",
       "   wc_mean  wc_sum  \n",
       "0      NaN     0.0  \n",
       "1      NaN     0.0  \n",
       "2    519.0  5190.0  \n",
       "3      NaN     0.0  \n",
       "4      NaN     0.0  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firmyear = firmyear.merge(results, how='left',\n",
    "                          on=['id_ticker', 'year'],\n",
    "                          validate='1:1')\n",
    "firmyear.head()"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}