2.merge_allstations_test_f.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "81378105",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "9d1f4b77",
   "metadata": {},
   "outputs": [],
   "source": [
    "test=pd.read_csv('C:/Users/kiran/OneDrive/Desktop/hacakthon_final/ProblemStatement_Data&Instructions-1616503373207/DataForPHD_Forall/20210328_Testdata/Test.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DEN_XNA_62349</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>1516</td>\n",
       "      <td>1756</td>\n",
       "      <td>100</td>\n",
       "      <td>DEN</td>\n",
       "      <td>XNA</td>\n",
       "      <td>616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>RIC_EWR_87213</td>\n",
       "      <td>2005</td>\n",
       "      <td>11</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>1300</td>\n",
       "      <td>1415</td>\n",
       "      <td>75</td>\n",
       "      <td>RIC</td>\n",
       "      <td>EWR</td>\n",
       "      <td>278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>IAD_GSO_86576</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>28</td>\n",
       "      <td>3</td>\n",
       "      <td>1040</td>\n",
       "      <td>1144</td>\n",
       "      <td>64</td>\n",
       "      <td>IAD</td>\n",
       "      <td>GSO</td>\n",
       "      <td>239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SAV_CVG_87264</td>\n",
       "      <td>2005</td>\n",
       "      <td>11</td>\n",
       "      <td>18</td>\n",
       "      <td>5</td>\n",
       "      <td>1120</td>\n",
       "      <td>1302</td>\n",
       "      <td>102</td>\n",
       "      <td>SAV</td>\n",
       "      <td>CVG</td>\n",
       "      <td>515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SBP_LAX_68158</td>\n",
       "      <td>2005</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>630</td>\n",
       "      <td>733</td>\n",
       "      <td>63</td>\n",
       "      <td>SBP</td>\n",
       "      <td>LAX</td>\n",
       "      <td>155</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6561</th>\n",
       "      <td>LAX_EWR_82392</td>\n",
       "      <td>2005</td>\n",
       "      <td>11</td>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>1210</td>\n",
       "      <td>2012</td>\n",
       "      <td>302</td>\n",
       "      <td>LAX</td>\n",
       "      <td>EWR</td>\n",
       "      <td>2454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6562</th>\n",
       "      <td>ORD_BOS_71467</td>\n",
       "      <td>2005</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>5</td>\n",
       "      <td>1717</td>\n",
       "      <td>2038</td>\n",
       "      <td>141</td>\n",
       "      <td>ORD</td>\n",
       "      <td>BOS</td>\n",
       "      <td>867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6563</th>\n",
       "      <td>MCI_BWI_101601</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>26</td>\n",
       "      <td>6</td>\n",
       "      <td>1210</td>\n",
       "      <td>1525</td>\n",
       "      <td>135</td>\n",
       "      <td>MCI</td>\n",
       "      <td>BWI</td>\n",
       "      <td>967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6564</th>\n",
       "      <td>SLC_COD_68517</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>21</td>\n",
       "      <td>3</td>\n",
       "      <td>1550</td>\n",
       "      <td>1713</td>\n",
       "      <td>83</td>\n",
       "      <td>SLC</td>\n",
       "      <td>COD</td>\n",
       "      <td>298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6565</th>\n",
       "      <td>MCI_ABQ_65005</td>\n",
       "      <td>2005</td>\n",
       "      <td>7</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "      <td>1135</td>\n",
       "      <td>1235</td>\n",
       "      <td>120</td>\n",
       "      <td>MCI</td>\n",
       "      <td>ABQ</td>\n",
       "      <td>718</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6566 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        FlightNumber  Year  Month  DayofMonth  DayOfWeek  ScheduledDepTime  \\\n",
       "0      DEN_XNA_62349  2005      9          12          1              1516   \n",
       "1      RIC_EWR_87213  2005     11          14          1              1300   \n",
       "2      IAD_GSO_86576  2005      9          28          3              1040   \n",
       "3      SAV_CVG_87264  2005     11          18          5              1120   \n",
       "4      SBP_LAX_68158  2005      7           5          2               630   \n",
       "...              ...   ...    ...         ...        ...               ...   \n",
       "6561   LAX_EWR_82392  2005     11          21          1              1210   \n",
       "6562   ORD_BOS_71467  2005     11          25          5              1717   \n",
       "6563  MCI_BWI_101601  2005      3          26          6              1210   \n",
       "6564   SLC_COD_68517  2005      9          21          3              1550   \n",
       "6565   MCI_ABQ_65005  2005      7          19          2              1135   \n",
       "\n",
       "      ScheduledArrTime  ScheduledTravelTime Origin Destination  Distance  \n",
       "0                 1756                  100    DEN         XNA       616  \n",
       "1                 1415                   75    RIC         EWR       278  \n",
       "2                 1144                   64    IAD         GSO       239  \n",
       "3                 1302                  102    SAV         CVG       515  \n",
       "4                  733                   63    SBP         LAX       155  \n",
       "...                ...                  ...    ...         ...       ...  \n",
       "6561              2012                  302    LAX         EWR      2454  \n",
       "6562              2038                  141    ORD         BOS       867  \n",
       "6563              1525                  135    MCI         BWI       967  \n",
       "6564              1713                   83    SLC         COD       298  \n",
       "6565              1235                  120    MCI         ABQ       718  \n",
       "\n",
       "[6566 rows x 11 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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": 31,
   "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": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allstationsdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "7775a5b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "origin_df=pd.merge(test, allstationsdf, left_on='Origin', right_on='AirportID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>Distance</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>DEN_XNA_62349</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>1516</td>\n",
       "      <td>1756</td>\n",
       "      <td>100</td>\n",
       "      <td>DEN</td>\n",
       "      <td>XNA</td>\n",
       "      <td>616</td>\n",
       "      <td>3017</td>\n",
       "      <td>DEN</td>\n",
       "      <td>5379</td>\n",
       "      <td>5431</td>\n",
       "      <td>5382</td>\n",
       "      <td>39.5</td>\n",
       "      <td>-104.4</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DEN_SAT_82515</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>855</td>\n",
       "      <td>1157</td>\n",
       "      <td>122</td>\n",
       "      <td>DEN</td>\n",
       "      <td>SAT</td>\n",
       "      <td>794</td>\n",
       "      <td>3017</td>\n",
       "      <td>DEN</td>\n",
       "      <td>5379</td>\n",
       "      <td>5431</td>\n",
       "      <td>5382</td>\n",
       "      <td>39.5</td>\n",
       "      <td>-104.4</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DEN_ICT_94174</td>\n",
       "      <td>2005</td>\n",
       "      <td>7</td>\n",
       "      <td>23</td>\n",
       "      <td>6</td>\n",
       "      <td>1556</td>\n",
       "      <td>1817</td>\n",
       "      <td>81</td>\n",
       "      <td>DEN</td>\n",
       "      <td>ICT</td>\n",
       "      <td>419</td>\n",
       "      <td>3017</td>\n",
       "      <td>DEN</td>\n",
       "      <td>5379</td>\n",
       "      <td>5431</td>\n",
       "      <td>5382</td>\n",
       "      <td>39.5</td>\n",
       "      <td>-104.4</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DEN_SAT_78504</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>1230</td>\n",
       "      <td>1529</td>\n",
       "      <td>119</td>\n",
       "      <td>DEN</td>\n",
       "      <td>SAT</td>\n",
       "      <td>794</td>\n",
       "      <td>3017</td>\n",
       "      <td>DEN</td>\n",
       "      <td>5379</td>\n",
       "      <td>5431</td>\n",
       "      <td>5382</td>\n",
       "      <td>39.5</td>\n",
       "      <td>-104.4</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DEN_PHL_82820</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>23</td>\n",
       "      <td>3</td>\n",
       "      <td>1405</td>\n",
       "      <td>1925</td>\n",
       "      <td>200</td>\n",
       "      <td>DEN</td>\n",
       "      <td>PHL</td>\n",
       "      <td>1557</td>\n",
       "      <td>3017</td>\n",
       "      <td>DEN</td>\n",
       "      <td>5379</td>\n",
       "      <td>5431</td>\n",
       "      <td>5382</td>\n",
       "      <td>39.5</td>\n",
       "      <td>-104.4</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FlightNumber  Year  Month  DayofMonth  DayOfWeek  ScheduledDepTime  \\\n",
       "0  DEN_XNA_62349  2005      9          12          1              1516   \n",
       "1  DEN_SAT_82515  2005      9           6          2               855   \n",
       "2  DEN_ICT_94174  2005      7          23          6              1556   \n",
       "3  DEN_SAT_78504  2005      9           4          7              1230   \n",
       "4  DEN_PHL_82820  2005      3          23          3              1405   \n",
       "\n",
       "   ScheduledArrTime  ScheduledTravelTime Origin Destination  Distance  \\\n",
       "0              1756                  100    DEN         XNA       616   \n",
       "1              1157                  122    DEN         SAT       794   \n",
       "2              1817                   81    DEN         ICT       419   \n",
       "3              1529                  119    DEN         SAT       794   \n",
       "4              1925                  200    DEN         PHL      1557   \n",
       "\n",
       "   WeatherStationID AirportID  GroundHeight  StationHeight  BarometerHeight  \\\n",
       "0              3017       DEN          5379           5431             5382   \n",
       "1              3017       DEN          5379           5431             5382   \n",
       "2              3017       DEN          5379           5431             5382   \n",
       "3              3017       DEN          5379           5431             5382   \n",
       "4              3017       DEN          5379           5431             5382   \n",
       "\n",
       "   Latitude  Longitude TimeZone  \n",
       "0      39.5     -104.4       +7  \n",
       "1      39.5     -104.4       +7  \n",
       "2      39.5     -104.4       +7  \n",
       "3      39.5     -104.4       +7  \n",
       "4      39.5     -104.4       +7  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "origin_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>Distance</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>DEN_XNA_62349</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>1516</td>\n",
       "      <td>1756</td>\n",
       "      <td>100</td>\n",
       "      <td>DEN</td>\n",
       "      <td>XNA</td>\n",
       "      <td>616</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>1</th>\n",
       "      <td>DEN_SAT_82515</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>855</td>\n",
       "      <td>1157</td>\n",
       "      <td>122</td>\n",
       "      <td>DEN</td>\n",
       "      <td>SAT</td>\n",
       "      <td>794</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>2</th>\n",
       "      <td>DEN_ICT_94174</td>\n",
       "      <td>2005</td>\n",
       "      <td>7</td>\n",
       "      <td>23</td>\n",
       "      <td>6</td>\n",
       "      <td>1556</td>\n",
       "      <td>1817</td>\n",
       "      <td>81</td>\n",
       "      <td>DEN</td>\n",
       "      <td>ICT</td>\n",
       "      <td>419</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>3</th>\n",
       "      <td>DEN_SAT_78504</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>1230</td>\n",
       "      <td>1529</td>\n",
       "      <td>119</td>\n",
       "      <td>DEN</td>\n",
       "      <td>SAT</td>\n",
       "      <td>794</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>4</th>\n",
       "      <td>DEN_PHL_82820</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>23</td>\n",
       "      <td>3</td>\n",
       "      <td>1405</td>\n",
       "      <td>1925</td>\n",
       "      <td>200</td>\n",
       "      <td>DEN</td>\n",
       "      <td>PHL</td>\n",
       "      <td>1557</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>...</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6561</th>\n",
       "      <td>GTR_ATL_83479</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>1415</td>\n",
       "      <td>1629</td>\n",
       "      <td>74</td>\n",
       "      <td>GTR</td>\n",
       "      <td>ATL</td>\n",
       "      <td>241</td>\n",
       "      <td>53893</td>\n",
       "      <td>GTR</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33.27</td>\n",
       "      <td>-88.35</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6562</th>\n",
       "      <td>BRO_IAH_75040</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>1330</td>\n",
       "      <td>1444</td>\n",
       "      <td>74</td>\n",
       "      <td>BRO</td>\n",
       "      <td>IAH</td>\n",
       "      <td>308</td>\n",
       "      <td>12919</td>\n",
       "      <td>BRO</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "      <td>24</td>\n",
       "      <td>25.55</td>\n",
       "      <td>-97.26</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6563</th>\n",
       "      <td>BRO_IAH_98355</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "      <td>1330</td>\n",
       "      <td>1444</td>\n",
       "      <td>74</td>\n",
       "      <td>BRO</td>\n",
       "      <td>IAH</td>\n",
       "      <td>308</td>\n",
       "      <td>12919</td>\n",
       "      <td>BRO</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "      <td>24</td>\n",
       "      <td>25.55</td>\n",
       "      <td>-97.26</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6564</th>\n",
       "      <td>FSM_DFW_89528</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>750</td>\n",
       "      <td>856</td>\n",
       "      <td>66</td>\n",
       "      <td>FSM</td>\n",
       "      <td>DFW</td>\n",
       "      <td>228</td>\n",
       "      <td>13964</td>\n",
       "      <td>FSM</td>\n",
       "      <td>477</td>\n",
       "      <td>462</td>\n",
       "      <td>480</td>\n",
       "      <td>35.20</td>\n",
       "      <td>-94.22</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6565</th>\n",
       "      <td>CMI_DFW_66446</td>\n",
       "      <td>2005</td>\n",
       "      <td>7</td>\n",
       "      <td>24</td>\n",
       "      <td>7</td>\n",
       "      <td>739</td>\n",
       "      <td>939</td>\n",
       "      <td>120</td>\n",
       "      <td>CMI</td>\n",
       "      <td>DFW</td>\n",
       "      <td>693</td>\n",
       "      <td>94870</td>\n",
       "      <td>CMI</td>\n",
       "      <td>749</td>\n",
       "      <td>763</td>\n",
       "      <td>752</td>\n",
       "      <td>40.02</td>\n",
       "      <td>-88.17</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6566 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       FlightNumber  Year  Month  DayofMonth  DayOfWeek  ScheduledDepTime  \\\n",
       "0     DEN_XNA_62349  2005      9          12          1              1516   \n",
       "1     DEN_SAT_82515  2005      9           6          2               855   \n",
       "2     DEN_ICT_94174  2005      7          23          6              1556   \n",
       "3     DEN_SAT_78504  2005      9           4          7              1230   \n",
       "4     DEN_PHL_82820  2005      3          23          3              1405   \n",
       "...             ...   ...    ...         ...        ...               ...   \n",
       "6561  GTR_ATL_83479  2005      3          20          7              1415   \n",
       "6562  BRO_IAH_75040  2005      3           7          1              1330   \n",
       "6563  BRO_IAH_98355  2005      3          16          3              1330   \n",
       "6564  FSM_DFW_89528  2005      3           7          1               750   \n",
       "6565  CMI_DFW_66446  2005      7          24          7               739   \n",
       "\n",
       "      ScheduledArrTime  ScheduledTravelTime Origin Destination  Distance  \\\n",
       "0                 1756                  100    DEN         XNA       616   \n",
       "1                 1157                  122    DEN         SAT       794   \n",
       "2                 1817                   81    DEN         ICT       419   \n",
       "3                 1529                  119    DEN         SAT       794   \n",
       "4                 1925                  200    DEN         PHL      1557   \n",
       "...                ...                  ...    ...         ...       ...   \n",
       "6561              1629                   74    GTR         ATL       241   \n",
       "6562              1444                   74    BRO         IAH       308   \n",
       "6563              1444                   74    BRO         IAH       308   \n",
       "6564               856                   66    FSM         DFW       228   \n",
       "6565               939                  120    CMI         DFW       693   \n",
       "\n",
       "      WeatherStationID AirportID  GroundHeight  StationHeight  \\\n",
       "0                 3017       DEN          5379           5431   \n",
       "1                 3017       DEN          5379           5431   \n",
       "2                 3017       DEN          5379           5431   \n",
       "3                 3017       DEN          5379           5431   \n",
       "4                 3017       DEN          5379           5431   \n",
       "...                ...       ...           ...            ...   \n",
       "6561             53893       GTR             0              0   \n",
       "6562             12919       BRO            21             22   \n",
       "6563             12919       BRO            21             22   \n",
       "6564             13964       FSM           477            462   \n",
       "6565             94870       CMI           749            763   \n",
       "\n",
       "      BarometerHeight  Latitude  Longitude TimeZone  \n",
       "0                5382     39.50    -104.40       +7  \n",
       "1                5382     39.50    -104.40       +7  \n",
       "2                5382     39.50    -104.40       +7  \n",
       "3                5382     39.50    -104.40       +7  \n",
       "4                5382     39.50    -104.40       +7  \n",
       "...               ...       ...        ...      ...  \n",
       "6561                0     33.27     -88.35       +6  \n",
       "6562               24     25.55     -97.26       +6  \n",
       "6563               24     25.55     -97.26       +6  \n",
       "6564              480     35.20     -94.22       +6  \n",
       "6565              752     40.02     -88.17       +6  \n",
       "\n",
       "[6566 rows x 19 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "origin_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "5e4444fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "dest_df=pd.merge(test, allstationsdf, left_on='Destination', right_on='AirportID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>Distance</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>DEN_XNA_62349</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>1516</td>\n",
       "      <td>1756</td>\n",
       "      <td>100</td>\n",
       "      <td>DEN</td>\n",
       "      <td>XNA</td>\n",
       "      <td>616</td>\n",
       "      <td>53922</td>\n",
       "      <td>XNA</td>\n",
       "      <td>1269</td>\n",
       "      <td>1319</td>\n",
       "      <td>1272</td>\n",
       "      <td>36.17</td>\n",
       "      <td>-94.19</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD_XNA_65795</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>5</td>\n",
       "      <td>800</td>\n",
       "      <td>948</td>\n",
       "      <td>108</td>\n",
       "      <td>ORD</td>\n",
       "      <td>XNA</td>\n",
       "      <td>522</td>\n",
       "      <td>53922</td>\n",
       "      <td>XNA</td>\n",
       "      <td>1269</td>\n",
       "      <td>1319</td>\n",
       "      <td>1272</td>\n",
       "      <td>36.17</td>\n",
       "      <td>-94.19</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD_XNA_98655</td>\n",
       "      <td>2005</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>800</td>\n",
       "      <td>947</td>\n",
       "      <td>107</td>\n",
       "      <td>ORD</td>\n",
       "      <td>XNA</td>\n",
       "      <td>522</td>\n",
       "      <td>53922</td>\n",
       "      <td>XNA</td>\n",
       "      <td>1269</td>\n",
       "      <td>1319</td>\n",
       "      <td>1272</td>\n",
       "      <td>36.17</td>\n",
       "      <td>-94.19</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DFW_XNA_75074</td>\n",
       "      <td>2005</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>1219</td>\n",
       "      <td>1327</td>\n",
       "      <td>68</td>\n",
       "      <td>DFW</td>\n",
       "      <td>XNA</td>\n",
       "      <td>281</td>\n",
       "      <td>53922</td>\n",
       "      <td>XNA</td>\n",
       "      <td>1269</td>\n",
       "      <td>1319</td>\n",
       "      <td>1272</td>\n",
       "      <td>36.17</td>\n",
       "      <td>-94.19</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ATL_XNA_81150</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>708</td>\n",
       "      <td>758</td>\n",
       "      <td>110</td>\n",
       "      <td>ATL</td>\n",
       "      <td>XNA</td>\n",
       "      <td>589</td>\n",
       "      <td>53922</td>\n",
       "      <td>XNA</td>\n",
       "      <td>1269</td>\n",
       "      <td>1319</td>\n",
       "      <td>1272</td>\n",
       "      <td>36.17</td>\n",
       "      <td>-94.19</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FlightNumber  Year  Month  DayofMonth  DayOfWeek  ScheduledDepTime  \\\n",
       "0  DEN_XNA_62349  2005      9          12          1              1516   \n",
       "1  ORD_XNA_65795  2005      9          30          5               800   \n",
       "2  ORD_XNA_98655  2005     11          11          5               800   \n",
       "3  DFW_XNA_75074  2005     11           8          2              1219   \n",
       "4  ATL_XNA_81150  2005      3           1          2               708   \n",
       "\n",
       "   ScheduledArrTime  ScheduledTravelTime Origin Destination  Distance  \\\n",
       "0              1756                  100    DEN         XNA       616   \n",
       "1               948                  108    ORD         XNA       522   \n",
       "2               947                  107    ORD         XNA       522   \n",
       "3              1327                   68    DFW         XNA       281   \n",
       "4               758                  110    ATL         XNA       589   \n",
       "\n",
       "   WeatherStationID AirportID  GroundHeight  StationHeight  BarometerHeight  \\\n",
       "0             53922       XNA          1269           1319             1272   \n",
       "1             53922       XNA          1269           1319             1272   \n",
       "2             53922       XNA          1269           1319             1272   \n",
       "3             53922       XNA          1269           1319             1272   \n",
       "4             53922       XNA          1269           1319             1272   \n",
       "\n",
       "   Latitude  Longitude TimeZone  \n",
       "0     36.17     -94.19       +6  \n",
       "1     36.17     -94.19       +6  \n",
       "2     36.17     -94.19       +6  \n",
       "3     36.17     -94.19       +6  \n",
       "4     36.17     -94.19       +6  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dest_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>Distance</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>DEN_XNA_62349</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>1516</td>\n",
       "      <td>1756</td>\n",
       "      <td>100</td>\n",
       "      <td>DEN</td>\n",
       "      <td>XNA</td>\n",
       "      <td>616</td>\n",
       "      <td>53922</td>\n",
       "      <td>XNA</td>\n",
       "      <td>1269</td>\n",
       "      <td>1319</td>\n",
       "      <td>1272</td>\n",
       "      <td>36.17</td>\n",
       "      <td>-94.19</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD_XNA_65795</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>5</td>\n",
       "      <td>800</td>\n",
       "      <td>948</td>\n",
       "      <td>108</td>\n",
       "      <td>ORD</td>\n",
       "      <td>XNA</td>\n",
       "      <td>522</td>\n",
       "      <td>53922</td>\n",
       "      <td>XNA</td>\n",
       "      <td>1269</td>\n",
       "      <td>1319</td>\n",
       "      <td>1272</td>\n",
       "      <td>36.17</td>\n",
       "      <td>-94.19</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD_XNA_98655</td>\n",
       "      <td>2005</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>800</td>\n",
       "      <td>947</td>\n",
       "      <td>107</td>\n",
       "      <td>ORD</td>\n",
       "      <td>XNA</td>\n",
       "      <td>522</td>\n",
       "      <td>53922</td>\n",
       "      <td>XNA</td>\n",
       "      <td>1269</td>\n",
       "      <td>1319</td>\n",
       "      <td>1272</td>\n",
       "      <td>36.17</td>\n",
       "      <td>-94.19</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DFW_XNA_75074</td>\n",
       "      <td>2005</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>1219</td>\n",
       "      <td>1327</td>\n",
       "      <td>68</td>\n",
       "      <td>DFW</td>\n",
       "      <td>XNA</td>\n",
       "      <td>281</td>\n",
       "      <td>53922</td>\n",
       "      <td>XNA</td>\n",
       "      <td>1269</td>\n",
       "      <td>1319</td>\n",
       "      <td>1272</td>\n",
       "      <td>36.17</td>\n",
       "      <td>-94.19</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ATL_XNA_81150</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>708</td>\n",
       "      <td>758</td>\n",
       "      <td>110</td>\n",
       "      <td>ATL</td>\n",
       "      <td>XNA</td>\n",
       "      <td>589</td>\n",
       "      <td>53922</td>\n",
       "      <td>XNA</td>\n",
       "      <td>1269</td>\n",
       "      <td>1319</td>\n",
       "      <td>1272</td>\n",
       "      <td>36.17</td>\n",
       "      <td>-94.19</td>\n",
       "      <td>+6</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6561</th>\n",
       "      <td>CVG_ACY_87937</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>18</td>\n",
       "      <td>5</td>\n",
       "      <td>1140</td>\n",
       "      <td>1321</td>\n",
       "      <td>101</td>\n",
       "      <td>CVG</td>\n",
       "      <td>ACY</td>\n",
       "      <td>542</td>\n",
       "      <td>93730</td>\n",
       "      <td>ACY</td>\n",
       "      <td>114</td>\n",
       "      <td>67</td>\n",
       "      <td>117</td>\n",
       "      <td>39.28</td>\n",
       "      <td>-74.28</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6562</th>\n",
       "      <td>DEN_BIL_99332</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>29</td>\n",
       "      <td>4</td>\n",
       "      <td>1425</td>\n",
       "      <td>1549</td>\n",
       "      <td>84</td>\n",
       "      <td>DEN</td>\n",
       "      <td>BIL</td>\n",
       "      <td>455</td>\n",
       "      <td>24033</td>\n",
       "      <td>BIL</td>\n",
       "      <td>3579</td>\n",
       "      <td>3570</td>\n",
       "      <td>3582</td>\n",
       "      <td>45.49</td>\n",
       "      <td>-108.32</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6563</th>\n",
       "      <td>SLC_COD_96091</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "      <td>1550</td>\n",
       "      <td>1713</td>\n",
       "      <td>83</td>\n",
       "      <td>SLC</td>\n",
       "      <td>COD</td>\n",
       "      <td>298</td>\n",
       "      <td>24045</td>\n",
       "      <td>COD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>44.37</td>\n",
       "      <td>-108.58</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6564</th>\n",
       "      <td>SLC_COD_68517</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>21</td>\n",
       "      <td>3</td>\n",
       "      <td>1550</td>\n",
       "      <td>1713</td>\n",
       "      <td>83</td>\n",
       "      <td>SLC</td>\n",
       "      <td>COD</td>\n",
       "      <td>298</td>\n",
       "      <td>24045</td>\n",
       "      <td>COD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>44.37</td>\n",
       "      <td>-108.58</td>\n",
       "      <td>+7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6565</th>\n",
       "      <td>ANC_SCC_62899</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>1137</td>\n",
       "      <td>1320</td>\n",
       "      <td>103</td>\n",
       "      <td>ANC</td>\n",
       "      <td>SCC</td>\n",
       "      <td>626</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>6566 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       FlightNumber  Year  Month  DayofMonth  DayOfWeek  ScheduledDepTime  \\\n",
       "0     DEN_XNA_62349  2005      9          12          1              1516   \n",
       "1     ORD_XNA_65795  2005      9          30          5               800   \n",
       "2     ORD_XNA_98655  2005     11          11          5               800   \n",
       "3     DFW_XNA_75074  2005     11           8          2              1219   \n",
       "4     ATL_XNA_81150  2005      3           1          2               708   \n",
       "...             ...   ...    ...         ...        ...               ...   \n",
       "6561  CVG_ACY_87937  2005      3          18          5              1140   \n",
       "6562  DEN_BIL_99332  2005      9          29          4              1425   \n",
       "6563  SLC_COD_96091  2005      9          17          6              1550   \n",
       "6564  SLC_COD_68517  2005      9          21          3              1550   \n",
       "6565  ANC_SCC_62899  2005      3           8          2              1137   \n",
       "\n",
       "      ScheduledArrTime  ScheduledTravelTime Origin Destination  Distance  \\\n",
       "0                 1756                  100    DEN         XNA       616   \n",
       "1                  948                  108    ORD         XNA       522   \n",
       "2                  947                  107    ORD         XNA       522   \n",
       "3                 1327                   68    DFW         XNA       281   \n",
       "4                  758                  110    ATL         XNA       589   \n",
       "...                ...                  ...    ...         ...       ...   \n",
       "6561              1321                  101    CVG         ACY       542   \n",
       "6562              1549                   84    DEN         BIL       455   \n",
       "6563              1713                   83    SLC         COD       298   \n",
       "6564              1713                   83    SLC         COD       298   \n",
       "6565              1320                  103    ANC         SCC       626   \n",
       "\n",
       "      WeatherStationID AirportID  GroundHeight  StationHeight  \\\n",
       "0                53922       XNA          1269           1319   \n",
       "1                53922       XNA          1269           1319   \n",
       "2                53922       XNA          1269           1319   \n",
       "3                53922       XNA          1269           1319   \n",
       "4                53922       XNA          1269           1319   \n",
       "...                ...       ...           ...            ...   \n",
       "6561             93730       ACY           114             67   \n",
       "6562             24033       BIL          3579           3570   \n",
       "6563             24045       COD             0              0   \n",
       "6564             24045       COD             0              0   \n",
       "6565             27406       SCC            66             60   \n",
       "\n",
       "      BarometerHeight  Latitude  Longitude TimeZone  \n",
       "0                1272     36.17     -94.19       +6  \n",
       "1                1272     36.17     -94.19       +6  \n",
       "2                1272     36.17     -94.19       +6  \n",
       "3                1272     36.17     -94.19       +6  \n",
       "4                1272     36.17     -94.19       +6  \n",
       "...               ...       ...        ...      ...  \n",
       "6561              117     39.28     -74.28       +5  \n",
       "6562             3582     45.49    -108.32       +7  \n",
       "6563                0     44.37    -108.58       +7  \n",
       "6564                0     44.37    -108.58       +7  \n",
       "6565               69     70.11    -148.29       +9  \n",
       "\n",
       "[6566 rows x 19 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dest_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "c08e6edc",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged=pd.merge(origin_df,dest_df, on='FlightNumber',how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>Destination_y</th>\n",
       "      <th>Distance_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>DEN_XNA_62349</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>1516</td>\n",
       "      <td>1756</td>\n",
       "      <td>100</td>\n",
       "      <td>DEN</td>\n",
       "      <td>XNA</td>\n",
       "      <td>...</td>\n",
       "      <td>XNA</td>\n",
       "      <td>616</td>\n",
       "      <td>53922</td>\n",
       "      <td>XNA</td>\n",
       "      <td>1269</td>\n",
       "      <td>1319</td>\n",
       "      <td>1272</td>\n",
       "      <td>36.17</td>\n",
       "      <td>-94.19</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DEN_SAT_82515</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>855</td>\n",
       "      <td>1157</td>\n",
       "      <td>122</td>\n",
       "      <td>DEN</td>\n",
       "      <td>SAT</td>\n",
       "      <td>...</td>\n",
       "      <td>SAT</td>\n",
       "      <td>794</td>\n",
       "      <td>12921</td>\n",
       "      <td>SAT</td>\n",
       "      <td>818</td>\n",
       "      <td>809</td>\n",
       "      <td>821</td>\n",
       "      <td>29.32</td>\n",
       "      <td>-98.28</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DEN_ICT_94174</td>\n",
       "      <td>2005</td>\n",
       "      <td>7</td>\n",
       "      <td>23</td>\n",
       "      <td>6</td>\n",
       "      <td>1556</td>\n",
       "      <td>1817</td>\n",
       "      <td>81</td>\n",
       "      <td>DEN</td>\n",
       "      <td>ICT</td>\n",
       "      <td>...</td>\n",
       "      <td>ICT</td>\n",
       "      <td>419</td>\n",
       "      <td>3928</td>\n",
       "      <td>ICT</td>\n",
       "      <td>1338</td>\n",
       "      <td>1340</td>\n",
       "      <td>1341</td>\n",
       "      <td>37.39</td>\n",
       "      <td>-97.26</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DEN_SAT_78504</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>1230</td>\n",
       "      <td>1529</td>\n",
       "      <td>119</td>\n",
       "      <td>DEN</td>\n",
       "      <td>SAT</td>\n",
       "      <td>...</td>\n",
       "      <td>SAT</td>\n",
       "      <td>794</td>\n",
       "      <td>12921</td>\n",
       "      <td>SAT</td>\n",
       "      <td>818</td>\n",
       "      <td>809</td>\n",
       "      <td>821</td>\n",
       "      <td>29.32</td>\n",
       "      <td>-98.28</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DEN_PHL_82820</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>23</td>\n",
       "      <td>3</td>\n",
       "      <td>1405</td>\n",
       "      <td>1925</td>\n",
       "      <td>200</td>\n",
       "      <td>DEN</td>\n",
       "      <td>PHL</td>\n",
       "      <td>...</td>\n",
       "      <td>PHL</td>\n",
       "      <td>1557</td>\n",
       "      <td>13739</td>\n",
       "      <td>PHL</td>\n",
       "      <td>59</td>\n",
       "      <td>28</td>\n",
       "      <td>62</td>\n",
       "      <td>39.52</td>\n",
       "      <td>-75.14</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>6561</th>\n",
       "      <td>GTR_ATL_83479</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>1415</td>\n",
       "      <td>1629</td>\n",
       "      <td>74</td>\n",
       "      <td>GTR</td>\n",
       "      <td>ATL</td>\n",
       "      <td>...</td>\n",
       "      <td>ATL</td>\n",
       "      <td>241</td>\n",
       "      <td>13874</td>\n",
       "      <td>ATL</td>\n",
       "      <td>971</td>\n",
       "      <td>1026</td>\n",
       "      <td>974</td>\n",
       "      <td>33.38</td>\n",
       "      <td>-84.26</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6562</th>\n",
       "      <td>BRO_IAH_75040</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>1330</td>\n",
       "      <td>1444</td>\n",
       "      <td>74</td>\n",
       "      <td>BRO</td>\n",
       "      <td>IAH</td>\n",
       "      <td>...</td>\n",
       "      <td>IAH</td>\n",
       "      <td>308</td>\n",
       "      <td>12960</td>\n",
       "      <td>IAH</td>\n",
       "      <td>118</td>\n",
       "      <td>108</td>\n",
       "      <td>121</td>\n",
       "      <td>29.59</td>\n",
       "      <td>-95.22</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6563</th>\n",
       "      <td>BRO_IAH_98355</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "      <td>1330</td>\n",
       "      <td>1444</td>\n",
       "      <td>74</td>\n",
       "      <td>BRO</td>\n",
       "      <td>IAH</td>\n",
       "      <td>...</td>\n",
       "      <td>IAH</td>\n",
       "      <td>308</td>\n",
       "      <td>12960</td>\n",
       "      <td>IAH</td>\n",
       "      <td>118</td>\n",
       "      <td>108</td>\n",
       "      <td>121</td>\n",
       "      <td>29.59</td>\n",
       "      <td>-95.22</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6564</th>\n",
       "      <td>FSM_DFW_89528</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>750</td>\n",
       "      <td>856</td>\n",
       "      <td>66</td>\n",
       "      <td>FSM</td>\n",
       "      <td>DFW</td>\n",
       "      <td>...</td>\n",
       "      <td>DFW</td>\n",
       "      <td>228</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>6565</th>\n",
       "      <td>CMI_DFW_66446</td>\n",
       "      <td>2005</td>\n",
       "      <td>7</td>\n",
       "      <td>24</td>\n",
       "      <td>7</td>\n",
       "      <td>739</td>\n",
       "      <td>939</td>\n",
       "      <td>120</td>\n",
       "      <td>CMI</td>\n",
       "      <td>DFW</td>\n",
       "      <td>...</td>\n",
       "      <td>DFW</td>\n",
       "      <td>693</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>6566 rows × 37 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       FlightNumber  Year_x  Month_x  DayofMonth_x  DayOfWeek_x  \\\n",
       "0     DEN_XNA_62349    2005        9            12            1   \n",
       "1     DEN_SAT_82515    2005        9             6            2   \n",
       "2     DEN_ICT_94174    2005        7            23            6   \n",
       "3     DEN_SAT_78504    2005        9             4            7   \n",
       "4     DEN_PHL_82820    2005        3            23            3   \n",
       "...             ...     ...      ...           ...          ...   \n",
       "6561  GTR_ATL_83479    2005        3            20            7   \n",
       "6562  BRO_IAH_75040    2005        3             7            1   \n",
       "6563  BRO_IAH_98355    2005        3            16            3   \n",
       "6564  FSM_DFW_89528    2005        3             7            1   \n",
       "6565  CMI_DFW_66446    2005        7            24            7   \n",
       "\n",
       "      ScheduledDepTime_x  ScheduledArrTime_x  ScheduledTravelTime_x Origin_x  \\\n",
       "0                   1516                1756                    100      DEN   \n",
       "1                    855                1157                    122      DEN   \n",
       "2                   1556                1817                     81      DEN   \n",
       "3                   1230                1529                    119      DEN   \n",
       "4                   1405                1925                    200      DEN   \n",
       "...                  ...                 ...                    ...      ...   \n",
       "6561                1415                1629                     74      GTR   \n",
       "6562                1330                1444                     74      BRO   \n",
       "6563                1330                1444                     74      BRO   \n",
       "6564                 750                 856                     66      FSM   \n",
       "6565                 739                 939                    120      CMI   \n",
       "\n",
       "     Destination_x  ...  Destination_y  Distance_y WeatherStationID_y  \\\n",
       "0              XNA  ...            XNA         616              53922   \n",
       "1              SAT  ...            SAT         794              12921   \n",
       "2              ICT  ...            ICT         419               3928   \n",
       "3              SAT  ...            SAT         794              12921   \n",
       "4              PHL  ...            PHL        1557              13739   \n",
       "...            ...  ...            ...         ...                ...   \n",
       "6561           ATL  ...            ATL         241              13874   \n",
       "6562           IAH  ...            IAH         308              12960   \n",
       "6563           IAH  ...            IAH         308              12960   \n",
       "6564           DFW  ...            DFW         228               3927   \n",
       "6565           DFW  ...            DFW         693               3927   \n",
       "\n",
       "      AirportID_y  GroundHeight_y  StationHeight_y  BarometerHeight_y  \\\n",
       "0             XNA            1269             1319               1272   \n",
       "1             SAT             818              809                821   \n",
       "2             ICT            1338             1340               1341   \n",
       "3             SAT             818              809                821   \n",
       "4             PHL              59               28                 62   \n",
       "...           ...             ...              ...                ...   \n",
       "6561          ATL             971             1026                974   \n",
       "6562          IAH             118              108                121   \n",
       "6563          IAH             118              108                121   \n",
       "6564          DFW             559              596                562   \n",
       "6565          DFW             559              596                562   \n",
       "\n",
       "      Latitude_y Longitude_y  TimeZone_y  \n",
       "0          36.17      -94.19          +6  \n",
       "1          29.32      -98.28          +6  \n",
       "2          37.39      -97.26          +6  \n",
       "3          29.32      -98.28          +6  \n",
       "4          39.52      -75.14          +5  \n",
       "...          ...         ...         ...  \n",
       "6561       33.38      -84.26          +5  \n",
       "6562       29.59      -95.22          +6  \n",
       "6563       29.59      -95.22          +6  \n",
       "6564       32.54      -97.02          +6  \n",
       "6565       32.54      -97.02          +6  \n",
       "\n",
       "[6566 rows x 37 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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', 'WeatherStationID_x',\n",
       "       'AirportID_x', 'GroundHeight_x', 'StationHeight_x', 'BarometerHeight_x',\n",
       "       'Latitude_x', 'Longitude_x', 'TimeZone_x', 'Year_y', 'Month_y',\n",
       "       'DayofMonth_y', 'DayOfWeek_y', 'ScheduledDepTime_y',\n",
       "       'ScheduledArrTime_y', 'ScheduledTravelTime_y', 'Origin_y',\n",
       "       'Destination_y', 'Distance_y', 'WeatherStationID_y', 'AirportID_y',\n",
       "       'GroundHeight_y', 'StationHeight_y', 'BarometerHeight_y', 'Latitude_y',\n",
       "       'Longitude_y', 'TimeZone_y'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "7acd8785",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['FlightNumber', 'Year', 'Month', 'DayofMonth', 'DayOfWeek',\n",
       "       'ScheduledDepTime', 'ScheduledArrTime', 'ScheduledTravelTime', 'Origin',\n",
       "       'Destination', 'Distance'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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', 'WeatherStationID_x',\n",
       "       'AirportID_x', 'GroundHeight_x', 'StationHeight_x', 'BarometerHeight_x',\n",
       "       'Latitude_x', 'Longitude_x', 'TimeZone_x', 'WeatherStationID_y',\n",
       "       'AirportID_y', 'GroundHeight_y', 'StationHeight_y', 'BarometerHeight_y',\n",
       "       'Latitude_y', 'Longitude_y', 'TimeZone_y'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "a46693e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged.columns=['FlightNumber', 'Year', 'Month', 'DayofMonth', 'DayOfWeek',\n",
    "       'ScheduledDepTime', 'ScheduledArrTime', 'ScheduledTravelTime',\n",
    "       'Origin', 'Destination', 'Distance', 'WeatherStationID_origin',\n",
    "       'AirportID_origin', 'GroundHeight_origin', 'StationHeight_origin', 'BarometerHeight_origin',\n",
    "       'Latitude_origin', 'Longitude_origin', 'TimeZone_origin', 'WeatherStationID_dest',\n",
    "       'AirportID_dest', 'GroundHeight_dest', 'StationHeight_dest', 'BarometerHeight_dest',\n",
    "       'Latitude_dest', 'Longitude_dest', 'TimeZone_dest']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>DEN_XNA_62349</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>1516</td>\n",
       "      <td>1756</td>\n",
       "      <td>100</td>\n",
       "      <td>DEN</td>\n",
       "      <td>XNA</td>\n",
       "      <td>...</td>\n",
       "      <td>-104.40</td>\n",
       "      <td>+7</td>\n",
       "      <td>53922</td>\n",
       "      <td>XNA</td>\n",
       "      <td>1269</td>\n",
       "      <td>1319</td>\n",
       "      <td>1272</td>\n",
       "      <td>36.17</td>\n",
       "      <td>-94.19</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DEN_SAT_82515</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>855</td>\n",
       "      <td>1157</td>\n",
       "      <td>122</td>\n",
       "      <td>DEN</td>\n",
       "      <td>SAT</td>\n",
       "      <td>...</td>\n",
       "      <td>-104.40</td>\n",
       "      <td>+7</td>\n",
       "      <td>12921</td>\n",
       "      <td>SAT</td>\n",
       "      <td>818</td>\n",
       "      <td>809</td>\n",
       "      <td>821</td>\n",
       "      <td>29.32</td>\n",
       "      <td>-98.28</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DEN_ICT_94174</td>\n",
       "      <td>2005</td>\n",
       "      <td>7</td>\n",
       "      <td>23</td>\n",
       "      <td>6</td>\n",
       "      <td>1556</td>\n",
       "      <td>1817</td>\n",
       "      <td>81</td>\n",
       "      <td>DEN</td>\n",
       "      <td>ICT</td>\n",
       "      <td>...</td>\n",
       "      <td>-104.40</td>\n",
       "      <td>+7</td>\n",
       "      <td>3928</td>\n",
       "      <td>ICT</td>\n",
       "      <td>1338</td>\n",
       "      <td>1340</td>\n",
       "      <td>1341</td>\n",
       "      <td>37.39</td>\n",
       "      <td>-97.26</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DEN_SAT_78504</td>\n",
       "      <td>2005</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>1230</td>\n",
       "      <td>1529</td>\n",
       "      <td>119</td>\n",
       "      <td>DEN</td>\n",
       "      <td>SAT</td>\n",
       "      <td>...</td>\n",
       "      <td>-104.40</td>\n",
       "      <td>+7</td>\n",
       "      <td>12921</td>\n",
       "      <td>SAT</td>\n",
       "      <td>818</td>\n",
       "      <td>809</td>\n",
       "      <td>821</td>\n",
       "      <td>29.32</td>\n",
       "      <td>-98.28</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DEN_PHL_82820</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>23</td>\n",
       "      <td>3</td>\n",
       "      <td>1405</td>\n",
       "      <td>1925</td>\n",
       "      <td>200</td>\n",
       "      <td>DEN</td>\n",
       "      <td>PHL</td>\n",
       "      <td>...</td>\n",
       "      <td>-104.40</td>\n",
       "      <td>+7</td>\n",
       "      <td>13739</td>\n",
       "      <td>PHL</td>\n",
       "      <td>59</td>\n",
       "      <td>28</td>\n",
       "      <td>62</td>\n",
       "      <td>39.52</td>\n",
       "      <td>-75.14</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>6561</th>\n",
       "      <td>GTR_ATL_83479</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>1415</td>\n",
       "      <td>1629</td>\n",
       "      <td>74</td>\n",
       "      <td>GTR</td>\n",
       "      <td>ATL</td>\n",
       "      <td>...</td>\n",
       "      <td>-88.35</td>\n",
       "      <td>+6</td>\n",
       "      <td>13874</td>\n",
       "      <td>ATL</td>\n",
       "      <td>971</td>\n",
       "      <td>1026</td>\n",
       "      <td>974</td>\n",
       "      <td>33.38</td>\n",
       "      <td>-84.26</td>\n",
       "      <td>+5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6562</th>\n",
       "      <td>BRO_IAH_75040</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>1330</td>\n",
       "      <td>1444</td>\n",
       "      <td>74</td>\n",
       "      <td>BRO</td>\n",
       "      <td>IAH</td>\n",
       "      <td>...</td>\n",
       "      <td>-97.26</td>\n",
       "      <td>+6</td>\n",
       "      <td>12960</td>\n",
       "      <td>IAH</td>\n",
       "      <td>118</td>\n",
       "      <td>108</td>\n",
       "      <td>121</td>\n",
       "      <td>29.59</td>\n",
       "      <td>-95.22</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6563</th>\n",
       "      <td>BRO_IAH_98355</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "      <td>1330</td>\n",
       "      <td>1444</td>\n",
       "      <td>74</td>\n",
       "      <td>BRO</td>\n",
       "      <td>IAH</td>\n",
       "      <td>...</td>\n",
       "      <td>-97.26</td>\n",
       "      <td>+6</td>\n",
       "      <td>12960</td>\n",
       "      <td>IAH</td>\n",
       "      <td>118</td>\n",
       "      <td>108</td>\n",
       "      <td>121</td>\n",
       "      <td>29.59</td>\n",
       "      <td>-95.22</td>\n",
       "      <td>+6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6564</th>\n",
       "      <td>FSM_DFW_89528</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>750</td>\n",
       "      <td>856</td>\n",
       "      <td>66</td>\n",
       "      <td>FSM</td>\n",
       "      <td>DFW</td>\n",
       "      <td>...</td>\n",
       "      <td>-94.22</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>6565</th>\n",
       "      <td>CMI_DFW_66446</td>\n",
       "      <td>2005</td>\n",
       "      <td>7</td>\n",
       "      <td>24</td>\n",
       "      <td>7</td>\n",
       "      <td>739</td>\n",
       "      <td>939</td>\n",
       "      <td>120</td>\n",
       "      <td>CMI</td>\n",
       "      <td>DFW</td>\n",
       "      <td>...</td>\n",
       "      <td>-88.17</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>6566 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       FlightNumber  Year  Month  DayofMonth  DayOfWeek  ScheduledDepTime  \\\n",
       "0     DEN_XNA_62349  2005      9          12          1              1516   \n",
       "1     DEN_SAT_82515  2005      9           6          2               855   \n",
       "2     DEN_ICT_94174  2005      7          23          6              1556   \n",
       "3     DEN_SAT_78504  2005      9           4          7              1230   \n",
       "4     DEN_PHL_82820  2005      3          23          3              1405   \n",
       "...             ...   ...    ...         ...        ...               ...   \n",
       "6561  GTR_ATL_83479  2005      3          20          7              1415   \n",
       "6562  BRO_IAH_75040  2005      3           7          1              1330   \n",
       "6563  BRO_IAH_98355  2005      3          16          3              1330   \n",
       "6564  FSM_DFW_89528  2005      3           7          1               750   \n",
       "6565  CMI_DFW_66446  2005      7          24          7               739   \n",
       "\n",
       "      ScheduledArrTime  ScheduledTravelTime Origin Destination  ...  \\\n",
       "0                 1756                  100    DEN         XNA  ...   \n",
       "1                 1157                  122    DEN         SAT  ...   \n",
       "2                 1817                   81    DEN         ICT  ...   \n",
       "3                 1529                  119    DEN         SAT  ...   \n",
       "4                 1925                  200    DEN         PHL  ...   \n",
       "...                ...                  ...    ...         ...  ...   \n",
       "6561              1629                   74    GTR         ATL  ...   \n",
       "6562              1444                   74    BRO         IAH  ...   \n",
       "6563              1444                   74    BRO         IAH  ...   \n",
       "6564               856                   66    FSM         DFW  ...   \n",
       "6565               939                  120    CMI         DFW  ...   \n",
       "\n",
       "      Longitude_origin  TimeZone_origin WeatherStationID_dest  AirportID_dest  \\\n",
       "0              -104.40               +7                 53922             XNA   \n",
       "1              -104.40               +7                 12921             SAT   \n",
       "2              -104.40               +7                  3928             ICT   \n",
       "3              -104.40               +7                 12921             SAT   \n",
       "4              -104.40               +7                 13739             PHL   \n",
       "...                ...              ...                   ...             ...   \n",
       "6561            -88.35               +6                 13874             ATL   \n",
       "6562            -97.26               +6                 12960             IAH   \n",
       "6563            -97.26               +6                 12960             IAH   \n",
       "6564            -94.22               +6                  3927             DFW   \n",
       "6565            -88.17               +6                  3927             DFW   \n",
       "\n",
       "      GroundHeight_dest  StationHeight_dest  BarometerHeight_dest  \\\n",
       "0                  1269                1319                  1272   \n",
       "1                   818                 809                   821   \n",
       "2                  1338                1340                  1341   \n",
       "3                   818                 809                   821   \n",
       "4                    59                  28                    62   \n",
       "...                 ...                 ...                   ...   \n",
       "6561                971                1026                   974   \n",
       "6562                118                 108                   121   \n",
       "6563                118                 108                   121   \n",
       "6564                559                 596                   562   \n",
       "6565                559                 596                   562   \n",
       "\n",
       "      Latitude_dest Longitude_dest  TimeZone_dest  \n",
       "0             36.17         -94.19             +6  \n",
       "1             29.32         -98.28             +6  \n",
       "2             37.39         -97.26             +6  \n",
       "3             29.32         -98.28             +6  \n",
       "4             39.52         -75.14             +5  \n",
       "...             ...            ...            ...  \n",
       "6561          33.38         -84.26             +5  \n",
       "6562          29.59         -95.22             +6  \n",
       "6563          29.59         -95.22             +6  \n",
       "6564          32.54         -97.02             +6  \n",
       "6565          32.54         -97.02             +6  \n",
       "\n",
       "[6566 rows x 27 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "0afb65ba",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6566, 27)"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "c291f689",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged.to_csv('merged_with_target_allstations_test.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b9b0a6ed",
   "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
}