Reading_data_py.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "outstanding-august",
   "metadata": {},
   "source": [
    "# Reading data\n",
    "* We will only consider csv files\n",
    "* Starting with FSU data file provided by Copa\n",
    "\n",
    "## Objective\n",
    "* How to read and treat data encountered for the first time\n",
    "* Look at types\n",
    "* Look at undefined data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ignored-lucas",
   "metadata": {},
   "source": [
    "# Use magic commands (%)\n",
    "\n",
    "# When modifying libraries, no need to restart the kernel\n",
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "cooked-fraud",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['/Users/erlebach/Dropbox/Copa_excel_files_python/copa/Rosetta Stone_Copa',\n",
       " '/Users/erlebach/Dropbox/Copa_excel_files_python/copa',\n",
       " '/usr/lib',\n",
       " '/Users/erlebach/Dropbox/Copa_excel_files_python/copa/Rosetta Stone_Copa',\n",
       " '/Users/erlebach/opt/anaconda3/envs/copa/lib/python39.zip',\n",
       " '/Users/erlebach/opt/anaconda3/envs/copa/lib/python3.9',\n",
       " '/Users/erlebach/opt/anaconda3/envs/copa/lib/python3.9/lib-dynload',\n",
       " '',\n",
       " '/Users/erlebach/opt/anaconda3/envs/copa/lib/python3.9/site-packages',\n",
       " '/Users/erlebach/opt/anaconda3/envs/copa/lib/python3.9/site-packages/pgmpy-0.1.14-py3.9.egg',\n",
       " '/Users/erlebach/opt/anaconda3/envs/copa/lib/python3.9/site-packages/IPython/extensions',\n",
       " '/Users/erlebach/.ipython']"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Make sure the paths are set correctly, either via sys.path or using \n",
    "# the PYTHONPATH environment variable.\n",
    "import sys\n",
    "sys.path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "careful-miniature",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/erlebach/Dropbox/Copa_excel_files_python/copa/Rosetta Stone_Copa\n"
     ]
    }
   ],
   "source": [
    "# Make sure the default directory is as expected\n",
    "!pwd    # pwd() is an alternative"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "prime-architecture",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/Users/erlebach/Dropbox/Copa_excel_files_python/copa:/usr/lib:'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check environment variable PYTHONPATH\n",
    "import os\n",
    "os.environ['PYTHONPATH']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "terminal-darwin",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "\n",
    "import src.copa.util_functions as u"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "understood-shadow",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Setup Pandas to display more columns, wider columns. \n",
    "# Limit number precision to two significant digits\n",
    "pd.set_option('display.max_rows', 200)\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.width', None)\n",
    "pd.set_option('display.max_colwidth', 30)\n",
    "pd.set_option('display.max_seq_items', 100)\n",
    "pd.set_option('display.precision', 2)\n",
    "\n",
    "# This function is defined in util_functions.setPandasOptions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cognitive-father",
   "metadata": {},
   "source": [
    "## Starting with the FSU Table\n",
    "* I will assume no knowledge of the file"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "noble-element",
   "metadata": {},
   "source": [
    "## Open the file\n",
    "* Analyze its structure\n",
    "* How many missing variables\n",
    "* Column types\n",
    "* Dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "infectious-cheese",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/erlebach/opt/anaconda3/envs/copa/lib/python3.9/site-packages/IPython/core/interactiveshell.py:3165: DtypeWarning: Columns (0,4,5,15,16,17,18,21,22,23,24,31,33) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,\n"
     ]
    }
   ],
   "source": [
    "fsu = pd.read_csv(\"../data/Copa-FSU-02172021.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "exotic-library",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ACTUAL_BLOCK_HR</th>\n",
       "      <th>AIRCRAFT_TYPE</th>\n",
       "      <th>AIRLINE_DESIGNATOR_CD</th>\n",
       "      <th>ARR_DELAY_INTERVAL</th>\n",
       "      <th>ARR_DELAY_MINUTES</th>\n",
       "      <th>ARR_DELAY_MINUTES_DV</th>\n",
       "      <th>BANK OVERALL</th>\n",
       "      <th>BANK_CD</th>\n",
       "      <th>BASE_CD</th>\n",
       "      <th>CANCEL_CD</th>\n",
       "      <th>CANCEL_REASON_DESCRIPTION</th>\n",
       "      <th>CAPACITY_C_CNT</th>\n",
       "      <th>CAPACITY_CNT</th>\n",
       "      <th>CAPACITY_Y_CNT</th>\n",
       "      <th>CLAVLOCOD</th>\n",
       "      <th>COUNT_ARR00</th>\n",
       "      <th>COUNT_ARR14</th>\n",
       "      <th>COUNT_ARR15</th>\n",
       "      <th>COUNT_ARR90</th>\n",
       "      <th>COUNT_ATB</th>\n",
       "      <th>COUNT_CANCELLATIONS</th>\n",
       "      <th>COUNT_DEP00</th>\n",
       "      <th>COUNT_DEP05</th>\n",
       "      <th>COUNT_DEP15</th>\n",
       "      <th>COUNT_DEP80</th>\n",
       "      <th>COUNT_DIVERSION</th>\n",
       "      <th>COUNT_DLY_FLTS_ARR00</th>\n",
       "      <th>COUNT_DLY_FLTS_ARR14</th>\n",
       "      <th>COUNT_DLY_FLTS_D3H</th>\n",
       "      <th>COUNT_DLY_FLTS_DEP00</th>\n",
       "      <th>COUNT_GTB</th>\n",
       "      <th>CREW_CNT</th>\n",
       "      <th>DEP_DELAY_INTERVAL</th>\n",
       "      <th>DEP_DELAY_MINUTES</th>\n",
       "      <th>DEST_CD</th>\n",
       "      <th>DWHLOAD_DTML</th>\n",
       "      <th>ETA_DTMZ</th>\n",
       "      <th>ETD_DTMZ</th>\n",
       "      <th>FLT_ACTUAL_HR</th>\n",
       "      <th>FLT_NUM</th>\n",
       "      <th>FLT_TYPE_CD</th>\n",
       "      <th>FLT_TYPE_NAME</th>\n",
       "      <th>FOD</th>\n",
       "      <th>FOD_REAL</th>\n",
       "      <th>HUB_STN</th>\n",
       "      <th>IN_DTMZ</th>\n",
       "      <th>LEG_STATUS_CD</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>OD</th>\n",
       "      <th>OFF_DTMZ</th>\n",
       "      <th>ON_DTMZ</th>\n",
       "      <th>OPERATED_ALL</th>\n",
       "      <th>OPERATED_CNT</th>\n",
       "      <th>OPERATED_CNT_ALL</th>\n",
       "      <th>ORIG_CD</th>\n",
       "      <th>ORIGIN_PLANNED</th>\n",
       "      <th>OUT_DTMZ</th>\n",
       "      <th>Q</th>\n",
       "      <th>ROTATION_@STATION</th>\n",
       "      <th>ROTATION_AVAILABLE_TM</th>\n",
       "      <th>ROTATION_PLANNED_TM</th>\n",
       "      <th>ROTATION_REAL_TM</th>\n",
       "      <th>SCH_ARR_DTL</th>\n",
       "      <th>SCH_ARR_DTML_PTY</th>\n",
       "      <th>SCH_ARR_DTMZ</th>\n",
       "      <th>SCH_ARR_DTZ</th>\n",
       "      <th>SCH_ARR_TML_PTY</th>\n",
       "      <th>SCH_ARR_TMZ</th>\n",
       "      <th>SCH_BLOCK_HR</th>\n",
       "      <th>SCH_DEP_DTL</th>\n",
       "      <th>SCH_DEP_DTML_PTY</th>\n",
       "      <th>SCH_DEP_DTMZ</th>\n",
       "      <th>SCH_DEP_DTZ</th>\n",
       "      <th>SCH_DEP_TML_PTY</th>\n",
       "      <th>SCH_DEP_TMZ</th>\n",
       "      <th>SCH_DEST_CD</th>\n",
       "      <th>SCHD_CNT_ALL</th>\n",
       "      <th>SCHEDULED_ALL</th>\n",
       "      <th>SCHEDULED_CNT</th>\n",
       "      <th>TAIL</th>\n",
       "      <th>TAXI_IN_MINUTES</th>\n",
       "      <th>TAXI_OUT_MINUTES</th>\n",
       "      <th>TIPO_OPR</th>\n",
       "      <th>WEEK</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>AC_OWNER_CARRIER_CD</th>\n",
       "      <th>AIRCRAFT_TYPE_ICAO</th>\n",
       "      <th>FUENTE</th>\n",
       "      <th>IN</th>\n",
       "      <th>ON</th>\n",
       "      <th>OUT</th>\n",
       "      <th>OFF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.80</td>\n",
       "      <td>700</td>\n",
       "      <td>CM</td>\n",
       "      <td>-3:02</td>\n",
       "      <td>-182.00</td>\n",
       "      <td>-182.00</td>\n",
       "      <td>Off Bank</td>\n",
       "      <td>Off Bank</td>\n",
       "      <td>CMP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>?</td>\n",
       "      <td>12.0</td>\n",
       "      <td>124.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>T</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.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.0</td>\n",
       "      <td>6.00</td>\n",
       "      <td>-0:11</td>\n",
       "      <td>-11.00</td>\n",
       "      <td>MEX</td>\n",
       "      <td>2/15/2020 09:53:37.000</td>\n",
       "      <td>2/15/2020 15:19:00.000</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>195</td>\n",
       "      <td>J</td>\n",
       "      <td>GTB</td>\n",
       "      <td>195MEXMEX</td>\n",
       "      <td>195MEXMEX</td>\n",
       "      <td>INTRACAM</td>\n",
       "      <td>2/15/2020 12:28:00.000</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2/1/2020</td>\n",
       "      <td>MEXMEX</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>MEX</td>\n",
       "      <td>MEX</td>\n",
       "      <td>2/15/2020 11:40:00.000</td>\n",
       "      <td>Q1</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2/15/2020</td>\n",
       "      <td>2/15/2020 10:30:00.000</td>\n",
       "      <td>2/15/2020 15:30:00.000</td>\n",
       "      <td>2/15/2020</td>\n",
       "      <td>10:30:00.000000</td>\n",
       "      <td>15:30:00.000000</td>\n",
       "      <td>3.65</td>\n",
       "      <td>2/15/2020</td>\n",
       "      <td>2/15/2020 06:51:00.000</td>\n",
       "      <td>2/15/2020 11:51:00.000</td>\n",
       "      <td>2/15/2020</td>\n",
       "      <td>06:51:00.000000</td>\n",
       "      <td>11:51:00.000000</td>\n",
       "      <td>MEX</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>HP1530</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>GTB</td>\n",
       "      <td>7</td>\n",
       "      <td>2020</td>\n",
       "      <td>CM</td>\n",
       "      <td>B737</td>\n",
       "      <td>TERADATA</td>\n",
       "      <td>12:28:00.000000</td>\n",
       "      <td>?</td>\n",
       "      <td>11:40:00.000000</td>\n",
       "      <td>?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4.07</td>\n",
       "      <td>800</td>\n",
       "      <td>CM</td>\n",
       "      <td>0:26</td>\n",
       "      <td>26.00</td>\n",
       "      <td>26.00</td>\n",
       "      <td>B6</td>\n",
       "      <td>B6 Dep</td>\n",
       "      <td>CMP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>?</td>\n",
       "      <td>16.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>144.0</td>\n",
       "      <td>T</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.00</td>\n",
       "      <td>-0:01</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>LIM</td>\n",
       "      <td>2/13/2020 09:15:48.000</td>\n",
       "      <td>2/13/2020 05:49:00.000</td>\n",
       "      <td>?</td>\n",
       "      <td>3.18</td>\n",
       "      <td>337</td>\n",
       "      <td>J</td>\n",
       "      <td>Schedule</td>\n",
       "      <td>337PTYLIM</td>\n",
       "      <td>337PTYLIM</td>\n",
       "      <td>HUB</td>\n",
       "      <td>2/13/2020 06:26:00.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2/1/2020</td>\n",
       "      <td>PTYLIM</td>\n",
       "      <td>2/13/2020 02:38:00.000</td>\n",
       "      <td>2/13/2020 05:49:00.000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>PTY</td>\n",
       "      <td>PTY</td>\n",
       "      <td>2/13/2020 02:22:00.000</td>\n",
       "      <td>Q1</td>\n",
       "      <td>PTY</td>\n",
       "      <td>80</td>\n",
       "      <td>67</td>\n",
       "      <td>79</td>\n",
       "      <td>2/13/2020</td>\n",
       "      <td>2/13/2020 01:00:00.000</td>\n",
       "      <td>2/13/2020 06:00:00.000</td>\n",
       "      <td>2/13/2020</td>\n",
       "      <td>01:00:00.000000</td>\n",
       "      <td>06:00:00.000000</td>\n",
       "      <td>3.62</td>\n",
       "      <td>2/12/2020</td>\n",
       "      <td>2/12/2020 21:23:00.000</td>\n",
       "      <td>2/13/2020 02:23:00.000</td>\n",
       "      <td>2/13/2020</td>\n",
       "      <td>21:23:00.000000</td>\n",
       "      <td>02:23:00.000000</td>\n",
       "      <td>LIM</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>HP1852</td>\n",
       "      <td>37.00</td>\n",
       "      <td>16.00</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>2020</td>\n",
       "      <td>CM</td>\n",
       "      <td>B738</td>\n",
       "      <td>TERADATA</td>\n",
       "      <td>06:26:00.000000</td>\n",
       "      <td>05:49:00.000000</td>\n",
       "      <td>02:22:00.000000</td>\n",
       "      <td>02:38:00.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.02</td>\n",
       "      <td>800</td>\n",
       "      <td>CM</td>\n",
       "      <td>0:02</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>B3</td>\n",
       "      <td>B3 Dep</td>\n",
       "      <td>CMP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>?</td>\n",
       "      <td>16.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>144.0</td>\n",
       "      <td>T</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.00</td>\n",
       "      <td>0:00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>TGU</td>\n",
       "      <td>12/6/2019 13:30:31.000</td>\n",
       "      <td>12/6/2019 18:15:00.000</td>\n",
       "      <td>?</td>\n",
       "      <td>1.48</td>\n",
       "      <td>426</td>\n",
       "      <td>J</td>\n",
       "      <td>Schedule</td>\n",
       "      <td>426PTYTGU</td>\n",
       "      <td>426PTYTGU</td>\n",
       "      <td>HUB</td>\n",
       "      <td>12/6/2019 18:23:00.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12/1/2019</td>\n",
       "      <td>PTYTGU</td>\n",
       "      <td>12/6/2019 16:45:00.000</td>\n",
       "      <td>12/6/2019 18:14:00.000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>PTY</td>\n",
       "      <td>PTY</td>\n",
       "      <td>12/6/2019 16:22:00.000</td>\n",
       "      <td>Q4</td>\n",
       "      <td>PTY</td>\n",
       "      <td>91</td>\n",
       "      <td>80</td>\n",
       "      <td>91</td>\n",
       "      <td>12/6/2019</td>\n",
       "      <td>12/6/2019 13:21:00.000</td>\n",
       "      <td>12/6/2019 18:21:00.000</td>\n",
       "      <td>12/6/2019</td>\n",
       "      <td>13:21:00.000000</td>\n",
       "      <td>18:21:00.000000</td>\n",
       "      <td>1.98</td>\n",
       "      <td>12/6/2019</td>\n",
       "      <td>12/6/2019 11:22:00.000</td>\n",
       "      <td>12/6/2019 16:22:00.000</td>\n",
       "      <td>12/6/2019</td>\n",
       "      <td>11:22:00.000000</td>\n",
       "      <td>16:22:00.000000</td>\n",
       "      <td>TGU</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>HP1856</td>\n",
       "      <td>9.00</td>\n",
       "      <td>23.00</td>\n",
       "      <td>1</td>\n",
       "      <td>49</td>\n",
       "      <td>2019</td>\n",
       "      <td>CM</td>\n",
       "      <td>B738</td>\n",
       "      <td>TERADATA</td>\n",
       "      <td>18:23:00.000000</td>\n",
       "      <td>18:14:00.000000</td>\n",
       "      <td>16:22:00.000000</td>\n",
       "      <td>16:45:00.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.63</td>\n",
       "      <td>E90</td>\n",
       "      <td>CM</td>\n",
       "      <td>0:49</td>\n",
       "      <td>49.00</td>\n",
       "      <td>49.00</td>\n",
       "      <td>B4</td>\n",
       "      <td>B4 Dep</td>\n",
       "      <td>CMP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>?</td>\n",
       "      <td>10.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>T</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.00</td>\n",
       "      <td>0:21</td>\n",
       "      <td>21.00</td>\n",
       "      <td>PTY</td>\n",
       "      <td>10/22/2019 18:12:55.000</td>\n",
       "      <td>10/22/2019 00:00:00.000</td>\n",
       "      <td>10/22/2019 21:50:00.000</td>\n",
       "      <td>1.03</td>\n",
       "      <td>15</td>\n",
       "      <td>J</td>\n",
       "      <td>ATB</td>\n",
       "      <td>15PTYPTY</td>\n",
       "      <td>15PTYPTY</td>\n",
       "      <td>HUB</td>\n",
       "      <td>10/22/2019 23:00:00.000</td>\n",
       "      <td>6.0</td>\n",
       "      <td>10/1/2019</td>\n",
       "      <td>PTYPTY</td>\n",
       "      <td>10/22/2019 21:50:00.000</td>\n",
       "      <td>10/22/2019 22:52:00.000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>PTY</td>\n",
       "      <td>PTY</td>\n",
       "      <td>10/22/2019 21:22:00.000</td>\n",
       "      <td>Q4</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>10/22/2019</td>\n",
       "      <td>10/22/2019 17:11:00.000</td>\n",
       "      <td>10/22/2019 22:11:00.000</td>\n",
       "      <td>10/22/2019</td>\n",
       "      <td>17:11:00.000000</td>\n",
       "      <td>22:11:00.000000</td>\n",
       "      <td>1.17</td>\n",
       "      <td>10/22/2019</td>\n",
       "      <td>10/22/2019 16:01:00.000</td>\n",
       "      <td>10/22/2019 21:01:00.000</td>\n",
       "      <td>10/22/2019</td>\n",
       "      <td>16:01:00.000000</td>\n",
       "      <td>21:01:00.000000</td>\n",
       "      <td>PTY</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>HP1568</td>\n",
       "      <td>8.00</td>\n",
       "      <td>28.00</td>\n",
       "      <td>ATB</td>\n",
       "      <td>43</td>\n",
       "      <td>2019</td>\n",
       "      <td>CM</td>\n",
       "      <td>E190</td>\n",
       "      <td>TERADATA</td>\n",
       "      <td>23:00:00.000000</td>\n",
       "      <td>22:52:00.000000</td>\n",
       "      <td>21:22:00.000000</td>\n",
       "      <td>21:50:00.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.90</td>\n",
       "      <td>700</td>\n",
       "      <td>CM</td>\n",
       "      <td>-1:47</td>\n",
       "      <td>-107.00</td>\n",
       "      <td>-107.00</td>\n",
       "      <td>B2</td>\n",
       "      <td>B2 Dep</td>\n",
       "      <td>CMP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>?</td>\n",
       "      <td>12.0</td>\n",
       "      <td>124.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>T</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.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.0</td>\n",
       "      <td>6.00</td>\n",
       "      <td>-0:10</td>\n",
       "      <td>-10.00</td>\n",
       "      <td>PTY</td>\n",
       "      <td>2/26/2020 13:46:09.000</td>\n",
       "      <td>2/26/2020 17:11:00.000</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>358</td>\n",
       "      <td>J</td>\n",
       "      <td>GTB</td>\n",
       "      <td>358PTYPTY</td>\n",
       "      <td>358PTYPTY</td>\n",
       "      <td>HUB</td>\n",
       "      <td>2/26/2020 15:40:00.000</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2/1/2020</td>\n",
       "      <td>PTYPTY</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>PTY</td>\n",
       "      <td>PTY</td>\n",
       "      <td>2/26/2020 14:46:00.000</td>\n",
       "      <td>Q1</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2/26/2020</td>\n",
       "      <td>2/26/2020 12:27:00.000</td>\n",
       "      <td>2/26/2020 17:27:00.000</td>\n",
       "      <td>2/26/2020</td>\n",
       "      <td>12:27:00.000000</td>\n",
       "      <td>17:27:00.000000</td>\n",
       "      <td>2.52</td>\n",
       "      <td>2/26/2020</td>\n",
       "      <td>2/26/2020 09:56:00.000</td>\n",
       "      <td>2/26/2020 14:56:00.000</td>\n",
       "      <td>2/26/2020</td>\n",
       "      <td>09:56:00.000000</td>\n",
       "      <td>14:56:00.000000</td>\n",
       "      <td>PTY</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>HP1374</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>GTB</td>\n",
       "      <td>9</td>\n",
       "      <td>2020</td>\n",
       "      <td>CM</td>\n",
       "      <td>B737</td>\n",
       "      <td>TERADATA</td>\n",
       "      <td>15:40:00.000000</td>\n",
       "      <td>?</td>\n",
       "      <td>14:46:00.000000</td>\n",
       "      <td>?</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",
       "      <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",
       "      <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",
       "      <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",
       "      <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>54069</th>\n",
       "      <td>5.27</td>\n",
       "      <td>800</td>\n",
       "      <td>CM</td>\n",
       "      <td>0:18</td>\n",
       "      <td>18.0</td>\n",
       "      <td>18.00</td>\n",
       "      <td>B4</td>\n",
       "      <td>B4 Arr</td>\n",
       "      <td>CMP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>?</td>\n",
       "      <td>16.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>144.0</td>\n",
       "      <td>T</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.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.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0:12</td>\n",
       "      <td>12.0</td>\n",
       "      <td>PTY</td>\n",
       "      <td>1/28/2020 09:10:52.000</td>\n",
       "      <td>12/25/2019 19:24:00.000</td>\n",
       "      <td>12/25/2019 14:30:00.000</td>\n",
       "      <td>4.75</td>\n",
       "      <td>357</td>\n",
       "      <td>J</td>\n",
       "      <td>Schedule</td>\n",
       "      <td>357IADPTY</td>\n",
       "      <td>357IADPTY</td>\n",
       "      <td>OUTSTATIONS</td>\n",
       "      <td>12/25/2019 19:29:00.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12/1/2019</td>\n",
       "      <td>IADPTY</td>\n",
       "      <td>12/25/2019 14:39:00.000</td>\n",
       "      <td>12/25/2019 19:24:00.000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>IAD</td>\n",
       "      <td>IAD</td>\n",
       "      <td>12/25/2019 14:13:00.000</td>\n",
       "      <td>Q4</td>\n",
       "      <td>IAD</td>\n",
       "      <td>392</td>\n",
       "      <td>387</td>\n",
       "      <td>404</td>\n",
       "      <td>12/25/2019</td>\n",
       "      <td>12/25/2019 14:11:00.000</td>\n",
       "      <td>12/25/2019 19:11:00.000</td>\n",
       "      <td>12/25/2019</td>\n",
       "      <td>14:11:00.000000</td>\n",
       "      <td>19:11:00.000000</td>\n",
       "      <td>5.17</td>\n",
       "      <td>12/25/2019</td>\n",
       "      <td>12/25/2019 09:01:00.000</td>\n",
       "      <td>12/25/2019 14:01:00.000</td>\n",
       "      <td>12/25/2019</td>\n",
       "      <td>09:01:00.000000</td>\n",
       "      <td>14:01:00.000000</td>\n",
       "      <td>PTY</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>HP1523</td>\n",
       "      <td>5.00</td>\n",
       "      <td>26.00</td>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "      <td>2019</td>\n",
       "      <td>CM</td>\n",
       "      <td>B738</td>\n",
       "      <td>TERADATA</td>\n",
       "      <td>19:29:00.000000</td>\n",
       "      <td>19:24:00.000000</td>\n",
       "      <td>14:13:00.000000</td>\n",
       "      <td>14:39:00.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54070</th>\n",
       "      <td>5.27</td>\n",
       "      <td>700</td>\n",
       "      <td>CM</td>\n",
       "      <td>0:04</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.00</td>\n",
       "      <td>B4</td>\n",
       "      <td>B4 Dep</td>\n",
       "      <td>CMP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>?</td>\n",
       "      <td>12.0</td>\n",
       "      <td>124.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>T</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>-0:04</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>VVI</td>\n",
       "      <td>12/14/2019 02:26:32.000</td>\n",
       "      <td>12/13/2019 01:40:00.000</td>\n",
       "      <td>?</td>\n",
       "      <td>4.67</td>\n",
       "      <td>127</td>\n",
       "      <td>J</td>\n",
       "      <td>Schedule</td>\n",
       "      <td>127PTYVVI</td>\n",
       "      <td>127PTYVVI</td>\n",
       "      <td>HUB</td>\n",
       "      <td>12/14/2019 01:49:00.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12/1/2019</td>\n",
       "      <td>PTYVVI</td>\n",
       "      <td>12/13/2019 21:01:00.000</td>\n",
       "      <td>12/14/2019 01:41:00.000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>PTY</td>\n",
       "      <td>PTY</td>\n",
       "      <td>12/13/2019 20:33:00.000</td>\n",
       "      <td>Q4</td>\n",
       "      <td>PTY</td>\n",
       "      <td>98</td>\n",
       "      <td>71</td>\n",
       "      <td>94</td>\n",
       "      <td>12/13/2019</td>\n",
       "      <td>12/13/2019 20:45:00.000</td>\n",
       "      <td>12/14/2019 01:45:00.000</td>\n",
       "      <td>12/14/2019</td>\n",
       "      <td>20:45:00.000000</td>\n",
       "      <td>01:45:00.000000</td>\n",
       "      <td>5.13</td>\n",
       "      <td>12/13/2019</td>\n",
       "      <td>12/13/2019 15:37:00.000</td>\n",
       "      <td>12/13/2019 20:37:00.000</td>\n",
       "      <td>12/13/2019</td>\n",
       "      <td>15:37:00.000000</td>\n",
       "      <td>20:37:00.000000</td>\n",
       "      <td>VVI</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>HP1521</td>\n",
       "      <td>8.00</td>\n",
       "      <td>28.00</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "      <td>2019</td>\n",
       "      <td>CM</td>\n",
       "      <td>B737</td>\n",
       "      <td>TERADATA</td>\n",
       "      <td>01:49:00.000000</td>\n",
       "      <td>01:41:00.000000</td>\n",
       "      <td>20:33:00.000000</td>\n",
       "      <td>21:01:00.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54071</th>\n",
       "      <td>5.27</td>\n",
       "      <td>700</td>\n",
       "      <td>CM</td>\n",
       "      <td>0:22</td>\n",
       "      <td>22.0</td>\n",
       "      <td>22.00</td>\n",
       "      <td>B5</td>\n",
       "      <td>B5 Dep</td>\n",
       "      <td>CMP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>?</td>\n",
       "      <td>12.0</td>\n",
       "      <td>124.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>T</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>IAD</td>\n",
       "      <td>1/24/2020 21:21:44.000</td>\n",
       "      <td>1/24/2020 04:52:00.000</td>\n",
       "      <td>?</td>\n",
       "      <td>4.70</td>\n",
       "      <td>488</td>\n",
       "      <td>J</td>\n",
       "      <td>Schedule</td>\n",
       "      <td>488PTYIAD</td>\n",
       "      <td>488PTYIAD</td>\n",
       "      <td>HUB</td>\n",
       "      <td>1/24/2020 04:57:00.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1/1/2020</td>\n",
       "      <td>PTYIAD</td>\n",
       "      <td>1/24/2020 00:08:00.000</td>\n",
       "      <td>1/24/2020 04:50:00.000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>PTY</td>\n",
       "      <td>PTY</td>\n",
       "      <td>1/23/2020 23:41:00.000</td>\n",
       "      <td>Q1</td>\n",
       "      <td>PTY</td>\n",
       "      <td>75</td>\n",
       "      <td>89</td>\n",
       "      <td>75</td>\n",
       "      <td>1/23/2020</td>\n",
       "      <td>1/23/2020 23:35:00.000</td>\n",
       "      <td>1/24/2020 04:35:00.000</td>\n",
       "      <td>1/24/2020</td>\n",
       "      <td>23:35:00.000000</td>\n",
       "      <td>04:35:00.000000</td>\n",
       "      <td>4.90</td>\n",
       "      <td>1/23/2020</td>\n",
       "      <td>1/23/2020 18:41:00.000</td>\n",
       "      <td>1/23/2020 23:41:00.000</td>\n",
       "      <td>1/23/2020</td>\n",
       "      <td>18:41:00.000000</td>\n",
       "      <td>23:41:00.000000</td>\n",
       "      <td>IAD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>HP1521</td>\n",
       "      <td>7.00</td>\n",
       "      <td>27.00</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2020</td>\n",
       "      <td>CM</td>\n",
       "      <td>B737</td>\n",
       "      <td>TERADATA</td>\n",
       "      <td>04:57:00.000000</td>\n",
       "      <td>04:50:00.000000</td>\n",
       "      <td>23:41:00.000000</td>\n",
       "      <td>00:08:00.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54072</th>\n",
       "      <td>5.27</td>\n",
       "      <td>700</td>\n",
       "      <td>CM</td>\n",
       "      <td>2:53</td>\n",
       "      <td>173.0</td>\n",
       "      <td>173.00</td>\n",
       "      <td>B3</td>\n",
       "      <td>B3 Dep</td>\n",
       "      <td>CMP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>?</td>\n",
       "      <td>12.0</td>\n",
       "      <td>124.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>T</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.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.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3:22</td>\n",
       "      <td>202.0</td>\n",
       "      <td>BOS</td>\n",
       "      <td>1/20/2020 21:21:35.000</td>\n",
       "      <td>1/21/2020 02:01:00.000</td>\n",
       "      <td>1/20/2020 20:40:00.000</td>\n",
       "      <td>4.80</td>\n",
       "      <td>311</td>\n",
       "      <td>J</td>\n",
       "      <td>Schedule</td>\n",
       "      <td>311PTYBOS</td>\n",
       "      <td>311PTYBOS</td>\n",
       "      <td>HUB</td>\n",
       "      <td>1/21/2020 02:12:00.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1/1/2020</td>\n",
       "      <td>PTYBOS</td>\n",
       "      <td>1/20/2020 21:13:00.000</td>\n",
       "      <td>1/21/2020 02:01:00.000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>PTY</td>\n",
       "      <td>PTY</td>\n",
       "      <td>1/20/2020 20:56:00.000</td>\n",
       "      <td>Q1</td>\n",
       "      <td>PTY</td>\n",
       "      <td>137</td>\n",
       "      <td>270</td>\n",
       "      <td>65</td>\n",
       "      <td>1/20/2020</td>\n",
       "      <td>1/20/2020 18:19:00.000</td>\n",
       "      <td>1/20/2020 23:19:00.000</td>\n",
       "      <td>1/20/2020</td>\n",
       "      <td>18:19:00.000000</td>\n",
       "      <td>23:19:00.000000</td>\n",
       "      <td>5.75</td>\n",
       "      <td>1/20/2020</td>\n",
       "      <td>1/20/2020 12:34:00.000</td>\n",
       "      <td>1/20/2020 17:34:00.000</td>\n",
       "      <td>1/20/2020</td>\n",
       "      <td>12:34:00.000000</td>\n",
       "      <td>17:34:00.000000</td>\n",
       "      <td>BOS</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>HP1375</td>\n",
       "      <td>11.00</td>\n",
       "      <td>17.00</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2020</td>\n",
       "      <td>CM</td>\n",
       "      <td>B737</td>\n",
       "      <td>TERADATA</td>\n",
       "      <td>02:12:00.000000</td>\n",
       "      <td>02:01:00.000000</td>\n",
       "      <td>20:56:00.000000</td>\n",
       "      <td>21:13:00.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54073</th>\n",
       "      <td>5.27</td>\n",
       "      <td>700</td>\n",
       "      <td>CM</td>\n",
       "      <td>-0:25</td>\n",
       "      <td>-25.0</td>\n",
       "      <td>-25.00</td>\n",
       "      <td>B5</td>\n",
       "      <td>B5 Dep</td>\n",
       "      <td>CMP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>?</td>\n",
       "      <td>12.0</td>\n",
       "      <td>124.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>T</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>-0:02</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>ORD</td>\n",
       "      <td>10/29/2019 23:53:55.000</td>\n",
       "      <td>10/30/2019 04:43:00.000</td>\n",
       "      <td>?</td>\n",
       "      <td>4.80</td>\n",
       "      <td>235</td>\n",
       "      <td>J</td>\n",
       "      <td>Schedule</td>\n",
       "      <td>235PTYORD</td>\n",
       "      <td>235PTYORD</td>\n",
       "      <td>HUB</td>\n",
       "      <td>10/30/2019 04:44:00.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10/1/2019</td>\n",
       "      <td>PTYORD</td>\n",
       "      <td>10/29/2019 23:48:00.000</td>\n",
       "      <td>10/30/2019 04:36:00.000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>PTY</td>\n",
       "      <td>PTY</td>\n",
       "      <td>10/29/2019 23:28:00.000</td>\n",
       "      <td>Q4</td>\n",
       "      <td>PTY</td>\n",
       "      <td>91</td>\n",
       "      <td>76</td>\n",
       "      <td>89</td>\n",
       "      <td>10/30/2019</td>\n",
       "      <td>10/30/2019 00:09:00.000</td>\n",
       "      <td>10/30/2019 05:09:00.000</td>\n",
       "      <td>10/30/2019</td>\n",
       "      <td>00:09:00.000000</td>\n",
       "      <td>05:09:00.000000</td>\n",
       "      <td>5.65</td>\n",
       "      <td>10/29/2019</td>\n",
       "      <td>10/29/2019 18:30:00.000</td>\n",
       "      <td>10/29/2019 23:30:00.000</td>\n",
       "      <td>10/29/2019</td>\n",
       "      <td>18:30:00.000000</td>\n",
       "      <td>23:30:00.000000</td>\n",
       "      <td>ORD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>HP1374</td>\n",
       "      <td>8.00</td>\n",
       "      <td>20.00</td>\n",
       "      <td>1</td>\n",
       "      <td>44</td>\n",
       "      <td>2019</td>\n",
       "      <td>CM</td>\n",
       "      <td>B737</td>\n",
       "      <td>TERADATA</td>\n",
       "      <td>04:44:00.000000</td>\n",
       "      <td>04:36:00.000000</td>\n",
       "      <td>23:28:00.000000</td>\n",
       "      <td>23:48:00.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>54074 rows × 92 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      ACTUAL_BLOCK_HR AIRCRAFT_TYPE AIRLINE_DESIGNATOR_CD ARR_DELAY_INTERVAL  \\\n",
       "0                0.80           700                   CM               -3:02   \n",
       "1                4.07           800                   CM               0:26    \n",
       "2                2.02           800                   CM               0:02    \n",
       "3                1.63           E90                   CM               0:49    \n",
       "4                0.90           700                   CM               -1:47   \n",
       "...               ...           ...                   ...                ...   \n",
       "54069            5.27           800                   CM               0:18    \n",
       "54070            5.27           700                   CM               0:04    \n",
       "54071            5.27           700                   CM               0:22    \n",
       "54072            5.27           700                   CM               2:53    \n",
       "54073            5.27           700                   CM               -0:25   \n",
       "\n",
       "      ARR_DELAY_MINUTES ARR_DELAY_MINUTES_DV BANK OVERALL   BANK_CD BASE_CD  \\\n",
       "0               -182.00              -182.00     Off Bank  Off Bank     CMP   \n",
       "1                 26.00                26.00           B6  B6 Dep       CMP   \n",
       "2                  2.00                 2.00           B3  B3 Dep       CMP   \n",
       "3                 49.00                49.00           B4  B4 Dep       CMP   \n",
       "4               -107.00              -107.00           B2  B2 Dep       CMP   \n",
       "...                 ...                  ...          ...       ...     ...   \n",
       "54069              18.0                18.00           B4  B4 Arr       CMP   \n",
       "54070               4.0                 4.00           B4  B4 Dep       CMP   \n",
       "54071              22.0                22.00           B5  B5 Dep       CMP   \n",
       "54072             173.0               173.00           B3  B3 Dep       CMP   \n",
       "54073             -25.0               -25.00           B5  B5 Dep       CMP   \n",
       "\n",
       "       CANCEL_CD CANCEL_REASON_DESCRIPTION  CAPACITY_C_CNT  CAPACITY_CNT  \\\n",
       "0            0.0                         ?            12.0         124.0   \n",
       "1            0.0                         ?            16.0         160.0   \n",
       "2            0.0                         ?            16.0         160.0   \n",
       "3            0.0                         ?            10.0          94.0   \n",
       "4            0.0                         ?            12.0         124.0   \n",
       "...          ...                       ...             ...           ...   \n",
       "54069        0.0                         ?            16.0         160.0   \n",
       "54070        0.0                         ?            12.0         124.0   \n",
       "54071        0.0                         ?            12.0         124.0   \n",
       "54072        0.0                         ?            12.0         124.0   \n",
       "54073        0.0                         ?            12.0         124.0   \n",
       "\n",
       "       CAPACITY_Y_CNT CLAVLOCOD COUNT_ARR00 COUNT_ARR14 COUNT_ARR15  \\\n",
       "0               112.0         T        1.00        1.00        1.00   \n",
       "1               144.0         T        0.00        0.00        0.00   \n",
       "2               144.0         T        0.00        1.00        1.00   \n",
       "3                84.0         T        0.00        0.00        0.00   \n",
       "4               112.0         T        1.00        1.00        1.00   \n",
       "...               ...       ...         ...         ...         ...   \n",
       "54069           144.0         T         0.0         0.0         0.0   \n",
       "54070           112.0         T         0.0         1.0         1.0   \n",
       "54071           112.0         T         0.0         0.0         0.0   \n",
       "54072           112.0         T         0.0         0.0         0.0   \n",
       "54073           112.0         T         1.0         1.0         1.0   \n",
       "\n",
       "      COUNT_ARR90  COUNT_ATB  COUNT_CANCELLATIONS COUNT_DEP00 COUNT_DEP05  \\\n",
       "0            1.00        0.0                  0.0        1.00        1.00   \n",
       "1            1.00        0.0                  0.0        1.00        1.00   \n",
       "2            1.00        0.0                  0.0        1.00        1.00   \n",
       "3            1.00        1.0                  0.0        0.00        0.00   \n",
       "4            1.00        0.0                  0.0        1.00        1.00   \n",
       "...           ...        ...                  ...         ...         ...   \n",
       "54069         1.0        0.0                  0.0         0.0         0.0   \n",
       "54070         1.0        0.0                  0.0         1.0         1.0   \n",
       "54071         1.0        0.0                  0.0         1.0         1.0   \n",
       "54072         0.0        0.0                  0.0         0.0         0.0   \n",
       "54073         1.0        0.0                  0.0         1.0         1.0   \n",
       "\n",
       "      COUNT_DEP15 COUNT_DEP80  COUNT_DIVERSION  COUNT_DLY_FLTS_ARR00  \\\n",
       "0            1.00        1.00              0.0                   0.0   \n",
       "1            1.00        1.00              0.0                   1.0   \n",
       "2            1.00        1.00              0.0                   1.0   \n",
       "3            0.00        1.00              0.0                   1.0   \n",
       "4            1.00        1.00              0.0                   0.0   \n",
       "...           ...         ...              ...                   ...   \n",
       "54069         1.0         1.0              0.0                   1.0   \n",
       "54070         1.0         1.0              0.0                   1.0   \n",
       "54071         1.0         1.0              0.0                   1.0   \n",
       "54072         0.0         0.0              0.0                   1.0   \n",
       "54073         1.0         1.0              0.0                   0.0   \n",
       "\n",
       "       COUNT_DLY_FLTS_ARR14  COUNT_DLY_FLTS_D3H  COUNT_DLY_FLTS_DEP00  \\\n",
       "0                       0.0                 0.0                   0.0   \n",
       "1                       1.0                 0.0                   0.0   \n",
       "2                       0.0                 0.0                   0.0   \n",
       "3                       1.0                 0.0                   1.0   \n",
       "4                       0.0                 0.0                   0.0   \n",
       "...                     ...                 ...                   ...   \n",
       "54069                   1.0                 0.0                   1.0   \n",
       "54070                   0.0                 0.0                   0.0   \n",
       "54071                   1.0                 0.0                   0.0   \n",
       "54072                   1.0                 1.0                   1.0   \n",
       "54073                   0.0                 0.0                   0.0   \n",
       "\n",
       "       COUNT_GTB CREW_CNT DEP_DELAY_INTERVAL DEP_DELAY_MINUTES DEST_CD  \\\n",
       "0            1.0     6.00              -0:11            -11.00     MEX   \n",
       "1            0.0     7.00              -0:01             -1.00     LIM   \n",
       "2            0.0     6.00              0:00               0.00     TGU   \n",
       "3            0.0     5.00              0:21              21.00     PTY   \n",
       "4            1.0     6.00              -0:10            -10.00     PTY   \n",
       "...          ...      ...                ...               ...     ...   \n",
       "54069        0.0      6.0              0:12               12.0     PTY   \n",
       "54070        0.0      6.0              -0:04              -4.0     VVI   \n",
       "54071        0.0      6.0              0:00                0.0     IAD   \n",
       "54072        0.0      6.0              3:22              202.0     BOS   \n",
       "54073        0.0      6.0              -0:02              -2.0     ORD   \n",
       "\n",
       "                  DWHLOAD_DTML                 ETA_DTMZ  \\\n",
       "0       2/15/2020 09:53:37.000   2/15/2020 15:19:00.000   \n",
       "1       2/13/2020 09:15:48.000   2/13/2020 05:49:00.000   \n",
       "2       12/6/2019 13:30:31.000   12/6/2019 18:15:00.000   \n",
       "3      10/22/2019 18:12:55.000  10/22/2019 00:00:00.000   \n",
       "4       2/26/2020 13:46:09.000   2/26/2020 17:11:00.000   \n",
       "...                        ...                      ...   \n",
       "54069   1/28/2020 09:10:52.000  12/25/2019 19:24:00.000   \n",
       "54070  12/14/2019 02:26:32.000  12/13/2019 01:40:00.000   \n",
       "54071   1/24/2020 21:21:44.000   1/24/2020 04:52:00.000   \n",
       "54072   1/20/2020 21:21:35.000   1/21/2020 02:01:00.000   \n",
       "54073  10/29/2019 23:53:55.000  10/30/2019 04:43:00.000   \n",
       "\n",
       "                      ETD_DTMZ FLT_ACTUAL_HR  FLT_NUM FLT_TYPE_CD  \\\n",
       "0                            ?             ?      195           J   \n",
       "1                            ?          3.18      337           J   \n",
       "2                            ?          1.48      426           J   \n",
       "3      10/22/2019 21:50:00.000          1.03       15           J   \n",
       "4                            ?             ?      358           J   \n",
       "...                        ...           ...      ...         ...   \n",
       "54069  12/25/2019 14:30:00.000          4.75      357           J   \n",
       "54070                        ?          4.67      127           J   \n",
       "54071                        ?          4.70      488           J   \n",
       "54072   1/20/2020 20:40:00.000          4.80      311           J   \n",
       "54073                        ?          4.80      235           J   \n",
       "\n",
       "      FLT_TYPE_NAME         FOD    FOD_REAL      HUB_STN  \\\n",
       "0               GTB  195MEXMEX   195MEXMEX   INTRACAM      \n",
       "1          Schedule  337PTYLIM   337PTYLIM   HUB           \n",
       "2          Schedule  426PTYTGU   426PTYTGU   HUB           \n",
       "3               ATB  15PTYPTY    15PTYPTY    HUB           \n",
       "4               GTB  358PTYPTY   358PTYPTY   HUB           \n",
       "...             ...         ...         ...          ...   \n",
       "54069      Schedule  357IADPTY   357IADPTY   OUTSTATIONS   \n",
       "54070      Schedule  127PTYVVI   127PTYVVI   HUB           \n",
       "54071      Schedule  488PTYIAD   488PTYIAD   HUB           \n",
       "54072      Schedule  311PTYBOS   311PTYBOS   HUB           \n",
       "54073      Schedule  235PTYORD   235PTYORD   HUB           \n",
       "\n",
       "                       IN_DTMZ  LEG_STATUS_CD      MONTH      OD  \\\n",
       "0       2/15/2020 12:28:00.000            5.0   2/1/2020  MEXMEX   \n",
       "1       2/13/2020 06:26:00.000            0.0   2/1/2020  PTYLIM   \n",
       "2       12/6/2019 18:23:00.000            0.0  12/1/2019  PTYTGU   \n",
       "3      10/22/2019 23:00:00.000            6.0  10/1/2019  PTYPTY   \n",
       "4       2/26/2020 15:40:00.000            5.0   2/1/2020  PTYPTY   \n",
       "...                        ...            ...        ...     ...   \n",
       "54069  12/25/2019 19:29:00.000            0.0  12/1/2019  IADPTY   \n",
       "54070  12/14/2019 01:49:00.000            0.0  12/1/2019  PTYVVI   \n",
       "54071   1/24/2020 04:57:00.000            0.0   1/1/2020  PTYIAD   \n",
       "54072   1/21/2020 02:12:00.000            0.0   1/1/2020  PTYBOS   \n",
       "54073  10/30/2019 04:44:00.000            0.0  10/1/2019  PTYORD   \n",
       "\n",
       "                      OFF_DTMZ                  ON_DTMZ  OPERATED_ALL  \\\n",
       "0                            ?                        ?           1.0   \n",
       "1       2/13/2020 02:38:00.000   2/13/2020 05:49:00.000           1.0   \n",
       "2       12/6/2019 16:45:00.000   12/6/2019 18:14:00.000           1.0   \n",
       "3      10/22/2019 21:50:00.000  10/22/2019 22:52:00.000           1.0   \n",
       "4                            ?                        ?           1.0   \n",
       "...                        ...                      ...           ...   \n",
       "54069  12/25/2019 14:39:00.000  12/25/2019 19:24:00.000           1.0   \n",
       "54070  12/13/2019 21:01:00.000  12/14/2019 01:41:00.000           1.0   \n",
       "54071   1/24/2020 00:08:00.000   1/24/2020 04:50:00.000           1.0   \n",
       "54072   1/20/2020 21:13:00.000   1/21/2020 02:01:00.000           1.0   \n",
       "54073  10/29/2019 23:48:00.000  10/30/2019 04:36:00.000           1.0   \n",
       "\n",
       "       OPERATED_CNT  OPERATED_CNT_ALL ORIG_CD ORIGIN_PLANNED  \\\n",
       "0               0.0               1.0     MEX            MEX   \n",
       "1               1.0               1.0     PTY            PTY   \n",
       "2               1.0               1.0     PTY            PTY   \n",
       "3               0.0               1.0     PTY            PTY   \n",
       "4               0.0               1.0     PTY            PTY   \n",
       "...             ...               ...     ...            ...   \n",
       "54069           1.0               1.0     IAD            IAD   \n",
       "54070           1.0               1.0     PTY            PTY   \n",
       "54071           1.0               1.0     PTY            PTY   \n",
       "54072           1.0               1.0     PTY            PTY   \n",
       "54073           1.0               1.0     PTY            PTY   \n",
       "\n",
       "                      OUT_DTMZ   Q ROTATION_@STATION ROTATION_AVAILABLE_TM  \\\n",
       "0       2/15/2020 11:40:00.000  Q1                 ?                     ?   \n",
       "1       2/13/2020 02:22:00.000  Q1               PTY                    80   \n",
       "2       12/6/2019 16:22:00.000  Q4               PTY                    91   \n",
       "3      10/22/2019 21:22:00.000  Q4                 ?                     ?   \n",
       "4       2/26/2020 14:46:00.000  Q1                 ?                     ?   \n",
       "...                        ...  ..               ...                   ...   \n",
       "54069  12/25/2019 14:13:00.000  Q4               IAD                   392   \n",
       "54070  12/13/2019 20:33:00.000  Q4               PTY                    98   \n",
       "54071   1/23/2020 23:41:00.000  Q1               PTY                    75   \n",
       "54072   1/20/2020 20:56:00.000  Q1               PTY                   137   \n",
       "54073  10/29/2019 23:28:00.000  Q4               PTY                    91   \n",
       "\n",
       "      ROTATION_PLANNED_TM ROTATION_REAL_TM SCH_ARR_DTL  \\\n",
       "0                       ?                ?   2/15/2020   \n",
       "1                      67               79   2/13/2020   \n",
       "2                      80               91   12/6/2019   \n",
       "3                       ?                ?  10/22/2019   \n",
       "4                       ?                ?   2/26/2020   \n",
       "...                   ...              ...         ...   \n",
       "54069                 387              404  12/25/2019   \n",
       "54070                  71               94  12/13/2019   \n",
       "54071                  89               75   1/23/2020   \n",
       "54072                 270               65   1/20/2020   \n",
       "54073                  76               89  10/30/2019   \n",
       "\n",
       "              SCH_ARR_DTML_PTY             SCH_ARR_DTMZ SCH_ARR_DTZ  \\\n",
       "0       2/15/2020 10:30:00.000   2/15/2020 15:30:00.000   2/15/2020   \n",
       "1       2/13/2020 01:00:00.000   2/13/2020 06:00:00.000   2/13/2020   \n",
       "2       12/6/2019 13:21:00.000   12/6/2019 18:21:00.000   12/6/2019   \n",
       "3      10/22/2019 17:11:00.000  10/22/2019 22:11:00.000  10/22/2019   \n",
       "4       2/26/2020 12:27:00.000   2/26/2020 17:27:00.000   2/26/2020   \n",
       "...                        ...                      ...         ...   \n",
       "54069  12/25/2019 14:11:00.000  12/25/2019 19:11:00.000  12/25/2019   \n",
       "54070  12/13/2019 20:45:00.000  12/14/2019 01:45:00.000  12/14/2019   \n",
       "54071   1/23/2020 23:35:00.000   1/24/2020 04:35:00.000   1/24/2020   \n",
       "54072   1/20/2020 18:19:00.000   1/20/2020 23:19:00.000   1/20/2020   \n",
       "54073  10/30/2019 00:09:00.000  10/30/2019 05:09:00.000  10/30/2019   \n",
       "\n",
       "       SCH_ARR_TML_PTY      SCH_ARR_TMZ  SCH_BLOCK_HR SCH_DEP_DTL  \\\n",
       "0      10:30:00.000000  15:30:00.000000          3.65   2/15/2020   \n",
       "1      01:00:00.000000  06:00:00.000000          3.62   2/12/2020   \n",
       "2      13:21:00.000000  18:21:00.000000          1.98   12/6/2019   \n",
       "3      17:11:00.000000  22:11:00.000000          1.17  10/22/2019   \n",
       "4      12:27:00.000000  17:27:00.000000          2.52   2/26/2020   \n",
       "...                ...              ...           ...         ...   \n",
       "54069  14:11:00.000000  19:11:00.000000          5.17  12/25/2019   \n",
       "54070  20:45:00.000000  01:45:00.000000          5.13  12/13/2019   \n",
       "54071  23:35:00.000000  04:35:00.000000          4.90   1/23/2020   \n",
       "54072  18:19:00.000000  23:19:00.000000          5.75   1/20/2020   \n",
       "54073  00:09:00.000000  05:09:00.000000          5.65  10/29/2019   \n",
       "\n",
       "              SCH_DEP_DTML_PTY             SCH_DEP_DTMZ SCH_DEP_DTZ  \\\n",
       "0       2/15/2020 06:51:00.000   2/15/2020 11:51:00.000   2/15/2020   \n",
       "1       2/12/2020 21:23:00.000   2/13/2020 02:23:00.000   2/13/2020   \n",
       "2       12/6/2019 11:22:00.000   12/6/2019 16:22:00.000   12/6/2019   \n",
       "3      10/22/2019 16:01:00.000  10/22/2019 21:01:00.000  10/22/2019   \n",
       "4       2/26/2020 09:56:00.000   2/26/2020 14:56:00.000   2/26/2020   \n",
       "...                        ...                      ...         ...   \n",
       "54069  12/25/2019 09:01:00.000  12/25/2019 14:01:00.000  12/25/2019   \n",
       "54070  12/13/2019 15:37:00.000  12/13/2019 20:37:00.000  12/13/2019   \n",
       "54071   1/23/2020 18:41:00.000   1/23/2020 23:41:00.000   1/23/2020   \n",
       "54072   1/20/2020 12:34:00.000   1/20/2020 17:34:00.000   1/20/2020   \n",
       "54073  10/29/2019 18:30:00.000  10/29/2019 23:30:00.000  10/29/2019   \n",
       "\n",
       "       SCH_DEP_TML_PTY      SCH_DEP_TMZ SCH_DEST_CD  SCHD_CNT_ALL  \\\n",
       "0      06:51:00.000000  11:51:00.000000         MEX           1.0   \n",
       "1      21:23:00.000000  02:23:00.000000         LIM           1.0   \n",
       "2      11:22:00.000000  16:22:00.000000         TGU           1.0   \n",
       "3      16:01:00.000000  21:01:00.000000         PTY           1.0   \n",
       "4      09:56:00.000000  14:56:00.000000         PTY           1.0   \n",
       "...                ...              ...         ...           ...   \n",
       "54069  09:01:00.000000  14:01:00.000000         PTY           1.0   \n",
       "54070  15:37:00.000000  20:37:00.000000         VVI           1.0   \n",
       "54071  18:41:00.000000  23:41:00.000000         IAD           1.0   \n",
       "54072  12:34:00.000000  17:34:00.000000         BOS           1.0   \n",
       "54073  18:30:00.000000  23:30:00.000000         ORD           1.0   \n",
       "\n",
       "       SCHEDULED_ALL  SCHEDULED_CNT    TAIL TAXI_IN_MINUTES TAXI_OUT_MINUTES  \\\n",
       "0                1.0            0.0  HP1530               ?                ?   \n",
       "1                1.0            1.0  HP1852           37.00            16.00   \n",
       "2                1.0            1.0  HP1856            9.00            23.00   \n",
       "3                1.0            0.0  HP1568            8.00            28.00   \n",
       "4                1.0            0.0  HP1374               ?                ?   \n",
       "...              ...            ...     ...             ...              ...   \n",
       "54069            1.0            1.0  HP1523            5.00            26.00   \n",
       "54070            1.0            1.0  HP1521            8.00            28.00   \n",
       "54071            1.0            1.0  HP1521            7.00            27.00   \n",
       "54072            1.0            1.0  HP1375           11.00            17.00   \n",
       "54073            1.0            1.0  HP1374            8.00            20.00   \n",
       "\n",
       "      TIPO_OPR  WEEK  YEAR AC_OWNER_CARRIER_CD AIRCRAFT_TYPE_ICAO    FUENTE  \\\n",
       "0          GTB     7  2020                 CM                B737  TERADATA   \n",
       "1          1       7  2020                 CM                B738  TERADATA   \n",
       "2          1      49  2019                 CM                B738  TERADATA   \n",
       "3          ATB    43  2019                 CM                E190  TERADATA   \n",
       "4          GTB     9  2020                 CM                B737  TERADATA   \n",
       "...        ...   ...   ...                 ...                ...       ...   \n",
       "54069      1      52  2019                 CM                B738  TERADATA   \n",
       "54070      1      50  2019                 CM                B737  TERADATA   \n",
       "54071      1       4  2020                 CM                B737  TERADATA   \n",
       "54072      1       4  2020                 CM                B737  TERADATA   \n",
       "54073      1      44  2019                 CM                B737  TERADATA   \n",
       "\n",
       "                    IN               ON              OUT              OFF  \n",
       "0      12:28:00.000000                ?  11:40:00.000000                ?  \n",
       "1      06:26:00.000000  05:49:00.000000  02:22:00.000000  02:38:00.000000  \n",
       "2      18:23:00.000000  18:14:00.000000  16:22:00.000000  16:45:00.000000  \n",
       "3      23:00:00.000000  22:52:00.000000  21:22:00.000000  21:50:00.000000  \n",
       "4      15:40:00.000000                ?  14:46:00.000000                ?  \n",
       "...                ...              ...              ...              ...  \n",
       "54069  19:29:00.000000  19:24:00.000000  14:13:00.000000  14:39:00.000000  \n",
       "54070  01:49:00.000000  01:41:00.000000  20:33:00.000000  21:01:00.000000  \n",
       "54071  04:57:00.000000  04:50:00.000000  23:41:00.000000  00:08:00.000000  \n",
       "54072  02:12:00.000000  02:01:00.000000  20:56:00.000000  21:13:00.000000  \n",
       "54073  04:44:00.000000  04:36:00.000000  23:28:00.000000  23:48:00.000000  \n",
       "\n",
       "[54074 rows x 92 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fsu"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "similar-seattle",
   "metadata": {},
   "source": [
    "* Look at the columns and shape (54k rows and 92 columns)\n",
    "* Look at types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "acceptable-office",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Index(['ACTUAL_BLOCK_HR', 'AIRCRAFT_TYPE', 'AIRLINE_DESIGNATOR_CD',\n",
       "        'ARR_DELAY_INTERVAL', 'ARR_DELAY_MINUTES', 'ARR_DELAY_MINUTES_DV',\n",
       "        'BANK OVERALL', 'BANK_CD', 'BASE_CD', 'CANCEL_CD',\n",
       "        'CANCEL_REASON_DESCRIPTION', 'CAPACITY_C_CNT', 'CAPACITY_CNT',\n",
       "        'CAPACITY_Y_CNT', 'CLAVLOCOD', 'COUNT_ARR00', 'COUNT_ARR14',\n",
       "        'COUNT_ARR15', 'COUNT_ARR90', 'COUNT_ATB', 'COUNT_CANCELLATIONS',\n",
       "        'COUNT_DEP00', 'COUNT_DEP05', 'COUNT_DEP15', 'COUNT_DEP80',\n",
       "        'COUNT_DIVERSION', 'COUNT_DLY_FLTS_ARR00', 'COUNT_DLY_FLTS_ARR14',\n",
       "        'COUNT_DLY_FLTS_D3H', 'COUNT_DLY_FLTS_DEP00', 'COUNT_GTB', 'CREW_CNT',\n",
       "        'DEP_DELAY_INTERVAL', 'DEP_DELAY_MINUTES', 'DEST_CD', 'DWHLOAD_DTML',\n",
       "        'ETA_DTMZ', 'ETD_DTMZ', 'FLT_ACTUAL_HR', 'FLT_NUM', 'FLT_TYPE_CD',\n",
       "        'FLT_TYPE_NAME', 'FOD', 'FOD_REAL', 'HUB_STN', 'IN_DTMZ',\n",
       "        'LEG_STATUS_CD', 'MONTH', 'OD', 'OFF_DTMZ', 'ON_DTMZ', 'OPERATED_ALL',\n",
       "        'OPERATED_CNT', 'OPERATED_CNT_ALL', 'ORIG_CD', 'ORIGIN_PLANNED',\n",
       "        'OUT_DTMZ', 'Q', 'ROTATION_@STATION', 'ROTATION_AVAILABLE_TM',\n",
       "        'ROTATION_PLANNED_TM', 'ROTATION_REAL_TM', 'SCH_ARR_DTL',\n",
       "        'SCH_ARR_DTML_PTY', 'SCH_ARR_DTMZ', 'SCH_ARR_DTZ', 'SCH_ARR_TML_PTY',\n",
       "        'SCH_ARR_TMZ', 'SCH_BLOCK_HR', 'SCH_DEP_DTL', 'SCH_DEP_DTML_PTY',\n",
       "        'SCH_DEP_DTMZ', 'SCH_DEP_DTZ', 'SCH_DEP_TML_PTY', 'SCH_DEP_TMZ',\n",
       "        'SCH_DEST_CD', 'SCHD_CNT_ALL', 'SCHEDULED_ALL', 'SCHEDULED_CNT', 'TAIL',\n",
       "        'TAXI_IN_MINUTES', 'TAXI_OUT_MINUTES', 'TIPO_OPR', 'WEEK', 'YEAR',\n",
       "        'AC_OWNER_CARRIER_CD', 'AIRCRAFT_TYPE_ICAO', 'FUENTE', 'IN', 'ON',\n",
       "        'OUT', 'OFF'],\n",
       "       dtype='object'),\n",
       " (54074, 92))"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fsu.columns, fsu.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "regular-translation",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ACTUAL_BLOCK_HR               object\n",
       "AIRCRAFT_TYPE                 object\n",
       "AIRLINE_DESIGNATOR_CD         object\n",
       "ARR_DELAY_INTERVAL            object\n",
       "ARR_DELAY_MINUTES             object\n",
       "ARR_DELAY_MINUTES_DV          object\n",
       "BANK OVERALL                  object\n",
       "BANK_CD                       object\n",
       "BASE_CD                       object\n",
       "CANCEL_CD                    float64\n",
       "CANCEL_REASON_DESCRIPTION     object\n",
       "CAPACITY_C_CNT               float64\n",
       "CAPACITY_CNT                 float64\n",
       "CAPACITY_Y_CNT               float64\n",
       "CLAVLOCOD                     object\n",
       "COUNT_ARR00                   object\n",
       "COUNT_ARR14                   object\n",
       "COUNT_ARR15                   object\n",
       "COUNT_ARR90                   object\n",
       "COUNT_ATB                    float64\n",
       "COUNT_CANCELLATIONS          float64\n",
       "COUNT_DEP00                   object\n",
       "COUNT_DEP05                   object\n",
       "COUNT_DEP15                   object\n",
       "COUNT_DEP80                   object\n",
       "COUNT_DIVERSION              float64\n",
       "COUNT_DLY_FLTS_ARR00         float64\n",
       "COUNT_DLY_FLTS_ARR14         float64\n",
       "COUNT_DLY_FLTS_D3H           float64\n",
       "COUNT_DLY_FLTS_DEP00         float64\n",
       "COUNT_GTB                    float64\n",
       "CREW_CNT                      object\n",
       "DEP_DELAY_INTERVAL            object\n",
       "DEP_DELAY_MINUTES             object\n",
       "DEST_CD                       object\n",
       "DWHLOAD_DTML                  object\n",
       "ETA_DTMZ                      object\n",
       "ETD_DTMZ                      object\n",
       "FLT_ACTUAL_HR                 object\n",
       "FLT_NUM                        int64\n",
       "FLT_TYPE_CD                   object\n",
       "FLT_TYPE_NAME                 object\n",
       "FOD                           object\n",
       "FOD_REAL                      object\n",
       "HUB_STN                       object\n",
       "IN_DTMZ                       object\n",
       "LEG_STATUS_CD                float64\n",
       "MONTH                         object\n",
       "OD                            object\n",
       "OFF_DTMZ                      object\n",
       "ON_DTMZ                       object\n",
       "OPERATED_ALL                 float64\n",
       "OPERATED_CNT                 float64\n",
       "OPERATED_CNT_ALL             float64\n",
       "ORIG_CD                       object\n",
       "ORIGIN_PLANNED                object\n",
       "OUT_DTMZ                      object\n",
       "Q                             object\n",
       "ROTATION_@STATION             object\n",
       "ROTATION_AVAILABLE_TM         object\n",
       "ROTATION_PLANNED_TM           object\n",
       "ROTATION_REAL_TM              object\n",
       "SCH_ARR_DTL                   object\n",
       "SCH_ARR_DTML_PTY              object\n",
       "SCH_ARR_DTMZ                  object\n",
       "SCH_ARR_DTZ                   object\n",
       "SCH_ARR_TML_PTY               object\n",
       "SCH_ARR_TMZ                   object\n",
       "SCH_BLOCK_HR                 float64\n",
       "SCH_DEP_DTL                   object\n",
       "SCH_DEP_DTML_PTY              object\n",
       "SCH_DEP_DTMZ                  object\n",
       "SCH_DEP_DTZ                   object\n",
       "SCH_DEP_TML_PTY               object\n",
       "SCH_DEP_TMZ                   object\n",
       "SCH_DEST_CD                   object\n",
       "SCHD_CNT_ALL                 float64\n",
       "SCHEDULED_ALL                float64\n",
       "SCHEDULED_CNT                float64\n",
       "TAIL                          object\n",
       "TAXI_IN_MINUTES               object\n",
       "TAXI_OUT_MINUTES              object\n",
       "TIPO_OPR                      object\n",
       "WEEK                           int64\n",
       "YEAR                           int64\n",
       "AC_OWNER_CARRIER_CD           object\n",
       "AIRCRAFT_TYPE_ICAO            object\n",
       "FUENTE                        object\n",
       "IN                            object\n",
       "ON                            object\n",
       "OUT                           object\n",
       "OFF                           object\n",
       "dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fsu.dtypes   # Fully displayed. Use head(n) to restrict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "remarkable-marathon",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CANCEL_CD</th>\n",
       "      <th>CAPACITY_C_CNT</th>\n",
       "      <th>CAPACITY_CNT</th>\n",
       "      <th>CAPACITY_Y_CNT</th>\n",
       "      <th>COUNT_ATB</th>\n",
       "      <th>COUNT_CANCELLATIONS</th>\n",
       "      <th>COUNT_DIVERSION</th>\n",
       "      <th>COUNT_DLY_FLTS_ARR00</th>\n",
       "      <th>COUNT_DLY_FLTS_ARR14</th>\n",
       "      <th>COUNT_DLY_FLTS_D3H</th>\n",
       "      <th>COUNT_DLY_FLTS_DEP00</th>\n",
       "      <th>COUNT_GTB</th>\n",
       "      <th>FLT_NUM</th>\n",
       "      <th>LEG_STATUS_CD</th>\n",
       "      <th>OPERATED_ALL</th>\n",
       "      <th>OPERATED_CNT</th>\n",
       "      <th>OPERATED_CNT_ALL</th>\n",
       "      <th>SCH_BLOCK_HR</th>\n",
       "      <th>SCHD_CNT_ALL</th>\n",
       "      <th>SCHEDULED_ALL</th>\n",
       "      <th>SCHEDULED_CNT</th>\n",
       "      <th>WEEK</th>\n",
       "      <th>YEAR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>5.41e+04</td>\n",
       "      <td>54074.0</td>\n",
       "      <td>54074.00</td>\n",
       "      <td>54074.00</td>\n",
       "      <td>5.41e+04</td>\n",
       "      <td>5.41e+04</td>\n",
       "      <td>5.41e+04</td>\n",
       "      <td>54074.00</td>\n",
       "      <td>54074.00</td>\n",
       "      <td>5.41e+04</td>\n",
       "      <td>54074.00</td>\n",
       "      <td>5.41e+04</td>\n",
       "      <td>54074.00</td>\n",
       "      <td>54074.00</td>\n",
       "      <td>54074.00</td>\n",
       "      <td>54074.00</td>\n",
       "      <td>54074.00</td>\n",
       "      <td>54074.00</td>\n",
       "      <td>54074.00</td>\n",
       "      <td>54074.0</td>\n",
       "      <td>54074.00</td>\n",
       "      <td>54074.00</td>\n",
       "      <td>54074.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.59e-03</td>\n",
       "      <td>13.5</td>\n",
       "      <td>142.52</td>\n",
       "      <td>129.03</td>\n",
       "      <td>2.40e-04</td>\n",
       "      <td>1.48e-03</td>\n",
       "      <td>2.37e-03</td>\n",
       "      <td>0.26</td>\n",
       "      <td>0.08</td>\n",
       "      <td>4.55e-03</td>\n",
       "      <td>0.18</td>\n",
       "      <td>2.50e-03</td>\n",
       "      <td>850.62</td>\n",
       "      <td>0.02</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.99</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.33</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.99</td>\n",
       "      <td>29.19</td>\n",
       "      <td>2019.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>3.98e-02</td>\n",
       "      <td>4.2</td>\n",
       "      <td>25.02</td>\n",
       "      <td>23.76</td>\n",
       "      <td>1.55e-02</td>\n",
       "      <td>3.84e-02</td>\n",
       "      <td>4.86e-02</td>\n",
       "      <td>0.44</td>\n",
       "      <td>0.27</td>\n",
       "      <td>6.73e-02</td>\n",
       "      <td>0.38</td>\n",
       "      <td>4.99e-02</td>\n",
       "      <td>1681.39</td>\n",
       "      <td>0.30</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.06</td>\n",
       "      <td>1.97</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.09</td>\n",
       "      <td>20.52</td>\n",
       "      <td>0.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>94.00</td>\n",
       "      <td>84.00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.33</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2019.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>12.0</td>\n",
       "      <td>124.00</td>\n",
       "      <td>112.00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>230.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.72</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>6.00</td>\n",
       "      <td>2019.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>16.0</td>\n",
       "      <td>154.00</td>\n",
       "      <td>138.00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>390.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.68</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>42.00</td>\n",
       "      <td>2019.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>16.0</td>\n",
       "      <td>160.00</td>\n",
       "      <td>144.00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00e+00</td>\n",
       "      <td>642.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>4.20</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>47.00</td>\n",
       "      <td>2020.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.00e+00</td>\n",
       "      <td>16.0</td>\n",
       "      <td>186.00</td>\n",
       "      <td>186.00</td>\n",
       "      <td>1.00e+00</td>\n",
       "      <td>1.00e+00</td>\n",
       "      <td>1.00e+00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00e+00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00e+00</td>\n",
       "      <td>7680.00</td>\n",
       "      <td>6.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>10.78</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>52.00</td>\n",
       "      <td>2020.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       CANCEL_CD  CAPACITY_C_CNT  CAPACITY_CNT  CAPACITY_Y_CNT  COUNT_ATB  \\\n",
       "count   5.41e+04         54074.0      54074.00        54074.00   5.41e+04   \n",
       "mean    1.59e-03            13.5        142.52          129.03   2.40e-04   \n",
       "std     3.98e-02             4.2         25.02           23.76   1.55e-02   \n",
       "min     0.00e+00             0.0         94.00           84.00   0.00e+00   \n",
       "25%     0.00e+00            12.0        124.00          112.00   0.00e+00   \n",
       "50%     0.00e+00            16.0        154.00          138.00   0.00e+00   \n",
       "75%     0.00e+00            16.0        160.00          144.00   0.00e+00   \n",
       "max     1.00e+00            16.0        186.00          186.00   1.00e+00   \n",
       "\n",
       "       COUNT_CANCELLATIONS  COUNT_DIVERSION  COUNT_DLY_FLTS_ARR00  \\\n",
       "count             5.41e+04         5.41e+04              54074.00   \n",
       "mean              1.48e-03         2.37e-03                  0.26   \n",
       "std               3.84e-02         4.86e-02                  0.44   \n",
       "min               0.00e+00         0.00e+00                  0.00   \n",
       "25%               0.00e+00         0.00e+00                  0.00   \n",
       "50%               0.00e+00         0.00e+00                  0.00   \n",
       "75%               0.00e+00         0.00e+00                  1.00   \n",
       "max               1.00e+00         1.00e+00                  1.00   \n",
       "\n",
       "       COUNT_DLY_FLTS_ARR14  COUNT_DLY_FLTS_D3H  COUNT_DLY_FLTS_DEP00  \\\n",
       "count              54074.00            5.41e+04              54074.00   \n",
       "mean                   0.08            4.55e-03                  0.18   \n",
       "std                    0.27            6.73e-02                  0.38   \n",
       "min                    0.00            0.00e+00                  0.00   \n",
       "25%                    0.00            0.00e+00                  0.00   \n",
       "50%                    0.00            0.00e+00                  0.00   \n",
       "75%                    0.00            0.00e+00                  0.00   \n",
       "max                    1.00            1.00e+00                  1.00   \n",
       "\n",
       "       COUNT_GTB   FLT_NUM  LEG_STATUS_CD  OPERATED_ALL  OPERATED_CNT  \\\n",
       "count   5.41e+04  54074.00       54074.00      54074.00      54074.00   \n",
       "mean    2.50e-03    850.62           0.02          1.00          0.99   \n",
       "std     4.99e-02   1681.39           0.30          0.04          0.10   \n",
       "min     0.00e+00     15.00           0.00          0.00          0.00   \n",
       "25%     0.00e+00    230.00           0.00          1.00          1.00   \n",
       "50%     0.00e+00    390.00           0.00          1.00          1.00   \n",
       "75%     0.00e+00    642.00           0.00          1.00          1.00   \n",
       "max     1.00e+00   7680.00           6.00          1.00          1.00   \n",
       "\n",
       "       OPERATED_CNT_ALL  SCH_BLOCK_HR  SCHD_CNT_ALL  SCHEDULED_ALL  \\\n",
       "count          54074.00      54074.00      54074.00        54074.0   \n",
       "mean               1.00          3.33          1.00            1.0   \n",
       "std                0.06          1.97          0.05            0.0   \n",
       "min                0.00          0.33          0.00            1.0   \n",
       "25%                1.00          1.72          1.00            1.0   \n",
       "50%                1.00          2.68          1.00            1.0   \n",
       "75%                1.00          4.20          1.00            1.0   \n",
       "max                1.00         10.78          1.00            1.0   \n",
       "\n",
       "       SCHEDULED_CNT      WEEK      YEAR  \n",
       "count       54074.00  54074.00  54074.00  \n",
       "mean            0.99     29.19   2019.40  \n",
       "std             0.09     20.52      0.49  \n",
       "min             0.00      1.00   2019.00  \n",
       "25%             1.00      6.00   2019.00  \n",
       "50%             1.00     42.00   2019.00  \n",
       "75%             1.00     47.00   2020.00  \n",
       "max             1.00     52.00   2020.00  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fsu.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "enclosed-stanford",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>CANCEL_CD</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>1.59e-03</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CAPACITY_C_CNT</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>1.35e+01</td>\n",
       "      <td>4.20</td>\n",
       "      <td>0.00</td>\n",
       "      <td>12.00</td>\n",
       "      <td>16.00</td>\n",
       "      <td>16.0</td>\n",
       "      <td>16.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CAPACITY_CNT</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>1.43e+02</td>\n",
       "      <td>25.02</td>\n",
       "      <td>94.00</td>\n",
       "      <td>124.00</td>\n",
       "      <td>154.00</td>\n",
       "      <td>160.0</td>\n",
       "      <td>186.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CAPACITY_Y_CNT</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>1.29e+02</td>\n",
       "      <td>23.76</td>\n",
       "      <td>84.00</td>\n",
       "      <td>112.00</td>\n",
       "      <td>138.00</td>\n",
       "      <td>144.0</td>\n",
       "      <td>186.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COUNT_ATB</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>2.40e-04</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COUNT_CANCELLATIONS</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>1.48e-03</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COUNT_DIVERSION</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>2.37e-03</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COUNT_DLY_FLTS_ARR00</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>2.64e-01</td>\n",
       "      <td>0.44</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COUNT_DLY_FLTS_ARR14</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>8.06e-02</td>\n",
       "      <td>0.27</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COUNT_DLY_FLTS_D3H</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>4.55e-03</td>\n",
       "      <td>0.07</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COUNT_DLY_FLTS_DEP00</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>1.80e-01</td>\n",
       "      <td>0.38</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COUNT_GTB</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>2.50e-03</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FLT_NUM</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>8.51e+02</td>\n",
       "      <td>1681.39</td>\n",
       "      <td>15.00</td>\n",
       "      <td>230.00</td>\n",
       "      <td>390.00</td>\n",
       "      <td>642.0</td>\n",
       "      <td>7680.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LEG_STATUS_CD</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>2.13e-02</td>\n",
       "      <td>0.30</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OPERATED_ALL</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>9.98e-01</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OPERATED_CNT</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>9.90e-01</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OPERATED_CNT_ALL</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>9.96e-01</td>\n",
       "      <td>0.06</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SCH_BLOCK_HR</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>3.33e+00</td>\n",
       "      <td>1.97</td>\n",
       "      <td>0.33</td>\n",
       "      <td>1.72</td>\n",
       "      <td>2.68</td>\n",
       "      <td>4.2</td>\n",
       "      <td>10.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SCHD_CNT_ALL</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>9.98e-01</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SCHEDULED_ALL</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>1.00e+00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SCHEDULED_CNT</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>9.91e-01</td>\n",
       "      <td>0.09</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WEEK</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>2.92e+01</td>\n",
       "      <td>20.52</td>\n",
       "      <td>1.00</td>\n",
       "      <td>6.00</td>\n",
       "      <td>42.00</td>\n",
       "      <td>47.0</td>\n",
       "      <td>52.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YEAR</th>\n",
       "      <td>54074.0</td>\n",
       "      <td>2.02e+03</td>\n",
       "      <td>0.49</td>\n",
       "      <td>2019.00</td>\n",
       "      <td>2019.00</td>\n",
       "      <td>2019.00</td>\n",
       "      <td>2020.0</td>\n",
       "      <td>2020.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        count      mean      std      min      25%      50%  \\\n",
       "CANCEL_CD             54074.0  1.59e-03     0.04     0.00     0.00     0.00   \n",
       "CAPACITY_C_CNT        54074.0  1.35e+01     4.20     0.00    12.00    16.00   \n",
       "CAPACITY_CNT          54074.0  1.43e+02    25.02    94.00   124.00   154.00   \n",
       "CAPACITY_Y_CNT        54074.0  1.29e+02    23.76    84.00   112.00   138.00   \n",
       "COUNT_ATB             54074.0  2.40e-04     0.02     0.00     0.00     0.00   \n",
       "COUNT_CANCELLATIONS   54074.0  1.48e-03     0.04     0.00     0.00     0.00   \n",
       "COUNT_DIVERSION       54074.0  2.37e-03     0.05     0.00     0.00     0.00   \n",
       "COUNT_DLY_FLTS_ARR00  54074.0  2.64e-01     0.44     0.00     0.00     0.00   \n",
       "COUNT_DLY_FLTS_ARR14  54074.0  8.06e-02     0.27     0.00     0.00     0.00   \n",
       "COUNT_DLY_FLTS_D3H    54074.0  4.55e-03     0.07     0.00     0.00     0.00   \n",
       "COUNT_DLY_FLTS_DEP00  54074.0  1.80e-01     0.38     0.00     0.00     0.00   \n",
       "COUNT_GTB             54074.0  2.50e-03     0.05     0.00     0.00     0.00   \n",
       "FLT_NUM               54074.0  8.51e+02  1681.39    15.00   230.00   390.00   \n",
       "LEG_STATUS_CD         54074.0  2.13e-02     0.30     0.00     0.00     0.00   \n",
       "OPERATED_ALL          54074.0  9.98e-01     0.04     0.00     1.00     1.00   \n",
       "OPERATED_CNT          54074.0  9.90e-01     0.10     0.00     1.00     1.00   \n",
       "OPERATED_CNT_ALL      54074.0  9.96e-01     0.06     0.00     1.00     1.00   \n",
       "SCH_BLOCK_HR          54074.0  3.33e+00     1.97     0.33     1.72     2.68   \n",
       "SCHD_CNT_ALL          54074.0  9.98e-01     0.05     0.00     1.00     1.00   \n",
       "SCHEDULED_ALL         54074.0  1.00e+00     0.00     1.00     1.00     1.00   \n",
       "SCHEDULED_CNT         54074.0  9.91e-01     0.09     0.00     1.00     1.00   \n",
       "WEEK                  54074.0  2.92e+01    20.52     1.00     6.00    42.00   \n",
       "YEAR                  54074.0  2.02e+03     0.49  2019.00  2019.00  2019.00   \n",
       "\n",
       "                         75%      max  \n",
       "CANCEL_CD                0.0     1.00  \n",
       "CAPACITY_C_CNT          16.0    16.00  \n",
       "CAPACITY_CNT           160.0   186.00  \n",
       "CAPACITY_Y_CNT         144.0   186.00  \n",
       "COUNT_ATB                0.0     1.00  \n",
       "COUNT_CANCELLATIONS      0.0     1.00  \n",
       "COUNT_DIVERSION          0.0     1.00  \n",
       "COUNT_DLY_FLTS_ARR00     1.0     1.00  \n",
       "COUNT_DLY_FLTS_ARR14     0.0     1.00  \n",
       "COUNT_DLY_FLTS_D3H       0.0     1.00  \n",
       "COUNT_DLY_FLTS_DEP00     0.0     1.00  \n",
       "COUNT_GTB                0.0     1.00  \n",
       "FLT_NUM                642.0  7680.00  \n",
       "LEG_STATUS_CD            0.0     6.00  \n",
       "OPERATED_ALL             1.0     1.00  \n",
       "OPERATED_CNT             1.0     1.00  \n",
       "OPERATED_CNT_ALL         1.0     1.00  \n",
       "SCH_BLOCK_HR             4.2    10.78  \n",
       "SCHD_CNT_ALL             1.0     1.00  \n",
       "SCHEDULED_ALL            1.0     1.00  \n",
       "SCHEDULED_CNT            1.0     1.00  \n",
       "WEEK                    47.0    52.00  \n",
       "YEAR                  2020.0  2020.00  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Only shows numeric columns\n",
    "fsu.describe().T"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "thick-minister",
   "metadata": {},
   "source": [
    "## Look at Unique Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "irish-catch",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    2524\n",
       "2    2561\n",
       "3    2550\n",
       "4    2527\n",
       "5    2498\n",
       "Name: WEEK, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fsu.WEEK.value_counts().sort_index().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "offensive-liberal",
   "metadata": {},
   "source": [
    "## Total number of days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "adjacent-trial",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(153,)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_days = fsu.groupby('SCH_DEP_DTZ')['FLT_NUM'].size().shape\n",
    "nb_days"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "detailed-daily",
   "metadata": {},
   "source": [
    "## Flight numbers: \n",
    "* sort_values: shows that some flights have a single flight. \n",
    "* sort_values: shows that there are two flights a day in many cases (there are only 153 days)\n",
    "* sort_index: shows that some flight numbers are very low and very high"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "accomplished-youth",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "407    263\n",
       "686    272\n",
       "687    274\n",
       "145    301\n",
       "144    304\n",
       "392    305\n",
       "787    306\n",
       "163    306\n",
       "788    306\n",
       "391    307\n",
       "Name: FLT_NUM, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fsu.FLT_NUM.value_counts().sort_values().tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "reasonable-swiss",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7231    18\n",
       "7248    48\n",
       "7249    48\n",
       "7252    29\n",
       "7253    43\n",
       "7254    14\n",
       "7310    13\n",
       "7311    11\n",
       "7432     1\n",
       "7680     1\n",
       "Name: FLT_NUM, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fsu.FLT_NUM.value_counts().sort_index().tail(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "homeless-kingdom",
   "metadata": {},
   "source": [
    "Let us plot the flight numbers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "paperback-charm",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAY4AAAD4CAYAAAD7CAEUAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuNCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8QVMy6AAAACXBIWXMAAAsTAAALEwEAmpwYAAAY3klEQVR4nO3dfZBV9Z3n8fdnIbLEjvg4XQywA6kQqxBmmXQXIZvV6h6ckRgnmimdgXIiVpwisUxtsrEqwmZrk9kUNTo7xi3NhBkyuGp06LgaA2VkEkftdXfLhwFDAqgkTezVFgZGIWgnhgrMd/84vy6PzaW7f/fc7j7Gz6vq1j33dx76c3joT5+HvlcRgZmZ2Vj9q8kOYGZmby8uDjMzy+LiMDOzLC4OMzPL4uIwM7MsUyc7QLPOPvvsmDt3blPr/vznP+fUU09tbaAWqXM2cL6q6pyvztnA+aooZ9u+ffsrEXFOpQ1GxNvy0dHREc167LHHml53vNU5W4TzVVXnfHXOFuF8VZSzAdui4vdfn6oyM7MsLg4zM8vi4jAzsywuDjMzy+LiMDOzLC4OMzPL4uIwM7MsLg4zM8vi4jAzsyxv27ccmQhz13x3zMv23/jRcUxiZlYfPuIwM7MsLg4zM8vi4jAzsywuDjMzy+LiMDOzLC4OMzPL4uIwM7MsLg4zM8vi4jAzsywuDjMzy+LiMDOzLKMWh6TbJR2UtKs09i1JO9KjX9KOND5X0huleX9dWqdD0k5JfZJulaQ0Pi1tr0/SU5Lmtn43zcysVcZyxHEHsLw8EBF/HBGLI2IxcD/w7dLsvUPzIuLTpfH1wGpgfnoMbfMa4HBEvA+4BbipmR0xM7OJMWpxRMTjwKFG89JRwx8Bm0bahqSZwGkR8UREBHAXcFmafSlwZ5q+D1g2dDRiZmb1o+L7+CgLFaePHoyIhcPGLwC+GhGdpeV2Az8GXgP+c0T8b0mdwI0RcWFa7nzghoi4JJ0CWx4RA2neXuCDEfFKgxyrKY5aaG9v7+jp6WlqpwcHB2lraxt1uZ0vHxnzNhfNmtFUluHGmm2yOF81dc5X52zgfFWUs3V3d28f+p7drKqfx7GStx5t7Af+TUS8KqkD+I6k84BGRxBDjTXSvLcORmwANgB0dnZGV1dXU6F7e3sZy7pX53wex5XNZRlurNkmi/NVU+d8dc4GzldFq7M1XRySpgJ/CHQMjUXEUeBomt6ejh7eDwwAs0urzwb2pekBYA4wkLY5g5OcGjMzs8lX5YjjQuD5oVNMAJLOAQ5FxHFJ76W4CP7TiDgk6XVJS4GngKuA29JqW4BVwBPA5cCjMZbzZxXsfPlI1tGEmZm9aSy3426i+KZ+rqQBSdekWSs48aL4BcCPJP2Q4kL3pyNi6OjhWuBvgT5gL7A1jW8EzpLUB3weWFNhf8zMbJyNesQREStPMn51g7H7KW7PbbT8NmBhg/FfAleMlsPMzOrBvzluZmZZXBxmZpbFxWFmZllcHGZmlsXFYWZmWVwcZmaWxcVhZmZZXBxmZpbFxWFmZllcHGZmlsXFYWZmWVwcZmaWxcVhZmZZXBxmZpbFxWFmZllcHGZmlsXFYWZmWVwcZmaWZSyfOX67pIOSdpXGvizpZUk70uPi0ry1kvok7ZF0UWm8Q9LONO9WSUrj0yR9K40/JWlui/fRzMxaaCxHHHcAyxuM3xIRi9PjIQBJC4AVwHlpna9LmpKWXw+sBuanx9A2rwEOR8T7gFuAm5rcFzMzmwCjFkdEPA4cGuP2LgV6IuJoRLwA9AFLJM0ETouIJyIigLuAy0rr3Jmm7wOWDR2NmJlZ/UytsO5nJF0FbAOuj4jDwCzgydIyA2nsV2l6+Djp+SWAiDgm6QhwFvDK8C8oaTXFUQvt7e309vY2Fbx9Oly/6FhT655Ms1mGGxwcbNm2xoPzVVPnfHXOBs5XRauzNVsc64GvAJGebwY+CTQ6UogRxhll3lsHIzYAGwA6Ozujq6srK/SQ2+7ZzM07q3TmifqvbC7LcL29vTS7XxPB+aqpc746ZwPnq6LV2Zq6qyoiDkTE8Yj4F+AbwJI0awCYU1p0NrAvjc9uMP6WdSRNBWYw9lNjZmY2wZoqjnTNYsjHgaE7rrYAK9KdUvMoLoI/HRH7gdclLU3XL64CNpfWWZWmLwceTddBzMyshkY9XyNpE9AFnC1pAPgS0CVpMcUppX7gUwARsVvSvcCzwDHguog4njZ1LcUdWtOBrekBsBH4pqQ+iiONFS3YLzMzGyejFkdErGwwvHGE5dcB6xqMbwMWNhj/JXDFaDnMzKwe/JvjZmaWxcVhZmZZXBxmZpbFxWFmZllcHGZmlsXFYWZmWVwcZmaWxcVhZmZZXBxmZpbFxWFmZllcHGZmlsXFYWZmWVwcZmaWxcVhZmZZXBxmZpbFxWFmZllcHGZmlsXFYWZmWUYtDkm3SzooaVdp7L9Jel7SjyQ9IOn0ND5X0huSdqTHX5fW6ZC0U1KfpFslKY1Pk/StNP6UpLmt300zM2uVsRxx3AEsHzb2MLAwIn4b+DGwtjRvb0QsTo9Pl8bXA6uB+ekxtM1rgMMR8T7gFuCm7L0wM7MJM2pxRMTjwKFhY9+PiGPp5ZPA7JG2IWkmcFpEPBERAdwFXJZmXwrcmabvA5YNHY2YmVn9tOIaxyeBraXX8yT9QNL/knR+GpsFDJSWGUhjQ/NeAkhldAQ4qwW5zMxsHKg4ABhloeK6w4MRsXDY+BeBTuAPIyIkTQPaIuJVSR3Ad4DzgHOBP4+IC9N65wNfiIg/kLQbuCgiBtK8vcCSiHi1QY7VFKe7aG9v7+jp6Wlqpw8eOsKBN5pa9aQWzZrRku0MDg7S1tbWkm2NB+erps756pwNnK+Kcrbu7u7tEdFZZXtTm11R0irgEmBZOv1ERBwFjqbp7akE3k9xhFE+nTUb2JemB4A5wICkqcAMhp0aGxIRG4ANAJ2dndHV1dVU9tvu2czNO5ve9Yb6r2wuy3C9vb00u18TwfmqqXO+OmcD56ui1dmaOlUlaTlwA/CxiPhFafwcSVPS9HspLoL/NCL2A69LWpquX1wFbE6rbQFWpenLgUdjLIdBZmY2KUb9sVvSJqALOFvSAPAliruopgEPp+vYT6Y7qC4A/qukY8Bx4NMRMXT0cC3FHVrTKa6JDF0X2Qh8U1IfxZHGipbsmZmZjYtRiyMiVjYY3niSZe8H7j/JvG3AwgbjvwSuGC2HmZnVg39z3MzMsrg4zMwsi4vDzMyyuDjMzCyLi8PMzLK4OMzMLIuLw8zMsrg4zMwsi4vDzMyyuDjMzCyLi8PMzLK4OMzMLIuLw8zMsrg4zMwsi4vDzMyyuDjMzCyLi8PMzLK4OMzMLIuLw8zMsoz6meOSbgcuAQ5GxMI0dibwLWAu0A/8UUQcTvPWAtcAx4H/EBHfS+MdwB3AdOAh4LMREZKmAXcBHcCrwB9HRH/L9nCCzF3z3TEt13/jR8c5iZnZ+BrLEccdwPJhY2uARyJiPvBIeo2kBcAK4Ly0ztclTUnrrAdWA/PTY2ib1wCHI+J9wC3ATc3ujJmZjb9RiyMiHgcODRu+FLgzTd8JXFYa74mIoxHxAtAHLJE0EzgtIp6IiKA4wriswbbuA5ZJUnO7Y2Zm403F9/FRFpLmAg+WTlX9LCJOL80/HBFnSPoa8GRE3J3GNwJbKU5n3RgRF6bx84EbIuISSbuA5RExkObtBT4YEa80yLGa4qiF9vb2jp6enqZ2+uChIxx4o6lVK1s0a8aI8wcHB2lra5ugNPmcr5o656tzNnC+KsrZuru7t0dEZ5XtjXqNI1OjI4UYYXykdU4cjNgAbADo7OyMrq6uJiLCbfds5uadrd71sem/smvE+b29vTS7XxPB+aqpc746ZwPnq6LV2Zq9q+pAOv1Eej6YxgeAOaXlZgP70vjsBuNvWUfSVGAGJ54aMzOzmmi2OLYAq9L0KmBzaXyFpGmS5lFcBH86IvYDr0tamq5fXDVsnaFtXQ48GmM5f2ZmZpNiLLfjbgK6gLMlDQBfAm4E7pV0DfAicAVAROyWdC/wLHAMuC4ijqdNXcubt+NuTQ+AjcA3JfVRHGmsaMmemZnZuBi1OCJi5UlmLTvJ8uuAdQ3GtwELG4z/klQ8ZmZWf/7NcTMzy+LiMDOzLC4OMzPL4uIwM7MsLg4zM8vi4jAzsywuDjMzy+LiMDOzLC4OMzPL4uIwM7MsLg4zM8vi4jAzsywuDjMzy+LiMDOzLC4OMzPL4uIwM7MsLg4zM8vi4jAzsyxNF4ekcyXtKD1ek/Q5SV+W9HJp/OLSOmsl9UnaI+mi0niHpJ1p3q2SVHXHzMxsfDRdHBGxJyIWR8RioAP4BfBAmn3L0LyIeAhA0gJgBXAesBz4uqQpafn1wGpgfnosbzaXmZmNr1adqloG7I2I/zfCMpcCPRFxNCJeAPqAJZJmAqdFxBMREcBdwGUtymVmZi3WquJYAWwqvf6MpB9Jul3SGWlsFvBSaZmBNDYrTQ8fNzOzGlLxQ36FDUinAPuA8yLigKR24BUggK8AMyPik5L+CngiIu5O620EHgJeBP48Ii5M4+cDX4iIP2jwtVZTnNKivb29o6enp6nMBw8d4cAbTa1a2aJZM0acPzg4SFtb2wSlyed81dQ5X52zgfNVUc7W3d29PSI6q2xvagsyfQR4JiIOAAw9A0j6BvBgejkAzCmtN5uicAbS9PDxE0TEBmADQGdnZ3R1dTUV+LZ7NnPzzlbser7+K7tGnN/b20uz+zURnK+aOuerczZwvipana0Vp6pWUjpNla5ZDPk4sCtNbwFWSJomaR7FRfCnI2I/8LqkpeluqquAzS3IZWZm46DSj92S3g38HvCp0vBfSFpMcaqqf2heROyWdC/wLHAMuC4ijqd1rgXuAKYDW9PDzMxqqFJxRMQvgLOGjX1ihOXXAesajG8DFlbJYmZmE8O/OW5mZllcHGZmlsXFYWZmWVwcZmaWxcVhZmZZXBxmZpbFxWFmZllcHGZmlsXFYWZmWVwcZmaWxcVhZmZZXBxmZpbFxWFmZllcHGZmlsXFYWZmWVwcZmaWxcVhZmZZXBxmZpbFxWFmZlkqFYekfkk7Je2QtC2NnSnpYUk/Sc9nlJZfK6lP0h5JF5XGO9J2+iTdKklVcpmZ2fhpxRFHd0QsjojO9HoN8EhEzAceSa+RtABYAZwHLAe+LmlKWmc9sBqYnx7LW5DLzMzGwXicqroUuDNN3wlcVhrviYijEfEC0AcskTQTOC0inoiIAO4qrWNmZjWj4nt1kytLLwCHgQD+JiI2SPpZRJxeWuZwRJwh6WvAkxFxdxrfCGwF+oEbI+LCNH4+cENEXNLg662mODKhvb29o6enp6ncBw8d4cAbTa1a2aJZM0acPzg4SFtb2wSlyed81dQ5X52zgfNVUc7W3d29vXSGqClTK+b5cETsk/QbwMOSnh9h2UbXLWKE8RMHIzYAGwA6Ozujq6srM27htns2c/POqrvenP4ru0ac39vbS7P7NRGcr5o656tzNnC+KlqdrdKpqojYl54PAg8AS4AD6fQT6flgWnwAmFNafTawL43PbjBuZmY11HRxSDpV0nuGpoHfB3YBW4BVabFVwOY0vQVYIWmapHkUF8Gfjoj9wOuSlqa7qa4qrWNmZjVT5XxNO/BAunN2KvB3EfH3kv4RuFfSNcCLwBUAEbFb0r3As8Ax4LqIOJ62dS1wBzCd4rrH1gq5zMxsHDVdHBHxU+DfNhh/FVh2knXWAesajG8DFjabxczMJo5/c9zMzLK4OMzMLIuLw8zMsrg4zMwsi4vDzMyyuDjMzCzL5LzvxjvY3DXfHXH+9YuOcfWa79J/40cnKJGZWR4fcZiZWRYXh5mZZXFxmJlZFheHmZllcXGYmVkWF4eZmWVxcZiZWRYXh5mZZXFxmJlZFheHmZllcXGYmVmWpotD0hxJj0l6TtJuSZ9N41+W9LKkHelxcWmdtZL6JO2RdFFpvEPSzjTvVqUPMjczs/qp8iaHx4DrI+IZSe8Btkt6OM27JSL+srywpAXACuA84DeBf5D0/og4DqwHVgNPAg8By4GtFbKZmdk4afqIIyL2R8Qzafp14Dlg1girXAr0RMTRiHgB6AOWSJoJnBYRT0REAHcBlzWby8zMxpeK79UVNyLNBR4HFgKfB64GXgO2URyVHJb0NeDJiLg7rbOR4qiiH7gxIi5M4+cDN0TEJQ2+zmqKIxPa29s7enp6msp78NARDrzR1Krjrn06HHgDFs2aMdlRGhocHKStrW2yY5yU8zWvztnA+aooZ+vu7t4eEZ1Vtlf58zgktQH3A5+LiNckrQe+AkR6vhn4JNDoukWMMH7iYMQGYANAZ2dndHV1NZX5tns2c/POen4UyfWLjnHzzqn0X9k12VEa6u3tpdk/94ngfM2rczZwvipana3Sd09J76IojXsi4tsAEXGgNP8bwIPp5QAwp7T6bGBfGp/dYNzMrLaGfyjb0IewDffr+KFsVe6qErAReC4ivloan1la7OPArjS9BVghaZqkecB84OmI2A+8Lmlp2uZVwOZmc5mZ2fiqcsTxYeATwE5JO9LYfwJWSlpMcbqpH/gUQETslnQv8CzFHVnXpTuqAK4F7gCmU1z38B1VZmY11XRxRMT/ofH1iYdGWGcdsK7B+DaKC+tmZlZz/s1xMzPL4uIwM7MsLg4zM8vi4jAzsywuDjMzy+LiMDOzLC4OMzPL4uIwM7MsLg4zM8tSz7eINTP7NTH8zRBH8nZ5Q0QfcZiZWRYXh5mZZXFxmJlZFheHmZllcXGYmVkWF4eZmWVxcZiZWRYXh5mZZXFxmJlZltoUh6TlkvZI6pO0ZrLzmJlZY7UoDklTgL8CPgIsAFZKWjC5qczMrJG6vFfVEqAvIn4KIKkHuBR4dlJTvQ3kvA/OWLxd3ivn18VY//7892J1ooiY7AxIuhxYHhF/ml5/AvhgRHxm2HKrgdXp5bnAnia/5NnAK02uO97qnA2cr6o656tzNnC+KsrZfisizqmysboccajB2AmNFhEbgA2Vv5i0LSI6q25nPNQ5GzhfVXXOV+ds4HxVtDpbLa5xAAPAnNLr2cC+ScpiZmYjqEtx/CMwX9I8SacAK4Atk5zJzMwaqMWpqog4JukzwPeAKcDtEbF7HL9k5dNd46jO2cD5qqpzvjpnA+eroqXZanFx3MzM3j7qcqrKzMzeJlwcZmaW5R1VHJP1tiaSbpd0UNKu0tiZkh6W9JP0fEZp3tqUcY+ki0rjHZJ2pnm3Smp0G3NutjmSHpP0nKTdkj5bs3z/WtLTkn6Y8v1ZnfKl7U6R9ANJD9YwW3/a7g5J22qY73RJ90l6Pv0b/FBd8kk6N/25DT1ek/S5GuX7j+n/xC5Jm9L/lYnJFhHviAfFRfe9wHuBU4AfAgsm6GtfAHwA2FUa+wtgTZpeA9yUphekbNOAeSnzlDTvaeBDFL/3shX4SAuyzQQ+kKbfA/w4ZahLPgFtafpdwFPA0rrkS9v9PPB3wIN1+rtN2+0Hzh42Vqd8dwJ/mqZPAU6vU75SzinAPwG/VYd8wCzgBWB6en0vcPVEZWvZH2zdH+kP5nul12uBtRP49efy1uLYA8xM0zOBPY1yUdxp9qG0zPOl8ZXA34xDzs3A79UxH/Bu4Bngg3XJR/E7R48Av8ubxVGLbGlb/ZxYHLXIB5xG8c1Pdcw3LNPvA/+3LvkoiuMl4EyKu2MfTBknJNs76VTV0B/0kIE0NlnaI2I/QHr+jTR+spyz0vTw8ZaRNBf4HYqf6muTL50K2gEcBB6OiDrl++/AF4B/KY3VJRsU78DwfUnbVbxlT53yvRf4Z+B/pFN9fyvp1BrlK1sBbErTk54vIl4G/hJ4EdgPHImI709UtndScYzpbU1q4GQ5xzW/pDbgfuBzEfHaSIueJMe45YuI4xGxmOKn+yWSFo6w+ITlk3QJcDAito91lZNkGM+/2w9HxAco3nn6OkkXjLDsROebSnEKd31E/A7wc4rTKyczWf83TgE+BvzP0RY9SY7x+Ld3BsUbwc4DfhM4VdKfTFS2d1Jx1O1tTQ5ImgmQng+m8ZPlHEjTw8crk/QuitK4JyK+Xbd8QyLiZ0AvsLwm+T4MfExSP9AD/K6ku2uSDYCI2JeeDwIPULwTdV3yDQAD6QgS4D6KIqlLviEfAZ6JiAPpdR3yXQi8EBH/HBG/Ar4N/LuJyvZOKo66va3JFmBVml5FcW1haHyFpGmS5gHzgafTYefrkpamux6uKq3TtLStjcBzEfHVGuY7R9LpaXo6xX+Y5+uQLyLWRsTsiJhL8e/p0Yj4kzpkA5B0qqT3DE1TnAPfVZd8EfFPwEuSzk1Dyyg+SqEW+UpW8uZpqqEck53vRWCppHenbS4DnpuwbK28gFT3B3AxxV1De4EvTuDX3URxHvJXFA1/DXAWxUXVn6TnM0vLfzFl3EPpDgegk+I//l7gawy7qNhktn9PcWj6I2BHelxco3y/Dfwg5dsF/Jc0Xot8pW138ebF8Vpko7iG8MP02D30b74u+dJ2FwPb0t/vd4Azapbv3cCrwIzSWC3yAX9G8UPULuCbFHdMTUg2v+WImZlleSedqjIzsxZwcZiZWRYXh5mZZXFxmJlZFheHmZllcXGYmVkWF4eZmWX5/2MbqkuxTVw3AAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "fsu.FLT_NUM.hist(bins=30)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "radical-calculation",
   "metadata": {},
   "source": [
    "We observe that there are two main regions. Let us magnify: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "portable-hebrew",
   "metadata": {},
   "outputs": [],
   "source": [
    "fsu.FLT_NUM.hist(bins=30)\n",
    "plt.ylim(0, 100)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "confidential-tanzania",
   "metadata": {},
   "source": [
    "At this point, the Copa team informs me to only consider flights between 1 and 1000.\n",
    "* So we delete flight numbers greater than 999 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "widespread-bidding",
   "metadata": {},
   "outputs": [],
   "source": [
    "fsu.loc[:, 'FLT_NUM'] = fsu[fsu['FLT_NUM'] < 1000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "current-saying",
   "metadata": {},
   "outputs": [],
   "source": [
    "# The flights have been removed. Always check your calculations. \n",
    "fsu.FLT_NUM.hist(bins=30)\n",
    "plt.ylim(0,100)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "consolidated-danish",
   "metadata": {},
   "source": [
    "## Most columns are objects\n",
    "* Many should be floats or integers\n",
    "* Look at MONTH (string), a WEEK (int), YEAR (int) \n",
    "* What is the MONTH of? Departure, arrival, something else? \n",
    "* Find lines where departure and arrival occur on separate days\n",
    "* From the list below, there is no way to tell. \n",
    "    * ==> Ignore MONTH column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "electronic-archive",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Departure and arrivals are sometimes on different dates\n",
    "# Questions: is the difference ever more than unity?\n",
    "fsux = fsu[fsu['SCH_DEP_DTL'] != fsu['SCH_ARR_DTL']]\n",
    "fsux[['SCH_DEP_DTL','SCH_ARR_DTL','MONTH','WEEK','YEAR']].head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dress-hudson",
   "metadata": {},
   "source": [
    "* So I must be able to subtract dates. This leads to the need of having functions to simplify the process. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "geological-destruction",
   "metadata": {},
   "source": [
    "### MONTH is incorrectly encoded\n",
    "* Remove MONTH from the DataFrame. We'll renmove WEEK and YEAR as well. \n",
    "* Easiest way: convert datetimes to nanoseconds, in integer format\n",
    "    * In this formation, conversion to other formats is extremely fast"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "photographic-feeding",
   "metadata": {},
   "outputs": [],
   "source": [
    "fsu['SCH_DEP_DTZ'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fatty-satisfaction",
   "metadata": {},
   "source": [
    "Immedate observation: this will create a problem with sorting in string format: \n",
    "* the following inequality should be True, but is not. \n",
    "* Reason the dates are sometimes one sometimes two digits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cross-guide",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"2/15/2020\" < \"12/15/2020\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "parental-inspiration",
   "metadata": {},
   "source": [
    "Write some utilities to transform dates to nanoseconds and back"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "outstanding-belly",
   "metadata": {},
   "outputs": [],
   "source": [
    "def to_nanoseconds(date):\n",
    "    return pd.to_datetime(date).astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "overhead-football",
   "metadata": {},
   "source": [
    "Notice how long this takes (for 50,000 rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "silver-joyce",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time \n",
    "nano = to_nanoseconds(fsu.SCH_DEP_DTMZ)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "graduate-advantage",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "pd.to_datetime(nano).dt.strftime('%Y/%m/%d')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "comparable-petroleum",
   "metadata": {},
   "source": [
    "Execution time is about 10x faster. \n",
    "* All strings are formatted identically. \n",
    "* Comparisons will work properly"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "careful-fishing",
   "metadata": {},
   "source": [
    "## How to convert columns to integers and floats, but not by hand\n",
    "* Date conversions are done in cleanupColumns() methods in src/copa/utils_function.py\n",
    "    * I do this in the function cleanupColumns() in utils_function"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "existing-routine",
   "metadata": {},
   "source": [
    "Utility function for date conversion (found in util_functions.py)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "demanding-scene",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a function to automate this process. Will be required multiple times\n",
    "# Once the function is debugged, transfer it to a library of functions (utils_function.py)\n",
    "def series_to_date_components(series, prefix=\"\", suffix=\"\"):\n",
    "    \"\"\"\n",
    "    Decomposes a date column into day, week, dayofweek, month, year\n",
    "    \n",
    "        Parameters:\n",
    "            series (DataSeries): A date column of a dataframe as a string or integer\n",
    "            prefix (string): prefix added to column names [default: \"\"]\n",
    "            suffix (string): suffix added to column names [default: \"\"]\n",
    "            \n",
    "        Return (DataFrame): a dataframe with the day, week, dayofweek, \n",
    "            month, year as columns\n",
    "    \"\"\"\n",
    "    \n",
    "    date = pd.to_datetime(series)\n",
    "    month = date.dt.month\n",
    "    year = date.dt.year\n",
    "    day = date.dt.day\n",
    "    week = date.dt.isocalendar().week\n",
    "    dayofweek = date.dt.dayofweek\n",
    "    titles = np.asarray(['d','w','dw','mo','y'])\n",
    "    t = list(map(lambda x: (prefix+x+suffix), titles))  # Anonymous function\n",
    "    df = pd.DataFrame({t[0]:day, t[1]:week, t[2]: dayofweek, t[3]:month, t[4]:year})\n",
    "    return df\n",
    "\n",
    "day = series_to_date_components(fsu['SCH_DEP_DTZ'], prefix='schdepz_')\n",
    "day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "innocent-third",
   "metadata": {},
   "outputs": [],
   "source": [
    "# While we are at it, create a function to extract time comopnents\n",
    "def series_to_time_components(series, prefix=\"\", suffix=\"\"):\n",
    "    \"\"\"\n",
    "    Decomposes a date columns into hours/min\n",
    "    \n",
    "        Parameters:\n",
    "            series (DataSeries): A date column of a dataframe as a string\n",
    "            prefix (string): prefix added to column names [default: \"\"]\n",
    "            suffix (string): suffix added to column names [default: \"\"]\n",
    "            \n",
    "         Return (DataFrame): a dataframe with the day, week, dayofweek, \n",
    "            month, year as column\n",
    "    \"\"\"\n",
    "    \n",
    "    date = pd.to_datetime(series)\n",
    "    hour = date.dt.hour\n",
    "    minute = date.dt.minute\n",
    "    titles = np.asarray(['h','mi'])\n",
    "    t = list(map(lambda x: (prefix+x+suffix), titles))\n",
    "    df = pd.DataFrame({t[0]:hour, t[1]:minute})\n",
    "    return df\n",
    "\n",
    "time = series_to_time_components(fsu['SCH_DEP_TMZ'], prefix='schdep_')\n",
    "time"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "hydraulic-embassy",
   "metadata": {},
   "source": [
    "### The two functions were added to src/copa/util_functions.py for use in other notebooks"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "front-decrease",
   "metadata": {},
   "source": [
    "## Iterate\n",
    "* As we continue to explore the data, we iterate on the cleanup process\n",
    "* Below is a function in util_functions.py to cleanup the data\n",
    "* As the function becomes more complex, it can broken up into simpler functions that are reusable. \n",
    "* The current cleanupColumn() has improvements\n",
    "    * all DTMZ columns have been converted to datetime and then int\n",
    "    * Undefined datas have been converted to a single date: Jan 1, 1960\n",
    "    * Undefined dates are detected by the number by a negative value for the number of nanoseconds \n",
    "        * Dates normally have an origin sometime in 1970.\n",
    "* Many string fields are trimmed. I found that Copa airlines 'CM' was actually stored as 'CM ' . Notice the space as the last character, which is invisible. \n",
    "* All question marks (?) are converted to NaN\n",
    "    * Must choose NaN versus None versus null versus an integer or float imputation\n",
    "    * NaN is a good choice because it considered as a float, and is ignored during various  calculations unless we specify otherwise. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "practical-queen",
   "metadata": {},
   "outputs": [],
   "source": [
    "def cleanupColumns(df1):\n",
    "    \"\"\"\n",
    "    cleanupColumns(df)\n",
    "\n",
    "    Cleanup the columns of the FSU tables provided by Copa in Jan. 2021.\n",
    "    No columns are deleted. Character columns are stripped (blank spaces removed in front and back).\n",
    "    Some columns are converted to int.\n",
    "    Time columns (DTMZ) only keep hours and minutes.\n",
    "    Date columns are changed so that the dates are sortable (yyyy/mm/dd) as strings.\n",
    "\n",
    "    Return: modified dataframe.\n",
    "    \"\"\"\n",
    "    df = df1.copy()\n",
    "    non_converted = []\n",
    "\n",
    "    # Strip 'object' or 'str' types\n",
    "    # Convert other columns to integers after converting NaN\n",
    "    for col in df.columns:\n",
    "        if df[col].dtype == 'O' or df[col].dtype == 'str':\n",
    "            df[col] = df[col].str.strip()\n",
    "        else:\n",
    "            try:\n",
    "                pass\n",
    "                df[col] = df[col].astype('int')\n",
    "            except:\n",
    "                #print(col)\n",
    "                try:\n",
    "                    df[col] = df[col].fillna(-1).astype('int')\n",
    "                    df[col] = df[col].astype('int')\n",
    "                except:\n",
    "                    non_converted.append(col)\n",
    "\n",
    "    df.SCH_DEP_TMZ = df.SCH_DEP_TMZ.str[0:5]\n",
    "    df.SCH_DEP_DTZ = pd.to_datetime(df.SCH_DEP_DTZ)\n",
    "    df.SCH_ARR_TMZ = df.SCH_ARR_TMZ.str[0:5]\n",
    "    df.SCH_ARR_DTZ = pd.to_datetime(df.SCH_ARR_DTZ)\n",
    "\n",
    "    # Remove extraneous blank space\n",
    "    df['FOD'] = df['FOD'].str.strip()\n",
    "    df['FOD_REAL'] = df['FOD_REAL'].str.strip()\n",
    "    df['OD'] = df['OD'].str.strip()\n",
    "    df['ORIGIN_PLANNED'] = df['ORIGIN_PLANNED'].str.strip()\n",
    "    df['ORIG_CD'] = df['ORIG_CD'].str.strip()\n",
    "    df['DEST_CD'] = df['DEST_CD'].str.strip()\n",
    "\n",
    "    df['COUNT_ATB'] = df['COUNT_ATB'].astype('int')\n",
    "    df['COUNT_GTB'] = df['COUNT_GTB'].astype('int')\n",
    "\n",
    "    df['COUNT_DIVERSION'] = df['COUNT_DIVERSION'].astype('int')\n",
    "\n",
    "    df['FOD_REAL'] = df['FOD_REAL'].str.strip()\n",
    "    df['FOD'] = df['FOD'].str.strip()\n",
    "    df['FLT_NUM'] = df['FLT_NUM'].astype('int')\n",
    "    df['DEP_DELAY_MINUTES'] = df['DEP_DELAY_MINUTES'].fillna(9999).astype('int')\n",
    "\n",
    "    df['WEEK'] = df['WEEK'].astype('int')\n",
    "    df['CAPACITY_CNT'] = df['CAPACITY_CNT'].astype('int')\n",
    "    df['CREW_CNT'] = df['CREW_CNT'].fillna(0).astype('int')\n",
    "    df['CAPACITY_C_CNT'] = df['CAPACITY_C_CNT'].astype('int')\n",
    "    df['CAPACITY_Y_CNT'] = df['CAPACITY_Y_CNT'].astype('int')\n",
    "\n",
    "    # Drop ETD_DTMZ: 93% of rows are '?'\n",
    "    df = df.drop(labels='ETD_DTMZ', axis=1)\n",
    "    df['ETA_DTMZ'] = df['ETA_DTMZ'].replace('?',\"0/0/0000 00:00:00.000\")\n",
    "    df['ON_DTMZ'] = df['ON_DTMZ'].replace('?',\"0/0/0000 00:00:00.000\")\n",
    "    df['OFF_DTMZ'] = df['OFF_DTMZ'].replace('?',\"0/0/0000 00:00:00.000\")\n",
    "    df['ACTUAL_BLOCK_HR'] = df['ACTUAL_BLOCK_HR'].replace('?',-1)\n",
    "    df['CANCEL_CD'] = df['CANCEL_CD'].replace('?',-1)\n",
    "    df['CANCEL_CD'] = df['CANCEL_CD'].fillna(-1)\n",
    "    df['CANCEL_CD'] = df['CANCEL_CD'].astype('int')\n",
    "    df['CANCEL_REASON_DESCRIPTION'] = df['CANCEL_REASON_DESCRIPTION'].replace('?','-1')\n",
    "\n",
    "    df.AC_OWNER_CARRIER_CD = df.AC_OWNER_CARRIER_CD.str.strip()\n",
    "\n",
    "    # Based on the output of pandas_profiling, remove columns where all variables are identical\n",
    "    df = df.drop(labels=['SCHEDULED_ALL','FUENTE'], axis=1)\n",
    "    return [df, non_converted]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "incomplete-baltimore",
   "metadata": {},
   "source": [
    "## Clean the FSU file and save to the Rosetta folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "interpreted-opening",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Notice the question mark. These are hard to deal with than NaNs\n",
    "# NaN are actually considered as floats\n",
    "# The '?' prevents the column from converting to either float or int\n",
    "f = fsu['DEP_DELAY_MINUTES']\n",
    "f[90:100]\n",
    "ff = f.replace('?', '9999')  # for some fields I convert to int\n",
    "ff[90:100]   # the '?': on line 95 is gone\n",
    "# Cannot convert '3.5' to int via astype('int')\n",
    "# But I can convert '3.0' to int via astype('int')\n",
    "ff.astype('float').astype('int')  \n",
    "\n",
    "# Update cleanupColumns()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "mental-husband",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Number of unknown crew counts: \", (fsu['CREW_CNT'] == '?').sum())\n",
    "fsu['CREW_CNT']\n",
    "#must again first convert to float, and then to int. I updated the underlying library"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "spatial-distinction",
   "metadata": {},
   "outputs": [],
   "source": [
    "fsu1 = fsu.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fatty-serum",
   "metadata": {},
   "outputs": [],
   "source": [
    "fsu1.OUT_DTMZ"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "animal-drive",
   "metadata": {},
   "source": [
    "### Use the cleanupColumns defined in `util_functions.py`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "alike-message",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time \n",
    "fsu1, non_converted = u.cleanupColumns(fsu)  # Use the underlying library\n",
    "print(non_converted)\n",
    "fsu1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "royal-burden",
   "metadata": {},
   "source": [
    "Total time to execute cleanupColumns is about 1m 30s. Very long. Mostly due to date conversions. \n",
    "* The file has 50,000 rows. If the file had 5 million rows, this would take 100 times longer!\n",
    "* This is why converting dates to integer once makes sense. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "opening-effects",
   "metadata": {},
   "source": [
    "## Looking at the column, there are several more needed enhancements\n",
    "* Some of the COUNT columns have NaN (i.e., COUNT_ARR14) ==> replace NaN by -1\n",
    "* Same idea with COUNT_ARR15, ARR90, DEP00, DEP05, DEP15, DEP80, TAXI_IN_MINUTES, TAX_OUT_MINUTES\n",
    "* It is not clear whether floats or integers are better. Probably floats if eventually, we move to machine learning and neural networks. \n",
    "* BEWARE: conditionals with floats are sometimes tricky. \n",
    "    * x = 1.;  x == 1.0 is True most of the time, but not always. Depends on the machine.\n",
    "    * if x is either 0. or 1., a better test is x > 0.5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "minimal-pearl",
   "metadata": {},
   "source": [
    "* Replace all '?' by -1. Create a function to process all relevant columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "mechanical-direction",
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['ARR00','ARR14','ARR15','ARR90','DEP00','DEP05','DEP15','DEP80']\n",
    "# A map() is an iterator. Transform to a list. \n",
    "cols1 = list(map(lambda x: 'COUNT_'+x, cols)) \n",
    "dfc = fsu[cols1]\n",
    "fsu2 = fsu.copy()\n",
    "\n",
    "# Apply a function to every column and return a dataframe\n",
    "fsu2[cols1] = dfc.apply(lambda x: x.replace('?',-1).astype(float).astype(int))   # Does not work"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "duplicate-elimination",
   "metadata": {},
   "source": [
    "The faster way to convert '?' to NaN would be: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "italic-testament",
   "metadata": {},
   "outputs": [],
   "source": [
    "fsu2 = fsu2.replace('?', np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "equal-reality",
   "metadata": {},
   "outputs": [],
   "source": [
    "fsu2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "correct-loading",
   "metadata": {},
   "outputs": [],
   "source": [
    "(fsu['IN_DTMZ'] == '?').sum()  # 98 values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "little-pearl",
   "metadata": {},
   "outputs": [],
   "source": [
    "(fsu2['IN_DTMZ'] == '?').sum()  #0 values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "former-investigator",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Look at IN. Only keep hours and min.\n",
    "u.series_to_time_components(fsu2['IN_DTMZ'])  # Crashes because of '?' in the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "collaborative-sheep",
   "metadata": {},
   "outputs": [],
   "source": [
    "fsu2.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "breeding-incident",
   "metadata": {},
   "outputs": [],
   "source": [
    "# DEP_DELAY_INTERVAL is redundant\n",
    "fsu3 = fsu2.drop(labels=['DEP_DELAY_INTERVAL','ARR_DELAY_INTERVAL','CANCEL_REASON_DESCRIPTION','DWHLOAD_DTML','ETD_DTMZ',\n",
    "                         'FOD','FOD_REAL','OPERATED_ALL','OPERATED_CNT_ALL','ROTATION_@STATION','SCH_ARR_DTL',\n",
    "                         'SCH_ARR_TML_PTY','SCH_DEP_DTL','SCH_DEP_DTML_PTY','SCH_DEP_TML_PTY','WEEK','YEAR','MONTH','FUENTE'], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "instant-dynamics",
   "metadata": {},
   "source": [
    "## Simplify columns. Times have the format hh:mm.ss.000.\n",
    "* Remove .ss.000 to make column content shorter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "chinese-manchester",
   "metadata": {},
   "outputs": [],
   "source": [
    "# These should be added to the cleanColumns() method\n",
    "fsu3['IN'] = fsu3['IN'].str[0:5]\n",
    "fsu3['OUT'] = fsu3['OUT'].str[0:5]\n",
    "fsu3['OFF'] = fsu3['OFF'].str[0:5]\n",
    "fsu3['ON'] = fsu3['ON'].str[0:5]\n",
    "fsu3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "municipal-selection",
   "metadata": {},
   "outputs": [],
   "source": [
    "fsu2['IN'] = fsu2['IN'].replace('?', '00:00')\n",
    "u.series_to_time_components(fsu2['IN'])  # Crashes because of '?' in the dat"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "available-postcard",
   "metadata": {},
   "source": [
    "# Reload the original FSU files and clean it, and output the result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "crude-microphone",
   "metadata": {},
   "outputs": [],
   "source": [
    "fsu = pd.read_csv(\"../data/Copa-FSU-02172021.csv\")\n",
    "fsu = fsu.iloc[:,:] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "prostate-brain",
   "metadata": {},
   "outputs": [],
   "source": [
    "fsu, not_converted = u.cleanupColumns(fsu)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "alternate-champagne",
   "metadata": {},
   "outputs": [],
   "source": [
    "fsu.OUT_DTMZ"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "metallic-swiss",
   "metadata": {},
   "outputs": [],
   "source": [
    "fsu.to_csv(\"data/fsu_clean.csv\", index=0)  # Use for plotting"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "atlantic-mason",
   "metadata": {},
   "source": [
    "* Return to the cleanup routine for further clean up we might have missed "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "exposed-tactics",
   "metadata": {},
   "outputs": [],
   "source": [
    "fsu.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "imposed-tennessee",
   "metadata": {},
   "source": [
    "## List of functions in src/copa/util_functions.py:\n",
    "* def setPandasOptions():\n",
    "* def upperToLower(df):\n",
    "* def getSortedHourCount(series, msg=\"\", nrows=10):\n",
    "* def cleanupColumns(df1):\n",
    "* def to_datetime(series):\n",
    "* def additionalCleanup(df):\n",
    "* def displayCancellations(df):\n",
    "* def listDiversions(df):\n",
    "* def listSpecialFlights(df):\n",
    "* def filterFlights(df):\n",
    "* def filterCarriers(df):\n",
    "* def removeCancellations(df):\n",
    "* def removeDiversions(df):\n",
    "* def removeTurnbacks(df):\n",
    "* def removeOeqD(df):\n",
    "* def relabelColumnsFSU(fsu):\n",
    "* def checkUniques(df, max_unique=200, print_unique=30):\n",
    "* def checkColFloat(df, col_nm):\n",
    "* def nbNonFloats(df):\n",
    "* def makeFloatDf(df):\n",
    "* def connectingFromPaxStatistics(df):\n",
    "* def connectingToPaxStatistics(df):\n",
    "* def plotFeederPaxByDates(df_list, ylim=None):\n",
    "* def plotPaxByDate(df_list, labels, title, ymonth=8000, ylim=None):\n",
    "* def plotOD(ax, OD, df_od_group):\n",
    "* def plotDelaysAllFlights():\n",
    "* def nonFeederDepartures(df, feeders=False):\n",
    "* def scatter_DepArrDelays(df):\n",
    "* def scatter_ArrMinusDepDelay(df):\n",
    "* def ArrDepFltTimeCorr(df):\n",
    "* def flights_nonfeeders(df):\n",
    "* def save_all_feeders(potential_feeders):\n",
    "* def plotFeederHist(dflist, binrange=(0.5,100.5), labels=('', ''), cols=('nb_feeders', 'nb_feeders'), \\\n",
    "*     - title: plot title with a default: \"Number of Feeders Histogram\".\n",
    "* def savePivotTables(df):\n",
    "* def series_to_date_components(series, prefix=\"\", suffix=\"\"):\n",
    "* def series_to_time_components(series, prefix=\"\", suffix=\"\"):\n",
    "* def Zulu2PTY(datetime):\n",
    "* def per_interval(df1, kind='SCHED', bin_size=60, min_delay=None, max_delay=None, title=\"\", ax=None, date=None, daterange=None, desired_timezone='Zulu'):\n",
    "* def delayByOD(df, delay_range=(None, None), filenm='od_arrival_delays_med,mean,std.csv'):\n",
    "* def time_2_AM_PM(df, time_col):\n",
    "* def createIdPair(df1, dest_col, cols, suffixes = ['_f','_nf']):\n",
    "* def createUniqueIdPairList(df, idcol, cols, suffixes=['_f','_nf']):\n",
    "* def createId(df1, dest_col, cols):\n",
    "* def createUniqueIdList(df, idcol, cols):\n",
    "* def createIdentifiers(fsu, dff, idcol, fsu_cols, dff_cols):\n",
    "* def noRecordsFound():\n",
    "* def fsuRecords(df_ids, ids, id_col='id', cols=None):\n",
    "* def bookingRecords(df_ids, ids, cols=None):\n",
    "* def aggreggateBookings(dff_ids, dff_id_cols, save_data=False, output=\"bookings\", out_suffix=(\"_f\",\"_nf\")):\n",
    "* def createMergeIdList(fsu_id, dff_id):\n",
    "* def mergeFSUBookings(fsu_ids, dff_ids, ids, cols_fsu=None, cols_dff=None):\n",
    "* def findFeederIds(df, nonfeeder_id):\n",
    "* def findNonFeederIds(df, feeder_id):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "smart-bryan",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.9.2"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}