"""SOURCE render logic for TI-MG-03 (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_03(reg: dict) -> dict: """What each genre is really made of - the defining subgenres (keyword layer) beneath each parent genre.""" chart_id = "TI-MG-03" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() # Multi-label genres (categorization_imdb_genres): a title contributes to EVERY genre it carries, so a # Drama+Horror title shows up under both - the primary_genre table collapsed these (the GENRE-TABLE trap). # pg is restricted to subgenre-covered titles FIRST (categorization_imdb_genres spans all of IMDb; the # unrestricted DISTINCT CTE referenced twice gets materialized in full and times out). df = lib.run_query(conn, """ WITH sg AS (SELECT DISTINCT imdb_id, subgenre FROM categorization_subgenre_gpt WHERE subgenre IS NOT NULL), pg AS (SELECT DISTINCT g.imdb_id, g.genre AS primary_genre FROM categorization_imdb_genres g JOIN (SELECT DISTINCT imdb_id FROM sg) s USING (imdb_id) WHERE g.genre IS NOT NULL AND g.genre NOT IN ('NA','N/A','Unknown','')), top_gen AS (SELECT primary_genre FROM pg GROUP BY primary_genre ORDER BY COUNT(*) DESC LIMIT 9), co AS ( SELECT pg.primary_genre, sg.subgenre, COUNT(DISTINCT pg.imdb_id) AS n_titles FROM pg JOIN top_gen USING (primary_genre) JOIN sg USING (imdb_id) GROUP BY pg.primary_genre, sg.subgenre ), ranked AS ( SELECT primary_genre, subgenre, n_titles, ROW_NUMBER() OVER (PARTITION BY primary_genre ORDER BY n_titles DESC) AS r FROM co ) SELECT primary_genre, subgenre, n_titles, r, (SELECT MAX(last_updated)::date FROM categorization_subgenre_gpt) AS freshness_date FROM ranked WHERE r <= 5 ORDER BY primary_genre, r; """) 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 subgenre data") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} freshness = str(df["freshness_date"].iloc[0]) # data as-of = the classifier table's last update df = df.drop(columns=["freshness_date"]) df["n_titles"] = df["n_titles"].astype(int) df["sg_short"] = df["subgenre"].apply(lambda s: lib.ellipsize(s, 24)) # order genres by their total title count (descending) for a sensible reading order order = df.groupby("primary_genre")["n_titles"].sum().sort_values(ascending=False).index.tolist() cols = 3; rows = (len(order) + cols - 1) // cols fig = make_subplots(rows=rows, cols=cols, subplot_titles=[f"{g}" for g in order], vertical_spacing=0.11, horizontal_spacing=0.16) for i, g in enumerate(order): sub = df[df["primary_genre"] == g].sort_values("n_titles", ascending=True) rr, cc = i // cols + 1, i % cols + 1 fig.add_trace(go.Bar(x=sub["n_titles"], y=sub["sg_short"], orientation="h", marker=dict(color=lib.NAVY), showlegend=False, # the printed count IS the readable encoding (panels use independent scales) text=[f" {v:,}" for v in sub["n_titles"]], textposition="outside", textfont=dict(size=8.5, color=lib.MUTED), cliponaxis=False, hovertemplate=f"{g} · %{{y}}
%{{x:,}} titles"), row=rr, col=cc) fig.update_xaxes(range=[0, float(sub["n_titles"].max()) * 1.3], row=rr, col=cc) fig.update_xaxes(gridcolor=lib.GRID, showticklabels=False) fig.update_yaxes(gridcolor=lib.GRID, showgrid=False, tickfont=dict(size=10), automargin=True) drama_top = df[df["primary_genre"] == "Drama"].nsmallest(1, "r") horror_top = df[df["primary_genre"] == "Horror"].nsmallest(1, "r") ex = [] if not drama_top.empty: ex.append(f"Drama most often means {drama_top.iloc[0]['subgenre'].lower()}") if not horror_top.empty: ex.append(f"Horror leans {horror_top.iloc[0]['subgenre'].lower()}") title_lesson = "Each genre is a bundle of specific flavors" + (" - " + "; ".join(ex) if ex else "") what = ("For each major genre, the 5 most-common subgenre tags beneath it (the number = how many titles carry the tag; " "each panel uses its own scale). Subgenres are the keyword-level flavors within a genre - the layer 'Drama' or 'Horror' alone hides.") why = ("Genre is too blunt for programming or discovery. The subgenre layer is where audience taste actually lives - it's how you pin " "the exact lane a hit occupies (‘folk horror’, not just ‘horror’) and spot the adjacent white space worth filling.") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1320, height=860)) fig.update_annotations(font=dict(size=12, color=lib.INK)) # catalog-tag chart: no panel data at all, so it carries NO panel-scope phrase (scope=None) lib.add_why_and_footer(fig, why, scope_footer(lib.assemble_footer("PC-MG-03", ["CL-010 genre taxonomy v2", "CL-015 subgenre tags are model-assigned"], freshness, ["categorization_imdb_genres", "GPT subgenre labels"], "catalog tags only (no viewing data); multi-label genres (a title counts in every genre it carries); bars use " "independent scales per genre; as-of = the classifier's last labeling run (labels refresh per run, not daily)"), scope=None), why_y=-0.10) # heatless grid of 9 panels leaves no clean interior region: pre-place the brand mark in the # bottom margin so the auto-spot can't drop it onto a panel title (it collided with 'Horror') fig.add_annotation(name="brand_watermark", text=lib.WATERMARK_TEXT, xref="paper", yref="paper", x=1.0, y=-95.0 / 860, 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=1320, height=860) 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-MG-03", intended_lesson_from_spec="Subgenre composition of each parent genre (keyword layer)", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": "9 genres x top-5 subgenres"}, row_count=len(df), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)}