Cleaning_Flights.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 404,
   "id": "d57f41c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "pd.set_option(\"display.max_columns\", None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 405,
   "id": "df145f21",
   "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>Unnamed: 0.2</th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Unnamed: 0.1</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>DAY</th>\n",
       "      <th>DAY_OF_WEEK</th>\n",
       "      <th>AIRLINE</th>\n",
       "      <th>FLIGHT_NUMBER</th>\n",
       "      <th>TAIL_NUMBER</th>\n",
       "      <th>ORIGIN_AIRPORT</th>\n",
       "      <th>DESTINATION_AIRPORT</th>\n",
       "      <th>SCHEDULED_DEPARTURE</th>\n",
       "      <th>DEPARTURE_TIME</th>\n",
       "      <th>DEPARTURE_DELAY</th>\n",
       "      <th>TAXI_OUT</th>\n",
       "      <th>WHEELS_OFF</th>\n",
       "      <th>SCHEDULED_TIME</th>\n",
       "      <th>ELAPSED_TIME</th>\n",
       "      <th>AIR_TIME</th>\n",
       "      <th>DISTANCE</th>\n",
       "      <th>WHEELS_ON</th>\n",
       "      <th>TAXI_IN</th>\n",
       "      <th>SCHEDULED_ARRIVAL</th>\n",
       "      <th>ARRIVAL_TIME</th>\n",
       "      <th>ARRIVAL_DELAY</th>\n",
       "      <th>DIVERTED</th>\n",
       "      <th>CANCELLED</th>\n",
       "      <th>CANCELLATION_REASON</th>\n",
       "      <th>AIR_SYSTEM_DELAY</th>\n",
       "      <th>SECURITY_DELAY</th>\n",
       "      <th>AIRLINE_DELAY</th>\n",
       "      <th>LATE_AIRCRAFT_DELAY</th>\n",
       "      <th>WEATHER_DELAY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>363169</td>\n",
       "      <td>5290975</td>\n",
       "      <td>2015</td>\n",
       "      <td>11</td>\n",
       "      <td>28</td>\n",
       "      <td>6</td>\n",
       "      <td>DL</td>\n",
       "      <td>1590</td>\n",
       "      <td>N971AT</td>\n",
       "      <td>CAE</td>\n",
       "      <td>ATL</td>\n",
       "      <td>700</td>\n",
       "      <td>655.0</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>712.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>192</td>\n",
       "      <td>751.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>808</td>\n",
       "      <td>756.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>1</td>\n",
       "      <td>704061</td>\n",
       "      <td>4899614</td>\n",
       "      <td>2015</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>AA</td>\n",
       "      <td>2516</td>\n",
       "      <td>N3CVAA</td>\n",
       "      <td>ORD</td>\n",
       "      <td>DEN</td>\n",
       "      <td>1715</td>\n",
       "      <td>1743.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>1823.0</td>\n",
       "      <td>164.0</td>\n",
       "      <td>177.0</td>\n",
       "      <td>126.0</td>\n",
       "      <td>888</td>\n",
       "      <td>1929.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1859</td>\n",
       "      <td>1940.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0.2  Unnamed: 0  Unnamed: 0.1  YEAR  MONTH  DAY  DAY_OF_WEEK  \\\n",
       "0             0      363169       5290975  2015     11   28            6   \n",
       "1             1      704061       4899614  2015     11    2            1   \n",
       "\n",
       "  AIRLINE  FLIGHT_NUMBER TAIL_NUMBER ORIGIN_AIRPORT DESTINATION_AIRPORT  \\\n",
       "0      DL           1590      N971AT            CAE                 ATL   \n",
       "1      AA           2516      N3CVAA            ORD                 DEN   \n",
       "\n",
       "   SCHEDULED_DEPARTURE  DEPARTURE_TIME  DEPARTURE_DELAY  TAXI_OUT  WHEELS_OFF  \\\n",
       "0                  700           655.0             -5.0      17.0       712.0   \n",
       "1                 1715          1743.0             28.0      40.0      1823.0   \n",
       "\n",
       "   SCHEDULED_TIME  ELAPSED_TIME  AIR_TIME  DISTANCE  WHEELS_ON  TAXI_IN  \\\n",
       "0            68.0          61.0      39.0       192      751.0      5.0   \n",
       "1           164.0         177.0     126.0       888     1929.0     11.0   \n",
       "\n",
       "   SCHEDULED_ARRIVAL  ARRIVAL_TIME  ARRIVAL_DELAY  DIVERTED  CANCELLED  \\\n",
       "0                808         756.0          -12.0         0          0   \n",
       "1               1859        1940.0           41.0         0          0   \n",
       "\n",
       "  CANCELLATION_REASON  AIR_SYSTEM_DELAY  SECURITY_DELAY  AIRLINE_DELAY  \\\n",
       "0                 NaN               NaN             NaN            NaN   \n",
       "1                 NaN              13.0             0.0           28.0   \n",
       "\n",
       "   LATE_AIRCRAFT_DELAY  WEATHER_DELAY  \n",
       "0                  NaN            NaN  \n",
       "1                  0.0            0.0  "
      ]
     },
     "execution_count": 405,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('flights.csv')\n",
    "df_airports = pd.read_csv('airports.csv')\n",
    "df_airlines = pd.read_csv('airlines.csv')\n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 406,
   "id": "ffb74174",
   "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>IATA_CODE</th>\n",
       "      <th>AIRPORT</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTRY</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ABE</td>\n",
       "      <td>Lehigh Valley International Airport</td>\n",
       "      <td>Allentown</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>40.65236</td>\n",
       "      <td>-75.4404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABI</td>\n",
       "      <td>Abilene Regional Airport</td>\n",
       "      <td>Abilene</td>\n",
       "      <td>TX</td>\n",
       "      <td>USA</td>\n",
       "      <td>32.41132</td>\n",
       "      <td>-99.6819</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  IATA_CODE                              AIRPORT       CITY STATE COUNTRY  \\\n",
       "0       ABE  Lehigh Valley International Airport  Allentown    PA     USA   \n",
       "1       ABI             Abilene Regional Airport    Abilene    TX     USA   \n",
       "\n",
       "   LATITUDE  LONGITUDE  \n",
       "0  40.65236   -75.4404  \n",
       "1  32.41132   -99.6819  "
      ]
     },
     "execution_count": 406,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_airports.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 407,
   "id": "f18572b8",
   "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>IATA_CODE</th>\n",
       "      <th>AIRLINE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>UA</td>\n",
       "      <td>United Air Lines Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  IATA_CODE                 AIRLINE\n",
       "0        UA   United Air Lines Inc.\n",
       "1        AA  American Airlines Inc."
      ]
     },
     "execution_count": 407,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_airlines.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9fa7288b",
   "metadata": {},
   "source": [
    "> We will use the `df_airports` and `df_airlines` to merge them with the `df` to creat a great One dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae5aac3c",
   "metadata": {},
   "source": [
    "# Lets first clean the flights dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 408,
   "id": "c3c5d153",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 274964 entries, 0 to 274963\n",
      "Data columns (total 34 columns):\n",
      " #   Column               Non-Null Count   Dtype  \n",
      "---  ------               --------------   -----  \n",
      " 0   Unnamed: 0.2         274964 non-null  int64  \n",
      " 1   Unnamed: 0           274964 non-null  int64  \n",
      " 2   Unnamed: 0.1         274964 non-null  int64  \n",
      " 3   YEAR                 274964 non-null  int64  \n",
      " 4   MONTH                274964 non-null  int64  \n",
      " 5   DAY                  274964 non-null  int64  \n",
      " 6   DAY_OF_WEEK          274964 non-null  int64  \n",
      " 7   AIRLINE              274964 non-null  object \n",
      " 8   FLIGHT_NUMBER        274964 non-null  int64  \n",
      " 9   TAIL_NUMBER          274225 non-null  object \n",
      " 10  ORIGIN_AIRPORT       274964 non-null  object \n",
      " 11  DESTINATION_AIRPORT  274964 non-null  object \n",
      " 12  SCHEDULED_DEPARTURE  274964 non-null  int64  \n",
      " 13  DEPARTURE_TIME       270719 non-null  float64\n",
      " 14  DEPARTURE_DELAY      270719 non-null  float64\n",
      " 15  TAXI_OUT             270571 non-null  float64\n",
      " 16  WHEELS_OFF           270571 non-null  float64\n",
      " 17  SCHEDULED_TIME       274964 non-null  float64\n",
      " 18  ELAPSED_TIME         269773 non-null  float64\n",
      " 19  AIR_TIME             269773 non-null  float64\n",
      " 20  DISTANCE             274964 non-null  int64  \n",
      " 21  WHEELS_ON            270377 non-null  float64\n",
      " 22  TAXI_IN              270377 non-null  float64\n",
      " 23  SCHEDULED_ARRIVAL    274964 non-null  int64  \n",
      " 24  ARRIVAL_TIME         270377 non-null  float64\n",
      " 25  ARRIVAL_DELAY        269773 non-null  float64\n",
      " 26  DIVERTED             274964 non-null  int64  \n",
      " 27  CANCELLED            274964 non-null  int64  \n",
      " 28  CANCELLATION_REASON  4433 non-null    object \n",
      " 29  AIR_SYSTEM_DELAY     51863 non-null   float64\n",
      " 30  SECURITY_DELAY       51863 non-null   float64\n",
      " 31  AIRLINE_DELAY        51863 non-null   float64\n",
      " 32  LATE_AIRCRAFT_DELAY  51863 non-null   float64\n",
      " 33  WEATHER_DELAY        51863 non-null   float64\n",
      "dtypes: float64(16), int64(13), object(5)\n",
      "memory usage: 71.3+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d059a01",
   "metadata": {},
   "source": [
    "> We notice that there are many columns we need to edit.\n",
    ">\n",
    "> 1. There are 3 Unnamed columns we need to drop.\n",
    ">\n",
    ">\n",
    "> 2. There are many Null values here. The last 6 columns have many Null values but we need from them the:\n",
    ">  * `CANCELLATION_REASON`. So, we will will try to replace the Null with readable value. \n",
    ">\n",
    ">\n",
    "> 3. * `SCHEDULED_DEPARTURE`, `DEPARTURE_TIME`, `SCHEDULED_TIME`, `ELAPSED_TIME`, `AIR_TIME`, `SCHEDULED_ARRIVAL`, `ARRIVAL_TIME`, `WHEELS_ON`, and `WHEELS_OFF` **All needed to be converted to string to handle the missed zeros then to DateTime.**\n",
    ">\n",
    ">  * Also we should convert the `DAY_OF_WEEK` column to be string with the day name."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f2e3715",
   "metadata": {},
   "source": [
    "> ### 1) Droping the Un-named columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 409,
   "id": "44eac2f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns=['Unnamed: 0.2', 'Unnamed: 0', 'Unnamed: 0.1'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cce049eb",
   "metadata": {},
   "source": [
    "> ### 2) Cleaning the `CANCELLATION_REASON` column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 410,
   "id": "754ab29f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "B    2397\n",
       "A    1260\n",
       "C     776\n",
       "Name: CANCELLATION_REASON, dtype: int64"
      ]
     },
     "execution_count": 410,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.CANCELLATION_REASON.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efd11b23",
   "metadata": {},
   "source": [
    "> Now we need to replace these values with readable reasons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 411,
   "id": "d0065f07",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_reasons(reason):\n",
    "    if reason == \"A\":\n",
    "        return \"CANCELLATION_REASON\"\n",
    "    elif reason == \"B\":\n",
    "        return \"Weather\"\n",
    "    elif reason == \"C\":\n",
    "        return \"National Air System\"\n",
    "    else:\n",
    "        return \"NO-CANCELLATION\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 412,
   "id": "c92a4a0e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NO-CANCELLATION        270531\n",
       "Weather                  2397\n",
       "CANCELLATION_REASON      1260\n",
       "National Air System       776\n",
       "Name: CANCELLATION_REASON, dtype: int64"
      ]
     },
     "execution_count": 412,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['CANCELLATION_REASON'] = df.CANCELLATION_REASON.apply(lambda reason: get_reasons(reason))\n",
    "df.CANCELLATION_REASON.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f4eb948",
   "metadata": {},
   "source": [
    "### 3) Operating with Null values from columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 413,
   "id": "0546fa47",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "YEAR                        0\n",
       "MONTH                       0\n",
       "DAY                         0\n",
       "DAY_OF_WEEK                 0\n",
       "AIRLINE                     0\n",
       "FLIGHT_NUMBER               0\n",
       "TAIL_NUMBER                 0\n",
       "ORIGIN_AIRPORT              0\n",
       "DESTINATION_AIRPORT         0\n",
       "SCHEDULED_DEPARTURE         0\n",
       "DEPARTURE_TIME              0\n",
       "DEPARTURE_DELAY             0\n",
       "TAXI_OUT                    0\n",
       "WHEELS_OFF                  0\n",
       "SCHEDULED_TIME              0\n",
       "ELAPSED_TIME              758\n",
       "AIR_TIME                  758\n",
       "DISTANCE                    0\n",
       "WHEELS_ON                 154\n",
       "TAXI_IN                   154\n",
       "SCHEDULED_ARRIVAL           0\n",
       "ARRIVAL_TIME              154\n",
       "ARRIVAL_DELAY             758\n",
       "DIVERTED                    0\n",
       "CANCELLED                   0\n",
       "CANCELLATION_REASON         0\n",
       "AIR_SYSTEM_DELAY       218668\n",
       "SECURITY_DELAY         218668\n",
       "AIRLINE_DELAY          218668\n",
       "LATE_AIRCRAFT_DELAY    218668\n",
       "WEATHER_DELAY          218668\n",
       "dtype: int64"
      ]
     },
     "execution_count": 413,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The null values in the not canceled flights.\n",
    "df[df.CANCELLED == 0].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 414,
   "id": "a2f206f5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "YEAR                      0\n",
       "MONTH                     0\n",
       "DAY                       0\n",
       "DAY_OF_WEEK               0\n",
       "AIRLINE                   0\n",
       "FLIGHT_NUMBER             0\n",
       "TAIL_NUMBER             739\n",
       "ORIGIN_AIRPORT            0\n",
       "DESTINATION_AIRPORT       0\n",
       "SCHEDULED_DEPARTURE       0\n",
       "DEPARTURE_TIME         4245\n",
       "DEPARTURE_DELAY        4245\n",
       "TAXI_OUT               4393\n",
       "WHEELS_OFF             4393\n",
       "SCHEDULED_TIME            0\n",
       "ELAPSED_TIME           4433\n",
       "AIR_TIME               4433\n",
       "DISTANCE                  0\n",
       "WHEELS_ON              4433\n",
       "TAXI_IN                4433\n",
       "SCHEDULED_ARRIVAL         0\n",
       "ARRIVAL_TIME           4433\n",
       "ARRIVAL_DELAY          4433\n",
       "DIVERTED                  0\n",
       "CANCELLED                 0\n",
       "CANCELLATION_REASON       0\n",
       "AIR_SYSTEM_DELAY       4433\n",
       "SECURITY_DELAY         4433\n",
       "AIRLINE_DELAY          4433\n",
       "LATE_AIRCRAFT_DELAY    4433\n",
       "WEATHER_DELAY          4433\n",
       "dtype: int64"
      ]
     },
     "execution_count": 414,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The null values in the canceled flights.\n",
    "df[df.CANCELLED == 1].isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f701d3b",
   "metadata": {},
   "source": [
    "> **We notice that the null values differ between the 2 statuses. It is logically true that the features of the canceled flights differ from the features of the not canceled flights, So, The Nulls are normal here.**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73fa7dc9",
   "metadata": {},
   "source": [
    "> But we do not need the last five columns and we will stay with `CANCELLATION_REASON` instead of it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 415,
   "id": "37748268",
   "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>MONTH</th>\n",
       "      <th>DAY</th>\n",
       "      <th>DAY_OF_WEEK</th>\n",
       "      <th>AIRLINE</th>\n",
       "      <th>FLIGHT_NUMBER</th>\n",
       "      <th>TAIL_NUMBER</th>\n",
       "      <th>ORIGIN_AIRPORT</th>\n",
       "      <th>DESTINATION_AIRPORT</th>\n",
       "      <th>SCHEDULED_DEPARTURE</th>\n",
       "      <th>DEPARTURE_TIME</th>\n",
       "      <th>DEPARTURE_DELAY</th>\n",
       "      <th>TAXI_OUT</th>\n",
       "      <th>WHEELS_OFF</th>\n",
       "      <th>SCHEDULED_TIME</th>\n",
       "      <th>ELAPSED_TIME</th>\n",
       "      <th>AIR_TIME</th>\n",
       "      <th>DISTANCE</th>\n",
       "      <th>WHEELS_ON</th>\n",
       "      <th>TAXI_IN</th>\n",
       "      <th>SCHEDULED_ARRIVAL</th>\n",
       "      <th>ARRIVAL_TIME</th>\n",
       "      <th>ARRIVAL_DELAY</th>\n",
       "      <th>DIVERTED</th>\n",
       "      <th>CANCELLED</th>\n",
       "      <th>CANCELLATION_REASON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015</td>\n",
       "      <td>11</td>\n",
       "      <td>28</td>\n",
       "      <td>6</td>\n",
       "      <td>DL</td>\n",
       "      <td>1590</td>\n",
       "      <td>N971AT</td>\n",
       "      <td>CAE</td>\n",
       "      <td>ATL</td>\n",
       "      <td>700</td>\n",
       "      <td>655.0</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>712.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>192</td>\n",
       "      <td>751.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>808</td>\n",
       "      <td>756.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NO-CANCELLATION</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   YEAR  MONTH  DAY  DAY_OF_WEEK AIRLINE  FLIGHT_NUMBER TAIL_NUMBER  \\\n",
       "0  2015     11   28            6      DL           1590      N971AT   \n",
       "\n",
       "  ORIGIN_AIRPORT DESTINATION_AIRPORT  SCHEDULED_DEPARTURE  DEPARTURE_TIME  \\\n",
       "0            CAE                 ATL                  700           655.0   \n",
       "\n",
       "   DEPARTURE_DELAY  TAXI_OUT  WHEELS_OFF  SCHEDULED_TIME  ELAPSED_TIME  \\\n",
       "0             -5.0      17.0       712.0            68.0          61.0   \n",
       "\n",
       "   AIR_TIME  DISTANCE  WHEELS_ON  TAXI_IN  SCHEDULED_ARRIVAL  ARRIVAL_TIME  \\\n",
       "0      39.0       192      751.0      5.0                808         756.0   \n",
       "\n",
       "   ARRIVAL_DELAY  DIVERTED  CANCELLED CANCELLATION_REASON  \n",
       "0          -12.0         0          0     NO-CANCELLATION  "
      ]
     },
     "execution_count": 415,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "needed_columns = df.columns[range(-5, 0, 1)]\n",
    "df.drop(columns= needed_columns, inplace=True)\n",
    "df.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0fdfb5d",
   "metadata": {},
   "source": [
    "### Cleaning time columns\n",
    "`SCHEDULED_DEPARTURE`, `DEPARTURE_TIME`, `SCHEDULED_TIME`, `ELAPSED_TIME`, `AIR_TIME`, `SCHEDULED_ARRIVAL`, `ARRIVAL_TIME` \n",
    "\n",
    "> We need to make them on the format **YYYY/MM/DD HH:MM** so:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4412f480",
   "metadata": {},
   "source": [
    "> First we need to get the Date part for each row ... so we will concatinate the first 3 columns to get one column called `DATE`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 416,
   "id": "922f0dba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    2015/11/28\n",
       "1     2015/11/2\n",
       "2     2015/8/22\n",
       "3     2015/11/9\n",
       "4     2015/6/12\n",
       "Name: DATE, dtype: object"
      ]
     },
     "execution_count": 416,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Date_Columns = df.columns[range(0,3, 1)] # getting the columns YEAR, MONTH, DAY\n",
    "\n",
    "# Converting them to str to concatinating them with each other in one column called \"DATE\"\n",
    "for column in Date_Columns:\n",
    "    df[column] = df[column].astype(str)\n",
    "\n",
    "Date_Format = df['YEAR'] + '/' + df['MONTH'] + '/' + df['DAY']\n",
    "df.insert(0, \"DATE\", Date_Format)\n",
    "df.DATE.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 417,
   "id": "eda35b48",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now we do not need the columns YEAR, MONTEH, DAY\n",
    "df.drop(columns=Date_Columns, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b21df657",
   "metadata": {},
   "source": [
    "> **Now we will format at first the time columns to be 'HH:MM'**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 418,
   "id": "5ddaa070",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Getting the needed columns names in one place.\n",
    "time_columns = ['SCHEDULED_DEPARTURE', 'DEPARTURE_TIME', 'SCHEDULED_TIME', 'ELAPSED_TIME', 'AIR_TIME', 'SCHEDULED_ARRIVAL', 'ARRIVAL_TIME', 'WHEELS_OFF', 'WHEELS_ON']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 419,
   "id": "d512c44a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def adding_zeros(row_value):\n",
    "    '''\n",
    "    Input: the row value come from the getting_time_form function.\n",
    "    Output: the new format after adding the needed number of zeos\n",
    "    \n",
    "    '''\n",
    "    nedded_number_of_zeors = 4 - len(row_value)\n",
    "    return  '0' *  nedded_number_of_zeors + row_value\n",
    "\n",
    "def getting_time_form(row_val):\n",
    "    '''\n",
    "    \n",
    "    Input: the current value in the column form convert_to_time function\n",
    "    Output: returning it on the format HH : MM, but still string\n",
    "    \n",
    "    '''\n",
    "    value = adding_zeros(row_val)\n",
    "    hours = value[0:2] \n",
    "    minutes = value[2:4] \n",
    "    \n",
    "    # If there is no problms with hours and minutes .. return this time.\n",
    "    if (int(hours) >= 0 and int(hours) < 24) and (int(minutes) >= 0 and int(minutes) < 60):\n",
    "        return hours + ':' + minutes\n",
    "    \n",
    "    # If there is a problem return Null. Like large hours than 23 or minutes than 59 or Nan value.\n",
    "    else:\n",
    "        # we can handel this error by taking the difference between the actual minutes - 60 to be the new minute.\n",
    "        # Also we can handel hours like that.\n",
    "        # But this will make many values unConsistant with each others as these times dependent on each others.\n",
    "        \n",
    "        # So, I will pass these values giving them Null as value.\n",
    "        pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 420,
   "id": "de2419e4",
   "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>SCHEDULED_DEPARTURE</th>\n",
       "      <th>DEPARTURE_TIME</th>\n",
       "      <th>SCHEDULED_TIME</th>\n",
       "      <th>ELAPSED_TIME</th>\n",
       "      <th>AIR_TIME</th>\n",
       "      <th>SCHEDULED_ARRIVAL</th>\n",
       "      <th>ARRIVAL_TIME</th>\n",
       "      <th>WHEELS_OFF</th>\n",
       "      <th>WHEELS_ON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>07:00</td>\n",
       "      <td>06:55</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>00:39</td>\n",
       "      <td>08:08</td>\n",
       "      <td>07:56</td>\n",
       "      <td>07:12</td>\n",
       "      <td>07:51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17:15</td>\n",
       "      <td>17:43</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>01:26</td>\n",
       "      <td>18:59</td>\n",
       "      <td>19:40</td>\n",
       "      <td>18:23</td>\n",
       "      <td>19:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13:55</td>\n",
       "      <td>13:53</td>\n",
       "      <td>02:20</td>\n",
       "      <td>02:06</td>\n",
       "      <td>None</td>\n",
       "      <td>16:35</td>\n",
       "      <td>16:19</td>\n",
       "      <td>14:13</td>\n",
       "      <td>16:15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>06:50</td>\n",
       "      <td>06:52</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>01:42</td>\n",
       "      <td>08:51</td>\n",
       "      <td>08:44</td>\n",
       "      <td>07:08</td>\n",
       "      <td>08:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>07:05</td>\n",
       "      <td>07:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>08:38</td>\n",
       "      <td>08:32</td>\n",
       "      <td>07:12</td>\n",
       "      <td>08:23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  SCHEDULED_DEPARTURE DEPARTURE_TIME SCHEDULED_TIME ELAPSED_TIME AIR_TIME  \\\n",
       "0               07:00          06:55           None         None    00:39   \n",
       "1               17:15          17:43           None         None    01:26   \n",
       "2               13:55          13:53          02:20        02:06     None   \n",
       "3               06:50          06:52           None         None    01:42   \n",
       "4               07:05          07:00           None         None     None   \n",
       "\n",
       "  SCHEDULED_ARRIVAL ARRIVAL_TIME WHEELS_OFF WHEELS_ON  \n",
       "0             08:08        07:56      07:12     07:51  \n",
       "1             18:59        19:40      18:23     19:29  \n",
       "2             16:35        16:19      14:13     16:15  \n",
       "3             08:51        08:44      07:08     08:30  \n",
       "4             08:38        08:32      07:12     08:23  "
      ]
     },
     "execution_count": 420,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for column in time_columns:\n",
    "    df[column] = df[column].fillna(5555) # Convert nan/null to 5555 to consider it as a wrong time.\n",
    "    df[column] = df[column].astype('int') # to get rid of the decimal sign.\n",
    "    df[column] = df[column].astype('str') # to handel its length and add easily zeros.\n",
    "    \n",
    "    # Converting the value from the shape '####' to the shape 'HH:MM'\n",
    "    df[column] = df[column].apply(lambda val: getting_time_form(val))\n",
    "    \n",
    "df[time_columns].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d7150b8",
   "metadata": {},
   "source": [
    "> Now our time columns ready to be compied in the `DATE` column to get the format _YYYY/MM/DD HH:MM_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 421,
   "id": "48461522",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 274964 entries, 0 to 274963\n",
      "Data columns (total 24 columns):\n",
      " #   Column               Non-Null Count   Dtype         \n",
      "---  ------               --------------   -----         \n",
      " 0   DATE                 274964 non-null  object        \n",
      " 1   DAY_OF_WEEK          274964 non-null  int64         \n",
      " 2   AIRLINE              274964 non-null  object        \n",
      " 3   FLIGHT_NUMBER        274964 non-null  int64         \n",
      " 4   TAIL_NUMBER          274225 non-null  object        \n",
      " 5   ORIGIN_AIRPORT       274964 non-null  object        \n",
      " 6   DESTINATION_AIRPORT  274964 non-null  object        \n",
      " 7   SCHEDULED_DEPARTURE  274964 non-null  datetime64[ns]\n",
      " 8   DEPARTURE_TIME       270691 non-null  datetime64[ns]\n",
      " 9   DEPARTURE_DELAY      270719 non-null  float64       \n",
      " 10  TAXI_OUT             270571 non-null  float64       \n",
      " 11  WHEELS_OFF           270538 non-null  datetime64[ns]\n",
      " 12  SCHEDULED_TIME       143600 non-null  datetime64[ns]\n",
      " 13  ELAPSED_TIME         146467 non-null  datetime64[ns]\n",
      " 14  AIR_TIME             163341 non-null  datetime64[ns]\n",
      " 15  DISTANCE             274964 non-null  int64         \n",
      " 16  WHEELS_ON            270291 non-null  datetime64[ns]\n",
      " 17  TAXI_IN              270377 non-null  float64       \n",
      " 18  SCHEDULED_ARRIVAL    274964 non-null  datetime64[ns]\n",
      " 19  ARRIVAL_TIME         270271 non-null  datetime64[ns]\n",
      " 20  ARRIVAL_DELAY        269773 non-null  float64       \n",
      " 21  DIVERTED             274964 non-null  int64         \n",
      " 22  CANCELLED            274964 non-null  int64         \n",
      " 23  CANCELLATION_REASON  274964 non-null  object        \n",
      "dtypes: datetime64[ns](9), float64(4), int64(5), object(6)\n",
      "memory usage: 50.3+ MB\n"
     ]
    }
   ],
   "source": [
    "for column in time_columns:\n",
    "    df[column] = df['DATE'] +' '+ df[column]\n",
    "    df[column] = pd.to_datetime(df[column], format=\"%Y/%m/%d %H:%M\")\n",
    "\n",
    "    \n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f4b17ce",
   "metadata": {},
   "source": [
    "> Now we converted the time columns to Datetime columns ... Now lets convert the `DAY_OF_WEEK` column to be string with the day name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 422,
   "id": "5ba069f2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    Saturday\n",
       "1      Monday\n",
       "2    Saturday\n",
       "3      Monday\n",
       "4      Friday\n",
       "Name: DAY_OF_WEEK, dtype: object"
      ]
     },
     "execution_count": 422,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['DAY_OF_WEEK'] = pd.to_datetime(df.DATE).dt.day_name()\n",
    "df.DAY_OF_WEEK.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1de0992",
   "metadata": {},
   "source": [
    "# Now, lets benifit from the airports dataset.\n",
    "> **Notice, we will not run it ... as in TABLEAU it make problems with longtudes and latitudes.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 423,
   "id": "4736fe82",
   "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>DATE</th>\n",
       "      <th>DAY_OF_WEEK</th>\n",
       "      <th>AIRLINE</th>\n",
       "      <th>FLIGHT_NUMBER</th>\n",
       "      <th>TAIL_NUMBER</th>\n",
       "      <th>ORIGIN_AIRPORT</th>\n",
       "      <th>DESTINATION_AIRPORT</th>\n",
       "      <th>SCHEDULED_DEPARTURE</th>\n",
       "      <th>DEPARTURE_TIME</th>\n",
       "      <th>DEPARTURE_DELAY</th>\n",
       "      <th>TAXI_OUT</th>\n",
       "      <th>WHEELS_OFF</th>\n",
       "      <th>SCHEDULED_TIME</th>\n",
       "      <th>ELAPSED_TIME</th>\n",
       "      <th>AIR_TIME</th>\n",
       "      <th>DISTANCE</th>\n",
       "      <th>WHEELS_ON</th>\n",
       "      <th>TAXI_IN</th>\n",
       "      <th>SCHEDULED_ARRIVAL</th>\n",
       "      <th>ARRIVAL_TIME</th>\n",
       "      <th>ARRIVAL_DELAY</th>\n",
       "      <th>DIVERTED</th>\n",
       "      <th>CANCELLED</th>\n",
       "      <th>CANCELLATION_REASON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015/11/28</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>DL</td>\n",
       "      <td>1590</td>\n",
       "      <td>N971AT</td>\n",
       "      <td>CAE</td>\n",
       "      <td>ATL</td>\n",
       "      <td>2015-11-28 07:00:00</td>\n",
       "      <td>2015-11-28 06:55:00</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2015-11-28 07:12:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2015-11-28 00:39:00</td>\n",
       "      <td>192</td>\n",
       "      <td>2015-11-28 07:51:00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2015-11-28 08:08:00</td>\n",
       "      <td>2015-11-28 07:56:00</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NO-CANCELLATION</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         DATE DAY_OF_WEEK AIRLINE  FLIGHT_NUMBER TAIL_NUMBER ORIGIN_AIRPORT  \\\n",
       "0  2015/11/28    Saturday      DL           1590      N971AT            CAE   \n",
       "\n",
       "  DESTINATION_AIRPORT SCHEDULED_DEPARTURE      DEPARTURE_TIME  \\\n",
       "0                 ATL 2015-11-28 07:00:00 2015-11-28 06:55:00   \n",
       "\n",
       "   DEPARTURE_DELAY  TAXI_OUT          WHEELS_OFF SCHEDULED_TIME ELAPSED_TIME  \\\n",
       "0             -5.0      17.0 2015-11-28 07:12:00            NaT          NaT   \n",
       "\n",
       "             AIR_TIME  DISTANCE           WHEELS_ON  TAXI_IN  \\\n",
       "0 2015-11-28 00:39:00       192 2015-11-28 07:51:00      5.0   \n",
       "\n",
       "    SCHEDULED_ARRIVAL        ARRIVAL_TIME  ARRIVAL_DELAY  DIVERTED  CANCELLED  \\\n",
       "0 2015-11-28 08:08:00 2015-11-28 07:56:00          -12.0         0          0   \n",
       "\n",
       "  CANCELLATION_REASON  \n",
       "0     NO-CANCELLATION  "
      ]
     },
     "execution_count": 423,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 424,
   "id": "26eb1986",
   "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>IATA_CODE</th>\n",
       "      <th>AIRPORT</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTRY</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ABE</td>\n",
       "      <td>Lehigh Valley International Airport</td>\n",
       "      <td>Allentown</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>40.65236</td>\n",
       "      <td>-75.44040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABI</td>\n",
       "      <td>Abilene Regional Airport</td>\n",
       "      <td>Abilene</td>\n",
       "      <td>TX</td>\n",
       "      <td>USA</td>\n",
       "      <td>32.41132</td>\n",
       "      <td>-99.68190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ABQ</td>\n",
       "      <td>Albuquerque International Sunport</td>\n",
       "      <td>Albuquerque</td>\n",
       "      <td>NM</td>\n",
       "      <td>USA</td>\n",
       "      <td>35.04022</td>\n",
       "      <td>-106.60919</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  IATA_CODE                              AIRPORT         CITY STATE COUNTRY  \\\n",
       "0       ABE  Lehigh Valley International Airport    Allentown    PA     USA   \n",
       "1       ABI             Abilene Regional Airport      Abilene    TX     USA   \n",
       "2       ABQ    Albuquerque International Sunport  Albuquerque    NM     USA   \n",
       "\n",
       "   LATITUDE  LONGITUDE  \n",
       "0  40.65236  -75.44040  \n",
       "1  32.41132  -99.68190  \n",
       "2  35.04022 -106.60919  "
      ]
     },
     "execution_count": 424,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_airports.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 425,
   "id": "12d01388",
   "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>DATE</th>\n",
       "      <th>DAY_OF_WEEK</th>\n",
       "      <th>AIRLINE</th>\n",
       "      <th>FLIGHT_NUMBER</th>\n",
       "      <th>TAIL_NUMBER</th>\n",
       "      <th>ORIGIN_AIRPORT</th>\n",
       "      <th>DESTINATION_AIRPORT</th>\n",
       "      <th>SCHEDULED_DEPARTURE</th>\n",
       "      <th>DEPARTURE_TIME</th>\n",
       "      <th>DEPARTURE_DELAY</th>\n",
       "      <th>TAXI_OUT</th>\n",
       "      <th>WHEELS_OFF</th>\n",
       "      <th>SCHEDULED_TIME</th>\n",
       "      <th>ELAPSED_TIME</th>\n",
       "      <th>AIR_TIME</th>\n",
       "      <th>DISTANCE</th>\n",
       "      <th>WHEELS_ON</th>\n",
       "      <th>TAXI_IN</th>\n",
       "      <th>SCHEDULED_ARRIVAL</th>\n",
       "      <th>ARRIVAL_TIME</th>\n",
       "      <th>ARRIVAL_DELAY</th>\n",
       "      <th>DIVERTED</th>\n",
       "      <th>CANCELLED</th>\n",
       "      <th>CANCELLATION_REASON</th>\n",
       "      <th>AIRPORT</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTRY</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015/11/28</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>DL</td>\n",
       "      <td>1590</td>\n",
       "      <td>N971AT</td>\n",
       "      <td>CAE</td>\n",
       "      <td>ATL</td>\n",
       "      <td>2015-11-28 07:00:00</td>\n",
       "      <td>2015-11-28 06:55:00</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2015-11-28 07:12:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2015-11-28 00:39:00</td>\n",
       "      <td>192</td>\n",
       "      <td>2015-11-28 07:51:00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2015-11-28 08:08:00</td>\n",
       "      <td>2015-11-28 07:56:00</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NO-CANCELLATION</td>\n",
       "      <td>Columbia Metropolitan Airport</td>\n",
       "      <td>Columbia</td>\n",
       "      <td>SC</td>\n",
       "      <td>USA</td>\n",
       "      <td>33.93884</td>\n",
       "      <td>-81.11954</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         DATE DAY_OF_WEEK AIRLINE  FLIGHT_NUMBER TAIL_NUMBER ORIGIN_AIRPORT  \\\n",
       "0  2015/11/28    Saturday      DL           1590      N971AT            CAE   \n",
       "\n",
       "  DESTINATION_AIRPORT SCHEDULED_DEPARTURE      DEPARTURE_TIME  \\\n",
       "0                 ATL 2015-11-28 07:00:00 2015-11-28 06:55:00   \n",
       "\n",
       "   DEPARTURE_DELAY  TAXI_OUT          WHEELS_OFF SCHEDULED_TIME ELAPSED_TIME  \\\n",
       "0             -5.0      17.0 2015-11-28 07:12:00            NaT          NaT   \n",
       "\n",
       "             AIR_TIME  DISTANCE           WHEELS_ON  TAXI_IN  \\\n",
       "0 2015-11-28 00:39:00       192 2015-11-28 07:51:00      5.0   \n",
       "\n",
       "    SCHEDULED_ARRIVAL        ARRIVAL_TIME  ARRIVAL_DELAY  DIVERTED  CANCELLED  \\\n",
       "0 2015-11-28 08:08:00 2015-11-28 07:56:00          -12.0         0          0   \n",
       "\n",
       "  CANCELLATION_REASON                        AIRPORT      CITY STATE COUNTRY  \\\n",
       "0     NO-CANCELLATION  Columbia Metropolitan Airport  Columbia    SC     USA   \n",
       "\n",
       "   LATITUDE  LONGITUDE  \n",
       "0  33.93884  -81.11954  "
      ]
     },
     "execution_count": 425,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Lets first compine the Origin airport informations.\n",
    "\n",
    "df = df.merge(df_airports, left_on='ORIGIN_AIRPORT', right_on='IATA_CODE')\n",
    "df.drop(columns=['IATA_CODE'], inplace=True)\n",
    "df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 426,
   "id": "d32184c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def Renaming_Columns(type_of_airport):\n",
    "    '''\n",
    "        INPUT: getting a string to add it to the columns of the merged airports\n",
    "        OUTPUT: NONE - as it compine the data frame inplace.\n",
    "    '''\n",
    "    Columns_Names = df.columns[range(-6, 0, 1)]\n",
    "    for column in Columns_Names:\n",
    "        df.rename(columns={column: column + type_of_airport}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 427,
   "id": "12d429f1",
   "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>DATE</th>\n",
       "      <th>DAY_OF_WEEK</th>\n",
       "      <th>AIRLINE</th>\n",
       "      <th>FLIGHT_NUMBER</th>\n",
       "      <th>TAIL_NUMBER</th>\n",
       "      <th>ORIGIN_AIRPORT</th>\n",
       "      <th>DESTINATION_AIRPORT</th>\n",
       "      <th>SCHEDULED_DEPARTURE</th>\n",
       "      <th>DEPARTURE_TIME</th>\n",
       "      <th>DEPARTURE_DELAY</th>\n",
       "      <th>TAXI_OUT</th>\n",
       "      <th>WHEELS_OFF</th>\n",
       "      <th>SCHEDULED_TIME</th>\n",
       "      <th>ELAPSED_TIME</th>\n",
       "      <th>AIR_TIME</th>\n",
       "      <th>DISTANCE</th>\n",
       "      <th>WHEELS_ON</th>\n",
       "      <th>TAXI_IN</th>\n",
       "      <th>SCHEDULED_ARRIVAL</th>\n",
       "      <th>ARRIVAL_TIME</th>\n",
       "      <th>ARRIVAL_DELAY</th>\n",
       "      <th>DIVERTED</th>\n",
       "      <th>CANCELLED</th>\n",
       "      <th>CANCELLATION_REASON</th>\n",
       "      <th>AIRPORT_ORIGIN</th>\n",
       "      <th>CITY_ORIGIN</th>\n",
       "      <th>STATE_ORIGIN</th>\n",
       "      <th>COUNTRY_ORIGIN</th>\n",
       "      <th>LATITUDE_ORIGIN</th>\n",
       "      <th>LONGITUDE_ORIGIN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015/11/28</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>DL</td>\n",
       "      <td>1590</td>\n",
       "      <td>N971AT</td>\n",
       "      <td>CAE</td>\n",
       "      <td>ATL</td>\n",
       "      <td>2015-11-28 07:00:00</td>\n",
       "      <td>2015-11-28 06:55:00</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2015-11-28 07:12:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2015-11-28 00:39:00</td>\n",
       "      <td>192</td>\n",
       "      <td>2015-11-28 07:51:00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2015-11-28 08:08:00</td>\n",
       "      <td>2015-11-28 07:56:00</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NO-CANCELLATION</td>\n",
       "      <td>Columbia Metropolitan Airport</td>\n",
       "      <td>Columbia</td>\n",
       "      <td>SC</td>\n",
       "      <td>USA</td>\n",
       "      <td>33.93884</td>\n",
       "      <td>-81.11954</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015/5/27</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>EV</td>\n",
       "      <td>5543</td>\n",
       "      <td>N846AS</td>\n",
       "      <td>CAE</td>\n",
       "      <td>ATL</td>\n",
       "      <td>2015-05-27 05:45:00</td>\n",
       "      <td>2015-05-27 05:40:00</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2015-05-27 05:51:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2015-05-27 00:59:00</td>\n",
       "      <td>2015-05-27 00:43:00</td>\n",
       "      <td>192</td>\n",
       "      <td>2015-05-27 06:34:00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2015-05-27 06:45:00</td>\n",
       "      <td>2015-05-27 06:39:00</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NO-CANCELLATION</td>\n",
       "      <td>Columbia Metropolitan Airport</td>\n",
       "      <td>Columbia</td>\n",
       "      <td>SC</td>\n",
       "      <td>USA</td>\n",
       "      <td>33.93884</td>\n",
       "      <td>-81.11954</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         DATE DAY_OF_WEEK AIRLINE  FLIGHT_NUMBER TAIL_NUMBER ORIGIN_AIRPORT  \\\n",
       "0  2015/11/28    Saturday      DL           1590      N971AT            CAE   \n",
       "1   2015/5/27   Wednesday      EV           5543      N846AS            CAE   \n",
       "\n",
       "  DESTINATION_AIRPORT SCHEDULED_DEPARTURE      DEPARTURE_TIME  \\\n",
       "0                 ATL 2015-11-28 07:00:00 2015-11-28 06:55:00   \n",
       "1                 ATL 2015-05-27 05:45:00 2015-05-27 05:40:00   \n",
       "\n",
       "   DEPARTURE_DELAY  TAXI_OUT          WHEELS_OFF SCHEDULED_TIME  \\\n",
       "0             -5.0      17.0 2015-11-28 07:12:00            NaT   \n",
       "1             -5.0      11.0 2015-05-27 05:51:00            NaT   \n",
       "\n",
       "         ELAPSED_TIME            AIR_TIME  DISTANCE           WHEELS_ON  \\\n",
       "0                 NaT 2015-11-28 00:39:00       192 2015-11-28 07:51:00   \n",
       "1 2015-05-27 00:59:00 2015-05-27 00:43:00       192 2015-05-27 06:34:00   \n",
       "\n",
       "   TAXI_IN   SCHEDULED_ARRIVAL        ARRIVAL_TIME  ARRIVAL_DELAY  DIVERTED  \\\n",
       "0      5.0 2015-11-28 08:08:00 2015-11-28 07:56:00          -12.0         0   \n",
       "1      5.0 2015-05-27 06:45:00 2015-05-27 06:39:00           -6.0         0   \n",
       "\n",
       "   CANCELLED CANCELLATION_REASON                 AIRPORT_ORIGIN CITY_ORIGIN  \\\n",
       "0          0     NO-CANCELLATION  Columbia Metropolitan Airport    Columbia   \n",
       "1          0     NO-CANCELLATION  Columbia Metropolitan Airport    Columbia   \n",
       "\n",
       "  STATE_ORIGIN COUNTRY_ORIGIN  LATITUDE_ORIGIN  LONGITUDE_ORIGIN  \n",
       "0           SC            USA         33.93884         -81.11954  \n",
       "1           SC            USA         33.93884         -81.11954  "
      ]
     },
     "execution_count": 427,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Renaming_Columns(\"_ORIGIN\")\n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4728d827",
   "metadata": {},
   "source": [
    "> Now, we can merge the DESTINATION_AIRPORT data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 428,
   "id": "4c3ef089",
   "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>DATE</th>\n",
       "      <th>DAY_OF_WEEK</th>\n",
       "      <th>AIRLINE</th>\n",
       "      <th>FLIGHT_NUMBER</th>\n",
       "      <th>TAIL_NUMBER</th>\n",
       "      <th>ORIGIN_AIRPORT</th>\n",
       "      <th>DESTINATION_AIRPORT</th>\n",
       "      <th>SCHEDULED_DEPARTURE</th>\n",
       "      <th>DEPARTURE_TIME</th>\n",
       "      <th>DEPARTURE_DELAY</th>\n",
       "      <th>TAXI_OUT</th>\n",
       "      <th>WHEELS_OFF</th>\n",
       "      <th>SCHEDULED_TIME</th>\n",
       "      <th>ELAPSED_TIME</th>\n",
       "      <th>AIR_TIME</th>\n",
       "      <th>DISTANCE</th>\n",
       "      <th>WHEELS_ON</th>\n",
       "      <th>TAXI_IN</th>\n",
       "      <th>SCHEDULED_ARRIVAL</th>\n",
       "      <th>ARRIVAL_TIME</th>\n",
       "      <th>ARRIVAL_DELAY</th>\n",
       "      <th>DIVERTED</th>\n",
       "      <th>CANCELLED</th>\n",
       "      <th>CANCELLATION_REASON</th>\n",
       "      <th>AIRPORT_ORIGIN</th>\n",
       "      <th>CITY_ORIGIN</th>\n",
       "      <th>STATE_ORIGIN</th>\n",
       "      <th>COUNTRY_ORIGIN</th>\n",
       "      <th>LATITUDE_ORIGIN</th>\n",
       "      <th>LONGITUDE_ORIGIN</th>\n",
       "      <th>AIRPORT</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTRY</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015/11/28</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>DL</td>\n",
       "      <td>1590</td>\n",
       "      <td>N971AT</td>\n",
       "      <td>CAE</td>\n",
       "      <td>ATL</td>\n",
       "      <td>2015-11-28 07:00:00</td>\n",
       "      <td>2015-11-28 06:55:00</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2015-11-28 07:12:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2015-11-28 00:39:00</td>\n",
       "      <td>192</td>\n",
       "      <td>2015-11-28 07:51:00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2015-11-28 08:08:00</td>\n",
       "      <td>2015-11-28 07:56:00</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NO-CANCELLATION</td>\n",
       "      <td>Columbia Metropolitan Airport</td>\n",
       "      <td>Columbia</td>\n",
       "      <td>SC</td>\n",
       "      <td>USA</td>\n",
       "      <td>33.93884</td>\n",
       "      <td>-81.11954</td>\n",
       "      <td>Hartsfield-Jackson Atlanta International Airport</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>GA</td>\n",
       "      <td>USA</td>\n",
       "      <td>33.64044</td>\n",
       "      <td>-84.42694</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         DATE DAY_OF_WEEK AIRLINE  FLIGHT_NUMBER TAIL_NUMBER ORIGIN_AIRPORT  \\\n",
       "0  2015/11/28    Saturday      DL           1590      N971AT            CAE   \n",
       "\n",
       "  DESTINATION_AIRPORT SCHEDULED_DEPARTURE      DEPARTURE_TIME  \\\n",
       "0                 ATL 2015-11-28 07:00:00 2015-11-28 06:55:00   \n",
       "\n",
       "   DEPARTURE_DELAY  TAXI_OUT          WHEELS_OFF SCHEDULED_TIME ELAPSED_TIME  \\\n",
       "0             -5.0      17.0 2015-11-28 07:12:00            NaT          NaT   \n",
       "\n",
       "             AIR_TIME  DISTANCE           WHEELS_ON  TAXI_IN  \\\n",
       "0 2015-11-28 00:39:00       192 2015-11-28 07:51:00      5.0   \n",
       "\n",
       "    SCHEDULED_ARRIVAL        ARRIVAL_TIME  ARRIVAL_DELAY  DIVERTED  CANCELLED  \\\n",
       "0 2015-11-28 08:08:00 2015-11-28 07:56:00          -12.0         0          0   \n",
       "\n",
       "  CANCELLATION_REASON                 AIRPORT_ORIGIN CITY_ORIGIN STATE_ORIGIN  \\\n",
       "0     NO-CANCELLATION  Columbia Metropolitan Airport    Columbia           SC   \n",
       "\n",
       "  COUNTRY_ORIGIN  LATITUDE_ORIGIN  LONGITUDE_ORIGIN  \\\n",
       "0            USA         33.93884         -81.11954   \n",
       "\n",
       "                                            AIRPORT     CITY STATE COUNTRY  \\\n",
       "0  Hartsfield-Jackson Atlanta International Airport  Atlanta    GA     USA   \n",
       "\n",
       "   LATITUDE  LONGITUDE  \n",
       "0  33.64044  -84.42694  "
      ]
     },
     "execution_count": 428,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Lets compine the DESTINATION_AIRPORT informations.\n",
    "\n",
    "df = df.merge(df_airports, left_on='DESTINATION_AIRPORT', right_on='IATA_CODE')\n",
    "df.drop(columns=['IATA_CODE'], inplace=True)\n",
    "df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 429,
   "id": "0c86713d",
   "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>DATE</th>\n",
       "      <th>DAY_OF_WEEK</th>\n",
       "      <th>AIRLINE</th>\n",
       "      <th>FLIGHT_NUMBER</th>\n",
       "      <th>TAIL_NUMBER</th>\n",
       "      <th>ORIGIN_AIRPORT</th>\n",
       "      <th>DESTINATION_AIRPORT</th>\n",
       "      <th>SCHEDULED_DEPARTURE</th>\n",
       "      <th>DEPARTURE_TIME</th>\n",
       "      <th>DEPARTURE_DELAY</th>\n",
       "      <th>TAXI_OUT</th>\n",
       "      <th>WHEELS_OFF</th>\n",
       "      <th>SCHEDULED_TIME</th>\n",
       "      <th>ELAPSED_TIME</th>\n",
       "      <th>AIR_TIME</th>\n",
       "      <th>DISTANCE</th>\n",
       "      <th>WHEELS_ON</th>\n",
       "      <th>TAXI_IN</th>\n",
       "      <th>SCHEDULED_ARRIVAL</th>\n",
       "      <th>ARRIVAL_TIME</th>\n",
       "      <th>ARRIVAL_DELAY</th>\n",
       "      <th>DIVERTED</th>\n",
       "      <th>CANCELLED</th>\n",
       "      <th>CANCELLATION_REASON</th>\n",
       "      <th>AIRPORT_ORIGIN</th>\n",
       "      <th>CITY_ORIGIN</th>\n",
       "      <th>STATE_ORIGIN</th>\n",
       "      <th>COUNTRY_ORIGIN</th>\n",
       "      <th>LATITUDE_ORIGIN</th>\n",
       "      <th>LONGITUDE_ORIGIN</th>\n",
       "      <th>AIRPORT_DESTINATION</th>\n",
       "      <th>CITY_DESTINATION</th>\n",
       "      <th>STATE_DESTINATION</th>\n",
       "      <th>COUNTRY_DESTINATION</th>\n",
       "      <th>LATITUDE_DESTINATION</th>\n",
       "      <th>LONGITUDE_DESTINATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015/11/28</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>DL</td>\n",
       "      <td>1590</td>\n",
       "      <td>N971AT</td>\n",
       "      <td>CAE</td>\n",
       "      <td>ATL</td>\n",
       "      <td>2015-11-28 07:00:00</td>\n",
       "      <td>2015-11-28 06:55:00</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2015-11-28 07:12:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2015-11-28 00:39:00</td>\n",
       "      <td>192</td>\n",
       "      <td>2015-11-28 07:51:00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2015-11-28 08:08:00</td>\n",
       "      <td>2015-11-28 07:56:00</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NO-CANCELLATION</td>\n",
       "      <td>Columbia Metropolitan Airport</td>\n",
       "      <td>Columbia</td>\n",
       "      <td>SC</td>\n",
       "      <td>USA</td>\n",
       "      <td>33.93884</td>\n",
       "      <td>-81.11954</td>\n",
       "      <td>Hartsfield-Jackson Atlanta International Airport</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>GA</td>\n",
       "      <td>USA</td>\n",
       "      <td>33.64044</td>\n",
       "      <td>-84.42694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015/5/27</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>EV</td>\n",
       "      <td>5543</td>\n",
       "      <td>N846AS</td>\n",
       "      <td>CAE</td>\n",
       "      <td>ATL</td>\n",
       "      <td>2015-05-27 05:45:00</td>\n",
       "      <td>2015-05-27 05:40:00</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2015-05-27 05:51:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2015-05-27 00:59:00</td>\n",
       "      <td>2015-05-27 00:43:00</td>\n",
       "      <td>192</td>\n",
       "      <td>2015-05-27 06:34:00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2015-05-27 06:45:00</td>\n",
       "      <td>2015-05-27 06:39:00</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NO-CANCELLATION</td>\n",
       "      <td>Columbia Metropolitan Airport</td>\n",
       "      <td>Columbia</td>\n",
       "      <td>SC</td>\n",
       "      <td>USA</td>\n",
       "      <td>33.93884</td>\n",
       "      <td>-81.11954</td>\n",
       "      <td>Hartsfield-Jackson Atlanta International Airport</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>GA</td>\n",
       "      <td>USA</td>\n",
       "      <td>33.64044</td>\n",
       "      <td>-84.42694</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         DATE DAY_OF_WEEK AIRLINE  FLIGHT_NUMBER TAIL_NUMBER ORIGIN_AIRPORT  \\\n",
       "0  2015/11/28    Saturday      DL           1590      N971AT            CAE   \n",
       "1   2015/5/27   Wednesday      EV           5543      N846AS            CAE   \n",
       "\n",
       "  DESTINATION_AIRPORT SCHEDULED_DEPARTURE      DEPARTURE_TIME  \\\n",
       "0                 ATL 2015-11-28 07:00:00 2015-11-28 06:55:00   \n",
       "1                 ATL 2015-05-27 05:45:00 2015-05-27 05:40:00   \n",
       "\n",
       "   DEPARTURE_DELAY  TAXI_OUT          WHEELS_OFF SCHEDULED_TIME  \\\n",
       "0             -5.0      17.0 2015-11-28 07:12:00            NaT   \n",
       "1             -5.0      11.0 2015-05-27 05:51:00            NaT   \n",
       "\n",
       "         ELAPSED_TIME            AIR_TIME  DISTANCE           WHEELS_ON  \\\n",
       "0                 NaT 2015-11-28 00:39:00       192 2015-11-28 07:51:00   \n",
       "1 2015-05-27 00:59:00 2015-05-27 00:43:00       192 2015-05-27 06:34:00   \n",
       "\n",
       "   TAXI_IN   SCHEDULED_ARRIVAL        ARRIVAL_TIME  ARRIVAL_DELAY  DIVERTED  \\\n",
       "0      5.0 2015-11-28 08:08:00 2015-11-28 07:56:00          -12.0         0   \n",
       "1      5.0 2015-05-27 06:45:00 2015-05-27 06:39:00           -6.0         0   \n",
       "\n",
       "   CANCELLED CANCELLATION_REASON                 AIRPORT_ORIGIN CITY_ORIGIN  \\\n",
       "0          0     NO-CANCELLATION  Columbia Metropolitan Airport    Columbia   \n",
       "1          0     NO-CANCELLATION  Columbia Metropolitan Airport    Columbia   \n",
       "\n",
       "  STATE_ORIGIN COUNTRY_ORIGIN  LATITUDE_ORIGIN  LONGITUDE_ORIGIN  \\\n",
       "0           SC            USA         33.93884         -81.11954   \n",
       "1           SC            USA         33.93884         -81.11954   \n",
       "\n",
       "                                AIRPORT_DESTINATION CITY_DESTINATION  \\\n",
       "0  Hartsfield-Jackson Atlanta International Airport          Atlanta   \n",
       "1  Hartsfield-Jackson Atlanta International Airport          Atlanta   \n",
       "\n",
       "  STATE_DESTINATION COUNTRY_DESTINATION  LATITUDE_DESTINATION  \\\n",
       "0                GA                 USA              33.64044   \n",
       "1                GA                 USA              33.64044   \n",
       "\n",
       "   LONGITUDE_DESTINATION  \n",
       "0              -84.42694  \n",
       "1              -84.42694  "
      ]
     },
     "execution_count": 429,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Renaming_Columns(\"_DESTINATION\")\n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce98b9ee",
   "metadata": {},
   "source": [
    "> There is no need for the `DESTINATION_AIRPORT` and `ORIGIN_AIRPORT`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 430,
   "id": "b8eff633",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns=['ORIGIN_AIRPORT', 'DESTINATION_AIRPORT'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46f32591",
   "metadata": {},
   "source": [
    "# Now, lets benifit from the airlines dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 431,
   "id": "3e1499e9",
   "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>IATA_CODE</th>\n",
       "      <th>AIRLINE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>UA</td>\n",
       "      <td>United Air Lines Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA</td>\n",
       "      <td>American Airlines Inc.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  IATA_CODE                 AIRLINE\n",
       "0        UA   United Air Lines Inc.\n",
       "1        AA  American Airlines Inc."
      ]
     },
     "execution_count": 431,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_airlines.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 432,
   "id": "1d519d9f",
   "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>DATE</th>\n",
       "      <th>DAY_OF_WEEK</th>\n",
       "      <th>FLIGHT_NUMBER</th>\n",
       "      <th>TAIL_NUMBER</th>\n",
       "      <th>SCHEDULED_DEPARTURE</th>\n",
       "      <th>DEPARTURE_TIME</th>\n",
       "      <th>DEPARTURE_DELAY</th>\n",
       "      <th>TAXI_OUT</th>\n",
       "      <th>WHEELS_OFF</th>\n",
       "      <th>SCHEDULED_TIME</th>\n",
       "      <th>ELAPSED_TIME</th>\n",
       "      <th>AIR_TIME</th>\n",
       "      <th>DISTANCE</th>\n",
       "      <th>WHEELS_ON</th>\n",
       "      <th>TAXI_IN</th>\n",
       "      <th>SCHEDULED_ARRIVAL</th>\n",
       "      <th>ARRIVAL_TIME</th>\n",
       "      <th>ARRIVAL_DELAY</th>\n",
       "      <th>DIVERTED</th>\n",
       "      <th>CANCELLED</th>\n",
       "      <th>CANCELLATION_REASON</th>\n",
       "      <th>AIRPORT_ORIGIN</th>\n",
       "      <th>CITY_ORIGIN</th>\n",
       "      <th>STATE_ORIGIN</th>\n",
       "      <th>COUNTRY_ORIGIN</th>\n",
       "      <th>LATITUDE_ORIGIN</th>\n",
       "      <th>LONGITUDE_ORIGIN</th>\n",
       "      <th>AIRPORT_DESTINATION</th>\n",
       "      <th>CITY_DESTINATION</th>\n",
       "      <th>STATE_DESTINATION</th>\n",
       "      <th>COUNTRY_DESTINATION</th>\n",
       "      <th>LATITUDE_DESTINATION</th>\n",
       "      <th>LONGITUDE_DESTINATION</th>\n",
       "      <th>AIRLINE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015/11/28</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>1590</td>\n",
       "      <td>N971AT</td>\n",
       "      <td>2015-11-28 07:00:00</td>\n",
       "      <td>2015-11-28 06:55:00</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2015-11-28 07:12:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2015-11-28 00:39:00</td>\n",
       "      <td>192</td>\n",
       "      <td>2015-11-28 07:51:00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2015-11-28 08:08:00</td>\n",
       "      <td>2015-11-28 07:56:00</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NO-CANCELLATION</td>\n",
       "      <td>Columbia Metropolitan Airport</td>\n",
       "      <td>Columbia</td>\n",
       "      <td>SC</td>\n",
       "      <td>USA</td>\n",
       "      <td>33.93884</td>\n",
       "      <td>-81.11954</td>\n",
       "      <td>Hartsfield-Jackson Atlanta International Airport</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>GA</td>\n",
       "      <td>USA</td>\n",
       "      <td>33.64044</td>\n",
       "      <td>-84.42694</td>\n",
       "      <td>Delta Air Lines Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015/4/28</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>1435</td>\n",
       "      <td>N909DE</td>\n",
       "      <td>2015-04-28 07:00:00</td>\n",
       "      <td>2015-04-28 06:55:00</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2015-04-28 07:08:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2015-04-28 00:58:00</td>\n",
       "      <td>2015-04-28 00:40:00</td>\n",
       "      <td>192</td>\n",
       "      <td>2015-04-28 07:48:00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2015-04-28 08:08:00</td>\n",
       "      <td>2015-04-28 07:53:00</td>\n",
       "      <td>-15.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NO-CANCELLATION</td>\n",
       "      <td>Columbia Metropolitan Airport</td>\n",
       "      <td>Columbia</td>\n",
       "      <td>SC</td>\n",
       "      <td>USA</td>\n",
       "      <td>33.93884</td>\n",
       "      <td>-81.11954</td>\n",
       "      <td>Hartsfield-Jackson Atlanta International Airport</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>GA</td>\n",
       "      <td>USA</td>\n",
       "      <td>33.64044</td>\n",
       "      <td>-84.42694</td>\n",
       "      <td>Delta Air Lines Inc.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         DATE DAY_OF_WEEK  FLIGHT_NUMBER TAIL_NUMBER SCHEDULED_DEPARTURE  \\\n",
       "0  2015/11/28    Saturday           1590      N971AT 2015-11-28 07:00:00   \n",
       "1   2015/4/28     Tuesday           1435      N909DE 2015-04-28 07:00:00   \n",
       "\n",
       "       DEPARTURE_TIME  DEPARTURE_DELAY  TAXI_OUT          WHEELS_OFF  \\\n",
       "0 2015-11-28 06:55:00             -5.0      17.0 2015-11-28 07:12:00   \n",
       "1 2015-04-28 06:55:00             -5.0      13.0 2015-04-28 07:08:00   \n",
       "\n",
       "  SCHEDULED_TIME        ELAPSED_TIME            AIR_TIME  DISTANCE  \\\n",
       "0            NaT                 NaT 2015-11-28 00:39:00       192   \n",
       "1            NaT 2015-04-28 00:58:00 2015-04-28 00:40:00       192   \n",
       "\n",
       "            WHEELS_ON  TAXI_IN   SCHEDULED_ARRIVAL        ARRIVAL_TIME  \\\n",
       "0 2015-11-28 07:51:00      5.0 2015-11-28 08:08:00 2015-11-28 07:56:00   \n",
       "1 2015-04-28 07:48:00      5.0 2015-04-28 08:08:00 2015-04-28 07:53:00   \n",
       "\n",
       "   ARRIVAL_DELAY  DIVERTED  CANCELLED CANCELLATION_REASON  \\\n",
       "0          -12.0         0          0     NO-CANCELLATION   \n",
       "1          -15.0         0          0     NO-CANCELLATION   \n",
       "\n",
       "                  AIRPORT_ORIGIN CITY_ORIGIN STATE_ORIGIN COUNTRY_ORIGIN  \\\n",
       "0  Columbia Metropolitan Airport    Columbia           SC            USA   \n",
       "1  Columbia Metropolitan Airport    Columbia           SC            USA   \n",
       "\n",
       "   LATITUDE_ORIGIN  LONGITUDE_ORIGIN  \\\n",
       "0         33.93884         -81.11954   \n",
       "1         33.93884         -81.11954   \n",
       "\n",
       "                                AIRPORT_DESTINATION CITY_DESTINATION  \\\n",
       "0  Hartsfield-Jackson Atlanta International Airport          Atlanta   \n",
       "1  Hartsfield-Jackson Atlanta International Airport          Atlanta   \n",
       "\n",
       "  STATE_DESTINATION COUNTRY_DESTINATION  LATITUDE_DESTINATION  \\\n",
       "0                GA                 USA              33.64044   \n",
       "1                GA                 USA              33.64044   \n",
       "\n",
       "   LONGITUDE_DESTINATION               AIRLINE  \n",
       "0              -84.42694  Delta Air Lines Inc.  \n",
       "1              -84.42694  Delta Air Lines Inc.  "
      ]
     },
     "execution_count": 432,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.merge(df_airlines, left_on='AIRLINE', right_on='IATA_CODE', suffixes=('_x', ''))\n",
    "df.drop(columns=['IATA_CODE', 'AIRLINE_x'], inplace=True)\n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d2d848a",
   "metadata": {},
   "source": [
    "> ### Now, we can say that our work in cleaning the data is done. Now we will go to TABLEAU to analyze the data \n",
    "\n",
    "> **You can find the repo [here](https://github.com/mo7amed7assan1911/Flight_Delays_USA_TABLEAU)**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 433,
   "id": "4bb54489",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('flights_One_DataSet.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}