"""SOURCE render logic for TI-PC-02 (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_PC_02(reg: dict) -> dict: """Each major studio's signature genre - genre over-index (vs the average major) for the most prominent studios.""" chart_id = "TI-PC-02" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() # Selection basis = Demand Score (audit #1); genre layer = multi-label categorization_imdb_genres (audit #5): # a title counts toward EVERY genre it carries, and shares are within-studio so the over-index stays comparable. df = lib.run_query(conn, """ WITH latest AS (SELECT MAX(file_date) AS fd FROM title_metrics_daily), pc AS (SELECT DISTINCT imdb_id, production_co FROM categorization_production_co WHERE production_co IS NOT NULL), cnt AS (SELECT imdb_id, COUNT(*) AS cc FROM pc GROUP BY imdb_id), 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'), -- PROMINENCE = total credit-shared demand (picks real majors, not prolific-but-obscure cos) top_pc AS ( SELECT pc.production_co FROM pc JOIN cnt USING (imdb_id) JOIN dem USING (imdb_id) GROUP BY pc.production_co HAVING COUNT(DISTINCT pc.imdb_id) >= 15 ORDER BY SUM(dem.demand / NULLIF(cnt.cc,0)) DESC LIMIT 12 ), gen AS (SELECT DISTINCT imdb_id, genre AS primary_genre FROM categorization_imdb_genres WHERE genre IS NOT NULL AND genre NOT IN ('NA','N/A','Unknown','')) SELECT pc.production_co, gen.primary_genre, COUNT(DISTINCT pc.imdb_id) AS title_count, (SELECT fd FROM latest) AS freshness_date FROM pc JOIN top_pc USING (production_co) JOIN gen USING (imdb_id) GROUP BY pc.production_co, gen.primary_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 prod-co x 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["title_count"] = df["title_count"].astype(int) pc_tot = df.groupby("production_co")["title_count"].sum() df["share"] = df.apply(lambda r: r["title_count"] / pc_tot[r["production_co"]], axis=1) # baseline = the genre's share across ALL these majors pooled → over-index = specialty vs a typical major g_tot = df.groupby("primary_genre")["title_count"].sum(); grand = df["title_count"].sum() base = (g_tot / grand).to_dict() df["over"] = df.apply(lambda r: r["share"] / base[r["primary_genre"]], axis=1) top_g = g_tot.nlargest(10).index.tolist() pc_order = pc_tot.loc[[c for c in pc_tot.index]].sort_values(ascending=False).index.tolist() pivot = df[df["primary_genre"].isin(top_g)].pivot_table(index="production_co", columns="primary_genre", values="over", fill_value=0).reindex(index=pc_order, columns=top_g) # parallel title-count pivot: only spotlight signatures backed by enough titles (a thin cell over-indexes noisily) cnt_pivot = df[df["primary_genre"].isin(top_g)].pivot_table(index="production_co", columns="primary_genre", values="title_count", fill_value=0).reindex(index=pc_order, columns=top_g) # annotate standout specialties (>=1.6x AND >=15 titles) so the eye lands on robust ones, not thin-sample noise ann = [] for yi, co in enumerate(pivot.index): for xi, g in enumerate(pivot.columns): v = pivot.values[yi, xi] if v >= 1.6 and cnt_pivot.values[yi, xi] >= 15: ann.append(dict(x=g, y=co, text=f"{v:.1f}×", showarrow=False, font=dict(size=9, color="white" if v >= 2.3 else lib.INK))) # THIN-CELL MASK: the >=15-title gate must apply to the COLOR too, not just the 'x' labels - # otherwise a 3-title fluke renders near-navy and out-shouts the gated annotations. Blank = too thin to call. z_masked = pivot.values.astype(float).copy() z_masked[cnt_pivot.values < 15] = np.nan fig = go.Figure(data=go.Heatmap(z=z_masked, x=pivot.columns.tolist(), y=pivot.index.tolist(), zmin=0, zmax=3, zmid=1, hoverongaps=False, colorscale=[[0.0, "#E7ECF3"], [0.33, "#F4F1EA"], [0.66, lib.BLUE], [1.0, lib.NAVY]], colorbar=dict(title="vs avg
major", tickvals=[0, 1, 2, 3], ticktext=["0×", "1×", "2×", "3×+"]), hovertemplate="%{y}
%{x}: %{z:.1f}× the average major")) # find strongest specialty backed by a solid sample (>=15 titles) - avoids headlining a thin composite-genre outlier _cand = df[(df["primary_genre"].isin(top_g)) & (df["title_count"] >= 15)] flat = (_cand if not _cand.empty else df[df["primary_genre"].isin(top_g)]).sort_values("over", ascending=False).iloc[0] title_lesson = (f"Taste fingerprint: normalize for size and the big studios show distinct genre identities - " f"{flat['production_co']} leans into {flat['primary_genre']} {flat['over']:.1f}× as hard as the average major") what = ("Each cell = how much more a studio leans into a genre than the average major, based on its mix of titles (not viewing). " "Dark = leaning harder; '×' labels the standouts (1.6× and up, on 15+ titles); pale = below average; blank = too few titles to call.") why = ("Raw genre counts just show that everyone makes Drama. Over-indexing reveals each studio's actual identity - who owns horror, " "who owns animation - which is what tells you where a studio is the natural home for a project and who your real competition is.") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1300, height=680, annotations=ann), xaxis=dict(side="top", tickfont=dict(size=11)), yaxis=dict(tickfont=dict(size=10.5), automargin=True)) # trust line scoped to what the panel actually does here: it RANKS the studios; the grid is catalog mix lib.add_why_and_footer(fig, why, scope_footer(lib.assemble_footer("PC-PC-02", ["CL-010 genre taxonomy v2"], freshness, ["title_metrics_daily", "categorization_production_co", "categorization_imdb_genres"], "12 studios with the most credit-shared Demand Score; cell = share of the studio's titles in that genre vs " "the pooled major mix; cells under 15 titles blanked; '×' marks 1.6×+ with 15+ titles"), scope="US viewer panel demand picks the 12 studios; the grid itself is catalog mix, not viewing")) # full-canvas heatmap: pre-place the brand mark in the bottom margin so the auto-spot # cannot drop it onto data cells (it landed on the 20th Century Fox / Universal rows) fig.add_annotation(name="brand_watermark", text=lib.WATERMARK_TEXT, xref="paper", yref="paper", x=1.0, y=-95.0 / 680, xanchor="right", yanchor="top", showarrow=False, align="right", font=dict(family="Arial, sans-serif", size=12.5, color="#8d94a1"), opacity=0.8) lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1300, height=680) 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-PC-02", intended_lesson_from_spec="Studio genre specialty (over-index, prominent studios only)", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": "Demand-Score-ranked studios + multi-label genre over-index"}, row_count=len(df), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)}