"""SOURCE render logic for TI-CT13-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_CT13_01(reg: dict) -> dict: """Single-Metric Share-Deviation Stripes - genre share deviation from overall baseline.""" chart_id = "TI-CT13-01" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() # Multi-label genres with 1/n_genres weighting on BOTH demand and title counts, so a multi-genre title's # demand (and its single unit of "supply") is split - not duplicated - across its genres, and the # demand-share minus supply-share comparison stays conserved. Demand Score basis (audience size). # NB: gen is restricted to the demand universe FIRST - categorization_imdb_genres spans all of IMDb, and an # unrestricted DISTINCT CTE referenced twice gets materialized in full (statement timeout). df = lib.run_query(conn, """ WITH latest AS (SELECT MAX(file_date) AS file_date FROM title_metrics_daily), dem AS (SELECT t.imdb_id, t.demand_score::numeric AS demand FROM title_metrics_daily t JOIN latest l USING (file_date) WHERE t.demand_score IS NOT NULL AND t.geo_code='US'), gen AS (SELECT DISTINCT g.imdb_id, g.genre FROM categorization_imdb_genres g JOIN dem USING (imdb_id) WHERE g.genre IS NOT NULL AND g.genre NOT IN ('NA','N/A','Unknown','')), gn AS (SELECT imdb_id, COUNT(*) AS n FROM gen GROUP BY imdb_id) SELECT g.genre AS primary_genre, SUM(dem.demand / gn.n) AS genre_demand, SUM(1.0 / gn.n) AS titles, (SELECT file_date FROM latest) AS freshness_date FROM dem JOIN gen g USING (imdb_id) JOIN gn USING (imdb_id) GROUP BY g.genre; """) 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 data") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} freshness = str(df["freshness_date"].iloc[0]) df = df.drop(columns=["freshness_date"]) df["genre_demand"] = df["genre_demand"].astype(float) df["titles"] = df["titles"].astype(float) total_demand = df["genre_demand"].sum() total_titles = df["titles"].sum() df["demand_share"] = df["genre_demand"] / total_demand df["supply_share"] = df["titles"] / total_titles df["dev"] = df["demand_share"] - df["supply_share"] df = df.sort_values("dev", ascending=True) fig = go.Figure() # Positive = over-indexes on viewing vs its title share; negative = high-volume, low-viewing. # These are STATIC states, not rising/falling movements, so the hues are neutral (blue/grey) per # the color contract - green stays reserved for rising-only. Sign alone decides the hue. colors = [lib.BLUE if v > 0 else (lib.MUTED if v < 0 else lib.PALE_BLUE) for v in df["dev"]] fig.add_trace(go.Bar(x=df["dev"], y=df["primary_genre"], orientation="h", marker=dict(color=colors), # near-zero labels suppressed: a wall of '+0.0%'/'-0.0%' is chartjunk and '-0.0%' reads as a glitch text=[(f"{v:+.1%}" if abs(v) >= 0.001 else "") for v in df["dev"]], textposition="outside", textfont=dict(size=10), cliponaxis=False, showlegend=False, hovertemplate="%{y}
Demand is %{x:+.1%} vs its share of titles")) fig.add_vline(x=0, line=dict(color=lib.INK, width=1)) # pole captions (no arrows - the old arrows pointed toward zero, against the meaning). # The blue caption sits a few rows BELOW the top, in the clear space right of the shorter bars: # at y=len-0.5 it was clipped by the plot edge and printed over the top bar. fig.add_annotation(x=float(df["dev"].max()), y=len(df)-3.5, text="draws more viewing than its shelf space", showarrow=False, xanchor="right", yanchor="middle", font=dict(size=10, color=lib.BLUE), bgcolor="rgba(255,255,255,0.92)") fig.add_annotation(x=float(df["dev"].min()), y=0.5, text="more shelf space than viewing", showarrow=False, xanchor="left", yanchor="middle", font=dict(size=10, color=lib.MUTED), bgcolor="rgba(255,255,255,0.85)") df_sorted = df.sort_values("dev", ascending=False) top_over = df_sorted.iloc[0]; top_under = df_sorted.iloc[-1] title_lesson = (f"Some genres punch above their weight, others below: {top_over['primary_genre']} draws more of the audience than its share of the catalog, " f"while {top_under['primary_genre']} fills the shelves but gets little attention") what = ("Each bar compares a genre's share of total US viewing demand with its share of total titles (multi-genre titles " "are split evenly across their genres, so nothing is double-counted). Blue (right of zero) = draws more " "attention than its catalog size suggests; gray = the reverse; bars near zero are left unlabeled.") why = ("It shows where the catalog's mix is out of step with what people actually watch - a prompt to look closer, not an order to " "‘make more of the blue.’ A genre that draws a lot per title won't necessarily reward simply making more of it, and high-volume genres " "like documentary earn their place as cheap, prestige, and niche content this view can't see.") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1200, height=720), xaxis=lib.standard_xaxis("Share of total US viewing demand minus share of titles", tickformat=".0%"), yaxis=lib.standard_yaxis(showgrid=False)) lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-CT-13", ["CL-006 viewer panel (distinct viewers), not census", "CL-010 genre taxonomy v2"], freshness, ["title_metrics_daily", "categorization_imdb_genres"], "US Demand Score basis; multi-label genres, 1/n-weighted so demand and supply are conserved")) lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1200, height=720) 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-CT-13", intended_lesson_from_spec="Genre demand-vs-supply opportunity map", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": "demand share - supply share; US Demand Score basis; multi-label genres 1/n-weighted"}, row_count=len(df), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)} def render_TI_Other_02(reg: dict) -> dict: """The flavors audiences are hungriest for - subgenres by demand PER TITLE (intensity, not tag count).""" chart_id = "TI-Other-02" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() # Title-level rows so the aggregation can be MEDIAN-based (validated methodology: median-not-mean for # demand-per-title-by-genre) with sibling-label merging before ranking. raw = lib.run_query(conn, """ WITH latest AS (SELECT MAX(file_date) AS fd FROM title_metrics_daily), dem AS ( SELECT imdb_id, demand_score::numeric AS demand FROM title_metrics_daily JOIN latest ON file_date=fd WHERE demand_score IS NOT NULL AND geo_code='US' ), sg AS (SELECT DISTINCT imdb_id, subgenre FROM categorization_subgenre_gpt WHERE subgenre IS NOT NULL) SELECT sg.subgenre, sg.imdb_id, dem.demand, (SELECT fd FROM latest) AS file_date FROM sg JOIN dem USING (imdb_id); """) q_sec = time.time() - t0 if raw.empty: conn.close() lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No subgenres") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} freshness = str(raw["file_date"].iloc[0]) raw["demand"] = raw["demand"].astype(float) # merge sibling GPT labels before ranking so one flavor (often the SAME exemplar show) isn't # counted as several bars at different multiples - the Doctor Who-at-5.7x-and-2.7x trap ALIASES = {"Galactic Showdown": "Space Adventure", "Alien Adventure": "Space Adventure", "Epic Fantasy": "High Fantasy", "Heroic Fantasy": "High Fantasy", "Temporal Conflict": "Time Travel", "Journeys Through Time": "Time Travel", "Espionage Thriller": "Spy Thriller", "Sci-Fi Espionage": "Spy Thriller"} raw["subgenre"] = raw["subgenre"].map(lambda s: ALIASES.get(s, s)) raw = raw.drop_duplicates(["subgenre", "imdb_id"]) overall_median = float(raw.drop_duplicates("imdb_id")["demand"].median()) df = (raw.groupby("subgenre") .agg(titles=("imdb_id", "nunique"), median_demand=("demand", "median"), mean_demand=("demand", "mean")) .reset_index()) df = df[df["titles"] >= 25].copy() df["over"] = df["median_demand"] / overall_median n_qualifying = len(df) # subgenres clearing the 25-title floor - disclosed on the footer (selection scope) df = df.sort_values("over", ascending=False).head(18) # 3 example titles per kept subgenre (concrete anchors for the opaque GPT labels) ex_rows = raw[raw["subgenre"].isin(df["subgenre"])].sort_values("demand", ascending=False).groupby("subgenre").head(3) ex_titles = lib.resolve_titles(conn, ex_rows["imdb_id"].unique().tolist()) conn.close() nm = dict(zip(ex_titles["imdb_id"], ex_titles["display_title"])) ex_lists = (ex_rows.assign(nm=ex_rows["imdb_id"].map(nm)) .groupby("subgenre")["nm"].apply(lambda s: [x for x in s if pd.notna(x)]).to_dict()) df["examples"] = df["subgenre"].map(lambda s: ", ".join(ex_lists.get(s, []))).fillna("") # the example title rides the bar itself: a static PNG reader gets no hover, and the GPT label # names (Edge of Extinction etc.) are opaque without a concrete anchor. Budget 40 chars: full # words beat ellipses, and pad_range below buys the headroom. Each row gets a DISTINCT exemplar # (its biggest not-yet-used title): one show fronting two bars at different multiples reads as # a contradiction even when the multi-tagging is legitimate. used_ex = set() def _pick_ex(s): cands = ex_lists.get(s, []) for c in cands: if c not in used_ex: used_ex.add(c) return c return cands[0] if cands else "" df = df.sort_values("over", ascending=False) df["ex1"] = [short_title(_pick_ex(s), 40) for s in df["subgenre"]] df = df.sort_values("over", ascending=True) # label rounds the 2dp sidecar value (2.75 in data.csv -> 2.8x, never a second-rounding 2.7x) bar_text = [f" {round(o, 2):.1f}× · {t:,} titles" + (f" · e.g. {e}" if e else "") for o, t, e in zip(df["over"], df["titles"], df["ex1"])] fig = go.Figure() fig.add_trace(go.Bar(x=df["over"], y=df["subgenre"], orientation="h", # color contract: NAVY = the leader (highest pull-per-title) ONLY; pale-steel field marker=dict(color=lib.leader_colors(df["over"], field=lib.PALE_BLUE)), text=bar_text, textposition="outside", textfont=dict(size=10, color=lib.MUTED), cliponaxis=False, showlegend=False, customdata=df[["median_demand", "titles", "examples", "mean_demand"]], hovertemplate=("%{y}
typical title %{x:.1f}× the overall typical title · median Demand Score " "%{customdata[0]:,.1f} (mean %{customdata[3]:,.1f}) · %{customdata[1]:,} titles" "
e.g. %{customdata[2]}"))) # the 1x line crosses every bar - draw it above the fills, label it with an anchored arrow vline_above(fig, 1.0) ref_label_arrow(fig, 1.0, "typical title (1×)", y_paper=0.03, ax=120) df_desc = df.sort_values("over", ascending=False) a = df_desc.iloc[0] title_lesson = f"Audiences pull hardest toward a few flavors: the typical {a['subgenre']} title draws {round(float(a['over']), 2):.1f}× the typical title's pull" what = ("Each bar = median Demand Score per title in that subgenre - what the typical (median) single title attracts, versus the typical title " "overall (dashed line = 1×). This measures pulling power per release - not how many such titles exist.") why = ("This is the external taste map at the flavor level. These are the lanes where one strong title travels furthest - the " "highest-leverage bets for new programming and the franchise territory worth chasing. Each bar names an example title.") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1320, height=800), # single-encoded axis: the bars encode the over-index MULTIPLE (subgenre median / overall median), # not the raw median score, so the axis names only the multiple xaxis=lib.standard_xaxis("Demand Score multiple (typical title in the subgenre vs typical title overall)"), yaxis=lib.standard_yaxis(showgrid=False)) fig.update_xaxes(range=lib.pad_range(df["over"], labels=bar_text, font_px=10, fig_width=1320)) lib.add_why_and_footer(fig, why, lib.assemble_footer(reg.get("pc_id", "PC-Other-02"), ["CL-006 viewer panel (distinct viewers), not census", "CL-015 subgenre tags are model-assigned"], freshness, # reader-facing source name (no internal project names, no raw table names); # method detail stays compact so the footer holds at trust + Sources ["title_metrics_daily", "model-assigned subgenre labels"], (f"top {len(df)} of {n_qualifying} subgenres with 25+ titles, ranked by per-title pull; sibling labels merged" if n_qualifying > len(df) else f"all {n_qualifying} subgenres with 25+ titles; sibling labels merged"))) lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1320, height=800) 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=reg.get("pc_id", "PC-Other-02"), intended_lesson_from_spec="Subgenre demand intensity (median per-title, external taste map)", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": "median Demand Score per title over-index; sibling GPT labels alias-merged; >=25 titles"}, row_count=len(df), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)} # =========================================================================== # SECTION 06 - Production, Talent and Reception (9 charts) # ===========================================================================