2.merge_allstations_train_f.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "323cf743",
   "metadata": {},
   "source": [
    "## Merging train dataset with all stations dataset to get the weather station IDs of the origin and departure airports"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24562104",
   "metadata": {},
   "source": [
    "Importing the datasets and required librares"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "81378105",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "9d1f4b77",
   "metadata": {},
   "outputs": [],
   "source": [
    "train=pd.read_csv('C:/Users/kiran/OneDrive/Desktop/final/train_with_target.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "70be5522",
   "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</th>\n",
       "      <th>FlightNumber</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>DayofMonth</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>ScheduledDepTime</th>\n",
       "      <th>ScheduledArrTime</th>\n",
       "      <th>ScheduledTravelTime</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Distance</th>\n",
       "      <th>ActualArrivalTimeStamp</th>\n",
       "      <th>result</th>\n",
       "      <th>delay</th>\n",
       "      <th>delay_in_seconds</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>BOS_CLE_29089</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1154</td>\n",
       "      <td>114</td>\n",
       "      <td>BOS</td>\n",
       "      <td>CLE</td>\n",
       "      <td>563</td>\n",
       "      <td>2004-09-20 11:54:00</td>\n",
       "      <td>2004-09-20 11:54:00</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>CLE_ATL_44346</td>\n",
       "      <td>2004</td>\n",
       "      <td>11</td>\n",
       "      <td>19</td>\n",
       "      <td>5</td>\n",
       "      <td>1440</td>\n",
       "      <td>1634</td>\n",
       "      <td>114</td>\n",
       "      <td>CLE</td>\n",
       "      <td>ATL</td>\n",
       "      <td>554</td>\n",
       "      <td>2004-11-19 18:55:00</td>\n",
       "      <td>2004-11-19 16:34:00</td>\n",
       "      <td>0 days 02:21:00</td>\n",
       "      <td>8460</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>SNA_LAS_44378</td>\n",
       "      <td>2004</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1800</td>\n",
       "      <td>1902</td>\n",
       "      <td>62</td>\n",
       "      <td>SNA</td>\n",
       "      <td>LAS</td>\n",
       "      <td>226</td>\n",
       "      <td>2004-05-04 19:02:00</td>\n",
       "      <td>2004-05-04 19:02:00</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>CLT_TPA_51502</td>\n",
       "      <td>2004</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>1400</td>\n",
       "      <td>1533</td>\n",
       "      <td>93</td>\n",
       "      <td>CLT</td>\n",
       "      <td>TPA</td>\n",
       "      <td>508</td>\n",
       "      <td>2004-07-06 15:33:00</td>\n",
       "      <td>2004-07-06 15:33:00</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>MSP_ORD_44884</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>23</td>\n",
       "      <td>4</td>\n",
       "      <td>830</td>\n",
       "      <td>946</td>\n",
       "      <td>76</td>\n",
       "      <td>MSP</td>\n",
       "      <td>ORD</td>\n",
       "      <td>334</td>\n",
       "      <td>2004-09-23 09:46:00</td>\n",
       "      <td>2004-09-23 09:46:00</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7856</th>\n",
       "      <td>7856</td>\n",
       "      <td>IAH_DEN_53058</td>\n",
       "      <td>2004</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>4</td>\n",
       "      <td>735</td>\n",
       "      <td>900</td>\n",
       "      <td>145</td>\n",
       "      <td>IAH</td>\n",
       "      <td>DEN</td>\n",
       "      <td>861</td>\n",
       "      <td>2004-05-27 09:00:00</td>\n",
       "      <td>2004-05-27 09:00:00</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7857</th>\n",
       "      <td>7857</td>\n",
       "      <td>EWR_MEM_40118</td>\n",
       "      <td>2004</td>\n",
       "      <td>11</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "      <td>1155</td>\n",
       "      <td>1354</td>\n",
       "      <td>179</td>\n",
       "      <td>EWR</td>\n",
       "      <td>MEM</td>\n",
       "      <td>946</td>\n",
       "      <td>2004-11-29 13:54:00</td>\n",
       "      <td>2004-11-29 13:54:00</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7858</th>\n",
       "      <td>7858</td>\n",
       "      <td>IAD_GSO_49378</td>\n",
       "      <td>2004</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>1250</td>\n",
       "      <td>1405</td>\n",
       "      <td>75</td>\n",
       "      <td>IAD</td>\n",
       "      <td>GSO</td>\n",
       "      <td>239</td>\n",
       "      <td>2004-05-11 14:05:00</td>\n",
       "      <td>2004-05-11 14:05:00</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7859</th>\n",
       "      <td>7859</td>\n",
       "      <td>SLC_GEG_25787</td>\n",
       "      <td>2004</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>1130</td>\n",
       "      <td>1214</td>\n",
       "      <td>104</td>\n",
       "      <td>SLC</td>\n",
       "      <td>GEG</td>\n",
       "      <td>546</td>\n",
       "      <td>2004-01-04 12:14:00</td>\n",
       "      <td>2004-01-04 12:14:00</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7860</th>\n",
       "      <td>7860</td>\n",
       "      <td>FLL_CLE_41967</td>\n",
       "      <td>2004</td>\n",
       "      <td>11</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>1325</td>\n",
       "      <td>1613</td>\n",
       "      <td>168</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLE</td>\n",
       "      <td>1062</td>\n",
       "      <td>2004-11-06 17:56:00</td>\n",
       "      <td>2004-11-06 16:13:00</td>\n",
       "      <td>0 days 01:43:00</td>\n",
       "      <td>6180</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7861 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Unnamed: 0   FlightNumber  Year  Month  DayofMonth  DayOfWeek  \\\n",
       "0              0  BOS_CLE_29089  2004      9          20          1   \n",
       "1              1  CLE_ATL_44346  2004     11          19          5   \n",
       "2              2  SNA_LAS_44378  2004      5           4          2   \n",
       "3              3  CLT_TPA_51502  2004      7           6          2   \n",
       "4              4  MSP_ORD_44884  2004      9          23          4   \n",
       "...          ...            ...   ...    ...         ...        ...   \n",
       "7856        7856  IAH_DEN_53058  2004      5          27          4   \n",
       "7857        7857  EWR_MEM_40118  2004     11          29          1   \n",
       "7858        7858  IAD_GSO_49378  2004      5          11          2   \n",
       "7859        7859  SLC_GEG_25787  2004      1           4          7   \n",
       "7860        7860  FLL_CLE_41967  2004     11           6          6   \n",
       "\n",
       "      ScheduledDepTime  ScheduledArrTime  ScheduledTravelTime Origin  \\\n",
       "0                 1000              1154                  114    BOS   \n",
       "1                 1440              1634                  114    CLE   \n",
       "2                 1800              1902                   62    SNA   \n",
       "3                 1400              1533                   93    CLT   \n",
       "4                  830               946                   76    MSP   \n",
       "...                ...               ...                  ...    ...   \n",
       "7856               735               900                  145    IAH   \n",
       "7857              1155              1354                  179    EWR   \n",
       "7858              1250              1405                   75    IAD   \n",
       "7859              1130              1214                  104    SLC   \n",
       "7860              1325              1613                  168    FLL   \n",
       "\n",
       "     Destination  Distance ActualArrivalTimeStamp               result  \\\n",
       "0            CLE       563    2004-09-20 11:54:00  2004-09-20 11:54:00   \n",
       "1            ATL       554    2004-11-19 18:55:00  2004-11-19 16:34:00   \n",
       "2            LAS       226    2004-05-04 19:02:00  2004-05-04 19:02:00   \n",
       "3            TPA       508    2004-07-06 15:33:00  2004-07-06 15:33:00   \n",
       "4            ORD       334    2004-09-23 09:46:00  2004-09-23 09:46:00   \n",
       "...          ...       ...                    ...                  ...   \n",
       "7856         DEN       861    2004-05-27 09:00:00  2004-05-27 09:00:00   \n",
       "7857         MEM       946    2004-11-29 13:54:00  2004-11-29 13:54:00   \n",
       "7858         GSO       239    2004-05-11 14:05:00  2004-05-11 14:05:00   \n",
       "7859         GEG       546    2004-01-04 12:14:00  2004-01-04 12:14:00   \n",
       "7860         CLE      1062    2004-11-06 17:56:00  2004-11-06 16:13:00   \n",
       "\n",
       "                delay  delay_in_seconds  target  \n",
       "0     0 days 00:00:00                 0       2  \n",
       "1     0 days 02:21:00              8460       1  \n",
       "2     0 days 00:00:00                 0       2  \n",
       "3     0 days 00:00:00                 0       2  \n",
       "4     0 days 00:00:00                 0       2  \n",
       "...               ...               ...     ...  \n",
       "7856  0 days 00:00:00                 0       2  \n",
       "7857  0 days 00:00:00                 0       2  \n",
       "7858  0 days 00:00:00                 0       2  \n",
       "7859  0 days 00:00:00                 0       2  \n",
       "7860  0 days 01:43:00              6180       1  \n",
       "\n",
       "[7861 rows x 17 columns]"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "c6d26dfb",
   "metadata": {},
   "outputs": [],
   "source": [
    "train = train.drop('Unnamed: 0', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "cfaad728",
   "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>FlightNumber</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>DayofMonth</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>ScheduledDepTime</th>\n",
       "      <th>ScheduledArrTime</th>\n",
       "      <th>ScheduledTravelTime</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Distance</th>\n",
       "      <th>ActualArrivalTimeStamp</th>\n",
       "      <th>result</th>\n",
       "      <th>delay</th>\n",
       "      <th>delay_in_seconds</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BOS_CLE_29089</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1154</td>\n",
       "      <td>114</td>\n",
       "      <td>BOS</td>\n",
       "      <td>CLE</td>\n",
       "      <td>563</td>\n",
       "      <td>2004-09-20 11:54:00</td>\n",
       "      <td>2004-09-20 11:54:00</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CLE_ATL_44346</td>\n",
       "      <td>2004</td>\n",
       "      <td>11</td>\n",
       "      <td>19</td>\n",
       "      <td>5</td>\n",
       "      <td>1440</td>\n",
       "      <td>1634</td>\n",
       "      <td>114</td>\n",
       "      <td>CLE</td>\n",
       "      <td>ATL</td>\n",
       "      <td>554</td>\n",
       "      <td>2004-11-19 18:55:00</td>\n",
       "      <td>2004-11-19 16:34:00</td>\n",
       "      <td>0 days 02:21:00</td>\n",
       "      <td>8460</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SNA_LAS_44378</td>\n",
       "      <td>2004</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1800</td>\n",
       "      <td>1902</td>\n",
       "      <td>62</td>\n",
       "      <td>SNA</td>\n",
       "      <td>LAS</td>\n",
       "      <td>226</td>\n",
       "      <td>2004-05-04 19:02:00</td>\n",
       "      <td>2004-05-04 19:02:00</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CLT_TPA_51502</td>\n",
       "      <td>2004</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>1400</td>\n",
       "      <td>1533</td>\n",
       "      <td>93</td>\n",
       "      <td>CLT</td>\n",
       "      <td>TPA</td>\n",
       "      <td>508</td>\n",
       "      <td>2004-07-06 15:33:00</td>\n",
       "      <td>2004-07-06 15:33:00</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MSP_ORD_44884</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>23</td>\n",
       "      <td>4</td>\n",
       "      <td>830</td>\n",
       "      <td>946</td>\n",
       "      <td>76</td>\n",
       "      <td>MSP</td>\n",
       "      <td>ORD</td>\n",
       "      <td>334</td>\n",
       "      <td>2004-09-23 09:46:00</td>\n",
       "      <td>2004-09-23 09:46:00</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7856</th>\n",
       "      <td>IAH_DEN_53058</td>\n",
       "      <td>2004</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>4</td>\n",
       "      <td>735</td>\n",
       "      <td>900</td>\n",
       "      <td>145</td>\n",
       "      <td>IAH</td>\n",
       "      <td>DEN</td>\n",
       "      <td>861</td>\n",
       "      <td>2004-05-27 09:00:00</td>\n",
       "      <td>2004-05-27 09:00:00</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7857</th>\n",
       "      <td>EWR_MEM_40118</td>\n",
       "      <td>2004</td>\n",
       "      <td>11</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "      <td>1155</td>\n",
       "      <td>1354</td>\n",
       "      <td>179</td>\n",
       "      <td>EWR</td>\n",
       "      <td>MEM</td>\n",
       "      <td>946</td>\n",
       "      <td>2004-11-29 13:54:00</td>\n",
       "      <td>2004-11-29 13:54:00</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7858</th>\n",
       "      <td>IAD_GSO_49378</td>\n",
       "      <td>2004</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>1250</td>\n",
       "      <td>1405</td>\n",
       "      <td>75</td>\n",
       "      <td>IAD</td>\n",
       "      <td>GSO</td>\n",
       "      <td>239</td>\n",
       "      <td>2004-05-11 14:05:00</td>\n",
       "      <td>2004-05-11 14:05:00</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7859</th>\n",
       "      <td>SLC_GEG_25787</td>\n",
       "      <td>2004</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>1130</td>\n",
       "      <td>1214</td>\n",
       "      <td>104</td>\n",
       "      <td>SLC</td>\n",
       "      <td>GEG</td>\n",
       "      <td>546</td>\n",
       "      <td>2004-01-04 12:14:00</td>\n",
       "      <td>2004-01-04 12:14:00</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7860</th>\n",
       "      <td>FLL_CLE_41967</td>\n",
       "      <td>2004</td>\n",
       "      <td>11</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>1325</td>\n",
       "      <td>1613</td>\n",
       "      <td>168</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLE</td>\n",
       "      <td>1062</td>\n",
       "      <td>2004-11-06 17:56:00</td>\n",
       "      <td>2004-11-06 16:13:00</td>\n",
       "      <td>0 days 01:43:00</td>\n",
       "      <td>6180</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7861 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       FlightNumber  Year  Month  DayofMonth  DayOfWeek  ScheduledDepTime  \\\n",
       "0     BOS_CLE_29089  2004      9          20          1              1000   \n",
       "1     CLE_ATL_44346  2004     11          19          5              1440   \n",
       "2     SNA_LAS_44378  2004      5           4          2              1800   \n",
       "3     CLT_TPA_51502  2004      7           6          2              1400   \n",
       "4     MSP_ORD_44884  2004      9          23          4               830   \n",
       "...             ...   ...    ...         ...        ...               ...   \n",
       "7856  IAH_DEN_53058  2004      5          27          4               735   \n",
       "7857  EWR_MEM_40118  2004     11          29          1              1155   \n",
       "7858  IAD_GSO_49378  2004      5          11          2              1250   \n",
       "7859  SLC_GEG_25787  2004      1           4          7              1130   \n",
       "7860  FLL_CLE_41967  2004     11           6          6              1325   \n",
       "\n",
       "      ScheduledArrTime  ScheduledTravelTime Origin Destination  Distance  \\\n",
       "0                 1154                  114    BOS         CLE       563   \n",
       "1                 1634                  114    CLE         ATL       554   \n",
       "2                 1902                   62    SNA         LAS       226   \n",
       "3                 1533                   93    CLT         TPA       508   \n",
       "4                  946                   76    MSP         ORD       334   \n",
       "...                ...                  ...    ...         ...       ...   \n",
       "7856               900                  145    IAH         DEN       861   \n",
       "7857              1354                  179    EWR         MEM       946   \n",
       "7858              1405                   75    IAD         GSO       239   \n",
       "7859              1214                  104    SLC         GEG       546   \n",
       "7860              1613                  168    FLL         CLE      1062   \n",
       "\n",
       "     ActualArrivalTimeStamp               result            delay  \\\n",
       "0       2004-09-20 11:54:00  2004-09-20 11:54:00  0 days 00:00:00   \n",
       "1       2004-11-19 18:55:00  2004-11-19 16:34:00  0 days 02:21:00   \n",
       "2       2004-05-04 19:02:00  2004-05-04 19:02:00  0 days 00:00:00   \n",
       "3       2004-07-06 15:33:00  2004-07-06 15:33:00  0 days 00:00:00   \n",
       "4       2004-09-23 09:46:00  2004-09-23 09:46:00  0 days 00:00:00   \n",
       "...                     ...                  ...              ...   \n",
       "7856    2004-05-27 09:00:00  2004-05-27 09:00:00  0 days 00:00:00   \n",
       "7857    2004-11-29 13:54:00  2004-11-29 13:54:00  0 days 00:00:00   \n",
       "7858    2004-05-11 14:05:00  2004-05-11 14:05:00  0 days 00:00:00   \n",
       "7859    2004-01-04 12:14:00  2004-01-04 12:14:00  0 days 00:00:00   \n",
       "7860    2004-11-06 17:56:00  2004-11-06 16:13:00  0 days 01:43:00   \n",
       "\n",
       "      delay_in_seconds  target  \n",
       "0                    0       2  \n",
       "1                 8460       1  \n",
       "2                    0       2  \n",
       "3                    0       2  \n",
       "4                    0       2  \n",
       "...                ...     ...  \n",
       "7856                 0       2  \n",
       "7857                 0       2  \n",
       "7858                 0       2  \n",
       "7859                 0       2  \n",
       "7860              6180       1  \n",
       "\n",
       "[7861 rows x 16 columns]"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "2d909bfe",
   "metadata": {},
   "outputs": [],
   "source": [
    "allstationsdf=pd.read_csv('C:/Users/kiran/OneDrive/Desktop/hacakthon_final/ProblemStatement_Data&Instructions-1616503373207/DataForPHD_Forall/20210322_weatherdatatoconsider/AllStationsData_PHD.txt', delimiter='|')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "9124b674",
   "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>WeatherStationID</th>\n",
       "      <th>AirportID</th>\n",
       "      <th>GroundHeight</th>\n",
       "      <th>StationHeight</th>\n",
       "      <th>BarometerHeight</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>TimeZone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3011</td>\n",
       "      <td>TEX</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>37.57</td>\n",
       "      <td>-107.55</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3012</td>\n",
       "      <td>SKX</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>36.28</td>\n",
       "      <td>-105.40</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3013</td>\n",
       "      <td>LAA</td>\n",
       "      <td>3683</td>\n",
       "      <td>3703</td>\n",
       "      <td>3675</td>\n",
       "      <td>38.04</td>\n",
       "      <td>-102.41</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3014</td>\n",
       "      <td>4SL</td>\n",
       "      <td>0</td>\n",
       "      <td>6909</td>\n",
       "      <td>0</td>\n",
       "      <td>35.47</td>\n",
       "      <td>-107.14</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3016</td>\n",
       "      <td>RIL</td>\n",
       "      <td>5503</td>\n",
       "      <td>5544</td>\n",
       "      <td>5506</td>\n",
       "      <td>39.32</td>\n",
       "      <td>-107.44</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1739</th>\n",
       "      <td>94997</td>\n",
       "      <td>ADC</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>46.27</td>\n",
       "      <td>-95.13</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1740</th>\n",
       "      <td>94998</td>\n",
       "      <td>ADU</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>41.42</td>\n",
       "      <td>-94.55</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1741</th>\n",
       "      <td>94999</td>\n",
       "      <td>AIT</td>\n",
       "      <td>0</td>\n",
       "      <td>1204</td>\n",
       "      <td>0</td>\n",
       "      <td>46.33</td>\n",
       "      <td>-93.41</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1742</th>\n",
       "      <td>96401</td>\n",
       "      <td>IEC</td>\n",
       "      <td>0</td>\n",
       "      <td>1350</td>\n",
       "      <td>0</td>\n",
       "      <td>62.50</td>\n",
       "      <td>-149.55</td>\n",
       "      <td>+9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1743</th>\n",
       "      <td>96402</td>\n",
       "      <td>JVM</td>\n",
       "      <td>0</td>\n",
       "      <td>870</td>\n",
       "      <td>0</td>\n",
       "      <td>61.44</td>\n",
       "      <td>-148.56</td>\n",
       "      <td>+9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1744 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      WeatherStationID AirportID  GroundHeight  StationHeight  \\\n",
       "0                 3011       TEX             0              0   \n",
       "1                 3012       SKX             0              0   \n",
       "2                 3013       LAA          3683           3703   \n",
       "3                 3014       4SL             0           6909   \n",
       "4                 3016       RIL          5503           5544   \n",
       "...                ...       ...           ...            ...   \n",
       "1739             94997       ADC             0              0   \n",
       "1740             94998       ADU             0              0   \n",
       "1741             94999       AIT             0           1204   \n",
       "1742             96401       IEC             0           1350   \n",
       "1743             96402       JVM             0            870   \n",
       "\n",
       "      BarometerHeight  Latitude  Longitude TimeZone  \n",
       "0                   0     37.57    -107.55       +7  \n",
       "1                   0     36.28    -105.40       +7  \n",
       "2                3675     38.04    -102.41       +7  \n",
       "3                   0     35.47    -107.14       +7  \n",
       "4                5506     39.32    -107.44       +7  \n",
       "...               ...       ...        ...      ...  \n",
       "1739                0     46.27     -95.13       +6  \n",
       "1740                0     41.42     -94.55       +6  \n",
       "1741                0     46.33     -93.41       +6  \n",
       "1742                0     62.50    -149.55       +9  \n",
       "1743                0     61.44    -148.56       +9  \n",
       "\n",
       "[1744 rows x 8 columns]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allstationsdf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14bd1497",
   "metadata": {},
   "source": [
    "### Merging the datasets which have the same columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "7775a5b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "### Merging together the train dataset with the allstationsdf and matching it with origin AirportID\n",
    "\n",
    "origin_df=pd.merge(train, allstationsdf, left_on='Origin', right_on='AirportID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "9cfd0b1a",
   "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>FlightNumber</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>DayofMonth</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>ScheduledDepTime</th>\n",
       "      <th>ScheduledArrTime</th>\n",
       "      <th>ScheduledTravelTime</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>...</th>\n",
       "      <th>delay_in_seconds</th>\n",
       "      <th>target</th>\n",
       "      <th>WeatherStationID</th>\n",
       "      <th>AirportID</th>\n",
       "      <th>GroundHeight</th>\n",
       "      <th>StationHeight</th>\n",
       "      <th>BarometerHeight</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>TimeZone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BOS_CLE_29089</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1154</td>\n",
       "      <td>114</td>\n",
       "      <td>BOS</td>\n",
       "      <td>CLE</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14739</td>\n",
       "      <td>BOS</td>\n",
       "      <td>19</td>\n",
       "      <td>30</td>\n",
       "      <td>180</td>\n",
       "      <td>42.22</td>\n",
       "      <td>-71.01</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BOS_OAK_55049</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>820</td>\n",
       "      <td>1200</td>\n",
       "      <td>400</td>\n",
       "      <td>BOS</td>\n",
       "      <td>OAK</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14739</td>\n",
       "      <td>BOS</td>\n",
       "      <td>19</td>\n",
       "      <td>30</td>\n",
       "      <td>180</td>\n",
       "      <td>42.22</td>\n",
       "      <td>-71.01</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BOS_RSW_47586</td>\n",
       "      <td>2004</td>\n",
       "      <td>3</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>700</td>\n",
       "      <td>1036</td>\n",
       "      <td>216</td>\n",
       "      <td>BOS</td>\n",
       "      <td>RSW</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14739</td>\n",
       "      <td>BOS</td>\n",
       "      <td>19</td>\n",
       "      <td>30</td>\n",
       "      <td>180</td>\n",
       "      <td>42.22</td>\n",
       "      <td>-71.01</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BOS_PIT_49759</td>\n",
       "      <td>2004</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>845</td>\n",
       "      <td>1031</td>\n",
       "      <td>106</td>\n",
       "      <td>BOS</td>\n",
       "      <td>PIT</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14739</td>\n",
       "      <td>BOS</td>\n",
       "      <td>19</td>\n",
       "      <td>30</td>\n",
       "      <td>180</td>\n",
       "      <td>42.22</td>\n",
       "      <td>-71.01</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BOS_DCA_32791</td>\n",
       "      <td>2004</td>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "      <td>4</td>\n",
       "      <td>1300</td>\n",
       "      <td>1430</td>\n",
       "      <td>90</td>\n",
       "      <td>BOS</td>\n",
       "      <td>DCA</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14739</td>\n",
       "      <td>BOS</td>\n",
       "      <td>19</td>\n",
       "      <td>30</td>\n",
       "      <td>180</td>\n",
       "      <td>42.22</td>\n",
       "      <td>-71.01</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    FlightNumber  Year  Month  DayofMonth  DayOfWeek  ScheduledDepTime  \\\n",
       "0  BOS_CLE_29089  2004      9          20          1              1000   \n",
       "1  BOS_OAK_55049  2004      9          10          5               820   \n",
       "2  BOS_RSW_47586  2004      3          22          1               700   \n",
       "3  BOS_PIT_49759  2004      5           1          6               845   \n",
       "4  BOS_DCA_32791  2004      7          15          4              1300   \n",
       "\n",
       "   ScheduledArrTime  ScheduledTravelTime Origin Destination  ...  \\\n",
       "0              1154                  114    BOS         CLE  ...   \n",
       "1              1200                  400    BOS         OAK  ...   \n",
       "2              1036                  216    BOS         RSW  ...   \n",
       "3              1031                  106    BOS         PIT  ...   \n",
       "4              1430                   90    BOS         DCA  ...   \n",
       "\n",
       "   delay_in_seconds target WeatherStationID AirportID  GroundHeight  \\\n",
       "0                 0      2            14739       BOS            19   \n",
       "1                 0      2            14739       BOS            19   \n",
       "2                 0      2            14739       BOS            19   \n",
       "3                 0      2            14739       BOS            19   \n",
       "4                 0      2            14739       BOS            19   \n",
       "\n",
       "   StationHeight  BarometerHeight Latitude  Longitude  TimeZone  \n",
       "0             30              180    42.22     -71.01        +5  \n",
       "1             30              180    42.22     -71.01        +5  \n",
       "2             30              180    42.22     -71.01        +5  \n",
       "3             30              180    42.22     -71.01        +5  \n",
       "4             30              180    42.22     -71.01        +5  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "origin_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "000c06ff",
   "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>FlightNumber</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>DayofMonth</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>ScheduledDepTime</th>\n",
       "      <th>ScheduledArrTime</th>\n",
       "      <th>ScheduledTravelTime</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>...</th>\n",
       "      <th>delay_in_seconds</th>\n",
       "      <th>target</th>\n",
       "      <th>WeatherStationID</th>\n",
       "      <th>AirportID</th>\n",
       "      <th>GroundHeight</th>\n",
       "      <th>StationHeight</th>\n",
       "      <th>BarometerHeight</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>TimeZone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BOS_CLE_29089</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1154</td>\n",
       "      <td>114</td>\n",
       "      <td>BOS</td>\n",
       "      <td>CLE</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14739</td>\n",
       "      <td>BOS</td>\n",
       "      <td>19</td>\n",
       "      <td>30</td>\n",
       "      <td>180</td>\n",
       "      <td>42.22</td>\n",
       "      <td>-71.01</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BOS_OAK_55049</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>820</td>\n",
       "      <td>1200</td>\n",
       "      <td>400</td>\n",
       "      <td>BOS</td>\n",
       "      <td>OAK</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14739</td>\n",
       "      <td>BOS</td>\n",
       "      <td>19</td>\n",
       "      <td>30</td>\n",
       "      <td>180</td>\n",
       "      <td>42.22</td>\n",
       "      <td>-71.01</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BOS_RSW_47586</td>\n",
       "      <td>2004</td>\n",
       "      <td>3</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>700</td>\n",
       "      <td>1036</td>\n",
       "      <td>216</td>\n",
       "      <td>BOS</td>\n",
       "      <td>RSW</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14739</td>\n",
       "      <td>BOS</td>\n",
       "      <td>19</td>\n",
       "      <td>30</td>\n",
       "      <td>180</td>\n",
       "      <td>42.22</td>\n",
       "      <td>-71.01</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BOS_PIT_49759</td>\n",
       "      <td>2004</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>845</td>\n",
       "      <td>1031</td>\n",
       "      <td>106</td>\n",
       "      <td>BOS</td>\n",
       "      <td>PIT</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14739</td>\n",
       "      <td>BOS</td>\n",
       "      <td>19</td>\n",
       "      <td>30</td>\n",
       "      <td>180</td>\n",
       "      <td>42.22</td>\n",
       "      <td>-71.01</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BOS_DCA_32791</td>\n",
       "      <td>2004</td>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "      <td>4</td>\n",
       "      <td>1300</td>\n",
       "      <td>1430</td>\n",
       "      <td>90</td>\n",
       "      <td>BOS</td>\n",
       "      <td>DCA</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14739</td>\n",
       "      <td>BOS</td>\n",
       "      <td>19</td>\n",
       "      <td>30</td>\n",
       "      <td>180</td>\n",
       "      <td>42.22</td>\n",
       "      <td>-71.01</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7856</th>\n",
       "      <td>LAW_DFW_46776</td>\n",
       "      <td>2004</td>\n",
       "      <td>11</td>\n",
       "      <td>24</td>\n",
       "      <td>3</td>\n",
       "      <td>1756</td>\n",
       "      <td>1857</td>\n",
       "      <td>61</td>\n",
       "      <td>LAW</td>\n",
       "      <td>DFW</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3950</td>\n",
       "      <td>LAW</td>\n",
       "      <td>1075</td>\n",
       "      <td>1108</td>\n",
       "      <td>1110</td>\n",
       "      <td>34.34</td>\n",
       "      <td>-98.25</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7857</th>\n",
       "      <td>MTJ_DEN_35253</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>640</td>\n",
       "      <td>744</td>\n",
       "      <td>64</td>\n",
       "      <td>MTJ</td>\n",
       "      <td>DEN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>93013</td>\n",
       "      <td>MTJ</td>\n",
       "      <td>5750</td>\n",
       "      <td>5759</td>\n",
       "      <td>5753</td>\n",
       "      <td>38.31</td>\n",
       "      <td>-107.54</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7858</th>\n",
       "      <td>JNU_SEA_32316</td>\n",
       "      <td>2004</td>\n",
       "      <td>3</td>\n",
       "      <td>18</td>\n",
       "      <td>4</td>\n",
       "      <td>1421</td>\n",
       "      <td>1738</td>\n",
       "      <td>137</td>\n",
       "      <td>JNU</td>\n",
       "      <td>SEA</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>25309</td>\n",
       "      <td>JNU</td>\n",
       "      <td>37</td>\n",
       "      <td>24</td>\n",
       "      <td>40</td>\n",
       "      <td>58.21</td>\n",
       "      <td>-134.34</td>\n",
       "      <td>+9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7859</th>\n",
       "      <td>DRO_DEN_27288</td>\n",
       "      <td>2004</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>3</td>\n",
       "      <td>800</td>\n",
       "      <td>914</td>\n",
       "      <td>74</td>\n",
       "      <td>DRO</td>\n",
       "      <td>DEN</td>\n",
       "      <td>...</td>\n",
       "      <td>6360</td>\n",
       "      <td>1</td>\n",
       "      <td>93005</td>\n",
       "      <td>DRO</td>\n",
       "      <td>6674</td>\n",
       "      <td>6685</td>\n",
       "      <td>6677</td>\n",
       "      <td>37.08</td>\n",
       "      <td>-107.46</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7860</th>\n",
       "      <td>ERI_CVG_47268</td>\n",
       "      <td>2004</td>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>1335</td>\n",
       "      <td>1452</td>\n",
       "      <td>77</td>\n",
       "      <td>ERI</td>\n",
       "      <td>CVG</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14860</td>\n",
       "      <td>ERI</td>\n",
       "      <td>753</td>\n",
       "      <td>737</td>\n",
       "      <td>756</td>\n",
       "      <td>42.05</td>\n",
       "      <td>-80.11</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7861 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       FlightNumber  Year  Month  DayofMonth  DayOfWeek  ScheduledDepTime  \\\n",
       "0     BOS_CLE_29089  2004      9          20          1              1000   \n",
       "1     BOS_OAK_55049  2004      9          10          5               820   \n",
       "2     BOS_RSW_47586  2004      3          22          1               700   \n",
       "3     BOS_PIT_49759  2004      5           1          6               845   \n",
       "4     BOS_DCA_32791  2004      7          15          4              1300   \n",
       "...             ...   ...    ...         ...        ...               ...   \n",
       "7856  LAW_DFW_46776  2004     11          24          3              1756   \n",
       "7857  MTJ_DEN_35253  2004      9           8          3               640   \n",
       "7858  JNU_SEA_32316  2004      3          18          4              1421   \n",
       "7859  DRO_DEN_27288  2004     11          17          3               800   \n",
       "7860  ERI_CVG_47268  2004      5          18          2              1335   \n",
       "\n",
       "      ScheduledArrTime  ScheduledTravelTime Origin Destination  ...  \\\n",
       "0                 1154                  114    BOS         CLE  ...   \n",
       "1                 1200                  400    BOS         OAK  ...   \n",
       "2                 1036                  216    BOS         RSW  ...   \n",
       "3                 1031                  106    BOS         PIT  ...   \n",
       "4                 1430                   90    BOS         DCA  ...   \n",
       "...                ...                  ...    ...         ...  ...   \n",
       "7856              1857                   61    LAW         DFW  ...   \n",
       "7857               744                   64    MTJ         DEN  ...   \n",
       "7858              1738                  137    JNU         SEA  ...   \n",
       "7859               914                   74    DRO         DEN  ...   \n",
       "7860              1452                   77    ERI         CVG  ...   \n",
       "\n",
       "      delay_in_seconds target WeatherStationID AirportID  GroundHeight  \\\n",
       "0                    0      2            14739       BOS            19   \n",
       "1                    0      2            14739       BOS            19   \n",
       "2                    0      2            14739       BOS            19   \n",
       "3                    0      2            14739       BOS            19   \n",
       "4                    0      2            14739       BOS            19   \n",
       "...                ...    ...              ...       ...           ...   \n",
       "7856                 0      2             3950       LAW          1075   \n",
       "7857                 0      2            93013       MTJ          5750   \n",
       "7858                 0      2            25309       JNU            37   \n",
       "7859              6360      1            93005       DRO          6674   \n",
       "7860                 0      2            14860       ERI           753   \n",
       "\n",
       "      StationHeight  BarometerHeight Latitude  Longitude  TimeZone  \n",
       "0                30              180    42.22     -71.01        +5  \n",
       "1                30              180    42.22     -71.01        +5  \n",
       "2                30              180    42.22     -71.01        +5  \n",
       "3                30              180    42.22     -71.01        +5  \n",
       "4                30              180    42.22     -71.01        +5  \n",
       "...             ...              ...      ...        ...       ...  \n",
       "7856           1108             1110    34.34     -98.25        +6  \n",
       "7857           5759             5753    38.31    -107.54        +7  \n",
       "7858             24               40    58.21    -134.34        +9  \n",
       "7859           6685             6677    37.08    -107.46        +7  \n",
       "7860            737              756    42.05     -80.11        +5  \n",
       "\n",
       "[7861 rows x 24 columns]"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "origin_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "5e4444fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "### Merging together the train dataset with the allstationsdf and matching it with departure AirportID\n",
    "\n",
    "dest_df=pd.merge(train, allstationsdf, left_on='Destination', right_on='AirportID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "b30aab3e",
   "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>FlightNumber</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>DayofMonth</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>ScheduledDepTime</th>\n",
       "      <th>ScheduledArrTime</th>\n",
       "      <th>ScheduledTravelTime</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>...</th>\n",
       "      <th>delay_in_seconds</th>\n",
       "      <th>target</th>\n",
       "      <th>WeatherStationID</th>\n",
       "      <th>AirportID</th>\n",
       "      <th>GroundHeight</th>\n",
       "      <th>StationHeight</th>\n",
       "      <th>BarometerHeight</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>TimeZone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BOS_CLE_29089</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1154</td>\n",
       "      <td>114</td>\n",
       "      <td>BOS</td>\n",
       "      <td>CLE</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14820</td>\n",
       "      <td>CLE</td>\n",
       "      <td>802</td>\n",
       "      <td>805</td>\n",
       "      <td>805</td>\n",
       "      <td>41.24</td>\n",
       "      <td>-81.51</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FLL_CLE_45262</td>\n",
       "      <td>2004</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>1600</td>\n",
       "      <td>1847</td>\n",
       "      <td>167</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLE</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14820</td>\n",
       "      <td>CLE</td>\n",
       "      <td>802</td>\n",
       "      <td>805</td>\n",
       "      <td>805</td>\n",
       "      <td>41.24</td>\n",
       "      <td>-81.51</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EWR_CLE_37064</td>\n",
       "      <td>2004</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>645</td>\n",
       "      <td>828</td>\n",
       "      <td>103</td>\n",
       "      <td>EWR</td>\n",
       "      <td>CLE</td>\n",
       "      <td>...</td>\n",
       "      <td>7200</td>\n",
       "      <td>1</td>\n",
       "      <td>14820</td>\n",
       "      <td>CLE</td>\n",
       "      <td>802</td>\n",
       "      <td>805</td>\n",
       "      <td>805</td>\n",
       "      <td>41.24</td>\n",
       "      <td>-81.51</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>IAD_CLE_47407</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>745</td>\n",
       "      <td>900</td>\n",
       "      <td>75</td>\n",
       "      <td>IAD</td>\n",
       "      <td>CLE</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14820</td>\n",
       "      <td>CLE</td>\n",
       "      <td>802</td>\n",
       "      <td>805</td>\n",
       "      <td>805</td>\n",
       "      <td>41.24</td>\n",
       "      <td>-81.51</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>STL_CLE_54043</td>\n",
       "      <td>2004</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>1200</td>\n",
       "      <td>1433</td>\n",
       "      <td>93</td>\n",
       "      <td>STL</td>\n",
       "      <td>CLE</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14820</td>\n",
       "      <td>CLE</td>\n",
       "      <td>802</td>\n",
       "      <td>805</td>\n",
       "      <td>805</td>\n",
       "      <td>41.24</td>\n",
       "      <td>-81.51</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    FlightNumber  Year  Month  DayofMonth  DayOfWeek  ScheduledDepTime  \\\n",
       "0  BOS_CLE_29089  2004      9          20          1              1000   \n",
       "1  FLL_CLE_45262  2004      3          13          6              1600   \n",
       "2  EWR_CLE_37064  2004      3           4          4               645   \n",
       "3  IAD_CLE_47407  2004      9          13          1               745   \n",
       "4  STL_CLE_54043  2004     11           7          7              1200   \n",
       "\n",
       "   ScheduledArrTime  ScheduledTravelTime Origin Destination  ...  \\\n",
       "0              1154                  114    BOS         CLE  ...   \n",
       "1              1847                  167    FLL         CLE  ...   \n",
       "2               828                  103    EWR         CLE  ...   \n",
       "3               900                   75    IAD         CLE  ...   \n",
       "4              1433                   93    STL         CLE  ...   \n",
       "\n",
       "   delay_in_seconds target WeatherStationID AirportID  GroundHeight  \\\n",
       "0                 0      2            14820       CLE           802   \n",
       "1                 0      2            14820       CLE           802   \n",
       "2              7200      1            14820       CLE           802   \n",
       "3                 0      2            14820       CLE           802   \n",
       "4                 0      2            14820       CLE           802   \n",
       "\n",
       "   StationHeight  BarometerHeight Latitude  Longitude  TimeZone  \n",
       "0            805              805    41.24     -81.51        +5  \n",
       "1            805              805    41.24     -81.51        +5  \n",
       "2            805              805    41.24     -81.51        +5  \n",
       "3            805              805    41.24     -81.51        +5  \n",
       "4            805              805    41.24     -81.51        +5  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dest_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "9a4e1b21",
   "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>FlightNumber</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>DayofMonth</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>ScheduledDepTime</th>\n",
       "      <th>ScheduledArrTime</th>\n",
       "      <th>ScheduledTravelTime</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>...</th>\n",
       "      <th>delay_in_seconds</th>\n",
       "      <th>target</th>\n",
       "      <th>WeatherStationID</th>\n",
       "      <th>AirportID</th>\n",
       "      <th>GroundHeight</th>\n",
       "      <th>StationHeight</th>\n",
       "      <th>BarometerHeight</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>TimeZone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BOS_CLE_29089</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1154</td>\n",
       "      <td>114</td>\n",
       "      <td>BOS</td>\n",
       "      <td>CLE</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14820</td>\n",
       "      <td>CLE</td>\n",
       "      <td>802</td>\n",
       "      <td>805</td>\n",
       "      <td>805</td>\n",
       "      <td>41.24</td>\n",
       "      <td>-81.51</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FLL_CLE_45262</td>\n",
       "      <td>2004</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>1600</td>\n",
       "      <td>1847</td>\n",
       "      <td>167</td>\n",
       "      <td>FLL</td>\n",
       "      <td>CLE</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14820</td>\n",
       "      <td>CLE</td>\n",
       "      <td>802</td>\n",
       "      <td>805</td>\n",
       "      <td>805</td>\n",
       "      <td>41.24</td>\n",
       "      <td>-81.51</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EWR_CLE_37064</td>\n",
       "      <td>2004</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>645</td>\n",
       "      <td>828</td>\n",
       "      <td>103</td>\n",
       "      <td>EWR</td>\n",
       "      <td>CLE</td>\n",
       "      <td>...</td>\n",
       "      <td>7200</td>\n",
       "      <td>1</td>\n",
       "      <td>14820</td>\n",
       "      <td>CLE</td>\n",
       "      <td>802</td>\n",
       "      <td>805</td>\n",
       "      <td>805</td>\n",
       "      <td>41.24</td>\n",
       "      <td>-81.51</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>IAD_CLE_47407</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>745</td>\n",
       "      <td>900</td>\n",
       "      <td>75</td>\n",
       "      <td>IAD</td>\n",
       "      <td>CLE</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14820</td>\n",
       "      <td>CLE</td>\n",
       "      <td>802</td>\n",
       "      <td>805</td>\n",
       "      <td>805</td>\n",
       "      <td>41.24</td>\n",
       "      <td>-81.51</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>STL_CLE_54043</td>\n",
       "      <td>2004</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>1200</td>\n",
       "      <td>1433</td>\n",
       "      <td>93</td>\n",
       "      <td>STL</td>\n",
       "      <td>CLE</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14820</td>\n",
       "      <td>CLE</td>\n",
       "      <td>802</td>\n",
       "      <td>805</td>\n",
       "      <td>805</td>\n",
       "      <td>41.24</td>\n",
       "      <td>-81.51</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7856</th>\n",
       "      <td>ANC_SCC_44981</td>\n",
       "      <td>2004</td>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "      <td>1110</td>\n",
       "      <td>1252</td>\n",
       "      <td>102</td>\n",
       "      <td>ANC</td>\n",
       "      <td>SCC</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>27406</td>\n",
       "      <td>SCC</td>\n",
       "      <td>66</td>\n",
       "      <td>60</td>\n",
       "      <td>69</td>\n",
       "      <td>70.11</td>\n",
       "      <td>-148.29</td>\n",
       "      <td>+9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7857</th>\n",
       "      <td>DFW_EGE_47115</td>\n",
       "      <td>2004</td>\n",
       "      <td>7</td>\n",
       "      <td>23</td>\n",
       "      <td>5</td>\n",
       "      <td>1545</td>\n",
       "      <td>1655</td>\n",
       "      <td>130</td>\n",
       "      <td>DFW</td>\n",
       "      <td>EGE</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>23063</td>\n",
       "      <td>EGE</td>\n",
       "      <td>0</td>\n",
       "      <td>6535</td>\n",
       "      <td>0</td>\n",
       "      <td>39.39</td>\n",
       "      <td>-106.55</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7858</th>\n",
       "      <td>ATL_ILM_27401</td>\n",
       "      <td>2004</td>\n",
       "      <td>5</td>\n",
       "      <td>31</td>\n",
       "      <td>1</td>\n",
       "      <td>1434</td>\n",
       "      <td>1555</td>\n",
       "      <td>81</td>\n",
       "      <td>ATL</td>\n",
       "      <td>ILM</td>\n",
       "      <td>...</td>\n",
       "      <td>10080</td>\n",
       "      <td>1</td>\n",
       "      <td>13748</td>\n",
       "      <td>ILM</td>\n",
       "      <td>30</td>\n",
       "      <td>38</td>\n",
       "      <td>33</td>\n",
       "      <td>34.16</td>\n",
       "      <td>-77.55</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7859</th>\n",
       "      <td>ATL_TRI_33614</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>1700</td>\n",
       "      <td>1806</td>\n",
       "      <td>66</td>\n",
       "      <td>ATL</td>\n",
       "      <td>TRI</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>13877</td>\n",
       "      <td>TRI</td>\n",
       "      <td>1536</td>\n",
       "      <td>1525</td>\n",
       "      <td>1539</td>\n",
       "      <td>36.29</td>\n",
       "      <td>-82.24</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7860</th>\n",
       "      <td>MSP_MOT_32606</td>\n",
       "      <td>2004</td>\n",
       "      <td>11</td>\n",
       "      <td>21</td>\n",
       "      <td>7</td>\n",
       "      <td>900</td>\n",
       "      <td>1031</td>\n",
       "      <td>91</td>\n",
       "      <td>MSP</td>\n",
       "      <td>MOT</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>24013</td>\n",
       "      <td>MOT</td>\n",
       "      <td>1665</td>\n",
       "      <td>1714</td>\n",
       "      <td>1706</td>\n",
       "      <td>48.16</td>\n",
       "      <td>-101.17</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7861 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       FlightNumber  Year  Month  DayofMonth  DayOfWeek  ScheduledDepTime  \\\n",
       "0     BOS_CLE_29089  2004      9          20          1              1000   \n",
       "1     FLL_CLE_45262  2004      3          13          6              1600   \n",
       "2     EWR_CLE_37064  2004      3           4          4               645   \n",
       "3     IAD_CLE_47407  2004      9          13          1               745   \n",
       "4     STL_CLE_54043  2004     11           7          7              1200   \n",
       "...             ...   ...    ...         ...        ...               ...   \n",
       "7856  ANC_SCC_44981  2004      3          14          7              1110   \n",
       "7857  DFW_EGE_47115  2004      7          23          5              1545   \n",
       "7858  ATL_ILM_27401  2004      5          31          1              1434   \n",
       "7859  ATL_TRI_33614  2004      9          15          3              1700   \n",
       "7860  MSP_MOT_32606  2004     11          21          7               900   \n",
       "\n",
       "      ScheduledArrTime  ScheduledTravelTime Origin Destination  ...  \\\n",
       "0                 1154                  114    BOS         CLE  ...   \n",
       "1                 1847                  167    FLL         CLE  ...   \n",
       "2                  828                  103    EWR         CLE  ...   \n",
       "3                  900                   75    IAD         CLE  ...   \n",
       "4                 1433                   93    STL         CLE  ...   \n",
       "...                ...                  ...    ...         ...  ...   \n",
       "7856              1252                  102    ANC         SCC  ...   \n",
       "7857              1655                  130    DFW         EGE  ...   \n",
       "7858              1555                   81    ATL         ILM  ...   \n",
       "7859              1806                   66    ATL         TRI  ...   \n",
       "7860              1031                   91    MSP         MOT  ...   \n",
       "\n",
       "      delay_in_seconds target WeatherStationID AirportID  GroundHeight  \\\n",
       "0                    0      2            14820       CLE           802   \n",
       "1                    0      2            14820       CLE           802   \n",
       "2                 7200      1            14820       CLE           802   \n",
       "3                    0      2            14820       CLE           802   \n",
       "4                    0      2            14820       CLE           802   \n",
       "...                ...    ...              ...       ...           ...   \n",
       "7856                 0      2            27406       SCC            66   \n",
       "7857                 0      2            23063       EGE             0   \n",
       "7858             10080      1            13748       ILM            30   \n",
       "7859                 0      2            13877       TRI          1536   \n",
       "7860                 0      2            24013       MOT          1665   \n",
       "\n",
       "      StationHeight  BarometerHeight Latitude  Longitude  TimeZone  \n",
       "0               805              805    41.24     -81.51        +5  \n",
       "1               805              805    41.24     -81.51        +5  \n",
       "2               805              805    41.24     -81.51        +5  \n",
       "3               805              805    41.24     -81.51        +5  \n",
       "4               805              805    41.24     -81.51        +5  \n",
       "...             ...              ...      ...        ...       ...  \n",
       "7856             60               69    70.11    -148.29        +9  \n",
       "7857           6535                0    39.39    -106.55        +7  \n",
       "7858             38               33    34.16     -77.55        +5  \n",
       "7859           1525             1539    36.29     -82.24        +5  \n",
       "7860           1714             1706    48.16    -101.17        +6  \n",
       "\n",
       "[7861 rows x 24 columns]"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dest_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "c08e6edc",
   "metadata": {},
   "outputs": [],
   "source": [
    "### Combining the 2 datasets to make it one\n",
    "\n",
    "merged=pd.merge(origin_df,dest_df, on='FlightNumber',how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "da6f5d93",
   "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>FlightNumber</th>\n",
       "      <th>Year_x</th>\n",
       "      <th>Month_x</th>\n",
       "      <th>DayofMonth_x</th>\n",
       "      <th>DayOfWeek_x</th>\n",
       "      <th>ScheduledDepTime_x</th>\n",
       "      <th>ScheduledArrTime_x</th>\n",
       "      <th>ScheduledTravelTime_x</th>\n",
       "      <th>Origin_x</th>\n",
       "      <th>Destination_x</th>\n",
       "      <th>...</th>\n",
       "      <th>delay_in_seconds_y</th>\n",
       "      <th>target_y</th>\n",
       "      <th>WeatherStationID_y</th>\n",
       "      <th>AirportID_y</th>\n",
       "      <th>GroundHeight_y</th>\n",
       "      <th>StationHeight_y</th>\n",
       "      <th>BarometerHeight_y</th>\n",
       "      <th>Latitude_y</th>\n",
       "      <th>Longitude_y</th>\n",
       "      <th>TimeZone_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BOS_CLE_29089</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1154</td>\n",
       "      <td>114</td>\n",
       "      <td>BOS</td>\n",
       "      <td>CLE</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14820</td>\n",
       "      <td>CLE</td>\n",
       "      <td>802</td>\n",
       "      <td>805</td>\n",
       "      <td>805</td>\n",
       "      <td>41.24</td>\n",
       "      <td>-81.51</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BOS_OAK_55049</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>820</td>\n",
       "      <td>1200</td>\n",
       "      <td>400</td>\n",
       "      <td>BOS</td>\n",
       "      <td>OAK</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>23230</td>\n",
       "      <td>OAK</td>\n",
       "      <td>85</td>\n",
       "      <td>88</td>\n",
       "      <td>88</td>\n",
       "      <td>37.43</td>\n",
       "      <td>-122.13</td>\n",
       "      <td>+8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BOS_RSW_47586</td>\n",
       "      <td>2004</td>\n",
       "      <td>3</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>700</td>\n",
       "      <td>1036</td>\n",
       "      <td>216</td>\n",
       "      <td>BOS</td>\n",
       "      <td>RSW</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>12894</td>\n",
       "      <td>RSW</td>\n",
       "      <td>29</td>\n",
       "      <td>30</td>\n",
       "      <td>32</td>\n",
       "      <td>26.32</td>\n",
       "      <td>-81.45</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BOS_PIT_49759</td>\n",
       "      <td>2004</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>845</td>\n",
       "      <td>1031</td>\n",
       "      <td>106</td>\n",
       "      <td>BOS</td>\n",
       "      <td>PIT</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>94823</td>\n",
       "      <td>PIT</td>\n",
       "      <td>1172</td>\n",
       "      <td>1203</td>\n",
       "      <td>1175</td>\n",
       "      <td>40.30</td>\n",
       "      <td>-80.14</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BOS_DCA_32791</td>\n",
       "      <td>2004</td>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "      <td>4</td>\n",
       "      <td>1300</td>\n",
       "      <td>1430</td>\n",
       "      <td>90</td>\n",
       "      <td>BOS</td>\n",
       "      <td>DCA</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>13743</td>\n",
       "      <td>DCA</td>\n",
       "      <td>10</td>\n",
       "      <td>65</td>\n",
       "      <td>3</td>\n",
       "      <td>38.52</td>\n",
       "      <td>-77.02</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7856</th>\n",
       "      <td>LAW_DFW_46776</td>\n",
       "      <td>2004</td>\n",
       "      <td>11</td>\n",
       "      <td>24</td>\n",
       "      <td>3</td>\n",
       "      <td>1756</td>\n",
       "      <td>1857</td>\n",
       "      <td>61</td>\n",
       "      <td>LAW</td>\n",
       "      <td>DFW</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3927</td>\n",
       "      <td>DFW</td>\n",
       "      <td>559</td>\n",
       "      <td>596</td>\n",
       "      <td>562</td>\n",
       "      <td>32.54</td>\n",
       "      <td>-97.02</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7857</th>\n",
       "      <td>MTJ_DEN_35253</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>640</td>\n",
       "      <td>744</td>\n",
       "      <td>64</td>\n",
       "      <td>MTJ</td>\n",
       "      <td>DEN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3017</td>\n",
       "      <td>DEN</td>\n",
       "      <td>5379</td>\n",
       "      <td>5431</td>\n",
       "      <td>5382</td>\n",
       "      <td>39.50</td>\n",
       "      <td>-104.40</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7858</th>\n",
       "      <td>JNU_SEA_32316</td>\n",
       "      <td>2004</td>\n",
       "      <td>3</td>\n",
       "      <td>18</td>\n",
       "      <td>4</td>\n",
       "      <td>1421</td>\n",
       "      <td>1738</td>\n",
       "      <td>137</td>\n",
       "      <td>JNU</td>\n",
       "      <td>SEA</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>24233</td>\n",
       "      <td>SEA</td>\n",
       "      <td>447</td>\n",
       "      <td>450</td>\n",
       "      <td>450</td>\n",
       "      <td>47.28</td>\n",
       "      <td>-122.19</td>\n",
       "      <td>+8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7859</th>\n",
       "      <td>DRO_DEN_27288</td>\n",
       "      <td>2004</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>3</td>\n",
       "      <td>800</td>\n",
       "      <td>914</td>\n",
       "      <td>74</td>\n",
       "      <td>DRO</td>\n",
       "      <td>DEN</td>\n",
       "      <td>...</td>\n",
       "      <td>6360</td>\n",
       "      <td>1</td>\n",
       "      <td>3017</td>\n",
       "      <td>DEN</td>\n",
       "      <td>5379</td>\n",
       "      <td>5431</td>\n",
       "      <td>5382</td>\n",
       "      <td>39.50</td>\n",
       "      <td>-104.40</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7860</th>\n",
       "      <td>ERI_CVG_47268</td>\n",
       "      <td>2004</td>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>1335</td>\n",
       "      <td>1452</td>\n",
       "      <td>77</td>\n",
       "      <td>ERI</td>\n",
       "      <td>CVG</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>93814</td>\n",
       "      <td>CVG</td>\n",
       "      <td>882</td>\n",
       "      <td>883</td>\n",
       "      <td>885</td>\n",
       "      <td>39.02</td>\n",
       "      <td>-84.40</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7861 rows × 47 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       FlightNumber  Year_x  Month_x  DayofMonth_x  DayOfWeek_x  \\\n",
       "0     BOS_CLE_29089    2004        9            20            1   \n",
       "1     BOS_OAK_55049    2004        9            10            5   \n",
       "2     BOS_RSW_47586    2004        3            22            1   \n",
       "3     BOS_PIT_49759    2004        5             1            6   \n",
       "4     BOS_DCA_32791    2004        7            15            4   \n",
       "...             ...     ...      ...           ...          ...   \n",
       "7856  LAW_DFW_46776    2004       11            24            3   \n",
       "7857  MTJ_DEN_35253    2004        9             8            3   \n",
       "7858  JNU_SEA_32316    2004        3            18            4   \n",
       "7859  DRO_DEN_27288    2004       11            17            3   \n",
       "7860  ERI_CVG_47268    2004        5            18            2   \n",
       "\n",
       "      ScheduledDepTime_x  ScheduledArrTime_x  ScheduledTravelTime_x Origin_x  \\\n",
       "0                   1000                1154                    114      BOS   \n",
       "1                    820                1200                    400      BOS   \n",
       "2                    700                1036                    216      BOS   \n",
       "3                    845                1031                    106      BOS   \n",
       "4                   1300                1430                     90      BOS   \n",
       "...                  ...                 ...                    ...      ...   \n",
       "7856                1756                1857                     61      LAW   \n",
       "7857                 640                 744                     64      MTJ   \n",
       "7858                1421                1738                    137      JNU   \n",
       "7859                 800                 914                     74      DRO   \n",
       "7860                1335                1452                     77      ERI   \n",
       "\n",
       "     Destination_x  ...  delay_in_seconds_y target_y WeatherStationID_y  \\\n",
       "0              CLE  ...                   0        2              14820   \n",
       "1              OAK  ...                   0        2              23230   \n",
       "2              RSW  ...                   0        2              12894   \n",
       "3              PIT  ...                   0        2              94823   \n",
       "4              DCA  ...                   0        2              13743   \n",
       "...            ...  ...                 ...      ...                ...   \n",
       "7856           DFW  ...                   0        2               3927   \n",
       "7857           DEN  ...                   0        2               3017   \n",
       "7858           SEA  ...                   0        2              24233   \n",
       "7859           DEN  ...                6360        1               3017   \n",
       "7860           CVG  ...                   0        2              93814   \n",
       "\n",
       "     AirportID_y  GroundHeight_y  StationHeight_y  BarometerHeight_y  \\\n",
       "0            CLE             802              805                805   \n",
       "1            OAK              85               88                 88   \n",
       "2            RSW              29               30                 32   \n",
       "3            PIT            1172             1203               1175   \n",
       "4            DCA              10               65                  3   \n",
       "...          ...             ...              ...                ...   \n",
       "7856         DFW             559              596                562   \n",
       "7857         DEN            5379             5431               5382   \n",
       "7858         SEA             447              450                450   \n",
       "7859         DEN            5379             5431               5382   \n",
       "7860         CVG             882              883                885   \n",
       "\n",
       "     Latitude_y  Longitude_y  TimeZone_y  \n",
       "0         41.24       -81.51          +5  \n",
       "1         37.43      -122.13          +8  \n",
       "2         26.32       -81.45          +5  \n",
       "3         40.30       -80.14          +5  \n",
       "4         38.52       -77.02          +5  \n",
       "...         ...          ...         ...  \n",
       "7856      32.54       -97.02          +6  \n",
       "7857      39.50      -104.40          +7  \n",
       "7858      47.28      -122.19          +8  \n",
       "7859      39.50      -104.40          +7  \n",
       "7860      39.02       -84.40          +5  \n",
       "\n",
       "[7861 rows x 47 columns]"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "5e8ccff2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['FlightNumber', 'Year_x', 'Month_x', 'DayofMonth_x', 'DayOfWeek_x',\n",
       "       'ScheduledDepTime_x', 'ScheduledArrTime_x', 'ScheduledTravelTime_x',\n",
       "       'Origin_x', 'Destination_x', 'Distance_x', 'ActualArrivalTimeStamp_x',\n",
       "       'result_x', 'delay_x', 'delay_in_seconds_x', 'target_x',\n",
       "       'WeatherStationID_x', 'AirportID_x', 'GroundHeight_x',\n",
       "       'StationHeight_x', 'BarometerHeight_x', 'Latitude_x', 'Longitude_x',\n",
       "       'TimeZone_x', 'Year_y', 'Month_y', 'DayofMonth_y', 'DayOfWeek_y',\n",
       "       'ScheduledDepTime_y', 'ScheduledArrTime_y', 'ScheduledTravelTime_y',\n",
       "       'Origin_y', 'Destination_y', 'Distance_y', 'ActualArrivalTimeStamp_y',\n",
       "       'result_y', 'delay_y', 'delay_in_seconds_y', 'target_y',\n",
       "       'WeatherStationID_y', 'AirportID_y', 'GroundHeight_y',\n",
       "       'StationHeight_y', 'BarometerHeight_y', 'Latitude_y', 'Longitude_y',\n",
       "       'TimeZone_y'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "7acd8785",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['FlightNumber', 'Year', 'Month', 'DayofMonth', 'DayOfWeek',\n",
       "       'ScheduledDepTime', 'ScheduledArrTime', 'ScheduledTravelTime', 'Origin',\n",
       "       'Destination', 'Distance', 'ActualArrivalTimeStamp', 'result', 'delay',\n",
       "       'delay_in_seconds', 'target'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13e4f2ce",
   "metadata": {},
   "source": [
    "### Removing the repeated columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "c94df4f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged=merged.drop(['Year_y', 'Month_y', 'DayofMonth_y', 'DayOfWeek_y',\n",
    "       'ScheduledDepTime_y', 'ScheduledArrTime_y', 'ScheduledTravelTime_y',\n",
    "       'Origin_y', 'Destination_y', 'Distance_y', 'ActualArrivalTimeStamp_y', 'result_y', 'delay_y',\n",
    "                   'delay_in_seconds_y', 'target_y'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "ca3a5e0e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['FlightNumber', 'Year_x', 'Month_x', 'DayofMonth_x', 'DayOfWeek_x',\n",
       "       'ScheduledDepTime_x', 'ScheduledArrTime_x', 'ScheduledTravelTime_x',\n",
       "       'Origin_x', 'Destination_x', 'Distance_x', 'ActualArrivalTimeStamp_x',\n",
       "       'result_x', 'delay_x', 'delay_in_seconds_x', 'target_x',\n",
       "       'WeatherStationID_x', 'AirportID_x', 'GroundHeight_x',\n",
       "       'StationHeight_x', 'BarometerHeight_x', 'Latitude_x', 'Longitude_x',\n",
       "       'TimeZone_x', 'WeatherStationID_y', 'AirportID_y', 'GroundHeight_y',\n",
       "       'StationHeight_y', 'BarometerHeight_y', 'Latitude_y', 'Longitude_y',\n",
       "       'TimeZone_y'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "a46693e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "### Renaming the columns\n",
    "\n",
    "merged.columns=['FlightNumber', 'Year', 'Month', 'DayOfMonth', 'DayOfWeek',\n",
    "       'ScheduledDepTime', 'ScheduledArrTime', 'ScheduledTravelTime',\n",
    "       'Origin', 'Destination', 'Distance', 'ActualArrivalTimeStamp',\n",
    "        'result', 'delay', 'delay_in_seconds', 'target',\n",
    "       'WeatherStationID_origin', 'AirportID_origin', 'GroundHeight_origin',\n",
    "       'StationHeight_origin', 'BarometerHeight_origin', 'Latitude_origin', 'Longitude_origin',\n",
    "       'TimeZone_origin', 'WeatherStationID_dest', 'AirportID_dest', 'GroundHeight_dest',\n",
    "       'StationHeight_dest', 'BarometerHeight_dest', 'Latitude_dest', 'Longitude_dest',\n",
    "       'TimeZone_dest']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "06841929",
   "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>FlightNumber</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>DayOfMonth</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>ScheduledDepTime</th>\n",
       "      <th>ScheduledArrTime</th>\n",
       "      <th>ScheduledTravelTime</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>...</th>\n",
       "      <th>Longitude_origin</th>\n",
       "      <th>TimeZone_origin</th>\n",
       "      <th>WeatherStationID_dest</th>\n",
       "      <th>AirportID_dest</th>\n",
       "      <th>GroundHeight_dest</th>\n",
       "      <th>StationHeight_dest</th>\n",
       "      <th>BarometerHeight_dest</th>\n",
       "      <th>Latitude_dest</th>\n",
       "      <th>Longitude_dest</th>\n",
       "      <th>TimeZone_dest</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BOS_CLE_29089</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1154</td>\n",
       "      <td>114</td>\n",
       "      <td>BOS</td>\n",
       "      <td>CLE</td>\n",
       "      <td>...</td>\n",
       "      <td>-71.01</td>\n",
       "      <td>+5</td>\n",
       "      <td>14820</td>\n",
       "      <td>CLE</td>\n",
       "      <td>802</td>\n",
       "      <td>805</td>\n",
       "      <td>805</td>\n",
       "      <td>41.24</td>\n",
       "      <td>-81.51</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BOS_OAK_55049</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>820</td>\n",
       "      <td>1200</td>\n",
       "      <td>400</td>\n",
       "      <td>BOS</td>\n",
       "      <td>OAK</td>\n",
       "      <td>...</td>\n",
       "      <td>-71.01</td>\n",
       "      <td>+5</td>\n",
       "      <td>23230</td>\n",
       "      <td>OAK</td>\n",
       "      <td>85</td>\n",
       "      <td>88</td>\n",
       "      <td>88</td>\n",
       "      <td>37.43</td>\n",
       "      <td>-122.13</td>\n",
       "      <td>+8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BOS_RSW_47586</td>\n",
       "      <td>2004</td>\n",
       "      <td>3</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>700</td>\n",
       "      <td>1036</td>\n",
       "      <td>216</td>\n",
       "      <td>BOS</td>\n",
       "      <td>RSW</td>\n",
       "      <td>...</td>\n",
       "      <td>-71.01</td>\n",
       "      <td>+5</td>\n",
       "      <td>12894</td>\n",
       "      <td>RSW</td>\n",
       "      <td>29</td>\n",
       "      <td>30</td>\n",
       "      <td>32</td>\n",
       "      <td>26.32</td>\n",
       "      <td>-81.45</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BOS_PIT_49759</td>\n",
       "      <td>2004</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>845</td>\n",
       "      <td>1031</td>\n",
       "      <td>106</td>\n",
       "      <td>BOS</td>\n",
       "      <td>PIT</td>\n",
       "      <td>...</td>\n",
       "      <td>-71.01</td>\n",
       "      <td>+5</td>\n",
       "      <td>94823</td>\n",
       "      <td>PIT</td>\n",
       "      <td>1172</td>\n",
       "      <td>1203</td>\n",
       "      <td>1175</td>\n",
       "      <td>40.30</td>\n",
       "      <td>-80.14</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BOS_DCA_32791</td>\n",
       "      <td>2004</td>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "      <td>4</td>\n",
       "      <td>1300</td>\n",
       "      <td>1430</td>\n",
       "      <td>90</td>\n",
       "      <td>BOS</td>\n",
       "      <td>DCA</td>\n",
       "      <td>...</td>\n",
       "      <td>-71.01</td>\n",
       "      <td>+5</td>\n",
       "      <td>13743</td>\n",
       "      <td>DCA</td>\n",
       "      <td>10</td>\n",
       "      <td>65</td>\n",
       "      <td>3</td>\n",
       "      <td>38.52</td>\n",
       "      <td>-77.02</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7856</th>\n",
       "      <td>LAW_DFW_46776</td>\n",
       "      <td>2004</td>\n",
       "      <td>11</td>\n",
       "      <td>24</td>\n",
       "      <td>3</td>\n",
       "      <td>1756</td>\n",
       "      <td>1857</td>\n",
       "      <td>61</td>\n",
       "      <td>LAW</td>\n",
       "      <td>DFW</td>\n",
       "      <td>...</td>\n",
       "      <td>-98.25</td>\n",
       "      <td>+6</td>\n",
       "      <td>3927</td>\n",
       "      <td>DFW</td>\n",
       "      <td>559</td>\n",
       "      <td>596</td>\n",
       "      <td>562</td>\n",
       "      <td>32.54</td>\n",
       "      <td>-97.02</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7857</th>\n",
       "      <td>MTJ_DEN_35253</td>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>640</td>\n",
       "      <td>744</td>\n",
       "      <td>64</td>\n",
       "      <td>MTJ</td>\n",
       "      <td>DEN</td>\n",
       "      <td>...</td>\n",
       "      <td>-107.54</td>\n",
       "      <td>+7</td>\n",
       "      <td>3017</td>\n",
       "      <td>DEN</td>\n",
       "      <td>5379</td>\n",
       "      <td>5431</td>\n",
       "      <td>5382</td>\n",
       "      <td>39.50</td>\n",
       "      <td>-104.40</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7858</th>\n",
       "      <td>JNU_SEA_32316</td>\n",
       "      <td>2004</td>\n",
       "      <td>3</td>\n",
       "      <td>18</td>\n",
       "      <td>4</td>\n",
       "      <td>1421</td>\n",
       "      <td>1738</td>\n",
       "      <td>137</td>\n",
       "      <td>JNU</td>\n",
       "      <td>SEA</td>\n",
       "      <td>...</td>\n",
       "      <td>-134.34</td>\n",
       "      <td>+9</td>\n",
       "      <td>24233</td>\n",
       "      <td>SEA</td>\n",
       "      <td>447</td>\n",
       "      <td>450</td>\n",
       "      <td>450</td>\n",
       "      <td>47.28</td>\n",
       "      <td>-122.19</td>\n",
       "      <td>+8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7859</th>\n",
       "      <td>DRO_DEN_27288</td>\n",
       "      <td>2004</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>3</td>\n",
       "      <td>800</td>\n",
       "      <td>914</td>\n",
       "      <td>74</td>\n",
       "      <td>DRO</td>\n",
       "      <td>DEN</td>\n",
       "      <td>...</td>\n",
       "      <td>-107.46</td>\n",
       "      <td>+7</td>\n",
       "      <td>3017</td>\n",
       "      <td>DEN</td>\n",
       "      <td>5379</td>\n",
       "      <td>5431</td>\n",
       "      <td>5382</td>\n",
       "      <td>39.50</td>\n",
       "      <td>-104.40</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7860</th>\n",
       "      <td>ERI_CVG_47268</td>\n",
       "      <td>2004</td>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>1335</td>\n",
       "      <td>1452</td>\n",
       "      <td>77</td>\n",
       "      <td>ERI</td>\n",
       "      <td>CVG</td>\n",
       "      <td>...</td>\n",
       "      <td>-80.11</td>\n",
       "      <td>+5</td>\n",
       "      <td>93814</td>\n",
       "      <td>CVG</td>\n",
       "      <td>882</td>\n",
       "      <td>883</td>\n",
       "      <td>885</td>\n",
       "      <td>39.02</td>\n",
       "      <td>-84.40</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7861 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       FlightNumber  Year  Month  DayOfMonth  DayOfWeek  ScheduledDepTime  \\\n",
       "0     BOS_CLE_29089  2004      9          20          1              1000   \n",
       "1     BOS_OAK_55049  2004      9          10          5               820   \n",
       "2     BOS_RSW_47586  2004      3          22          1               700   \n",
       "3     BOS_PIT_49759  2004      5           1          6               845   \n",
       "4     BOS_DCA_32791  2004      7          15          4              1300   \n",
       "...             ...   ...    ...         ...        ...               ...   \n",
       "7856  LAW_DFW_46776  2004     11          24          3              1756   \n",
       "7857  MTJ_DEN_35253  2004      9           8          3               640   \n",
       "7858  JNU_SEA_32316  2004      3          18          4              1421   \n",
       "7859  DRO_DEN_27288  2004     11          17          3               800   \n",
       "7860  ERI_CVG_47268  2004      5          18          2              1335   \n",
       "\n",
       "      ScheduledArrTime  ScheduledTravelTime Origin Destination  ...  \\\n",
       "0                 1154                  114    BOS         CLE  ...   \n",
       "1                 1200                  400    BOS         OAK  ...   \n",
       "2                 1036                  216    BOS         RSW  ...   \n",
       "3                 1031                  106    BOS         PIT  ...   \n",
       "4                 1430                   90    BOS         DCA  ...   \n",
       "...                ...                  ...    ...         ...  ...   \n",
       "7856              1857                   61    LAW         DFW  ...   \n",
       "7857               744                   64    MTJ         DEN  ...   \n",
       "7858              1738                  137    JNU         SEA  ...   \n",
       "7859               914                   74    DRO         DEN  ...   \n",
       "7860              1452                   77    ERI         CVG  ...   \n",
       "\n",
       "      Longitude_origin TimeZone_origin WeatherStationID_dest AirportID_dest  \\\n",
       "0               -71.01              +5                 14820            CLE   \n",
       "1               -71.01              +5                 23230            OAK   \n",
       "2               -71.01              +5                 12894            RSW   \n",
       "3               -71.01              +5                 94823            PIT   \n",
       "4               -71.01              +5                 13743            DCA   \n",
       "...                ...             ...                   ...            ...   \n",
       "7856            -98.25              +6                  3927            DFW   \n",
       "7857           -107.54              +7                  3017            DEN   \n",
       "7858           -134.34              +9                 24233            SEA   \n",
       "7859           -107.46              +7                  3017            DEN   \n",
       "7860            -80.11              +5                 93814            CVG   \n",
       "\n",
       "      GroundHeight_dest  StationHeight_dest  BarometerHeight_dest  \\\n",
       "0                   802                 805                   805   \n",
       "1                    85                  88                    88   \n",
       "2                    29                  30                    32   \n",
       "3                  1172                1203                  1175   \n",
       "4                    10                  65                     3   \n",
       "...                 ...                 ...                   ...   \n",
       "7856                559                 596                   562   \n",
       "7857               5379                5431                  5382   \n",
       "7858                447                 450                   450   \n",
       "7859               5379                5431                  5382   \n",
       "7860                882                 883                   885   \n",
       "\n",
       "     Latitude_dest  Longitude_dest  TimeZone_dest  \n",
       "0            41.24          -81.51             +5  \n",
       "1            37.43         -122.13             +8  \n",
       "2            26.32          -81.45             +5  \n",
       "3            40.30          -80.14             +5  \n",
       "4            38.52          -77.02             +5  \n",
       "...            ...             ...            ...  \n",
       "7856         32.54          -97.02             +6  \n",
       "7857         39.50         -104.40             +7  \n",
       "7858         47.28         -122.19             +8  \n",
       "7859         39.50         -104.40             +7  \n",
       "7860         39.02          -84.40             +5  \n",
       "\n",
       "[7861 rows x 32 columns]"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "0afb65ba",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7861, 32)"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "c291f689",
   "metadata": {},
   "outputs": [],
   "source": [
    "### Saving the dataframe into a csv file\n",
    "\n",
    "merged.to_csv('merged_with_target_allstations.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b9b0a6ed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['FlightNumber', 'Year', 'Month', 'DayOfMonth', 'DayOfWeek',\n",
       "       'ScheduledDepTime', 'ScheduledArrTime', 'ScheduledTravelTime', 'Origin',\n",
       "       'Destination', 'Distance', 'ActualArrivalTimeStamp', 'result', 'delay',\n",
       "       'delay_in_seconds', 'target', 'WeatherStationID_origin',\n",
       "       'AirportID_origin', 'GroundHeight_origin', 'StationHeight_origin',\n",
       "       'BarometerHeight_origin', 'Latitude_origin', 'Longitude_origin',\n",
       "       'TimeZone_origin', 'WeatherStationID_dest', 'AirportID_dest',\n",
       "       'GroundHeight_dest', 'StationHeight_dest', 'BarometerHeight_dest',\n",
       "       'Latitude_dest', 'Longitude_dest', 'TimeZone_dest'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df=pd.read_csv('merged_with_target_allstations.csv')\n",
    "df.drop('Unnamed: 0', axis=1, inplace=True)\n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ddbf33c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}