"""SOURCE render logic for TI-Cal-04 (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_04(reg: dict) -> dict: """Genre Seasonality Matrix - genre x month heatmap.""" chart_id = "TI-Cal-04" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() with conn.cursor() as cur: cur.execute("SET statement_timeout = '240s'") # multi-label genre join over 3 years is a heavy aggregate t0 = time.time() # Multi-label genres (categorization_imdb_genres): each title's viewing counts toward every genre it carries; # rows are normalized to each genre's own yearly mean, so the per-genre seasonal SHAPE is unaffected by the # cross-genre overlap (the primary_genre table collapsed multi-genre titles - the GENRE-TABLE trap). # Basis = raw reach (distinct panel viewers), matching Cal-01: the momentum-weighted di series inflates # exactly the high-release months and would partly manufacture the seasonality being shown. # PERF: the day-level scan is aggregated per (title, month) FIRST, then joined to genres. df = lib.run_query(conn, """ WITH tm AS ( SELECT imdb_id, EXTRACT(MONTH FROM file_date)::integer AS month, AVG(reach::numeric) AS mean_reach_title, COUNT(*) AS n_days 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 GROUP BY imdb_id, EXTRACT(MONTH FROM file_date) ), gen AS (SELECT DISTINCT g.imdb_id, g.genre AS primary_genre FROM categorization_imdb_genres g JOIN (SELECT DISTINCT imdb_id FROM tm) u USING (imdb_id) WHERE g.genre IS NOT NULL AND g.genre NOT IN ('NA','N/A','Unknown','')) SELECT g.primary_genre AS genre, tm.month, SUM(tm.mean_reach_title * tm.n_days) / NULLIF(SUM(tm.n_days), 0) AS mean_reach FROM tm JOIN gen g USING (imdb_id) GROUP BY g.primary_genre, tm.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 genre-season data") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} df["mean_reach"] = df["mean_reach"].astype(float) # curated, layman-recognizable genres (replaces top-12-by-volume, which surfaced News/Talk-Show formats) CURATED = ["Drama", "Comedy", "Action", "Horror", "Family", "Romance", "Thriller", "Sci-Fi", "Animation", "Crime", "Fantasy", "Documentary"] top_g = [g for g in CURATED if g in set(df["genre"])] pivot = df[df["genre"].isin(top_g)].pivot_table(index="genre", columns="month", values="mean_reach", fill_value=0).reindex(top_g) pivot_z = pivot.div(pivot.mean(axis=1), axis=0) # order rows by how seasonal each genre is (peak deviation) so the strongest patterns sit together pivot_z = pivot_z.reindex((pivot_z.max(axis=1) - pivot_z.min(axis=1)).sort_values(ascending=False).index) months = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"] months_full = ["January","February","March","April","May","June","July","August","September","October","November","December"] # annotate every row's single busiest and quietest month - PLUS any cell 18%+ off its norm, so # the eye never lands on a near-peak dark cell (Family-January, Sci-Fi-July) with no number ann = [] labeled = set() for gname in pivot_z.index: row = pivot_z.loc[gname] xi_max = int(row.values.argmax()); xi_min = int(row.values.argmin()) for xi in (xi_max, xi_min): v = float(row.values[xi]) ann.append(dict(x=months[xi], y=gname, text=lib.fmt_signed((v - 1) * 100, nd=0, unit="%"), showarrow=False, font=dict(size=8.5, color="white"))) labeled.add((months[xi], gname)) for gname in pivot_z.index: row = pivot_z.loc[gname] for xi, v in enumerate(row.values): v = float(v) if abs(v - 1) >= 0.18 and (months[xi], gname) not in labeled: ann.append(dict(x=months[xi], y=gname, text=lib.fmt_signed((v - 1) * 100, nd=0, unit="%"), showarrow=False, font=dict(size=8.5, color="white"))) labeled.add((months[xi], gname)) # honest legend: the color range spans the actual data extremes, so no cell saturates beyond # the top label (data peaked at +31% while the old anchors stopped at +20%) dev = max(abs(float(pivot_z.values.max()) - 1), abs(1 - float(pivot_z.values.min())), 0.20) fig = go.Figure(data=go.Heatmap(z=pivot_z.values, x=months[:pivot_z.shape[1]], y=pivot_z.index.tolist(), zmid=1, zmin=1 - dev, zmax=1 + dev, colorscale=[[0,"#B0464E"],[0.5,"#F4F1EA"],[1,lib.NAVY]], colorbar=dict(title="vs genre's
year avg", tickvals=[1 - dev, 1.0, 1 + dev], ticktext=[f"−{dev:.0%}", "avg", f"+{dev:.0%}"]), hovertemplate="%{y} in %{x}
%{z:.0%} of its yearly average")) rng_rank = (pivot_z.max(axis=1) - pivot_z.min(axis=1)).sort_values(ascending=False) g_seas = rng_rank.index[0] peak_xi = int(pivot_z.loc[g_seas].values.argmax()) peak_mo = months_full[peak_xi] peak_pct = (float(pivot_z.loc[g_seas].values[peak_xi]) - 1) * 100 g2 = rng_rank.index[1] if len(rng_rank) > 1 else g_seas g2_mo = months_full[int(pivot_z.loc[g2].values.argmax())] title_lesson = f"Here's where the real seasonality lives: {g_seas} viewing runs {peak_pct:+.0f}% above its norm in {peak_mo}" what = ("Each row = a genre; each cell = that month's viewing vs that genre's own yearly average (normalized per row, so big " "genres don't drown small ones). Blue = its busiest months, red = its quietest; every strong cell plus each genre's busiest " "and quietest months are labelled, most-seasonal rows at top.") why = (f"This is the actual timing playbook. Aggregate demand barely moves by season, but individual genres swing hard - " f"{g_seas} into {peak_mo}, {g2} into {g2_mo}. It's when to release and market each kind of title.") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1240, height=620, annotations=ann), xaxis=dict(side="top", tickfont=dict(size=11)), yaxis=dict(tickfont=dict(size=11), autorange="reversed")) lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-Cal-04", ["CL-006 viewer panel (distinct viewers), not census", "CL-010 genre taxonomy v2"], "2025-12-31", ["title_metrics_daily", "categorization_imdb_genres"], "raw panel reach, 2022-2025; multi-label genres; 12 curated major genres; each row normalized to that genre's yearly mean")) lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1240, 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-04", intended_lesson_from_spec="Genre seasonality matrix (row-normalized, multi-year)", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": "raw reach; 2022-2025; curated 12 genres; row-normalized; per-row peak/trough labels"}, 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)}