{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<p style=\"font-size:30px; text-align:center; line-height:120%\">\n",
"<br>\n",
"Assignment 5<br>\n",
"Basic Familiarity with Pandas and Scikit Learn<br>\n",
"(10 Points)\n",
"</p>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Introduction and Overview\n",
"\n",
"- The Definition of the Homework is in the final section.\n",
"\n",
"\n",
"- This is a vastly simplified version of what HW 5 would have been, and is straightforward. \n",
"\n",
"\n",
"- But remember, ...\n",
"\n",
"<img src=\"./its_a_trap.png\">\n",
"\n",
"\n",
"- The submission is a zip file with completed version of this notebook with the code cells executes. Some of the cells may generate plots in the directory containing the file, and you should include this plots in the submission."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Setup\n",
"\n",
"- Standard setup test.\n",
"\n",
"\n",
"- The extra-credit assignment requires Lahman2019clean."
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [],
"source": [
"import json\n",
"import pandas"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
   "The sql extension is already loaded. To reload it, use:\n",
   " %reload_ext sql\n"
]
},
{
"data": {
   "text/plain": [
   "u'Connected: root@lahman2019clean'"
   ]
},
"execution_count": 15,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%load_ext sql\n",
"%sql mysql+pymysql://dbuser:dbuserdbuser@localhost/lahman2019clean"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
   " * mysql+pymysql://root:***@localhost/lahman2019clean\n",
   "1 rows affected.\n"
]
},
{
"data": {
   "text/html": [
   "<table>\n",
   " <tr>\n",
   "    <th>playerID</th>\n",
   "    <th>birthYear</th>\n",
   "    <th>birthMonth</th>\n",
   "    <th>birthDay</th>\n",
   "    <th>birthCountry</th>\n",
   "    <th>birthState</th>\n",
   "    <th>birthCity</th>\n",
   "    <th>deathYear</th>\n",
   "    <th>deathMonth</th>\n",
   "    <th>deathDay</th>\n",
   "    <th>deathCountry</th>\n",
   "    <th>deathState</th>\n",
   "    <th>deathCity</th>\n",
   "    <th>nameFirst</th>\n",
   "    <th>nameLast</th>\n",
   "    <th>nameGiven</th>\n",
   "    <th>weight</th>\n",
   "    <th>height</th>\n",
   "    <th>bats</th>\n",
   "    <th>throws</th>\n",
   "    <th>debut</th>\n",
   "    <th>finalGame</th>\n",
   "    <th>retroID</th>\n",
   "    <th>bbrefID</th>\n",
   "    <th>birthDate</th>\n",
   "    <th>deathDate</th>\n",
   " </tr>\n",
   " <tr>\n",
   "    <td>willite01</td>\n",
   "    <td>1918</td>\n",
   "    <td>8</td>\n",
   "    <td>30</td>\n",
   "    <td>USA</td>\n",
   "    <td>CA</td>\n",
   "    <td>San Diego</td>\n",
   "    <td>2002</td>\n",
   "    <td>7</td>\n",
   "    <td>5</td>\n",
   "    <td>USA</td>\n",
   "    <td>FL</td>\n",
   "    <td>Inverness</td>\n",
   "    <td>Ted</td>\n",
   "    <td>Williams</td>\n",
   "    <td>Theodore Samuel</td>\n",
   "    <td>205</td>\n",
   "    <td>75</td>\n",
   "    <td>L</td>\n",
   "    <td>R</td>\n",
   "    <td>1939-04-20</td>\n",
   "    <td>1960-09-28</td>\n",
   "    <td>willt103</td>\n",
   "    <td>willite01</td>\n",
   "    <td>1918-08-30 00:00:00</td>\n",
   "    <td>2002-07-05 00:00:00</td>\n",
   " </tr>\n",
   "</table>"
   ],
   "text/plain": [
   "[('willite01', '1918', '8', '30', 'USA', 'CA', 'San Diego', '2002', '7', '5', 'USA', 'FL', 'Inverness', 'Ted', 'Williams', 'Theodore Samuel', 205, '75', 'L', 'R', '1939-04-20', '1960-09-28', 'willt103', 'willite01', datetime.datetime(1918, 8, 30, 0, 0), datetime.datetime(2002, 7, 5, 0, 0))]"
   ]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql select * from people where playerid='willite01'"
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {},
"outputs": [],
"source": [
"import pymysql\n",
"import logging\n",
"import json\n",
"\n",
"default_db_host=\"localhost\"\n",
"default_db_user=\"dbuser\"\n",
"default_db_password=\"dbuserdbuser\"\n",
"\n",
"def get_new_connection(host, user, password):\n",
" result_conn = pymysql.connect(\n",
"    host=host,\n",
"    user=user,\n",
"    password=password,\n",
"    cursorclass=pymysql.cursors.DictCursor,\n",
"    autocommit=False)\n",
" return result_conn\n",
"\n",
"#\n",
"# You may reuse this connection for the code cells if you need a connection\n",
"#\n",
"hw5_conn = get_new_connection(default_db_host, default_db_user, default_db_password)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Instructions\n",
"\n",
"- Use ```Lahman2019clean.People```, ```Lahman2019clean.Batting``` and ```Lahman2019clean.teams``` for this assignment."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Part 1: Comparing Pandas and SQL\n",
"\n",
"__NOTE:__\n",
"\n",
"- I was going to make figuring out how to achieve my sample SQL results using Pandas part of the homework. You would have to use Pandas functions and scripts to produce equivalent results.\n",
"\n",
"\n",
"- I decided that we have ALL had enough fun for one semester and gave you all of the answers instead of asking you to produce the Pandas equivalent functions. Consider this a gift from the [Holiday Armadillo.](https://www.youtube.com/watch?v=ungLa3ECO-E)\n",
"\n",
"<img src=\"./armadillo.jpg\">\n",
"\n",
"\n",
"- Your homework for Pandas and SQL sections is to just look at the notebook and get a feel for what is happening.\n",
" "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Pandas and SQL\n",
"\n",
"- \"A (Pandas) Data frame is a two-dimensional data structure, i.e., data is aligned in a tabular fashion in rows and columns. Pandas DataFrame consists of three principal components, the data, rows, and columns.\" (https://www.geeksforgeeks.org/python-pandas-dataframe/)\n",
"\n",
"\n",
"- For numerical data, a Pandas data frame is very much like a relational table.\n",
"\n",
"<img src=\"https://media.geeksforgeeks.org/wp-content/uploads/finallpandas.png\">\n",
"\n",
"\n",
"- Pandas supports many, many operations and capabilities. Many of the [capabilities support functions similar to SQL.](https://pandas.pydata.org/pandas-docs/stable/getting_started/comparison/comparison_with_sql.html) These include functions similar to:\n",
" - Selection\n",
" - Projection\n",
" - Aggregation and Group By\n",
" - Join\n",
" - etc."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Loading the Dataframes"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {},
"outputs": [],
"source": [
"# Make sure we have Pandas\n",
"import pandas"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"- In the next text cell, use Pandas and SQL to create three dataframes: ```people_df, batting_df``` and ```teams_df```"
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {},
"outputs": [],
"source": [
"# Your code to load the data into the dataframes goes here.\n",
"#\n",
"# NOTE: I decided to just show you the answers.\n",
"#\n",
"people_df = pandas.read_sql(\"select * from lahman2019clean.people\", hw5_conn)\n",
"batting_df = pandas.read_sql(\"select * from lahman2019clean.batting\", hw5_conn)\n",
"teams_df = pandas.read_sql(\"select * from lahman2019clean.teams\", hw5_conn)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"- Test Answers: Run the following cells to reproduce the results."
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {},
"outputs": [
{
"data": {
   "text/html": [
   "<div>\n",
   "<style scoped>\n",
   " .dataframe tbody tr th:only-of-type {\n",
   "    vertical-align: middle;\n",
   " }\n",
   "\n",
   " .dataframe tbody tr th {\n",
   "    vertical-align: top;\n",
   " }\n",
   "\n",
   " .dataframe thead th {\n",
   "    text-align: right;\n",
   " }\n",
   "</style>\n",
   "<table border=\"1\" class=\"dataframe\">\n",
   " <thead>\n",
   " <tr style=\"text-align: right;\">\n",
   "    <th></th>\n",
   "    <th>playerID</th>\n",
   "    <th>birthYear</th>\n",
   "    <th>birthMonth</th>\n",
   "    <th>birthDay</th>\n",
   "    <th>birthCountry</th>\n",
   "    <th>birthState</th>\n",
   "    <th>birthCity</th>\n",
   "    <th>deathYear</th>\n",
   "    <th>deathMonth</th>\n",
   "    <th>deathDay</th>\n",
   "    <th>...</th>\n",
   "    <th>weight</th>\n",
   "    <th>height</th>\n",
   "    <th>bats</th>\n",
   "    <th>throws</th>\n",
   "    <th>debut</th>\n",
   "    <th>finalGame</th>\n",
   "    <th>retroID</th>\n",
   "    <th>bbrefID</th>\n",
   "    <th>birthDate</th>\n",
   "    <th>deathDate</th>\n",
   " </tr>\n",
   " </thead>\n",
   " <tbody>\n",
   " <tr>\n",
   "    <th>0</th>\n",
   "    <td>aardsda01</td>\n",
   "    <td>1981</td>\n",
   "    <td>12</td>\n",
   "    <td>27</td>\n",
   "    <td>USA</td>\n",
   "    <td>CO</td>\n",
   "    <td>Denver</td>\n",
   "    <td>None</td>\n",
   "    <td>None</td>\n",
   "    <td>None</td>\n",
   "    <td>...</td>\n",
   "    <td>215.0</td>\n",
   "    <td>75</td>\n",
   "    <td>R</td>\n",
   "    <td>R</td>\n",
   "    <td>2004-04-06</td>\n",
   "    <td>2015-08-23</td>\n",
   "    <td>aardd001</td>\n",
   "    <td>aardsda01</td>\n",
   "    <td>1981-12-27</td>\n",
   "    <td>NaT</td>\n",
   " </tr>\n",
   " <tr>\n",
   "    <th>1</th>\n",
   "    <td>aaronha01</td>\n",
   "    <td>1934</td>\n",
   "    <td>2</td>\n",
   "    <td>5</td>\n",
   "    <td>USA</td>\n",
   "    <td>AL</td>\n",
   "    <td>Mobile</td>\n",
   "    <td>None</td>\n",
   "    <td>None</td>\n",
   "    <td>None</td>\n",
   "    <td>...</td>\n",
   "    <td>180.0</td>\n",
   "    <td>72</td>\n",
   "    <td>R</td>\n",
   "    <td>R</td>\n",
   "    <td>1954-04-13</td>\n",
   "    <td>1976-10-03</td>\n",
   "    <td>aaroh101</td>\n",
   "    <td>aaronha01</td>\n",
   "    <td>1934-02-05</td>\n",
   "    <td>NaT</td>\n",
   " </tr>\n",
   " <tr>\n",
   "    <th>2</th>\n",
   "    <td>aaronto01</td>\n",
   "    <td>1939</td>\n",
   "    <td>8</td>\n",
   "    <td>5</td>\n",
   "    <td>USA</td>\n",
   "    <td>AL</td>\n",
   "    <td>Mobile</td>\n",
   "    <td>1984</td>\n",
   "    <td>8</td>\n",
   "    <td>16</td>\n",
   "    <td>...</td>\n",
   "    <td>190.0</td>\n",
   "    <td>75</td>\n",
   "    <td>R</td>\n",
   "    <td>R</td>\n",
   "    <td>1962-04-10</td>\n",
   "    <td>1971-09-26</td>\n",
   "    <td>aarot101</td>\n",
   "    <td>aaronto01</td>\n",
   "    <td>1939-08-05</td>\n",
   "    <td>1984-08-16</td>\n",
   " </tr>\n",
   " <tr>\n",
   "    <th>3</th>\n",
   "    <td>aasedo01</td>\n",
   "    <td>1954</td>\n",
   "    <td>9</td>\n",
   "    <td>8</td>\n",
   "    <td>USA</td>\n",
   "    <td>CA</td>\n",
   "    <td>Orange</td>\n",
   "    <td>None</td>\n",
   "    <td>None</td>\n",
   "    <td>None</td>\n",
   "    <td>...</td>\n",
   "    <td>190.0</td>\n",
   "    <td>75</td>\n",
   "    <td>R</td>\n",
   "    <td>R</td>\n",
   "    <td>1977-07-26</td>\n",
   "    <td>1990-10-03</td>\n",
   "    <td>aased001</td>\n",
   "    <td>aasedo01</td>\n",
   "    <td>1954-09-08</td>\n",
   "    <td>NaT</td>\n",
   " </tr>\n",
   " <tr>\n",
   "    <th>4</th>\n",
   "    <td>abadan01</td>\n",
   "    <td>1972</td>\n",
   "    <td>8</td>\n",
   "    <td>25</td>\n",
   "    <td>USA</td>\n",
   "    <td>FL</td>\n",
   "    <td>Palm Beach</td>\n",
   "    <td>None</td>\n",
   "    <td>None</td>\n",
   "    <td>None</td>\n",
   "    <td>...</td>\n",
   "    <td>184.0</td>\n",
   "    <td>73</td>\n",
   "    <td>L</td>\n",
   "    <td>L</td>\n",
   "    <td>2001-09-10</td>\n",
   "    <td>2006-04-13</td>\n",
   "    <td>abada001</td>\n",
   "    <td>abadan01</td>\n",
   "    <td>1972-08-25</td>\n",
   "    <td>NaT</td>\n",
   " </tr>\n",
   " <tr>\n",
   "    <th>5</th>\n",
   "    <td>abadfe01</td>\n",
   "    <td>1985</td>\n",
   "    <td>12</td>\n",
   "    <td>17</td>\n",
   "    <td>D.R.</td>\n",
   "    <td>La Romana</td>\n",
   "    <td>La Romana</td>\n",
   "    <td>None</td>\n",
   "    <td>None</td>\n",
   "    <td>None</td>\n",
   "    <td>...</td>\n",
   "    <td>220.0</td>\n",
   "    <td>73</td>\n",
   "    <td>L</td>\n",
   "    <td>L</td>\n",
   "    <td>2010-07-28</td>\n",
   "    <td>2017-10-01</td>\n",
   "    <td>abadf001</td>\n",
   "    <td>abadfe01</td>\n",
   "    <td>1985-12-17</td>\n",
   "    <td>NaT</td>\n",
   " </tr>\n",
   " <tr>\n",
   "    <th>6</th>\n",
   "    <td>abadijo01</td>\n",
   "    <td>1850</td>\n",
   "    <td>11</td>\n",
   "    <td>4</td>\n",
   "    <td>USA</td>\n",
   "    <td>PA</td>\n",
   "    <td>Philadelphia</td>\n",
   "    <td>1905</td>\n",
   "    <td>5</td>\n",
   "    <td>17</td>\n",
   "    <td>...</td>\n",
   "    <td>192.0</td>\n",
   "    <td>72</td>\n",
   "    <td>R</td>\n",
   "    <td>R</td>\n",
   "    <td>1875-04-26</td>\n",
   "    <td>1875-06-10</td>\n",
   "    <td>abadj101</td>\n",
   "    <td>abadijo01</td>\n",
   "    <td>1850-11-04 00:00:00</td>\n",
   "    <td>1905-05-17</td>\n",
   " </tr>\n",
   " <tr>\n",
   "    <th>7</th>\n",
   "    <td>abbated01</td>\n",
   "    <td>1877</td>\n",
   "    <td>4</td>\n",
   "    <td>15</td>\n",
   "    <td>USA</td>\n",
   "    <td>PA</td>\n",
   "    <td>Latrobe</td>\n",
   "    <td>1957</td>\n",
   "    <td>1</td>\n",
   "    <td>6</td>\n",
   "    <td>...</td>\n",
   "    <td>170.0</td>\n",
   "    <td>71</td>\n",
   "    <td>R</td>\n",
   "    <td>R</td>\n",
   "    <td>1897-09-04</td>\n",
   "    <td>1910-09-15</td>\n",
   "    <td>abbae101</td>\n",
   "    <td>abbated01</td>\n",
   "    <td>1877-04-15 00:00:00</td>\n",
   "    <td>1957-01-06</td>\n",
   " </tr>\n",
   " <tr>\n",
   "    <th>8</th>\n",
   "    <td>abbeybe01</td>\n",
   "    <td>1869</td>\n",
   "    <td>11</td>\n",
   "    <td>11</td>\n",
   "    <td>USA</td>\n",
   "    <td>VT</td>\n",
   "    <td>Essex</td>\n",
   "    <td>1962</td>\n",
   "    <td>6</td>\n",
   "    <td>11</td>\n",
   "    <td>...</td>\n",
   "    <td>175.0</td>\n",
   "    <td>71</td>\n",
   "    <td>R</td>\n",
   "    <td>R</td>\n",
   "    <td>1892-06-14</td>\n",
   "    <td>1896-09-23</td>\n",
   "    <td>abbeb101</td>\n",
   "    <td>abbeybe01</td>\n",
   "    <td>1869-11-11 00:00:00</td>\n",
   "    <td>1962-06-11</td>\n",
   " </tr>\n",
   " <tr>\n",
   "    <th>9</th>\n",
   "    <td>abbeych01</td>\n",
   "    <td>1866</td>\n",
   "    <td>10</td>\n",
   "    <td>14</td>\n",
   "    <td>USA</td>\n",
   "    <td>NE</td>\n",
   "    <td>Falls City</td>\n",
   "    <td>1926</td>\n",
   "    <td>4</td>\n",
   "    <td>27</td>\n",
   "    <td>...</td>\n",
   "    <td>169.0</td>\n",
   "    <td>68</td>\n",
   "    <td>L</td>\n",
   "    <td>L</td>\n",
   "    <td>1893-08-16</td>\n",
   "    <td>1897-08-19</td>\n",
   "    <td>abbec101</td>\n",
   "    <td>abbeych01</td>\n",
   "    <td>1866-10-14 00:00:00</td>\n",
   "    <td>1926-04-27</td>\n",
   " </tr>\n",
   " </tbody>\n",
   "</table>\n",
   "<p>10 rows × 26 columns</p>\n",
   "</div>"
   ],
   "text/plain": [
   " playerID birthYear birthMonth birthDay birthCountry birthState \\\n",
   "0 aardsda01    1981       12    27       USA       CO \n",
   "1 aaronha01    1934       2    5       USA       AL \n",
   "2 aaronto01    1939       8    5       USA       AL \n",
   "3 aasedo01    1954       9    8       USA       CA \n",
   "4 abadan01    1972       8    25       USA       FL \n",
   "5 abadfe01    1985       12    17       D.R. La Romana \n",
   "6 abadijo01    1850       11    4       USA       PA \n",
   "7 abbated01    1877       4    15       USA       PA \n",
   "8 abbeybe01    1869       11    11       USA       VT \n",
   "9 abbeych01    1866       10    14       USA       NE \n",
   "\n",
   "    birthCity deathYear deathMonth deathDay ... weight height bats \\\n",
   "0    Denver    None    None None ...    215.0 75 R \n",
   "1    Mobile    None    None None ...    180.0 72 R \n",
   "2    Mobile    1984       8    16 ...    190.0 75 R \n",
   "3    Orange    None    None None ...    190.0 75 R \n",
   "4 Palm Beach    None    None None ...    184.0 73 L \n",
   "5 La Romana    None    None None ...    220.0 73 L \n",
   "6 Philadelphia    1905       5    17 ...    192.0 72 R \n",
   "7    Latrobe    1957       1    6 ...    170.0 71 R \n",
   "8       Essex    1962       6    11 ...    175.0 71 R \n",
   "9 Falls City    1926       4    27 ...    169.0 68 L \n",
   "\n",
   " throws    debut finalGame retroID bbrefID       birthDate \\\n",
   "0    R 2004-04-06 2015-08-23 aardd001 aardsda01       1981-12-27 \n",
   "1    R 1954-04-13 1976-10-03 aaroh101 aaronha01       1934-02-05 \n",
   "2    R 1962-04-10 1971-09-26 aarot101 aaronto01       1939-08-05 \n",
   "3    R 1977-07-26 1990-10-03 aased001 aasedo01       1954-09-08 \n",
   "4    L 2001-09-10 2006-04-13 abada001 abadan01       1972-08-25 \n",
   "5    L 2010-07-28 2017-10-01 abadf001 abadfe01       1985-12-17 \n",
   "6    R 1875-04-26 1875-06-10 abadj101 abadijo01 1850-11-04 00:00:00 \n",
   "7    R 1897-09-04 1910-09-15 abbae101 abbated01 1877-04-15 00:00:00 \n",
   "8    R 1892-06-14 1896-09-23 abbeb101 abbeybe01 1869-11-11 00:00:00 \n",
   "9    L 1893-08-16 1897-08-19 abbec101 abbeych01 1866-10-14 00:00:00 \n",
   "\n",
   " deathDate \n",
   "0    NaT \n",
   "1    NaT \n",
   "2 1984-08-16 \n",
   "3    NaT \n",
   "4    NaT \n",
   "5    NaT \n",
   "6 1905-05-17 \n",
   "7 1957-01-06 \n",
   "8 1962-06-11 \n",
   "9 1926-04-27 \n",
   "\n",
   "[10 rows x 26 columns]"
   ]
},
"execution_count": 20,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# The head() function is similar to SQL LIMIT.\n",
"# There is also a tail() function to see the last set of rows.\n",
"#\n",
"people_df.head(10)"
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {},
"outputs": [
{
"data": {
   "text/html": [
   "<div>\n",
   "<style scoped>\n",
   " .dataframe tbody tr th:only-of-type {\n",
   "    vertical-align: middle;\n",
   " }\n",
   "\n",
   " .dataframe tbody tr th {\n",
   "    vertical-align: top;\n",
   " }\n",
   "\n",
   " .dataframe thead th {\n",
   "    text-align: right;\n",
   " }\n",
   "</style>\n",
   "<table border=\"1\" class=\"dataframe\">\n",
   " <thead>\n",
   " <tr style=\"text-align: right;\">\n",
   "    <th></th>\n",
   "    <th>playerID</th>\n",
   "    <th>yearID</th>\n",
   "    <th>stint</th>\n",
   "    <th>teamID</th>\n",
   "    <th>lgID</th>\n",
   "    <th>G</th>\n",
   "    <th>AB</th>\n",
   "    <th>R</th>\n",
   "    <th>H</th>\n",
   "    <th>2B</th>\n",
   "    <th>...</th>\n",
   "    <th>RBI</th>\n",
   "    <th>SB</th>\n",
   "    <th>CS</th>\n",
   "    <th>BB</th>\n",
   "    <th>SO</th>\n",
   "    <th>IBB</th>\n",
   "    <th>HBP</th>\n",
   "    <th>SH</th>\n",
   "    <th>SF</th>\n",
   "    <th>GIDP</th>\n",
   " </tr>\n",
   " </thead>\n",
   " <tbody>\n",
   " <tr>\n",
   "    <th>0</th>\n",
   "    <td>aardsda01</td>\n",
   "    <td>2015</td>\n",
   "    <td>1</td>\n",
   "    <td>ATL</td>\n",
   "    <td>NL</td>\n",
   "    <td>33</td>\n",
   "    <td>1</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>...</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>1</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   " </tr>\n",
   " <tr>\n",
   "    <th>1</th>\n",
   "    <td>aardsda01</td>\n",
   "    <td>2008</td>\n",
   "    <td>1</td>\n",
   "    <td>BOS</td>\n",
   "    <td>AL</td>\n",
   "    <td>47</td>\n",
   "    <td>1</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>...</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>1</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   " </tr>\n",
   " <tr>\n",
   "    <th>2</th>\n",
   "    <td>aardsda01</td>\n",
   "    <td>2007</td>\n",
   "    <td>1</td>\n",
   "    <td>CHA</td>\n",
   "    <td>AL</td>\n",
   "    <td>25</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>...</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   " </tr>\n",
   " <tr>\n",
   "    <th>3</th>\n",
   "    <td>aardsda01</td>\n",
   "    <td>2006</td>\n",
   "    <td>1</td>\n",
   "    <td>CHN</td>\n",
   "    <td>NL</td>\n",
   "    <td>45</td>\n",
   "    <td>2</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>...</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>1</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   " </tr>\n",
   " <tr>\n",
   "    <th>4</th>\n",
   "    <td>aardsda01</td>\n",
   "    <td>2012</td>\n",
   "    <td>1</td>\n",
   "    <td>NYA</td>\n",
   "    <td>AL</td>\n",
   "    <td>1</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>...</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   " </tr>\n",
   " <tr>\n",
   "    <th>5</th>\n",
   "    <td>aardsda01</td>\n",
   "    <td>2013</td>\n",
   "    <td>1</td>\n",
   "    <td>NYN</td>\n",
   "    <td>NL</td>\n",
   "    <td>43</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>...</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   " </tr>\n",
   " <tr>\n",
   "    <th>6</th>\n",
   "    <td>aardsda01</td>\n",
   "    <td>2009</td>\n",
   "    <td>1</td>\n",
   "    <td>SEA</td>\n",
   "    <td>AL</td>\n",
   "    <td>73</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>...</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   " </tr>\n",
   " <tr>\n",
   "    <th>7</th>\n",
   "    <td>aardsda01</td>\n",
   "    <td>2010</td>\n",
   "    <td>1</td>\n",
   "    <td>SEA</td>\n",
   "    <td>AL</td>\n",
   "    <td>53</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>...</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   " </tr>\n",
   " <tr>\n",
   "    <th>8</th>\n",
   "    <td>aardsda01</td>\n",
   "    <td>2004</td>\n",
   "    <td>1</td>\n",
   "    <td>SFN</td>\n",
   "    <td>NL</td>\n",
   "    <td>11</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>0</td>\n",
   "    <td>...</td>\n",
   "    <td>0</td>\n",

You will be required to submit two deliverables: 1. A color-coded...