{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "Notebook showing usage of the data detective." ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "# To import detective from relative folder\n", "import detective.core as detective\n", "import detective.functions as functions\n", "from pathlib import Path\n", "import pandas as pd" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Supply the db_url as described in the [recorder integration docs](https://www.home-assistant.io/integrations/recorder/)." ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "db_url = f\"sqlite:///{Path('../tests/test.db').resolve()}\"" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Successfully connected to database sqlite:////Users/robin.cole/Documents/GitHub/HASS-data-detective/tests/test.db\n", "There are 9 entities with data\n" ] } ], "source": [ "db = detective.HassDatabase(db_url) # To init without fetching entities fetch_entities=False" ] }, { "cell_type": "markdown", "metadata": { "collapsed": false, "inputHidden": false, "jupyter": { "outputs_hidden": false }, "outputHidden": false }, "source": [ "Alternatively, detective can discover your database credentials" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false, "inputHidden": false, "jupyter": { "outputs_hidden": false }, "outputHidden": false }, "outputs": [], "source": [ "# db = detective.db_from_hass_config()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Entities are listed on an attribute" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "['persistent_notification.config_entry_discovery',\n", " 'sensor.sun_next_dawn',\n", " 'sensor.sun_next_dusk',\n", " 'sensor.sun_next_midnight',\n", " 'sensor.sun_next_noon',\n", " 'sensor.sun_next_rising',\n", " 'sensor.sun_next_setting',\n", " 'sun.sun',\n", " 'zone.home']" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db.entities" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Use `fetch_all_data_of()` to get data for a single entity" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", " WITH combined_states AS (\n", " SELECT states.state, states.last_updated_ts, states_meta.entity_id\n", " FROM states\n", " JOIN states_meta\n", " ON states.metadata_id = states_meta.metadata_id\n", " )\n", " SELECT *\n", " FROM combined_states\n", " WHERE \n", " entity_id IN ('zone.home')\n", " AND\n", " state NOT IN ('unknown', 'unavailable')\n", " ORDER BY last_updated_ts DESC\n", " LIMIT 50000\n", "The returned Pandas dataframe has 1 rows of data.\n" ] } ], "source": [ "df = db.fetch_all_data_of(('zone.home',))" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
statelast_updated_tsentity_id
001.680324e+09zone.home
\n", "
" ], "text/plain": [ " state last_updated_ts entity_id\n", "0 0 1.680324e+09 zone.home" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.head()" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "Use `fetch_all_sensor_data()` to fetch all your sensor data into a pandas dataframe in memory. \n", "\n", "**Note** that by default the number of states returned is limited but this can optionally be over-ridden as shown below. " ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", " SELECT states.state,\n", " datetime(states.last_updated_ts, 'unixepoch', 'subsec') as last_updated_ts,\n", " states_meta.entity_id,\n", " state_attributes.shared_attrs\n", " FROM states\n", " JOIN states_meta ON states.metadata_id = states_meta.metadata_id\n", " LEFT JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id\n", " WHERE states_meta.entity_id LIKE '%sensor%'\n", " AND states.state NOT IN ('unknown',\n", " 'unavailable')\n", " ORDER BY last_updated_ts DESC\n", " LIMIT 100000\n", "The returned Pandas dataframe has 6 rows of data.\n" ] } ], "source": [ "df = db.fetch_all_sensor_data(limit=100000)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Take a minute to read about the differences between [wide-form and long-form data](https://altair-viz.github.io/user_guide/data.html#long-form-vs-wide-form-data). The Pandas dataframe we have is in long form." ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
statelast_updated_tsentity_idshared_attrs
02023-04-01T12:04:03+00:002023-04-01 04:39:46.026sensor.sun_next_noon{\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl...
12023-04-01T06:00:49+00:002023-04-01 04:39:46.026sensor.sun_next_rising{\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl...
22023-04-01T18:07:00+00:002023-04-01 04:39:46.026sensor.sun_next_setting{\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl...
32023-04-01T18:28:08+00:002023-04-01 04:39:46.025sensor.sun_next_dusk{\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl...
42023-04-02T00:03:37+00:002023-04-01 04:39:46.025sensor.sun_next_midnight{\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl...
52023-04-01T05:39:42+00:002023-04-01 04:39:46.024sensor.sun_next_dawn{\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl...
\n", "
" ], "text/plain": [ " state last_updated_ts \\\n", "0 2023-04-01T12:04:03+00:00 2023-04-01 04:39:46.026 \n", "1 2023-04-01T06:00:49+00:00 2023-04-01 04:39:46.026 \n", "2 2023-04-01T18:07:00+00:00 2023-04-01 04:39:46.026 \n", "3 2023-04-01T18:28:08+00:00 2023-04-01 04:39:46.025 \n", "4 2023-04-02T00:03:37+00:00 2023-04-01 04:39:46.025 \n", "5 2023-04-01T05:39:42+00:00 2023-04-01 04:39:46.024 \n", "\n", " entity_id shared_attrs \n", "0 sensor.sun_next_noon {\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl... \n", "1 sensor.sun_next_rising {\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl... \n", "2 sensor.sun_next_setting {\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl... \n", "3 sensor.sun_next_dusk {\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl... \n", "4 sensor.sun_next_midnight {\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl... \n", "5 sensor.sun_next_dawn {\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl... " ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "It is necessary to format the data before we can plot it, and detective provides a functions to assist" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [], "source": [ "df = functions.format_dataframe(df)" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
statelast_updated_tsentity_idshared_attrs
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [state, last_updated_ts, entity_id, shared_attrs]\n", "Index: []" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "It is straightforward to create your own features, for example to add a `day_of_week` column" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [], "source": [ "df['day_of_week'] = df['last_updated_ts'].apply(lambda x : x.dayofweek)\n", "df['is_temperature'] = df['entity_id'].apply(lambda x : 'temperature' in x)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can fetch [long-term statistics](https://data.home-assistant.io/docs/statistics/) using a separate function" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", " WITH combined_states AS (\n", " SELECT\n", " statistics.created_ts,\n", " statistics.start_ts,\n", " statistics.last_reset_ts,\n", " statistics.mean,\n", " statistics.max,\n", " statistics.sum,\n", " statistics.state,\n", " statistics_meta.statistic_id,\n", " statistics_meta.source,\n", " statistics_meta.unit_of_measurement,\n", " statistics_meta.has_mean,\n", " statistics_meta.has_sum\n", " FROM statistics\n", " JOIN statistics_meta\n", " ON statistics.metadata_id = statistics_meta.id\n", " )\n", " SELECT *\n", " FROM combined_states\n", " WHERE \n", " statistic_id IN ('sensor.temperature', 'sensor:temperature')\n", " ORDER BY created_ts DESC\n", " \n", "The returned Pandas dataframe has 0 rows of data.\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
created_tsstart_tslast_reset_tsmeanmaxsumstatestatistic_idsourceunit_of_measurementhas_meanhas_sum
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [created_ts, start_ts, last_reset_ts, mean, max, sum, state, statistic_id, source, unit_of_measurement, has_mean, has_sum]\n", "Index: []" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_long_term = db.fetch_all_statistics_of(('sensor.temperature',), limit=None)\n", "df_long_term" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "From here you can begin plotting" ] } ], "metadata": { "kernelspec": { "display_name": "venv (3.12.9)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.12.9" }, "nteract": { "version": "0.15.0" } }, "nbformat": 4, "nbformat_minor": 4 }