Clean.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from utils import save_df_to_csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_988\\2956509124.py:14: DtypeWarning: Columns (9,10,11,12,13,14,24) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df5 = pd.read_csv(f5)\n",
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_988\\2956509124.py:15: DtypeWarning: Columns (0) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df6 = pd.read_csv(f6)\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "f1 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\training2_fdwind.csv\"\n",
    "f2 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\training2_fdwindairport.csv\"\n",
    "f3 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\training2_fdwindaltitude.csv\"\n",
    "f4 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\training2_fdwindreport.csv\"\n",
    "f5 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\training2_flighthistory.csv\"\n",
    "f6 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\training2_flighthistoryevents.csv\"\n",
    "\n",
    "df1 = pd.read_csv(f1)\n",
    "df2 = pd.read_csv(f2)\n",
    "df3 = pd.read_csv(f3)\n",
    "df4 = pd.read_csv(f4)\n",
    "df5 = pd.read_csv(f5)\n",
    "df6 = pd.read_csv(f6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_988\\3442454240.py:6: DtypeWarning: Columns (7) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  dc1 = pd.read_csv(c1)\n",
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_988\\3442454240.py:8: DtypeWarning: Columns (9,10,11,12,13,14,24) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  dc3 = pd.read_csv(c3)\n",
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_988\\3442454240.py:9: DtypeWarning: Columns (0) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  dc4 = pd.read_csv(c4)\n"
     ]
    }
   ],
   "source": [
    "c1 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\merged1and2.csv\"\n",
    "c2 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\merged3and4.csv\"\n",
    "c3 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\training2_flighthistory.csv\"\n",
    "c4 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\training2_flighthistoryevents.csv\"\n",
    "\n",
    "dc1 = pd.read_csv(c1)\n",
    "dc2 = pd.read_csv(c2)\n",
    "dc3 = pd.read_csv(c3)\n",
    "dc4 = pd.read_csv(c4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Unnamed: 0_x</th>\n",
       "      <th>fbwindairportid</th>\n",
       "      <th>ordinal</th>\n",
       "      <th>bearing</th>\n",
       "      <th>knots</th>\n",
       "      <th>temperature</th>\n",
       "      <th>Unnamed: 0_y</th>\n",
       "      <th>fbwindreportid</th>\n",
       "      <th>airportcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1414</td>\n",
       "      <td>211666</td>\n",
       "      <td>0</td>\n",
       "      <td>80</td>\n",
       "      <td>21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1286</td>\n",
       "      <td>2632</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1415</td>\n",
       "      <td>211666</td>\n",
       "      <td>1</td>\n",
       "      <td>80</td>\n",
       "      <td>21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1286</td>\n",
       "      <td>2632</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1416</td>\n",
       "      <td>211666</td>\n",
       "      <td>2</td>\n",
       "      <td>80</td>\n",
       "      <td>22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1286</td>\n",
       "      <td>2632</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1417</td>\n",
       "      <td>211666</td>\n",
       "      <td>3</td>\n",
       "      <td>80</td>\n",
       "      <td>22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1286</td>\n",
       "      <td>2632</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1418</td>\n",
       "      <td>211666</td>\n",
       "      <td>4</td>\n",
       "      <td>70</td>\n",
       "      <td>20</td>\n",
       "      <td>16.0</td>\n",
       "      <td>1286</td>\n",
       "      <td>2632</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  Unnamed: 0_x  fbwindairportid ordinal  bearing knots  \\\n",
       "0           0          1414           211666       0       80    21   \n",
       "1           1          1415           211666       1       80    21   \n",
       "2           2          1416           211666       2       80    22   \n",
       "3           3          1417           211666       3       80    22   \n",
       "4           4          1418           211666       4       70    20   \n",
       "\n",
       "  temperature Unnamed: 0_y  fbwindreportid airportcode  \n",
       "0         NaN         1286            2632         JON  \n",
       "1         NaN         1286            2632         JON  \n",
       "2         NaN         1286            2632         JON  \n",
       "3         NaN         1286            2632         JON  \n",
       "4        16.0         1286            2632         JON  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dc1.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>fbwindairportid</th>\n",
       "      <th>ordinal</th>\n",
       "      <th>bearing</th>\n",
       "      <th>knots</th>\n",
       "      <th>temperature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>243222</td>\n",
       "      <td>0</td>\n",
       "      <td>90</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>243222</td>\n",
       "      <td>1</td>\n",
       "      <td>90</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>243222</td>\n",
       "      <td>2</td>\n",
       "      <td>90</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>243222</td>\n",
       "      <td>3</td>\n",
       "      <td>80</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>243222</td>\n",
       "      <td>4</td>\n",
       "      <td>60</td>\n",
       "      <td>12</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  fbwindairportid ordinal  bearing knots temperature\n",
       "0           0           243222       0       90    12         NaN\n",
       "1           1           243222       1       90    12         NaN\n",
       "2           2           243222       2       90    12         NaN\n",
       "3           3           243222       3       80    12         NaN\n",
       "4           4           243222       4       60    12        15.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>fbwindairportid</th>\n",
       "      <th>fbwindreportid</th>\n",
       "      <th>airportcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>O</td>\n",
       "      <td>243222</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>243223</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>243224</td>\n",
       "      <td>3018</td>\n",
       "      <td>MAJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>243225</td>\n",
       "      <td>3018</td>\n",
       "      <td>MDY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>243226</td>\n",
       "      <td>3018</td>\n",
       "      <td>PPG</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Unnamed: 0  fbwindairportid  fbwindreportid airportcode\n",
       "0          O           243222            3018         JON\n",
       "1          1           243223            3018         KWA\n",
       "2          2           243224            3018         MAJ\n",
       "3          3           243225            3018         MDY\n",
       "4          4           243226            3018         PPG"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>id</th>\n",
       "      <th>airline_code</th>\n",
       "      <th>airline_icao_code</th>\n",
       "      <th>flight_number</th>\n",
       "      <th>departure_airport_code</th>\n",
       "      <th>departure_airport_icao_code</th>\n",
       "      <th>arrival_airport_code</th>\n",
       "      <th>arrival_airport_icao_code</th>\n",
       "      <th>published_departure</th>\n",
       "      <th>...</th>\n",
       "      <th>scheduled_runway_arrival</th>\n",
       "      <th>actual_runway_arrival</th>\n",
       "      <th>creator_code</th>\n",
       "      <th>scheduled_air_time</th>\n",
       "      <th>scheduled_block_time</th>\n",
       "      <th>departure_airport_timezone_offset</th>\n",
       "      <th>arrival_airport_timezone_offset</th>\n",
       "      <th>scheduled_aircraft_type</th>\n",
       "      <th>actual_aircraft_type</th>\n",
       "      <th>icao_aircraft_type_actual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>307025132</td>\n",
       "      <td>G4</td>\n",
       "      <td>AAY</td>\n",
       "      <td>565</td>\n",
       "      <td>EUG</td>\n",
       "      <td>KEUG</td>\n",
       "      <td>LAS</td>\n",
       "      <td>KLAS</td>\n",
       "      <td>2013-08-26 02:25:00+00</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I</td>\n",
       "      <td>NaN</td>\n",
       "      <td>115.0</td>\n",
       "      <td>-7</td>\n",
       "      <td>-7</td>\n",
       "      <td>M80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>307089377</td>\n",
       "      <td>9K</td>\n",
       "      <td>KAP</td>\n",
       "      <td>3312</td>\n",
       "      <td>BOS</td>\n",
       "      <td>KBOS</td>\n",
       "      <td>MVY</td>\n",
       "      <td>KMVY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.0</td>\n",
       "      <td>-4</td>\n",
       "      <td>-4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>307391933</td>\n",
       "      <td>AA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23</td>\n",
       "      <td>DEN</td>\n",
       "      <td>KDEN</td>\n",
       "      <td>DFW</td>\n",
       "      <td>KDFW</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2013-O8/26 17:31:00+0O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>111.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>-6</td>\n",
       "      <td>-5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>306201343</td>\n",
       "      <td>ZW</td>\n",
       "      <td>AWI</td>\n",
       "      <td>19</td>\n",
       "      <td>DCA</td>\n",
       "      <td>KDCA</td>\n",
       "      <td>ORF</td>\n",
       "      <td>KORF</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2O13-08/15 18:14:0O+OO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>29.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-4</td>\n",
       "      <td>-4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>306690299</td>\n",
       "      <td>5A</td>\n",
       "      <td>AIP</td>\n",
       "      <td>5103</td>\n",
       "      <td>GTF</td>\n",
       "      <td>KGTF</td>\n",
       "      <td>BIL</td>\n",
       "      <td>KBIL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2013-O8/19 O9:17:00+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>42.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-6</td>\n",
       "      <td>-6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>307497253</td>\n",
       "      <td>JUS</td>\n",
       "      <td>JUS</td>\n",
       "      <td>195</td>\n",
       "      <td>YIP</td>\n",
       "      <td>KYIP</td>\n",
       "      <td>SDF</td>\n",
       "      <td>KSDF</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2013-O8-27 19:49:0O+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>49.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-4</td>\n",
       "      <td>-4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>308173014</td>\n",
       "      <td>CPT</td>\n",
       "      <td>CPT</td>\n",
       "      <td>7572</td>\n",
       "      <td>BJI</td>\n",
       "      <td>KBJI</td>\n",
       "      <td>GFK</td>\n",
       "      <td>KGFK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2013-09-03 00:17:00+0O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>38.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-5</td>\n",
       "      <td>-5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>308178129</td>\n",
       "      <td>AMF</td>\n",
       "      <td>AMF</td>\n",
       "      <td>213</td>\n",
       "      <td>OAK</td>\n",
       "      <td>KOAK</td>\n",
       "      <td>RNO</td>\n",
       "      <td>KRNO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2013/09-04 15:41:0O+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>51.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-7</td>\n",
       "      <td>-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>308466974</td>\n",
       "      <td>1I</td>\n",
       "      <td>EJA</td>\n",
       "      <td>676</td>\n",
       "      <td>SMO</td>\n",
       "      <td>KSMO</td>\n",
       "      <td>SDM</td>\n",
       "      <td>KSDM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2013-09-06 20:35:O0+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-7</td>\n",
       "      <td>-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>308563519</td>\n",
       "      <td>GB</td>\n",
       "      <td>ABX</td>\n",
       "      <td>16</td>\n",
       "      <td>CVG</td>\n",
       "      <td>KCVG</td>\n",
       "      <td>MEM</td>\n",
       "      <td>KMEM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2013-09-O7 06:08:00+O0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>65.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-4</td>\n",
       "      <td>-5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0         id airline_code airline_icao_code  flight_number  \\\n",
       "0           0  307025132           G4               AAY            565   \n",
       "1           1  307089377           9K               KAP           3312   \n",
       "2           2  307391933           AA               NaN             23   \n",
       "3           3  306201343           ZW               AWI             19   \n",
       "4           4  306690299           5A               AIP           5103   \n",
       "5           5  307497253          JUS               JUS            195   \n",
       "6           6  308173014          CPT               CPT           7572   \n",
       "7           7  308178129          AMF               AMF            213   \n",
       "8           8  308466974           1I               EJA            676   \n",
       "9           9  308563519           GB               ABX             16   \n",
       "\n",
       "  departure_airport_code departure_airport_icao_code arrival_airport_code  \\\n",
       "0                    EUG                        KEUG                  LAS   \n",
       "1                    BOS                        KBOS                  MVY   \n",
       "2                    DEN                        KDEN                  DFW   \n",
       "3                    DCA                        KDCA                  ORF   \n",
       "4                    GTF                        KGTF                  BIL   \n",
       "5                    YIP                        KYIP                  SDF   \n",
       "6                    BJI                        KBJI                  GFK   \n",
       "7                    OAK                        KOAK                  RNO   \n",
       "8                    SMO                        KSMO                  SDM   \n",
       "9                    CVG                        KCVG                  MEM   \n",
       "\n",
       "  arrival_airport_icao_code     published_departure  ...  \\\n",
       "0                      KLAS  2013-08-26 02:25:00+00  ...   \n",
       "1                      KMVY                     NaN  ...   \n",
       "2                      KDFW                     NaN  ...   \n",
       "3                      KORF                     NaN  ...   \n",
       "4                      KBIL                     NaN  ...   \n",
       "5                      KSDF                     NaN  ...   \n",
       "6                      KGFK                     NaN  ...   \n",
       "7                      KRNO                     NaN  ...   \n",
       "8                      KSDM                     NaN  ...   \n",
       "9                      KMEM                     NaN  ...   \n",
       "\n",
       "  scheduled_runway_arrival actual_runway_arrival creator_code  \\\n",
       "0                      NaN                   NaN            I   \n",
       "1                      NaN                   NaN            D   \n",
       "2   2013-O8/26 17:31:00+0O                   NaN            D   \n",
       "3   2O13-08/15 18:14:0O+OO                   NaN            A   \n",
       "4   2013-O8/19 O9:17:00+00                   NaN            A   \n",
       "5   2013-O8-27 19:49:0O+00                   NaN            A   \n",
       "6   2013-09-03 00:17:00+0O                   NaN            A   \n",
       "7   2013/09-04 15:41:0O+00                   NaN            A   \n",
       "8   2013-09-06 20:35:O0+00                   NaN            A   \n",
       "9   2013-09-O7 06:08:00+O0                   NaN            A   \n",
       "\n",
       "  scheduled_air_time scheduled_block_time departure_airport_timezone_offset  \\\n",
       "0                NaN                115.0                                -7   \n",
       "1                NaN                 37.0                                -4   \n",
       "2              111.0                115.0                                -6   \n",
       "3               29.0                  NaN                                -4   \n",
       "4               42.0                  NaN                                -6   \n",
       "5               49.0                  NaN                                -4   \n",
       "6               38.0                  NaN                                -5   \n",
       "7               51.0                  NaN                                -7   \n",
       "8               25.0                  NaN                                -7   \n",
       "9               65.0                  NaN                                -4   \n",
       "\n",
       "  arrival_airport_timezone_offset scheduled_aircraft_type  \\\n",
       "0                              -7                     M80   \n",
       "1                              -4                     NaN   \n",
       "2                              -5                     NaN   \n",
       "3                              -4                     NaN   \n",
       "4                              -6                     NaN   \n",
       "5                              -4                     NaN   \n",
       "6                              -5                     NaN   \n",
       "7                              -7                     NaN   \n",
       "8                              -7                     NaN   \n",
       "9                              -5                     NaN   \n",
       "\n",
       "  actual_aircraft_type icao_aircraft_type_actual  \n",
       "0                  NaN                       NaN  \n",
       "1                  NaN                       NaN  \n",
       "2                  NaN                       NaN  \n",
       "3                  NaN                       NaN  \n",
       "4                  NaN                       NaN  \n",
       "5                  NaN                       NaN  \n",
       "6                  NaN                       NaN  \n",
       "7                  NaN                       NaN  \n",
       "8                  NaN                       NaN  \n",
       "9                  NaN                       NaN  \n",
       "\n",
       "[10 rows x 27 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df5.head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>id</th>\n",
       "      <th>airline_code</th>\n",
       "      <th>airline_icao_code</th>\n",
       "      <th>flight_number</th>\n",
       "      <th>departure_airport_code</th>\n",
       "      <th>departure_airport_icao_code</th>\n",
       "      <th>arrival_airport_code</th>\n",
       "      <th>arrival_airport_icao_code</th>\n",
       "      <th>published_departure</th>\n",
       "      <th>published_arrival</th>\n",
       "      <th>scheduled_gate_departure</th>\n",
       "      <th>actual_gate_departure</th>\n",
       "      <th>scheduled_gate_arrival</th>\n",
       "      <th>actual_gate_arrival</th>\n",
       "      <th>scheduled_runway_departure</th>\n",
       "      <th>actual_runway_departure</th>\n",
       "      <th>scheduled_runway_arrival</th>\n",
       "      <th>actual_runway_arrival</th>\n",
       "      <th>creator_code</th>\n",
       "      <th>scheduled_air_time</th>\n",
       "      <th>scheduled_block_time</th>\n",
       "      <th>departure_airport_timezone_offset</th>\n",
       "      <th>arrival_airport_timezone_offset</th>\n",
       "      <th>scheduled_aircraft_type</th>\n",
       "      <th>actual_aircraft_type</th>\n",
       "      <th>icao_aircraft_type_actual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>307025132</td>\n",
       "      <td>G4</td>\n",
       "      <td>AAY</td>\n",
       "      <td>565</td>\n",
       "      <td>EUG</td>\n",
       "      <td>KEUG</td>\n",
       "      <td>LAS</td>\n",
       "      <td>KLAS</td>\n",
       "      <td>2013-08-26 02:25:00+00</td>\n",
       "      <td>2013-O8-26 04:20:00+00</td>\n",
       "      <td>2013-O8-26 02:25:00+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-08-26 O4:2O:00+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I</td>\n",
       "      <td>NaN</td>\n",
       "      <td>115.0</td>\n",
       "      <td>-7</td>\n",
       "      <td>-7</td>\n",
       "      <td>M80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>307089377</td>\n",
       "      <td>9K</td>\n",
       "      <td>KAP</td>\n",
       "      <td>3312</td>\n",
       "      <td>BOS</td>\n",
       "      <td>KBOS</td>\n",
       "      <td>MVY</td>\n",
       "      <td>KMVY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-08-23 17:20:00+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-O8-23 17:57:00+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.0</td>\n",
       "      <td>-4</td>\n",
       "      <td>-4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>307391933</td>\n",
       "      <td>AA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23</td>\n",
       "      <td>DEN</td>\n",
       "      <td>KDEN</td>\n",
       "      <td>DFW</td>\n",
       "      <td>KDFW</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013/08/26 15:25:0O+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-08/26 17:2O:00+0O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-08-26 15:40:0O+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-O8/26 17:31:00+0O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>111.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>-6</td>\n",
       "      <td>-5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>306201343</td>\n",
       "      <td>ZW</td>\n",
       "      <td>AWI</td>\n",
       "      <td>19</td>\n",
       "      <td>DCA</td>\n",
       "      <td>KDCA</td>\n",
       "      <td>ORF</td>\n",
       "      <td>KORF</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-08/15 17:45:O0+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2O13-08/15 18:14:0O+OO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>29.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-4</td>\n",
       "      <td>-4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>306690299</td>\n",
       "      <td>5A</td>\n",
       "      <td>AIP</td>\n",
       "      <td>5103</td>\n",
       "      <td>GTF</td>\n",
       "      <td>KGTF</td>\n",
       "      <td>BIL</td>\n",
       "      <td>KBIL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-08-19 08:35:0O+O0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-O8/19 O9:17:00+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>42.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-6</td>\n",
       "      <td>-6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0         id airline_code airline_icao_code  flight_number  \\\n",
       "0           0  307025132           G4               AAY            565   \n",
       "1           1  307089377           9K               KAP           3312   \n",
       "2           2  307391933           AA               NaN             23   \n",
       "3           3  306201343           ZW               AWI             19   \n",
       "4           4  306690299           5A               AIP           5103   \n",
       "\n",
       "  departure_airport_code departure_airport_icao_code arrival_airport_code  \\\n",
       "0                    EUG                        KEUG                  LAS   \n",
       "1                    BOS                        KBOS                  MVY   \n",
       "2                    DEN                        KDEN                  DFW   \n",
       "3                    DCA                        KDCA                  ORF   \n",
       "4                    GTF                        KGTF                  BIL   \n",
       "\n",
       "  arrival_airport_icao_code     published_departure       published_arrival  \\\n",
       "0                      KLAS  2013-08-26 02:25:00+00  2013-O8-26 04:20:00+00   \n",
       "1                      KMVY                     NaN                     NaN   \n",
       "2                      KDFW                     NaN                     NaN   \n",
       "3                      KORF                     NaN                     NaN   \n",
       "4                      KBIL                     NaN                     NaN   \n",
       "\n",
       "  scheduled_gate_departure actual_gate_departure  scheduled_gate_arrival  \\\n",
       "0   2013-O8-26 02:25:00+00                   NaN  2013-08-26 O4:2O:00+00   \n",
       "1   2013-08-23 17:20:00+00                   NaN  2013-O8-23 17:57:00+00   \n",
       "2   2013/08/26 15:25:0O+00                   NaN  2013-08/26 17:2O:00+0O   \n",
       "3                      NaN                   NaN                     NaN   \n",
       "4                      NaN                   NaN                     NaN   \n",
       "\n",
       "  actual_gate_arrival scheduled_runway_departure actual_runway_departure  \\\n",
       "0                 NaN                        NaN                     NaN   \n",
       "1                 NaN                        NaN                     NaN   \n",
       "2                 NaN     2013-08-26 15:40:0O+00                     NaN   \n",
       "3                 NaN     2013-08/15 17:45:O0+00                     NaN   \n",
       "4                 NaN     2013-08-19 08:35:0O+O0                     NaN   \n",
       "\n",
       "  scheduled_runway_arrival actual_runway_arrival creator_code  \\\n",
       "0                      NaN                   NaN            I   \n",
       "1                      NaN                   NaN            D   \n",
       "2   2013-O8/26 17:31:00+0O                   NaN            D   \n",
       "3   2O13-08/15 18:14:0O+OO                   NaN            A   \n",
       "4   2013-O8/19 O9:17:00+00                   NaN            A   \n",
       "\n",
       "   scheduled_air_time  scheduled_block_time  \\\n",
       "0                 NaN                 115.0   \n",
       "1                 NaN                  37.0   \n",
       "2               111.0                 115.0   \n",
       "3                29.0                   NaN   \n",
       "4                42.0                   NaN   \n",
       "\n",
       "   departure_airport_timezone_offset  arrival_airport_timezone_offset  \\\n",
       "0                                 -7                               -7   \n",
       "1                                 -4                               -4   \n",
       "2                                 -6                               -5   \n",
       "3                                 -4                               -4   \n",
       "4                                 -6                               -6   \n",
       "\n",
       "  scheduled_aircraft_type  actual_aircraft_type icao_aircraft_type_actual  \n",
       "0                     M80                   NaN                       NaN  \n",
       "1                     NaN                   NaN                       NaN  \n",
       "2                     NaN                   NaN                       NaN  \n",
       "3                     NaN                   NaN                       NaN  \n",
       "4                     NaN                   NaN                       NaN  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option('display.max_columns',None)\n",
    "df5.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "14\n"
     ]
    }
   ],
   "source": [
    "file_path = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdiairway.csv\"\n",
    "path = pd.read_csv(file_path)\n",
    "unique_counts = path[\"ordinal\"].nunique()\n",
    "print(unique_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "a1 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_airsigmet.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "training2_airsigmet.csv\n",
      "training2_airsigmetarea.csv\n",
      "training2_asdiairway.csv\n",
      "training2_asdiflightplan.csv\n",
      "training2_asdifpcenter.csv\n",
      "training2_asdifpfix.csv\n",
      "training2_asdifpsector.csv\n",
      "training2_asdifpwaypoint.csv\n",
      "training2_asdiposition.csv\n",
      "training2_fdwind.csv\n",
      "training2_fdwindairport.csv\n",
      "training2_fdwindaltitude.csv\n",
      "training2_fdwindreport.csv\n",
      "training2_flighthistory.csv\n",
      "training2_flighthistoryevents.csv\n",
      "training2_metar_presentconditions.csv\n",
      "training2_metar_reports.csv\n",
      "training2_metar_runwaygroups.csv\n",
      "training2_metar_skyconditions.csv\n",
      "training2_taf.csv\n",
      "training2_tafforecast.csv\n",
      "training2_taficing.csv\n",
      "training2_tafsky.csv\n",
      "training2_taftemperature.csv\n",
      "training2_tafturbulence.csv\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "\n",
    "directory_path = r'C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2'\n",
    "\n",
    "file_names = os.listdir(directory_path)\n",
    "file_names = [f for f in file_names if os.path.isfile(os.path.join(directory_path, f))]\n",
    "\n",
    "for file in file_names:\n",
    "    print(file)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_988\\1234303474.py:6: DtypeWarning: Columns (0) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  a4 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdiflightplan.csv\")\n",
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_988\\1234303474.py:9: DtypeWarning: Columns (0) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  a7 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdifpsector.csv\")\n",
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_988\\1234303474.py:11: DtypeWarning: Columns (4) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  a9 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdiposition.csv\")\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "a1 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_airsigmet.csv\")\n",
    "a2 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_airsigmetarea.csv\")\n",
    "a3 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdiairway.csv\")\n",
    "a4 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdiflightplan.csv\")\n",
    "a5 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdifpcenter.csv\")\n",
    "a6 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdifpfix.csv\")\n",
    "a7 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdifpsector.csv\")\n",
    "a8 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdifpwaypoint.csv\")\n",
    "a9 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdiposition.csv\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>airsigmetid</th>\n",
       "      <th>timevalidfromutc</th>\n",
       "      <th>timevalidtoutc</th>\n",
       "      <th>movementdirdegrees</th>\n",
       "      <th>movementspeedknots</th>\n",
       "      <th>hazardtype</th>\n",
       "      <th>hazardseverity</th>\n",
       "      <th>airsigmettype</th>\n",
       "      <th>altitudeminft</th>\n",
       "      <th>altitudemaxft</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>32927</td>\n",
       "      <td>2013-08-17 14:55:OO+O0</td>\n",
       "      <td>2013/08-17 18:55:0O+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CONVECTIVE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OUTLOOK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>33194</td>\n",
       "      <td>2013-08-19 19:21:00+00</td>\n",
       "      <td>2013-08-19 22:O0:0O+0O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MTN OBSCN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AIRMET</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>33266</td>\n",
       "      <td>2013-08/20 03:45:0O+0O</td>\n",
       "      <td>2013-O8-20 O9:44:59+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MTN OBSCN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AIRMET</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>33295</td>\n",
       "      <td>2013/O8-2O 09:45:O0+0O</td>\n",
       "      <td>2013-O8/20 15:44:59+0O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TURB</td>\n",
       "      <td>MOD</td>\n",
       "      <td>AIRMET</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>33576</td>\n",
       "      <td>2O13-08-22 15:45:O0+0O</td>\n",
       "      <td>2013/08/22 21:44:59+0O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MTN OBSCN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AIRMET</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  airsigmetid        timevalidfromutc          timevalidtoutc  \\\n",
       "0           0        32927  2013-08-17 14:55:OO+O0  2013/08-17 18:55:0O+00   \n",
       "1           1        33194  2013-08-19 19:21:00+00  2013-08-19 22:O0:0O+0O   \n",
       "2           2        33266  2013-08/20 03:45:0O+0O  2013-O8-20 O9:44:59+00   \n",
       "3           3        33295  2013/O8-2O 09:45:O0+0O  2013-O8/20 15:44:59+0O   \n",
       "4           4        33576  2O13-08-22 15:45:O0+0O  2013/08/22 21:44:59+0O   \n",
       "\n",
       "   movementdirdegrees  movementspeedknots  hazardtype hazardseverity  \\\n",
       "0                 NaN                 NaN  CONVECTIVE            NaN   \n",
       "1                 NaN                 NaN   MTN OBSCN            NaN   \n",
       "2                 NaN                 NaN   MTN OBSCN            NaN   \n",
       "3                 NaN                 NaN        TURB            MOD   \n",
       "4                 NaN                 NaN   MTN OBSCN            NaN   \n",
       "\n",
       "  airsigmettype altitudeminft altitudemaxft  \n",
       "0       OUTLOOK           NaN           NaN  \n",
       "1        AIRMET           0.0           0.0  \n",
       "2        AIRMET           0.0           0.0  \n",
       "3        AIRMET           0.0           0.0  \n",
       "4        AIRMET           0.0           0.0  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a1.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>airsigmetid</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>ordinal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>32564</td>\n",
       "      <td>47.5893</td>\n",
       "      <td>-69.3144</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>32564</td>\n",
       "      <td>47.2380</td>\n",
       "      <td>-67.3977</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>32564</td>\n",
       "      <td>46.0400</td>\n",
       "      <td>-67.8300</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>32564</td>\n",
       "      <td>43.4300</td>\n",
       "      <td>-70.6100</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>32564</td>\n",
       "      <td>37.6415</td>\n",
       "      <td>-74.6296</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  airsigmetid  latitude  longitude ordinal\n",
       "0           0        32564   47.5893   -69.3144       0\n",
       "1           1        32564   47.2380   -67.3977       1\n",
       "2           2        32564   46.0400   -67.8300       2\n",
       "3           3        32564   43.4300   -70.6100       3\n",
       "4           4        32564   37.6415   -74.6296       4"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a2.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>id</th>\n",
       "      <th>asdiflightplan_id</th>\n",
       "      <th>ordinal</th>\n",
       "      <th>airway</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>114132776</td>\n",
       "      <td>O</td>\n",
       "      <td>V312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>114132776</td>\n",
       "      <td>1</td>\n",
       "      <td>V139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>114132777</td>\n",
       "      <td>0</td>\n",
       "      <td>SPRTN3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>26</td>\n",
       "      <td>114132779</td>\n",
       "      <td>O</td>\n",
       "      <td>Q42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>27</td>\n",
       "      <td>114132779</td>\n",
       "      <td>1</td>\n",
       "      <td>Q48O</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  id  asdiflightplan_id ordinal  airway\n",
       "0           0  18          114132776       O    V312\n",
       "1           1  19          114132776       1    V139\n",
       "2           2  20          114132777       0  SPRTN3\n",
       "3           3  26          114132779       O     Q42\n",
       "4           4  27          114132779       1    Q48O"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "a3.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>id</th>\n",
       "      <th>update_time_utc</th>\n",
       "      <th>flighthistory_id</th>\n",
       "      <th>departure_airport</th>\n",
       "      <th>arrival_airport</th>\n",
       "      <th>aircraft_id</th>\n",
       "      <th>legacy_route</th>\n",
       "      <th>original_departure_utc</th>\n",
       "      <th>estimated_departure_utc</th>\n",
       "      <th>original_arrival_utc</th>\n",
       "      <th>estimated_arrival_utc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>O</td>\n",
       "      <td>114294485</td>\n",
       "      <td>2O13-O8-21 13:45:45+O0</td>\n",
       "      <td>306898411</td>\n",
       "      <td>SAC</td>\n",
       "      <td>OAK</td>\n",
       "      <td>AMF287</td>\n",
       "      <td>SAC..OAK</td>\n",
       "      <td>2013-O8-22 06:45:O0+O0</td>\n",
       "      <td>2O13/O8-22 O6:55:O0+00</td>\n",
       "      <td>2013/O8-22 O7:15:00+O0</td>\n",
       "      <td>2013/08-22 07:20:00+OO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>114332979</td>\n",
       "      <td>2O13/08-21 18:58:29+OO</td>\n",
       "      <td>306610801</td>\n",
       "      <td>CGI</td>\n",
       "      <td>STL</td>\n",
       "      <td>KAP4O9</td>\n",
       "      <td>CGI..STL/OO55</td>\n",
       "      <td>2013-08-21 11:0O:00+00</td>\n",
       "      <td>2O13-O8-21 12:42:O0+OO</td>\n",
       "      <td>2013-08-21 12:05:00+00</td>\n",
       "      <td>2013-08-21 13:23:O0+O0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>114775397</td>\n",
       "      <td>2013-O8-22 18:54:16+0O</td>\n",
       "      <td>306904458</td>\n",
       "      <td>FSD</td>\n",
       "      <td>MBG</td>\n",
       "      <td>CCQ60O</td>\n",
       "      <td>FSD..MBG</td>\n",
       "      <td>2013-O8-22 10:30:00+00</td>\n",
       "      <td>2013/08/22 11:54:OO+O0</td>\n",
       "      <td>2013/O8-22 11:40:O0+O0</td>\n",
       "      <td>2013/O8-22 13:O3:00+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>114850278</td>\n",
       "      <td>2013-O8-22 22:14:0O+00</td>\n",
       "      <td>306995156</td>\n",
       "      <td>YKM</td>\n",
       "      <td>MWH</td>\n",
       "      <td>CFS797</td>\n",
       "      <td>YKM..MWH</td>\n",
       "      <td>2O13-08-22 15:0O:OO+0O</td>\n",
       "      <td>2013-O8-22 15:14:O0+00</td>\n",
       "      <td>2O13-08/22 15:50:O0+0O</td>\n",
       "      <td>2O13-08-22 15:40:00+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>117989171</td>\n",
       "      <td>2013-08-30 06:59:01+0O</td>\n",
       "      <td>307685375</td>\n",
       "      <td>VIS</td>\n",
       "      <td>FAT</td>\n",
       "      <td>AMF238</td>\n",
       "      <td>VIS..FAT/O015</td>\n",
       "      <td>2O13-08-30 O2:40:O0+O0</td>\n",
       "      <td>2013/08/3O 01:11:00+O0</td>\n",
       "      <td>2013-08-30 03:00:00+OO</td>\n",
       "      <td>2O13/08/30 01:25:00+OO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Unnamed: 0         id         update_time_utc  flighthistory_id  \\\n",
       "0          O  114294485  2O13-O8-21 13:45:45+O0         306898411   \n",
       "1          1  114332979  2O13/08-21 18:58:29+OO         306610801   \n",
       "2          2  114775397  2013-O8-22 18:54:16+0O         306904458   \n",
       "3          3  114850278  2013-O8-22 22:14:0O+00         306995156   \n",
       "4          4  117989171  2013-08-30 06:59:01+0O         307685375   \n",
       "\n",
       "  departure_airport arrival_airport aircraft_id   legacy_route  \\\n",
       "0               SAC             OAK      AMF287       SAC..OAK   \n",
       "1               CGI             STL      KAP4O9  CGI..STL/OO55   \n",
       "2               FSD             MBG      CCQ60O       FSD..MBG   \n",
       "3               YKM             MWH      CFS797       YKM..MWH   \n",
       "4               VIS             FAT      AMF238  VIS..FAT/O015   \n",
       "\n",
       "   original_departure_utc estimated_departure_utc    original_arrival_utc  \\\n",
       "0  2013-O8-22 06:45:O0+O0  2O13/O8-22 O6:55:O0+00  2013/O8-22 O7:15:00+O0   \n",
       "1  2013-08-21 11:0O:00+00  2O13-O8-21 12:42:O0+OO  2013-08-21 12:05:00+00   \n",
       "2  2013-O8-22 10:30:00+00  2013/08/22 11:54:OO+O0  2013/O8-22 11:40:O0+O0   \n",
       "3  2O13-08-22 15:0O:OO+0O  2013-O8-22 15:14:O0+00  2O13-08/22 15:50:O0+0O   \n",
       "4  2O13-08-30 O2:40:O0+O0  2013/08/3O 01:11:00+O0  2013-08-30 03:00:00+OO   \n",
       "\n",
       "    estimated_arrival_utc  \n",
       "0  2013/08-22 07:20:00+OO  \n",
       "1  2013-08-21 13:23:O0+O0  \n",
       "2  2013/O8-22 13:O3:00+00  \n",
       "3  2O13-08-22 15:40:00+00  \n",
       "4  2O13/08/30 01:25:00+OO  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a4.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>id</th>\n",
       "      <th>asdiflightplan_id</th>\n",
       "      <th>ordinal</th>\n",
       "      <th>center</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>114132776</td>\n",
       "      <td>0</td>\n",
       "      <td>ZNY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>21</td>\n",
       "      <td>114132776</td>\n",
       "      <td>1</td>\n",
       "      <td>ZBW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>22</td>\n",
       "      <td>114132776</td>\n",
       "      <td>2</td>\n",
       "      <td>ZNY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>23</td>\n",
       "      <td>114132777</td>\n",
       "      <td>O</td>\n",
       "      <td>ZAU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>24</td>\n",
       "      <td>114132777</td>\n",
       "      <td>1</td>\n",
       "      <td>ZOB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  id  asdiflightplan_id ordinal center\n",
       "0           0  20          114132776       0    ZNY\n",
       "1           1  21          114132776       1    ZBW\n",
       "2           2  22          114132776       2    ZNY\n",
       "3           3  23          114132777       O    ZAU\n",
       "4           4  24          114132777       1    ZOB"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "a5.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>id</th>\n",
       "      <th>asdiflightplan_id</th>\n",
       "      <th>ordinal</th>\n",
       "      <th>fix</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>118</td>\n",
       "      <td>114132776</td>\n",
       "      <td>0</td>\n",
       "      <td>PHL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>119</td>\n",
       "      <td>114132776</td>\n",
       "      <td>1</td>\n",
       "      <td>DITCH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>120</td>\n",
       "      <td>114132776</td>\n",
       "      <td>2</td>\n",
       "      <td>DITCH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>121</td>\n",
       "      <td>114132776</td>\n",
       "      <td>3</td>\n",
       "      <td>V312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>122</td>\n",
       "      <td>114132776</td>\n",
       "      <td>4</td>\n",
       "      <td>CYN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0   id  asdiflightplan_id ordinal    fix\n",
       "0           0  118          114132776       0    PHL\n",
       "1           1  119          114132776       1  DITCH\n",
       "2           2  120          114132776       2  DITCH\n",
       "3           3  121          114132776       3   V312\n",
       "4           4  122          114132776       4    CYN"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "a6.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>id</th>\n",
       "      <th>asdiflightplan_id</th>\n",
       "      <th>ordinal</th>\n",
       "      <th>sector</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>O</td>\n",
       "      <td>58</td>\n",
       "      <td>114132776</td>\n",
       "      <td>0</td>\n",
       "      <td>ZNYPHL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>59</td>\n",
       "      <td>114132776</td>\n",
       "      <td>1</td>\n",
       "      <td>ZNY68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>60</td>\n",
       "      <td>114132776</td>\n",
       "      <td>2</td>\n",
       "      <td>ZNY66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>61</td>\n",
       "      <td>114132776</td>\n",
       "      <td>3</td>\n",
       "      <td>ZBW32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>62</td>\n",
       "      <td>114132776</td>\n",
       "      <td>4</td>\n",
       "      <td>ZNYISE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Unnamed: 0  id  asdiflightplan_id ordinal  sector\n",
       "0          O  58          114132776       0  ZNYPHL\n",
       "1          1  59          114132776       1   ZNY68\n",
       "2          2  60          114132776       2   ZNY66\n",
       "3          3  61          114132776       3   ZBW32\n",
       "4          4  62          114132776       4  ZNYISE"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a7.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>id</th>\n",
       "      <th>asdiflightplan_id</th>\n",
       "      <th>ordinal</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>296</td>\n",
       "      <td>114132776</td>\n",
       "      <td>0</td>\n",
       "      <td>39.866667</td>\n",
       "      <td>-75.233333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>297</td>\n",
       "      <td>114132776</td>\n",
       "      <td>1</td>\n",
       "      <td>39.866667</td>\n",
       "      <td>-75.150000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>298</td>\n",
       "      <td>114132776</td>\n",
       "      <td>2</td>\n",
       "      <td>39.783333</td>\n",
       "      <td>-75.116667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>299</td>\n",
       "      <td>114132776</td>\n",
       "      <td>3</td>\n",
       "      <td>39.783333</td>\n",
       "      <td>-75.066667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>300</td>\n",
       "      <td>114132776</td>\n",
       "      <td>4</td>\n",
       "      <td>39.783333</td>\n",
       "      <td>-75.050000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0   id  asdiflightplan_id ordinal   latitude  longitude\n",
       "0           0  296          114132776       0  39.866667 -75.233333\n",
       "1           1  297          114132776       1  39.866667 -75.150000\n",
       "2           2  298          114132776       2  39.783333 -75.116667\n",
       "3           3  299          114132776       3  39.783333 -75.066667\n",
       "4           4  300          114132776       4  39.783333 -75.050000"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a8.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>id</th>\n",
       "      <th>received</th>\n",
       "      <th>callsign</th>\n",
       "      <th>altitude</th>\n",
       "      <th>ground_speed</th>\n",
       "      <th>latitude_degrees</th>\n",
       "      <th>longitude_degrees</th>\n",
       "      <th>flighthistory_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>108923402</td>\n",
       "      <td>2013-O9-09 16:27:54+00</td>\n",
       "      <td>AWE4O4</td>\n",
       "      <td>37000</td>\n",
       "      <td>454</td>\n",
       "      <td>39.330002</td>\n",
       "      <td>-91.730003</td>\n",
       "      <td>308536896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>108923403</td>\n",
       "      <td>2013-09-09 16:27:54+O0</td>\n",
       "      <td>EJA955</td>\n",
       "      <td>43000</td>\n",
       "      <td>496</td>\n",
       "      <td>39.130001</td>\n",
       "      <td>-95.970001</td>\n",
       "      <td>308759273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>108923404</td>\n",
       "      <td>2O13-09-09 16:27:54+0O</td>\n",
       "      <td>JBU223</td>\n",
       "      <td>36000</td>\n",
       "      <td>467</td>\n",
       "      <td>37.169998</td>\n",
       "      <td>-98.980003</td>\n",
       "      <td>308510756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>108923405</td>\n",
       "      <td>2013-09-09 16:27:54+00</td>\n",
       "      <td>UAL436</td>\n",
       "      <td>37000</td>\n",
       "      <td>474</td>\n",
       "      <td>38.919998</td>\n",
       "      <td>-97.529999</td>\n",
       "      <td>308546747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>108923406</td>\n",
       "      <td>2013-O9-09 16:28:00+00</td>\n",
       "      <td>SKW6407</td>\n",
       "      <td>33000</td>\n",
       "      <td>451</td>\n",
       "      <td>43.169998</td>\n",
       "      <td>-115.269997</td>\n",
       "      <td>308543571</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0         id                received callsign altitude  \\\n",
       "0           0  108923402  2013-O9-09 16:27:54+00   AWE4O4    37000   \n",
       "1           1  108923403  2013-09-09 16:27:54+O0   EJA955    43000   \n",
       "2           2  108923404  2O13-09-09 16:27:54+0O   JBU223    36000   \n",
       "3           3  108923405  2013-09-09 16:27:54+00   UAL436    37000   \n",
       "4           4  108923406  2013-O9-09 16:28:00+00  SKW6407    33000   \n",
       "\n",
       "  ground_speed  latitude_degrees  longitude_degrees  flighthistory_id  \n",
       "0          454         39.330002         -91.730003         308536896  \n",
       "1          496         39.130001         -95.970001         308759273  \n",
       "2          467         37.169998         -98.980003         308510756  \n",
       "3          474         38.919998         -97.529999         308546747  \n",
       "4          451         43.169998        -115.269997         308543571  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a9.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique Count: 8\n",
      "Unique Elements: [nan 10. 20. 30. 15. 25. 35.  5. 40.]\n"
     ]
    }
   ],
   "source": [
    "unique_counts = a1[\"movementspeedknots\"].nunique()\n",
    "unique_elements = a1[\"movementspeedknots\"].unique()\n",
    "\n",
    "print(\"Unique Count:\", unique_counts)\n",
    "print(\"Unique Elements:\", unique_elements)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "m1 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_metar_presentconditions.csv\")\n",
    "m3 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_metar_runwaygroups.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>id</th>\n",
       "      <th>metar_reports_id</th>\n",
       "      <th>present_condition</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>41855519</td>\n",
       "      <td>240371769</td>\n",
       "      <td>Thunderstorm Rain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>41857053</td>\n",
       "      <td>240374007</td>\n",
       "      <td>Drizzle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>41857054</td>\n",
       "      <td>240374007</td>\n",
       "      <td>Mist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>41857061</td>\n",
       "      <td>240373090</td>\n",
       "      <td>Thunderstorm Rain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>42058621</td>\n",
       "      <td>240373078</td>\n",
       "      <td>Rain in the vicinity</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id  metar_reports_id     present_condition\n",
       "0  41855519         240371769     Thunderstorm Rain\n",
       "1  41857053         240374007               Drizzle\n",
       "2  41857054         240374007                  Mist\n",
       "3  41857061         240373090     Thunderstorm Rain\n",
       "4  42058621         240373078  Rain in the vicinity"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>id</th>\n",
       "      <th>is_varying</th>\n",
       "      <th>max_visible</th>\n",
       "      <th>metar_reports_id</th>\n",
       "      <th>min_visible</th>\n",
       "      <th>runway</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>433764</td>\n",
       "      <td>T</td>\n",
       "      <td>2400</td>\n",
       "      <td>236330001</td>\n",
       "      <td>1600.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>433618</td>\n",
       "      <td>F</td>\n",
       "      <td>5500</td>\n",
       "      <td>236232728</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>433666</td>\n",
       "      <td>F</td>\n",
       "      <td>5500</td>\n",
       "      <td>236266126</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>433804</td>\n",
       "      <td>T</td>\n",
       "      <td>6000</td>\n",
       "      <td>236370909</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>433817</td>\n",
       "      <td>F</td>\n",
       "      <td>5500</td>\n",
       "      <td>236394560</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id is_varying  max_visible  metar_reports_id  min_visible  runway\n",
       "0  433764          T         2400         236330001       1600.0       3\n",
       "1  433618          F         5500         236232728          NaN      31\n",
       "2  433666          F         5500         236266126          NaN      18\n",
       "3  433804          T         6000         236370909       5000.0      18\n",
       "4  433817          F         5500         236394560          NaN      15"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4145, 9)"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m3.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id                   0.000000\n",
      "is_varying           0.000000\n",
      "max_visible          0.000000\n",
      "metar_reports_id     0.000000\n",
      "min_visible         57.732207\n",
      "runway               0.000000\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "null_percentage = m3.isnull().mean() * 100\n",
    "print(null_percentage)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "max_prefix\n",
       "greater than    1718\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m3['max_prefix'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>id</th>\n",
       "      <th>max_visible</th>\n",
       "      <th>metar_reports_id</th>\n",
       "      <th>min_prefix</th>\n",
       "      <th>min_visible</th>\n",
       "      <th>runway</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>4145.000000</td>\n",
       "      <td>4145.000000</td>\n",
       "      <td>4.145000e+03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1752.000000</td>\n",
       "      <td>4145.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>435636.257177</td>\n",
       "      <td>4656.718938</td>\n",
       "      <td>2.376163e+08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3176.940639</td>\n",
       "      <td>18.176357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1167.689741</td>\n",
       "      <td>1690.400973</td>\n",
       "      <td>7.648914e+05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1636.101913</td>\n",
       "      <td>10.987731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>433612.000000</td>\n",
       "      <td>400.000000</td>\n",
       "      <td>2.362273e+08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>434625.000000</td>\n",
       "      <td>3500.000000</td>\n",
       "      <td>2.369530e+08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1800.000000</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>435640.000000</td>\n",
       "      <td>5500.000000</td>\n",
       "      <td>2.376687e+08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3000.000000</td>\n",
       "      <td>18.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>436645.000000</td>\n",
       "      <td>6000.000000</td>\n",
       "      <td>2.381868e+08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4500.000000</td>\n",
       "      <td>29.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>437656.000000</td>\n",
       "      <td>6000.000000</td>\n",
       "      <td>2.389711e+08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6000.000000</td>\n",
       "      <td>36.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  id  max_visible  metar_reports_id  min_prefix  min_visible  \\\n",
       "count    4145.000000  4145.000000      4.145000e+03         0.0  1752.000000   \n",
       "mean   435636.257177  4656.718938      2.376163e+08         NaN  3176.940639   \n",
       "std      1167.689741  1690.400973      7.648914e+05         NaN  1636.101913   \n",
       "min    433612.000000   400.000000      2.362273e+08         NaN   100.000000   \n",
       "25%    434625.000000  3500.000000      2.369530e+08         NaN  1800.000000   \n",
       "50%    435640.000000  5500.000000      2.376687e+08         NaN  3000.000000   \n",
       "75%    436645.000000  6000.000000      2.381868e+08         NaN  4500.000000   \n",
       "max    437656.000000  6000.000000      2.389711e+08         NaN  6000.000000   \n",
       "\n",
       "            runway  \n",
       "count  4145.000000  \n",
       "mean     18.176357  \n",
       "std      10.987731  \n",
       "min       1.000000  \n",
       "25%       7.000000  \n",
       "50%      18.000000  \n",
       "75%      29.000000  \n",
       "max      36.000000  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m3.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>id</th>\n",
       "      <th>metar_reports_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>3.361550e+05</td>\n",
       "      <td>3.361550e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>4.190956e+07</td>\n",
       "      <td>2.376369e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>9.700550e+04</td>\n",
       "      <td>7.873997e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>4.174126e+07</td>\n",
       "      <td>2.362238e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>4.182555e+07</td>\n",
       "      <td>2.369616e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>4.190955e+07</td>\n",
       "      <td>2.376597e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>4.199357e+07</td>\n",
       "      <td>2.382892e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>4.207777e+07</td>\n",
       "      <td>2.403766e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 id  metar_reports_id\n",
       "count  3.361550e+05      3.361550e+05\n",
       "mean   4.190956e+07      2.376369e+08\n",
       "std    9.700550e+04      7.873997e+05\n",
       "min    4.174126e+07      2.362238e+08\n",
       "25%    4.182555e+07      2.369616e+08\n",
       "50%    4.190955e+07      2.376597e+08\n",
       "75%    4.199357e+07      2.382892e+08\n",
       "max    4.207777e+07      2.403766e+08"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m1.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "m1.drop(columns=['Unnamed: 0'],inplace=True,axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(336055, 3)"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "m1 = m1.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "m3.drop(columns=['Unnamed: 0'],inplace=True,axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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>id</th>\n",
       "      <th>is_varying</th>\n",
       "      <th>max_visible</th>\n",
       "      <th>metar_reports_id</th>\n",
       "      <th>min_visible</th>\n",
       "      <th>runway</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>433764</td>\n",
       "      <td>T</td>\n",
       "      <td>2400</td>\n",
       "      <td>236330001</td>\n",
       "      <td>1600.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>433618</td>\n",
       "      <td>F</td>\n",
       "      <td>5500</td>\n",
       "      <td>236232728</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>433666</td>\n",
       "      <td>F</td>\n",
       "      <td>5500</td>\n",
       "      <td>236266126</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>433804</td>\n",
       "      <td>T</td>\n",
       "      <td>6000</td>\n",
       "      <td>236370909</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>433817</td>\n",
       "      <td>F</td>\n",
       "      <td>5500</td>\n",
       "      <td>236394560</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>433904</td>\n",
       "      <td>F</td>\n",
       "      <td>6000</td>\n",
       "      <td>236434446</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>433943</td>\n",
       "      <td>F</td>\n",
       "      <td>6000</td>\n",
       "      <td>236470365</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id is_varying  max_visible  metar_reports_id  min_visible  runway\n",
       "0  433764          T         2400         236330001       1600.0       3\n",
       "1  433618          F         5500         236232728          NaN      31\n",
       "2  433666          F         5500         236266126          NaN      18\n",
       "3  433804          T         6000         236370909       5000.0      18\n",
       "4  433817          F         5500         236394560          NaN      15\n",
       "5  433904          F         6000         236434446          NaN       1\n",
       "6  433943          F         6000         236470365          NaN       6"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m3.head(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4045, 6)"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m3.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "m3 = m3.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "m3.drop(columns=['Unnamed: 0'],inplace=True,axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "m3.drop(columns=['min_prefix','max_prefix','approach_direction'],inplace=True,axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "m1_m3 = pd.merge(m1, m3, on='metar_reports_id', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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>id_x</th>\n",
       "      <th>metar_reports_id</th>\n",
       "      <th>present_condition</th>\n",
       "      <th>id_y</th>\n",
       "      <th>is_varying</th>\n",
       "      <th>max_visible</th>\n",
       "      <th>min_visible</th>\n",
       "      <th>runway</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>41855519</td>\n",
       "      <td>240371769</td>\n",
       "      <td>Thunderstorm Rain</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>41857053</td>\n",
       "      <td>240374007</td>\n",
       "      <td>Drizzle</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>41857054</td>\n",
       "      <td>240374007</td>\n",
       "      <td>Mist</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>41857061</td>\n",
       "      <td>240373090</td>\n",
       "      <td>Thunderstorm Rain</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>42058621</td>\n",
       "      <td>240373078</td>\n",
       "      <td>Rain in the vicinity</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>41762827</td>\n",
       "      <td>240371228</td>\n",
       "      <td>Smoke</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>42066175</td>\n",
       "      <td>240371896</td>\n",
       "      <td>Heavy Rain</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id_x  metar_reports_id     present_condition  id_y is_varying  \\\n",
       "0  41855519         240371769     Thunderstorm Rain   NaN        NaN   \n",
       "1  41857053         240374007               Drizzle   NaN        NaN   \n",
       "2  41857054         240374007                  Mist   NaN        NaN   \n",
       "3  41857061         240373090     Thunderstorm Rain   NaN        NaN   \n",
       "4  42058621         240373078  Rain in the vicinity   NaN        NaN   \n",
       "5  41762827         240371228                 Smoke   NaN        NaN   \n",
       "6  42066175         240371896            Heavy Rain   NaN        NaN   \n",
       "\n",
       "   max_visible  min_visible  runway  \n",
       "0          NaN          NaN     NaN  \n",
       "1          NaN          NaN     NaN  \n",
       "2          NaN          NaN     NaN  \n",
       "3          NaN          NaN     NaN  \n",
       "4          NaN          NaN     NaN  \n",
       "5          NaN          NaN     NaN  \n",
       "6          NaN          NaN     NaN  "
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m1_m3.head(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_988\\1778149829.py:1: DtypeWarning: Columns (1,3,11,13,16) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  m2 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_metar_reports.csv\")\n"
     ]
    }
   ],
   "source": [
    "m2 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_metar_reports.csv\")\n",
    "m4 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_metar_skyconditions.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "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>altimeter</th>\n",
       "      <th>date_time_issued</th>\n",
       "      <th>dewpoint</th>\n",
       "      <th>is_visibility_less_than</th>\n",
       "      <th>is_wind_direction_variable</th>\n",
       "      <th>id</th>\n",
       "      <th>remark</th>\n",
       "      <th>report_modifier</th>\n",
       "      <th>sea_level_pressure</th>\n",
       "      <th>station_type</th>\n",
       "      <th>temperature</th>\n",
       "      <th>variable_wind_direction</th>\n",
       "      <th>visibility</th>\n",
       "      <th>weather_station_code</th>\n",
       "      <th>wind_direction</th>\n",
       "      <th>wind_gusts</th>\n",
       "      <th>wind_speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>28.88</td>\n",
       "      <td>2013-O8-14 O9:0O:00+00</td>\n",
       "      <td>32.0</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "      <td>236223760</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>37.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.9</td>\n",
       "      <td>SAWH</td>\n",
       "      <td>360.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>29.88</td>\n",
       "      <td>2013/08-14 09:O0:00+0O</td>\n",
       "      <td>55.0</td>\n",
       "      <td>F</td>\n",
       "      <td>T</td>\n",
       "      <td>236223809</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>89.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.21</td>\n",
       "      <td>LQMO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30.03</td>\n",
       "      <td>2013-08/14 09:O0:00+O0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "      <td>236223816</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>80.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.21</td>\n",
       "      <td>BKPR</td>\n",
       "      <td>360.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>29.88</td>\n",
       "      <td>2O13/08-14 O9:00:00+0O</td>\n",
       "      <td>75.0</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "      <td>236223829</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>75.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.11</td>\n",
       "      <td>SBBV</td>\n",
       "      <td>210.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30.12</td>\n",
       "      <td>2O13-08-14 09:O0:00+0O</td>\n",
       "      <td>55.0</td>\n",
       "      <td>F</td>\n",
       "      <td>T</td>\n",
       "      <td>236223830</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>73.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.9</td>\n",
       "      <td>LQSA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>29.88</td>\n",
       "      <td>2013-08/14 09:O0:O0+0O</td>\n",
       "      <td>69.0</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "      <td>236223832</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>78.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.9</td>\n",
       "      <td>SBSL</td>\n",
       "      <td>60.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>30.03</td>\n",
       "      <td>2013-08-14 09:OO:OO+O0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "      <td>236223852</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>68.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.9</td>\n",
       "      <td>NFTF</td>\n",
       "      <td>140.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.45</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  altimeter        date_time_issued dewpoint is_visibility_less_than  \\\n",
       "0     28.88  2013-O8-14 O9:0O:00+00     32.0                       F   \n",
       "1     29.88  2013/08-14 09:O0:00+0O     55.0                       F   \n",
       "2     30.03  2013-08/14 09:O0:00+O0     53.0                       F   \n",
       "3     29.88  2O13/08-14 O9:00:00+0O     75.0                       F   \n",
       "4     30.12  2O13-08-14 09:O0:00+0O     55.0                       F   \n",
       "5     29.88  2013-08/14 09:O0:O0+0O     69.0                       F   \n",
       "6     30.03  2013-08-14 09:OO:OO+O0     66.0                       F   \n",
       "\n",
       "  is_wind_direction_variable         id remark report_modifier  \\\n",
       "0                          F  236223760    NaN             NaN   \n",
       "1                          T  236223809    NaN             NaN   \n",
       "2                          F  236223816    NaN             NaN   \n",
       "3                          F  236223829    NaN             NaN   \n",
       "4                          T  236223830    NaN             NaN   \n",
       "5                          F  236223832    NaN             NaN   \n",
       "6                          F  236223852    NaN             NaN   \n",
       "\n",
       "   sea_level_pressure station_type temperature variable_wind_direction  \\\n",
       "0                 NaN      Unknown        37.0                     NaN   \n",
       "1                 NaN      Unknown        89.0                     NaN   \n",
       "2                 NaN      Unknown        80.0                     NaN   \n",
       "3                 NaN      Unknown        75.0                     NaN   \n",
       "4                 NaN      Unknown        73.0                     NaN   \n",
       "5                 NaN      Unknown        78.0                     NaN   \n",
       "6                 NaN      Unknown        68.0                     NaN   \n",
       "\n",
       "  visibility weather_station_code wind_direction wind_gusts wind_speed  \n",
       "0        5.9                 SAWH          360.0        NaN      20.71  \n",
       "1       6.21                 LQMO            NaN        NaN       9.21  \n",
       "2       6.21                 BKPR          360.0        NaN      14.96  \n",
       "3       3.11                 SBBV          210.0        NaN        4.6  \n",
       "4        5.9                 LQSA            NaN        NaN       3.45  \n",
       "5        5.9                 SBSL           60.0        NaN        4.6  \n",
       "6        5.9                 NFTF          140.0        NaN       3.45  "
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m2.head(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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>id</th>\n",
       "      <th>metar_reports_id</th>\n",
       "      <th>sky_condition</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>282872896</td>\n",
       "      <td>240375720</td>\n",
       "      <td>Scattered layer at 11O00 feet.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>281955932</td>\n",
       "      <td>240376268</td>\n",
       "      <td>Scattered layer at 20O0 feet.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>283829070</td>\n",
       "      <td>240373115</td>\n",
       "      <td>Broken layer at 3000 feet.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>283829071</td>\n",
       "      <td>240373154</td>\n",
       "      <td>Few clouds at 2000 feet.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>283829205</td>\n",
       "      <td>240373720</td>\n",
       "      <td>Broken layer at 2O00 feet.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>282887280</td>\n",
       "      <td>240371284</td>\n",
       "      <td>Broken layer at 7000 feet.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>283341080</td>\n",
       "      <td>240371801</td>\n",
       "      <td>Scattered layer at 20O0 feet.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          id  metar_reports_id                    sky_condition\n",
       "0  282872896         240375720  Scattered layer at 11O00 feet. \n",
       "1  281955932         240376268   Scattered layer at 20O0 feet. \n",
       "2  283829070         240373115      Broken layer at 3000 feet. \n",
       "3  283829071         240373154        Few clouds at 2000 feet. \n",
       "4  283829205         240373720      Broken layer at 2O00 feet. \n",
       "5  282887280         240371284      Broken layer at 7000 feet. \n",
       "6  283341080         240371801   Scattered layer at 20O0 feet. "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m4.head(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "m2.drop(columns=['Unnamed: 0'],inplace=True,axis = 1)\n",
    "m4.drop(columns=['Unnamed: 0'],inplace=True,axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "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>altimeter</th>\n",
       "      <th>date_time_issued</th>\n",
       "      <th>dewpoint</th>\n",
       "      <th>is_visibility_less_than</th>\n",
       "      <th>is_wind_direction_variable</th>\n",
       "      <th>id</th>\n",
       "      <th>remark</th>\n",
       "      <th>report_modifier</th>\n",
       "      <th>station_type</th>\n",
       "      <th>temperature</th>\n",
       "      <th>visibility</th>\n",
       "      <th>weather_station_code</th>\n",
       "      <th>wind_direction</th>\n",
       "      <th>wind_speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>28.88</td>\n",
       "      <td>2013-O8-14 O9:0O:00+00</td>\n",
       "      <td>32.0</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "      <td>236223760</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>37.0</td>\n",
       "      <td>5.9</td>\n",
       "      <td>SAWH</td>\n",
       "      <td>360.0</td>\n",
       "      <td>20.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>29.88</td>\n",
       "      <td>2013/08-14 09:O0:00+0O</td>\n",
       "      <td>55.0</td>\n",
       "      <td>F</td>\n",
       "      <td>T</td>\n",
       "      <td>236223809</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>89.0</td>\n",
       "      <td>6.21</td>\n",
       "      <td>LQMO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30.03</td>\n",
       "      <td>2013-08/14 09:O0:00+O0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "      <td>236223816</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>80.0</td>\n",
       "      <td>6.21</td>\n",
       "      <td>BKPR</td>\n",
       "      <td>360.0</td>\n",
       "      <td>14.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>29.88</td>\n",
       "      <td>2O13/08-14 O9:00:00+0O</td>\n",
       "      <td>75.0</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "      <td>236223829</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>75.0</td>\n",
       "      <td>3.11</td>\n",
       "      <td>SBBV</td>\n",
       "      <td>210.0</td>\n",
       "      <td>4.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30.12</td>\n",
       "      <td>2O13-08-14 09:O0:00+0O</td>\n",
       "      <td>55.0</td>\n",
       "      <td>F</td>\n",
       "      <td>T</td>\n",
       "      <td>236223830</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>73.0</td>\n",
       "      <td>5.9</td>\n",
       "      <td>LQSA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.45</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  altimeter        date_time_issued dewpoint is_visibility_less_than  \\\n",
       "0     28.88  2013-O8-14 O9:0O:00+00     32.0                       F   \n",
       "1     29.88  2013/08-14 09:O0:00+0O     55.0                       F   \n",
       "2     30.03  2013-08/14 09:O0:00+O0     53.0                       F   \n",
       "3     29.88  2O13/08-14 O9:00:00+0O     75.0                       F   \n",
       "4     30.12  2O13-08-14 09:O0:00+0O     55.0                       F   \n",
       "\n",
       "  is_wind_direction_variable         id remark report_modifier station_type  \\\n",
       "0                          F  236223760    NaN             NaN      Unknown   \n",
       "1                          T  236223809    NaN             NaN      Unknown   \n",
       "2                          F  236223816    NaN             NaN      Unknown   \n",
       "3                          F  236223829    NaN             NaN      Unknown   \n",
       "4                          T  236223830    NaN             NaN      Unknown   \n",
       "\n",
       "  temperature visibility weather_station_code wind_direction wind_speed  \n",
       "0        37.0        5.9                 SAWH          360.0      20.71  \n",
       "1        89.0       6.21                 LQMO            NaN       9.21  \n",
       "2        80.0       6.21                 BKPR          360.0      14.96  \n",
       "3        75.0       3.11                 SBBV          210.0        4.6  \n",
       "4        73.0        5.9                 LQSA            NaN       3.45  "
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "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>id</th>\n",
       "      <th>metar_reports_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2.887751e+06</td>\n",
       "      <td>2.887751e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2.826146e+08</td>\n",
       "      <td>2.376048e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>8.335618e+05</td>\n",
       "      <td>7.931537e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2.811677e+08</td>\n",
       "      <td>2.362238e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2.818927e+08</td>\n",
       "      <td>2.369212e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2.826146e+08</td>\n",
       "      <td>2.376042e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2.833365e+08</td>\n",
       "      <td>2.382866e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2.840600e+08</td>\n",
       "      <td>2.403767e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 id  metar_reports_id\n",
       "count  2.887751e+06      2.887751e+06\n",
       "mean   2.826146e+08      2.376048e+08\n",
       "std    8.335618e+05      7.931537e+05\n",
       "min    2.811677e+08      2.362238e+08\n",
       "25%    2.818927e+08      2.369212e+08\n",
       "50%    2.826146e+08      2.376042e+08\n",
       "75%    2.833365e+08      2.382866e+08\n",
       "max    2.840600e+08      2.403767e+08"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m4.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "m4 = m4.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "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>id_x</th>\n",
       "      <th>metar_reports_id</th>\n",
       "      <th>id_y</th>\n",
       "      <th>max_visible</th>\n",
       "      <th>min_visible</th>\n",
       "      <th>runway</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>3.364600e+05</td>\n",
       "      <td>3.364600e+05</td>\n",
       "      <td>3952.000000</td>\n",
       "      <td>3952.000000</td>\n",
       "      <td>1892.000000</td>\n",
       "      <td>3952.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>4.190956e+07</td>\n",
       "      <td>2.376369e+08</td>\n",
       "      <td>435666.429909</td>\n",
       "      <td>4518.446356</td>\n",
       "      <td>3305.602537</td>\n",
       "      <td>17.679403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>9.699439e+04</td>\n",
       "      <td>7.873090e+05</td>\n",
       "      <td>1179.495387</td>\n",
       "      <td>1746.501385</td>\n",
       "      <td>1624.114389</td>\n",
       "      <td>10.810589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>4.174126e+07</td>\n",
       "      <td>2.362238e+08</td>\n",
       "      <td>433612.000000</td>\n",
       "      <td>400.000000</td>\n",
       "      <td>300.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>4.182558e+07</td>\n",
       "      <td>2.369619e+08</td>\n",
       "      <td>434641.000000</td>\n",
       "      <td>3000.000000</td>\n",
       "      <td>1800.000000</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>4.190960e+07</td>\n",
       "      <td>2.376600e+08</td>\n",
       "      <td>435739.500000</td>\n",
       "      <td>5500.000000</td>\n",
       "      <td>3000.000000</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>4.199352e+07</td>\n",
       "      <td>2.382889e+08</td>\n",
       "      <td>436671.250000</td>\n",
       "      <td>6000.000000</td>\n",
       "      <td>5000.000000</td>\n",
       "      <td>28.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>4.207777e+07</td>\n",
       "      <td>2.403766e+08</td>\n",
       "      <td>437654.000000</td>\n",
       "      <td>6000.000000</td>\n",
       "      <td>6000.000000</td>\n",
       "      <td>36.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               id_x  metar_reports_id           id_y  max_visible  \\\n",
       "count  3.364600e+05      3.364600e+05    3952.000000  3952.000000   \n",
       "mean   4.190956e+07      2.376369e+08  435666.429909  4518.446356   \n",
       "std    9.699439e+04      7.873090e+05    1179.495387  1746.501385   \n",
       "min    4.174126e+07      2.362238e+08  433612.000000   400.000000   \n",
       "25%    4.182558e+07      2.369619e+08  434641.000000  3000.000000   \n",
       "50%    4.190960e+07      2.376600e+08  435739.500000  5500.000000   \n",
       "75%    4.199352e+07      2.382889e+08  436671.250000  6000.000000   \n",
       "max    4.207777e+07      2.403766e+08  437654.000000  6000.000000   \n",
       "\n",
       "       min_visible       runway  \n",
       "count  1892.000000  3952.000000  \n",
       "mean   3305.602537    17.679403  \n",
       "std    1624.114389    10.810589  \n",
       "min     300.000000     1.000000  \n",
       "25%    1800.000000     7.000000  \n",
       "50%    3000.000000    16.000000  \n",
       "75%    5000.000000    28.000000  \n",
       "max    6000.000000    36.000000  "
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m1_m3.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "m134 = pd.merge(m1_m3, m4, on='metar_reports_id', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(569292, 10)"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m134.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['altimeter', 'date_time_issued', 'dewpoint', 'is_visibility_less_than',\n",
       "       'is_wind_direction_variable', 'id', 'remark', 'report_modifier',\n",
       "       'station_type', 'temperature', 'visibility', 'weather_station_code',\n",
       "       'wind_direction', 'wind_speed'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "altimeter                      7.692829\n",
      "date_time_issued               0.000000\n",
      "dewpoint                       1.940620\n",
      "is_visibility_less_than       10.036931\n",
      "is_wind_direction_variable     1.313722\n",
      "id                             0.000000\n",
      "remark                        47.212089\n",
      "report_modifier               47.992570\n",
      "sea_level_pressure            64.722039\n",
      "station_type                   0.000000\n",
      "temperature                    0.933153\n",
      "variable_wind_direction       93.629053\n",
      "visibility                    10.036931\n",
      "weather_station_code           0.000000\n",
      "wind_direction                 5.893989\n",
      "wind_gusts                    94.481116\n",
      "wind_speed                     1.313722\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "null_percentage = m2.isnull().mean() * 100\n",
    "print(null_percentage)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "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>altimeter</th>\n",
       "      <th>date_time_issued</th>\n",
       "      <th>dewpoint</th>\n",
       "      <th>is_visibility_less_than</th>\n",
       "      <th>is_wind_direction_variable</th>\n",
       "      <th>id</th>\n",
       "      <th>remark</th>\n",
       "      <th>report_modifier</th>\n",
       "      <th>station_type</th>\n",
       "      <th>temperature</th>\n",
       "      <th>visibility</th>\n",
       "      <th>weather_station_code</th>\n",
       "      <th>wind_direction</th>\n",
       "      <th>wind_speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>28.88</td>\n",
       "      <td>2013-O8-14 O9:0O:00+00</td>\n",
       "      <td>32.0</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "      <td>236223760</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>37.0</td>\n",
       "      <td>5.9</td>\n",
       "      <td>SAWH</td>\n",
       "      <td>360.0</td>\n",
       "      <td>20.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>29.88</td>\n",
       "      <td>2013/08-14 09:O0:00+0O</td>\n",
       "      <td>55.0</td>\n",
       "      <td>F</td>\n",
       "      <td>T</td>\n",
       "      <td>236223809</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>89.0</td>\n",
       "      <td>6.21</td>\n",
       "      <td>LQMO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30.03</td>\n",
       "      <td>2013-08/14 09:O0:00+O0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "      <td>236223816</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>80.0</td>\n",
       "      <td>6.21</td>\n",
       "      <td>BKPR</td>\n",
       "      <td>360.0</td>\n",
       "      <td>14.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>29.88</td>\n",
       "      <td>2O13/08-14 O9:00:00+0O</td>\n",
       "      <td>75.0</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "      <td>236223829</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>75.0</td>\n",
       "      <td>3.11</td>\n",
       "      <td>SBBV</td>\n",
       "      <td>210.0</td>\n",
       "      <td>4.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30.12</td>\n",
       "      <td>2O13-08-14 09:O0:00+0O</td>\n",
       "      <td>55.0</td>\n",
       "      <td>F</td>\n",
       "      <td>T</td>\n",
       "      <td>236223830</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>73.0</td>\n",
       "      <td>5.9</td>\n",
       "      <td>LQSA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>29.88</td>\n",
       "      <td>2013-08/14 09:O0:O0+0O</td>\n",
       "      <td>69.0</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "      <td>236223832</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>78.0</td>\n",
       "      <td>5.9</td>\n",
       "      <td>SBSL</td>\n",
       "      <td>60.0</td>\n",
       "      <td>4.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>30.03</td>\n",
       "      <td>2013-08-14 09:OO:OO+O0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "      <td>236223852</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>68.0</td>\n",
       "      <td>5.9</td>\n",
       "      <td>NFTF</td>\n",
       "      <td>140.0</td>\n",
       "      <td>3.45</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  altimeter        date_time_issued dewpoint is_visibility_less_than  \\\n",
       "0     28.88  2013-O8-14 O9:0O:00+00     32.0                       F   \n",
       "1     29.88  2013/08-14 09:O0:00+0O     55.0                       F   \n",
       "2     30.03  2013-08/14 09:O0:00+O0     53.0                       F   \n",
       "3     29.88  2O13/08-14 O9:00:00+0O     75.0                       F   \n",
       "4     30.12  2O13-08-14 09:O0:00+0O     55.0                       F   \n",
       "5     29.88  2013-08/14 09:O0:O0+0O     69.0                       F   \n",
       "6     30.03  2013-08-14 09:OO:OO+O0     66.0                       F   \n",
       "\n",
       "  is_wind_direction_variable         id remark report_modifier station_type  \\\n",
       "0                          F  236223760    NaN             NaN      Unknown   \n",
       "1                          T  236223809    NaN             NaN      Unknown   \n",
       "2                          F  236223816    NaN             NaN      Unknown   \n",
       "3                          F  236223829    NaN             NaN      Unknown   \n",
       "4                          T  236223830    NaN             NaN      Unknown   \n",
       "5                          F  236223832    NaN             NaN      Unknown   \n",
       "6                          F  236223852    NaN             NaN      Unknown   \n",
       "\n",
       "  temperature visibility weather_station_code wind_direction wind_speed  \n",
       "0        37.0        5.9                 SAWH          360.0      20.71  \n",
       "1        89.0       6.21                 LQMO            NaN       9.21  \n",
       "2        80.0       6.21                 BKPR          360.0      14.96  \n",
       "3        75.0       3.11                 SBBV          210.0        4.6  \n",
       "4        73.0        5.9                 LQSA            NaN       3.45  \n",
       "5        78.0        5.9                 SBSL           60.0        4.6  \n",
       "6        68.0        5.9                 NFTF          140.0       3.45  "
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m2.head(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "m2.drop(columns=['variable_wind_direction','wind_gusts','sea_level_pressure'],inplace=True,axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "station_type\n",
       "Unknown    1314472\n",
       "AO2        1115808\n",
       "AO1         314822\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m2['station_type'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "m2.drop(columns=['remark','report_modifier','station_type'],inplace=True,axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "m134.to_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\m_134.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "m2.to_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\m2.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "as1 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdiairway.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_988\\1763804175.py:1: DtypeWarning: Columns (0) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  as2 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdiflightplan.csv\")\n",
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_988\\1763804175.py:4: DtypeWarning: Columns (0) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  as5 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdifpsector.csv\")\n",
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_988\\1763804175.py:6: DtypeWarning: Columns (4) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  as7 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdiposition.csv\")\n"
     ]
    }
   ],
   "source": [
    "as2 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdiflightplan.csv\")\n",
    "as3 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdifpcenter.csv\")\n",
    "as4 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdifpfix.csv\")\n",
    "as5 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdifpsector.csv\")\n",
    "as6 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdifpwaypoint.csv\")\n",
    "as7 = pd.read_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\dirty data 2\\training2_asdiposition.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "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>id</th>\n",
       "      <th>asdiflightplan_id</th>\n",
       "      <th>ordinal</th>\n",
       "      <th>airway</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>114132776</td>\n",
       "      <td>O</td>\n",
       "      <td>V312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>114132776</td>\n",
       "      <td>1</td>\n",
       "      <td>V139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>114132777</td>\n",
       "      <td>0</td>\n",
       "      <td>SPRTN3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>26</td>\n",
       "      <td>114132779</td>\n",
       "      <td>O</td>\n",
       "      <td>Q42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>27</td>\n",
       "      <td>114132779</td>\n",
       "      <td>1</td>\n",
       "      <td>Q48O</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  id  asdiflightplan_id ordinal  airway\n",
       "0           0  18          114132776       O    V312\n",
       "1           1  19          114132776       1    V139\n",
       "2           2  20          114132777       0  SPRTN3\n",
       "3           3  26          114132779       O     Q42\n",
       "4           4  27          114132779       1    Q48O"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "as1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "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>id</th>\n",
       "      <th>asdiflightplan_id</th>\n",
       "      <th>ordinal</th>\n",
       "      <th>center</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>114132776</td>\n",
       "      <td>0</td>\n",
       "      <td>ZNY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>21</td>\n",
       "      <td>114132776</td>\n",
       "      <td>1</td>\n",
       "      <td>ZBW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>22</td>\n",
       "      <td>114132776</td>\n",
       "      <td>2</td>\n",
       "      <td>ZNY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>23</td>\n",
       "      <td>114132777</td>\n",
       "      <td>O</td>\n",
       "      <td>ZAU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>24</td>\n",
       "      <td>114132777</td>\n",
       "      <td>1</td>\n",
       "      <td>ZOB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  id  asdiflightplan_id ordinal center\n",
       "0           0  20          114132776       0    ZNY\n",
       "1           1  21          114132776       1    ZBW\n",
       "2           2  22          114132776       2    ZNY\n",
       "3           3  23          114132777       O    ZAU\n",
       "4           4  24          114132777       1    ZOB"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "as3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "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>id</th>\n",
       "      <th>asdiflightplan_id</th>\n",
       "      <th>ordinal</th>\n",
       "      <th>fix</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>118</td>\n",
       "      <td>114132776</td>\n",
       "      <td>0</td>\n",
       "      <td>PHL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>119</td>\n",
       "      <td>114132776</td>\n",
       "      <td>1</td>\n",
       "      <td>DITCH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>120</td>\n",
       "      <td>114132776</td>\n",
       "      <td>2</td>\n",
       "      <td>DITCH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>121</td>\n",
       "      <td>114132776</td>\n",
       "      <td>3</td>\n",
       "      <td>V312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>122</td>\n",
       "      <td>114132776</td>\n",
       "      <td>4</td>\n",
       "      <td>CYN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0   id  asdiflightplan_id ordinal    fix\n",
       "0           0  118          114132776       0    PHL\n",
       "1           1  119          114132776       1  DITCH\n",
       "2           2  120          114132776       2  DITCH\n",
       "3           3  121          114132776       3   V312\n",
       "4           4  122          114132776       4    CYN"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "as4.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "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>id</th>\n",
       "      <th>asdiflightplan_id</th>\n",
       "      <th>ordinal</th>\n",
       "      <th>sector</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>O</td>\n",
       "      <td>58</td>\n",
       "      <td>114132776</td>\n",
       "      <td>0</td>\n",
       "      <td>ZNYPHL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>59</td>\n",
       "      <td>114132776</td>\n",
       "      <td>1</td>\n",
       "      <td>ZNY68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>60</td>\n",
       "      <td>114132776</td>\n",
       "      <td>2</td>\n",
       "      <td>ZNY66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>61</td>\n",
       "      <td>114132776</td>\n",
       "      <td>3</td>\n",
       "      <td>ZBW32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>62</td>\n",
       "      <td>114132776</td>\n",
       "      <td>4</td>\n",
       "      <td>ZNYISE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Unnamed: 0  id  asdiflightplan_id ordinal  sector\n",
       "0          O  58          114132776       0  ZNYPHL\n",
       "1          1  59          114132776       1   ZNY68\n",
       "2          2  60          114132776       2   ZNY66\n",
       "3          3  61          114132776       3   ZBW32\n",
       "4          4  62          114132776       4  ZNYISE"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "as5.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "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>id</th>\n",
       "      <th>asdiflightplan_id</th>\n",
       "      <th>ordinal</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>296</td>\n",
       "      <td>114132776</td>\n",
       "      <td>0</td>\n",
       "      <td>39.866667</td>\n",
       "      <td>-75.233333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>297</td>\n",
       "      <td>114132776</td>\n",
       "      <td>1</td>\n",
       "      <td>39.866667</td>\n",
       "      <td>-75.150000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>298</td>\n",
       "      <td>114132776</td>\n",
       "      <td>2</td>\n",
       "      <td>39.783333</td>\n",
       "      <td>-75.116667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>299</td>\n",
       "      <td>114132776</td>\n",
       "      <td>3</td>\n",
       "      <td>39.783333</td>\n",
       "      <td>-75.066667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>300</td>\n",
       "      <td>114132776</td>\n",
       "      <td>4</td>\n",
       "      <td>39.783333</td>\n",
       "      <td>-75.050000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0   id  asdiflightplan_id ordinal   latitude  longitude\n",
       "0           0  296          114132776       0  39.866667 -75.233333\n",
       "1           1  297          114132776       1  39.866667 -75.150000\n",
       "2           2  298          114132776       2  39.783333 -75.116667\n",
       "3           3  299          114132776       3  39.783333 -75.066667\n",
       "4           4  300          114132776       4  39.783333 -75.050000"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "as6.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_5480\\22495181.py:12: DtypeWarning: Columns (9,10,11,12,13,14,24) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df5 = pd.read_csv(f5)\n",
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_5480\\22495181.py:13: DtypeWarning: Columns (0) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df6 = pd.read_csv(f6)\n",
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_5480\\22495181.py:20: DtypeWarning: Columns (7) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  dc1 = pd.read_csv(c1)\n",
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_5480\\22495181.py:22: DtypeWarning: Columns (9,10,11,12,13,14,24) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  dc3 = pd.read_csv(c3)\n",
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_5480\\22495181.py:23: DtypeWarning: Columns (0) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  dc4 = pd.read_csv(c4)\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "f1 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\training2_fdwind.csv\"\n",
    "f2 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\training2_fdwindairport.csv\"\n",
    "f3 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\training2_fdwindaltitude.csv\"\n",
    "f5 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\training2_flighthistory.csv\"\n",
    "f6 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\training2_flighthistoryevents.csv\"\n",
    "\n",
    "df1 = pd.read_csv(f1)\n",
    "df2 = pd.read_csv(f2)\n",
    "df3 = pd.read_csv(f3)\n",
    "df5 = pd.read_csv(f5)\n",
    "df6 = pd.read_csv(f6)\n",
    "\n",
    "c1 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\merged1and2.csv\"\n",
    "c2 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\merged3and4.csv\"\n",
    "c3 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\training2_flighthistory.csv\"\n",
    "c4 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Cleaned\\training2_flighthistoryevents.csv\"\n",
    "\n",
    "dc1 = pd.read_csv(c1)\n",
    "dc2 = pd.read_csv(c2)\n",
    "dc3 = pd.read_csv(c3)\n",
    "dc4 = pd.read_csv(c4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unnamed: 0         0.000000\n",
      "fbwindairportid    0.000000\n",
      "ordinal            0.000000\n",
      "bearing            0.000000\n",
      "knots              0.000000\n",
      "temperature        8.209868\n",
      "dtype: float64\n",
      "Unnamed: 0         0.0\n",
      "fbwindairportid    0.0\n",
      "fbwindreportid     0.0\n",
      "airportcode        0.0\n",
      "dtype: float64\n",
      "Unnamed: 0        0.0\n",
      "fbwindreportid    0.0\n",
      "ordinal           0.0\n",
      "altitude          0.0\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "null_percent1 = df1.isnull().mean() * 100\n",
    "null_percent2 = df2.isnull().mean() * 100\n",
    "null_percent3 = df3.isnull().mean() * 100\n",
    "\n",
    "print (null_percent1)\n",
    "print(null_percent2)\n",
    "print(null_percent3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "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>fbwindairportid</th>\n",
       "      <th>ordinal</th>\n",
       "      <th>bearing</th>\n",
       "      <th>knots</th>\n",
       "      <th>temperature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>243222</td>\n",
       "      <td>0</td>\n",
       "      <td>90</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>243222</td>\n",
       "      <td>1</td>\n",
       "      <td>90</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>243222</td>\n",
       "      <td>2</td>\n",
       "      <td>90</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>243222</td>\n",
       "      <td>3</td>\n",
       "      <td>80</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>243222</td>\n",
       "      <td>4</td>\n",
       "      <td>60</td>\n",
       "      <td>12</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  fbwindairportid ordinal  bearing knots temperature\n",
       "0           0           243222       0       90    12         NaN\n",
       "1           1           243222       1       90    12         NaN\n",
       "2           2           243222       2       90    12         NaN\n",
       "3           3           243222       3       80    12         NaN\n",
       "4           4           243222       4       60    12        15.0"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "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>fbwindairportid</th>\n",
       "      <th>fbwindreportid</th>\n",
       "      <th>airportcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>O</td>\n",
       "      <td>243222</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>243223</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>243224</td>\n",
       "      <td>3018</td>\n",
       "      <td>MAJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>243225</td>\n",
       "      <td>3018</td>\n",
       "      <td>MDY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>243226</td>\n",
       "      <td>3018</td>\n",
       "      <td>PPG</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Unnamed: 0  fbwindairportid  fbwindreportid airportcode\n",
       "0          O           243222            3018         JON\n",
       "1          1           243223            3018         KWA\n",
       "2          2           243224            3018         MAJ\n",
       "3          3           243225            3018         MDY\n",
       "4          4           243226            3018         PPG"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "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>fbwindreportid</th>\n",
       "      <th>ordinal</th>\n",
       "      <th>altitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2632</td>\n",
       "      <td>0</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2632</td>\n",
       "      <td>1</td>\n",
       "      <td>1500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2632</td>\n",
       "      <td>2</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2632</td>\n",
       "      <td>3</td>\n",
       "      <td>3000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2632</td>\n",
       "      <td>4</td>\n",
       "      <td>6000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  fbwindreportid ordinal  altitude\n",
       "0           0            2632       0      1000\n",
       "1           1            2632       1      1500\n",
       "2           2            2632       2      2000\n",
       "3           3            2632       3      3000\n",
       "4           4            2632       4      6000"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(270723, 6)"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(35646, 4)"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df1and2 = pd.merge(df1, df2, on='fbwindairportid', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(270723, 9)"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df1and2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df1and2 = pd.merge(df1, df2, on='fbwindairportid', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(270723, 9)"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df1and2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df1and2 = pd.merge(df1, df2, on='fbwindairportid', how='right')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(270723, 9)"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df1and2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df1and2 = pd.merge(df1, df2, on='fbwindairportid', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(270723, 9)"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df1and2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df1and2.to_csv(r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_1.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "FD_1 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_1.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_5480\\2046194894.py:1: DtypeWarning: Columns (6) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  dFD_1 = pd.read_csv(FD_1)\n"
     ]
    }
   ],
   "source": [
    "dFD_1 = pd.read_csv(FD_1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Unnamed: 0_x  fbwindairportid ordinal  bearing knots temperature  \\\n",
      "0             0           243222       0       90    12         NaN   \n",
      "1             1           243222       1       90    12         NaN   \n",
      "2             2           243222       2       90    12         NaN   \n",
      "3             3           243222       3       80    12         NaN   \n",
      "4             4           243222       4       60    12        15.0   \n",
      "\n",
      "  Unnamed: 0_y  fbwindreportid airportcode  \n",
      "0            O            3018         JON  \n",
      "1            O            3018         JON  \n",
      "2            O            3018         JON  \n",
      "3            O            3018         JON  \n",
      "4            O            3018         JON  \n",
      "(270723, 9)\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 270723 entries, 0 to 270722\n",
      "Data columns (total 9 columns):\n",
      " #   Column           Non-Null Count   Dtype \n",
      "---  ------           --------------   ----- \n",
      " 0   Unnamed: 0_x     270723 non-null  int64 \n",
      " 1   fbwindairportid  270723 non-null  int64 \n",
      " 2   ordinal          270723 non-null  object\n",
      " 3   bearing          270723 non-null  int64 \n",
      " 4   knots            270723 non-null  object\n",
      " 5   temperature      248497 non-null  object\n",
      " 6   Unnamed: 0_y     270723 non-null  object\n",
      " 7   fbwindreportid   270723 non-null  int64 \n",
      " 8   airportcode      270723 non-null  object\n",
      "dtypes: int64(4), object(5)\n",
      "memory usage: 18.6+ MB\n",
      "None\n",
      "Unnamed: 0_x           0\n",
      "fbwindairportid        0\n",
      "ordinal                0\n",
      "bearing                0\n",
      "knots                  0\n",
      "temperature        22226\n",
      "Unnamed: 0_y           0\n",
      "fbwindreportid         0\n",
      "airportcode            0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(dFD_1.head())\n",
    "\n",
    "print(dFD_1.shape)\n",
    "\n",
    "print(dFD_1.info())\n",
    "\n",
    "print(dFD_1.isnull().sum())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_988\\1250474903.py:3: FutureWarning: errors='ignore' is deprecated and will raise in a future version. Use to_numeric without passing `errors` and catch exceptions explicitly instead\n",
      "  dFD_1 = dFD_1.apply(pd.to_numeric, errors='ignore')\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Unnamed: 0_x  fbwindairportid  ordinal  bearing  knots  temperature  \\\n",
      "0             0           243222        0       90     12          NaN   \n",
      "1             1           243222        1       90     12          NaN   \n",
      "2             2           243222        2       90     12          NaN   \n",
      "3             3           243222        3       80     12          NaN   \n",
      "4             4           243222        4       60     12         15.0   \n",
      "\n",
      "   Unnamed: 0_y  fbwindreportid airportcode  \n",
      "0             0            3018         JON  \n",
      "1             0            3018         JON  \n",
      "2             0            3018         JON  \n",
      "3             0            3018         JON  \n",
      "4             0            3018         JON  \n"
     ]
    }
   ],
   "source": [
    "columns_to_modify = ['Unnamed: 0_x', 'fbwindairportid', 'ordinal', 'bearing', 'knots', 'temperature', 'Unnamed: 0_y', 'fbwindreportid']\n",
    "dFD_1[columns_to_modify] = dFD_1[columns_to_modify].replace(\"O\", 0)\n",
    "dFD_1 = dFD_1.apply(pd.to_numeric, errors='ignore')\n",
    "dFD_1.to_csv('FD_1_updated.csv', index=False)\n",
    "print(dFD_1.head())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Unnamed: 0  fbwindreportid ordinal  altitude\n",
      "0           0            2632       0      1000\n",
      "1           1            2632       1      1500\n",
      "2           2            2632       2      2000\n",
      "3           3            2632       3      3000\n",
      "4           4            2632       4      6000\n",
      "(3870, 4)\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3870 entries, 0 to 3869\n",
      "Data columns (total 4 columns):\n",
      " #   Column          Non-Null Count  Dtype \n",
      "---  ------          --------------  ----- \n",
      " 0   Unnamed: 0      3870 non-null   int64 \n",
      " 1   fbwindreportid  3870 non-null   int64 \n",
      " 2   ordinal         3870 non-null   object\n",
      " 3   altitude        3870 non-null   int64 \n",
      "dtypes: int64(3), object(1)\n",
      "memory usage: 121.1+ KB\n",
      "None\n",
      "Unnamed: 0        0\n",
      "fbwindreportid    0\n",
      "ordinal           0\n",
      "altitude          0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "FD_2 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_2.csv\"\n",
    "dFD_2 = pd.read_csv(FD_2)\n",
    "print(dFD_2.head())\n",
    "print(dFD_2.shape)\n",
    "print(dFD_2.info())\n",
    "print(dFD_2.isnull().sum())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Unnamed: 0  fbwindreportid  ordinal  altitude\n",
      "0           0            2632        0      1000\n",
      "1           1            2632        1      1500\n",
      "2           2            2632        2      2000\n",
      "3           3            2632        3      3000\n",
      "4           4            2632        4      6000\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\omnku\\AppData\\Local\\Temp\\ipykernel_988\\291684479.py:5: FutureWarning: errors='ignore' is deprecated and will raise in a future version. Use to_numeric without passing `errors` and catch exceptions explicitly instead\n",
      "  dDF_2 = dFD_2.apply(pd.to_numeric, errors='ignore')\n"
     ]
    }
   ],
   "source": [
    "columns_to_modify = ['Unnamed: 0', 'fbwindreportid', 'ordinal', 'altitude']\n",
    "\n",
    "dFD_2[columns_to_modify] = dFD_2[columns_to_modify].replace(\"O\", 0)\n",
    "\n",
    "dDF_2 = dFD_2.apply(pd.to_numeric, errors='ignore')\n",
    "dDF_2.to_csv('FD_1_updated.csv', index=False)\n",
    "print(dDF_2.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame saved to C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_2final.csv\n"
     ]
    }
   ],
   "source": [
    "save_df_to_csv(dDF_2,r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_2final.csv\" )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame saved to C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_1final.csv\n"
     ]
    }
   ],
   "source": [
    "save_df_to_csv(dFD_1,r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_1final.csv\" )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No duplicates found.\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def check_duplicates(filename):\n",
    "    df = pd.read_csv(filename)\n",
    "    str_columns = df.select_dtypes(include=['object']).columns\n",
    "    df[str_columns] = df[str_columns].apply(lambda x: x.str.lower())\n",
    "    duplicates = df[df.duplicated(keep=False)]\n",
    "    if not duplicates.empty:\n",
    "        print(\"Duplicates found:\")\n",
    "        print(duplicates)\n",
    "    else:\n",
    "        print(\"No duplicates found.\")\n",
    "        \n",
    "check_duplicates(r'C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_2final.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No duplicates found.\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def check_duplicates(filename):\n",
    "    df = pd.read_csv(filename)\n",
    "    str_columns = df.select_dtypes(include=['object']).columns\n",
    "    df[str_columns] = df[str_columns].apply(lambda x: x.str.lower())\n",
    "    duplicates = df[df.duplicated(keep=False)]\n",
    "    if not duplicates.empty:\n",
    "        print(\"Duplicates found:\")\n",
    "        print(duplicates)\n",
    "    else:\n",
    "        print(\"No duplicates found.\")\n",
    "        \n",
    "check_duplicates(r'C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_1final.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   fbwindairportid  ordinal  bearing  knots  temperature  fbwindreportid  \\\n",
      "0           243222        0       90     12          NaN            3018   \n",
      "1           243222        1       90     12          NaN            3018   \n",
      "2           243222        2       90     12          NaN            3018   \n",
      "3           243222        3       80     12          NaN            3018   \n",
      "4           243222        4       60     12         15.0            3018   \n",
      "5           243222        5       50     10         11.0            3018   \n",
      "6           243222        6       60      8          8.0            3018   \n",
      "7           243222        7       60      7          3.0            3018   \n",
      "8           243222        8       80      5         -4.0            3018   \n",
      "9           243222        9      110      5        -17.0            3018   \n",
      "\n",
      "  airportcode  \n",
      "0         JON  \n",
      "1         JON  \n",
      "2         JON  \n",
      "3         JON  \n",
      "4         JON  \n",
      "5         JON  \n",
      "6         JON  \n",
      "7         JON  \n",
      "8         JON  \n",
      "9         JON  \n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv(r'C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_1final.csv')\n",
    "df.drop(columns=['Unnamed: 0_x', 'Unnamed: 0_y'], inplace=True)\n",
    "\n",
    "print(df.head(10))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   fbwindreportid  ordinal  altitude\n",
      "0            2632        0      1000\n",
      "1            2632        1      1500\n",
      "2            2632        2      2000\n",
      "3            2632        3      3000\n",
      "4            2632        4      6000\n",
      "5            2632        5      9000\n",
      "6            2632        6     12000\n",
      "7            2632        7     15000\n",
      "8            2632        8     18000\n",
      "9            2632        9     24000\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv(r'C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_2final.csv')\n",
    "df.drop(columns=df.columns[df.columns.str.contains(\"Unnamed:\")], inplace=True)\n",
    "\n",
    "print(df.head(10))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "FD_1 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_1final.csv\"\n",
    "FD_2 = r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_2final.csv\"\n",
    "\n",
    "dFD_1 = pd.read_csv(FD_1)\n",
    "dDF_2 = pd.read_csv(FD_2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[0 1 2 3 4 5 6 7 8 9]\n",
      "fbwindreportid    int64\n",
      "ordinal           int32\n",
      "altitude          int64\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Load your dataset\n",
    "dFD_2 = pd.read_csv(FD_2)\n",
    "\n",
    "# Check unique values in the 'ordinal' column\n",
    "print(dFD_2['ordinal'].unique())\n",
    "\n",
    "# Attempt to convert 'ordinal' to int, handling errors\n",
    "try:\n",
    "    dFD_2['ordinal'] = dFD_2['ordinal'].astype(int)\n",
    "except ValueError as e:\n",
    "    print(\"Conversion error:\", e)\n",
    "    # Optionally: Clean the data or remove problematic rows\n",
    "    # dFD_2 = dFD_2[pd.to_numeric(dFD_2['ordinal'], errors='coerce').notnull()]\n",
    "    # dFD_2['ordinal'] = dFD_2['ordinal'].astype(int)\n",
    "\n",
    "# Drop 'Unnamed: 0' column if it exists\n",
    "if 'Unnamed: 0' in dFD_2.columns:\n",
    "    df = dFD_2.drop(columns=['Unnamed: 0'])\n",
    "else:\n",
    "    df = dFD_2.copy()\n",
    "\n",
    "# Display the data types to verify changes\n",
    "print(df.dtypes)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3870 entries, 0 to 3869\n",
      "Data columns (total 4 columns):\n",
      " #   Column          Non-Null Count  Dtype\n",
      "---  ------          --------------  -----\n",
      " 0   Unnamed: 0      3870 non-null   int64\n",
      " 1   fbwindreportid  3870 non-null   int64\n",
      " 2   ordinal         3870 non-null   int64\n",
      " 3   altitude        3870 non-null   int64\n",
      "dtypes: int64(4)\n",
      "memory usage: 121.1 KB\n"
     ]
    }
   ],
   "source": [
    "dDF_2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 270723 entries, 0 to 270722\n",
      "Data columns (total 9 columns):\n",
      " #   Column           Non-Null Count   Dtype  \n",
      "---  ------           --------------   -----  \n",
      " 0   Unnamed: 0_x     270723 non-null  int64  \n",
      " 1   fbwindairportid  270723 non-null  int64  \n",
      " 2   ordinal          270723 non-null  int32  \n",
      " 3   bearing          270723 non-null  int64  \n",
      " 4   knots            270723 non-null  int64  \n",
      " 5   temperature      248497 non-null  float64\n",
      " 6   Unnamed: 0_y     270723 non-null  int64  \n",
      " 7   fbwindreportid   270723 non-null  int64  \n",
      " 8   airportcode      270723 non-null  object \n",
      "dtypes: float64(1), int32(1), int64(6), object(1)\n",
      "memory usage: 17.6+ MB\n"
     ]
    }
   ],
   "source": [
    "dFD_1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unnamed: 0_x        int64\n",
      "fbwindairportid     int64\n",
      "ordinal             int32\n",
      "bearing             int64\n",
      "knots               int64\n",
      "temperature         int32\n",
      "Unnamed: 0_y        int64\n",
      "fbwindreportid      int64\n",
      "airportcode        object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# Fill NaN values with a specific value, like 0\n",
    "dFD_1['temperature'] = dFD_1['temperature'].fillna(0).astype(int)\n",
    "\n",
    "# Display the data types to verify the change\n",
    "print(dFD_1.dtypes)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 270723 entries, 0 to 270722\n",
      "Data columns (total 9 columns):\n",
      " #   Column           Non-Null Count   Dtype \n",
      "---  ------           --------------   ----- \n",
      " 0   Unnamed: 0_x     270723 non-null  int64 \n",
      " 1   fbwindairportid  270723 non-null  int64 \n",
      " 2   ordinal          270723 non-null  int32 \n",
      " 3   bearing          270723 non-null  int64 \n",
      " 4   knots            270723 non-null  int64 \n",
      " 5   temperature      270723 non-null  int32 \n",
      " 6   Unnamed: 0_y     270723 non-null  int64 \n",
      " 7   fbwindreportid   270723 non-null  int64 \n",
      " 8   airportcode      270723 non-null  object\n",
      "dtypes: int32(2), int64(6), object(1)\n",
      "memory usage: 16.5+ MB\n"
     ]
    }
   ],
   "source": [
    "dFD_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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_x</th>\n",
       "      <th>fbwindairportid</th>\n",
       "      <th>ordinal</th>\n",
       "      <th>bearing</th>\n",
       "      <th>knots</th>\n",
       "      <th>temperature</th>\n",
       "      <th>Unnamed: 0_y</th>\n",
       "      <th>fbwindreportid</th>\n",
       "      <th>airportcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>243222</td>\n",
       "      <td>0</td>\n",
       "      <td>90</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>243222</td>\n",
       "      <td>1</td>\n",
       "      <td>90</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>243222</td>\n",
       "      <td>2</td>\n",
       "      <td>90</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>243222</td>\n",
       "      <td>3</td>\n",
       "      <td>80</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>243222</td>\n",
       "      <td>4</td>\n",
       "      <td>60</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>243222</td>\n",
       "      <td>5</td>\n",
       "      <td>50</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>243222</td>\n",
       "      <td>6</td>\n",
       "      <td>60</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>243222</td>\n",
       "      <td>7</td>\n",
       "      <td>60</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>243222</td>\n",
       "      <td>8</td>\n",
       "      <td>80</td>\n",
       "      <td>5</td>\n",
       "      <td>-4</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>243222</td>\n",
       "      <td>9</td>\n",
       "      <td>110</td>\n",
       "      <td>5</td>\n",
       "      <td>-17</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>243223</td>\n",
       "      <td>0</td>\n",
       "      <td>130</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>243223</td>\n",
       "      <td>1</td>\n",
       "      <td>130</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>243223</td>\n",
       "      <td>2</td>\n",
       "      <td>130</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>243223</td>\n",
       "      <td>3</td>\n",
       "      <td>120</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>243223</td>\n",
       "      <td>4</td>\n",
       "      <td>110</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>15</td>\n",
       "      <td>243223</td>\n",
       "      <td>5</td>\n",
       "      <td>110</td>\n",
       "      <td>16</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16</td>\n",
       "      <td>243223</td>\n",
       "      <td>6</td>\n",
       "      <td>110</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>17</td>\n",
       "      <td>243223</td>\n",
       "      <td>7</td>\n",
       "      <td>90</td>\n",
       "      <td>22</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18</td>\n",
       "      <td>243223</td>\n",
       "      <td>8</td>\n",
       "      <td>80</td>\n",
       "      <td>24</td>\n",
       "      <td>-6</td>\n",
       "      <td>1</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>19</td>\n",
       "      <td>243223</td>\n",
       "      <td>9</td>\n",
       "      <td>80</td>\n",
       "      <td>21</td>\n",
       "      <td>-16</td>\n",
       "      <td>1</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0_x  fbwindairportid  ordinal  bearing  knots  temperature  \\\n",
       "0              0           243222        0       90     12            0   \n",
       "1              1           243222        1       90     12            0   \n",
       "2              2           243222        2       90     12            0   \n",
       "3              3           243222        3       80     12            0   \n",
       "4              4           243222        4       60     12           15   \n",
       "5              5           243222        5       50     10           11   \n",
       "6              6           243222        6       60      8            8   \n",
       "7              7           243222        7       60      7            3   \n",
       "8              8           243222        8       80      5           -4   \n",
       "9              9           243222        9      110      5          -17   \n",
       "10            10           243223        0      130      8            0   \n",
       "11            11           243223        1      130      8            0   \n",
       "12            12           243223        2      130      8            0   \n",
       "13            13           243223        3      120      9            0   \n",
       "14            14           243223        4      110     11           17   \n",
       "15            15           243223        5      110     16           12   \n",
       "16            16           243223        6      110     20            7   \n",
       "17            17           243223        7       90     22            2   \n",
       "18            18           243223        8       80     24           -6   \n",
       "19            19           243223        9       80     21          -16   \n",
       "\n",
       "    Unnamed: 0_y  fbwindreportid airportcode  \n",
       "0              0            3018         JON  \n",
       "1              0            3018         JON  \n",
       "2              0            3018         JON  \n",
       "3              0            3018         JON  \n",
       "4              0            3018         JON  \n",
       "5              0            3018         JON  \n",
       "6              0            3018         JON  \n",
       "7              0            3018         JON  \n",
       "8              0            3018         JON  \n",
       "9              0            3018         JON  \n",
       "10             1            3018         KWA  \n",
       "11             1            3018         KWA  \n",
       "12             1            3018         KWA  \n",
       "13             1            3018         KWA  \n",
       "14             1            3018         KWA  \n",
       "15             1            3018         KWA  \n",
       "16             1            3018         KWA  \n",
       "17             1            3018         KWA  \n",
       "18             1            3018         KWA  \n",
       "19             1            3018         KWA  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dFD_1.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    fbwindairportid  ordinal  bearing  knots  temperature  fbwindreportid  \\\n",
      "0            243222        0       90     12            0            3018   \n",
      "1            243222        1       90     12            0            3018   \n",
      "2            243222        2       90     12            0            3018   \n",
      "3            243222        3       80     12            0            3018   \n",
      "4            243222        4       60     12           15            3018   \n",
      "5            243222        5       50     10           11            3018   \n",
      "6            243222        6       60      8            8            3018   \n",
      "7            243222        7       60      7            3            3018   \n",
      "8            243222        8       80      5           -4            3018   \n",
      "9            243222        9      110      5          -17            3018   \n",
      "10           243223        0      130      8            0            3018   \n",
      "11           243223        1      130      8            0            3018   \n",
      "12           243223        2      130      8            0            3018   \n",
      "13           243223        3      120      9            0            3018   \n",
      "14           243223        4      110     11           17            3018   \n",
      "15           243223        5      110     16           12            3018   \n",
      "16           243223        6      110     20            7            3018   \n",
      "17           243223        7       90     22            2            3018   \n",
      "18           243223        8       80     24           -6            3018   \n",
      "19           243223        9       80     21          -16            3018   \n",
      "\n",
      "   airportcode  \n",
      "0          JON  \n",
      "1          JON  \n",
      "2          JON  \n",
      "3          JON  \n",
      "4          JON  \n",
      "5          JON  \n",
      "6          JON  \n",
      "7          JON  \n",
      "8          JON  \n",
      "9          JON  \n",
      "10         KWA  \n",
      "11         KWA  \n",
      "12         KWA  \n",
      "13         KWA  \n",
      "14         KWA  \n",
      "15         KWA  \n",
      "16         KWA  \n",
      "17         KWA  \n",
      "18         KWA  \n",
      "19         KWA  \n"
     ]
    }
   ],
   "source": [
    "dFD_1.drop(columns=['Unnamed: 0_x', 'Unnamed: 0_y'], inplace=True)\n",
    "\n",
    "print(dFD_1.head(20))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 270723 entries, 0 to 270722\n",
      "Data columns (total 7 columns):\n",
      " #   Column           Non-Null Count   Dtype \n",
      "---  ------           --------------   ----- \n",
      " 0   fbwindairportid  270723 non-null  int64 \n",
      " 1   ordinal          270723 non-null  int32 \n",
      " 2   bearing          270723 non-null  int64 \n",
      " 3   knots            270723 non-null  int64 \n",
      " 4   temperature      270723 non-null  int32 \n",
      " 5   fbwindreportid   270723 non-null  int64 \n",
      " 6   airportcode      270723 non-null  object\n",
      "dtypes: int32(2), int64(4), object(1)\n",
      "memory usage: 12.4+ MB\n"
     ]
    }
   ],
   "source": [
    "dFD_1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame saved to C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_1Final.csv\n"
     ]
    }
   ],
   "source": [
    "save_df_to_csv(dFD_1, r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_1Final.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame saved to C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_2Final.csv\n"
     ]
    }
   ],
   "source": [
    "save_df_to_csv(dFD_2, r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_2Final.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 270723 entries, 0 to 270722\n",
      "Data columns (total 7 columns):\n",
      " #   Column           Non-Null Count   Dtype \n",
      "---  ------           --------------   ----- \n",
      " 0   fbwindairportid  270723 non-null  int64 \n",
      " 1   ordinal          270723 non-null  int64 \n",
      " 2   bearing          270723 non-null  int64 \n",
      " 3   knots            270723 non-null  int64 \n",
      " 4   temperature      270723 non-null  int64 \n",
      " 5   fbwindreportid   270723 non-null  int64 \n",
      " 6   airportcode      270723 non-null  object\n",
      "dtypes: int64(6), object(1)\n",
      "memory usage: 14.5+ MB\n"
     ]
    },
    {
     "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>fbwindairportid</th>\n",
       "      <th>ordinal</th>\n",
       "      <th>bearing</th>\n",
       "      <th>knots</th>\n",
       "      <th>temperature</th>\n",
       "      <th>fbwindreportid</th>\n",
       "      <th>airportcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>243222</td>\n",
       "      <td>0</td>\n",
       "      <td>90</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>243222</td>\n",
       "      <td>1</td>\n",
       "      <td>90</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>243222</td>\n",
       "      <td>2</td>\n",
       "      <td>90</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>243222</td>\n",
       "      <td>3</td>\n",
       "      <td>80</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>243222</td>\n",
       "      <td>4</td>\n",
       "      <td>60</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>243222</td>\n",
       "      <td>5</td>\n",
       "      <td>50</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>243222</td>\n",
       "      <td>6</td>\n",
       "      <td>60</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>243222</td>\n",
       "      <td>7</td>\n",
       "      <td>60</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>243222</td>\n",
       "      <td>8</td>\n",
       "      <td>80</td>\n",
       "      <td>5</td>\n",
       "      <td>-4</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>243222</td>\n",
       "      <td>9</td>\n",
       "      <td>110</td>\n",
       "      <td>5</td>\n",
       "      <td>-17</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>243223</td>\n",
       "      <td>0</td>\n",
       "      <td>130</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>243223</td>\n",
       "      <td>1</td>\n",
       "      <td>130</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>243223</td>\n",
       "      <td>2</td>\n",
       "      <td>130</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>243223</td>\n",
       "      <td>3</td>\n",
       "      <td>120</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>243223</td>\n",
       "      <td>4</td>\n",
       "      <td>110</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>243223</td>\n",
       "      <td>5</td>\n",
       "      <td>110</td>\n",
       "      <td>16</td>\n",
       "      <td>12</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>243223</td>\n",
       "      <td>6</td>\n",
       "      <td>110</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>243223</td>\n",
       "      <td>7</td>\n",
       "      <td>90</td>\n",
       "      <td>22</td>\n",
       "      <td>2</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>243223</td>\n",
       "      <td>8</td>\n",
       "      <td>80</td>\n",
       "      <td>24</td>\n",
       "      <td>-6</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>243223</td>\n",
       "      <td>9</td>\n",
       "      <td>80</td>\n",
       "      <td>21</td>\n",
       "      <td>-16</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    fbwindairportid  ordinal  bearing  knots  temperature  fbwindreportid  \\\n",
       "0            243222        0       90     12            0            3018   \n",
       "1            243222        1       90     12            0            3018   \n",
       "2            243222        2       90     12            0            3018   \n",
       "3            243222        3       80     12            0            3018   \n",
       "4            243222        4       60     12           15            3018   \n",
       "5            243222        5       50     10           11            3018   \n",
       "6            243222        6       60      8            8            3018   \n",
       "7            243222        7       60      7            3            3018   \n",
       "8            243222        8       80      5           -4            3018   \n",
       "9            243222        9      110      5          -17            3018   \n",
       "10           243223        0      130      8            0            3018   \n",
       "11           243223        1      130      8            0            3018   \n",
       "12           243223        2      130      8            0            3018   \n",
       "13           243223        3      120      9            0            3018   \n",
       "14           243223        4      110     11           17            3018   \n",
       "15           243223        5      110     16           12            3018   \n",
       "16           243223        6      110     20            7            3018   \n",
       "17           243223        7       90     22            2            3018   \n",
       "18           243223        8       80     24           -6            3018   \n",
       "19           243223        9       80     21          -16            3018   \n",
       "\n",
       "   airportcode  \n",
       "0          JON  \n",
       "1          JON  \n",
       "2          JON  \n",
       "3          JON  \n",
       "4          JON  \n",
       "5          JON  \n",
       "6          JON  \n",
       "7          JON  \n",
       "8          JON  \n",
       "9          JON  \n",
       "10         KWA  \n",
       "11         KWA  \n",
       "12         KWA  \n",
       "13         KWA  \n",
       "14         KWA  \n",
       "15         KWA  \n",
       "16         KWA  \n",
       "17         KWA  \n",
       "18         KWA  \n",
       "19         KWA  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 =  r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_1final.csv\"\n",
    "df = pd.read_csv(df1)\n",
    "df.info()\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 270723 entries, 0 to 270722\n",
      "Data columns (total 7 columns):\n",
      " #   Column           Non-Null Count   Dtype \n",
      "---  ------           --------------   ----- \n",
      " 0   fbwindairportid  270723 non-null  int64 \n",
      " 1   ordinal          270723 non-null  int64 \n",
      " 2   bearing          270723 non-null  int64 \n",
      " 3   knots            270723 non-null  int64 \n",
      " 4   temperature      270723 non-null  int64 \n",
      " 5   fbwindreportid   270723 non-null  int64 \n",
      " 6   airportcode      270723 non-null  object\n",
      "dtypes: int64(6), object(1)\n",
      "memory usage: 14.5+ MB\n"
     ]
    },
    {
     "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>fbwindairportid</th>\n",
       "      <th>ordinal</th>\n",
       "      <th>bearing</th>\n",
       "      <th>knots</th>\n",
       "      <th>temperature</th>\n",
       "      <th>fbwindreportid</th>\n",
       "      <th>airportcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>243222</td>\n",
       "      <td>0</td>\n",
       "      <td>90</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>243222</td>\n",
       "      <td>1</td>\n",
       "      <td>90</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>243222</td>\n",
       "      <td>2</td>\n",
       "      <td>90</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>243222</td>\n",
       "      <td>3</td>\n",
       "      <td>80</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>243222</td>\n",
       "      <td>4</td>\n",
       "      <td>60</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>243222</td>\n",
       "      <td>5</td>\n",
       "      <td>50</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>243222</td>\n",
       "      <td>6</td>\n",
       "      <td>60</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>243222</td>\n",
       "      <td>7</td>\n",
       "      <td>60</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>243222</td>\n",
       "      <td>8</td>\n",
       "      <td>80</td>\n",
       "      <td>5</td>\n",
       "      <td>-4</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>243222</td>\n",
       "      <td>9</td>\n",
       "      <td>110</td>\n",
       "      <td>5</td>\n",
       "      <td>-17</td>\n",
       "      <td>3018</td>\n",
       "      <td>JON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>243223</td>\n",
       "      <td>0</td>\n",
       "      <td>130</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>243223</td>\n",
       "      <td>1</td>\n",
       "      <td>130</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>243223</td>\n",
       "      <td>2</td>\n",
       "      <td>130</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>243223</td>\n",
       "      <td>3</td>\n",
       "      <td>120</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>243223</td>\n",
       "      <td>4</td>\n",
       "      <td>110</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>243223</td>\n",
       "      <td>5</td>\n",
       "      <td>110</td>\n",
       "      <td>16</td>\n",
       "      <td>12</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>243223</td>\n",
       "      <td>6</td>\n",
       "      <td>110</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>243223</td>\n",
       "      <td>7</td>\n",
       "      <td>90</td>\n",
       "      <td>22</td>\n",
       "      <td>2</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>243223</td>\n",
       "      <td>8</td>\n",
       "      <td>80</td>\n",
       "      <td>24</td>\n",
       "      <td>-6</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>243223</td>\n",
       "      <td>9</td>\n",
       "      <td>80</td>\n",
       "      <td>21</td>\n",
       "      <td>-16</td>\n",
       "      <td>3018</td>\n",
       "      <td>KWA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    fbwindairportid  ordinal  bearing  knots  temperature  fbwindreportid  \\\n",
       "0            243222        0       90     12            0            3018   \n",
       "1            243222        1       90     12            0            3018   \n",
       "2            243222        2       90     12            0            3018   \n",
       "3            243222        3       80     12            0            3018   \n",
       "4            243222        4       60     12           15            3018   \n",
       "5            243222        5       50     10           11            3018   \n",
       "6            243222        6       60      8            8            3018   \n",
       "7            243222        7       60      7            3            3018   \n",
       "8            243222        8       80      5           -4            3018   \n",
       "9            243222        9      110      5          -17            3018   \n",
       "10           243223        0      130      8            0            3018   \n",
       "11           243223        1      130      8            0            3018   \n",
       "12           243223        2      130      8            0            3018   \n",
       "13           243223        3      120      9            0            3018   \n",
       "14           243223        4      110     11           17            3018   \n",
       "15           243223        5      110     16           12            3018   \n",
       "16           243223        6      110     20            7            3018   \n",
       "17           243223        7       90     22            2            3018   \n",
       "18           243223        8       80     24           -6            3018   \n",
       "19           243223        9       80     21          -16            3018   \n",
       "\n",
       "   airportcode  \n",
       "0          JON  \n",
       "1          JON  \n",
       "2          JON  \n",
       "3          JON  \n",
       "4          JON  \n",
       "5          JON  \n",
       "6          JON  \n",
       "7          JON  \n",
       "8          JON  \n",
       "9          JON  \n",
       "10         KWA  \n",
       "11         KWA  \n",
       "12         KWA  \n",
       "13         KWA  \n",
       "14         KWA  \n",
       "15         KWA  \n",
       "16         KWA  \n",
       "17         KWA  \n",
       "18         KWA  \n",
       "19         KWA  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 =  r\"C:\\Users\\omnku\\Desktop\\Data hack 24hrs\\data science dataset\\Final\\FD_2final.csv\"\n",
    "df = pd.read_csv(df1)\n",
    "df.info()\n",
    "df.head(20)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}