EDA1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Summary\n",
    "\n",
    "1. **Create list of file imports with path name**\n",
    "    - This list gets passed through joblib and function Panda\n",
    "   \n",
    "   \n",
    "2. **Create a pandas multilevel index DataFrame**\n",
    "    - Drop all rows that are beyond the RAWS latest collecting year (these are all nulls anyways) \n",
    "    - Create df with station 4-digit code as name\n",
    "    - Drop all values that should be null, in this case '-9999'\n",
    "    - Remove all rows where station metrics are all nulls, set date to be null\n",
    "    - concats all df together to create a multilevel index dataframe\n",
    "    - After running in parallel, concat list of all multilevel dataframes\n",
    "    \n",
    "    \n",
    "3. **Redundancy Checks & EDA**\n",
    "\n",
    "\n",
    "4. **Loading dates DataFrame**\n",
    "    - Repeats step 2 but this time creates single level df where each column is a RAWS and rows are the days that the site contains data\n",
    "    \n",
    "    \n",
    "5. **Loading NESSID dataset**\n",
    "    - Used to find NESSID for each 4-digit RAWS code\n",
    "    - Created in webscrape notebook\n",
    "    \n",
    "    \n",
    "6. **More EDA**\n",
    "    - Remove all Stations that are completly empty, aka no data collected\n",
    "    \n",
    "    \n",
    "7. **Adding NESSID**\n",
    "    - Takes NESSID and replace 4-digit code with NESSID\n",
    "    \n",
    "    \n",
    "10. **Dataset Uses**\n",
    "    - stations_dates \n",
    "        - Used to locate the nearest RAWS with data  to a fire occurrence \n",
    "    - RAWS_station\n",
    "        - Used to pull meteorological data for each fire based on the RAWS it is using"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Create list of file imports with path name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary>Click to show file names</summary>\n",
    "\n",
    "sl_0_20_0.csv sl_0_20_0.json sl_0_20_1.csv sl_0_20_1.json sl_0_20_2.csv sl_0_20_2.json sl_0_20_3.csv sl_0_20_3.json sl_0_20_4.csv sl_0_20_4.json sl_0_20_5.csv sl_0_20_5.json sl_0_20_6.csv sl_0_20_6.json sl_0_20_7.csv sl_0_20_7.json sl_0_45_0.csv sl_0_45_0.json sl_0_45_1.csv sl_0_45_1.json sl_1_20_0.csv sl_1_20_0.json sl_1_20_1.csv sl_1_20_1.json sl_1_20_2.csv sl_1_20_2.json sl_1_20_3.csv sl_1_20_3.json sl_1_20_4.csv sl_1_20_4.json sl_1_20_5.csv sl_1_20_5.json sl_1_20_6.csv sl_1_20_6.json sl_1_20_7.csv sl_1_20_7.json sl_1_45_0.csv sl_1_45_0.json sl_1_45_1.csv sl_1_45_1.json sl_2_20_0.csv sl_2_20_0.json sl_2_20_1.csv sl_2_20_1.json sl_2_20_2.csv sl_2_20_2.json sl_2_20_3.csv sl_2_20_3.json sl_2_20_4.csv sl_2_20_4.json sl_2_20_5.csv sl_2_20_5.json sl_2_20_6.csv sl_2_20_6.json sl_2_20_7.csv sl_2_20_7.json sl_2_20_8.csv sl_2_20_8.json sl_2_20_9.csv sl_2_20_9.json sl_2_20_10.csv sl_2_20_10.json sl_2_20_11.csv sl_2_20_11.json sl_2_20_12.csv sl_2_20_12.json sl_3_20_0.csv sl_3_20_0.json sl_3_20_1.csv sl_3_20_1.json sl_3_20_2.csv sl_3_20_2.json sl_3_20_3.csv sl_3_20_3.json sl_3_20_4.csv sl_3_20_4.json sl_3_20_5.csv sl_3_20_5.json sl_3_20_6.csv sl_3_20_6.json sl_3_20_7.csv sl_3_20_7.json sl_3_20_8.csv sl_3_20_8.json sl_3_20_9.csv sl_3_20_9.json sl_3_20_10.csv sl_3_20_10.json sl_3_45_0.csv sl_3_45_0.json sl_4_20_0.csv sl_4_20_0.json sl_4_20_1.csv sl_4_20_1.json sl_4_20_2.csv sl_4_20_2.json sl_4_20_3.csv sl_4_20_3.json sl_4_20_4.csv sl_4_20_4.json sl_4_20_5.csv sl_4_20_5.json sl_4_20_6.csv sl_4_20_6.json sl_4_20_7.csv sl_4_20_7.json sl_4_20_8.csv sl_4_20_8.json sl_4_20_9.csv sl_4_20_9.json sl_4_20_10.csv sl_4_20_10.json sl_4_45_0.csv sl_4_45_0.json sl_5_20_0.csv sl_5_20_0.json sl_5_20_1.csv sl_5_20_1.json sl_5_20_2.csv sl_5_20_2.json sl_5_20_3.csv sl_5_20_3.json sl_5_20_4.csv sl_5_20_4.json sl_5_20_5.csv sl_5_20_5.json sl_5_20_6.csv sl_5_20_6.json sl_5_20_7.csv sl_5_20_7.json sl_5_20_8.csv sl_5_20_8.json sl_5_20_9.csv sl_5_20_9.json sl_5_20_10.csv sl_5_20_10.json sl_5_20_11.csv sl_5_20_11.json sl_5_20_12.csv sl_5_20_12.json sl_6_20_0.csv sl_6_20_0.json sl_6_20_1.csv sl_6_20_1.json sl_6_20_2.csv sl_6_20_2.json sl_6_20_3.csv sl_6_20_3.json sl_6_20_4.csv sl_6_20_4.json sl_6_20_5.csv sl_6_20_5.json sl_6_20_6.csv sl_6_20_6.json sl_6_20_7.csv sl_6_20_7.json sl_6_20_8.csv sl_6_20_8.json sl_6_20_9.csv sl_6_20_9.json sl_6_20_10.csv sl_6_20_10.json sl_6_45_0.csv sl_6_45_0.json sl_7_20_0.csv sl_7_20_0.json sl_7_20_1.csv sl_7_20_1.json sl_7_20_2.csv sl_7_20_2.json sl_7_20_3.csv sl_7_20_3.json sl_7_20_4.csv sl_7_20_4.json sl_7_20_5.csv sl_7_20_5.json sl_7_20_6.csv sl_7_20_6.json sl_7_20_7.csv sl_7_20_7.json sl_7_20_8.csv sl_7_20_8.json sl_7_20_9.csv sl_7_20_9.json sl_7_20_10.csv sl_7_20_10.json sl_7_45_0.csv sl_7_45_0.json sl_8_20_0.csv sl_8_20_0.json sl_8_20_1.csv sl_8_20_1.json sl_8_20_2.csv sl_8_20_2.json sl_8_20_3.csv sl_8_20_3.json sl_8_20_4.csv sl_8_20_4.json sl_8_20_5.csv sl_8_20_5.json sl_8_20_6.csv sl_8_20_6.json sl_8_20_7.csv sl_8_20_7.json sl_8_20_8.csv sl_8_20_8.json sl_8_20_9.csv sl_8_20_9.json sl_8_20_10.csv sl_8_20_10.json sl_8_20_11.csv sl_8_20_11.json sl_8_20_12.csv sl_8_20_12.json sl_9_20_0.csv sl_9_20_0.json\n",
    "    \n",
    "</details>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "# takes input copy of files, and turns them into strings\n",
    "# Above markdown contains full list, note make sure .json and .csv are in the same directory\n",
    "files = input()\n",
    "files = files.split(\" \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# take files and seperates .json and .csv files\n",
    "def import_list(files, path=''):\n",
    "    '''\n",
    "    Function takes a list of strings, seperates .json and .csv, option to add a path name to all files\n",
    "    files : list of files .csv or .json\n",
    "    path : input path name if file is not in the same directory\n",
    "    '''\n",
    "    file_list = files\n",
    "    json_files = []\n",
    "    csv_files = []\n",
    "    for file_name in file_list:\n",
    "        if file_name.endswith(\".json\"):\n",
    "            json_files.append(path + file_name)\n",
    "\n",
    "        elif file_name.endswith(\".csv\"):\n",
    "            csv_files.append(path + file_name)\n",
    "            \n",
    "    return json_files, csv_files\n",
    "\n",
    "json_files, csv_files = import_list(files)\n",
    "# Add path name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "import json\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pickle\n",
    "from joblib import Parallel, delayed"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Create a pandas multilevel index DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def Panda(data,date=None):\n",
    "    '''\n",
    "    Function: load in multiple json files, clean and create multilevel index dataframe, run with parallel process\n",
    "                output is a list of multilevel index dataframes \n",
    "                \n",
    "    data : input list of json file paths\n",
    "    \n",
    "    date : used for creating a single level of (will drop and run in new function)\n",
    "    \n",
    "    \n",
    "    Summary:\n",
    "    \n",
    "    threshold = file of each stations latest record, when pulling data, post request form pulled from 2023-2014\n",
    "                so if latest record as 2019, then all rows after are -9999, except for date, year, and day_of_year\n",
    "    \n",
    "    key_name = list of station code names\n",
    "    values = station data\n",
    "    \n",
    "    create rows, header, dataset: \n",
    "            removes the header and pulls the actual data, \n",
    "            input headers are incorrect so header is created with list below\n",
    "            \n",
    "    creating dataframes:\n",
    "            variable name for each df is the station code name, threshold is used to drop all rows that are beyond\n",
    "            collection date, '-9999' is changed to nulls, and any rows missing all weather metrics have their \n",
    "            date set to null, this ensures that day will not be used.\n",
    "            \n",
    "    final step: concats all dataframes together with multilevel indexing, each station at index 0, data at index 1\n",
    "    '''\n",
    "    # Station latest recorded year, stored as dict\n",
    "    with open('Data/threshold_year.pickle', 'rb') as file:\n",
    "        threshold = pickle.load(file)\n",
    "    \n",
    "    conl = []\n",
    "    # Reading json files, each contains x number of stations\n",
    "    with open(data) as file:   \n",
    "        data = json.load(file)\n",
    "    for station in data:\n",
    "        # pull station code name\n",
    "        key_name = list(station.keys())[0]\n",
    "        # Get the value of the dictionary\n",
    "        values = list(station.values())[0]\n",
    "\n",
    "        # create rows\n",
    "        rows = [line.strip().split() for line in (list(station.values())[0]).strip().split('\\n')]\n",
    "\n",
    "        header = ['date', 'year', 'day_of_year', 'day_of_run', 'total_solar_radiation_ly',\n",
    "                  'ave_mean_wind_speed_mph', 'ave_mean_wind_direction_deg', 'max_maximum_wind_gust_mph',\n",
    "                  'ave_average_air_temperature_deg_f', 'max_average_air_temperature_deg_f',\n",
    "                  'min_average_air_temperature_deg_f', 'ave_average_relative_humidity',\n",
    "                  'max_average_relative_humidity', 'min_average_relative_humidity', 'total_precipitation_in']\n",
    "\n",
    "\n",
    "        # remove header and ending rows\n",
    "        dataset = rows[7:-6]\n",
    "\n",
    "        # Creating individual dataframes\n",
    "        globals()[key_name] = pd.DataFrame(dataset, columns=header)\n",
    "\n",
    "        # Change year to int so it can be dropped\n",
    "        globals()[key_name]['year'] = globals()[key_name]['year'].astype(int)\n",
    "\n",
    "        # Dropping the extra rows\n",
    "        globals()[key_name] = globals()[key_name].drop(globals()[key_name][globals()[key_name]['year'] > threshold[key_name]].index)\n",
    "\n",
    "        # Changing all -9999 to nulls\n",
    "        globals()[key_name]= globals()[key_name].replace('-9999', np.nan)\n",
    "\n",
    "        # Removing all rows that have all station metrics as nulls\n",
    "        for index, row in globals()[key_name].iterrows():\n",
    "            if row.iloc[-11:].isna().all():\n",
    "                # use .at, might be faster\n",
    "                globals()[key_name].at[index, 'date'] = np.nan\n",
    "\n",
    "\n",
    "        # Creating multi level dataframe\n",
    "        globals()[key_name].columns = pd.MultiIndex.from_product([[key_name], globals()[key_name].columns])\n",
    "\n",
    "        # create dataframe of only the dates\n",
    "        if date:\n",
    "            globals()[key_name] = globals()[key_name].xs('date', axis = 1, level = 1)\n",
    "            \n",
    "        # Creating list of dataframes to concat\n",
    "        conl.append(globals()[key_name])\n",
    "\n",
    "    final_dfj = pd.concat(conl, axis = 1)\n",
    "\n",
    "    return final_dfj\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Parallel(n_jobs=-1)]: Using backend LokyBackend with 8 concurrent workers.\n",
      "[Parallel(n_jobs=-1)]: Done  34 tasks      | elapsed:  2.1min\n",
      "[Parallel(n_jobs=-1)]: Done 108 out of 108 | elapsed:  5.7min finished\n"
     ]
    }
   ],
   "source": [
    "# Run panda() function in parallel\n",
    "results = Parallel(n_jobs=-1, verbose=1)(\n",
    "    delayed(Panda)(files) for files in json_files\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "json_df = pd.concat(results, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total File Size: 5.62 GB\n"
     ]
    }
   ],
   "source": [
    "# Check file size\n",
    "total_memory_gb = json_df.memory_usage(deep=True).sum() / 1024**3\n",
    "print(f\"Total File Size: {total_memory_gb:.2f} GB\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. Redundancy Checks & EDA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"10\" halign=\"left\">IIND</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"10\" halign=\"left\">TGIL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>year</th>\n",
       "      <th>day_of_year</th>\n",
       "      <th>day_of_run</th>\n",
       "      <th>total_solar_radiation_ly</th>\n",
       "      <th>ave_mean_wind_speed_mph</th>\n",
       "      <th>ave_mean_wind_direction_deg</th>\n",
       "      <th>max_maximum_wind_gust_mph</th>\n",
       "      <th>ave_average_air_temperature_deg_f</th>\n",
       "      <th>max_average_air_temperature_deg_f</th>\n",
       "      <th>...</th>\n",
       "      <th>ave_mean_wind_speed_mph</th>\n",
       "      <th>ave_mean_wind_direction_deg</th>\n",
       "      <th>max_maximum_wind_gust_mph</th>\n",
       "      <th>ave_average_air_temperature_deg_f</th>\n",
       "      <th>max_average_air_temperature_deg_f</th>\n",
       "      <th>min_average_air_temperature_deg_f</th>\n",
       "      <th>ave_average_relative_humidity</th>\n",
       "      <th>max_average_relative_humidity</th>\n",
       "      <th>min_average_relative_humidity</th>\n",
       "      <th>total_precipitation_in</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>270</td>\n",
       "      <td>0.42</td>\n",
       "      <td>93</td>\n",
       "      <td>12.00</td>\n",
       "      <td>26.3</td>\n",
       "      <td>44.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.54</td>\n",
       "      <td>192</td>\n",
       "      <td>21.00</td>\n",
       "      <td>57.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>92</td>\n",
       "      <td>98</td>\n",
       "      <td>79</td>\n",
       "      <td>0.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>207</td>\n",
       "      <td>3.50</td>\n",
       "      <td>146</td>\n",
       "      <td>20.00</td>\n",
       "      <td>17.6</td>\n",
       "      <td>26.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.71</td>\n",
       "      <td>358</td>\n",
       "      <td>20.00</td>\n",
       "      <td>36.7</td>\n",
       "      <td>40.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>96</td>\n",
       "      <td>98</td>\n",
       "      <td>91</td>\n",
       "      <td>0.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>34</td>\n",
       "      <td>3</td>\n",
       "      <td>114</td>\n",
       "      <td>0.96</td>\n",
       "      <td>232</td>\n",
       "      <td>15.00</td>\n",
       "      <td>24.8</td>\n",
       "      <td>38.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.62</td>\n",
       "      <td>66</td>\n",
       "      <td>11.00</td>\n",
       "      <td>37.2</td>\n",
       "      <td>45.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>88</td>\n",
       "      <td>96</td>\n",
       "      <td>71</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>35</td>\n",
       "      <td>4</td>\n",
       "      <td>237</td>\n",
       "      <td>5.71</td>\n",
       "      <td>120</td>\n",
       "      <td>16.00</td>\n",
       "      <td>12.7</td>\n",
       "      <td>19.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.96</td>\n",
       "      <td>93</td>\n",
       "      <td>16.00</td>\n",
       "      <td>37.9</td>\n",
       "      <td>41.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>96</td>\n",
       "      <td>100</td>\n",
       "      <td>88</td>\n",
       "      <td>0.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>36</td>\n",
       "      <td>5</td>\n",
       "      <td>294</td>\n",
       "      <td>4.92</td>\n",
       "      <td>122</td>\n",
       "      <td>17.00</td>\n",
       "      <td>6.2</td>\n",
       "      <td>15.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7.46</td>\n",
       "      <td>347</td>\n",
       "      <td>23.00</td>\n",
       "      <td>33.1</td>\n",
       "      <td>40.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>69</td>\n",
       "      <td>100</td>\n",
       "      <td>56</td>\n",
       "      <td>0.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3388</th>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>133</td>\n",
       "      <td>3389</td>\n",
       "      <td>330</td>\n",
       "      <td>3.08</td>\n",
       "      <td>125</td>\n",
       "      <td>23.00</td>\n",
       "      <td>59.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.46</td>\n",
       "      <td>134</td>\n",
       "      <td>25.00</td>\n",
       "      <td>76.4</td>\n",
       "      <td>86.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>83</td>\n",
       "      <td>96</td>\n",
       "      <td>62</td>\n",
       "      <td>0.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3389</th>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>134</td>\n",
       "      <td>3390</td>\n",
       "      <td>608</td>\n",
       "      <td>4.08</td>\n",
       "      <td>103</td>\n",
       "      <td>34.00</td>\n",
       "      <td>64.9</td>\n",
       "      <td>81.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.25</td>\n",
       "      <td>131</td>\n",
       "      <td>29.00</td>\n",
       "      <td>72.5</td>\n",
       "      <td>85.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>94</td>\n",
       "      <td>100</td>\n",
       "      <td>65</td>\n",
       "      <td>0.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3390</th>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>135</td>\n",
       "      <td>3391</td>\n",
       "      <td>335</td>\n",
       "      <td>1.79</td>\n",
       "      <td>96</td>\n",
       "      <td>14.00</td>\n",
       "      <td>60.2</td>\n",
       "      <td>74.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.92</td>\n",
       "      <td>218</td>\n",
       "      <td>22.00</td>\n",
       "      <td>75.6</td>\n",
       "      <td>88.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>83</td>\n",
       "      <td>100</td>\n",
       "      <td>52</td>\n",
       "      <td>0.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3391</th>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>136</td>\n",
       "      <td>3392</td>\n",
       "      <td>571</td>\n",
       "      <td>2.04</td>\n",
       "      <td>187</td>\n",
       "      <td>19.00</td>\n",
       "      <td>61.5</td>\n",
       "      <td>80.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.17</td>\n",
       "      <td>285</td>\n",
       "      <td>12.00</td>\n",
       "      <td>71.5</td>\n",
       "      <td>78.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>85</td>\n",
       "      <td>100</td>\n",
       "      <td>65</td>\n",
       "      <td>0.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3392</th>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>137</td>\n",
       "      <td>3393</td>\n",
       "      <td>476</td>\n",
       "      <td>2.54</td>\n",
       "      <td>108</td>\n",
       "      <td>19.00</td>\n",
       "      <td>61.7</td>\n",
       "      <td>77.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.25</td>\n",
       "      <td>318</td>\n",
       "      <td>14.00</td>\n",
       "      <td>70.5</td>\n",
       "      <td>80.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>79</td>\n",
       "      <td>100</td>\n",
       "      <td>57</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3393 rows × 33780 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            IIND                                                        \\\n",
       "            date  year day_of_year day_of_run total_solar_radiation_ly   \n",
       "0     02/01/2014  2014          32          1                      270   \n",
       "1     02/02/2014  2014          33          2                      207   \n",
       "2     02/03/2014  2014          34          3                      114   \n",
       "3     02/04/2014  2014          35          4                      237   \n",
       "4     02/05/2014  2014          36          5                      294   \n",
       "...          ...   ...         ...        ...                      ...   \n",
       "3388  05/13/2023  2023         133       3389                      330   \n",
       "3389  05/14/2023  2023         134       3390                      608   \n",
       "3390  05/15/2023  2023         135       3391                      335   \n",
       "3391  05/16/2023  2023         136       3392                      571   \n",
       "3392  05/17/2023  2023         137       3393                      476   \n",
       "\n",
       "                                                          \\\n",
       "     ave_mean_wind_speed_mph ave_mean_wind_direction_deg   \n",
       "0                       0.42                          93   \n",
       "1                       3.50                         146   \n",
       "2                       0.96                         232   \n",
       "3                       5.71                         120   \n",
       "4                       4.92                         122   \n",
       "...                      ...                         ...   \n",
       "3388                    3.08                         125   \n",
       "3389                    4.08                         103   \n",
       "3390                    1.79                          96   \n",
       "3391                    2.04                         187   \n",
       "3392                    2.54                         108   \n",
       "\n",
       "                                                                  \\\n",
       "     max_maximum_wind_gust_mph ave_average_air_temperature_deg_f   \n",
       "0                        12.00                              26.3   \n",
       "1                        20.00                              17.6   \n",
       "2                        15.00                              24.8   \n",
       "3                        16.00                              12.7   \n",
       "4                        17.00                               6.2   \n",
       "...                        ...                               ...   \n",
       "3388                     23.00                              59.0   \n",
       "3389                     34.00                              64.9   \n",
       "3390                     14.00                              60.2   \n",
       "3391                     19.00                              61.5   \n",
       "3392                     19.00                              61.7   \n",
       "\n",
       "                                        ...                    TGIL  \\\n",
       "     max_average_air_temperature_deg_f  ... ave_mean_wind_speed_mph   \n",
       "0                                 44.0  ...                    6.54   \n",
       "1                                 26.0  ...                    6.71   \n",
       "2                                 38.0  ...                    3.62   \n",
       "3                                 19.0  ...                    2.96   \n",
       "4                                 15.0  ...                    7.46   \n",
       "...                                ...  ...                     ...   \n",
       "3388                              70.0  ...                    4.46   \n",
       "3389                              81.0  ...                    3.25   \n",
       "3390                              74.0  ...                    2.92   \n",
       "3391                              80.0  ...                    3.17   \n",
       "3392                              77.0  ...                    2.25   \n",
       "\n",
       "                                                            \\\n",
       "     ave_mean_wind_direction_deg max_maximum_wind_gust_mph   \n",
       "0                            192                     21.00   \n",
       "1                            358                     20.00   \n",
       "2                             66                     11.00   \n",
       "3                             93                     16.00   \n",
       "4                            347                     23.00   \n",
       "...                          ...                       ...   \n",
       "3388                         134                     25.00   \n",
       "3389                         131                     29.00   \n",
       "3390                         218                     22.00   \n",
       "3391                         285                     12.00   \n",
       "3392                         318                     14.00   \n",
       "\n",
       "                                                                          \\\n",
       "     ave_average_air_temperature_deg_f max_average_air_temperature_deg_f   \n",
       "0                                 57.0                              65.0   \n",
       "1                                 36.7                              40.0   \n",
       "2                                 37.2                              45.0   \n",
       "3                                 37.9                              41.0   \n",
       "4                                 33.1                              40.0   \n",
       "...                                ...                               ...   \n",
       "3388                              76.4                              86.0   \n",
       "3389                              72.5                              85.0   \n",
       "3390                              75.6                              88.0   \n",
       "3391                              71.5                              78.0   \n",
       "3392                              70.5                              80.0   \n",
       "\n",
       "                                                                      \\\n",
       "     min_average_air_temperature_deg_f ave_average_relative_humidity   \n",
       "0                                 41.0                            92   \n",
       "1                                 34.0                            96   \n",
       "2                                 33.0                            88   \n",
       "3                                 35.0                            96   \n",
       "4                                 26.0                            69   \n",
       "...                                ...                           ...   \n",
       "3388                              68.0                            83   \n",
       "3389                              67.0                            94   \n",
       "3390                              68.0                            83   \n",
       "3391                              66.0                            85   \n",
       "3392                              64.0                            79   \n",
       "\n",
       "                                                                  \\\n",
       "     max_average_relative_humidity min_average_relative_humidity   \n",
       "0                               98                            79   \n",
       "1                               98                            91   \n",
       "2                               96                            71   \n",
       "3                              100                            88   \n",
       "4                              100                            56   \n",
       "...                            ...                           ...   \n",
       "3388                            96                            62   \n",
       "3389                           100                            65   \n",
       "3390                           100                            52   \n",
       "3391                           100                            65   \n",
       "3392                           100                            57   \n",
       "\n",
       "                             \n",
       "     total_precipitation_in  \n",
       "0                      0.42  \n",
       "1                      0.68  \n",
       "2                      0.00  \n",
       "3                      0.68  \n",
       "4                      0.01  \n",
       "...                     ...  \n",
       "3388                   0.27  \n",
       "3389                   0.65  \n",
       "3390                   0.06  \n",
       "3391                   0.01  \n",
       "3392                   0.00  \n",
       "\n",
       "[3393 rows x 33780 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The value is not present in the DataFrame.\n"
     ]
    }
   ],
   "source": [
    "def null_checker(data):\n",
    "    '''\n",
    "    Checks if -9999 value is in daataframe, converts columns to numeric to ensure higher accuracy \n",
    "    Inputs:\n",
    "    data : DataFrame to check, single level index\n",
    "    '''\n",
    "    # data copy and convert columns to float\n",
    "    js = data.copy()\n",
    "    js.drop(columns='date', inplace=True)\n",
    "    js =js[js.select_dtypes(include='object').columns].astype(float)\n",
    "    # null value to find\n",
    "    value = -9999\n",
    "    is_present = (js[-10:].values == value).any()\n",
    "    count = (js[-10:].values == value).sum()\n",
    "\n",
    "    if is_present:\n",
    "        print(f\"The value is present in the DataFrame.\\n{count}\")\n",
    "    else:\n",
    "        print(\"The value is not present in the DataFrame.\")\n",
    "        \n",
    "null_checker(json_df['WGOL'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>year</th>\n",
       "      <th>day_of_year</th>\n",
       "      <th>day_of_run</th>\n",
       "      <th>total_solar_radiation_ly</th>\n",
       "      <th>ave_mean_wind_speed_mph</th>\n",
       "      <th>ave_mean_wind_direction_deg</th>\n",
       "      <th>max_maximum_wind_gust_mph</th>\n",
       "      <th>ave_average_air_temperature_deg_f</th>\n",
       "      <th>max_average_air_temperature_deg_f</th>\n",
       "      <th>min_average_air_temperature_deg_f</th>\n",
       "      <th>ave_average_relative_humidity</th>\n",
       "      <th>max_average_relative_humidity</th>\n",
       "      <th>min_average_relative_humidity</th>\n",
       "      <th>total_precipitation_in</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>202</td>\n",
       "      <td>4.12</td>\n",
       "      <td>118</td>\n",
       "      <td>10.00</td>\n",
       "      <td>14.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>78</td>\n",
       "      <td>94</td>\n",
       "      <td>60</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>71</td>\n",
       "      <td>3.29</td>\n",
       "      <td>25</td>\n",
       "      <td>16.00</td>\n",
       "      <td>14.8</td>\n",
       "      <td>21.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>86</td>\n",
       "      <td>89</td>\n",
       "      <td>78</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>34</td>\n",
       "      <td>3</td>\n",
       "      <td>36</td>\n",
       "      <td>3.75</td>\n",
       "      <td>22</td>\n",
       "      <td>14.00</td>\n",
       "      <td>13.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>85</td>\n",
       "      <td>89</td>\n",
       "      <td>78</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>35</td>\n",
       "      <td>4</td>\n",
       "      <td>205</td>\n",
       "      <td>6.38</td>\n",
       "      <td>37</td>\n",
       "      <td>21.00</td>\n",
       "      <td>2.3</td>\n",
       "      <td>12.0</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>72</td>\n",
       "      <td>82</td>\n",
       "      <td>55</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>36</td>\n",
       "      <td>5</td>\n",
       "      <td>233</td>\n",
       "      <td>6.79</td>\n",
       "      <td>43</td>\n",
       "      <td>23.00</td>\n",
       "      <td>-3.6</td>\n",
       "      <td>5.0</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>60</td>\n",
       "      <td>70</td>\n",
       "      <td>46</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3388</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2023</td>\n",
       "      <td>133</td>\n",
       "      <td>3389</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3389</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2023</td>\n",
       "      <td>134</td>\n",
       "      <td>3390</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3390</th>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>135</td>\n",
       "      <td>3391</td>\n",
       "      <td>378</td>\n",
       "      <td>7.50</td>\n",
       "      <td>235</td>\n",
       "      <td>18.00</td>\n",
       "      <td>67.3</td>\n",
       "      <td>75.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>46</td>\n",
       "      <td>53</td>\n",
       "      <td>36</td>\n",
       "      <td>5.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3391</th>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>136</td>\n",
       "      <td>3392</td>\n",
       "      <td>360</td>\n",
       "      <td>8.21</td>\n",
       "      <td>120</td>\n",
       "      <td>21.00</td>\n",
       "      <td>56.7</td>\n",
       "      <td>66.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>88</td>\n",
       "      <td>100</td>\n",
       "      <td>57</td>\n",
       "      <td>0.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3392</th>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>137</td>\n",
       "      <td>3393</td>\n",
       "      <td>627</td>\n",
       "      <td>6.42</td>\n",
       "      <td>267</td>\n",
       "      <td>15.00</td>\n",
       "      <td>62.7</td>\n",
       "      <td>71.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>69</td>\n",
       "      <td>92</td>\n",
       "      <td>52</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3393 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            date  year day_of_year day_of_run total_solar_radiation_ly  \\\n",
       "0     02/01/2014  2014          32          1                      202   \n",
       "1     02/02/2014  2014          33          2                       71   \n",
       "2     02/03/2014  2014          34          3                       36   \n",
       "3     02/04/2014  2014          35          4                      205   \n",
       "4     02/05/2014  2014          36          5                      233   \n",
       "...          ...   ...         ...        ...                      ...   \n",
       "3388         NaN  2023         133       3389                      NaN   \n",
       "3389         NaN  2023         134       3390                      NaN   \n",
       "3390  05/15/2023  2023         135       3391                      378   \n",
       "3391  05/16/2023  2023         136       3392                      360   \n",
       "3392  05/17/2023  2023         137       3393                      627   \n",
       "\n",
       "     ave_mean_wind_speed_mph ave_mean_wind_direction_deg  \\\n",
       "0                       4.12                         118   \n",
       "1                       3.29                          25   \n",
       "2                       3.75                          22   \n",
       "3                       6.38                          37   \n",
       "4                       6.79                          43   \n",
       "...                      ...                         ...   \n",
       "3388                     NaN                         NaN   \n",
       "3389                     NaN                         NaN   \n",
       "3390                    7.50                         235   \n",
       "3391                    8.21                         120   \n",
       "3392                    6.42                         267   \n",
       "\n",
       "     max_maximum_wind_gust_mph ave_average_air_temperature_deg_f  \\\n",
       "0                        10.00                              14.0   \n",
       "1                        16.00                              14.8   \n",
       "2                        14.00                              13.0   \n",
       "3                        21.00                               2.3   \n",
       "4                        23.00                              -3.6   \n",
       "...                        ...                               ...   \n",
       "3388                       NaN                               NaN   \n",
       "3389                       NaN                               NaN   \n",
       "3390                     18.00                              67.3   \n",
       "3391                     21.00                              56.7   \n",
       "3392                     15.00                              62.7   \n",
       "\n",
       "     max_average_air_temperature_deg_f min_average_air_temperature_deg_f  \\\n",
       "0                                 22.0                               8.0   \n",
       "1                                 21.0                              12.0   \n",
       "2                                 22.0                               5.0   \n",
       "3                                 12.0                              -6.0   \n",
       "4                                  5.0                             -10.0   \n",
       "...                                ...                               ...   \n",
       "3388                               NaN                               NaN   \n",
       "3389                               NaN                               NaN   \n",
       "3390                              75.0                              62.0   \n",
       "3391                              66.0                              50.0   \n",
       "3392                              71.0                              56.0   \n",
       "\n",
       "     ave_average_relative_humidity max_average_relative_humidity  \\\n",
       "0                               78                            94   \n",
       "1                               86                            89   \n",
       "2                               85                            89   \n",
       "3                               72                            82   \n",
       "4                               60                            70   \n",
       "...                            ...                           ...   \n",
       "3388                           NaN                           NaN   \n",
       "3389                           NaN                           NaN   \n",
       "3390                            46                            53   \n",
       "3391                            88                           100   \n",
       "3392                            69                            92   \n",
       "\n",
       "     min_average_relative_humidity total_precipitation_in  \n",
       "0                               60                   0.00  \n",
       "1                               78                   0.00  \n",
       "2                               78                   0.00  \n",
       "3                               55                   0.00  \n",
       "4                               46                   0.00  \n",
       "...                            ...                    ...  \n",
       "3388                           NaN                    NaN  \n",
       "3389                           NaN                    NaN  \n",
       "3390                            36                   5.90  \n",
       "3391                            57                   0.68  \n",
       "3392                            52                   0.00  \n",
       "\n",
       "[3393 rows x 15 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# double checking\n",
    "json_df['WGOL']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of Stations: 2252\n"
     ]
    }
   ],
   "source": [
    "# Number of stations\n",
    "def number_of_stations(df, Return=True):\n",
    "    '''\n",
    "    Returns the number of columns in a multilevel index, also returns a list of the columns\n",
    "    df : multilevel index dataframe\n",
    "    Return : True  - set to False to only return nothing and only print\n",
    "    '''\n",
    "    col = df.columns.get_level_values(0).unique().tolist()\n",
    "    print(f'Number of Stations: {len(col)}')\n",
    "    if Return:\n",
    "        return col\n",
    "\n",
    "station_names = number_of_stations(json_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "19\n"
     ]
    }
   ],
   "source": [
    "# station index locator\n",
    "def station_name_locator(station):\n",
    "    '''\n",
    "    Function locates the index of a station in the list returned by number_of_stations\n",
    "    Used to find index location of certain stations in stations_names list\n",
    "    '''\n",
    "    counter = -1\n",
    "    for s in station_names:\n",
    "        counter +=1\n",
    "        if s == station:\n",
    "            print(counter)\n",
    "            \n",
    "station_name_locator('WGOL')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'WGOL'"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Double check\n",
    "station_names[19]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of Stations: 15\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['date',\n",
       " 'year',\n",
       " 'day_of_year',\n",
       " 'day_of_run',\n",
       " 'total_solar_radiation_ly',\n",
       " 'ave_mean_wind_speed_mph',\n",
       " 'ave_mean_wind_direction_deg',\n",
       " 'max_maximum_wind_gust_mph',\n",
       " 'ave_average_air_temperature_deg_f',\n",
       " 'max_average_air_temperature_deg_f',\n",
       " 'min_average_air_temperature_deg_f',\n",
       " 'ave_average_relative_humidity',\n",
       " 'max_average_relative_humidity',\n",
       " 'min_average_relative_humidity',\n",
       " 'total_precipitation_in']"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Select name from list: \n"
     ]
    }
   ],
   "source": [
    "def columCheck(df, inputs=False, Return=False):\n",
    "    '''\n",
    "    Returns the number of columns in a multilevel index at level 1, \n",
    "    also returns a list of the columns\n",
    "    '''\n",
    "    # list of every column at level1\n",
    "    col = df.columns.get_level_values(1).unique().tolist()\n",
    "    print(f'Number of Stations: {len(col)}')\n",
    "    display(col)\n",
    "    # list of columns that contain the input\n",
    "    if inputs:\n",
    "        i = inputs\n",
    "    else: \n",
    "        i = input('Select name from list: ')\n",
    "    if i:\n",
    "        val = [l for l in df.columns if i in l[1]]\n",
    "        print(f'Main columns with {i}\\n {val}')\n",
    "\n",
    "    if Return:\n",
    "        return col\n",
    "\n",
    "columCheck(json_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Drop stations that have no values\n",
    "    - These stations tend to have 1-5 days recorded in the most recent dataset but i will be dropping these as most don't cover enough days\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4.  Loading dates DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"10\" halign=\"left\">IIND</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"10\" halign=\"left\">TGIL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>year</th>\n",
       "      <th>day_of_year</th>\n",
       "      <th>day_of_run</th>\n",
       "      <th>total_solar_radiation_ly</th>\n",
       "      <th>ave_mean_wind_speed_mph</th>\n",
       "      <th>ave_mean_wind_direction_deg</th>\n",
       "      <th>max_maximum_wind_gust_mph</th>\n",
       "      <th>ave_average_air_temperature_deg_f</th>\n",
       "      <th>max_average_air_temperature_deg_f</th>\n",
       "      <th>...</th>\n",
       "      <th>ave_mean_wind_speed_mph</th>\n",
       "      <th>ave_mean_wind_direction_deg</th>\n",
       "      <th>max_maximum_wind_gust_mph</th>\n",
       "      <th>ave_average_air_temperature_deg_f</th>\n",
       "      <th>max_average_air_temperature_deg_f</th>\n",
       "      <th>min_average_air_temperature_deg_f</th>\n",
       "      <th>ave_average_relative_humidity</th>\n",
       "      <th>max_average_relative_humidity</th>\n",
       "      <th>min_average_relative_humidity</th>\n",
       "      <th>total_precipitation_in</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>270</td>\n",
       "      <td>0.42</td>\n",
       "      <td>93</td>\n",
       "      <td>12.00</td>\n",
       "      <td>26.3</td>\n",
       "      <td>44.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.54</td>\n",
       "      <td>192</td>\n",
       "      <td>21.00</td>\n",
       "      <td>57.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>92</td>\n",
       "      <td>98</td>\n",
       "      <td>79</td>\n",
       "      <td>0.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>207</td>\n",
       "      <td>3.50</td>\n",
       "      <td>146</td>\n",
       "      <td>20.00</td>\n",
       "      <td>17.6</td>\n",
       "      <td>26.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.71</td>\n",
       "      <td>358</td>\n",
       "      <td>20.00</td>\n",
       "      <td>36.7</td>\n",
       "      <td>40.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>96</td>\n",
       "      <td>98</td>\n",
       "      <td>91</td>\n",
       "      <td>0.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>34</td>\n",
       "      <td>3</td>\n",
       "      <td>114</td>\n",
       "      <td>0.96</td>\n",
       "      <td>232</td>\n",
       "      <td>15.00</td>\n",
       "      <td>24.8</td>\n",
       "      <td>38.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.62</td>\n",
       "      <td>66</td>\n",
       "      <td>11.00</td>\n",
       "      <td>37.2</td>\n",
       "      <td>45.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>88</td>\n",
       "      <td>96</td>\n",
       "      <td>71</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>35</td>\n",
       "      <td>4</td>\n",
       "      <td>237</td>\n",
       "      <td>5.71</td>\n",
       "      <td>120</td>\n",
       "      <td>16.00</td>\n",
       "      <td>12.7</td>\n",
       "      <td>19.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.96</td>\n",
       "      <td>93</td>\n",
       "      <td>16.00</td>\n",
       "      <td>37.9</td>\n",
       "      <td>41.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>96</td>\n",
       "      <td>100</td>\n",
       "      <td>88</td>\n",
       "      <td>0.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>36</td>\n",
       "      <td>5</td>\n",
       "      <td>294</td>\n",
       "      <td>4.92</td>\n",
       "      <td>122</td>\n",
       "      <td>17.00</td>\n",
       "      <td>6.2</td>\n",
       "      <td>15.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7.46</td>\n",
       "      <td>347</td>\n",
       "      <td>23.00</td>\n",
       "      <td>33.1</td>\n",
       "      <td>40.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>69</td>\n",
       "      <td>100</td>\n",
       "      <td>56</td>\n",
       "      <td>0.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3388</th>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>133</td>\n",
       "      <td>3389</td>\n",
       "      <td>330</td>\n",
       "      <td>3.08</td>\n",
       "      <td>125</td>\n",
       "      <td>23.00</td>\n",
       "      <td>59.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.46</td>\n",
       "      <td>134</td>\n",
       "      <td>25.00</td>\n",
       "      <td>76.4</td>\n",
       "      <td>86.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>83</td>\n",
       "      <td>96</td>\n",
       "      <td>62</td>\n",
       "      <td>0.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3389</th>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>134</td>\n",
       "      <td>3390</td>\n",
       "      <td>608</td>\n",
       "      <td>4.08</td>\n",
       "      <td>103</td>\n",
       "      <td>34.00</td>\n",
       "      <td>64.9</td>\n",
       "      <td>81.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.25</td>\n",
       "      <td>131</td>\n",
       "      <td>29.00</td>\n",
       "      <td>72.5</td>\n",
       "      <td>85.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>94</td>\n",
       "      <td>100</td>\n",
       "      <td>65</td>\n",
       "      <td>0.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3390</th>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>135</td>\n",
       "      <td>3391</td>\n",
       "      <td>335</td>\n",
       "      <td>1.79</td>\n",
       "      <td>96</td>\n",
       "      <td>14.00</td>\n",
       "      <td>60.2</td>\n",
       "      <td>74.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.92</td>\n",
       "      <td>218</td>\n",
       "      <td>22.00</td>\n",
       "      <td>75.6</td>\n",
       "      <td>88.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>83</td>\n",
       "      <td>100</td>\n",
       "      <td>52</td>\n",
       "      <td>0.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3391</th>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>136</td>\n",
       "      <td>3392</td>\n",
       "      <td>571</td>\n",
       "      <td>2.04</td>\n",
       "      <td>187</td>\n",
       "      <td>19.00</td>\n",
       "      <td>61.5</td>\n",
       "      <td>80.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.17</td>\n",
       "      <td>285</td>\n",
       "      <td>12.00</td>\n",
       "      <td>71.5</td>\n",
       "      <td>78.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>85</td>\n",
       "      <td>100</td>\n",
       "      <td>65</td>\n",
       "      <td>0.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3392</th>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>137</td>\n",
       "      <td>3393</td>\n",
       "      <td>476</td>\n",
       "      <td>2.54</td>\n",
       "      <td>108</td>\n",
       "      <td>19.00</td>\n",
       "      <td>61.7</td>\n",
       "      <td>77.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.25</td>\n",
       "      <td>318</td>\n",
       "      <td>14.00</td>\n",
       "      <td>70.5</td>\n",
       "      <td>80.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>79</td>\n",
       "      <td>100</td>\n",
       "      <td>57</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3393 rows × 33780 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            IIND                                                        \\\n",
       "            date  year day_of_year day_of_run total_solar_radiation_ly   \n",
       "0     02/01/2014  2014          32          1                      270   \n",
       "1     02/02/2014  2014          33          2                      207   \n",
       "2     02/03/2014  2014          34          3                      114   \n",
       "3     02/04/2014  2014          35          4                      237   \n",
       "4     02/05/2014  2014          36          5                      294   \n",
       "...          ...   ...         ...        ...                      ...   \n",
       "3388  05/13/2023  2023         133       3389                      330   \n",
       "3389  05/14/2023  2023         134       3390                      608   \n",
       "3390  05/15/2023  2023         135       3391                      335   \n",
       "3391  05/16/2023  2023         136       3392                      571   \n",
       "3392  05/17/2023  2023         137       3393                      476   \n",
       "\n",
       "                                                          \\\n",
       "     ave_mean_wind_speed_mph ave_mean_wind_direction_deg   \n",
       "0                       0.42                          93   \n",
       "1                       3.50                         146   \n",
       "2                       0.96                         232   \n",
       "3                       5.71                         120   \n",
       "4                       4.92                         122   \n",
       "...                      ...                         ...   \n",
       "3388                    3.08                         125   \n",
       "3389                    4.08                         103   \n",
       "3390                    1.79                          96   \n",
       "3391                    2.04                         187   \n",
       "3392                    2.54                         108   \n",
       "\n",
       "                                                                  \\\n",
       "     max_maximum_wind_gust_mph ave_average_air_temperature_deg_f   \n",
       "0                        12.00                              26.3   \n",
       "1                        20.00                              17.6   \n",
       "2                        15.00                              24.8   \n",
       "3                        16.00                              12.7   \n",
       "4                        17.00                               6.2   \n",
       "...                        ...                               ...   \n",
       "3388                     23.00                              59.0   \n",
       "3389                     34.00                              64.9   \n",
       "3390                     14.00                              60.2   \n",
       "3391                     19.00                              61.5   \n",
       "3392                     19.00                              61.7   \n",
       "\n",
       "                                        ...                    TGIL  \\\n",
       "     max_average_air_temperature_deg_f  ... ave_mean_wind_speed_mph   \n",
       "0                                 44.0  ...                    6.54   \n",
       "1                                 26.0  ...                    6.71   \n",
       "2                                 38.0  ...                    3.62   \n",
       "3                                 19.0  ...                    2.96   \n",
       "4                                 15.0  ...                    7.46   \n",
       "...                                ...  ...                     ...   \n",
       "3388                              70.0  ...                    4.46   \n",
       "3389                              81.0  ...                    3.25   \n",
       "3390                              74.0  ...                    2.92   \n",
       "3391                              80.0  ...                    3.17   \n",
       "3392                              77.0  ...                    2.25   \n",
       "\n",
       "                                                            \\\n",
       "     ave_mean_wind_direction_deg max_maximum_wind_gust_mph   \n",
       "0                            192                     21.00   \n",
       "1                            358                     20.00   \n",
       "2                             66                     11.00   \n",
       "3                             93                     16.00   \n",
       "4                            347                     23.00   \n",
       "...                          ...                       ...   \n",
       "3388                         134                     25.00   \n",
       "3389                         131                     29.00   \n",
       "3390                         218                     22.00   \n",
       "3391                         285                     12.00   \n",
       "3392                         318                     14.00   \n",
       "\n",
       "                                                                          \\\n",
       "     ave_average_air_temperature_deg_f max_average_air_temperature_deg_f   \n",
       "0                                 57.0                              65.0   \n",
       "1                                 36.7                              40.0   \n",
       "2                                 37.2                              45.0   \n",
       "3                                 37.9                              41.0   \n",
       "4                                 33.1                              40.0   \n",
       "...                                ...                               ...   \n",
       "3388                              76.4                              86.0   \n",
       "3389                              72.5                              85.0   \n",
       "3390                              75.6                              88.0   \n",
       "3391                              71.5                              78.0   \n",
       "3392                              70.5                              80.0   \n",
       "\n",
       "                                                                      \\\n",
       "     min_average_air_temperature_deg_f ave_average_relative_humidity   \n",
       "0                                 41.0                            92   \n",
       "1                                 34.0                            96   \n",
       "2                                 33.0                            88   \n",
       "3                                 35.0                            96   \n",
       "4                                 26.0                            69   \n",
       "...                                ...                           ...   \n",
       "3388                              68.0                            83   \n",
       "3389                              67.0                            94   \n",
       "3390                              68.0                            83   \n",
       "3391                              66.0                            85   \n",
       "3392                              64.0                            79   \n",
       "\n",
       "                                                                  \\\n",
       "     max_average_relative_humidity min_average_relative_humidity   \n",
       "0                               98                            79   \n",
       "1                               98                            91   \n",
       "2                               96                            71   \n",
       "3                              100                            88   \n",
       "4                              100                            56   \n",
       "...                            ...                           ...   \n",
       "3388                            96                            62   \n",
       "3389                           100                            65   \n",
       "3390                           100                            52   \n",
       "3391                           100                            65   \n",
       "3392                           100                            57   \n",
       "\n",
       "                             \n",
       "     total_precipitation_in  \n",
       "0                      0.42  \n",
       "1                      0.68  \n",
       "2                      0.00  \n",
       "3                      0.68  \n",
       "4                      0.01  \n",
       "...                     ...  \n",
       "3388                   0.27  \n",
       "3389                   0.65  \n",
       "3390                   0.06  \n",
       "3391                   0.01  \n",
       "3392                   0.00  \n",
       "\n",
       "[3393 rows x 33780 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Parallel(n_jobs=-1)]: Using backend LokyBackend with 8 concurrent workers.\n",
      "[Parallel(n_jobs=-1)]: Done  34 tasks      | elapsed:  2.5min\n",
      "[Parallel(n_jobs=-1)]: Done 108 out of 108 | elapsed:  6.2min finished\n"
     ]
    }
   ],
   "source": [
    "# Run panda() function in parallel\n",
    "results = Parallel(n_jobs=-1, verbose=1)(\n",
    "    delayed(Panda)(files, True) for files in json_files\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>IIND</th>\n",
       "      <th>HAHU</th>\n",
       "      <th>OWIL</th>\n",
       "      <th>MPHL</th>\n",
       "      <th>CALT</th>\n",
       "      <th>MPIS</th>\n",
       "      <th>TLEW</th>\n",
       "      <th>XMAG</th>\n",
       "      <th>CHOM</th>\n",
       "      <th>HMOL</th>\n",
       "      <th>...</th>\n",
       "      <th>OLAV</th>\n",
       "      <th>CMVA</th>\n",
       "      <th>AOPE</th>\n",
       "      <th>CSKU</th>\n",
       "      <th>MANT</th>\n",
       "      <th>OSIL</th>\n",
       "      <th>ACOA</th>\n",
       "      <th>WKOS</th>\n",
       "      <th>DBLB</th>\n",
       "      <th>TGIL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3388</th>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3389</th>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3390</th>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3391</th>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3392</th>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3393 rows × 2252 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            IIND        HAHU        OWIL        MPHL        CALT        MPIS  \\\n",
       "0     02/01/2014  02/01/2014  02/01/2014  02/01/2014  02/01/2014  02/01/2014   \n",
       "1     02/02/2014  02/02/2014  02/02/2014  02/02/2014  02/02/2014  02/02/2014   \n",
       "2     02/03/2014  02/03/2014  02/03/2014  02/03/2014  02/03/2014  02/03/2014   \n",
       "3     02/04/2014  02/04/2014  02/04/2014  02/04/2014  02/04/2014  02/04/2014   \n",
       "4     02/05/2014  02/05/2014  02/05/2014  02/05/2014  02/05/2014  02/05/2014   \n",
       "...          ...         ...         ...         ...         ...         ...   \n",
       "3388  05/13/2023  05/13/2023  05/13/2023  05/13/2023  05/13/2023         NaN   \n",
       "3389  05/14/2023  05/14/2023  05/14/2023  05/14/2023  05/14/2023         NaN   \n",
       "3390  05/15/2023  05/15/2023  05/15/2023  05/15/2023  05/15/2023         NaN   \n",
       "3391  05/16/2023  05/16/2023  05/16/2023  05/16/2023  05/16/2023         NaN   \n",
       "3392  05/17/2023  05/17/2023  05/17/2023  05/17/2023  05/17/2023         NaN   \n",
       "\n",
       "            TLEW        XMAG        CHOM HMOL  ...        OLAV CMVA  \\\n",
       "0     02/01/2014  02/01/2014  02/01/2014  NaN  ...  02/01/2014  NaN   \n",
       "1     02/02/2014  02/02/2014  02/02/2014  NaN  ...  02/02/2014  NaN   \n",
       "2     02/03/2014  02/03/2014  02/03/2014  NaN  ...  02/03/2014  NaN   \n",
       "3     02/04/2014  02/04/2014  02/04/2014  NaN  ...  02/04/2014  NaN   \n",
       "4     02/05/2014  02/05/2014  02/05/2014  NaN  ...  02/05/2014  NaN   \n",
       "...          ...         ...         ...  ...  ...         ...  ...   \n",
       "3388  05/13/2023  05/13/2023         NaN  NaN  ...  05/13/2023  NaN   \n",
       "3389  05/14/2023  05/14/2023         NaN  NaN  ...  05/14/2023  NaN   \n",
       "3390  05/15/2023  05/15/2023         NaN  NaN  ...  05/15/2023  NaN   \n",
       "3391  05/16/2023  05/16/2023         NaN  NaN  ...  05/16/2023  NaN   \n",
       "3392  05/17/2023  05/17/2023         NaN  NaN  ...  05/17/2023  NaN   \n",
       "\n",
       "            AOPE        CSKU        MANT        OSIL        ACOA        WKOS  \\\n",
       "0     02/01/2014  02/01/2014  02/01/2014  02/01/2014  02/01/2014  02/01/2014   \n",
       "1     02/02/2014  02/02/2014  02/02/2014  02/02/2014  02/02/2014  02/02/2014   \n",
       "2     02/03/2014  02/03/2014  02/03/2014  02/03/2014  02/03/2014  02/03/2014   \n",
       "3     02/04/2014  02/04/2014  02/04/2014  02/04/2014  02/04/2014  02/04/2014   \n",
       "4     02/05/2014  02/05/2014  02/05/2014  02/05/2014  02/05/2014  02/05/2014   \n",
       "...          ...         ...         ...         ...         ...         ...   \n",
       "3388  05/13/2023  05/13/2023  05/13/2023  05/13/2023  05/13/2023  05/13/2023   \n",
       "3389  05/14/2023  05/14/2023  05/14/2023  05/14/2023  05/14/2023  05/14/2023   \n",
       "3390  05/15/2023  05/15/2023  05/15/2023  05/15/2023  05/15/2023  05/15/2023   \n",
       "3391  05/16/2023  05/16/2023  05/16/2023  05/16/2023  05/16/2023  05/16/2023   \n",
       "3392  05/17/2023  05/17/2023  05/17/2023  05/17/2023  05/17/2023  05/17/2023   \n",
       "\n",
       "            DBLB        TGIL  \n",
       "0     02/01/2014  02/01/2014  \n",
       "1     02/02/2014  02/02/2014  \n",
       "2     02/03/2014  02/03/2014  \n",
       "3     02/04/2014  02/04/2014  \n",
       "4     02/05/2014  02/05/2014  \n",
       "...          ...         ...  \n",
       "3388  05/13/2023  05/13/2023  \n",
       "3389  05/14/2023  05/14/2023  \n",
       "3390  05/15/2023  05/15/2023  \n",
       "3391  05/16/2023  05/16/2023  \n",
       "3392  05/17/2023  05/17/2023  \n",
       "\n",
       "[3393 rows x 2252 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date_df = pd.concat(results, axis=1)\n",
    "date_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total File Size: 0.43 GB\n"
     ]
    }
   ],
   "source": [
    "# Check file size\n",
    "total_memory_gb = date_df.memory_usage(deep=True).sum() / 1024**3\n",
    "print(f\"Total File Size: {total_memory_gb:.2f} GB\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 5. Loading NESSID dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Location</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>NESS ID</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>NWS ID</th>\n",
       "      <th>Elevation</th>\n",
       "      <th>Agency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>NSCK</th>\n",
       "      <td>Sand Creek North Dakota</td>\n",
       "      <td>46° 32' 44\"</td>\n",
       "      <td>32553302</td>\n",
       "      <td>103° 31' 06\"</td>\n",
       "      <td>323804</td>\n",
       "      <td>2700 ft.</td>\n",
       "      <td>BLM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WSND</th>\n",
       "      <td>Sand Island Wisconsin</td>\n",
       "      <td>47° 00' 11\"</td>\n",
       "      <td>3275B4F8</td>\n",
       "      <td>90° 56' 11\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>615 ft.</td>\n",
       "      <td>NPS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>XBEW</th>\n",
       "      <td>Bear Wallow New Mexico</td>\n",
       "      <td>33° 27' 18\"</td>\n",
       "      <td>326C15C2</td>\n",
       "      <td>108° 39' 54\"</td>\n",
       "      <td>292010</td>\n",
       "      <td>9953 ft.</td>\n",
       "      <td>USFS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CLOS</th>\n",
       "      <td>Los Pinos Creek Colorado</td>\n",
       "      <td>38° 12' 48\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>106° 45' 27\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9600 ft.</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HLUA</th>\n",
       "      <td>Lua Makika Hawaii</td>\n",
       "      <td>20° 33' 45\"</td>\n",
       "      <td>327906EA</td>\n",
       "      <td>156° 33' 47\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1100 ft.</td>\n",
       "      <td>S&amp;amp;PF</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TMCK</th>\n",
       "      <td>McKittrick Texas</td>\n",
       "      <td>31° 58' 39\"</td>\n",
       "      <td>3237015E</td>\n",
       "      <td>104° 45' 06\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5004 ft.</td>\n",
       "      <td>NPS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBRA</th>\n",
       "      <td>Brasstown #1 Georgia</td>\n",
       "      <td>34° 48' 10\"</td>\n",
       "      <td>327357C4</td>\n",
       "      <td>83° 42' 36\"</td>\n",
       "      <td>090701</td>\n",
       "      <td>3280 ft.</td>\n",
       "      <td>USFS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NWIL</th>\n",
       "      <td>Williams Lookout North Dakota</td>\n",
       "      <td>46° 21' 43\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>102° 03' 43\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2375 ft.</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NKAN</th>\n",
       "      <td>Kane Springs Nevada</td>\n",
       "      <td>37° 15' 02\"</td>\n",
       "      <td>325CC5DA</td>\n",
       "      <td>114° 42' 27\"</td>\n",
       "      <td>261604</td>\n",
       "      <td>4590 ft.</td>\n",
       "      <td>BLM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WHAL</th>\n",
       "      <td>Half Moon Wyoming</td>\n",
       "      <td>42° 54' 53\"</td>\n",
       "      <td>32317700</td>\n",
       "      <td>109° 44' 50\"</td>\n",
       "      <td>481309</td>\n",
       "      <td>8530 ft.</td>\n",
       "      <td>USFS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2868 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Location     Latitude   NESS ID     Longitude   \\\n",
       "NSCK        Sand Creek North Dakota  46° 32' 44\"  32553302  103° 31' 06\"   \n",
       "WSND          Sand Island Wisconsin  47° 00' 11\"  3275B4F8   90° 56' 11\"   \n",
       "XBEW         Bear Wallow New Mexico  33° 27' 18\"  326C15C2  108° 39' 54\"   \n",
       "CLOS       Los Pinos Creek Colorado  38° 12' 48\"       NaN  106° 45' 27\"   \n",
       "HLUA              Lua Makika Hawaii  20° 33' 45\"  327906EA  156° 33' 47\"   \n",
       "...                             ...          ...       ...           ...   \n",
       "TMCK               McKittrick Texas  31° 58' 39\"  3237015E  104° 45' 06\"   \n",
       "GBRA           Brasstown #1 Georgia  34° 48' 10\"  327357C4   83° 42' 36\"   \n",
       "NWIL  Williams Lookout North Dakota  46° 21' 43\"       NaN  102° 03' 43\"   \n",
       "NKAN            Kane Springs Nevada  37° 15' 02\"  325CC5DA  114° 42' 27\"   \n",
       "WHAL              Half Moon Wyoming  42° 54' 53\"  32317700  109° 44' 50\"   \n",
       "\n",
       "     NWS ID  Elevation    Agency   \n",
       "NSCK  323804   2700 ft.       BLM  \n",
       "WSND     NaN    615 ft.       NPS  \n",
       "XBEW  292010   9953 ft.      USFS  \n",
       "CLOS     NaN   9600 ft.       NaN  \n",
       "HLUA     NaN   1100 ft.  S&amp;PF  \n",
       "...      ...        ...       ...  \n",
       "TMCK     NaN   5004 ft.       NPS  \n",
       "GBRA  090701   3280 ft.      USFS  \n",
       "NWIL     NaN   2375 ft.       NaN  \n",
       "NKAN  261604   4590 ft.       BLM  \n",
       "WHAL  481309   8530 ft.      USFS  \n",
       "\n",
       "[2868 rows x 7 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nessid = pd.read_csv('nessid.csv', index_col=0)\n",
    "nessid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Location</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>NESS ID</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>NWS ID</th>\n",
       "      <th>Elevation</th>\n",
       "      <th>Agency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>WGOL</th>\n",
       "      <td>Gold Mountain Washington</td>\n",
       "      <td>48° 10' 50\"</td>\n",
       "      <td>52118404</td>\n",
       "      <td>118° 27' 49\"</td>\n",
       "      <td>452027</td>\n",
       "      <td>4686 ft.</td>\n",
       "      <td>BIA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Location     Latitude   NESS ID     Longitude  NWS ID   \\\n",
       "WGOL  Gold Mountain Washington  48° 10' 50\"  52118404  118° 27' 49\"  452027   \n",
       "\n",
       "     Elevation  Agency   \n",
       "WGOL   4686 ft.     BIA  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nessid[nessid[\"NESS ID \"]== '52118404']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 6. Creating list of stations and their NESS ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2252\n"
     ]
    }
   ],
   "source": [
    "stations = {}\n",
    "for col in list(date_df.columns):\n",
    "    stations[col] = nessid[nessid.index == col.upper()]['NESS ID '].values[0]\n",
    "print(len(stations))  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove all Stations that are completly empty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of empty stations: 313\n"
     ]
    }
   ],
   "source": [
    "# checks each date column is entire date column is nulls then it is empty and dropped\n",
    "final_count = 0\n",
    "empty_stations = {}\n",
    "for name, value in date_df.isna().sum().items():\n",
    "    if value == len(date_df):\n",
    "        final_count +=1\n",
    "        empty_stations[name] = value\n",
    "        \n",
    "# Adding NESS ID as value\n",
    "for key, value in empty_stations.items():\n",
    "    empty_stations[key] = nessid[nessid.index == key.upper()]['NESS ID '].values[0]\n",
    "print(f\"Number of empty stations: {final_count}\")        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'HMOL': 'AAB6D15A',\n",
       " 'MNWL': '32D77244',\n",
       " 'TFHD': nan,\n",
       " 'NBRP': '32D08E26',\n",
       " 'TKYL': '32D03DA8',\n",
       " 'CCMT': 'AAB5D654',\n",
       " 'CSMR': nan,\n",
       " 'CLSK': nan,\n",
       " 'ATEL': '3390720E',\n",
       " 'WBRK': nan,\n",
       " 'TDRI': '32D04B38',\n",
       " 'FSUW': nan,\n",
       " 'PTUM': nan,\n",
       " 'CSJK': nan,\n",
       " 'CBML': '32B5385A',\n",
       " 'CSMP': 'CF1004EE',\n",
       " 'MCRZ': '32A0B0C2',\n",
       " 'COAR': '32B525FE',\n",
       " 'IDEN': '326FC4A4',\n",
       " 'CRFU': 'CF100A3C',\n",
       " 'ALGC': '9A70305C',\n",
       " 'CSLO': 'CA2384AE',\n",
       " 'MSOF': nan,\n",
       " 'WLAN': '32458712',\n",
       " 'MDRD': '3282C248',\n",
       " 'ADEC': '3962A300',\n",
       " 'ANUU': '3962C6E6',\n",
       " 'PASE': nan,\n",
       " 'WBAI': nan,\n",
       " 'GPEM': 'F8E017BC',\n",
       " 'VFDE': '3279707A',\n",
       " 'MFIN': '326F51C6',\n",
       " 'CRMA': nan,\n",
       " 'COLV': '3287D6FE',\n",
       " 'WTCR': '30001528',\n",
       " 'UFMB': '329337A8',\n",
       " 'AMCK': 'FA6035AC',\n",
       " 'UDGS': '32B37B9E',\n",
       " 'NHOH': '407203EC',\n",
       " 'HPHE': '32D6803A',\n",
       " 'MKET': 'FA65E404',\n",
       " 'UBCS': '32B6A4E4',\n",
       " 'CLFL': '923046D2',\n",
       " 'NASH': '83780484',\n",
       " 'TPSF': '32A1E244',\n",
       " 'ANUN': '3962B076',\n",
       " 'WSND': '3275B4F8',\n",
       " 'NMRN': '32A1F132',\n",
       " 'CF31': 'CA48D548',\n",
       " 'PRIE': '3265E31A',\n",
       " 'AGOL': '32D06DD4',\n",
       " 'CGAV': '32B65460',\n",
       " 'CDCB': nan,\n",
       " 'CRNP': nan,\n",
       " 'VFLW': '3281D630',\n",
       " 'ASDH': '32B3C6C2',\n",
       " 'CKLM': nan,\n",
       " 'AGTR': '32B3B052',\n",
       " 'WLCR': nan,\n",
       " 'FJAX': nan,\n",
       " 'NDEC': '32D754A8',\n",
       " 'MCMD': '5730062E',\n",
       " 'CGND': nan,\n",
       " 'WENT': '32417C7E',\n",
       " 'WAPP': '32A28E7E',\n",
       " 'VFEO': '328232CC',\n",
       " 'MZRT': '324AE466',\n",
       " 'HNEN': '32D64524',\n",
       " 'CMLL': nan,\n",
       " 'IROU': '3239E6C4',\n",
       " 'CBNR': 'CA21F7CA',\n",
       " 'CSQD': '83792092',\n",
       " 'OMRR': '32B6F498',\n",
       " 'ARUT': '3960157A',\n",
       " 'UDSP': '3257C672',\n",
       " 'MCYC': '3241915E',\n",
       " 'NWSB': '32349332',\n",
       " 'PFTR': nan,\n",
       " 'NWIL': nan,\n",
       " 'CPUR': '32A206B8',\n",
       " 'PDIS': nan,\n",
       " 'MREX': '0800A33A',\n",
       " 'AGRO': '32B3C810',\n",
       " 'GOKN': '32B00A00',\n",
       " 'VFSO': '3274E67E',\n",
       " 'HKLL': '32792006',\n",
       " 'MPI2': nan,\n",
       " 'VAPP': nan,\n",
       " 'GRIC': 'F8E0196E',\n",
       " 'PBRK': nan,\n",
       " 'HUSG': '3181742E',\n",
       " 'PCWP': nan,\n",
       " 'COPS': 'CA215732',\n",
       " 'MTRO': 'AAB5F0B8',\n",
       " 'CCHH': nan,\n",
       " 'FLAK': nan,\n",
       " 'IBAI': 'FA66B376',\n",
       " 'MSPO': '3248D408',\n",
       " 'OGEL': '6000026E',\n",
       " 'SSFS': nan,\n",
       " 'MSKB': 'AAB044F6',\n",
       " 'MNMA': '3274D3E4',\n",
       " 'ABRB': '32B3BE80',\n",
       " 'ALOS': '12603368',\n",
       " 'IKEL': '323734C4',\n",
       " 'TARN': nan,\n",
       " 'MBIG': '52110210',\n",
       " 'ADBY': '39622514',\n",
       " 'CBLV': 'CA801094',\n",
       " 'MPLW': '080010B4',\n",
       " 'SRCW': '32D5515C',\n",
       " 'OMUL': nan,\n",
       " 'GOKW': nan,\n",
       " 'WHAG': '32617342',\n",
       " 'CCDP': '32B32530',\n",
       " 'VELK': '900043E6',\n",
       " 'ICRS': nan,\n",
       " 'WSHA': 'AAB716BE',\n",
       " 'ACSG': '5214304A',\n",
       " 'UWIL': '32B6B792',\n",
       " 'HMRG': '3266C2F8',\n",
       " 'CCRP': '32A1AF9C',\n",
       " 'UCRC': '32B6AA36',\n",
       " 'UGRC': nan,\n",
       " 'IBWF': '32B163CE',\n",
       " 'CKNW': '32B6FA4A',\n",
       " 'XBLM': '5213D18C',\n",
       " 'TCOV': '32B79384',\n",
       " 'ABRI': '32B65AB2',\n",
       " 'UKFO': '3237C440',\n",
       " 'UDGN': '32B3754C',\n",
       " 'CJAP': nan,\n",
       " 'CGME': nan,\n",
       " 'OSLG': nan,\n",
       " 'CNVF': '52014684',\n",
       " 'VFAI': '3274E67E',\n",
       " 'AWUP': 'FA65B478',\n",
       " 'NRUV': '33355574',\n",
       " 'IEAG': '326FD7D2',\n",
       " 'OCHD': nan,\n",
       " 'CWIR': '923055A4',\n",
       " 'SMCR': 'AAAB2526',\n",
       " 'JMID': 'FF10372A',\n",
       " 'CNST': nan,\n",
       " 'GBAI': nan,\n",
       " 'HMAP': '324C075A',\n",
       " 'MEF3': '328E632C',\n",
       " 'ALIR': 'FA509324',\n",
       " 'MPTX': '8377411C',\n",
       " 'AML2': nan,\n",
       " 'TSN2': '3335D360',\n",
       " 'CSRE': nan,\n",
       " 'CBKE': '32B5A3EA',\n",
       " 'ALYN': '32B661FA',\n",
       " 'MGAT': '16C4536A',\n",
       " 'CMTH': '32A151CA',\n",
       " 'VKEA': '9000F068',\n",
       " 'UBTC': '32B5C60C',\n",
       " 'WGAN': nan,\n",
       " 'ACHA': '3247221E',\n",
       " 'AONE': '32B3ADF6',\n",
       " 'WOHA': 'FA65211A',\n",
       " 'MCPE': 'ADB00D40',\n",
       " 'NBAK': 'FA62B64C',\n",
       " 'CROB': '92303042',\n",
       " 'SMAR': 'AAA26070',\n",
       " 'OBCP': nan,\n",
       " 'CDV2': 'CA44F238',\n",
       " 'TJCR': '32D0CD2C',\n",
       " 'SORA': '32B73DAE',\n",
       " 'WSTC': '32A293DA',\n",
       " 'CCDA': nan,\n",
       " 'AGLA': '3962D590',\n",
       " 'CFLB': '92300B0A',\n",
       " 'ACHP': '3962661E',\n",
       " 'CHRM': nan,\n",
       " 'ASCL': '323C02F6',\n",
       " 'ALTB': '39623662',\n",
       " 'UDGW': '32B385C8',\n",
       " 'GLAW': 'F8E00A18',\n",
       " 'ICTT': 'AAC4A792',\n",
       " 'FBLA': nan,\n",
       " 'MMGR': 'AAB6574E',\n",
       " 'MYAA': '3273A740',\n",
       " 'AKUG': '396203F8',\n",
       " 'PYEL': nan,\n",
       " 'VFNO': '326A70EA',\n",
       " 'TLAC': '32D0A8CA',\n",
       " 'CCAA': '325C7654',\n",
       " 'CPRT': nan,\n",
       " 'LBAR': nan,\n",
       " 'WEGL': '32A280AC',\n",
       " 'MBLH': '32B277B6',\n",
       " 'XSLB': 'AAB621DE',\n",
       " 'CORE': nan,\n",
       " 'WGUE': 'C55AB568',\n",
       " 'NWAM': '8374B696',\n",
       " 'WJIM': nan,\n",
       " 'ALNE': '396008DE',\n",
       " 'ATRP': '3242534E',\n",
       " 'CRAM': nan,\n",
       " 'CGA2': '3239C028',\n",
       " 'HKPD': '32B2C438',\n",
       " 'YBOL': '32B0E720',\n",
       " 'CKYR': 'AA110350',\n",
       " 'TBIB': '3237015E',\n",
       " 'MRLK': nan,\n",
       " 'CLDK': '32B33894',\n",
       " 'WWRX': '3244C6E2',\n",
       " 'WBBL': '32A18970',\n",
       " 'WLNC': nan,\n",
       " 'ADIX': '32B26A12',\n",
       " 'CCWC': nan,\n",
       " 'ASRS': '32B17E6A',\n",
       " 'MCSS': '3290E6CE',\n",
       " 'NMCK': '32D0EBC0',\n",
       " 'ATRI': '32A179F4',\n",
       " 'AREI': '12609390',\n",
       " 'CSED': '32B649C4',\n",
       " 'JWAL': '8377F292',\n",
       " 'VHIL': nan,\n",
       " 'CRTL': 'FA626024',\n",
       " 'WPDW': '32B23A6E',\n",
       " 'CTEP': 'CF101798',\n",
       " 'ULAK': '327E47D4',\n",
       " 'MBOG': '32B108FA',\n",
       " 'TSMW': '32D02EDE',\n",
       " 'IRRP': '3263A0DE',\n",
       " 'AWLR': '32B66F28',\n",
       " 'CTAL': '920040A2',\n",
       " 'WWLC': nan,\n",
       " 'HP17': '3279630C',\n",
       " 'PDOL': nan,\n",
       " 'CMTR': '923005D8',\n",
       " 'OMAU': '6140506C',\n",
       " 'FAVN': nan,\n",
       " 'NBUX': nan,\n",
       " 'CALC': '32A16450',\n",
       " 'CSC3': '9330E788',\n",
       " 'GFTG': 'FE100312',\n",
       " 'WARC': nan,\n",
       " 'NLAN': '328D6422',\n",
       " 'HDIL': '3278D278',\n",
       " 'PTRX': nan,\n",
       " 'AEUT': '32B3D5B4',\n",
       " 'MHNT': '3241915E',\n",
       " 'MBPN': '324C32C0',\n",
       " 'WPOL': 'AAB5E3CE',\n",
       " 'TWIM': '32D01B44',\n",
       " 'WDYC': nan,\n",
       " 'ISTE': 'FD1042FE',\n",
       " 'AUCR': '3960D064',\n",
       " 'ACTU': '396020E0',\n",
       " 'VAUG': nan,\n",
       " 'FINL': nan,\n",
       " 'CBUK': 'AAB72324',\n",
       " 'MMVW': 'AAB64438',\n",
       " 'JBLU': '3291C2D8',\n",
       " 'GMOO': '32B76DD2',\n",
       " 'WSPC': nan,\n",
       " 'TCOK': '3244E00E',\n",
       " 'MWAH': '323C41FC',\n",
       " 'CTDN': '32B52B2C',\n",
       " 'FTAL': nan,\n",
       " 'TRCK': '32D0BBBC',\n",
       " 'GGLI': 'F8E004CA',\n",
       " 'AGOA': '396285EC',\n",
       " 'AHEL': '126006F2',\n",
       " 'CSC1': '9330D212',\n",
       " 'GOAK': nan,\n",
       " 'CALA': nan,\n",
       " 'ABBY': nan,\n",
       " 'CSSM': '32B53688',\n",
       " 'CSCE': '93305406',\n",
       " 'CBLH': nan,\n",
       " 'ATAN': '3960709C',\n",
       " 'SHOR': '3250C446',\n",
       " 'CALG': 'CA80539E',\n",
       " 'WMOU': '3250943A',\n",
       " 'HPUN': '48703040',\n",
       " 'OECK': '323EB48C',\n",
       " 'IDTC': 'AAB6F7B6',\n",
       " 'GTAY': 'F8E02226',\n",
       " 'HLKA': '32D620C2',\n",
       " 'HKAW': '3278C10E',\n",
       " 'PIND': '32A0FD1A',\n",
       " 'TNEA': 'AAC08444',\n",
       " 'CSUG': 'FA60A0CE',\n",
       " 'AGRA': '1260B57C',\n",
       " 'GCOC': nan,\n",
       " 'NSKY': '32B1B5A6',\n",
       " 'TDEL': nan,\n",
       " 'CCAS': '92302334',\n",
       " 'CTLR': '923016AE',\n",
       " 'STAB': '32B745EC',\n",
       " 'MBCB': '3275447C',\n",
       " 'ABEV': '12613192',\n",
       " 'CBNN': '32A101B6',\n",
       " 'CCUY': '32A1FFE0',\n",
       " 'ATLA': '339054E2',\n",
       " 'UOSP': '32D3948C',\n",
       " 'SDUR': '32B7337C',\n",
       " 'TWOL': 'AAC0B1DE',\n",
       " 'CMSG': 'CA8040E8',\n",
       " 'NWRN': nan,\n",
       " 'PCHE': nan,\n",
       " 'MOBR': '32B0C1CC',\n",
       " 'AQUE': '3962969A',\n",
       " 'YCAT': '6250A590',\n",
       " 'ICRE': '3463600C',\n",
       " 'NMON': 'AA10C4B4',\n",
       " 'CBRK': '3231C48E',\n",
       " 'CMVA': 'CA801094'}"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "empty_stations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dropping empty stations from list\n",
    "date_df.drop(columns=list(empty_stations.keys()), inplace=True)\n",
    "json_df.drop(columns=list(empty_stations.keys()), level=0, inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of Stations: 1939\n",
      "Number of Stations: 1939\n",
      "Number of stations that should be left: 1939\n"
     ]
    }
   ],
   "source": [
    "# Checking number of stations after dropping.\n",
    "print(f'Number of Stations: {len(date_df.columns)}')\n",
    "number_of_stations(json_df, Return=False)\n",
    "print(f\"Number of stations that should be left: {len(stations) - final_count}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "tags": [
     "testtag"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Location</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>NESS ID</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>NWS ID</th>\n",
       "      <th>Elevation</th>\n",
       "      <th>Agency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>NSCK</th>\n",
       "      <td>Sand Creek North Dakota</td>\n",
       "      <td>46° 32' 44\"</td>\n",
       "      <td>32553302</td>\n",
       "      <td>103° 31' 06\"</td>\n",
       "      <td>323804</td>\n",
       "      <td>2700 ft.</td>\n",
       "      <td>BLM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WSND</th>\n",
       "      <td>Sand Island Wisconsin</td>\n",
       "      <td>47° 00' 11\"</td>\n",
       "      <td>3275B4F8</td>\n",
       "      <td>90° 56' 11\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>615 ft.</td>\n",
       "      <td>NPS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>XBEW</th>\n",
       "      <td>Bear Wallow New Mexico</td>\n",
       "      <td>33° 27' 18\"</td>\n",
       "      <td>326C15C2</td>\n",
       "      <td>108° 39' 54\"</td>\n",
       "      <td>292010</td>\n",
       "      <td>9953 ft.</td>\n",
       "      <td>USFS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CLOS</th>\n",
       "      <td>Los Pinos Creek Colorado</td>\n",
       "      <td>38° 12' 48\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>106° 45' 27\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9600 ft.</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HLUA</th>\n",
       "      <td>Lua Makika Hawaii</td>\n",
       "      <td>20° 33' 45\"</td>\n",
       "      <td>327906EA</td>\n",
       "      <td>156° 33' 47\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1100 ft.</td>\n",
       "      <td>S&amp;amp;PF</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TMCK</th>\n",
       "      <td>McKittrick Texas</td>\n",
       "      <td>31° 58' 39\"</td>\n",
       "      <td>3237015E</td>\n",
       "      <td>104° 45' 06\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5004 ft.</td>\n",
       "      <td>NPS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBRA</th>\n",
       "      <td>Brasstown #1 Georgia</td>\n",
       "      <td>34° 48' 10\"</td>\n",
       "      <td>327357C4</td>\n",
       "      <td>83° 42' 36\"</td>\n",
       "      <td>090701</td>\n",
       "      <td>3280 ft.</td>\n",
       "      <td>USFS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NWIL</th>\n",
       "      <td>Williams Lookout North Dakota</td>\n",
       "      <td>46° 21' 43\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>102° 03' 43\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2375 ft.</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NKAN</th>\n",
       "      <td>Kane Springs Nevada</td>\n",
       "      <td>37° 15' 02\"</td>\n",
       "      <td>325CC5DA</td>\n",
       "      <td>114° 42' 27\"</td>\n",
       "      <td>261604</td>\n",
       "      <td>4590 ft.</td>\n",
       "      <td>BLM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WHAL</th>\n",
       "      <td>Half Moon Wyoming</td>\n",
       "      <td>42° 54' 53\"</td>\n",
       "      <td>32317700</td>\n",
       "      <td>109° 44' 50\"</td>\n",
       "      <td>481309</td>\n",
       "      <td>8530 ft.</td>\n",
       "      <td>USFS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2868 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Location     Latitude   NESS ID     Longitude   \\\n",
       "NSCK        Sand Creek North Dakota  46° 32' 44\"  32553302  103° 31' 06\"   \n",
       "WSND          Sand Island Wisconsin  47° 00' 11\"  3275B4F8   90° 56' 11\"   \n",
       "XBEW         Bear Wallow New Mexico  33° 27' 18\"  326C15C2  108° 39' 54\"   \n",
       "CLOS       Los Pinos Creek Colorado  38° 12' 48\"       NaN  106° 45' 27\"   \n",
       "HLUA              Lua Makika Hawaii  20° 33' 45\"  327906EA  156° 33' 47\"   \n",
       "...                             ...          ...       ...           ...   \n",
       "TMCK               McKittrick Texas  31° 58' 39\"  3237015E  104° 45' 06\"   \n",
       "GBRA           Brasstown #1 Georgia  34° 48' 10\"  327357C4   83° 42' 36\"   \n",
       "NWIL  Williams Lookout North Dakota  46° 21' 43\"       NaN  102° 03' 43\"   \n",
       "NKAN            Kane Springs Nevada  37° 15' 02\"  325CC5DA  114° 42' 27\"   \n",
       "WHAL              Half Moon Wyoming  42° 54' 53\"  32317700  109° 44' 50\"   \n",
       "\n",
       "     NWS ID  Elevation    Agency   \n",
       "NSCK  323804   2700 ft.       BLM  \n",
       "WSND     NaN    615 ft.       NPS  \n",
       "XBEW  292010   9953 ft.      USFS  \n",
       "CLOS     NaN   9600 ft.       NaN  \n",
       "HLUA     NaN   1100 ft.  S&amp;PF  \n",
       "...      ...        ...       ...  \n",
       "TMCK     NaN   5004 ft.       NPS  \n",
       "GBRA  090701   3280 ft.      USFS  \n",
       "NWIL     NaN   2375 ft.       NaN  \n",
       "NKAN  261604   4590 ft.       BLM  \n",
       "WHAL  481309   8530 ft.      USFS  \n",
       "\n",
       "[2868 rows x 7 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nessid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>IIND</th>\n",
       "      <th>HAHU</th>\n",
       "      <th>OWIL</th>\n",
       "      <th>MPHL</th>\n",
       "      <th>CALT</th>\n",
       "      <th>MPIS</th>\n",
       "      <th>TLEW</th>\n",
       "      <th>XMAG</th>\n",
       "      <th>CHOM</th>\n",
       "      <th>OFIS</th>\n",
       "      <th>...</th>\n",
       "      <th>CLAB</th>\n",
       "      <th>OLAV</th>\n",
       "      <th>AOPE</th>\n",
       "      <th>CSKU</th>\n",
       "      <th>MANT</th>\n",
       "      <th>OSIL</th>\n",
       "      <th>ACOA</th>\n",
       "      <th>WKOS</th>\n",
       "      <th>DBLB</th>\n",
       "      <th>TGIL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>...</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>...</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>...</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>...</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>...</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3388</th>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>...</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>05/13/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3389</th>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>...</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>05/14/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3390</th>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>...</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>05/15/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3391</th>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>...</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>05/16/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3392</th>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>...</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>05/17/2023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3393 rows × 1939 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            IIND        HAHU        OWIL        MPHL        CALT        MPIS  \\\n",
       "0     02/01/2014  02/01/2014  02/01/2014  02/01/2014  02/01/2014  02/01/2014   \n",
       "1     02/02/2014  02/02/2014  02/02/2014  02/02/2014  02/02/2014  02/02/2014   \n",
       "2     02/03/2014  02/03/2014  02/03/2014  02/03/2014  02/03/2014  02/03/2014   \n",
       "3     02/04/2014  02/04/2014  02/04/2014  02/04/2014  02/04/2014  02/04/2014   \n",
       "4     02/05/2014  02/05/2014  02/05/2014  02/05/2014  02/05/2014  02/05/2014   \n",
       "...          ...         ...         ...         ...         ...         ...   \n",
       "3388  05/13/2023  05/13/2023  05/13/2023  05/13/2023  05/13/2023         NaN   \n",
       "3389  05/14/2023  05/14/2023  05/14/2023  05/14/2023  05/14/2023         NaN   \n",
       "3390  05/15/2023  05/15/2023  05/15/2023  05/15/2023  05/15/2023         NaN   \n",
       "3391  05/16/2023  05/16/2023  05/16/2023  05/16/2023  05/16/2023         NaN   \n",
       "3392  05/17/2023  05/17/2023  05/17/2023  05/17/2023  05/17/2023         NaN   \n",
       "\n",
       "            TLEW        XMAG        CHOM        OFIS  ...        CLAB  \\\n",
       "0     02/01/2014  02/01/2014  02/01/2014  02/01/2014  ...  02/01/2014   \n",
       "1     02/02/2014  02/02/2014  02/02/2014  02/02/2014  ...  02/02/2014   \n",
       "2     02/03/2014  02/03/2014  02/03/2014  02/03/2014  ...  02/03/2014   \n",
       "3     02/04/2014  02/04/2014  02/04/2014  02/04/2014  ...  02/04/2014   \n",
       "4     02/05/2014  02/05/2014  02/05/2014  02/05/2014  ...  02/05/2014   \n",
       "...          ...         ...         ...         ...  ...         ...   \n",
       "3388  05/13/2023  05/13/2023         NaN  05/13/2023  ...  05/13/2023   \n",
       "3389  05/14/2023  05/14/2023         NaN  05/14/2023  ...  05/14/2023   \n",
       "3390  05/15/2023  05/15/2023         NaN  05/15/2023  ...  05/15/2023   \n",
       "3391  05/16/2023  05/16/2023         NaN  05/16/2023  ...  05/16/2023   \n",
       "3392  05/17/2023  05/17/2023         NaN  05/17/2023  ...  05/17/2023   \n",
       "\n",
       "            OLAV        AOPE        CSKU        MANT        OSIL        ACOA  \\\n",
       "0     02/01/2014  02/01/2014  02/01/2014  02/01/2014  02/01/2014  02/01/2014   \n",
       "1     02/02/2014  02/02/2014  02/02/2014  02/02/2014  02/02/2014  02/02/2014   \n",
       "2     02/03/2014  02/03/2014  02/03/2014  02/03/2014  02/03/2014  02/03/2014   \n",
       "3     02/04/2014  02/04/2014  02/04/2014  02/04/2014  02/04/2014  02/04/2014   \n",
       "4     02/05/2014  02/05/2014  02/05/2014  02/05/2014  02/05/2014  02/05/2014   \n",
       "...          ...         ...         ...         ...         ...         ...   \n",
       "3388  05/13/2023  05/13/2023  05/13/2023  05/13/2023  05/13/2023  05/13/2023   \n",
       "3389  05/14/2023  05/14/2023  05/14/2023  05/14/2023  05/14/2023  05/14/2023   \n",
       "3390  05/15/2023  05/15/2023  05/15/2023  05/15/2023  05/15/2023  05/15/2023   \n",
       "3391  05/16/2023  05/16/2023  05/16/2023  05/16/2023  05/16/2023  05/16/2023   \n",
       "3392  05/17/2023  05/17/2023  05/17/2023  05/17/2023  05/17/2023  05/17/2023   \n",
       "\n",
       "            WKOS        DBLB        TGIL  \n",
       "0     02/01/2014  02/01/2014  02/01/2014  \n",
       "1     02/02/2014  02/02/2014  02/02/2014  \n",
       "2     02/03/2014  02/03/2014  02/03/2014  \n",
       "3     02/04/2014  02/04/2014  02/04/2014  \n",
       "4     02/05/2014  02/05/2014  02/05/2014  \n",
       "...          ...         ...         ...  \n",
       "3388  05/13/2023  05/13/2023  05/13/2023  \n",
       "3389  05/14/2023  05/14/2023  05/14/2023  \n",
       "3390  05/15/2023  05/15/2023  05/15/2023  \n",
       "3391  05/16/2023  05/16/2023  05/16/2023  \n",
       "3392  05/17/2023  05/17/2023  05/17/2023  \n",
       "\n",
       "[3393 rows x 1939 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"10\" halign=\"left\">IIND</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"10\" halign=\"left\">TGIL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>year</th>\n",
       "      <th>day_of_year</th>\n",
       "      <th>day_of_run</th>\n",
       "      <th>total_solar_radiation_ly</th>\n",
       "      <th>ave_mean_wind_speed_mph</th>\n",
       "      <th>ave_mean_wind_direction_deg</th>\n",
       "      <th>max_maximum_wind_gust_mph</th>\n",
       "      <th>ave_average_air_temperature_deg_f</th>\n",
       "      <th>max_average_air_temperature_deg_f</th>\n",
       "      <th>...</th>\n",
       "      <th>ave_mean_wind_speed_mph</th>\n",
       "      <th>ave_mean_wind_direction_deg</th>\n",
       "      <th>max_maximum_wind_gust_mph</th>\n",
       "      <th>ave_average_air_temperature_deg_f</th>\n",
       "      <th>max_average_air_temperature_deg_f</th>\n",
       "      <th>min_average_air_temperature_deg_f</th>\n",
       "      <th>ave_average_relative_humidity</th>\n",
       "      <th>max_average_relative_humidity</th>\n",
       "      <th>min_average_relative_humidity</th>\n",
       "      <th>total_precipitation_in</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>270</td>\n",
       "      <td>0.42</td>\n",
       "      <td>93</td>\n",
       "      <td>12.00</td>\n",
       "      <td>26.3</td>\n",
       "      <td>44.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.54</td>\n",
       "      <td>192</td>\n",
       "      <td>21.00</td>\n",
       "      <td>57.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>92</td>\n",
       "      <td>98</td>\n",
       "      <td>79</td>\n",
       "      <td>0.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>207</td>\n",
       "      <td>3.50</td>\n",
       "      <td>146</td>\n",
       "      <td>20.00</td>\n",
       "      <td>17.6</td>\n",
       "      <td>26.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.71</td>\n",
       "      <td>358</td>\n",
       "      <td>20.00</td>\n",
       "      <td>36.7</td>\n",
       "      <td>40.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>96</td>\n",
       "      <td>98</td>\n",
       "      <td>91</td>\n",
       "      <td>0.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>34</td>\n",
       "      <td>3</td>\n",
       "      <td>114</td>\n",
       "      <td>0.96</td>\n",
       "      <td>232</td>\n",
       "      <td>15.00</td>\n",
       "      <td>24.8</td>\n",
       "      <td>38.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.62</td>\n",
       "      <td>66</td>\n",
       "      <td>11.00</td>\n",
       "      <td>37.2</td>\n",
       "      <td>45.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>88</td>\n",
       "      <td>96</td>\n",
       "      <td>71</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>35</td>\n",
       "      <td>4</td>\n",
       "      <td>237</td>\n",
       "      <td>5.71</td>\n",
       "      <td>120</td>\n",
       "      <td>16.00</td>\n",
       "      <td>12.7</td>\n",
       "      <td>19.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.96</td>\n",
       "      <td>93</td>\n",
       "      <td>16.00</td>\n",
       "      <td>37.9</td>\n",
       "      <td>41.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>96</td>\n",
       "      <td>100</td>\n",
       "      <td>88</td>\n",
       "      <td>0.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>36</td>\n",
       "      <td>5</td>\n",
       "      <td>294</td>\n",
       "      <td>4.92</td>\n",
       "      <td>122</td>\n",
       "      <td>17.00</td>\n",
       "      <td>6.2</td>\n",
       "      <td>15.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7.46</td>\n",
       "      <td>347</td>\n",
       "      <td>23.00</td>\n",
       "      <td>33.1</td>\n",
       "      <td>40.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>69</td>\n",
       "      <td>100</td>\n",
       "      <td>56</td>\n",
       "      <td>0.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3388</th>\n",
       "      <td>05/13/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>133</td>\n",
       "      <td>3389</td>\n",
       "      <td>330</td>\n",
       "      <td>3.08</td>\n",
       "      <td>125</td>\n",
       "      <td>23.00</td>\n",
       "      <td>59.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.46</td>\n",
       "      <td>134</td>\n",
       "      <td>25.00</td>\n",
       "      <td>76.4</td>\n",
       "      <td>86.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>83</td>\n",
       "      <td>96</td>\n",
       "      <td>62</td>\n",
       "      <td>0.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3389</th>\n",
       "      <td>05/14/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>134</td>\n",
       "      <td>3390</td>\n",
       "      <td>608</td>\n",
       "      <td>4.08</td>\n",
       "      <td>103</td>\n",
       "      <td>34.00</td>\n",
       "      <td>64.9</td>\n",
       "      <td>81.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.25</td>\n",
       "      <td>131</td>\n",
       "      <td>29.00</td>\n",
       "      <td>72.5</td>\n",
       "      <td>85.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>94</td>\n",
       "      <td>100</td>\n",
       "      <td>65</td>\n",
       "      <td>0.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3390</th>\n",
       "      <td>05/15/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>135</td>\n",
       "      <td>3391</td>\n",
       "      <td>335</td>\n",
       "      <td>1.79</td>\n",
       "      <td>96</td>\n",
       "      <td>14.00</td>\n",
       "      <td>60.2</td>\n",
       "      <td>74.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.92</td>\n",
       "      <td>218</td>\n",
       "      <td>22.00</td>\n",
       "      <td>75.6</td>\n",
       "      <td>88.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>83</td>\n",
       "      <td>100</td>\n",
       "      <td>52</td>\n",
       "      <td>0.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3391</th>\n",
       "      <td>05/16/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>136</td>\n",
       "      <td>3392</td>\n",
       "      <td>571</td>\n",
       "      <td>2.04</td>\n",
       "      <td>187</td>\n",
       "      <td>19.00</td>\n",
       "      <td>61.5</td>\n",
       "      <td>80.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.17</td>\n",
       "      <td>285</td>\n",
       "      <td>12.00</td>\n",
       "      <td>71.5</td>\n",
       "      <td>78.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>85</td>\n",
       "      <td>100</td>\n",
       "      <td>65</td>\n",
       "      <td>0.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3392</th>\n",
       "      <td>05/17/2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>137</td>\n",
       "      <td>3393</td>\n",
       "      <td>476</td>\n",
       "      <td>2.54</td>\n",
       "      <td>108</td>\n",
       "      <td>19.00</td>\n",
       "      <td>61.7</td>\n",
       "      <td>77.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.25</td>\n",
       "      <td>318</td>\n",
       "      <td>14.00</td>\n",
       "      <td>70.5</td>\n",
       "      <td>80.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>79</td>\n",
       "      <td>100</td>\n",
       "      <td>57</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3393 rows × 29085 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            IIND                                                        \\\n",
       "            date  year day_of_year day_of_run total_solar_radiation_ly   \n",
       "0     02/01/2014  2014          32          1                      270   \n",
       "1     02/02/2014  2014          33          2                      207   \n",
       "2     02/03/2014  2014          34          3                      114   \n",
       "3     02/04/2014  2014          35          4                      237   \n",
       "4     02/05/2014  2014          36          5                      294   \n",
       "...          ...   ...         ...        ...                      ...   \n",
       "3388  05/13/2023  2023         133       3389                      330   \n",
       "3389  05/14/2023  2023         134       3390                      608   \n",
       "3390  05/15/2023  2023         135       3391                      335   \n",
       "3391  05/16/2023  2023         136       3392                      571   \n",
       "3392  05/17/2023  2023         137       3393                      476   \n",
       "\n",
       "                                                          \\\n",
       "     ave_mean_wind_speed_mph ave_mean_wind_direction_deg   \n",
       "0                       0.42                          93   \n",
       "1                       3.50                         146   \n",
       "2                       0.96                         232   \n",
       "3                       5.71                         120   \n",
       "4                       4.92                         122   \n",
       "...                      ...                         ...   \n",
       "3388                    3.08                         125   \n",
       "3389                    4.08                         103   \n",
       "3390                    1.79                          96   \n",
       "3391                    2.04                         187   \n",
       "3392                    2.54                         108   \n",
       "\n",
       "                                                                  \\\n",
       "     max_maximum_wind_gust_mph ave_average_air_temperature_deg_f   \n",
       "0                        12.00                              26.3   \n",
       "1                        20.00                              17.6   \n",
       "2                        15.00                              24.8   \n",
       "3                        16.00                              12.7   \n",
       "4                        17.00                               6.2   \n",
       "...                        ...                               ...   \n",
       "3388                     23.00                              59.0   \n",
       "3389                     34.00                              64.9   \n",
       "3390                     14.00                              60.2   \n",
       "3391                     19.00                              61.5   \n",
       "3392                     19.00                              61.7   \n",
       "\n",
       "                                        ...                    TGIL  \\\n",
       "     max_average_air_temperature_deg_f  ... ave_mean_wind_speed_mph   \n",
       "0                                 44.0  ...                    6.54   \n",
       "1                                 26.0  ...                    6.71   \n",
       "2                                 38.0  ...                    3.62   \n",
       "3                                 19.0  ...                    2.96   \n",
       "4                                 15.0  ...                    7.46   \n",
       "...                                ...  ...                     ...   \n",
       "3388                              70.0  ...                    4.46   \n",
       "3389                              81.0  ...                    3.25   \n",
       "3390                              74.0  ...                    2.92   \n",
       "3391                              80.0  ...                    3.17   \n",
       "3392                              77.0  ...                    2.25   \n",
       "\n",
       "                                                            \\\n",
       "     ave_mean_wind_direction_deg max_maximum_wind_gust_mph   \n",
       "0                            192                     21.00   \n",
       "1                            358                     20.00   \n",
       "2                             66                     11.00   \n",
       "3                             93                     16.00   \n",
       "4                            347                     23.00   \n",
       "...                          ...                       ...   \n",
       "3388                         134                     25.00   \n",
       "3389                         131                     29.00   \n",
       "3390                         218                     22.00   \n",
       "3391                         285                     12.00   \n",
       "3392                         318                     14.00   \n",
       "\n",
       "                                                                          \\\n",
       "     ave_average_air_temperature_deg_f max_average_air_temperature_deg_f   \n",
       "0                                 57.0                              65.0   \n",
       "1                                 36.7                              40.0   \n",
       "2                                 37.2                              45.0   \n",
       "3                                 37.9                              41.0   \n",
       "4                                 33.1                              40.0   \n",
       "...                                ...                               ...   \n",
       "3388                              76.4                              86.0   \n",
       "3389                              72.5                              85.0   \n",
       "3390                              75.6                              88.0   \n",
       "3391                              71.5                              78.0   \n",
       "3392                              70.5                              80.0   \n",
       "\n",
       "                                                                      \\\n",
       "     min_average_air_temperature_deg_f ave_average_relative_humidity   \n",
       "0                                 41.0                            92   \n",
       "1                                 34.0                            96   \n",
       "2                                 33.0                            88   \n",
       "3                                 35.0                            96   \n",
       "4                                 26.0                            69   \n",
       "...                                ...                           ...   \n",
       "3388                              68.0                            83   \n",
       "3389                              67.0                            94   \n",
       "3390                              68.0                            83   \n",
       "3391                              66.0                            85   \n",
       "3392                              64.0                            79   \n",
       "\n",
       "                                                                  \\\n",
       "     max_average_relative_humidity min_average_relative_humidity   \n",
       "0                               98                            79   \n",
       "1                               98                            91   \n",
       "2                               96                            71   \n",
       "3                              100                            88   \n",
       "4                              100                            56   \n",
       "...                            ...                           ...   \n",
       "3388                            96                            62   \n",
       "3389                           100                            65   \n",
       "3390                           100                            52   \n",
       "3391                           100                            65   \n",
       "3392                           100                            57   \n",
       "\n",
       "                             \n",
       "     total_precipitation_in  \n",
       "0                      0.42  \n",
       "1                      0.68  \n",
       "2                      0.00  \n",
       "3                      0.68  \n",
       "4                      0.01  \n",
       "...                     ...  \n",
       "3388                   0.27  \n",
       "3389                   0.65  \n",
       "3390                   0.06  \n",
       "3391                   0.01  \n",
       "3392                   0.00  \n",
       "\n",
       "[3393 rows x 29085 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 7. Adding NESSID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Changing Station names from the 4 letters code to the NESS ID\n",
    "\n",
    "json_df.rename(columns=stations, level=0, inplace=True)\n",
    "date_df.rename(columns=stations, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"10\" halign=\"left\">3234455A</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"10\" halign=\"left\">88400776</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>year</th>\n",
       "      <th>day_of_year</th>\n",
       "      <th>day_of_run</th>\n",
       "      <th>total_solar_radiation_ly</th>\n",
       "      <th>ave_mean_wind_speed_mph</th>\n",
       "      <th>ave_mean_wind_direction_deg</th>\n",
       "      <th>max_maximum_wind_gust_mph</th>\n",
       "      <th>ave_average_air_temperature_deg_f</th>\n",
       "      <th>max_average_air_temperature_deg_f</th>\n",
       "      <th>...</th>\n",
       "      <th>ave_mean_wind_speed_mph</th>\n",
       "      <th>ave_mean_wind_direction_deg</th>\n",
       "      <th>max_maximum_wind_gust_mph</th>\n",
       "      <th>ave_average_air_temperature_deg_f</th>\n",
       "      <th>max_average_air_temperature_deg_f</th>\n",
       "      <th>min_average_air_temperature_deg_f</th>\n",
       "      <th>ave_average_relative_humidity</th>\n",
       "      <th>max_average_relative_humidity</th>\n",
       "      <th>min_average_relative_humidity</th>\n",
       "      <th>total_precipitation_in</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>270</td>\n",
       "      <td>0.42</td>\n",
       "      <td>93</td>\n",
       "      <td>12.00</td>\n",
       "      <td>26.3</td>\n",
       "      <td>44.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.54</td>\n",
       "      <td>192</td>\n",
       "      <td>21.00</td>\n",
       "      <td>57.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>92</td>\n",
       "      <td>98</td>\n",
       "      <td>79</td>\n",
       "      <td>0.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>207</td>\n",
       "      <td>3.50</td>\n",
       "      <td>146</td>\n",
       "      <td>20.00</td>\n",
       "      <td>17.6</td>\n",
       "      <td>26.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.71</td>\n",
       "      <td>358</td>\n",
       "      <td>20.00</td>\n",
       "      <td>36.7</td>\n",
       "      <td>40.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>96</td>\n",
       "      <td>98</td>\n",
       "      <td>91</td>\n",
       "      <td>0.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>34</td>\n",
       "      <td>3</td>\n",
       "      <td>114</td>\n",
       "      <td>0.96</td>\n",
       "      <td>232</td>\n",
       "      <td>15.00</td>\n",
       "      <td>24.8</td>\n",
       "      <td>38.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.62</td>\n",
       "      <td>66</td>\n",
       "      <td>11.00</td>\n",
       "      <td>37.2</td>\n",
       "      <td>45.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>88</td>\n",
       "      <td>96</td>\n",
       "      <td>71</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>35</td>\n",
       "      <td>4</td>\n",
       "      <td>237</td>\n",
       "      <td>5.71</td>\n",
       "      <td>120</td>\n",
       "      <td>16.00</td>\n",
       "      <td>12.7</td>\n",
       "      <td>19.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.96</td>\n",
       "      <td>93</td>\n",
       "      <td>16.00</td>\n",
       "      <td>37.9</td>\n",
       "      <td>41.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>96</td>\n",
       "      <td>100</td>\n",
       "      <td>88</td>\n",
       "      <td>0.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>36</td>\n",
       "      <td>5</td>\n",
       "      <td>294</td>\n",
       "      <td>4.92</td>\n",
       "      <td>122</td>\n",
       "      <td>17.00</td>\n",
       "      <td>6.2</td>\n",
       "      <td>15.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7.46</td>\n",
       "      <td>347</td>\n",
       "      <td>23.00</td>\n",
       "      <td>33.1</td>\n",
       "      <td>40.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>69</td>\n",
       "      <td>100</td>\n",
       "      <td>56</td>\n",
       "      <td>0.01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29085 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     3234455A                                                        \\\n",
       "         date  year day_of_year day_of_run total_solar_radiation_ly   \n",
       "0  02/01/2014  2014          32          1                      270   \n",
       "1  02/02/2014  2014          33          2                      207   \n",
       "2  02/03/2014  2014          34          3                      114   \n",
       "3  02/04/2014  2014          35          4                      237   \n",
       "4  02/05/2014  2014          36          5                      294   \n",
       "\n",
       "                                                       \\\n",
       "  ave_mean_wind_speed_mph ave_mean_wind_direction_deg   \n",
       "0                    0.42                          93   \n",
       "1                    3.50                         146   \n",
       "2                    0.96                         232   \n",
       "3                    5.71                         120   \n",
       "4                    4.92                         122   \n",
       "\n",
       "                                                               \\\n",
       "  max_maximum_wind_gust_mph ave_average_air_temperature_deg_f   \n",
       "0                     12.00                              26.3   \n",
       "1                     20.00                              17.6   \n",
       "2                     15.00                              24.8   \n",
       "3                     16.00                              12.7   \n",
       "4                     17.00                               6.2   \n",
       "\n",
       "                                     ...                88400776  \\\n",
       "  max_average_air_temperature_deg_f  ... ave_mean_wind_speed_mph   \n",
       "0                              44.0  ...                    6.54   \n",
       "1                              26.0  ...                    6.71   \n",
       "2                              38.0  ...                    3.62   \n",
       "3                              19.0  ...                    2.96   \n",
       "4                              15.0  ...                    7.46   \n",
       "\n",
       "                                                         \\\n",
       "  ave_mean_wind_direction_deg max_maximum_wind_gust_mph   \n",
       "0                         192                     21.00   \n",
       "1                         358                     20.00   \n",
       "2                          66                     11.00   \n",
       "3                          93                     16.00   \n",
       "4                         347                     23.00   \n",
       "\n",
       "                                                                       \\\n",
       "  ave_average_air_temperature_deg_f max_average_air_temperature_deg_f   \n",
       "0                              57.0                              65.0   \n",
       "1                              36.7                              40.0   \n",
       "2                              37.2                              45.0   \n",
       "3                              37.9                              41.0   \n",
       "4                              33.1                              40.0   \n",
       "\n",
       "                                                                   \\\n",
       "  min_average_air_temperature_deg_f ave_average_relative_humidity   \n",
       "0                              41.0                            92   \n",
       "1                              34.0                            96   \n",
       "2                              33.0                            88   \n",
       "3                              35.0                            96   \n",
       "4                              26.0                            69   \n",
       "\n",
       "                                                               \\\n",
       "  max_average_relative_humidity min_average_relative_humidity   \n",
       "0                            98                            79   \n",
       "1                            98                            91   \n",
       "2                            96                            71   \n",
       "3                           100                            88   \n",
       "4                           100                            56   \n",
       "\n",
       "                          \n",
       "  total_precipitation_in  \n",
       "0                   0.42  \n",
       "1                   0.68  \n",
       "2                   0.00  \n",
       "3                   0.68  \n",
       "4                   0.01  \n",
       "\n",
       "[5 rows x 29085 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>3234455A</th>\n",
       "      <th>4870D3B2</th>\n",
       "      <th>324AD1FC</th>\n",
       "      <th>323FA500</th>\n",
       "      <th>CA41F5F8</th>\n",
       "      <th>327BC600</th>\n",
       "      <th>02609414</th>\n",
       "      <th>32336182</th>\n",
       "      <th>91C0165C</th>\n",
       "      <th>325D842A</th>\n",
       "      <th>...</th>\n",
       "      <th>CA2311CC</th>\n",
       "      <th>323582BE</th>\n",
       "      <th>327E82CA</th>\n",
       "      <th>324AA76C</th>\n",
       "      <th>325363B0</th>\n",
       "      <th>324BD306</th>\n",
       "      <th>39605670</th>\n",
       "      <th>3000D036</th>\n",
       "      <th>DF10101C</th>\n",
       "      <th>88400776</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>...</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "      <td>02/01/2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>...</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "      <td>02/02/2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>...</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "      <td>02/03/2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>...</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "      <td>02/04/2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>...</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "      <td>02/05/2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 1939 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     3234455A    4870D3B2    324AD1FC    323FA500    CA41F5F8    327BC600  \\\n",
       "0  02/01/2014  02/01/2014  02/01/2014  02/01/2014  02/01/2014  02/01/2014   \n",
       "1  02/02/2014  02/02/2014  02/02/2014  02/02/2014  02/02/2014  02/02/2014   \n",
       "2  02/03/2014  02/03/2014  02/03/2014  02/03/2014  02/03/2014  02/03/2014   \n",
       "3  02/04/2014  02/04/2014  02/04/2014  02/04/2014  02/04/2014  02/04/2014   \n",
       "4  02/05/2014  02/05/2014  02/05/2014  02/05/2014  02/05/2014  02/05/2014   \n",
       "\n",
       "     02609414    32336182    91C0165C    325D842A  ...    CA2311CC  \\\n",
       "0  02/01/2014  02/01/2014  02/01/2014  02/01/2014  ...  02/01/2014   \n",
       "1  02/02/2014  02/02/2014  02/02/2014  02/02/2014  ...  02/02/2014   \n",
       "2  02/03/2014  02/03/2014  02/03/2014  02/03/2014  ...  02/03/2014   \n",
       "3  02/04/2014  02/04/2014  02/04/2014  02/04/2014  ...  02/04/2014   \n",
       "4  02/05/2014  02/05/2014  02/05/2014  02/05/2014  ...  02/05/2014   \n",
       "\n",
       "     323582BE    327E82CA    324AA76C    325363B0    324BD306    39605670  \\\n",
       "0  02/01/2014  02/01/2014  02/01/2014  02/01/2014  02/01/2014  02/01/2014   \n",
       "1  02/02/2014  02/02/2014  02/02/2014  02/02/2014  02/02/2014  02/02/2014   \n",
       "2  02/03/2014  02/03/2014  02/03/2014  02/03/2014  02/03/2014  02/03/2014   \n",
       "3  02/04/2014  02/04/2014  02/04/2014  02/04/2014  02/04/2014  02/04/2014   \n",
       "4  02/05/2014  02/05/2014  02/05/2014  02/05/2014  02/05/2014  02/05/2014   \n",
       "\n",
       "     3000D036    DF10101C    88400776  \n",
       "0  02/01/2014  02/01/2014  02/01/2014  \n",
       "1  02/02/2014  02/02/2014  02/02/2014  \n",
       "2  02/03/2014  02/03/2014  02/03/2014  \n",
       "3  02/04/2014  02/04/2014  02/04/2014  \n",
       "4  02/05/2014  02/05/2014  02/05/2014  \n",
       "\n",
       "[5 rows x 1939 columns]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Exporting both datasets as a csv\n",
    "\n",
    "# date_df.to_csv('stations_dates.csv', index=False)\n",
    "# json_df.to_csv('RAWS_stations.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Raw Cell Format",
  "kernelspec": {
   "display_name": "Python (flatiron-env)",
   "language": "python",
   "name": "flatiron-env"
  },
  "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}