"""SOURCE render logic for TI-D-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_D_03(reg: dict) -> dict: """Local Over-Index Map - countries where local titles over-index vs global.""" chart_id = "TI-D-03" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() df = lib.run_query(conn, """ WITH latest_cs AS (SELECT MAX(file_date) AS file_date FROM metrics_country_share_90d), title_primary AS ( SELECT DISTINCT ON (imdb_id) imdb_id, country_code AS primary_country FROM metrics_country_share_90d JOIN latest_cs USING (file_date) WHERE share_pct IS NOT NULL ORDER BY imdb_id, share_pct DESC ), country_pool AS ( SELECT cs.country_code AS geo_code, cs.imdb_id, cs.n_unique_viewers::numeric AS viewers FROM metrics_country_share_90d cs JOIN latest_cs USING (file_date) WHERE cs.n_unique_viewers IS NOT NULL ), global_pool AS (SELECT geo_code, SUM(viewers) AS country_total FROM country_pool GROUP BY geo_code) SELECT cp.geo_code, gp.country_total, SUM(CASE WHEN tp.primary_country = cp.geo_code THEN cp.viewers ELSE 0 END) AS local_origin_di, (SELECT file_date FROM latest_cs) AS freshness_date FROM country_pool cp LEFT JOIN title_primary tp USING (imdb_id) JOIN global_pool gp USING (geo_code) GROUP BY cp.geo_code, gp.country_total HAVING gp.country_total > 0 ORDER BY gp.country_total DESC LIMIT 30; """) 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 local over-index 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["country_total"] = df["country_total"].astype(float) df["local_origin_di"] = df["local_origin_di"].astype(float) df["local_share"] = df["local_origin_di"] / df["country_total"] # Focus on established markets (drop tiny markets where the home-grown proxy is 0/noisy) MAJOR = ['US','GB','CA','AU','IN','BR','MX','FR','DE','ES','IT','JP','KR','NL','SE','PL','TR','AR','ZA','PH'] df = df[df["geo_code"].isin(MAJOR)].sort_values("local_share", ascending=True) if df.empty: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No major-market rows") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} df["geo_code"] = df["geo_code"].map(cn) # full country names for the layman colors = [lib.NAVY if s >= 0.4 else (lib.MUTED if s < 0.1 else lib.BLUE) for s in df["local_share"]] fig = go.Figure() fig.add_trace(go.Bar(x=df["local_share"], y=df["geo_code"], orientation="h", marker=dict(color=colors), # sub-1% shares print one decimal so a real 0.4% never rounds to a broken-looking "0%" text=[(f"{v:.1%}" if v < 0.01 else f"{v:.0%}") for v in df["local_share"]], textposition="outside", textfont=dict(size=11, color=lib.INK), cliponaxis=False, showlegend=False, hovertemplate="%{y}
%{x:.1%} of viewing is home-grown")) fig.update_xaxes(range=[0, min(1.0, float(df["local_share"].max()) * 1.18)]) top_local = df.iloc[-1]; low_local = df.iloc[0] title_lesson = (f"{top_local['geo_code']} watches its own - {top_local['local_share']:.0%} of its viewing is home-grown; " f"{low_local['geo_code']} watches almost none ({low_local['local_share']:.1%} - nearly all imports)") what = ("For each major market, the share of viewing that goes to home-grown titles (home-grown = the market where the title " "is most-watched) vs. imported ones. Navy = local-first (40%+), mid-blue = mixed, gray = import-driven (under 10%).") why = ("This splits 'local-first' markets - where homegrown originals win and local production pays off - from 'import' markets where " "licensed global hits dominate. It's a direct guide to where to commission local content vs. license international titles.") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1200, height=680), xaxis=lib.standard_xaxis("Share of viewing that is home-grown", tickformat=".0%"), yaxis=lib.standard_yaxis(showgrid=False)) lib.add_why_and_footer(fig, why, scope_footer(lib.assemble_footer("PC-D-03", ["CL-006 viewer panel (distinct viewers), not census", "home market proxied by where a title is most-watched"], freshness, ["PeerLogix country-viewing share (90-day)"], "established markets only; the proxy favors very large markets (a global hit most-watched in India counts as Indian)"))) lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1200, 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-D-03", intended_lesson_from_spec="Local vs imports by major market", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "moderate", "rules_applied": "home-grown share, major markets; proxy inflates very large markets"}, row_count=len(df), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)} def render_TI_D_04(reg: dict) -> dict: """Country Momentum Rotation - week-over-week viewer-share rank change.""" chart_id = "TI-D-04" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() # REBUILT (audit #11): the old source (metrics_country_share_90d_weekly_snapshots) still contains banned # countries (CN ranked #1) and mixes pre/post-dust-floor snapshots. Compare two snapshots of the REPAIRED # metrics_country_share_90d instead: latest date vs the earliest date within ~30 days - both post-repair, # so the endpoints are methodology-consistent. df = lib.run_query(conn, """ WITH d1 AS (SELECT MAX(file_date) AS d FROM metrics_country_share_90d), d0 AS (SELECT MIN(file_date) AS d FROM metrics_country_share_90d WHERE file_date >= (SELECT d FROM d1) - INTERVAL '30 days' AND file_date < (SELECT d FROM d1)) SELECT cs.file_date AS d, cs.country_code AS geo_code, SUM(cs.n_unique_viewers::numeric) AS total_viewers, RANK() OVER (PARTITION BY cs.file_date ORDER BY SUM(cs.n_unique_viewers::numeric) DESC) AS rk FROM metrics_country_share_90d cs WHERE cs.file_date IN ((SELECT d FROM d0), (SELECT d FROM d1)) AND cs.n_unique_viewers IS NOT NULL GROUP BY cs.file_date, cs.country_code; """) q_sec = time.time() - t0 conn.close() if df.empty or df["d"].nunique() < 2: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "Repaired metrics_country_share_90d has fewer than 2 snapshots within ~30 days - " "no methodology-consistent endpoint pair to compare yet") write_shim(output_dir, chart_id) return {"status": "needs_spec_review"} df["d"] = pd.to_datetime(df["d"]); df["rk"] = df["rk"].astype(int) BANNED_PROBE = {"CN", "KP", "IR"} if BANNED_PROBE & set(df["geo_code"].unique()): lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "Banned countries present in a chosen endpoint snapshot - endpoints are not " "methodology-consistent (repair incomplete for one of the dates)") write_shim(output_dir, chart_id) return {"status": "needs_spec_review"} dates = sorted(df["d"].unique()) d0, d1 = pd.Timestamp(dates[0]), pd.Timestamp(dates[-1]) gap_days = int((d1 - d0).days) gap_label = f"{gap_days // 7} weeks ago" if gap_days % 7 == 0 and gap_days >= 7 else f"{gap_days} days ago" # Slope chart: rank then vs rank now, for countries in current top 12 now = df[df["d"] == d1].set_index("geo_code")["rk"] then = df[df["d"] == d0].set_index("geo_code")["rk"] share_now = df[df["d"] == d1].set_index("geo_code")["total_viewers"].astype(float) share_now = share_now / share_now.sum() top_countries = now.sort_values().head(12).index.tolist() rows = [{"geo_code": c, "then": int(then.get(c, len(now))), "now": int(now.get(c, len(now))), "share_now": float(share_now.get(c, 0.0))} for c in top_countries if c in now.index] sl = pd.DataFrame(rows) sl["delta"] = sl["then"] - sl["now"] # positive = climbed sl["geo_code"] = sl["geo_code"].map(cn) # full country names for the layman # tie-aware share labels: a swap pair (the chart's headline event) must never PRINT the same # number - any countries whose 1-decimal shares collide re-print at 2 decimals (US 7.41% vs # Spain 7.40% is exactly WHY the ranks flipped) lab1 = [f"{v:.1%}" for v in sl["share_now"]] dup1 = {l for l in lab1 if lab1.count(l) > 1} sl["share_label"] = [f"{v:.2%}" if l in dup1 else l for v, l in zip(sl["share_now"], lab1)] fig = go.Figure() for _, r in sl.iterrows(): col = lib.GREEN if r["delta"] > 0 else (lib.RED if r["delta"] < 0 else lib.MUTED) fig.add_trace(go.Scatter(x=[0, 1], y=[r["then"], r["now"]], mode="lines+markers", line=dict(color=col, width=2.5), marker=dict(size=8, color=col), showlegend=False, hoverinfo="skip")) fig.add_annotation(x=-0.04, y=r["then"], text=f"{r['geo_code']} #{r['then']}", showarrow=False, xanchor="right", font=dict(size=10, color=lib.INK)) fig.add_annotation(x=1.04, y=r["now"], text=f"#{r['now']} {r['geo_code']} · {r['share_label']}", showarrow=False, xanchor="left", font=dict(size=10, color=lib.INK)) fig.update_yaxes(autorange="reversed", showgrid=False, zeroline=False, showticklabels=False, title="") fig.update_xaxes(showgrid=False, zeroline=False, tickvals=[0, 1], ticktext=[f"{d0.date()}
({gap_label})", f"{d1.date()}
(now)"], range=[-0.25, 1.3]) # Headline logic: never crown a sole "fastest riser" when the data shows a tie (audit: 3 countries tied at +1) climbers = sl[sl["delta"] > 0].sort_values("delta", ascending=False) pass_pairs = [] for _, c in climbers.iterrows(): passed = sl[(sl["then"] == c["now"]) & (sl["delta"] < 0)] if len(passed): pass_pairs.append(f"{c['geo_code']} passed {passed.iloc[0]['geo_code']}") if climbers.empty: title_lesson = f"Country audience rankings held steady over the last {gap_days} days" elif int(climbers["delta"].max()) == 1 and len(climbers) > 1: title_lesson = (f"Country rankings are sticky - just {len(climbers)} one-spot swaps among the top 12 " f"in {gap_days} days") elif int(climbers["delta"].max()) == 1: c = climbers.iloc[0] moved = f"{c['geo_code']} edged up one spot" if not pass_pairs else pass_pairs[0] title_lesson = f"Country rankings are sticky - {moved} was the only climb in {gap_days} days" else: c = climbers.iloc[0] title_lesson = f"{c['geo_code']} is the fastest-rising audience - up {int(c['delta'])} spots in the country rankings in {gap_days} days" what = (f"Each line is one country's rank by total streaming audience, {gap_label} (left) vs. now (right), " "with its current share of panel viewing after the name. Lines sloping up = a market climbing the rankings (green); down = slipping (red).") swaps_text = "; ".join(pass_pairs[:3]) if pass_pairs else "no markets traded places" why = (f"Shifts in national rankings flag where streaming is heating up or cooling - useful for timing market entry, ad spend, or local " f"content pushes. This window: {swaps_text}. (A {gap_days}-day move is a short-term read, not a long trend.)") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1100, height=680)) lib.add_why_and_footer(fig, why, scope_footer(lib.assemble_footer("PC-D-04", ["CL-006 viewer panel (distinct viewers), not census", f"CL-005 {gap_days}-day window (earliest consistent snapshot within ~30d)"], str(d1.date()), ["PeerLogix country-viewing share (90-day)"], "excluded countries filtered; trace viewing floored to zero; endpoints are overlapping 90-day windows, which damps swings"))) lib.save_csv(sl, output_dir); lib.export_chart(fig, output_dir, width=1100, 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-D-04", intended_lesson_from_spec="Country rank slope (rebuilt on repaired metrics_country_share_90d)", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "moderate", "rules_applied": f"2-point slope chart, {d0.date()} vs {d1.date()} ({gap_days}d)"}, row_count=len(df), freshness_date=str(d1.date()), query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)}