<jupyter_start><jupyter_text>EnergyUsagePrediction.data_exp.asum.v1_0_5.ipynbFor my use case "Energy usage prediction based on historical weather and energy usage data.", I use a dataset which can be downloaded from kaggleThe last line of the csv contains garbage. I've already removed that line before starting the data exploration.This task is crucial for understanding your data. Data quality is the most important driver for success in any data science project. So, this task lets you address data quality from the beginning. This includes going back to the data owners and asking them for better quality data, if applicable.Load smart-home-dataset-with-weather-information_filtered.csv file into pandas dataframe.Explore data.<jupyter_code>import types
import numpy as np
import pandas as pd
from botocore.client import Config
import ibm_boto3

def __iter__(self): return 0

# @hidden_cell
# The following code accesses a file in your IBM Cloud Object Storage. It includes your credentials.
# You might want to remove those credentials before you share the notebook.
client_x = ibm_boto3.client(service_name='s3',
    ibm_api_key_id='[credentials]]',
    ibm_auth_endpoint="https://iam.cloud.ibm.com/oidc/token",
    config=Config(signature_version='oauth'),
    endpoint_url='https://s3.eu-geo.objectstorage.service.networklayer.com')

body = client_x.get_object(Bucket='xyz',Key='smart-home-dataset-with-weather-information_filtered.csv')['Body']
# add missing __iter__ method, so pandas accepts body as file-like object
if not hasattr(body, "__iter__"): body.__iter__ = types.MethodType( __iter__, body )

df = pd.read_csv(body)
df.head()<jupyter_output>/opt/conda/envs/Python-3.7-main/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3063: DtypeWarning: Columns (27) have mixed types.Specify dtype option on import or set low_memory=False.
  interactivity=interactivity, compiler=compiler, result=result)<jupyter_text>Inspect data typesThe pandas dataframe df contains our csv content.Let have a look at the data types. df.types<jupyter_code>df.dtypes<jupyter_output><empty_output><jupyter_text>From the dataset description we known that 'time' is a one minute based timeline for 350 daysAll 'xyz [kW]' items contain information about energy usage or generation.So let's in spect the other columns first.Most values are float or integer. icon, summary and cloudCover are of type object.check unique items of icon, summary and cloudCover columns to see if this are numbers or textual items<jupyter_code>df['icon'].unique()
df['summary'].unique()
df['cloudCover'].unique()<jupyter_output><empty_output><jupyter_text>As you can see icon and summary contain small lists of items (enumerators).cloudCover contains text and floats values.we can either remove the textual items or replace them. First we need to know how much bad (garbage) data is inside this column.<jupyter_code>garbage = df['cloudCover']
(df[garbage == 'cloudCover'])['cloudCover'].count()
df['cloudCover'].count()<jupyter_output><empty_output><jupyter_text>So we have 58 garbage items in a total of 503910 items. Less than 0.012% Let's have look at the mean, standard defiation, min, and max values. The pandas dataframe methode desribe has al these properties build in. To make it more readable we swap rows and columns.<jupyter_code>df.describe().transpose()<jupyter_output><empty_output><jupyter_text>For our use case we need the weather information as input and energy usage as output.So we can remove all 'xyz [kW]' items except for 'use [kW]'. But we'll do that in the etl part of the project. Let's check if we can convert are time column to a pandas timestamp<jupyter_code>pd.to_datetime(df['time'], unit='s')
df2 = pd.to_datetime(df['time'], unit ='s')
print(df2.min())
print(df2.max())<jupyter_output>2016-01-01 05:00:00
2016-01-07 00:58:29<jupyter_text>Ok, it can be converted to timestamps, but according to the dataset description, it should be 1-minute timestep. So in our ETL we need to convert fix the timestamp to be real 1 minute steps and convert it to day of year, total minutes of day, etc.We can use the icon indicator to find the first occurance of 'day' and 'night'.<jupyter_code>def printFirstIndexOf(label, value):
    matches =  (df[df[label] == value]).index
    if(matches.size >0):
        print(str(value) + ", first match:" + str(matches[0]))
        return
    print(str(value) + ' was not found in ' + label)


#print(firstIndexOf('icon', 'clear-night'))
printFirstIndexOf('icon', 'clear-night')
printFirstIndexOf('icon', 'partly-cloudy-night')
printFirstIndexOf('icon', 'clear-day')
printFirstIndexOf('icon', 'cloudy')
printFirstIndexOf('icon', 'partly-cloudy-day')
printFirstIndexOf('icon', 'rain')
printFirstIndexOf('icon', 'snow')
printFirstIndexOf('icon', 'wind')
printFirstIndexOf('icon', 'fog')



print(df['icon'].iloc[0])
print(df['icon'].iloc[58])
print(df['icon'].iloc[464])
print(df['icon'].iloc[754])
print(df['icon'].iloc[2030])
print(df['icon'].iloc[12876])
print(df['icon'].iloc[16298])
print(df['icon'].iloc[25578])
print(df['icon'].iloc[106082])<jupyter_output>clear-night
partly-cloudy-night
clear-day
cloudy
partly-cloudy-day
rain
snow
wind
fog<jupyter_text>Ok, so the first occurance of day is at 464: 7.73 hours after first occurance of midnight. If the first data item would indeed be '2016-01-01 05:00:00' then our first day item would be at 12.44 That could be if true if you have a very short period of sunlight during day (Daytime is from sunrise (this varies, but we can say approximately 6am) to sunset (we can say approximately 6pm). Night-time is from sunset to sunrise. Every day starts precisely at midnight. AM (ante-meridiem = before noon) starts just after midnight.) But then the number of daytime items following after 12.44 should be rather small.Let's check if we have still day items after sunrise<jupyter_code>dayOffset = 460
minutesInDay = 24*60

def getDayOrNight(value):
    if(value == 'clear-night'):
        return '0'
    if(value == 'partly-cloudy-night'):
        return '0'
    if(value == 'clear-day'):
        return '1'
    if(value == 'partly-cloudy-day'):
        return '1'
    return '?'

def printDayValues(days):
    print(''
        + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)]) 
        + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+60])
        + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+120])
        + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+180])
        + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+240])
        + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+300])
        + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+360])
        + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+420])
        + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+480])
        + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+540])
        + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+600])
        + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+660])
        + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+720])
          + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+780])
          + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+840])
          + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+900])
          + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+960])
          + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+1020])
          + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+1080])
          + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+1140])
          + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+1200])
          + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+1260])
          + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+1320])
          + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+1380])
          + ', ' + getDayOrNight(df['icon'].iloc[dayOffset+(days*minutesInDay)+1440])
        + ' ('+ str(days)+')' 
    )


#t1
printDayValues(1)
#t1+month
printDayValues(31)

#t1+ 2 months
printDayValues(61)

#t1+ 3 months
printDayValues(92)

#t1+ 4 months
printDayValues(120)

#t1+ 5 months
printDayValues(153)

#t1+ 6 months
printDayValues(183)

#t1+ 7 months
printDayValues(214)

#t1+ 8 months
printDayValues(244)

# plus 9 months
printDayValues(275)

# plus 10 months
printDayValues(305)

# plus 11 months
printDayValues(330)


# almost 12 months
printDayValues(348)


#lets have a look at the icon distrubution
print(df['icon'].hist(xrot=90))<jupyter_output>, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, ?, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1 (1)
, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ?, ?, ?, ? (31)
, 1, 1, ?, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1 (61)
, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1 (92)
, ?, 1, ?, ?, 1, 1, ?, 1, ?, 0, ?, 0, 0, 0, ?, 0, 0, 0, 1, 1, ?, 1, 1, 1, 1 (120)
, ?, 1, ?, 1, ?, ?, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, ?, 1 (153)
, ?, 1, 1, 1, 1, 0, 0, ?, 0, ?, ?, 0, ?, 0, 1, 1, ?, 1, 1, ?, 1, ?, 1, ?, ? (183)
, ?, ?, ?, ?, ?, ?, ?, 1, 1, 1, 1, ?, 1, ?, ?, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1 (214)
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, ?, ?, ?, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0 (244)
, 1, 1, 1, ?, ?, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1 (275)
, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0 (305)
, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1 (330)
, 0, 1, 1, 1, ?, ?, ?[...]<jupyter_text>From the histogram we can see that the amount of night-items and the day-items looks fairly 50/50. But the shifts in start of day seem quite extreme to me, we don't exactly know where the house is located. But is it an indication of poor data. So I think it might be wise to leave out the icon data in our model. Now let's see how the energy usage is distributed. For this we can use boxplot from matplotlib<jupyter_code>energySamples = df['use [kW]'].sample(n=100, random_state=5)
%matplotlib inline
import matplotlib.pyplot as plt
plt.boxplot(energySamples)
plt.show()<jupyter_output><empty_output><jupyter_text>The use [kW] has outliers above approximately. We can     ignore the outliers.    we can remove all items with outliers    or fill them we the mean value or with zeros.All of the above solutions can have a huge influence on performance of the model. Let's have a look at the energy usaga through time.We can see here that if we cut of above 2 kW, we loose quite some data.<jupyter_code>#samples = df[['time','use [kW]']].sample(n=100, random_state=5).sort_index()
samples = df[['time','use [kW]']]
samples_x=samples['time']
samples_y = samples['use [kW]']

plt.plot(samples_x,samples_y)
plt.xlabel('time')
plt.ylabel('use [kW]')
plt.show()<jupyter_output><empty_output><jupyter_text>Let's have a look at the temperature, humidity, windspeed and dewpoint as well.<jupyter_code>#Temperature has no outliers
temperatureSamples = df['temperature'].sample(n=100, random_state=5)
plt.boxplot(temperatureSamples)
plt.show()

#samples = df[['time','temperature']].sample(n=100, random_state=5).sort_index()
samples = df[['time','temperature']]
samples_x=samples['time']
samples_y = samples['temperature']

plt.plot(samples_x,samples_y)
plt.xlabel('time')
plt.ylabel('temperature')
plt.show()
#Windspeed has outliers avbove 15  (mph? km/h)
windSpeedSamples = df['windSpeed'].sample(n=100, random_state=5)
plt.boxplot(windSpeedSamples)
plt.show()

#samples = df[['time','windSpeed']].sample(n=100, random_state=5).sort_index()
samples = df[['time','windSpeed']]
samples_x=samples['time']
samples_y = samples['windSpeed']

plt.plot(samples_x,samples_y)
plt.xlabel('time')
plt.ylabel('windSpeed')
plt.show()
#Humitidy show no outliers
humiditySamples = df['humidity'].sample(n=100, random_state=5)
plt.boxplot(humiditySamples)
plt.show()


#samples = df[['time','humidity']].sample(n=100, random_state=5).sort_index()
samples = df[['time','humidity']]
samples_x=samples['time']
samples_y = samples['humidity']

plt.plot(samples_x,samples_y)
plt.xlabel('time')
plt.ylabel('humidity')
plt.show()
#Pressure show outliers below 1000 and above 1030
pressureSamples = df['pressure'].sample(n=100, random_state=5)
plt.boxplot(pressureSamples)
plt.show()


#samples = df[['time','pressure']].sample(n=100, random_state=5).sort_index()
samples = df[['time','pressure']]
samples_x=samples['time']
samples_y = samples['pressure']

plt.plot(samples_x,samples_y)
plt.xlabel('time')
plt.ylabel('pressure')
plt.show()
#Pressure show outliers no outliers
dewPointSamples = df['dewPoint'].sample(n=100, random_state=5)
plt.boxplot(dewPointSamples)
plt.show()


#samples = df[['time','dewPoint']].sample(n=100, random_state=5).sort_index()
samples = df[['time','dewPoint']]
samples_x=samples['time']
samples_y = samples['dewPoint']

plt.plot(samples_x,samples_y)
plt.xlabel('time')
plt.ylabel('dewPoint')
plt.show()<jupyter_output><empty_output><jupyter_text>The histogram function also gives nice information about the destribution of each parameterThis also makes the outliers nice visible. use [kW] has outliers starting at 2kW up to 15 kW (!)<jupyter_code>dfSubSet = df[[ 'temperature','windSpeed','humidity','pressure', 'dewPoint','use [kW]']]

dfSubSet.hist(figsize = (12,10))<jupyter_output><empty_output><jupyter_text>What's the correlation towards 'use [kW]' Let's have a look at the correlations between 'use [kW]' and the weather data properties<jupyter_code>dfSubSet = df[['time', 'temperature','windSpeed','humidity','pressure', 'dewPoint','use [kW]']]
corr_matrix=dfSubSet.corr()
corr_matrix['use [kW]'].sort_values(ascending=False)<jupyter_output><empty_output><jupyter_text>The use [kW] has some correlation with dewPoint, humidity, pressure and temperature and a very small correlation with tim e and windSpeed. The correlation with time could be stronger if we split this day of year part and minute of day part. At least that's the for my personal situation. But let's not make assumptions<jupyter_code>from pandas.plotting import scatter_matrix
attributes=['use [kW]', 'temperature','windSpeed','humidity','pressure', 'dewPoint']
scatter_matrix(df[attributes].sample(n=10000, random_state=5).sort_index(), figsize=(10,10))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15460683352656346, 'fraction_numerical': 0.04446566187001296, 'mean_word_length': 5.2854693527646806, 'pattern_counts': {'":': 0, '<': 53, '<?xml version=': 0, '>': 54, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '551820', 'license': 'MIT', 'n_tokens_mistral': 5772, 'n_tokens_neox': 5052, 'n_words': 1456, 'path': 'EnergyUsagePrediction.data_exp.asum.v1_0_5.ipynb', 'repo_name': 'eeuwevandijk/energy-usage-prediction', 'size': '14663'}