00-build-dataset.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Building a dataset for NREL and LCD weather and solar data\n",
    "This notebook is intended to be a first step in gathering data for a solar energy forecasting model. The final dataset will consist of hourly weather data and matching hourly solar data for multiple weather stations. This dataset can then be used for analytical analysis or to train a machine learning model."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## LCD data acquisition\n",
    "We are going to first grab data from the [Local Climatological Data (LCD)](https://www.ncdc.noaa.gov/data-access/land-based-station-data/land-based-datasets/quality-controlled-local-climatological-data-qclcd) Dataset.\n",
    "LCD dataset is a NCEI NOAA open database. It contains historic hourly weather data\n",
    "for major weather stations in the US. Primarily what we are looking for is cloud coverage data to compare with the solar data later on. There are many other public and private datasets that could have been used instead of LCD, but this one is free and open to anyone. For more accurate data, a paid historical dataset could have been used."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To begin, first we need to set the parameter variables for selecting our data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "year = '2017' # 2017 is the max year\n",
    "#, 'Iowa', 'Arizona', 'Tennessee', 'Oregon', 'Colorado', 'North Carolina', 'North Dakota'\n",
    "states = ['Nevada'] # Use full capitalized names (any US state)\n",
    "station_count = None # None = all stations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can set up the url path and the station metadata that will be used after to extract the raw data using Pandas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(22, 10)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>file</th>\n",
       "      <th>STATION_ID</th>\n",
       "      <th>STATION</th>\n",
       "      <th>BEGIN_DATE</th>\n",
       "      <th>END_DATE</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTRY</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "      <th>ELEVATION_(M)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>72054900171.csv</td>\n",
       "      <td>171</td>\n",
       "      <td>CARSON AIRPORT, NV US</td>\n",
       "      <td>2010-07-13</td>\n",
       "      <td>2020-06-21</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>United States</td>\n",
       "      <td>39.18300</td>\n",
       "      <td>-119.7330</td>\n",
       "      <td>1432.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261</th>\n",
       "      <td>72074100269.csv</td>\n",
       "      <td>269</td>\n",
       "      <td>BOULDER CITY MUNICIPAL AIRPORT, NV US</td>\n",
       "      <td>2010-06-23</td>\n",
       "      <td>2020-06-21</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>United States</td>\n",
       "      <td>35.94700</td>\n",
       "      <td>-114.8610</td>\n",
       "      <td>671.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>272</th>\n",
       "      <td>72083900279.csv</td>\n",
       "      <td>279</td>\n",
       "      <td>RENO STEAD AIRPORT, NV US</td>\n",
       "      <td>2011-09-20</td>\n",
       "      <td>2020-06-21</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>United States</td>\n",
       "      <td>39.66700</td>\n",
       "      <td>-119.8760</td>\n",
       "      <td>1540.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>404</th>\n",
       "      <td>72209653127.csv</td>\n",
       "      <td>53127</td>\n",
       "      <td>LAS VEGAS HENDERSON AIRPORT, NV US</td>\n",
       "      <td>2005-01-03</td>\n",
       "      <td>2020-06-21</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>United States</td>\n",
       "      <td>35.97611</td>\n",
       "      <td>-115.1325</td>\n",
       "      <td>749.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>927</th>\n",
       "      <td>72386023169.csv</td>\n",
       "      <td>23169</td>\n",
       "      <td>MCCARRAN INTERNATIONAL AIRPORT, NV US</td>\n",
       "      <td>1948-12-18</td>\n",
       "      <td>2020-06-21</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>United States</td>\n",
       "      <td>36.07190</td>\n",
       "      <td>-115.1634</td>\n",
       "      <td>664.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                file  STATION_ID                                STATION  \\\n",
       "172  72054900171.csv         171                  CARSON AIRPORT, NV US   \n",
       "261  72074100269.csv         269  BOULDER CITY MUNICIPAL AIRPORT, NV US   \n",
       "272  72083900279.csv         279              RENO STEAD AIRPORT, NV US   \n",
       "404  72209653127.csv       53127     LAS VEGAS HENDERSON AIRPORT, NV US   \n",
       "927  72386023169.csv       23169  MCCARRAN INTERNATIONAL AIRPORT, NV US   \n",
       "\n",
       "     BEGIN_DATE    END_DATE   STATE        COUNTRY  LATITUDE  LONGITUDE  \\\n",
       "172  2010-07-13  2020-06-21  Nevada  United States  39.18300  -119.7330   \n",
       "261  2010-06-23  2020-06-21  Nevada  United States  35.94700  -114.8610   \n",
       "272  2011-09-20  2020-06-21  Nevada  United States  39.66700  -119.8760   \n",
       "404  2005-01-03  2020-06-21  Nevada  United States  35.97611  -115.1325   \n",
       "927  1948-12-18  2020-06-21  Nevada  United States  36.07190  -115.1634   \n",
       "\n",
       "     ELEVATION_(M)  \n",
       "172         1432.3  \n",
       "261          671.2  \n",
       "272         1540.2  \n",
       "404          749.2  \n",
       "927          664.5  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "data_path = 'https://www.ncei.noaa.gov/data/local-climatological-data/access/'+ year +'/'\n",
    "stations = pd.read_csv('stations.csv', dtype={\"LATITUDE\": float, \"LONGITUDE\": float})\n",
    "stations = stations[stations['STATE'].isin(states)][:station_count]\n",
    "\n",
    "print(stations.shape)\n",
    "stations.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we get the data, we will create a lambda expression that will clean up floating point numbers by removing unwanted characters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'23.5'"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_float = lambda x: ''.join(filter(lambda y: y.isdigit() or y == '.', x))\n",
    "clean_float('23.5s')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we will import tqdm. This library adds loading bar functionality\n",
    "which will help visualize some of the larger Pandas operations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/gagekrumbach/.local/share/virtualenvs/Solar_Forcaster-m3btlaz4/lib/python3.7/site-packages/tqdm/std.py:668: FutureWarning: The Panel class is removed from pandas. Accessing it from the top-level namespace will also be removed in the next version\n",
      "  from pandas import Panel\n"
     ]
    }
   ],
   "source": [
    "from tqdm.auto import tqdm\n",
    "tqdm.pandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally we can access our data through the LCD database using HTTPS. We only extract a set amount of columns that we need. We also pass in our float cleaner for three of the columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "66b23e60d2fa440c9a57a9c743b03d56",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, max=22.0), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "from datetime import datetime as dt\n",
    "\n",
    "w_data = pd.concat([pd.read_csv(data_path + station[1]['file'],\n",
    "                            usecols=['STATION', 'DATE', 'HourlySkyConditions', 'HourlyStationPressure',\n",
    "                                'HourlyVisibility', 'HourlyPresentWeatherType', 'HourlyPrecipitation',\n",
    "                                    'HourlyDewPointTemperature', 'HourlyDryBulbTemperature',\n",
    "                                    'HourlyRelativeHumidity', 'HourlyWindSpeed'],\n",
    "                            parse_dates=['DATE'],\n",
    "                            dtype={'HourlySkyConditions': str, 'HourlyPresentWeatherType': str},\n",
    "                            converters={'HourlyStationPressure': clean_float, 'HourlyVisibility': clean_float,\n",
    "                                        'HourlyPrecipitation': clean_float, 'HourlyDewPointTemperature': clean_float,\n",
    "                                       'HourlyDryBulbTemperature': clean_float, 'HourlyRelativeHumidity': clean_float,\n",
    "                                       'HourlyWindSpeed': clean_float})\n",
    "                         for station in tqdm(stations.iterrows(), total=stations.shape[0])\\\n",
    "                      if dt.strptime(station[1][\"BEGIN_DATE\"], '%Y-%m-%d') < dt.strptime(year, '%Y')\\\n",
    "                      and dt.strptime(station[1][\"END_DATE\"], '%Y-%m-%d') > dt.strptime(year, '%Y')])\n",
    "\n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data manipulation with Pandas\n",
    "\n",
    "Now we will convert weather codes (ex: `-SN:03 BR:1 |SN |`) to a less specific form that can be used by the ML model later on. More information on these codes can be found [here](https://www.ncei.noaa.gov/data/local-climatological-data/doc/LCD_documentation.pdf). Codes are in the format `AU | AW | MW` with each observation separated by a bar. They need to be categorized into `freezing_rain_heavy, freezing_rainice_pellets, ice_pellets_light, tstorm` and other categories. We will perform this operation by using a json lookup file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "31fdb2641d36427faceab5e21217e452",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, max=590412.0), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "with open('weather_lookup_converter.json') as json_file:\n",
    "    lookup = json.load(json_file)\n",
    "\n",
    "def weather_decoder(value):\n",
    "    if pd.notnull(value):\n",
    "        for weather_type in lookup:\n",
    "            for code in lookup[weather_type]:\n",
    "                if code in value:\n",
    "                    return weather_type\n",
    "\n",
    "# apply the decoder using a lambda expression\n",
    "w_data['weather_type'] = w_data.progress_apply(lambda row: weather_decoder(row.HourlyPresentWeatherType), axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we do the same manipulation but with cloud observations (ex: `OVC:08 90`). We break up the hourly sky conditions column to a cloud string and cloud cover percentage using another lookup table. These will be categorized into `cloudy, partly_cloudy, clear` and other categories."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "36ed9d5ce52f41d485bd7e48c36047bb",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, max=590412.0), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "96b0d9df9ad8430a83d40826d65834f0",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, max=590412.0), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "      <th>DATE</th>\n",
       "      <th>HourlyDewPointTemperature</th>\n",
       "      <th>HourlyDryBulbTemperature</th>\n",
       "      <th>HourlyPrecipitation</th>\n",
       "      <th>HourlyPresentWeatherType</th>\n",
       "      <th>HourlyRelativeHumidity</th>\n",
       "      <th>HourlySkyConditions</th>\n",
       "      <th>HourlyStationPressure</th>\n",
       "      <th>HourlyVisibility</th>\n",
       "      <th>HourlyWindSpeed</th>\n",
       "      <th>weather_type</th>\n",
       "      <th>cloud_type</th>\n",
       "      <th>cloud_cover</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>72054900171</td>\n",
       "      <td>2017-01-01 00:15:00</td>\n",
       "      <td>23</td>\n",
       "      <td>28</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>80</td>\n",
       "      <td>CLR:00</td>\n",
       "      <td>25.12</td>\n",
       "      <td>10.00</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>clear</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>72054900171</td>\n",
       "      <td>2017-01-01 00:35:00</td>\n",
       "      <td>23</td>\n",
       "      <td>28</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>80</td>\n",
       "      <td>CLR:00</td>\n",
       "      <td>25.11</td>\n",
       "      <td>10.00</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>clear</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>72054900171</td>\n",
       "      <td>2017-01-01 00:55:00</td>\n",
       "      <td>23</td>\n",
       "      <td>28</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>80</td>\n",
       "      <td>CLR:00</td>\n",
       "      <td>25.11</td>\n",
       "      <td>10.00</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>clear</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>72054900171</td>\n",
       "      <td>2017-01-01 01:15:00</td>\n",
       "      <td>21</td>\n",
       "      <td>27</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>80</td>\n",
       "      <td>CLR:00</td>\n",
       "      <td>25.10</td>\n",
       "      <td>10.00</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>clear</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>72054900171</td>\n",
       "      <td>2017-01-01 01:35:00</td>\n",
       "      <td>21</td>\n",
       "      <td>27</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>80</td>\n",
       "      <td>CLR:00</td>\n",
       "      <td>25.10</td>\n",
       "      <td>10.00</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>clear</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       STATION                DATE HourlyDewPointTemperature  \\\n",
       "0  72054900171 2017-01-01 00:15:00                        23   \n",
       "1  72054900171 2017-01-01 00:35:00                        23   \n",
       "2  72054900171 2017-01-01 00:55:00                        23   \n",
       "3  72054900171 2017-01-01 01:15:00                        21   \n",
       "4  72054900171 2017-01-01 01:35:00                        21   \n",
       "\n",
       "  HourlyDryBulbTemperature HourlyPrecipitation HourlyPresentWeatherType  \\\n",
       "0                       28                                          NaN   \n",
       "1                       28                                          NaN   \n",
       "2                       28                                          NaN   \n",
       "3                       27                                          NaN   \n",
       "4                       27                                          NaN   \n",
       "\n",
       "  HourlyRelativeHumidity HourlySkyConditions HourlyStationPressure  \\\n",
       "0                     80              CLR:00                 25.12   \n",
       "1                     80              CLR:00                 25.11   \n",
       "2                     80              CLR:00                 25.11   \n",
       "3                     80              CLR:00                 25.10   \n",
       "4                     80              CLR:00                 25.10   \n",
       "\n",
       "  HourlyVisibility HourlyWindSpeed weather_type cloud_type  cloud_cover  \n",
       "0            10.00               5         None      clear          0.0  \n",
       "1            10.00               5         None      clear          0.0  \n",
       "2            10.00               6         None      clear          0.0  \n",
       "3            10.00               5         None      clear          0.0  \n",
       "4            10.00               3         None      clear          0.0  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open('cloud_lookup.json') as json_file:\n",
    "    lookup = json.load(json_file)\n",
    "\n",
    "def cloud_decoder(value):\n",
    "    if pd.notnull(value):\n",
    "        for cloud_type in lookup:\n",
    "            if cloud_type in value:\n",
    "                return lookup[cloud_type]\n",
    "    return lookup[\"CLR\"]\n",
    "            \n",
    "w_data['cloud_type'] = w_data.progress_apply(lambda row: cloud_decoder(row.HourlySkyConditions)[\"cloud_str\"], axis=1)\n",
    "w_data['cloud_cover'] = w_data.progress_apply(lambda row: cloud_decoder(row.HourlySkyConditions)[\"cloud_cover\"], axis=1)\n",
    "\n",
    "w_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combine meta data and actual data\n",
    "Next we will combine the station metadata from before (lat, long, elevation) with the historic weather data. Before we do this, we need to alter the historic weather data station ids so they match the metadata station ids.<br/><br/>\n",
    "The metadata uses a 5 digit WBAN id: `54852` and the historic data uses a 6 digit USAF MASTER id + the WBAN id: `72034354852`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove USAF MASTER id\n",
    "w_data['STATION'] = w_data['STATION'].map(lambda x: int(str(x)[-5:]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "Wrong number of items passed 22, placement implies 1",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m~/.local/share/virtualenvs/Solar_Forcaster-m3btlaz4/lib/python3.7/site-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36mget_loc\u001b[0;34m(self, key, method, tolerance)\u001b[0m\n\u001b[1;32m   2645\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2646\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2647\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: 'latitude'",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m~/.local/share/virtualenvs/Solar_Forcaster-m3btlaz4/lib/python3.7/site-packages/pandas/core/internals/managers.py\u001b[0m in \u001b[0;36mset\u001b[0;34m(self, item, value)\u001b[0m\n\u001b[1;32m   1070\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1071\u001b[0;31m             \u001b[0mloc\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mitems\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mitem\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1072\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.local/share/virtualenvs/Solar_Forcaster-m3btlaz4/lib/python3.7/site-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36mget_loc\u001b[0;34m(self, key, method, tolerance)\u001b[0m\n\u001b[1;32m   2647\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2648\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_maybe_cast_indexer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2649\u001b[0m         \u001b[0mindexer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_indexer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtolerance\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mtolerance\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: 'latitude'",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-60-07dea94e5ac4>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# add the meta data to the weather data\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m w_data[\"latitude\"] = w_data.apply(lambda row:\n\u001b[0;32m----> 3\u001b[0;31m                                       stations[stations[\"STATION_ID\"] == row.STATION]['LATITUDE'], axis=1)\n\u001b[0m",
      "\u001b[0;32m~/.local/share/virtualenvs/Solar_Forcaster-m3btlaz4/lib/python3.7/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36m__setitem__\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m   2936\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2937\u001b[0m             \u001b[0;31m# set column\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2938\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_set_item\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2939\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2940\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_setitem_slice\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.local/share/virtualenvs/Solar_Forcaster-m3btlaz4/lib/python3.7/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36m_set_item\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m   2999\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_ensure_valid_index\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3000\u001b[0m         \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_sanitize_column\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3001\u001b[0;31m         \u001b[0mNDFrame\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_set_item\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3002\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3003\u001b[0m         \u001b[0;31m# check if we are modifying a copy\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.local/share/virtualenvs/Solar_Forcaster-m3btlaz4/lib/python3.7/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36m_set_item\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m   3622\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3623\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_set_item\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m->\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3624\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_data\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mset\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3625\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_clear_item_cache\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3626\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.local/share/virtualenvs/Solar_Forcaster-m3btlaz4/lib/python3.7/site-packages/pandas/core/internals/managers.py\u001b[0m in \u001b[0;36mset\u001b[0;34m(self, item, value)\u001b[0m\n\u001b[1;32m   1072\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1073\u001b[0m             \u001b[0;31m# This item wasn't present, just insert at end\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1074\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mitems\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mitem\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1075\u001b[0m             \u001b[0;32mreturn\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1076\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.local/share/virtualenvs/Solar_Forcaster-m3btlaz4/lib/python3.7/site-packages/pandas/core/internals/managers.py\u001b[0m in \u001b[0;36minsert\u001b[0;34m(self, loc, item, value, allow_duplicates)\u001b[0m\n\u001b[1;32m   1179\u001b[0m         \u001b[0mnew_axis\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mitems\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mloc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mitem\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1180\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1181\u001b[0;31m         \u001b[0mblock\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mmake_block\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mndim\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mndim\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mplacement\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mslice\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mloc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mloc\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1182\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1183\u001b[0m         \u001b[0;32mfor\u001b[0m \u001b[0mblkno\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcount\u001b[0m \u001b[0;32min\u001b[0m \u001b[0m_fast_count_smallints\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_blknos\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mloc\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.local/share/virtualenvs/Solar_Forcaster-m3btlaz4/lib/python3.7/site-packages/pandas/core/internals/blocks.py\u001b[0m in \u001b[0;36mmake_block\u001b[0;34m(values, placement, klass, ndim, dtype)\u001b[0m\n\u001b[1;32m   3051\u001b[0m         \u001b[0mvalues\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mDatetimeArray\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_simple_new\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3052\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3053\u001b[0;31m     \u001b[0;32mreturn\u001b[0m \u001b[0mklass\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mndim\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mndim\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mplacement\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mplacement\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3054\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3055\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.local/share/virtualenvs/Solar_Forcaster-m3btlaz4/lib/python3.7/site-packages/pandas/core/internals/blocks.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, values, placement, ndim)\u001b[0m\n\u001b[1;32m    123\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_validate_ndim\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mndim\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmgr_locs\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m!=\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mvalues\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    124\u001b[0m             raise ValueError(\n\u001b[0;32m--> 125\u001b[0;31m                 \u001b[0;34mf\"Wrong number of items passed {len(self.values)}, \"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    126\u001b[0m                 \u001b[0;34mf\"placement implies {len(self.mgr_locs)}\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    127\u001b[0m             )\n",
      "\u001b[0;31mValueError\u001b[0m: Wrong number of items passed 22, placement implies 1"
     ]
    }
   ],
   "source": [
    "# add the meta data to the weather data\n",
    "w_data[\"latitude\"] = w_data.apply(lambda row:\n",
    "                                      stations[stations[\"STATION_ID\"] == row.STATION]['LATITUDE'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "w_data[\"longitude\"] = w_data.apply(lambda row:\n",
    "                                      float(stations[stations[\"STATION_ID\"] == row.STATION]['LONGITUDE']), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "w_data[\"elevation\"] = w_data.apply(lambda row:\n",
    "                                      float(stations[stations[\"STATION_ID\"] == row.STATION]['ELEVATION_(M)']), axis=1)\n",
    "w_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Solar data acquisition\n",
    "Now we will load the [NREL](https://nsrdb.nrel.gov/) data from the National Solar Radiation Database (NSREL). We use their api to get solar data for each station in our meta data. This will later be merged together to form a complete dataset.<br/><br/>\n",
    "We will be using the Physical Solar Model ([PSM](https://nsrdb.nrel.gov/about/u-s-data.html#psm)) v3 that NSREL provides. To get an api key, follow the guide [here](https://developer.nrel.gov/docs/solar/nsrdb/psm3_data_download/), but the api key `DEMO_KEY` will also work fine. Keep in mind that this key only has a rate limit of 30 calls a day per IP address."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set api key (use 'DEMO_KEY' if not set up)\n",
    "key = 'DEMO_KEY'\n",
    "\n",
    "import os\n",
    "key = os.getenv(\"MY_VAR\")\n",
    "\n",
    "# trim dataset to match DEMO_KEY limits\n",
    "if(key == 'DEMO_KEY'):\n",
    "    w_data = w_data[w_data[\"STATION\"].isin(w_data['STATION'].unique()[:3])]\n",
    "    print(\"Stations: \" + str(w_data['STATION'].unique()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Because the api only allows us to get one location per call, we will need to complete a series of calls. First we will create a function to create the urls."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_url(lat, lon, year, api_key, attributes, your_name, reason_for_use, your_affiliation, your_email):\n",
    "    return 'https://developer.nrel.gov/api/solar/nsrdb_psm3_download.csv?wkt=POINT({lon}%20{lat})&names={year}&leap_day=false&interval=30&utc=false&full_name={name}&email={email}&affiliation={affiliation}&mailing_list=false&reason={reason}&api_key={api}&attributes={attributes}'\\\n",
    "    .format(year=year, lat=lat, lon=lon,\n",
    "        name=your_name, email=your_email,\n",
    "        affiliation=your_affiliation,\n",
    "        reason=reason_for_use, api=api_key,\n",
    "        attributes=attributes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we will iterate through each station in our data by using Pandas' `.unique()` function. Then we will form the url and append the data into one list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "for step, point in enumerate(tqdm(stations[stations[\"STATION_ID\"].isin(w_data['STATION'].unique())].iterrows(),\n",
    "                                  total=len(w_data['STATION'].unique()))):\n",
    "    url = create_url(lat=str(point[1][7]),\n",
    "                     lon=str(point[1][8]),\n",
    "                     api_key=key,\n",
    "                     attributes=\"dhi,dni,ghi,clearsky_dhi,clearsky_dni,clearsky_ghi\",\n",
    "                     year=year,\n",
    "                     your_name='Gage+Krumbach',\n",
    "                     reason_for_use='demo',\n",
    "                     your_affiliation='my+institution',\n",
    "                     your_email='gkrumbac@redhat.com')\n",
    "    \n",
    "    output = pd.read_csv(url, header=2)\n",
    "    \n",
    "    output[\"STATION\"] = int(point[1][1])\n",
    "    output[\"DATE\"] =pd.date_range('1/1/{yr}'.format(yr=year),\n",
    "                                                    freq='30'+'Min',\n",
    "                                                    periods=525600/60)\n",
    "    if step==0:\n",
    "        solar_data = output\n",
    "    else:\n",
    "        solar_data = solar_data.append(output)\n",
    "    \n",
    "    # we wait here because the api can't be called more than once in 1 seconds\n",
    "    time.sleep(1)\n",
    "\n",
    "solar_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge solar data and weather data\n",
    "Finally we can merge the solar data and weather data. We do this by using Pandas' `merge_asof()` function which will merge two dataframes by the nearest key. Meaning, a row with a date of `1-2-2017 11:34` will merge with a date `1-2-2017 11:55`, because it is the closest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge_asof = pd.merge_asof(solar_data.sort_values(by=['DATE']), w_data.sort_values(by=['DATE']),\n",
    "              on='DATE',\n",
    "              by='STATION',\n",
    "             direction='nearest')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Altair visualization\n",
    "We can use Altair to visualize the weather stations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import altair as alt\n",
    "from vega_datasets import data\n",
    "\n",
    "states = alt.topo_feature(data.us_10m.url, feature='states')\n",
    "\n",
    "coord_center = [w_data['longitude'].unique().mean(),w_data['latitude'].unique().mean()]\n",
    "\n",
    "background = alt.Chart(states).mark_geoshape(\n",
    "    fill='#4D4D4D',\n",
    "    stroke='white'\n",
    ").project(\n",
    "    type= 'mercator',\n",
    "    scale= 500,\n",
    "    center= coord_center\n",
    ").properties(\n",
    "    title='Stations',\n",
    "    width=400, height=300\n",
    ")\n",
    "\n",
    "st = alt.Chart(stations[stations[\"STATION_ID\"].isin(w_data['STATION'].unique())]\n",
    ").mark_circle(\n",
    "    size=45,\n",
    "    color='white'\n",
    ").encode(\n",
    "    longitude='LONGITUDE:Q',\n",
    "    latitude='LATITUDE:Q',\n",
    "    tooltip=['STATION:N']\n",
    ").project(\n",
    "    type= 'mercator',\n",
    "    scale= 2000,\n",
    "    center= coord_center\n",
    ")\n",
    "\n",
    "all_st = alt.Chart(pd.read_csv('stations.csv', dtype={\"LATITUDE\": float, \"LONGITUDE\": float})\n",
    ").mark_circle(\n",
    "    size=10,\n",
    "    color='#FAB6B6'\n",
    ").encode(\n",
    "    longitude='LONGITUDE:Q',\n",
    "    latitude='LATITUDE:Q'\n",
    "    ).project(\n",
    "    type= 'mercator',\n",
    "    scale= 2000,\n",
    "    center= coord_center\n",
    ")\n",
    "\n",
    "background + all_st + st"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To cleanup the data further, we only select the columns that are relevant."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge_asof = df_merge_asof[['STATION', 'DATE', 'latitude', 'longitude', 'elevation',\n",
    "                               'Temperature', 'Dew Point', 'Relative Humidity', 'HourlyStationPressure',\n",
    "                               'Wind Direction', 'Wind Speed', 'HourlyVisibility', 'HourlyPrecipitation',\n",
    "                               'weather_type', 'cloud_type', 'cloud_cover', 'DHI', 'DNI', 'GHI',\n",
    "                               \"Clearsky DHI\",\"Clearsky DNI\",\"Clearsky GHI\"\n",
    "                              ]]\n",
    "df_merge_asof.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We take this final data and export it back out as a csv."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge_asof.to_csv(\"out.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}