"""SOURCE render logic for TI-MG-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_MG_06(reg: dict) -> dict: """Top genres by total weekly Demand Score (founder-commissioned 2026-06-12) - the first question a streaming fan asks that no other chart answered. Genre source: categorization_imdb_genres (multi-genre, one row per title-genre) - NEVER categorization_primary_genre (collapses multi-genre titles, known trap). Credit method: FRACTIONAL (1/n per genre), chosen over full-credit by EDA 2026-06-12: board titles carry a median of 3 genre tags, so full-credit counts the week's total 2.85x over and hands Drama >50% of a week it does not own; fractional conserves the total exactly (bars are honest slices of one whole) and the ranking order is essentially unchanged (top 12 identical but one adjacent swap).""" chart_id = "TI-MG-06" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly(); t0 = time.time() # Demand basis = the RC family's weekly board metric (demand_score_7d), US, shows + movies; # movie rows gated on di_rank (the PVOD side-lane is unranked and must not leak in). titles = lib.run_query(conn, """ SELECT r.imdb_id, r.demand_score_7d::numeric AS ds, r.file_date AS freshness_date FROM metrics_demand_rank_history r WHERE r.file_date = (SELECT MAX(file_date) FROM metrics_demand_rank_history) AND r.geo_code = 'US' AND r.demand_score_7d IS NOT NULL AND (r.title_type <> 'movie' OR r.di_rank IS NOT NULL); """) if titles.empty: conn.close() lib.needs_spec_review(output_dir, chart_id, reg.get("spec_file", "specs/05_genre_subgenre_demand.md"), reg.get("pc_id", "PC-MG-06"), "Empty weekly board universe") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} titles = lib.drop_demand_orphans(titles, conn) # hide in-theaters / not-yet-streaming demand-orphans genres = lib.run_query(conn, """ SELECT DISTINCT imdb_id, genre FROM categorization_imdb_genres WHERE imdb_id = ANY(%s) AND genre IS NOT NULL AND genre NOT IN ('', '\\N'); """, (titles["imdb_id"].astype(str).tolist(),)) ph = lib.run_query(conn, "SELECT degraded FROM metrics_panel_health WHERE file_date = (SELECT MAX(file_date) FROM metrics_demand_rank_history);") panel_degraded = bool(ph["degraded"].iloc[0]) if len(ph) else False conn.close(); q_sec = time.time() - t0 if genres.empty: lib.needs_spec_review(output_dir, chart_id, reg.get("spec_file", "specs/05_genre_subgenre_genre.md"), reg.get("pc_id", "PC-MG-06"), "No genre labels for the weekly board universe") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} titles["ds"] = titles["ds"].astype(float) freshness = str(titles["freshness_date"].iloc[0]) m = genres.merge(titles[["imdb_id", "ds"]], on="imdb_id", how="inner") m["n_genres"] = m.groupby("imdb_id")["genre"].transform("count") m["credit"] = m["ds"] / m["n_genres"] # fractional 1/n split (no double counting) d = (m.groupby("genre").agg(total=("credit", "sum"), titles=("imdb_id", "nunique")) .reset_index().sort_values("total", ascending=False).reset_index(drop=True)) grand = float(d["total"].sum()) d["share"] = d["total"] / grand if grand > 0 else 0.0 hidden = d[d["share"] < 0.005] # de-minimis floor, disclosed in footer n_hidden = int(len(hidden)) hidden_total = float(hidden["total"].sum()) # residual carried into the sidecar so the hidden_share = float(hidden["share"].sum()) # '+~X% in N smaller genres' note reconstructs hidden_titles = int(hidden["titles"].sum()) d = d[d["share"] >= 0.005].reset_index(drop=True) if len(d) < 3: lib.needs_spec_review(output_dir, chart_id, reg.get("spec_file", "specs/05_genre_subgenre_demand.md"), reg.get("pc_id", "PC-MG-06"), f"Only {len(d)} genres above the 0.5% floor") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} n_titles = int(titles["imdb_id"].nunique()) plot = d.sort_values("total", ascending=True).reset_index(drop=True) # largest renders at top # field = BLUE (the contract's default bar hue); PALE_BLUE is reserved for de-emphasis only, # so leader (navy) / field (blue) stay semantically distinct colors = lib.leader_colors(plot["total"], leader=lib.NAVY, field=lib.BLUE) # SHARE-ONLY bar labels: the subtitle frames this purely as a share of the weekly total, and # the Demand Score has no public unit, so printing the raw internal total (e.g. 16,357) is the # no-absolute-score-leak guard's exact target and reads as a meaningless "what is 16,357?" number. # Sub-1% genres get a one-decimal label (0.9%, 0.8%, ...) rather than a shared "<1%": five # different-length bars all reading "<1%" invites a reader to wonder why they differ. One decimal # makes every label reconcile to its own bar length while staying honestly under one percent. pct_str = [f"{s:.1%}" if s < 0.0095 else f"{s:.0%}" for s in plot["share"]] bar_text = [f" {p}" for p in pct_str] ticks = [(f"{g}" if float(v) == float(plot["total"].max()) else str(g)) + "  " for g, v in zip(plot["genre"], plot["total"])] fig = go.Figure() fig.add_trace(go.Bar( x=plot["total"], y=ticks, orientation="h", marker=dict(color=colors, line=dict(width=0)), text=bar_text, textposition="outside", textfont=dict(size=11, color=lib.MUTED), cliponaxis=False, showlegend=False, customdata=plot[["titles", "share"]].values, hovertemplate="%{y}
%{customdata[1]:.1%} of this week's total · %{customdata[0]:,} titles", )) fig.update_xaxes(range=lib.pad_range(plot["total"], labels=bar_text, font_px=11, fig_width=1160)) # Reconcile the visible percents to 100% ON the face: the shown bars sum to a hair under 100% # because the sub-0.5% genres were dropped. A faint end-cap note accounts for that residual so a # reader can add the labels up without hunting the footer for the de-minimis disclosure. The residual # equals the hidden-genres aggregate carried in the sidecar (so the note is reconstructable from data.csv). resid_pct = max(0.0, hidden_share * 100.0) if resid_pct >= 0.5: # Round the residual to the nearest 0.5% rather than the nearest whole percent: a true # hidden mass of 2.54% rounded with :.0f reads "~3%", which a strict reader can call an # over-round (2.54 is closer to 2.5). Half-point rounding makes the tilde honest. resid_half = round(resid_pct * 2) / 2 resid_str = "<1%" if resid_pct < 1.0 else f"~{resid_half:g}%" fig.add_annotation(x=float(plot["total"].max()) * 0.985, y=plot["genre"].iloc[0], xref="x", yref="y", xanchor="right", yanchor="middle", showarrow=False, text=f"+ {resid_str} in {n_hidden} smaller genres (not shown)", font=dict(size=9.5, color=lib.MUTED)) top = d.iloc[0] if lib.near_tie(d["total"].head(2), fmt=lambda v: f"{v:,.0f}"): takeaway = (f"{d['genre'].iloc[0]} and {d['genre'].iloc[1]} are neck and neck as America's " f"most-watched streaming genre this week") else: # metric qualified: 'this week's total Demand Score', never a bare 'demand' noun takeaway = (f"{top['genre']} is America's most-watched streaming genre this week, " f"holding {top['share']:.0%} of this week's total Demand Score") what = (f"Each genre's slice of total weekly Demand Score, our measure of weekly at-home audience, " f"across the {n_titles:,} most-watched US titles this week. A title with several genre tags " f"splits its score equally between them, so no title is counted twice.") why = ("Genre is the first filter every streaming fan applies, and the slices are not close. " "Where the big genres sit, and how steep the falloff is after them, is the context for " "every deeper genre chart here.") cautions = ["Demand Score: panel viewing, weighted to reflect the broad US streaming audience (methodology: METH-03)"] if panel_degraded: cautions.append("panel volume atypical on this date; scores use a 7-day basis") # Disclose BOTH halves of the multi-genre treatment so the model is symmetric on the face: a # title's SCORE is split fractionally across its genres (slices stay honest), but the title-COUNT # in the sidecar counts each title once per genre tag, so per-genre title counts overlap and do # not sum to the pool. Stating both pre-empts the "fractional score vs whole title count" catch. extra = (f"score split equally across each title's genres (no double counting); a multi-genre " f"title is, however, counted once in each of its genres' title tallies, so those tallies " f"overlap · genres under 0.5% of the weekly total not shown ({n_hidden}) · US · weekly") fig.update_layout(**lib.layout_v2(takeaway, what, width=1160, height=740), xaxis=lib.standard_xaxis("", showticklabels=False, showgrid=False), yaxis=lib.standard_yaxis(showgrid=False)) lib.add_why_and_footer(fig, why, lib.assemble_footer(reg.get("pc_id", "PC-MG-06"), cautions, freshness, ["metrics_demand_rank_history", "categorization_imdb_genres"], extra)) # Ship the title-pool scalar so the subtitle's '{n_titles} most-watched US titles this week' claim # is verifiable straight from the sidecar (every genre row carries the same constant). Append the # hidden-genres aggregate as one residual row so the face annotation '+ ~X% in N smaller genres # (not shown)' is fully reconstructable from data.csv (count = N, residual share = the row's share). csv_df = d.copy() if n_hidden > 0: resid_row = pd.DataFrame([{ "genre": f"(all other genres, under 0.5% each)", "total": hidden_total, "titles": hidden_titles, "share": hidden_share, }]) csv_df = pd.concat([csv_df, resid_row], ignore_index=True) csv_df["shown_on_chart"] = [True] * len(d) + ([False] if n_hidden > 0 else []) csv_df["hidden_genre_count"] = n_hidden csv_df["unique_titles_in_pool"] = n_titles lib.save_csv(csv_df, output_dir) lib.export_chart(fig, output_dir, width=1160, height=740) what_plain = re.sub(r"", "", what) 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/05_genre_subgenre_demand.md"), pc_id=reg.get("pc_id", "PC-MG-06"), intended_lesson_from_spec="Top genres by total weekly demand (founder gap chart)", rendered_lesson={"reader_title": takeaway, "reader_subtitle": what_plain, "claim_strength": "strong", "rules_applied": f"fractional 1/n genre credit; 0.5% floor (hid {n_hidden}); orphan-drop; movie di_rank gate"}, row_count=len(d), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(d)}