"""SOURCE render logic for TI-C-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_C_04(reg: dict) -> dict: """Genre-Dominance Map by Platform - uses metrics_genre_dominance_daily.""" chart_id = "TI-C-04" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() df = lib.run_query(conn, """ WITH latest AS (SELECT MAX(file_date) AS file_date FROM metrics_genre_dominance_daily) SELECT gd.platform, gd.genre, gd.total_di::numeric AS total_di, gd.share_pct::numeric AS share_pct, gd.file_date AS freshness_date FROM metrics_genre_dominance_daily gd JOIN latest USING (file_date) WHERE gd.share_pct IS NOT NULL AND gd.platform NOT IN ('ALL','All','all') -- 'ALL' is a cross-platform aggregate, not a platform AND gd.platform NOT IN ('Philo','YouTube TV','fuboTV','Plex','Sling TV') -- live-TV bundles/aggregators: linear carriage distorts genre mix AND gd.genre NOT IN ('NA','N/A','Unknown','','ALL','All'); """) 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-dominance rows") write_shim(output_dir, chart_id) return {"status": "needs_spec_review"} freshness = str(df["freshness_date"].iloc[0]) df["share_pct"] = df["share_pct"].astype(float) df["total_di"] = pd.to_numeric(df["total_di"], errors="coerce").fillna(0).astype(float) # Curated streaming services (the row set the story is about): the 8 big SVOD + the two genre # specialists. Falls back to top-10-by-demand only if the curated set is missing from the data. CURATED = ["Netflix", "Prime Video", "Hulu", "Max", "Disney+", "Paramount+", "Peacock", "Apple TV+", "Crunchyroll", "Tubi"] present = [p for p in CURATED if p in set(df["platform"])] top_platforms = present if len(present) >= 6 else df.groupby("platform")["total_di"].sum().nlargest(10).index.tolist() scoped = df[df["platform"].isin(top_platforms)] top_genres = scoped.groupby("genre")["total_di"].sum().nlargest(13).index.tolist() # the 'Action & Adventure' combo column splits one signal across three near-duplicate columns if "Action & Adventure" in top_genres and "Action" in top_genres and "Adventure" in top_genres: top_genres.remove("Action & Adventure") top_genres = top_genres[:12] pivot = df[df["platform"].isin(top_platforms) & df["genre"].isin(top_genres)] \ .pivot_table(index="platform", columns="genre", values="share_pct", fill_value=0) pivot = pivot.reindex(top_platforms).reindex(columns=top_genres) # NORMALIZE: over-index vs the all-platform average share for each genre. # This removes genres that are big everywhere (Drama) and reveals each platform's distinctive strengths. baseline = pivot.mean(axis=0) # avg share of each genre across platforms over_index = pivot.div(baseline, axis=1) # 1.0 = typical; 2.0 = twice the typical share # Row order with visible logic: biggest service (by total demand) on TOP (heatmap y renders # bottom-up, so the index is reversed). row_order = scoped.groupby("platform")["total_di"].sum().reindex(over_index.index).sort_values(ascending=False).index.tolist() over_index = over_index.reindex(row_order[::-1]) # Clip for color stability z = over_index.clip(upper=4.0) # Annotate the standout cells with their ACTUAL multiple - the colour ramp caps at 4×, so a 6× cell # would otherwise look identical to a 4× one and the headline "6×" would be unreadable off the chart. LABEL_FLOOR = 2.0 # same threshold as the subtitle declares - no unexplained borderline-dark cells ann = [] for yi, p in enumerate(over_index.index): for xi, g in enumerate(over_index.columns): v = float(over_index.values[yi, xi]) if v >= LABEL_FLOOR: # white text from the same threshold that triggers labeling: every labeled cell # sits on a dark-enough ramp color, so no dark-ink-on-navy unreadable labels ann.append(dict(x=g, y=p, text=(f"{v:.0f}×" if v >= 10 else f"{v:.1f}×"), showarrow=False, font=dict(size=9, color="white"))) fig = go.Figure(data=go.Heatmap( z=z.values, x=z.columns.tolist(), y=z.index.tolist(), # DIVERGING ramp around 'typical': tan = below typical, near-white = typical, blue = above # (a single-hue ramp made 'below typical' and 'typical' the same pale wash) colorscale=[[0, "#C2A878"], [0.25, "#F6F7FA"], [0.5, lib.PALE_BLUE], [0.75, lib.BLUE], [1.0, lib.NAVY]], zmin=0, zmax=4, showscale=True, colorbar=dict(title="vs typical", tickvals=[0.25, 1, 2, 3, 4], ticktext=["¼× (below)", "typical", "2×", "3×", "4×+"]), hovertemplate="%{y} carries %{x} at %{z:.1f}× the typical service's share", )) # Strongest signature per platform (highest over-index) sig = over_index.idxmax(axis=1) sig_val = over_index.max(axis=1) top_sig_plat = sig_val.idxmax() top_sig_genre = sig[top_sig_plat] top_sig_val = sig_val[top_sig_plat] title_lesson = (f"Taste fingerprint: strip away what everyone carries and " f"{top_sig_plat} shows its signature - {top_sig_val:.1f}× the typical service's share of {top_sig_genre}") what = ("Each cell = how much more (or less) of a genre a service carries vs. the typical service; rows are sorted biggest " "service first, and every 2×+ cell is labelled. Genres big everywhere, like Drama, sit near-white ('typical'); " "tan = below typical, dark blue = a real signature.") why = ("A raw genre chart just says 'everyone has Drama.' Normalizing reveals each service's identity - what it's unusually " "strong in (anime, kids, prestige) - which is what actually differentiates services for viewers and guides what to acquire.") footer = lib.assemble_footer(pc_id="PC-C-04", caution_labels=["CL-010 genre taxonomy v2"], freshness_date=freshness, sources=["metrics_genre_dominance_daily"], extra=("10 curated streaming services ('typical' is the average across these 10) · " "live-TV bundles excluded (they carry channels, not curated catalogs)")) fig.update_layout(**lib.layout_v2(title_lesson, what, width=1300, height=640, annotations=ann)) lib.add_why_and_footer(fig, why, footer) # sidecar hygiene: the internal metric column name never ships ('DI' is a banned reader-facing token) lib.save_csv(df.rename(columns={"total_di": "total_demand"}), output_dir) lib.export_chart(fig, output_dir, width=1300, height=640) 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-C-04", intended_lesson_from_spec="Genre over-index by platform (normalized, redesigned)", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": "over-index vs curated-10 avg; vMVPDs excluded; Action-family deduped"}, row_count=len(df), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)}