!pip install HASS-data-detective
Collecting HASS-data-detective
Downloading https://files.pythonhosted.org/packages/25/92/3e8835e94617e5d2edc84c8fae68ead866d7f13448085c3b5709d2c03a6d/HASS-data-detective-0.4.tar.gz
Requirement already satisfied: requests in /usr/local/lib/python3.6/dist-packages (from HASS-data-detective) (2.18.4)
Requirement already satisfied: matplotlib in /usr/local/lib/python3.6/dist-packages (from HASS-data-detective) (2.1.2)
Requirement already satisfied: pandas in /usr/local/lib/python3.6/dist-packages (from HASS-data-detective) (0.22.0)
Collecting sqlalchemy (from HASS-data-detective)
Downloading https://files.pythonhosted.org/packages/b4/9c/411a9bac1a471bed54ec447dc183aeed12a75c1b648307e18b56e3829363/SQLAlchemy-1.2.8.tar.gz (5.6MB)
100% |████████████████████████████████| 5.6MB 5.7MB/s
Requirement already satisfied: idna<2.7,>=2.5 in /usr/local/lib/python3.6/dist-packages (from requests->HASS-data-detective) (2.6)
Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.6/dist-packages (from requests->HASS-data-detective) (2018.4.16)
Requirement already satisfied: urllib3<1.23,>=1.21.1 in /usr/local/lib/python3.6/dist-packages (from requests->HASS-data-detective) (1.22)
Requirement already satisfied: chardet<3.1.0,>=3.0.2 in /usr/local/lib/python3.6/dist-packages (from requests->HASS-data-detective) (3.0.4)
Requirement already satisfied: pytz in /usr/local/lib/python3.6/dist-packages (from matplotlib->HASS-data-detective) (2018.4)
Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.1 in /usr/local/lib/python3.6/dist-packages (from matplotlib->HASS-data-detective) (2.2.0)
Requirement already satisfied: six>=1.10 in /usr/local/lib/python3.6/dist-packages (from matplotlib->HASS-data-detective) (1.11.0)
Requirement already satisfied: cycler>=0.10 in /usr/local/lib/python3.6/dist-packages (from matplotlib->HASS-data-detective) (0.10.0)
Requirement already satisfied: numpy>=1.7.1 in /usr/local/lib/python3.6/dist-packages (from matplotlib->HASS-data-detective) (1.14.3)
Requirement already satisfied: python-dateutil>=2.1 in /usr/local/lib/python3.6/dist-packages (from matplotlib->HASS-data-detective) (2.5.3)
Building wheels for collected packages: HASS-data-detective, sqlalchemy
Running setup.py bdist_wheel for HASS-data-detective ... - done
Stored in directory: /content/.cache/pip/wheels/d8/bd/89/6e155dd59f79ccd003f8f6756e75148b71abd48bd1bb2d6b4d
Running setup.py bdist_wheel for sqlalchemy ... - \ | / - \ | / done
Stored in directory: /content/.cache/pip/wheels/df/fc/61/df2f43ec3f11f864554bdc006a866a3ffffa59740bcf3674ef
Successfully built HASS-data-detective sqlalchemy
tensorflow 1.2.0 has requirement markdown==2.2.0, but you'll have markdown 2.6.11 which is incompatible.
Installing collected packages: sqlalchemy, HASS-data-detective
Successfully installed HASS-data-detective-0.4 sqlalchemy-1.2.8
import os
import sys
import detective.core as detective
import detective.helpers as helpers
import numpy as np
import pandas as pd
pd.options.mode.chained_assignment = None # Suppress chained_assignment warning, default='warn'
import seaborn as sns
from sqlalchemy import text
import matplotlib.pyplot as plt
%matplotlib inline
https://github.com/robmarkcole/HASS-data-detective/blob/master/usage/Usage%20of%20detective.ipynb
Lets upload my HA .db file
from google.colab import files
uploaded = files.upload()
for fn in uploaded.keys():
print('User uploaded file "{name}" with length {length} bytes'.format(
name=fn, length=len(uploaded[fn])))
Saving home-assistant_v2.db to home-assistant_v2.db User uploaded file "home-assistant_v2.db" with length 11096064 bytes
ls
datalab/ home-assistant_v2.db
os.getcwd()
'/content'
db_file = '/home-assistant_v2.db'
DB_URL = 'sqlite:////' + os.getcwd() + db_file
DB_URL
'sqlite://///content/home-assistant_v2.db'
%%time
db = detective.HassDatabase(DB_URL)
Successfully connected to database There are 40 entities with data CPU times: user 5 ms, sys: 2 ms, total: 7 ms Wall time: 11.2 ms
db.domains
['persistent_notification', 'light', 'sun', 'group', 'automation', 'sensor', 'history_graph', 'camera']
Simple query
db.fetch_all_data()
Querying the database, this could take a while master_df created successfully.
sensors_num_df = detective.NumericalSensors(db.master_df)
sensors_num_df.entities
['sensor.yweather_visibility', 'sensor.yweather_wind_speed', 'sensor.yweather_humidity', 'sensor.yweather_temperature_min', 'sensor.yweather_temperature_max', 'sensor.yweather_temperature', 'sensor.yweather_pressure', 'sensor.database_sensor', 'sensor.filesize_sensor', 'sensor.homeassistant_v2db', 'sensor.homeassistantlog', 'sensor.phueconf', 'sensor.homeassistant_v2dbwal']
sensors_num_df.data.head()
entity | sensor.database_sensor | sensor.filesize_sensor | sensor.homeassistant_v2db | sensor.homeassistant_v2dbwal | sensor.homeassistantlog | sensor.phueconf | sensor.yweather_humidity | sensor.yweather_pressure | sensor.yweather_temperature | sensor.yweather_temperature_max | sensor.yweather_temperature_min | sensor.yweather_visibility | sensor.yweather_wind_speed |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
last_changed | |||||||||||||
2018-01-23 21:21:48.448609 | 7.88 | 8.42 | 10.32 | 4.16 | 0.0 | 0.0 | 83.0 | 1009.0 | 12.0 | 13.0 | 8.0 | 16.09 | 17.99 |
2018-01-23 21:21:48.452004 | 7.88 | 8.42 | 10.32 | 4.16 | 0.0 | 0.0 | 83.0 | 1009.0 | 12.0 | 13.0 | 8.0 | 16.09 | 17.99 |
2018-01-23 21:21:48.453546 | 7.88 | 8.42 | 10.32 | 4.16 | 0.0 | 0.0 | 83.0 | 1009.0 | 12.0 | 13.0 | 8.0 | 16.09 | 17.99 |
2018-01-23 21:21:49.541845 | 7.88 | 8.42 | 10.32 | 4.16 | 0.0 | 0.0 | 83.0 | 1009.0 | 12.0 | 13.0 | 8.0 | 16.09 | 17.99 |
2018-01-23 21:21:49.543196 | 7.88 | 8.42 | 10.32 | 4.16 | 0.0 | 0.0 | 83.0 | 1009.0 | 12.0 | 13.0 | 8.0 | 16.09 | 17.99 |
sensors_num_df.plot('sensor.yweather_wind_speed')