<jupyter_start><jupyter_text>In this notebook we do some more advanced analysis using data from a 4 GB database<jupyter_code>!pip show HASS-data-detective|grep Version
import detective.core as detective
import pandas as pd
import matplotlib.pyplot as plt
import os
%matplotlib inline
pd.options.mode.chained_assignment = None  # Suppress chained_assignment warning, default='warn'
db_path = 'Users/robincole/Documents/Home-assistant_database/home-assistant_v2.db'
%%time
db = detective.HassDatabase('sqlite:////' + db_path) 
db.domains
db.entities['binary_sensor']<jupyter_output><empty_output><jupyter_text>Query all dataData-detective takes care of parsing data from the database, intelligently sorting out numerical and categorical data and formatting them correctly. Use fetch_all_data to import all your db data into a pandas dataframe in memory -> this approach means it can take a while to load the data into memory, but subsequent processing and handling are much faster/easier.<jupyter_code>%%time
db.fetch_all_data(limit=1e9) # Pass in a high limit to return all data<jupyter_output>Querying the database, this could take a while
master_df created successfully.
CPU times: user 17.1 s, sys: 19 s, total: 36.1 s
Wall time: 1min 32s<jupyter_text>The unprocessed dataframe is in the `master_df` attribute<jupyter_code>f"There are {db.master_df.shape[0]/1e6} million rows of data in the database"<jupyter_output><empty_output><jupyter_text>Numerical sensor dataWe now do additional processing to put numerical sensor data into its own dataframe<jupyter_code>sensors_num = detective.NumericalSensors(db.master_df)
f"There are {sensors_num.data.shape[0]/1e6} million rows of data in the sensors_num dataframe"
sensors_num.data.head()<jupyter_output><empty_output><jupyter_text>Lets plot a few entities<jupyter_code>to_plot = ['sensor.living_room_temperature',
           'sensor.bedroom_temperature',
           'sensor.darksky_sensor_temperature']

sensors_num.plot(to_plot)<jupyter_output><empty_output><jupyter_text>We can export the dataframe to a csv<jupyter_code>sensors_num.data.to_csv('data/sensors_num.csv')
f"Exporting all the numerical data to csv created a file of size {os.stat('data/sensors_num.csv').st_size/1e6} MB"<jupyter_output><empty_output><jupyter_text>We can push files [up to 100MB](https://help.github.com/articles/what-is-my-disk-quota/) to Github  Binary dataThe BinarySensors class is for binary sensor data with on/off states.<jupyter_code>sensors_binary = detective.BinarySensors(db.master_df)
f"There are {sensors_binary.data.shape[0]/1e3} thousand rows of data in the sensors_binary dataframe"
sensors_binary.entities<jupyter_output><empty_output><jupyter_text>Currently we can plot a single binary sensor with the plot() method<jupyter_code>sensors_binary.plot('binary_sensor.motion_at_home')<jupyter_output><empty_output><jupyter_text>Lets create a csv for the binary dataframe also<jupyter_code>sensors_binary.data.to_csv('data/sensors_binary.csv')<jupyter_output><empty_output><jupyter_text>Day of week analysisLets analyse the `motion_at_home,` create some features for day of week and time category, then analyse motion by these features.<jupyter_code>motion_df = sensors_binary.data[['binary_sensor.motion_at_home']] # Must pass a list to return correctly indexed df
motion_df.head()<jupyter_output><empty_output><jupyter_text>We create our first feature `weekday` from the index<jupyter_code>motion_df['weekday'] = motion_df.index.weekday_name
motion_df.head()<jupyter_output><empty_output><jupyter_text>Detective has a couple of helpers to create additional features<jupyter_code>from detective.time import is_weekday, time_category
motion_df['is_weekday'] = motion_df.index.map(lambda x: is_weekday(x))
motion_df['time_category'] = motion_df.index.map(lambda x: time_category(x))<jupyter_output><empty_output><jupyter_text>Lets keep only true detection events<jupyter_code>motion_df = motion_df[motion_df['binary_sensor.motion_at_home'] == True]<jupyter_output><empty_output><jupyter_text>We can now see the new features in the dataframe<jupyter_code>motion_df.head()<jupyter_output><empty_output><jupyter_text>We can now use a `groupby` operation to get the number of motion counts per day<jupyter_code>motion_df_gb = motion_df['binary_sensor.motion_at_home'].groupby([motion_df['weekday'], motion_df['time_category']]).sum().unstack()
motion_df_gb.fillna(value=0, inplace=True)   # Replace NaN with 0
motion_df_gb = motion_df_gb.astype('int')              # Ints rather than floats
motion_df_gb = motion_df_gb.T
motion_df_gb<jupyter_output><empty_output><jupyter_text>Finally we can use seaborn to visualise this data<jupyter_code>import seaborn as sns
fig, ax = plt.subplots(figsize=(14, 6))
days_list = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
times_list = ['morning', 'daytime', 'evening', 'night']
ax = sns.heatmap(motion_df_gb[days_list].loc[times_list], annot=True, linewidths=.5, fmt="d", ax=ax, cmap='Reds');
ax.set_title('Activity at home by day and time category')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09901768172888016, 'fraction_numerical': 0.0068762278978389, 'mean_word_length': 7.599662162162162, 'pattern_counts': {'":': 0, '<': 64, '<?xml version=': 0, '>': 65, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '135773', 'license': 'MIT', 'n_tokens_mistral': 1758, 'n_tokens_neox': 1664, 'n_words': 421, 'path': 'Advanced_usage.ipynb', 'repo_name': 'robmarkcole/HASS-data-detective-analysis', 'size': '5090'}