Datapreprocessing1_Flight_Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Module 1 - Data Prepocessing - Processing Flight data\n",
    "### Objectives \n",
    "1. Import the files from their respective folders and then save them into different dataframes\n",
    "2. Filter each dataframe such that it only covers the airports as asked in the question.\n",
    "    1. `['ATL','CLT','DEN','DFW','EWR','IAH','JFK','LAS','LAX','MCO','MIA','ORD','PHX','SEA','SFO']`\n",
    "3. Merge both the datsets(one for 2016 and one for 2017)\n",
    "4. Filter out the dataset to the columns we need as specified in the question.\n",
    "    1. `['ArrDel15', 'ArrDelayMinutes', 'ArrTime', 'CRSArrTime', 'CRSDepTime', 'DayofMonth', 'DepDel15', 'DepDelayMinutes', `\n",
    " `       'DepTime', 'DestAirportID', 'FlightDate', 'Month', 'Origin', 'OriginAirportID', 'Quarter', 'Year']`\n",
    "5. Save the final dataset to a new `.csv` file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing the necessary modules\n",
    "We will only be needing pandas for this module"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Defining the list with the values of the airports that are under observation\n",
    "1. This list comes from the problem statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "airport_list = ['ATL', 'CLT', 'DEN', 'DFW', 'EWR', 'IAH', 'JFK', 'LAS', \n",
    "                'LAX', 'MCO', 'MIA', 'ORD', 'PHX', 'SEA', 'SFO']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Looking at one of the 2016 datasets\n",
    "1. Let us now look at the dataset from one of the 2016 files\n",
    "2. We shall look at the:\n",
    "    1. The first few rows of the dataframe\n",
    "    2. The shape of the dataframe\n",
    "\n",
    "The next few kernels of code are mainly for understanding how the dataframe looks like and understand the variables we are dealing with"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\HP\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3072: DtypeWarning: Columns (77) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "# The path of the file\n",
    "path_2016 = r\"C:\\Users\\HP\\1. ALL MY FOLDERS\\SOLARILLION\\ML PROJECT\\Data\\2016\\On_Time_On_Time_Performance_2016_1\\On_Time_On_Time_Performance_2016_1.csv\"\n",
    "\n",
    "# Reading the file\n",
    "df_2016 = pd.read_csv(path_2016)"
   ]
  },
  {
   "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>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>Month</th>\n",
       "      <th>DayofMonth</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>FlightDate</th>\n",
       "      <th>UniqueCarrier</th>\n",
       "      <th>AirlineID</th>\n",
       "      <th>Carrier</th>\n",
       "      <th>TailNum</th>\n",
       "      <th>...</th>\n",
       "      <th>Div4TailNum</th>\n",
       "      <th>Div5Airport</th>\n",
       "      <th>Div5AirportID</th>\n",
       "      <th>Div5AirportSeqID</th>\n",
       "      <th>Div5WheelsOn</th>\n",
       "      <th>Div5TotalGTime</th>\n",
       "      <th>Div5LongestGTime</th>\n",
       "      <th>Div5WheelsOff</th>\n",
       "      <th>Div5TailNum</th>\n",
       "      <th>Unnamed: 109</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-01-06</td>\n",
       "      <td>AA</td>\n",
       "      <td>19805</td>\n",
       "      <td>AA</td>\n",
       "      <td>N4YBAA</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>2016-01-07</td>\n",
       "      <td>AA</td>\n",
       "      <td>19805</td>\n",
       "      <td>AA</td>\n",
       "      <td>N434AA</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>2016-01-08</td>\n",
       "      <td>AA</td>\n",
       "      <td>19805</td>\n",
       "      <td>AA</td>\n",
       "      <td>N541AA</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>2016-01-09</td>\n",
       "      <td>AA</td>\n",
       "      <td>19805</td>\n",
       "      <td>AA</td>\n",
       "      <td>N489AA</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>2016-01-10</td>\n",
       "      <td>AA</td>\n",
       "      <td>19805</td>\n",
       "      <td>AA</td>\n",
       "      <td>N439AA</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 110 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  Quarter  Month  DayofMonth  DayOfWeek  FlightDate UniqueCarrier  \\\n",
       "0  2016        1      1           6          3  2016-01-06            AA   \n",
       "1  2016        1      1           7          4  2016-01-07            AA   \n",
       "2  2016        1      1           8          5  2016-01-08            AA   \n",
       "3  2016        1      1           9          6  2016-01-09            AA   \n",
       "4  2016        1      1          10          7  2016-01-10            AA   \n",
       "\n",
       "   AirlineID Carrier TailNum  ...  Div4TailNum  Div5Airport  Div5AirportID  \\\n",
       "0      19805      AA  N4YBAA  ...          NaN          NaN            NaN   \n",
       "1      19805      AA  N434AA  ...          NaN          NaN            NaN   \n",
       "2      19805      AA  N541AA  ...          NaN          NaN            NaN   \n",
       "3      19805      AA  N489AA  ...          NaN          NaN            NaN   \n",
       "4      19805      AA  N439AA  ...          NaN          NaN            NaN   \n",
       "\n",
       "   Div5AirportSeqID Div5WheelsOn Div5TotalGTime Div5LongestGTime  \\\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",
       "   Div5WheelsOff Div5TailNum  Unnamed: 109  \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 110 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Displaying the first 5 row of the dataframe\n",
    "df_2016.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(445827, 110)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Displaying the shape of the dataframe\n",
    "df_2016.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting the overall dataset for all the months of 2016\n",
    "1. First we define a path to the file and since it only changes with the integer, which always increments by one, we just add the string of that integer\n",
    "2. Then we filter out the file where the `Origin` and the `Destination` of the Flight is in the airport list as we defined above.\n",
    "3. We append each dataframe to a larger dataframe which we use for later purposes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\HP\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3072: DtypeWarning: Columns (77) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n",
      "C:\\Users\\HP\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3072: DtypeWarning: Columns (77,84) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n",
      "C:\\Users\\HP\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3072: DtypeWarning: Columns (48,77,84) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n",
      "C:\\Users\\HP\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3072: DtypeWarning: Columns (48,77,84,85) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n",
      "C:\\Users\\HP\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3072: DtypeWarning: Columns (48,69,76,77,84) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "# Creating the main overall dataframe\n",
    "df_main1 = pd.DataFrame()\n",
    "\n",
    "# Starting the `for` loop which helps us change the file name\n",
    "for i in range(1, 13):\n",
    "    # The path changes as the value of `i` changes\n",
    "    path = r\"Data\\2016\\On_Time_On_Time_Performance_2016_\"+str(i)+'/'+\"On_Time_On_Time_Performance_2016_\"+str(i)+'.csv'\n",
    "    df = pd.read_csv(path)\n",
    "    \n",
    "    #Filtering the dataframe based on the airport list\n",
    "    df1 = df[df['Origin'].isin(airport_list)]\n",
    "    df2 = df1[df1['Dest'].isin(airport_list)]\n",
    "    # Creating a temporary dataframe to append to this new dataframe\n",
    "    if i == 1:\n",
    "        temp_df = pd.DataFrame()\n",
    "        df_main1 = temp_df.append(df2,ignore_index = True)\n",
    "    else:\n",
    "        df_main1 = df_main1.append(df2,ignore_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(948819, 110)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Looking at the shape of the dataframe\n",
    "df_main1.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Looking at one of the 2017 datasets\n",
    "1. Let us now look at the dataset from one of the 2017 files\n",
    "2. We shall look at the:\n",
    "    1. The first few rows of the dataframe\n",
    "    2. The shape of the dataframe\n",
    "\n",
    "The next few kernels of code are mainly for understanding how the dataframe looks like and understand the variables we are dealing with"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The path for one of the file\n",
    "path2 = r\"C:\\Users\\HP\\1. ALL MY FOLDERS\\SOLARILLION\\ML PROJECT\\Data\\2017\\On_Time_On_Time_Performance_2017_1\\On_Time_On_Time_Performance_2017_1.csv\"\n",
    "\n",
    "# Reading the dataset\n",
    "df_2017 = pd.read_csv(path2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "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>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>Month</th>\n",
       "      <th>DayofMonth</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>FlightDate</th>\n",
       "      <th>UniqueCarrier</th>\n",
       "      <th>AirlineID</th>\n",
       "      <th>Carrier</th>\n",
       "      <th>TailNum</th>\n",
       "      <th>...</th>\n",
       "      <th>Div4TailNum</th>\n",
       "      <th>Div5Airport</th>\n",
       "      <th>Div5AirportID</th>\n",
       "      <th>Div5AirportSeqID</th>\n",
       "      <th>Div5WheelsOn</th>\n",
       "      <th>Div5TotalGTime</th>\n",
       "      <th>Div5LongestGTime</th>\n",
       "      <th>Div5WheelsOff</th>\n",
       "      <th>Div5TailNum</th>\n",
       "      <th>Unnamed: 109</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "      <td>2017-01-17</td>\n",
       "      <td>AA</td>\n",
       "      <td>19805</td>\n",
       "      <td>AA</td>\n",
       "      <td>N583AA</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>3</td>\n",
       "      <td>2017-01-18</td>\n",
       "      <td>AA</td>\n",
       "      <td>19805</td>\n",
       "      <td>AA</td>\n",
       "      <td>N544AA</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>4</td>\n",
       "      <td>2017-01-19</td>\n",
       "      <td>AA</td>\n",
       "      <td>19805</td>\n",
       "      <td>AA</td>\n",
       "      <td>N553AA</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "      <td>2017-01-20</td>\n",
       "      <td>AA</td>\n",
       "      <td>19805</td>\n",
       "      <td>AA</td>\n",
       "      <td>N191AA</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>21</td>\n",
       "      <td>6</td>\n",
       "      <td>2017-01-21</td>\n",
       "      <td>AA</td>\n",
       "      <td>19805</td>\n",
       "      <td>AA</td>\n",
       "      <td>N170AA</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 110 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  Quarter  Month  DayofMonth  DayOfWeek  FlightDate UniqueCarrier  \\\n",
       "0  2017        1      1          17          2  2017-01-17            AA   \n",
       "1  2017        1      1          18          3  2017-01-18            AA   \n",
       "2  2017        1      1          19          4  2017-01-19            AA   \n",
       "3  2017        1      1          20          5  2017-01-20            AA   \n",
       "4  2017        1      1          21          6  2017-01-21            AA   \n",
       "\n",
       "   AirlineID Carrier TailNum  ...  Div4TailNum  Div5Airport  Div5AirportID  \\\n",
       "0      19805      AA  N583AA  ...          NaN          NaN            NaN   \n",
       "1      19805      AA  N544AA  ...          NaN          NaN            NaN   \n",
       "2      19805      AA  N553AA  ...          NaN          NaN            NaN   \n",
       "3      19805      AA  N191AA  ...          NaN          NaN            NaN   \n",
       "4      19805      AA  N170AA  ...          NaN          NaN            NaN   \n",
       "\n",
       "   Div5AirportSeqID Div5WheelsOn Div5TotalGTime Div5LongestGTime  \\\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",
       "   Div5WheelsOff Div5TailNum  Unnamed: 109  \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 110 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Looking at the first few rows\n",
    "df_2017.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\HP\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3072: DtypeWarning: Columns (48,77,84) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n",
      "C:\\Users\\HP\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3072: DtypeWarning: Columns (48,77) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n",
      "C:\\Users\\HP\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3072: DtypeWarning: Columns (77,84) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "df_main2 = pd.DataFrame()\n",
    "for i in range(1, 13):\n",
    "    path = r\"Data\\2017\\On_Time_On_Time_Performance_2017_\"+str(i)+'/'+\"On_Time_On_Time_Performance_2017_\"+str(i)+'.csv'\n",
    "    df = pd.read_csv(path)\n",
    "    df1 = df[df['Origin'].isin(airport_list)]\n",
    "    df2 = df1[df1['Dest'].isin(airport_list)]\n",
    "    if i == 1:\n",
    "        temp_df = pd.DataFrame()\n",
    "        df_main2 = temp_df.append(df2,ignore_index = True)\n",
    "    else:\n",
    "        df_main2 = df_main2.append(df2,ignore_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(928477, 110)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_main2.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dropping the empty values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating the final dataframe\n",
    "1. Appending both of the 2016 and 2017 datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = df_main1.append(df_main2,ignore_index = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering out\n",
    "These are the variables that we will be looking at in the future"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['ArrDel15', 'ArrDelayMinutes', 'ArrTime', 'CRSArrTime', 'CRSDepTime', 'DayofMonth', 'DepDel15', 'DepDelayMinutes', \n",
    "        'DepTime', 'Dest', 'DestAirportID', 'FlightDate', 'Month', 'Origin', 'OriginAirportID', 'Quarter', 'Year']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = final_df[[col for col in cols]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = final_df.dropna(how = 'any')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "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>ArrDel15</th>\n",
       "      <th>ArrDelayMinutes</th>\n",
       "      <th>ArrTime</th>\n",
       "      <th>CRSArrTime</th>\n",
       "      <th>CRSDepTime</th>\n",
       "      <th>DayofMonth</th>\n",
       "      <th>DepDel15</th>\n",
       "      <th>DepDelayMinutes</th>\n",
       "      <th>DepTime</th>\n",
       "      <th>Dest</th>\n",
       "      <th>DestAirportID</th>\n",
       "      <th>FlightDate</th>\n",
       "      <th>Month</th>\n",
       "      <th>Origin</th>\n",
       "      <th>OriginAirportID</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1610.0</td>\n",
       "      <td>1602</td>\n",
       "      <td>745</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>741.0</td>\n",
       "      <td>JFK</td>\n",
       "      <td>12478</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>SEA</td>\n",
       "      <td>14747</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1613.0</td>\n",
       "      <td>1602</td>\n",
       "      <td>745</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>737.0</td>\n",
       "      <td>JFK</td>\n",
       "      <td>12478</td>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>1</td>\n",
       "      <td>SEA</td>\n",
       "      <td>14747</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1547.0</td>\n",
       "      <td>1602</td>\n",
       "      <td>745</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>743.0</td>\n",
       "      <td>JFK</td>\n",
       "      <td>12478</td>\n",
       "      <td>2016-01-03</td>\n",
       "      <td>1</td>\n",
       "      <td>SEA</td>\n",
       "      <td>14747</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1551.0</td>\n",
       "      <td>1602</td>\n",
       "      <td>745</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>737.0</td>\n",
       "      <td>JFK</td>\n",
       "      <td>12478</td>\n",
       "      <td>2016-01-04</td>\n",
       "      <td>1</td>\n",
       "      <td>SEA</td>\n",
       "      <td>14747</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1524.0</td>\n",
       "      <td>1527</td>\n",
       "      <td>710</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>708.0</td>\n",
       "      <td>JFK</td>\n",
       "      <td>12478</td>\n",
       "      <td>2016-01-05</td>\n",
       "      <td>1</td>\n",
       "      <td>SEA</td>\n",
       "      <td>14747</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ArrDel15  ArrDelayMinutes  ArrTime  CRSArrTime  CRSDepTime  DayofMonth  \\\n",
       "0       0.0              8.0   1610.0        1602         745           1   \n",
       "1       0.0             11.0   1613.0        1602         745           2   \n",
       "2       0.0              0.0   1547.0        1602         745           3   \n",
       "3       0.0              0.0   1551.0        1602         745           4   \n",
       "4       0.0              0.0   1524.0        1527         710           5   \n",
       "\n",
       "   DepDel15  DepDelayMinutes  DepTime Dest  DestAirportID  FlightDate  Month  \\\n",
       "0       0.0              0.0    741.0  JFK          12478  2016-01-01      1   \n",
       "1       0.0              0.0    737.0  JFK          12478  2016-01-02      1   \n",
       "2       0.0              0.0    743.0  JFK          12478  2016-01-03      1   \n",
       "3       0.0              0.0    737.0  JFK          12478  2016-01-04      1   \n",
       "4       0.0              0.0    708.0  JFK          12478  2016-01-05      1   \n",
       "\n",
       "  Origin  OriginAirportID  Quarter  Year  \n",
       "0    SEA            14747        1  2016  \n",
       "1    SEA            14747        1  2016  \n",
       "2    SEA            14747        1  2016  \n",
       "3    SEA            14747        1  2016  \n",
       "4    SEA            14747        1  2016  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1851436, 17)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.drop_duplicates(inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving it to a file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.to_csv(r'C:\\Users\\HP\\1. ALL MY FOLDERS\\SOLARILLION\\ML PROJECT\\Data\\Flight_Data.csv',index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let us try to read the file we saved"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>ArrDel15</th>\n",
       "      <th>ArrDelayMinutes</th>\n",
       "      <th>ArrTime</th>\n",
       "      <th>CRSArrTime</th>\n",
       "      <th>CRSDepTime</th>\n",
       "      <th>DayofMonth</th>\n",
       "      <th>DepDel15</th>\n",
       "      <th>DepDelayMinutes</th>\n",
       "      <th>DepTime</th>\n",
       "      <th>Dest</th>\n",
       "      <th>DestAirportID</th>\n",
       "      <th>FlightDate</th>\n",
       "      <th>Month</th>\n",
       "      <th>Origin</th>\n",
       "      <th>OriginAirportID</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1610.0</td>\n",
       "      <td>1602</td>\n",
       "      <td>745</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>741.0</td>\n",
       "      <td>JFK</td>\n",
       "      <td>12478</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>SEA</td>\n",
       "      <td>14747</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1613.0</td>\n",
       "      <td>1602</td>\n",
       "      <td>745</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>737.0</td>\n",
       "      <td>JFK</td>\n",
       "      <td>12478</td>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>1</td>\n",
       "      <td>SEA</td>\n",
       "      <td>14747</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1547.0</td>\n",
       "      <td>1602</td>\n",
       "      <td>745</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>743.0</td>\n",
       "      <td>JFK</td>\n",
       "      <td>12478</td>\n",
       "      <td>2016-01-03</td>\n",
       "      <td>1</td>\n",
       "      <td>SEA</td>\n",
       "      <td>14747</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1551.0</td>\n",
       "      <td>1602</td>\n",
       "      <td>745</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>737.0</td>\n",
       "      <td>JFK</td>\n",
       "      <td>12478</td>\n",
       "      <td>2016-01-04</td>\n",
       "      <td>1</td>\n",
       "      <td>SEA</td>\n",
       "      <td>14747</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1524.0</td>\n",
       "      <td>1527</td>\n",
       "      <td>710</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>708.0</td>\n",
       "      <td>JFK</td>\n",
       "      <td>12478</td>\n",
       "      <td>2016-01-05</td>\n",
       "      <td>1</td>\n",
       "      <td>SEA</td>\n",
       "      <td>14747</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ArrDel15  ArrDelayMinutes  ArrTime  CRSArrTime  CRSDepTime  DayofMonth  \\\n",
       "0       0.0              8.0   1610.0        1602         745           1   \n",
       "1       0.0             11.0   1613.0        1602         745           2   \n",
       "2       0.0              0.0   1547.0        1602         745           3   \n",
       "3       0.0              0.0   1551.0        1602         745           4   \n",
       "4       0.0              0.0   1524.0        1527         710           5   \n",
       "\n",
       "   DepDel15  DepDelayMinutes  DepTime Dest  DestAirportID  FlightDate  Month  \\\n",
       "0       0.0              0.0    741.0  JFK          12478  2016-01-01      1   \n",
       "1       0.0              0.0    737.0  JFK          12478  2016-01-02      1   \n",
       "2       0.0              0.0    743.0  JFK          12478  2016-01-03      1   \n",
       "3       0.0              0.0    737.0  JFK          12478  2016-01-04      1   \n",
       "4       0.0              0.0    708.0  JFK          12478  2016-01-05      1   \n",
       "\n",
       "  Origin  OriginAirportID  Quarter  Year  \n",
       "0    SEA            14747        1  2016  \n",
       "1    SEA            14747        1  2016  \n",
       "2    SEA            14747        1  2016  \n",
       "3    SEA            14747        1  2016  \n",
       "4    SEA            14747        1  2016  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "dataframe = pd.read_csv(\"Data\\Flight_Data.csv\")\n",
    "dataframe.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1851433, 17)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataframe.shape"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}