"""SOURCE render logic for TI-A-06 (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_A_06(reg: dict) -> dict: """Breakout Temperature - weekly count of meaningful breakouts over recent weeks (is the market heating up or cooling?).""" chart_id = "TI-A-06" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() df = lib.run_query(conn, """ SELECT date_trunc('week', spike_peak_date)::date AS wk, COUNT(*) FILTER (WHERE ratio >= 3.0) AS big_spikes, COUNT(*) AS all_spikes, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ratio::numeric) FILTER (WHERE ratio >= 3.0) AS median_big FROM spike_pulse_events WHERE spike_peak_date >= CURRENT_DATE - INTERVAL '20 weeks' AND spike_peak_date < date_trunc('week', CURRENT_DATE) AND ratio IS NOT NULL AND baseline_value >= 20 -- count real breakouts only (exclude divide-by-near-zero ratio noise) AND COALESCE((SELECT r.num_votes FROM categorization_imdb_ratings r WHERE r.imdb_id = spike_pulse_events.imdb_id), 0) >= CASE WHEN (SELECT bt.title_type FROM categorization_imdb_title_basics bt WHERE bt.imdb_id = spike_pulse_events.imdb_id) = 'movie' THEN 3000 ELSE 20000 END -- type-aware corroboration gate (cred>=0.5: films K=3K, shows K=20K) GROUP BY 1 ORDER BY 1; """) # L-046: raw weekly breakout COUNTS track panel coverage (a small-panel week detects fewer spikes), so # normalize each week's count by that week's total panel viewing (metrics_panel_health.total_reach). pw = lib.run_query(conn, """ SELECT date_trunc('week', file_date)::date AS wk, SUM(total_reach)::numeric AS wk_reach, COUNT(*) FILTER (WHERE degraded) AS degraded_days FROM metrics_panel_health WHERE file_date >= CURRENT_DATE - INTERVAL '20 weeks' AND file_date < date_trunc('week', CURRENT_DATE) GROUP BY 1 ORDER BY 1; """) q_sec = time.time() - t0 conn.close() if df.empty or len(df) < 4: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "Too few weekly spike rows") write_shim(output_dir, chart_id) return {"status": "needs_spec_review"} df["wk"] = pd.to_datetime(df["wk"]) df["big_spikes"] = df["big_spikes"].astype(int) df["median_big"] = pd.to_numeric(df["median_big"], errors="coerce") pw["wk"] = pd.to_datetime(pw["wk"]); pw["wk_reach"] = pw["wk_reach"].astype(float) df = df.merge(pw, on="wk", how="left") df = df[df["wk_reach"].notna() & (df["wk_reach"] > 0)].reset_index(drop=True) if len(df) < 4: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "Too few weeks with panel-reach coverage") write_shim(output_dir, chart_id) return {"status": "needs_spec_review"} # breakout RATE per 10M panel viewer-days that week (panel-size-proof) df["rate"] = df["big_spikes"] / (df["wk_reach"] / 1e7) # Trust flag applied to EVERY week consistently (audit: flagging only the latest week left a # worse-coverage mid-window week rendered as trustworthy): a week with 2+ degraded panel days is # 'panel atypical' - grey + hatch - and is excluded from the average AND the verdict. flagged = (pd.to_numeric(df.get("degraded_days"), errors="coerce").fillna(0).astype(int) >= 2 if "degraded_days" in df.columns else pd.Series(False, index=df.index)) trust = ~flagged if int(trust.sum()) < 3: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "Too few trustworthy (non-degraded) weeks for a breakout-pace read") write_shim(output_dir, chart_id) return {"status": "needs_spec_review"} last_trust_i = int(np.where(trust.values)[0][-1]) recent = float(df["rate"].iloc[last_trust_i]) recent_n = int(df["big_spikes"].iloc[last_trust_i]) prior_mask = trust.copy(); prior_mask.iloc[last_trust_i:] = False prior_avg = float(df.loc[prior_mask, "rate"].mean()) n_prior = int(prior_mask.sum()) n_flagged = int(flagged.sum()) latest_flagged = bool(flagged.iloc[-1]) # above/below the average is a CLASS, so both sides use neutral blues (navy stays leader-only) colors = [lib.BLUE if v >= prior_avg else lib.PALE_BLUE for v in df["rate"]] pattern_shapes = [""] * len(df) for i in range(len(df)): if bool(flagged.iloc[i]): colors[i] = "#DCE1E9" # neutral grey: flagged weeks sit OUTSIDE the above/below split pattern_shapes[i] = "/" # hatch = 'panel atypical' fig = go.Figure() fig.add_trace(go.Bar( x=df["wk"], y=df["rate"], marker=dict(color=colors, pattern=dict(shape=pattern_shapes, fgcolor=lib.MUTED, size=7, solidity=0.35)), showlegend=False, cliponaxis=False, customdata=df[["big_spikes"]].values, hovertemplate="Week of %{x|%b %d}
%{y:.0f} breakouts per 10M panel viewer-days (%{customdata[0]} raw)", )) # integer labels on every bar (the average chip below carries a decimal, so a bar can never # print equal to the line while sitting off it), drawn as WHITE-BACKED annotations: bars whose # tops sit near the dashed average were getting their value labels struck through by the dashes for wk_x, r in zip(df["wk"], df["rate"].astype(float)): fig.add_annotation(x=wk_x, y=r, text=f"{r:.0f}", showarrow=False, xanchor="center", yanchor="bottom", yshift=2, font=dict(size=10, color=lib.INK), bgcolor="rgba(255,255,255,0.88)") lib.add_ref_line(fig, y=prior_avg) # average label placed over the 3-week window whose bars sit FURTHEST below the line (least ink # underneath), so the box stops landing on bar fills rates = df["rate"].astype(float).values win_max = [max(rates[i:i + 3]) for i in range(0, max(1, len(rates) - 2))] best_i = int(np.argmin(win_max)) lab_x = df["wk"].iloc[min(best_i + 1, len(df) - 1)] fig.add_annotation(x=lab_x, y=prior_avg, xanchor="center", yanchor="bottom", text=f"trustworthy prior-weeks average: {prior_avg:.1f}", showarrow=False, font=dict(size=10, color=lib.MUTED), bgcolor="rgba(255,255,255,0.9)") if latest_flagged: fig.add_annotation(x=df["wk"].iloc[-1], y=float(df["rate"].iloc[-1]), text="panel atypical
read with caution", showarrow=False, yshift=36, font=dict(size=9.5, color=lib.MUTED)) # verdict computed from the most recent TRUSTWORTHY week vs the trustworthy prior average if recent >= prior_avg * 1.15: temp = "heating up" elif recent <= prior_avg * 0.85: temp = "cooling off" else: temp = "steady" # HONEST RATE-VS-COUNT HEADLINE, ONE BREATH: lead with the raw count of breakout titles (the # number a layman can quote safely), then the panel-normalized pace with its implied noun # SPELLED OUT ('breakouts per 10M viewer-days') and the benchmark at the SAME 1dp precision the # on-chart dashed-line chip prints (a .0f headline beside a .1f chip showed two different # numbers for one quantity). The week-of date and the set-aside-weeks aside move off the H1: # the date reads off the axis/hover, the aside lives in the subtitle's hatched-bars clause. n_trailing = 0 for i in range(len(df) - 1, -1, -1): if bool(flagged.iloc[i]): n_trailing += 1 else: break takeaway = (f"Breakout activity is {temp}: {recent_n} titles broke out in the latest reliable week, " f"a pace of {recent:.0f} breakouts per 10M viewer-days of panel viewing vs the prior-weeks average of {prior_avg:.1f}") pale_clause = ("; grey hatched bars = weeks whose panel coverage was too atypical to trust, whatever their height" if n_flagged else "") if n_flagged and n_trailing: pale_clause += (f" (the newest {n_trailing} weeks fall there)" if n_trailing > 1 else " (the newest week falls there)") what = ("Each bar = how many titles had a meaningful breakout (daily Demand Score jumped to 3x+ the title's own normal level) that week, " "shown per 10M viewer-days of panel viewing so a small-panel week can't read as a quiet one. " f"Darker bars sit above the trustworthy prior-weeks average (the dashed line); pale bars sit below{pale_clause}.") why = ("This is the catalog's temperature gauge. Lots of breakouts = a hot, fast-moving moment where 'what's trending' " "content lands; few breakouts = a quiet stretch where evergreen and catalog titles carry the load. It tells you what kind of week it is.") footer = lib.assemble_footer(pc_id="PC-A-04", caution_labels=["CL-013 last 20 complete weeks (the in-progress week is excluded)", "counts normalized to weekly panel viewing (raw counts would track panel-size swings)"], freshness_date=str((df["wk"].max() + pd.Timedelta(days=6)).date()), sources=["spike_pulse_events", "metrics_panel_health"], extra="breakout: a title jumping to 3x+ its own normal daily Demand Score") fig.update_layout(**lib.layout_v2(takeaway, what, width=1240, height=640), # Ticks pinned to each bar's Monday week-start: Plotly's default date ticks # fall on Sundays (one day off every bar), so the headline's 'week of ...' # could not be read off the axis. One tick per bar, labeled with the same # week-start date the headline and hover use. xaxis=lib.standard_xaxis("", tickmode="array", tickvals=list(df["wk"]), ticktext=[d.strftime("%b %d") for d in df["wk"]], tickangle=-45), yaxis=lib.standard_yaxis("Breakouts per 10M viewer-days of panel viewing, each week")) lib.add_why_and_footer(fig, why, footer) lesson = {"reader_title": takeaway, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": (f"weekly >=3x count per 10M panel viewer-days, 20wk; flag rule: 2+ degraded days -> hatched, " f"excluded from avg+verdict ({n_flagged} flagged); recent trustworthy={recent:.0f} vs norm {prior_avg:.1f}")} lib.save_csv(df, output_dir) lib.export_chart(fig, output_dir, width=1240, height=640) lib.write_lesson_design(output_dir, chart_id, f"# {chart_id}\n\n{takeaway}\n\n{what}\n") write_shim(output_dir, chart_id) lib.write_manifest(output_dir, chart_id, "ok", reg["spec_file"], pc_id="PC-A-04", intended_lesson_from_spec="Breakout temperature over recent weeks (redesigned from window CDFs)", rendered_lesson=lesson, row_count=len(df), freshness_date=str((df["wk"].max() + pd.Timedelta(days=6)).date()), query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)}