0.1-etl_parse_weather_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b529e788-8130-443e-8a68-0a240bb2f8c8",
   "metadata": {},
   "source": [
    "# ETL Hourly Weather Data Per Airport\n",
    "Local Climatological Data (LCD) can be found on NOAA's website: https://www.ncdc.noaa.gov/cdo-web/datatools/lcd. All times provided are in local standard time. It is more convenient to download the archive weather data (https://www.ncei.noaa.gov/data/local-climatological-data/archive/) which is physically partitioned by year and station number. Mapping of station identification number to airport IATA codes can be found at: https://www.ncdc.noaa.gov/data-access/land-based-station-data/station-metadata (download isd-history.txt). There may be overlap between multiple station numbers and IATA codes over the same period of time.  \n",
    "\n",
    "Data pre-processing is performed to read data directly from within the compressed archive files (YYYY.tar.gz) and transform it into parquet for subsequent processing. Fully extracting the weather data requires >20GB per year since ~10k station files are included. Data down-selection is applied to filter for the stations that correspond to ~400 airports in the BTS Airline On-Time Performance dataset. Pandas merge_asof() is applied to impute missing times within a few hours of the data gap. Larger gaps are assumed to be nominal conditions (e.g., clear sky, full visibility, etc.) for irregularly occurring attributes. Interpolation is applied to continuous attributes (only HourlyDryBulbTemperature at the moment), however, a seasonality model would be needed to fill larger data gaps.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8c5ab1ed-7223-4888-b143-d0f3e14c9cc1",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/btong/miniconda3/envs/rapids-21.12/lib/python3.8/site-packages/distributed/node.py:160: UserWarning: Port 8787 is already in use.\n",
      "Perhaps you already have a cluster running?\n",
      "Hosting the HTTP server on port 46881 instead\n",
      "  warnings.warn(\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "    <div style=\"width: 24px; height: 24px; background-color: #e1e1e1; border: 3px solid #9D9D9D; border-radius: 5px; position: absolute;\"> </div>\n",
       "    <div style=\"margin-left: 48px;\">\n",
       "        <h3 style=\"margin-bottom: 0px;\">Client</h3>\n",
       "        <p style=\"color: #9D9D9D; margin-bottom: 0px;\">Client-08c01dd6-7d6e-11ec-8018-a87eeae84103</p>\n",
       "        <table style=\"width: 100%; text-align: left;\">\n",
       "\n",
       "        <tr>\n",
       "        \n",
       "            <td style=\"text-align: left;\"><strong>Connection method:</strong> Cluster object</td>\n",
       "            <td style=\"text-align: left;\"><strong>Cluster type:</strong> distributed.LocalCluster</td>\n",
       "        \n",
       "        </tr>\n",
       "\n",
       "        \n",
       "            <tr>\n",
       "                <td style=\"text-align: left;\">\n",
       "                    <strong>Dashboard: </strong> <a href=\"http://127.0.0.1:46881/status\" target=\"_blank\">http://127.0.0.1:46881/status</a>\n",
       "                </td>\n",
       "                <td style=\"text-align: left;\"></td>\n",
       "            </tr>\n",
       "        \n",
       "\n",
       "        </table>\n",
       "\n",
       "        \n",
       "            <details>\n",
       "            <summary style=\"margin-bottom: 20px;\"><h3 style=\"display: inline;\">Cluster Info</h3></summary>\n",
       "            <div class=\"jp-RenderedHTMLCommon jp-RenderedHTML jp-mod-trusted jp-OutputArea-output\">\n",
       "    <div style=\"width: 24px; height: 24px; background-color: #e1e1e1; border: 3px solid #9D9D9D; border-radius: 5px; position: absolute;\">\n",
       "    </div>\n",
       "    <div style=\"margin-left: 48px;\">\n",
       "        <h3 style=\"margin-bottom: 0px; margin-top: 0px;\">LocalCluster</h3>\n",
       "        <p style=\"color: #9D9D9D; margin-bottom: 0px;\">cff3042f</p>\n",
       "        <table style=\"width: 100%; text-align: left;\">\n",
       "            <tr>\n",
       "                <td style=\"text-align: left;\">\n",
       "                    <strong>Dashboard:</strong> <a href=\"http://127.0.0.1:46881/status\" target=\"_blank\">http://127.0.0.1:46881/status</a>\n",
       "                </td>\n",
       "                <td style=\"text-align: left;\">\n",
       "                    <strong>Workers:</strong> 4\n",
       "                </td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                <td style=\"text-align: left;\">\n",
       "                    <strong>Total threads:</strong> 64\n",
       "                </td>\n",
       "                <td style=\"text-align: left;\">\n",
       "                    <strong>Total memory:</strong> 251.65 GiB\n",
       "                </td>\n",
       "            </tr>\n",
       "            \n",
       "            <tr>\n",
       "    <td style=\"text-align: left;\"><strong>Status:</strong> running</td>\n",
       "    <td style=\"text-align: left;\"><strong>Using processes:</strong> True</td>\n",
       "</tr>\n",
       "\n",
       "            \n",
       "        </table>\n",
       "\n",
       "        <details>\n",
       "            <summary style=\"margin-bottom: 20px;\">\n",
       "                <h3 style=\"display: inline;\">Scheduler Info</h3>\n",
       "            </summary>\n",
       "\n",
       "            <div style=\"\">\n",
       "    <div>\n",
       "        <div style=\"width: 24px; height: 24px; background-color: #FFF7E5; border: 3px solid #FF6132; border-radius: 5px; position: absolute;\"> </div>\n",
       "        <div style=\"margin-left: 48px;\">\n",
       "            <h3 style=\"margin-bottom: 0px;\">Scheduler</h3>\n",
       "            <p style=\"color: #9D9D9D; margin-bottom: 0px;\">Scheduler-ba053adb-0f00-4734-9c12-5113ccae2ae7</p>\n",
       "            <table style=\"width: 100%; text-align: left;\">\n",
       "                <tr>\n",
       "                    <td style=\"text-align: left;\">\n",
       "                        <strong>Comm:</strong> tcp://127.0.0.1:37105\n",
       "                    </td>\n",
       "                    <td style=\"text-align: left;\">\n",
       "                        <strong>Workers:</strong> 4\n",
       "                    </td>\n",
       "                </tr>\n",
       "                <tr>\n",
       "                    <td style=\"text-align: left;\">\n",
       "                        <strong>Dashboard:</strong> <a href=\"http://127.0.0.1:46881/status\" target=\"_blank\">http://127.0.0.1:46881/status</a>\n",
       "                    </td>\n",
       "                    <td style=\"text-align: left;\">\n",
       "                        <strong>Total threads:</strong> 64\n",
       "                    </td>\n",
       "                </tr>\n",
       "                <tr>\n",
       "                    <td style=\"text-align: left;\">\n",
       "                        <strong>Started:</strong> Just now\n",
       "                    </td>\n",
       "                    <td style=\"text-align: left;\">\n",
       "                        <strong>Total memory:</strong> 251.65 GiB\n",
       "                    </td>\n",
       "                </tr>\n",
       "            </table>\n",
       "        </div>\n",
       "    </div>\n",
       "\n",
       "    <details style=\"margin-left: 48px;\">\n",
       "        <summary style=\"margin-bottom: 20px;\">\n",
       "            <h3 style=\"display: inline;\">Workers</h3>\n",
       "        </summary>\n",
       "\n",
       "        \n",
       "        <div style=\"margin-bottom: 20px;\">\n",
       "            <div style=\"width: 24px; height: 24px; background-color: #DBF5FF; border: 3px solid #4CC9FF; border-radius: 5px; position: absolute;\"> </div>\n",
       "            <div style=\"margin-left: 48px;\">\n",
       "            <details>\n",
       "                <summary>\n",
       "                    <h4 style=\"margin-bottom: 0px; display: inline;\">Worker: 0</h4>\n",
       "                </summary>\n",
       "                <table style=\"width: 100%; text-align: left;\">\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Comm: </strong> tcp://127.0.0.1:35005\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Total threads: </strong> 16\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Dashboard: </strong> <a href=\"http://127.0.0.1:39653/status\" target=\"_blank\">http://127.0.0.1:39653/status</a>\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Memory: </strong> 62.91 GiB\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Nanny: </strong> tcp://127.0.0.1:37579\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\"></td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td colspan=\"2\" style=\"text-align: left;\">\n",
       "                            <strong>Local directory: </strong> /home/btong/github/flight-delay-causes/dask-worker-space/worker-e3xvilek\n",
       "                        </td>\n",
       "                    </tr>\n",
       "\n",
       "                    \n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>GPU: </strong>Quadro RTX 8000\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>GPU memory: </strong> 48.00 GiB\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    \n",
       "\n",
       "                    \n",
       "\n",
       "                </table>\n",
       "            </details>\n",
       "            </div>\n",
       "        </div>\n",
       "        \n",
       "        <div style=\"margin-bottom: 20px;\">\n",
       "            <div style=\"width: 24px; height: 24px; background-color: #DBF5FF; border: 3px solid #4CC9FF; border-radius: 5px; position: absolute;\"> </div>\n",
       "            <div style=\"margin-left: 48px;\">\n",
       "            <details>\n",
       "                <summary>\n",
       "                    <h4 style=\"margin-bottom: 0px; display: inline;\">Worker: 1</h4>\n",
       "                </summary>\n",
       "                <table style=\"width: 100%; text-align: left;\">\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Comm: </strong> tcp://127.0.0.1:37085\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Total threads: </strong> 16\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Dashboard: </strong> <a href=\"http://127.0.0.1:41023/status\" target=\"_blank\">http://127.0.0.1:41023/status</a>\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Memory: </strong> 62.91 GiB\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Nanny: </strong> tcp://127.0.0.1:35593\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\"></td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td colspan=\"2\" style=\"text-align: left;\">\n",
       "                            <strong>Local directory: </strong> /home/btong/github/flight-delay-causes/dask-worker-space/worker-a7g5la17\n",
       "                        </td>\n",
       "                    </tr>\n",
       "\n",
       "                    \n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>GPU: </strong>Quadro RTX 8000\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>GPU memory: </strong> 48.00 GiB\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    \n",
       "\n",
       "                    \n",
       "\n",
       "                </table>\n",
       "            </details>\n",
       "            </div>\n",
       "        </div>\n",
       "        \n",
       "        <div style=\"margin-bottom: 20px;\">\n",
       "            <div style=\"width: 24px; height: 24px; background-color: #DBF5FF; border: 3px solid #4CC9FF; border-radius: 5px; position: absolute;\"> </div>\n",
       "            <div style=\"margin-left: 48px;\">\n",
       "            <details>\n",
       "                <summary>\n",
       "                    <h4 style=\"margin-bottom: 0px; display: inline;\">Worker: 2</h4>\n",
       "                </summary>\n",
       "                <table style=\"width: 100%; text-align: left;\">\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Comm: </strong> tcp://127.0.0.1:39477\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Total threads: </strong> 16\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Dashboard: </strong> <a href=\"http://127.0.0.1:34645/status\" target=\"_blank\">http://127.0.0.1:34645/status</a>\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Memory: </strong> 62.91 GiB\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Nanny: </strong> tcp://127.0.0.1:34325\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\"></td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td colspan=\"2\" style=\"text-align: left;\">\n",
       "                            <strong>Local directory: </strong> /home/btong/github/flight-delay-causes/dask-worker-space/worker-nfx77vmg\n",
       "                        </td>\n",
       "                    </tr>\n",
       "\n",
       "                    \n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>GPU: </strong>Quadro RTX 8000\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>GPU memory: </strong> 48.00 GiB\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    \n",
       "\n",
       "                    \n",
       "\n",
       "                </table>\n",
       "            </details>\n",
       "            </div>\n",
       "        </div>\n",
       "        \n",
       "        <div style=\"margin-bottom: 20px;\">\n",
       "            <div style=\"width: 24px; height: 24px; background-color: #DBF5FF; border: 3px solid #4CC9FF; border-radius: 5px; position: absolute;\"> </div>\n",
       "            <div style=\"margin-left: 48px;\">\n",
       "            <details>\n",
       "                <summary>\n",
       "                    <h4 style=\"margin-bottom: 0px; display: inline;\">Worker: 3</h4>\n",
       "                </summary>\n",
       "                <table style=\"width: 100%; text-align: left;\">\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Comm: </strong> tcp://127.0.0.1:33597\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Total threads: </strong> 16\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Dashboard: </strong> <a href=\"http://127.0.0.1:38073/status\" target=\"_blank\">http://127.0.0.1:38073/status</a>\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Memory: </strong> 62.91 GiB\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Nanny: </strong> tcp://127.0.0.1:45149\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\"></td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td colspan=\"2\" style=\"text-align: left;\">\n",
       "                            <strong>Local directory: </strong> /home/btong/github/flight-delay-causes/dask-worker-space/worker-ep25a2fc\n",
       "                        </td>\n",
       "                    </tr>\n",
       "\n",
       "                    \n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>GPU: </strong>Quadro RTX 8000\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>GPU memory: </strong> 48.00 GiB\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    \n",
       "\n",
       "                    \n",
       "\n",
       "                </table>\n",
       "            </details>\n",
       "            </div>\n",
       "        </div>\n",
       "        \n",
       "\n",
       "    </details>\n",
       "</div>\n",
       "\n",
       "        </details>\n",
       "    </div>\n",
       "</div>\n",
       "            </details>\n",
       "        \n",
       "\n",
       "    </div>\n",
       "</div>"
      ],
      "text/plain": [
       "<Client: 'tcp://127.0.0.1:37105' processes=4 threads=64, memory=251.65 GiB>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "import io\n",
    "import sys\n",
    "import glob\n",
    "import shutil\n",
    "import tarfile\n",
    "import pandas as pd\n",
    "import pyarrow as pa\n",
    "import pyarrow.parquet as pq\n",
    "from pandas.api.types import CategoricalDtype\n",
    "import numpy as np\n",
    "from functools import reduce\n",
    "from time import time\n",
    "import re\n",
    "from scipy.interpolate import interp1d\n",
    "\n",
    "import dask\n",
    "import dask.dataframe as dd\n",
    "from dask.distributed import Client, wait, progress, get_worker\n",
    "\n",
    "\n",
    "analysis_years = np.arange(2021,2022,1)\n",
    "\n",
    "# Directory of archive LCD weather data:\n",
    "wx_raw_data_dir = './data/raw/weather/noaa_lcd_archive/'\n",
    "wx_processed_data_dir = './data/converted/weather/NAS/'\n",
    "os.makedirs(wx_processed_data_dir, exist_ok = True)\n",
    "\n",
    "# Write intermediate parquet file to tmp:\n",
    "tmp_wx_output_dir = './tmp/wx_archive_parquet/' # DATA WILL BE WIPED WITH EACH RUN\n",
    "os.makedirs(tmp_wx_output_dir, exist_ok = True)\n",
    "\n",
    "use_cols = ['STATION', 'DATE', 'REPORT_TYPE', \n",
    "            'HourlyDryBulbTemperature', 'HourlyPresentWeatherType', 'HourlySkyConditions', \n",
    "            'HourlyPrecipitation', 'HourlyPressureChange', 'HourlyVisibility', 'HourlyWindDirection', 'HourlyWindGustSpeed', 'HourlyWindSpeed'\n",
    "           ]\n",
    "\n",
    "# HourlyRelativeHumidity has a lot of missing values (73k/161k hours missing). Would be difficult to impute so should be removed.\n",
    "# HourlyPresentWeatherType: needs to be decoded.\n",
    "# HourlySkyConditions: needs to be decoded. Probably care about SEVERE weather which is likely sparse.\n",
    "\n",
    "client = Client(n_workers=4, threads_per_worker=16) # Good for DSWS.\n",
    "client"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "a7c68c70-9abd-4f42-94bf-0f232b7a4d90",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Airports without weather data: ['AZA_AZ', 'BET_AK', 'BQN_PR', 'BRW_AK', 'CDB_AK', 'CDV_AK', 'DLG_AK', 'FAI_AK', 'FCA_MT', 'GUM_TT', 'HHH_SC', 'ITO_HI', 'JNU_AK', 'KOA_HI', 'KTN_AK', 'LIH_HI', 'OGG_HI', 'OME_AK', 'OTZ_AK', 'PSE_PR', 'PSG_AK', 'SCC_AK', 'SCE_PA', 'SIT_AK', 'SJU_PR', 'SPN_TT', 'STT_VI', 'STX_VI', 'USA_NC', 'WRG_AK', 'XWA_ND', 'YAK_AK']\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>USAF</th>\n",
       "      <th>WBAN</th>\n",
       "      <th>STATION NAME</th>\n",
       "      <th>CTRY</th>\n",
       "      <th>ST</th>\n",
       "      <th>CALL</th>\n",
       "      <th>LAT</th>\n",
       "      <th>LON</th>\n",
       "      <th>ELEV(M)</th>\n",
       "      <th>BEGIN</th>\n",
       "      <th>END</th>\n",
       "      <th>IATA</th>\n",
       "      <th>STATION</th>\n",
       "      <th>IATA_ST</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15316</th>\n",
       "      <td>702730</td>\n",
       "      <td>26451</td>\n",
       "      <td>TED STEVENS ANCHORAGE I</td>\n",
       "      <td>US</td>\n",
       "      <td>AK</td>\n",
       "      <td>PANC</td>\n",
       "      <td>61.169</td>\n",
       "      <td>-150.028</td>\n",
       "      <td>36.6</td>\n",
       "      <td>19730101.0</td>\n",
       "      <td>20210515.0</td>\n",
       "      <td>ANC</td>\n",
       "      <td>70273026451</td>\n",
       "      <td>ANC_AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15382</th>\n",
       "      <td>703260</td>\n",
       "      <td>25503</td>\n",
       "      <td>KING SALMON AIRPORT</td>\n",
       "      <td>US</td>\n",
       "      <td>AK</td>\n",
       "      <td>PAKN</td>\n",
       "      <td>58.683</td>\n",
       "      <td>-156.656</td>\n",
       "      <td>20.4</td>\n",
       "      <td>19420110.0</td>\n",
       "      <td>20210515.0</td>\n",
       "      <td>AKN</td>\n",
       "      <td>70326025503</td>\n",
       "      <td>AKN_AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15401</th>\n",
       "      <td>703500</td>\n",
       "      <td>25501</td>\n",
       "      <td>KODIAK AIRPORT</td>\n",
       "      <td>US</td>\n",
       "      <td>AK</td>\n",
       "      <td>PADQ</td>\n",
       "      <td>57.751</td>\n",
       "      <td>-152.486</td>\n",
       "      <td>24.4</td>\n",
       "      <td>19450101.0</td>\n",
       "      <td>20210515.0</td>\n",
       "      <td>ADQ</td>\n",
       "      <td>70350025501</td>\n",
       "      <td>ADQ_AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15472</th>\n",
       "      <td>704540</td>\n",
       "      <td>25704</td>\n",
       "      <td>ADAK NAS</td>\n",
       "      <td>US</td>\n",
       "      <td>AK</td>\n",
       "      <td>PADK</td>\n",
       "      <td>51.883</td>\n",
       "      <td>-176.650</td>\n",
       "      <td>5.2</td>\n",
       "      <td>19421030.0</td>\n",
       "      <td>20210515.0</td>\n",
       "      <td>ADK</td>\n",
       "      <td>70454025704</td>\n",
       "      <td>ADK_AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15473</th>\n",
       "      <td>704540</td>\n",
       "      <td>99999</td>\n",
       "      <td>ADAK (NAS)</td>\n",
       "      <td>US</td>\n",
       "      <td>AK</td>\n",
       "      <td>PADK</td>\n",
       "      <td>51.883</td>\n",
       "      <td>-176.650</td>\n",
       "      <td>5.0</td>\n",
       "      <td>20000101.0</td>\n",
       "      <td>20031231.0</td>\n",
       "      <td>ADK</td>\n",
       "      <td>70454099999</td>\n",
       "      <td>ADK_AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29505</th>\n",
       "      <td>999999</td>\n",
       "      <td>94849</td>\n",
       "      <td>ALPENA PHELPS COLLINS F</td>\n",
       "      <td>US</td>\n",
       "      <td>MI</td>\n",
       "      <td>KAPN</td>\n",
       "      <td>45.072</td>\n",
       "      <td>-83.564</td>\n",
       "      <td>211.2</td>\n",
       "      <td>19590901.0</td>\n",
       "      <td>19721231.0</td>\n",
       "      <td>APN</td>\n",
       "      <td>99999994849</td>\n",
       "      <td>APN_MI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29506</th>\n",
       "      <td>999999</td>\n",
       "      <td>94860</td>\n",
       "      <td>GRAND RAPIDS KENT COUNT</td>\n",
       "      <td>US</td>\n",
       "      <td>MI</td>\n",
       "      <td>KGRR</td>\n",
       "      <td>42.883</td>\n",
       "      <td>-85.524</td>\n",
       "      <td>244.8</td>\n",
       "      <td>19631201.0</td>\n",
       "      <td>19721231.0</td>\n",
       "      <td>GRR</td>\n",
       "      <td>99999994860</td>\n",
       "      <td>GRR_MI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29510</th>\n",
       "      <td>999999</td>\n",
       "      <td>94908</td>\n",
       "      <td>DUBUQUE MUNICIPAL AP</td>\n",
       "      <td>US</td>\n",
       "      <td>IA</td>\n",
       "      <td>KDBQ</td>\n",
       "      <td>42.398</td>\n",
       "      <td>-90.704</td>\n",
       "      <td>329.2</td>\n",
       "      <td>19510201.0</td>\n",
       "      <td>19721231.0</td>\n",
       "      <td>DBQ</td>\n",
       "      <td>99999994908</td>\n",
       "      <td>DBQ_IA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29511</th>\n",
       "      <td>999999</td>\n",
       "      <td>94910</td>\n",
       "      <td>WATERLOO MUNICIPAL AP</td>\n",
       "      <td>US</td>\n",
       "      <td>IA</td>\n",
       "      <td>KALO</td>\n",
       "      <td>42.554</td>\n",
       "      <td>-92.401</td>\n",
       "      <td>267.6</td>\n",
       "      <td>19490101.0</td>\n",
       "      <td>19721231.0</td>\n",
       "      <td>ALO</td>\n",
       "      <td>99999994910</td>\n",
       "      <td>ALO_IA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29513</th>\n",
       "      <td>999999</td>\n",
       "      <td>94931</td>\n",
       "      <td>HIBBING CHISHOLM-HIBBIN</td>\n",
       "      <td>US</td>\n",
       "      <td>MN</td>\n",
       "      <td>KHIB</td>\n",
       "      <td>47.386</td>\n",
       "      <td>-92.839</td>\n",
       "      <td>413.6</td>\n",
       "      <td>19720101.0</td>\n",
       "      <td>19721231.0</td>\n",
       "      <td>HIB</td>\n",
       "      <td>99999994931</td>\n",
       "      <td>HIB_MN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>702 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         USAF   WBAN             STATION NAME CTRY  ST  CALL     LAT      LON  \\\n",
       "15316  702730  26451  TED STEVENS ANCHORAGE I   US  AK  PANC  61.169 -150.028   \n",
       "15382  703260  25503      KING SALMON AIRPORT   US  AK  PAKN  58.683 -156.656   \n",
       "15401  703500  25501           KODIAK AIRPORT   US  AK  PADQ  57.751 -152.486   \n",
       "15472  704540  25704                 ADAK NAS   US  AK  PADK  51.883 -176.650   \n",
       "15473  704540  99999               ADAK (NAS)   US  AK  PADK  51.883 -176.650   \n",
       "...       ...    ...                      ...  ...  ..   ...     ...      ...   \n",
       "29505  999999  94849  ALPENA PHELPS COLLINS F   US  MI  KAPN  45.072  -83.564   \n",
       "29506  999999  94860  GRAND RAPIDS KENT COUNT   US  MI  KGRR  42.883  -85.524   \n",
       "29510  999999  94908     DUBUQUE MUNICIPAL AP   US  IA  KDBQ  42.398  -90.704   \n",
       "29511  999999  94910    WATERLOO MUNICIPAL AP   US  IA  KALO  42.554  -92.401   \n",
       "29513  999999  94931  HIBBING CHISHOLM-HIBBIN   US  MN  KHIB  47.386  -92.839   \n",
       "\n",
       "       ELEV(M)       BEGIN         END IATA      STATION IATA_ST  \n",
       "15316     36.6  19730101.0  20210515.0  ANC  70273026451  ANC_AK  \n",
       "15382     20.4  19420110.0  20210515.0  AKN  70326025503  AKN_AK  \n",
       "15401     24.4  19450101.0  20210515.0  ADQ  70350025501  ADQ_AK  \n",
       "15472      5.2  19421030.0  20210515.0  ADK  70454025704  ADK_AK  \n",
       "15473      5.0  20000101.0  20031231.0  ADK  70454099999  ADK_AK  \n",
       "...        ...         ...         ...  ...          ...     ...  \n",
       "29505    211.2  19590901.0  19721231.0  APN  99999994849  APN_MI  \n",
       "29506    244.8  19631201.0  19721231.0  GRR  99999994860  GRR_MI  \n",
       "29510    329.2  19510201.0  19721231.0  DBQ  99999994908  DBQ_IA  \n",
       "29511    267.6  19490101.0  19721231.0  ALO  99999994910  ALO_IA  \n",
       "29513    413.6  19720101.0  19721231.0  HIB  99999994931  HIB_MN  \n",
       "\n",
       "[702 rows x 14 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_file = './data/raw/weather/isd-history.txt'\n",
    "station_df = pd.read_fwf(station_file, skiprows=20).drop(0)\n",
    "station_df['WBAN'] = station_df['WBAN'].astype(int)\n",
    "\n",
    "# Multiple USAF/WBAN code can correspond to same CALL since station can be moved. Check BEGIN/END date to make sure data still valid for interested airports.\n",
    "station_df['IATA'] = station_df['CALL'].str[1:] # Approximate by dropping first letter\n",
    "station_df['STATION'] = station_df['USAF'].astype(str) + station_df['WBAN'].astype(str).str.zfill(5)\n",
    "station_df['IATA_ST'] = station_df['IATA'] + '_' + station_df['ST']\n",
    "\n",
    "# Identify unique airports within data:\n",
    "flt_pq_output_dir = './data/converted/flight'\n",
    "flt_data = dd.read_parquet(flt_pq_output_dir, columns=['DEST', 'ORIGIN']).categorize()\n",
    "od_arpt = sorted(set(flt_data['ORIGIN'].cat.categories.values) | set(flt_data['DEST'].cat.categories.values))\n",
    "# print('NAS ORIGIN/DEST airports:', od_arpt)\n",
    "\n",
    "# Filter for NAS airports:\n",
    "station_df = station_df[station_df['IATA_ST'].isin(od_arpt)]\n",
    "\n",
    "# Airports without wx data:\n",
    "print('Airports without weather data:', sorted(set(od_arpt) - set(station_df['IATA_ST'])))\n",
    "\n",
    "station_df"
   ]
  },
  {
   "cell_type": "raw",
   "id": "50d9f6e4-911e-4ca3-9015-83c380662483",
   "metadata": {},
   "source": [
    "# Filter sample csv files for export. Only run this cell if creating sample data for github repo.\n",
    "wx_csv_fn = list(station_df['USAF'].astype(str) + station_df['WBAN'].astype(str) + '.csv')\n",
    "wx_csv_fn = wx_csv_fn[:400] # Reduce number of airports\n",
    "\n",
    "csv_files = glob.glob('./data/raw/weather/noaa_lcd_archive/test/*.csv')\n",
    "csv_files = pd.DataFrame({'file_path': csv_files})\n",
    "csv_files['fn'] = csv_files['file_path'].str.split('/').str[-1]\n",
    "csv_files = csv_files[csv_files['fn'].isin(wx_csv_fn)]\n",
    "\n",
    "# Create tar.gz file with only stations of interest:\n",
    "with tarfile.open(\"wx_sample.tar.gz\", \"w:gz\") as tar:\n",
    "    for name in csv_files['file_path']:\n",
    "        tar.add(name, arcname=name.split('/')[-1])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dfc4da02-6295-4dd2-bde7-1c72187e7873",
   "metadata": {},
   "source": [
    "# Convert tar.gz Archived CSV to Parquet:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "09e0e11e-6ce5-4f99-a0b8-6970d5756f79",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/btong/miniconda3/envs/rapids-21.12/lib/python3.8/site-packages/distributed/node.py:160: UserWarning: Port 8787 is already in use.\n",
      "Perhaps you already have a cluster running?\n",
      "Hosting the HTTP server on port 35165 instead\n",
      "  warnings.warn(\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "tar.gz to parquet conversion time: 9.35\n",
      "CPU times: user 721 ms, sys: 327 ms, total: 1.05 s\n",
      "Wall time: 10.1 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "def wx_tar_to_df(tar, fn_list, usecols, output_dir):\n",
    "    \"\"\"\n",
    "    Read csv within tar file and convert results to a dataframe.\n",
    "    \n",
    "    tar: tarfile object\n",
    "        tarfile object that is open for reading.\n",
    "    fn: str\n",
    "        Filename of csv file within tarfile\n",
    "    usecols: list\n",
    "        Columns to read within csv.\n",
    "    \"\"\"\n",
    "    tmp_ = []\n",
    "    for fn in fn_list:\n",
    "        tmp_.append(pd.read_csv(io.BytesIO(tar.extractfile(fn).read()), usecols=usecols, low_memory=False, dtype=str))\n",
    "    \n",
    "    out = pd.concat(tmp_)\n",
    "    out['STATION'] = out['STATION'].astype(str) # STATION identifier can be str. Need consistent dtype for merging.\n",
    "    out.to_parquet(output_dir)\n",
    "    return()\n",
    "\n",
    "@dask.delayed\n",
    "def process_wx_targz(cur_year):\n",
    "    \"\"\"\n",
    "    Process individual tar.gz weather archive files. Dask cannot serialize tarfile, so can only parallelize individual year archives.\n",
    "    \"\"\"\n",
    "    tar = tarfile.open(wx_raw_data_dir+str(cur_year)+'.tar.gz', 'r')\n",
    "    weather_files = tar.getnames() # Takes about 1 minute to get filenames?\n",
    "\n",
    "    # Read and convert files using dask. Split workload based on number of files.\n",
    "    toc = pd.DataFrame(weather_files)\n",
    "    toc.columns = ['filename']\n",
    "    toc['STATION'] = toc['filename'].str.split('.').str[0]\n",
    "\n",
    "    # Process stations corresponding to flight records:\n",
    "    toc = toc[toc['STATION'].isin(station_df['STATION'])]\n",
    "    \n",
    "    wx_data = wx_tar_to_df(tar, list(toc['filename']), use_cols, tmp_wx_output_dir+str(cur_year)+'.parquet')\n",
    "    return()\n",
    "\n",
    "\n",
    "# Check if data exists in weather data output directory:\n",
    "num_pq_files = len(glob.glob(tmp_wx_output_dir+'/*.parquet'))\n",
    "\n",
    "if num_pq_files == len(analysis_years):\n",
    "    print('Expected number of parquet files detected in weather output temp directory. Parquet conversion skipped.')\n",
    "    \n",
    "else:\n",
    "    # Remove and remake directory:\n",
    "    try:\n",
    "        # RECURSIVELY DELETE DIRECTORY and then add it\n",
    "        shutil.rmtree(tmp_wx_output_dir)\n",
    "    except:\n",
    "        pass\n",
    "\n",
    "    os.mkdir(tmp_wx_output_dir)\n",
    "\n",
    "    # Process weather data from annual zip archive file. Need to also merge station number with common name. \n",
    "    # ~10.6k stations/files in 2003 LCD archive. Select only relevant files based on available flight data (e.g., ~400 airports).\n",
    "\n",
    "    # Dask currently only used for tar.gz processing. \n",
    "#     n_workers = 18\n",
    "#     client = Client(n_workers=n_workers, threads_per_worker=1)\n",
    "    client = Client()\n",
    "    \n",
    "    tic = time()\n",
    "    dask_tasks = [process_wx_targz(yr) for yr in analysis_years]\n",
    "    dask.compute(dask_tasks)\n",
    "    t_tar_conversion = time() - tic\n",
    "    print('tar.gz to parquet conversion time:', np.round(t_tar_conversion,2))\n",
    "    client.close()\n",
    "    \n",
    "# Parquet conversion takes ~13 minutes for 16-yr data running on 18 cores (1-core per year). A lot of time is spent on tar.getnames() within archive."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d678d9fb-922b-4f42-bfdd-81e095336daf",
   "metadata": {},
   "source": [
    "# Load Parquet Weather Data and Run ETL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "dd77116b-0f3d-4aec-a214-5c873a928e72",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique REPORT_TYPE: Dask Series Structure:\n",
      "npartitions=1\n",
      "    object\n",
      "       ...\n",
      "Name: REPORT_TYPE, dtype: object\n",
      "Dask Name: unique-agg, 14 tasks\n",
      "CPU times: user 71 ms, sys: 4.53 ms, total: 75.5 ms\n",
      "Wall time: 542 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "wx_data = dd.read_parquet(tmp_wx_output_dir)\n",
    "\n",
    "# Get start/end times for hourly resampling. Faster to compute before running other dask operations.\n",
    "wx_start = pd.to_datetime(np.min(wx_data['DATE']).compute())\n",
    "wx_end = pd.to_datetime(np.max(wx_data['DATE']).compute()) + pd.Timedelta('1D')\n",
    "\n",
    "# Merge station info:\n",
    "# wx_data = wx_data.merge(station_df[['STATION', 'IATA_ST', 'CALL', 'BEGIN', 'END']], on='STATION', how='left')\n",
    "wx_data = wx_data.merge(station_df[['STATION', 'IATA_ST']], on='STATION', how='left')\n",
    "# wx_data.loc[wx_data['IATA_ST'].isna(), 'IATA_ST'] = wx_data.loc[wx_data['IATA_ST'].isna(), 'STATION']\n",
    "\n",
    "# Source Type: https://www1.ncdc.noaa.gov/pub/data/ish/ish-format-document.pdf\n",
    "#  SOM: Summary of Month\n",
    "#  SOD: Summary of Day\n",
    "#  FM-15: METAR Aviation routine weather report\n",
    "#  FM-16: SPECI Aviation selected special weather report\n",
    "\n",
    "use_report_type = ['FM-12', 'FM-15', 'FM-16', 'SY-MT']\n",
    "# wx_data = wx_data[wx_data['REPORT_TYPE'].isin(use_report_type)] # .isin() has issues when using dask.dataframe.\n",
    "wx_data = wx_data.query(\"REPORT_TYPE=='FM-12' or REPORT_TYPE=='FM-15' or REPORT_TYPE=='FM-16' or REPORT_TYPE=='SY-MT'\")\n",
    "wx_data['DATE'] = dd.to_datetime(wx_data['DATE'])\n",
    "wx_data['DATE_HR_orig'] = wx_data['DATE'].dt.round('1H')\n",
    "wx_data = wx_data.sort_values('DATE')\n",
    "\n",
    "wx_grp = wx_data.groupby('IATA_ST')\n",
    "\n",
    "print('Unique REPORT_TYPE:', wx_data['REPORT_TYPE'].unique())\n",
    "# # Estimate number of unique hourly samples at each station:\n",
    "# num_records_orig = len(wx_data[['IATA_ST', 'DATE_HR_orig', 'STATION']].groupby(['IATA_ST', 'DATE_HR_orig']).last())\n",
    "# print('Number of original data samples:', num_records_orig)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "fa0abeb4-325d-477c-aeec-82e0074038db",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Numeric columns: ['HourlyWindDirection', 'HourlyWindSpeed', 'HourlyDryBulbTemperature', 'HourlyPressureChange', 'HourlyPrecipitation', 'HourlyVisibility', 'HourlyWindGustSpeed']\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<timed exec>:246: UserWarning: `meta` is not specified, inferred from partial data. Please provide `meta` if the result is unexpected.\n",
      "  Before: .apply(func)\n",
      "  After:  .apply(func, meta={'x': 'f8', 'y': 'f8'}) for dataframe result\n",
      "  or:     .apply(func, meta=('x', 'f8'))            for series result\n",
      "distributed.utils_perf - WARNING - full garbage collections took 11% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 11% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 11% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 11% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 11% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 11% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 11% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 11% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 11% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 11% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 11% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 11% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 11% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 11% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 11% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 11% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Weather processing time: 34.88\n",
      "CPU times: user 7.05 s, sys: 550 ms, total: 7.6 s\n",
      "Wall time: 34.9 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "def process_sky_condition(weather_df):\n",
    "    def parse_HourlySkyConditions(input_str):\n",
    "        \"\"\"Parse HourlySkyConditions from NOAA LCD data.\"\"\"\n",
    "        if input_str != None:\n",
    "            dc_list = input_str.split(' ')\n",
    "            if len(dc_list) > 1:\n",
    "                out = list(zip(dc_list[::2], dc_list[1::2]))\n",
    "            else:\n",
    "                out = None\n",
    "        else:\n",
    "            out = None\n",
    "        return(out)\n",
    "    # Process HourlySkyConditions by pre-filtering for non-NA's.\n",
    "    sky_condition = weather_df[~weather_df['HourlySkyConditions'].isna()]['HourlySkyConditions']\n",
    "    sky_condition = sky_condition.apply(parse_HourlySkyConditions)\n",
    "    sky_condition = sky_condition[~sky_condition.isna()] # Remove NA's introduced during parsing\n",
    "    sky_condition = sky_condition.explode()\n",
    "    sky_condition = sky_condition.reset_index()\n",
    "\n",
    "    sky_condition_sm = pd.DataFrame(list(sky_condition['HourlySkyConditions'].values), columns=['HourlySkyConditions_cover', 'HourlySkyConditions_height'])\n",
    "    sky_condition_sm['HourlySkyConditions_height'] = sky_condition_sm['HourlySkyConditions_height'].str.replace('s|\\*','0', regex=True)\n",
    "    sky_condition_sm['DATE'] = sky_condition['DATE']\n",
    "\n",
    "    # Restrict valid value of field based on str length:\n",
    "    sky_condition_sm['cover_len'] = sky_condition_sm['HourlySkyConditions_cover'].str.len()\n",
    "    sky_condition_sm = sky_condition_sm[sky_condition_sm['cover_len'].between(5,6)]\n",
    "\n",
    "    # Ensure unique HourlySkyConditions_cover per hour (e.g., okay to have one of each type, but not multiples). Use last known update.\n",
    "    sky_condition_sm = sky_condition_sm.groupby(['DATE', 'HourlySkyConditions_cover'], as_index=False).last()\n",
    "#     print('Sky Condition categories after filtering:')\n",
    "#     print(sky_condition_sm['HourlySkyConditions_cover'].value_counts())\n",
    "#     print()\n",
    "\n",
    "    # Truncate before the ':' to get larger grouping.\n",
    "    sky_condition_sm['HourlySkyConditions_cover'] = sky_condition_sm['HourlySkyConditions_cover'].str.split(':').str[0]\n",
    "#     print('Sky Condition categories after combining:')\n",
    "#     print(sky_condition_sm['HourlySkyConditions_cover'].value_counts())\n",
    "#     print()\n",
    "\n",
    "    sky_condition_pv = sky_condition_sm.pivot(columns=['HourlySkyConditions_cover'], values='HourlySkyConditions_height').fillna(0)\n",
    "    sky_condition_pv.columns = 'HourlySkyCover_'+sky_condition_pv.columns\n",
    "    sky_condition_pv = sky_condition_pv.merge(sky_condition_sm['DATE'], left_index=True, right_index=True, how='left').set_index('DATE')\n",
    "    sky_condition_pv = sky_condition_pv.replace('', '0').astype('int16') # int8 [-128,127], but range of data range is [0,350]. \n",
    "    \n",
    "#     sky_condition_pv = sky_condition_pv.drop(columns=['HourlySkyCover_CLR', 'HourlySkyCover_X'])\n",
    "    sky_condition_pv = sky_condition_pv.drop([cc for cc in ['HourlySkyCover_CLR', 'HourlySkyCover_X'] if cc in sky_condition_pv.columns], axis=1)\n",
    "    \n",
    "    return(sky_condition_pv)\n",
    "\n",
    "def process_present_wx(weather_df):\n",
    "    def parse_HourlyPresentWeatherType(input_str):\n",
    "        \"\"\"\n",
    "        Parse HourlyPresentWeatherType from NOAA LCD data.\n",
    "        AU|AW|MW for Automatic and Manual reporting. Simplify by dropping +- and numeric portion. Merge unique letter codes in all split fields.\n",
    "        \"\"\"\n",
    "        if input_str != None:\n",
    "            dc_list = list(set(re.sub('[\\|+-:]', '', input_str).split(' ')))\n",
    "            if len(dc_list) > 0:\n",
    "                out = dc_list\n",
    "            else:\n",
    "                out = None\n",
    "        else:\n",
    "            out = None\n",
    "        return(out)\n",
    "\n",
    "    # Process HourlyPresentWeatherType:\n",
    "    present_wx_type = weather_df[~weather_df['HourlyPresentWeatherType'].isna()]['HourlyPresentWeatherType']\n",
    "#     print('Top 10 HourlyPresentWeatherType:')\n",
    "#     print(present_wx_type.value_counts()[:10])\n",
    "#     print()\n",
    "\n",
    "    present_wx_type = present_wx_type.apply(parse_HourlyPresentWeatherType).reset_index()\n",
    "    present_wx_type['tmp'] = 1 # Used in pivot operation\n",
    "\n",
    "    # Expansion via OHE might be too sparse. There are 24 associated columns. \n",
    "    # Use alphabetical ordered list for each condition, then reduce list to single string.\n",
    "    present_wx_type['HourlyPresentWeatherTypeCombo'] = present_wx_type['HourlyPresentWeatherType'].apply(lambda x: '_'.join(list(set(x))))\n",
    "\n",
    "    # OHE expansion of HourlyPresentWeatherType too sparse. Use explicit combinations and TargetEncode. \n",
    "#     present_wx_type_pv = present_wx_type.explode('HourlyPresentWeatherType').pivot(columns=['HourlyPresentWeatherType'], values='tmp')\n",
    "\n",
    "#     try:\n",
    "#         present_wx_type_pv = present_wx_type_pv.drop(columns = ['', '*', 's'])\n",
    "#     except:\n",
    "#         pass\n",
    "\n",
    "#     present_wx_type_pv = present_wx_type_pv.fillna(0).astype('int16')\n",
    "\n",
    "#     # Merge weather data:\n",
    "#     present_wx_type_pv = (present_wx_type[['DATE', 'tmp']]\n",
    "#                           .merge(present_wx_type_pv, left_index=True, right_index=True, how='left')\n",
    "#                           .drop(columns='tmp')\n",
    "#                           .set_index('DATE')\n",
    "#                          )\n",
    "\n",
    "#     present_wx_type_pv.columns = 'HourlyPresentWeatherType_' + present_wx_type_pv.columns\n",
    "    return(present_wx_type)\n",
    "\n",
    "def fix_numeric_dtypes(weather_df):\n",
    "    weather_df = weather_df.drop(columns=['HourlySkyConditions', 'HourlyPresentWeatherType'])\n",
    "#     print(weather_df.dtypes)\n",
    "    \n",
    "    # Remove characters like T|V|s from the numerical fields:\n",
    "    for cc in numeric_cols:\n",
    "        # Replace missing data string as np.nan:\n",
    "        weather_df[cc] = weather_df[cc].replace('None|''', np.nan)        \n",
    "        \n",
    "        try:\n",
    "            # Handle case where column not recognized as str type. Maybe it's completely empty. \n",
    "            if cc == 'HourlyPrecipitation':\n",
    "                weather_df[cc] = weather_df[cc].str.split('.').apply(lambda x: '.'.join(x[:2]) if x != None else '0')\n",
    "\n",
    "            elif cc == 'HourlyWindDirection':\n",
    "                # Replace variable wind direction.\n",
    "                weather_df[cc] = weather_df[cc].str.replace('VRB','0')\n",
    "        except:\n",
    "            pass\n",
    "            \n",
    "        try:\n",
    "            weather_df[cc] = weather_df[cc].str.replace('s|T|V|\\*','', regex=True)\n",
    "        except:\n",
    "            pass\n",
    "\n",
    "        weather_df.loc[weather_df['HourlyPressureChange'].isin(['-', '+']), 'HourlyPressureChange'] = 0\n",
    "        weather_df[cc] = weather_df[cc].replace('', np.nan).astype('float32')\n",
    "\n",
    "\n",
    "    # Fix nan representation in float, then convert to int:\n",
    "    zero_na_fill_cols = ['HourlyPrecipitation', 'HourlyPressureChange', 'HourlyWindSpeed', 'HourlyWindDirection', 'HourlyWindGustSpeed']\n",
    "    weather_df[zero_na_fill_cols] = weather_df[zero_na_fill_cols].fillna(0)\n",
    "    weather_df['HourlyVisibility'] = weather_df['HourlyVisibility'].fillna(10) # Assume clear visibility if not provided\n",
    "    \n",
    "    # TODO: better imputation of missing numerics using seasonality model. Entire days could be missing so backf/forward filling not possible.\n",
    "    # Should use daily average or adjacent day average to avoid abrupt change.\n",
    "    \n",
    "    # Cast int fields to reduce storage:\n",
    "    for cc in col_dtypes['int16']:\n",
    "        weather_df[cc] = weather_df[cc].astype('int16')\n",
    "    \n",
    "    return(weather_df)\n",
    "\n",
    "col_dtypes = {'int16': ['HourlyWindDirection', 'HourlyWindSpeed'],\n",
    "              'float32': ['HourlyDryBulbTemperature', 'HourlyPressureChange', 'HourlyPrecipitation', 'HourlyVisibility', 'HourlyWindGustSpeed']\n",
    "             }\n",
    "\n",
    "numeric_cols = reduce(lambda x,y: x+y, col_dtypes.values())\n",
    "print('Numeric columns:', numeric_cols)\n",
    "\n",
    "\n",
    "def process_each_arpt(sgl_arpt_wx):\n",
    "    # Match non-uniform sampling of weather data to closest observation on common hourly timeline.\n",
    "    hourly_df = pd.DataFrame({'DATE': pd.date_range(start=wx_start, \n",
    "                            end=wx_end, freq='1H')})\n",
    "    # Setting tolerance is required due to missing data at certain airports.\n",
    "    sgl_arpt_wx = pd.merge_asof(hourly_df, sgl_arpt_wx, on='DATE', direction='nearest', tolerance=pd.Timedelta('6H')\n",
    "                               ).drop(columns='REPORT_TYPE').set_index('DATE').sort_index()\n",
    "    \n",
    "    # merge_asof doesn't produce unique timestamps. Need to select last().\n",
    "    sgl_arpt_wx = sgl_arpt_wx.groupby(sgl_arpt_wx.index).last()\n",
    "    \n",
    "    # Certain fields will be null due to merge:\n",
    "    sgl_arpt_wx = sgl_arpt_wx.fillna('')\n",
    "    \n",
    "    # TODO: Build seasonality model per numerical column. Then use it to fill missing values.\n",
    "    # May not be possible to fill in missing sporadic fields like skycover, present weather type, etc.\n",
    "\n",
    "    # Encode HourlySkyConditions and HourlyPresentWeatherType\n",
    "    sky_condition_pv = process_sky_condition(sgl_arpt_wx)\n",
    "    present_wx_type = process_present_wx(sgl_arpt_wx)\n",
    "\n",
    "    # Apply fixes for numeric dtypes:\n",
    "    weather_df_fix = fix_numeric_dtypes(sgl_arpt_wx)\n",
    "    # print(weather_df_fix.isna().sum())\n",
    "    # print()\n",
    "\n",
    "    # Chain merge decoded weather fields and fix NA's:\n",
    "    weather_df_fix_mg = weather_df_fix.merge(sky_condition_pv, left_index=True, right_index=True, how='left')\n",
    "    weather_df_fix_mg[sky_condition_pv.columns] = weather_df_fix_mg[sky_condition_pv.columns].fillna(0).astype('int16')\n",
    "\n",
    "    # Use OHE encoding:\n",
    "    # weather_df_fix_mg = weather_df_fix_mg.merge(present_wx_type_pv, left_index=True, right_index=True, how='left')\n",
    "    # weather_df_fix_mg[present_wx_type_pv.columns] = weather_df_fix_mg[present_wx_type_pv.columns].fillna(0).astype(bool)\n",
    "    # weather_df_fix_mg = weather_df_fix_mg.reset_index().rename(columns={'DATE': 'DT_LOCAL'})\n",
    "\n",
    "    # Use unique string combination:\n",
    "    weather_df_fix_mg = weather_df_fix_mg.merge(present_wx_type[['DATE', 'HourlyPresentWeatherTypeCombo']], \n",
    "                                                left_index=True, right_on='DATE', how='left')\n",
    "    weather_df_fix_mg['HourlyPresentWeatherTypeCombo'] = weather_df_fix_mg['HourlyPresentWeatherTypeCombo'].fillna('')\n",
    "    weather_df_fix_mg = weather_df_fix_mg.drop(columns=['STATION'])\n",
    "\n",
    "    # Reduce storage of data. Currently, rapids conda env doesn't have pyarrow support for uint and halffloats. \n",
    "    # f32_cols = list(weather_df_fix_mg.select_dtypes('float32').columns)\n",
    "    # weather_df_fix_mg[f32_cols] = weather_df_fix_mg[f32_cols].astype('float16')\n",
    "\n",
    "    weather_df_fix_mg = weather_df_fix_mg.reset_index(drop=True).rename(columns={'DATE': 'DT_LOCAL_HR'})\n",
    "    weather_df_fix_mg['MISSING_WX'] = weather_df_fix_mg['DATE_HR_orig'] != weather_df_fix_mg['DT_LOCAL_HR'] # Identify imputed hourly weather times\n",
    "    weather_df_fix_mg = weather_df_fix_mg.drop(columns='DATE_HR_orig')\n",
    "    \n",
    "    weather_df_fix_mg.loc[weather_df_fix_mg['MISSING_WX']==True, 'HourlyDryBulbTemperature'] = np.nan\n",
    "    \n",
    "    # Some airports do not have sufficient data (e.g., few measurement points; missing year). Avoid extrapolation with unknown time gap.\n",
    "    # Alternatively, use MISSING_WX field to limit data samples when weather considered.\n",
    "    try:\n",
    "        # Assume internal time gaps are not too large. Otherwise, interval needs to be sub-divided.\n",
    "        # Interpolate hourly values to fill in missing continous attributes:\n",
    "        valid_sample = weather_df_fix_mg[~weather_df_fix_mg['HourlyDryBulbTemperature'].isna()]\n",
    "\n",
    "        # Perform cubic fit between valid observations within interval:\n",
    "        cubic_interp = interp1d(valid_sample.index, valid_sample['HourlyDryBulbTemperature'], kind='cubic')\n",
    "        \n",
    "        # Generate interpolated samples within valid interpolation region:\n",
    "        x_interval = np.arange(valid_sample.index[0], valid_sample.index[-1]+1)\n",
    "        weather_df_fix_mg.loc[x_interval, 'HourlyDryBulbTemperature'] = cubic_interp(x_interval)\n",
    "    except:\n",
    "        pass\n",
    "    \n",
    "    # Fill remaining NA's with column average values. Best effort fill was made via cubic interp in valid regions.\n",
    "    na_mask = weather_df_fix_mg['HourlyDryBulbTemperature'].isna()\n",
    "    weather_df_fix_mg.loc[na_mask, 'HourlyDryBulbTemperature'] = weather_df_fix_mg['HourlyDryBulbTemperature'].mean()\n",
    "    weather_df_fix_mg['HourlyDryBulbTemperature'] = weather_df_fix_mg['HourlyDryBulbTemperature'].astype('float32')\n",
    "    \n",
    "    weather_df_fix_mg['YEAR'] = weather_df_fix_mg['DT_LOCAL_HR'].dt.year\n",
    "    weather_df_fix_mg['YEAR'] = weather_df_fix_mg['YEAR'].astype('int16')\n",
    "    return([weather_df_fix_mg])\n",
    "\n",
    "\n",
    "\n",
    "try:\n",
    "    # RECURSIVELY DELETE DIRECTORY and then add it\n",
    "    shutil.rmtree(wx_processed_data_dir)\n",
    "except:\n",
    "    pass\n",
    "\n",
    "os.mkdir(wx_processed_data_dir)\n",
    "\n",
    "\n",
    "tic = time() \n",
    "\n",
    "# Dask has issues with missing columns (from pivot table) when processing individual airports. Workaround is to wrap dataframe in list.\n",
    "    # ValueError: The columns in the computed data do not match the columns in the provided metadata\n",
    "    #   Extra:   ['HourlySkyCover_BKN', 'HourlySkyCover_FEW', 'HourlySkyCover_OVC', 'HourlySkyCover_SCT', 'HourlySkyCover_VV']\n",
    "    #   Missing: []\n",
    "\n",
    "# Apply per airport weather processor. Convert results into pandas, otherwise get the following error:\n",
    "    # NotImplementedError: Series getitem is only supported for other series objects with matching partition structure\n",
    "wx_per_arpt = wx_grp.apply(process_each_arpt).compute()\n",
    "\n",
    "# Combine pandas into dask.dataframe:\n",
    "wx_store = dd.concat([wx_per_arpt[ii][0] for ii in range(len(wx_per_arpt))]).reset_index(drop=True)\n",
    "wx_store = wx_store.rename(columns={'IATA_ST': 'ARPT_NAME'})\n",
    "\n",
    "# Repair cols that got lost during merge:\n",
    "all_skycover_cols = [cc for cc in wx_store.columns if cc.startswith('HourlySkyCover_')]\n",
    "wx_store[all_skycover_cols] = wx_store[all_skycover_cols].fillna(0).astype('int16')\n",
    "\n",
    "# Fix remaining uniqueness issues over all airports. Maybe due to temporal merge_asof + interpolation?\n",
    "wx_store = wx_store.groupby(['ARPT_NAME', 'DT_LOCAL_HR']).last().reset_index()\n",
    "wx_store = wx_store[wx_store['ARPT_NAME'] != ''] # Remove blank airport name. Spark cannot read it as partition key.\n",
    "\n",
    "# Repartition and write to disk:\n",
    "wx_store.repartition(1).to_parquet(wx_processed_data_dir, engine='pyarrow', overwrite=True, \n",
    "                                   partition_on=['ARPT_NAME'], flavor='spark', write_metadata_file=False)\n",
    "\n",
    "t_wx_process = time() - tic\n",
    "print('Weather processing time:', np.round(t_wx_process,2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f4edaaa8-d3b1-4e93-82f3-cb3419f3326a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of ORIGINAL wx observations: 905126\n",
      "Total number of wx observations after processing: 907188\n",
      "Relative fraction of ORIGINAL wx observations: 0.9977270422448269\n",
      "CPU times: user 332 ms, sys: 31 ms, total: 363 ms\n",
      "Wall time: 973 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# Read processed weather data:\n",
    "wx_read_proc = dd.read_parquet(wx_processed_data_dir)\n",
    "\n",
    "num_records_orig = np.sum(~wx_read_proc['MISSING_WX']).compute()\n",
    "print('Number of ORIGINAL wx observations:', num_records_orig)\n",
    "print('Total number of wx observations after processing:', len(wx_read_proc))\n",
    "print('Relative fraction of ORIGINAL wx observations:', num_records_orig/len(wx_read_proc))\n",
    "\n",
    "# Weather data is incomplete, even at major airports included in BTS data set. May be best to use weather as-is \n",
    "# and determine time periods where most weather data available across NAS, simultaneously to investigate weather impacts.\n",
    "# For demo purposes, imputed weather data can be used to ensure complete temporal coverage. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c42f808-dedc-4c65-b08d-ac380328ebfb",
   "metadata": {},
   "source": [
    "# Inspect Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4afdbbd1-a994-47d2-9283-8122750ebef1",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\n",
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\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>HourlyDryBulbTemperature</th>\n",
       "      <th>HourlyPrecipitation</th>\n",
       "      <th>HourlyPressureChange</th>\n",
       "      <th>HourlyVisibility</th>\n",
       "      <th>HourlyWindDirection</th>\n",
       "      <th>HourlyWindGustSpeed</th>\n",
       "      <th>HourlyWindSpeed</th>\n",
       "      <th>HourlySkyCover_BKN</th>\n",
       "      <th>HourlySkyCover_FEW</th>\n",
       "      <th>HourlySkyCover_OVC</th>\n",
       "      <th>HourlySkyCover_SCT</th>\n",
       "      <th>HourlySkyCover_VV</th>\n",
       "      <th>YEAR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>907188.000000</td>\n",
       "      <td>907188.000000</td>\n",
       "      <td>907188.000000</td>\n",
       "      <td>907188.000000</td>\n",
       "      <td>907188.000000</td>\n",
       "      <td>907188.000000</td>\n",
       "      <td>907188.000000</td>\n",
       "      <td>907188.000000</td>\n",
       "      <td>907188.000000</td>\n",
       "      <td>907188.000000</td>\n",
       "      <td>907188.000000</td>\n",
       "      <td>907188.000000</td>\n",
       "      <td>907188.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>56.727595</td>\n",
       "      <td>0.002582</td>\n",
       "      <td>0.000312</td>\n",
       "      <td>9.306474</td>\n",
       "      <td>161.748124</td>\n",
       "      <td>3.072452</td>\n",
       "      <td>8.270874</td>\n",
       "      <td>6.672493</td>\n",
       "      <td>12.665983</td>\n",
       "      <td>9.695988</td>\n",
       "      <td>13.498761</td>\n",
       "      <td>0.021935</td>\n",
       "      <td>2021.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>19.527906</td>\n",
       "      <td>0.026660</td>\n",
       "      <td>0.022583</td>\n",
       "      <td>2.014818</td>\n",
       "      <td>116.942712</td>\n",
       "      <td>8.619223</td>\n",
       "      <td>6.582865</td>\n",
       "      <td>33.505852</td>\n",
       "      <td>46.628157</td>\n",
       "      <td>30.111518</td>\n",
       "      <td>45.372648</td>\n",
       "      <td>0.426463</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-29.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-0.320000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2021.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>50.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>110.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2021.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>66.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>210.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2021.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>83.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>330.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>21.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>22.000000</td>\n",
       "      <td>42.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2021.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>155.508087</td>\n",
       "      <td>2.630000</td>\n",
       "      <td>0.370000</td>\n",
       "      <td>53.000000</td>\n",
       "      <td>360.000000</td>\n",
       "      <td>81.000000</td>\n",
       "      <td>2237.000000</td>\n",
       "      <td>350.000000</td>\n",
       "      <td>350.000000</td>\n",
       "      <td>350.000000</td>\n",
       "      <td>350.000000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>2021.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       HourlyDryBulbTemperature  HourlyPrecipitation  HourlyPressureChange  \\\n",
       "count             907188.000000        907188.000000         907188.000000   \n",
       "mean                  56.727595             0.002582              0.000312   \n",
       "std                   19.527906             0.026660              0.022583   \n",
       "min                  -29.000000             0.000000             -0.320000   \n",
       "25%                   50.000000             0.000000              0.000000   \n",
       "50%                   66.000000             0.000000              0.000000   \n",
       "75%                   83.000000             0.000000              0.000000   \n",
       "max                  155.508087             2.630000              0.370000   \n",
       "\n",
       "       HourlyVisibility  HourlyWindDirection  HourlyWindGustSpeed  \\\n",
       "count     907188.000000        907188.000000        907188.000000   \n",
       "mean           9.306474           161.748124             3.072452   \n",
       "std            2.014818           116.942712             8.619223   \n",
       "min            0.000000             0.000000             0.000000   \n",
       "25%           10.000000           110.000000             0.000000   \n",
       "50%           10.000000           210.000000             0.000000   \n",
       "75%           10.000000           330.000000            23.000000   \n",
       "max           53.000000           360.000000            81.000000   \n",
       "\n",
       "       HourlyWindSpeed  HourlySkyCover_BKN  HourlySkyCover_FEW  \\\n",
       "count    907188.000000       907188.000000       907188.000000   \n",
       "mean          8.270874            6.672493           12.665983   \n",
       "std           6.582865           33.505852           46.628157   \n",
       "min           0.000000            0.000000            0.000000   \n",
       "25%           6.000000            0.000000            0.000000   \n",
       "50%           9.000000            0.000000            0.000000   \n",
       "75%          21.000000            0.000000           30.000000   \n",
       "max        2237.000000          350.000000          350.000000   \n",
       "\n",
       "       HourlySkyCover_OVC  HourlySkyCover_SCT  HourlySkyCover_VV      YEAR  \n",
       "count       907188.000000       907188.000000      907188.000000  907188.0  \n",
       "mean             9.695988           13.498761           0.021935    2021.0  \n",
       "std             30.111518           45.372648           0.426463       0.0  \n",
       "min              0.000000            0.000000           0.000000    2021.0  \n",
       "25%              0.000000            0.000000           0.000000    2021.0  \n",
       "50%              0.000000            0.000000           0.000000    2021.0  \n",
       "75%             22.000000           42.000000           0.000000    2021.0  \n",
       "max            350.000000          350.000000          20.000000    2021.0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wx_read_proc.describe().compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "80fe5b67-8cb5-4d43-9f4b-c8c33fdcb30f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DT_LOCAL_HR                      datetime64[ns]\n",
       "HourlyDryBulbTemperature                float32\n",
       "HourlyPrecipitation                     float32\n",
       "HourlyPressureChange                    float32\n",
       "HourlyVisibility                        float32\n",
       "HourlyWindDirection                       int16\n",
       "HourlyWindGustSpeed                     float32\n",
       "HourlyWindSpeed                           int16\n",
       "HourlySkyCover_BKN                        int16\n",
       "HourlySkyCover_FEW                        int16\n",
       "HourlySkyCover_OVC                        int16\n",
       "HourlySkyCover_SCT                        int16\n",
       "HourlySkyCover_VV                         int16\n",
       "HourlyPresentWeatherTypeCombo            object\n",
       "MISSING_WX                                 bool\n",
       "YEAR                                      int16\n",
       "ARPT_NAME                              category\n",
       "dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wx_read_proc.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ff160b0a-6a03-40d7-bfa5-3f4472ded547",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "distributed.utils_perf - WARNING - full garbage collections took 12% CPU time recently (threshold: 10%)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "DT_LOCAL_HR                      0\n",
       "HourlyDryBulbTemperature         0\n",
       "HourlyPrecipitation              0\n",
       "HourlyPressureChange             0\n",
       "HourlyVisibility                 0\n",
       "HourlyWindDirection              0\n",
       "HourlyWindGustSpeed              0\n",
       "HourlyWindSpeed                  0\n",
       "HourlySkyCover_BKN               0\n",
       "HourlySkyCover_FEW               0\n",
       "HourlySkyCover_OVC               0\n",
       "HourlySkyCover_SCT               0\n",
       "HourlySkyCover_VV                0\n",
       "HourlyPresentWeatherTypeCombo    0\n",
       "MISSING_WX                       0\n",
       "YEAR                             0\n",
       "ARPT_NAME                        0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wx_read_proc.isna().sum().compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "61792c60-b367-4fc3-82f7-e2f87022b0f6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXAAAAD4CAYAAAD1jb0+AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMSwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/YYfK9AAAACXBIWXMAAAsTAAALEwEAmpwYAABOh0lEQVR4nO2deZgdVZn/v2/Vvb0nnX0PnZUsJCQhW0cdVEAQRHAXUUHEQUccxQWFcYuOOuI4/kBEFAUGB0QUZZHRYQmLKOkknYSErCSQhOzpbJ2kO9333qrz+6OWW8up5d6+S9/u9/M8eXLr1KlTp0/3feut97wLCSHAMAzDVB5KuSfAMAzD5AcLcIZhmAqFBTjDMEyFwgKcYRimQmEBzjAMU6EkSnmzYcOGiQkTJpTylgzDMBXP6tWrDwshhnvbSyrAJ0yYgNbW1lLekmEYpuIhol2ydjahMAzDVCgswBmGYSoUFuAMwzAVCgtwhmGYCoUFOMMwTIXCApxhGKZCYQHOMAxTobAAZximLKx85Hbs37U1tM+W1mV4dc0LWPWn26BrWolmVjmUNJCHYRgGAE62H8Widd/AK1v+iNE3Px/Yb/oT77M/rwKw8H1fKP7kKgjWwBmGKSm7Nq/GpqfuAQBM7Npit3d3daL18V9A6Lr0Oq3jSEnmF4dX/vYINq94Eq1P3IXTHSfLNg/WwBmGKSnVD30Ii3EYAKAT2e1r//sraD7wANY1DMKc867wXdebqofNfvYT9ucVO/6Bxf96X1nmwQKcYZjYvLzsd5g49+1oHDoy7zFGmcIbAASyAjzReRAA0LXxr9g7YTYObGnBfOeFQq6ZWxw9tBe7N76EZO0ADBo1EWMmTMt7jrmQ7CrfmwELcIZhYtF+5CDmvvhpbFoxG43/9veCjFkl0r62xUceBX7zKMb6zoRr4EfuuhxzMtuyDUvbezq9WOhKVUnuI4Nt4AzDhLL2qfvR1XkK6e4uAMDM1CsFG/s4NcbvHKKBr3/uYUx1Cu8ikUmnsPov97raGjt3Ydfm1UW/twwW4AzDBLKldRnmvXQ91t19PdLproKPryDcLOIiwAZ+rG0/zn7h2gLNKJxV//N1zF95g6ttqrYdTQ+dV5L7e2EBzjBMIN0nDPtubcceCL3wm4gjcQQv/+id8ToHCHBdL51/ePLYayW7VxxYgDMMEwOCrmWKMvLczuXY+/rmGD3L74Wi6KlyT8EFC3CGKTPH2vZjy8qnyz0NH7s2r8bpg9vtYyF6ruluePExaXvq9Inoi9Od2PD3x33NooQRmoLUkt0rDuyFwjBlpv3OCzFdfwNYVBqvibg0PXQemhzHeg9NKPt3bcWsZVdJz5GiAg6XQhlL9t8P7L8fh6e8gmGjzrDbNb04bwYyhNK7RCZr4AxTZibobwAAtIxfEG148TGkugu/eRiGlslg/fN/9LWLHgrKzvZgf2nZzx5Ed2eH67hYph0vW1qXobFjZ0nuFRcW4AzTS0inu13Hr655HrOWXYU199xQ0nmsevC7OPv5T/raRQ83C7VMsP1Yy8HDxTuPUgnw6U+8D1O17dEdSwgLcIbpJXg17c4j+wAAI9uWY/e2dSWbBx2XFkB3mVBe+dsjOY+reR5QTjLdp2OPk065+xZagG/4++M5vRGUExbgDNNLyKTcAlyYgSsT9Z0Y/8C5pZsIycSCcJlQZj/7CRw5uCenYaM0cEHhNnALr7AvZJrZV/72GGY983GsvP8bBRuzmLAAZ5heQibtFnAkFaQlIOC+3iyBWsYfBh+Glgo2k4Rp514yHg0813mE0X3ceOupP9CK1zesKNi4xYIFOMP0EtLezUqlPF9PEaSBe0LZcw2g0bo7As/pqdOgmNkGM551Ovbkf+Q0jzBITQIAzu5ahUkPX1iwcYsFC3CG6SVkPFpob9PAvbbmw7s24fCBN2IPq3V3Bp7T092IG6jTsWs1TrYftY+Hdsg3Fl9d80LkWNvX/QP7d23Fhhcfw66tL0OL448eQDkqBvUup0aG6cd4zQjlE+D+YBUSAvCYUGY983HgGcTO+qenwgR4F9SYgULNr92Grbf/H6Z9YyWA4HwqZz5+GXYP+BvGT50TONaURy4BAIw2j5sCe0aj6xoUtbSBPrH+Qojoi0S0kYg2ENGDRFRDREOI6Gki2mb+P7jYk2WYYvHqmhcCK8GUCyqTCUVGldaJ9l0984QRIQJcZLpBOUR6Tstka2kqIVkKTxzKbaO1J+hl+PuJ/AshorEAPg9ggRBiFgAVwBUAbgKwTAgxFcAy85hhKo71z/8RZz5+GVb+4UdlnYev4kxMr4yCz0OigU/LbMHiTd/r2cAhvt4i0wWKKNgQRFhGw+7lv8hrzHwoZVIti7iP+ASAWiJKAKgDsA/A5QCsOkL3AXhPwWfHMEXmWNt+dG5ZZhy0hVdILzoeAVa2N4IiPTjCcqkobVtQlc6vtmSYBj6kc2deY3qJ87sox+8rUoALIfYC+DGANwDsB9AuhHgKwEghxH6zz34AI2TXE9F1RNRKRK1tbW2FmznDFICan52N5gMPmEfl0XiD8Hp9lAylSHbckJ9n0fG/4KxUfiaavQNmB547Uj8lrzG9aDGChXqlBm7ati8HMBHAGAD1RPSxuDcQQtwlhFgghFgwfPjw/GfKMEWglrK+13L3udLhM6EUIPtfr6JIDyRtwlsDz6WHz7I/d53uwBuvvpzXPTIxfM17pQ0cwAUAdggh2oQQaQB/AvAmAAeJaDQAmP8fKt40GaYElMnmHITQyqSBFyu7X5EEeGiOFsfvdOMdV+KM374Vnadyz/oYJ1y/twrwNwA0E1EdERGA8wFsBvA4gKvNPlcDkCf6ZZhKoQwauDvaz62Bl8uEQkUwBbTt2wnl9NHojvkQtk6Ot5qzTv4DQDZgKhebdSZObpQymFAi/cCFECuI6GEAawBkAKwFcBeABgC/J6JrYQj5DxZzogxTfEqrgR9r2++K9vOaULzpW4Wul8a1UC9caLrF8LvmoGgG1BAB7nwI1pDxc6XNlAW6riOutV/0Ug08ViCPEOLbAL7tae6GoY0zTJ9AJKpLer/u06dCz3trUAohSvKIoRIWSOgJ+3dtxeimaaEmFGd4vi4ICgk74lXTMrEFeMVuYjJMv0EtrQD32dwjNjHjCJFCQEXQwIvB6HsXoburM0IDz66hZoo7K+I1lzS0egwTSjlMXizAmX7Lvp0ev+8SRz5G5c7wFS4olYZXwPsIXY9ZsDg/jh/eD3SG2NYdD0Xr/eXwrk1Ip7qx85WXYt9HVrZtTfNtWDXo4uz4ug6h69i3Y0vscXsK50Jh+iWvvdKCyX+8yN1Y4oK13k00vxuhJ/tfiZIlKQXUwFc98lMsesVrfS0cI399DkaGdXCsoaWBz3nhU2hdcz4WnFwW+z6yAg81jSNxorrRPtZ1DSse/Hc0b/sJdnzoGUycuTD2+PnCGjjT7zh6aC+ObF/pay918qioEmW+/NtFNKG0HzmIXVvWoP1oG5LpwhVXVna9WLCx8sEZnq87xF1c4b1j0ypomYy0HqiiqK6HvtB11O4xPF0ObXyhJFV9WANn+h1dd74di8RB/4kSm1D8oeXeTUuvCaV4NtbG28+EpUsG5+7LHTUTv1RaMXDapbU8HtATf38BWqZ9DWMXvMt3jtSEq0q9rmm2xr94479j+fHdWPLPt+Ux6/iwBs70O8bIhDdQcj9wr0nE55fsNbGUaBOzoJQ7OCpAA88FpW2TdBNTSSQBswAEYGyKkuMhPOKAkY/8wO7tOZefiwtr4AxjUWoTSpTXgiidCaVYiHLriE43wh7MRZeYULR0N+DUwPWMKyWulWRr1N3zjYaYedNzgTVwhrGIWdKrYLfz2MCF14Ti80LpXfnKY1FuDRw918AB+Samnkm7BLjQdZcGrqL4D1wW4Ey/Iix8evard5RwJoCuRzwwfOllKy+5VbkThFEMDXzFsPdFjiNb+wHDxqBufDYToq5rrk3ThGABzjAFxeeq56CeggsOFAOfZ4MvlL5MfuARxBF4WaI18K2JaWgZ+ZH8J+SgHfXYpYzLNpgCtf3IwUABPueTt/valo/9RPZA13DywGv2oSYIuz78LMZPnYM5512B5WdcZ3bTkNSzf0MJUfyAKBbgTL+itwhBIEYyJZ8feO+wgatNzTn0jjZLHR35JlRNenP+E3KwNzkRbQ3THbfX0bZvJxpvPxOjcFh6TXVNnet4U3IWaicsto8XHf8Lzllxg32skkDTjPn2cc24OeatMjgz86rdnmATCsMUlt5kR/ZtYkYE8pw8sr/HVV86T7UjYyZzSnV34cTxI7kPUsC9gtfe/yQWXfNjTF74TrutZeqX7M+b3vkQ1iy+Nf6ARK6ScMnTh3Bg25rwSxzuoy3DP4iRn3oIqVPx14VUww7u9SoaIDpwst2IEt2tFyeVFwtwpl/RW7RYQPI2EJEL5czHL0fL/T2Laqz78RlYd5uROHTjbe9F160Lch8kp43J8L6TZzdDTSQwcNBQAIb2O3DSIvv8zOZ3YvjU+HMUcNf0nH/yOcx+7prY1yfGL8DQkeNQP3Jy7GusADCvyStJGgb8v4kAgBf1Wb7rCgELcKZf0X40wAe8HESFxku07aYdD+V9O6uQwfxTzwMA5nW+hBE4iq7O4KyIK876ltseDMQW4F2dp3xvEYcwBKvm/YevLykKdl3xHMZ/7s+RKQXCoZweMK9/4CnXsZI0EppNnhdc5cd3R7MEndA1dAp5QrSjGBh7vFxgAc70G1b96TaM/PU55Z6Gje71MolR0KFKdOd1r5WP3I66H58hPVfzo7GB1w2btgQNZ7qFWe3g4P4WWiaDmh+NtR8WFh3KQCy8/LPSa5qmn4MBjUNQP9hdXre2flDk/SwEKS4NPIpJsxa7jpVkDQAgmQzOTPmaOsl1TKYroa5rOKoMll5zQAyJPadcYAHOFIXurs5yT8FH9au9q2hUkD3bXjtJTcx8M4Int/0lp/6rB5yHV877b0ya1YxZ//QerGnICvEJZ4dvOApdx6mTx6Xn9Bja8YQZbpPJsDFNaJn21ehJw1yfmK6L3Tft97WppuBWE/IYx5Vzvodhn3va1UaqaULRtMDApS5UxZpTrrAAZwrO2ifvQ/UPR2PHxhXRnUuI0suKBHtznQghsGXFU6j+4WhsePEx6WZhT4JRckFL1mP2ue8FKQpIUZBuygpwIoIuggVxy91fQuNtchtyvpGZgyYviu4EwDChRGvgLaM+6vM+AbIaeBBDJp2DxsHD3I3m76nj0OvQAx4eQaaVnsICnCkoWiaDzOa/AgDatizv8XhC122viZ7S6wS4t2ixEDi28RkAwMmtz0ttv1qJUt56q/KQQ3NWFBVpuCMQnUzf+8fI8Q9cuxp7r2qJP6GYicYEEYQSvUYLrr1V2q5WhQtaRfVr5o0jm7L3D3hDSiEpbe8pLMCZgqJ+bygWHjcEuNB6Hsiw6k+3IvH94Ti0d0ePx1J6W6kwyQNlwa5fAwAo0y0V4Plq4BTDH9vV35sT3Gn6IHIJcK9r5mCciJzHqPFTMHbSjNjzUZS4aZuiTSinRC0SSblJIxGhgSuq/+FQXWdsUC5a9w1M0HcHzKo4aRpYgDNFoxACvP7VPwEADu3c0OOx4pBO5bdJmA+ygg5JMoQ6pU5KBXjeyaFy9N1WNO9bj0cDp6xAzSXJlpJn2bG4xZx1JRlqQtlLI3HkI8H7AcmqKAHuf5BU1zZEzktBceIPWIAzxcMnBHLH8ijIJZVq6ttDsPV7i/0nYmygdZ3uiH2fniJCcmXoDaPkGnieuUVy1QBTde46N04TChFBc5QDziW6lZCfGau+MV4gzOlhs0O9UMaKg2ia7vdEOgjDDz1RHSHAJW8CNXX1kfM6KtiNkKkwRCEEeB5eF1WkYVomv7qEUZXiC4m36jyEwIbquQCAugnzpX7get4ZoOML8NaBF2DGFd93X+2xgWccNt1cokPjPkh2ffhZ7L8mWzVpzMTp2Hjhg2hHsLBc+6Y7MPfD38orA6K1t0ARewxKwm/LrorQ2r/V+D2sofjmolxgAc4UjwKYUOxX9wKEb8cZobuzhBq4RHPVyRAQQsCVW9oi303MOaf9JeSCSMx+PxqHeitNujXwPQPOto9z0cDjmlCaZszH6KZprraz3nQJuhG8yTjvwo+htn4AKJ27C+uBOuNeichNTP/6h5l31tT/EwbOvDA0iVpPYAHOFAyfJpbpuT2ZLNthifJKp7pKqIF7sw9Ch/2Y0fUAE0rxvVBkAUTOeqFEhMlX3YEtCUOrzCW/jJqnCSUXRE1jdCcP0z5zPzZc8D8YMXai3bb3qhYcgNtlUJXYwINYNe8/cMbH7wRRLu8/ucECnCkY3i8yFcCEYo8VYPttefAHwNJGnDpxLHKMOBuA7ftfi+xTMCTpZK180oYQlfiBU/GLaFU1SKIGnTZwRcHg4aNxfJJRJzIXAZ7vJqbFgZqJkX2oVh4NGUb9gEGY9ZbLXG1jJ83A/rozXW2yTcwg5l3yKQwbNR6E4tUKYQHOFAzvqzRpPdPAtUzGFmh6Rm6Ombz1LgDAqfYY2ePKXh3GjaXprqtZGNChcG6EXk1SxqrGd+LlN9+Js950ie8cydbOeqjmYkLpoQY+4TN/wIoZN4d3kriLHsOAvO4nPA/MXDRw21WxiH93LMCZguEV4IsP/6lH4x38/gyclVoPADj7hWux/L9v8vWpEUYCfSVG8EYcRAHMPrHvZQrorskXWw2wtW6hgSSCMZc8H05OJKJzceiJasx9x5UBZ/1CyHorykkD76E73cBBQzF+0XtC+yQPrvO1ZczN39Mit5B24fE6kW1ilhMW4EzB6Gmuau9YY8QhV9uSnXf6+lliJU60Zpy3WC1TOLNPJOZ6kePhY3lpCKcwdyDy1Oa85iNv7moAgBIinEI08Jw2MQvgD52McPU768SL9merOo8OBVsu/gPaP5VbdLCueDXw6Afo+pqFWP/Wu+1ja+WKsZHJApwpGIWsdpMJMJn47mkKlkIJXhHzvvmyfd3fgaWN2L7u71kvFCsdqXDUcBRCakKReabEwVubMpXyl48TaogHRogAl216BlEIAV4VETjzRtKwk+9SxmH/mIsAGN470xdfiFHjp+R0r3xMKB2Dp+Hst3/APqbCOVL5YAHOFAzNo9Xl+rrqJG42Q8tP3LKRh78FRP+5iyJr4G2tj5j/P2oL6KwGnt24FMLwQukU1Wg58yv29fluAnr96bu7Tvv7JPIU4CU0oQBA4+BhaJ3/o8DzE258EWvr34JRX11l50XJN4ujUN1vJUGRmjs/vAzbVTOBl+dhSXneOw4swJmC0PLgD+zqIxYH1VF5j9cdMyLS2tTTMmlsWv5XnPjuuMC+scwPe1bFuq+MLd9fgnW3vCOil/1CbWuu5LLfZzVwEjpOUT3mfyCbSrUptT2vuXk18LRsfdXgB65MCFn+z7m8eSUKlFBswbs/HXiuqroG8278XyPbYE/3RjxmpaA0sxNmLMDhJsMrhwLiH4rhiFJ8nySmXzB7y22+fa6eeByku/0aooysAE/h1KoH0IieBeLoVfl5KwDA9PQmIMoCYwkwUrORllYgiBBO8Q4IHQKEpCO4pJ0Gwp8ENRq/Bu5/w6EcNXDKQwMvVlKnQMLs+jHIadM4WQvA732VNaEIRJWYy5VYGjgRDSKih4loCxFtJqIlRDSEiJ4mom3m/7k7XzJ9BtkXsyc+v+nu3E0o+XpoWHSIGgACu757Flbe9lG7/eVnHgSWNuL44QM9Gr/z2yOwZM89AIwqLpYNnJx2VuHWwL2bj8nIJ4SfzLcH2948FgN+c56/Y5gAl4gKkYcXilqkpE5BUE818Bw2ja1c4t74h+xDufDENaHcBuD/hBDTAcwBsBnATQCWCSGmAlhmHjP9FJltsycauJ6Jl7zK0sB1LR27EksQAgQIgSZ9DxYde8JuT640vF/2bM6/QIXQddRRVjMTimrbwBWroosQ2QehrgPQfcmrEsgtJW4mnUKC/L+bgchNA5eawPPQwEsRienCFOD5673xr7TWTwnUwPOeRCCRf/FENBDAuQDuNiYhUkKI4wAuB3Cf2e0+AO8p/PSYSkEpsAburRcZhKWBa5lUpAYetZFleLT4fw7NdCU73bYz8NpUt9+rw4nXq4YcAtyZ/jT7JmNo4Lpnzgmh4fC+XcDSRmz4x59D72nMK54pCsjDhGLbwI2HyoE3tgFLw8PYVSqtCYVsN8A875uDUmAVRFZ0jwZe5kCeSQDaANxLRGuJ6NdEVA9gpBBiPwCY/4+QXUxE1xFRKxG1trW1FWziTO9CZkLpibYVV6uzTAwik46xYZX9IrWMvAKvvf9JrJz1bbx62ePYcumfAJAd+ekkkzDd1kKKCkRFgvpszk4TijVvh4omhACE8JlQEsjgjfXPAQDSL/0i9J4A0H06fmInJaSQb1gkpvVz7Gp5NPa9SgWZZo2ehvDHwTaheIthmHiLVheCOJuYCQDnAPhXIcQKIroNOZhLhBB3AbgLABYsWFDiHQymVEht4D2wd8oy9cnQzUxBupaJrIXo9EKpm3EhJs9uxuTZzdkOT3RgcdvDvus02z86+M9XTQR7cKy89SNYdNxdRIDUhJ3jnEzfYmPj0rKB6yDoPs+ZBDR7beZ1/D3wnhapmHsJQIQLpWRtrQeP9bClHMLMS4VSZWz55mp6sshlXyVpVuZJ1cjTFpTLD3wPgD1CCMsA+DAMgX6QiEYDgPn/oYDrmX6AzDzRIw08psZk28AzqVi1EC3iVngxe0fPKeScV3jbY1o2cFckppXMyr2Jufni32N9zUIkSId2+mTsmacl/t5BZE4cjN3XmKwVgGTMuW5UbkEypUCtNvKH57P5CwBk+oFvqJ6Ll//pl6F9py26CC2Tv4BJH/h39xhFTMET+VcshDgAYDcRWcl5zwewCcDjAK42264G8FhRZshUBJrkT8mpgbf++ZfY8d05sULeVz32c0x55F3Sc6+84M6vYptQtHSoiQNwRDkiOLthGGFRmk5PDGmYuncsXfPbwJ2bmA43QgCYsfgidIycb13sGmvXd89CywPfld4n3R3frTLUBi7rrxhzm/DQ+QAALQd7eyHYR96c5X7UKsO1LxlS/SgMYZpFTg6ZhbnnXxHaN1lVjeaPfxfDx0xwtRczkCfuO8+/AniAiKoAvA7gGhjC//dEdC2ANwB8sDhTZCoBmQbuDNo4c/VSDEQn2k+2o3FIeHmsmWu+E7j5r/7jJ8Bb35e9r2VC0TPRAtxp0slDA0eAbRNwB7NkMmlUOXJm7FLGo8lb7FbP2Fn8SJXMRdfNTUzHOTPQpnb4BADAAQzHSF1Hk74HTdv+C8C3fMOkIzZXLVrOvBHnvP8rgeflKWazP6OWyUBPl1aAK5/8C1rXPoOqgcNRN3gUZPp/osbQwPM1oZC55lSAgtjFMKHEEuBCiJcBLJCcOr+gs2F6LZtXPIkz/nIVMp9fL6nWIhfgTg28VnQDBGTS0dn+woI9ZqZewa7Nq9E0Y7553+wmJkUEbbg18Bz8g8134LBMhU7zipZJA46ESyeTQ4FuvwAXtgnF+hrq9s8+eNP/4FT1CFcEpWVj1sw8Jp1KPY4d3g+naD22dLxRFX5pOwAgk4onVJuv/Ebo+cGjJ/nanBub3V0d9rzCOIxBMRLbxmPU+CmRuU2SpgCvovzMeUq9UStT5JFj3MJ2IyzCJiaH0jOxSD/3n6inLuxc/zfpeW+e6j00yhW0YQkmrQDJovY/f5f92X5waHG8UBzzyUkDN+8VUljZaTbxugzKCkkIXbMfKIormZUxx6nadtMG7ngwmg8ozdR0FejoaD/sGncwTriOtYhNzNaBF6B1wX+G9gGAUWdM9bVZ5gkAyGQy0B0Pi/U187G+xq3ztUz9Mo5edh9KSVVNdMHhMGZf+Aksb/oMZl725bzHyGYj7NFUpPS+bWOmInH+ba5e9BOk923EmN332G2Wn3gcG3hUuPWkg0857msF8mQizSJODVyJaQNfedtHsej4X42DkApDThOKt/iENw+J2Sn7UwbM2/BCcZ4zr9j0OABgjLYPVQ+cCwA4KWpdJQt0TYOiqpFacWb0OWi+9LrQPoC8FmSiOhvUv+l/7wA5PHE6B02HMnI6sK7Vbjvr0s9hQGN0XvJCkuyhAK+tH4Al19zSozHKuonJMAbhQtXp7kakQCgqFBK2ZqqQpYFHC3BZUJCTEThqf7YjFfW0tIq7k3xs4M6IzKAkRYDb7dGvgUu+wY5NTMXKeOdR0bwauDhlxFHMP/U8AKCKgt8INPNtQYTY7Y15xLftrpzz71j7pjuylzqubd72X8ZGsjVXUu2fax+NQMuUG0ouvAGgurZnAryQcDIrpuwE76g7X/UTtjlD0zIu7U0vgAbuxBJwQktDRGS6cwZzxPFCWffDCzDHeS+JMNz4g3NxctB0NB96yG7TvaYW2b2EBkt/cppznEWLqzOn3OYXNX5iJk3LoPVXX8PcPQ/4NoQPYBhGwTC95OJ5sui9n3cdV9d5oi6dDwMiWyM/UjUOzR/7Tuz7FJKauvDc4aWAbDfU8gTyMIxN0EaMM+SbiGwPBS2TdmXTi1OoISoAaGtiGiyfVtvEoGd87nWWGcGel8sG7jcJrK17E+Z1vmQfz+lyp5aVaeBnpdYBh9wlvLwbtUEmFCgJaMIhXYVAt1oPy2FiWmYLNidnZu+fQ9pWLZPGkr33Sr15jlx4O3ZsfQEAMP89N8Qe08uUOW9xOQ+79ghIyXpwlDiBlZOaiOIPpSC7iVl4WIAzMQk35Dk1RVJU22PCK7D1GCYUWfIlJ13JrObn1MC9JhSv9u/U7GWbmPqsDwErX/K129c4NPAVD90Cde9KqWuWVwOXmVBI1yBIgQ7F5c3hfftwaeCJ8FJiTrzFNZw0DBmFs3po1wUka+jQwIkUu34kFWP3LiZW/u5jGIhyp0vlijxMr8X5t0mKQwP3CJJClD5z5ivRoVoD+zRwzSNIKcKEElmw1iHAF2/+ARaceEbaze9pI7OBZwDdY+MWui8Xi3NvYe6H/i18fq7hg9908vHACWKHMiF74Ji7IAWqmVsl3zJwhaLlzBtx8JJ7ojsWiXIns2IYRG5iOoUUqXZQzb6fX4Yd381aksMES/yZyDTaTLZYgnUvrwB3aeB+E0pYMicAWHzkschse4BfgMtMKPXtr4KEZkawOjRwX0h+9towe+4AOo3XHescZqoqpEA5OOGy7IHTBq6otpeKmmcUZKFovvIbmL4oqlJSCWA3QqbcBG1iOoWq4jChzEhvdPWLY0KJoqthfPZeZr4VofvzgXu1f6dwVBRJ4FFISbFcyHhDyiUCM1U1GNmKO4ZpRE93QRVe4R9f2E7Sd2avC7GX5xTEFIErP4xnE1M1w9C9P1N/w1VlqcCwBs64WPnI7Vh1a3jOBxlOAU6KGpgZsBAauFPTtrRqktjAvTlJojRwNRlPgEflOlGecHtryAJ5DLOCgA4FVaZmrXd3ICHcDzg9T2EbFjBFkodXvriiW50PDVKgmiYptcwmlHLDVemZkrFo3Tew0ApcyQG3AE9ACUgtqof4Ukexcs73jPGdm2WWBig0n63Va293eaFIzBpNs5bEmkdUkYTJ2g53g9SNUAd0DToRasw0pFp3B6p0b7i++9qWqV+KNUctpKJRYTVwx5o7P5Pq2EDu31mkixjHwwKcyQ3Za2DrT97vCq4hVQ0Maxch0YxRzHvXddiP4W4BbgplkrkRZsJs4BK79IBBsebRHTNBlIzXP/AUXlcmgIRmJ6uqrTdiKJu33oLxYp+rv9eEMmxWvPRDid/IszkChdXA4dLAnV4oZL/lpJXcshz2VcpZE5Pp9xh/fl7baqq7y+eNoSiqo5SVm56YUBKJJDRSXZq2FZxDetonwDMeDdwVyBPwgFm75KdYV7sodB7pHNOmOudLigKdVPPNwai4U10TXGfeV5GnKp4r4UgEVwjKJ5VuIIE2cAVN085By7hrMeDj9xfufhWItWmsF8GGwgKcyQlvQqeTxw/7+pCiApLcGcb1+QtwUhRolIDicOezzSK6zISSnWvLLz7r0m6VAFe6eRddjYHv/oGvfVNylv05EyPrngvn5ikpEKQYcxUadFCoW5/XgyWRjO8LHkQh3Qidb1qu9ScFpCho/tRPMLppmuTC/kNCNQT48tfCy+7lAwtwJjc8QlKmjSpqwpEi1U2+NvCWScbGoI6EWwM3Bbgi0cA1R/rX5gMPuM6F2YETns3M1QPOg+74edI5CnCXYFNUI3gHuj/boPxq99yqey7AlRwqF0Ux54OGb/oBDLfzmwOQ2/37KRfPGo2vvnMaZo+NdkHNFV5lRsrLt1yEE8f9GoPXt1rmkUFK1g/cS1hVmzCarzLKVGUogXkdf8fLzzxo3Mt8LSUhCeQx7yWfY7DgVD35QQa940bolA3yyfz2Y4HXHoHxJd37+ubsvTzui4JUKMJIZuVNw+vFW5MxGdOEUirqGhrROvACZDymLRbgWYbUV+Gzb5uCCcMKn1iLV5mRMvd0Czb91V/13FsX0pe4CaYGHlTgNio7noTXHdF+milI5/79M8a9TD9wcmT3201jjFuZAlzmNRJkA5eRqKqB5igWMdHhb+1lKIxCCof/8IXsvZxvDGrC0MCFDggRKcC9PuRVIfbyuMQpqpELulKFhMj4bP1M8eFVZoJxehhYn3Ud+3Zswcu3XIQDu7dLMwAqqhooIMXh7TlP49DwbOV4zVN1x/YDNwXIIQzBkbd82+hrCnBvSD0QYUbwVoKvqkWuzmCaw/PC5f1CqmkD10FCizSheG3gVQUwocTJyZ4LQk1iFA67N7NZAy8JvMpMME6vDWuzUAjsevZXmHu6BTueuy/APJEAAjLQiWRuGuTWxDRMuPRG+1gnt2ZveZYYNnBDo7XyUFtRnxmJT3RYQYfRZ0xFy8iP2MfJ6pqcs/JriezP6dZMCYIUKKYNXJfMY/mYq3DILJTm9UKp6oEJ5TV1ElpGXoGxE2dGd84BoUgCoFiAlwReZSYEvwZumFAMYTZ65yM48PRtvqsUVYXQ5S5TuRaHbbjy3tC6h9Ym5pyuVZh+/HkIkJ2UyjLvSEuhhbzik6Kg+V+y5qOq6lqfLToKrWYwuk53YNWtV2BgOuupQ4oKAQXT05uw4MTTPgG9ctAlWHLd7Xht9KXmBe4HR09ME+0149D8L7+UVtfpEdIUBMUMX2EsWIAzwbj8Vs3PQgdM+/YEfTcWH3nUd5miJIJzceQYyOP1FvHmlnbmDh+ITkO79WjgchNK/D/9ZHVNzgIcpOCVp+7DwuN/dVWkVxQVA9Nt9rFlQlk52yh4MObSr5sdzTqZEYWac5pSkfJyC5kA7+fh86WCBXg/RMtksOJn12Dfji3hHV0bllkNnCKSPilq8J9Vc9sf0N0VXmjXiddbxJtb2ps/W0c2D7Vue6HkaAP3YGjgOX5V9Iw0659CCtKUXT/LhLLo/TcAS9sxborpb2568ehqAaMYfZkOC4RsLXMoPsHkDwvwfsi2tc9j8eE/4cRvrwnv6NzEtD7oemT1d0VNhGpgG559KPCcF+9maI120n0vj1YpQFkN3DTXyDTwnLxQEkkIipe4s2XKDcb4WoCvuBmJmZ1vwFfQml+I5u+sTxmHXErV5YJ6ar+vTeRoKmPygwV4f8TM2jc9vQkt9387sJvr6y4cJpQI+6Ya4AMeRTrV7cuE6NWUu1W3L61XgOuk2lnwbA1csomZiy2ZFAUi5s80ZObbsR/DoWS6IFsnr+kmWIAb9wu777wLg/3RZfhzjRcG6b4GC/CSwAK8H6I7vsjN22+NdY1lPxVCj/TIIFXFlOZ34wRy8zjZv3OzLxOi1wwxM/WK61gRfg3cSgtrbV7KTChxihpsOP83WD76KmOsmAKcSEFKqYaqdUnXSU24zU960Dwsk02eD0MpxRLg5tvWLmU8OkW1eavy1cHsT7AA74doqZjJmFxuhP62IFQ1gQGNQ7Dvkt/kNK/TJ4762sI05Zaf/zMGkPtnMdwITe1VC97E1GMImFn/dDmWfPp24yCuAFdUpKkaitYtFeDVNbUuU0agBm4GPMV9cMRhSPfego3lxNLA2+Z/AevGmm9QeQRsMbnDArwfkjTzT0fjMKLkYEKxqvGogT7LcuGZ7jrlH8uzefjKedmHQvOh3/v6C1LsUHgrcZbTHrspOQurGt+JAQNzK3EbWwNXVGSUaiR0twZ+FAOxcsi7fZkHAzdHzU3AoKyOFitm3BxrXgB8qWoLxZgP/ghr6s/FzLd+KDtf1sBLAgvwfojMLzrV3YWWOz/taiNTZrc+/gtMzxi5PYJejTde+KD9WTX9jL3mguwE/Jtpqx69A6fW/9nX7rWBzz73ciwf/yn5uDA0cDVp2MCzJpTshmr7sLlY+MWHcveFjulGqCiEjFKFhJ5ylZ/bWT8Xiz5/v/FGIZwaeMDD0HzoCDXcjXDxh29Cy+QvhPYpNqObpuGcG/+MuoZGx3z7dxGHUsECvB+ieXJhaJkM1v31bjQf/J2rXZia8oI1X3M0ygW409ShWhp4QJFgmY/4wpf/TapRS/21E8HRiIIUJFRLgFtuhI70s3nmZJ7ynpuwJTEjsp+hgdcg4amsE6RpB5lQbOEfxx0vQshbpEWBA3gkNL3tGmyonovx515d9HsxLMD7JbonF0Y61QXt8GuSjhJhbRYi8OLUlC3tNpGUCxY9k0MyJYm7HyVrA7sLKPYm5pyNtyCd6o6sYRmHYaPOwMSvPBvZj0iBrlZjivYa1I0PZ+cVoMGPyuyWtgvzZ4zjORJlZrE4QrmZjfJhzMTpmHXzC1l/dqaosADvh2hpt49yKtUtr34u24gyazl6IUW1NVQrX0eQCUXkkA1PpoGHCnDKBvLUUBrr/u8etwau5Z+JLxnwRuGam6JAU42ff27ncucJ+2Ot3mF/bkT2s5PJF3wKG6vmYPx51/rOrZzzPbSM+JB9HKdIxrraRTh+8Z2R/ZjKIvYWNxkxza0A9gohLiWiIQAeAjABwE4AHxJCHCvGJJnC4tWAO08ekycfkuUQCTShqJj2jRbjs9mWqAowoeSggcsEuFIVLMB1KEg6NH9dS7tNNkOD86pEziWG3ZwUFXrCPz+nBn6obirGdfiDX5yMGDsRI/7tb9Jzi977r67jmpFTgW3h85rztafDOzAVSS4a+BcAbHYc3wRgmRBiKoBl5jFTAQhPrcid//sTqQCXaatCCAiJHVkm3JIeQXtKmMc5CXD/uGqIABdESDg1ZV1zB/JEpAHoKYqiQEhs9E4beMP5XynoPYO9fZi+TiwBTkTjALwLwK8dzZcDuM/8fB+A9xR0ZkVkxe1Xo+Xn/1zuaZSNYS//3HV89v6HUdP2iq+f1NwgNKN4grevxA5bXeMWtBtGv88cIn5CK1nAjVodJsBVJBJZDVzomsuNsNiFBohUqQB32vLPPOeteE2dWLB7Jqp7XuSBqUzi/jXfCuCrcDvwjhRC7AcA8/8RsguJ6DoiaiWi1ra2NlmXkrP4yKNSj4f+wKkTxzBBf8PVVkfdmNf5D19fkglaocuLOEhKlFV5BK1Qq6AJ8mngYVF7MoGrVgeXpjJyoTi0dj3jMqFMPfcKyVX54Sx0DACbkzMxZOQ4qZeM1wslfdF/AgCWN32mx/NION5IVg26GBuq52IfSb+OTB8jUoAT0aUADgkhVudzAyHEXUKIBUKIBcOHD89niKLR8usvYcuqZyL7pVPdWH7X53Fgd+7VZHobXadOxO6r6Cm/cBVC6sctK2LsNasQKVBJYMnee9HVmQ3a2b/r1eA5SEwoYRqn19tD2bca3auNgsavXvYYBg8fHXhtrmhv+TJWzjFqda6vWYgZX1+OmroG+SarZ17TF70DWNqOJdfc0uN5JBwPyoU3/A6zbn4Bu4cs6fG4TO8njgb+ZgCXEdFOAL8DcB4R3Q/gIBGNBgDz/0NFm2WRaN5zNwb+5V8i+7266hks2Xcfdj/89RLMqri0H86GU++hUaF9FS2FlLcCuxDSTIMUY4PPqYVubfmL/bnz/iuD5yAR4EmPBr584vXZe3gCYxa2P2WX+solA2EUO5QmTJz3dihm1Ker6o5UgBfPdCOrk7n4yGNFux/Te4j8qxJC3CyEGCeEmADgCgDPCiE+BuBxAJa3/tUAyvoXs7X1WbT89ns4tnQ8Ut0BqTwljBHRzx3LZFB3urChyF2nO7D811/EkYN7CjpuGMf3bLU/a1eEp3VV9W7/Wgpd6okSSzg6hJgQOtY/9zBaH78TUzSJD7o1h4Rfs6/y2NZHLbjcNkWEBfnnkgM8ionfWo+GgYOhmBumirNwcVJmQileEE2SbeD9lp5kyvkhgN8T0bUA3gDwwcJMKT+mPfFe+/OqOz+BhTf8LqR3biimP7MqCpsic+Nzv8eSPfeg5ZFuDP3Mz6MvKADzV95gf06GbAYChgDPpNz2ahK6rzI9IDeh+HBuSAqBs1/w+zjHYdiYSa7jZHW9nWyKQvKQK4nCVbfJjunXwJUdz/s7FlMDj/g9rqk/F+cU7e5MOcnpr0oI8bwQ4lLz8xEhxPlCiKnm//5UcmUikWqXtqe6u7D8N9/0ta969GexxlUKLMD1lBHEoZ4u/uau0HVXru3VC/8LyQA/7eVnfBobqudC1dPQPFGbY/f+FXVHN/uuCavCY+Esj6al4lfl8VJbP8B1nKypBZkP2bCyYYXUwC2sdAHOvOTpOv8GoijCvS2SIZXq99BonHOjP8cM0zfok5GYqQlvl7av+cMPseT1n/raF7789dBwa6u2YqE1cEsryzc/Ry5sWfW0K9d20/wL3f7SThQFGiWRECmkPVGb48U+zDm9wndJkAll5ZB325+d0Z7pXatC53sUA7FNjRd0U1VdB5i1I8NCz0ulgQ9701X+jkU0odTWGQ+015UJdtuahrcCAPYPPLto92XKT58U4NM2GZXSWx78AXZtfTl74lSwvXvF3Tdg/XMPS89ZlV0KLcCzPs7FF+DeqjSDh40JjJSErkNXq5HQU9DS8fI6WwmsvCz6/P1YPsbYKnE+qJw+5sfRgMOfcfuhb226ElO/Gc/xKVldA1JjCPBCFkcwUc01bMwcsdtGTzpLcvPiCXBFVYGl7Zj0rXV2W2r0AgCAVj2oaPdlyk+fFOCDcAqHD+xG89Zb0P0HR8BOSJL5Jft+E2iTtXJpFFwDt7bcSqCBWwmeAEAXBDWRCDShQOuGplQjIdK+zIVBhAbIWNkBHeuvZLKFGNJIYtBQj0dMjIo5AHAQQ1FbNwBkvk2oIvh3HMfME8WK4R/A2ro328cdh3YCAEbhsN3mzfkNoKgauIymt3wYaaFi5Nv6b8Baf6BiBPjxwwew8uGfYPP33+TSqk+dOIYVv/+Rr397m+EuNzntSBKR55fIyiutonCVtlv//EtMXvN9Y1ol0MCd4dYZ89celJyJMt0QahWSIoWM+faxdonf9OQk1L5sab5aCqdvNDxuak9nc4EMxzEkkp4Q95ibfiOXvg41kbCDexIhAjwwP3kOLL7+bsz7atYFUpYa1/ezAEXdxJQxumkakt85iokzF5b0vkxpqRgBvvOeT2DRhu9gRnojmh58q92++Z5/weJN3/f1P/y8kXnNpcfF+BKdOuHPx2WZUMKEQ64sWP1VDIYVVFN8AS6LdpS56AFGBKauVqMKKdv+TxE5RIJMKM5rSUvbHhMD0kd8/VYMfY/zotD7rWq8CGvqz83e3xbgwWH6ORdxiMHERe8y5jPoYle7LxKyyCH8TP+k8EbBItHUuVHaXt11WNo+4tjLAACFHNVPYtghjx/aiwZHuS2h62hYYzwMRiDraNN1ugPrnrgTtcMnYsCIMwI1nQ0vPoa6waMwadbiwHuOPbkhcl49xZlSVQ3x1AAA0lPQE3UYghN4dfWjmIoYft5h562CA3rafmg06f482Iv/9T6su+UdmHN6ZWTR4YVfdKdCSNQYAlwNcSNUYxY+yIWhI8cBS9vh/e2P+fY2YGljtqHEJhSmf1AxAtyro6a6u1AV4j7VmWgEvMpYDA28/dAbrmT0hw+8gWmZrb5+a3/zVSzZf3+2YancdXHWMtMjYZb8PACMQvHdCJ2V2VUK1/hJS0FUGw+x5j13G20RAlwN0W7tDUY9eg/BDnjJ0eRgRWda+xTrahdhzumVrj5KyFtCMVhTfy7O6TBTwmbiB5cxTFwq5r1O90z1dMdJ81NWGLVMucH+fFZqHbyQRAvafLFbk/NWbO/qkAte9XR+bu9aJoOVf/x/eV3bE/QYSf8tSM8ACbd9PCqLX5gJxUrhSp5N5Feq5xkPPsfDT1OqrRvGni8AVNWaAtzcp5jztaexqvEiV59imFDCOOfGP2P5GUadUUqfjujNMLlTMQK8XXWXg+rqlCRlUpJYOeiSwDFkJpRkjTuvhtbtroze3ek+zo4Vrc3J7Omtf/oJFr2yNPLaQiMrZCzjEIagpvkaiQBX8WrizMDrwgQ8mf7X5HmIzO5e6+urm9VschfgAwEACWR/TuGx2yeK4AcexeQ3/gDASNnLMIWmYgR4g+YW2K8/adilp59+2W4jNYlFNzyIXEjWNLiO1bX32dr9ltZlOLL1Jel1eoPb7a3lwe/jxPHsxlz70TZs+N9seHzm24PR8ovPYuC2R3xj7aYxOc05H6yNWC8p4X4QjVi6A7Pe/G6QT4AncOY3VmHFsPdJxwnTwBVLA/e4YR7DQP88rVSsMd0ILarr/ClmheIW2KXWwAGgThiadx3lX8qNYYKoGAHudbVbsvtXyKRTqKKsUNC7IlKlOmywqwe8HftoJKpr3V/8s7tWY/29nwcATH/ifVi86Xvy+VS7w7mbt/4IGx/JujPu/NWVaN6aPU6QjuYDD2BGepNvLGk9ygITpIGnA7ZByJOQyRJ+QlIuDIgyoRjXem3gW6f6fZTtYgg5FH0AgIGDR+AQhuC1s7PVbvwaeHGr8cjYutjwkFpxlj+FA8P0lIrZxKzy7UgCx48cxDDHsWVnbF34YyxYJSlb5RAg87/8KADgWJu/NmFV54HoCckSOp3KXjegO352XSVG5fGeIgKCmNIk/xNQJCYUANJiBYC8ck725sLqBMAIJFJIYPwSf/4zS4CLVG4246rqGoxYusNVVcQrwGX1NYvN/EuuBS65FsE+SAyTPxWjgdcJ/y7+wdfWu44HzroQAJCoGeDrC8AuBdYyPCs4GhqH+PqFJa2yc6Zk/A8UvT4rPjQlvr01UfAITz96gAaeiamBk2mTFpJUqUC4DXzc2UZumqqF1wDIunbW1Et+T6a/utIR4yEahUeAF7ucGsOUmor4i051d7lMJS2TvwAAOLHZSNS/YvpN0L95FDOb3wkAGHrGDN8Yxw8fwLQDj+OUqEXz9dnSnrJw8mFdu3B43y5XmxVkkkp1YUvrMjTvvMN3HVVlQ6g1ii/AwzLoFYogE0oG8nl6C+VaLnjSYgURjDpjKrC0HbPPvdzV7s0qCADqiKnG/XIofBxIEfy+GaY3UREC/LTHE0SpHQQAWLLnHgAAVTe4NqiGjp7gG2Prb2/EYJxEA/lfzbtE0rU5N1YcROau81199GojKCOd6sb0J+QbecKRuU8LME3IUMopwINMKEm5CSUfAR5ETW2Dr82KqFS1/NPN2pjpehmmr1IRAtzrQaF4PEcSnuO6hkbX8fJf3YCJR/8eOH7Ndw5j8efudbX5gmtMwSXz5li98L98xXqdJpSur+3DysHvCrx/sQX43tc3Q9+9UnouSICrHkFtaeBKAQW4zCvEjqjUeh74og4PdntkmL5ARQhwzeM/nKxzu59RxKvykr33usLgg1jd8LbAc5bmmZEIcAGBblSBHBn2dIcJpaa2PtB7Ayi+AB/7m2YsPvKo9FzQm4Kz0jmQFbZqAcp3OfcgvIydbhTjzcwOrpMZl1JHXjJMqakMAe4Rmslat4Y9ePx03zX6N49iUzIbEt8hgsPuLc75kt9H28IS4LLNwLqh43GaakCOcGkrJPwAhhvXJYIFXylMKEEE2epVT5kuaxPT254Pzdf/OjD1wLAxTcDSdsy/5Joe30eUIE0vw5STChHghtB8NXEmukUSDUNHu85X1/k3wxRVReecq+3jeop+JQ+PJqw25+LXwEdNmo1uqoaSydpt01XGQ2bXTCOUGgGmh2MYUBI3wiBqdLmd2Fsv09bAqyqogC4LcKaPUyEC3HDZO3H2J6F8fS/qGtxh9bX1jbLLsODS67Dl0j/1+P5podpmGi2TgS4cPs9L2zFkxFikqAYNnXvw6poXABg+yCdQj8UfutHol/B7u6ytfwu2jrw0MjtgMTlWPU7abiWHsrDMEYmKqoDOApzp21SEALc3DlWjikzDoKGu8zJfbosho90VzDdUzw29V6fwC9oMVJApwLRMt+3HvLFqtt0npdRgRnoTznz8MgBGjcRuZP2Qx+78o29cXakCSCmrCSUz+8PS9qoajwnFzP0yYOjYos+pUDhNKDuUpjLOhGGKQ2UIcHMTUzG14Jq6rNdJy5QvyiugmAwens0z0jL8g5j+ladD71X9zX22n7mFU4BnUoanyfKmz+Csf8t6tqQVT+CLnoGGrJfFYM2/iSpIgVBUKEXQFHdsXIFDe3dIz6VFdl7zL7lWao+uCjChjJkwLdtoZRIMsGeXG6s+ZuvACzD2a3IvHIapZCpCgFs2cJJkAFTrBvvanLgCdUgJFfaAUaVGHTDS1ZahhG1CyKRMW7ondDyjZgW40HWQnoHuSF+7ceS7IaVIGvjEP1yIEb+aKz3XOuajkdfXDRjkMhVZJdMqKZrR0sCFkgzNHc8wlUpFfBttDVxSAoxysMmqY86O188zpgbVLgumWcE6ntzimprVWLe9/CKquw+7NPBhb/6E7z6CVBCpSFDhBPiRg3vQ8lt5Ai4AEN86huZ/vi1ynGRVNVJf22MflyOTH8Mw4VSEo6xlAydJfpGG4RNij6NW+1OOxumXQSJrAzc1cG+FmoTDA8Wyg+9Uxtttw8ZOltyJ7BzluqYVREgOvfMsDA05H6ZBb01Mg8NA4jJVOYsWd4skDqgj0futyqYGjuJne2SYclAZAtz0vVYcCflTNx/EiSMHMX1MfDESlOTKi9fXWSPVNqFoaTPa0iPAO8csAV5f5bkuu7yDho3CK+f9N2Y/+wm7TZBiFy7QtExZtVztG0cwNSSjoDNdbOKbBzC+BClwewy7ETJ9nMoyoTgiLquqa4ygjxxISHJvyPC60GmU1cCtKu3eijFU7R9bh1sgJ2s9DxAiu8ybrgcX4y0FaiIR+gAhhwCP6ttbGDrtTQCAxIzgKk0MU8lUhAZuJWLqaWh0VUwBXuUp8qAhYT889AAbOEkCXHRPmLov4Mj0QgEcaWp7yEEMxUgcie6YI2FFi3srk2c3o2vKPpxTG890xjCVRoVo4IbWq/SwpmHtgHCPFQuvoNccXiiWAPfawGU5QnSPkK+uH+Q6FqTYNmktZs3KKESRfqWhFXd6MTUsvJk+TEUI8M4drQB6roGPGDclVr8qT6FjnVSoZjkuPcAGLtsg9QlwnzAhW5PXCqSBF8vq6/ULZxim/FSEAD9z9+8BAGqeGvjqAUZFGFnxBhleDVxAyT48zLcBnxeKVAN3P3DqB3reAEjJPggKZAMvdH3NDdVzkRFKRdi8Gaa/EanSEtF4AL8BMAqADuAuIcRtRDQEwEMAJgDYCeBDQohjxZik5QaWbx6OOZ//PTq6OhH3Zbq2zm8rt8w3wsz5TZ5NTLkAdws93+u80O1x4phQdm9bhyGjmlA/YFBgn0K7zE35wv/iZMdJxDM+MQxTSuJo4BkAXxZCzADQDOB6IpoJ4CYAy4QQUwEsM4+LSrIqv2i6RLIqVOj57iOJ1rQ9YCwvFI8GLjPv6BK/9W1q1oxDesYeJ8oLReg6xj9wLnbefll4vwIL8Jq6BgwePjq6I8MwJSdSgAsh9gsh1pifTwLYDGAsgMsB3Gd2uw/Ae4o0R5t8TSi5Igt2sTbxhGZq4Eq0SUGQv8/oLzyD5eM/ZYwhNNsGHuWF0tlxAgBwVmpdYJ+trc+iXrhLka196v7IeTIMU5nkZAMnogkA5gFYAWCkEGI/YAh5ACMCrrmOiFqJqLWtrU3WJZLtw98BAKhtkKeNLQW2B0yABj5opN8nXUhytzQMHAylwVgq0jP2gyBKA+84EW6dOvDGNkx74r0YjBOu9nkvXR96HcMwlUtsAU5EDQD+COAGIcSJqP4WQoi7hBALhBALhg8fns8csfDTv8Cx67egwbsJWEROfWknWs78CgDDLKGaeVhq2rcD8NvAR4ydiNb5P4o1NpkeLSQ0wNT2ozRwWSUgJ6dPHY91b4Zh+g6xBDgRJWEI7weEEFaFhINENNo8PxrAoeJM0Yj8K7UdtmHgYCiO0m2WG+G8zpcAyE0otUPcxRHmn3xOOnbytacAAGefeMHOsCgrluxERFTtKVQgEMMwlUOkACciAnA3gM1CiJ84Tj0OwKpZdjWAxwo/vfLiDN1PeO3vEvOILFuijKFduwAASdKgmA8Gq+pQEEIP9/DWtfAHAMMwfY84GvibAXwcwHlE9LL57xIAPwTwDiLaBuAd5nGfwlnt3hsFqkg0cIoZaLRv2Ft842bSEQJcRJhYIjR4hmH6HpESRwjxdyDQN+38wk6nd2EJV4JAwqNdkyrzVInnJTPh3V8F7n4IhzEoWyzZivD00LZvJwYOGQFdDzehiAKF4jMMUzlURCRmubCKOAASF0aJi6BlDrHYkpwpHXfgYGMz97XhF/hD9B3omobhd83Bxp99GCLCSyVOIFBG8K+bYfoS/I0OwW0DdwtnmQlFdWjpK2bcjKYbnpKOW9fQiN0f/RvmfPKnUEwNPJPxC/BU92kAwDmn/oaTbXvtdlmtS6FnBfhr6iSse+uvXedbRn4EHTdsk86HYZjKhAV4CE4buOo1oUgEeOp0h/151JwLUVsfXEBi/NQ5qKmth5q0NHC/Dby767T9+aynr7Q/y2pdOk0oaarGyCnz3Ofrh6Nx8LDA+TAMU3mwAA/Bzn8i2QKQFVjOdGejIOOmvlWThgYu8yJJWwWUJXR1nnIdO/3EhaR4M1VCBR2GYXKCBXgIYTmwSZKdr3HkGfZnRSLgpfcwBbiQmFDCgncO7t7uOnZq4Dop/g1V4l81w/Q1+FsdRojWqkgE4thJZ2XPS7xUZFgCPHVsn6v91IljaD+0O/C6VKc7GNYZii9I9b8BsABnmD4Hf6vzJSI/dtziEwlTgC/e8kMceCO7yXjgtvMx9bF3B16X7upwHbs1cNXn9sgCnGH6HvytzoE9H/uH/TnKRKLGNKEkqrK26uMHd9mfp2ivhV7ntLcDgNCzNnQBReL2yL9qhulr8Lc6hLpBhr/2sRGLAADjpsyyz0VVqJHZyGVYGrgxZvyScT4B7trEVH2h/7IUuQzDVDaVWam2RJxx5lxsedcfMe+sZt85bzZCLzI/cRkJR5GK7pNHkeruQlV1dOEKreMo0qluu0yc0w9ckOLfgGUNnGH6HPytjmD6wgtQIyuxViAbeJWjTufs567BoR/OiXXdwvXfwv4fOny9HZuYkzvX+TVuFuAM0+fgb3WeRNnA4xYB9tbJHCcOxJ7DGboRnZlJp6B3HLXbB6LD1zfqjYFhmMqDTSh5EmXjVmMK8ELYptf99MNoPvlsxI04kIdh+hqsluVJsro29HwuG5Lrzv1Vj+Yy3yO8U0KS6pY1cIbpc/C3Okc2JQ1PlNr68PqccTcxAWDqoot6NCcvKvypZwULcIbpc/C3OkdGXPNbvHLef2PQsFGh/cLC8L3U5VCseV3Nwsg+KoVX72EYpm/AAjxHho1pwuxz3xvZT8nRtr2bxtifw6rzVL/9qziEITmNDcDlpcIwTN+ABXiRyHVzUqOsxt512u9FYqMoyFDue8+Ca2YyTJ+DBXiB2aWMi+4UQSYlL68GGJuRakB9TKcW70PnkmsM09dgAV5gGj+7DFsvfSTn64TjVxGWB1xWSMIiQ+7w+d0f/ZttMxcswBmmz8ECvMAMGjYK0xacl/N17dXZTdGujvbAfkSEo0n5BqrmMa2MnzoHXQ1GjnLRcSTnOTEM07thAd5LEI7Izq5TIQJcUaBLCioDcldBvcpMAyDCq9ozDFN5sADvJQiHUO7uDNPAgwX40UajoMQ+Gmm3qcOnAACUxrGFmCbDML0IDqXvJehK1n6d9phQlk+8HnNf/xVqKQVSVJewB4ADGIZBoh3TP/pjbNp6BUZMzKa9XXDZ9Vhb14iFF3ysuD8AwzAlhzXwXoJw2K/nvXS969yZ7/wsusjIWkiKgtN1bm+TBDJYP+QiNA4ZjplLLsawUePtc4qqYt5FV8dOrsUwTOXAAryXIEK8S1Q1gTQMDV0hBZM/9AMcxiDjOl2HCs1lQ2cYpn/AAryXoA2aGHiO1ATSlougomLYqPHYPuGjAIB0OoWEyEAoycDrGYbpm7AA7yWc/b4bsWrQxdJzqqoiQ0btTNuf28y1kk51IQENYA2cYfodLMB7CfUDBmHgW66TnlMUxQ7SyVhBPqoh0Ov/qwm1lIJQq6TXMgzTd2EB3otQk3IhrKgJO0jHEuCU9NTNVNmEwjD9DRbgvQglQAiragInag3PEjVhCHmlqs5zMZtQGKa/wQK8FxGUQ1xVE5hx3T1YNe8/MHXeuUZblaciEJtQGKbf0SMBTkTvJKKtRLSdiG4q1KT6K0pCLsAVVUX9gEFYePln7Ta12l0MuRC1NRmGqSzy/tYTkQrgDgAXA5gJ4CNENLNQE+uPKDlo0aq3JiebUBim39ETtW0RgO1CiNeFECkAvwNweWGm1T9Rk24b+CkRXDg56dHAq4ZOKMaUGIbpxfREgI8FsNtxvMdsc0FE1xFRKxG1trW19eB2fZ+EYxNz+cTP4cQnnseaxbdK+1bVZgX4utpFmHnu+4o9PYZhehk9ee8mSZuvmq4Q4i4AdwHAggULuNpuCE4b+IKPfAvJqmqMmThd2tepgc/52tNFnxvDML2PnmjgewCMdxyPA7CvZ9Pp31gugkB0VfvquoZiT4dhmF5OTwT4KgBTiWgiEVUBuALA44WZVv9EdWjgUdkDq2rqQ88zDNP3yduEIoTIENHnADwJQAVwjxBiY8Fm1g9JBERiyqipYwHOMP2dHvmeCSH+AuAvBZpLvyeRiB8OX1VVE92JYZg+DUd/9CKi7N5OOHCHYRiWAr0IFsoMw+QCh+9VMK0DL0Cmdjiayz0RhmHKAgvwCmbBl/5Y7ikwDFNG+J2dYRimQmEBzjAMU6GwAGcYhqlQWIAzDMNUKLyJ2ctY+6Y7AADzyjwPhmF6PyzAexnzLvxYuafAMEyFwCYUhmGYCoUFOMMwTIXCApxhGKZCYQHOMAxTobAAZxiGqVBYgDMMw1QoLMAZhmEqFBbgDMMwFQoJIUp3M6I2ALtKdsPCMgzA4XJPoszwGhjwOhjwOhiUYh2ahBDDvY0lFeCVDBG1CiEWlHse5YTXwIDXwYDXwaCc68AmFIZhmAqFBTjDMEyFwgI8PneVewK9AF4DA14HA14Hg7KtA9vAGYZhKhTWwBmGYSoUFuAMwzAVSp8X4EQ0iIgeJqItRLSZiJYQ0RAiepqItpn/Dzb7fpSIXnb804lornluPhG9QkTbieinRERmezURPWS2ryCiCY57X23eYxsRXV2On98xl1zWIUlE95k/72YiutkxTn9ahyoiutf8edcR0dsc41TsOgSswQeJaKP5N7/A0/9m8+fZSkQXOdordg3MucReByIaSkTPEdEpIvqZZ5zyrYMQok//A3AfgE+Zn6sADALwIwA3mW03AbhFct1sAK87jlcCWAKAAPwVwMVm+2cB/ML8fAWAh8zPQwC8bv4/2Pw8uBLWAcCVAH5nfq4DsBPAhH64DtcDuNf8PALAagBKpa9DwBrMADANwPMAFjj6zgSwDkA1gIkAXgOgVvoa5LEO9QDeAuAzAH7mGads61CWhSvhL2gggB0wN2sd7VsBjDY/jwawVXLtDwB839Fni+PcRwD80vz8JIAl5ucEjIgscvYxz/0SwEcqYR3Muf/Z/HmGAnjV/GPrb+twB4CPOfotA7CoktchaA0c55+HW3DdDOBmx/GTprCq2DXIZx0c7Z+AQ4CXex36ugllEoA2APcS0Voi+jUR1QMYKYTYDwDm/yMk134YwIPm57EA9jjO7THbrHO7zbEyANphCD27XXJNqcl1HR4G0AFgP4A3APxYCHEU/W8d1gG4nIgSRDQRwHwA41HZ6xC0BkEEzbuS1wDIfR2CKOs69HUBngBwDoA7hRDzYAilm6IuIqLFADqFEBusJkk3EXEu7JpSk+s6LAKgARgD47X5y0Q0Cf1vHe6B8eVqBXArgJcAZFDZ65DrGuTz8/T2NQDylA0SyroOfV2A7wGwRwixwjx+GMYv7SARjQYA8/9DnuuuQFb7tsYZ5zgeB2Cf49x4c6wEgEYAR53tkmtKTa7rcCWA/xNCpIUQhwD8A8AC9LN1EEJkhBBfFELMFUJcDsNGug2VvQ5BaxDWXzbvSl4DIPd1CBunbOvQpwW4EOIAgN1ENM1sOh/AJgCPA7B2fq8G8Jh1DREpAD4I4HeOcfYDOElEzeYO81WOa5xjfQDAs8IwbD0J4EIiGmx6NVxotpWcPNbhDQDnkUE9gGYYdr5+tQ5EVGe9VhPROwBkhBCbKnkdQtYgiMcBXGF6VEwEMBXAykpeAyCvdQgap7zrUI4NhFL+AzAXxivwegCPwtj1HQpjQ2qb+f8QR/+3AWiRjLMAwAYYu/A/QzaKtQbAHwBsh7EbPclxzSfN9u0ArqmUdQDQYP5MG2H8Ud/YT9dhAowNzs0AnoGR0rPi1yFgDd4LQzPsBnAQwJOO/l83f86tMD0sKn0N8lyHnTA06FNmn5nlXgcOpWcYhqlQ+rQJhWEYpi/DApxhGKZCYQHOMAxTobAAZxiGqVBYgDMMw1QoLMAZhmEqFBbgDMMwFcr/B7kGBqVFOCTNAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Plot temperature profile at single airport to confirm best effort interpolation is completed:\n",
    "sel_arpt = 'ORD_IL'\n",
    "sgl_arpt_inspect = wx_read_proc[wx_read_proc['ARPT_NAME']==sel_arpt].compute()\n",
    "sgl_arpt_inspect['HourlyDryBulbTemperature'].plot()\n",
    "sgl_arpt_inspect[sgl_arpt_inspect['MISSING_WX']==False]['HourlyDryBulbTemperature'].plot()\n",
    "\n",
    "# YUM became NYL in 2008. Need to create consistent weather station to \"airport\" mapping.\n",
    "# Check if NYL used in flight data? Maybe airport move coincide w/ station CALL update.\n",
    "\n",
    "# TODO: For continuous attributes, should use seasonality model to fill in missing data, including extrapolation.\n",
    "# Difficult to fill in sporadic fields like sky condition. Should seek out nearby weather data for prioritized substitution."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}