Exploration/data_overview.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Exploratory Data Analysis**\n",
    "---\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **1) Overview**\n",
    "---\n",
    "*Dealing with missing values*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **flights** ([sample](../Scripts/sampling.sql)) & **flights_test**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Overview:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### **flights** sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "M:\\Anaconda\\envs\\LHL\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3444: DtypeWarning: Columns (25) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    },
    {
     "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>fl_date</th>\n",
       "      <th>mkt_unique_carrier</th>\n",
       "      <th>branded_code_share</th>\n",
       "      <th>mkt_carrier</th>\n",
       "      <th>mkt_carrier_fl_num</th>\n",
       "      <th>op_unique_carrier</th>\n",
       "      <th>tail_num</th>\n",
       "      <th>op_carrier_fl_num</th>\n",
       "      <th>origin_airport_id</th>\n",
       "      <th>origin</th>\n",
       "      <th>...</th>\n",
       "      <th>distance</th>\n",
       "      <th>carrier_delay</th>\n",
       "      <th>weather_delay</th>\n",
       "      <th>nas_delay</th>\n",
       "      <th>security_delay</th>\n",
       "      <th>late_aircraft_delay</th>\n",
       "      <th>first_dep_time</th>\n",
       "      <th>total_add_gtime</th>\n",
       "      <th>longest_add_gtime</th>\n",
       "      <th>no_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-05-19</td>\n",
       "      <td>UA</td>\n",
       "      <td>UA_CODESHARE</td>\n",
       "      <td>UA</td>\n",
       "      <td>4264</td>\n",
       "      <td>EV</td>\n",
       "      <td>N48901</td>\n",
       "      <td>4264</td>\n",
       "      <td>12266</td>\n",
       "      <td>IAH</td>\n",
       "      <td>...</td>\n",
       "      <td>127</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-05-19</td>\n",
       "      <td>UA</td>\n",
       "      <td>UA_CODESHARE</td>\n",
       "      <td>UA</td>\n",
       "      <td>4266</td>\n",
       "      <td>EV</td>\n",
       "      <td>N12540</td>\n",
       "      <td>4266</td>\n",
       "      <td>13244</td>\n",
       "      <td>MEM</td>\n",
       "      <td>...</td>\n",
       "      <td>468</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-05-19</td>\n",
       "      <td>UA</td>\n",
       "      <td>UA_CODESHARE</td>\n",
       "      <td>UA</td>\n",
       "      <td>4272</td>\n",
       "      <td>EV</td>\n",
       "      <td>N11164</td>\n",
       "      <td>4272</td>\n",
       "      <td>12266</td>\n",
       "      <td>IAH</td>\n",
       "      <td>...</td>\n",
       "      <td>1091</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-05-19</td>\n",
       "      <td>UA</td>\n",
       "      <td>UA_CODESHARE</td>\n",
       "      <td>UA</td>\n",
       "      <td>4281</td>\n",
       "      <td>EV</td>\n",
       "      <td>N13995</td>\n",
       "      <td>4281</td>\n",
       "      <td>11042</td>\n",
       "      <td>CLE</td>\n",
       "      <td>...</td>\n",
       "      <td>310</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-05-19</td>\n",
       "      <td>UA</td>\n",
       "      <td>UA_CODESHARE</td>\n",
       "      <td>UA</td>\n",
       "      <td>4286</td>\n",
       "      <td>EV</td>\n",
       "      <td>N13903</td>\n",
       "      <td>4286</td>\n",
       "      <td>13061</td>\n",
       "      <td>LRD</td>\n",
       "      <td>...</td>\n",
       "      <td>301</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      fl_date mkt_unique_carrier branded_code_share mkt_carrier  \\\n",
       "0  2019-05-19                 UA       UA_CODESHARE          UA   \n",
       "1  2019-05-19                 UA       UA_CODESHARE          UA   \n",
       "2  2019-05-19                 UA       UA_CODESHARE          UA   \n",
       "3  2019-05-19                 UA       UA_CODESHARE          UA   \n",
       "4  2019-05-19                 UA       UA_CODESHARE          UA   \n",
       "\n",
       "   mkt_carrier_fl_num op_unique_carrier tail_num  op_carrier_fl_num  \\\n",
       "0                4264                EV   N48901               4264   \n",
       "1                4266                EV   N12540               4266   \n",
       "2                4272                EV   N11164               4272   \n",
       "3                4281                EV   N13995               4281   \n",
       "4                4286                EV   N13903               4286   \n",
       "\n",
       "   origin_airport_id origin  ... distance  carrier_delay weather_delay  \\\n",
       "0              12266    IAH  ...      127            NaN           NaN   \n",
       "1              13244    MEM  ...      468            NaN           NaN   \n",
       "2              12266    IAH  ...     1091            NaN           NaN   \n",
       "3              11042    CLE  ...      310            NaN           NaN   \n",
       "4              13061    LRD  ...      301            NaN           NaN   \n",
       "\n",
       "  nas_delay  security_delay  late_aircraft_delay  first_dep_time  \\\n",
       "0       NaN             NaN                  NaN             NaN   \n",
       "1       NaN             NaN                  NaN             NaN   \n",
       "2       NaN             NaN                  NaN             NaN   \n",
       "3       NaN             NaN                  NaN             NaN   \n",
       "4       NaN             NaN                  NaN             NaN   \n",
       "\n",
       "   total_add_gtime  longest_add_gtime  no_name  \n",
       "0              NaN                NaN      NaN  \n",
       "1              NaN                NaN      NaN  \n",
       "2              NaN                NaN      NaN  \n",
       "3              NaN                NaN      NaN  \n",
       "4              NaN                NaN      NaN  \n",
       "\n",
       "[5 rows x 42 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights = pd.read_csv('../Data/files/flights_sample.csv')\n",
    "flights.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2387955 entries, 0 to 2387954\n",
      "Data columns (total 42 columns):\n",
      " #   Column               Dtype  \n",
      "---  ------               -----  \n",
      " 0   fl_date              object \n",
      " 1   mkt_unique_carrier   object \n",
      " 2   branded_code_share   object \n",
      " 3   mkt_carrier          object \n",
      " 4   mkt_carrier_fl_num   int64  \n",
      " 5   op_unique_carrier    object \n",
      " 6   tail_num             object \n",
      " 7   op_carrier_fl_num    int64  \n",
      " 8   origin_airport_id    int64  \n",
      " 9   origin               object \n",
      " 10  origin_city_name     object \n",
      " 11  dest_airport_id      int64  \n",
      " 12  dest                 object \n",
      " 13  dest_city_name       object \n",
      " 14  crs_dep_time         int64  \n",
      " 15  dep_time             float64\n",
      " 16  dep_delay            float64\n",
      " 17  taxi_out             float64\n",
      " 18  wheels_off           float64\n",
      " 19  wheels_on            float64\n",
      " 20  taxi_in              float64\n",
      " 21  crs_arr_time         int64  \n",
      " 22  arr_time             float64\n",
      " 23  arr_delay            float64\n",
      " 24  cancelled            int64  \n",
      " 25  cancellation_code    object \n",
      " 26  diverted             int64  \n",
      " 27  dup                  object \n",
      " 28  crs_elapsed_time     float64\n",
      " 29  actual_elapsed_time  float64\n",
      " 30  air_time             float64\n",
      " 31  flights              int64  \n",
      " 32  distance             int64  \n",
      " 33  carrier_delay        float64\n",
      " 34  weather_delay        float64\n",
      " 35  nas_delay            float64\n",
      " 36  security_delay       float64\n",
      " 37  late_aircraft_delay  float64\n",
      " 38  first_dep_time       float64\n",
      " 39  total_add_gtime      float64\n",
      " 40  longest_add_gtime    float64\n",
      " 41  no_name              float64\n",
      "dtypes: float64(20), int64(10), object(12)\n",
      "memory usage: 765.2+ MB\n"
     ]
    }
   ],
   "source": [
    "flights.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### **flights_test**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>fl_date</th>\n",
       "      <th>mkt_unique_carrier</th>\n",
       "      <th>branded_code_share</th>\n",
       "      <th>mkt_carrier</th>\n",
       "      <th>mkt_carrier_fl_num</th>\n",
       "      <th>op_unique_carrier</th>\n",
       "      <th>tail_num</th>\n",
       "      <th>op_carrier_fl_num</th>\n",
       "      <th>origin_airport_id</th>\n",
       "      <th>origin</th>\n",
       "      <th>origin_city_name</th>\n",
       "      <th>dest_airport_id</th>\n",
       "      <th>dest</th>\n",
       "      <th>dest_city_name</th>\n",
       "      <th>crs_dep_time</th>\n",
       "      <th>crs_arr_time</th>\n",
       "      <th>dup</th>\n",
       "      <th>crs_elapsed_time</th>\n",
       "      <th>flights</th>\n",
       "      <th>distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-01 00:00:00</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN</td>\n",
       "      <td>5888</td>\n",
       "      <td>WN</td>\n",
       "      <td>N951WN</td>\n",
       "      <td>5888</td>\n",
       "      <td>13891</td>\n",
       "      <td>ONT</td>\n",
       "      <td>Ontario, CA</td>\n",
       "      <td>14771</td>\n",
       "      <td>SFO</td>\n",
       "      <td>San Francisco, CA</td>\n",
       "      <td>1810</td>\n",
       "      <td>1945</td>\n",
       "      <td>N</td>\n",
       "      <td>95</td>\n",
       "      <td>1</td>\n",
       "      <td>363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-01 00:00:00</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN</td>\n",
       "      <td>6276</td>\n",
       "      <td>WN</td>\n",
       "      <td>N467WN</td>\n",
       "      <td>6276</td>\n",
       "      <td>13891</td>\n",
       "      <td>ONT</td>\n",
       "      <td>Ontario, CA</td>\n",
       "      <td>14771</td>\n",
       "      <td>SFO</td>\n",
       "      <td>San Francisco, CA</td>\n",
       "      <td>1150</td>\n",
       "      <td>1320</td>\n",
       "      <td>N</td>\n",
       "      <td>90</td>\n",
       "      <td>1</td>\n",
       "      <td>363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-01 00:00:00</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN</td>\n",
       "      <td>4598</td>\n",
       "      <td>WN</td>\n",
       "      <td>N7885A</td>\n",
       "      <td>4598</td>\n",
       "      <td>13891</td>\n",
       "      <td>ONT</td>\n",
       "      <td>Ontario, CA</td>\n",
       "      <td>14831</td>\n",
       "      <td>SJC</td>\n",
       "      <td>San Jose, CA</td>\n",
       "      <td>2020</td>\n",
       "      <td>2130</td>\n",
       "      <td>N</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-01 00:00:00</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN</td>\n",
       "      <td>4761</td>\n",
       "      <td>WN</td>\n",
       "      <td>N551WN</td>\n",
       "      <td>4761</td>\n",
       "      <td>13891</td>\n",
       "      <td>ONT</td>\n",
       "      <td>Ontario, CA</td>\n",
       "      <td>14831</td>\n",
       "      <td>SJC</td>\n",
       "      <td>San Jose, CA</td>\n",
       "      <td>1340</td>\n",
       "      <td>1455</td>\n",
       "      <td>N</td>\n",
       "      <td>75</td>\n",
       "      <td>1</td>\n",
       "      <td>333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-01 00:00:00</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN</td>\n",
       "      <td>WN</td>\n",
       "      <td>5162</td>\n",
       "      <td>WN</td>\n",
       "      <td>N968WN</td>\n",
       "      <td>5162</td>\n",
       "      <td>13891</td>\n",
       "      <td>ONT</td>\n",
       "      <td>Ontario, CA</td>\n",
       "      <td>14831</td>\n",
       "      <td>SJC</td>\n",
       "      <td>San Jose, CA</td>\n",
       "      <td>915</td>\n",
       "      <td>1035</td>\n",
       "      <td>N</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               fl_date mkt_unique_carrier branded_code_share mkt_carrier  \\\n",
       "0  2020-01-01 00:00:00                 WN                 WN          WN   \n",
       "1  2020-01-01 00:00:00                 WN                 WN          WN   \n",
       "2  2020-01-01 00:00:00                 WN                 WN          WN   \n",
       "3  2020-01-01 00:00:00                 WN                 WN          WN   \n",
       "4  2020-01-01 00:00:00                 WN                 WN          WN   \n",
       "\n",
       "   mkt_carrier_fl_num op_unique_carrier tail_num  op_carrier_fl_num  \\\n",
       "0                5888                WN   N951WN               5888   \n",
       "1                6276                WN   N467WN               6276   \n",
       "2                4598                WN   N7885A               4598   \n",
       "3                4761                WN   N551WN               4761   \n",
       "4                5162                WN   N968WN               5162   \n",
       "\n",
       "   origin_airport_id origin origin_city_name  dest_airport_id dest  \\\n",
       "0              13891    ONT      Ontario, CA            14771  SFO   \n",
       "1              13891    ONT      Ontario, CA            14771  SFO   \n",
       "2              13891    ONT      Ontario, CA            14831  SJC   \n",
       "3              13891    ONT      Ontario, CA            14831  SJC   \n",
       "4              13891    ONT      Ontario, CA            14831  SJC   \n",
       "\n",
       "      dest_city_name  crs_dep_time  crs_arr_time dup  crs_elapsed_time  \\\n",
       "0  San Francisco, CA          1810          1945   N                95   \n",
       "1  San Francisco, CA          1150          1320   N                90   \n",
       "2       San Jose, CA          2020          2130   N                70   \n",
       "3       San Jose, CA          1340          1455   N                75   \n",
       "4       San Jose, CA           915          1035   N                80   \n",
       "\n",
       "   flights  distance  \n",
       "0        1       363  \n",
       "1        1       363  \n",
       "2        1       333  \n",
       "3        1       333  \n",
       "4        1       333  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights_test = pd.read_csv('../Data/files/flights_test.csv')\n",
    "flights_test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 660556 entries, 0 to 660555\n",
      "Data columns (total 20 columns):\n",
      " #   Column              Non-Null Count   Dtype \n",
      "---  ------              --------------   ----- \n",
      " 0   fl_date             660556 non-null  object\n",
      " 1   mkt_unique_carrier  660556 non-null  object\n",
      " 2   branded_code_share  660556 non-null  object\n",
      " 3   mkt_carrier         660556 non-null  object\n",
      " 4   mkt_carrier_fl_num  660556 non-null  int64 \n",
      " 5   op_unique_carrier   660556 non-null  object\n",
      " 6   tail_num            659057 non-null  object\n",
      " 7   op_carrier_fl_num   660556 non-null  int64 \n",
      " 8   origin_airport_id   660556 non-null  int64 \n",
      " 9   origin              660556 non-null  object\n",
      " 10  origin_city_name    660556 non-null  object\n",
      " 11  dest_airport_id     660556 non-null  int64 \n",
      " 12  dest                660556 non-null  object\n",
      " 13  dest_city_name      660556 non-null  object\n",
      " 14  crs_dep_time        660556 non-null  int64 \n",
      " 15  crs_arr_time        660556 non-null  int64 \n",
      " 16  dup                 660556 non-null  object\n",
      " 17  crs_elapsed_time    660556 non-null  int64 \n",
      " 18  flights             660556 non-null  int64 \n",
      " 19  distance            660556 non-null  int64 \n",
      "dtypes: int64(9), object(11)\n",
      "memory usage: 100.8+ MB\n"
     ]
    }
   ],
   "source": [
    "flights_test.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **flights** sample vs. **flights_test** feature space"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To establish the similarities and difference between the feature spaces of **flights** and **flights_test** rather quickly, we can leverage sets to find the intersection and symmetric difference of their features."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights_initial_features = set(flights.columns)\n",
    "flights_test_initial_features = set(flights_test.columns)\n",
    "\n",
    "common_initial_features = flights_initial_features.intersection(flights_test_initial_features)\n",
    "exclusive_initial_features = flights_initial_features.symmetric_difference(flights_test_initial_features)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us check if the flights_test feature space is entirely a subset of flights."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n",
      "20\n"
     ]
    }
   ],
   "source": [
    "print(\n",
    "    len(common_initial_features) == len(flights_test_initial_features), \n",
    "    len(flights_test_initial_features),\n",
    "    sep=\"\\n\",\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This confirms that the feature space of flights_test is a subset of flights (which is expected given its [description](../Data/descriptions/flights_test_description.md)). Now let us check which additional features we have in the **flights** table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Features exclusive to flights:\n",
      "\n",
      "late_aircraft_delay\n",
      "taxi_out\n",
      "carrier_delay\n",
      "wheels_off\n",
      "wheels_on\n",
      "weather_delay\n",
      "no_name\n",
      "dep_delay\n",
      "cancellation_code\n",
      "air_time\n",
      "actual_elapsed_time\n",
      "diverted\n",
      "first_dep_time\n",
      "dep_time\n",
      "cancelled\n",
      "arr_delay\n",
      "longest_add_gtime\n",
      "security_delay\n",
      "nas_delay\n",
      "arr_time\n",
      "taxi_in\n",
      "total_add_gtime\n",
      "\n",
      "Number of features exclusive to flights: 22\n"
     ]
    }
   ],
   "source": [
    "print('Features exclusive to flights:\\n')\n",
    "for feature in exclusive_initial_features:\n",
    "    print(feature)\n",
    "\n",
    "print(f'\\nNumber of features exclusive to flights: {len(exclusive_initial_features)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All of these features exclusive to **flights** are those that we can only know once the plane has already arrived (excluding `no_name`). Therefore, any useful features as part of this set would need to be predicted in order to make an inference about future flight delays."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Missing data investigation:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Preview missing data percentages for features with null values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tail_num                 0.304947\n",
       "dep_time                 1.616865\n",
       "dep_delay                1.647979\n",
       "taxi_out                 1.707151\n",
       "wheels_off               1.707151\n",
       "wheels_on                1.754681\n",
       "taxi_in                  1.754681\n",
       "arr_time                 1.718165\n",
       "arr_delay                1.944132\n",
       "cancellation_code       98.317305\n",
       "crs_elapsed_time         0.000168\n",
       "actual_elapsed_time      1.927172\n",
       "air_time                 1.963605\n",
       "carrier_delay           81.124770\n",
       "weather_delay           81.124770\n",
       "nas_delay               81.124770\n",
       "security_delay          81.124770\n",
       "late_aircraft_delay     81.124770\n",
       "first_dep_time          99.313597\n",
       "total_add_gtime         99.313722\n",
       "longest_add_gtime       99.313681\n",
       "no_name                100.000000\n",
       "dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights_missing_values = flights.isna().sum() > 0\n",
    "flights_missing_value_percentage = flights.isna().sum() / flights.shape[0] * 100\n",
    "\n",
    "flights_missing_value_percentage[flights_missing_values]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tail_num    0.062773\n",
       "dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights_test_missing_values = flights_test.isna().sum() > 0\n",
    "flights_test_missing_value_percentage = flights_test.isna().sum() / flights.shape[0] * 100\n",
    "\n",
    "flights_test_missing_value_percentage[flights_test_missing_values]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Empty rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will start by ensuring that empty rows are discarded as they offer no information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights = flights.dropna(how='all')\n",
    "flights_test = flights_test.dropna(how='all')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### `no_name`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "no_name is completely blank, so we can drop it with absolutely no information loss."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights = flights.drop('no_name', axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### `cancellation_code`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([nan, 'A', 'C', 'B', 'D'], dtype=object)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.cancellation_code.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It may be worthwhile to investigate the `cancellation_code` feature further, but the missing proportion here should correspond with the `cancelled` feature assuming that codes are only present for cancelled flights (as suggested in the flights description). In other words, most data in this column is ***structually missing*** (i.e. missing because most flights were not cancelled)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    98.317305\n",
       "1     1.682695\n",
       "Name: cancelled, dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights['cancelled'].value_counts()/flights.shape[0] * 100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The percentages support the structurally missing data hypothesis, but let us confirm by inspecting the relevant subset more rigorously."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['A', 'C', 'B', 'D'], dtype=object)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights[flights.cancelled > 0]['cancellation_code'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([nan], dtype=object)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights[flights.cancelled == 0]['cancellation_code'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have the most support that we can obtain from the data for the structurally missing hypothesis with respect to `cancellation_code`! It remains possible that human errors (e.g. data entry) are present but obscured in this table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "float"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(flights['cancellation_code'].unique()[0]) # nan is of type float in this case."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['none', 'A', 'C', 'B', 'D'], dtype=object)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Replace the missing value for now so that we do not consider the column further.\n",
    "flights['cancellation_code'] = flights['cancellation_code'].apply(lambda code: 'none' if (isinstance(code, float)) else code)\n",
    "flights['cancellation_code'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### '`_delay`' columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All of the delay columns have identical percentages of missing data in **flights** (none are present in **flights_test**). Here we will test the hypothesis that the missing rows for all of these columns are the same. Resultantly, we predict that missing data will be consistent between rows for these features. This may suggest that these data are also ***structurally missing***.  \n",
    "\n",
    "This may mean that any time a flight is delayed, all delay columns are assigned a baseline value of 0 (as opposed to NaN) and this baseline is incremented appropriately according to the class of delay."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>carrier_delay</th>\n",
       "      <th>weather_delay</th>\n",
       "      <th>nas_delay</th>\n",
       "      <th>security_delay</th>\n",
       "      <th>late_aircraft_delay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   carrier_delay  weather_delay  nas_delay  security_delay  \\\n",
       "0            NaN            NaN        NaN             NaN   \n",
       "1            NaN            NaN        NaN             NaN   \n",
       "2            NaN            NaN        NaN             NaN   \n",
       "3            NaN            NaN        NaN             NaN   \n",
       "4            NaN            NaN        NaN             NaN   \n",
       "\n",
       "   late_aircraft_delay  \n",
       "0                  NaN  \n",
       "1                  NaN  \n",
       "2                  NaN  \n",
       "3                  NaN  \n",
       "4                  NaN  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "delay_columns = ['carrier_delay', 'weather_delay', 'nas_delay', 'security_delay', 'late_aircraft_delay']\n",
    "delays_subframe = flights[delay_columns]\n",
    "delays_subframe.head()\n",
    "# These 5 rows concurrently displaying NaN provide very weak support for the above hypothesis. Let us investigate more closely."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us check the rows that remain for each delay feature when those with missing values are discarded and ensure that it matches the value predicted given that our hypothesis is correct. Since we know that all delay columns have the same missing value percentage, we can use one as a reference value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Determine the actual percentages of non-missing data for the delay rows and the values we expect to see given our hypothesis.\n",
    "delay_value_percentages = [(delays_subframe[column].dropna().count() / flights.shape[0] * 100)\n",
    "                           for column in delays_subframe]\n",
    "predicted_value = (100 - flights_missing_value_percentage.late_aircraft_delay)\n",
    "\n",
    "np.allclose(delay_value_percentages, predicted_value) # used to prevent rounding errors from causing problems with strict equality"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It does appear that the missing values correspond with no delay, and are thus structurally missing. It should be noted that the proportion of delayed flights in this sample lines up fairly well with what is observed overall in the publicly available data from the [US Department of Transportation](https://www.transtats.bts.gov/OT_Delay/OT_DelayCause1.asp?20=E). On this basis, we can impute these missing values as 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights[delay_columns] = delays_subframe.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tail_num                0.304947\n",
       "dep_time                1.616865\n",
       "dep_delay               1.647979\n",
       "taxi_out                1.707151\n",
       "wheels_off              1.707151\n",
       "wheels_on               1.754681\n",
       "taxi_in                 1.754681\n",
       "arr_time                1.718165\n",
       "arr_delay               1.944132\n",
       "crs_elapsed_time        0.000168\n",
       "actual_elapsed_time     1.927172\n",
       "air_time                1.963605\n",
       "first_dep_time         99.313597\n",
       "total_add_gtime        99.313722\n",
       "longest_add_gtime      99.313681\n",
       "dtype: float64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights_missing_values = flights.isna().sum() > 0\n",
    "flights_missing_value_percentage = flights.isna().sum() / flights.shape[0] * 100\n",
    "\n",
    "flights_missing_value_percentage[flights_missing_values]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### `dep_time` and `_gtime` columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These columns are interesting because not only is the majority of data missing in similar percentages in all 3 of these columns, but the values are not precisely the same. Here we will investigate points of similarity and divergence between observations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "departure_and_gate_time_columns = ['first_dep_time', 'total_add_gtime', 'longest_add_gtime']\n",
    "departure_and_gate_times = flights[departure_and_gate_time_columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>813489</th>\n",
       "      <th>2020945</th>\n",
       "      <th>2027091</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>fl_date</th>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>2018-02-26</td>\n",
       "      <td>2018-02-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mkt_unique_carrier</th>\n",
       "      <td>B6</td>\n",
       "      <td>AS</td>\n",
       "      <td>DL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>branded_code_share</th>\n",
       "      <td>B6</td>\n",
       "      <td>AS_CODESHARE</td>\n",
       "      <td>DL_CODESHARE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mkt_carrier</th>\n",
       "      <td>B6</td>\n",
       "      <td>AS</td>\n",
       "      <td>DL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mkt_carrier_fl_num</th>\n",
       "      <td>631</td>\n",
       "      <td>3296</td>\n",
       "      <td>5909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>op_unique_carrier</th>\n",
       "      <td>B6</td>\n",
       "      <td>KS</td>\n",
       "      <td>YX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tail_num</th>\n",
       "      <td>N339JB</td>\n",
       "      <td>N682PA</td>\n",
       "      <td>N860RW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>op_carrier_fl_num</th>\n",
       "      <td>631</td>\n",
       "      <td>3296</td>\n",
       "      <td>5909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>origin_airport_id</th>\n",
       "      <td>10721</td>\n",
       "      <td>10299</td>\n",
       "      <td>12953</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>origin</th>\n",
       "      <td>BOS</td>\n",
       "      <td>ANC</td>\n",
       "      <td>LGA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>origin_city_name</th>\n",
       "      <td>Boston, MA</td>\n",
       "      <td>Anchorage, AK</td>\n",
       "      <td>New York, NY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dest_airport_id</th>\n",
       "      <td>12953</td>\n",
       "      <td>11445</td>\n",
       "      <td>11298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dest</th>\n",
       "      <td>LGA</td>\n",
       "      <td>DUT</td>\n",
       "      <td>DFW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dest_city_name</th>\n",
       "      <td>New York, NY</td>\n",
       "      <td>Unalaska, AK</td>\n",
       "      <td>Dallas/Fort Worth, TX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>crs_dep_time</th>\n",
       "      <td>703</td>\n",
       "      <td>1515</td>\n",
       "      <td>1755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dep_time</th>\n",
       "      <td>923.0</td>\n",
       "      <td>1652.0</td>\n",
       "      <td>1750.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dep_delay</th>\n",
       "      <td>140.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>-5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>taxi_out</th>\n",
       "      <td>32.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wheels_off</th>\n",
       "      <td>955.0</td>\n",
       "      <td>1716.0</td>\n",
       "      <td>1808.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wheels_on</th>\n",
       "      <td>1044.0</td>\n",
       "      <td>1945.0</td>\n",
       "      <td>33.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>taxi_in</th>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>crs_arr_time</th>\n",
       "      <td>828</td>\n",
       "      <td>1730</td>\n",
       "      <td>2121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>arr_time</th>\n",
       "      <td>1053.0</td>\n",
       "      <td>1947.0</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>arr_delay</th>\n",
       "      <td>145.0</td>\n",
       "      <td>137.0</td>\n",
       "      <td>201.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cancelled</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cancellation_code</th>\n",
       "      <td>none</td>\n",
       "      <td>none</td>\n",
       "      <td>none</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>diverted</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dup</th>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>crs_elapsed_time</th>\n",
       "      <td>85.0</td>\n",
       "      <td>135.0</td>\n",
       "      <td>266.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>actual_elapsed_time</th>\n",
       "      <td>90.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>472.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>air_time</th>\n",
       "      <td>49.0</td>\n",
       "      <td>149.0</td>\n",
       "      <td>445.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>flights</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>distance</th>\n",
       "      <td>184</td>\n",
       "      <td>792</td>\n",
       "      <td>1389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>carrier_delay</th>\n",
       "      <td>140.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>weather_delay</th>\n",
       "      <td>0.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>nas_delay</th>\n",
       "      <td>5.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>201.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>security_delay</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>late_aircraft_delay</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_dep_time</th>\n",
       "      <td>653.0</td>\n",
       "      <td>1507.0</td>\n",
       "      <td>2137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>total_add_gtime</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>longest_add_gtime</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          813489         2020945                2027091\n",
       "fl_date                2019-06-20     2018-02-26             2018-02-28\n",
       "mkt_unique_carrier             B6             AS                     DL\n",
       "branded_code_share             B6   AS_CODESHARE           DL_CODESHARE\n",
       "mkt_carrier                    B6             AS                     DL\n",
       "mkt_carrier_fl_num            631           3296                   5909\n",
       "op_unique_carrier              B6             KS                     YX\n",
       "tail_num                   N339JB         N682PA                 N860RW\n",
       "op_carrier_fl_num             631           3296                   5909\n",
       "origin_airport_id           10721          10299                  12953\n",
       "origin                        BOS            ANC                    LGA\n",
       "origin_city_name       Boston, MA  Anchorage, AK           New York, NY\n",
       "dest_airport_id             12953          11445                  11298\n",
       "dest                          LGA            DUT                    DFW\n",
       "dest_city_name       New York, NY   Unalaska, AK  Dallas/Fort Worth, TX\n",
       "crs_dep_time                  703           1515                   1755\n",
       "dep_time                    923.0         1652.0                 1750.0\n",
       "dep_delay                   140.0           97.0                   -5.0\n",
       "taxi_out                     32.0           24.0                   18.0\n",
       "wheels_off                  955.0         1716.0                 1808.0\n",
       "wheels_on                  1044.0         1945.0                   33.0\n",
       "taxi_in                       9.0            2.0                    9.0\n",
       "crs_arr_time                  828           1730                   2121\n",
       "arr_time                   1053.0         1947.0                   42.0\n",
       "arr_delay                   145.0          137.0                  201.0\n",
       "cancelled                       0              0                      0\n",
       "cancellation_code            none           none                   none\n",
       "diverted                        0              0                      0\n",
       "dup                             N              N                      N\n",
       "crs_elapsed_time             85.0          135.0                  266.0\n",
       "actual_elapsed_time          90.0          175.0                  472.0\n",
       "air_time                     49.0          149.0                  445.0\n",
       "flights                         1              1                      1\n",
       "distance                      184            792                   1389\n",
       "carrier_delay               140.0            0.0                    0.0\n",
       "weather_delay                 0.0           97.0                    0.0\n",
       "nas_delay                     5.0           40.0                  201.0\n",
       "security_delay                0.0            0.0                    0.0\n",
       "late_aircraft_delay           0.0            0.0                    0.0\n",
       "first_dep_time              653.0         1507.0                 2137.0\n",
       "total_add_gtime               NaN            NaN                    NaN\n",
       "longest_add_gtime             NaN            0.0                    NaN"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "some_missing = departure_and_gate_times.dropna(how='all')\n",
    "no_missing = departure_and_gate_times.dropna()\n",
    "\n",
    "missing_rows = set(some_missing.index).symmetric_difference(set(no_missing.index))\n",
    "flights.loc[missing_rows].T # display the transpose for the 3 indices."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "813489     145.0\n",
       "2020945    137.0\n",
       "2027091    201.0\n",
       "Name: arr_delay, dtype: float64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.loc[missing_rows]['arr_delay']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are only 3 rows here with mixed missing values, and all have a departure time. Since none of these flights were cancelled, it is sensible to conclude that either none of them returned to the gate or these may be data entry errors (this is likely for index 2020945, which apparently never left the gate according to the 0 for `longest_add_gtime`)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Overall, because it is not clear what these features are adding and they are missing entirely for the overwhelming majority of the data, we will simply discard them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights = flights.drop(columns=departure_and_gate_time_columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Remainder of missing values:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Given the surplus of observations we have for the **flights** table, and the low missing data percentages for all other observations (<2%), it is worth checking how much data would be lost if all rows missing data were discarded."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total observations retained / 2387955: 2339962\n",
      "Percentage retained: 97.99\n"
     ]
    }
   ],
   "source": [
    "total_observations_retained = flights.dropna().shape[0]\n",
    "percent_data_retained = total_observations_retained / flights.shape[0] * 100\n",
    "\n",
    "print(f'Total observations retained / {flights.shape[0]}: {total_observations_retained}')\n",
    "print(f'Percentage retained: {percent_data_retained:.2f}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Given the minimal percentage loss in tandem with the massive sample size retained by dropping rows with missing values, the tradeoff it worth it in this instance."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights = flights.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By the above logic, the tradeoff should be similarly valuable for **flights_test**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total observations retained / 660556: 659057\n",
      "Percentage retained: 99.77\n"
     ]
    }
   ],
   "source": [
    "total_observations_retained = flights_test.dropna().shape[0]\n",
    "percent_data_retained = total_observations_retained / flights_test.shape[0] * 100\n",
    "\n",
    "print(f'Total observations retained / {flights_test.shape[0]}: {total_observations_retained}')\n",
    "print(f'Percentage retained: {percent_data_retained:.2f}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights_test = flights_test.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us exclude cancelled flights from consideration, as they are not expected to ever rach their intended destination."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights = flights[flights.cancelled == 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **passengers**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Overview:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>departures_scheduled</th>\n",
       "      <th>departures_performed</th>\n",
       "      <th>payload</th>\n",
       "      <th>seats</th>\n",
       "      <th>passengers</th>\n",
       "      <th>freight</th>\n",
       "      <th>mail</th>\n",
       "      <th>distance</th>\n",
       "      <th>ramp_to_ramp</th>\n",
       "      <th>air_time</th>\n",
       "      <th>...</th>\n",
       "      <th>dest_country</th>\n",
       "      <th>dest_country_name</th>\n",
       "      <th>aircraft_group</th>\n",
       "      <th>aircraft_type</th>\n",
       "      <th>aircraft_config</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>distance_group</th>\n",
       "      <th>class</th>\n",
       "      <th>data_source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>75800</td>\n",
       "      <td>300</td>\n",
       "      <td>244</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1020</td>\n",
       "      <td>310</td>\n",
       "      <td>259</td>\n",
       "      <td>...</td>\n",
       "      <td>US</td>\n",
       "      <td>United States</td>\n",
       "      <td>6</td>\n",
       "      <td>694</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>F</td>\n",
       "      <td>DU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>72400</td>\n",
       "      <td>256</td>\n",
       "      <td>219</td>\n",
       "      <td>458</td>\n",
       "      <td>950</td>\n",
       "      <td>255</td>\n",
       "      <td>140</td>\n",
       "      <td>90</td>\n",
       "      <td>...</td>\n",
       "      <td>US</td>\n",
       "      <td>United States</td>\n",
       "      <td>6</td>\n",
       "      <td>698</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>DU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>76000</td>\n",
       "      <td>300</td>\n",
       "      <td>240</td>\n",
       "      <td>75</td>\n",
       "      <td>335</td>\n",
       "      <td>601</td>\n",
       "      <td>208</td>\n",
       "      <td>174</td>\n",
       "      <td>...</td>\n",
       "      <td>US</td>\n",
       "      <td>United States</td>\n",
       "      <td>6</td>\n",
       "      <td>694</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>F</td>\n",
       "      <td>DU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>104600</td>\n",
       "      <td>376</td>\n",
       "      <td>187</td>\n",
       "      <td>0</td>\n",
       "      <td>58</td>\n",
       "      <td>304</td>\n",
       "      <td>156</td>\n",
       "      <td>103</td>\n",
       "      <td>...</td>\n",
       "      <td>US</td>\n",
       "      <td>United States</td>\n",
       "      <td>6</td>\n",
       "      <td>622</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>DU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>72700</td>\n",
       "      <td>256</td>\n",
       "      <td>214</td>\n",
       "      <td>3</td>\n",
       "      <td>553</td>\n",
       "      <td>507</td>\n",
       "      <td>196</td>\n",
       "      <td>156</td>\n",
       "      <td>...</td>\n",
       "      <td>US</td>\n",
       "      <td>United States</td>\n",
       "      <td>6</td>\n",
       "      <td>698</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>F</td>\n",
       "      <td>DU</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 38 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   departures_scheduled  departures_performed  payload  seats  passengers  \\\n",
       "0                     2                     2    75800    300         244   \n",
       "1                     2                     2    72400    256         219   \n",
       "2                     2                     2    76000    300         240   \n",
       "3                     2                     2   104600    376         187   \n",
       "4                     2                     2    72700    256         214   \n",
       "\n",
       "   freight  mail  distance  ramp_to_ramp  air_time  ... dest_country  \\\n",
       "0        0     0      1020           310       259  ...           US   \n",
       "1      458   950       255           140        90  ...           US   \n",
       "2       75   335       601           208       174  ...           US   \n",
       "3        0    58       304           156       103  ...           US   \n",
       "4        3   553       507           196       156  ...           US   \n",
       "\n",
       "   dest_country_name aircraft_group aircraft_type aircraft_config  year  \\\n",
       "0      United States              6           694               1  2017   \n",
       "1      United States              6           698               1  2017   \n",
       "2      United States              6           694               1  2017   \n",
       "3      United States              6           622               1  2017   \n",
       "4      United States              6           698               1  2017   \n",
       "\n",
       "   month  distance_group  class  data_source  \n",
       "0      3               3      F           DU  \n",
       "1      3               1      F           DU  \n",
       "2      3               2      F           DU  \n",
       "3      3               1      F           DU  \n",
       "4      3               2      F           DU  \n",
       "\n",
       "[5 rows x 38 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "passengers = pd.read_csv('../Data/files/passengers.csv')\n",
    "passengers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2350497 entries, 0 to 2350496\n",
      "Data columns (total 38 columns):\n",
      " #   Column                 Dtype \n",
      "---  ------                 ----- \n",
      " 0   departures_scheduled   int64 \n",
      " 1   departures_performed   int64 \n",
      " 2   payload                int64 \n",
      " 3   seats                  int64 \n",
      " 4   passengers             int64 \n",
      " 5   freight                int64 \n",
      " 6   mail                   int64 \n",
      " 7   distance               int64 \n",
      " 8   ramp_to_ramp           int64 \n",
      " 9   air_time               int64 \n",
      " 10  unique_carrier         object\n",
      " 11  airline_id             int64 \n",
      " 12  unique_carrier_name    object\n",
      " 13  region                 object\n",
      " 14  carrier                object\n",
      " 15  carrier_name           object\n",
      " 16  carrier_group          int64 \n",
      " 17  carrier_group_new      int64 \n",
      " 18  origin_airport_id      int64 \n",
      " 19  origin_city_market_id  int64 \n",
      " 20  origin                 object\n",
      " 21  origin_city_name       object\n",
      " 22  origin_country         object\n",
      " 23  origin_country_name    object\n",
      " 24  dest_airport_id        int64 \n",
      " 25  dest_city_market_id    int64 \n",
      " 26  dest                   object\n",
      " 27  dest_city_name         object\n",
      " 28  dest_country           object\n",
      " 29  dest_country_name      object\n",
      " 30  aircraft_group         int64 \n",
      " 31  aircraft_type          int64 \n",
      " 32  aircraft_config        int64 \n",
      " 33  year                   int64 \n",
      " 34  month                  int64 \n",
      " 35  distance_group         int64 \n",
      " 36  class                  object\n",
      " 37  data_source            object\n",
      "dtypes: int64(23), object(15)\n",
      "memory usage: 681.4+ MB\n"
     ]
    }
   ],
   "source": [
    "passengers.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Interestingly, this data comes from a number of different sources. While we do not necessarily require this column for future analysis, my curiosity will not allow me to ignore this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['DU', 'IU', 'IF', 'DF'], dtype=object)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "passengers.data_source.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 4 unique sources. I wonder what their distribution looks like..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DU    80.252772\n",
       "IU    10.931901\n",
       "IF     8.170825\n",
       "DF     0.644502\n",
       "Name: data_source, dtype: float64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "passengers.data_source.value_counts() / passengers.shape[0] * 100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It looks as though the data is heavily skewed towards the `DU` source at more than 80%. `IU` and `IF` are similarly represented, and `DF` is by far the least frequently observed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **passengers** feature space"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Missing data investigation:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Preview missing data percentages for features with null values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "origin_country    0.000128\n",
       "dtype: float64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "passengers_missing_values = passengers.isna().sum() > 0\n",
    "passengers_missing_value_percentage = passengers.isna().sum() / passengers.shape[0] * 100\n",
    "\n",
    "passengers_missing_value_percentage[passengers_missing_values]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total observations retained / 2350497: 2350494\n",
      "Percentage retained: 99.9999\n"
     ]
    }
   ],
   "source": [
    "passengers_total_observations_retained = passengers.dropna().shape[0]\n",
    "passengers_percent_data_retained = passengers_total_observations_retained / passengers.shape[0] * 100\n",
    "\n",
    "print(f'Total observations retained / {passengers.shape[0]}: {passengers_total_observations_retained}')\n",
    "print(f'Percentage retained: {passengers_percent_data_retained:.4f}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On the passengers table, only the `origin_country` feature had missing values and this column is of the str datatype. It is a column of str data type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['US', 'CA', 'GB', 'JP', 'EC', 'DO', 'MX', 'CO', 'BS', 'TC', 'CR',\n",
       "       'AG', 'BB', 'CW', 'JM', 'VE', 'NI', 'SR', 'TT', 'PA', 'KN', 'LC',\n",
       "       'BQ', 'BZ', 'KY', 'HN', 'FR', 'SN', 'CN', 'FM', 'GT', 'SV', 'NL',\n",
       "       'IE', 'MQ', 'GD', 'HT', 'IL', 'BR', 'KR', 'AW', 'ES', 'DE', 'BM',\n",
       "       'SX', 'TW', 'BL', 'VC', 'IT', 'CU', 'CZ', 'DM', 'GP', 'AR', 'BE',\n",
       "       'PT', 'AU', 'GY', 'CH', 'SG', 'HK', 'PE', 'SE', 'DK', 'RU', 'GH',\n",
       "       'PF', 'CL', 'PW', 'VG', 'GR', 'IS', 'PH', 'NZ', 'UY', 'MH', 'AI',\n",
       "       'NG', 'ZA', 'IN', 'BO', 'HU', 'WS', 'NO', 'AZ', 'FI', 'SA', 'AE',\n",
       "       'KI', 'FJ', 'LV', 'PL', 'CK', 'AT', 'PK', 'UA', 'QA', 'JO', 'CV',\n",
       "       'AO', 'LU', 'RO', 'KW', nan, 'GL', 'DZ', 'SL', 'MT', 'RS', 'LR',\n",
       "       'MA', 'GF', 'KH', 'HR', 'EG', 'MO', 'ET', 'LK', 'TR', 'GM', 'PY',\n",
       "       'SI', 'KZ', 'MY', 'PG', 'BD', 'VN', 'TH', 'CF', 'BG', 'BH', 'ID',\n",
       "       'ME', 'EE', 'GE', 'GI', 'CY', 'LT', 'BF', 'TO', 'SK', 'KE', 'UZ',\n",
       "       'GA', 'NC', 'MD', 'VU', 'ML', 'AM', 'MK', 'BJ', 'LB', 'MF', 'TJ',\n",
       "       'NE'], dtype=object)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "passengers.origin_country.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are only 3 rows with null/missing values for the origin_country feature on this table.\n"
     ]
    }
   ],
   "source": [
    "print(f'There are only {passengers.origin_country.isna().sum()} rows with null/missing values for the origin_country feature on this table.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "463912     NaN\n",
       "585528     NaN\n",
       "1020880    NaN\n",
       "Name: origin_country, dtype: object"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "passengers[passengers.origin_country.isna()].origin_country"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The rows with missing values for the `origin_country` feature could be safely dropped because they only accounted for 0.000128 % of the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "passengers = passengers.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], dtype: float64)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "passengers_missing_values = passengers.isna().sum() > 0\n",
    "passengers_missing_value_percentage = passengers.isna().sum() / passengers.shape[0] * 100\n",
    "\n",
    "passengers_missing_value_percentage[passengers_missing_values]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Drop empty rows from **passengers**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "passengers = passengers.dropna(how='all')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **fuel_consumption**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Overview:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>month</th>\n",
       "      <th>airline_id</th>\n",
       "      <th>unique_carrier</th>\n",
       "      <th>carrier</th>\n",
       "      <th>carrier_name</th>\n",
       "      <th>carrier_group_new</th>\n",
       "      <th>sdomt_gallons</th>\n",
       "      <th>satl_gallons</th>\n",
       "      <th>spac_gallons</th>\n",
       "      <th>slat_gallons</th>\n",
       "      <th>...</th>\n",
       "      <th>sdomt_cost</th>\n",
       "      <th>satl_cost</th>\n",
       "      <th>spac_cost</th>\n",
       "      <th>slat_cost</th>\n",
       "      <th>sint_cost</th>\n",
       "      <th>ts_cost</th>\n",
       "      <th>tdomt_cost</th>\n",
       "      <th>tint_cost</th>\n",
       "      <th>total_cost</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0JQ</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>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</td>\n",
       "      <td>5100</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5100</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>21352.0</td>\n",
       "      <td>0WQ</td>\n",
       "      <td>0WQ</td>\n",
       "      <td>Avjet Corporation</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>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</td>\n",
       "      <td>396216</td>\n",
       "      <td>140239.0</td>\n",
       "      <td>536455</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>21645.0</td>\n",
       "      <td>23Q</td>\n",
       "      <td>23Q</td>\n",
       "      <td>Songbird Airways Inc.</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>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</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>21652.0</td>\n",
       "      <td>27Q</td>\n",
       "      <td>27Q</td>\n",
       "      <td>Jet Aviation Flight Services, Inc.</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>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</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>20408.0</td>\n",
       "      <td>5V</td>\n",
       "      <td>5V</td>\n",
       "      <td>Tatonduk Outfitters Limited d/b/a Everts Air A...</td>\n",
       "      <td>1</td>\n",
       "      <td>260848.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>522405</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>522405</td>\n",
       "      <td>569497</td>\n",
       "      <td>0.0</td>\n",
       "      <td>569497</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   month  airline_id unique_carrier carrier  \\\n",
       "0      1         NaN            NaN     0JQ   \n",
       "1      1     21352.0            0WQ     0WQ   \n",
       "2      1     21645.0            23Q     23Q   \n",
       "3      1     21652.0            27Q     27Q   \n",
       "4      1     20408.0             5V      5V   \n",
       "\n",
       "                                        carrier_name  carrier_group_new  \\\n",
       "0                                                NaN                  1   \n",
       "1                                  Avjet Corporation                  1   \n",
       "2                              Songbird Airways Inc.                  1   \n",
       "3                 Jet Aviation Flight Services, Inc.                  1   \n",
       "4  Tatonduk Outfitters Limited d/b/a Everts Air A...                  1   \n",
       "\n",
       "   sdomt_gallons  satl_gallons  spac_gallons  slat_gallons  ...  sdomt_cost  \\\n",
       "0            0.0           0.0           0.0           0.0  ...           0   \n",
       "1            0.0           0.0           0.0           0.0  ...           0   \n",
       "2            0.0           0.0           0.0           0.0  ...           0   \n",
       "3            0.0           0.0           0.0           0.0  ...           0   \n",
       "4       260848.0           0.0           0.0           0.0  ...      522405   \n",
       "\n",
       "   satl_cost  spac_cost  slat_cost  sint_cost  ts_cost  tdomt_cost  tint_cost  \\\n",
       "0        0.0        0.0        0.0        0.0        0        5100        0.0   \n",
       "1        0.0        0.0        0.0        0.0        0      396216   140239.0   \n",
       "2        0.0        0.0        0.0        0.0        0           0        0.0   \n",
       "3        0.0        0.0        0.0        0.0        0           0        0.0   \n",
       "4        0.0        0.0        0.0        0.0   522405      569497        0.0   \n",
       "\n",
       "   total_cost  year  \n",
       "0        5100  2016  \n",
       "1      536455  2016  \n",
       "2           0  2016  \n",
       "3           0  2016  \n",
       "4      569497  2016  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fuel = pd.read_csv('../Data/files/fuel_consumption.csv')\n",
    "fuel.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3035 entries, 0 to 3034\n",
      "Data columns (total 25 columns):\n",
      " #   Column             Non-Null Count  Dtype  \n",
      "---  ------             --------------  -----  \n",
      " 0   month              3035 non-null   int64  \n",
      " 1   airline_id         3034 non-null   float64\n",
      " 2   unique_carrier     3034 non-null   object \n",
      " 3   carrier            3035 non-null   object \n",
      " 4   carrier_name       3034 non-null   object \n",
      " 5   carrier_group_new  3035 non-null   int64  \n",
      " 6   sdomt_gallons      3034 non-null   float64\n",
      " 7   satl_gallons       3034 non-null   float64\n",
      " 8   spac_gallons       3034 non-null   float64\n",
      " 9   slat_gallons       3033 non-null   float64\n",
      " 10  sint_gallons       3034 non-null   float64\n",
      " 11  ts_gallons         3034 non-null   float64\n",
      " 12  tdomt_gallons      3032 non-null   float64\n",
      " 13  tint_gallons       3034 non-null   float64\n",
      " 14  total_gallons      3029 non-null   float64\n",
      " 15  sdomt_cost         3035 non-null   int64  \n",
      " 16  satl_cost          3034 non-null   float64\n",
      " 17  spac_cost          3034 non-null   float64\n",
      " 18  slat_cost          3034 non-null   float64\n",
      " 19  sint_cost          3034 non-null   float64\n",
      " 20  ts_cost            3035 non-null   int64  \n",
      " 21  tdomt_cost         3035 non-null   int64  \n",
      " 22  tint_cost          3034 non-null   float64\n",
      " 23  total_cost         3035 non-null   int64  \n",
      " 24  year               3035 non-null   int64  \n",
      "dtypes: float64(15), int64(7), object(3)\n",
      "memory usage: 592.9+ KB\n"
     ]
    }
   ],
   "source": [
    "fuel.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['month', 'airline_id', 'unique_carrier', 'carrier', 'carrier_name',\n",
       "       'carrier_group_new', 'sdomt_gallons', 'satl_gallons', 'spac_gallons',\n",
       "       'slat_gallons', 'sint_gallons', 'ts_gallons', 'tdomt_gallons',\n",
       "       'tint_gallons', 'total_gallons', 'sdomt_cost', 'satl_cost', 'spac_cost',\n",
       "       'slat_cost', 'sint_cost', 'ts_cost', 'tdomt_cost', 'tint_cost',\n",
       "       'total_cost', 'year'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fuel.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Missing data investigation:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Preview missing data percentages for features with null values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "airline_id        0.032949\n",
       "unique_carrier    0.032949\n",
       "carrier_name      0.032949\n",
       "sdomt_gallons     0.032949\n",
       "satl_gallons      0.032949\n",
       "spac_gallons      0.032949\n",
       "slat_gallons      0.065898\n",
       "sint_gallons      0.032949\n",
       "ts_gallons        0.032949\n",
       "tdomt_gallons     0.098847\n",
       "tint_gallons      0.032949\n",
       "total_gallons     0.197694\n",
       "satl_cost         0.032949\n",
       "spac_cost         0.032949\n",
       "slat_cost         0.032949\n",
       "sint_cost         0.032949\n",
       "tint_cost         0.032949\n",
       "dtype: float64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fuel_missing_values = fuel.isna().sum() > 0\n",
    "fuel_missing_value_percentage = fuel.isna().sum() / fuel.shape[0] * 100\n",
    "\n",
    "fuel_missing_value_percentage[fuel_missing_values]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total observations retained / 3035: 3025\n",
      "Percentage retained: 99.67\n"
     ]
    }
   ],
   "source": [
    "fuel_total_observations_retained = fuel.dropna().shape[0]\n",
    "fuel_percent_data_retained = fuel_total_observations_retained / fuel.shape[0] * 100\n",
    "\n",
    "print(f'Total observations retained / {fuel.shape[0]}: {fuel_total_observations_retained}')\n",
    "print(f'Percentage retained: {fuel_percent_data_retained:.2f}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The rows with missing values for these features could be safely dropped because of their relatively low composition on the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "fuel = fuel.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], dtype: float64)"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fuel_missing_values = fuel.isna().sum() > 0\n",
    "fuel_missing_value_percentage = fuel.isna().sum() / fuel.shape[0] * 100\n",
    "\n",
    "fuel_missing_value_percentage[fuel_missing_values]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "fuel = fuel.dropna(how='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 3025 entries, 1 to 3034\n",
      "Data columns (total 25 columns):\n",
      " #   Column             Non-Null Count  Dtype  \n",
      "---  ------             --------------  -----  \n",
      " 0   month              3025 non-null   int64  \n",
      " 1   airline_id         3025 non-null   float64\n",
      " 2   unique_carrier     3025 non-null   object \n",
      " 3   carrier            3025 non-null   object \n",
      " 4   carrier_name       3025 non-null   object \n",
      " 5   carrier_group_new  3025 non-null   int64  \n",
      " 6   sdomt_gallons      3025 non-null   float64\n",
      " 7   satl_gallons       3025 non-null   float64\n",
      " 8   spac_gallons       3025 non-null   float64\n",
      " 9   slat_gallons       3025 non-null   float64\n",
      " 10  sint_gallons       3025 non-null   float64\n",
      " 11  ts_gallons         3025 non-null   float64\n",
      " 12  tdomt_gallons      3025 non-null   float64\n",
      " 13  tint_gallons       3025 non-null   float64\n",
      " 14  total_gallons      3025 non-null   float64\n",
      " 15  sdomt_cost         3025 non-null   int64  \n",
      " 16  satl_cost          3025 non-null   float64\n",
      " 17  spac_cost          3025 non-null   float64\n",
      " 18  slat_cost          3025 non-null   float64\n",
      " 19  sint_cost          3025 non-null   float64\n",
      " 20  ts_cost            3025 non-null   int64  \n",
      " 21  tdomt_cost         3025 non-null   int64  \n",
      " 22  tint_cost          3025 non-null   float64\n",
      " 23  total_cost         3025 non-null   int64  \n",
      " 24  year               3025 non-null   int64  \n",
      "dtypes: float64(15), int64(7), object(3)\n",
      "memory usage: 614.5+ KB\n"
     ]
    }
   ],
   "source": [
    "fuel.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are no empty rows in **fuel_consumption**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Storing the processed files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights.to_csv('../Data/files/flights_no_missing.csv', index=False)\n",
    "flights_test.to_csv('../Data/files/flights_test_no_missing.csv', index=False)\n",
    "fuel.to_csv('../Data/files/fuel_no_missing.csv', index=False)\n",
    "passengers.to_csv('../Data/files/passengers_no_missing.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "8d6676aaa1277e38a8b007c79f4c02c0460f54b902bd054fa5584050907dc207"
  },
  "kernelspec": {
   "display_name": "Python 3.9.7 64-bit ('LHL': conda)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.13"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}