<jupyter_start><jupyter_text>Forecasting Energy Demand Data WranglingThe project consists of two data sets:* Hourly electricity demand data from the EIA;* Hourly observed weather data from LCD/NOAA. Additionally to demand and weather data, I'll create features based on time to see how the trends are impacted by day of week, hour, week of year, if is holiday, etc.To limit the scope of the project, I'll use data from Los Angeles exclusively to validate if is possible to improve electricity demand forecasting using weather data.<jupyter_code>import boto3
import io
from sagemaker import get_execution_role

role = get_execution_role()
bucket ='sagemaker-data-energy-demand'
S3_CLIENT = boto3.client('s3')
files_list = S3_CLIENT.list_objects_v2(Bucket=bucket, Prefix='raw_data/weather/')
s3_files = files_list['Contents']        
latest_weather_data = max(s3_files, key=lambda x: x['LastModified'])

weather_data_location = 's3://{}/{}'.format(bucket, latest_weather_data['Key'])
import requests
import json
import datetime
import pandas as pd
from scipy import stats
from pandas.io.json import json_normalize
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

import warnings
warnings.filterwarnings('ignore')<jupyter_output><empty_output><jupyter_text>Electricity data Electricity data were retrieved using EIA’s API and then unpacked into a dataframe. The API contain hourly entries from July 2015 to present.The electricity data required just simple cleaning. There were few null values in the set and a very small number of outliers. Removing outliers cut only ~.01% of the data.<jupyter_code>EIA5__API_KEY = '1d48c7c8354cc4408732174250d3e8ff'
REGION_CODE = 'LDWP'
CITY = 'LosAngeles'

def str_to_isodatetime(string):
    '''
    This function transforms strings to an ISO Datetime.
    '''
    year = string[:4]
    month = string[4:6]
    day =  string[6:8]
    time = string[8:11] + ':00:00+0000'
    return year + month + day + time

def eia2dataframe(response):
    '''
    This function unpacks the JSON file from EIA API into a pandas dataframe.
    '''
    data = response['series'][0]['data']
    dates = []
    values = []
    for date, demand in data:
        if demand is None or demand <= 0:    
            continue   
        dates.append(str_to_isodatetime(date))
        values.append(float(demand))
    df = pd.DataFrame({'datetime': dates, 'demand': values})
    df['datetime'] = pd.to_datetime(df['datetime'])
    df.set_index('datetime', inplace=True)
    df = df.sort_index()
    return df

electricity_api_response = requests.get('http://api.eia.gov/series/?api_key=%s&series_id=EBA.%s-ALL.D.H' % (EIA__API_KEY, REGION_CODE)).json()
electricity_df = eia2dataframe(electricity_api_response)
electricity_df.isnull().sum()
print(res)<jupyter_output>01:00:00    39873
dtype: int64<jupyter_text>Observed weather dataLCD data are not available via NOAA’s API so I manually downloaded from the website as a CSV file which I imported to a pandas DataFrame. As common in data that come from physical sensors, LCD data required extensive cleansing.The main challenges in cleaning the LCD data was that there were in some cases multiple entries for the same hour. I wanted to have just one entry per hour such that I could eventually align LCD data with the hourly entries in the electricity data.I wrote a function that group weather data by hour and the mode of the entries for same hour. I performed the cleaning this way because either way, the values for multiple per-hour entries are very similar, so the choice of which entry to keep doesn’t make a real difference.<jupyter_code>def fix_date(df):
    '''
    This function goes through the dates in the weather dataframe and if there is more than one record for each
    hour, we pick the record closest to the hour and drop the rows with the remaining records for that hour.
    This is so we can align this dataframe with the one containing electricity data.
    input: Pandas DataFrame
    output: 
    '''
    
    df['date'] = pd.to_datetime(df['date']).dt.tz_localize('UTC')
    df['date_rounded'] = df['date'].dt.floor('H')
    df.drop('date', axis=1, inplace=True)
    df.rename({"date_rounded": "datetime"}, axis=1, inplace=True)
    df.set_index('datetime', inplace=True)
    last_of_hour = df[~df.index.duplicated(keep='last')]    
    last_of_hour.sort_index(ascending=True, inplace=True, kind='mergesort')
    
    return last_of_hour
def clean_sky_condition(df):
    '''
    This function cleans the hourly sky condition column by assigning the hourly sky condition to be the one at the
    top cloud layer, which is the best determination of the sky condition, as described by the documentation.
    input: Pandas DataFrame
    output: 
    '''
    conditions = df['hourlyskyconditions']
    new_condition = []
    for k, condition in enumerate(conditions):
        if type(condition) != str and np.isnan(condition):
            new_condition.append(np.nan)
        else:
            colon_indices = [i for i, char in enumerate(condition) if char == ':']
            n_layers = len(colon_indices)
            try:
                colon_position = colon_indices[n_layers - 1]
                if condition[colon_position - 1] == 'V':
                    condition_code = condition[colon_position - 2 : colon_position]
                else:
                    condition_code = condition[colon_position - 3 : colon_position]
                new_condition.append(condition_code)
            except:
                new_condition.append(np.nan)

    df['hourlyskyconditions'] = new_condition
    df['hourlyskyconditions'] = df['hourlyskyconditions'].astype('category')
    return df

def hourly_degree_days(df):
    '''
    This function adds hourly heating and cooling degree days to the weather DataFrame.
    '''
    df['hourlycoolingdegrees'] = df['hourlydrybulbtemperature'].apply(lambda x: x - 65. if x >= 65. else 0.)
    df['hourlyheatingdegrees'] = df['hourlydrybulbtemperature'].apply(lambda x: 65. - x if x <= 65. else 0.)
    return df

# import csv
weather_df = pd.read_csv(weather_data_location, usecols=['DATE', 'DailyCoolingDegreeDays', 'DailyHeatingDegreeDays', 'HourlyDewPointTemperature', 'HourlyPrecipitation', 'HourlyRelativeHumidity', 'HourlySeaLevelPressure', 'HourlySkyConditions', 'HourlyStationPressure', 'HourlyVisibility', 'HourlyDryBulbTemperature', 'HourlyWindSpeed'],
                        dtype={
                                'DATE': object,
                                'DailyCoolingDegreeDays': object,
                                'DailyHeatingDegreeDays': object,
                                'HourlyDewPointTemperature': object,
                                'HourlyPrecipitation': object,
                                'HourlyRelativeHumidity': object,
                                'HourlySeaLevelPressure': object,
                                'HourlySkyConditions': object,
                                'HourlyStationPressure': object,
                                'HourlyVisibility': object,
                                'HourlyDryBulbTemperature': object,
                                'HourlyWindSpeed': object
                        })

# make columns lowercase for easier access
weather_df.columns = [col.lower() for col in weather_df.columns]

# clean dataframe so that there's only one record per hour
weather_df = fix_date(weather_df)

# fill the daily heating and cooling degree days such that each hour in an individual day has the same value
weather_df['dailyheatingdegreedays'] = weather_df['dailyheatingdegreedays'].apply(lambda x: float(x) if str(x)[-1] != 's' else float(str(x)[:-1]))
weather_df.dailyheatingdegreedays.astype('float64')
weather_df['dailycoolingdegreedays'] = weather_df['dailycoolingdegreedays'].apply(lambda x: float(x) if str(x)[-1] != 's' else float(str(x)[:-1]))
weather_df.dailycoolingdegreedays.astype('float64')
weather_df['dailyheatingdegreedays'] = weather_df['dailyheatingdegreedays'].bfill()
weather_df['dailycoolingdegreedays'] = weather_df['dailycoolingdegreedays'].bfill()

weather_df = clean_sky_condition(weather_df)

# clean other columns by replacing string based values with floats
# values with an 's' following indicate uncertain measurments. we simply change those to floats and include them like normal
weather_df['hourlyvisibility'] = weather_df['hourlyvisibility'].apply(lambda x: float(x) if str(x)[-1] != 'V' else float(str(x)[:-1]))

weather_df['hourlydrybulbtemperature'] = weather_df['hourlydrybulbtemperature'].apply(lambda x: float(x) if str(x)[-1] != 's' else float(str(x)[:-1]))

weather_df['hourlydewpointtemperature'] = weather_df['hourlydewpointtemperature'].apply(lambda x: float(x) if str(x)[-1] != 's' else float(str(x)[:-1]))

# set trace amounts equal to zero and change data type
weather_df['hourlyprecipitation'].where(weather_df['hourlyprecipitation'] != 'T', 0.0, inplace=True)
weather_df['hourlyprecipitation'] = weather_df['hourlyprecipitation'].apply(lambda x: float(x) if str(x)[-1] != 's' else float(str(x)[:-1]))
weather_df['hourlystationpressure'] = weather_df['hourlystationpressure'].apply(lambda x: float(x) if str(x)[-1] != 's' else float(str(x)[:-1]))
weather_df['hourlywindspeed'] = weather_df['hourlywindspeed'].apply(lambda x: float(x) if str(x)[-1] != 's' else float(str(x)[:-1]))
weather_df['hourlyrelativehumidity'] = weather_df['hourlyrelativehumidity'].apply(lambda x: float(x) if str(x)[-1] != 's' else float(str(x)[:-1]))
weather_df['hourlysealevelpressure'] = weather_df['hourlysealevelpressure'].apply(lambda x: float(x) if str(x)[-1] != 's' else float(str(x)[:-1]))

weather_df.hourlyprecipitation.astype('float64')
weather_df.hourlyvisibility.astype('float64')
weather_df.hourlyrelativehumidity.astype('float64')
weather_df.hourlysealevelpressure.astype('float64')
weather_df.hourlystationpressure.astype('float64')
weather_df.hourlywindspeed.astype('float64')

weather_df = hourly_degree_days(weather_df)
weather_df.hourlyrelativehumidity.astype('float64')
weather_df.hourlysealevelpressure.astype('float64')
weather_df.dtypes
## Cut dataframes based on date to align sources
cut_electricity = electricity_df[:weather_df.index.max()]
cut_weather = weather_df[electricity_df.index.min():]<jupyter_output><empty_output><jupyter_text>Dealing with outliers and NaN valuesThe plot distributions bof the features below is used to determine what columns should be filled by using the medianand which should be filled according to ffill. The features whose ```medians``` and ```means``` are close together suggest that the ```median``` is a good choice for NaNs.Conversely features whose median and means are further apart suggest the presence of outliers and in this case I use ```ffill``` because we are dealing with time series and values in previous time steps are useful in predicting values for later time steps<jupyter_code>diff = max(cut_weather.index) - min(cut_electricity.index) 

days, seconds = diff.days, diff.seconds
hours = days * 24 + seconds // 3600
minutes = (seconds % 3600) // 60
seconds = seconds % 60

number_of_steps = hours + 1
print('*** min ***')
print(min(cut_electricity.index))
print(min(cut_weather.index))
print(cut_weather.index.min() == cut_electricity.index.min())
print('*** max ***')
print(max(cut_electricity.index))
print(max(cut_weather.index))
print(cut_weather.index.max() == cut_electricity.index.max())
print('*** instances quantity is equal? ***')
print(cut_weather.shape[0] == cut_electricity.shape[0])
print('*** weather, demand, expected ***')
print(cut_weather.shape[0], cut_electricity.shape[0], number_of_steps)
fill_dict = {'median': ['dailyheatingdegreedays', 'hourlyaltimetersetting', 'hourlydrybulbtemperature', 'hourlyprecipitation', 'hourlysealevelpressure', 'hourlystationpressure', 'hourlywetbulbtempf', 'dailycoolingdegreedays', 'hourlyvisibility', 'hourlywindspeed', 'hourlycoolingdegrees', 'hourlyheatingdegrees'], 'ffill': ['demand', 'hourlydewpointtemperature', 'hourlyrelativehumidity']}

# fill electricity data NaNs
for col in cut_electricity.columns:
    if col in fill_dict['median']:
        cut_electricity[col].fillna(cut_electricity[col].median(), inplace=True)
    else:
        cut_electricity[col].fillna(cut_electricity[col].ffill(), inplace=True)

# fill weather data NaNs
for col in cut_weather.columns:
    if col == 'hourlyskyconditions':
        cut_weather[col].fillna(cut_weather[col].value_counts().index[0], inplace=True) 
    elif col in fill_dict['median']:
        cut_weather[col].fillna(cut_weather[col].median(), inplace=True)
    else:
        cut_weather[col].fillna(cut_weather[col].ffill(), inplace=True)

print(cut_weather.shape[0] == cut_electricity.shape[0])
electricity_set = set(cut_electricity.index)
weather_set = set(cut_weather.index)
print(len(electricity_set.difference(weather_set)))
# finally merge the data to get a complete dataframe for LA, ready for training
merged_df = cut_weather.merge(cut_electricity, right_index=True, left_index=True, how='inner')
merged_df = pd.get_dummies(merged_df)
merged_df.head()
merged_df.index.name = 'datetime'
if 'hourlyskyconditions_VV' in list(merged_df.columns):
    merged_df.drop('hourlyskyconditions_VV', axis=1, inplace=True)
if 'hourlyskyconditions_' in list(merged_df.columns):
    merged_df.drop('hourlyskyconditions_', axis=1, inplace=True) 
# save as csv file to continue in another notebook
csv_buffer = io.StringIO()
s3_resource = boto3.resource('s3')
key = 'dataframes/%s_dataset.csv' % CITY

merged_df.to_csv(csv_buffer, compression=None)
s3_resource.Object(bucket, key).put(Body=csv_buffer.getvalue())<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup