nb2-preparing-sample-data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# \"Big Pandas\" - Dask from the Inside\n",
    "## Part 2 - Retrieving BTS OTP sample data \n",
    "## PyData Amsterdam tutorial, Friday 7 April 2017\n",
    "## Stephen Simmons\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook is for downloading and preparing the original source data.\n",
    "\n",
    "You can also downloading pre-prepared data from here:  \n",
    "* http://www.stevesimmons.com/pydata-ams2017/flights-201601-201701-csv-xz.tar (151MB)\n",
    "* http://www.stevesimmons.com/pydata-ams2017/flights-201601-201701-parq.tar (158MB)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('1.11.3', '0.19.2', '0.14.1')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import dask\n",
    "import dask.dataframe as dd\n",
    "\n",
    "import graphviz\n",
    "import os\n",
    "import time\n",
    "\n",
    "np.__version__, pd.__version__, dask.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Support multiple lines of output in each cell\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\"\n",
    "\n",
    "# Don't wrap tables\n",
    "pd.options.display.max_rows = 20\n",
    "pd.options.display.width = 200\n",
    "\n",
    "# Show matplotlib graphs inline in Jupyter notebook\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# The sample data is the USA Bureau of Transportation Statistics 'On-Time' monthly series.\n",
    "# This has actual arrival/departure times versus schedule for every domestic flight\n",
    "# by major US carriers. For details, see the BTS website:\n",
    "#    https://www.transtats.bts.gov/DL_SelectFields.asp?Table_ID=236&DB_Short_Name=On-Time\n",
    "\n",
    "# We can download the monthly results directly from this URL, filling in the\n",
    "# two parameters with the year (e.g. '2016') and month ('1' for January, '12' for December).\n",
    "# The resulting zip files, each around 23MB in size when compressed,\n",
    "# contain a 200MB .csv file with the same name (On_Time_On_Time_Performance_2016_1.csv)\n",
    "# plus a 'readme.html' explaining the fields.\n",
    "OTP_URL = 'https://transtats.bts.gov/PREZIP/On_Time_On_Time_Performance_%s_%s.zip'\n",
    "\n",
    "OTP_COLUMNS_TO_LOAD = [\n",
    "        'FlightDate', 'Origin', 'Dest', 'Distance',\n",
    "        'Carrier', 'FlightNum', 'TailNum',\n",
    "        'CRSDepTime', 'CRSArrTime', 'CRSElapsedTime',\n",
    "        'Flights', 'Cancelled','Diverted',\n",
    "        'DepTime', 'ArrTime', 'ActualElapsedTime',\n",
    "        'DepDelay', 'ArrDelay', 'AirTime',\n",
    "    ]\n",
    "\n",
    "# Directory to store the resulting .zip files\n",
    "if os.path.exists('/home/stephen/do-not-backup'):\n",
    "    DIR_NAME = '/home/stephen/do-not-backup/data/usa-flights-otp'\n",
    "else:\n",
    "    DIR_NAME = '~/pydata-pandas/data'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Download USA flight data as described at \n",
    "# https://www.transtats.bts.gov/DL_SelectFields.asp?Table_ID=236&DB_Short_Name=On-Time\n",
    "import logging\n",
    "import multiprocessing\n",
    "import ssl\n",
    "import urllib.request\n",
    "\n",
    "BASE_URL = 'https://transtats.bts.gov//PREZIP/On_Time_On_Time_Performance_%s_%s.zip'\n",
    "DIR_NAME = '/home/stephen/do-not-backup/data/usa-flights-otp'\n",
    "\n",
    "def retrieve_data(year_month):\n",
    "    \"\"\"\n",
    "    Retrieve BTS on-time flight data for given year and month,\n",
    "    unpack csv data from zip file and save as 'flights-yyyy-mm.xz'.\n",
    "     \n",
    "    year_month  - Month of data to retrieve, in form of a tuple of ints \n",
    "                    like (2016, 1) for January 2016.\n",
    "    \"\"\"\n",
    "    os.makedirs(DIR_NAME, exist_ok=True)\n",
    "    filename = 'flights-%04d-%02d' % year_month\n",
    "    zip_path = os.path.join(DIR_NAME, filename + '.zip')\n",
    "    xz_path  = os.path.join(DIR_NAME, filename + '.xz' )\n",
    "    csv_filename = 'On_Time_On_Time_Performance_%s_%s.csv' % year_month\n",
    "\n",
    "    if os.path.exists(xz_path):\n",
    "        print(\"%s - Already have .xz file\" % filename)\n",
    "    else:\n",
    "        started = time.time()\n",
    "        # Get zip file's data\n",
    "        if os.path.exists(zip_path):\n",
    "            # Extract from previously downloaded zip file\n",
    "            print(\"%s - Reading csv from %s\" % (filename, zip_path))\n",
    "            zip_src = zip_path\n",
    "        else:\n",
    "            # Download zip file to memory\n",
    "            url = OTP_URL % year_month\n",
    "            print(\"%s - Downloading %s\" % (filename, url))\n",
    "            # We would like to do simply this:\n",
    "            #   urllib.request.urlretrieve(url, dest_path)\n",
    "            # but that gives SSL errors\n",
    "            ctx = ssl.create_default_context()\n",
    "            ctx.check_hostname = False\n",
    "            ctx.verify_mode = ssl.CERT_NONE\n",
    "            zip_data = urllib.request.urlopen(url, context=ctx).read() # 25MB\n",
    "            zip_src = io.BytesIO(zip_data)\n",
    "        # Extract csv data and recompress to .xz archive\n",
    "        csv_data = zipfile.ZipFile(zip_src).open(csv_filename).read()  # 200MB!\n",
    "        MB = 1024.0 * 1024.0\n",
    "        print(\"%s - csv data is %0.1fMB. Now compressing...\"\n",
    "                        % (filename, len(csv_data) / MB))\n",
    "        with lzma.open(xz_path, 'wb') as xzf:\n",
    "            xzf.write(csv_data)\n",
    "            csv_MB = xzf.tell() / MB\n",
    "            xz_MB = xzf._fp.tell() / MB\n",
    "        mins, secs = divmod(time.time() - started, 60)\n",
    "        print(\"%s - Compressed csv from %0.1fMB to %0.1fMB [%02d:%02d, %0.1fMB mem]\"\n",
    "                        % (filename, csv_MB, xz_MB, mins, secs, memory_usage() ))\n",
    "\n",
    "\n",
    "def download_flight_data(start='1988-01', end=None, num_threads=4):\n",
    "    \"\"\"\n",
    "    Download BTS On-Time flight data for one month or a range of months.\n",
    "    Data is available from '1987-12' to '2017-01' inclusive.\n",
    "    The resulting zip files are named 'files-yyyy-mm.zip'.\n",
    "    \"\"\"\n",
    "    end   = tuple(map(int, min(end or start, '2017-01').split('-')))\n",
    "    start = tuple(map(int, max(start,        '1987-12').split('-')))\n",
    "\n",
    "    dates = (\n",
    "        (year, month)\n",
    "            for year in range(end[0], start[0] - 1, -1)\n",
    "                for month in range(12, 0, -1)\n",
    "                    if start <= (year, month) <= end\n",
    "    )\n",
    "\n",
    "    multiprocessing.Pool(num_threads).map(retrieve_data, dates)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "download_flight_data('2016-01', '2016-12')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The downloaded zip files are around 23MB each, and contain two files:\n",
    "- The `.csv` data, around 220MB in size, with a name like `On_Time_On_Time_Performance_2014_8.csv`\n",
    "- `readme.html` explaining the fields.\n",
    "\n",
    "To simplify subsequent mass processing, let's recompress just the .csv file. Using xz compression results in files around 12MB, half the size of zip. \n",
    "\n",
    "```\n",
    "  $ for file in flights-*.zip; do \n",
    "      if [ ! -a \"${file%.zip}.xz\" ]; then \n",
    "        unzip -p \"$file\" *.csv | xz > \"${file%.zip}.xz\" \n",
    "      fi\n",
    "    done\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10 entries, 0 to 9\n",
      "Columns: 110 entries, Year to Unnamed: 109\n",
      "dtypes: float64(69), int64(27), object(14)\n",
      "memory usage: 8.7+ KB\n"
     ]
    }
   ],
   "source": [
    "path = os.path.join(DIR_NAME, 'flights-2017-01.xz')\n",
    "df = pd.read_csv(path, dialect=\"excel\", nrows=10)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def memory_usage(log=False):\n",
    "    \"\"\"Return current memory usage or print in log. Requires `psutil` package installed.\"\"\"\n",
    "    pid = os.getpid()\n",
    "    try:\n",
    "        import psutil\n",
    "        mem_MB = psutil.Process(pid).memory_info().rss / 1024.0 / 1024.0\n",
    "        msg = \"Memory for process %s is %0.1fMB\" % (pid, mem_MB)\n",
    "    except:\n",
    "        mem_MB = None\n",
    "        msg = \"Process is pid %s. Memory usage unavailable\" % pid\n",
    "    if log:\n",
    "        logging.info(msg)\n",
    "    return mem_MB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def load_one_month(yyyy_mm, nrows=None):\n",
    "    \"\"\"\n",
    "    Load one month's data as a pandas DataFrame. \n",
    "    Optionally limit max number of rows read.\n",
    "    \"\"\"\n",
    "    started = time.time()\n",
    "\n",
    "    # Load the csv from xz-compressed file\n",
    "    path = os.path.join(DIR_NAME, 'flights-%s.xz' % yyyy_mm)\n",
    "    df = pd.read_csv(path,\n",
    "                     dialect=\"excel\",\n",
    "                     usecols=OTP_COLUMNS_TO_LOAD,\n",
    "                     nrows=nrows,\n",
    "                     parse_dates=['FlightDate'],\n",
    "                     dtype={ 'FlightNum': str, }, # Keep as string, to later combine with carrier\n",
    "                     )\n",
    "\n",
    "    # Put columns in our standard order\n",
    "    df = df[OTP_COLUMNS_TO_LOAD]\n",
    "    df['FlightNum'] = df['Carrier'] + df['FlightNum']   # to give 'AA494'\n",
    "\n",
    "    mm, ss = divmod(time.time() - started, 60)\n",
    "    logging.info(\"Loading pd.DataFrame for %s took %02d:%02d (%dMB mem)\", yyyy_mm, mm, ss, memory_usage())\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 450017 entries, 0 to 450016\n",
      "Data columns (total 19 columns):\n",
      "FlightDate           450017 non-null datetime64[ns]\n",
      "Origin               450017 non-null object\n",
      "Dest                 450017 non-null object\n",
      "Distance             450017 non-null float64\n",
      "Carrier              450017 non-null object\n",
      "FlightNum            450017 non-null object\n",
      "TailNum              449378 non-null object\n",
      "CRSDepTime           450017 non-null int64\n",
      "CRSArrTime           450017 non-null int64\n",
      "CRSElapsedTime       450013 non-null float64\n",
      "Flights              450017 non-null float64\n",
      "Cancelled            450017 non-null float64\n",
      "Diverted             450017 non-null float64\n",
      "DepTime              441476 non-null float64\n",
      "ArrTime              440746 non-null float64\n",
      "ActualElapsedTime    439645 non-null float64\n",
      "DepDelay             441476 non-null float64\n",
      "ArrDelay             439645 non-null float64\n",
      "AirTime              439645 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(11), int64(2), object(5)\n",
      "memory usage: 65.2+ MB\n"
     ]
    }
   ],
   "source": [
    "%timeit\n",
    "df = load_one_month('2017-01')\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 450017 entries, 0 to 450016\n",
      "Data columns (total 19 columns):\n",
      "FlightDate           450017 non-null category\n",
      "Origin               450017 non-null category\n",
      "Dest                 450017 non-null category\n",
      "Distance             450017 non-null float64\n",
      "Carrier              450017 non-null category\n",
      "FlightNum            450017 non-null category\n",
      "TailNum              449378 non-null category\n",
      "CRSDepTime           450017 non-null int64\n",
      "CRSArrTime           450017 non-null int64\n",
      "CRSElapsedTime       450013 non-null float64\n",
      "Flights              450017 non-null float64\n",
      "Cancelled            450017 non-null float64\n",
      "Diverted             450017 non-null float64\n",
      "DepTime              441476 non-null float64\n",
      "ArrTime              440746 non-null float64\n",
      "ActualElapsedTime    439645 non-null float64\n",
      "DepDelay             441476 non-null float64\n",
      "ArrDelay             439645 non-null float64\n",
      "AirTime              439645 non-null float64\n",
      "dtypes: category(6), float64(11), int64(2)\n",
      "memory usage: 49.1 MB\n"
     ]
    }
   ],
   "source": [
    "for col in ['FlightDate','Origin','Dest','Carrier','FlightNum','TailNum']:\n",
    "    df[col] = df[col].astype('category', ordered=True)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['AA', 'AS', 'B6', 'DL', 'EV', 'F9', 'HA', 'NK', 'OO', 'UA', 'VX', 'WN'], dtype='object')"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Carrier'].cat.categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FlightDate</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Dest</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Carrier</th>\n",
       "      <th>FlightNum</th>\n",
       "      <th>TailNum</th>\n",
       "      <th>CRSDepTime</th>\n",
       "      <th>CRSArrTime</th>\n",
       "      <th>CRSElapsedTime</th>\n",
       "      <th>Flights</th>\n",
       "      <th>Cancelled</th>\n",
       "      <th>Diverted</th>\n",
       "      <th>DepTime</th>\n",
       "      <th>ArrTime</th>\n",
       "      <th>ActualElapsedTime</th>\n",
       "      <th>DepDelay</th>\n",
       "      <th>ArrDelay</th>\n",
       "      <th>AirTime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-01-17</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>AA</td>\n",
       "      <td>AA494</td>\n",
       "      <td>N583AA</td>\n",
       "      <td>1619</td>\n",
       "      <td>1856</td>\n",
       "      <td>277.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1616.0</td>\n",
       "      <td>1842.0</td>\n",
       "      <td>266.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>-14.0</td>\n",
       "      <td>244.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-01-18</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>AA</td>\n",
       "      <td>AA494</td>\n",
       "      <td>N544AA</td>\n",
       "      <td>1619</td>\n",
       "      <td>1856</td>\n",
       "      <td>277.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1614.0</td>\n",
       "      <td>1821.0</td>\n",
       "      <td>247.0</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>-35.0</td>\n",
       "      <td>228.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-01-19</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>AA</td>\n",
       "      <td>AA494</td>\n",
       "      <td>N553AA</td>\n",
       "      <td>1619</td>\n",
       "      <td>1856</td>\n",
       "      <td>277.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1611.0</td>\n",
       "      <td>1826.0</td>\n",
       "      <td>255.0</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>-30.0</td>\n",
       "      <td>236.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-01-20</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>AA</td>\n",
       "      <td>AA494</td>\n",
       "      <td>N191AA</td>\n",
       "      <td>1619</td>\n",
       "      <td>1856</td>\n",
       "      <td>277.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1656.0</td>\n",
       "      <td>1929.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>252.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-01-21</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>AA</td>\n",
       "      <td>AA494</td>\n",
       "      <td>N170AA</td>\n",
       "      <td>1619</td>\n",
       "      <td>1856</td>\n",
       "      <td>277.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1632.0</td>\n",
       "      <td>1858.0</td>\n",
       "      <td>266.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>245.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2017-01-22</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>AA</td>\n",
       "      <td>AA494</td>\n",
       "      <td>N179AA</td>\n",
       "      <td>1619</td>\n",
       "      <td>1856</td>\n",
       "      <td>277.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1636.0</td>\n",
       "      <td>1921.0</td>\n",
       "      <td>285.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>254.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2017-01-23</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>AA</td>\n",
       "      <td>AA494</td>\n",
       "      <td>N579AA</td>\n",
       "      <td>1619</td>\n",
       "      <td>1856</td>\n",
       "      <td>277.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1616.0</td>\n",
       "      <td>1907.0</td>\n",
       "      <td>291.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>264.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2017-01-24</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>AA</td>\n",
       "      <td>AA494</td>\n",
       "      <td>N583AA</td>\n",
       "      <td>1619</td>\n",
       "      <td>1856</td>\n",
       "      <td>277.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1619.0</td>\n",
       "      <td>1904.0</td>\n",
       "      <td>285.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>261.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2017-01-25</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>AA</td>\n",
       "      <td>AA494</td>\n",
       "      <td>N167AA</td>\n",
       "      <td>1619</td>\n",
       "      <td>1856</td>\n",
       "      <td>277.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1616.0</td>\n",
       "      <td>1906.0</td>\n",
       "      <td>290.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>262.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2017-01-26</td>\n",
       "      <td>CLT</td>\n",
       "      <td>PHX</td>\n",
       "      <td>1773.0</td>\n",
       "      <td>AA</td>\n",
       "      <td>AA494</td>\n",
       "      <td>N551AA</td>\n",
       "      <td>1619</td>\n",
       "      <td>1856</td>\n",
       "      <td>277.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1618.0</td>\n",
       "      <td>1902.0</td>\n",
       "      <td>284.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>268.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450007</th>\n",
       "      <td>2017-01-24</td>\n",
       "      <td>CMH</td>\n",
       "      <td>DAL</td>\n",
       "      <td>919.0</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN51</td>\n",
       "      <td>N8611F</td>\n",
       "      <td>1620</td>\n",
       "      <td>1805</td>\n",
       "      <td>165.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1616.0</td>\n",
       "      <td>1755.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>142.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450008</th>\n",
       "      <td>2017-01-24</td>\n",
       "      <td>CMH</td>\n",
       "      <td>DCA</td>\n",
       "      <td>323.0</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN509</td>\n",
       "      <td>N7849A</td>\n",
       "      <td>730</td>\n",
       "      <td>855</td>\n",
       "      <td>85.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>723.0</td>\n",
       "      <td>828.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>-7.0</td>\n",
       "      <td>-27.0</td>\n",
       "      <td>51.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450009</th>\n",
       "      <td>2017-01-24</td>\n",
       "      <td>CMH</td>\n",
       "      <td>DCA</td>\n",
       "      <td>323.0</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN1170</td>\n",
       "      <td>N7739A</td>\n",
       "      <td>1730</td>\n",
       "      <td>1845</td>\n",
       "      <td>75.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1722.0</td>\n",
       "      <td>1833.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450010</th>\n",
       "      <td>2017-01-24</td>\n",
       "      <td>CMH</td>\n",
       "      <td>DEN</td>\n",
       "      <td>1154.0</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN1059</td>\n",
       "      <td>N706SW</td>\n",
       "      <td>1850</td>\n",
       "      <td>2010</td>\n",
       "      <td>200.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1844.0</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>203.0</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>185.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450011</th>\n",
       "      <td>2017-01-24</td>\n",
       "      <td>CMH</td>\n",
       "      <td>FLL</td>\n",
       "      <td>973.0</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN116</td>\n",
       "      <td>N600WN</td>\n",
       "      <td>1035</td>\n",
       "      <td>1320</td>\n",
       "      <td>165.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1034.0</td>\n",
       "      <td>1307.0</td>\n",
       "      <td>153.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-13.0</td>\n",
       "      <td>133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450012</th>\n",
       "      <td>2017-01-24</td>\n",
       "      <td>CMH</td>\n",
       "      <td>LAS</td>\n",
       "      <td>1771.0</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN510</td>\n",
       "      <td>N8504G</td>\n",
       "      <td>800</td>\n",
       "      <td>930</td>\n",
       "      <td>270.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>757.0</td>\n",
       "      <td>933.0</td>\n",
       "      <td>276.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>262.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450013</th>\n",
       "      <td>2017-01-24</td>\n",
       "      <td>CMH</td>\n",
       "      <td>LAS</td>\n",
       "      <td>1771.0</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN902</td>\n",
       "      <td>N8692F</td>\n",
       "      <td>1430</td>\n",
       "      <td>1555</td>\n",
       "      <td>265.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1424.0</td>\n",
       "      <td>1557.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>256.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450014</th>\n",
       "      <td>2017-01-24</td>\n",
       "      <td>CMH</td>\n",
       "      <td>MCO</td>\n",
       "      <td>802.0</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN1093</td>\n",
       "      <td>N927WN</td>\n",
       "      <td>1415</td>\n",
       "      <td>1630</td>\n",
       "      <td>135.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1412.0</td>\n",
       "      <td>1610.0</td>\n",
       "      <td>118.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>-20.0</td>\n",
       "      <td>103.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450015</th>\n",
       "      <td>2017-01-24</td>\n",
       "      <td>CMH</td>\n",
       "      <td>MCO</td>\n",
       "      <td>802.0</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN1893</td>\n",
       "      <td>N604SW</td>\n",
       "      <td>755</td>\n",
       "      <td>1010</td>\n",
       "      <td>135.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>751.0</td>\n",
       "      <td>953.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>-17.0</td>\n",
       "      <td>103.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450016</th>\n",
       "      <td>2017-01-24</td>\n",
       "      <td>CMH</td>\n",
       "      <td>MCO</td>\n",
       "      <td>802.0</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN4314</td>\n",
       "      <td>N449WN</td>\n",
       "      <td>1620</td>\n",
       "      <td>1840</td>\n",
       "      <td>140.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1615.0</td>\n",
       "      <td>1809.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>-31.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>450017 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       FlightDate Origin Dest  Distance Carrier FlightNum TailNum  CRSDepTime  CRSArrTime  CRSElapsedTime  Flights  Cancelled  Diverted  DepTime  ArrTime  ActualElapsedTime  DepDelay  ArrDelay  AirTime\n",
       "0      2017-01-17    CLT  PHX    1773.0      AA     AA494  N583AA        1619        1856           277.0      1.0        0.0       0.0   1616.0   1842.0              266.0      -3.0     -14.0    244.0\n",
       "1      2017-01-18    CLT  PHX    1773.0      AA     AA494  N544AA        1619        1856           277.0      1.0        0.0       0.0   1614.0   1821.0              247.0      -5.0     -35.0    228.0\n",
       "2      2017-01-19    CLT  PHX    1773.0      AA     AA494  N553AA        1619        1856           277.0      1.0        0.0       0.0   1611.0   1826.0              255.0      -8.0     -30.0    236.0\n",
       "3      2017-01-20    CLT  PHX    1773.0      AA     AA494  N191AA        1619        1856           277.0      1.0        0.0       0.0   1656.0   1929.0              273.0      37.0      33.0    252.0\n",
       "4      2017-01-21    CLT  PHX    1773.0      AA     AA494  N170AA        1619        1856           277.0      1.0        0.0       0.0   1632.0   1858.0              266.0      13.0       2.0    245.0\n",
       "5      2017-01-22    CLT  PHX    1773.0      AA     AA494  N179AA        1619        1856           277.0      1.0        0.0       0.0   1636.0   1921.0              285.0      17.0      25.0    254.0\n",
       "6      2017-01-23    CLT  PHX    1773.0      AA     AA494  N579AA        1619        1856           277.0      1.0        0.0       0.0   1616.0   1907.0              291.0      -3.0      11.0    264.0\n",
       "7      2017-01-24    CLT  PHX    1773.0      AA     AA494  N583AA        1619        1856           277.0      1.0        0.0       0.0   1619.0   1904.0              285.0       0.0       8.0    261.0\n",
       "8      2017-01-25    CLT  PHX    1773.0      AA     AA494  N167AA        1619        1856           277.0      1.0        0.0       0.0   1616.0   1906.0              290.0      -3.0      10.0    262.0\n",
       "9      2017-01-26    CLT  PHX    1773.0      AA     AA494  N551AA        1619        1856           277.0      1.0        0.0       0.0   1618.0   1902.0              284.0      -1.0       6.0    268.0\n",
       "...           ...    ...  ...       ...     ...       ...     ...         ...         ...             ...      ...        ...       ...      ...      ...                ...       ...       ...      ...\n",
       "450007 2017-01-24    CMH  DAL     919.0      WN      WN51  N8611F        1620        1805           165.0      1.0        0.0       0.0   1616.0   1755.0              159.0      -4.0     -10.0    142.0\n",
       "450008 2017-01-24    CMH  DCA     323.0      WN     WN509  N7849A         730         855            85.0      1.0        0.0       0.0    723.0    828.0               65.0      -7.0     -27.0     51.0\n",
       "450009 2017-01-24    CMH  DCA     323.0      WN    WN1170  N7739A        1730        1845            75.0      1.0        0.0       0.0   1722.0   1833.0               71.0      -8.0     -12.0     55.0\n",
       "450010 2017-01-24    CMH  DEN    1154.0      WN    WN1059  N706SW        1850        2010           200.0      1.0        0.0       0.0   1844.0   2007.0              203.0      -6.0      -3.0    185.0\n",
       "450011 2017-01-24    CMH  FLL     973.0      WN     WN116  N600WN        1035        1320           165.0      1.0        0.0       0.0   1034.0   1307.0              153.0      -1.0     -13.0    133.0\n",
       "450012 2017-01-24    CMH  LAS    1771.0      WN     WN510  N8504G         800         930           270.0      1.0        0.0       0.0    757.0    933.0              276.0      -3.0       3.0    262.0\n",
       "450013 2017-01-24    CMH  LAS    1771.0      WN     WN902  N8692F        1430        1555           265.0      1.0        0.0       0.0   1424.0   1557.0              273.0      -6.0       2.0    256.0\n",
       "450014 2017-01-24    CMH  MCO     802.0      WN    WN1093  N927WN        1415        1630           135.0      1.0        0.0       0.0   1412.0   1610.0              118.0      -3.0     -20.0    103.0\n",
       "450015 2017-01-24    CMH  MCO     802.0      WN    WN1893  N604SW         755        1010           135.0      1.0        0.0       0.0    751.0    953.0              122.0      -4.0     -17.0    103.0\n",
       "450016 2017-01-24    CMH  MCO     802.0      WN    WN4314  N449WN        1620        1840           140.0      1.0        0.0       0.0   1615.0   1809.0              114.0      -5.0     -31.0    100.0\n",
       "\n",
       "[450017 rows x 19 columns]"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "52720496"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.memory_usage(deep=True).sum()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}