Pandas cheat sheet.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas cheat sheet\n",
    "\n",
    "This notebook has some common data manipulations you might do while working in the popular Python data analysis library [`pandas`](https://pandas.pydata.org/). It assumes you're already are set up to analyze data in pandas using Python 3.\n",
    "\n",
    "(If you're _not_ set up, [here's IRE's guide](https://docs.google.com/document/d/1cYmpfZEZ8r-09Q6Go917cKVcQk_d0P61gm0q8DAdIdg/edit#) to setting up Python. [Hit me up](mailto:cody@ire.org) if you get stuck.)\n",
    "\n",
    "### Topics\n",
    "- [Importing pandas](#Importing-pandas)\n",
    "- [Creating a dataframe from a CSV](#Creating-a-dataframe-from-a-CSV)\n",
    "- [Checking out the data](#Checking-out-the-data)\n",
    "- [Selecting columns of data](#Selecting-columns-of-data)\n",
    "- [Getting unique values in a column](#Getting-unique-values-in-a-column)\n",
    "- [Running basic summary stats](#Running-basic-summary-stats)\n",
    "- [Sorting your data](#Sorting-your-data)\n",
    "- [Filtering rows of data](#Filtering-rows-of-data)\n",
    "- [Filtering text columns with string methods](#Filtering-text-columns-with-string-methods)\n",
    "- [Filtering against multiple values](#Filtering-against-multiple-values)\n",
    "- [Exclusion filtering](#Exclusion-filtering)\n",
    "- [Adding a calculated column](#Adding-a-calculated-column)\n",
    "- [Filtering for nulls](#Filtering-for-nulls)\n",
    "- [Grouping and aggregating data](#Grouping-and-aggregating-data)\n",
    "- [Pivot tables](#Pivot-tables)\n",
    "- [Applying a function across rows](#Applying-a-function-across-rows)\n",
    "- [Joining data](#Joining-data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing pandas\n",
    "\n",
    "Before we can use pandas, we need to import it. The most common way to do this is:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating a dataframe from a CSV\n",
    "\n",
    "To begin with, let's import a CSV of Major League Baseball player salaries on opening day. The file, which is in the same directory as this notebook, is called `mlb.csv`.\n",
    "\n",
    "Pandas has a `read_csv()` method that we can use to get this data into a [dataframe](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html) (it has methods to read other file types, too). At minimum, you need to tell this method where the file lives:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "mlb = pd.read_csv('mlb.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checking out the data\n",
    "\n",
    "When you first load up your data, you'll want to get a sense of what's in there. A pandas dataframe has several useful things to help you get a quick read of your data:\n",
    "\n",
    "- `.head()`: Shows you the first 5 records in the data frame (optionally, if you want to see a different number of records, you can pass in a number)\n",
    "- `.tail()`: Same as `head()`, but it pull records from the end of the dataframe\n",
    "- `.sample(n)` will give you a sample of *n* rows of the data -- just pass in a number\n",
    "- `.info()` will give you a count of non-null values in each column -- useful for seeing if any columns have null values\n",
    "- `.describe()` will compute summary stats for numeric columns\n",
    "- `.columns` will list the column names\n",
    "- `.dtypes` will list the data types of each column\n",
    "- `.shape` will give you a pair of numbers: _(number of rows, number of columns)_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>POS</th>\n",
       "      <th>SALARY</th>\n",
       "      <th>START_YEAR</th>\n",
       "      <th>END_YEAR</th>\n",
       "      <th>YEARS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Clayton Kershaw</td>\n",
       "      <td>LAD</td>\n",
       "      <td>SP</td>\n",
       "      <td>33000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2020</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Zack Greinke</td>\n",
       "      <td>ARI</td>\n",
       "      <td>SP</td>\n",
       "      <td>31876966</td>\n",
       "      <td>2016</td>\n",
       "      <td>2021</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>David Price</td>\n",
       "      <td>BOS</td>\n",
       "      <td>SP</td>\n",
       "      <td>30000000</td>\n",
       "      <td>2016</td>\n",
       "      <td>2022</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Miguel Cabrera</td>\n",
       "      <td>DET</td>\n",
       "      <td>1B</td>\n",
       "      <td>28000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2023</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Justin Verlander</td>\n",
       "      <td>DET</td>\n",
       "      <td>SP</td>\n",
       "      <td>28000000</td>\n",
       "      <td>2013</td>\n",
       "      <td>2019</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               NAME TEAM POS    SALARY  START_YEAR  END_YEAR  YEARS\n",
       "0   Clayton Kershaw  LAD  SP  33000000        2014      2020      7\n",
       "1      Zack Greinke  ARI  SP  31876966        2016      2021      6\n",
       "2       David Price  BOS  SP  30000000        2016      2022      7\n",
       "3    Miguel Cabrera  DET  1B  28000000        2014      2023     10\n",
       "4  Justin Verlander  DET  SP  28000000        2013      2019      7"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>POS</th>\n",
       "      <th>SALARY</th>\n",
       "      <th>START_YEAR</th>\n",
       "      <th>END_YEAR</th>\n",
       "      <th>YEARS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>863</th>\n",
       "      <td>Steve Selsky</td>\n",
       "      <td>BOS</td>\n",
       "      <td>RF</td>\n",
       "      <td>535000</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>864</th>\n",
       "      <td>Stuart Turner</td>\n",
       "      <td>CIN</td>\n",
       "      <td>C</td>\n",
       "      <td>535000</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>865</th>\n",
       "      <td>Vicente Campos</td>\n",
       "      <td>LAA</td>\n",
       "      <td>RP</td>\n",
       "      <td>535000</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>866</th>\n",
       "      <td>Wandy Peralta</td>\n",
       "      <td>CIN</td>\n",
       "      <td>RP</td>\n",
       "      <td>535000</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>867</th>\n",
       "      <td>Yandy Diaz</td>\n",
       "      <td>CLE</td>\n",
       "      <td>3B</td>\n",
       "      <td>535000</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               NAME TEAM POS  SALARY  START_YEAR  END_YEAR  YEARS\n",
       "863    Steve Selsky  BOS  RF  535000        2017      2017      1\n",
       "864   Stuart Turner  CIN   C  535000        2017      2017      1\n",
       "865  Vicente Campos  LAA  RP  535000        2017      2017      1\n",
       "866   Wandy Peralta  CIN  RP  535000        2017      2017      1\n",
       "867      Yandy Diaz  CLE  3B  535000        2017      2017      1"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>POS</th>\n",
       "      <th>SALARY</th>\n",
       "      <th>START_YEAR</th>\n",
       "      <th>END_YEAR</th>\n",
       "      <th>YEARS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>784</th>\n",
       "      <td>David Dahl</td>\n",
       "      <td>COL</td>\n",
       "      <td>CF</td>\n",
       "      <td>537000</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>734</th>\n",
       "      <td>Jett Bandy</td>\n",
       "      <td>MIL</td>\n",
       "      <td>C</td>\n",
       "      <td>539800</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>Wei-Yin Chen</td>\n",
       "      <td>MIA</td>\n",
       "      <td>SP</td>\n",
       "      <td>15500000</td>\n",
       "      <td>2016</td>\n",
       "      <td>2020</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>665</th>\n",
       "      <td>Kendall Graveman</td>\n",
       "      <td>OAK</td>\n",
       "      <td>SP</td>\n",
       "      <td>545000</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td>Aaron Hill</td>\n",
       "      <td>SF</td>\n",
       "      <td>2B</td>\n",
       "      <td>2000000</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 NAME TEAM POS    SALARY  START_YEAR  END_YEAR  YEARS\n",
       "784        David Dahl  COL  CF    537000        2017      2017      1\n",
       "734        Jett Bandy  MIL   C    539800        2017      2017      1\n",
       "63       Wei-Yin Chen  MIA  SP  15500000        2016      2020      5\n",
       "665  Kendall Graveman  OAK  SP    545000        2017      2017      1\n",
       "395        Aaron Hill   SF  2B   2000000        2017      2017      1"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 868 entries, 0 to 867\n",
      "Data columns (total 7 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   NAME        868 non-null    object\n",
      " 1   TEAM        868 non-null    object\n",
      " 2   POS         868 non-null    object\n",
      " 3   SALARY      868 non-null    int64 \n",
      " 4   START_YEAR  868 non-null    int64 \n",
      " 5   END_YEAR    868 non-null    int64 \n",
      " 6   YEARS       868 non-null    int64 \n",
      "dtypes: int64(4), object(3)\n",
      "memory usage: 47.6+ KB\n"
     ]
    }
   ],
   "source": [
    "mlb.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SALARY</th>\n",
       "      <th>START_YEAR</th>\n",
       "      <th>END_YEAR</th>\n",
       "      <th>YEARS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>8.680000e+02</td>\n",
       "      <td>868.000000</td>\n",
       "      <td>868.000000</td>\n",
       "      <td>868.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>4.468069e+06</td>\n",
       "      <td>2016.486175</td>\n",
       "      <td>2017.430876</td>\n",
       "      <td>1.944700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>5.948459e+06</td>\n",
       "      <td>1.205923</td>\n",
       "      <td>1.163087</td>\n",
       "      <td>1.916764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>5.350000e+05</td>\n",
       "      <td>2008.000000</td>\n",
       "      <td>2015.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>5.455000e+05</td>\n",
       "      <td>2017.000000</td>\n",
       "      <td>2017.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.562500e+06</td>\n",
       "      <td>2017.000000</td>\n",
       "      <td>2017.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>6.000000e+06</td>\n",
       "      <td>2017.000000</td>\n",
       "      <td>2017.000000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>3.300000e+07</td>\n",
       "      <td>2017.000000</td>\n",
       "      <td>2027.000000</td>\n",
       "      <td>13.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             SALARY   START_YEAR     END_YEAR       YEARS\n",
       "count  8.680000e+02   868.000000   868.000000  868.000000\n",
       "mean   4.468069e+06  2016.486175  2017.430876    1.944700\n",
       "std    5.948459e+06     1.205923     1.163087    1.916764\n",
       "min    5.350000e+05  2008.000000  2015.000000    1.000000\n",
       "25%    5.455000e+05  2017.000000  2017.000000    1.000000\n",
       "50%    1.562500e+06  2017.000000  2017.000000    1.000000\n",
       "75%    6.000000e+06  2017.000000  2017.000000    2.000000\n",
       "max    3.300000e+07  2017.000000  2027.000000   13.000000"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['NAME', 'TEAM', 'POS', 'SALARY', 'START_YEAR', 'END_YEAR', 'YEARS'], dtype='object')"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NAME          object\n",
       "TEAM          object\n",
       "POS           object\n",
       "SALARY         int64\n",
       "START_YEAR     int64\n",
       "END_YEAR       int64\n",
       "YEARS          int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(868, 7)"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To get the number of records in a dataframe, you can access the first item in the `shape` pair, or you can just use the Python function `len()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "868"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(mlb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting columns of data\n",
    "\n",
    "If you need to select just one column of data, you can use \"dot notation\" (`mlb.SALARY`) as long as your column name doesn't have spaces and it isn't the name of a dataframe method (e.g., `product`). Otherwise, you can use \"bracket notation\" (`mlb['SALARY']`).\n",
    "\n",
    "Selecting one column will return a [`Series`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.html).\n",
    "\n",
    "If you want to select multiple columns of data, use bracket notation and pass in a _list_ of columns that you want to select. In Python, a list is a collection of items enclosed in square brackets, separated by commas: `['SALARY', 'NAME']`.\n",
    "\n",
    "Selecting multiple columns will return a [`DataFrame`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    LAD\n",
       "1    ARI\n",
       "2    BOS\n",
       "3    DET\n",
       "4    DET\n",
       "Name: TEAM, dtype: object"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# select one column of data\n",
    "teams = mlb.TEAM\n",
    "\n",
    "# bracket notation would do the same thing -- note the quotes around the column name\n",
    "# teams = mlb['TEAM']\n",
    "\n",
    "teams.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(teams)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "# select multiple columns of data\n",
    "salaries_and_names = mlb[['SALARY', 'NAME']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SALARY</th>\n",
       "      <th>NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>33000000</td>\n",
       "      <td>Clayton Kershaw</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31876966</td>\n",
       "      <td>Zack Greinke</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30000000</td>\n",
       "      <td>David Price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28000000</td>\n",
       "      <td>Miguel Cabrera</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28000000</td>\n",
       "      <td>Justin Verlander</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     SALARY              NAME\n",
       "0  33000000   Clayton Kershaw\n",
       "1  31876966      Zack Greinke\n",
       "2  30000000       David Price\n",
       "3  28000000    Miguel Cabrera\n",
       "4  28000000  Justin Verlander"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salaries_and_names.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(salaries_and_names)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting unique values in a column\n",
    "\n",
    "As you evaluate your data, you'll often want to get a list of unique values in a column (for cleaning, filtering, grouping, etc.).\n",
    "\n",
    "To do this, you can use the Series method `unique()`. If you wanted to get a list of baseball positions, you could do:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['SP', '1B', 'RF', '2B', 'DH', 'CF', 'C', 'LF', '3B', 'SS', 'OF',\n",
       "       'RP', 'P'], dtype=object)"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.POS.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If useful, you could also sort the results alphabetically with the Python [`sorted()`](https://docs.python.org/3/library/functions.html#sorted) function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['1B', '2B', '3B', 'C', 'CF', 'DH', 'LF', 'OF', 'P', 'RF', 'RP', 'SP', 'SS']"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sorted(mlb.POS.unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes you just need the _number_ of unique values in a column. To do this, you can use the pandas method `nunique()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "13"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.POS.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(You can also run `nunique()` on an entire dataframe:)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NAME          867\n",
       "TEAM           30\n",
       "POS            13\n",
       "SALARY        419\n",
       "START_YEAR      8\n",
       "END_YEAR       10\n",
       "YEARS          11\n",
       "dtype: int64"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to count up the number of times a value appears in a column of data -- the equivalent of doing a pivot table in Excel and aggregating by count -- you can use the Series method [`value_counts()`](https://pandas.pydata.org/pandas-docs/version/0.22/generated/pandas.Series.value_counts.html).\n",
    "\n",
    "To get a list of MLB teams and the number of times each one appears in our salary data -- in other words, the roster count for each team -- we could do:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "TEX    34\n",
       "COL    32\n",
       "TB     32\n",
       "NYM    31\n",
       "CIN    31\n",
       "LAD    31\n",
       "BOS    31\n",
       "SEA    31\n",
       "SD     31\n",
       "STL    30\n",
       "LAA    30\n",
       "OAK    30\n",
       "ATL    30\n",
       "TOR    29\n",
       "MIN    29\n",
       "CWS    28\n",
       "MIA    28\n",
       "BAL    28\n",
       "ARI    28\n",
       "SF     28\n",
       "CLE    28\n",
       "KC     28\n",
       "HOU    27\n",
       "NYY    27\n",
       "DET    26\n",
       "PIT    26\n",
       "WSH    26\n",
       "MIL    26\n",
       "CHC    26\n",
       "PHI    26\n",
       "Name: TEAM, dtype: int64"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.TEAM.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Running basic summary stats\n",
    "\n",
    "Some of this already surfaced with `describe()`, but in some cases you'll want to compute these stats manually:\n",
    "- `sum()`\n",
    "- `mean()`\n",
    "- `median()`\n",
    "- `max()`\n",
    "- `min()`\n",
    "\n",
    "You can run these on a Series (e.g., a column of data), or on an entire DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3878284045"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.SALARY.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4468069.176267281"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.SALARY.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1562500.0"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.SALARY.median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "33000000"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.SALARY.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "535000"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.SALARY.min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SALARY        4.468069e+06\n",
       "START_YEAR    2.016486e+03\n",
       "END_YEAR      2.017431e+03\n",
       "YEARS         1.944700e+00\n",
       "dtype: float64"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# entire dataframe\n",
    "mlb.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sorting your data\n",
    "\n",
    "You can use the [`sort_values()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sort_values.html) method to sort a dataframe by one or more columns. The default is to sort the values ascending; if you want your results sorted descending, specify `ascending=False`.\n",
    "\n",
    "Let's sort our dataframe by `SALARY` descending:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>POS</th>\n",
       "      <th>SALARY</th>\n",
       "      <th>START_YEAR</th>\n",
       "      <th>END_YEAR</th>\n",
       "      <th>YEARS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Clayton Kershaw</td>\n",
       "      <td>LAD</td>\n",
       "      <td>SP</td>\n",
       "      <td>33000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2020</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Zack Greinke</td>\n",
       "      <td>ARI</td>\n",
       "      <td>SP</td>\n",
       "      <td>31876966</td>\n",
       "      <td>2016</td>\n",
       "      <td>2021</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>David Price</td>\n",
       "      <td>BOS</td>\n",
       "      <td>SP</td>\n",
       "      <td>30000000</td>\n",
       "      <td>2016</td>\n",
       "      <td>2022</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Miguel Cabrera</td>\n",
       "      <td>DET</td>\n",
       "      <td>1B</td>\n",
       "      <td>28000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2023</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Justin Verlander</td>\n",
       "      <td>DET</td>\n",
       "      <td>SP</td>\n",
       "      <td>28000000</td>\n",
       "      <td>2013</td>\n",
       "      <td>2019</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               NAME TEAM POS    SALARY  START_YEAR  END_YEAR  YEARS\n",
       "0   Clayton Kershaw  LAD  SP  33000000        2014      2020      7\n",
       "1      Zack Greinke  ARI  SP  31876966        2016      2021      6\n",
       "2       David Price  BOS  SP  30000000        2016      2022      7\n",
       "3    Miguel Cabrera  DET  1B  28000000        2014      2023     10\n",
       "4  Justin Verlander  DET  SP  28000000        2013      2019      7"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.sort_values('SALARY', ascending=False).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To sort by multiple columns, pass a list of columns to the `sort_values()` method -- the sorting will happen in the order you specify in the list. You'll also need to pass a list to the `ascending` keyword argument, otherwise both will sort ascending.\n",
    "\n",
    "Let's sort our dataframe first by `TEAM` ascending, then by `SALARY` descending:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>POS</th>\n",
       "      <th>SALARY</th>\n",
       "      <th>START_YEAR</th>\n",
       "      <th>END_YEAR</th>\n",
       "      <th>YEARS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Zack Greinke</td>\n",
       "      <td>ARI</td>\n",
       "      <td>SP</td>\n",
       "      <td>31876966</td>\n",
       "      <td>2016</td>\n",
       "      <td>2021</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>Yasmany Tomas</td>\n",
       "      <td>ARI</td>\n",
       "      <td>OF</td>\n",
       "      <td>9500000</td>\n",
       "      <td>2015</td>\n",
       "      <td>2020</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>Paul Goldschmidt</td>\n",
       "      <td>ARI</td>\n",
       "      <td>1B</td>\n",
       "      <td>8833333</td>\n",
       "      <td>2014</td>\n",
       "      <td>2018</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>A.J. Pollock</td>\n",
       "      <td>ARI</td>\n",
       "      <td>CF</td>\n",
       "      <td>6750000</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>262</th>\n",
       "      <td>Shelby Miller</td>\n",
       "      <td>ARI</td>\n",
       "      <td>SP</td>\n",
       "      <td>4700000</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 NAME TEAM POS    SALARY  START_YEAR  END_YEAR  YEARS\n",
       "1        Zack Greinke  ARI  SP  31876966        2016      2021      6\n",
       "137     Yasmany Tomas  ARI  OF   9500000        2015      2020      6\n",
       "149  Paul Goldschmidt  ARI  1B   8833333        2014      2018      5\n",
       "190      A.J. Pollock  ARI  CF   6750000        2016      2017      2\n",
       "262     Shelby Miller  ARI  SP   4700000        2017      2017      1"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.sort_values(['TEAM', 'SALARY'], ascending=[True, False]).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering rows of data\n",
    "\n",
    "To filter your data by some criteria, you'd pass your filtering condition(s) to a dataframe using bracket notation.\n",
    "\n",
    "You can use Python's [comparison operators](https://docs.python.org/3/reference/expressions.html#comparisons) in your filters, which include:\n",
    "- `>` greater than\n",
    "- `<` less than\n",
    "- `>=` greater than or equal to\n",
    "- `<=` less than or equal to\n",
    "- `==` equal to\n",
    "- `!=` not equal to\n",
    "\n",
    "Example: You want to filter your data to keep records where the `TEAM` value is 'ARI':"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "diamondbacks = mlb[mlb.TEAM == 'ARI']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>POS</th>\n",
       "      <th>SALARY</th>\n",
       "      <th>START_YEAR</th>\n",
       "      <th>END_YEAR</th>\n",
       "      <th>YEARS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Zack Greinke</td>\n",
       "      <td>ARI</td>\n",
       "      <td>SP</td>\n",
       "      <td>31876966</td>\n",
       "      <td>2016</td>\n",
       "      <td>2021</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>Yasmany Tomas</td>\n",
       "      <td>ARI</td>\n",
       "      <td>OF</td>\n",
       "      <td>9500000</td>\n",
       "      <td>2015</td>\n",
       "      <td>2020</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>Paul Goldschmidt</td>\n",
       "      <td>ARI</td>\n",
       "      <td>1B</td>\n",
       "      <td>8833333</td>\n",
       "      <td>2014</td>\n",
       "      <td>2018</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>A.J. Pollock</td>\n",
       "      <td>ARI</td>\n",
       "      <td>CF</td>\n",
       "      <td>6750000</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>262</th>\n",
       "      <td>Shelby Miller</td>\n",
       "      <td>ARI</td>\n",
       "      <td>SP</td>\n",
       "      <td>4700000</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 NAME TEAM POS    SALARY  START_YEAR  END_YEAR  YEARS\n",
       "1        Zack Greinke  ARI  SP  31876966        2016      2021      6\n",
       "137     Yasmany Tomas  ARI  OF   9500000        2015      2020      6\n",
       "149  Paul Goldschmidt  ARI  1B   8833333        2014      2018      5\n",
       "190      A.J. Pollock  ARI  CF   6750000        2016      2017      2\n",
       "262     Shelby Miller  ARI  SP   4700000        2017      2017      1"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diamondbacks.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could filter to get all records where the `TEAM` value is _not_ 'ARI':"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "non_diamondbacks = mlb[mlb.TEAM != 'ARI']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>POS</th>\n",
       "      <th>SALARY</th>\n",
       "      <th>START_YEAR</th>\n",
       "      <th>END_YEAR</th>\n",
       "      <th>YEARS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Clayton Kershaw</td>\n",
       "      <td>LAD</td>\n",
       "      <td>SP</td>\n",
       "      <td>33000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2020</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>David Price</td>\n",
       "      <td>BOS</td>\n",
       "      <td>SP</td>\n",
       "      <td>30000000</td>\n",
       "      <td>2016</td>\n",
       "      <td>2022</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Miguel Cabrera</td>\n",
       "      <td>DET</td>\n",
       "      <td>1B</td>\n",
       "      <td>28000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2023</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Justin Verlander</td>\n",
       "      <td>DET</td>\n",
       "      <td>SP</td>\n",
       "      <td>28000000</td>\n",
       "      <td>2013</td>\n",
       "      <td>2019</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Jason Heyward</td>\n",
       "      <td>CHC</td>\n",
       "      <td>RF</td>\n",
       "      <td>26055288</td>\n",
       "      <td>2016</td>\n",
       "      <td>2023</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               NAME TEAM POS    SALARY  START_YEAR  END_YEAR  YEARS\n",
       "0   Clayton Kershaw  LAD  SP  33000000        2014      2020      7\n",
       "2       David Price  BOS  SP  30000000        2016      2022      7\n",
       "3    Miguel Cabrera  DET  1B  28000000        2014      2023     10\n",
       "4  Justin Verlander  DET  SP  28000000        2013      2019      7\n",
       "5     Jason Heyward  CHC  RF  26055288        2016      2023      8"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "non_diamondbacks.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could filter our data to just grab the players that make at least $1 million:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "million_a_year  = mlb[mlb.SALARY >= 1000000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>POS</th>\n",
       "      <th>SALARY</th>\n",
       "      <th>START_YEAR</th>\n",
       "      <th>END_YEAR</th>\n",
       "      <th>YEARS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Clayton Kershaw</td>\n",
       "      <td>LAD</td>\n",
       "      <td>SP</td>\n",
       "      <td>33000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2020</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Zack Greinke</td>\n",
       "      <td>ARI</td>\n",
       "      <td>SP</td>\n",
       "      <td>31876966</td>\n",
       "      <td>2016</td>\n",
       "      <td>2021</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>David Price</td>\n",
       "      <td>BOS</td>\n",
       "      <td>SP</td>\n",
       "      <td>30000000</td>\n",
       "      <td>2016</td>\n",
       "      <td>2022</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Miguel Cabrera</td>\n",
       "      <td>DET</td>\n",
       "      <td>1B</td>\n",
       "      <td>28000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2023</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Justin Verlander</td>\n",
       "      <td>DET</td>\n",
       "      <td>SP</td>\n",
       "      <td>28000000</td>\n",
       "      <td>2013</td>\n",
       "      <td>2019</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               NAME TEAM POS    SALARY  START_YEAR  END_YEAR  YEARS\n",
       "0   Clayton Kershaw  LAD  SP  33000000        2014      2020      7\n",
       "1      Zack Greinke  ARI  SP  31876966        2016      2021      6\n",
       "2       David Price  BOS  SP  30000000        2016      2022      7\n",
       "3    Miguel Cabrera  DET  1B  28000000        2014      2023     10\n",
       "4  Justin Verlander  DET  SP  28000000        2013      2019      7"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "million_a_year.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering against multiple values\n",
    "\n",
    "You can use the `isin()` method to test a value against multiple matches -- just hand it a _list_ of values to check against.\n",
    "\n",
    "Example: Let's say we wanted to filter to get just players in Texas (in other words, just the Texas Rangers and the Houston Astros):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "tx = mlb[mlb.TEAM.isin(['TEX', 'HOU'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>POS</th>\n",
       "      <th>SALARY</th>\n",
       "      <th>START_YEAR</th>\n",
       "      <th>END_YEAR</th>\n",
       "      <th>YEARS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Prince Fielder</td>\n",
       "      <td>TEX</td>\n",
       "      <td>DH</td>\n",
       "      <td>24000000</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Cole Hamels</td>\n",
       "      <td>TEX</td>\n",
       "      <td>SP</td>\n",
       "      <td>22500000</td>\n",
       "      <td>2013</td>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Shin-Soo Choo</td>\n",
       "      <td>TEX</td>\n",
       "      <td>RF</td>\n",
       "      <td>20000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2020</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Adrian Beltre</td>\n",
       "      <td>TEX</td>\n",
       "      <td>3B</td>\n",
       "      <td>18000000</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Brian McCann</td>\n",
       "      <td>HOU</td>\n",
       "      <td>C</td>\n",
       "      <td>17000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2018</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              NAME TEAM POS    SALARY  START_YEAR  END_YEAR  YEARS\n",
       "11  Prince Fielder  TEX  DH  24000000        2017      2017      1\n",
       "15     Cole Hamels  TEX  SP  22500000        2013      2018      6\n",
       "35   Shin-Soo Choo  TEX  RF  20000000        2014      2020      7\n",
       "45   Adrian Beltre  TEX  3B  18000000        2017      2018      2\n",
       "52    Brian McCann  HOU   C  17000000        2014      2018      5"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tx.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exclusion filtering\n",
    "\n",
    "Sometimes it's easier to specify what records you _don't_ want returned. To flip the meaning of a filter condition, prepend a tilde `~`.\n",
    "\n",
    "For instance, if we wanted to get all players who are _not_ from Texas, we'd use the same filter condition we just used to get the TX players but add a tilde at the beginning:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "not_tx = mlb[~mlb.TEAM.isin(['TEX', 'HOU'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>POS</th>\n",
       "      <th>SALARY</th>\n",
       "      <th>START_YEAR</th>\n",
       "      <th>END_YEAR</th>\n",
       "      <th>YEARS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Clayton Kershaw</td>\n",
       "      <td>LAD</td>\n",
       "      <td>SP</td>\n",
       "      <td>33000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2020</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Zack Greinke</td>\n",
       "      <td>ARI</td>\n",
       "      <td>SP</td>\n",
       "      <td>31876966</td>\n",
       "      <td>2016</td>\n",
       "      <td>2021</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>David Price</td>\n",
       "      <td>BOS</td>\n",
       "      <td>SP</td>\n",
       "      <td>30000000</td>\n",
       "      <td>2016</td>\n",
       "      <td>2022</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Miguel Cabrera</td>\n",
       "      <td>DET</td>\n",
       "      <td>1B</td>\n",
       "      <td>28000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2023</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Justin Verlander</td>\n",
       "      <td>DET</td>\n",
       "      <td>SP</td>\n",
       "      <td>28000000</td>\n",
       "      <td>2013</td>\n",
       "      <td>2019</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               NAME TEAM POS    SALARY  START_YEAR  END_YEAR  YEARS\n",
       "0   Clayton Kershaw  LAD  SP  33000000        2014      2020      7\n",
       "1      Zack Greinke  ARI  SP  31876966        2016      2021      6\n",
       "2       David Price  BOS  SP  30000000        2016      2022      7\n",
       "3    Miguel Cabrera  DET  1B  28000000        2014      2023     10\n",
       "4  Justin Verlander  DET  SP  28000000        2013      2019      7"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "not_tx.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering text columns with string methods\n",
    "\n",
    "You can access the text values in a column with `.str`, and you can use any of Python's native string functions to manipulate them.\n",
    "\n",
    "For our purposes, though, the pandas [`str.contains()`](https://pandas.pydata.org/pandas-docs/version/0.22/generated/pandas.Series.str.contains.html) method is useful for filtering data by matching text patterns.\n",
    "\n",
    "If we wanted to get every player with 'John' in their name, we could do something like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "johns = mlb[mlb.NAME.str.contains('John', case=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>POS</th>\n",
       "      <th>SALARY</th>\n",
       "      <th>START_YEAR</th>\n",
       "      <th>END_YEAR</th>\n",
       "      <th>YEARS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Johnny Cueto</td>\n",
       "      <td>SF</td>\n",
       "      <td>SP</td>\n",
       "      <td>23500000</td>\n",
       "      <td>2016</td>\n",
       "      <td>2021</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>John Lackey</td>\n",
       "      <td>CHC</td>\n",
       "      <td>SP</td>\n",
       "      <td>16000000</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>237</th>\n",
       "      <td>John Axford</td>\n",
       "      <td>OAK</td>\n",
       "      <td>RP</td>\n",
       "      <td>5500000</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>Jim Johnson</td>\n",
       "      <td>ATL</td>\n",
       "      <td>RP</td>\n",
       "      <td>5000000</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>295</th>\n",
       "      <td>John Jaso</td>\n",
       "      <td>PIT</td>\n",
       "      <td>1B</td>\n",
       "      <td>4000000</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             NAME TEAM POS    SALARY  START_YEAR  END_YEAR  YEARS\n",
       "12   Johnny Cueto   SF  SP  23500000        2016      2021      6\n",
       "60    John Lackey  CHC  SP  16000000        2016      2017      2\n",
       "237   John Axford  OAK  RP   5500000        2016      2017      2\n",
       "255   Jim Johnson  ATL  RP   5000000        2017      2018      2\n",
       "295     John Jaso  PIT  1B   4000000        2016      2017      2"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "johns.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note the `case=False` keyword argument -- we're telling pandas to match case-insensitive. And if the pattern you're trying to match is more complex, the method is set up to support [regular expressions](https://docs.python.org/3/howto/regex.html) by default."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Multiple filters\n",
    "\n",
    "Sometimes you have multiple filters to apply to your data. Lots of the time, it makes sense to break the filters out into separate statements.\n",
    "\n",
    "For instance, if you wanted to get all Texas players who make at least $1 million, I might do this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "tx = mlb[mlb.TEAM.isin(['TEX', 'HOU'])]\n",
    "\n",
    "# note that I'm filtering the dataframe I just created,  not the original `mlb` dataframe\n",
    "tx_million_a_year = tx[tx.SALARY >= 1000000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>POS</th>\n",
       "      <th>SALARY</th>\n",
       "      <th>START_YEAR</th>\n",
       "      <th>END_YEAR</th>\n",
       "      <th>YEARS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Prince Fielder</td>\n",
       "      <td>TEX</td>\n",
       "      <td>DH</td>\n",
       "      <td>24000000</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Cole Hamels</td>\n",
       "      <td>TEX</td>\n",
       "      <td>SP</td>\n",
       "      <td>22500000</td>\n",
       "      <td>2013</td>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Shin-Soo Choo</td>\n",
       "      <td>TEX</td>\n",
       "      <td>RF</td>\n",
       "      <td>20000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2020</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Adrian Beltre</td>\n",
       "      <td>TEX</td>\n",
       "      <td>3B</td>\n",
       "      <td>18000000</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Brian McCann</td>\n",
       "      <td>HOU</td>\n",
       "      <td>C</td>\n",
       "      <td>17000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2018</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              NAME TEAM POS    SALARY  START_YEAR  END_YEAR  YEARS\n",
       "11  Prince Fielder  TEX  DH  24000000        2017      2017      1\n",
       "15     Cole Hamels  TEX  SP  22500000        2013      2018      6\n",
       "35   Shin-Soo Choo  TEX  RF  20000000        2014      2020      7\n",
       "45   Adrian Beltre  TEX  3B  18000000        2017      2018      2\n",
       "52    Brian McCann  HOU   C  17000000        2014      2018      5"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tx_million_a_year.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But sometimes you want to chain your filters together into one statement. Use `|` for \"or\" and `&` for \"and\" rather than Python's built-in `or` and `and` statements, and use grouping parentheses around each statement.\n",
    "\n",
    "The same filter in one statement:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "tx_million_a_year = mlb[(mlb.TEAM.isin(['TEX', 'HOU'])) & (mlb.SALARY > 1000000)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>POS</th>\n",
       "      <th>SALARY</th>\n",
       "      <th>START_YEAR</th>\n",
       "      <th>END_YEAR</th>\n",
       "      <th>YEARS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Prince Fielder</td>\n",
       "      <td>TEX</td>\n",
       "      <td>DH</td>\n",
       "      <td>24000000</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Cole Hamels</td>\n",
       "      <td>TEX</td>\n",
       "      <td>SP</td>\n",
       "      <td>22500000</td>\n",
       "      <td>2013</td>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Shin-Soo Choo</td>\n",
       "      <td>TEX</td>\n",
       "      <td>RF</td>\n",
       "      <td>20000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2020</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Adrian Beltre</td>\n",
       "      <td>TEX</td>\n",
       "      <td>3B</td>\n",
       "      <td>18000000</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Brian McCann</td>\n",
       "      <td>HOU</td>\n",
       "      <td>C</td>\n",
       "      <td>17000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2018</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              NAME TEAM POS    SALARY  START_YEAR  END_YEAR  YEARS\n",
       "11  Prince Fielder  TEX  DH  24000000        2017      2017      1\n",
       "15     Cole Hamels  TEX  SP  22500000        2013      2018      6\n",
       "35   Shin-Soo Choo  TEX  RF  20000000        2014      2020      7\n",
       "45   Adrian Beltre  TEX  3B  18000000        2017      2018      2\n",
       "52    Brian McCann  HOU   C  17000000        2014      2018      5"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tx_million_a_year.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Do what works for you and makes sense in context, but I find the first version a little easier to read."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding a calculated column\n",
    "\n",
    "To add a new column to a dataframe, use bracket notation to supply the name of the new column (in quotes, or apostrophes, as long as they match), then set it equal to a value -- maybe a calculation derived from other data in your dataframe.\n",
    "\n",
    "For example, let's create a new column, `contract_total`, that multiplies the annual salary by the number of contract years:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "mlb['contract_total'] = mlb['SALARY'] * mlb['YEARS']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>POS</th>\n",
       "      <th>SALARY</th>\n",
       "      <th>START_YEAR</th>\n",
       "      <th>END_YEAR</th>\n",
       "      <th>YEARS</th>\n",
       "      <th>contract_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Clayton Kershaw</td>\n",
       "      <td>LAD</td>\n",
       "      <td>SP</td>\n",
       "      <td>33000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2020</td>\n",
       "      <td>7</td>\n",
       "      <td>231000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Zack Greinke</td>\n",
       "      <td>ARI</td>\n",
       "      <td>SP</td>\n",
       "      <td>31876966</td>\n",
       "      <td>2016</td>\n",
       "      <td>2021</td>\n",
       "      <td>6</td>\n",
       "      <td>191261796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>David Price</td>\n",
       "      <td>BOS</td>\n",
       "      <td>SP</td>\n",
       "      <td>30000000</td>\n",
       "      <td>2016</td>\n",
       "      <td>2022</td>\n",
       "      <td>7</td>\n",
       "      <td>210000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Miguel Cabrera</td>\n",
       "      <td>DET</td>\n",
       "      <td>1B</td>\n",
       "      <td>28000000</td>\n",
       "      <td>2014</td>\n",
       "      <td>2023</td>\n",
       "      <td>10</td>\n",
       "      <td>280000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Justin Verlander</td>\n",
       "      <td>DET</td>\n",
       "      <td>SP</td>\n",
       "      <td>28000000</td>\n",
       "      <td>2013</td>\n",
       "      <td>2019</td>\n",
       "      <td>7</td>\n",
       "      <td>196000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               NAME TEAM POS    SALARY  START_YEAR  END_YEAR  YEARS  \\\n",
       "0   Clayton Kershaw  LAD  SP  33000000        2014      2020      7   \n",
       "1      Zack Greinke  ARI  SP  31876966        2016      2021      6   \n",
       "2       David Price  BOS  SP  30000000        2016      2022      7   \n",
       "3    Miguel Cabrera  DET  1B  28000000        2014      2023     10   \n",
       "4  Justin Verlander  DET  SP  28000000        2013      2019      7   \n",
       "\n",
       "   contract_total  \n",
       "0       231000000  \n",
       "1       191261796  \n",
       "2       210000000  \n",
       "3       280000000  \n",
       "4       196000000  "
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering for nulls\n",
    "\n",
    "You can use the `isnull()` method to get records that are null, or `notnull()` to get records that aren't. The most common use I've seen for these methods is during filtering to see how many records you're missing (and, therefore, how that affects your analysis).\n",
    "\n",
    "The MLB data is complete, so to demonstrate this, let's load up a new data set: A cut of the [National Inventory of Dams](https://ire.org/nicar/database-library/databases/national-inventory-of-dams/) database, courtesy of the NICAR data library. (We'll need to specify the `encoding` on this CSV because it's not UTF-8.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "dams = pd.read_csv('dams.csv',\n",
    "                   encoding='latin-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NIDID</th>\n",
       "      <th>Dam_Name</th>\n",
       "      <th>Insp_Date</th>\n",
       "      <th>Submit_Date</th>\n",
       "      <th>River</th>\n",
       "      <th>City_02</th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>Cong_Dist</th>\n",
       "      <th>Cong_Rep</th>\n",
       "      <th>...</th>\n",
       "      <th>Fed_Fund</th>\n",
       "      <th>Fed_Design</th>\n",
       "      <th>Fed_Con</th>\n",
       "      <th>Fed_Reg</th>\n",
       "      <th>Fed_Insp</th>\n",
       "      <th>Srce_Agncy</th>\n",
       "      <th>Oth_StrucID</th>\n",
       "      <th>Num_Struc</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>VA16104</td>\n",
       "      <td>CLIFFORD D. CRAIG MEMORIAL DAM</td>\n",
       "      <td>2007-09-06</td>\n",
       "      <td>2013-03-12</td>\n",
       "      <td>TRIB. TO ROANOKE RIVER</td>\n",
       "      <td>SALEM</td>\n",
       "      <td>ROANOKE CO</td>\n",
       "      <td>VA</td>\n",
       "      <td>VA09</td>\n",
       "      <td>H. MORGAN GRIFFITH (R)</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>-80.1750</td>\n",
       "      <td>37.2250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>VA07915</td>\n",
       "      <td>GREENE MOUNTAIN LAKE DAM</td>\n",
       "      <td>2008-07-14</td>\n",
       "      <td>2013-03-12</td>\n",
       "      <td>BLUE RUN</td>\n",
       "      <td>ADVANCE MILLS</td>\n",
       "      <td>GREENE</td>\n",
       "      <td>VA</td>\n",
       "      <td>VA05</td>\n",
       "      <td>ROBERT HURT (R)</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>-78.4366</td>\n",
       "      <td>38.2700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>VA06906</td>\n",
       "      <td>LEHMANS DAM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-03-12</td>\n",
       "      <td>GOUGH RUN</td>\n",
       "      <td>MARLBORO</td>\n",
       "      <td>FREDERICK</td>\n",
       "      <td>VA</td>\n",
       "      <td>VA10</td>\n",
       "      <td>FRANK R. WOLF (R)</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>-78.3083</td>\n",
       "      <td>39.1516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>VA13905</td>\n",
       "      <td>LURAY</td>\n",
       "      <td>2010-12-22</td>\n",
       "      <td>2013-02-28</td>\n",
       "      <td>SOUTH FORK SHENANDOAH RIVER</td>\n",
       "      <td>RILEYVILLE</td>\n",
       "      <td>PAGE</td>\n",
       "      <td>VA</td>\n",
       "      <td>VA06</td>\n",
       "      <td>BOB GOODLATTE (R)</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FERC</td>\n",
       "      <td>FERC</td>\n",
       "      <td>FERC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>-78.4999</td>\n",
       "      <td>38.6774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>VA06106</td>\n",
       "      <td>MATHEWS DAM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-03-12</td>\n",
       "      <td>TR-GAP RUN</td>\n",
       "      <td>RECTORTOWN</td>\n",
       "      <td>FAUQUIER</td>\n",
       "      <td>VA</td>\n",
       "      <td>VA05</td>\n",
       "      <td>ROBERT HURT (R)</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>-77.9600</td>\n",
       "      <td>38.9800</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     NIDID                        Dam_Name   Insp_Date Submit_Date  \\\n",
       "0  VA16104  CLIFFORD D. CRAIG MEMORIAL DAM  2007-09-06  2013-03-12   \n",
       "1  VA07915        GREENE MOUNTAIN LAKE DAM  2008-07-14  2013-03-12   \n",
       "2  VA06906                     LEHMANS DAM         NaN  2013-03-12   \n",
       "3  VA13905                           LURAY  2010-12-22  2013-02-28   \n",
       "4  VA06106                     MATHEWS DAM         NaN  2013-03-12   \n",
       "\n",
       "                         River        City_02      County State Cong_Dist  \\\n",
       "0       TRIB. TO ROANOKE RIVER          SALEM  ROANOKE CO    VA      VA09   \n",
       "1                     BLUE RUN  ADVANCE MILLS      GREENE    VA      VA05   \n",
       "2                    GOUGH RUN       MARLBORO   FREDERICK    VA      VA10   \n",
       "3  SOUTH FORK SHENANDOAH RIVER     RILEYVILLE        PAGE    VA      VA06   \n",
       "4                   TR-GAP RUN     RECTORTOWN    FAUQUIER    VA      VA05   \n",
       "\n",
       "                 Cong_Rep  ... Fed_Fund Fed_Design Fed_Con  Fed_Reg Fed_Insp  \\\n",
       "0  H. MORGAN GRIFFITH (R)  ...      NaN        NaN     NaN      NaN      NaN   \n",
       "1         ROBERT HURT (R)  ...      NaN        NaN     NaN      NaN      NaN   \n",
       "2       FRANK R. WOLF (R)  ...      NaN        NaN     NaN      NaN      NaN   \n",
       "3       BOB GOODLATTE (R)  ...      NaN        NaN     NaN     FERC     FERC   \n",
       "4         ROBERT HURT (R)  ...      NaN        NaN     NaN      NaN      NaN   \n",
       "\n",
       "  Srce_Agncy Oth_StrucID Num_Struc Longitude Latitude  \n",
       "0         VA         NaN         0  -80.1750  37.2250  \n",
       "1         VA         NaN         0  -78.4366  38.2700  \n",
       "2         VA         NaN         0  -78.3083  39.1516  \n",
       "3       FERC         NaN         1  -78.4999  38.6774  \n",
       "4         VA         NaN         0  -77.9600  38.9800  \n",
       "\n",
       "[5 rows x 42 columns]"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dams.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Maybe we're interested in looking at the year the dam was completed (the `Year_Comp`) column. Running `.info()` on the dataframe shows that we're missing some values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2482 entries, 0 to 2481\n",
      "Data columns (total 42 columns):\n",
      " #   Column         Non-Null Count  Dtype  \n",
      "---  ------         --------------  -----  \n",
      " 0   NIDID          2482 non-null   object \n",
      " 1   Dam_Name       2480 non-null   object \n",
      " 2   Insp_Date      1093 non-null   object \n",
      " 3   Submit_Date    2482 non-null   object \n",
      " 4   River          2264 non-null   object \n",
      " 5   City_02        1407 non-null   object \n",
      " 6   County         2477 non-null   object \n",
      " 7   State          2482 non-null   object \n",
      " 8   Cong_Dist      2445 non-null   object \n",
      " 9   Cong_Rep       2445 non-null   object \n",
      " 10  Party          2445 non-null   object \n",
      " 11  Owner_Type     2482 non-null   object \n",
      " 12  Owner_Name     2199 non-null   object \n",
      " 13  Year_Comp      1663 non-null   float64\n",
      " 14  Year_Mod       438 non-null    object \n",
      " 15  Private_Dam    2482 non-null   object \n",
      " 16  NPDP_Hazard    1487 non-null   object \n",
      " 17  Permit_Auth    2482 non-null   object \n",
      " 18  Insp_Auth      2482 non-null   object \n",
      " 19  Enfrc_Auth     2482 non-null   object \n",
      " 20  Juris_Dam      2482 non-null   object \n",
      " 21  NID_Height     2468 non-null   float64\n",
      " 22  NID_Storage    2453 non-null   float64\n",
      " 23  Dam_Length     1813 non-null   float64\n",
      " 24  Max_Discharge  831 non-null    float64\n",
      " 25  Drain_Area     1188 non-null   float64\n",
      " 26  Dam_Designer   831 non-null    object \n",
      " 27  EAP            2482 non-null   object \n",
      " 28  Insp_Freq      2482 non-null   int64  \n",
      " 29  St_Reg_Dam     2482 non-null   object \n",
      " 30  St_Reg_Agncy   2374 non-null   object \n",
      " 31  Volume         530 non-null    float64\n",
      " 32  Fed_Fund       219 non-null    object \n",
      " 33  Fed_Design     578 non-null    object \n",
      " 34  Fed_Con        221 non-null    object \n",
      " 35  Fed_Reg        132 non-null    object \n",
      " 36  Fed_Insp       146 non-null    object \n",
      " 37  Srce_Agncy     2482 non-null   object \n",
      " 38  Oth_StrucID    17 non-null     object \n",
      " 39  Num_Struc      2482 non-null   int64  \n",
      " 40  Longitude      2482 non-null   float64\n",
      " 41  Latitude       2482 non-null   float64\n",
      "dtypes: float64(9), int64(2), object(31)\n",
      "memory usage: 814.5+ KB\n"
     ]
    }
   ],
   "source": [
    "dams.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can filter for `isnull()` to take a closer look:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "no_year_comp = dams[dams.Year_Comp.isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NIDID</th>\n",
       "      <th>Dam_Name</th>\n",
       "      <th>Insp_Date</th>\n",
       "      <th>Submit_Date</th>\n",
       "      <th>River</th>\n",
       "      <th>City_02</th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>Cong_Dist</th>\n",
       "      <th>Cong_Rep</th>\n",
       "      <th>...</th>\n",
       "      <th>Fed_Fund</th>\n",
       "      <th>Fed_Design</th>\n",
       "      <th>Fed_Con</th>\n",
       "      <th>Fed_Reg</th>\n",
       "      <th>Fed_Insp</th>\n",
       "      <th>Srce_Agncy</th>\n",
       "      <th>Oth_StrucID</th>\n",
       "      <th>Num_Struc</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>DE00095</td>\n",
       "      <td>WAPLES POND DAM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-02-04</td>\n",
       "      <td>PRIMEHOOK CREEK</td>\n",
       "      <td>BROADKILL BEACH  E</td>\n",
       "      <td>SUSSEX</td>\n",
       "      <td>DE</td>\n",
       "      <td>DE00</td>\n",
       "      <td>JOHN C. CARNEY JR. (D)</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>-75.3087</td>\n",
       "      <td>38.8240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>VA17710</td>\n",
       "      <td>LEE LAKE DAM</td>\n",
       "      <td>2003-09-08</td>\n",
       "      <td>2013-03-12</td>\n",
       "      <td>WILDERNESS RUN</td>\n",
       "      <td>MINE RUN</td>\n",
       "      <td>SPOTSYLVANIA</td>\n",
       "      <td>VA</td>\n",
       "      <td>VA07</td>\n",
       "      <td>ERIC CANTOR (R)</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>-77.7400</td>\n",
       "      <td>38.3033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>VA19104</td>\n",
       "      <td>HIDDEN VALLEY LAKE DAM</td>\n",
       "      <td>2004-12-31</td>\n",
       "      <td>2013-03-12</td>\n",
       "      <td>BRUMLEY CREEK</td>\n",
       "      <td>DUNCANVILLE</td>\n",
       "      <td>WASHINGTON</td>\n",
       "      <td>VA</td>\n",
       "      <td>VA09</td>\n",
       "      <td>H. MORGAN GRIFFITH (R)</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>-82.0733</td>\n",
       "      <td>36.8500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>MD00018</td>\n",
       "      <td>EMMITSBURG DAM</td>\n",
       "      <td>2012-08-30</td>\n",
       "      <td>2013-02-04</td>\n",
       "      <td>TURKEY CREEK</td>\n",
       "      <td>EMMITSBURG</td>\n",
       "      <td>FREDERICK</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD08</td>\n",
       "      <td>CHRIS VAN HOLLEN (D)</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>-77.3885</td>\n",
       "      <td>39.6959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263</th>\n",
       "      <td>DE00070</td>\n",
       "      <td>BLAIRS POND DAM</td>\n",
       "      <td>2011-07-26</td>\n",
       "      <td>2013-02-04</td>\n",
       "      <td>BEAVERDAM BRANCH</td>\n",
       "      <td>HOUSTON</td>\n",
       "      <td>KENT</td>\n",
       "      <td>DE</td>\n",
       "      <td>DE00</td>\n",
       "      <td>JOHN C. CARNEY JR. (D)</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>-75.4848</td>\n",
       "      <td>38.9039</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       NIDID                Dam_Name   Insp_Date Submit_Date  \\\n",
       "43   DE00095         WAPLES POND DAM         NaN  2013-02-04   \n",
       "114  VA17710            LEE LAKE DAM  2003-09-08  2013-03-12   \n",
       "152  VA19104  HIDDEN VALLEY LAKE DAM  2004-12-31  2013-03-12   \n",
       "212  MD00018          EMMITSBURG DAM  2012-08-30  2013-02-04   \n",
       "263  DE00070         BLAIRS POND DAM  2011-07-26  2013-02-04   \n",
       "\n",
       "                River             City_02        County State Cong_Dist  \\\n",
       "43    PRIMEHOOK CREEK  BROADKILL BEACH  E        SUSSEX    DE      DE00   \n",
       "114    WILDERNESS RUN            MINE RUN  SPOTSYLVANIA    VA      VA07   \n",
       "152     BRUMLEY CREEK         DUNCANVILLE    WASHINGTON    VA      VA09   \n",
       "212      TURKEY CREEK          EMMITSBURG     FREDERICK    MD      MD08   \n",
       "263  BEAVERDAM BRANCH             HOUSTON          KENT    DE      DE00   \n",
       "\n",
       "                   Cong_Rep  ... Fed_Fund Fed_Design Fed_Con  Fed_Reg  \\\n",
       "43   JOHN C. CARNEY JR. (D)  ...      NaN        NaN     NaN      NaN   \n",
       "114         ERIC CANTOR (R)  ...      NaN        NaN     NaN      NaN   \n",
       "152  H. MORGAN GRIFFITH (R)  ...      NaN        NaN     NaN      NaN   \n",
       "212    CHRIS VAN HOLLEN (D)  ...      NaN        NaN     NaN      NaN   \n",
       "263  JOHN C. CARNEY JR. (D)  ...      NaN        NaN     NaN      NaN   \n",
       "\n",
       "    Fed_Insp Srce_Agncy Oth_StrucID Num_Struc Longitude Latitude  \n",
       "43       NaN         DE         NaN         0  -75.3087  38.8240  \n",
       "114      NaN         VA         NaN         0  -77.7400  38.3033  \n",
       "152      NaN         VA         NaN         0  -82.0733  36.8500  \n",
       "212      NaN         MD         NaN         0  -77.3885  39.6959  \n",
       "263      NaN         DE         NaN         0  -75.4848  38.9039  \n",
       "\n",
       "[5 rows x 42 columns]"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "no_year_comp.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many are we missing? That will help us determine whether the analysis would be valid:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32.99758259468171"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calculate the percentage of records with no Year_Comp value\n",
    "# (part / whole) * 100\n",
    "\n",
    "(len(no_year_comp) / len(dams)) * 100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So this piece of our analysis would exclude one-third of our records -- something you'd need to explain to your audience, if indeed your reporting showed that the results of your analysis would still be meaningful.\n",
    "\n",
    "To get records where the `Year_Comp` is not null, we'd use `notnull()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [],
   "source": [
    "has_year_comp = dams[dams.Year_Comp.notnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NIDID</th>\n",
       "      <th>Dam_Name</th>\n",
       "      <th>Insp_Date</th>\n",
       "      <th>Submit_Date</th>\n",
       "      <th>River</th>\n",
       "      <th>City_02</th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>Cong_Dist</th>\n",
       "      <th>Cong_Rep</th>\n",
       "      <th>...</th>\n",
       "      <th>Fed_Fund</th>\n",
       "      <th>Fed_Design</th>\n",
       "      <th>Fed_Con</th>\n",
       "      <th>Fed_Reg</th>\n",
       "      <th>Fed_Insp</th>\n",
       "      <th>Srce_Agncy</th>\n",
       "      <th>Oth_StrucID</th>\n",
       "      <th>Num_Struc</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>VA16104</td>\n",
       "      <td>CLIFFORD D. CRAIG MEMORIAL DAM</td>\n",
       "      <td>2007-09-06</td>\n",
       "      <td>2013-03-12</td>\n",
       "      <td>TRIB. TO ROANOKE RIVER</td>\n",
       "      <td>SALEM</td>\n",
       "      <td>ROANOKE CO</td>\n",
       "      <td>VA</td>\n",
       "      <td>VA09</td>\n",
       "      <td>H. MORGAN GRIFFITH (R)</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>-80.1750</td>\n",
       "      <td>37.2250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>VA07915</td>\n",
       "      <td>GREENE MOUNTAIN LAKE DAM</td>\n",
       "      <td>2008-07-14</td>\n",
       "      <td>2013-03-12</td>\n",
       "      <td>BLUE RUN</td>\n",
       "      <td>ADVANCE MILLS</td>\n",
       "      <td>GREENE</td>\n",
       "      <td>VA</td>\n",
       "      <td>VA05</td>\n",
       "      <td>ROBERT HURT (R)</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>-78.4366</td>\n",
       "      <td>38.2700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>VA06906</td>\n",
       "      <td>LEHMANS DAM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-03-12</td>\n",
       "      <td>GOUGH RUN</td>\n",
       "      <td>MARLBORO</td>\n",
       "      <td>FREDERICK</td>\n",
       "      <td>VA</td>\n",
       "      <td>VA10</td>\n",
       "      <td>FRANK R. WOLF (R)</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>-78.3083</td>\n",
       "      <td>39.1516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>VA13905</td>\n",
       "      <td>LURAY</td>\n",
       "      <td>2010-12-22</td>\n",
       "      <td>2013-02-28</td>\n",
       "      <td>SOUTH FORK SHENANDOAH RIVER</td>\n",
       "      <td>RILEYVILLE</td>\n",
       "      <td>PAGE</td>\n",
       "      <td>VA</td>\n",
       "      <td>VA06</td>\n",
       "      <td>BOB GOODLATTE (R)</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FERC</td>\n",
       "      <td>FERC</td>\n",
       "      <td>FERC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>-78.4999</td>\n",
       "      <td>38.6774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>VA06106</td>\n",
       "      <td>MATHEWS DAM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-03-12</td>\n",
       "      <td>TR-GAP RUN</td>\n",
       "      <td>RECTORTOWN</td>\n",
       "      <td>FAUQUIER</td>\n",
       "      <td>VA</td>\n",
       "      <td>VA05</td>\n",
       "      <td>ROBERT HURT (R)</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>-77.9600</td>\n",
       "      <td>38.9800</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     NIDID                        Dam_Name   Insp_Date Submit_Date  \\\n",
       "0  VA16104  CLIFFORD D. CRAIG MEMORIAL DAM  2007-09-06  2013-03-12   \n",
       "1  VA07915        GREENE MOUNTAIN LAKE DAM  2008-07-14  2013-03-12   \n",
       "2  VA06906                     LEHMANS DAM         NaN  2013-03-12   \n",
       "3  VA13905                           LURAY  2010-12-22  2013-02-28   \n",
       "4  VA06106                     MATHEWS DAM         NaN  2013-03-12   \n",
       "\n",
       "                         River        City_02      County State Cong_Dist  \\\n",
       "0       TRIB. TO ROANOKE RIVER          SALEM  ROANOKE CO    VA      VA09   \n",
       "1                     BLUE RUN  ADVANCE MILLS      GREENE    VA      VA05   \n",
       "2                    GOUGH RUN       MARLBORO   FREDERICK    VA      VA10   \n",
       "3  SOUTH FORK SHENANDOAH RIVER     RILEYVILLE        PAGE    VA      VA06   \n",
       "4                   TR-GAP RUN     RECTORTOWN    FAUQUIER    VA      VA05   \n",
       "\n",
       "                 Cong_Rep  ... Fed_Fund Fed_Design Fed_Con  Fed_Reg Fed_Insp  \\\n",
       "0  H. MORGAN GRIFFITH (R)  ...      NaN        NaN     NaN      NaN      NaN   \n",
       "1         ROBERT HURT (R)  ...      NaN        NaN     NaN      NaN      NaN   \n",
       "2       FRANK R. WOLF (R)  ...      NaN        NaN     NaN      NaN      NaN   \n",
       "3       BOB GOODLATTE (R)  ...      NaN        NaN     NaN     FERC     FERC   \n",
       "4         ROBERT HURT (R)  ...      NaN        NaN     NaN      NaN      NaN   \n",
       "\n",
       "  Srce_Agncy Oth_StrucID Num_Struc Longitude Latitude  \n",
       "0         VA         NaN         0  -80.1750  37.2250  \n",
       "1         VA         NaN         0  -78.4366  38.2700  \n",
       "2         VA         NaN         0  -78.3083  39.1516  \n",
       "3       FERC         NaN         1  -78.4999  38.6774  \n",
       "4         VA         NaN         0  -77.9600  38.9800  \n",
       "\n",
       "[5 rows x 42 columns]"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "has_year_comp.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What years remain? Let's use `value_counts()` to find out:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1960.0    86\n",
       "1965.0    56\n",
       "1974.0    54\n",
       "1955.0    52\n",
       "1967.0    51\n",
       "          ..\n",
       "1832.0     1\n",
       "1914.0     1\n",
       "1682.0     1\n",
       "1922.0     1\n",
       "1881.0     1\n",
       "Name: Year_Comp, Length: 142, dtype: int64"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "has_year_comp.Year_Comp.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(To sort by year, not count, we could tack on a `sort_index()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1682.0     1\n",
       "1694.0     1\n",
       "1780.0     2\n",
       "1800.0    11\n",
       "1801.0     1\n",
       "          ..\n",
       "2008.0     7\n",
       "2009.0     6\n",
       "2010.0     2\n",
       "2011.0     1\n",
       "2012.0     1\n",
       "Name: Year_Comp, Length: 142, dtype: int64"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "has_year_comp.Year_Comp.value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouping and aggregating data\n",
    "\n",
    "You can use the `groupby()` method to group and aggregate data in pandas, similar to what you'd get by running a pivot table in Excel or a `GROUP BY` query in SQL. We'll also provide the aggregate function to use.\n",
    "\n",
    "Let's group our baseball salary data by team to see which teams have the biggest payrolls -- in other words, we want to use `sum()` as our aggregate function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_mlb = mlb.groupby('TEAM').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SALARY</th>\n",
       "      <th>START_YEAR</th>\n",
       "      <th>END_YEAR</th>\n",
       "      <th>YEARS</th>\n",
       "      <th>contract_total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TEAM</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ARI</th>\n",
       "      <td>90730499</td>\n",
       "      <td>56469</td>\n",
       "      <td>56485</td>\n",
       "      <td>44</td>\n",
       "      <td>341698661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ATL</th>\n",
       "      <td>137339527</td>\n",
       "      <td>60491</td>\n",
       "      <td>60525</td>\n",
       "      <td>64</td>\n",
       "      <td>593579662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BAL</th>\n",
       "      <td>161684185</td>\n",
       "      <td>56460</td>\n",
       "      <td>56485</td>\n",
       "      <td>53</td>\n",
       "      <td>510234644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BOS</th>\n",
       "      <td>174287098</td>\n",
       "      <td>62510</td>\n",
       "      <td>62541</td>\n",
       "      <td>62</td>\n",
       "      <td>749308534</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHC</th>\n",
       "      <td>170088502</td>\n",
       "      <td>52429</td>\n",
       "      <td>52456</td>\n",
       "      <td>53</td>\n",
       "      <td>648189802</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         SALARY  START_YEAR  END_YEAR  YEARS  contract_total\n",
       "TEAM                                                        \n",
       "ARI    90730499       56469     56485     44       341698661\n",
       "ATL   137339527       60491     60525     64       593579662\n",
       "BAL   161684185       56460     56485     53       510234644\n",
       "BOS   174287098       62510     62541     62       749308534\n",
       "CHC   170088502       52429     52456     53       648189802"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_mlb.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you don't specify what columns you want, it will run `sum()` on every numeric column. Typically I select just the grouping column and the column I'm running the aggregation on:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_mlb = mlb[['TEAM', 'SALARY']].groupby('TEAM').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SALARY</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TEAM</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ARI</th>\n",
       "      <td>90730499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ATL</th>\n",
       "      <td>137339527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BAL</th>\n",
       "      <td>161684185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BOS</th>\n",
       "      <td>174287098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHC</th>\n",
       "      <td>170088502</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         SALARY\n",
       "TEAM           \n",
       "ARI    90730499\n",
       "ATL   137339527\n",
       "BAL   161684185\n",
       "BOS   174287098\n",
       "CHC   170088502"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_mlb.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "... and we can sort descending, with `head()` to get the top payrolls:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SALARY</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TEAM</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>LAD</th>\n",
       "      <td>187989811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DET</th>\n",
       "      <td>180250600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TEX</th>\n",
       "      <td>178431396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SF</th>\n",
       "      <td>176531278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NYM</th>\n",
       "      <td>176284679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BOS</th>\n",
       "      <td>174287098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NYY</th>\n",
       "      <td>170389199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHC</th>\n",
       "      <td>170088502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WSH</th>\n",
       "      <td>162742157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TOR</th>\n",
       "      <td>162353367</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         SALARY\n",
       "TEAM           \n",
       "LAD   187989811\n",
       "DET   180250600\n",
       "TEX   178431396\n",
       "SF    176531278\n",
       "NYM   176284679\n",
       "BOS   174287098\n",
       "NYY   170389199\n",
       "CHC   170088502\n",
       "WSH   162742157\n",
       "TOR   162353367"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_mlb.sort_values('SALARY', ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can use different aggregate functions, too. Let's say we wanted to get the top median salaries by team:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SALARY</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TEAM</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>WSH</th>\n",
       "      <td>4000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KC</th>\n",
       "      <td>4000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HOU</th>\n",
       "      <td>3725000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BAL</th>\n",
       "      <td>3462500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PIT</th>\n",
       "      <td>2962500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CLE</th>\n",
       "      <td>2950000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TOR</th>\n",
       "      <td>2887500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STL</th>\n",
       "      <td>2762500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MIA</th>\n",
       "      <td>2762500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHC</th>\n",
       "      <td>2750000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       SALARY\n",
       "TEAM         \n",
       "WSH   4000000\n",
       "KC    4000000\n",
       "HOU   3725000\n",
       "BAL   3462500\n",
       "PIT   2962500\n",
       "CLE   2950000\n",
       "TOR   2887500\n",
       "STL   2762500\n",
       "MIA   2762500\n",
       "CHC   2750000"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb[['TEAM', 'SALARY']].groupby('TEAM').median().sort_values('SALARY', ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can group by multiple columns by passing a list. Here, we'll select our columns of interest and group by `TEAM`, then by `POS`, using `sum()` as our aggregate function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>SALARY</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TEAM</th>\n",
       "      <th>POS</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">ARI</th>\n",
       "      <th>1B</th>\n",
       "      <td>10183333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3B</th>\n",
       "      <td>1127200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>4437500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CF</th>\n",
       "      <td>7289500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LF</th>\n",
       "      <td>542500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">WSH</th>\n",
       "      <th>LF</th>\n",
       "      <td>22971429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RF</th>\n",
       "      <td>13625000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RP</th>\n",
       "      <td>15698700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SP</th>\n",
       "      <td>54886428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SS</th>\n",
       "      <td>537800</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>306 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            SALARY\n",
       "TEAM POS          \n",
       "ARI  1B   10183333\n",
       "     3B    1127200\n",
       "     C     4437500\n",
       "     CF    7289500\n",
       "     LF     542500\n",
       "...            ...\n",
       "WSH  LF   22971429\n",
       "     RF   13625000\n",
       "     RP   15698700\n",
       "     SP   54886428\n",
       "     SS     537800\n",
       "\n",
       "[306 rows x 1 columns]"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlb[['TEAM', 'POS', 'SALARY']].groupby(['TEAM', 'POS']).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivot tables\n",
    "\n",
    "Sometimes you need a full-blown pivot table, and [pandas has a function to make one](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.pivot_table.html).\n",
    "\n",
    "For this example, we'll look at some foreign trade data -- specifically, eel product imports from 2010 to mid-2017:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "eels = pd.read_csv('eels.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>country</th>\n",
       "      <th>product</th>\n",
       "      <th>kilos</th>\n",
       "      <th>dollars</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>CHINA</td>\n",
       "      <td>EELS FROZEN</td>\n",
       "      <td>49087</td>\n",
       "      <td>393583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>JAPAN</td>\n",
       "      <td>EELS FRESH</td>\n",
       "      <td>263</td>\n",
       "      <td>7651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>TAIWAN</td>\n",
       "      <td>EELS FROZEN</td>\n",
       "      <td>9979</td>\n",
       "      <td>116359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>VIETNAM</td>\n",
       "      <td>EELS FRESH</td>\n",
       "      <td>1938</td>\n",
       "      <td>10851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>VIETNAM</td>\n",
       "      <td>EELS FROZEN</td>\n",
       "      <td>21851</td>\n",
       "      <td>69955</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  month  country      product  kilos  dollars\n",
       "0  2010      1    CHINA  EELS FROZEN  49087   393583\n",
       "1  2010      1    JAPAN   EELS FRESH    263     7651\n",
       "2  2010      1   TAIWAN  EELS FROZEN   9979   116359\n",
       "3  2010      1  VIETNAM   EELS FRESH   1938    10851\n",
       "4  2010      1  VIETNAM  EELS FROZEN  21851    69955"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eels.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's run a pivot table where the grouping column is `country`, the values are the sum of `kilos`, and the columns are the year:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [],
   "source": [
    "pivoted_sums = pd.pivot_table(eels,\n",
    "                              index='country',\n",
    "                              columns='year',\n",
    "                              values='kilos',\n",
    "                              aggfunc=sum)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>year</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>BANGLADESH</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BURMA</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>699.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CANADA</th>\n",
       "      <td>13552.0</td>\n",
       "      <td>24968.0</td>\n",
       "      <td>110796.0</td>\n",
       "      <td>44455.0</td>\n",
       "      <td>31546.0</td>\n",
       "      <td>28619.0</td>\n",
       "      <td>68568.0</td>\n",
       "      <td>23571.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHILE</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6185.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHINA</th>\n",
       "      <td>372397.0</td>\n",
       "      <td>249232.0</td>\n",
       "      <td>1437392.0</td>\n",
       "      <td>1090135.0</td>\n",
       "      <td>1753140.0</td>\n",
       "      <td>4713882.0</td>\n",
       "      <td>4578546.0</td>\n",
       "      <td>1771272.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "year            2010      2011       2012       2013       2014       2015  \\\n",
       "country                                                                      \n",
       "BANGLADESH       NaN       NaN       13.0        NaN        NaN      600.0   \n",
       "BURMA            NaN       NaN        NaN        NaN        NaN        NaN   \n",
       "CANADA       13552.0   24968.0   110796.0    44455.0    31546.0    28619.0   \n",
       "CHILE            NaN       NaN        NaN        NaN     6185.0        NaN   \n",
       "CHINA       372397.0  249232.0  1437392.0  1090135.0  1753140.0  4713882.0   \n",
       "\n",
       "year             2016       2017  \n",
       "country                           \n",
       "BANGLADESH        NaN        NaN  \n",
       "BURMA           699.0        NaN  \n",
       "CANADA        68568.0    23571.0  \n",
       "CHILE             NaN        NaN  \n",
       "CHINA       4578546.0  1771272.0  "
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivoted_sums.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's sort by the `2017` value. While we're at it, let's fill in null values (`NaN`) with zeroes using the [`fillna()`](https://pandas.pydata.org/pandas-docs/version/0.22/generated/pandas.DataFrame.fillna.html) method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>year</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>CHINA</th>\n",
       "      <td>372397.0</td>\n",
       "      <td>249232.0</td>\n",
       "      <td>1437392.0</td>\n",
       "      <td>1090135.0</td>\n",
       "      <td>1753140.0</td>\n",
       "      <td>4713882.0</td>\n",
       "      <td>4578546.0</td>\n",
       "      <td>1771272.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TAIWAN</th>\n",
       "      <td>73842.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>53774.0</td>\n",
       "      <td>39752.0</td>\n",
       "      <td>83478.0</td>\n",
       "      <td>48272.0</td>\n",
       "      <td>99535.0</td>\n",
       "      <td>44087.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SOUTH KOREA</th>\n",
       "      <td>42929.0</td>\n",
       "      <td>41385.0</td>\n",
       "      <td>28146.0</td>\n",
       "      <td>27353.0</td>\n",
       "      <td>37708.0</td>\n",
       "      <td>8386.0</td>\n",
       "      <td>14729.0</td>\n",
       "      <td>42904.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JAPAN</th>\n",
       "      <td>1326.0</td>\n",
       "      <td>2509.0</td>\n",
       "      <td>32255.0</td>\n",
       "      <td>105758.0</td>\n",
       "      <td>40177.0</td>\n",
       "      <td>69699.0</td>\n",
       "      <td>71748.0</td>\n",
       "      <td>37892.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>THAILAND</th>\n",
       "      <td>2866.0</td>\n",
       "      <td>5018.0</td>\n",
       "      <td>9488.0</td>\n",
       "      <td>4488.0</td>\n",
       "      <td>15110.0</td>\n",
       "      <td>41771.0</td>\n",
       "      <td>26931.0</td>\n",
       "      <td>31884.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VIETNAM</th>\n",
       "      <td>63718.0</td>\n",
       "      <td>155488.0</td>\n",
       "      <td>118063.0</td>\n",
       "      <td>100828.0</td>\n",
       "      <td>38112.0</td>\n",
       "      <td>36859.0</td>\n",
       "      <td>96179.0</td>\n",
       "      <td>28490.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CANADA</th>\n",
       "      <td>13552.0</td>\n",
       "      <td>24968.0</td>\n",
       "      <td>110796.0</td>\n",
       "      <td>44455.0</td>\n",
       "      <td>31546.0</td>\n",
       "      <td>28619.0</td>\n",
       "      <td>68568.0</td>\n",
       "      <td>23571.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PORTUGAL</th>\n",
       "      <td>2081.0</td>\n",
       "      <td>3672.0</td>\n",
       "      <td>2579.0</td>\n",
       "      <td>2041.0</td>\n",
       "      <td>7215.0</td>\n",
       "      <td>8013.0</td>\n",
       "      <td>9105.0</td>\n",
       "      <td>6747.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PANAMA</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11849.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>974.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BANGLADESH</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BURMA</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>699.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHILE</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6185.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHINA - HONG KONG</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>735.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COSTA RICA</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>563.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>INDIA</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2200.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MEXICO</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16860.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NEW ZEALAND</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2652.0</td>\n",
       "      <td>900.0</td>\n",
       "      <td>270.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NORWAY</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17391.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAKISTAN</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22453.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PHILIPPINES</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>610.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>POLAND</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1296.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>864.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SENEGAL</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1350.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SPAIN</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>977.0</td>\n",
       "      <td>275.0</td>\n",
       "      <td>1019.0</td>\n",
       "      <td>719.0</td>\n",
       "      <td>1008.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UKRAINE</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11414.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "year                   2010      2011       2012       2013       2014  \\\n",
       "country                                                                  \n",
       "CHINA              372397.0  249232.0  1437392.0  1090135.0  1753140.0   \n",
       "TAIWAN              73842.0       0.0    53774.0    39752.0    83478.0   \n",
       "SOUTH KOREA         42929.0   41385.0    28146.0    27353.0    37708.0   \n",
       "JAPAN                1326.0    2509.0    32255.0   105758.0    40177.0   \n",
       "THAILAND             2866.0    5018.0     9488.0     4488.0    15110.0   \n",
       "VIETNAM             63718.0  155488.0   118063.0   100828.0    38112.0   \n",
       "CANADA              13552.0   24968.0   110796.0    44455.0    31546.0   \n",
       "PORTUGAL             2081.0    3672.0     2579.0     2041.0     7215.0   \n",
       "PANAMA                  0.0       0.0        0.0    11849.0        0.0   \n",
       "BANGLADESH              0.0       0.0       13.0        0.0        0.0   \n",
       "BURMA                   0.0       0.0        0.0        0.0        0.0   \n",
       "CHILE                   0.0       0.0        0.0        0.0     6185.0   \n",
       "CHINA - HONG KONG       0.0       0.0        0.0        0.0        0.0   \n",
       "COSTA RICA              0.0       0.0        0.0        0.0        0.0   \n",
       "INDIA                   0.0       0.0        0.0        0.0        0.0   \n",
       "MEXICO                  0.0       0.0        0.0     4000.0        0.0   \n",
       "NEW ZEALAND             0.0    2652.0      900.0      270.0        0.0   \n",
       "NORWAY                  0.0       0.0        0.0    17391.0        0.0   \n",
       "PAKISTAN                0.0       0.0        0.0    22453.0        0.0   \n",
       "PHILIPPINES             0.0       0.0        0.0      610.0        0.0   \n",
       "POLAND                  0.0       0.0     1296.0        0.0      864.0   \n",
       "SENEGAL                 0.0    1350.0        0.0        0.0        0.0   \n",
       "SPAIN                   0.0       0.0      977.0      275.0     1019.0   \n",
       "UKRAINE                 0.0       0.0        0.0        0.0        0.0   \n",
       "\n",
       "year                    2015       2016       2017  \n",
       "country                                             \n",
       "CHINA              4713882.0  4578546.0  1771272.0  \n",
       "TAIWAN               48272.0    99535.0    44087.0  \n",
       "SOUTH KOREA           8386.0    14729.0    42904.0  \n",
       "JAPAN                69699.0    71748.0    37892.0  \n",
       "THAILAND             41771.0    26931.0    31884.0  \n",
       "VIETNAM              36859.0    96179.0    28490.0  \n",
       "CANADA               28619.0    68568.0    23571.0  \n",
       "PORTUGAL              8013.0     9105.0     6747.0  \n",
       "PANAMA                   0.0        0.0      974.0  \n",
       "BANGLADESH             600.0        0.0        0.0  \n",
       "BURMA                    0.0      699.0        0.0  \n",
       "CHILE                    0.0        0.0        0.0  \n",
       "CHINA - HONG KONG        0.0      735.0        0.0  \n",
       "COSTA RICA               0.0      563.0        0.0  \n",
       "INDIA                    0.0     2200.0        0.0  \n",
       "MEXICO               16860.0        0.0        0.0  \n",
       "NEW ZEALAND              0.0        0.0        0.0  \n",
       "NORWAY                   0.0        0.0        0.0  \n",
       "PAKISTAN                 0.0        0.0        0.0  \n",
       "PHILIPPINES              0.0        0.0        0.0  \n",
       "POLAND                   0.0        0.0        0.0  \n",
       "SENEGAL                  0.0        0.0        0.0  \n",
       "SPAIN                  719.0     1008.0        0.0  \n",
       "UKRAINE                  0.0    11414.0        0.0  "
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivoted_sums.sort_values(2017, ascending=False).fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Applying a function across rows\n",
    "\n",
    "Often, you'll want to calculate a value for every column but it won't be that simple, and you'll write a separate function that accepts one row of data, does some calculations and returns a value. We'll use the [`apply()`](https://pandas.pydata.org/pandas-docs/version/0.22/generated/pandas.DataFrame.apply.html) method to accomplish this.\n",
    "\n",
    "For this example, we're going to load up a CSV of gators killed by hunters in Florida:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [],
   "source": [
    "gators = pd.read_csv('gators.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>Area Number</th>\n",
       "      <th>Area Name</th>\n",
       "      <th>Carcass Size</th>\n",
       "      <th>Harvest Date</th>\n",
       "      <th>Location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000</td>\n",
       "      <td>101</td>\n",
       "      <td>LAKE PIERCE</td>\n",
       "      <td>11 ft. 5 in.</td>\n",
       "      <td>09-22-2000</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2000</td>\n",
       "      <td>101</td>\n",
       "      <td>LAKE PIERCE</td>\n",
       "      <td>9 ft. 0 in.</td>\n",
       "      <td>10-02-2000</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2000</td>\n",
       "      <td>101</td>\n",
       "      <td>LAKE PIERCE</td>\n",
       "      <td>8 ft. 10 in.</td>\n",
       "      <td>10-06-2000</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2000</td>\n",
       "      <td>101</td>\n",
       "      <td>LAKE PIERCE</td>\n",
       "      <td>8 ft. 0 in.</td>\n",
       "      <td>09-25-2000</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2000</td>\n",
       "      <td>101</td>\n",
       "      <td>LAKE PIERCE</td>\n",
       "      <td>8 ft. 0 in.</td>\n",
       "      <td>10-07-2000</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  Area Number    Area Name  Carcass Size Harvest Date Location\n",
       "0  2000          101  LAKE PIERCE  11 ft. 5 in.   09-22-2000         \n",
       "1  2000          101  LAKE PIERCE   9 ft. 0 in.   10-02-2000         \n",
       "2  2000          101  LAKE PIERCE  8 ft. 10 in.   10-06-2000         \n",
       "3  2000          101  LAKE PIERCE   8 ft. 0 in.   09-25-2000         \n",
       "4  2000          101  LAKE PIERCE   8 ft. 0 in.   10-07-2000         "
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gators.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We want to find the longest gator in our data, of course, but there's a problem: right now, the caracass size value is being stored as text: `{} ft. {} in.`. The pattern is predicatable, though, and we can use some Python to turn those values into constant numbers -- inches -- that we can then sort on. Here's our function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_inches(row):\n",
    "    '''Accepts a row from our dataframe, calculates carcass length in inches and returns that value'''\n",
    "\n",
    "    # get the value in the 'Carcass Size' column\n",
    "    carcass_size = row['Carcass Size']\n",
    "    \n",
    "    # split the text on 'ft.'\n",
    "    # the result is a list\n",
    "    size_split = carcass_size.split('ft.')\n",
    "    \n",
    "    # strip whitespace from the first item ([0]) in the resulting list -- the feet --\n",
    "    # and coerce it to an integer with the Python `int()` function\n",
    "    feet = int(size_split[0].strip())\n",
    "    \n",
    "    # in the second item ([1]) in the resulting list -- the inches -- replace 'in.' with nothing,\n",
    "    # strip whitespace and coerce to an integer\n",
    "    inches = int(size_split[1].replace('in.', '').strip())\n",
    "    \n",
    "    # add the feet times 12 plus the inches and return that value\n",
    "    return inches + (feet * 12)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we're going to create a new column, `length_in` and use the [`apply()`](https://pandas.pydata.org/pandas-docs/version/0.22/generated/pandas.DataFrame.apply.html) method to apply our function to every row. The `axis=1` keyword argument means that we're applying our function row-wise, not column-wise."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [],
   "source": [
    "gators['length_in'] = gators.apply(get_inches, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>Area Number</th>\n",
       "      <th>Area Name</th>\n",
       "      <th>Carcass Size</th>\n",
       "      <th>Harvest Date</th>\n",
       "      <th>Location</th>\n",
       "      <th>length_in</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>44996</th>\n",
       "      <td>2010</td>\n",
       "      <td>502</td>\n",
       "      <td>ST. JOHNS RIVER (LAKE POINSETT)</td>\n",
       "      <td>14 ft. 3 in.</td>\n",
       "      <td>10-31-2010</td>\n",
       "      <td></td>\n",
       "      <td>171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78315</th>\n",
       "      <td>2014</td>\n",
       "      <td>828</td>\n",
       "      <td>HIGHLANDS COUNTY</td>\n",
       "      <td>14 ft. 3 in.</td>\n",
       "      <td>10-28-2014</td>\n",
       "      <td>LITTLE RED WATER LAKE</td>\n",
       "      <td>171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31961</th>\n",
       "      <td>2008</td>\n",
       "      <td>510</td>\n",
       "      <td>LAKE JESUP</td>\n",
       "      <td>14 ft. 1 in.</td>\n",
       "      <td>08-26-2008</td>\n",
       "      <td></td>\n",
       "      <td>169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70005</th>\n",
       "      <td>2013</td>\n",
       "      <td>733</td>\n",
       "      <td>LAKE TALQUIN</td>\n",
       "      <td>14 ft. 1 in.</td>\n",
       "      <td>09-02-2013</td>\n",
       "      <td></td>\n",
       "      <td>169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63077</th>\n",
       "      <td>2012</td>\n",
       "      <td>828</td>\n",
       "      <td>HIGHLANDS COUNTY</td>\n",
       "      <td>14 ft. 0 in.</td>\n",
       "      <td>10-31-2012</td>\n",
       "      <td>boat ramp north of boat ramp road</td>\n",
       "      <td>168</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Year  Area Number                        Area Name  Carcass Size  \\\n",
       "44996  2010          502  ST. JOHNS RIVER (LAKE POINSETT)  14 ft. 3 in.   \n",
       "78315  2014          828                 HIGHLANDS COUNTY  14 ft. 3 in.   \n",
       "31961  2008          510                       LAKE JESUP  14 ft. 1 in.   \n",
       "70005  2013          733                     LAKE TALQUIN  14 ft. 1 in.   \n",
       "63077  2012          828                 HIGHLANDS COUNTY  14 ft. 0 in.   \n",
       "\n",
       "      Harvest Date                           Location  length_in  \n",
       "44996   10-31-2010                                           171  \n",
       "78315   10-28-2014              LITTLE RED WATER LAKE        171  \n",
       "31961   08-26-2008                                           169  \n",
       "70005   09-02-2013                                           169  \n",
       "63077   10-31-2012  boat ramp north of boat ramp road        168  "
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gators.sort_values('length_in', ascending=False).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Joining data\n",
    "\n",
    "You can use [`merge()`](https://pandas.pydata.org/pandas-docs/version/0.22/generated/pandas.DataFrame.merge.html) to join data in pandas.\n",
    "\n",
    "In this simple example, we're going to take a CSV of country population data in which each country is represented by an [ISO 3166-1 numeric country code](https://en.wikipedia.org/wiki/ISO_3166-1_numeric) and join it to a CSV that's basically a lookup table with the ISO codes and the names of the countries to which they refer.\n",
    "\n",
    "Some of the country codes have leading zeroes, so we're going to use the `dtype` keyword when we import each CSV to specify that the `'code'` column in each dataset should be treated as a string (text), not a number."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [],
   "source": [
    "pop_csv = pd.read_csv('country-population.csv', dtype={'code': str})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>pop2000</th>\n",
       "      <th>pop2001</th>\n",
       "      <th>pop2002</th>\n",
       "      <th>pop2003</th>\n",
       "      <th>pop2004</th>\n",
       "      <th>pop2005</th>\n",
       "      <th>pop2006</th>\n",
       "      <th>pop2007</th>\n",
       "      <th>pop2008</th>\n",
       "      <th>pop2009</th>\n",
       "      <th>pop2010</th>\n",
       "      <th>pop2011</th>\n",
       "      <th>pop2012</th>\n",
       "      <th>pop2013</th>\n",
       "      <th>pop2014</th>\n",
       "      <th>pop2015</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>108</td>\n",
       "      <td>6401.0</td>\n",
       "      <td>6556.0</td>\n",
       "      <td>6742.0</td>\n",
       "      <td>6953.0</td>\n",
       "      <td>7182.0</td>\n",
       "      <td>7423.0</td>\n",
       "      <td>7675.0</td>\n",
       "      <td>7940.0</td>\n",
       "      <td>8212.0</td>\n",
       "      <td>8489.0</td>\n",
       "      <td>8767.0</td>\n",
       "      <td>9044.0</td>\n",
       "      <td>9320.0</td>\n",
       "      <td>9600.0</td>\n",
       "      <td>9892.0</td>\n",
       "      <td>10199.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>174</td>\n",
       "      <td>542.0</td>\n",
       "      <td>556.0</td>\n",
       "      <td>569.0</td>\n",
       "      <td>583.0</td>\n",
       "      <td>597.0</td>\n",
       "      <td>612.0</td>\n",
       "      <td>626.0</td>\n",
       "      <td>642.0</td>\n",
       "      <td>657.0</td>\n",
       "      <td>673.0</td>\n",
       "      <td>690.0</td>\n",
       "      <td>707.0</td>\n",
       "      <td>724.0</td>\n",
       "      <td>742.0</td>\n",
       "      <td>759.0</td>\n",
       "      <td>777.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>262</td>\n",
       "      <td>718.0</td>\n",
       "      <td>733.0</td>\n",
       "      <td>746.0</td>\n",
       "      <td>759.0</td>\n",
       "      <td>771.0</td>\n",
       "      <td>783.0</td>\n",
       "      <td>796.0</td>\n",
       "      <td>809.0</td>\n",
       "      <td>823.0</td>\n",
       "      <td>837.0</td>\n",
       "      <td>851.0</td>\n",
       "      <td>866.0</td>\n",
       "      <td>881.0</td>\n",
       "      <td>897.0</td>\n",
       "      <td>912.0</td>\n",
       "      <td>927.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>232</td>\n",
       "      <td>3393.0</td>\n",
       "      <td>3497.0</td>\n",
       "      <td>3615.0</td>\n",
       "      <td>3738.0</td>\n",
       "      <td>3859.0</td>\n",
       "      <td>3969.0</td>\n",
       "      <td>4067.0</td>\n",
       "      <td>4153.0</td>\n",
       "      <td>4233.0</td>\n",
       "      <td>4310.0</td>\n",
       "      <td>4391.0</td>\n",
       "      <td>4475.0</td>\n",
       "      <td>4561.0</td>\n",
       "      <td>4651.0</td>\n",
       "      <td>4746.0</td>\n",
       "      <td>4847.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>231</td>\n",
       "      <td>66537.0</td>\n",
       "      <td>68492.0</td>\n",
       "      <td>70497.0</td>\n",
       "      <td>72545.0</td>\n",
       "      <td>74624.0</td>\n",
       "      <td>76727.0</td>\n",
       "      <td>78851.0</td>\n",
       "      <td>81000.0</td>\n",
       "      <td>83185.0</td>\n",
       "      <td>85416.0</td>\n",
       "      <td>87703.0</td>\n",
       "      <td>90047.0</td>\n",
       "      <td>92444.0</td>\n",
       "      <td>94888.0</td>\n",
       "      <td>97367.0</td>\n",
       "      <td>99873.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code  pop2000  pop2001  pop2002  pop2003  pop2004  pop2005  pop2006  \\\n",
       "0  108   6401.0   6556.0   6742.0   6953.0   7182.0   7423.0   7675.0   \n",
       "1  174    542.0    556.0    569.0    583.0    597.0    612.0    626.0   \n",
       "2  262    718.0    733.0    746.0    759.0    771.0    783.0    796.0   \n",
       "3  232   3393.0   3497.0   3615.0   3738.0   3859.0   3969.0   4067.0   \n",
       "4  231  66537.0  68492.0  70497.0  72545.0  74624.0  76727.0  78851.0   \n",
       "\n",
       "   pop2007  pop2008  pop2009  pop2010  pop2011  pop2012  pop2013  pop2014  \\\n",
       "0   7940.0   8212.0   8489.0   8767.0   9044.0   9320.0   9600.0   9892.0   \n",
       "1    642.0    657.0    673.0    690.0    707.0    724.0    742.0    759.0   \n",
       "2    809.0    823.0    837.0    851.0    866.0    881.0    897.0    912.0   \n",
       "3   4153.0   4233.0   4310.0   4391.0   4475.0   4561.0   4651.0   4746.0   \n",
       "4  81000.0  83185.0  85416.0  87703.0  90047.0  92444.0  94888.0  97367.0   \n",
       "\n",
       "   pop2015  \n",
       "0  10199.0  \n",
       "1    777.0  \n",
       "2    927.0  \n",
       "3   4847.0  \n",
       "4  99873.0  "
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop_csv.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [],
   "source": [
    "code_csv = pd.read_csv('country-codes.csv', dtype={'code': str})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>108</td>\n",
       "      <td>Burundi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>174</td>\n",
       "      <td>Comoros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>262</td>\n",
       "      <td>Djibouti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>232</td>\n",
       "      <td>Eritrea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>231</td>\n",
       "      <td>Ethiopia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code   country\n",
       "0  108   Burundi\n",
       "1  174   Comoros\n",
       "2  262  Djibouti\n",
       "3  232   Eritrea\n",
       "4  231  Ethiopia"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "code_csv.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we'll use `merge()` to join them.\n",
    "\n",
    "The `on` keyword argument tells the method what column to join on. If the names of the columns were different, you'd use `left_on` and `right_on`, with the \"left\" dataframe being the first one you hand to the `merge()` function.\n",
    "\n",
    "The `how` keyword argument tells the method what type of join to use -- the default is `'inner'`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_data = pd.merge(pop_csv,\n",
    "                       code_csv,\n",
    "                       on='code',\n",
    "                       how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>pop2000</th>\n",
       "      <th>pop2001</th>\n",
       "      <th>pop2002</th>\n",
       "      <th>pop2003</th>\n",
       "      <th>pop2004</th>\n",
       "      <th>pop2005</th>\n",
       "      <th>pop2006</th>\n",
       "      <th>pop2007</th>\n",
       "      <th>pop2008</th>\n",
       "      <th>pop2009</th>\n",
       "      <th>pop2010</th>\n",
       "      <th>pop2011</th>\n",
       "      <th>pop2012</th>\n",
       "      <th>pop2013</th>\n",
       "      <th>pop2014</th>\n",
       "      <th>pop2015</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>108</td>\n",
       "      <td>6401.0</td>\n",
       "      <td>6556.0</td>\n",
       "      <td>6742.0</td>\n",
       "      <td>6953.0</td>\n",
       "      <td>7182.0</td>\n",
       "      <td>7423.0</td>\n",
       "      <td>7675.0</td>\n",
       "      <td>7940.0</td>\n",
       "      <td>8212.0</td>\n",
       "      <td>8489.0</td>\n",
       "      <td>8767.0</td>\n",
       "      <td>9044.0</td>\n",
       "      <td>9320.0</td>\n",
       "      <td>9600.0</td>\n",
       "      <td>9892.0</td>\n",
       "      <td>10199.0</td>\n",
       "      <td>Burundi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>174</td>\n",
       "      <td>542.0</td>\n",
       "      <td>556.0</td>\n",
       "      <td>569.0</td>\n",
       "      <td>583.0</td>\n",
       "      <td>597.0</td>\n",
       "      <td>612.0</td>\n",
       "      <td>626.0</td>\n",
       "      <td>642.0</td>\n",
       "      <td>657.0</td>\n",
       "      <td>673.0</td>\n",
       "      <td>690.0</td>\n",
       "      <td>707.0</td>\n",
       "      <td>724.0</td>\n",
       "      <td>742.0</td>\n",
       "      <td>759.0</td>\n",
       "      <td>777.0</td>\n",
       "      <td>Comoros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>262</td>\n",
       "      <td>718.0</td>\n",
       "      <td>733.0</td>\n",
       "      <td>746.0</td>\n",
       "      <td>759.0</td>\n",
       "      <td>771.0</td>\n",
       "      <td>783.0</td>\n",
       "      <td>796.0</td>\n",
       "      <td>809.0</td>\n",
       "      <td>823.0</td>\n",
       "      <td>837.0</td>\n",
       "      <td>851.0</td>\n",
       "      <td>866.0</td>\n",
       "      <td>881.0</td>\n",
       "      <td>897.0</td>\n",
       "      <td>912.0</td>\n",
       "      <td>927.0</td>\n",
       "      <td>Djibouti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>232</td>\n",
       "      <td>3393.0</td>\n",
       "      <td>3497.0</td>\n",
       "      <td>3615.0</td>\n",
       "      <td>3738.0</td>\n",
       "      <td>3859.0</td>\n",
       "      <td>3969.0</td>\n",
       "      <td>4067.0</td>\n",
       "      <td>4153.0</td>\n",
       "      <td>4233.0</td>\n",
       "      <td>4310.0</td>\n",
       "      <td>4391.0</td>\n",
       "      <td>4475.0</td>\n",
       "      <td>4561.0</td>\n",
       "      <td>4651.0</td>\n",
       "      <td>4746.0</td>\n",
       "      <td>4847.0</td>\n",
       "      <td>Eritrea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>231</td>\n",
       "      <td>66537.0</td>\n",
       "      <td>68492.0</td>\n",
       "      <td>70497.0</td>\n",
       "      <td>72545.0</td>\n",
       "      <td>74624.0</td>\n",
       "      <td>76727.0</td>\n",
       "      <td>78851.0</td>\n",
       "      <td>81000.0</td>\n",
       "      <td>83185.0</td>\n",
       "      <td>85416.0</td>\n",
       "      <td>87703.0</td>\n",
       "      <td>90047.0</td>\n",
       "      <td>92444.0</td>\n",
       "      <td>94888.0</td>\n",
       "      <td>97367.0</td>\n",
       "      <td>99873.0</td>\n",
       "      <td>Ethiopia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code  pop2000  pop2001  pop2002  pop2003  pop2004  pop2005  pop2006  \\\n",
       "0  108   6401.0   6556.0   6742.0   6953.0   7182.0   7423.0   7675.0   \n",
       "1  174    542.0    556.0    569.0    583.0    597.0    612.0    626.0   \n",
       "2  262    718.0    733.0    746.0    759.0    771.0    783.0    796.0   \n",
       "3  232   3393.0   3497.0   3615.0   3738.0   3859.0   3969.0   4067.0   \n",
       "4  231  66537.0  68492.0  70497.0  72545.0  74624.0  76727.0  78851.0   \n",
       "\n",
       "   pop2007  pop2008  pop2009  pop2010  pop2011  pop2012  pop2013  pop2014  \\\n",
       "0   7940.0   8212.0   8489.0   8767.0   9044.0   9320.0   9600.0   9892.0   \n",
       "1    642.0    657.0    673.0    690.0    707.0    724.0    742.0    759.0   \n",
       "2    809.0    823.0    837.0    851.0    866.0    881.0    897.0    912.0   \n",
       "3   4153.0   4233.0   4310.0   4391.0   4475.0   4561.0   4651.0   4746.0   \n",
       "4  81000.0  83185.0  85416.0  87703.0  90047.0  92444.0  94888.0  97367.0   \n",
       "\n",
       "   pop2015   country  \n",
       "0  10199.0   Burundi  \n",
       "1    777.0   Comoros  \n",
       "2    927.0  Djibouti  \n",
       "3   4847.0   Eritrea  \n",
       "4  99873.0  Ethiopia  "
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined_data.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}