<jupyter_start><jupyter_text>SQLAlchemy used under the hood of HA? Nevertheless it is used in the example notebookshttp://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.pdfGoal - class wrapper for HA dataframe. Adds convenience methods to query states at a given time. Indexes are unique id on main data dataframe, but once data is filtered to a single entity_id the index becomes a datetime object for easy plotting.<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
# Convenience functions
def isfloat(value):
    """Check if string can be parsed to a float."""
    try:
        float(value)
        return True
    except ValueError:
        return False

isfloat('23.6')
# Convenience functions
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'))<jupyter_output>True
False
nan<jupyter_text>Convenience function for checking a states.<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 14.2 ms, sys: 3.56 ms, total: 17.7 ms
Wall time: 8.99 s<jupyter_text>Lets get some useful lists of different sensors<jupyter_code>binary_sensors_list = [entity[0] for entity in entity_id_list if entity[0].split('.')[0] == 'binary_sensor'] # Print only the sensors
binary_sensors_list
devices_list = [entity[0] for entity in entity_id_list if entity[0].split('.')[0] == 'device_tracker'] # Print only the sensors
devices_list
motion_sensors_list = [entity[0] for entity in entity_id_list if 'motion' in entity[0]] # Print only the sensors
motion_sensors_list
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 for sensor in sensors_list if 'temperature' in sensor.split('.')[1]] 
temperature_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.head()
allqueryDF.info()
allqueryDF['domain'].unique()
allqueryDF.loc[0]['last_changed']<jupyter_output><empty_output><jupyter_text>Parse the dataWe need to prepare the data, performing actions such as converting strings containing numbers into floats. I've also created a couple of features, such as time_category and is_weekday.<jupyter_code>def parse_allquery(allqueryDF):
    """Lets parse the raw data into useful format."""
    parsedDF = allqueryDF[['state_id', 'domain', 'entity_id', 'state', 'last_changed']] # Keep only these cols
    parsedDF['date'] = parsedDF['last_changed'].dt.date  # Extract the date for convenience
    parsedDF['time'] = parsedDF['last_changed'].dt.time
    parsedDF['time_category'] = parsedDF['last_changed'].apply(lambda x: time_category(x)) # Generate time_category
    parsedDF['is_weekday'] = parsedDF['last_changed'].apply(lambda x: is_weekday(x)) 
    parsedDF = parsedDF[parsedDF['state']!= 'unknown']  # drop the 'unknown' values  
    parsedDF = parsedDF.set_index('state_id') # Set index
    return parsedDF
%%time
parsedDF = parse_allquery(allqueryDF)
parsedDF.head()<jupyter_output><empty_output><jupyter_text>SensorsThese have state data which are strings, convert to float. Tidy all this up so there is a single parsing operation on the input db<jupyter_code>def parse_sensor_domain(parsedDF):
    """Parse raw df from HA to return numeric sensor data."""
    parsed_df = parsedDF[parsedDF['domain'] == 'sensor']
    parsed_df = parsed_df[parsed_df['state'].apply(lambda x: isfloat(x))]  # drop values that cant be converted to float 
    parsed_df['state'] = parsed_df['state'].apply(lambda x: float(x))
    return parsed_df

sensors_df = parse_sensor_domain(parsedDF)
print(sensors_df['entity_id'].unique())<jupyter_output>['sensor.bme680humidity' 'sensor.bme680temperature' 'sensor.bme680pressure'
 'sensor.bme680air_qual' 'sensor.next_train_to_wim'
 'sensor.electric_charger_distance'
 'sensor.darksky_sensor_precip_probability'
 'sensor.darksky_sensor_temperature' 'sensor.robins_iphone_battery_level'
 'sensor.iphone_battery_level'
 'sensor.blink_blink_camera_percy_notifications'
 'sensor.blink_blink_camera_percy_temperature' 'sensor.mean_temperature'
 'sensor.bedroom_temperature' 'sensor.living_room_temperature'
 'sensor.next_train_in' 'sensor.hall_temperature' 'sensor.work_to_home'
 'sensor.home_to_waterloo' 'sensor.home_to_work' 'sensor.memory_usage_real'
 'sensor.cpu_load_total' 'sensor.volume_used_volume_1' 'sensor.network_up'
 'sensor.speedtest_download']<jupyter_text>Temperature sensorsLets get only the temperature sensors<jupyter_code>temperature_sensors_df = sensors_df[sensors_df['entity_id'].isin(temperature_sensors_list)]
temperature_sensors_df.head()
temperature_sensors_df['state'].hist(bins=10);  # Plot histogram of all temperature sensors
plt.xlabel("Temperature $^\circ$C");<jupyter_output><empty_output><jupyter_text>Clearly the outside temperature is lower!<jupyter_code>temperature_sensors_df['state'][
    temperature_sensors_df['entity_id'] == 'sensor.darksky_sensor_temperature'].hist(
    bins=50); 
plt.title("Outside temperature");
plt.xlabel("Temperature $^\circ$C");
temperature_sensors_df['state'][
    temperature_sensors_df['entity_id'] != 'sensor.darksky_sensor_temperature'].hist(
    bins=50); 

plt.title("Inside temperature");
plt.xlabel("Temperature $^\circ$C");<jupyter_output><empty_output><jupyter_text>Query a single sensor, the Dark Sky outside temperature<jupyter_code>dark_sky = temperature_sensors_df[
    temperature_sensors_df['entity_id']=='sensor.darksky_sensor_temperature']
dark_sky = dark_sky.set_index('last_changed')
dark_sky.head()<jupyter_output><empty_output><jupyter_text>Have some state unknowns to filter. Also state is a string, so convert to floats for plotting.<jupyter_code>dark_sky['state'].plot(figsize=(20,5));
plt.ylabel("Temperature $^\circ$C");<jupyter_output><empty_output><jupyter_text>Parse devicesdevice_tracker registers significant changes in location of a device. I need to translate this into a function which will return whether a device is home at the time of a sensor reading.<jupyter_code>devices_list
def parse_devices_domain(df):
    """Parse raw df from HA to return numeric sensor data."""
    parsed_df = df[df['domain'] == 'device_tracker']
    return parsed_df

devices_df = parse_devices_domain(parsedDF)
print(devices_df['entity_id'].unique())
devices_df['state'].unique()<jupyter_output><empty_output><jupyter_text>Get a single device<jupyter_code>robin_df = devices_df[devices_df['entity_id'] == 'device_tracker.robins_iphone'].set_index('last_changed').drop_duplicates() # Can set this index as will be unique for single device
robin_df[0:10]<jupyter_output><empty_output><jupyter_text>Use state_at_time() to check the last registered state at some query time. Need to update this function to accept string datetimes.<jupyter_code>def get_device_state(parsedDF, device_id, dtObj):
    """Convenience that takes a device id and returns the state at a dtObj"""
    try:
        df = parsedDF[parsedDF['entity_id'] == device_id].set_index(
            'last_changed').drop_duplicates() # the df for device_id with index on last_changed
        state = state_at_time(df, dtObj)
        return state
    except:
        return float('nan')

dtObj = datetime(2017,10,30,9)
device_id = 'device_tracker.robins_iphone'
print("At {} {} is {}".format(dtObj, device_id, get_device_state(parsedDF, device_id, dtObj)))<jupyter_output>At 2017-10-30 09:00:00 device_tracker.robins_iphone is not_home<jupyter_text>Binary sensors<jupyter_code>%%time
def parse_binary_sensor_domain(parsedDF):
    """Parse raw df from HA to return a single domain."""
    parsed_df = parsedDF[parsedDF['domain'] == 'binary_sensor']
    parsed_df['state'] = parsed_df['state'].apply(lambda x: binary_state(x))
  #  parsed_df['robin'] = parsed_df['last_changed'].apply(
   #     lambda x: get_device_state(parsedDF, 'device_tracker.robins_iphone', x))
    return parsed_df

binary_sensors_df = parse_binary_sensor_domain(parsedDF)
print(binary_sensors_df['entity_id'].unique())
dtObj = binary_sensors_df.iloc[0]['last_changed']
device_id = 'device_tracker.robins_iphone'
print("At {} {} is {}".format(dtObj, device_id, get_device_state(parsedDF, device_id, dtObj)))<jupyter_output>At 2017-10-30 05:46:00 device_tracker.robins_iphone is not_home<jupyter_text>Need to speed up the following<jupyter_code>%%time
binary_sensors_df['robin'] = binary_sensors_df['last_changed'].apply(
    lambda x: get_device_state(parsedDF, 'device_tracker.robins_iphone', x))
binary_sensors_df.head()
dtObj = binary_sensors_df['last_changed'].iloc[0]
device_id = 'device_tracker.robins_iphone'
print("At {} {} is {}".format(dtObj, device_id, get_device_state(parsedDF, device_id, dtObj)))<jupyter_output>At 2017-10-30 05:46:00 device_tracker.robins_iphone is not_home<jupyter_text>Motion at home sensorLets focus on the motion sensors<jupyter_code>motion_sensors_df = parsedDF[parsedDF['entity_id'].isin(motion_sensors_list)]
motion_sensors_df['state'] = motion_sensors_df['state'].apply(lambda x: binary_state(x)) # Binarise
motion_sensors_df= motion_sensors_df[motion_sensors_df['state']!=0]                      # Get only motion events
motion_sensors_df.head()<jupyter_output>/usr/local/lib/python3.6/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy<jupyter_text>Try a facet grid to represent data https://seaborn.pydata.org/generated/seaborn.FacetGrid.html?highlight=facetseaborn.FacetGrid<jupyter_code>motion_at_home_df = binary_sensors_df[binary_sensors_df['entity_id']=='binary_sensor.motion_at_home']
motion_at_home_df = motion_at_home_df[motion_at_home_df['state']==True] # Since on/off are always paired for motion, drop false
motion_at_home_df.head()
fg = sns.FacetGrid(data=motion_at_home_df[motion_at_home_df['is_weekday']==0], hue='time_category', aspect=5)  # 
fg.map(plt.scatter, 'time', 'state').add_legend()
g = sns.FacetGrid(data=motion_at_home_df, row="is_weekday",  hue="time_category", aspect=3, size=3)
g = (g.map(plt.scatter, "time", "state", edgecolor="w").add_legend())<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11281622730380612, 'fraction_numerical': 0.011167666945225252, 'mean_word_length': 6.013319126265317, 'pattern_counts': {'":': 0, '<': 81, '<?xml version=': 0, '>': 75, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 1, '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': '20', 'dir': 'jupyter-structured-clean-dedup', 'id': '573568', 'license': 'MIT', 'n_tokens_mistral': 4437, 'n_tokens_neox': 4255, 'n_words': 1026, 'path': 'SQL/Assorted/SQLAlchemy HASS 11-11-2017.ipynb', 'repo_name': 'afcarl/Useful-python', 'size': '13163'}