02_python_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Introduction\" data-toc-modified-id=\"Introduction-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Introduction</a></span></li><li><span><a href=\"#Starting-pandas\" data-toc-modified-id=\"Starting-pandas-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Starting pandas</a></span></li><li><span><a href=\"#Data-import-and-export\" data-toc-modified-id=\"Data-import-and-export-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Data import and export</a></span></li><li><span><a href=\"#Exploring-a-data-set\" data-toc-modified-id=\"Exploring-a-data-set-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>Exploring a data set</a></span></li><li><span><a href=\"#Data-structures-and-types\" data-toc-modified-id=\"Data-structures-and-types-5\"><span class=\"toc-item-num\">5&nbsp;&nbsp;</span>Data structures and types</a></span><ul class=\"toc-item\"><li><span><a href=\"#pandas.Series\" data-toc-modified-id=\"pandas.Series-5.1\"><span class=\"toc-item-num\">5.1&nbsp;&nbsp;</span>pandas.Series</a></span></li><li><span><a href=\"#pandas.DataFrame\" data-toc-modified-id=\"pandas.DataFrame-5.2\"><span class=\"toc-item-num\">5.2&nbsp;&nbsp;</span>pandas.DataFrame</a></span><ul class=\"toc-item\"><li><span><a href=\"#Creating-a-DataFrame\" data-toc-modified-id=\"Creating-a-DataFrame-5.2.1\"><span class=\"toc-item-num\">5.2.1&nbsp;&nbsp;</span>Creating a DataFrame</a></span></li><li><span><a href=\"#Working-with-a-DataFrame\" data-toc-modified-id=\"Working-with-a-DataFrame-5.2.2\"><span class=\"toc-item-num\">5.2.2&nbsp;&nbsp;</span>Working with a DataFrame</a></span></li><li><span><a href=\"#Extracting-rows-and-columns\" data-toc-modified-id=\"Extracting-rows-and-columns-5.2.3\"><span class=\"toc-item-num\">5.2.3&nbsp;&nbsp;</span>Extracting rows and columns</a></span></li><li><span><a href=\"#Boolean-selection\" data-toc-modified-id=\"Boolean-selection-5.2.4\"><span class=\"toc-item-num\">5.2.4&nbsp;&nbsp;</span>Boolean selection</a></span></li><li><span><a href=\"#query\" data-toc-modified-id=\"query-5.2.5\"><span class=\"toc-item-num\">5.2.5&nbsp;&nbsp;</span><code>query</code></a></span></li><li><span><a href=\"#Replacing-values-in-a-DataFrame\" data-toc-modified-id=\"Replacing-values-in-a-DataFrame-5.2.6\"><span class=\"toc-item-num\">5.2.6&nbsp;&nbsp;</span>Replacing values in a DataFrame</a></span></li></ul></li><li><span><a href=\"#Categorical-data\" data-toc-modified-id=\"Categorical-data-5.3\"><span class=\"toc-item-num\">5.3&nbsp;&nbsp;</span>Categorical data</a></span><ul class=\"toc-item\"><li><span><a href=\"#Re-organizing-categories\" data-toc-modified-id=\"Re-organizing-categories-5.3.1\"><span class=\"toc-item-num\">5.3.1&nbsp;&nbsp;</span>Re-organizing categories</a></span></li></ul></li><li><span><a href=\"#Missing-data\" data-toc-modified-id=\"Missing-data-5.4\"><span class=\"toc-item-num\">5.4&nbsp;&nbsp;</span>Missing data</a></span></li></ul></li><li><span><a href=\"#Data-transformation\" data-toc-modified-id=\"Data-transformation-6\"><span class=\"toc-item-num\">6&nbsp;&nbsp;</span>Data transformation</a></span><ul class=\"toc-item\"><li><span><a href=\"#Arithmetic-operations\" data-toc-modified-id=\"Arithmetic-operations-6.1\"><span class=\"toc-item-num\">6.1&nbsp;&nbsp;</span>Arithmetic operations</a></span></li><li><span><a href=\"#Concatenation-of-data-sets\" data-toc-modified-id=\"Concatenation-of-data-sets-6.2\"><span class=\"toc-item-num\">6.2&nbsp;&nbsp;</span>Concatenation of data sets</a></span><ul class=\"toc-item\"><li><span><a href=\"#Adding-columns\" data-toc-modified-id=\"Adding-columns-6.2.1\"><span class=\"toc-item-num\">6.2.1&nbsp;&nbsp;</span>Adding columns</a></span></li></ul></li><li><span><a href=\"#Merging-data-sets\" data-toc-modified-id=\"Merging-data-sets-6.3\"><span class=\"toc-item-num\">6.3&nbsp;&nbsp;</span>Merging data sets</a></span></li><li><span><a href=\"#Tidy-data-principles-and-reshaping-datasets\" data-toc-modified-id=\"Tidy-data-principles-and-reshaping-datasets-6.4\"><span class=\"toc-item-num\">6.4&nbsp;&nbsp;</span>Tidy data principles and reshaping datasets</a></span></li><li><span><a href=\"#Melting-(unpivoting)-data\" data-toc-modified-id=\"Melting-(unpivoting)-data-6.5\"><span class=\"toc-item-num\">6.5&nbsp;&nbsp;</span>Melting (unpivoting) data</a></span></li><li><span><a href=\"#Separating-columns-containing-multiple-variables\" data-toc-modified-id=\"Separating-columns-containing-multiple-variables-6.6\"><span class=\"toc-item-num\">6.6&nbsp;&nbsp;</span>Separating columns containing multiple variables</a></span></li><li><span><a href=\"#Pivot/spread-datasets\" data-toc-modified-id=\"Pivot/spread-datasets-6.7\"><span class=\"toc-item-num\">6.7&nbsp;&nbsp;</span>Pivot/spread datasets</a></span></li></ul></li><li><span><a href=\"#Data-aggregation-and-split-apply-combine\" data-toc-modified-id=\"Data-aggregation-and-split-apply-combine-7\"><span class=\"toc-item-num\">7&nbsp;&nbsp;</span>Data aggregation and split-apply-combine</a></span><ul class=\"toc-item\"><li><ul class=\"toc-item\"><li><span><a href=\"#Transformation\" data-toc-modified-id=\"Transformation-7.0.1\"><span class=\"toc-item-num\">7.0.1&nbsp;&nbsp;</span>Transformation</a></span></li><li><span><a href=\"#Filter\" data-toc-modified-id=\"Filter-7.0.2\"><span class=\"toc-item-num\">7.0.2&nbsp;&nbsp;</span>Filter</a></span></li></ul></li></ul></li></ul></div>"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "tags": [
     "active-Rmd"
    ]
   },
   "source": [
    "%%R\n",
    "reticulate::use_condaenv('ds', required=T)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas\n",
    "\n",
    "## Introduction\n",
    "\n",
    "`pandas` is the Python Data Analysis package. It allows for data ingestion, transformation and cleaning, and creates objects that can then be passed on to analytic packages like `statsmodels` and `scikit-learn` for modeling and packages like `matplotlib`, `seaborn`, and `plotly` for visualization. \n",
    "\n",
    "`pandas` is built on top of numpy, so many numpy functions are commonly used in manipulating `pandas` objects. \n",
    "\n",
    "> `pandas` is a pretty extensive package, and we'll only be able to cover some of its features. For more details, there is free online documentation at [pandas.pydata.org](https://pandas.pydata.org). You can also look at the book [\"Python for Data Analysis (2nd edition)\"](https://www.amazon.com/Python-Data-Analysis-Wrangling-IPython-dp-1491957662/dp/1491957662/) by Wes McKinney, the original developer of the pandas package, for more details.\n",
    "\n",
    "## Starting pandas\n",
    "\n",
    "As with any Python module, you have to \"activate\" `pandas` by using `import`. The \"standard\" alias for `pandas` is `pd`. We will also import `numpy`, since `pandas` uses some `numpy` functions in the workflows. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "lines_to_next_cell": 0,
    "name": "02-python-pandas-2"
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data import and export\n",
    "\n",
    "Most data sets you will work with are set up in tables, so are rectangular in shape. Think Excel spreadsheets. In `pandas` the structure that will hold this kind of data is a `DataFrame`.  We can read external data into a `DataFrame` using one of many `read_*` functions. We can also write from a `DataFrame` to a variety of formats using `to_*` functions. The most common of these are listed below:\n",
    "\n",
    "| Format type | Description | reader       | writer     |\n",
    "| ----------- | ----------- | ------------ | ---------- |\n",
    "| text        | CSV         | read_csv     | to_csv     |\n",
    "|             | Excel       | read_excel   | to_excel   |\n",
    "| text        | JSON        | read_json    | to_json    |\n",
    "| binary      | Feather     | read_feather | to_feather |\n",
    "| binary      | SAS         | read_sas     |            |\n",
    "| SQL         | SQL         | read_sql     | to_sql     |\n",
    "\n",
    "We'll start by reading in the `mtcars` dataset stored as a CSV file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "name": "02-python-pandas-3"
   },
   "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>make</th>\n",
       "      <th>mpg</th>\n",
       "      <th>cyl</th>\n",
       "      <th>disp</th>\n",
       "      <th>hp</th>\n",
       "      <th>drat</th>\n",
       "      <th>wt</th>\n",
       "      <th>qsec</th>\n",
       "      <th>vs</th>\n",
       "      <th>am</th>\n",
       "      <th>gear</th>\n",
       "      <th>carb</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mazda RX4</td>\n",
       "      <td>21.0</td>\n",
       "      <td>6</td>\n",
       "      <td>160.0</td>\n",
       "      <td>110</td>\n",
       "      <td>3.90</td>\n",
       "      <td>2.620</td>\n",
       "      <td>16.46</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mazda RX4 Wag</td>\n",
       "      <td>21.0</td>\n",
       "      <td>6</td>\n",
       "      <td>160.0</td>\n",
       "      <td>110</td>\n",
       "      <td>3.90</td>\n",
       "      <td>2.875</td>\n",
       "      <td>17.02</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Datsun 710</td>\n",
       "      <td>22.8</td>\n",
       "      <td>4</td>\n",
       "      <td>108.0</td>\n",
       "      <td>93</td>\n",
       "      <td>3.85</td>\n",
       "      <td>2.320</td>\n",
       "      <td>18.61</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Hornet 4 Drive</td>\n",
       "      <td>21.4</td>\n",
       "      <td>6</td>\n",
       "      <td>258.0</td>\n",
       "      <td>110</td>\n",
       "      <td>3.08</td>\n",
       "      <td>3.215</td>\n",
       "      <td>19.44</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Hornet Sportabout</td>\n",
       "      <td>18.7</td>\n",
       "      <td>8</td>\n",
       "      <td>360.0</td>\n",
       "      <td>175</td>\n",
       "      <td>3.15</td>\n",
       "      <td>3.440</td>\n",
       "      <td>17.02</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Valiant</td>\n",
       "      <td>18.1</td>\n",
       "      <td>6</td>\n",
       "      <td>225.0</td>\n",
       "      <td>105</td>\n",
       "      <td>2.76</td>\n",
       "      <td>3.460</td>\n",
       "      <td>20.22</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Duster 360</td>\n",
       "      <td>14.3</td>\n",
       "      <td>8</td>\n",
       "      <td>360.0</td>\n",
       "      <td>245</td>\n",
       "      <td>3.21</td>\n",
       "      <td>3.570</td>\n",
       "      <td>15.84</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Merc 240D</td>\n",
       "      <td>24.4</td>\n",
       "      <td>4</td>\n",
       "      <td>146.7</td>\n",
       "      <td>62</td>\n",
       "      <td>3.69</td>\n",
       "      <td>3.190</td>\n",
       "      <td>20.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Merc 230</td>\n",
       "      <td>22.8</td>\n",
       "      <td>4</td>\n",
       "      <td>140.8</td>\n",
       "      <td>95</td>\n",
       "      <td>3.92</td>\n",
       "      <td>3.150</td>\n",
       "      <td>22.90</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Merc 280</td>\n",
       "      <td>19.2</td>\n",
       "      <td>6</td>\n",
       "      <td>167.6</td>\n",
       "      <td>123</td>\n",
       "      <td>3.92</td>\n",
       "      <td>3.440</td>\n",
       "      <td>18.30</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Merc 280C</td>\n",
       "      <td>17.8</td>\n",
       "      <td>6</td>\n",
       "      <td>167.6</td>\n",
       "      <td>123</td>\n",
       "      <td>3.92</td>\n",
       "      <td>3.440</td>\n",
       "      <td>18.90</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Merc 450SE</td>\n",
       "      <td>16.4</td>\n",
       "      <td>8</td>\n",
       "      <td>275.8</td>\n",
       "      <td>180</td>\n",
       "      <td>3.07</td>\n",
       "      <td>4.070</td>\n",
       "      <td>17.40</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Merc 450SL</td>\n",
       "      <td>17.3</td>\n",
       "      <td>8</td>\n",
       "      <td>275.8</td>\n",
       "      <td>180</td>\n",
       "      <td>3.07</td>\n",
       "      <td>3.730</td>\n",
       "      <td>17.60</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Merc 450SLC</td>\n",
       "      <td>15.2</td>\n",
       "      <td>8</td>\n",
       "      <td>275.8</td>\n",
       "      <td>180</td>\n",
       "      <td>3.07</td>\n",
       "      <td>3.780</td>\n",
       "      <td>18.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Cadillac Fleetwood</td>\n",
       "      <td>10.4</td>\n",
       "      <td>8</td>\n",
       "      <td>472.0</td>\n",
       "      <td>205</td>\n",
       "      <td>2.93</td>\n",
       "      <td>5.250</td>\n",
       "      <td>17.98</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Lincoln Continental</td>\n",
       "      <td>10.4</td>\n",
       "      <td>8</td>\n",
       "      <td>460.0</td>\n",
       "      <td>215</td>\n",
       "      <td>3.00</td>\n",
       "      <td>5.424</td>\n",
       "      <td>17.82</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Chrysler Imperial</td>\n",
       "      <td>14.7</td>\n",
       "      <td>8</td>\n",
       "      <td>440.0</td>\n",
       "      <td>230</td>\n",
       "      <td>3.23</td>\n",
       "      <td>5.345</td>\n",
       "      <td>17.42</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Fiat 128</td>\n",
       "      <td>32.4</td>\n",
       "      <td>4</td>\n",
       "      <td>78.7</td>\n",
       "      <td>66</td>\n",
       "      <td>4.08</td>\n",
       "      <td>2.200</td>\n",
       "      <td>19.47</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Honda Civic</td>\n",
       "      <td>30.4</td>\n",
       "      <td>4</td>\n",
       "      <td>75.7</td>\n",
       "      <td>52</td>\n",
       "      <td>4.93</td>\n",
       "      <td>1.615</td>\n",
       "      <td>18.52</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Toyota Corolla</td>\n",
       "      <td>33.9</td>\n",
       "      <td>4</td>\n",
       "      <td>71.1</td>\n",
       "      <td>65</td>\n",
       "      <td>4.22</td>\n",
       "      <td>1.835</td>\n",
       "      <td>19.90</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Toyota Corona</td>\n",
       "      <td>21.5</td>\n",
       "      <td>4</td>\n",
       "      <td>120.1</td>\n",
       "      <td>97</td>\n",
       "      <td>3.70</td>\n",
       "      <td>2.465</td>\n",
       "      <td>20.01</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Dodge Challenger</td>\n",
       "      <td>15.5</td>\n",
       "      <td>8</td>\n",
       "      <td>318.0</td>\n",
       "      <td>150</td>\n",
       "      <td>2.76</td>\n",
       "      <td>3.520</td>\n",
       "      <td>16.87</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>AMC Javelin</td>\n",
       "      <td>15.2</td>\n",
       "      <td>8</td>\n",
       "      <td>304.0</td>\n",
       "      <td>150</td>\n",
       "      <td>3.15</td>\n",
       "      <td>3.435</td>\n",
       "      <td>17.30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Camaro Z28</td>\n",
       "      <td>13.3</td>\n",
       "      <td>8</td>\n",
       "      <td>350.0</td>\n",
       "      <td>245</td>\n",
       "      <td>3.73</td>\n",
       "      <td>3.840</td>\n",
       "      <td>15.41</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Pontiac Firebird</td>\n",
       "      <td>19.2</td>\n",
       "      <td>8</td>\n",
       "      <td>400.0</td>\n",
       "      <td>175</td>\n",
       "      <td>3.08</td>\n",
       "      <td>3.845</td>\n",
       "      <td>17.05</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Fiat X1-9</td>\n",
       "      <td>27.3</td>\n",
       "      <td>4</td>\n",
       "      <td>79.0</td>\n",
       "      <td>66</td>\n",
       "      <td>4.08</td>\n",
       "      <td>1.935</td>\n",
       "      <td>18.90</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Porsche 914-2</td>\n",
       "      <td>26.0</td>\n",
       "      <td>4</td>\n",
       "      <td>120.3</td>\n",
       "      <td>91</td>\n",
       "      <td>4.43</td>\n",
       "      <td>2.140</td>\n",
       "      <td>16.70</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Lotus Europa</td>\n",
       "      <td>30.4</td>\n",
       "      <td>4</td>\n",
       "      <td>95.1</td>\n",
       "      <td>113</td>\n",
       "      <td>3.77</td>\n",
       "      <td>1.513</td>\n",
       "      <td>16.90</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Ford Pantera L</td>\n",
       "      <td>15.8</td>\n",
       "      <td>8</td>\n",
       "      <td>351.0</td>\n",
       "      <td>264</td>\n",
       "      <td>4.22</td>\n",
       "      <td>3.170</td>\n",
       "      <td>14.50</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Ferrari Dino</td>\n",
       "      <td>19.7</td>\n",
       "      <td>6</td>\n",
       "      <td>145.0</td>\n",
       "      <td>175</td>\n",
       "      <td>3.62</td>\n",
       "      <td>2.770</td>\n",
       "      <td>15.50</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Maserati Bora</td>\n",
       "      <td>15.0</td>\n",
       "      <td>8</td>\n",
       "      <td>301.0</td>\n",
       "      <td>335</td>\n",
       "      <td>3.54</td>\n",
       "      <td>3.570</td>\n",
       "      <td>14.60</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Volvo 142E</td>\n",
       "      <td>21.4</td>\n",
       "      <td>4</td>\n",
       "      <td>121.0</td>\n",
       "      <td>109</td>\n",
       "      <td>4.11</td>\n",
       "      <td>2.780</td>\n",
       "      <td>18.60</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   make   mpg  cyl   disp   hp  drat     wt   qsec  vs  am  \\\n",
       "0             Mazda RX4  21.0    6  160.0  110  3.90  2.620  16.46   0   1   \n",
       "1         Mazda RX4 Wag  21.0    6  160.0  110  3.90  2.875  17.02   0   1   \n",
       "2            Datsun 710  22.8    4  108.0   93  3.85  2.320  18.61   1   1   \n",
       "3        Hornet 4 Drive  21.4    6  258.0  110  3.08  3.215  19.44   1   0   \n",
       "4     Hornet Sportabout  18.7    8  360.0  175  3.15  3.440  17.02   0   0   \n",
       "5               Valiant  18.1    6  225.0  105  2.76  3.460  20.22   1   0   \n",
       "6            Duster 360  14.3    8  360.0  245  3.21  3.570  15.84   0   0   \n",
       "7             Merc 240D  24.4    4  146.7   62  3.69  3.190  20.00   1   0   \n",
       "8              Merc 230  22.8    4  140.8   95  3.92  3.150  22.90   1   0   \n",
       "9              Merc 280  19.2    6  167.6  123  3.92  3.440  18.30   1   0   \n",
       "10            Merc 280C  17.8    6  167.6  123  3.92  3.440  18.90   1   0   \n",
       "11           Merc 450SE  16.4    8  275.8  180  3.07  4.070  17.40   0   0   \n",
       "12           Merc 450SL  17.3    8  275.8  180  3.07  3.730  17.60   0   0   \n",
       "13          Merc 450SLC  15.2    8  275.8  180  3.07  3.780  18.00   0   0   \n",
       "14   Cadillac Fleetwood  10.4    8  472.0  205  2.93  5.250  17.98   0   0   \n",
       "15  Lincoln Continental  10.4    8  460.0  215  3.00  5.424  17.82   0   0   \n",
       "16    Chrysler Imperial  14.7    8  440.0  230  3.23  5.345  17.42   0   0   \n",
       "17             Fiat 128  32.4    4   78.7   66  4.08  2.200  19.47   1   1   \n",
       "18          Honda Civic  30.4    4   75.7   52  4.93  1.615  18.52   1   1   \n",
       "19       Toyota Corolla  33.9    4   71.1   65  4.22  1.835  19.90   1   1   \n",
       "20        Toyota Corona  21.5    4  120.1   97  3.70  2.465  20.01   1   0   \n",
       "21     Dodge Challenger  15.5    8  318.0  150  2.76  3.520  16.87   0   0   \n",
       "22          AMC Javelin  15.2    8  304.0  150  3.15  3.435  17.30   0   0   \n",
       "23           Camaro Z28  13.3    8  350.0  245  3.73  3.840  15.41   0   0   \n",
       "24     Pontiac Firebird  19.2    8  400.0  175  3.08  3.845  17.05   0   0   \n",
       "25            Fiat X1-9  27.3    4   79.0   66  4.08  1.935  18.90   1   1   \n",
       "26        Porsche 914-2  26.0    4  120.3   91  4.43  2.140  16.70   0   1   \n",
       "27         Lotus Europa  30.4    4   95.1  113  3.77  1.513  16.90   1   1   \n",
       "28       Ford Pantera L  15.8    8  351.0  264  4.22  3.170  14.50   0   1   \n",
       "29         Ferrari Dino  19.7    6  145.0  175  3.62  2.770  15.50   0   1   \n",
       "30        Maserati Bora  15.0    8  301.0  335  3.54  3.570  14.60   0   1   \n",
       "31           Volvo 142E  21.4    4  121.0  109  4.11  2.780  18.60   1   1   \n",
       "\n",
       "    gear  carb  \n",
       "0      4     4  \n",
       "1      4     4  \n",
       "2      4     1  \n",
       "3      3     1  \n",
       "4      3     2  \n",
       "5      3     1  \n",
       "6      3     4  \n",
       "7      4     2  \n",
       "8      4     2  \n",
       "9      4     4  \n",
       "10     4     4  \n",
       "11     3     3  \n",
       "12     3     3  \n",
       "13     3     3  \n",
       "14     3     4  \n",
       "15     3     4  \n",
       "16     3     4  \n",
       "17     4     1  \n",
       "18     4     2  \n",
       "19     4     1  \n",
       "20     3     1  \n",
       "21     3     2  \n",
       "22     3     2  \n",
       "23     3     4  \n",
       "24     3     2  \n",
       "25     4     1  \n",
       "26     5     2  \n",
       "27     5     2  \n",
       "28     5     4  \n",
       "29     5     6  \n",
       "30     5     8  \n",
       "31     4     2  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data/mtcars.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This just prints out the data, but then it's lost. To use this data, we have to give it a name, so it's stored in Python's memory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "name": "02-python-pandas-4"
   },
   "outputs": [],
   "source": [
    "mtcars = pd.read_csv('data/mtcars.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> One of the big differences between a spreadsheet program and a programming language from the data science perspective is that you have to load data into the programming language. It's not \"just there\" like Excel. This is a good thing, since it allows the common functionality of the programming language to work across multiple data sets, and also keeps the original data set pristine. Excel users can run into problems and [corrupt their data](https://nature.berkeley.edu/garbelottoat/?p=1488) if they are not careful.\n",
    "\n",
    "If we wanted to write this data set back out into an Excel file, say, we could do"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "name": "02-python-pandas-5"
   },
   "outputs": [],
   "source": [
    "mtcars.to_excel('data/mtcars.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> You may get an error if you don't have the `openpyxl` package installed. You can easily install it from the Anaconda prompt using `conda install openpyxl` and following the prompts. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring a data set\n",
    "\n",
    "We would like to get some idea about this data set. There are a bunch of functions linked to the `DataFrame` object that help us in this. First we will use `head` to see the first 8 rows of this data set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "lines_to_next_cell": 0,
    "name": "02-python-pandas-6"
   },
   "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>make</th>\n",
       "      <th>mpg</th>\n",
       "      <th>cyl</th>\n",
       "      <th>disp</th>\n",
       "      <th>hp</th>\n",
       "      <th>drat</th>\n",
       "      <th>wt</th>\n",
       "      <th>qsec</th>\n",
       "      <th>vs</th>\n",
       "      <th>am</th>\n",
       "      <th>gear</th>\n",
       "      <th>carb</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mazda RX4</td>\n",
       "      <td>21.0</td>\n",
       "      <td>6</td>\n",
       "      <td>160.0</td>\n",
       "      <td>110</td>\n",
       "      <td>3.90</td>\n",
       "      <td>2.620</td>\n",
       "      <td>16.46</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mazda RX4 Wag</td>\n",
       "      <td>21.0</td>\n",
       "      <td>6</td>\n",
       "      <td>160.0</td>\n",
       "      <td>110</td>\n",
       "      <td>3.90</td>\n",
       "      <td>2.875</td>\n",
       "      <td>17.02</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Datsun 710</td>\n",
       "      <td>22.8</td>\n",
       "      <td>4</td>\n",
       "      <td>108.0</td>\n",
       "      <td>93</td>\n",
       "      <td>3.85</td>\n",
       "      <td>2.320</td>\n",
       "      <td>18.61</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Hornet 4 Drive</td>\n",
       "      <td>21.4</td>\n",
       "      <td>6</td>\n",
       "      <td>258.0</td>\n",
       "      <td>110</td>\n",
       "      <td>3.08</td>\n",
       "      <td>3.215</td>\n",
       "      <td>19.44</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Hornet Sportabout</td>\n",
       "      <td>18.7</td>\n",
       "      <td>8</td>\n",
       "      <td>360.0</td>\n",
       "      <td>175</td>\n",
       "      <td>3.15</td>\n",
       "      <td>3.440</td>\n",
       "      <td>17.02</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Valiant</td>\n",
       "      <td>18.1</td>\n",
       "      <td>6</td>\n",
       "      <td>225.0</td>\n",
       "      <td>105</td>\n",
       "      <td>2.76</td>\n",
       "      <td>3.460</td>\n",
       "      <td>20.22</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Duster 360</td>\n",
       "      <td>14.3</td>\n",
       "      <td>8</td>\n",
       "      <td>360.0</td>\n",
       "      <td>245</td>\n",
       "      <td>3.21</td>\n",
       "      <td>3.570</td>\n",
       "      <td>15.84</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Merc 240D</td>\n",
       "      <td>24.4</td>\n",
       "      <td>4</td>\n",
       "      <td>146.7</td>\n",
       "      <td>62</td>\n",
       "      <td>3.69</td>\n",
       "      <td>3.190</td>\n",
       "      <td>20.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                make   mpg  cyl   disp   hp  drat     wt   qsec  vs  am  gear  \\\n",
       "0          Mazda RX4  21.0    6  160.0  110  3.90  2.620  16.46   0   1     4   \n",
       "1      Mazda RX4 Wag  21.0    6  160.0  110  3.90  2.875  17.02   0   1     4   \n",
       "2         Datsun 710  22.8    4  108.0   93  3.85  2.320  18.61   1   1     4   \n",
       "3     Hornet 4 Drive  21.4    6  258.0  110  3.08  3.215  19.44   1   0     3   \n",
       "4  Hornet Sportabout  18.7    8  360.0  175  3.15  3.440  17.02   0   0     3   \n",
       "5            Valiant  18.1    6  225.0  105  2.76  3.460  20.22   1   0     3   \n",
       "6         Duster 360  14.3    8  360.0  245  3.21  3.570  15.84   0   0     3   \n",
       "7          Merc 240D  24.4    4  146.7   62  3.69  3.190  20.00   1   0     4   \n",
       "\n",
       "   carb  \n",
       "0     4  \n",
       "1     4  \n",
       "2     1  \n",
       "3     1  \n",
       "4     2  \n",
       "5     1  \n",
       "6     4  \n",
       "7     2  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mtcars.head(8)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is our first look into this data. We notice a few things. Each column has a name, and each row has an *index*, starting at 0. \n",
    "\n",
    "> If you're interested in the last N rows, there is a corresponding `tail` function\n",
    "\n",
    "Let's look at the data types of each of the columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "name": "02-python-pandas-7"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "make     object\n",
       "mpg     float64\n",
       "cyl       int64\n",
       "disp    float64\n",
       "hp        int64\n",
       "drat    float64\n",
       "wt      float64\n",
       "qsec    float64\n",
       "vs        int64\n",
       "am        int64\n",
       "gear      int64\n",
       "carb      int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mtcars.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This tells us that some of the variables, like `mpg` and `disp`, are floating point (decimal) numbers, several are integers, and `make` is an \"object\". The `dtypes` function borrows from `numpy`, where there isn't really a type for character or categorical variables. So most often, when you see \"object\" in the output of `dtypes`, you think it's a character or categorical variable. \n",
    "\n",
    "We can also look at the data structure in a bit more detail."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "name": "02-python-pandas-8"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 32 entries, 0 to 31\n",
      "Data columns (total 12 columns):\n",
      " #   Column  Non-Null Count  Dtype  \n",
      "---  ------  --------------  -----  \n",
      " 0   make    32 non-null     object \n",
      " 1   mpg     32 non-null     float64\n",
      " 2   cyl     32 non-null     int64  \n",
      " 3   disp    32 non-null     float64\n",
      " 4   hp      32 non-null     int64  \n",
      " 5   drat    32 non-null     float64\n",
      " 6   wt      32 non-null     float64\n",
      " 7   qsec    32 non-null     float64\n",
      " 8   vs      32 non-null     int64  \n",
      " 9   am      32 non-null     int64  \n",
      " 10  gear    32 non-null     int64  \n",
      " 11  carb    32 non-null     int64  \n",
      "dtypes: float64(5), int64(6), object(1)\n",
      "memory usage: 3.1+ KB\n"
     ]
    }
   ],
   "source": [
    "mtcars.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This tells us that this is indeed a `DataFrame`, wth 12 columns, each with 32 valid observations. Each row has an index value ranging from 0 to 11. We also get the approximate size of this object in memory.\n",
    "\n",
    "You can also quickly find the number of rows and columns of a data set by using `shape`, which is borrowed from numpy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "name": "02-python-pandas-9"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(32, 12)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mtcars.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "More generally, we can get a summary of each variable using the `describe` function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "lines_to_next_cell": 0,
    "name": "02-python-pandas-10"
   },
   "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>mpg</th>\n",
       "      <th>cyl</th>\n",
       "      <th>disp</th>\n",
       "      <th>hp</th>\n",
       "      <th>drat</th>\n",
       "      <th>wt</th>\n",
       "      <th>qsec</th>\n",
       "      <th>vs</th>\n",
       "      <th>am</th>\n",
       "      <th>gear</th>\n",
       "      <th>carb</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>32.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>32.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>20.090625</td>\n",
       "      <td>6.187500</td>\n",
       "      <td>230.721875</td>\n",
       "      <td>146.687500</td>\n",
       "      <td>3.596563</td>\n",
       "      <td>3.217250</td>\n",
       "      <td>17.848750</td>\n",
       "      <td>0.437500</td>\n",
       "      <td>0.406250</td>\n",
       "      <td>3.687500</td>\n",
       "      <td>2.8125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>6.026948</td>\n",
       "      <td>1.785922</td>\n",
       "      <td>123.938694</td>\n",
       "      <td>68.562868</td>\n",
       "      <td>0.534679</td>\n",
       "      <td>0.978457</td>\n",
       "      <td>1.786943</td>\n",
       "      <td>0.504016</td>\n",
       "      <td>0.498991</td>\n",
       "      <td>0.737804</td>\n",
       "      <td>1.6152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>10.400000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>71.100000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>2.760000</td>\n",
       "      <td>1.513000</td>\n",
       "      <td>14.500000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>15.425000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>120.825000</td>\n",
       "      <td>96.500000</td>\n",
       "      <td>3.080000</td>\n",
       "      <td>2.581250</td>\n",
       "      <td>16.892500</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>2.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>19.200000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>196.300000</td>\n",
       "      <td>123.000000</td>\n",
       "      <td>3.695000</td>\n",
       "      <td>3.325000</td>\n",
       "      <td>17.710000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>2.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>22.800000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>326.000000</td>\n",
       "      <td>180.000000</td>\n",
       "      <td>3.920000</td>\n",
       "      <td>3.610000</td>\n",
       "      <td>18.900000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>4.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>33.900000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>472.000000</td>\n",
       "      <td>335.000000</td>\n",
       "      <td>4.930000</td>\n",
       "      <td>5.424000</td>\n",
       "      <td>22.900000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>8.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             mpg        cyl        disp          hp       drat         wt  \\\n",
       "count  32.000000  32.000000   32.000000   32.000000  32.000000  32.000000   \n",
       "mean   20.090625   6.187500  230.721875  146.687500   3.596563   3.217250   \n",
       "std     6.026948   1.785922  123.938694   68.562868   0.534679   0.978457   \n",
       "min    10.400000   4.000000   71.100000   52.000000   2.760000   1.513000   \n",
       "25%    15.425000   4.000000  120.825000   96.500000   3.080000   2.581250   \n",
       "50%    19.200000   6.000000  196.300000  123.000000   3.695000   3.325000   \n",
       "75%    22.800000   8.000000  326.000000  180.000000   3.920000   3.610000   \n",
       "max    33.900000   8.000000  472.000000  335.000000   4.930000   5.424000   \n",
       "\n",
       "            qsec         vs         am       gear     carb  \n",
       "count  32.000000  32.000000  32.000000  32.000000  32.0000  \n",
       "mean   17.848750   0.437500   0.406250   3.687500   2.8125  \n",
       "std     1.786943   0.504016   0.498991   0.737804   1.6152  \n",
       "min    14.500000   0.000000   0.000000   3.000000   1.0000  \n",
       "25%    16.892500   0.000000   0.000000   3.000000   2.0000  \n",
       "50%    17.710000   0.000000   0.000000   4.000000   2.0000  \n",
       "75%    18.900000   1.000000   1.000000   4.000000   4.0000  \n",
       "max    22.900000   1.000000   1.000000   5.000000   8.0000  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mtcars.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These are usually the first steps in exploring the data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data structures and types\n",
    "\n",
    "pandas has two main data types: `Series` and `DataFrame`. These are analogous to vectors and matrices, in that a `Series` is 1-dimensional while a `DataFrame` is 2-dimensional. \n",
    "\n",
    "### pandas.Series\n",
    "\n",
    "The `Series` object holds data from a single input variable, and is required, much like numpy arrays, to be homogeneous in type. You can create `Series` objects from lists or numpy arrays quite easily"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "name": "02-python-pandas-11"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     1.0\n",
       "1     3.0\n",
       "2     5.0\n",
       "3     NaN\n",
       "4     9.0\n",
       "5    13.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = pd.Series([1,3,5,np.nan, 9, 13])\n",
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "lines_to_next_cell": 0,
    "name": "02-python-pandas-12"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      1\n",
       "1      2\n",
       "2      3\n",
       "3      4\n",
       "4      5\n",
       "5      6\n",
       "6      7\n",
       "7      8\n",
       "8      9\n",
       "9     10\n",
       "10    11\n",
       "11    12\n",
       "12    13\n",
       "13    14\n",
       "14    15\n",
       "15    16\n",
       "16    17\n",
       "17    18\n",
       "18    19\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s2 = pd.Series(np.arange(1,20))\n",
    "s2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can access elements of a `Series` much like a `dict`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "name": "02-python-pandas-13"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s2[4]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is no requirement that the index of a `Series` has to be numeric. It can be any kind of scalar object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "name": "02-python-pandas-14"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    0.375096\n",
       "b    1.766950\n",
       "c    0.133634\n",
       "d   -0.188296\n",
       "e   -1.206115\n",
       "dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s3 = pd.Series(np.random.normal(0,1, (5,)), index = ['a','b','c','d','e'])\n",
    "s3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "name": "02-python-pandas-15"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-0.18829610291631763"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s3['d']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "name": "02-python-pandas-16"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    0.375096\n",
       "b    1.766950\n",
       "c    0.133634\n",
       "d   -0.188296\n",
       "dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s3['a':'d']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Well, slicing worked, but it gave us something different than expected. It gave us both the start **and** end of the slice, which is unlike what we've encountered so far!! \n",
    "\n",
    "It turns out that in `pandas`, slicing by index actually does this. It is a discrepancy from `numpy` and Python in general that we have to be careful about. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can extract the actual values into a numpy array"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "name": "02-python-pandas-17"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0.37509614,  1.76694952,  0.13363448, -0.1882961 , -1.20611518])"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s3.to_numpy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In fact, you'll see that much of `pandas`' structures are build on top of `numpy` arrays. This is a good thing, since you can take advantage of the powerful numpy functions that are built for fast, efficient scientific computing. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Making the point about slicing again, "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "name": "02-python-pandas-18"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0.37509614, 1.76694952, 0.13363448])"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s3.to_numpy()[0:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is different from index-based slicing done earlier."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### pandas.DataFrame\n",
    "\n",
    "The `DataFrame` object holds a rectangular data set. Each column of a `DataFrame` is a `Series` object. This means that each column of a `DataFrame` must be comprised of data of the same type, but different columns can hold data of different types. This structure is extremely useful in practical data science. The invention of this structure was, in my opinion, transformative in making Python an effective data science tool."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Creating a DataFrame\n",
    "\n",
    "The `DataFrame` can be created by importing data, as we saw in the previous section. It can also be created by a few methods within Python.\n",
    "\n",
    "First, it can be created from a 2-dimensional `numpy` array."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "name": "02-python-pandas-19"
   },
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.228273</td>\n",
       "      <td>1.026890</td>\n",
       "      <td>-0.839585</td>\n",
       "      <td>-0.591182</td>\n",
       "      <td>-0.956888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-0.222326</td>\n",
       "      <td>-0.619915</td>\n",
       "      <td>1.837905</td>\n",
       "      <td>-2.053231</td>\n",
       "      <td>0.868583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-0.920734</td>\n",
       "      <td>-0.232312</td>\n",
       "      <td>2.152957</td>\n",
       "      <td>-1.334661</td>\n",
       "      <td>0.076380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-1.246089</td>\n",
       "      <td>1.202272</td>\n",
       "      <td>-1.049942</td>\n",
       "      <td>1.056610</td>\n",
       "      <td>-0.419678</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          0         1         2         3         4\n",
       "0  0.228273  1.026890 -0.839585 -0.591182 -0.956888\n",
       "1 -0.222326 -0.619915  1.837905 -2.053231  0.868583\n",
       "2 -0.920734 -0.232312  2.152957 -1.334661  0.076380\n",
       "3 -1.246089  1.202272 -1.049942  1.056610 -0.419678"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rng = np.random.RandomState(25)\n",
    "d1 = pd.DataFrame(rng.normal(0,1, (4,5)))\n",
    "d1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You will notice that it creates default column names, that are merely the column number, starting from 0. We can also create the column names and row index (similar to the `Series` index we saw earlier) directly during creation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "name": "02-python-pandas-20"
   },
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>2.294842</td>\n",
       "      <td>-2.594487</td>\n",
       "      <td>2.822756</td>\n",
       "      <td>0.680889</td>\n",
       "      <td>-1.577693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>-1.976254</td>\n",
       "      <td>0.533340</td>\n",
       "      <td>-0.290870</td>\n",
       "      <td>-0.513520</td>\n",
       "      <td>1.982626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>0.226001</td>\n",
       "      <td>-1.839905</td>\n",
       "      <td>1.607671</td>\n",
       "      <td>0.388292</td>\n",
       "      <td>0.399732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>0.405477</td>\n",
       "      <td>0.217002</td>\n",
       "      <td>-0.633439</td>\n",
       "      <td>0.246622</td>\n",
       "      <td>-1.939546</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A         B         C         D         E\n",
       "a  2.294842 -2.594487  2.822756  0.680889 -1.577693\n",
       "b -1.976254  0.533340 -0.290870 -0.513520  1.982626\n",
       "c  0.226001 -1.839905  1.607671  0.388292  0.399732\n",
       "d  0.405477  0.217002 -0.633439  0.246622 -1.939546"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d2 = pd.DataFrame(rng.normal(0,1, (4, 5)), \n",
    "                  columns = ['A','B','C','D','E'], \n",
    "                  index = ['a','b','c','d'])\n",
    "d2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> We could also create a `DataFrame` from a list of lists, as long as things line up, just as we showed for `numpy` arrays. However, to me, other ways, including the `dict` method below, make more sense."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can change the column names (which can be extracted and replaced with the `columns` attribute) and the index values (using the `index` attribute)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "name": "02-python-pandas-21"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['A', 'B', 'C', 'D', 'E'], dtype='object')"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "name": "02-python-pandas-22"
   },
   "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>V1</th>\n",
       "      <th>V2</th>\n",
       "      <th>V3</th>\n",
       "      <th>V4</th>\n",
       "      <th>V5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>2.294842</td>\n",
       "      <td>-2.594487</td>\n",
       "      <td>2.822756</td>\n",
       "      <td>0.680889</td>\n",
       "      <td>-1.577693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>-1.976254</td>\n",
       "      <td>0.533340</td>\n",
       "      <td>-0.290870</td>\n",
       "      <td>-0.513520</td>\n",
       "      <td>1.982626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>0.226001</td>\n",
       "      <td>-1.839905</td>\n",
       "      <td>1.607671</td>\n",
       "      <td>0.388292</td>\n",
       "      <td>0.399732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>0.405477</td>\n",
       "      <td>0.217002</td>\n",
       "      <td>-0.633439</td>\n",
       "      <td>0.246622</td>\n",
       "      <td>-1.939546</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         V1        V2        V3        V4        V5\n",
       "a  2.294842 -2.594487  2.822756  0.680889 -1.577693\n",
       "b -1.976254  0.533340 -0.290870 -0.513520  1.982626\n",
       "c  0.226001 -1.839905  1.607671  0.388292  0.399732\n",
       "d  0.405477  0.217002 -0.633439  0.246622 -1.939546"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d2.columns = pd.Index(['V'+str(i) for i in range(1,6)]) # Index creates the right objects for both column names and row names, which can be extracted and changed with the `index` attribute\n",
    "d2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise:** Can you explain what I did in the list comprehension above? The key points are understanding `str` and how I constructed the `range`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "name": "02-python-pandas-23"
   },
   "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>V1</th>\n",
       "      <th>V2</th>\n",
       "      <th>V3</th>\n",
       "      <th>V4</th>\n",
       "      <th>V5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>o1</th>\n",
       "      <td>2.294842</td>\n",
       "      <td>-2.594487</td>\n",
       "      <td>2.822756</td>\n",
       "      <td>0.680889</td>\n",
       "      <td>-1.577693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>o2</th>\n",
       "      <td>-1.976254</td>\n",
       "      <td>0.533340</td>\n",
       "      <td>-0.290870</td>\n",
       "      <td>-0.513520</td>\n",
       "      <td>1.982626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>o3</th>\n",
       "      <td>0.226001</td>\n",
       "      <td>-1.839905</td>\n",
       "      <td>1.607671</td>\n",
       "      <td>0.388292</td>\n",
       "      <td>0.399732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>o4</th>\n",
       "      <td>0.405477</td>\n",
       "      <td>0.217002</td>\n",
       "      <td>-0.633439</td>\n",
       "      <td>0.246622</td>\n",
       "      <td>-1.939546</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          V1        V2        V3        V4        V5\n",
       "o1  2.294842 -2.594487  2.822756  0.680889 -1.577693\n",
       "o2 -1.976254  0.533340 -0.290870 -0.513520  1.982626\n",
       "o3  0.226001 -1.839905  1.607671  0.388292  0.399732\n",
       "o4  0.405477  0.217002 -0.633439  0.246622 -1.939546"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d2.index = ['o1','o2','o3','o4']\n",
    "d2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also extract data from a homogeneous `DataFrame` to a `numpy` array"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "name": "02-python-pandas-24"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[ 0.22827309,  1.0268903 , -0.83958485, -0.59118152, -0.9568883 ],\n",
       "       [-0.22232569, -0.61991511,  1.83790458, -2.05323076,  0.86858305],\n",
       "       [-0.92073444, -0.23231186,  2.1529569 , -1.33466147,  0.07637965],\n",
       "       [-1.24608928,  1.20227231, -1.04994158,  1.05661011, -0.41967767]])"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d1.to_numpy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> It turns out that you can use `to_numpy` for a non-homogeneous `DataFrame` as well. `numpy` just makes it homogeneous by assigning each column the data type `object`. This also limits what you can do in `numpy` with the array and may require changing data types using the [`astype` function](https://numpy.org/devdocs/reference/generated/numpy.ndarray.astype.html). There is some more detail about the `object` data type in the Python Tools for Data Science ([notebook](01_python_tools_ds.ipynb#object), [PDF](01_python_tools_ds.pdf)) document."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The other easy way to create a `DataFrame` is from a `dict` object, where each component object is either a list or a numpy array, and is homogeneous in type. One exception is if a component is of size 1; then it is repeated to meet the needs of the `DataFrame`'s dimensions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "name": "02-python-pandas-25"
   },
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.958092</td>\n",
       "      <td>2020-05-12</td>\n",
       "      <td>6</td>\n",
       "      <td>yes</td>\n",
       "      <td>NIH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.883201</td>\n",
       "      <td>2020-05-12</td>\n",
       "      <td>6</td>\n",
       "      <td>no</td>\n",
       "      <td>NIH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.295432</td>\n",
       "      <td>2020-05-12</td>\n",
       "      <td>6</td>\n",
       "      <td>no</td>\n",
       "      <td>NIH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.512376</td>\n",
       "      <td>2020-05-12</td>\n",
       "      <td>6</td>\n",
       "      <td>yes</td>\n",
       "      <td>NIH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.088702</td>\n",
       "      <td>2020-05-12</td>\n",
       "      <td>6</td>\n",
       "      <td>no</td>\n",
       "      <td>NIH</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A         B          C  D    E    F\n",
       "0  3.0  0.958092 2020-05-12  6  yes  NIH\n",
       "1  3.0  0.883201 2020-05-12  6   no  NIH\n",
       "2  3.0  0.295432 2020-05-12  6   no  NIH\n",
       "3  3.0  0.512376 2020-05-12  6  yes  NIH\n",
       "4  3.0  0.088702 2020-05-12  6   no  NIH"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "    'A':3.,\n",
    "    'B':rng.random_sample(5),\n",
    "    'C': pd.Timestamp('20200512'),\n",
    "    'D': np.array([6] * 5),\n",
    "    'E': pd.Categorical(['yes','no','no','yes','no']),\n",
    "    'F': 'NIH'})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "name": "02-python-pandas-26"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5 entries, 0 to 4\n",
      "Data columns (total 6 columns):\n",
      " #   Column  Non-Null Count  Dtype         \n",
      "---  ------  --------------  -----         \n",
      " 0   A       5 non-null      float64       \n",
      " 1   B       5 non-null      float64       \n",
      " 2   C       5 non-null      datetime64[ns]\n",
      " 3   D       5 non-null      int64         \n",
      " 4   E       5 non-null      category      \n",
      " 5   F       5 non-null      object        \n",
      "dtypes: category(1), datetime64[ns](1), float64(2), int64(1), object(1)\n",
      "memory usage: 429.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We note that C is a date object, E is a category object, and F is a text/string object. pandas has excellent time series capabilities (having origins in FinTech), and the `TimeStamp` function creates datetime objects which can be queried and manipulated in Python. We'll describe category data in the next section."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also create a `DataFrame` where each column is composed of composite objects, like lists and dicts, as well. This might have limited value in some settings, but may be useful in others. In particular, this allows capabilities like the [*list-column* construct in R tibbles](https://jennybc.github.io/purrr-tutorial/ls13_list-columns.html). For example, "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "name": "02-python-pandas-27"
   },
   "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>list</th>\n",
       "      <th>tuple</th>\n",
       "      <th>set</th>\n",
       "      <th>dicts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[1, 2]</td>\n",
       "      <td>(a, b)</td>\n",
       "      <td>{A, B, C}</td>\n",
       "      <td>{'A': [1, 2, 3]}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[3, 4]</td>\n",
       "      <td>(c, d)</td>\n",
       "      <td>{D, E}</td>\n",
       "      <td>{'B': [5, 6, 8]}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[5, 6]</td>\n",
       "      <td>(e, f)</td>\n",
       "      <td>{F}</td>\n",
       "      <td>{'C': [3, 9]}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     list   tuple        set             dicts\n",
       "0  [1, 2]  (a, b)  {A, B, C}  {'A': [1, 2, 3]}\n",
       "1  [3, 4]  (c, d)     {D, E}  {'B': [5, 6, 8]}\n",
       "2  [5, 6]  (e, f)        {F}     {'C': [3, 9]}"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame({'list' :[[1,2],[3,4],[5,6]],\n",
    "             'tuple' : [('a','b'), ('c','d'), ('e','f')],\n",
    "              'set' : [{'A','B','C'}, {'D','E'}, {'F'}], \n",
    "            'dicts' : [{'A': [1,2,3]}, {'B':[5,6,8]}, {'C': [3,9]}]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Working with a DataFrame\n",
    "\n",
    "You can extract particular columns of a `DataFrame` by name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "name": "02-python-pandas-28"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    yes\n",
       "1     no\n",
       "2     no\n",
       "3    yes\n",
       "4     no\n",
       "Name: E, dtype: category\n",
       "Categories (2, object): [no, yes]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['E']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "name": "02-python-pandas-29"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.958092\n",
       "1    0.883201\n",
       "2    0.295432\n",
       "3    0.512376\n",
       "4    0.088702\n",
       "Name: B, dtype: float64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['B']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> There is also a shortcut for accessing single columns, using Python's dot (`.`) notation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "name": "02-python-pandas-30"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.958092\n",
       "1    0.883201\n",
       "2    0.295432\n",
       "3    0.512376\n",
       "4    0.088702\n",
       "Name: B, dtype: float64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.B"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> This notation can be more convenient if we need to perform operations on a single column. If we want to extract multiple columns, this notation will not work. Also, if we want to create new columns or replace existing columns, we need to use the array notation with the column name in quotes. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at slicing a `DataFrame`\n",
    "\n",
    "#### Extracting rows and columns\n",
    "\n",
    "There are two extractor functions in `pandas`:\n",
    "\n",
    "+ `loc` extracts by label (index label, column label, slice of labels, etc.\n",
    "+ `iloc` extracts by index (integers, slice objects, etc.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "name": "02-python-pandas-31"
   },
   "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>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "      <th>four</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   one  two  three  four\n",
       "a    5    3      2     8\n",
       "b    9    3      0     5\n",
       "c    8    4      3     3\n",
       "d    5    2      7     1\n",
       "e    6    7      8     7"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.DataFrame(rng.randint(0,10, (5,4)), \n",
    "                  index = ['a','b','c','d','e'],\n",
    "                  columns = ['one','two','three','four'])\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, let's see what naively slicing this `DataFrame` does."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "name": "02-python-pandas-32"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    5\n",
       "b    9\n",
       "c    8\n",
       "d    5\n",
       "e    6\n",
       "Name: one, dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2['one']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, that works. It grabs one column from the dataset. How about the dot notation?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "name": "02-python-pandas-33"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    5\n",
       "b    9\n",
       "c    8\n",
       "d    5\n",
       "e    6\n",
       "Name: one, dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.one"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see what this produces."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "name": "02-python-pandas-34"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(df2.one)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So this is a series, so we can potentially do slicing of this series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "name": "02-python-pandas-35"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.one['b']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "name": "02-python-pandas-36"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "b    9\n",
       "c    8\n",
       "d    5\n",
       "Name: one, dtype: int64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.one['b':'d']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "name": "02-python-pandas-37"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    5\n",
       "b    9\n",
       "c    8\n",
       "Name: one, dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.one[:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, so we have all the `Series` slicing available. The problem here is in semantics, in that we are grabbing one column and then slicing the rows. That doesn't quite work with our sense that a `DataFrame` is a rectangle with rows and columns, and we tend to think of rows, then columns. \n",
    "\n",
    "Let's see if we can do column slicing with this. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "name": "02-python-pandas-38"
   },
   "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>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "      <th>four</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   one  two  three  four\n",
       "a    5    3      2     8\n",
       "b    9    3      0     5\n",
       "c    8    4      3     3\n",
       "d    5    2      7     1\n",
       "e    6    7      8     7"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2[:'two']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's not what we want, of course. It's giving back the entire data frame. We'll come back to this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "name": "02-python-pandas-39"
   },
   "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>one</th>\n",
       "      <th>three</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   one  three\n",
       "a    5      2\n",
       "b    9      0\n",
       "c    8      3\n",
       "d    5      7\n",
       "e    6      8"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2[['one','three']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That works correctly though. We can give a list of column names. Ok. \n",
    "\n",
    "How about row slices?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "name": "02-python-pandas-40"
   },
   "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>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "      <th>four</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   one  two  three  four\n",
       "a    5    3      2     8\n",
       "b    9    3      0     5\n",
       "c    8    4      3     3"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#df2['a'] # Doesn't work\n",
    "df2['a':'c'] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, that works. It slices rows, but includes the largest index, like a `Series` but unlike `numpy` arrays. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "name": "02-python-pandas-41"
   },
   "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>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "      <th>four</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   one  two  three  four\n",
       "a    5    3      2     8\n",
       "b    9    3      0     5"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2[0:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Slices by location work too, but use the `numpy` slicing rules. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This entire extraction method becomes confusing. Let's simplify things for this, and then move on to more consistent ways to extract elements of a `DataFrame`. Let's agree on two things. If we're going the direct extraction route, \n",
    "\n",
    "1. We will extract single columns of a `DataFrame` with `[]` or `.`, i.e., `df2['one']` or `df2.one`\n",
    "1. We will extract slices of rows of a `DataFrame` using location only, i.e., `df2[:3]`. \n",
    "\n",
    "For everything else, we'll use two functions, `loc` and `iloc`.\n",
    "\n",
    "+ `loc` extracts elements like a matrix, using index and columns\n",
    "+ `iloc` extracts elements like a matrix, using location"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "name": "02-python-pandas-42"
   },
   "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>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   one  two  three\n",
       "a    5    3      2\n",
       "b    9    3      0\n",
       "c    8    4      3\n",
       "d    5    2      7\n",
       "e    6    7      8"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.loc[:,'one':'three']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "name": "02-python-pandas-43"
   },
   "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>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "      <th>four</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   one  two  three  four\n",
       "a    5    3      2     8\n",
       "b    9    3      0     5\n",
       "c    8    4      3     3\n",
       "d    5    2      7     1"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.loc['a':'d',:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "name": "02-python-pandas-44"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.loc['b', 'three']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So `loc` works just like a matrix, but with `pandas` slicing rules (include largest index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "name": "02-python-pandas-45"
   },
   "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>two</th>\n",
       "      <th>three</th>\n",
       "      <th>four</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   two  three  four\n",
       "a    3      2     8\n",
       "b    3      0     5\n",
       "c    4      3     3\n",
       "d    2      7     1\n",
       "e    7      8     7"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.iloc[:,1:4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "name": "02-python-pandas-46"
   },
   "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>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "      <th>four</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   one  two  three  four\n",
       "b    9    3      0     5\n",
       "c    8    4      3     3"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.iloc[1:3,:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "name": "02-python-pandas-47"
   },
   "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>two</th>\n",
       "      <th>three</th>\n",
       "      <th>four</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   two  three  four\n",
       "b    3      0     5\n",
       "c    4      3     3"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.iloc[1:3, 1:4]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`iloc` slices like a matrix, but uses `numpy` slicing conventions (does **not** include highest index)\n",
    "\n",
    "If we want to extract a single element from a dataset, there are two functions available, `iat` and `at`, with behavior corresponding to `iloc` and `loc`, respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.iat[2,3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "df2.at['b','three']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Boolean selection\n",
    "\n",
    "We can also use tests to extract data from a `DataFrame`. For example, we can extract only rows where column labeled `one` is greater than 3. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-48"
   },
   "outputs": [],
   "source": [
    "df2[df2.one > 3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also do composite tests. Here we ask for rows where `one` is greater than 3 and `three` is less than 9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-49"
   },
   "outputs": [],
   "source": [
    "df2[(df2.one > 3) & (df2.three < 9)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### `query`\n",
    "\n",
    "`DataFrame`'s have a `query` method allowing selection using a Python expression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-50"
   },
   "outputs": [],
   "source": [
    "n = 10\n",
    "df = pd.DataFrame(np.random.rand(n, 3), columns = list('abc'))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-51"
   },
   "outputs": [],
   "source": [
    "df[(df.a < df.b) & (df.b < df.c)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can equivalently write this query as "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2,
    "name": "02-python-pandas-52"
   },
   "outputs": [],
   "source": [
    "df.query('(a < b) & (b < c)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Replacing values in a DataFrame\n",
    "\n",
    "We can replace values within a DataFrame either by position or using a query. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['one'] = [2,5,2,5,2]\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.iat[2,3] = -9 # missing value\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now replace values using `replace` which is more flexible. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.replace(0, -9) # replace 0 with -9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.replace({2: 2.5, 8: 6.5}) # multiple replacements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.replace({'one': {5: 500}, 'three': {0: -9, 8: 800}}) \n",
    "# different replacements in different columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "See more examples in the [documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.replace.html?highlight=replace#pandas.DataFrame.replace)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Categorical data\n",
    "\n",
    "`pandas` provides a `Categorical` function and a `category` object type to Python. This type is analogous to the `factor` data type in R. It is meant to address categorical or discrete variables, where we need to use them in analyses. Categorical variables typically take on a small number of unique values, like gender, blood type, country of origin, race, etc. \n",
    "\n",
    "You can create categorical `Series` in a couple of ways:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-53"
   },
   "outputs": [],
   "source": [
    "s = pd.Series(['a','b','c'], dtype='category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-54"
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame({\n",
    "    'A':3.,\n",
    "    'B':rng.random_sample(5),\n",
    "    'C': pd.Timestamp('20200512'),\n",
    "    'D': np.array([6] * 5),\n",
    "    'E': pd.Categorical(['yes','no','no','yes','no']),\n",
    "    'F': 'NIH'})\n",
    "df['F'].astype('category')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also create `DataFrame`'s where each column is categorical"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-55"
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'A': list('abcd'), 'B': list('bdca')})\n",
    "df_cat = df.astype('category')\n",
    "df_cat.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can explore categorical data in a variety of ways"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-56"
   },
   "outputs": [],
   "source": [
    "df_cat['A'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-57"
   },
   "outputs": [],
   "source": [
    "df['A'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One issue with categories is that, if a particular level of a category is not seen before, it can create an error. So you can pre-specify the categories you expect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-58"
   },
   "outputs": [],
   "source": [
    "df_cat['B'] = pd.Categorical(list('aabb'), categories = ['a','b','c','d'])\n",
    "df_cat['B'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Re-organizing categories\n",
    "\n",
    "In categorical data, there is often the concept of a \"first\" or \"reference\" category, and an ordering of categories. This tends to be important in both visualization as well as in regression modeling. Both aspects of a category can be addressed using the `reorder_categories` function.\n",
    "\n",
    "In our earlier example, we can see that the `A` variable has 4 categories, with the \"first\" category being \"a\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cat.A"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Suppose we want to change this ordering to the reverse ordering, where\n",
    "\"d\" is the \"first\" category, and then it goes in reverse order. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cat['A'] = df_cat.A.cat.reorder_categories(['d','c','b','a'])\n",
    "df_cat.A"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Missing data\n",
    "\n",
    "Both `numpy` and `pandas` allow for missing values, which are a reality in data science. The missing values are coded as `np.nan`. Let's create some data and force some missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-59"
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame(np.random.randn(5, 3), index = ['a','c','e', 'f','g'], columns = ['one','two','three']) # pre-specify index and column names\n",
    "df['four'] = 20 # add a column named \"four\", which will all be 20\n",
    "df['five'] = df['one'] > 0\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-60"
   },
   "outputs": [],
   "source": [
    "df2 = df.reindex(['a','b','c','d','e','f','g'])\n",
    "df2.style.applymap(lambda x: 'background-color:yellow', subset = pd.IndexSlice[['b','d'],:])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The code above is creating new blank rows based on the new index values, some of which are present in the existing data and some of which are missing.\n",
    "\n",
    "We can create *masks* of the data indicating where missing values reside in a data set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-61"
   },
   "outputs": [],
   "source": [
    "df2.isna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-62"
   },
   "outputs": [],
   "source": [
    "df2['one'].notna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can obtain complete data by dropping any row that has any missing value. This is called *complete case analysis*, and you should be very careful using it. It is *only* valid if we belive that the missingness is missing at random, and not related to some characteristic of the data or the data gathering process. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-63"
   },
   "outputs": [],
   "source": [
    "df2.dropna(how='any')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also fill in, or *impute*, missing values. This can be done using a single value.."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-64"
   },
   "outputs": [],
   "source": [
    "out1 = df2.fillna(value = 5)\n",
    "\n",
    "out1.style.applymap(lambda x: 'background-color:yellow', subset = pd.IndexSlice[['b','d'],:])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "or a computed value like a column mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-65"
   },
   "outputs": [],
   "source": [
    "df3 = df2.copy()\n",
    "df3 = df3.select_dtypes(exclude=[object])   # remove non-numeric columns\n",
    "out2 = df3.fillna(df3.mean())  # df3.mean() computes column-wise means\n",
    "\n",
    "out2.style.applymap(lambda x: 'background-color:yellow', subset = pd.IndexSlice[['b','d'],:])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also impute based on the principle of *last value carried forward* which is common in time series. This means that the missing value is imputed with the previous recorded value. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-66"
   },
   "outputs": [],
   "source": [
    "out3 = df2.fillna(method = 'ffill') # Fill forward\n",
    "\n",
    "out3.style.applymap(lambda x: 'background-color:yellow', subset = pd.IndexSlice[['b','d'],:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-67"
   },
   "outputs": [],
   "source": [
    "out4 = df2.fillna(method = 'bfill') # Fill backward\n",
    "\n",
    "out4.style.applymap(lambda x: 'background-color:yellow', subset = pd.IndexSlice[['b','d'],:])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data transformation\n",
    "\n",
    "### Arithmetic operations\n",
    "\n",
    "If you have a `Series` or `DataFrame` that is all numeric, you can add or multiply single numbers to all the elements together."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-68"
   },
   "outputs": [],
   "source": [
    "A = pd.DataFrame(np.random.randn(4,5))\n",
    "print(A)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-69"
   },
   "outputs": [],
   "source": [
    "print(A + 6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-70"
   },
   "outputs": [],
   "source": [
    "print(A * -10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you have two compatible (same dimension) numeric `DataFrame`s, you can add, subtract, multiply and divide elementwise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-71"
   },
   "outputs": [],
   "source": [
    "B = pd.DataFrame(np.random.randn(4,5) + 4)\n",
    "print(A + B)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-72"
   },
   "outputs": [],
   "source": [
    "print(A * B)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you have a `Series` with the same number of elements as the number of columns of a `DataFrame`, you can do arithmetic operations, with each element of the `Series` acting upon each column of the `DataFrame`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-73"
   },
   "outputs": [],
   "source": [
    "c = pd.Series([1,2,3,4,5])\n",
    "print(A + c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 0,
    "name": "02-python-pandas-74"
   },
   "outputs": [],
   "source": [
    "print(A * c)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This idea can be used to standardize a dataset, i.e. make each column have mean 0 and standard deviation 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-75"
   },
   "outputs": [],
   "source": [
    "means = A.mean(axis=0)\n",
    "stds = A.std(axis = 0)\n",
    "\n",
    "(A - means)/stds"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Concatenation of data sets\n",
    "\n",
    "Let's create some example data sets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-76"
   },
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({'A': ['a'+str(i) for i in range(4)],\n",
    "    'B': ['b'+str(i) for i in range(4)],\n",
    "    'C': ['c'+str(i) for i in range(4)],\n",
    "    'D': ['d'+str(i) for i in range(4)]})\n",
    "\n",
    "df2 =  pd.DataFrame({'A': ['a'+str(i) for i in range(4,8)],\n",
    "    'B': ['b'+str(i) for i in range(4,8)],\n",
    "    'C': ['c'+str(i) for i in range(4,8)],\n",
    "    'D': ['d'+str(i) for i in range(4,8)]})\n",
    "df3 =  pd.DataFrame({'A': ['a'+str(i) for i in range(8,12)],\n",
    "    'B': ['b'+str(i) for i in range(8,12)],\n",
    "    'C': ['c'+str(i) for i in range(8,12)],\n",
    "    'D': ['d'+str(i) for i in range(8,12)]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can concatenate these `DataFrame` objects by row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-77"
   },
   "outputs": [],
   "source": [
    "row_concatenate = pd.concat([df1, df2, df3])\n",
    "print(row_concatenate)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This stacks the dataframes together. They are literally stacked, as is evidenced by the index values being repeated. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This same exercise can be done by the `append` function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-78"
   },
   "outputs": [],
   "source": [
    "df1.append(df2).append(df3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Suppose we want to append a new row to `df1`. Lets create a new row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-79"
   },
   "outputs": [],
   "source": [
    "new_row = pd.Series(['n1','n2','n3','n4'])\n",
    "pd.concat([df1, new_row])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's a lot of missing values. The issue is that the we don't have column names in the `new_row`, and the indices are the same, so pandas tries to append it my making a new column. The solution is to make it a `DataFrame`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-80"
   },
   "outputs": [],
   "source": [
    "new_row = pd.DataFrame([['n1','n2','n3','n4']], columns = ['A','B','C','D'])\n",
    "print(new_row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-81"
   },
   "outputs": [],
   "source": [
    "pd.concat([df1, new_row])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "or"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-82"
   },
   "outputs": [],
   "source": [
    "df1.append(new_row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Adding columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-83"
   },
   "outputs": [],
   "source": [
    "pd.concat([df1,df2,df3], axis = 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The option `axis=1` ensures that concatenation happens by columns. The default value `axis = 0` concatenates by rows."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's play a little game. Let's change the column names of `df2` and `df3` so they are not the same as `df1`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-84"
   },
   "outputs": [],
   "source": [
    "df2.columns = ['E','F','G','H']\n",
    "df3.columns = ['A','D','F','H']\n",
    "pd.concat([df1,df2,df3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now pandas ensures that all column names are represented in the new data frame, but with missing values where the row indices and column indices are mismatched. Some of this can be avoided by only joining on common columns. This is done using the `join` option ir `concat`. The default value is 'outer`, which is what you see. above"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-85"
   },
   "outputs": [],
   "source": [
    "pd.concat([df1, df3], join = 'inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can do the same thing when joining by rows, using `axis = 0` and `join=\"inner\"` to only join on rows with matching indices. Reminder that the indices are just labels and happen to be the row numbers by default. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merging data sets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For this section we'll use a set of data from a survey, also used by Daniel Chen in \"Pandas for Everyone\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-86"
   },
   "outputs": [],
   "source": [
    "person = pd.read_csv('data/survey_person.csv')\n",
    "site = pd.read_csv('data/survey_site.csv')\n",
    "survey = pd.read_csv('data/survey_survey.csv')\n",
    "visited = pd.read_csv('data/survey_visited.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-87"
   },
   "outputs": [],
   "source": [
    "print(person)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-88"
   },
   "outputs": [],
   "source": [
    "print(site)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-89"
   },
   "outputs": [],
   "source": [
    "print(survey)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-90"
   },
   "outputs": [],
   "source": [
    "print(visited)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are basically four kinds of joins:\n",
    "\n",
    "| pandas | R          | SQL         | Description                     |\n",
    "| ------ | ---------- | ----------- | ------------------------------- |\n",
    "| left   | left_join  | left outer  | keep all rows on left           |\n",
    "| right  | right_join | right outer | keep all rows on right          |\n",
    "| outer  | outer_join | full outer  | keep all rows from both         |\n",
    "| inner  | inner_join | inner       | keep only rows with common keys |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](graphs/joins.png)\n",
    "\n",
    "The terms `left` and `right` refer to which data set you call first and second respectively. \n",
    "\n",
    "We start with an left join\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-91"
   },
   "outputs": [],
   "source": [
    "s2v_merge = survey.merge(visited, left_on = 'taken',right_on = 'ident', how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-92"
   },
   "outputs": [],
   "source": [
    "print(s2v_merge)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, the left dataset is `survey` and the right one is `visited`. Since we're doing a left join, we keed all the rows from `survey` and add columns from `visited`, matching on the common key, called \"taken\" in one dataset and \"ident\" in the other. Note that the rows of `visited` are repeated as needed to line up with all the rows with common \"taken\" values. \n",
    "\n",
    "We can now add location information, where the common key is the site code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-93"
   },
   "outputs": [],
   "source": [
    "s2v2loc_merge = s2v_merge.merge(site, how = 'left', left_on = 'site', right_on = 'name')\n",
    "print(s2v2loc_merge)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lastly, we add the person information to this dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-94"
   },
   "outputs": [],
   "source": [
    "merged = s2v2loc_merge.merge(person, how = 'left', left_on = 'person', right_on = 'ident')\n",
    "print(merged.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can merge based on multiple columns as long as they match up. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-95"
   },
   "outputs": [],
   "source": [
    "ps = person.merge(survey, left_on = 'ident', right_on = 'person')\n",
    "vs = visited.merge(survey, left_on = 'ident', right_on = 'taken')\n",
    "print(ps)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-96"
   },
   "outputs": [],
   "source": [
    "print(vs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-97"
   },
   "outputs": [],
   "source": [
    "ps_vs = ps.merge(vs, \n",
    "                left_on = ['ident','taken', 'quant','reading'],\n",
    "                right_on = ['person','ident','quant','reading']) # The keys need to correspond\n",
    "ps_vs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that since there are common column names, the merge appends `_x` and `_y` to denote which column came from the left and right, respectively.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tidy data principles and reshaping datasets\n",
    "\n",
    "The tidy data principle is a principle espoused by Dr. Hadley Wickham, one of the foremost R developers. [Tidy data](http://vita.had.co.nz/papers/tidy-data.pdf) is a structure for datasets to make them more easily analyzed on computers. The basic principles are\n",
    "\n",
    "+ Each row is an observation\n",
    "+ Each column is a variable\n",
    "+ Each type of observational unit forms a table\n",
    "\n",
    "> Tidy data is tidy in one way. Untidy data can be untidy in many ways\n",
    "\n",
    "Let's look at some examples."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-98"
   },
   "outputs": [],
   "source": [
    "from glob import glob\n",
    "filenames = sorted(glob('data/table*.csv')) # find files matching pattern. I know there are 6 of them\n",
    "table1, table2, table3, table4a, table4b, table5 = [pd.read_csv(f) for f in filenames] # Use a list comprehension"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This code imports data from 6 files matching a pattern. Python allows multiple assignments on the left of the `=`, and as each dataset is imported, it gets assigned in order to the variables on the left. In the second line I sort the file names so that they match the order in which I'm storing them in the 3rd line. The function `glob` does pattern-matching of file names. \n",
    "\n",
    "The following tables refer to the number of TB cases and population in Afghanistan, Brazil and China in 1999 and 2000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-99"
   },
   "outputs": [],
   "source": [
    "print(table1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 0,
    "name": "02-python-pandas-100"
   },
   "outputs": [],
   "source": [
    "print(table2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-101"
   },
   "outputs": [],
   "source": [
    "print(table3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-102"
   },
   "outputs": [],
   "source": [
    "print(table4a) # cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-103"
   },
   "outputs": [],
   "source": [
    "print(table4b) # population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-104"
   },
   "outputs": [],
   "source": [
    "print(table5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise:** Describe why and why not each of these datasets are tidy."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Melting (unpivoting) data\n",
    "\n",
    "Melting is the operation of collapsing multiple columns into 2 columns, where one column is formed by the old column names, and the other by the corresponding values. Some columns may be kept fixed and their data are repeated to maintain the interrelationships between the variables.\n",
    "\n",
    "We'll start with loading some data on income and religion in the US from the Pew Research Center."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-105"
   },
   "outputs": [],
   "source": [
    "pew = pd.read_csv('data/pew.csv')\n",
    "print(pew.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This dataset is considered in \"wide\" format. There are several issues with it, including the fact that column headers have data. Those column headers are income groups, that should be a column by tidy principles. Our job is to turn this dataset into \"long\" format with a column for income group. \n",
    "\n",
    "We will use the function `melt` to achieve this. This takes a few parameters:\n",
    "\n",
    "+ **id_vars** is a list of variables that will remain as is\n",
    "+ **value_vars** is a list of column nmaes that we will melt (or unpivot). By default, it will melt all columns not mentioned in id_vars\n",
    "+ **var_name** is a string giving the name of the new column created by the headers (default: `variable`)\n",
    "+ **value_name** is a string giving the name of the new column created by the values (default: `value`)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-106"
   },
   "outputs": [],
   "source": [
    "pew_long = pew.melt(id_vars = ['religion'], var_name = 'income_group', value_name = 'count')\n",
    "print(pew_long.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Separating columns containing multiple variables\n",
    "\n",
    "We will use an Ebola dataset to illustrate this principle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-107"
   },
   "outputs": [],
   "source": [
    "ebola = pd.read_csv('data/country_timeseries.csv')\n",
    "print(ebola.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that for each country we have two columns -- one for cases (number infected) and one for deaths. Ideally we want one column for country, one for cases and one for deaths. \n",
    "\n",
    "The first step will be to melt this data sets so that the column headers in question from a column and the corresponding data forms a second column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-108"
   },
   "outputs": [],
   "source": [
    "ebola_long = ebola.melt(id_vars = ['Date','Day'])\n",
    "print(ebola_long.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now need to split the data in the `variable` column to make two columns. One will contain the country name and the other either Cases or Deaths. We will use some string manipulation functions that we will see later to achieve this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-109"
   },
   "outputs": [],
   "source": [
    "variable_split = ebola_long['variable'].str.split('_', expand=True) # split on the `_` character\n",
    "print(variable_split[:5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `expand=True` option forces the creation of an `DataFrame` rather than a list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-110"
   },
   "outputs": [],
   "source": [
    "type(variable_split)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now concatenate this to the original data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-111"
   },
   "outputs": [],
   "source": [
    "variable_split.columns = ['status','country']\n",
    "\n",
    "ebola_parsed = pd.concat([ebola_long, variable_split], axis = 1)\n",
    "\n",
    "ebola_parsed.drop('variable', axis = 1, inplace=True) # Remove the column named \"variable\" and replace the old data with the new one in the same location\n",
    "\n",
    "print(ebola_parsed.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivot/spread datasets\n",
    "\n",
    "If we wanted to, we could also make two columns based on cases and deaths, so for each country and date you could easily read off the cases and deaths. This is achieved using the `pivot_table` function.\n",
    "\n",
    "In the `pivot_table` syntax, `index` refers to the columns we don't want to change, `columns` refers to the column whose values will form the column names of the new columns, and `values` is the name of the column that will form the values in the pivoted dataset. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-112"
   },
   "outputs": [],
   "source": [
    "ebola_parsed.pivot_table(index = ['Date','Day', 'country'], columns = 'status', values = 'value')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This creates something called `MultiIndex` in the `pandas` `DataFrame`. This is useful in some advanced cases, but here, we just want a normal `DataFrame` back. We can achieve that by using the `reset_index` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-113"
   },
   "outputs": [],
   "source": [
    "ebola_parsed.pivot_table(index = ['Date','Day','country'], columns = 'status', values = 'value').reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pivoting is a 2-column to many-column operation, with the number of columns formed depending on the number of unique values present in the column of the original data that is entered into the `columns` argument of `pivot_table`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise:** Load the file `weather.csv` into Python and work on making it a tidy dataset. It requires melting and pivoting. The dataset comprises of the maximun and minimum temperatures recorded each day in 2010. There are lots of missing value. Ultimately we want columns for days of the month, maximum temperature and minimum tempearture along with the location ID, the year and the month."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data aggregation and split-apply-combine\n",
    "\n",
    "We'll use the Gapminder dataset for this section"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 0,
    "name": "02-python-pandas-114"
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/gapminder.tsv', sep = '\\t') # data is tab-separated, so we use `\\t` to specify that"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The paradigm we will be exploring is often called *split-apply-combine* or MapReduce or grouped aggregation. The basic idea is that you split a data set up by some feature, apply a recipe to each piece, compute the result, and then put the results back together into a dataset. This can be described in teh following schematic."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](graphs/split-apply-combine.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`pandas` is set up for this. It features the `groupby` function that allows the \"split\" part of the operation. We can then apply a function to each part and put it back together. Let's see how."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-115"
   },
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-116"
   },
   "outputs": [],
   "source": [
    "f\"This dataset has {len(df['country'].unique())} countries in it\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One of the variables in this dataset is life expectancy at birth, `lifeExp`. Suppose we want to find the average life expectancy of each country over the period of study."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-117"
   },
   "outputs": [],
   "source": [
    "df.groupby('country')['lifeExp'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So what's going on here? First, we use the `groupby` function, telling `pandas` to split the dataset up by values of the column `country`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-118"
   },
   "outputs": [],
   "source": [
    "df.groupby('country')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`pandas` won't show you the actual data, but will tell you that it is a grouped dataframe object. This means that each element of this object is a `DataFrame` with data from one country."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-119"
   },
   "outputs": [],
   "source": [
    "df.groupby('country').ngroups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-120"
   },
   "outputs": [],
   "source": [
    "df.groupby('country').get_group('United Kingdom')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-121"
   },
   "outputs": [],
   "source": [
    "type(df.groupby('country').get_group('United Kingdom'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-122"
   },
   "outputs": [],
   "source": [
    "avg_lifeexp_country = df.groupby('country').lifeExp.mean()\n",
    "avg_lifeexp_country['United Kingdom']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-123"
   },
   "outputs": [],
   "source": [
    "df.groupby('country').get_group('United Kingdom').lifeExp.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at if life expectancy has gone up over time, by continent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-124"
   },
   "outputs": [],
   "source": [
    "df.groupby(['continent','year']).lifeExp.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-125"
   },
   "outputs": [],
   "source": [
    "avg_lifeexp_continent_yr = df.groupby(['continent','year']).lifeExp.mean().reset_index()\n",
    "avg_lifeexp_continent_yr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-126"
   },
   "outputs": [],
   "source": [
    "type(avg_lifeexp_continent_yr)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The aggregation function, in this case `mean`, does both the \"apply\" and \"combine\" parts of the process."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can do quick aggregations with `pandas`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-127"
   },
   "outputs": [],
   "source": [
    "df.groupby('continent').lifeExp.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-128"
   },
   "outputs": [],
   "source": [
    "df.groupby('continent').nth(10) # Tenth observation in each group"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also use functions from other modules, or your own functions in this aggregation work."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-129"
   },
   "outputs": [],
   "source": [
    "df.groupby('continent').lifeExp.agg(np.mean)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-130"
   },
   "outputs": [],
   "source": [
    "def my_mean(values):\n",
    "    n = len(values)\n",
    "    sum = 0\n",
    "    for value in values:\n",
    "        sum += value\n",
    "    return(sum/n)\n",
    "\n",
    "df.groupby('continent').lifeExp.agg(my_mean)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can do many functions at once"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-131"
   },
   "outputs": [],
   "source": [
    "df.groupby('year').lifeExp.agg([np.count_nonzero, np.mean, np.std])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also aggregate on different columns at the same time by passing a `dict` to the `agg` function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-132"
   },
   "outputs": [],
   "source": [
    "df.groupby('year').agg({'lifeExp': np.mean,'pop': np.median,'gdpPercap': np.median}).reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Transformation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can do grouped transformations using this same method. We will compute the z-score for each year, i.e. we will substract the average life expectancy and divide by the standard deviation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-133"
   },
   "outputs": [],
   "source": [
    "def my_zscore(values):\n",
    "    m = np.mean(values)\n",
    "    s = np.std(values)\n",
    "    return((values - m)/s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-134"
   },
   "outputs": [],
   "source": [
    "df.groupby('year').lifeExp.transform(my_zscore)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-135"
   },
   "outputs": [],
   "source": [
    "df['lifeExp_z'] = df.groupby('year').lifeExp.transform(my_zscore)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "name": "02-python-pandas-136"
   },
   "outputs": [],
   "source": [
    "df.groupby('year').lifeExp_z.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Filter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can split the dataset by values of one variable, and filter out those splits that fail some criterion. The following code only keeps countries with a population of at least 10 million at some point during the study period"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 0,
    "name": "02-python-pandas-137"
   },
   "outputs": [],
   "source": [
    "df.groupby('country').filter(lambda d: d['pop'].max() > 10000000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "jupytext": {
   "formats": "ipynb,Rmd"
  },
  "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.8.2"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}