"""SOURCE render logic for TI-AGE-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_AGE_01(reg: dict) -> dict: """Audience Age-Skew (M052) - how much a title over-indexes YOUNG households vs the average title, after de-skewing our viewing panel back to the population. Diverging dot plot centered on 1.0x: buzzy new prestige/horror skews young (right), legacy network/long-runners skew old (left). English-language titles only (foreign titles read 'old' as a ZIP-profile artifact); recognizable launches; ranked by the conservative CI bound so the leaderboard is not driven by noise.""" chart_id = "TI-AGE-01" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() # Recognizability floor repointed: the age-skew table's own di column went dark (all-NULL), so the floor # joins the LATEST title_metrics_daily.demand_score (official audience-size basis). 361 English titles # clear demand_score >= 100 today. df = lib.run_query(conn, """ WITH latest AS (SELECT MAX(window_end_date) AS d FROM metrics_persona_age_skew), latest_t AS (SELECT MAX(file_date) AS fd FROM title_metrics_daily WHERE demand_score IS NOT NULL AND geo_code='US') SELECT a.imdb_id, a.age_skew_oi, a.ci_low, a.ci_high, a.dsk_young_pct, t.demand_score::numeric AS demand, tb.primary_title, tb.start_year, a.window_end_date FROM metrics_persona_age_skew a JOIN categorization_imdb_title_basics tb USING (imdb_id) JOIN categorization_title_metadata tm USING (imdb_id) JOIN title_metrics_daily t ON t.imdb_id = a.imdb_id AND t.file_date = (SELECT fd FROM latest_t) AND t.geo_code = 'US' WHERE a.window_end_date = (SELECT d FROM latest) AND a.n_segments >= 8 AND t.demand_score >= 100 AND tm.language_origin = 'English' ORDER BY a.age_skew_oi DESC; """) q_sec = time.time() - t0 if df.empty or len(df) < 12: lib.needs_spec_review(output_dir, chart_id, reg.get("spec_file", "specs/15_audience.md"), "PC-AGE-01", "Too few English age-skew titles at Demand Score >= 100") conn.close(); write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} conn.close() fresh = str(df["window_end_date"].iloc[0]) for c in ["age_skew_oi", "ci_low", "ci_high", "dsk_young_pct", "demand"]: df[c] = pd.to_numeric(df[c], errors="coerce") N = 10 df["start_year"] = pd.to_numeric(df["start_year"], errors="coerce") # Among titles with a clear + 95%-confident skew, show the most RECOGNIZABLE (highest demand) - so the # examples land with names people know. SYMMETRIC selection on both sides (an old-side-only release-year # filter curated the picture toward the new-young/old-old story; the headline now claims only the split, # so both pools take the same rule). Falls back to CI-ranking if a pool is thin. # display-stability gate: a row whose 95% interval spans more than 50 points is direction- # confident but magnitude-unstable - it stretches the axis across the canvas and its point # estimate reads as noise (verified: one 132-point-wide interval compressed every other row) stable = (df["ci_high"] - df["ci_low"]) <= 0.50 yp = df[(df["ci_low"] >= 1.08) & stable] op = df[(df["ci_high"] <= 0.88) & stable] if len(yp) < 6: yp = df.sort_values("ci_low", ascending=False).head(2 * N) if len(op) < 6: op = df.sort_values("ci_high", ascending=True).head(2 * N) young = yp.sort_values("demand", ascending=False).head(N).assign(grp="young") old = op.sort_values("demand", ascending=False).head(N).assign(grp="old") plot = (pd.concat([young, old]).drop_duplicates("imdb_id") .sort_values("age_skew_oi").reset_index(drop=True)) plot["label"] = plot["primary_title"].astype(str).str.slice(0, 32) # express the over-index as a PERCENT vs a typical title (1.51x -> +51%, 0.72x -> -28%): clearer than x plot["pct"] = (plot["age_skew_oi"] - 1) * 100 plot["lo"] = (plot["ci_low"] - 1) * 100 plot["hi"] = (plot["ci_high"] - 1) * 100 # the two age classes take NEUTRAL hues (purple young / slate old): amber stays reserved for the # NEW chip, navy for a leader, and nothing green-ADJACENT for a class - the old teal #3E8A77 # read as green, which the contract reserves for rising-only AGE_YOUNG, AGE_OLD = lib.PURPLE, "#46606E" colors = [AGE_YOUNG if g == "young" else AGE_OLD for g in plot["grp"]] tpos = ["middle right" if g == "young" else "middle left" for g in plot["grp"]] fig = go.Figure() cx, cy = [], [] for lbl, p in zip(plot["label"], plot["pct"]): cx += [0, p, None]; cy += [lbl, lbl, None] fig.add_trace(go.Scatter(x=cx, y=cy, mode="lines", line=dict(color="rgba(104,112,125,0.30)", width=2.0), showlegend=False, hoverinfo="skip")) wx, wy = [], [] for lbl, lo, hi in zip(plot["label"], plot["lo"], plot["hi"]): wx += [lo, hi, None]; wy += [lbl, lbl, None] fig.add_trace(go.Scatter(x=wx, y=wy, mode="lines", line=dict(color="rgba(32,36,42,0.22)", width=1.0), showlegend=False, hoverinfo="skip")) fig.add_trace(go.Scatter( x=plot["pct"], y=plot["label"], mode="markers+text", marker=dict(size=12, color=colors, line=dict(width=1, color="white")), text=[f"{v:+.0f}%" for v in plot["pct"]], textposition=tpos, textfont=dict(size=10, color=lib.INK), cliponaxis=False, showlegend=False, customdata=plot[["demand"]].values, hovertemplate="%{y}
%{x:+.0f}% young viewers vs a typical title · Demand Score %{customdata[0]:,.0f}")) fig.add_vline(x=0, line=dict(color=lib.INK, width=1.1)) fig.add_annotation(x=0, y=len(plot) - 0.2, text="typical title (0%)", showarrow=False, yshift=8, xanchor="center", yanchor="bottom", font=dict(size=10, color=lib.MUTED), bgcolor="rgba(255,255,255,0.85)") # direction hints live in the AXIS TITLE, not floating in the data field (the in-plot # '← older' hint sat on the bottom rows' whiskers in dense renders) # headline exemplars chosen by the CONSERVATIVE confidence bound and phrased as 'at least', # so the headline can never ride the noisiest point estimate on the chart yng_pool = plot[plot.grp == "young"]; od_pool = plot[plot.grp == "old"] yng = yng_pool.loc[yng_pool["lo"].idxmax()] od = od_pool.loc[od_pool["hi"].idxmin()] if float(yng["lo"]) > 0 and float(od["hi"]) < 0: # 'at least' claims FLOOR the bound (24.6% -> 'at least 24%'): rounding up would overstate # exactly the quantity the conservative phrasing promises not to yng_floor = int(np.floor(float(yng["lo"]))) od_floor = int(np.floor(abs(float(od["hi"])))) # the headline claims only the SPLIT the dots draw - no new-young/old-old pattern claim, # which the chart's own picks can contradict (a rediscovered classic can skew young) takeaway = (f"Who a title is for splits hard by age: {str(yng['primary_title'])[:28]} draws at least " f"{yng_floor}% more young viewers than a typical title, while {str(od['primary_title'])[:28]} draws at least {od_floor}% fewer") else: # fallback pools can include sub-confident rows: phrase from point estimates, hedged yng = yng_pool.sort_values("pct", ascending=False).iloc[0] od = od_pool.sort_values("pct").iloc[0] takeaway = (f"Who a title is for splits hard by age: {str(yng['primary_title'])[:28]} draws about " f"{yng['pct']:.0f}% more young viewers than a typical title, while {str(od['primary_title'])[:28]} draws about {abs(od['pct']):.0f}% fewer") # the headline's selection rule is stated here: it names the strongest CAUTIOUS (95%) bound, # which need not be the farthest dot (a wide-interval outlier can out-point it) what = ("This shows whether a title's audience skews younger or older than a typical streaming title, after " "adjusting our US viewing panel to match the population. 0% = typical; to the right is younger, to the left " "older (recognizable US titles; thin bars show the 95% range - the headline names the strongest cautious bound, not the farthest dot).") # WHY phrased in VIEWING terms only (demand-not-discourse discipline): no 'conversation', and no # 'under-35' age boundary the chart and data.csv never define - young/old here is always relative # to a typical title, the same basis the dots draw why = ("Audience demand says how many pressed play; age-skew says who. And the split is behavioral, not a guess - nor does it " "simply track release year: buzzy new prestige often pulls a young crowd, but so can a rediscovered classic. That tells you " "who a title is really for - which titles actually pull the younger end of the viewing audience, and " "which quietly anchor an older audience worth programming and selling against.") footer = lib.assemble_footer(pc_id="PC-AGE-01", caution_labels=["CL-006 viewer panel (distinct viewers), not census", "M052 audience age-skew (methodology v1)"], freshness_date=fresh, sources=["metrics_persona_age_skew", "title_metrics_daily"], extra="panel adjusted to match the US population · shown: most-watched English titles with a clear, 95%-confident, stably-estimated skew · Demand Score 100+") fig.update_layout(**lib.layout_v2(takeaway, what, width=1200, height=860), xaxis=lib.standard_xaxis("Audience age vs a typical title (← skews older · 0% = typical · skews younger →)"), yaxis=lib.standard_yaxis(showgrid=False)) fig.update_xaxes(range=[plot["lo"].min() - 6, plot["hi"].max() + 8], ticksuffix="%") fig.update_yaxes(automargin=True) lib.add_why_and_footer(fig, why, footer) lesson = {"reader_title": takeaway, "reader_subtitle": what, "claim_strength": "moderate", "rules_applied": "M052 de-skewed age over-index; English only; Demand Score >= 100 & n_seg >= 8; top10 young by ci_low + top10 old by ci_high; diverging centered 1.0x"} lib.save_csv(plot, output_dir); lib.export_chart(fig, output_dir, width=1200, height=860) 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.get("spec_file", "specs/15_audience.md"), pc_id="PC-AGE-01", intended_lesson_from_spec="Audience age-skew (M052): title over-index of young vs the average title", rendered_lesson=lesson, row_count=len(plot), freshness_date=fresh, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(plot)}