05-01_NYCFlights13.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights = pd.read_csv('nycflights.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "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>day</th>\n",
       "      <th>dep_time</th>\n",
       "      <th>sched_dep_time</th>\n",
       "      <th>dep_delay</th>\n",
       "      <th>arr_time</th>\n",
       "      <th>sched_arr_time</th>\n",
       "      <th>arr_delay</th>\n",
       "      <th>carrier</th>\n",
       "      <th>flight</th>\n",
       "      <th>tailnum</th>\n",
       "      <th>origin</th>\n",
       "      <th>dest</th>\n",
       "      <th>air_time</th>\n",
       "      <th>distance</th>\n",
       "      <th>hour</th>\n",
       "      <th>minute</th>\n",
       "      <th>time_hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>517.0</td>\n",
       "      <td>515</td>\n",
       "      <td>2.0</td>\n",
       "      <td>830.0</td>\n",
       "      <td>819</td>\n",
       "      <td>11.0</td>\n",
       "      <td>UA</td>\n",
       "      <td>1545</td>\n",
       "      <td>N14228</td>\n",
       "      <td>EWR</td>\n",
       "      <td>IAH</td>\n",
       "      <td>227.0</td>\n",
       "      <td>1400</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>533.0</td>\n",
       "      <td>529</td>\n",
       "      <td>4.0</td>\n",
       "      <td>850.0</td>\n",
       "      <td>830</td>\n",
       "      <td>20.0</td>\n",
       "      <td>UA</td>\n",
       "      <td>1714</td>\n",
       "      <td>N24211</td>\n",
       "      <td>LGA</td>\n",
       "      <td>IAH</td>\n",
       "      <td>227.0</td>\n",
       "      <td>1416</td>\n",
       "      <td>5</td>\n",
       "      <td>29</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>542.0</td>\n",
       "      <td>540</td>\n",
       "      <td>2.0</td>\n",
       "      <td>923.0</td>\n",
       "      <td>850</td>\n",
       "      <td>33.0</td>\n",
       "      <td>AA</td>\n",
       "      <td>1141</td>\n",
       "      <td>N619AA</td>\n",
       "      <td>JFK</td>\n",
       "      <td>MIA</td>\n",
       "      <td>160.0</td>\n",
       "      <td>1089</td>\n",
       "      <td>5</td>\n",
       "      <td>40</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>544.0</td>\n",
       "      <td>545</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1004.0</td>\n",
       "      <td>1022</td>\n",
       "      <td>-18.0</td>\n",
       "      <td>B6</td>\n",
       "      <td>725</td>\n",
       "      <td>N804JB</td>\n",
       "      <td>JFK</td>\n",
       "      <td>BQN</td>\n",
       "      <td>183.0</td>\n",
       "      <td>1576</td>\n",
       "      <td>5</td>\n",
       "      <td>45</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>554.0</td>\n",
       "      <td>600</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>812.0</td>\n",
       "      <td>837</td>\n",
       "      <td>-25.0</td>\n",
       "      <td>DL</td>\n",
       "      <td>461</td>\n",
       "      <td>N668DN</td>\n",
       "      <td>LGA</td>\n",
       "      <td>ATL</td>\n",
       "      <td>116.0</td>\n",
       "      <td>762</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>1/1/2013 6:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  month  day  dep_time  sched_dep_time  dep_delay  arr_time  \\\n",
       "0  2013      1    1     517.0             515        2.0     830.0   \n",
       "1  2013      1    1     533.0             529        4.0     850.0   \n",
       "2  2013      1    1     542.0             540        2.0     923.0   \n",
       "3  2013      1    1     544.0             545       -1.0    1004.0   \n",
       "4  2013      1    1     554.0             600       -6.0     812.0   \n",
       "\n",
       "   sched_arr_time  arr_delay carrier  flight tailnum origin dest  air_time  \\\n",
       "0             819       11.0      UA    1545  N14228    EWR  IAH     227.0   \n",
       "1             830       20.0      UA    1714  N24211    LGA  IAH     227.0   \n",
       "2             850       33.0      AA    1141  N619AA    JFK  MIA     160.0   \n",
       "3            1022      -18.0      B6     725  N804JB    JFK  BQN     183.0   \n",
       "4             837      -25.0      DL     461  N668DN    LGA  ATL     116.0   \n",
       "\n",
       "   distance  hour  minute      time_hour  \n",
       "0      1400     5      15  1/1/2013 5:00  \n",
       "1      1416     5      29  1/1/2013 5:00  \n",
       "2      1089     5      40  1/1/2013 5:00  \n",
       "3      1576     5      45  1/1/2013 5:00  \n",
       "4       762     6       0  1/1/2013 6:00  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 336776 entries, 0 to 336775\n",
      "Data columns (total 19 columns):\n",
      "year              336776 non-null int64\n",
      "month             336776 non-null int64\n",
      "day               336776 non-null int64\n",
      "dep_time          328521 non-null float64\n",
      "sched_dep_time    336776 non-null int64\n",
      "dep_delay         328521 non-null float64\n",
      "arr_time          328063 non-null float64\n",
      "sched_arr_time    336776 non-null int64\n",
      "arr_delay         327346 non-null float64\n",
      "carrier           336776 non-null object\n",
      "flight            336776 non-null int64\n",
      "tailnum           334264 non-null object\n",
      "origin            336776 non-null object\n",
      "dest              336776 non-null object\n",
      "air_time          327346 non-null float64\n",
      "distance          336776 non-null int64\n",
      "hour              336776 non-null int64\n",
      "minute            336776 non-null int64\n",
      "time_hour         336776 non-null object\n",
      "dtypes: float64(5), int64(9), object(5)\n",
      "memory usage: 48.8+ MB\n"
     ]
    }
   ],
   "source": [
    "flights.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We load the data, take a look at the head, and the descriptions.\n",
    "\n",
    "It seems that dep_time, dep_delay, arr_time, arr_delay, and air_time were all converted into floats. These are supposed to represent times where '1455' represent the time '14:55'. There should not be any decimal values. The fact that decimals are appearing means that something happened in the import that made python believe they were floating point numbers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>dep_time</th>\n",
       "      <th>sched_dep_time</th>\n",
       "      <th>dep_delay</th>\n",
       "      <th>arr_time</th>\n",
       "      <th>sched_arr_time</th>\n",
       "      <th>arr_delay</th>\n",
       "      <th>carrier</th>\n",
       "      <th>flight</th>\n",
       "      <th>tailnum</th>\n",
       "      <th>origin</th>\n",
       "      <th>dest</th>\n",
       "      <th>air_time</th>\n",
       "      <th>distance</th>\n",
       "      <th>hour</th>\n",
       "      <th>minute</th>\n",
       "      <th>time_hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>336771</th>\n",
       "      <td>2013</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1455</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1634</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9E</td>\n",
       "      <td>3393</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JFK</td>\n",
       "      <td>DCA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>213</td>\n",
       "      <td>14</td>\n",
       "      <td>55</td>\n",
       "      <td>9/30/2013 14:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336772</th>\n",
       "      <td>2013</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2312</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9E</td>\n",
       "      <td>3525</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LGA</td>\n",
       "      <td>SYR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>198</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>9/30/2013 22:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336773</th>\n",
       "      <td>2013</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1210</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1330</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MQ</td>\n",
       "      <td>3461</td>\n",
       "      <td>N535MQ</td>\n",
       "      <td>LGA</td>\n",
       "      <td>BNA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>764</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>9/30/2013 12:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336774</th>\n",
       "      <td>2013</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1159</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1344</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MQ</td>\n",
       "      <td>3572</td>\n",
       "      <td>N511MQ</td>\n",
       "      <td>LGA</td>\n",
       "      <td>CLE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>419</td>\n",
       "      <td>11</td>\n",
       "      <td>59</td>\n",
       "      <td>9/30/2013 11:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336775</th>\n",
       "      <td>2013</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>840</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MQ</td>\n",
       "      <td>3531</td>\n",
       "      <td>N839MQ</td>\n",
       "      <td>LGA</td>\n",
       "      <td>RDU</td>\n",
       "      <td>NaN</td>\n",
       "      <td>431</td>\n",
       "      <td>8</td>\n",
       "      <td>40</td>\n",
       "      <td>9/30/2013 8:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        year  month  day  dep_time  sched_dep_time  dep_delay  arr_time  \\\n",
       "336771  2013      9   30       NaN            1455        NaN       NaN   \n",
       "336772  2013      9   30       NaN            2200        NaN       NaN   \n",
       "336773  2013      9   30       NaN            1210        NaN       NaN   \n",
       "336774  2013      9   30       NaN            1159        NaN       NaN   \n",
       "336775  2013      9   30       NaN             840        NaN       NaN   \n",
       "\n",
       "        sched_arr_time  arr_delay carrier  flight tailnum origin dest  \\\n",
       "336771            1634        NaN      9E    3393     NaN    JFK  DCA   \n",
       "336772            2312        NaN      9E    3525     NaN    LGA  SYR   \n",
       "336773            1330        NaN      MQ    3461  N535MQ    LGA  BNA   \n",
       "336774            1344        NaN      MQ    3572  N511MQ    LGA  CLE   \n",
       "336775            1020        NaN      MQ    3531  N839MQ    LGA  RDU   \n",
       "\n",
       "        air_time  distance  hour  minute        time_hour  \n",
       "336771       NaN       213    14      55  9/30/2013 14:00  \n",
       "336772       NaN       198    22       0  9/30/2013 22:00  \n",
       "336773       NaN       764    12      10  9/30/2013 12:00  \n",
       "336774       NaN       419    11      59  9/30/2013 11:00  \n",
       "336775       NaN       431     8      40   9/30/2013 8:00  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An inspection of the tail shows that there are missing values in dep_time and the other columns. Because python represents missing numeric values with NaN, they were convered to floating point numbers.\n",
    "\n",
    "We will reload the flights csv file, this time specifying the offending columns as 'object' type. (It's a little weird, but we do not use str type because str uses NumPy's str type which is different from regular Python strings. To use Python strings, Pandas uses 'object' type)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading the data again and doing a little bit of clean up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights = pd.read_csv('nycflights.csv', dtype = {'dep_time' : object, \n",
    "                                                 'dep_delay': object, \n",
    "                                                 'arr_time' : object, \n",
    "                                                 'arr_delay': object, \n",
    "                                                 'air_time' : object})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>day</th>\n",
       "      <th>dep_time</th>\n",
       "      <th>sched_dep_time</th>\n",
       "      <th>dep_delay</th>\n",
       "      <th>arr_time</th>\n",
       "      <th>sched_arr_time</th>\n",
       "      <th>arr_delay</th>\n",
       "      <th>carrier</th>\n",
       "      <th>flight</th>\n",
       "      <th>tailnum</th>\n",
       "      <th>origin</th>\n",
       "      <th>dest</th>\n",
       "      <th>air_time</th>\n",
       "      <th>distance</th>\n",
       "      <th>hour</th>\n",
       "      <th>minute</th>\n",
       "      <th>time_hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>517</td>\n",
       "      <td>515</td>\n",
       "      <td>2</td>\n",
       "      <td>830</td>\n",
       "      <td>819</td>\n",
       "      <td>11</td>\n",
       "      <td>UA</td>\n",
       "      <td>1545</td>\n",
       "      <td>N14228</td>\n",
       "      <td>EWR</td>\n",
       "      <td>IAH</td>\n",
       "      <td>227</td>\n",
       "      <td>1400</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>533</td>\n",
       "      <td>529</td>\n",
       "      <td>4</td>\n",
       "      <td>850</td>\n",
       "      <td>830</td>\n",
       "      <td>20</td>\n",
       "      <td>UA</td>\n",
       "      <td>1714</td>\n",
       "      <td>N24211</td>\n",
       "      <td>LGA</td>\n",
       "      <td>IAH</td>\n",
       "      <td>227</td>\n",
       "      <td>1416</td>\n",
       "      <td>5</td>\n",
       "      <td>29</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>542</td>\n",
       "      <td>540</td>\n",
       "      <td>2</td>\n",
       "      <td>923</td>\n",
       "      <td>850</td>\n",
       "      <td>33</td>\n",
       "      <td>AA</td>\n",
       "      <td>1141</td>\n",
       "      <td>N619AA</td>\n",
       "      <td>JFK</td>\n",
       "      <td>MIA</td>\n",
       "      <td>160</td>\n",
       "      <td>1089</td>\n",
       "      <td>5</td>\n",
       "      <td>40</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>544</td>\n",
       "      <td>545</td>\n",
       "      <td>-1</td>\n",
       "      <td>1004</td>\n",
       "      <td>1022</td>\n",
       "      <td>-18</td>\n",
       "      <td>B6</td>\n",
       "      <td>725</td>\n",
       "      <td>N804JB</td>\n",
       "      <td>JFK</td>\n",
       "      <td>BQN</td>\n",
       "      <td>183</td>\n",
       "      <td>1576</td>\n",
       "      <td>5</td>\n",
       "      <td>45</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>554</td>\n",
       "      <td>600</td>\n",
       "      <td>-6</td>\n",
       "      <td>812</td>\n",
       "      <td>837</td>\n",
       "      <td>-25</td>\n",
       "      <td>DL</td>\n",
       "      <td>461</td>\n",
       "      <td>N668DN</td>\n",
       "      <td>LGA</td>\n",
       "      <td>ATL</td>\n",
       "      <td>116</td>\n",
       "      <td>762</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>1/1/2013 6:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  month  day dep_time  sched_dep_time dep_delay arr_time  \\\n",
       "0  2013      1    1      517             515         2      830   \n",
       "1  2013      1    1      533             529         4      850   \n",
       "2  2013      1    1      542             540         2      923   \n",
       "3  2013      1    1      544             545        -1     1004   \n",
       "4  2013      1    1      554             600        -6      812   \n",
       "\n",
       "   sched_arr_time arr_delay carrier  flight tailnum origin dest air_time  \\\n",
       "0             819        11      UA    1545  N14228    EWR  IAH      227   \n",
       "1             830        20      UA    1714  N24211    LGA  IAH      227   \n",
       "2             850        33      AA    1141  N619AA    JFK  MIA      160   \n",
       "3            1022       -18      B6     725  N804JB    JFK  BQN      183   \n",
       "4             837       -25      DL     461  N668DN    LGA  ATL      116   \n",
       "\n",
       "   distance  hour  minute      time_hour  \n",
       "0      1400     5      15  1/1/2013 5:00  \n",
       "1      1416     5      29  1/1/2013 5:00  \n",
       "2      1089     5      40  1/1/2013 5:00  \n",
       "3      1576     5      45  1/1/2013 5:00  \n",
       "4       762     6       0  1/1/2013 6:00  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.head() # the dep_time column no longer has any decimals in it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>dep_time</th>\n",
       "      <th>sched_dep_time</th>\n",
       "      <th>dep_delay</th>\n",
       "      <th>arr_time</th>\n",
       "      <th>sched_arr_time</th>\n",
       "      <th>arr_delay</th>\n",
       "      <th>carrier</th>\n",
       "      <th>flight</th>\n",
       "      <th>tailnum</th>\n",
       "      <th>origin</th>\n",
       "      <th>dest</th>\n",
       "      <th>air_time</th>\n",
       "      <th>distance</th>\n",
       "      <th>hour</th>\n",
       "      <th>minute</th>\n",
       "      <th>time_hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>336766</th>\n",
       "      <td>2013</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>2240</td>\n",
       "      <td>2250</td>\n",
       "      <td>-10</td>\n",
       "      <td>2347</td>\n",
       "      <td>7</td>\n",
       "      <td>-20</td>\n",
       "      <td>B6</td>\n",
       "      <td>2002</td>\n",
       "      <td>N281JB</td>\n",
       "      <td>JFK</td>\n",
       "      <td>BUF</td>\n",
       "      <td>52</td>\n",
       "      <td>301</td>\n",
       "      <td>22</td>\n",
       "      <td>50</td>\n",
       "      <td>9/30/2013 22:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336767</th>\n",
       "      <td>2013</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>2241</td>\n",
       "      <td>2246</td>\n",
       "      <td>-5</td>\n",
       "      <td>2345</td>\n",
       "      <td>1</td>\n",
       "      <td>-16</td>\n",
       "      <td>B6</td>\n",
       "      <td>486</td>\n",
       "      <td>N346JB</td>\n",
       "      <td>JFK</td>\n",
       "      <td>ROC</td>\n",
       "      <td>47</td>\n",
       "      <td>264</td>\n",
       "      <td>22</td>\n",
       "      <td>46</td>\n",
       "      <td>9/30/2013 22:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336768</th>\n",
       "      <td>2013</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>2307</td>\n",
       "      <td>2255</td>\n",
       "      <td>12</td>\n",
       "      <td>2359</td>\n",
       "      <td>2358</td>\n",
       "      <td>1</td>\n",
       "      <td>B6</td>\n",
       "      <td>718</td>\n",
       "      <td>N565JB</td>\n",
       "      <td>JFK</td>\n",
       "      <td>BOS</td>\n",
       "      <td>33</td>\n",
       "      <td>187</td>\n",
       "      <td>22</td>\n",
       "      <td>55</td>\n",
       "      <td>9/30/2013 22:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336769</th>\n",
       "      <td>2013</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>2349</td>\n",
       "      <td>2359</td>\n",
       "      <td>-10</td>\n",
       "      <td>325</td>\n",
       "      <td>350</td>\n",
       "      <td>-25</td>\n",
       "      <td>B6</td>\n",
       "      <td>745</td>\n",
       "      <td>N516JB</td>\n",
       "      <td>JFK</td>\n",
       "      <td>PSE</td>\n",
       "      <td>196</td>\n",
       "      <td>1617</td>\n",
       "      <td>23</td>\n",
       "      <td>59</td>\n",
       "      <td>9/30/2013 23:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336770</th>\n",
       "      <td>2013</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1842</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EV</td>\n",
       "      <td>5274</td>\n",
       "      <td>N740EV</td>\n",
       "      <td>LGA</td>\n",
       "      <td>BNA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>764</td>\n",
       "      <td>18</td>\n",
       "      <td>42</td>\n",
       "      <td>9/30/2013 18:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336771</th>\n",
       "      <td>2013</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1455</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1634</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9E</td>\n",
       "      <td>3393</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JFK</td>\n",
       "      <td>DCA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>213</td>\n",
       "      <td>14</td>\n",
       "      <td>55</td>\n",
       "      <td>9/30/2013 14:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336772</th>\n",
       "      <td>2013</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2312</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9E</td>\n",
       "      <td>3525</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LGA</td>\n",
       "      <td>SYR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>198</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>9/30/2013 22:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336773</th>\n",
       "      <td>2013</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1210</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1330</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MQ</td>\n",
       "      <td>3461</td>\n",
       "      <td>N535MQ</td>\n",
       "      <td>LGA</td>\n",
       "      <td>BNA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>764</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>9/30/2013 12:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336774</th>\n",
       "      <td>2013</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1159</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1344</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MQ</td>\n",
       "      <td>3572</td>\n",
       "      <td>N511MQ</td>\n",
       "      <td>LGA</td>\n",
       "      <td>CLE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>419</td>\n",
       "      <td>11</td>\n",
       "      <td>59</td>\n",
       "      <td>9/30/2013 11:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336775</th>\n",
       "      <td>2013</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>840</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MQ</td>\n",
       "      <td>3531</td>\n",
       "      <td>N839MQ</td>\n",
       "      <td>LGA</td>\n",
       "      <td>RDU</td>\n",
       "      <td>NaN</td>\n",
       "      <td>431</td>\n",
       "      <td>8</td>\n",
       "      <td>40</td>\n",
       "      <td>9/30/2013 8:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        year  month  day dep_time  sched_dep_time dep_delay arr_time  \\\n",
       "336766  2013      9   30     2240            2250       -10     2347   \n",
       "336767  2013      9   30     2241            2246        -5     2345   \n",
       "336768  2013      9   30     2307            2255        12     2359   \n",
       "336769  2013      9   30     2349            2359       -10      325   \n",
       "336770  2013      9   30      NaN            1842       NaN      NaN   \n",
       "336771  2013      9   30      NaN            1455       NaN      NaN   \n",
       "336772  2013      9   30      NaN            2200       NaN      NaN   \n",
       "336773  2013      9   30      NaN            1210       NaN      NaN   \n",
       "336774  2013      9   30      NaN            1159       NaN      NaN   \n",
       "336775  2013      9   30      NaN             840       NaN      NaN   \n",
       "\n",
       "        sched_arr_time arr_delay carrier  flight tailnum origin dest air_time  \\\n",
       "336766               7       -20      B6    2002  N281JB    JFK  BUF       52   \n",
       "336767               1       -16      B6     486  N346JB    JFK  ROC       47   \n",
       "336768            2358         1      B6     718  N565JB    JFK  BOS       33   \n",
       "336769             350       -25      B6     745  N516JB    JFK  PSE      196   \n",
       "336770            2019       NaN      EV    5274  N740EV    LGA  BNA      NaN   \n",
       "336771            1634       NaN      9E    3393     NaN    JFK  DCA      NaN   \n",
       "336772            2312       NaN      9E    3525     NaN    LGA  SYR      NaN   \n",
       "336773            1330       NaN      MQ    3461  N535MQ    LGA  BNA      NaN   \n",
       "336774            1344       NaN      MQ    3572  N511MQ    LGA  CLE      NaN   \n",
       "336775            1020       NaN      MQ    3531  N839MQ    LGA  RDU      NaN   \n",
       "\n",
       "        distance  hour  minute        time_hour  \n",
       "336766       301    22      50  9/30/2013 22:00  \n",
       "336767       264    22      46  9/30/2013 22:00  \n",
       "336768       187    22      55  9/30/2013 22:00  \n",
       "336769      1617    23      59  9/30/2013 23:00  \n",
       "336770       764    18      42  9/30/2013 18:00  \n",
       "336771       213    14      55  9/30/2013 14:00  \n",
       "336772       198    22       0  9/30/2013 22:00  \n",
       "336773       764    12      10  9/30/2013 12:00  \n",
       "336774       419    11      59  9/30/2013 11:00  \n",
       "336775       431     8      40   9/30/2013 8:00  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.tail(10) # the missing values are still preserved"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The next thing I notice is that the dates are split across three columns: year, month, day.\n",
    "\n",
    "I will use Python's date functions to combine the three values into a single date value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights['date'] = pd.to_datetime({'year':flights.year, 'month':flights.month, 'day':flights.day})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>dep_time</th>\n",
       "      <th>sched_dep_time</th>\n",
       "      <th>dep_delay</th>\n",
       "      <th>arr_time</th>\n",
       "      <th>sched_arr_time</th>\n",
       "      <th>arr_delay</th>\n",
       "      <th>carrier</th>\n",
       "      <th>flight</th>\n",
       "      <th>tailnum</th>\n",
       "      <th>origin</th>\n",
       "      <th>dest</th>\n",
       "      <th>air_time</th>\n",
       "      <th>distance</th>\n",
       "      <th>hour</th>\n",
       "      <th>minute</th>\n",
       "      <th>time_hour</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>517</td>\n",
       "      <td>515</td>\n",
       "      <td>2</td>\n",
       "      <td>830</td>\n",
       "      <td>819</td>\n",
       "      <td>11</td>\n",
       "      <td>UA</td>\n",
       "      <td>1545</td>\n",
       "      <td>N14228</td>\n",
       "      <td>EWR</td>\n",
       "      <td>IAH</td>\n",
       "      <td>227</td>\n",
       "      <td>1400</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "      <td>2013-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>533</td>\n",
       "      <td>529</td>\n",
       "      <td>4</td>\n",
       "      <td>850</td>\n",
       "      <td>830</td>\n",
       "      <td>20</td>\n",
       "      <td>UA</td>\n",
       "      <td>1714</td>\n",
       "      <td>N24211</td>\n",
       "      <td>LGA</td>\n",
       "      <td>IAH</td>\n",
       "      <td>227</td>\n",
       "      <td>1416</td>\n",
       "      <td>5</td>\n",
       "      <td>29</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "      <td>2013-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>542</td>\n",
       "      <td>540</td>\n",
       "      <td>2</td>\n",
       "      <td>923</td>\n",
       "      <td>850</td>\n",
       "      <td>33</td>\n",
       "      <td>AA</td>\n",
       "      <td>1141</td>\n",
       "      <td>N619AA</td>\n",
       "      <td>JFK</td>\n",
       "      <td>MIA</td>\n",
       "      <td>160</td>\n",
       "      <td>1089</td>\n",
       "      <td>5</td>\n",
       "      <td>40</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "      <td>2013-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>544</td>\n",
       "      <td>545</td>\n",
       "      <td>-1</td>\n",
       "      <td>1004</td>\n",
       "      <td>1022</td>\n",
       "      <td>-18</td>\n",
       "      <td>B6</td>\n",
       "      <td>725</td>\n",
       "      <td>N804JB</td>\n",
       "      <td>JFK</td>\n",
       "      <td>BQN</td>\n",
       "      <td>183</td>\n",
       "      <td>1576</td>\n",
       "      <td>5</td>\n",
       "      <td>45</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "      <td>2013-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>554</td>\n",
       "      <td>600</td>\n",
       "      <td>-6</td>\n",
       "      <td>812</td>\n",
       "      <td>837</td>\n",
       "      <td>-25</td>\n",
       "      <td>DL</td>\n",
       "      <td>461</td>\n",
       "      <td>N668DN</td>\n",
       "      <td>LGA</td>\n",
       "      <td>ATL</td>\n",
       "      <td>116</td>\n",
       "      <td>762</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>1/1/2013 6:00</td>\n",
       "      <td>2013-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  month  day dep_time  sched_dep_time dep_delay arr_time  \\\n",
       "0  2013      1    1      517             515         2      830   \n",
       "1  2013      1    1      533             529         4      850   \n",
       "2  2013      1    1      542             540         2      923   \n",
       "3  2013      1    1      544             545        -1     1004   \n",
       "4  2013      1    1      554             600        -6      812   \n",
       "\n",
       "   sched_arr_time arr_delay carrier  flight tailnum origin dest air_time  \\\n",
       "0             819        11      UA    1545  N14228    EWR  IAH      227   \n",
       "1             830        20      UA    1714  N24211    LGA  IAH      227   \n",
       "2             850        33      AA    1141  N619AA    JFK  MIA      160   \n",
       "3            1022       -18      B6     725  N804JB    JFK  BQN      183   \n",
       "4             837       -25      DL     461  N668DN    LGA  ATL      116   \n",
       "\n",
       "   distance  hour  minute      time_hour       date  \n",
       "0      1400     5      15  1/1/2013 5:00 2013-01-01  \n",
       "1      1416     5      29  1/1/2013 5:00 2013-01-01  \n",
       "2      1089     5      40  1/1/2013 5:00 2013-01-01  \n",
       "3      1576     5      45  1/1/2013 5:00 2013-01-01  \n",
       "4       762     6       0  1/1/2013 6:00 2013-01-01  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.head() # we have now appeneded a new column that shows the date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the times expressed as they are, we can't calculate the real time difference between columns. The time between '600' and '545' is really only 15 minutes, but the math will make the wrong calculation of 55. Thus, we need to split our text into hour and minute and make them into date-time values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    5\n",
      "1    5\n",
      "2    5\n",
      "3    5\n",
      "4    5\n",
      "Name: dep_time, dtype: object\n",
      "0    17\n",
      "1    33\n",
      "2    42\n",
      "3    44\n",
      "4    54\n",
      "Name: dep_time, dtype: object\n"
     ]
    }
   ],
   "source": [
    "# we filter the dep_time to just non_null values\n",
    "# I will extract the minute values by treating the series as a string and pulling the last two characters\n",
    "# we get the hour by taking everything but the last two characters\n",
    "dep_min  = flights.dep_time[ flights.dep_time.notnull() ].astype(str).str[-2:  ]\n",
    "dep_hour = flights.dep_time[ flights.dep_time.notnull() ].astype(str).str[  :-2]\n",
    "print(dep_hour.head())\n",
    "print(dep_min.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "dep_date_time = pd.to_datetime({'year':flights.year, 'month':flights.month, 'day':flights.day, \n",
    "                                'hour': dep_hour, 'minute': dep_min})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0   2013-01-01 05:17:00\n",
      "1   2013-01-01 05:33:00\n",
      "2   2013-01-01 05:42:00\n",
      "3   2013-01-01 05:44:00\n",
      "4   2013-01-01 05:54:00\n",
      "5   2013-01-01 05:54:00\n",
      "6   2013-01-01 05:55:00\n",
      "7   2013-01-01 05:57:00\n",
      "8   2013-01-01 05:57:00\n",
      "9   2013-01-01 05:58:00\n",
      "dtype: datetime64[ns]\n",
      "336771   NaT\n",
      "336772   NaT\n",
      "336773   NaT\n",
      "336774   NaT\n",
      "336775   NaT\n",
      "dtype: datetime64[ns]\n"
     ]
    }
   ],
   "source": [
    "print(dep_date_time.head(10))\n",
    "print(dep_date_time.tail())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I'd like to do the same kind of conversion for all of the other time columns. \n",
    "\n",
    "Instead of copying and pasting the code and modifying it for each column, I'm going to build a function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_time(column_name):\n",
    "    the_min = flights[column_name][ flights[column_name].notnull() ].astype(str).str[-2:  ]\n",
    "    the_hr  = flights[column_name][ flights[column_name].notnull() ].astype(str).str[  :-2]\n",
    "    date_time = pd.to_datetime({'year':flights.year, 'month':flights.month, 'day':flights.day, \n",
    "                                'hour': the_hr, 'minute': the_min})\n",
    "    # the magic of indexes allow us to combine flights.year which has all the rows\n",
    "    # and the_hr which only exists for rows where dep_time is not NaN\n",
    "    return date_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# let's test out the function\n",
    "dep_date_time = convert_time('dep_time')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0   2013-01-01 05:17:00\n",
      "1   2013-01-01 05:33:00\n",
      "2   2013-01-01 05:42:00\n",
      "3   2013-01-01 05:44:00\n",
      "4   2013-01-01 05:54:00\n",
      "5   2013-01-01 05:54:00\n",
      "6   2013-01-01 05:55:00\n",
      "7   2013-01-01 05:57:00\n",
      "8   2013-01-01 05:57:00\n",
      "9   2013-01-01 05:58:00\n",
      "dtype: datetime64[ns]\n",
      "336766   2013-09-30 22:40:00\n",
      "336767   2013-09-30 22:41:00\n",
      "336768   2013-09-30 23:07:00\n",
      "336769   2013-09-30 23:49:00\n",
      "336770                   NaT\n",
      "336771                   NaT\n",
      "336772                   NaT\n",
      "336773                   NaT\n",
      "336774                   NaT\n",
      "336775                   NaT\n",
      "dtype: datetime64[ns]\n"
     ]
    }
   ],
   "source": [
    "print(dep_date_time.head(10))\n",
    "print(dep_date_time.tail(10))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# seems to work!\n",
    "# let's go ahead and use it:\n",
    "# dep_date_time       = convert_time('dep_time')\n",
    "sched_dep_date_time = convert_time('sched_dep_time')\n",
    "arr_date_time       = convert_time('arr_time')\n",
    "sched_arr_date_time = convert_time('sched_arr_time')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "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>arr_date_time</th>\n",
       "      <th>dep_date_time</th>\n",
       "      <th>sched_arr_date_time</th>\n",
       "      <th>sched_dep_date_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-01-01 08:30:00</td>\n",
       "      <td>2013-01-01 05:17:00</td>\n",
       "      <td>2013-01-01 08:19:00</td>\n",
       "      <td>2013-01-01 05:15:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-01-01 08:50:00</td>\n",
       "      <td>2013-01-01 05:33:00</td>\n",
       "      <td>2013-01-01 08:30:00</td>\n",
       "      <td>2013-01-01 05:29:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-01-01 09:23:00</td>\n",
       "      <td>2013-01-01 05:42:00</td>\n",
       "      <td>2013-01-01 08:50:00</td>\n",
       "      <td>2013-01-01 05:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-01-01 10:04:00</td>\n",
       "      <td>2013-01-01 05:44:00</td>\n",
       "      <td>2013-01-01 10:22:00</td>\n",
       "      <td>2013-01-01 05:45:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-01-01 08:12:00</td>\n",
       "      <td>2013-01-01 05:54:00</td>\n",
       "      <td>2013-01-01 08:37:00</td>\n",
       "      <td>2013-01-01 06:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        arr_date_time       dep_date_time sched_arr_date_time  \\\n",
       "0 2013-01-01 08:30:00 2013-01-01 05:17:00 2013-01-01 08:19:00   \n",
       "1 2013-01-01 08:50:00 2013-01-01 05:33:00 2013-01-01 08:30:00   \n",
       "2 2013-01-01 09:23:00 2013-01-01 05:42:00 2013-01-01 08:50:00   \n",
       "3 2013-01-01 10:04:00 2013-01-01 05:44:00 2013-01-01 10:22:00   \n",
       "4 2013-01-01 08:12:00 2013-01-01 05:54:00 2013-01-01 08:37:00   \n",
       "\n",
       "  sched_dep_date_time  \n",
       "0 2013-01-01 05:15:00  \n",
       "1 2013-01-01 05:29:00  \n",
       "2 2013-01-01 05:40:00  \n",
       "3 2013-01-01 05:45:00  \n",
       "4 2013-01-01 06:00:00  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pd.concat creates a new dataframe by joining several pandas objects together\n",
    "datetimes = pd.concat({'dep_date_time': dep_date_time,\n",
    "           'sched_dep_date_time': sched_dep_date_time, \n",
    "           'arr_date_time':arr_date_time, \n",
    "           'sched_arr_date_time':sched_arr_date_time}, axis = 1)\n",
    "datetimes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights = flights.join(datetimes)  # we use the .join() function"
   ]
  },
  {
   "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>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>dep_time</th>\n",
       "      <th>sched_dep_time</th>\n",
       "      <th>dep_delay</th>\n",
       "      <th>arr_time</th>\n",
       "      <th>sched_arr_time</th>\n",
       "      <th>arr_delay</th>\n",
       "      <th>carrier</th>\n",
       "      <th>...</th>\n",
       "      <th>air_time</th>\n",
       "      <th>distance</th>\n",
       "      <th>hour</th>\n",
       "      <th>minute</th>\n",
       "      <th>time_hour</th>\n",
       "      <th>date</th>\n",
       "      <th>arr_date_time</th>\n",
       "      <th>dep_date_time</th>\n",
       "      <th>sched_arr_date_time</th>\n",
       "      <th>sched_dep_date_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>517</td>\n",
       "      <td>515</td>\n",
       "      <td>2</td>\n",
       "      <td>830</td>\n",
       "      <td>819</td>\n",
       "      <td>11</td>\n",
       "      <td>UA</td>\n",
       "      <td>...</td>\n",
       "      <td>227</td>\n",
       "      <td>1400</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>2013-01-01 08:30:00</td>\n",
       "      <td>2013-01-01 05:17:00</td>\n",
       "      <td>2013-01-01 08:19:00</td>\n",
       "      <td>2013-01-01 05:15:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>533</td>\n",
       "      <td>529</td>\n",
       "      <td>4</td>\n",
       "      <td>850</td>\n",
       "      <td>830</td>\n",
       "      <td>20</td>\n",
       "      <td>UA</td>\n",
       "      <td>...</td>\n",
       "      <td>227</td>\n",
       "      <td>1416</td>\n",
       "      <td>5</td>\n",
       "      <td>29</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>2013-01-01 08:50:00</td>\n",
       "      <td>2013-01-01 05:33:00</td>\n",
       "      <td>2013-01-01 08:30:00</td>\n",
       "      <td>2013-01-01 05:29:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>542</td>\n",
       "      <td>540</td>\n",
       "      <td>2</td>\n",
       "      <td>923</td>\n",
       "      <td>850</td>\n",
       "      <td>33</td>\n",
       "      <td>AA</td>\n",
       "      <td>...</td>\n",
       "      <td>160</td>\n",
       "      <td>1089</td>\n",
       "      <td>5</td>\n",
       "      <td>40</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>2013-01-01 09:23:00</td>\n",
       "      <td>2013-01-01 05:42:00</td>\n",
       "      <td>2013-01-01 08:50:00</td>\n",
       "      <td>2013-01-01 05:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>544</td>\n",
       "      <td>545</td>\n",
       "      <td>-1</td>\n",
       "      <td>1004</td>\n",
       "      <td>1022</td>\n",
       "      <td>-18</td>\n",
       "      <td>B6</td>\n",
       "      <td>...</td>\n",
       "      <td>183</td>\n",
       "      <td>1576</td>\n",
       "      <td>5</td>\n",
       "      <td>45</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>2013-01-01 10:04:00</td>\n",
       "      <td>2013-01-01 05:44:00</td>\n",
       "      <td>2013-01-01 10:22:00</td>\n",
       "      <td>2013-01-01 05:45:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>554</td>\n",
       "      <td>600</td>\n",
       "      <td>-6</td>\n",
       "      <td>812</td>\n",
       "      <td>837</td>\n",
       "      <td>-25</td>\n",
       "      <td>DL</td>\n",
       "      <td>...</td>\n",
       "      <td>116</td>\n",
       "      <td>762</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>1/1/2013 6:00</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>2013-01-01 08:12:00</td>\n",
       "      <td>2013-01-01 05:54:00</td>\n",
       "      <td>2013-01-01 08:37:00</td>\n",
       "      <td>2013-01-01 06:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  month  day dep_time  sched_dep_time dep_delay arr_time  \\\n",
       "0  2013      1    1      517             515         2      830   \n",
       "1  2013      1    1      533             529         4      850   \n",
       "2  2013      1    1      542             540         2      923   \n",
       "3  2013      1    1      544             545        -1     1004   \n",
       "4  2013      1    1      554             600        -6      812   \n",
       "\n",
       "   sched_arr_time arr_delay carrier  ...  air_time distance hour minute  \\\n",
       "0             819        11      UA  ...       227     1400    5     15   \n",
       "1             830        20      UA  ...       227     1416    5     29   \n",
       "2             850        33      AA  ...       160     1089    5     40   \n",
       "3            1022       -18      B6  ...       183     1576    5     45   \n",
       "4             837       -25      DL  ...       116      762    6      0   \n",
       "\n",
       "       time_hour       date       arr_date_time       dep_date_time  \\\n",
       "0  1/1/2013 5:00 2013-01-01 2013-01-01 08:30:00 2013-01-01 05:17:00   \n",
       "1  1/1/2013 5:00 2013-01-01 2013-01-01 08:50:00 2013-01-01 05:33:00   \n",
       "2  1/1/2013 5:00 2013-01-01 2013-01-01 09:23:00 2013-01-01 05:42:00   \n",
       "3  1/1/2013 5:00 2013-01-01 2013-01-01 10:04:00 2013-01-01 05:44:00   \n",
       "4  1/1/2013 6:00 2013-01-01 2013-01-01 08:12:00 2013-01-01 05:54:00   \n",
       "\n",
       "  sched_arr_date_time sched_dep_date_time  \n",
       "0 2013-01-01 08:19:00 2013-01-01 05:15:00  \n",
       "1 2013-01-01 08:30:00 2013-01-01 05:29:00  \n",
       "2 2013-01-01 08:50:00 2013-01-01 05:40:00  \n",
       "3 2013-01-01 10:22:00 2013-01-01 05:45:00  \n",
       "4 2013-01-01 08:37:00 2013-01-01 06:00:00  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's go ahead and drop the year, month, and day columns as we don't need them anymore."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights = flights.drop(['year','month','day'], axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dep_time</th>\n",
       "      <th>sched_dep_time</th>\n",
       "      <th>dep_delay</th>\n",
       "      <th>arr_time</th>\n",
       "      <th>sched_arr_time</th>\n",
       "      <th>arr_delay</th>\n",
       "      <th>carrier</th>\n",
       "      <th>flight</th>\n",
       "      <th>tailnum</th>\n",
       "      <th>origin</th>\n",
       "      <th>...</th>\n",
       "      <th>air_time</th>\n",
       "      <th>distance</th>\n",
       "      <th>hour</th>\n",
       "      <th>minute</th>\n",
       "      <th>time_hour</th>\n",
       "      <th>date</th>\n",
       "      <th>arr_date_time</th>\n",
       "      <th>dep_date_time</th>\n",
       "      <th>sched_arr_date_time</th>\n",
       "      <th>sched_dep_date_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>517</td>\n",
       "      <td>515</td>\n",
       "      <td>2</td>\n",
       "      <td>830</td>\n",
       "      <td>819</td>\n",
       "      <td>11</td>\n",
       "      <td>UA</td>\n",
       "      <td>1545</td>\n",
       "      <td>N14228</td>\n",
       "      <td>EWR</td>\n",
       "      <td>...</td>\n",
       "      <td>227</td>\n",
       "      <td>1400</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>2013-01-01 08:30:00</td>\n",
       "      <td>2013-01-01 05:17:00</td>\n",
       "      <td>2013-01-01 08:19:00</td>\n",
       "      <td>2013-01-01 05:15:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>533</td>\n",
       "      <td>529</td>\n",
       "      <td>4</td>\n",
       "      <td>850</td>\n",
       "      <td>830</td>\n",
       "      <td>20</td>\n",
       "      <td>UA</td>\n",
       "      <td>1714</td>\n",
       "      <td>N24211</td>\n",
       "      <td>LGA</td>\n",
       "      <td>...</td>\n",
       "      <td>227</td>\n",
       "      <td>1416</td>\n",
       "      <td>5</td>\n",
       "      <td>29</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>2013-01-01 08:50:00</td>\n",
       "      <td>2013-01-01 05:33:00</td>\n",
       "      <td>2013-01-01 08:30:00</td>\n",
       "      <td>2013-01-01 05:29:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>542</td>\n",
       "      <td>540</td>\n",
       "      <td>2</td>\n",
       "      <td>923</td>\n",
       "      <td>850</td>\n",
       "      <td>33</td>\n",
       "      <td>AA</td>\n",
       "      <td>1141</td>\n",
       "      <td>N619AA</td>\n",
       "      <td>JFK</td>\n",
       "      <td>...</td>\n",
       "      <td>160</td>\n",
       "      <td>1089</td>\n",
       "      <td>5</td>\n",
       "      <td>40</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>2013-01-01 09:23:00</td>\n",
       "      <td>2013-01-01 05:42:00</td>\n",
       "      <td>2013-01-01 08:50:00</td>\n",
       "      <td>2013-01-01 05:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>544</td>\n",
       "      <td>545</td>\n",
       "      <td>-1</td>\n",
       "      <td>1004</td>\n",
       "      <td>1022</td>\n",
       "      <td>-18</td>\n",
       "      <td>B6</td>\n",
       "      <td>725</td>\n",
       "      <td>N804JB</td>\n",
       "      <td>JFK</td>\n",
       "      <td>...</td>\n",
       "      <td>183</td>\n",
       "      <td>1576</td>\n",
       "      <td>5</td>\n",
       "      <td>45</td>\n",
       "      <td>1/1/2013 5:00</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>2013-01-01 10:04:00</td>\n",
       "      <td>2013-01-01 05:44:00</td>\n",
       "      <td>2013-01-01 10:22:00</td>\n",
       "      <td>2013-01-01 05:45:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>554</td>\n",
       "      <td>600</td>\n",
       "      <td>-6</td>\n",
       "      <td>812</td>\n",
       "      <td>837</td>\n",
       "      <td>-25</td>\n",
       "      <td>DL</td>\n",
       "      <td>461</td>\n",
       "      <td>N668DN</td>\n",
       "      <td>LGA</td>\n",
       "      <td>...</td>\n",
       "      <td>116</td>\n",
       "      <td>762</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>1/1/2013 6:00</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>2013-01-01 08:12:00</td>\n",
       "      <td>2013-01-01 05:54:00</td>\n",
       "      <td>2013-01-01 08:37:00</td>\n",
       "      <td>2013-01-01 06:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  dep_time  sched_dep_time dep_delay arr_time  sched_arr_time arr_delay  \\\n",
       "0      517             515         2      830             819        11   \n",
       "1      533             529         4      850             830        20   \n",
       "2      542             540         2      923             850        33   \n",
       "3      544             545        -1     1004            1022       -18   \n",
       "4      554             600        -6      812             837       -25   \n",
       "\n",
       "  carrier  flight tailnum origin  ... air_time distance  hour  minute  \\\n",
       "0      UA    1545  N14228    EWR  ...      227     1400     5      15   \n",
       "1      UA    1714  N24211    LGA  ...      227     1416     5      29   \n",
       "2      AA    1141  N619AA    JFK  ...      160     1089     5      40   \n",
       "3      B6     725  N804JB    JFK  ...      183     1576     5      45   \n",
       "4      DL     461  N668DN    LGA  ...      116      762     6       0   \n",
       "\n",
       "       time_hour       date       arr_date_time       dep_date_time  \\\n",
       "0  1/1/2013 5:00 2013-01-01 2013-01-01 08:30:00 2013-01-01 05:17:00   \n",
       "1  1/1/2013 5:00 2013-01-01 2013-01-01 08:50:00 2013-01-01 05:33:00   \n",
       "2  1/1/2013 5:00 2013-01-01 2013-01-01 09:23:00 2013-01-01 05:42:00   \n",
       "3  1/1/2013 5:00 2013-01-01 2013-01-01 10:04:00 2013-01-01 05:44:00   \n",
       "4  1/1/2013 6:00 2013-01-01 2013-01-01 08:12:00 2013-01-01 05:54:00   \n",
       "\n",
       "  sched_arr_date_time sched_dep_date_time  \n",
       "0 2013-01-01 08:19:00 2013-01-01 05:15:00  \n",
       "1 2013-01-01 08:30:00 2013-01-01 05:29:00  \n",
       "2 2013-01-01 08:50:00 2013-01-01 05:40:00  \n",
       "3 2013-01-01 10:22:00 2013-01-01 05:45:00  \n",
       "4 2013-01-01 08:37:00 2013-01-01 06:00:00  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we need to get the month or day, we can extract them using the .dt. object attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1\n",
       "1    1\n",
       "2    1\n",
       "3    1\n",
       "4    1\n",
       "Name: date, dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.date.dt.month.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we've got the data in and cleaned up, let's do some Basic Exploratory Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's start with some basics:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How many unique carriers are there? How many flights for each carrier?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['UA', 'AA', 'B6', 'DL', 'EV', 'MQ', 'US', 'WN', 'VX', 'FL', 'AS',\n",
       "       '9E', 'F9', 'HA', 'YV', 'OO'], dtype=object)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.carrier.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.carrier.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "UA    58665\n",
       "B6    54635\n",
       "EV    54173\n",
       "DL    48110\n",
       "AA    32729\n",
       "MQ    26397\n",
       "US    20536\n",
       "9E    18460\n",
       "WN    12275\n",
       "VX     5162\n",
       "FL     3260\n",
       "AS      714\n",
       "F9      685\n",
       "YV      601\n",
       "HA      342\n",
       "OO       32\n",
       "Name: carrier, dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "carrier_counts = flights.carrier.value_counts()\n",
    "carrier_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>carrier</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9E</th>\n",
       "      <td>Endeavor Air Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AA</th>\n",
       "      <td>American Airlines Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AS</th>\n",
       "      <td>Alaska Airlines Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B6</th>\n",
       "      <td>JetBlue Airways</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DL</th>\n",
       "      <td>Delta Air Lines Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EV</th>\n",
       "      <td>ExpressJet Airlines Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F9</th>\n",
       "      <td>Frontier Airlines Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FL</th>\n",
       "      <td>AirTran Airways Corporation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HA</th>\n",
       "      <td>Hawaiian Airlines Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MQ</th>\n",
       "      <td>Envoy Air</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OO</th>\n",
       "      <td>SkyWest Airlines Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UA</th>\n",
       "      <td>United Air Lines Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>US</th>\n",
       "      <td>US Airways Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VX</th>\n",
       "      <td>Virgin America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WN</th>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YV</th>\n",
       "      <td>Mesa Airlines Inc.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                name\n",
       "carrier                             \n",
       "9E                 Endeavor Air Inc.\n",
       "AA            American Airlines Inc.\n",
       "AS              Alaska Airlines Inc.\n",
       "B6                   JetBlue Airways\n",
       "DL              Delta Air Lines Inc.\n",
       "EV          ExpressJet Airlines Inc.\n",
       "F9            Frontier Airlines Inc.\n",
       "FL       AirTran Airways Corporation\n",
       "HA            Hawaiian Airlines Inc.\n",
       "MQ                         Envoy Air\n",
       "OO             SkyWest Airlines Inc.\n",
       "UA             United Air Lines Inc.\n",
       "US                   US Airways Inc.\n",
       "VX                    Virgin America\n",
       "WN            Southwest Airlines Co.\n",
       "YV                Mesa Airlines Inc."
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airlines = pd.read_csv('airlines.csv', index_col = 'carrier')\n",
    "airlines"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>carrier</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>carrier</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9E</th>\n",
       "      <td>Endeavor Air Inc.</td>\n",
       "      <td>18460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AA</th>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>32729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AS</th>\n",
       "      <td>Alaska Airlines Inc.</td>\n",
       "      <td>714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B6</th>\n",
       "      <td>JetBlue Airways</td>\n",
       "      <td>54635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DL</th>\n",
       "      <td>Delta Air Lines Inc.</td>\n",
       "      <td>48110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EV</th>\n",
       "      <td>ExpressJet Airlines Inc.</td>\n",
       "      <td>54173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F9</th>\n",
       "      <td>Frontier Airlines Inc.</td>\n",
       "      <td>685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FL</th>\n",
       "      <td>AirTran Airways Corporation</td>\n",
       "      <td>3260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HA</th>\n",
       "      <td>Hawaiian Airlines Inc.</td>\n",
       "      <td>342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MQ</th>\n",
       "      <td>Envoy Air</td>\n",
       "      <td>26397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OO</th>\n",
       "      <td>SkyWest Airlines Inc.</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UA</th>\n",
       "      <td>United Air Lines Inc.</td>\n",
       "      <td>58665</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>US</th>\n",
       "      <td>US Airways Inc.</td>\n",
       "      <td>20536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VX</th>\n",
       "      <td>Virgin America</td>\n",
       "      <td>5162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WN</th>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>12275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YV</th>\n",
       "      <td>Mesa Airlines Inc.</td>\n",
       "      <td>601</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                name  carrier\n",
       "carrier                                      \n",
       "9E                 Endeavor Air Inc.    18460\n",
       "AA            American Airlines Inc.    32729\n",
       "AS              Alaska Airlines Inc.      714\n",
       "B6                   JetBlue Airways    54635\n",
       "DL              Delta Air Lines Inc.    48110\n",
       "EV          ExpressJet Airlines Inc.    54173\n",
       "F9            Frontier Airlines Inc.      685\n",
       "FL       AirTran Airways Corporation     3260\n",
       "HA            Hawaiian Airlines Inc.      342\n",
       "MQ                         Envoy Air    26397\n",
       "OO             SkyWest Airlines Inc.       32\n",
       "UA             United Air Lines Inc.    58665\n",
       "US                   US Airways Inc.    20536\n",
       "VX                    Virgin America     5162\n",
       "WN            Southwest Airlines Co.    12275\n",
       "YV                Mesa Airlines Inc.      601"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airlines_count = airlines.join(carrier_counts)\n",
    "airlines_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>carrier</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9E</th>\n",
       "      <td>Endeavor Air Inc.</td>\n",
       "      <td>18460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AA</th>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>32729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AS</th>\n",
       "      <td>Alaska Airlines Inc.</td>\n",
       "      <td>714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B6</th>\n",
       "      <td>JetBlue Airways</td>\n",
       "      <td>54635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DL</th>\n",
       "      <td>Delta Air Lines Inc.</td>\n",
       "      <td>48110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EV</th>\n",
       "      <td>ExpressJet Airlines Inc.</td>\n",
       "      <td>54173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F9</th>\n",
       "      <td>Frontier Airlines Inc.</td>\n",
       "      <td>685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FL</th>\n",
       "      <td>AirTran Airways Corporation</td>\n",
       "      <td>3260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HA</th>\n",
       "      <td>Hawaiian Airlines Inc.</td>\n",
       "      <td>342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MQ</th>\n",
       "      <td>Envoy Air</td>\n",
       "      <td>26397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OO</th>\n",
       "      <td>SkyWest Airlines Inc.</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UA</th>\n",
       "      <td>United Air Lines Inc.</td>\n",
       "      <td>58665</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>US</th>\n",
       "      <td>US Airways Inc.</td>\n",
       "      <td>20536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VX</th>\n",
       "      <td>Virgin America</td>\n",
       "      <td>5162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WN</th>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>12275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YV</th>\n",
       "      <td>Mesa Airlines Inc.</td>\n",
       "      <td>601</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                name  count\n",
       "carrier                                    \n",
       "9E                 Endeavor Air Inc.  18460\n",
       "AA            American Airlines Inc.  32729\n",
       "AS              Alaska Airlines Inc.    714\n",
       "B6                   JetBlue Airways  54635\n",
       "DL              Delta Air Lines Inc.  48110\n",
       "EV          ExpressJet Airlines Inc.  54173\n",
       "F9            Frontier Airlines Inc.    685\n",
       "FL       AirTran Airways Corporation   3260\n",
       "HA            Hawaiian Airlines Inc.    342\n",
       "MQ                         Envoy Air  26397\n",
       "OO             SkyWest Airlines Inc.     32\n",
       "UA             United Air Lines Inc.  58665\n",
       "US                   US Airways Inc.  20536\n",
       "VX                    Virgin America   5162\n",
       "WN            Southwest Airlines Co.  12275\n",
       "YV                Mesa Airlines Inc.    601"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airlines_count.rename(index = str, columns = {\"name\": \"name\", \"carrier\": \"count\" })"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What about origination airports?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['EWR', 'LGA', 'JFK'], dtype=object)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.origin.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "EWR    120835\n",
       "JFK    111279\n",
       "LGA    104662\n",
       "Name: origin, dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.origin.value_counts()  # it appears that the three airports in the new york area are used with similar frequency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "origin\n",
       "EWR    120835\n",
       "JFK    111279\n",
       "LGA    104662\n",
       "Name: sched_dep_date_time, dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# alternative method using .groupby(), which is similar to dplyr::groupby\n",
    "flights.groupby('origin').sched_dep_date_time.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "origin\n",
       "EWR    117441\n",
       "JFK    108816\n",
       "LGA    101383\n",
       "Name: dep_date_time, dtype: int64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# if we perform a count on the dep_date_time column, it does not include NaNs.\n",
    "flights.groupby('origin').dep_date_time.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Destination airports?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['IAH', 'MIA', 'BQN', 'ATL', 'ORD', 'FLL', 'IAD', 'MCO', 'PBI',\n",
       "       'TPA', 'LAX', 'SFO', 'DFW', 'BOS', 'LAS', 'MSP', 'DTW', 'RSW',\n",
       "       'SJU', 'PHX', 'BWI', 'CLT', 'BUF', 'DEN', 'SNA', 'MSY', 'SLC',\n",
       "       'XNA', 'MKE', 'SEA', 'ROC', 'SYR', 'SRQ', 'RDU', 'CMH', 'JAX',\n",
       "       'CHS', 'MEM', 'PIT', 'SAN', 'DCA', 'CLE', 'STL', 'MYR', 'JAC',\n",
       "       'MDW', 'HNL', 'BNA', 'AUS', 'BTV', 'PHL', 'STT', 'EGE', 'AVL',\n",
       "       'PWM', 'IND', 'SAV', 'CAK', 'HOU', 'LGB', 'DAY', 'ALB', 'BDL',\n",
       "       'MHT', 'MSN', 'GSO', 'CVG', 'BUR', 'RIC', 'GSP', 'GRR', 'MCI',\n",
       "       'ORF', 'SAT', 'SDF', 'PDX', 'SJC', 'OMA', 'CRW', 'OAK', 'SMF',\n",
       "       'TUL', 'TYS', 'OKC', 'PVD', 'DSM', 'PSE', 'BHM', 'CAE', 'HDN',\n",
       "       'BZN', 'MTJ', 'EYW', 'PSP', 'ACK', 'BGR', 'ABQ', 'ILM', 'MVY',\n",
       "       'SBN', 'LEX', 'CHO', 'TVC', 'ANC', 'LGA'], dtype=object)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.dest.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "105"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.dest.nunique()  # 105 destinations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ORD    17283\n",
       "ATL    17215\n",
       "LAX    16174\n",
       "BOS    15508\n",
       "MCO    14082\n",
       "CLT    14064\n",
       "SFO    13331\n",
       "FLL    12055\n",
       "MIA    11728\n",
       "DCA     9705\n",
       "Name: dest, dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.dest.value_counts().head(10)  # a quick peek at the top 10 destinations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Which month is most busy?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights.dep_date_time.dt.month.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights.dep_date_time.dt.month.value_counts().sort_index().plot(kind = 'bar', color = 'dodgerblue')\n",
    "plt.title('Number of Flights Each Month')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What time of day is most busy?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# using groupby instead of value_counts\n",
    "flights.groupby(dep_date_time.dt.hour).date.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights.groupby(dep_date_time.dt.hour).date.count().plot(kind = 'bar', color = 'dodgerblue')\n",
    "plt.title('Flights Each Hour')\n",
    "plt.show()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}