"""SOURCE render logic for TI-Subgenre-Country-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_Subgenre_Country_01(reg: dict) -> dict: """Each country's SUB-GENRE taste fingerprint - subgenre over-index vs the typical country (the granular sibling of TI-D-08's broad-genre map). One best-fit subgenre per title; top 14 subgenres by demand.""" chart_id = "TI-Subgenre-Country-01" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() MAJOR = ['US', 'GB', 'CA', 'AU', 'DE', 'FR', 'ES', 'IT', 'JP', 'KR', 'BR', 'MX', 'IN', 'NL', 'SE'] # one best-fit subgenre per title (highest GPT confidence) - mirrors primary_genre's one-per-title, no inflation df = lib.run_query(conn, """ WITH latest AS (SELECT MAX(file_date) AS fd FROM metrics_country_share_90d), sg AS (SELECT DISTINCT ON (imdb_id) imdb_id, subgenre FROM categorization_subgenre_gpt WHERE subgenre IS NOT NULL AND subgenre NOT IN ('NA','N/A','Unknown','') ORDER BY imdb_id, confidence_score DESC NULLS LAST) SELECT cs.country_code AS geo, sg.subgenre AS subgenre, SUM(cs.n_unique_viewers::numeric) AS v, (SELECT fd FROM latest) AS freshness_date FROM metrics_country_share_90d cs JOIN latest ON cs.file_date=fd JOIN sg USING (imdb_id) WHERE cs.country_code = ANY(%s) AND cs.n_unique_viewers IS NOT NULL GROUP BY cs.country_code, sg.subgenre; """, (MAJOR,)) tc = lib.run_query(conn, """ WITH sg AS (SELECT DISTINCT ON (imdb_id) imdb_id, subgenre FROM categorization_subgenre_gpt WHERE subgenre IS NOT NULL AND subgenre NOT IN ('NA','N/A','Unknown','') ORDER BY imdb_id, confidence_score DESC NULLS LAST) SELECT subgenre, COUNT(*) AS titles FROM sg GROUP BY subgenre; """) 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-subgenre data") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} NAMES = {"US": "United States", "GB": "United Kingdom", "CA": "Canada", "AU": "Australia", "DE": "Germany", "FR": "France", "ES": "Spain", "IT": "Italy", "JP": "Japan", "KR": "South Korea", "BR": "Brazil", "MX": "Mexico", "IN": "India", "NL": "Netherlands", "SE": "Sweden"} freshness = str(df["freshness_date"].iloc[0]) df = df.drop(columns=["freshness_date"]) df["v"] = df["v"].astype(float) use_c = [c for c in MAJOR if c in set(df["geo"])] piv = df.pivot_table(index="geo", columns="subgenre", values="v", fill_value=0.0).reindex(use_c) # share of each country's demand vs ALL its subgenres, THEN over-index vs the typical country (same method as D-08) cshare = piv.div(piv.sum(axis=1), axis=0) baseline = cshare.mean(axis=0) over_all = cshare.div(baseline, axis=1) # pick the top 14 subgenres by panel volume, with a title-count floor so the over-index is stable titles = tc.set_index("subgenre")["titles"] vol = piv.sum(axis=0) eligible = [s for s in vol.index if int(titles.get(s, 0)) >= 200] topg = vol[eligible].nlargest(14).index.tolist() over = over_all[topg] # order countries by their single strongest over-index; ascending => most distinctive land at TOP of heatmap over = over.loc[over.max(axis=1).sort_values(ascending=True).index] over.index = [NAMES[c] for c in over.index] z = over.clip(upper=3.0) # decimal HALF-UP at 1dp for every printed ratio: float storage made 1.55 print '1.5×' while # 2.05 printed '2.1×' - inconsistent rounding direction at the .05 boundary on one surface from decimal import Decimal, ROUND_HALF_UP def _x1(v) -> str: return str(Decimal(str(float(v))).quantize(Decimal("0.1"), rounding=ROUND_HALF_UP)) ann = [] for yi, c in enumerate(over.index): for xi, g in enumerate(over.columns): val = float(over.values[yi, xi]) # label from 1.5x: near-threshold cells render almost as dark as 2x ones, so an # unlabeled 1.7x next to a labeled 2.1x sent readers hunting for the missing number if val >= 1.5: # font contrast follows the ramp: ink on the pale mid-band, white once the cell is dark ann.append(dict(x=g, y=c, text=(f"{val:.0f}×" if val >= 10 else f"{_x1(val)}×"), showarrow=False, font=dict(size=9, color=("white" if val >= 1.9 else lib.INK)))) # DIVERGING ramp shared with the platform fingerprint heatmap (library consistency): tan = # below typical, near-white = typical (at z=1 -> position 1/3 on the 0-3 scale), blues above. fig = go.Figure(data=go.Heatmap(z=z.values, x=z.columns.tolist(), y=z.index.tolist(), colorscale=[[0, "#C2A878"], [0.333, "#F6F7FA"], [0.55, lib.PALE_BLUE], [0.78, lib.BLUE], [1.0, lib.NAVY]], zmin=0, zmax=3, showscale=True, colorbar=dict(title="vs typical", tickvals=[0.5, 1, 2, 3], ticktext=["½× (below)", "typical", "2×", "3×+"]), hovertemplate="%{y} over-indexes on %{x}
%{z:.1f}× the typical country's appetite")) flat = over.stack() top_idx = flat.idxmax(); top_country, top_genre = top_idx; top_val = float(flat.max()) title_lesson = (f"Taste fingerprint: at the sub-genre level, {top_country}'s appetite for " f"{top_genre.lower()} runs {_x1(top_val)}× the typical country's") # ONE reference word on this surface: 'typical (country)' - matching the colorbar and the cell # definition, and DEFINED in the subtitle (the average of the markets shown) what = ("Each cell = how much more (or less) a country leans into a sub-genre than the typical country - " "the average of the markets shown. " "Dark blue = a standout local taste (cells from 1.5× carry their number); tan = below the typical country.") # WHY cites only cells the chart actually labels (>=1.5x), never invisible evidence lab = flat[flat >= 1.5].sort_values(ascending=False) seen = set(); examples = [] for (c, g), v in lab.items(): if c not in seen: examples.append(f"{c}'s {g.lower()}"); seen.add(c) if len(examples) == 3: break ex_txt = ": " + ", ".join(examples) if examples else "" why = (f"Broad genres blur cultures together - 'everyone watches drama.' Sub-genres expose the real signature{ex_txt}. " "This is the granular map for what to license, dub and promote market by market.") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1340, height=720, annotations=ann), xaxis=dict(tickangle=-25, tickfont=dict(size=9.5), automargin=True), yaxis=dict(tickfont=dict(size=10.5), automargin=True)) lib.add_why_and_footer(fig, why, scope_footer(lib.assemble_footer("PC-D-13", ["CL-006 viewer panel (distinct viewers), not census", "CL-010 genre taxonomy v2", "sub-genre labels are model-assigned (one best fit per title)"], freshness, ["PeerLogix country-viewing share (90-day)", "GPT subgenre labels"], "'typical country' = the average of the countries shown, per sub-genre share of viewing; " "raw shares ship in the data download; top 14 sub-genres by 90-day panel viewing (200+ titles each); major markets; " "rows ordered by each country's strongest signature"))) # data.csv ships the RAW SHARES alongside the final ratios, so an expert can reconstruct the # index from a named definition: over_index = country share ÷ typical-country share, where the # typical-country share = the mean of that sub-genre's share across the countries shown cshare_named = cshare.copy(); cshare_named.index = [NAMES[c] for c in cshare_named.index] csv_rows = [{"country": c, "subgenre": g, "share_of_country_90d_panel_viewing": float(cshare_named.loc[c, g]), "typical_country_share": float(baseline[g]), "over_index_vs_typical_country": float(over.loc[c, g])} for c in over.index for g in over.columns] lib.save_csv(pd.DataFrame(csv_rows), output_dir); lib.export_chart(fig, output_dir, width=1340, 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-D-13", intended_lesson_from_spec="National taste fingerprint at SUB-GENRE granularity (over-index by country)", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": "subgenre over-index vs typical country; top-14 by volume, >=200 titles"}, row_count=int(over.size), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": int(over.size)} # =========================================================================== # SECTION 05 - Genre & Subgenre Demand (4 charts) # ===========================================================================