In [1]:
!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
In [0]:
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
In [5]:
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])))
Upload widget is only available when the cell has been executed in the current browser session. Please rerun this cell to enable.
Saving home-assistant_v2.db to home-assistant_v2.db
User uploaded file "home-assistant_v2.db" with length 11096064 bytes
In [6]:
ls
datalab/  home-assistant_v2.db
In [11]:
os.getcwd()
Out[11]:
'/content'
In [16]:
db_file = '/home-assistant_v2.db'
DB_URL = 'sqlite:////' + os.getcwd() + db_file
DB_URL
Out[16]:
'sqlite://///content/home-assistant_v2.db'
In [17]:
%%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
In [18]:
db.domains
Out[18]:
['persistent_notification',
 'light',
 'sun',
 'group',
 'automation',
 'sensor',
 'history_graph',
 'camera']

Simple query

In [23]:
db.fetch_all_data()
Querying the database, this could take a while
master_df created successfully.
In [0]:
sensors_num_df = detective.NumericalSensors(db.master_df)
In [31]:
sensors_num_df.entities
Out[31]:
['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']
In [27]:
sensors_num_df.data.head()
Out[27]:
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
In [32]:
sensors_num_df.plot('sensor.yweather_wind_speed')