<jupyter_start><jupyter_text>Goal - convenience functions to parse HA data into useful format for data science. SQLAlchemy is used in this notebook but other suitable libraries are available.http://nbviewer.jupyter.org/github/home-assistant/home-assistant-notebooks/blob/master/DataExploration-1/DataExploration-1.ipynbhttp://www.sqlalchemy.org/https://www.dataquest.io/blog/images/cheat-sheets/pandas-cheat-sheet.pdfhttps://chrisalbon.com/ Useful ref<jupyter_code>%matplotlib inline
from sqlalchemy import create_engine, text
import json
from datetime import datetime, timedelta
import seaborn as sns
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>Some helper functions for parsing HA data<jupyter_code>def time_category(dtObj):
    """Return a time category, bed, home, work, given a datetime object dtObj."""
    if 9 <= dtObj.hour <= 17:
        return 'daytime'
    elif 5 <= dtObj.hour < 9:
        return 'morning'
    elif 17 < dtObj.hour < 23:
        return 'evening'
    else:
        return 'night'
def is_weekday(dtObj):
    """Check a datetime object dtObj is a weekday"""
    if dtObj.weekday() < 5:
        return True
    else:
        return False
def isfloat(value):
    """Check if string can be parsed to a float."""
    try:
        float(value)
        return True
    except ValueError:
        return False

isfloat('23.6')
def binary_state(value):
    """Return a binary for the state of binary sensors"""
    if  value == 'on':
        return True
    elif value == 'off':
        return False
    else:
        return float('nan')

print(binary_state('on'))
print(binary_state('off'))
print(binary_state('foo'))
def rename_entity(entity_id):
    """Takes an entity_if of form sensor.name and returns name."""
    return entity_id.split('.')[1]

rename_entity('sensor.bme680humidity')<jupyter_output><empty_output><jupyter_text>Convenience function for checking a state. This is incredibly slow when applied to large data frames. Better approach is place data on time index and fill.<jupyter_code>def state_at_time(df, dtObj):
    """Check the last registered state at some time using asof.
    Passed df needs to be time indexed and should only include the entitiy_id of interest."""
    found_index = df.index.asof(dtObj)     # Find the closest index asof time test_time
    if pd.isnull(found_index):             # If dtObj is before the first index of dt, will return NaT
        return float('nan')
    else:
        state = df.loc[found_index]['state']      # Get the state at the found_index
        return state<jupyter_output><empty_output><jupyter_text>Fetch the data Important to add +pymysql to the following, otherwise got import error<jupyter_code>DB_URL = "mysql+pymysql://hass:12345@192.168.0.30/homeassistant?charset=utf8"
engine = create_engine(DB_URL)
%%time
entity_id_list =  list(engine.execute("SELECT entity_id, COUNT(*) FROM states \
GROUP BY entity_id ORDER by 2 DESC"))<jupyter_output>CPU times: user 17.8 ms, sys: 5.83 ms, total: 23.6 ms
Wall time: 1min 12s<jupyter_text>Lets get some useful lists of different sensors<jupyter_code>sensors_list = [entity[0] for entity in entity_id_list if entity[0].split('.')[0] == 'sensor'] # Print only the sensors
sensors_list
temperature_sensors_list = [sensor.split('.')[1] for sensor in sensors_list if 'temperature' in sensor.split('.')[1]] 
temperature_sensors_list
motion_sensors_list = [entity[0] for entity in entity_id_list if 'motion' in entity[0]] # Print only the sensors
motion_sensors_list<jupyter_output><empty_output><jupyter_text>Select all state data for last X days, store in a single dataframe for convenience<jupyter_code>%%time
# query to pull all rows form the states table where last_changed field is on \
# or after the date_filter value
stmt = text("SELECT * FROM states where last_changed>=:date_filter")

# bind parameters to the stmt value, specifying the date_filter to be 10 days \
# before today
stmt = stmt.bindparams(date_filter=datetime.now()-timedelta(days=100))

# execute the SQL statement
allquery = engine.execute(stmt)

# get rows from query into a pandas dataframe
allqueryDF = pd.DataFrame(allquery.fetchall())
allqueryDF.shape<jupyter_output><empty_output><jupyter_text>Name the columns, need to look into the difference between last_changed & last_updated<jupyter_code>allqueryDF.columns = ['state_id', 'domain', 'entity_id', 'state', 'attributes',
                      'origin', 'last_changed', 'last_updated',
                      'created'] # 'event_id' no longer exists?
allqueryDF.info()
allqueryDF['domain'].unique()
allqueryDF.loc[0]['last_changed']  # We will use this timestamp as keys<jupyter_output><empty_output><jupyter_text>Sensors 21-11-2017Sensors have state data which are strings, convert to float.<jupyter_code>%%time

def parse_sensor_domain(raw_df):
    """Parse raw df from HA to return numeric sensor data."""
    df = raw_df[raw_df['domain'] == 'sensor']  # get only sensors
    df = df[['entity_id', 'state', 'last_changed']] # Keep only these cols
    df['entity'] = df['entity_id'].apply(lambda x: rename_entity(x)) # Lets change sensor.name to name and rename col for clarity
    df.drop('entity_id', axis=1, inplace=True)
    df = df[df['state'].apply(lambda x: isfloat(x))]  # drop values that cant be converted to float 
    df['state'] = df['state'].apply(lambda x: float(x)) # Convert remaining data to floats
    df = df.pivot_table(index='last_changed', columns='entity', values='state', aggfunc='mean') # Perform a pivot. Where there are duplicates, take the mean
    return df

sensors_df = parse_sensor_domain(allqueryDF)<jupyter_output>CPU times: user 9.22 s, sys: 1.76 s, total: 11 s
Wall time: 11.2 s<jupyter_text>Now have a nice dataframe for analysis<jupyter_code>sensors_df.head()<jupyter_output><empty_output><jupyter_text>Temperature sensorsLets get only the temperature sensors<jupyter_code>temperature_sensors_df = sensors_df[temperature_sensors_list]
temperature_sensors_df.dropna(axis=0, how='all', inplace=True) # Drop any rows with all NaN
temperature_sensors_df.head()
temperature_sensors_df.describe()
temperature_sensors_df.corr()
blink_temperature = temperature_sensors_df['blink_blink_camera_percy_temperature']
blink_temperature.shape
blink_temperature.plot(style='ro-', figsize=(20,5))  # Have lots of NaN in the data<jupyter_output><empty_output><jupyter_text>Lots of NaN to deal with. First forward fill the NaN<jupyter_code>blink_temperature.fillna(method='ffill').plot(figsize=(20,5))<jupyter_output><empty_output><jupyter_text>Now compare unfilled<jupyter_code>height=6
width=14

ax = temperature_sensors_df.plot(figsize=(width, height))
ax.set_ylim(0,23)
ax.legend(loc='center left', bbox_to_anchor=(1, 0.5));<jupyter_output><empty_output><jupyter_text>Now with filled data<jupyter_code>temperature_sensors_df_filled = temperature_sensors_df.fillna(method='ffill')
temperature_sensors_df_filled.dropna(axis=0, how='any', inplace=True)  # Drop any remaining NaN

ax = temperature_sensors_df_filled.plot(figsize=(width, height))
ax.set_ylim(0,23)
ax.legend(loc='center left', bbox_to_anchor=(1, 0.5));<jupyter_output><empty_output><jupyter_text>Definitelty the filled data is easier to compare.Now lets look at correlations using a pairplot. Clearly the indoor temperatures are well correlated whilst the outdoor temperature stands apart.<jupyter_code>sns.pairplot(temperature_sensors_df_filled[['living_room_temperature', 
                                             'hall_temperature', 
                                             'bedroom_temperature', 
                                             'darksky_sensor_temperature']])<jupyter_output><empty_output><jupyter_text>And just the histograms.<jupyter_code>height=12
width=16

temperature_sensors_df.hist(bins=40, figsize=(width, height));  # Plot histogram of all temperature sensors<jupyter_output><empty_output><jupyter_text>Air pressure vs rainfall likelihoodWe should see an inverse correlation between my measured air pressure (sensor.bme680pressure) and dark sky rainfall likelihood (sensor.darksky_sensor_precip_probability). We don't see that correlation, perhaps more data is required.<jupyter_code>sensors_df.plot.scatter('bme680pressure', 'darksky_sensor_precip_probability')
sensors_df[['darksky_sensor_precip_probability']].dropna().hist(bins=10)
ax.legend(loc='center left', bbox_to_anchor=(1, 0.5));
sensors_df[['bme680pressure']].dropna().hist(bins=10)
ax.legend(loc='center left', bbox_to_anchor=(1, 0.5));<jupyter_output><empty_output><jupyter_text>Motion at home sensorLets focus on the binary_sensor.motion_at_home which is a sensor that indicates if there is any activity at home. In the pivot_table apply np.min to prevent np.mean (the default) from returning 0.5 in some cases (why is this?)<jupyter_code>motion_df = allqueryDF[allqueryDF['entity_id'] == 'binary_sensor.motion_at_home']
motion_df['state'] = motion_df['state'].apply(lambda x: binary_state(x)) # Binarise
motion_df = motion_df.pivot_table(index='last_changed', columns='entity_id', values='state', aggfunc=np.min)
motion_df.head()
np.unique(motion_df.values)<jupyter_output><empty_output><jupyter_text>Create some featuresLets get the weekday<jupyter_code>motion_df['weekday'] = motion_df.index.weekday_name
motion_df.head()
motion_df['time'] = motion_df.index.time
motion_df['datetime'] = motion_df.index # Convenience to operate on datetime
motion_df['time_category'] = motion_df['datetime'].apply(lambda x: time_category(x))
motion_df['is_weekday'] = motion_df['datetime'].apply(lambda x: is_weekday(x))
motion_df = motion_df[motion_df['binary_sensor.motion_at_home'] == True] # Keep only true detection events
motion_df.head()<jupyter_output><empty_output><jupyter_text>Analysis of motion dataTry a facet grid to represent data https://seaborn.pydata.org/generated/seaborn.FacetGrid.html?highlight=facetseaborn.FacetGridLets see if there is more motion at home on weekends.<jupyter_code>motion_df['binary_sensor.motion_at_home'].groupby(motion_df['is_weekday']).describe()['count']<jupyter_output><empty_output><jupyter_text>OK, so there is more activity at home on weekdays, but there are also 5 days in the working week and 2 in the weekend.Now, how about activity by weekday.<jupyter_code>motion_df['binary_sensor.motion_at_home'].groupby(motion_df['weekday']).describe()['count'].sort_values()<jupyter_output><empty_output><jupyter_text>And activity by weekday and time category.<jupyter_code>df = motion_df['binary_sensor.motion_at_home'].groupby([motion_df['weekday'], motion_df['time_category']]).sum().unstack()
df.fillna(value=0, inplace=True)   # Replace NaN with 0
df = df.astype('int')              # Ints rather than floats
df = df.T
df = df[['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']]
df = df.reindex(["morning", "daytime", "evening", "night"])
df
f, ax = plt.subplots(figsize=(14, 6))
sns.heatmap(df, annot=True, linewidths=.5, fmt="d", ax=ax, cmap='Reds');<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11373293553928217, 'fraction_numerical': 0.012476267968538107, 'mean_word_length': 5.580606781677573, 'pattern_counts': {'":': 0, '<': 90, '<?xml version=': 0, '>': 84, 'https://': 3, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '21', 'dir': 'jupyter-structured-clean-dedup', 'id': '470054', 'license': 'MIT', 'n_tokens_mistral': 3712, 'n_tokens_neox': 3481, 'n_words': 910, 'path': 'archive/HASS data science 7-12-2017.ipynb', 'repo_name': 'robmarkcole/HASS-data-science', 'size': '11061'}