<jupyter_start><jupyter_text>Read back `hourly` data from database<jupyter_code>year = dt.date.today().year
wss = db.session.query(WalliStat).filter(WalliStat.campaign_id==0,
                                         WalliStat.datetime>=dt.date(year,1,1),
                                         WalliStat.datetime< dt.date(year,12,31)).all()
df = WalliStat.to_dataframe(wss)
df.tail(2)<jupyter_output><empty_output><jupyter_text>Remove non-useful columns<jupyter_code>df.columns
df = df.drop(['I_L1', 'I_L2', 'I_L3', 'V_L1', 'V_L2', 'V_L3', 'extern_lock_state', 'charging_state', 
              'energy_pwr_on', 'I_max_cfg', 'I_min_cfg', 'modbus_watchdog_timeout', 'power_kW', 
              'remote_lock', 'I_max_cmd', 'I_fail_safe', 'campaign_id'], axis=1).set_index("datetime")
df.tail()
import matplotlib.pyplot as plt
df[["energy_kWh"]].plot(figsize=(15,3), grid="both");
df.shape<jupyter_output><empty_output><jupyter_text>Calculated delta Energy<jupyter_code>df["charged_kWh"] = df["energy_kWh"].diff()
df.head()
df[['charged_kWh']].plot(figsize=(15,3), grid="both");<jupyter_output><empty_output><jupyter_text>Analyze `charged_kWh` spikes<jupyter_code>pd.options.display.min_rows = 100

df[df.index.date == dt.date(2021, 10, 29)]<jupyter_output><empty_output><jupyter_text>--> `charged_kWh` spikes are explainable. The **parking heater** is used on regular workdays (non home office) in the morning between 7:00 and 8:00. Also those heating periods are shorter than one hour, so they don't necessarily show up in the `power_kW` signal.   Create a Github-like maps<jupyter_code>df["date"] = [idx.date() for idx in df.index]
df["weekday"] = [idx.day_of_week for idx in df.index]
df["week"] = [idx.weekofyear for idx in df.index]
df["month"] = [idx.month for idx in df.index]
df.sample(6)
zeros = pd.DataFrame(columns=np.arange(1, 53, dtype=int), data=np.zeros(shape=(7, 52)))
wks = df.pivot_table(index="weekday", columns="week", values="charged_kWh", aggfunc="sum") + zeros
wks.iloc[:,35:45]
fig = px.imshow(wks, labels={"x": "calender week", "y": "", "color": "charged_kWh"},
                color_continuous_scale='Greens', width=1000, height=300)
fig.update_layout(yaxis = {"tickmode": 'array',
                           "tickvals": [ 0,    1,    2,    3,    4,    5,    6  ],
                           "ticktext": ['Mo', 'Tu', 'We', 'Th', 'Fr', 'Sa', 'Su']})
fig.show()<jupyter_output><empty_output><jupyter_text>Create temperature chart<jupyter_code>temps = df[["temperature", "date"]].groupby(by="date").agg(["max", "mean", "min"])
temps.columns = [f"temperature {c}" for c in temps.columns.droplevel()]
temps.plot(figsize=(12,3), grid="both");
temps.tail(3)
fig = go.Figure()
for col in temps.columns:
    fig.add_trace(go.Scatter(x=temps.index, y=temps[col], name=col))
fig.update_layout(width=1000, height=300, margin=dict(l=10, r=10, b=10, t=10))
fig.show()<jupyter_output><empty_output><jupyter_text>charging trend<jupyter_code>kwh = df[["date", "charged_kWh"]].groupby("date").agg("sum")
kwh["rolling_mean"] = kwh["charged_kWh"].rolling(10, win_type="triang", center=True).mean()
kwh["mean"] = [kwh.charged_kWh.mean()] * kwh.shape[0]
kwh.tail()
fig = go.Figure()
fig.add_trace(go.Scatter(x=kwh.index, y=kwh.charged_kWh, mode="markers", name="charged_kWh"))
fig.add_trace(go.Scatter(x=kwh.index, y=kwh.rolling_mean, name="rolling_mean"))
fig.add_trace(go.Scatter(x=kwh.index, y=kwh["mean"], name="mean"))
fig.update_layout(width=1000, height=300, margin=dict(l=10, r=10, b=10, t=10))
fig.show();<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18809926677946984, 'fraction_numerical': 0.02820078962210942, 'mean_word_length': 5.368043087971275, 'pattern_counts': {'":': 6, '<': 30, '<?xml version=': 0, '>': 31, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '467514', 'license': 'MIT', 'n_tokens_mistral': 1435, 'n_tokens_neox': 1361, 'n_words': 232, 'path': 'flask/script_calc_walli_statistics.ipynb', 'repo_name': 'munich-ml/raspi_walli', 'size': '3546'}