"""SOURCE render logic for TI-Cal-01 (REFERENCE ONLY).
This is the exact function from our pipeline. It is DB-coupled — you cannot run it without our database.
The query result it produces is materialized for you in data.csv, and the rendered output is figure.json
(load that to display the chart). This file is provided so you can understand / re-theme the logic.
"""
def render_TI_Cal_01(reg: dict) -> dict:
"""Seasonal Demand Heatmap - month-of-year x year heatmap of total DI."""
chart_id = "TI-Cal-01"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
with conn.cursor() as cur:
cur.execute("SET statement_timeout = '360s'")
t0 = time.time()
# Basis = raw reach (distinct panel viewers): demand_score exists only for the latest snapshot (no history)
# and di is momentum-weighted, which would distort the seasonal shape. Window extended to the 4 complete
# years 2022-2025 (daily data IS populated for 2022). Each year is indexed to its own mean below - that is
# share-of-year normalization, which removes cross-year panel drift; pooling 4 years damps the residual
# week-level panel swings (L-046).
df = lib.run_query(conn, """
SELECT EXTRACT(YEAR FROM file_date)::integer AS year,
EXTRACT(MONTH FROM file_date)::integer AS month,
AVG(reach::numeric) AS mean_reach
FROM title_metrics_daily
WHERE reach IS NOT NULL AND geo_code='US'
AND file_date >= DATE '2022-01-01' AND file_date < DATE '2026-01-01' -- complete years 2022-2025; partial 2026 excluded
GROUP BY EXTRACT(YEAR FROM file_date), EXTRACT(MONTH FROM file_date)
ORDER BY year, month;
""")
q_sec = time.time() - t0
conn.close()
if df.empty:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No seasonality data")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
df["mean_reach"] = df["mean_reach"].astype(float)
pivot = df.pivot(index="year", columns="month", values="mean_reach")
norm = pivot.div(pivot.mean(axis=1), axis=0) # each year indexed to its own mean → seasonal shape, drift removed
months = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]
xmn = list(range(1, 13))
fig = go.Figure()
# one faint line per year (shows cross-year consistency / spread)
for yr in norm.index:
row = norm.loc[yr]
fig.add_trace(go.Scatter(x=[m for m in xmn if m in row.index], y=[row[m] for m in xmn if m in row.index],
mode="lines", line=dict(color=lib.PALE_BLUE, width=1.4), opacity=0.7,
name=str(int(yr)), showlegend=False, hovertemplate=f"{int(yr)} %{{x}}
%{{y:.0%}} of year avg"))
pooled = norm.mean(axis=0)
fig.add_trace(go.Scatter(x=pooled.index.tolist(), y=pooled.values, mode="lines+markers",
line=dict(color=lib.NAVY, width=3.5), marker=dict(size=7, color=lib.NAVY),
name="average", showlegend=False,
hovertemplate="%{x}: %{y:.0%} of a typical month (avg of all years)"))
fig.add_hline(y=1.0, line=dict(color=lib.MUTED, width=1, dash="dash"))
swing = (pooled.max() - pooled.min())
mn_names = ["","January","February","March","April","May","June","July","August","September","October","November","December"]
pk = int(pooled.idxmax()); tr = int(pooled.idxmin())
# name the eye-catching outlier year instead of leaving it as an open question
yr_out = norm.max(axis=1).idxmax()
mo_out = int(norm.loc[yr_out].idxmax())
fig.add_annotation(x=mo_out, y=float(norm.loc[yr_out, mo_out]),
text=f"{int(yr_out)}: an unusually hot {mn_names[mo_out]}", showarrow=True,
arrowhead=0, arrowwidth=1, arrowcolor=lib.MUTED, ax=-40, ay=-18,
font=dict(size=10, color=lib.MUTED), bgcolor="rgba(255,255,255,0.8)")
title_lesson = (f"Overall demand is remarkably steady year-round - it swings only ~{swing*100:.0f}% from peak ({mn_names[pk]}) "
f"to trough ({mn_names[tr]}). The big seasonality is genre-specific, not catalog-wide")
what = ("Bold line = the average month-to-month shape of raw panel viewing (across 2022-2025), indexed so 100% = a typical month "
"of its year. Faint lines = each individual year; most years stay within a few points of the average - December is the loosest month.")
why = ("It corrects a common assumption: total streaming demand does not swing wildly by season. Real timing leverage is at the "
"genre level (horror in October, family at the holidays - see the genre seasonality chart), not the calendar as a whole. "
"Month-level readings carry some panel-coverage noise; averaging four years damps it, so read the bold line, not any single year.")
fig.update_layout(**lib.layout_v2(title_lesson, what, width=1200, height=620),
xaxis=lib.standard_xaxis(""), yaxis=lib.standard_yaxis("Viewing vs a typical month", tickformat=".0%"))
fig.update_xaxes(tickmode="array", tickvals=xmn, ticktext=months)
lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-Cal-01",
["CL-006 viewer panel (distinct viewers), not census",
"within-year indexing (share-of-year); single-month readings carry panel noise"], "2025-12-31", ["title_metrics_daily"],
"2022-2025 (4 complete years); raw panel reach, each year indexed to its own mean"))
lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1200, height=620)
lib.write_lesson_design(output_dir, chart_id, f"# {chart_id}\n\n{title_lesson}\n\n{what}\n")
write_shim(output_dir, chart_id)
lib.write_manifest(output_dir, chart_id, "ok", reg["spec_file"], pc_id="PC-Cal-01",
intended_lesson_from_spec="Seasonal demand rhythm (multi-year, within-year normalized)",
rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what,
"claim_strength": "moderate",
"rules_applied": "2022-2025; raw reach; within-year (share-of-year) indexing, pooled across years"},
row_count=len(df), freshness_date="2025-12-31",
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}