2a_handling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data handling\n",
    "\n",
    "In this notebook, we will work with the following:\n",
    "\n",
    "- Reading data with `pandas`.\n",
    "- Cleaning and transforming data.\n",
    "- Viewing and selecting data.\n",
    "- Merging and querying.\n",
    "- Exporting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading 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",
       "      <td>0</td>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\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": [
    "# Cleaning data\n",
    "\n",
    "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.\n",
    "\n",
    "## Data types"
   ]
  },
  {
   "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": "markdown",
   "metadata": {},
   "source": [
    "Note that all of the columns above are of type `object`, which often means that they are strings.\n",
    "We want to change the things that we know are numbers (i.e. `count_of_employees` and `year`) into the appropriate types (both `int` in this case)."
   ]
  },
  {
   "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",
       "      <td>0</td>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>74</td>\n",
       "      <td>Google</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\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": "markdown",
   "metadata": {},
   "source": [
    "## Renaming columns\n",
    "\n",
    "We could rename columns by creating a new column with the correct name and dropping the prior one, but this is more efficient and easily extended to the multiple column case."
   ]
  },
  {
   "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",
       "      <td>0</td>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>74</td>\n",
       "      <td>Google</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\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": "markdown",
   "metadata": {},
   "source": [
    "## Transformations\n",
    "\n",
    "We can also do transformations that apply some sort of function or method to data by groups.\n",
    "This is a fairly simple example, but `pandas` makes it fairly easy to do sophisticated transformations.\n",
    "See the [split-apply-combine](https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html) documentation.\n",
    "This is a big topic, and, like before, we are only scratching the surface."
   ]
  },
  {
   "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",
       "      <td>0</td>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>74</td>\n",
       "      <td>Google</td>\n",
       "      <td>2017</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\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": [
    "# Viewing and selecting data\n",
    "\n",
    "pandas has a number of tools for viewing and selecting data.\n",
    "The one we see above is the `df.head()` method that displays the first five rows at the top (or head) of the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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",
       "      <td>0</td>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\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": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firmyear.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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",
       "      <td>0</td>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>74</td>\n",
       "      <td>Google</td>\n",
       "      <td>2017</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\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": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We can give it a parameter to modify the number of rows.\n",
    "# Here, we only have six rows, so that's all we get.\n",
    "firmyear.head(8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The len() function works on dataframes.\n",
    "len(firmyear)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also select one or more columns by using indexing that is somewhat like what we did with dictionaries earlier.\n",
    "However, we can give the indexer a list, and get the named columns.\n",
    "\n",
    "Note that, when we ask for one column, pandas gives us a series, not a dataframe, so the display is a little less fancy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    Microsoft\n",
       "1    Microsoft\n",
       "2    Microsoft\n",
       "3       Google\n",
       "4       Google\n",
       "5       Google\n",
       "Name: name, dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firmyear['name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>name</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Google</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Google</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        name  year\n",
       "0  Microsoft  2016\n",
       "1  Microsoft  2017\n",
       "2  Microsoft  2018\n",
       "3     Google  2016\n",
       "4     Google  2017\n",
       "5     Google  2018"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Note the two sets of brackets.\n",
    "# The outer set is for the indexing syntax.\n",
    "# The inner set is for the list that we're asking the indexer for.\n",
    "firmyear[['name', 'year']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also ask for rows that meet certain conditions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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",
       "      <td>0</td>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "      <td>7.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"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firmyear[firmyear['name'] == 'Microsoft']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     True\n",
       "1     True\n",
       "2     True\n",
       "3    False\n",
       "4    False\n",
       "5    False\n",
       "Name: name, dtype: bool"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Note that the expression used for indexing is returning a series of boolean values.\n",
    "firmyear['name'] == 'Microsoft'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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",
       "      <td>0</td>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\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": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We can use compound statements that return one boolean value per row.\n",
    "# Here, it's name == Microsoft or the year is less than 2018.\n",
    "firmyear[(firmyear['name'] == 'Microsoft') | (firmyear['year'] < 2018)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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",
       "      <td>1</td>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>74</td>\n",
       "      <td>Google</td>\n",
       "      <td>2017</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\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",
       "1       124  Microsoft  2017             10.0\n",
       "2       131  Microsoft  2018              7.0\n",
       "4        74     Google  2017              2.0\n",
       "5        76     Google  2018              2.0"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Series have methods for checking whether they're NA.\n",
    "# This is true if each row is not NA.\n",
    "firmyear[firmyear['size_emp_change'].notna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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",
       "      <td>0</td>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</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",
       "3        72     Google  2016              NaN"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This is True if each row is NA.\n",
    "firmyear[firmyear['size_emp_change'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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",
       "      <td>0</td>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</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",
       "3        72     Google  2016              NaN"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We can also use ~ to negate the condition after it.\n",
    "# So, here is not not NA (same as is NA).\n",
    "firmyear[~firmyear['size_emp_change'].notna()]"
   ]
  },
  {
   "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": 22,
   "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",
       "      <td>0</td>\n",
       "      <td>114</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>124</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2017</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>131</td>\n",
       "      <td>Microsoft</td>\n",
       "      <td>2018</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>72</td>\n",
       "      <td>Google</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\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": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remember our firm year data.\n",
    "firmyear.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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",
       "      <td>0</td>\n",
       "      <td>86.13</td>\n",
       "      <td>msft</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>62.79</td>\n",
       "      <td>msft</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\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": 23,
     "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": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "lookup = {\n",
    "    'Microsoft': 'MSFT',\n",
    "    'Google': 'GOOG'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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",
       "      <td>0</td>\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",
       "      <td>1</td>\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",
       "      <td>2</td>\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",
       "      <td>3</td>\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",
       "      <td>4</td>\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": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firmyear['id_ticker'] = firmyear['name'].map(lookup)\n",
    "firmyear.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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",
       "      <td>0</td>\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",
       "      <td>1</td>\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",
       "      <td>2</td>\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",
       "      <td>3</td>\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",
       "      <td>4</td>\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": 26,
     "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.\n",
    "\n",
    "Note the validate parameter. This tells pandas that we have an expectation about how these data align with each other, and it should raise an exception if our expectation isn't met.\n",
    "If you merge data without this parameter, and it unexpectedly grows in length, you may be unintentionally doing a many-to-many merge (which generally returns a new row for every pair of matches within the groups specified)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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": 28,
   "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",
       "      <td>0</td>\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",
       "      <td>1</td>\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",
       "      <td>2</td>\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",
       "      <td>3</td>\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",
       "      <td>4</td>\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": 28,
     "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 an average word count of articles from some NYT data (similar to what we'll retrieve later) to our firmyear data.\n",
    "We're only going to have results for 2018, as that's all the data I included."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "msft_nyt = pd.read_csv('data/msft_nyt.csv', index_col=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>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",
       "      <th>id_ticker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>5bbb75cb068401528a2df0f5</td>\n",
       "      <td>REUTERS</td>\n",
       "      <td>By REUTERS</td>\n",
       "      <td>[]</td>\n",
       "      <td>article</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft Testing New Game-Streaming Service</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft Testing New Game-Streaming Service</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-10-08 15:20:41</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",
       "      <td>msft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>5bbbe59b068401528a2df1ef</td>\n",
       "      <td>REUTERS</td>\n",
       "      <td>By REUTERS</td>\n",
       "      <td>[]</td>\n",
       "      <td>article</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft to Invest in Southeast Asian Ride-Ha...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft to Invest in Southeast Asian Ride-Ha...</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-10-08 23:17:43</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",
       "      <td>msft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>5bbb4bb5068401528a2df0a8</td>\n",
       "      <td>REUTERS</td>\n",
       "      <td>By REUTERS</td>\n",
       "      <td>[]</td>\n",
       "      <td>article</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Exclusive: Microsoft's $7.5 Billion GitHub Dea...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Exclusive: Microsoft's $7.5 Billion GitHub Dea...</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-10-08 12:21:08</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",
       "      <td>msft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>5bbcabf1068401528a2df379</td>\n",
       "      <td>REUTERS</td>\n",
       "      <td>By REUTERS</td>\n",
       "      <td>[]</td>\n",
       "      <td>article</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft Expands Cloud Service in Push for $1...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft Expands Cloud Service in Push for $1...</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-10-09 13:23:59</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",
       "      <td>msft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5bb295c6068401528a2ddeeb</td>\n",
       "      <td>REUTERS</td>\n",
       "      <td>By REUTERS</td>\n",
       "      <td>[]</td>\n",
       "      <td>article</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft Co-Founder Paul Allen Treated Again ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft Co-Founder Paul Allen Treated Again ...</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-10-01 21:46:45</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",
       "      <td>msft</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 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                     NaN             NaN   \n",
       "1       article                     NaN             NaN   \n",
       "2       article                     NaN             NaN   \n",
       "3       article                     NaN             NaN   \n",
       "4       article                     NaN             NaN   \n",
       "\n",
       "                                       headline.main  headline.name  \\\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",
       "                             headline.print_headline  ...            pub_date  \\\n",
       "0       Microsoft Testing New Game-Streaming Service  ... 2018-10-08 15:20:41   \n",
       "1  Microsoft to Invest in Southeast Asian Ride-Ha...  ... 2018-10-08 23:17:43   \n",
       "2  Exclusive: Microsoft's $7.5 Billion GitHub Dea...  ... 2018-10-08 12:21:08   \n",
       "3  Microsoft Expands Cloud Service in Push for $1...  ... 2018-10-09 13:23:59   \n",
       "4  Microsoft Co-Founder Paul Allen Treated Again ...  ... 2018-10-01 21:46:45   \n",
       "\n",
       "       score section_name                                            snippet  \\\n",
       "0  297.65900          NaN  Microsoft Corp is testing a new game streaming...   \n",
       "1  276.20792          NaN  Microsoft Corp is investing in Southeast Asian...   \n",
       "2  271.93472          NaN  U.S. software giant Microsoft is set to win un...   \n",
       "3  270.83615          NaN  Microsoft Corp said on Tuesday its expanded Az...   \n",
       "4  257.48560          NaN  Microsoft Corp co-founder Paul Allen said on M...   \n",
       "\n",
       "    source  type_of_material  \\\n",
       "0  Reuters              News   \n",
       "1  Reuters              News   \n",
       "2  Reuters              News   \n",
       "3  Reuters              News   \n",
       "4  Reuters              News   \n",
       "\n",
       "                                                 uri  \\\n",
       "0  nyt://article/b5c2521b-0946-5436-adb1-62d71a73...   \n",
       "1  nyt://article/f5290bfb-217c-5308-b763-2207b405...   \n",
       "2  nyt://article/928a254e-c6eb-5eca-896c-3556a5fa...   \n",
       "3  nyt://article/94179203-ee24-5ce5-bb96-641ee251...   \n",
       "4  nyt://article/2f19d5d8-3def-5d8d-9ba8-519385d2...   \n",
       "\n",
       "                                             web_url word_count id_ticker  \n",
       "0  https://www.nytimes.com/reuters/2018/10/08/tec...        141      msft  \n",
       "1  https://www.nytimes.com/reuters/2018/10/08/bus...        327      msft  \n",
       "2  https://www.nytimes.com/reuters/2018/10/08/bus...        174      msft  \n",
       "3  https://www.nytimes.com/reuters/2018/10/09/bus...        158      msft  \n",
       "4  https://www.nytimes.com/reuters/2018/10/01/us/...        273      msft  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "msft_nyt['pub_date'] = pd.to_datetime(msft_nyt['pub_date'])\n",
    "msft_nyt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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": 32,
   "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": 33,
   "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",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>msft</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>msft</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>519.0</td>\n",
       "      <td>5190.0</td>\n",
       "      <td>msft</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>goog</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>goog</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>0</td>\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": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results"
   ]
  },
  {
   "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>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",
       "      <td>0</td>\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",
       "      <td>1</td>\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",
       "      <td>2</td>\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",
       "      <td>3</td>\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",
       "      <td>4</td>\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": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firmyear = firmyear.merge(results, how='left',\n",
    "                          on=['id_ticker', 'year'],\n",
    "                          validate='1:1')\n",
    "firmyear.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Saving and exporting\n",
    "\n",
    "pandas is able to write data in a number of formats that you may need.\n",
    "You can see a [reference](https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html) in the user guide.\n",
    "\n",
    "Two in particular merit an additional mention:\n",
    "\n",
    "1. **Parquet.** Apache Parquet is a high-performance compressed file format that I like to use for data that I want to use again in Python. It retains the type information, and it continues to work well up to file sizes of a few GBs.\n",
    "1. **SQL.** If you are working with a database directly (including the kind of cases I mention in the self-study on data handling), the SQL support in pandas is really convenient. That said, if you are using a service with its own package (e.g., WRDS), you probably want the more specific package."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Bonus content\n",
    "\n",
    "One thing to notice in our code above is that we have several datasets all in memory at once.\n",
    "In some stats packages, this is not nearly so easy.\n",
    "\n",
    "For example, in Stata, they recently added the concept of multiple datasets, but the interface is much more difficult to use.\n",
    "In contrast, with pandas, we simply use the name of the dataframe and then whatever operation that we are doing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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",
       "      <td>0</td>\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",
       "      <td>1</td>\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",
       "      <td>2</td>\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",
       "      <td>3</td>\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",
       "      <td>4</td>\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": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firmyear.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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",
       "      <td>0</td>\n",
       "      <td>86.13</td>\n",
       "      <td>msft</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>62.79</td>\n",
       "      <td>msft</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\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": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stock.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>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",
       "      <th>id_ticker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>5bbb75cb068401528a2df0f5</td>\n",
       "      <td>REUTERS</td>\n",
       "      <td>By REUTERS</td>\n",
       "      <td>[]</td>\n",
       "      <td>article</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft Testing New Game-Streaming Service</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft Testing New Game-Streaming Service</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-10-08 15:20:41</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",
       "      <td>msft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>5bbbe59b068401528a2df1ef</td>\n",
       "      <td>REUTERS</td>\n",
       "      <td>By REUTERS</td>\n",
       "      <td>[]</td>\n",
       "      <td>article</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft to Invest in Southeast Asian Ride-Ha...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft to Invest in Southeast Asian Ride-Ha...</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-10-08 23:17:43</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",
       "      <td>msft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>5bbb4bb5068401528a2df0a8</td>\n",
       "      <td>REUTERS</td>\n",
       "      <td>By REUTERS</td>\n",
       "      <td>[]</td>\n",
       "      <td>article</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Exclusive: Microsoft's $7.5 Billion GitHub Dea...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Exclusive: Microsoft's $7.5 Billion GitHub Dea...</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-10-08 12:21:08</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",
       "      <td>msft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>5bbcabf1068401528a2df379</td>\n",
       "      <td>REUTERS</td>\n",
       "      <td>By REUTERS</td>\n",
       "      <td>[]</td>\n",
       "      <td>article</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft Expands Cloud Service in Push for $1...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft Expands Cloud Service in Push for $1...</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-10-09 13:23:59</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",
       "      <td>msft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5bb295c6068401528a2ddeeb</td>\n",
       "      <td>REUTERS</td>\n",
       "      <td>By REUTERS</td>\n",
       "      <td>[]</td>\n",
       "      <td>article</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft Co-Founder Paul Allen Treated Again ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft Co-Founder Paul Allen Treated Again ...</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-10-01 21:46:45</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",
       "      <td>msft</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 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                     NaN             NaN   \n",
       "1       article                     NaN             NaN   \n",
       "2       article                     NaN             NaN   \n",
       "3       article                     NaN             NaN   \n",
       "4       article                     NaN             NaN   \n",
       "\n",
       "                                       headline.main  headline.name  \\\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",
       "                             headline.print_headline  ...            pub_date  \\\n",
       "0       Microsoft Testing New Game-Streaming Service  ... 2018-10-08 15:20:41   \n",
       "1  Microsoft to Invest in Southeast Asian Ride-Ha...  ... 2018-10-08 23:17:43   \n",
       "2  Exclusive: Microsoft's $7.5 Billion GitHub Dea...  ... 2018-10-08 12:21:08   \n",
       "3  Microsoft Expands Cloud Service in Push for $1...  ... 2018-10-09 13:23:59   \n",
       "4  Microsoft Co-Founder Paul Allen Treated Again ...  ... 2018-10-01 21:46:45   \n",
       "\n",
       "       score section_name                                            snippet  \\\n",
       "0  297.65900          NaN  Microsoft Corp is testing a new game streaming...   \n",
       "1  276.20792          NaN  Microsoft Corp is investing in Southeast Asian...   \n",
       "2  271.93472          NaN  U.S. software giant Microsoft is set to win un...   \n",
       "3  270.83615          NaN  Microsoft Corp said on Tuesday its expanded Az...   \n",
       "4  257.48560          NaN  Microsoft Corp co-founder Paul Allen said on M...   \n",
       "\n",
       "    source  type_of_material  \\\n",
       "0  Reuters              News   \n",
       "1  Reuters              News   \n",
       "2  Reuters              News   \n",
       "3  Reuters              News   \n",
       "4  Reuters              News   \n",
       "\n",
       "                                                 uri  \\\n",
       "0  nyt://article/b5c2521b-0946-5436-adb1-62d71a73...   \n",
       "1  nyt://article/f5290bfb-217c-5308-b763-2207b405...   \n",
       "2  nyt://article/928a254e-c6eb-5eca-896c-3556a5fa...   \n",
       "3  nyt://article/94179203-ee24-5ce5-bb96-641ee251...   \n",
       "4  nyt://article/2f19d5d8-3def-5d8d-9ba8-519385d2...   \n",
       "\n",
       "                                             web_url word_count id_ticker  \n",
       "0  https://www.nytimes.com/reuters/2018/10/08/tec...        141      msft  \n",
       "1  https://www.nytimes.com/reuters/2018/10/08/bus...        327      msft  \n",
       "2  https://www.nytimes.com/reuters/2018/10/08/bus...        174      msft  \n",
       "3  https://www.nytimes.com/reuters/2018/10/09/bus...        158      msft  \n",
       "4  https://www.nytimes.com/reuters/2018/10/01/us/...        273      msft  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "msft_nyt.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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}