multiclass_large_data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Large Data Cleaning Multiclass Classification\n",
    "\n",
    "The target variables are **CARRIER_DELAY, WEATHER_DELAY, NAS_DELAY, SECURITY_DELAY, LATE_AIRCRAFT_DELAY**. We need to do additional transformations because these variables are not binary but continuos. For each flight that was delayed, we need to have one of these variables as 1 and others 0.\n",
    "\n",
    "It can happen that we have two types of delays with more than 0 minutes. In this case, take the bigger one as 1 and others as 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Once deleted, variables cannot be recovered. Proceed (y/[n])?  y\n"
     ]
    }
   ],
   "source": [
    "%reset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import data the large data set from csv."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights = pd.read_csv(\"data/large/flights_7_mil.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'fl_date', 'mkt_unique_carrier', 'branded_code_share',\n",
       "       'mkt_carrier', 'mkt_carrier_fl_num', 'op_unique_carrier', 'tail_num',\n",
       "       'op_carrier_fl_num', 'origin_airport_id', 'origin', 'origin_city_name',\n",
       "       'dest_airport_id', 'dest', 'dest_city_name', 'crs_dep_time', 'dep_time',\n",
       "       'dep_delay', 'taxi_out', 'wheels_off', 'wheels_on', 'taxi_in',\n",
       "       'crs_arr_time', 'arr_time', 'arr_delay', 'cancelled',\n",
       "       'cancellation_code', 'diverted', 'dup', 'crs_elapsed_time',\n",
       "       'actual_elapsed_time', 'air_time', 'flights', 'distance',\n",
       "       'carrier_delay', 'weather_delay', 'nas_delay', 'security_delay',\n",
       "       'late_aircraft_delay', 'first_dep_time', 'total_add_gtime',\n",
       "       'longest_add_gtime', 'no_name'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Drop unnecessary columns\n",
    "flights = flights[[\n",
    "                    'fl_date', 'mkt_unique_carrier','cancelled','diverted',\n",
    "                    'crs_elapsed_time','crs_dep_time','crs_arr_time',\n",
    "                    'carrier_delay', 'weather_delay', 'nas_delay', 'security_delay','late_aircraft_delay',\n",
    "                    'dep_delay','arr_delay',\n",
    "                   ]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "fl_date                      0\n",
       "mkt_unique_carrier           0\n",
       "cancelled                    0\n",
       "diverted                     0\n",
       "crs_elapsed_time            19\n",
       "crs_dep_time                 0\n",
       "crs_arr_time                 0\n",
       "carrier_delay          5723546\n",
       "weather_delay          5723546\n",
       "nas_delay              5723546\n",
       "security_delay         5723546\n",
       "late_aircraft_delay    5723546\n",
       "dep_delay               113102\n",
       "arr_delay               133431\n",
       "dtype: int64"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "test = flights[(flights['cancelled'] != 1)&(flights['diverted']!=1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "fl_date                      0\n",
       "mkt_unique_carrier           0\n",
       "cancelled                    0\n",
       "diverted                     0\n",
       "crs_elapsed_time             0\n",
       "crs_dep_time                 0\n",
       "crs_arr_time                 0\n",
       "carrier_delay          5591085\n",
       "weather_delay          5591085\n",
       "nas_delay              5591085\n",
       "security_delay         5591085\n",
       "late_aircraft_delay    5591085\n",
       "dep_delay                 1818\n",
       "arr_delay                  970\n",
       "dtype: int64"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = test[(test['crs_elapsed_time'].notnull())]\n",
    "test.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fill_columns(fill,columns,df):\n",
    "    for col in columns:\n",
    "        df[col].fillna(fill,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fill all null delays columns with 0.\n",
    "columns = ['late_aircraft_delay','security_delay','nas_delay','weather_delay','carrier_delay','dep_delay','arr_delay']\n",
    "fill_columns(0,columns,test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "fl_date                0\n",
       "mkt_unique_carrier     0\n",
       "cancelled              0\n",
       "diverted               0\n",
       "crs_elapsed_time       0\n",
       "crs_dep_time           0\n",
       "crs_arr_time           0\n",
       "carrier_delay          0\n",
       "weather_delay          0\n",
       "nas_delay              0\n",
       "security_delay         0\n",
       "late_aircraft_delay    0\n",
       "dep_delay              0\n",
       "arr_delay              0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "test['fl_date'] = pd.to_datetime(test['fl_date'],format='%Y-%m-%d')\n",
    "test['year'] = pd.DatetimeIndex(test['fl_date']).year\n",
    "test['month'] = pd.DatetimeIndex(test['fl_date']).month\n",
    "test['day'] = pd.DatetimeIndex(test['fl_date']).day\n",
    "test = test.drop(columns=['fl_date','cancelled','diverted'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "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>mkt_unique_carrier</th>\n",
       "      <th>crs_elapsed_time</th>\n",
       "      <th>crs_dep_time</th>\n",
       "      <th>crs_arr_time</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>dep_delay</th>\n",
       "      <th>arr_delay</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA</td>\n",
       "      <td>104.0</td>\n",
       "      <td>1837</td>\n",
       "      <td>2021</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA</td>\n",
       "      <td>142.0</td>\n",
       "      <td>709</td>\n",
       "      <td>931</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA</td>\n",
       "      <td>152.0</td>\n",
       "      <td>1012</td>\n",
       "      <td>1244</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA</td>\n",
       "      <td>303.0</td>\n",
       "      <td>1538</td>\n",
       "      <td>2341</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>-21.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA</td>\n",
       "      <td>90.0</td>\n",
       "      <td>1007</td>\n",
       "      <td>1137</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  mkt_unique_carrier  crs_elapsed_time  crs_dep_time  crs_arr_time  \\\n",
       "0                 AA             104.0          1837          2021   \n",
       "1                 AA             142.0           709           931   \n",
       "2                 AA             152.0          1012          1244   \n",
       "3                 AA             303.0          1538          2341   \n",
       "4                 AA              90.0          1007          1137   \n",
       "\n",
       "   carrier_delay  weather_delay  nas_delay  security_delay  \\\n",
       "0            2.0            0.0        0.0             0.0   \n",
       "1            0.0            0.0       46.0             0.0   \n",
       "2            8.0            0.0        1.0             0.0   \n",
       "3            0.0            0.0        0.0             0.0   \n",
       "4            0.0            0.0        0.0             0.0   \n",
       "\n",
       "   late_aircraft_delay  dep_delay  arr_delay  year  month  day  \n",
       "0                 35.0       50.0       37.0  2019      8   27  \n",
       "1                  0.0       -5.0       46.0  2019      8   27  \n",
       "2                 45.0       53.0       54.0  2019      8   27  \n",
       "3                  0.0       -1.0      -21.0  2019      8   27  \n",
       "4                 56.0       69.0       56.0  2019      8   27  "
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['mkt_unique_carrier'], dtype='object')"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.select_dtypes(include='object').columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "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>mkt_unique_carrier</th>\n",
       "      <th>crs_elapsed_time</th>\n",
       "      <th>crs_dep_time</th>\n",
       "      <th>crs_arr_time</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>dep_delay</th>\n",
       "      <th>arr_delay</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA</td>\n",
       "      <td>104.0</td>\n",
       "      <td>1837</td>\n",
       "      <td>2021</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA</td>\n",
       "      <td>142.0</td>\n",
       "      <td>709</td>\n",
       "      <td>931</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA</td>\n",
       "      <td>152.0</td>\n",
       "      <td>1012</td>\n",
       "      <td>1244</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA</td>\n",
       "      <td>303.0</td>\n",
       "      <td>1538</td>\n",
       "      <td>2341</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>-21.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA</td>\n",
       "      <td>90.0</td>\n",
       "      <td>1007</td>\n",
       "      <td>1137</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  mkt_unique_carrier  crs_elapsed_time  crs_dep_time  crs_arr_time  \\\n",
       "0                 AA             104.0          1837          2021   \n",
       "1                 AA             142.0           709           931   \n",
       "2                 AA             152.0          1012          1244   \n",
       "3                 AA             303.0          1538          2341   \n",
       "4                 AA              90.0          1007          1137   \n",
       "\n",
       "   carrier_delay  weather_delay  nas_delay  security_delay  \\\n",
       "0            2.0            0.0        0.0             0.0   \n",
       "1            0.0            0.0       46.0             0.0   \n",
       "2            8.0            0.0        1.0             0.0   \n",
       "3            0.0            0.0        0.0             0.0   \n",
       "4            0.0            0.0        0.0             0.0   \n",
       "\n",
       "   late_aircraft_delay  dep_delay  arr_delay  year  month  day  \n",
       "0                 35.0       50.0       37.0  2019      8   27  \n",
       "1                  0.0       -5.0       46.0  2019      8   27  \n",
       "2                 45.0       53.0       54.0  2019      8   27  \n",
       "3                  0.0       -1.0      -21.0  2019      8   27  \n",
       "4                 56.0       69.0       56.0  2019      8   27  "
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# test = pd.get_dummies(test, columns=test.select_dtypes(include='object').columns)\n",
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "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>crs_elapsed_time</th>\n",
       "      <th>crs_dep_time</th>\n",
       "      <th>crs_arr_time</th>\n",
       "      <th>dep_delay</th>\n",
       "      <th>arr_delay</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>Max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>104.0</td>\n",
       "      <td>1837</td>\n",
       "      <td>2021</td>\n",
       "      <td>50.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>27</td>\n",
       "      <td>late_aircraft_delay</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>142.0</td>\n",
       "      <td>709</td>\n",
       "      <td>931</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>27</td>\n",
       "      <td>nas_delay</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>152.0</td>\n",
       "      <td>1012</td>\n",
       "      <td>1244</td>\n",
       "      <td>53.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>27</td>\n",
       "      <td>late_aircraft_delay</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>303.0</td>\n",
       "      <td>1538</td>\n",
       "      <td>2341</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-21.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>27</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>90.0</td>\n",
       "      <td>1007</td>\n",
       "      <td>1137</td>\n",
       "      <td>69.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>27</td>\n",
       "      <td>late_aircraft_delay</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   crs_elapsed_time  crs_dep_time  crs_arr_time  dep_delay  arr_delay  year  \\\n",
       "0             104.0          1837          2021       50.0       37.0  2019   \n",
       "1             142.0           709           931       -5.0       46.0  2019   \n",
       "2             152.0          1012          1244       53.0       54.0  2019   \n",
       "3             303.0          1538          2341       -1.0      -21.0  2019   \n",
       "4              90.0          1007          1137       69.0       56.0  2019   \n",
       "\n",
       "   month  day                  Max  \n",
       "0      8   27  late_aircraft_delay  \n",
       "1      8   27            nas_delay  \n",
       "2      8   27  late_aircraft_delay  \n",
       "3      8   27                  NaN  \n",
       "4      8   27  late_aircraft_delay  "
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# fill Max column with the largest value of delay column name\n",
    "# only flights with delays > 0\n",
    "\n",
    "test['Max'] = test[test['arr_delay']>0][['late_aircraft_delay','security_delay',\n",
    "                         'nas_delay','weather_delay','carrier_delay']].idxmax(axis=1)\n",
    "\n",
    "# drop the delay columns\n",
    "test = test.drop(columns = ['late_aircraft_delay','security_delay',\n",
    "                            'nas_delay','weather_delay','carrier_delay',\n",
    "                            'mkt_unique_carrier'])\n",
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "test.fillna('early',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "sample = test.sample(100000,weights=test.groupby('Max')['Max'].transform('count'), random_state=123)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Max\n",
       "carrier_delay            508\n",
       "early                  87932\n",
       "late_aircraft_delay    10918\n",
       "nas_delay                630\n",
       "weather_delay             12\n",
       "Name: Max, dtype: int64"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample.groupby('Max')['Max'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Max\n",
       "carrier_delay           334773\n",
       "early                  4512541\n",
       "late_aircraft_delay    1577042\n",
       "nas_delay               400003\n",
       "security_delay            2227\n",
       "weather_delay            40952\n",
       "Name: Max, dtype: int64"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.groupby('Max')['Max'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6867538, 14)"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get dummies for the delay columns\n",
    "test = pd.get_dummies(test, prefix='', prefix_sep='')\n",
    "test.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Export the dataframe to csv."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "test.to_csv('data/large/flights_7mil_encoded_delays_no_catagorical_sub.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "lhl_env",
   "language": "python",
   "name": "lhl_env"
  },
  "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}