"""SOURCE render logic for TI-RC-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_RC_04(reg: dict) -> dict: """One Show Away (monthly) - what share of each service's viewing is just its top 10 titles (M055).""" chart_id = "TI-RC-04" output_dir = THIS_DIR / chart_id / ROLE # purge stale companion crops/previews from earlier rounds (they showed an older render and # could mislead downstream consumers of the folder) if output_dir.exists(): for f in output_dir.glob("_*.png"): f.unlink() conn = lib.connect_readonly() t0 = time.time() df = lib.run_query(conn, """ WITH l AS (SELECT MAX(file_date) AS d FROM metrics_platform_concentration_daily WHERE country_code='US'), p AS (SELECT COALESCE( (SELECT MAX(file_date) FROM metrics_platform_concentration_daily, l WHERE country_code='US' AND file_date <= l.d - 90), (SELECT MIN(file_date) FROM metrics_platform_concentration_daily WHERE country_code='US')) AS d) SELECT c.platform, c.top10_share::numeric AS top10, c.hhi::numeric AS hhi, c.n_titles_with_demand AS n_titles, c.file_date AS freshness_date, pr.top10_share::numeric AS top10_prior, (SELECT d FROM p) AS prior_date FROM metrics_platform_concentration_daily c LEFT JOIN metrics_platform_concentration_daily pr ON pr.platform = c.platform AND pr.country_code = 'US' AND pr.file_date = (SELECT d FROM p) WHERE c.file_date = (SELECT d FROM l) AND c.country_code = 'US' ORDER BY c.top10_share DESC; """) q_sec = time.time() - t0 conn.close() if df.empty: lib.needs_spec_review(output_dir, chart_id, reg.get("spec_file", "specs/16_recurring.md"), reg.get("pc_id", "PC-RC-04"), "No platform concentration") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} df["top10"] = df["top10"].astype(float).round(1) df["top10_prior"] = df["top10_prior"].astype(float).round(1) freshness = str(df["freshness_date"].iloc[0]) prior_date = pd.to_datetime(df["prior_date"].iloc[0]) prior_mon = prior_date.strftime("%B") # display copy spells months out has_trend = df["top10_prior"].notna().any() and str(prior_date.date()) != freshness df["delta"] = (df["top10"] - df["top10_prior"]).round(1) plot = df.sort_values("top10", ascending=True).reset_index(drop=True) # most-concentrated on top n = len(plot) cats = list(plot["platform"]) # color contract: NAVY = the leader (the most-concentrated service, the headline subject) ONLY; # the field gets the muted steel-blue (amber stays reserved for the NEW chip; judgment stays # neutral - the footer itself says hit-dependence is a strategy, not a weakness) colors = [lib.NAVY if i == n - 1 else lib.PALE_BLUE for i in range(n)] fig = go.Figure() # Ghost dots/connectors only where the move is big enough to SEE (a -0.5 pt hollow dot hides # inside the filled dot and reads as a stray ring). SHOW_GHOST = 1.0 most = plot.iloc[-1]; least = plot.iloc[0] mv = plot.loc[plot["delta"].abs().idxmax()] if (has_trend and plot["delta"].notna().any()) else None mv_flagged = mv is not None and abs(float(mv["delta"])) >= 1 ghosted = plot["top10_prior"].notna() & (plot["delta"].abs() >= SHOW_GHOST) if has_trend else plot["top10_prior"] != plot["top10_prior"] for (_, r), g in zip(plot.iterrows(), ghosted): if g: # drift connector: where it was (ghost) -> where it is now fig.add_trace(go.Scatter(x=[float(r["top10_prior"]), float(r["top10"])], y=[r["platform"], r["platform"]], mode="lines", line=dict(color="#D7DBE4", width=2), showlegend=False, hoverinfo="skip")) if has_trend and ghosted.any(): gp = plot[ghosted] fig.add_trace(go.Scatter( # ghost dots = three months ago x=gp["top10_prior"], y=gp["platform"], mode="markers", marker=dict(size=11, color="white", line=dict(width=1.5, color="#9AA3B2")), showlegend=False, hoverinfo="skip")) # Labels: the VALUE always sits beside the CURRENT (filled) dot - on declining rows it goes to # the LEFT so it can never read as the hollow dot's number; the delta commentary sits to the # right of the rightmost marker in muted grey. vx, vy, vt, vpos = [], [], [], [] dx, dy, dt = [], [], [] for (_, r), g in zip(plot.iterrows(), ghosted): cur = float(r["top10"]); pri = r["top10_prior"]; d = r["delta"] declining = bool(g) and pd.notna(pri) and float(pri) > cur vtxt = f"{r['top10']:.0f}%" # ONE unit word on the surface (%): the change is stated as the PRIOR level ('was 31% in # March'), never as a second 'pts' unit if not has_trend or pd.isna(d): dtxt = "" elif abs(d) < 0.05: # 'unchanged' spelled out so a zero-shift can't read as missing data dtxt = f"(unchanged since {prior_mon})" elif f"{float(pri):.0f}" == f"{cur:.0f}": dtxt = f"(little changed since {prior_mon})" else: dtxt = f"(was {float(pri):.0f}% in {prior_mon})" vx.append(cur); vy.append(r["platform"]) if declining: # value LEFT of the filled dot; the delta rides right of the hollow (March) dot vt.append(vtxt + " "); vpos.append("middle left") if dtxt: dx.append(float(pri)); dy.append(r["platform"]); dt.append(" " + dtxt) else: # rising/flat: one combined label right of the rightmost marker (no overprint) joined = f" {vtxt}" + (f" {dtxt}" if dtxt else "") vt.append(joined); vpos.append("middle right") hover_prior = [(f"was {p:.0f}% three months ago" if pd.notna(p) else "no three-month-ago reading") for p in plot["top10_prior"]] fig.add_trace(go.Scatter( # current dots x=plot["top10"], y=plot["platform"], mode="markers", marker=dict(size=17, color=colors, line=dict(width=1.5, color="white")), showlegend=False, customdata=np.array(hover_prior, dtype=object).reshape(-1, 1), hovertemplate="%{y}
top 10 titles = %{x:.0f}% of its total Demand Score · %{customdata[0]}")) fig.add_trace(go.Scatter(x=vx, y=vy, mode="text", text=vt, textposition=vpos, cliponaxis=False, textfont=dict(size=11, color=lib.INK), showlegend=False, hoverinfo="skip")) if dt: fig.add_trace(go.Scatter(x=dx, y=dy, mode="text", text=dt, textposition="middle right", cliponaxis=False, textfont=dict(size=10, color=lib.MUTED), showlegend=False, hoverinfo="skip")) fig.update_xaxes(range=[0, float(plot[["top10", "top10_prior"]].max().max()) * 1.16], dtick=10) least2 = plot.iloc[1] if len(plot) > 1 else None if least2 is not None and lib.near_tie([least["top10"], least2["top10"]], fmt=lambda v: f"{v:.0f}"): least_clause = (f"on the most balanced, {least['platform']} and {least2['platform']}, " f"the top 10 carry only ~{least['top10']:.0f}%") else: least_clause = f"on {least['platform']} the top 10 carry only {least['top10']:.0f}%" takeaway = f"Hit-dependence by service: just 10 titles drive {most['top10']:.0f}% of {most['platform']}'s total Demand Score; {least_clause}" if mv_flagged: direction = "leaning harder on its hits" if mv["delta"] > 0 else "spreading its bets" mv_x = float(max(mv["top10"], mv["top10_prior"]) if pd.notna(mv["top10_prior"]) else mv["top10"]) fig.add_annotation(x=mv_x, y=mv["platform"], xref="x", yref="y", text=f"biggest shift since {prior_mon}: {direction}", showarrow=False, xanchor="left", xshift=10, yshift=-15, font=dict(size=9.5, color=lib.MUTED)) what = ("Hit-dependence is how much of a service's total viewing rides on its few biggest titles - here, the share of " "each service's total US Demand Score that comes from just its top 10 titles. Where a service has shifted at least a point, " "a hollow dot marks where it stood three months ago; steadier rows note their change in text instead.") why = ("It separates the catalogs you'd miss if a few titles left from the ones with real depth. A prestige service " "leaning on a handful of hits is a strategy by design, not a weakness; a deep-library service is far harder " "to cancel.") footer = lib.assemble_footer(pc_id=reg.get("pc_id", "PC-RC-04"), caution_labels=["CL-006 US viewer panel (distinct viewers), not total plays"], freshness_date=freshness, sources=["metrics_platform_concentration_daily"], extra="8 curated SVOD · United States · refreshed monthly") fig.update_layout(**lib.layout_v2(takeaway, what, width=1180, height=680), xaxis=lib.standard_xaxis("Top-10 titles' share of the service's total Demand Score", showgrid=True, ticksuffix="%"), yaxis=lib.standard_yaxis(showgrid=False, categoryorder="array", categoryarray=cats)) lib.add_why_and_footer(fig, why, footer) lib.save_csv(df, output_dir) lib.export_chart(fig, output_dir, width=1180, height=680) what_plain = re.sub(r"", "", what) lesson = {"reader_title": takeaway, "reader_subtitle": what_plain, "claim_strength": "strong", "rules_applied": "M055 latest US top10_share; dot plot; navy = most concentrated (leader), pale-steel field"} lib.write_lesson_design(output_dir, chart_id, f"# {chart_id}\n\n{takeaway}\n\n{what_plain}\n") write_shim(output_dir, chart_id) lib.write_manifest(output_dir, chart_id, "ok", reg.get("spec_file", "specs/16_recurring.md"), pc_id=reg.get("pc_id", "PC-RC-04"), intended_lesson_from_spec="Monthly platform tentpole-dependency (top-10 share, M055)", rendered_lesson=lesson, row_count=len(df), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)}