"""SOURCE render logic for TI-D-08 (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_D_08(reg: dict) -> dict: """Country Genre Taste Matrix - genre share of viewer-days within each country.""" chart_id = "TI-D-08" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() with conn.cursor() as cur: cur.execute("SET statement_timeout = '240s'") # multi-label genre join over the country-share snapshot t0 = time.time() # Multi-label genres (categorization_imdb_genres) with 1/n_genres weighting so each title's viewers are # conserved across its genres (categorization_primary_genre collapses multi-genre titles - L-046 gotcha). df = lib.run_query(conn, """ WITH latest AS (SELECT MAX(file_date) AS file_date FROM metrics_country_share_90d), gen AS (SELECT DISTINCT imdb_id, genre FROM categorization_imdb_genres WHERE genre IS NOT NULL AND genre NOT IN ('NA','N/A','Unknown','')), gn AS (SELECT imdb_id, COUNT(*) AS n FROM gen GROUP BY imdb_id) SELECT cs.country_code AS geo_code, g.genre AS genre, SUM(cs.n_unique_viewers::numeric / gn.n) AS genre_di, (SELECT file_date FROM latest) AS freshness_date FROM metrics_country_share_90d cs JOIN latest l USING (file_date) JOIN gen g USING (imdb_id) JOIN gn USING (imdb_id) WHERE cs.n_unique_viewers IS NOT NULL GROUP BY cs.country_code, 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 country-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_di"] = df["genre_di"].astype(float) MAJOR = ['US','GB','CA','AU','DE','FR','ES','IT','JP','KR','BR','MX','IN','NL','SE'] country_totals = df.groupby("geo_code")["genre_di"].sum() df["share"] = df.apply(lambda r: r["genre_di"] / country_totals[r["geo_code"]], axis=1) use_c = [c for c in MAJOR if c in set(df["geo_code"])] top_g = df.groupby("genre")["genre_di"].sum().nlargest(12).index.tolist() pivot = df[df["geo_code"].isin(use_c) & df["genre"].isin(top_g)] \ .pivot_table(index="geo_code", columns="genre", values="share", fill_value=0).reindex(use_c).reindex(columns=top_g) # NORMALIZE: over-index vs the all-country average for each genre, so Drama (big everywhere) goes neutral baseline = pivot.mean(axis=0) over = pivot.div(baseline, axis=1) over.index = [cn(c) for c in over.index] # full country names on the axis + in the title z = over.clip(upper=3.0) # label the standout cells with their actual multiple - the colour ramp caps at 3×, so a 6× cell would # otherwise be indistinguishable from a 3× one and the headline "6×" would be unreadable off the chart. ann = [] labeled_cells = [] for yi, c in enumerate(over.index): for xi, g in enumerate(over.columns): v = float(over.values[yi, xi]) if v >= 2.0: ann.append(dict(x=g, y=c, text=(f"{v:.0f}×" if v >= 10 else f"{v:.1f}×"), showarrow=False, font=dict(size=9, color="white"))) labeled_cells.append((c, g, v)) # DIVERGING palette centered on 'typical' (1x): a sequential single-hue ramp could not separate # 'slightly below typical' from 'typical' - now red = below, cream = typical, blue = above fig = go.Figure(data=go.Heatmap(z=z.values, x=z.columns.tolist(), y=z.index.tolist(), colorscale=[[0, "#B0464E"], [0.333, "#F4F1EA"], [0.6, lib.BLUE], [1.0, lib.NAVY]], zmin=0, zmax=3, showscale=True, colorbar=dict(title="vs typical", tickvals=[0.5, 1, 2, 3], ticktext=["½×", "typical", "2×", "3×+"]), hovertemplate="%{y} over-indexes on %{x}
%{z:.1f}× the typical country's taste")) sig = over.idxmax(axis=1); sigv = over.max(axis=1) top_c_sig = sigv.idxmax(); top_g_sig = sig[top_c_sig]; top_v = sigv[top_c_sig] # V2: headline multiple uses the SAME precision as the on-chart cell label (2.7x stays 2.7x, never "3x") title_lesson = (f"Taste fingerprint: every market has a signature once common ground is stripped out - " f"{top_c_sig} over-indexes {top_v:.1f}× on {top_g_sig}") what = ("Each cell = how much more (or less) a country leans into a genre than the typical country - genres everyone watches " "(Drama) cancel out to neutral. Dark blue = standout local taste (2×+, labeled); cream = typical; red = below; major markets only.") # WHY cites only cells the reader can verify on the surface (labeled at 2x+), never invisible evidence ex_cells = sorted(labeled_cells, key=lambda t: -t[2]) seen = set(); examples = [] for c, g, v in ex_cells: if c not in seen: examples.append(f"{c}'s {g.lower()}"); seen.add(c) if len(examples) == 2: break ex_txt = " - " + " and ".join(examples) + " -" if examples else "" why = (f"Raw taste charts just say 'everyone likes Drama.' Normalizing reveals each market's local flavor{ex_txt} " "which is exactly what guides which titles to license or commission for each country.") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1300, height=640, annotations=ann)) lib.add_why_and_footer(fig, why, scope_footer(lib.assemble_footer("PC-D-08", ["CL-006 viewer panel (distinct viewers), not census", "CL-010 genre taxonomy v2"], freshness, ["PeerLogix country-viewing share (90-day)", "categorization_imdb_genres"], "over-index = country share ÷ all-country avg; multi-genre titles split evenly across their genres; " "columns ordered by total panel viewing (biggest genre first)"))) lib.save_csv(df, 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-D-08", intended_lesson_from_spec="Country genre taste, normalized over-index (major markets)", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": "over-index vs all-country avg; 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)}