03-03 EDA with Pandas .ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's read in some data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('nycflights.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll look at the head and some info about the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   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  \n"
     ]
    }
   ],
   "source": [
    "print(df.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": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(336776, 19)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['EWR', 'LGA', 'JFK'], dtype=object)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['origin'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0          2013-1-1\n",
       "1          2013-1-1\n",
       "2          2013-1-1\n",
       "3          2013-1-1\n",
       "4          2013-1-1\n",
       "5          2013-1-1\n",
       "6          2013-1-1\n",
       "7          2013-1-1\n",
       "8          2013-1-1\n",
       "9          2013-1-1\n",
       "10         2013-1-1\n",
       "11         2013-1-1\n",
       "12         2013-1-1\n",
       "13         2013-1-1\n",
       "14         2013-1-1\n",
       "15         2013-1-1\n",
       "16         2013-1-1\n",
       "17         2013-1-1\n",
       "18         2013-1-1\n",
       "19         2013-1-1\n",
       "20         2013-1-1\n",
       "21         2013-1-1\n",
       "22         2013-1-1\n",
       "23         2013-1-1\n",
       "24         2013-1-1\n",
       "25         2013-1-1\n",
       "26         2013-1-1\n",
       "27         2013-1-1\n",
       "28         2013-1-1\n",
       "29         2013-1-1\n",
       "            ...    \n",
       "336746    2013-9-30\n",
       "336747    2013-9-30\n",
       "336748    2013-9-30\n",
       "336749    2013-9-30\n",
       "336750    2013-9-30\n",
       "336751    2013-9-30\n",
       "336752    2013-9-30\n",
       "336753    2013-9-30\n",
       "336754    2013-9-30\n",
       "336755    2013-9-30\n",
       "336756    2013-9-30\n",
       "336757    2013-9-30\n",
       "336758    2013-9-30\n",
       "336759    2013-9-30\n",
       "336760    2013-9-30\n",
       "336761    2013-9-30\n",
       "336762    2013-9-30\n",
       "336763    2013-9-30\n",
       "336764    2013-9-30\n",
       "336765    2013-9-30\n",
       "336766    2013-9-30\n",
       "336767    2013-9-30\n",
       "336768    2013-9-30\n",
       "336769    2013-9-30\n",
       "336770    2013-9-30\n",
       "336771    2013-9-30\n",
       "336772    2013-9-30\n",
       "336773    2013-9-30\n",
       "336774    2013-9-30\n",
       "336775    2013-9-30\n",
       "Length: 336776, dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['year'].astype(str) + \"-\" + df['month'].astype(str) + '-' + df['day'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['date'] = df['year'].astype(str) + \"-\" + df['month'].astype(str) + \"-\" + df['day'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    2013-1-1\n",
      "1    2013-1-1\n",
      "2    2013-1-1\n",
      "3    2013-1-1\n",
      "4    2013-1-1\n",
      "5    2013-1-1\n",
      "6    2013-1-1\n",
      "7    2013-1-1\n",
      "8    2013-1-1\n",
      "9    2013-1-1\n",
      "Name: date, dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(df['date'][:10])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['date'] = pd.to_datetime(df['date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 336776 entries, 0 to 336775\n",
      "Data columns (total 20 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",
      "date              336776 non-null datetime64[ns]\n",
      "dtypes: datetime64[ns](1), float64(5), int64(9), object(5)\n",
      "memory usage: 51.4+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.set_index(['date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2013-01-01', '2013-01-01', '2013-01-01', '2013-01-01',\n",
       "               '2013-01-01', '2013-01-01', '2013-01-01', '2013-01-01',\n",
       "               '2013-01-01', '2013-01-01',\n",
       "               ...\n",
       "               '2013-09-30', '2013-09-30', '2013-09-30', '2013-09-30',\n",
       "               '2013-09-30', '2013-09-30', '2013-09-30', '2013-09-30',\n",
       "               '2013-09-30', '2013-09-30'],\n",
       "              dtype='datetime64[ns]', name='date', length=336776, freq=None)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            year  month  day  dep_time  sched_dep_time  dep_delay  arr_time  \\\n",
      "date                                                                          \n",
      "2013-01-01  2013      1    1     517.0             515        2.0     830.0   \n",
      "2013-01-01  2013      1    1     533.0             529        4.0     850.0   \n",
      "2013-01-01  2013      1    1     542.0             540        2.0     923.0   \n",
      "2013-01-01  2013      1    1     544.0             545       -1.0    1004.0   \n",
      "2013-01-01  2013      1    1     554.0             600       -6.0     812.0   \n",
      "\n",
      "            sched_arr_time  arr_delay carrier  flight tailnum origin dest  \\\n",
      "date                                                                        \n",
      "2013-01-01             819       11.0      UA    1545  N14228    EWR  IAH   \n",
      "2013-01-01             830       20.0      UA    1714  N24211    LGA  IAH   \n",
      "2013-01-01             850       33.0      AA    1141  N619AA    JFK  MIA   \n",
      "2013-01-01            1022      -18.0      B6     725  N804JB    JFK  BQN   \n",
      "2013-01-01             837      -25.0      DL     461  N668DN    LGA  ATL   \n",
      "\n",
      "            air_time  distance  hour  minute      time_hour  \n",
      "date                                                         \n",
      "2013-01-01     227.0      1400     5      15  1/1/2013 5:00  \n",
      "2013-01-01     227.0      1416     5      29  1/1/2013 5:00  \n",
      "2013-01-01     160.0      1089     5      40  1/1/2013 5:00  \n",
      "2013-01-01     183.0      1576     5      45  1/1/2013 5:00  \n",
      "2013-01-01     116.0       762     6       0  1/1/2013 6:00  \n"
     ]
    }
   ],
   "source": [
    "print(df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.DataFrameGroupBy object at 0x000002650F7C8160>"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('carrier')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.SeriesGroupBy object at 0x000002650F7C88D0>"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('carrier')['dest']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "carrier\n",
       "9E    [MSP, IAD, BUF, SYR, ROC, BWI, ORD, IND, BNA, ...\n",
       "AA    [MIA, ORD, DFW, SJU, MCO, FLL, LAX, SFO, BOS, ...\n",
       "AS                                                [SEA]\n",
       "B6    [BQN, FLL, MCO, PBI, TPA, BOS, RSW, SJU, BUF, ...\n",
       "DL    [ATL, MSP, PBI, SLC, SFO, MIA, FLL, DTW, SEA, ...\n",
       "EV    [IAD, MSP, JAX, CHS, MEM, CLE, MYR, RDU, DCA, ...\n",
       "F9                                                [DEN]\n",
       "FL                                      [MKE, ATL, CAK]\n",
       "HA                                                [HNL]\n",
       "MQ    [ATL, DTW, ORD, MSP, XNA, RDU, CMH, CLT, DCA, ...\n",
       "OO                            [ORD, MSP, IAD, DTW, CLE]\n",
       "UA    [IAH, ORD, LAX, SFO, LAS, MIA, PBI, FLL, DEN, ...\n",
       "US                       [PHX, CLT, PHL, DCA, BOS, LGA]\n",
       "VX                            [LAX, SFO, LAS, PSP, SJC]\n",
       "WN    [BWI, DEN, MDW, MKE, STL, HOU, PHX, BNA, AUS, ...\n",
       "YV                                      [IAD, CLT, PHL]\n",
       "Name: dest, dtype: object"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('carrier')['dest'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "carrier\n",
       "9E    [JFK, EWR, LGA]\n",
       "AA    [JFK, LGA, EWR]\n",
       "AS              [EWR]\n",
       "B6    [JFK, EWR, LGA]\n",
       "DL    [LGA, JFK, EWR]\n",
       "EV    [LGA, EWR, JFK]\n",
       "F9              [LGA]\n",
       "FL              [LGA]\n",
       "HA              [JFK]\n",
       "MQ    [LGA, EWR, JFK]\n",
       "OO         [LGA, EWR]\n",
       "UA    [EWR, LGA, JFK]\n",
       "US    [EWR, JFK, LGA]\n",
       "VX         [JFK, EWR]\n",
       "WN         [LGA, EWR]\n",
       "YV              [LGA]\n",
       "Name: origin, dtype: object"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('carrier')['origin'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "origin\n",
      "EWR    117596\n",
      "JFK    109416\n",
      "LGA    101509\n",
      "Name: dep_time, dtype: int64\n",
      "origin\n",
      "EWR    120835\n",
      "JFK    111279\n",
      "LGA    104662\n",
      "Name: dest, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# busiest origin?\n",
    "print(df.groupby('origin')['dep_time'].count())  # has NaNs, and they are not counted\n",
    "print(df.groupby('origin')['dest'].count()) # no NaNs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "month\n",
      "1     10.036665\n",
      "2     10.816843\n",
      "3     13.227076\n",
      "4     13.938038\n",
      "5     12.986859\n",
      "6     20.846332\n",
      "7     21.727787\n",
      "8     12.611040\n",
      "9      6.722476\n",
      "10     6.243988\n",
      "11     5.435362\n",
      "12    16.576688\n",
      "Name: dep_delay, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(df.groupby('month')['dep_delay'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16174"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# how many flights to LAX?\n",
    "sum(df['dest'] == 'LAX')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "lax_df = df[ df['dest']=='LAX' ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-01</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>558.0</td>\n",
       "      <td>600</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>924.0</td>\n",
       "      <td>917</td>\n",
       "      <td>7.0</td>\n",
       "      <td>UA</td>\n",
       "      <td>194</td>\n",
       "      <td>N29129</td>\n",
       "      <td>JFK</td>\n",
       "      <td>LAX</td>\n",
       "      <td>345.0</td>\n",
       "      <td>2475</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>1/1/2013 6:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-01</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>628.0</td>\n",
       "      <td>630</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>1016.0</td>\n",
       "      <td>947</td>\n",
       "      <td>29.0</td>\n",
       "      <td>UA</td>\n",
       "      <td>1665</td>\n",
       "      <td>N33289</td>\n",
       "      <td>EWR</td>\n",
       "      <td>LAX</td>\n",
       "      <td>366.0</td>\n",
       "      <td>2454</td>\n",
       "      <td>6</td>\n",
       "      <td>30</td>\n",
       "      <td>1/1/2013 6:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-01</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>658.0</td>\n",
       "      <td>700</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>1027.0</td>\n",
       "      <td>1025</td>\n",
       "      <td>2.0</td>\n",
       "      <td>VX</td>\n",
       "      <td>399</td>\n",
       "      <td>N627VA</td>\n",
       "      <td>JFK</td>\n",
       "      <td>LAX</td>\n",
       "      <td>361.0</td>\n",
       "      <td>2475</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>1/1/2013 7:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-01</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>702.0</td>\n",
       "      <td>700</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1058.0</td>\n",
       "      <td>1014</td>\n",
       "      <td>44.0</td>\n",
       "      <td>B6</td>\n",
       "      <td>671</td>\n",
       "      <td>N779JB</td>\n",
       "      <td>JFK</td>\n",
       "      <td>LAX</td>\n",
       "      <td>381.0</td>\n",
       "      <td>2475</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>1/1/2013 7:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-01</th>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>743.0</td>\n",
       "      <td>730</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1107.0</td>\n",
       "      <td>1100</td>\n",
       "      <td>7.0</td>\n",
       "      <td>AA</td>\n",
       "      <td>33</td>\n",
       "      <td>N338AA</td>\n",
       "      <td>JFK</td>\n",
       "      <td>LAX</td>\n",
       "      <td>358.0</td>\n",
       "      <td>2475</td>\n",
       "      <td>7</td>\n",
       "      <td>30</td>\n",
       "      <td>1/1/2013 7: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",
       "date                                                                          \n",
       "2013-01-01  2013      1    1     558.0             600       -2.0     924.0   \n",
       "2013-01-01  2013      1    1     628.0             630       -2.0    1016.0   \n",
       "2013-01-01  2013      1    1     658.0             700       -2.0    1027.0   \n",
       "2013-01-01  2013      1    1     702.0             700        2.0    1058.0   \n",
       "2013-01-01  2013      1    1     743.0             730       13.0    1107.0   \n",
       "\n",
       "            sched_arr_time  arr_delay carrier  flight tailnum origin dest  \\\n",
       "date                                                                        \n",
       "2013-01-01             917        7.0      UA     194  N29129    JFK  LAX   \n",
       "2013-01-01             947       29.0      UA    1665  N33289    EWR  LAX   \n",
       "2013-01-01            1025        2.0      VX     399  N627VA    JFK  LAX   \n",
       "2013-01-01            1014       44.0      B6     671  N779JB    JFK  LAX   \n",
       "2013-01-01            1100        7.0      AA      33  N338AA    JFK  LAX   \n",
       "\n",
       "            air_time  distance  hour  minute      time_hour  \n",
       "date                                                         \n",
       "2013-01-01     345.0      2475     6       0  1/1/2013 6:00  \n",
       "2013-01-01     366.0      2454     6      30  1/1/2013 6:00  \n",
       "2013-01-01     361.0      2475     7       0  1/1/2013 7:00  \n",
       "2013-01-01     381.0      2475     7       0  1/1/2013 7:00  \n",
       "2013-01-01     358.0      2475     7      30  1/1/2013 7:00  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lax_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "origin\n",
       "EWR     4912\n",
       "JFK    11262\n",
       "Name: origin, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lax_df.groupby('origin')['origin'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "origin\n",
      "EWR    0.0\n",
      "JFK   -1.0\n",
      "Name: dep_delay, dtype: float64\n",
      "origin\n",
      "EWR    11.417623\n",
      "JFK     8.522508\n",
      "Name: dep_delay, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(lax_df.groupby('origin')['dep_delay'].median())\n",
    "print(lax_df.groupby('origin')['dep_delay'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "carrier\n",
      "AA   -2.0\n",
      "B6   -1.0\n",
      "DL   -1.0\n",
      "UA    0.0\n",
      "VX    0.0\n",
      "Name: dep_delay, dtype: float64\n",
      "carrier\n",
      "AA     9.168119\n",
      "B6     8.994652\n",
      "DL     5.668539\n",
      "UA    10.757738\n",
      "VX    10.561890\n",
      "Name: dep_delay, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(lax_df.groupby('carrier')['dep_delay'].median())\n",
    "print(lax_df.groupby('carrier')['dep_delay'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "carrier  origin\n",
      "AA       EWR        5.861496\n",
      "         JFK        9.541615\n",
      "B6       JFK        8.994652\n",
      "DL       JFK        5.668539\n",
      "UA       EWR       12.472334\n",
      "         JFK        7.616552\n",
      "VX       EWR        8.924165\n",
      "         JFK       11.276500\n",
      "Name: dep_delay, dtype: float64\n",
      "carrier  origin\n",
      "AA       EWR      -3.0\n",
      "         JFK      -2.0\n",
      "B6       JFK      -1.0\n",
      "DL       JFK      -1.0\n",
      "UA       EWR       1.0\n",
      "         JFK      -2.0\n",
      "VX       EWR      -1.0\n",
      "         JFK       0.0\n",
      "Name: dep_delay, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(lax_df.groupby(['carrier','origin'])['dep_delay'].mean())\n",
    "print(lax_df.groupby(['carrier','origin'])['dep_delay'].median())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all flights again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date\n",
       "2013-03-08    83.536921\n",
       "2013-07-01    56.233825\n",
       "2013-09-02    53.029551\n",
       "2013-07-10    52.860702\n",
       "2013-12-05    52.327990\n",
       "Name: dep_delay, dtype: float64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# on what days was the departure delay the largest on average?\n",
    "df.groupby('date')['dep_delay'].mean().sort_values(ascending = False).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date\n",
       "2013-12-23    674\n",
       "2013-03-08    653\n",
       "2013-07-01    652\n",
       "2013-06-25    649\n",
       "2013-07-23    645\n",
       "Name: dep_delay, dtype: int64"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# on what days did we have the greatest number of delayed flights?\n",
    "df[df['dep_delay'] > 0].groupby('date')['dep_delay'].count().sort_values(ascending = False).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>carrier</th>\n",
       "      <th>9E</th>\n",
       "      <th>AA</th>\n",
       "      <th>AS</th>\n",
       "      <th>B6</th>\n",
       "      <th>DL</th>\n",
       "      <th>EV</th>\n",
       "      <th>F9</th>\n",
       "      <th>FL</th>\n",
       "      <th>HA</th>\n",
       "      <th>MQ</th>\n",
       "      <th>OO</th>\n",
       "      <th>UA</th>\n",
       "      <th>US</th>\n",
       "      <th>VX</th>\n",
       "      <th>WN</th>\n",
       "      <th>YV</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>574.0</td>\n",
       "      <td>904.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1734.0</td>\n",
       "      <td>798.0</td>\n",
       "      <td>2052.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>563.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2070.0</td>\n",
       "      <td>349.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>389.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>511.0</td>\n",
       "      <td>842.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>1797.0</td>\n",
       "      <td>872.0</td>\n",
       "      <td>1808.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>118.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>560.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1781.0</td>\n",
       "      <td>290.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>385.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>538.0</td>\n",
       "      <td>877.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>2065.0</td>\n",
       "      <td>1268.0</td>\n",
       "      <td>2341.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2268.0</td>\n",
       "      <td>389.0</td>\n",
       "      <td>149.0</td>\n",
       "      <td>493.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>471.0</td>\n",
       "      <td>786.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>1889.0</td>\n",
       "      <td>1165.0</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>138.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>699.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2236.0</td>\n",
       "      <td>403.0</td>\n",
       "      <td>231.0</td>\n",
       "      <td>454.0</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>641.0</td>\n",
       "      <td>841.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>1522.0</td>\n",
       "      <td>1370.0</td>\n",
       "      <td>2261.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>756.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2408.0</td>\n",
       "      <td>416.0</td>\n",
       "      <td>263.0</td>\n",
       "      <td>569.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>683.0</td>\n",
       "      <td>964.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>1809.0</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>827.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2753.0</td>\n",
       "      <td>486.0</td>\n",
       "      <td>276.0</td>\n",
       "      <td>641.0</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>749.0</td>\n",
       "      <td>1020.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2550.0</td>\n",
       "      <td>1931.0</td>\n",
       "      <td>2161.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>164.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>933.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2802.0</td>\n",
       "      <td>524.0</td>\n",
       "      <td>284.0</td>\n",
       "      <td>682.0</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>639.0</td>\n",
       "      <td>841.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2179.0</td>\n",
       "      <td>1547.0</td>\n",
       "      <td>1743.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>716.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2518.0</td>\n",
       "      <td>442.0</td>\n",
       "      <td>162.0</td>\n",
       "      <td>675.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>449.0</td>\n",
       "      <td>709.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1190.0</td>\n",
       "      <td>919.0</td>\n",
       "      <td>1338.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>556.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1559.0</td>\n",
       "      <td>306.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>483.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>587.0</td>\n",
       "      <td>670.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1040.0</td>\n",
       "      <td>1019.0</td>\n",
       "      <td>1767.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>547.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1895.0</td>\n",
       "      <td>333.0</td>\n",
       "      <td>146.0</td>\n",
       "      <td>522.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>479.0</td>\n",
       "      <td>613.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1123.0</td>\n",
       "      <td>881.0</td>\n",
       "      <td>1581.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>489.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1845.0</td>\n",
       "      <td>377.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>500.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>742.0</td>\n",
       "      <td>1095.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>2376.0</td>\n",
       "      <td>1662.0</td>\n",
       "      <td>2090.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>151.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>785.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3126.0</td>\n",
       "      <td>460.0</td>\n",
       "      <td>221.0</td>\n",
       "      <td>765.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "carrier     9E      AA    AS      B6      DL      EV    F9     FL    HA  \\\n",
       "month                                                                     \n",
       "1        574.0   904.0  23.0  1734.0   798.0  2052.0  14.0   76.0  11.0   \n",
       "2        511.0   842.0  16.0  1797.0   872.0  1808.0  15.0  118.0   9.0   \n",
       "3        538.0   877.0  19.0  2065.0  1268.0  2341.0  20.0  160.0  11.0   \n",
       "4        471.0   786.0  21.0  1889.0  1165.0  2007.0  18.0  138.0   4.0   \n",
       "5        641.0   841.0  19.0  1522.0  1370.0  2261.0  42.0  155.0   5.0   \n",
       "6        683.0   964.0  21.0  1980.0  1809.0  1990.0  35.0  157.0   6.0   \n",
       "7        749.0  1020.0  24.0  2550.0  1931.0  2161.0  52.0  164.0   2.0   \n",
       "8        639.0   841.0  17.0  2179.0  1547.0  1743.0  46.0  159.0   6.0   \n",
       "9        449.0   709.0   7.0  1190.0   919.0  1338.0  29.0  109.0   2.0   \n",
       "10       587.0   670.0  11.0  1040.0  1019.0  1767.0  24.0  140.0   2.0   \n",
       "11       479.0   613.0  13.0  1123.0   881.0  1581.0  28.0  127.0   5.0   \n",
       "12       742.0  1095.0  35.0  2376.0  1662.0  2090.0  18.0  151.0   6.0   \n",
       "\n",
       "carrier     MQ   OO      UA     US     VX     WN    YV  \n",
       "month                                                   \n",
       "1        563.0  1.0  2070.0  349.0   89.0  389.0  15.0  \n",
       "2        560.0  NaN  1781.0  290.0  104.0  385.0  16.0  \n",
       "3        600.0  NaN  2268.0  389.0  149.0  493.0  11.0  \n",
       "4        699.0  NaN  2236.0  403.0  231.0  454.0  21.0  \n",
       "5        756.0  NaN  2408.0  416.0  263.0  569.0  23.0  \n",
       "6        827.0  1.0  2753.0  486.0  276.0  641.0  26.0  \n",
       "7        933.0  NaN  2802.0  524.0  284.0  682.0  31.0  \n",
       "8        716.0  3.0  2518.0  442.0  162.0  675.0  20.0  \n",
       "9        556.0  1.0  1559.0  306.0  142.0  483.0  16.0  \n",
       "10       547.0  NaN  1895.0  333.0  146.0  522.0  19.0  \n",
       "11       489.0  3.0  1845.0  377.0  158.0  500.0  17.0  \n",
       "12       785.0  NaN  3126.0  460.0  221.0  765.0  18.0  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# subset the dataframe to rows where dep-delay is > 0\n",
    "# groupby month and carrier\n",
    "# select the dep_delay column\n",
    "# we count those\n",
    "df[ df['dep_delay'] > 0 ].groupby(['month','carrier'])['dep_delay'].aggregate('count').unstack()\n",
    "# number of flights for each month and each carrier that were delayed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>carrier</th>\n",
       "      <th>9E</th>\n",
       "      <th>AA</th>\n",
       "      <th>AS</th>\n",
       "      <th>B6</th>\n",
       "      <th>DL</th>\n",
       "      <th>EV</th>\n",
       "      <th>F9</th>\n",
       "      <th>FL</th>\n",
       "      <th>HA</th>\n",
       "      <th>MQ</th>\n",
       "      <th>OO</th>\n",
       "      <th>UA</th>\n",
       "      <th>US</th>\n",
       "      <th>VX</th>\n",
       "      <th>WN</th>\n",
       "      <th>YV</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1498.0</td>\n",
       "      <td>2735.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>4418.0</td>\n",
       "      <td>3661.0</td>\n",
       "      <td>3989.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>324.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2206.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4605.0</td>\n",
       "      <td>1555.0</td>\n",
       "      <td>315.0</td>\n",
       "      <td>985.0</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1353.0</td>\n",
       "      <td>2405.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>3950.0</td>\n",
       "      <td>3312.0</td>\n",
       "      <td>3558.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>282.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1904.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4166.0</td>\n",
       "      <td>1462.0</td>\n",
       "      <td>261.0</td>\n",
       "      <td>861.0</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1514.0</td>\n",
       "      <td>2746.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>4753.0</td>\n",
       "      <td>4131.0</td>\n",
       "      <td>4387.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>305.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2137.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4926.0</td>\n",
       "      <td>1648.0</td>\n",
       "      <td>303.0</td>\n",
       "      <td>955.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1407.0</td>\n",
       "      <td>2663.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>4468.0</td>\n",
       "      <td>4089.0</td>\n",
       "      <td>4345.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>305.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4992.0</td>\n",
       "      <td>1678.0</td>\n",
       "      <td>466.0</td>\n",
       "      <td>966.0</td>\n",
       "      <td>36.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1388.0</td>\n",
       "      <td>2770.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>4543.0</td>\n",
       "      <td>4076.0</td>\n",
       "      <td>4586.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>322.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2212.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4910.0</td>\n",
       "      <td>1747.0</td>\n",
       "      <td>496.0</td>\n",
       "      <td>988.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1276.0</td>\n",
       "      <td>2700.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>4572.0</td>\n",
       "      <td>4090.0</td>\n",
       "      <td>4106.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>243.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1998.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4910.0</td>\n",
       "      <td>1655.0</td>\n",
       "      <td>480.0</td>\n",
       "      <td>1018.0</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1364.0</td>\n",
       "      <td>2797.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>4924.0</td>\n",
       "      <td>4201.0</td>\n",
       "      <td>4341.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>252.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2132.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>1703.0</td>\n",
       "      <td>486.0</td>\n",
       "      <td>1065.0</td>\n",
       "      <td>69.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1378.0</td>\n",
       "      <td>2830.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>4947.0</td>\n",
       "      <td>4313.0</td>\n",
       "      <td>4354.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>256.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2168.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5106.0</td>\n",
       "      <td>1741.0</td>\n",
       "      <td>489.0</td>\n",
       "      <td>1047.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1477.0</td>\n",
       "      <td>2584.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>4279.0</td>\n",
       "      <td>3874.0</td>\n",
       "      <td>4572.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>254.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>2111.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>4653.0</td>\n",
       "      <td>1657.0</td>\n",
       "      <td>453.0</td>\n",
       "      <td>1007.0</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1642.0</td>\n",
       "      <td>2706.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>4359.0</td>\n",
       "      <td>4091.0</td>\n",
       "      <td>4820.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>234.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>2186.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5040.0</td>\n",
       "      <td>1815.0</td>\n",
       "      <td>471.0</td>\n",
       "      <td>1089.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1575.0</td>\n",
       "      <td>2558.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>4286.0</td>\n",
       "      <td>3847.0</td>\n",
       "      <td>4389.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>202.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4838.0</td>\n",
       "      <td>1668.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>1028.0</td>\n",
       "      <td>48.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1544.0</td>\n",
       "      <td>2599.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>4670.0</td>\n",
       "      <td>4076.0</td>\n",
       "      <td>3909.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>208.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1998.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4833.0</td>\n",
       "      <td>1544.0</td>\n",
       "      <td>468.0</td>\n",
       "      <td>1074.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "carrier      9E      AA    AS      B6      DL      EV    F9     FL    HA  \\\n",
       "month                                                                      \n",
       "1        1498.0  2735.0  62.0  4418.0  3661.0  3989.0  59.0  324.0  31.0   \n",
       "2        1353.0  2405.0  54.0  3950.0  3312.0  3558.0  48.0  282.0  28.0   \n",
       "3        1514.0  2746.0  62.0  4753.0  4131.0  4387.0  57.0  305.0  31.0   \n",
       "4        1407.0  2663.0  60.0  4468.0  4089.0  4345.0  57.0  305.0  30.0   \n",
       "5        1388.0  2770.0  62.0  4543.0  4076.0  4586.0  58.0  322.0  31.0   \n",
       "6        1276.0  2700.0  60.0  4572.0  4090.0  4106.0  55.0  243.0  30.0   \n",
       "7        1364.0  2797.0  62.0  4924.0  4201.0  4341.0  58.0  252.0  31.0   \n",
       "8        1378.0  2830.0  62.0  4947.0  4313.0  4354.0  55.0  256.0  31.0   \n",
       "9        1477.0  2584.0  60.0  4279.0  3874.0  4572.0  57.0  254.0  25.0   \n",
       "10       1642.0  2706.0  62.0  4359.0  4091.0  4820.0  57.0  234.0  21.0   \n",
       "11       1575.0  2558.0  52.0  4286.0  3847.0  4389.0  60.0  202.0  25.0   \n",
       "12       1544.0  2599.0  54.0  4670.0  4076.0  3909.0  61.0  208.0  28.0   \n",
       "\n",
       "carrier      MQ    OO      UA      US     VX      WN    YV  \n",
       "month                                                       \n",
       "1        2206.0   1.0  4605.0  1555.0  315.0   985.0  39.0  \n",
       "2        1904.0   NaN  4166.0  1462.0  261.0   861.0  46.0  \n",
       "3        2137.0   NaN  4926.0  1648.0  303.0   955.0  18.0  \n",
       "4        2100.0   NaN  4992.0  1678.0  466.0   966.0  36.0  \n",
       "5        2212.0   NaN  4910.0  1747.0  496.0   988.0  44.0  \n",
       "6        1998.0   2.0  4910.0  1655.0  480.0  1018.0  39.0  \n",
       "7        2132.0   NaN  5000.0  1703.0  486.0  1065.0  69.0  \n",
       "8        2168.0   4.0  5106.0  1741.0  489.0  1047.0  60.0  \n",
       "9        2111.0  17.0  4653.0  1657.0  453.0  1007.0  42.0  \n",
       "10       2186.0   NaN  5040.0  1815.0  471.0  1089.0  60.0  \n",
       "11       2011.0   5.0  4838.0  1668.0  443.0  1028.0  48.0  \n",
       "12       1998.0   NaN  4833.0  1544.0  468.0  1074.0  44.0  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# number of flights total by month and carrier\n",
    "df.groupby(['month','carrier'])['dep_time'].aggregate('count').unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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>carrier</th>\n",
       "      <th>9E</th>\n",
       "      <th>AA</th>\n",
       "      <th>AS</th>\n",
       "      <th>B6</th>\n",
       "      <th>DL</th>\n",
       "      <th>EV</th>\n",
       "      <th>F9</th>\n",
       "      <th>FL</th>\n",
       "      <th>HA</th>\n",
       "      <th>MQ</th>\n",
       "      <th>OO</th>\n",
       "      <th>UA</th>\n",
       "      <th>US</th>\n",
       "      <th>VX</th>\n",
       "      <th>WN</th>\n",
       "      <th>YV</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1498.0</td>\n",
       "      <td>2735.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>4418.0</td>\n",
       "      <td>3661.0</td>\n",
       "      <td>3989.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>324.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2206.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4605.0</td>\n",
       "      <td>1555.0</td>\n",
       "      <td>315.0</td>\n",
       "      <td>985.0</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1353.0</td>\n",
       "      <td>2405.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>3950.0</td>\n",
       "      <td>3312.0</td>\n",
       "      <td>3558.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>282.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1904.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4166.0</td>\n",
       "      <td>1462.0</td>\n",
       "      <td>261.0</td>\n",
       "      <td>861.0</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1514.0</td>\n",
       "      <td>2746.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>4753.0</td>\n",
       "      <td>4131.0</td>\n",
       "      <td>4387.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>305.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2137.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4926.0</td>\n",
       "      <td>1648.0</td>\n",
       "      <td>303.0</td>\n",
       "      <td>955.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1407.0</td>\n",
       "      <td>2663.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>4468.0</td>\n",
       "      <td>4089.0</td>\n",
       "      <td>4345.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>305.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4992.0</td>\n",
       "      <td>1678.0</td>\n",
       "      <td>466.0</td>\n",
       "      <td>966.0</td>\n",
       "      <td>36.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1388.0</td>\n",
       "      <td>2770.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>4543.0</td>\n",
       "      <td>4076.0</td>\n",
       "      <td>4586.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>322.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2212.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4910.0</td>\n",
       "      <td>1747.0</td>\n",
       "      <td>496.0</td>\n",
       "      <td>988.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1276.0</td>\n",
       "      <td>2700.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>4572.0</td>\n",
       "      <td>4090.0</td>\n",
       "      <td>4106.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>243.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1998.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4910.0</td>\n",
       "      <td>1655.0</td>\n",
       "      <td>480.0</td>\n",
       "      <td>1018.0</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1364.0</td>\n",
       "      <td>2797.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>4924.0</td>\n",
       "      <td>4201.0</td>\n",
       "      <td>4341.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>252.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2132.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>1703.0</td>\n",
       "      <td>486.0</td>\n",
       "      <td>1065.0</td>\n",
       "      <td>69.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1378.0</td>\n",
       "      <td>2830.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>4947.0</td>\n",
       "      <td>4313.0</td>\n",
       "      <td>4354.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>256.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2168.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5106.0</td>\n",
       "      <td>1741.0</td>\n",
       "      <td>489.0</td>\n",
       "      <td>1047.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1477.0</td>\n",
       "      <td>2584.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>4279.0</td>\n",
       "      <td>3874.0</td>\n",
       "      <td>4572.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>254.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>2111.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>4653.0</td>\n",
       "      <td>1657.0</td>\n",
       "      <td>453.0</td>\n",
       "      <td>1007.0</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1642.0</td>\n",
       "      <td>2706.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>4359.0</td>\n",
       "      <td>4091.0</td>\n",
       "      <td>4820.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>234.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>2186.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5040.0</td>\n",
       "      <td>1815.0</td>\n",
       "      <td>471.0</td>\n",
       "      <td>1089.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1575.0</td>\n",
       "      <td>2558.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>4286.0</td>\n",
       "      <td>3847.0</td>\n",
       "      <td>4389.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>202.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4838.0</td>\n",
       "      <td>1668.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>1028.0</td>\n",
       "      <td>48.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1544.0</td>\n",
       "      <td>2599.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>4670.0</td>\n",
       "      <td>4076.0</td>\n",
       "      <td>3909.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>208.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1998.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4833.0</td>\n",
       "      <td>1544.0</td>\n",
       "      <td>468.0</td>\n",
       "      <td>1074.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "carrier      9E      AA    AS      B6      DL      EV    F9     FL    HA  \\\n",
       "month                                                                      \n",
       "1        1498.0  2735.0  62.0  4418.0  3661.0  3989.0  59.0  324.0  31.0   \n",
       "2        1353.0  2405.0  54.0  3950.0  3312.0  3558.0  48.0  282.0  28.0   \n",
       "3        1514.0  2746.0  62.0  4753.0  4131.0  4387.0  57.0  305.0  31.0   \n",
       "4        1407.0  2663.0  60.0  4468.0  4089.0  4345.0  57.0  305.0  30.0   \n",
       "5        1388.0  2770.0  62.0  4543.0  4076.0  4586.0  58.0  322.0  31.0   \n",
       "6        1276.0  2700.0  60.0  4572.0  4090.0  4106.0  55.0  243.0  30.0   \n",
       "7        1364.0  2797.0  62.0  4924.0  4201.0  4341.0  58.0  252.0  31.0   \n",
       "8        1378.0  2830.0  62.0  4947.0  4313.0  4354.0  55.0  256.0  31.0   \n",
       "9        1477.0  2584.0  60.0  4279.0  3874.0  4572.0  57.0  254.0  25.0   \n",
       "10       1642.0  2706.0  62.0  4359.0  4091.0  4820.0  57.0  234.0  21.0   \n",
       "11       1575.0  2558.0  52.0  4286.0  3847.0  4389.0  60.0  202.0  25.0   \n",
       "12       1544.0  2599.0  54.0  4670.0  4076.0  3909.0  61.0  208.0  28.0   \n",
       "\n",
       "carrier      MQ    OO      UA      US     VX      WN    YV  \n",
       "month                                                       \n",
       "1        2206.0   1.0  4605.0  1555.0  315.0   985.0  39.0  \n",
       "2        1904.0   NaN  4166.0  1462.0  261.0   861.0  46.0  \n",
       "3        2137.0   NaN  4926.0  1648.0  303.0   955.0  18.0  \n",
       "4        2100.0   NaN  4992.0  1678.0  466.0   966.0  36.0  \n",
       "5        2212.0   NaN  4910.0  1747.0  496.0   988.0  44.0  \n",
       "6        1998.0   2.0  4910.0  1655.0  480.0  1018.0  39.0  \n",
       "7        2132.0   NaN  5000.0  1703.0  486.0  1065.0  69.0  \n",
       "8        2168.0   4.0  5106.0  1741.0  489.0  1047.0  60.0  \n",
       "9        2111.0  17.0  4653.0  1657.0  453.0  1007.0  42.0  \n",
       "10       2186.0   NaN  5040.0  1815.0  471.0  1089.0  60.0  \n",
       "11       2011.0   5.0  4838.0  1668.0  443.0  1028.0  48.0  \n",
       "12       1998.0   NaN  4833.0  1544.0  468.0  1074.0  44.0  "
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index = 'month', columns = 'carrier', values = 'dep_time', aggfunc = 'count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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>carrier</th>\n",
       "      <th>9E</th>\n",
       "      <th>AA</th>\n",
       "      <th>AS</th>\n",
       "      <th>B6</th>\n",
       "      <th>DL</th>\n",
       "      <th>EV</th>\n",
       "      <th>F9</th>\n",
       "      <th>FL</th>\n",
       "      <th>HA</th>\n",
       "      <th>MQ</th>\n",
       "      <th>OO</th>\n",
       "      <th>UA</th>\n",
       "      <th>US</th>\n",
       "      <th>VX</th>\n",
       "      <th>WN</th>\n",
       "      <th>YV</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.383178</td>\n",
       "      <td>0.330530</td>\n",
       "      <td>0.370968</td>\n",
       "      <td>0.392485</td>\n",
       "      <td>0.217973</td>\n",
       "      <td>0.514415</td>\n",
       "      <td>0.237288</td>\n",
       "      <td>0.234568</td>\n",
       "      <td>0.354839</td>\n",
       "      <td>0.255213</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.449511</td>\n",
       "      <td>0.224437</td>\n",
       "      <td>0.282540</td>\n",
       "      <td>0.394924</td>\n",
       "      <td>0.384615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.377679</td>\n",
       "      <td>0.350104</td>\n",
       "      <td>0.296296</td>\n",
       "      <td>0.454937</td>\n",
       "      <td>0.263285</td>\n",
       "      <td>0.508151</td>\n",
       "      <td>0.312500</td>\n",
       "      <td>0.418440</td>\n",
       "      <td>0.321429</td>\n",
       "      <td>0.294118</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.427508</td>\n",
       "      <td>0.198358</td>\n",
       "      <td>0.398467</td>\n",
       "      <td>0.447154</td>\n",
       "      <td>0.347826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.355350</td>\n",
       "      <td>0.319374</td>\n",
       "      <td>0.306452</td>\n",
       "      <td>0.434462</td>\n",
       "      <td>0.306947</td>\n",
       "      <td>0.533622</td>\n",
       "      <td>0.350877</td>\n",
       "      <td>0.524590</td>\n",
       "      <td>0.354839</td>\n",
       "      <td>0.280767</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.460414</td>\n",
       "      <td>0.236044</td>\n",
       "      <td>0.491749</td>\n",
       "      <td>0.516230</td>\n",
       "      <td>0.611111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.334755</td>\n",
       "      <td>0.295156</td>\n",
       "      <td>0.350000</td>\n",
       "      <td>0.422784</td>\n",
       "      <td>0.284911</td>\n",
       "      <td>0.461910</td>\n",
       "      <td>0.315789</td>\n",
       "      <td>0.452459</td>\n",
       "      <td>0.133333</td>\n",
       "      <td>0.332857</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.447917</td>\n",
       "      <td>0.240167</td>\n",
       "      <td>0.495708</td>\n",
       "      <td>0.469979</td>\n",
       "      <td>0.583333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.461816</td>\n",
       "      <td>0.303610</td>\n",
       "      <td>0.306452</td>\n",
       "      <td>0.335021</td>\n",
       "      <td>0.336114</td>\n",
       "      <td>0.493022</td>\n",
       "      <td>0.724138</td>\n",
       "      <td>0.481366</td>\n",
       "      <td>0.161290</td>\n",
       "      <td>0.341772</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.490428</td>\n",
       "      <td>0.238122</td>\n",
       "      <td>0.530242</td>\n",
       "      <td>0.575911</td>\n",
       "      <td>0.522727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.535266</td>\n",
       "      <td>0.357037</td>\n",
       "      <td>0.350000</td>\n",
       "      <td>0.433071</td>\n",
       "      <td>0.442298</td>\n",
       "      <td>0.484657</td>\n",
       "      <td>0.636364</td>\n",
       "      <td>0.646091</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.413914</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.560692</td>\n",
       "      <td>0.293656</td>\n",
       "      <td>0.575000</td>\n",
       "      <td>0.629666</td>\n",
       "      <td>0.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.549120</td>\n",
       "      <td>0.364676</td>\n",
       "      <td>0.387097</td>\n",
       "      <td>0.517872</td>\n",
       "      <td>0.459652</td>\n",
       "      <td>0.497812</td>\n",
       "      <td>0.896552</td>\n",
       "      <td>0.650794</td>\n",
       "      <td>0.064516</td>\n",
       "      <td>0.437617</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.560400</td>\n",
       "      <td>0.307692</td>\n",
       "      <td>0.584362</td>\n",
       "      <td>0.640376</td>\n",
       "      <td>0.449275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.463716</td>\n",
       "      <td>0.297173</td>\n",
       "      <td>0.274194</td>\n",
       "      <td>0.440469</td>\n",
       "      <td>0.358683</td>\n",
       "      <td>0.400322</td>\n",
       "      <td>0.836364</td>\n",
       "      <td>0.621094</td>\n",
       "      <td>0.193548</td>\n",
       "      <td>0.330258</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>0.493145</td>\n",
       "      <td>0.253877</td>\n",
       "      <td>0.331288</td>\n",
       "      <td>0.644699</td>\n",
       "      <td>0.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.303995</td>\n",
       "      <td>0.274381</td>\n",
       "      <td>0.116667</td>\n",
       "      <td>0.278102</td>\n",
       "      <td>0.237223</td>\n",
       "      <td>0.292651</td>\n",
       "      <td>0.508772</td>\n",
       "      <td>0.429134</td>\n",
       "      <td>0.080000</td>\n",
       "      <td>0.263382</td>\n",
       "      <td>0.058824</td>\n",
       "      <td>0.335053</td>\n",
       "      <td>0.184671</td>\n",
       "      <td>0.313466</td>\n",
       "      <td>0.479643</td>\n",
       "      <td>0.380952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0.357491</td>\n",
       "      <td>0.247598</td>\n",
       "      <td>0.177419</td>\n",
       "      <td>0.238587</td>\n",
       "      <td>0.249083</td>\n",
       "      <td>0.366598</td>\n",
       "      <td>0.421053</td>\n",
       "      <td>0.598291</td>\n",
       "      <td>0.095238</td>\n",
       "      <td>0.250229</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.375992</td>\n",
       "      <td>0.183471</td>\n",
       "      <td>0.309979</td>\n",
       "      <td>0.479339</td>\n",
       "      <td>0.316667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0.304127</td>\n",
       "      <td>0.239640</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>0.262016</td>\n",
       "      <td>0.229010</td>\n",
       "      <td>0.360219</td>\n",
       "      <td>0.466667</td>\n",
       "      <td>0.628713</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.243163</td>\n",
       "      <td>0.600000</td>\n",
       "      <td>0.381356</td>\n",
       "      <td>0.226019</td>\n",
       "      <td>0.356659</td>\n",
       "      <td>0.486381</td>\n",
       "      <td>0.354167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0.480570</td>\n",
       "      <td>0.421316</td>\n",
       "      <td>0.648148</td>\n",
       "      <td>0.508779</td>\n",
       "      <td>0.407753</td>\n",
       "      <td>0.534664</td>\n",
       "      <td>0.295082</td>\n",
       "      <td>0.725962</td>\n",
       "      <td>0.214286</td>\n",
       "      <td>0.392893</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.646803</td>\n",
       "      <td>0.297927</td>\n",
       "      <td>0.472222</td>\n",
       "      <td>0.712291</td>\n",
       "      <td>0.409091</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "carrier        9E        AA        AS        B6        DL        EV        F9  \\\n",
       "month                                                                           \n",
       "1        0.383178  0.330530  0.370968  0.392485  0.217973  0.514415  0.237288   \n",
       "2        0.377679  0.350104  0.296296  0.454937  0.263285  0.508151  0.312500   \n",
       "3        0.355350  0.319374  0.306452  0.434462  0.306947  0.533622  0.350877   \n",
       "4        0.334755  0.295156  0.350000  0.422784  0.284911  0.461910  0.315789   \n",
       "5        0.461816  0.303610  0.306452  0.335021  0.336114  0.493022  0.724138   \n",
       "6        0.535266  0.357037  0.350000  0.433071  0.442298  0.484657  0.636364   \n",
       "7        0.549120  0.364676  0.387097  0.517872  0.459652  0.497812  0.896552   \n",
       "8        0.463716  0.297173  0.274194  0.440469  0.358683  0.400322  0.836364   \n",
       "9        0.303995  0.274381  0.116667  0.278102  0.237223  0.292651  0.508772   \n",
       "10       0.357491  0.247598  0.177419  0.238587  0.249083  0.366598  0.421053   \n",
       "11       0.304127  0.239640  0.250000  0.262016  0.229010  0.360219  0.466667   \n",
       "12       0.480570  0.421316  0.648148  0.508779  0.407753  0.534664  0.295082   \n",
       "\n",
       "carrier        FL        HA        MQ        OO        UA        US        VX  \\\n",
       "month                                                                           \n",
       "1        0.234568  0.354839  0.255213  1.000000  0.449511  0.224437  0.282540   \n",
       "2        0.418440  0.321429  0.294118       NaN  0.427508  0.198358  0.398467   \n",
       "3        0.524590  0.354839  0.280767       NaN  0.460414  0.236044  0.491749   \n",
       "4        0.452459  0.133333  0.332857       NaN  0.447917  0.240167  0.495708   \n",
       "5        0.481366  0.161290  0.341772       NaN  0.490428  0.238122  0.530242   \n",
       "6        0.646091  0.200000  0.413914  0.500000  0.560692  0.293656  0.575000   \n",
       "7        0.650794  0.064516  0.437617       NaN  0.560400  0.307692  0.584362   \n",
       "8        0.621094  0.193548  0.330258  0.750000  0.493145  0.253877  0.331288   \n",
       "9        0.429134  0.080000  0.263382  0.058824  0.335053  0.184671  0.313466   \n",
       "10       0.598291  0.095238  0.250229       NaN  0.375992  0.183471  0.309979   \n",
       "11       0.628713  0.200000  0.243163  0.600000  0.381356  0.226019  0.356659   \n",
       "12       0.725962  0.214286  0.392893       NaN  0.646803  0.297927  0.472222   \n",
       "\n",
       "carrier        WN        YV  \n",
       "month                        \n",
       "1        0.394924  0.384615  \n",
       "2        0.447154  0.347826  \n",
       "3        0.516230  0.611111  \n",
       "4        0.469979  0.583333  \n",
       "5        0.575911  0.522727  \n",
       "6        0.629666  0.666667  \n",
       "7        0.640376  0.449275  \n",
       "8        0.644699  0.333333  \n",
       "9        0.479643  0.380952  \n",
       "10       0.479339  0.316667  \n",
       "11       0.486381  0.354167  \n",
       "12       0.712291  0.409091  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[ df['dep_delay'] > 0].pivot_table(index = 'month', columns = 'carrier', values = 'dep_time', aggfunc = 'count') / \\\n",
    "df.pivot_table(index = 'month', columns = 'carrier', values = 'dep_time', aggfunc = 'count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "month\n",
       "1     0.364838\n",
       "2     0.385141\n",
       "3     0.400708\n",
       "4     0.381137\n",
       "5     0.399922\n",
       "6     0.464677\n",
       "7     0.488292\n",
       "8     0.406123\n",
       "9     0.288142\n",
       "10    0.304401\n",
       "11    0.304753\n",
       "12    0.499816\n",
       "Name: dep_delay, dtype: float64"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['dep_delay'] > 0].groupby('month')['dep_delay'].count() / df.groupby('month')['dep_delay'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "carrier\n",
       "F9    0.325991\n",
       "FL    0.285669\n",
       "EV    0.280191\n",
       "YV    0.273897\n",
       "OO    0.241379\n",
       "MQ    0.230179\n",
       "B6    0.225832\n",
       "9E    0.220076\n",
       "WN    0.209565\n",
       "UA    0.184746\n",
       "AA    0.160046\n",
       "VX    0.157936\n",
       "DL    0.153447\n",
       "US    0.147698\n",
       "AS    0.118477\n",
       "HA    0.093567\n",
       "Name: arr_delay, dtype: float64"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "( df[df['arr_delay'] > 20].groupby('carrier')['arr_delay'].count() / df.groupby('carrier')['arr_delay'].count() ).sort_values(ascending = False)"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}