FlightDelays.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import folium as fol\n",
    "import pickle\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "airports_loc_pairs = pd.read_pickle('data/airport_coordinates.pickle')\n",
    "\n",
    "airports_loc = pd.DataFrame([[iata, loc[0], loc[1]] for iata, loc in airports_loc_pairs.items() if loc], columns=['Code', 'Lat', 'Lng'])\n",
    "airports_loc = airports_loc.set_index('Code')\n",
    "airports_loc\n",
    "\n",
    "airlines = pd.read_csv('data/L_AIRLINE_ID.csv', index_col='Code')\n",
    "airports = pd.read_csv('data/L_AIRPORT.csv', index_col='Code')\n",
    "\n",
    "airlines_splitted = airlines.Description.apply(lambda d: d.split(': '))\n",
    "airlines['CarrierName'] = airlines_splitted.apply(lambda d: d[0])\n",
    "airlines['CarrierCode'] = airlines_splitted.apply(lambda d: d[-1])\n",
    "airlines = airlines.drop('Description', axis=1)\n",
    "\n",
    "airports_splitted = airports.Description.apply(lambda d: d.split(': '))\n",
    "airports['AirportLocation'] = airports_splitted.apply(lambda d: d[0])\n",
    "airports['AirportName'] = airports_splitted.apply(lambda d: d[-1])\n",
    "airports = airports.merge(airports_loc, how='left', left_index=True, right_index=True)\n",
    "airports = airports.drop('Description', axis=1)\n",
    "\n",
    "\n",
    "def getMonthlyDelays(month,year):\n",
    "    data = pd.read_csv('data/On_Time_On_Time_Performance_2017_1.csv')\n",
    "    data = data.merge(airlines, how='left', left_on='AirlineID', right_on='Code', right_index=True)\n",
    "    #data = data[[\n",
    "    #    'Year', 'Month', 'DayofMonth', 'DayOfWeek',\n",
    "    #    'CarrierName', 'CarrierCode', 'FlightNum', 'TailNum',\n",
    "    #    'Origin', 'Dest',\n",
    "    #    'CRSDepTime', 'DepTime', 'DepDelayMinutes', 'CRSArrTime', 'ArrTime', 'ArrDelay',\n",
    "    #    'CRSElapsedTime', 'ActualElapsedTime', 'Distance',\n",
    "    #    'Cancelled', 'CancellationCode', 'Diverted',\n",
    "    #    'CarrierDelay', 'WeatherDelay', 'NASDelay', 'SecurityDelay', 'LateAircraftDelay'\n",
    "    #]]\n",
    "    data['Flights'] = 1\n",
    "\n",
    "    #airports_delay = data[['DepDelayMinutes', 'Origin']].groupby('Origin').quantile(q=0.9)\n",
    "    #airports_flights = data[['Flights', 'Origin']].groupby('Origin').sum()\n",
    "    #airports_delay = airports_delay.merge(airports_flights, how='left', left_index=True, right_index=True)\n",
    "    #airports_delay = airports_delay.merge(airports_loc, how='left', left_index=True, right_index=True)\n",
    "    ##airports_delay.to_csv('airports-delay.csv')\n",
    "    \n",
    "    airport_delay_causes = data[['Origin', 'DayofMonth', 'CarrierDelay', 'WeatherDelay', 'NASDelay', 'SecurityDelay', 'LateAircraftDelay']].copy()\n",
    "    airport_delay_causes['UnknownDelay'] = -data['DepDelayMinutes']\n",
    "    airport_delay_causes = airport_delay_causes.set_index(['Origin', 'DayofMonth'])\n",
    "    airport_delay_causes = airport_delay_causes.fillna(0)\n",
    "    airport_delay_causes = airport_delay_causes.groupby(['Origin', 'DayofMonth']).sum()\n",
    "    airport_delay_causes['UnknownDelay'] = -airport_delay_causes.sum(axis=1)\n",
    "    airport_delay_causes['UnknownDelay'][airport_delay_causes['UnknownDelay'] < 0] = 0\n",
    "    airport_delay_causes = airport_delay_causes.sort_index()\n",
    "\n",
    "    return airport_delay_causes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/cranium/.anaconda3/envs/ada/lib/python3.5/site-packages/IPython/core/interactiveshell.py:2850: DtypeWarning: Columns (48,77,84) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  if self.run_code(code, result):\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Monthly delays for 1-1987 done (3.3743104934692383)\n",
      "Monthly delays for 2-1987 done (3.3270888328552246)\n",
      "Monthly delays for 3-1987 done (3.308990240097046)\n",
      "Monthly delays for 4-1987 done (3.1677727699279785)\n",
      "Monthly delays for 5-1987 done (3.31842041015625)\n",
      "Monthly delays for 6-1987 done (3.2278389930725098)\n",
      "Monthly delays for 7-1987 done (3.457570791244507)\n",
      "Monthly delays for 8-1987 done (3.399739980697632)\n",
      "Monthly delays for 9-1987 done (3.460057020187378)\n",
      "Monthly delays for 10-1987 done (3.3790247440338135)\n",
      "Monthly delays for 11-1987 done (3.2348861694335938)\n",
      "Monthly delays for 12-1987 done (3.272721767425537)\n",
      "Monthly delays for 1-1988 done (3.3051557540893555)\n",
      "Monthly delays for 2-1988 done (3.2428600788116455)\n",
      "Monthly delays for 3-1988 done (3.2444212436676025)\n",
      "Monthly delays for 4-1988 done (3.354300022125244)\n",
      "Monthly delays for 5-1988 done (3.5912845134735107)\n",
      "Monthly delays for 6-1988 done (3.233557939529419)\n",
      "Monthly delays for 7-1988 done (3.3826799392700195)\n",
      "Monthly delays for 8-1988 done (3.1300876140594482)\n",
      "Monthly delays for 9-1988 done (3.1156063079833984)\n",
      "Monthly delays for 10-1988 done (3.245497703552246)\n",
      "Monthly delays for 11-1988 done (3.3271214962005615)\n",
      "Monthly delays for 12-1988 done (3.189328908920288)\n",
      "Monthly delays for 1-1989 done (3.180471420288086)\n",
      "Monthly delays for 2-1989 done (3.249309778213501)\n",
      "Monthly delays for 3-1989 done (3.283322811126709)\n",
      "Monthly delays for 4-1989 done (3.403571128845215)\n",
      "Monthly delays for 5-1989 done (3.125046491622925)\n",
      "Monthly delays for 6-1989 done (3.134646415710449)\n",
      "Monthly delays for 7-1989 done (3.28448486328125)\n",
      "Monthly delays for 8-1989 done (3.2219207286834717)\n",
      "Monthly delays for 9-1989 done (3.095900297164917)\n",
      "Monthly delays for 10-1989 done (3.0980653762817383)\n",
      "Monthly delays for 11-1989 done (3.1778371334075928)\n",
      "Monthly delays for 12-1989 done (3.2823526859283447)\n",
      "Monthly delays for 1-1990 done (3.1912639141082764)\n",
      "Monthly delays for 2-1990 done (3.1912429332733154)\n",
      "Monthly delays for 3-1990 done (3.301215887069702)\n",
      "Monthly delays for 4-1990 done (3.2978198528289795)\n",
      "Monthly delays for 5-1990 done (3.248692274093628)\n",
      "Monthly delays for 6-1990 done (3.4323203563690186)\n",
      "Monthly delays for 7-1990 done (3.2074596881866455)\n",
      "Monthly delays for 8-1990 done (3.2041914463043213)\n",
      "Monthly delays for 9-1990 done (3.226194143295288)\n",
      "Monthly delays for 10-1990 done (3.3176944255828857)\n",
      "Monthly delays for 11-1990 done (3.178342342376709)\n",
      "Monthly delays for 12-1990 done (3.3083038330078125)\n",
      "Monthly delays for 1-1991 done (3.2395567893981934)\n",
      "Monthly delays for 2-1991 done (3.168407440185547)\n",
      "Monthly delays for 3-1991 done (3.349508047103882)\n",
      "Monthly delays for 4-1991 done (3.1594536304473877)\n",
      "Monthly delays for 5-1991 done (3.2575857639312744)\n",
      "Monthly delays for 6-1991 done (3.1623783111572266)\n",
      "Monthly delays for 7-1991 done (3.235811710357666)\n",
      "Monthly delays for 8-1991 done (3.1527299880981445)\n",
      "Monthly delays for 9-1991 done (3.204005479812622)\n",
      "Monthly delays for 10-1991 done (3.281656265258789)\n",
      "Monthly delays for 11-1991 done (3.160356283187866)\n",
      "Monthly delays for 12-1991 done (3.5019097328186035)\n",
      "Monthly delays for 1-1992 done (3.1182172298431396)\n",
      "Monthly delays for 2-1992 done (3.114300489425659)\n",
      "Monthly delays for 3-1992 done (3.1507437229156494)\n",
      "Monthly delays for 4-1992 done (3.1879470348358154)\n",
      "Monthly delays for 5-1992 done (3.357455015182495)\n",
      "Monthly delays for 6-1992 done (3.1463623046875)\n",
      "Monthly delays for 7-1992 done (3.1929726600646973)\n",
      "Monthly delays for 8-1992 done (3.2802271842956543)\n",
      "Monthly delays for 9-1992 done (3.150724411010742)\n",
      "Monthly delays for 10-1992 done (3.428889751434326)\n",
      "Monthly delays for 11-1992 done (3.711682081222534)\n",
      "Monthly delays for 12-1992 done (3.8262858390808105)\n",
      "Monthly delays for 1-1993 done (3.4777743816375732)\n",
      "Monthly delays for 2-1993 done (3.453976631164551)\n",
      "Monthly delays for 3-1993 done (3.501044988632202)\n",
      "Monthly delays for 4-1993 done (3.2716429233551025)\n",
      "Monthly delays for 5-1993 done (3.4458487033843994)\n",
      "Monthly delays for 6-1993 done (3.1496260166168213)\n",
      "Monthly delays for 7-1993 done (3.2138047218322754)\n",
      "Monthly delays for 8-1993 done (3.439289093017578)\n",
      "Monthly delays for 9-1993 done (3.110816717147827)\n",
      "Monthly delays for 10-1993 done (3.273238182067871)\n",
      "Monthly delays for 11-1993 done (3.354703187942505)\n",
      "Monthly delays for 12-1993 done (3.267900228500366)\n",
      "Monthly delays for 1-1994 done (3.4940319061279297)\n",
      "Monthly delays for 2-1994 done (3.361492872238159)\n",
      "Monthly delays for 3-1994 done (3.3335227966308594)\n",
      "Monthly delays for 4-1994 done (3.389195442199707)\n",
      "Monthly delays for 5-1994 done (3.300764799118042)\n",
      "Monthly delays for 6-1994 done (3.173650026321411)\n",
      "Monthly delays for 7-1994 done (3.210028886795044)\n",
      "Monthly delays for 8-1994 done (3.2242815494537354)\n",
      "Monthly delays for 9-1994 done (3.2265775203704834)\n",
      "Monthly delays for 10-1994 done (3.268341064453125)\n",
      "Monthly delays for 11-1994 done (3.549626111984253)\n",
      "Monthly delays for 12-1994 done (3.5546131134033203)\n",
      "Monthly delays for 1-1995 done (3.203482151031494)\n",
      "Monthly delays for 2-1995 done (3.197683811187744)\n",
      "Monthly delays for 3-1995 done (3.253797769546509)\n",
      "Monthly delays for 4-1995 done (3.397242784500122)\n",
      "Monthly delays for 5-1995 done (3.3239076137542725)\n",
      "Monthly delays for 6-1995 done (3.2379941940307617)\n",
      "Monthly delays for 7-1995 done (3.6068296432495117)\n",
      "Monthly delays for 8-1995 done (3.610048294067383)\n",
      "Monthly delays for 9-1995 done (3.349473714828491)\n",
      "Monthly delays for 10-1995 done (3.397674083709717)\n",
      "Monthly delays for 11-1995 done (3.228252649307251)\n",
      "Monthly delays for 12-1995 done (3.589292526245117)\n",
      "Monthly delays for 1-1996 done (3.5314738750457764)\n",
      "Monthly delays for 2-1996 done (4.256078481674194)\n",
      "Monthly delays for 3-1996 done (4.20471715927124)\n",
      "Monthly delays for 4-1996 done (3.846311569213867)\n",
      "Monthly delays for 5-1996 done (3.430994987487793)\n",
      "Monthly delays for 6-1996 done (3.462744951248169)\n",
      "Monthly delays for 7-1996 done (3.15836763381958)\n",
      "Monthly delays for 8-1996 done (3.1675565242767334)\n",
      "Monthly delays for 9-1996 done (3.3560469150543213)\n",
      "Monthly delays for 10-1996 done (3.6161155700683594)\n",
      "Monthly delays for 11-1996 done (3.2247884273529053)\n",
      "Monthly delays for 12-1996 done (3.212184429168701)\n",
      "Monthly delays for 1-1997 done (3.2108073234558105)\n",
      "Monthly delays for 2-1997 done (3.374915361404419)\n",
      "Monthly delays for 3-1997 done (3.283189535140991)\n",
      "Monthly delays for 4-1997 done (3.438528060913086)\n",
      "Monthly delays for 5-1997 done (3.2924857139587402)\n",
      "Monthly delays for 6-1997 done (3.3081624507904053)\n",
      "Monthly delays for 7-1997 done (3.2944319248199463)\n",
      "Monthly delays for 8-1997 done (3.638246536254883)\n",
      "Monthly delays for 9-1997 done (40.30360007286072)\n",
      "Monthly delays for 10-1997 done (3.3195440769195557)\n",
      "Monthly delays for 11-1997 done (4.378551244735718)\n",
      "Monthly delays for 12-1997 done (3.686570644378662)\n",
      "Monthly delays for 1-1998 done (3.458165168762207)\n",
      "Monthly delays for 2-1998 done (4.1059839725494385)\n",
      "Monthly delays for 3-1998 done (4.487982511520386)\n",
      "Monthly delays for 4-1998 done (4.689837455749512)\n",
      "Monthly delays for 5-1998 done (5.026516675949097)\n",
      "Monthly delays for 6-1998 done (4.81649923324585)\n",
      "Monthly delays for 7-1998 done (4.991456985473633)\n",
      "Monthly delays for 8-1998 done (5.14367151260376)\n",
      "Monthly delays for 9-1998 done (4.950998067855835)\n",
      "Monthly delays for 10-1998 done (5.145157337188721)\n",
      "Monthly delays for 11-1998 done (5.256120443344116)\n",
      "Monthly delays for 12-1998 done (4.900528907775879)\n",
      "Monthly delays for 1-1999 done (4.840139150619507)\n",
      "Monthly delays for 2-1999 done (4.871150255203247)\n",
      "Monthly delays for 3-1999 done (4.941980600357056)\n",
      "Monthly delays for 4-1999 done (5.1349873542785645)\n",
      "Monthly delays for 5-1999 done (4.680476665496826)\n",
      "Monthly delays for 6-1999 done (3.6694858074188232)\n",
      "Monthly delays for 7-1999 done (3.309898614883423)\n",
      "Monthly delays for 8-1999 done (4.287904739379883)\n",
      "Monthly delays for 9-1999 done (3.4186606407165527)\n",
      "Monthly delays for 10-1999 done (3.386075496673584)\n",
      "Monthly delays for 11-1999 done (3.555121421813965)\n",
      "Monthly delays for 12-1999 done (3.2713656425476074)\n",
      "Monthly delays for 1-2000 done (3.498016357421875)\n",
      "Monthly delays for 2-2000 done (3.3883578777313232)\n",
      "Monthly delays for 3-2000 done (3.3897221088409424)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Monthly delays for 4-2000 done (3.269148588180542)\n",
      "Monthly delays for 5-2000 done (3.4215261936187744)\n",
      "Monthly delays for 6-2000 done (3.553457021713257)\n",
      "Monthly delays for 7-2000 done (3.178581953048706)\n",
      "Monthly delays for 8-2000 done (3.11979341506958)\n",
      "Monthly delays for 9-2000 done (3.1757121086120605)\n",
      "Monthly delays for 10-2000 done (3.2317044734954834)\n",
      "Monthly delays for 11-2000 done (3.3572514057159424)\n",
      "Monthly delays for 12-2000 done (3.2062838077545166)\n",
      "Monthly delays for 1-2001 done (3.2488553524017334)\n",
      "Monthly delays for 2-2001 done (3.503779172897339)\n",
      "Monthly delays for 3-2001 done (3.28648042678833)\n",
      "Monthly delays for 4-2001 done (3.2259278297424316)\n",
      "Monthly delays for 5-2001 done (3.2621982097625732)\n",
      "Monthly delays for 6-2001 done (3.25985050201416)\n",
      "Monthly delays for 7-2001 done (3.3549551963806152)\n",
      "Monthly delays for 8-2001 done (3.318769931793213)\n",
      "Monthly delays for 9-2001 done (3.4429030418395996)\n",
      "Monthly delays for 10-2001 done (3.227583169937134)\n",
      "Monthly delays for 11-2001 done (3.290010929107666)\n",
      "Monthly delays for 12-2001 done (3.1470186710357666)\n",
      "Monthly delays for 1-2002 done (3.1329479217529297)\n",
      "Monthly delays for 2-2002 done (3.1416070461273193)\n",
      "Monthly delays for 3-2002 done (3.3476908206939697)\n",
      "Monthly delays for 4-2002 done (3.201726198196411)\n",
      "Monthly delays for 5-2002 done (3.158949851989746)\n",
      "Monthly delays for 6-2002 done (3.243873357772827)\n",
      "Monthly delays for 7-2002 done (3.164912700653076)\n",
      "Monthly delays for 8-2002 done (3.4812984466552734)\n",
      "Monthly delays for 9-2002 done (3.1359810829162598)\n",
      "Monthly delays for 10-2002 done (3.1282548904418945)\n",
      "Monthly delays for 11-2002 done (3.1687941551208496)\n",
      "Monthly delays for 12-2002 done (3.174684524536133)\n",
      "Monthly delays for 1-2003 done (3.237856149673462)\n",
      "Monthly delays for 2-2003 done (3.112107515335083)\n",
      "Monthly delays for 3-2003 done (3.259827136993408)\n",
      "Monthly delays for 4-2003 done (3.2346978187561035)\n",
      "Monthly delays for 5-2003 done (3.2595059871673584)\n",
      "Monthly delays for 6-2003 done (3.536968469619751)\n",
      "Monthly delays for 7-2003 done (3.2874433994293213)\n",
      "Monthly delays for 8-2003 done (3.7418715953826904)\n",
      "Monthly delays for 9-2003 done (5.041335344314575)\n",
      "Monthly delays for 10-2003 done (4.014037370681763)\n",
      "Monthly delays for 11-2003 done (3.2152292728424072)\n",
      "Monthly delays for 12-2003 done (3.2520861625671387)\n",
      "Monthly delays for 1-2004 done (3.222490072250366)\n",
      "Monthly delays for 2-2004 done (3.225621461868286)\n",
      "Monthly delays for 3-2004 done (3.180917263031006)\n",
      "Monthly delays for 4-2004 done (3.323718547821045)\n",
      "Monthly delays for 5-2004 done (3.2892112731933594)\n",
      "Monthly delays for 6-2004 done (3.2220263481140137)\n",
      "Monthly delays for 7-2004 done (3.4096591472625732)\n",
      "Monthly delays for 8-2004 done (3.246436357498169)\n",
      "Monthly delays for 9-2004 done (3.150136709213257)\n",
      "Monthly delays for 10-2004 done (3.1720616817474365)\n",
      "Monthly delays for 11-2004 done (3.137397527694702)\n",
      "Monthly delays for 12-2004 done (3.0785889625549316)\n",
      "Monthly delays for 1-2005 done (3.1637885570526123)\n",
      "Monthly delays for 2-2005 done (3.2168095111846924)\n",
      "Monthly delays for 3-2005 done (3.1685760021209717)\n",
      "Monthly delays for 4-2005 done (3.2559633255004883)\n",
      "Monthly delays for 5-2005 done (3.1951072216033936)\n",
      "Monthly delays for 6-2005 done (3.4582343101501465)\n",
      "Monthly delays for 7-2005 done (4.022105693817139)\n",
      "Monthly delays for 8-2005 done (3.333157777786255)\n",
      "Monthly delays for 9-2005 done (3.1723861694335938)\n",
      "Monthly delays for 10-2005 done (3.4329121112823486)\n",
      "Monthly delays for 11-2005 done (3.2298812866210938)\n",
      "Monthly delays for 12-2005 done (3.4621622562408447)\n",
      "Monthly delays for 1-2006 done (4.658438205718994)\n",
      "Monthly delays for 2-2006 done (4.121338367462158)\n",
      "Monthly delays for 3-2006 done (4.010645151138306)\n",
      "Monthly delays for 4-2006 done (3.4503893852233887)\n",
      "Monthly delays for 5-2006 done (3.2878775596618652)\n",
      "Monthly delays for 6-2006 done (3.7391273975372314)\n",
      "Monthly delays for 7-2006 done (3.5518341064453125)\n",
      "Monthly delays for 8-2006 done (3.7540066242218018)\n",
      "Monthly delays for 9-2006 done (3.255249261856079)\n",
      "Monthly delays for 10-2006 done (3.2412919998168945)\n",
      "Monthly delays for 11-2006 done (4.428071022033691)\n",
      "Monthly delays for 12-2006 done (4.724070310592651)\n",
      "Monthly delays for 1-2007 done (3.505908727645874)\n",
      "Monthly delays for 2-2007 done (3.293379545211792)\n",
      "Monthly delays for 3-2007 done (3.353026866912842)\n",
      "Monthly delays for 4-2007 done (3.1652655601501465)\n",
      "Monthly delays for 5-2007 done (3.2560949325561523)\n",
      "Monthly delays for 6-2007 done (3.1889777183532715)\n",
      "Monthly delays for 7-2007 done (3.2952442169189453)\n",
      "Monthly delays for 8-2007 done (3.176840305328369)\n",
      "Monthly delays for 9-2007 done (3.2505125999450684)\n",
      "Monthly delays for 10-2007 done (3.4104690551757812)\n",
      "Monthly delays for 11-2007 done (3.149331569671631)\n",
      "Monthly delays for 12-2007 done (3.1266796588897705)\n",
      "Monthly delays for 1-2008 done (3.15625)\n",
      "Monthly delays for 2-2008 done (3.217010974884033)\n",
      "Monthly delays for 3-2008 done (3.2055981159210205)\n",
      "Monthly delays for 4-2008 done (3.263906478881836)\n",
      "Monthly delays for 5-2008 done (3.2419943809509277)\n",
      "Monthly delays for 6-2008 done (3.1988461017608643)\n",
      "Monthly delays for 7-2008 done (3.238243579864502)\n",
      "Monthly delays for 8-2008 done (3.2381269931793213)\n",
      "Monthly delays for 9-2008 done (3.203542470932007)\n",
      "Monthly delays for 10-2008 done (3.231397867202759)\n",
      "Monthly delays for 11-2008 done (3.281118392944336)\n",
      "Monthly delays for 12-2008 done (3.169997215270996)\n",
      "Monthly delays for 1-2009 done (3.1670007705688477)\n",
      "Monthly delays for 2-2009 done (3.253676176071167)\n",
      "Monthly delays for 3-2009 done (3.305622100830078)\n",
      "Monthly delays for 4-2009 done (3.395296096801758)\n",
      "Monthly delays for 5-2009 done (3.224335193634033)\n",
      "Monthly delays for 6-2009 done (3.2908031940460205)\n",
      "Monthly delays for 7-2009 done (3.2319695949554443)\n",
      "Monthly delays for 8-2009 done (3.1637701988220215)\n",
      "Monthly delays for 9-2009 done (3.1834557056427)\n",
      "Monthly delays for 10-2009 done (3.3349671363830566)\n",
      "Monthly delays for 11-2009 done (3.2420334815979004)\n",
      "Monthly delays for 12-2009 done (3.15771746635437)\n",
      "Monthly delays for 1-2010 done (3.4054195880889893)\n",
      "Monthly delays for 2-2010 done (3.209853172302246)\n",
      "Monthly delays for 3-2010 done (3.2683427333831787)\n",
      "Monthly delays for 4-2010 done (3.1523828506469727)\n",
      "Monthly delays for 5-2010 done (3.2295210361480713)\n",
      "Monthly delays for 6-2010 done (3.2778451442718506)\n",
      "Monthly delays for 7-2010 done (3.169236183166504)\n",
      "Monthly delays for 8-2010 done (3.306950330734253)\n",
      "Monthly delays for 9-2010 done (3.2829809188842773)\n",
      "Monthly delays for 10-2010 done (3.45595645904541)\n",
      "Monthly delays for 11-2010 done (3.68025279045105)\n",
      "Monthly delays for 12-2010 done (3.4371368885040283)\n",
      "Monthly delays for 1-2011 done (3.5653951168060303)\n",
      "Monthly delays for 2-2011 done (3.294867753982544)\n",
      "Monthly delays for 3-2011 done (3.6349823474884033)\n",
      "Monthly delays for 4-2011 done (3.369342565536499)\n",
      "Monthly delays for 5-2011 done (3.299854040145874)\n",
      "Monthly delays for 6-2011 done (3.353022575378418)\n",
      "Monthly delays for 7-2011 done (3.2323262691497803)\n",
      "Monthly delays for 8-2011 done (3.1823832988739014)\n",
      "Monthly delays for 9-2011 done (3.2683207988739014)\n",
      "Monthly delays for 10-2011 done (3.420706272125244)\n",
      "Monthly delays for 11-2011 done (3.162118434906006)\n",
      "Monthly delays for 12-2011 done (3.182403802871704)\n",
      "Monthly delays for 1-2012 done (3.3145320415496826)\n",
      "Monthly delays for 2-2012 done (3.1516122817993164)\n",
      "Monthly delays for 3-2012 done (3.5470781326293945)\n",
      "Monthly delays for 4-2012 done (3.1423285007476807)\n",
      "Monthly delays for 5-2012 done (3.249680280685425)\n",
      "Monthly delays for 6-2012 done (3.249533176422119)\n",
      "Monthly delays for 7-2012 done (3.3505942821502686)\n",
      "Monthly delays for 8-2012 done (3.2085678577423096)\n",
      "Monthly delays for 9-2012 done (3.243913412094116)\n",
      "Monthly delays for 10-2012 done (3.2791659832000732)\n",
      "Monthly delays for 11-2012 done (3.251046657562256)\n",
      "Monthly delays for 12-2012 done (3.396798610687256)\n",
      "Monthly delays for 1-2013 done (3.5290560722351074)\n",
      "Monthly delays for 2-2013 done (3.40553617477417)\n",
      "Monthly delays for 3-2013 done (3.2169971466064453)\n",
      "Monthly delays for 4-2013 done (3.210707902908325)\n",
      "Monthly delays for 5-2013 done (3.262364149093628)\n",
      "Monthly delays for 6-2013 done (3.2972097396850586)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Monthly delays for 7-2013 done (3.239116907119751)\n",
      "Monthly delays for 8-2013 done (3.2796528339385986)\n",
      "Monthly delays for 9-2013 done (3.2371718883514404)\n",
      "Monthly delays for 10-2013 done (3.2715811729431152)\n",
      "Monthly delays for 11-2013 done (3.32541823387146)\n",
      "Monthly delays for 12-2013 done (3.2405755519866943)\n",
      "Monthly delays for 1-2014 done (3.7589330673217773)\n",
      "Monthly delays for 2-2014 done (3.6578948497772217)\n",
      "Monthly delays for 3-2014 done (3.516664505004883)\n",
      "Monthly delays for 4-2014 done (3.522472858428955)\n",
      "Monthly delays for 5-2014 done (3.180440664291382)\n",
      "Monthly delays for 6-2014 done (3.7094719409942627)\n",
      "Monthly delays for 7-2014 done (3.419642448425293)\n",
      "Monthly delays for 8-2014 done (3.397170066833496)\n",
      "Monthly delays for 9-2014 done (3.130364418029785)\n",
      "Monthly delays for 10-2014 done (3.199307918548584)\n",
      "Monthly delays for 11-2014 done (3.2078323364257812)\n",
      "Monthly delays for 12-2014 done (3.1506524085998535)\n",
      "Monthly delays for 1-2015 done (3.2418203353881836)\n",
      "Monthly delays for 2-2015 done (3.2893428802490234)\n",
      "Monthly delays for 3-2015 done (3.144861936569214)\n",
      "Monthly delays for 4-2015 done (3.122769355773926)\n",
      "Monthly delays for 5-2015 done (3.2502901554107666)\n",
      "Monthly delays for 6-2015 done (3.274045467376709)\n",
      "Monthly delays for 7-2015 done (3.43270206451416)\n",
      "Monthly delays for 8-2015 done (3.1077353954315186)\n",
      "Monthly delays for 9-2015 done (3.13041353225708)\n",
      "Monthly delays for 10-2015 done (3.138252019882202)\n",
      "Monthly delays for 11-2015 done (3.27103328704834)\n",
      "Monthly delays for 12-2015 done (3.3102757930755615)\n",
      "Monthly delays for 1-2016 done (3.359053373336792)\n",
      "Monthly delays for 2-2016 done (3.5600032806396484)\n",
      "Monthly delays for 3-2016 done (3.20210337638855)\n",
      "Monthly delays for 4-2016 done (3.519439220428467)\n",
      "Monthly delays for 5-2016 done (3.3715412616729736)\n",
      "Monthly delays for 6-2016 done (3.152113437652588)\n",
      "Monthly delays for 7-2016 done (3.2077548503875732)\n",
      "Monthly delays for 8-2016 done (3.1087663173675537)\n",
      "Monthly delays for 9-2016 done (3.2586145401000977)\n",
      "Monthly delays for 10-2016 done (3.1439154148101807)\n",
      "Monthly delays for 11-2016 done (3.321876287460327)\n",
      "Monthly delays for 12-2016 done (3.2134783267974854)\n",
      "Monthly delays for 1-2017 done (3.2590363025665283)\n",
      "Monthly delays for 2-2017 done (3.7278618812561035)\n",
      "Monthly delays for 3-2017 done (3.298006534576416)\n",
      "Monthly delays for 4-2017 done (3.2916574478149414)\n",
      "Monthly delays for 5-2017 done (3.781491994857788)\n",
      "Monthly delays for 6-2017 done (3.5283470153808594)\n",
      "Monthly delays for 7-2017 done (3.3563058376312256)\n",
      "Monthly delays for 8-2017 done (3.4274044036865234)\n",
      "Monthly delays for 9-2017 done (3.519413709640503)\n"
     ]
    }
   ],
   "source": [
    "import time\n",
    "import os\n",
    "\n",
    "date_range = []\n",
    "for year in range(1987, 2018):\n",
    "    for month in range(1, 13):\n",
    "        if not (year == 2017 and month >= 10):\n",
    "            date_range.append({'year':year,'month':month})\n",
    "\n",
    "\n",
    "for date in date_range:\n",
    "    dirname = \"delays/{year}/{month}/\".format(**date)\n",
    "    os.makedirs(dirname, exist_ok=True)\n",
    "    start = time.time()\n",
    "    mon = getMonthlyDelays(**date)\n",
    "    mon.to_csv(dirname+'airport_causes.csv'.format(**date))\n",
    "    print(\"Monthly delays for {month}-{year} done ({seconds})\".format(seconds=time.time()-start, **date))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Read data and filter columns\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/thierry/bin/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (48,77,84) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# of columns before: 112\n",
      "# of columns  after: 28\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>DayofMonth</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>CarrierName</th>\n",
       "      <th>CarrierCode</th>\n",
       "      <th>FlightNum</th>\n",
       "      <th>TailNum</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Dest</th>\n",
       "      <th>...</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Cancelled</th>\n",
       "      <th>CancellationCode</th>\n",
       "      <th>Diverted</th>\n",
       "      <th>CarrierDelay</th>\n",
       "      <th>WeatherDelay</th>\n",
       "      <th>NASDelay</th>\n",
       "      <th>SecurityDelay</th>\n",
       "      <th>LateAircraftDelay</th>\n",
       "      <th>Flights</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N583AA</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>...</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>3</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N544AA</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>...</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>4</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N553AA</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>...</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N191AA</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>...</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>21</td>\n",
       "      <td>6</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N170AA</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>...</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>7</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N179AA</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>...</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N579AA</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>...</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N583AA</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>...</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>3</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N167AA</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>...</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>26</td>\n",
       "      <td>4</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N551AA</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>...</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>27</td>\n",
       "      <td>5</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N919AA</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>...</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>6</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N542AA</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>...</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>7</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N559AA</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>...</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N921AA</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>...</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>2</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N972AA</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>...</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N185AA</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLT</td>\n",
       "      <td>...</td>\n",
       "      <td>632.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N970AA</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLT</td>\n",
       "      <td>...</td>\n",
       "      <td>632.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>5</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N199AA</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLT</td>\n",
       "      <td>...</td>\n",
       "      <td>632.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N919AA</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLT</td>\n",
       "      <td>...</td>\n",
       "      <td>632.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>3</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N190AA</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLT</td>\n",
       "      <td>...</td>\n",
       "      <td>632.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>4</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N553AA</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLT</td>\n",
       "      <td>...</td>\n",
       "      <td>632.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N191AA</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLT</td>\n",
       "      <td>...</td>\n",
       "      <td>632.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>21</td>\n",
       "      <td>6</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N170AA</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLT</td>\n",
       "      <td>...</td>\n",
       "      <td>632.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>7</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N179AA</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLT</td>\n",
       "      <td>...</td>\n",
       "      <td>632.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>3</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N167AA</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLT</td>\n",
       "      <td>...</td>\n",
       "      <td>632.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>26</td>\n",
       "      <td>4</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N551AA</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLT</td>\n",
       "      <td>...</td>\n",
       "      <td>632.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>27</td>\n",
       "      <td>5</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N919AA</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLT</td>\n",
       "      <td>...</td>\n",
       "      <td>632.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>6</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N510AA</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLT</td>\n",
       "      <td>...</td>\n",
       "      <td>632.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>7</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N559AA</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLT</td>\n",
       "      <td>...</td>\n",
       "      <td>632.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "      <td>AA</td>\n",
       "      <td>494</td>\n",
       "      <td>N554AA</td>\n",
       "      <td>PHX</td>\n",
       "      <td>LAS</td>\n",
       "      <td>...</td>\n",
       "      <td>255.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449987</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>1442</td>\n",
       "      <td>N415WN</td>\n",
       "      <td>CLE</td>\n",
       "      <td>MDW</td>\n",
       "      <td>...</td>\n",
       "      <td>307.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449988</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>503</td>\n",
       "      <td>N8322X</td>\n",
       "      <td>CLE</td>\n",
       "      <td>PHX</td>\n",
       "      <td>...</td>\n",
       "      <td>1737.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449989</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>477</td>\n",
       "      <td>N8650F</td>\n",
       "      <td>CLE</td>\n",
       "      <td>STL</td>\n",
       "      <td>...</td>\n",
       "      <td>487.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449990</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>138</td>\n",
       "      <td>N475WN</td>\n",
       "      <td>CLT</td>\n",
       "      <td>BNA</td>\n",
       "      <td>...</td>\n",
       "      <td>328.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449991</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>1489</td>\n",
       "      <td>N7736A</td>\n",
       "      <td>CLT</td>\n",
       "      <td>BWI</td>\n",
       "      <td>...</td>\n",
       "      <td>361.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449992</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>4647</td>\n",
       "      <td>N453WN</td>\n",
       "      <td>CLT</td>\n",
       "      <td>BWI</td>\n",
       "      <td>...</td>\n",
       "      <td>361.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449993</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>1568</td>\n",
       "      <td>N711HK</td>\n",
       "      <td>CLT</td>\n",
       "      <td>DAL</td>\n",
       "      <td>...</td>\n",
       "      <td>926.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449994</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>504</td>\n",
       "      <td>N7737E</td>\n",
       "      <td>CLT</td>\n",
       "      <td>HOU</td>\n",
       "      <td>...</td>\n",
       "      <td>920.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449995</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>1435</td>\n",
       "      <td>N444WN</td>\n",
       "      <td>CLT</td>\n",
       "      <td>MDW</td>\n",
       "      <td>...</td>\n",
       "      <td>584.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449996</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>1758</td>\n",
       "      <td>N953WN</td>\n",
       "      <td>CLT</td>\n",
       "      <td>MDW</td>\n",
       "      <td>...</td>\n",
       "      <td>584.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449997</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>122</td>\n",
       "      <td>N8601C</td>\n",
       "      <td>CMH</td>\n",
       "      <td>ATL</td>\n",
       "      <td>...</td>\n",
       "      <td>447.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449998</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>1351</td>\n",
       "      <td>N8682B</td>\n",
       "      <td>CMH</td>\n",
       "      <td>ATL</td>\n",
       "      <td>...</td>\n",
       "      <td>447.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449999</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>1492</td>\n",
       "      <td>N917WN</td>\n",
       "      <td>CMH</td>\n",
       "      <td>ATL</td>\n",
       "      <td>...</td>\n",
       "      <td>447.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450000</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>294</td>\n",
       "      <td>N606SW</td>\n",
       "      <td>CMH</td>\n",
       "      <td>BNA</td>\n",
       "      <td>...</td>\n",
       "      <td>337.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450001</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>1475</td>\n",
       "      <td>N701GS</td>\n",
       "      <td>CMH</td>\n",
       "      <td>BNA</td>\n",
       "      <td>...</td>\n",
       "      <td>337.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450002</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>420</td>\n",
       "      <td>N201LV</td>\n",
       "      <td>CMH</td>\n",
       "      <td>BOS</td>\n",
       "      <td>...</td>\n",
       "      <td>640.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450003</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>1359</td>\n",
       "      <td>N376SW</td>\n",
       "      <td>CMH</td>\n",
       "      <td>BOS</td>\n",
       "      <td>...</td>\n",
       "      <td>640.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450004</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>499</td>\n",
       "      <td>N7833A</td>\n",
       "      <td>CMH</td>\n",
       "      <td>BWI</td>\n",
       "      <td>...</td>\n",
       "      <td>337.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450005</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>1456</td>\n",
       "      <td>N451WN</td>\n",
       "      <td>CMH</td>\n",
       "      <td>BWI</td>\n",
       "      <td>...</td>\n",
       "      <td>337.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450006</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>1704</td>\n",
       "      <td>N761RR</td>\n",
       "      <td>CMH</td>\n",
       "      <td>BWI</td>\n",
       "      <td>...</td>\n",
       "      <td>337.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450007</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>51</td>\n",
       "      <td>N8611F</td>\n",
       "      <td>CMH</td>\n",
       "      <td>DAL</td>\n",
       "      <td>...</td>\n",
       "      <td>919.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450008</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>509</td>\n",
       "      <td>N7849A</td>\n",
       "      <td>CMH</td>\n",
       "      <td>DCA</td>\n",
       "      <td>...</td>\n",
       "      <td>323.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450009</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>1170</td>\n",
       "      <td>N7739A</td>\n",
       "      <td>CMH</td>\n",
       "      <td>DCA</td>\n",
       "      <td>...</td>\n",
       "      <td>323.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450010</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>1059</td>\n",
       "      <td>N706SW</td>\n",
       "      <td>CMH</td>\n",
       "      <td>DEN</td>\n",
       "      <td>...</td>\n",
       "      <td>1154.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450011</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>116</td>\n",
       "      <td>N600WN</td>\n",
       "      <td>CMH</td>\n",
       "      <td>FLL</td>\n",
       "      <td>...</td>\n",
       "      <td>973.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450012</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>510</td>\n",
       "      <td>N8504G</td>\n",
       "      <td>CMH</td>\n",
       "      <td>LAS</td>\n",
       "      <td>...</td>\n",
       "      <td>1771.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450013</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>902</td>\n",
       "      <td>N8692F</td>\n",
       "      <td>CMH</td>\n",
       "      <td>LAS</td>\n",
       "      <td>...</td>\n",
       "      <td>1771.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450014</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>1093</td>\n",
       "      <td>N927WN</td>\n",
       "      <td>CMH</td>\n",
       "      <td>MCO</td>\n",
       "      <td>...</td>\n",
       "      <td>802.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450015</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>1893</td>\n",
       "      <td>N604SW</td>\n",
       "      <td>CMH</td>\n",
       "      <td>MCO</td>\n",
       "      <td>...</td>\n",
       "      <td>802.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450016</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>Southwest Airlines Co.</td>\n",
       "      <td>WN</td>\n",
       "      <td>4314</td>\n",
       "      <td>N449WN</td>\n",
       "      <td>CMH</td>\n",
       "      <td>MCO</td>\n",
       "      <td>...</td>\n",
       "      <td>802.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>450017 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Year  Month  DayofMonth  DayOfWeek             CarrierName  \\\n",
       "0       2017      1          17          2  American Airlines Inc.   \n",
       "1       2017      1          18          3  American Airlines Inc.   \n",
       "2       2017      1          19          4  American Airlines Inc.   \n",
       "3       2017      1          20          5  American Airlines Inc.   \n",
       "4       2017      1          21          6  American Airlines Inc.   \n",
       "5       2017      1          22          7  American Airlines Inc.   \n",
       "6       2017      1          23          1  American Airlines Inc.   \n",
       "7       2017      1          24          2  American Airlines Inc.   \n",
       "8       2017      1          25          3  American Airlines Inc.   \n",
       "9       2017      1          26          4  American Airlines Inc.   \n",
       "10      2017      1          27          5  American Airlines Inc.   \n",
       "11      2017      1          28          6  American Airlines Inc.   \n",
       "12      2017      1          29          7  American Airlines Inc.   \n",
       "13      2017      1          30          1  American Airlines Inc.   \n",
       "14      2017      1          31          2  American Airlines Inc.   \n",
       "15      2017      1          11          3  American Airlines Inc.   \n",
       "16      2017      1          12          4  American Airlines Inc.   \n",
       "17      2017      1          13          5  American Airlines Inc.   \n",
       "18      2017      1          14          6  American Airlines Inc.   \n",
       "19      2017      1          18          3  American Airlines Inc.   \n",
       "20      2017      1          19          4  American Airlines Inc.   \n",
       "21      2017      1          20          5  American Airlines Inc.   \n",
       "22      2017      1          21          6  American Airlines Inc.   \n",
       "23      2017      1          22          7  American Airlines Inc.   \n",
       "24      2017      1          25          3  American Airlines Inc.   \n",
       "25      2017      1          26          4  American Airlines Inc.   \n",
       "26      2017      1          27          5  American Airlines Inc.   \n",
       "27      2017      1          28          6  American Airlines Inc.   \n",
       "28      2017      1          29          7  American Airlines Inc.   \n",
       "29      2017      1           6          5  American Airlines Inc.   \n",
       "...      ...    ...         ...        ...                     ...   \n",
       "449987  2017      1          24          2  Southwest Airlines Co.   \n",
       "449988  2017      1          24          2  Southwest Airlines Co.   \n",
       "449989  2017      1          24          2  Southwest Airlines Co.   \n",
       "449990  2017      1          24          2  Southwest Airlines Co.   \n",
       "449991  2017      1          24          2  Southwest Airlines Co.   \n",
       "449992  2017      1          24          2  Southwest Airlines Co.   \n",
       "449993  2017      1          24          2  Southwest Airlines Co.   \n",
       "449994  2017      1          24          2  Southwest Airlines Co.   \n",
       "449995  2017      1          24          2  Southwest Airlines Co.   \n",
       "449996  2017      1          24          2  Southwest Airlines Co.   \n",
       "449997  2017      1          24          2  Southwest Airlines Co.   \n",
       "449998  2017      1          24          2  Southwest Airlines Co.   \n",
       "449999  2017      1          24          2  Southwest Airlines Co.   \n",
       "450000  2017      1          24          2  Southwest Airlines Co.   \n",
       "450001  2017      1          24          2  Southwest Airlines Co.   \n",
       "450002  2017      1          24          2  Southwest Airlines Co.   \n",
       "450003  2017      1          24          2  Southwest Airlines Co.   \n",
       "450004  2017      1          24          2  Southwest Airlines Co.   \n",
       "450005  2017      1          24          2  Southwest Airlines Co.   \n",
       "450006  2017      1          24          2  Southwest Airlines Co.   \n",
       "450007  2017      1          24          2  Southwest Airlines Co.   \n",
       "450008  2017      1          24          2  Southwest Airlines Co.   \n",
       "450009  2017      1          24          2  Southwest Airlines Co.   \n",
       "450010  2017      1          24          2  Southwest Airlines Co.   \n",
       "450011  2017      1          24          2  Southwest Airlines Co.   \n",
       "450012  2017      1          24          2  Southwest Airlines Co.   \n",
       "450013  2017      1          24          2  Southwest Airlines Co.   \n",
       "450014  2017      1          24          2  Southwest Airlines Co.   \n",
       "450015  2017      1          24          2  Southwest Airlines Co.   \n",
       "450016  2017      1          24          2  Southwest Airlines Co.   \n",
       "\n",
       "       CarrierCode  FlightNum TailNum Origin Dest   ...     Distance  \\\n",
       "0               AA        494  N583AA    CLT  PHX   ...       1773.0   \n",
       "1               AA        494  N544AA    CLT  PHX   ...       1773.0   \n",
       "2               AA        494  N553AA    CLT  PHX   ...       1773.0   \n",
       "3               AA        494  N191AA    CLT  PHX   ...       1773.0   \n",
       "4               AA        494  N170AA    CLT  PHX   ...       1773.0   \n",
       "5               AA        494  N179AA    CLT  PHX   ...       1773.0   \n",
       "6               AA        494  N579AA    CLT  PHX   ...       1773.0   \n",
       "7               AA        494  N583AA    CLT  PHX   ...       1773.0   \n",
       "8               AA        494  N167AA    CLT  PHX   ...       1773.0   \n",
       "9               AA        494  N551AA    CLT  PHX   ...       1773.0   \n",
       "10              AA        494  N919AA    CLT  PHX   ...       1773.0   \n",
       "11              AA        494  N542AA    CLT  PHX   ...       1773.0   \n",
       "12              AA        494  N559AA    CLT  PHX   ...       1773.0   \n",
       "13              AA        494  N921AA    CLT  PHX   ...       1773.0   \n",
       "14              AA        494  N972AA    CLT  PHX   ...       1773.0   \n",
       "15              AA        494  N185AA    FLL  CLT   ...        632.0   \n",
       "16              AA        494  N970AA    FLL  CLT   ...        632.0   \n",
       "17              AA        494  N199AA    FLL  CLT   ...        632.0   \n",
       "18              AA        494  N919AA    FLL  CLT   ...        632.0   \n",
       "19              AA        494  N190AA    FLL  CLT   ...        632.0   \n",
       "20              AA        494  N553AA    FLL  CLT   ...        632.0   \n",
       "21              AA        494  N191AA    FLL  CLT   ...        632.0   \n",
       "22              AA        494  N170AA    FLL  CLT   ...        632.0   \n",
       "23              AA        494  N179AA    FLL  CLT   ...        632.0   \n",
       "24              AA        494  N167AA    FLL  CLT   ...        632.0   \n",
       "25              AA        494  N551AA    FLL  CLT   ...        632.0   \n",
       "26              AA        494  N919AA    FLL  CLT   ...        632.0   \n",
       "27              AA        494  N510AA    FLL  CLT   ...        632.0   \n",
       "28              AA        494  N559AA    FLL  CLT   ...        632.0   \n",
       "29              AA        494  N554AA    PHX  LAS   ...        255.0   \n",
       "...            ...        ...     ...    ...  ...   ...          ...   \n",
       "449987          WN       1442  N415WN    CLE  MDW   ...        307.0   \n",
       "449988          WN        503  N8322X    CLE  PHX   ...       1737.0   \n",
       "449989          WN        477  N8650F    CLE  STL   ...        487.0   \n",
       "449990          WN        138  N475WN    CLT  BNA   ...        328.0   \n",
       "449991          WN       1489  N7736A    CLT  BWI   ...        361.0   \n",
       "449992          WN       4647  N453WN    CLT  BWI   ...        361.0   \n",
       "449993          WN       1568  N711HK    CLT  DAL   ...        926.0   \n",
       "449994          WN        504  N7737E    CLT  HOU   ...        920.0   \n",
       "449995          WN       1435  N444WN    CLT  MDW   ...        584.0   \n",
       "449996          WN       1758  N953WN    CLT  MDW   ...        584.0   \n",
       "449997          WN        122  N8601C    CMH  ATL   ...        447.0   \n",
       "449998          WN       1351  N8682B    CMH  ATL   ...        447.0   \n",
       "449999          WN       1492  N917WN    CMH  ATL   ...        447.0   \n",
       "450000          WN        294  N606SW    CMH  BNA   ...        337.0   \n",
       "450001          WN       1475  N701GS    CMH  BNA   ...        337.0   \n",
       "450002          WN        420  N201LV    CMH  BOS   ...        640.0   \n",
       "450003          WN       1359  N376SW    CMH  BOS   ...        640.0   \n",
       "450004          WN        499  N7833A    CMH  BWI   ...        337.0   \n",
       "450005          WN       1456  N451WN    CMH  BWI   ...        337.0   \n",
       "450006          WN       1704  N761RR    CMH  BWI   ...        337.0   \n",
       "450007          WN         51  N8611F    CMH  DAL   ...        919.0   \n",
       "450008          WN        509  N7849A    CMH  DCA   ...        323.0   \n",
       "450009          WN       1170  N7739A    CMH  DCA   ...        323.0   \n",
       "450010          WN       1059  N706SW    CMH  DEN   ...       1154.0   \n",
       "450011          WN        116  N600WN    CMH  FLL   ...        973.0   \n",
       "450012          WN        510  N8504G    CMH  LAS   ...       1771.0   \n",
       "450013          WN        902  N8692F    CMH  LAS   ...       1771.0   \n",
       "450014          WN       1093  N927WN    CMH  MCO   ...        802.0   \n",
       "450015          WN       1893  N604SW    CMH  MCO   ...        802.0   \n",
       "450016          WN       4314  N449WN    CMH  MCO   ...        802.0   \n",
       "\n",
       "        Cancelled  CancellationCode  Diverted  CarrierDelay  WeatherDelay  \\\n",
       "0             0.0               NaN       0.0           NaN           NaN   \n",
       "1             0.0               NaN       0.0           NaN           NaN   \n",
       "2             0.0               NaN       0.0           NaN           NaN   \n",
       "3             0.0               NaN       0.0          33.0           0.0   \n",
       "4             0.0               NaN       0.0           NaN           NaN   \n",
       "5             0.0               NaN       0.0           0.0           0.0   \n",
       "6             0.0               NaN       0.0           NaN           NaN   \n",
       "7             0.0               NaN       0.0           NaN           NaN   \n",
       "8             0.0               NaN       0.0           NaN           NaN   \n",
       "9             0.0               NaN       0.0           NaN           NaN   \n",
       "10            0.0               NaN       0.0           NaN           NaN   \n",
       "11            0.0               NaN       0.0           NaN           NaN   \n",
       "12            0.0               NaN       0.0           NaN           NaN   \n",
       "13            0.0               NaN       0.0           NaN           NaN   \n",
       "14            0.0               NaN       0.0           NaN           NaN   \n",
       "15            0.0               NaN       0.0           NaN           NaN   \n",
       "16            0.0               NaN       0.0           NaN           NaN   \n",
       "17            0.0               NaN       0.0           NaN           NaN   \n",
       "18            0.0               NaN       0.0           NaN           NaN   \n",
       "19            0.0               NaN       0.0           NaN           NaN   \n",
       "20            0.0               NaN       0.0           NaN           NaN   \n",
       "21            0.0               NaN       0.0           NaN           NaN   \n",
       "22            0.0               NaN       0.0           NaN           NaN   \n",
       "23            0.0               NaN       0.0           8.0           0.0   \n",
       "24            0.0               NaN       0.0           NaN           NaN   \n",
       "25            0.0               NaN       0.0           NaN           NaN   \n",
       "26            0.0               NaN       0.0           NaN           NaN   \n",
       "27            0.0               NaN       0.0           NaN           NaN   \n",
       "28            0.0               NaN       0.0           NaN           NaN   \n",
       "29            0.0               NaN       0.0           NaN           NaN   \n",
       "...           ...               ...       ...           ...           ...   \n",
       "449987        0.0               NaN       0.0           NaN           NaN   \n",
       "449988        0.0               NaN       0.0           0.0           0.0   \n",
       "449989        0.0               NaN       0.0           NaN           NaN   \n",
       "449990        0.0               NaN       0.0           NaN           NaN   \n",
       "449991        0.0               NaN       0.0           NaN           NaN   \n",
       "449992        0.0               NaN       0.0           NaN           NaN   \n",
       "449993        0.0               NaN       0.0          33.0           0.0   \n",
       "449994        0.0               NaN       0.0           NaN           NaN   \n",
       "449995        0.0               NaN       0.0           NaN           NaN   \n",
       "449996        0.0               NaN       0.0           NaN           NaN   \n",
       "449997        0.0               NaN       0.0           NaN           NaN   \n",
       "449998        0.0               NaN       0.0           NaN           NaN   \n",
       "449999        0.0               NaN       0.0           NaN           NaN   \n",
       "450000        0.0               NaN       0.0           NaN           NaN   \n",
       "450001        0.0               NaN       0.0           NaN           NaN   \n",
       "450002        0.0               NaN       0.0           0.0           0.0   \n",
       "450003        0.0               NaN       0.0           0.0           0.0   \n",
       "450004        0.0               NaN       0.0           NaN           NaN   \n",
       "450005        0.0               NaN       0.0           NaN           NaN   \n",
       "450006        0.0               NaN       0.0           NaN           NaN   \n",
       "450007        0.0               NaN       0.0           NaN           NaN   \n",
       "450008        0.0               NaN       0.0           NaN           NaN   \n",
       "450009        0.0               NaN       0.0           NaN           NaN   \n",
       "450010        0.0               NaN       0.0           NaN           NaN   \n",
       "450011        0.0               NaN       0.0           NaN           NaN   \n",
       "450012        0.0               NaN       0.0           NaN           NaN   \n",
       "450013        0.0               NaN       0.0           NaN           NaN   \n",
       "450014        0.0               NaN       0.0           NaN           NaN   \n",
       "450015        0.0               NaN       0.0           NaN           NaN   \n",
       "450016        0.0               NaN       0.0           NaN           NaN   \n",
       "\n",
       "        NASDelay  SecurityDelay  LateAircraftDelay  Flights  \n",
       "0            NaN            NaN                NaN        1  \n",
       "1            NaN            NaN                NaN        1  \n",
       "2            NaN            NaN                NaN        1  \n",
       "3            0.0            0.0                0.0        1  \n",
       "4            NaN            NaN                NaN        1  \n",
       "5            8.0            0.0               17.0        1  \n",
       "6            NaN            NaN                NaN        1  \n",
       "7            NaN            NaN                NaN        1  \n",
       "8            NaN            NaN                NaN        1  \n",
       "9            NaN            NaN                NaN        1  \n",
       "10           NaN            NaN                NaN        1  \n",
       "11           NaN            NaN                NaN        1  \n",
       "12           NaN            NaN                NaN        1  \n",
       "13           NaN            NaN                NaN        1  \n",
       "14           NaN            NaN                NaN        1  \n",
       "15           NaN            NaN                NaN        1  \n",
       "16           NaN            NaN                NaN        1  \n",
       "17           NaN            NaN                NaN        1  \n",
       "18           NaN            NaN                NaN        1  \n",
       "19           NaN            NaN                NaN        1  \n",
       "20           NaN            NaN                NaN        1  \n",
       "21           NaN            NaN                NaN        1  \n",
       "22           NaN            NaN                NaN        1  \n",
       "23           0.0            0.0               28.0        1  \n",
       "24           NaN            NaN                NaN        1  \n",
       "25           NaN            NaN                NaN        1  \n",
       "26           NaN            NaN                NaN        1  \n",
       "27           NaN            NaN                NaN        1  \n",
       "28           NaN            NaN                NaN        1  \n",
       "29           NaN            NaN                NaN        1  \n",
       "...          ...            ...                ...      ...  \n",
       "449987       NaN            NaN                NaN        1  \n",
       "449988      20.0            0.0                0.0        1  \n",
       "449989       NaN            NaN                NaN        1  \n",
       "449990       NaN            NaN                NaN        1  \n",
       "449991       NaN            NaN                NaN        1  \n",
       "449992       NaN            NaN                NaN        1  \n",
       "449993       0.0            0.0                0.0        1  \n",
       "449994       NaN            NaN                NaN        1  \n",
       "449995       NaN            NaN                NaN        1  \n",
       "449996       NaN            NaN                NaN        1  \n",
       "449997       NaN            NaN                NaN        1  \n",
       "449998       NaN            NaN                NaN        1  \n",
       "449999       NaN            NaN                NaN        1  \n",
       "450000       NaN            NaN                NaN        1  \n",
       "450001       NaN            NaN                NaN        1  \n",
       "450002      60.0            0.0                0.0        1  \n",
       "450003       8.0            0.0               74.0        1  \n",
       "450004       NaN            NaN                NaN        1  \n",
       "450005       NaN            NaN                NaN        1  \n",
       "450006       NaN            NaN                NaN        1  \n",
       "450007       NaN            NaN                NaN        1  \n",
       "450008       NaN            NaN                NaN        1  \n",
       "450009       NaN            NaN                NaN        1  \n",
       "450010       NaN            NaN                NaN        1  \n",
       "450011       NaN            NaN                NaN        1  \n",
       "450012       NaN            NaN                NaN        1  \n",
       "450013       NaN            NaN                NaN        1  \n",
       "450014       NaN            NaN                NaN        1  \n",
       "450015       NaN            NaN                NaN        1  \n",
       "450016       NaN            NaN                NaN        1  \n",
       "\n",
       "[450017 rows x 28 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"Read data and filter columns\")\n",
    "data = pd.read_csv('data/On_Time_On_Time_Performance_2017_1.zip')\n",
    "data = data.merge(airlines, how='left', left_on='AirlineID', right_on='Code', right_index=True)\n",
    "print(\"# of columns before: {}\".format(data.columns.size))\n",
    "data = data[[\n",
    "    'Year', 'Month', 'DayofMonth', 'DayOfWeek',\n",
    "    'CarrierName', 'CarrierCode', 'FlightNum', 'TailNum',\n",
    "    'Origin', 'Dest',\n",
    "    'CRSDepTime', 'DepTime', 'DepDelayMinutes', 'CRSArrTime', 'ArrTime', 'ArrDelay',\n",
    "    'CRSElapsedTime', 'ActualElapsedTime', 'Distance',\n",
    "    'Cancelled', 'CancellationCode', 'Diverted',\n",
    "    'CarrierDelay', 'WeatherDelay', 'NASDelay', 'SecurityDelay', 'LateAircraftDelay'\n",
    "]]\n",
    "data['Flights'] = 1\n",
    "print(\"# of columns  after: {}\".format(data.columns.size))\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DepDelayMinutes</th>\n",
       "      <th>Flights</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Lng</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Origin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ABE</th>\n",
       "      <td>58.8</td>\n",
       "      <td>191.0</td>\n",
       "      <td>40.651940</td>\n",
       "      <td>-75.440560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABI</th>\n",
       "      <td>98.2</td>\n",
       "      <td>28.0</td>\n",
       "      <td>32.411390</td>\n",
       "      <td>-99.681940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABQ</th>\n",
       "      <td>34.0</td>\n",
       "      <td>1395.0</td>\n",
       "      <td>35.040278</td>\n",
       "      <td>-106.609167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABR</th>\n",
       "      <td>65.1</td>\n",
       "      <td>62.0</td>\n",
       "      <td>45.449170</td>\n",
       "      <td>-98.421940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABY</th>\n",
       "      <td>48.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>31.535560</td>\n",
       "      <td>-84.194440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ACT</th>\n",
       "      <td>58.3</td>\n",
       "      <td>104.0</td>\n",
       "      <td>31.611390</td>\n",
       "      <td>-97.230560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ACV</th>\n",
       "      <td>35.6</td>\n",
       "      <td>92.0</td>\n",
       "      <td>40.978060</td>\n",
       "      <td>-124.108610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ACY</th>\n",
       "      <td>33.6</td>\n",
       "      <td>341.0</td>\n",
       "      <td>39.457500</td>\n",
       "      <td>-74.577220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ADK</th>\n",
       "      <td>25.8</td>\n",
       "      <td>9.0</td>\n",
       "      <td>51.878060</td>\n",
       "      <td>-176.646110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ADQ</th>\n",
       "      <td>22.5</td>\n",
       "      <td>42.0</td>\n",
       "      <td>57.750000</td>\n",
       "      <td>-152.493890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AEX</th>\n",
       "      <td>47.2</td>\n",
       "      <td>258.0</td>\n",
       "      <td>31.327500</td>\n",
       "      <td>-92.548610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGS</th>\n",
       "      <td>64.2</td>\n",
       "      <td>194.0</td>\n",
       "      <td>33.370000</td>\n",
       "      <td>-81.964167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ALB</th>\n",
       "      <td>29.0</td>\n",
       "      <td>767.0</td>\n",
       "      <td>42.745833</td>\n",
       "      <td>-73.805278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AMA</th>\n",
       "      <td>19.8</td>\n",
       "      <td>248.0</td>\n",
       "      <td>35.219440</td>\n",
       "      <td>-101.705830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ANC</th>\n",
       "      <td>16.6</td>\n",
       "      <td>1223.0</td>\n",
       "      <td>61.174167</td>\n",
       "      <td>-149.981667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>APN</th>\n",
       "      <td>79.8</td>\n",
       "      <td>52.0</td>\n",
       "      <td>45.078060</td>\n",
       "      <td>-83.560280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ASE</th>\n",
       "      <td>124.4</td>\n",
       "      <td>695.0</td>\n",
       "      <td>39.223060</td>\n",
       "      <td>-106.868890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ATL</th>\n",
       "      <td>48.0</td>\n",
       "      <td>30138.0</td>\n",
       "      <td>33.639444</td>\n",
       "      <td>-84.428056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ATW</th>\n",
       "      <td>44.6</td>\n",
       "      <td>213.0</td>\n",
       "      <td>44.258060</td>\n",
       "      <td>-88.519170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AUS</th>\n",
       "      <td>30.0</td>\n",
       "      <td>3737.0</td>\n",
       "      <td>30.194444</td>\n",
       "      <td>-97.670000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AVL</th>\n",
       "      <td>55.4</td>\n",
       "      <td>212.0</td>\n",
       "      <td>35.436110</td>\n",
       "      <td>-82.541670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AVP</th>\n",
       "      <td>87.5</td>\n",
       "      <td>100.0</td>\n",
       "      <td>41.338330</td>\n",
       "      <td>-75.723330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AZO</th>\n",
       "      <td>62.4</td>\n",
       "      <td>153.0</td>\n",
       "      <td>42.235000</td>\n",
       "      <td>-85.551940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BDL</th>\n",
       "      <td>26.0</td>\n",
       "      <td>1731.0</td>\n",
       "      <td>41.938889</td>\n",
       "      <td>-72.683333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BET</th>\n",
       "      <td>13.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>60.779722</td>\n",
       "      <td>-161.838056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BFL</th>\n",
       "      <td>0.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>35.433610</td>\n",
       "      <td>-119.056670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BGM</th>\n",
       "      <td>51.6</td>\n",
       "      <td>58.0</td>\n",
       "      <td>42.208610</td>\n",
       "      <td>-75.979720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BHM</th>\n",
       "      <td>47.0</td>\n",
       "      <td>912.0</td>\n",
       "      <td>33.561667</td>\n",
       "      <td>-86.753056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BIL</th>\n",
       "      <td>41.6</td>\n",
       "      <td>240.0</td>\n",
       "      <td>45.807780</td>\n",
       "      <td>-108.542780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BIS</th>\n",
       "      <td>74.3</td>\n",
       "      <td>108.0</td>\n",
       "      <td>46.772780</td>\n",
       "      <td>-100.745830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SLC</th>\n",
       "      <td>49.0</td>\n",
       "      <td>9097.0</td>\n",
       "      <td>40.786111</td>\n",
       "      <td>-111.982222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SMF</th>\n",
       "      <td>47.0</td>\n",
       "      <td>3352.0</td>\n",
       "      <td>38.695556</td>\n",
       "      <td>-121.590833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SNA</th>\n",
       "      <td>42.0</td>\n",
       "      <td>3365.0</td>\n",
       "      <td>33.675556</td>\n",
       "      <td>-117.868333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SPI</th>\n",
       "      <td>98.6</td>\n",
       "      <td>140.0</td>\n",
       "      <td>39.844170</td>\n",
       "      <td>-89.678060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SPS</th>\n",
       "      <td>56.8</td>\n",
       "      <td>88.0</td>\n",
       "      <td>33.988890</td>\n",
       "      <td>-98.491940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRQ</th>\n",
       "      <td>42.0</td>\n",
       "      <td>311.0</td>\n",
       "      <td>27.395560</td>\n",
       "      <td>-82.554440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STL</th>\n",
       "      <td>33.0</td>\n",
       "      <td>4661.0</td>\n",
       "      <td>38.747222</td>\n",
       "      <td>-90.361389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STT</th>\n",
       "      <td>32.7</td>\n",
       "      <td>498.0</td>\n",
       "      <td>18.336944</td>\n",
       "      <td>-64.973056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STX</th>\n",
       "      <td>24.2</td>\n",
       "      <td>110.0</td>\n",
       "      <td>17.701670</td>\n",
       "      <td>-64.801670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SUN</th>\n",
       "      <td>94.3</td>\n",
       "      <td>136.0</td>\n",
       "      <td>43.503890</td>\n",
       "      <td>-114.295560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWF</th>\n",
       "      <td>30.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>41.504170</td>\n",
       "      <td>-74.104720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SYR</th>\n",
       "      <td>33.0</td>\n",
       "      <td>448.0</td>\n",
       "      <td>43.112778</td>\n",
       "      <td>-76.111944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TKI</th>\n",
       "      <td>45.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>55.937220</td>\n",
       "      <td>-133.326670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TLH</th>\n",
       "      <td>54.2</td>\n",
       "      <td>165.0</td>\n",
       "      <td>30.395278</td>\n",
       "      <td>-84.345278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPA</th>\n",
       "      <td>34.0</td>\n",
       "      <td>5914.0</td>\n",
       "      <td>27.975556</td>\n",
       "      <td>-82.533333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TRI</th>\n",
       "      <td>41.1</td>\n",
       "      <td>163.0</td>\n",
       "      <td>36.475214</td>\n",
       "      <td>-82.407419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TTN</th>\n",
       "      <td>75.8</td>\n",
       "      <td>187.0</td>\n",
       "      <td>40.276670</td>\n",
       "      <td>-74.813330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TUL</th>\n",
       "      <td>21.0</td>\n",
       "      <td>1155.0</td>\n",
       "      <td>36.198330</td>\n",
       "      <td>-95.888060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TUS</th>\n",
       "      <td>40.0</td>\n",
       "      <td>1446.0</td>\n",
       "      <td>32.116110</td>\n",
       "      <td>-110.941110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TVC</th>\n",
       "      <td>90.0</td>\n",
       "      <td>208.0</td>\n",
       "      <td>44.741390</td>\n",
       "      <td>-85.582220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TWF</th>\n",
       "      <td>81.0</td>\n",
       "      <td>125.0</td>\n",
       "      <td>42.481803</td>\n",
       "      <td>-114.487733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TXK</th>\n",
       "      <td>37.2</td>\n",
       "      <td>51.0</td>\n",
       "      <td>33.453610</td>\n",
       "      <td>-93.991110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TYR</th>\n",
       "      <td>32.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>32.354213</td>\n",
       "      <td>-95.402419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TYS</th>\n",
       "      <td>50.6</td>\n",
       "      <td>548.0</td>\n",
       "      <td>35.811110</td>\n",
       "      <td>-83.993890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VLD</th>\n",
       "      <td>62.4</td>\n",
       "      <td>87.0</td>\n",
       "      <td>30.781390</td>\n",
       "      <td>-83.276110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VPS</th>\n",
       "      <td>74.4</td>\n",
       "      <td>254.0</td>\n",
       "      <td>30.483333</td>\n",
       "      <td>-86.525278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WRG</th>\n",
       "      <td>6.6</td>\n",
       "      <td>62.0</td>\n",
       "      <td>56.484440</td>\n",
       "      <td>-132.369720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>XNA</th>\n",
       "      <td>76.0</td>\n",
       "      <td>417.0</td>\n",
       "      <td>36.281940</td>\n",
       "      <td>-94.306940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YAK</th>\n",
       "      <td>12.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>59.503330</td>\n",
       "      <td>-139.660280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YUM</th>\n",
       "      <td>12.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>32.656670</td>\n",
       "      <td>-114.606110</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>298 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        DepDelayMinutes  Flights        Lat         Lng\n",
       "Origin                                                 \n",
       "ABE                58.8    191.0  40.651940  -75.440560\n",
       "ABI                98.2     28.0  32.411390  -99.681940\n",
       "ABQ                34.0   1395.0  35.040278 -106.609167\n",
       "ABR                65.1     62.0  45.449170  -98.421940\n",
       "ABY                48.0     82.0  31.535560  -84.194440\n",
       "ACT                58.3    104.0  31.611390  -97.230560\n",
       "ACV                35.6     92.0  40.978060 -124.108610\n",
       "ACY                33.6    341.0  39.457500  -74.577220\n",
       "ADK                25.8      9.0  51.878060 -176.646110\n",
       "ADQ                22.5     42.0  57.750000 -152.493890\n",
       "AEX                47.2    258.0  31.327500  -92.548610\n",
       "AGS                64.2    194.0  33.370000  -81.964167\n",
       "ALB                29.0    767.0  42.745833  -73.805278\n",
       "AMA                19.8    248.0  35.219440 -101.705830\n",
       "ANC                16.6   1223.0  61.174167 -149.981667\n",
       "APN                79.8     52.0  45.078060  -83.560280\n",
       "ASE               124.4    695.0  39.223060 -106.868890\n",
       "ATL                48.0  30138.0  33.639444  -84.428056\n",
       "ATW                44.6    213.0  44.258060  -88.519170\n",
       "AUS                30.0   3737.0  30.194444  -97.670000\n",
       "AVL                55.4    212.0  35.436110  -82.541670\n",
       "AVP                87.5    100.0  41.338330  -75.723330\n",
       "AZO                62.4    153.0  42.235000  -85.551940\n",
       "BDL                26.0   1731.0  41.938889  -72.683333\n",
       "BET                13.0     83.0  60.779722 -161.838056\n",
       "BFL                 0.0    178.0  35.433610 -119.056670\n",
       "BGM                51.6     58.0  42.208610  -75.979720\n",
       "BHM                47.0    912.0  33.561667  -86.753056\n",
       "BIL                41.6    240.0  45.807780 -108.542780\n",
       "BIS                74.3    108.0  46.772780 -100.745830\n",
       "...                 ...      ...        ...         ...\n",
       "SLC                49.0   9097.0  40.786111 -111.982222\n",
       "SMF                47.0   3352.0  38.695556 -121.590833\n",
       "SNA                42.0   3365.0  33.675556 -117.868333\n",
       "SPI                98.6    140.0  39.844170  -89.678060\n",
       "SPS                56.8     88.0  33.988890  -98.491940\n",
       "SRQ                42.0    311.0  27.395560  -82.554440\n",
       "STL                33.0   4661.0  38.747222  -90.361389\n",
       "STT                32.7    498.0  18.336944  -64.973056\n",
       "STX                24.2    110.0  17.701670  -64.801670\n",
       "SUN                94.3    136.0  43.503890 -114.295560\n",
       "SWF                30.0     62.0  41.504170  -74.104720\n",
       "SYR                33.0    448.0  43.112778  -76.111944\n",
       "TKI                45.0      1.0  55.937220 -133.326670\n",
       "TLH                54.2    165.0  30.395278  -84.345278\n",
       "TPA                34.0   5914.0  27.975556  -82.533333\n",
       "TRI                41.1    163.0  36.475214  -82.407419\n",
       "TTN                75.8    187.0  40.276670  -74.813330\n",
       "TUL                21.0   1155.0  36.198330  -95.888060\n",
       "TUS                40.0   1446.0  32.116110 -110.941110\n",
       "TVC                90.0    208.0  44.741390  -85.582220\n",
       "TWF                81.0    125.0  42.481803 -114.487733\n",
       "TXK                37.2     51.0  33.453610  -93.991110\n",
       "TYR                32.0      8.0  32.354213  -95.402419\n",
       "TYS                50.6    548.0  35.811110  -83.993890\n",
       "VLD                62.4     87.0  30.781390  -83.276110\n",
       "VPS                74.4    254.0  30.483333  -86.525278\n",
       "WRG                 6.6     62.0  56.484440 -132.369720\n",
       "XNA                76.0    417.0  36.281940  -94.306940\n",
       "YAK                12.0     60.0  59.503330 -139.660280\n",
       "YUM                12.0    120.0  32.656670 -114.606110\n",
       "\n",
       "[298 rows x 4 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pickle\n",
    "\n",
    "airports_loc_pairs = pd.read_pickle('data/airport_coordinates.pickle')\n",
    "airports_loc = pd.DataFrame([[iata, loc[0], loc[1]] for iata, loc in airports_loc_pairs.items() if loc], columns=['Code', 'Lat', 'Lng'])\n",
    "airports_loc = airports_loc.set_index('Code')\n",
    "airports_loc\n",
    "\n",
    "airports_delay = data[['DepDelayMinutes', 'Origin']].groupby('Origin').quantile(q=0.9)\n",
    "airports_flights = data[['Flights', 'Origin']].groupby('Origin').sum()\n",
    "airports_delay = airports_delay.merge(airports_flights, how='left', left_index=True, right_index=True)\n",
    "airports_delay = airports_delay.merge(airports_loc, how='left', left_index=True, right_index=True)\n",
    "airports_delay.to_csv('airports-delay.csv')\n",
    "airports_delay"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2017_9'"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import re\n",
    "\n",
    "s = 'data/On_Time_On_Time_Performance_2017_9.zip'\n",
    "r = re.search(r'(\\d{4}_\\d{1,2}).zip$', s)\n",
    "r.group(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Working on file 1 of 21: data/On_Time_On_Time_Performance_2017_9.zip\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/thierry/bin/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (77,84) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing file to:  data/delay-causes_2017_9.csv\n",
      "Working on file 2 of 21: data/On_Time_On_Time_Performance_2017_8.zip\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/thierry/bin/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (48,77,84) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing file to:  data/delay-causes_2017_8.csv\n",
      "Working on file 3 of 21: data/On_Time_On_Time_Performance_2016_1.zip\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/thierry/bin/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (77) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing file to:  data/delay-causes_2016_1.csv\n",
      "Working on file 4 of 21: data/On_Time_On_Time_Performance_2016_2.zip\n",
      "Writing file to:  data/delay-causes_2016_2.csv\n",
      "Working on file 5 of 21: data/On_Time_On_Time_Performance_2016_3.zip\n",
      "Writing file to:  data/delay-causes_2016_3.csv\n",
      "Working on file 6 of 21: data/On_Time_On_Time_Performance_2016_11.zip\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/thierry/bin/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (48,69,76,77,84) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing file to:  data/delay-causes_2016_11.csv\n",
      "Working on file 7 of 21: data/On_Time_On_Time_Performance_2017_5.zip\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/thierry/bin/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (48,77) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing file to:  data/delay-causes_2017_5.csv\n",
      "Working on file 8 of 21: data/On_Time_On_Time_Performance_2017_2.zip\n",
      "Writing file to:  data/delay-causes_2017_2.csv\n",
      "Working on file 9 of 21: data/On_Time_On_Time_Performance_2016_7.zip\n",
      "Writing file to:  data/delay-causes_2016_7.csv\n",
      "Working on file 10 of 21: data/On_Time_On_Time_Performance_2017_4.zip\n",
      "Writing file to:  data/delay-causes_2017_4.csv\n",
      "Working on file 11 of 21: data/On_Time_On_Time_Performance_2017_7.zip\n",
      "Writing file to:  data/delay-causes_2017_7.csv\n",
      "Working on file 12 of 21: data/On_Time_On_Time_Performance_2016_12.zip\n",
      "Writing file to:  data/delay-causes_2016_12.csv\n",
      "Working on file 13 of 21: data/On_Time_On_Time_Performance_2016_4.zip\n",
      "Writing file to:  data/delay-causes_2016_4.csv\n",
      "Working on file 14 of 21: data/On_Time_On_Time_Performance_2016_10.zip\n",
      "Writing file to:  data/delay-causes_2016_10.csv\n",
      "Working on file 15 of 21: data/On_Time_On_Time_Performance_2017_1.zip\n",
      "Writing file to:  data/delay-causes_2017_1.csv\n",
      "Working on file 16 of 21: data/On_Time_On_Time_Performance_2016_6.zip\n",
      "Writing file to:  data/delay-causes_2016_6.csv\n",
      "Working on file 17 of 21: data/On_Time_On_Time_Performance_2016_8.zip\n",
      "Writing file to:  data/delay-causes_2016_8.csv\n",
      "Working on file 18 of 21: data/On_Time_On_Time_Performance_2016_5.zip\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/thierry/bin/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (48,77,84,85) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing file to:  data/delay-causes_2016_5.csv\n",
      "Working on file 19 of 21: data/On_Time_On_Time_Performance_2017_3.zip\n",
      "Writing file to:  data/delay-causes_2017_3.csv\n",
      "Working on file 20 of 21: data/On_Time_On_Time_Performance_2016_9.zip\n",
      "Writing file to:  data/delay-causes_2016_9.csv\n",
      "Working on file 21 of 21: data/On_Time_On_Time_Performance_2017_6.zip\n",
      "Writing file to:  data/delay-causes_2017_6.csv\n"
     ]
    }
   ],
   "source": [
    "import glob\n",
    "\n",
    "i = 0\n",
    "datapaths = glob.glob('data/On_Time_On_Time_Performance_*.zip')\n",
    "for datapath in datapaths:\n",
    "    i += 1\n",
    "    date = re.search(r'(\\d{4}_\\d{1,2}).zip$', datapath).group(1)\n",
    "    print(\"Working on file {} of {}: {}\".format(i, len(datapaths), datapath))\n",
    "    data = pd.read_csv(datapath)\n",
    "    data = data.merge(airlines, how='left', left_on='AirlineID', right_on='Code', right_index=True)\n",
    "    data = data[[\n",
    "        'Year', 'Month', 'DayofMonth', 'DayOfWeek',\n",
    "        'CarrierName', 'CarrierCode', 'FlightNum', 'TailNum',\n",
    "        'Origin', 'Dest',\n",
    "        'CRSDepTime', 'DepTime', 'DepDelayMinutes', 'CRSArrTime', 'ArrTime', 'ArrDelay',\n",
    "        'CRSElapsedTime', 'ActualElapsedTime', 'Distance',\n",
    "        'Cancelled', 'CancellationCode', 'Diverted',\n",
    "        'CarrierDelay', 'WeatherDelay', 'NASDelay', 'SecurityDelay', 'LateAircraftDelay'\n",
    "    ]]\n",
    "    data['Flights'] = 1\n",
    "\n",
    "    airport_delay_causes = data[['Origin', 'DayofMonth', 'CarrierDelay', 'WeatherDelay', 'NASDelay', 'SecurityDelay', 'LateAircraftDelay']].copy()\n",
    "    airport_delay_causes['UnknownDelay'] = -data['DepDelayMinutes']\n",
    "    airport_delay_causes = airport_delay_causes.set_index(['Origin', 'DayofMonth'])\n",
    "    airport_delay_causes = airport_delay_causes.fillna(0)\n",
    "    airport_delay_causes = airport_delay_causes.groupby(['Origin', 'DayofMonth']).sum()\n",
    "    airport_delay_causes['UnknownDelay'] = -airport_delay_causes.sum(axis=1)\n",
    "    airport_delay_causes['UnknownDelay'][airport_delay_causes['UnknownDelay'] < 0] = 0\n",
    "    airport_delay_causes = airport_delay_causes.sort_index()\n",
    "    delaypath = 'data/delay-causes_{}.csv'.format(date)\n",
    "    print(\"Writing file to: \", delaypath)\n",
    "    airport_delay_causes.to_csv(delaypath)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AirportLocation</th>\n",
       "      <th>AirportName</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Lng</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>01A</th>\n",
       "      <td>Afognak Lake, AK</td>\n",
       "      <td>Afognak Lake Airport</td>\n",
       "      <td>62.946389</td>\n",
       "      <td>-152.275833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>03A</th>\n",
       "      <td>Granite Mountain, AK</td>\n",
       "      <td>Bear Creek Mining Strip</td>\n",
       "      <td>65.548056</td>\n",
       "      <td>-161.071667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>04A</th>\n",
       "      <td>Lik, AK</td>\n",
       "      <td>Lik Mining Camp</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05A</th>\n",
       "      <td>Little Squaw, AK</td>\n",
       "      <td>Little Squaw Airport</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>06A</th>\n",
       "      <td>Kizhuyak, AK</td>\n",
       "      <td>Kizhuyak Bay</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>07A</th>\n",
       "      <td>Klawock, AK</td>\n",
       "      <td>Klawock Seaplane Base</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>08A</th>\n",
       "      <td>Elizabeth Island, AK</td>\n",
       "      <td>Elizabeth Island Airport</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>09A</th>\n",
       "      <td>Homer, AK</td>\n",
       "      <td>Augustin Island</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1B1</th>\n",
       "      <td>Hudson, NY</td>\n",
       "      <td>Columbia County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1G4</th>\n",
       "      <td>Peach Springs, AZ</td>\n",
       "      <td>Grand Canyon West</td>\n",
       "      <td>35.987778</td>\n",
       "      <td>-113.820000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1N7</th>\n",
       "      <td>Blairstown, NJ</td>\n",
       "      <td>Blairstown Airport</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1NY</th>\n",
       "      <td>Penn Yan, NY</td>\n",
       "      <td>Penn Yan Airport</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6B0</th>\n",
       "      <td>Middlebury, VT</td>\n",
       "      <td>Middlebury State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7AK</th>\n",
       "      <td>Akun, AK</td>\n",
       "      <td>Akun Airport</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8F3</th>\n",
       "      <td>Crosbyton, TX</td>\n",
       "      <td>Crosbyton Municipal</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A01</th>\n",
       "      <td>Fairbanks/Ft. Wainwright, AK</td>\n",
       "      <td>Blair Lake</td>\n",
       "      <td>64.364167</td>\n",
       "      <td>-147.361389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A02</th>\n",
       "      <td>Deadmans Bay, AK</td>\n",
       "      <td>Deadmans Bay Airport</td>\n",
       "      <td>57.066667</td>\n",
       "      <td>-153.937778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A03</th>\n",
       "      <td>Hallo Bay, AK</td>\n",
       "      <td>Hallo Bay Airport</td>\n",
       "      <td>58.457500</td>\n",
       "      <td>-154.023333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A04</th>\n",
       "      <td>Red Lake, AK</td>\n",
       "      <td>Red Lake Airport</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A05</th>\n",
       "      <td>Shell Lake, AK</td>\n",
       "      <td>Shell Lake Airport</td>\n",
       "      <td>45.521389</td>\n",
       "      <td>-115.513056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A06</th>\n",
       "      <td>Kougarok, AK</td>\n",
       "      <td>Navigator Airstrip</td>\n",
       "      <td>65.655556</td>\n",
       "      <td>-165.356389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A07</th>\n",
       "      <td>Selawik, AK</td>\n",
       "      <td>Roland Norton Memorial</td>\n",
       "      <td>66.765833</td>\n",
       "      <td>-160.152500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A08</th>\n",
       "      <td>Pillar Bay, AK</td>\n",
       "      <td>Pillar Bay Airport</td>\n",
       "      <td>32.510556</td>\n",
       "      <td>-87.384722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A09</th>\n",
       "      <td>Johnstone Point, AK</td>\n",
       "      <td>Johnstone Point Airport</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A11</th>\n",
       "      <td>Bell Creek, AK</td>\n",
       "      <td>Bell Creek Airport</td>\n",
       "      <td>60.783889</td>\n",
       "      <td>-159.540278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A12</th>\n",
       "      <td>Cinnabar, AK</td>\n",
       "      <td>Cinnabar Airport</td>\n",
       "      <td>60.785278</td>\n",
       "      <td>-158.864444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A13</th>\n",
       "      <td>Mountaintop Mine, AK</td>\n",
       "      <td>Mountaintop Mine Airport</td>\n",
       "      <td>61.341111</td>\n",
       "      <td>-148.998889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A14</th>\n",
       "      <td>Whitebear, AK</td>\n",
       "      <td>Whitebear Airport</td>\n",
       "      <td>58.906389</td>\n",
       "      <td>-157.711111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A15</th>\n",
       "      <td>Puviaq, AK</td>\n",
       "      <td>Puviaq Airport</td>\n",
       "      <td>70.718611</td>\n",
       "      <td>-154.388333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A16</th>\n",
       "      <td>Arley Lake, AK</td>\n",
       "      <td>Arley Lake Airport</td>\n",
       "      <td>70.746944</td>\n",
       "      <td>-155.360833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZVA</th>\n",
       "      <td>Miandrivazo, Madagascar</td>\n",
       "      <td>Miandrivazo Airport</td>\n",
       "      <td>-19.562780</td>\n",
       "      <td>45.450830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZVK</th>\n",
       "      <td>Savannakhet, Laos</td>\n",
       "      <td>Savannakhet International</td>\n",
       "      <td>16.556670</td>\n",
       "      <td>104.759440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZWL</th>\n",
       "      <td>Wollaston Lake, Canada</td>\n",
       "      <td>Wollaston Lake Airport</td>\n",
       "      <td>58.100000</td>\n",
       "      <td>-103.183333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXA</th>\n",
       "      <td>Rota, Spain</td>\n",
       "      <td>Rota NS</td>\n",
       "      <td>36.646667</td>\n",
       "      <td>-6.346111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXB</th>\n",
       "      <td>Albuquerque, NM</td>\n",
       "      <td>Double Eagle II</td>\n",
       "      <td>35.146667</td>\n",
       "      <td>-106.805000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXC</th>\n",
       "      <td>Gasquet, CA</td>\n",
       "      <td>Ward Field</td>\n",
       "      <td>41.845833</td>\n",
       "      <td>-123.983333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXD</th>\n",
       "      <td>San Isidro, Dominican Republic</td>\n",
       "      <td>San Isidro International</td>\n",
       "      <td>18.503333</td>\n",
       "      <td>-69.762500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXE</th>\n",
       "      <td>Sculthorpe, United Kingdom</td>\n",
       "      <td>Sculthorpe Airport</td>\n",
       "      <td>52.845278</td>\n",
       "      <td>0.767222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXF</th>\n",
       "      <td>Illinois Creek, AK</td>\n",
       "      <td>Illinois Creek Airport</td>\n",
       "      <td>64.116667</td>\n",
       "      <td>-157.350000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXH</th>\n",
       "      <td>Chomondely Sound, AK</td>\n",
       "      <td>Chomondely Sound Airport</td>\n",
       "      <td>55.239167</td>\n",
       "      <td>-132.114167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXI</th>\n",
       "      <td>Coal Bay, AK</td>\n",
       "      <td>Coal Bay Airport</td>\n",
       "      <td>55.500556</td>\n",
       "      <td>-132.487500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXJ</th>\n",
       "      <td>Happy Harbour, AK</td>\n",
       "      <td>Happy Harbour Airport</td>\n",
       "      <td>55.488333</td>\n",
       "      <td>-132.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXK</th>\n",
       "      <td>Lancaster Cove, AK</td>\n",
       "      <td>Lancaster Cove Airport</td>\n",
       "      <td>55.216667</td>\n",
       "      <td>-132.086389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXL</th>\n",
       "      <td>Reid Cove, AK</td>\n",
       "      <td>Reid Cove Airport</td>\n",
       "      <td>55.200000</td>\n",
       "      <td>-132.238333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXM</th>\n",
       "      <td>Saltery Cove, AK</td>\n",
       "      <td>Saltery Cove Airport</td>\n",
       "      <td>55.408611</td>\n",
       "      <td>-132.329167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXN</th>\n",
       "      <td>Clover Bay, AK</td>\n",
       "      <td>Clover Bay Airport</td>\n",
       "      <td>55.298889</td>\n",
       "      <td>-132.145000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXO</th>\n",
       "      <td>El Capitan, AK</td>\n",
       "      <td>El Capitan Peak</td>\n",
       "      <td>56.184167</td>\n",
       "      <td>-133.310556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXP</th>\n",
       "      <td>Salmon Bay Lake, AK</td>\n",
       "      <td>Salmon Bay Lake Airport</td>\n",
       "      <td>56.237222</td>\n",
       "      <td>-133.188889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXQ</th>\n",
       "      <td>Santa Ynez, CA</td>\n",
       "      <td>Santa Ynez Airport</td>\n",
       "      <td>34.606944</td>\n",
       "      <td>-120.074722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXT</th>\n",
       "      <td>Cloverdale, CA</td>\n",
       "      <td>Cloverdale Municipal</td>\n",
       "      <td>38.774444</td>\n",
       "      <td>-122.991111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXU</th>\n",
       "      <td>North Kingstown, RI</td>\n",
       "      <td>Quonset State</td>\n",
       "      <td>41.595833</td>\n",
       "      <td>-71.405556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXV</th>\n",
       "      <td>Nappanee, IN</td>\n",
       "      <td>Nappanee Municipal</td>\n",
       "      <td>41.446111</td>\n",
       "      <td>-85.934722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXW</th>\n",
       "      <td>Paradise, CA</td>\n",
       "      <td>Paradise Skypark</td>\n",
       "      <td>39.709722</td>\n",
       "      <td>-121.615278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXX</th>\n",
       "      <td>Kennewick, WA</td>\n",
       "      <td>Vista Field</td>\n",
       "      <td>46.218333</td>\n",
       "      <td>-119.209444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXY</th>\n",
       "      <td>Delta, CO</td>\n",
       "      <td>Blake Field</td>\n",
       "      <td>38.783333</td>\n",
       "      <td>-108.065278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZXZ</th>\n",
       "      <td>Waterville, WA</td>\n",
       "      <td>Waterville Airport</td>\n",
       "      <td>47.654167</td>\n",
       "      <td>-120.054167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZYL</th>\n",
       "      <td>Sylhet, Bangladesh</td>\n",
       "      <td>Savannakhet</td>\n",
       "      <td>24.963330</td>\n",
       "      <td>91.866940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZZU</th>\n",
       "      <td>Mzuzu, Malawi</td>\n",
       "      <td>Mzuzu Airport</td>\n",
       "      <td>-11.444720</td>\n",
       "      <td>34.011670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZZV</th>\n",
       "      <td>Zanesville, OH</td>\n",
       "      <td>Zanesville Municipal</td>\n",
       "      <td>39.944440</td>\n",
       "      <td>-81.892220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZZZ</th>\n",
       "      <td>Unknown Point in Alaska</td>\n",
       "      <td>Unknown Point in Alaska</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6452 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     AirportLocation                AirportName        Lat  \\\n",
       "Code                                                                         \n",
       "01A                 Afognak Lake, AK       Afognak Lake Airport  62.946389   \n",
       "03A             Granite Mountain, AK    Bear Creek Mining Strip  65.548056   \n",
       "04A                          Lik, AK            Lik Mining Camp        NaN   \n",
       "05A                 Little Squaw, AK       Little Squaw Airport        NaN   \n",
       "06A                     Kizhuyak, AK               Kizhuyak Bay        NaN   \n",
       "07A                      Klawock, AK      Klawock Seaplane Base        NaN   \n",
       "08A             Elizabeth Island, AK   Elizabeth Island Airport        NaN   \n",
       "09A                        Homer, AK            Augustin Island        NaN   \n",
       "1B1                       Hudson, NY            Columbia County        NaN   \n",
       "1G4                Peach Springs, AZ          Grand Canyon West  35.987778   \n",
       "1N7                   Blairstown, NJ         Blairstown Airport        NaN   \n",
       "1NY                     Penn Yan, NY           Penn Yan Airport        NaN   \n",
       "6B0                   Middlebury, VT           Middlebury State        NaN   \n",
       "7AK                         Akun, AK               Akun Airport        NaN   \n",
       "8F3                    Crosbyton, TX        Crosbyton Municipal        NaN   \n",
       "A01     Fairbanks/Ft. Wainwright, AK                 Blair Lake  64.364167   \n",
       "A02                 Deadmans Bay, AK       Deadmans Bay Airport  57.066667   \n",
       "A03                    Hallo Bay, AK          Hallo Bay Airport  58.457500   \n",
       "A04                     Red Lake, AK           Red Lake Airport        NaN   \n",
       "A05                   Shell Lake, AK         Shell Lake Airport  45.521389   \n",
       "A06                     Kougarok, AK         Navigator Airstrip  65.655556   \n",
       "A07                      Selawik, AK     Roland Norton Memorial  66.765833   \n",
       "A08                   Pillar Bay, AK         Pillar Bay Airport  32.510556   \n",
       "A09              Johnstone Point, AK    Johnstone Point Airport        NaN   \n",
       "A11                   Bell Creek, AK         Bell Creek Airport  60.783889   \n",
       "A12                     Cinnabar, AK           Cinnabar Airport  60.785278   \n",
       "A13             Mountaintop Mine, AK   Mountaintop Mine Airport  61.341111   \n",
       "A14                    Whitebear, AK          Whitebear Airport  58.906389   \n",
       "A15                       Puviaq, AK             Puviaq Airport  70.718611   \n",
       "A16                   Arley Lake, AK         Arley Lake Airport  70.746944   \n",
       "...                              ...                        ...        ...   \n",
       "ZVA          Miandrivazo, Madagascar        Miandrivazo Airport -19.562780   \n",
       "ZVK                Savannakhet, Laos  Savannakhet International  16.556670   \n",
       "ZWL           Wollaston Lake, Canada     Wollaston Lake Airport  58.100000   \n",
       "ZXA                      Rota, Spain                    Rota NS  36.646667   \n",
       "ZXB                  Albuquerque, NM            Double Eagle II  35.146667   \n",
       "ZXC                      Gasquet, CA                 Ward Field  41.845833   \n",
       "ZXD   San Isidro, Dominican Republic   San Isidro International  18.503333   \n",
       "ZXE       Sculthorpe, United Kingdom         Sculthorpe Airport  52.845278   \n",
       "ZXF               Illinois Creek, AK     Illinois Creek Airport  64.116667   \n",
       "ZXH             Chomondely Sound, AK   Chomondely Sound Airport  55.239167   \n",
       "ZXI                     Coal Bay, AK           Coal Bay Airport  55.500556   \n",
       "ZXJ                Happy Harbour, AK      Happy Harbour Airport  55.488333   \n",
       "ZXK               Lancaster Cove, AK     Lancaster Cove Airport  55.216667   \n",
       "ZXL                    Reid Cove, AK          Reid Cove Airport  55.200000   \n",
       "ZXM                 Saltery Cove, AK       Saltery Cove Airport  55.408611   \n",
       "ZXN                   Clover Bay, AK         Clover Bay Airport  55.298889   \n",
       "ZXO                   El Capitan, AK            El Capitan Peak  56.184167   \n",
       "ZXP              Salmon Bay Lake, AK    Salmon Bay Lake Airport  56.237222   \n",
       "ZXQ                   Santa Ynez, CA         Santa Ynez Airport  34.606944   \n",
       "ZXT                   Cloverdale, CA       Cloverdale Municipal  38.774444   \n",
       "ZXU              North Kingstown, RI              Quonset State  41.595833   \n",
       "ZXV                     Nappanee, IN         Nappanee Municipal  41.446111   \n",
       "ZXW                     Paradise, CA           Paradise Skypark  39.709722   \n",
       "ZXX                    Kennewick, WA                Vista Field  46.218333   \n",
       "ZXY                        Delta, CO                Blake Field  38.783333   \n",
       "ZXZ                   Waterville, WA         Waterville Airport  47.654167   \n",
       "ZYL               Sylhet, Bangladesh                Savannakhet  24.963330   \n",
       "ZZU                    Mzuzu, Malawi              Mzuzu Airport -11.444720   \n",
       "ZZV                   Zanesville, OH       Zanesville Municipal  39.944440   \n",
       "ZZZ          Unknown Point in Alaska    Unknown Point in Alaska        NaN   \n",
       "\n",
       "             Lng  \n",
       "Code              \n",
       "01A  -152.275833  \n",
       "03A  -161.071667  \n",
       "04A          NaN  \n",
       "05A          NaN  \n",
       "06A          NaN  \n",
       "07A          NaN  \n",
       "08A          NaN  \n",
       "09A          NaN  \n",
       "1B1          NaN  \n",
       "1G4  -113.820000  \n",
       "1N7          NaN  \n",
       "1NY          NaN  \n",
       "6B0          NaN  \n",
       "7AK          NaN  \n",
       "8F3          NaN  \n",
       "A01  -147.361389  \n",
       "A02  -153.937778  \n",
       "A03  -154.023333  \n",
       "A04          NaN  \n",
       "A05  -115.513056  \n",
       "A06  -165.356389  \n",
       "A07  -160.152500  \n",
       "A08   -87.384722  \n",
       "A09          NaN  \n",
       "A11  -159.540278  \n",
       "A12  -158.864444  \n",
       "A13  -148.998889  \n",
       "A14  -157.711111  \n",
       "A15  -154.388333  \n",
       "A16  -155.360833  \n",
       "...          ...  \n",
       "ZVA    45.450830  \n",
       "ZVK   104.759440  \n",
       "ZWL  -103.183333  \n",
       "ZXA    -6.346111  \n",
       "ZXB  -106.805000  \n",
       "ZXC  -123.983333  \n",
       "ZXD   -69.762500  \n",
       "ZXE     0.767222  \n",
       "ZXF  -157.350000  \n",
       "ZXH  -132.114167  \n",
       "ZXI  -132.487500  \n",
       "ZXJ  -132.333333  \n",
       "ZXK  -132.086389  \n",
       "ZXL  -132.238333  \n",
       "ZXM  -132.329167  \n",
       "ZXN  -132.145000  \n",
       "ZXO  -133.310556  \n",
       "ZXP  -133.188889  \n",
       "ZXQ  -120.074722  \n",
       "ZXT  -122.991111  \n",
       "ZXU   -71.405556  \n",
       "ZXV   -85.934722  \n",
       "ZXW  -121.615278  \n",
       "ZXX  -119.209444  \n",
       "ZXY  -108.065278  \n",
       "ZXZ  -120.054167  \n",
       "ZYL    91.866940  \n",
       "ZZU    34.011670  \n",
       "ZZV   -81.892220  \n",
       "ZZZ          NaN  \n",
       "\n",
       "[6452 rows x 4 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airlines = pd.read_csv('data/L_AIRLINE_ID.csv', index_col='Code')\n",
    "airports = pd.read_csv('data/L_AIRPORT.csv', index_col='Code')\n",
    "\n",
    "airlines_splitted = airlines.Description.apply(lambda d: d.split(': '))\n",
    "airlines['CarrierName'] = airlines_splitted.apply(lambda d: d[0])\n",
    "airlines['CarrierCode'] = airlines_splitted.apply(lambda d: d[-1])\n",
    "airlines = airlines.drop('Description', axis=1)\n",
    "\n",
    "airports_splitted = airports.Description.apply(lambda d: d.split(': '))\n",
    "airports['AirportLocation'] = airports_splitted.apply(lambda d: d[0])\n",
    "airports['AirportName'] = airports_splitted.apply(lambda d: d[-1])\n",
    "airports = airports.merge(airports_loc, how='left', left_index=True, right_index=True)\n",
    "airports = airports.drop('Description', axis=1)\n",
    "\n",
    "airports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "travel_base_url = \"https://www.travelmath.com/airport/\"\n",
    "airport_coordinates_file = 'data/airport_coordinates.pickle'\n",
    "\n",
    "import requests as rq\n",
    "from bs4 import BeautifulSoup as bs\n",
    "import pickle\n",
    "import os\n",
    "\n",
    "def get_coordinates_for_airport(airport_code):\n",
    "    req = rq.get(travel_base_url+airport_code)\n",
    "    cont = bs(req.text, 'html.parser')\n",
    "    lat_long_elem = [ e for e in cont.select('div.leftcolumn p') if \"Latitude/Longitude:\" in e.text][0]\n",
    "    lat_long = list(lat_long_elem.children)[1].split(\",\")\n",
    "    lat, long = list(map(float, lat_long))\n",
    "    return lat, long\n",
    "\n",
    "def save_coords(airport_code, n):\n",
    "    if n % 100 == 0:\n",
    "        print(\"Airport {}\".format(n))\n",
    "    try:\n",
    "        lat, long = get_coordinates_for_airport(airport_code)\n",
    "        return airport_code, (lat, long)\n",
    "    except Exception:\n",
    "        return airport_code, None\n",
    "    \n",
    "if os.path.exists(airport_coordinates_file):\n",
    "    with open(airport_coordinates_file, 'rb') as f:\n",
    "        codes = pickle.load(f)\n",
    "else:\n",
    "    from concurrent import futures\n",
    "\n",
    "    with futures.ProcessPoolExecutor(20) as executor:\n",
    "        results = [executor.submit(save_coords, code, i) for i, code in enumerate(airports.index)]\n",
    "        done, not_done = futures.wait(results)\n",
    "    \n",
    "    codes = {}\n",
    "\n",
    "    for future in done:\n",
    "        airport_code, long_lat = future.result()\n",
    "        codes[airport_code] = long_lat\n",
    "\n",
    "    with open(airport_coordinates_file, 'wb') as f:\n",
    "        pickle.dump(codes, f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting downloads...\n",
      "All done!\n"
     ]
    }
   ],
   "source": [
    "# Download data zip from https://www.transtats.bts.gov/DL_SelectFields.asp?Table_ID=236&DB_Short_Name=On-Time\n",
    "# Data exists from January 1987 to September 2017\n",
    "\n",
    "from urllib.request import urlretrieve\n",
    "from IPython.display import clear_output\n",
    "from concurrent import futures\n",
    "import os\n",
    "import math\n",
    "from time import sleep\n",
    "\n",
    "transtats_base_download_url = \"https://transtats.bts.gov/PREZIP/\"\n",
    "filename_template = \"On_Time_On_Time_Performance_{year}_{month}.zip\"\n",
    "\n",
    "class DownloadHandler(object):\n",
    "    def __init__(self, downloads):\n",
    "        self.downloads = downloads\n",
    "        self.status = {}\n",
    "    \n",
    "    def start(self, parallelism=None):\n",
    "        print(\"Starting downloads...\")\n",
    "        with futures.ThreadPoolExecutor(parallelism) as executor:\n",
    "            results = [executor.submit(self.download_routes_zip, *args) for args in self.downloads]\n",
    "            while not self.done():\n",
    "                self.print_status()\n",
    "                sleep(5)\n",
    "            done, not_done = futures.wait(results)\n",
    "            print(\"All done!\")\n",
    "    \n",
    "    def done(self):\n",
    "        return all(map(lambda x: x[0] == 1, self.status.values()))\n",
    "    \n",
    "    def print_status(self):\n",
    "        for filename, (percent, total) in self.status.items():\n",
    "            print(\"{}: {:<30} [{}%, size={}]\".format(filename, \"#\"*int(math.round(30*percent)), percent, total))\n",
    "    \n",
    "    def download_routes_zip(self, year, month):\n",
    "        filename = filename_template.format(year=year, month=month)\n",
    "        dl_url = transtats_base_download_url + filename\n",
    "        dest_path = 'data/' + filename\n",
    "        if os.path.exists(dest_path) and os.stat(dest_path).st_size > 0:\n",
    "            self.status[filename] = (1, os.stat(dest_path).st_size)\n",
    "        else:\n",
    "            urlretrieve(dl_url, dest_path, reporthook=self.create_download_hook(filename))\n",
    "            print(\"Downloaded: {}\".format(filename))\n",
    "\n",
    "    def create_download_hook(self, filename):\n",
    "        def hook(downloaded_blocks, block_size, total_size):\n",
    "            self.status[filename] = (min(downloaded_blocks * block_size / total_size, 1), total_size)\n",
    "            print(self.status[filename])\n",
    "\n",
    "to_download = []\n",
    "for year in range(1987, 2018):\n",
    "    for month in range(1, 13):\n",
    "        if not (year == 2017 and month >= 10):\n",
    "            to_download.append((year,month))\n",
    "            \n",
    "dh = DownloadHandler(to_download)\n",
    "dh.start()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "routes = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/cranium/.anaconda3/envs/ada/lib/python3.5/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (77,84) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "import zipfile\n",
    "from io import BytesIO\n",
    "\n",
    "year = 2016\n",
    "for month in range(1,13):\n",
    "    with zipfile.ZipFile('data/On_Time_On_Time_Performance_{year}_{month}.zip'.format(year=year, month=month), 'r') as z:\n",
    "        for filename in z.namelist():\n",
    "            if filename.endswith(\".csv\"): \n",
    "                df = pd.read_csv(BytesIO(z.read(filename)))\n",
    "                new_routes = pd.DataFrame({'count': df.groupby([\"OriginAirportID\", \"DestAirportID\"]).size()})\n",
    "                if routes is None:\n",
    "                    routes = new_routes\n",
    "                else:\n",
    "                    routes = routes.add(new_routes, fill_value=0).astype(int) # type is converted to float when using add"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/cranium/.anaconda3/envs/ada/lib/python3.5/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (77) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n",
      "/home/cranium/.anaconda3/envs/ada/lib/python3.5/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (77,84) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n",
      "/home/cranium/.anaconda3/envs/ada/lib/python3.5/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (48,77,84) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n",
      "/home/cranium/.anaconda3/envs/ada/lib/python3.5/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (48,77,84,85) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n",
      "/home/cranium/.anaconda3/envs/ada/lib/python3.5/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (48,69,76,77,84) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "import zipfile\n",
    "from io import BytesIO\n",
    "\n",
    "routes = None\n",
    "\n",
    "# Sum of delays for year 2016 per airport, granularity of month\n",
    "year = 2016\n",
    "month = 10\n",
    "for month in range(1,13):\n",
    "    with zipfile.ZipFile('data/On_Time_On_Time_Performance_{year}_{month}.zip'.format(year=year, month=month), 'r') as z:\n",
    "        for filename in z.namelist():\n",
    "            if filename.endswith(\".csv\"): \n",
    "                df = pd.read_csv(BytesIO(z.read(filename)))\n",
    "                per_airport = df.groupby(['Origin'])\n",
    "                new_routes = pd.DataFrame({'flights': per_airport.size(), 'total_delays': per_airport['DepDelayMinutes'].sum()})\n",
    "                new_routes = new_routes.set_index([new_routes.index, [month]*len(new_routes)])\n",
    "                new_routes.index.names = [\"Airport\", \"Month\"]\n",
    "                if routes is None:\n",
    "                    routes = new_routes\n",
    "                else:\n",
    "                    routes = pd.concat([routes, new_routes])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "metadata": {},
   "outputs": [],
   "source": [
    "routes = routes.loc[routes.index.sort_values()]\n",
    "with open('data/airport_delays_2016_months.json', 'w') as f:\n",
    "    routes.to_csv(f, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['DepDelay',\n",
       " 'DepDelayMinutes',\n",
       " 'DepartureDelayGroups',\n",
       " 'ArrDelay',\n",
       " 'ArrDelayMinutes',\n",
       " 'ArrivalDelayGroups',\n",
       " 'CarrierDelay',\n",
       " 'WeatherDelay',\n",
       " 'NASDelay',\n",
       " 'SecurityDelay',\n",
       " 'LateAircraftDelay',\n",
       " 'DivArrDelay']"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "delay_columns = list(c for c in df.columns if \"Delay\" in c)\n",
    "delay_columns"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda env:ada]",
   "language": "python",
   "name": "conda-env-ada-py"
  },
  "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.5.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}