home-assistant/notebooks/boiler_warning.ipynb

In [12]:

import pandas as pd
from datetime import datetime
from zoneinfo import ZoneInfo

from utils.database import get_state

local_tz = ZoneInfo("Europe/Copenhagen")

df = get_state(
    entity_id="sensor.shellyplus1pm_345f452121a8_power",
    # limit=100,
    start_time=datetime(2026, 1, 23, 0, 0, tzinfo=local_tz),
    end_time=datetime(2026, 1, 24, 2, 0, tzinfo=local_tz),
)

# Convert time to Copenhagen timezone
df["time"] = df["time"].dt.tz_convert(local_tz)

# Convert state to numeric, forcing errors to NaN
df["state"] = pd.to_numeric(df["state"], errors="coerce")

df.head()

Out [12]:

	time	state
0	2026-01-24 01:59:57.148284+01:00	69.0
1	2026-01-24 01:59:56.138423+01:00	87.6
2	2026-01-24 01:59:55.148949+01:00	105.3
3	2026-01-24 01:59:54.140738+01:00	95.8
4	2026-01-24 01:59:37.148956+01:00	69.0

In [13]:

import plotly.express as px

# Create interactive line plot
fig = px.line(
    df,
    x="time",
    y="state",
    title="Power Consumption Over Time",
    labels={"time": "Time", "state": "Power (W)"},
)

# Customize hover template
fig.update_traces(hovertemplate="<b>Time:</b> %{x}<br><b>Power:</b> %{y:.2f} W<extra></extra>")

fig.update_layout(height=600, hovermode="x unified")

fig.show()

[Data output - unsupported data type map[string]interface {} for mime type application/vnd.plotly.v1+json]

Aggregate over 1 minute pieces to avoid varying sampling frequency

In [14]:

# Calculate running average with time-based window
window_size = "1min"  # Can use: '1min', '5min', '15min', '1h', etc.
df["state_smoothed"] = df.rolling(window=window_size, on="time", center=True)["state"].mean()

# Create plot with both raw and smoothed data
fig = px.line(
    df,
    x="time",
    y=["state", "state_smoothed"],
    title=f"Power Consumption - Raw vs Smoothed (window={window_size})",
    labels={"time": "Time", "value": "Power (W)"},
)

# Update trace names
fig.data[0].name = "Raw"
fig.data[1].name = "Smoothed"

# Customize hover template
fig.update_traces(hovertemplate="<b>Time:</b> %{x}<br><b>Power:</b> %{y:.2f} W<extra></extra>")

fig.update_layout(height=600, hovermode="x unified", legend_title_text="Data Type")

fig.show()

[Data output - unsupported data type map[string]interface {} for mime type application/vnd.plotly.v1+json]

In [15]:

# Slice out data from period with healthy boiler operation
slice_start = datetime(2026, 1, 23, 0, 0, tzinfo=local_tz)
slice_end = datetime(2026, 1, 24, 0, 0, tzinfo=local_tz)

# Filter dataframe to time range
df_healthy = df[(df["time"] >= slice_start) & (df["time"] <= slice_end)].copy()

print(f"Selected {len(df_healthy)} records from {slice_start} to {slice_end}")
df_healthy.head()

Out [15]:

Selected 14832 records from 2026-01-23 00:00:00+01:00 to 2026-01-24 00:00:00+01:00

	time	state	state_smoothed
783	2026-01-23 23:59:47.293393+01:00	67.4	84.025000
784	2026-01-23 23:59:46.282721+01:00	87.9	84.025000
785	2026-01-23 23:59:45.292127+01:00	102.5	84.025000
786	2026-01-23 23:59:44.282316+01:00	90.8	84.025000
787	2026-01-23 23:59:27.292454+01:00	67.4	81.827273

In [ ]:

# Identify periods when power above 200W
threshold = 200

# Create boolean mask when starting burner
df_healthy["above_threshold"] = df_healthy["state"] > threshold

# Identify transitions (when above_threshold changes)
df_healthy["transition"] = df_healthy["above_threshold"].ne(df_healthy["above_threshold"].shift())

# Create group ID for each continuous period
df_healthy["period_id"] = df_healthy["transition"].cumsum()

# Filter only periods above threshold
high_power_groups = df_healthy[df_healthy["above_threshold"]].groupby("period_id")

# Calculate duration for each period
periods = []
for period_id, group in high_power_groups:
    start_time = group["time"].min()
    end_time = group["time"].max()
    duration = (end_time - start_time).total_seconds()  # Duration in seconds
    periods.append(
        {
            "period_id": period_id,
            "start": start_time,
            "end": end_time,
            "duration_seconds": duration,
            "duration_minutes": duration / 60,
        }
    )

periods_df = pd.DataFrame(periods)

# Calculate statistics
mean_duration = periods_df["duration_minutes"].mean()
std_duration = periods_df["duration_minutes"].std()
total_periods = len(periods_df)

print(f"Found {total_periods} periods above {threshold}W")
print(f"Mean duration: {mean_duration:.2f} minutes")
print(f"Std duration: {std_duration:.2f} minutes")
print("\nPeriod details:")
periods_df

Found 7 periods above 200W
Mean duration: 4.83 minutes
Std duration: 0.71 minutes

Period details:

	period_id	start	end	duration_seconds	duration_minutes
0	2	2026-01-23 21:04:09.503776+01:00	2026-01-23 21:08:00.022894+01:00	230.519118	3.841985
1	4	2026-01-23 17:45:17.740772+01:00	2026-01-23 17:49:18.725014+01:00	240.984242	4.016404
2	6	2026-01-23 14:25:44.968623+01:00	2026-01-23 14:31:06.337639+01:00	321.369016	5.356150
3	8	2026-01-23 11:05:48.218650+01:00	2026-01-23 11:11:00.017227+01:00	311.798577	5.196643
4	10	2026-01-23 07:46:25.445047+01:00	2026-01-23 07:50:53.440529+01:00	267.995482	4.466591
5	12	2026-01-23 04:26:19.684041+01:00	2026-01-23 04:31:39.678008+01:00	319.993967	5.333233
6	14	2026-01-23 01:05:46.933333+01:00	2026-01-23 01:11:23.918698+01:00	336.985365	5.616423

In [24]:

# Estimate healthy light up maximum duration
duration_limit = mean_duration + 2 * std_duration

# Convert to minutes and seconds
minutes = int(duration_limit)
seconds = int((duration_limit - minutes) * 60)

print(f"Duration limit for warnings should be set to: {minutes} minutes and {seconds} seconds")

Duration limit for warnings should be set to: 6 minutes and 15 seconds

Conclusion

Alert should be sent out when the boiler power is above 200W for more than 6 minutes and 15 seconds.

In [ ]:

# HAPSQL! package name for pypi!