{
"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",
" state | \n",
" last_updated_ts | \n",
" entity_id | \n",
"
\n",
" \n",
" \n",
" \n",
" | 0 | \n",
" 0 | \n",
" 1.680324e+09 | \n",
" zone.home | \n",
"
\n",
" \n",
"
\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",
" state | \n",
" last_updated_ts | \n",
" entity_id | \n",
" shared_attrs | \n",
"
\n",
" \n",
" \n",
" \n",
" | 0 | \n",
" 2023-04-01T12:04:03+00:00 | \n",
" 2023-04-01 04:39:46.026 | \n",
" sensor.sun_next_noon | \n",
" {\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl... | \n",
"
\n",
" \n",
" | 1 | \n",
" 2023-04-01T06:00:49+00:00 | \n",
" 2023-04-01 04:39:46.026 | \n",
" sensor.sun_next_rising | \n",
" {\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl... | \n",
"
\n",
" \n",
" | 2 | \n",
" 2023-04-01T18:07:00+00:00 | \n",
" 2023-04-01 04:39:46.026 | \n",
" sensor.sun_next_setting | \n",
" {\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl... | \n",
"
\n",
" \n",
" | 3 | \n",
" 2023-04-01T18:28:08+00:00 | \n",
" 2023-04-01 04:39:46.025 | \n",
" sensor.sun_next_dusk | \n",
" {\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl... | \n",
"
\n",
" \n",
" | 4 | \n",
" 2023-04-02T00:03:37+00:00 | \n",
" 2023-04-01 04:39:46.025 | \n",
" sensor.sun_next_midnight | \n",
" {\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl... | \n",
"
\n",
" \n",
" | 5 | \n",
" 2023-04-01T05:39:42+00:00 | \n",
" 2023-04-01 04:39:46.024 | \n",
" sensor.sun_next_dawn | \n",
" {\"device_class\":\"timestamp\",\"icon\":\"mdi:sun-cl... | \n",
"
\n",
" \n",
"
\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",
" state | \n",
" last_updated_ts | \n",
" entity_id | \n",
" shared_attrs | \n",
"
\n",
" \n",
" \n",
" \n",
"
\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",
" created_ts | \n",
" start_ts | \n",
" last_reset_ts | \n",
" mean | \n",
" max | \n",
" sum | \n",
" state | \n",
" statistic_id | \n",
" source | \n",
" unit_of_measurement | \n",
" has_mean | \n",
" has_sum | \n",
"
\n",
" \n",
" \n",
" \n",
"
\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
}