"""SOURCE render logic for TI-DP-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_DP_01(reg: dict) -> dict:
"""Catalog Gravity - REDESIGNED per agent-B as saturation/denominator diagnostic + absolute exclusive_di leaderboard."""
chart_id = "TI-DP-01"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
df = lib.run_query(conn, """
WITH latest AS (SELECT MAX(file_date) AS file_date FROM metrics_catalog_gravity_daily)
SELECT platform, country_code, total_di::numeric AS total_di,
exclusive_di::numeric AS exclusive_di, gravity_pct::numeric AS gravity_pct,
file_date AS freshness_date
FROM metrics_catalog_gravity_daily JOIN latest USING (file_date)
WHERE gravity_pct IS NOT NULL AND total_di IS NOT NULL;
""")
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 catalog gravity data")
write_shim(output_dir, chart_id)
return {"status": "needs_spec_review"}
freshness = str(df["freshness_date"].iloc[0])
for c in ["total_di", "exclusive_di", "gravity_pct"]:
df[c] = df[c].astype(float)
# Aggregate the confusing country-pairs up to PLATFORM level: how lock-in-heavy is each platform's demand?
# exclusivity rate = exclusive demand (only-here titles) / total demand on the platform (US market).
us = df[df["country_code"] == "US"] if (df["country_code"] == "US").any() else df
agg = us.groupby("platform").agg(excl=("exclusive_di", "sum"), tot=("total_di", "sum")).reset_index()
agg = agg[agg["tot"] > 0]
agg["excl_rate"] = agg["excl"] / agg["tot"]
# Eligibility floor LOW enough to keep every household-name service (a 40K demand floor silently
# dropped Apple TV+/Disney+/Paramount+/Peacock - the right tail of an exclusivity-RANKED chart).
DP01_DEMAND_FLOOR = 10000
agg = agg[agg["tot"] >= DP01_DEMAND_FLOOR]
# Live-TV bundles are excluded with an on-chart note: their low exclusivity is structural
# (they carry linear channels, not exclusive libraries), which would distort the SVOD comparison.
VMVPDS = {"YouTube TV", "Philo", "fuboTV", "Sling TV", "DIRECTV STREAM", "Hulu + Live TV"}
bundles = agg[agg["platform"].isin(VMVPDS)]
bundle_max = float(bundles["excl_rate"].max()) if len(bundles) else 0.0
agg = agg[~agg["platform"].isin(VMVPDS)]
agg = agg.nlargest(15, "excl_rate").sort_values("excl_rate", ascending=True)
if agg.empty:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No platform exclusivity data")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
colors = lib.leader_colors(agg["excl_rate"])
# UNIFORM one-decimal labels: mixing whole-% and 1dp labels (only where neighbours collided)
# read as inconsistency, and 1dp keeps near-tied bars distinguishable
disp = [f"{v * 100:.1f}%" for v in agg["excl_rate"]]
disp_map = dict(zip(agg["platform"], disp))
fig = go.Figure()
fig.add_trace(go.Bar(x=agg["excl_rate"], y=agg["platform"], orientation="h",
marker=dict(color=colors),
text=disp,
textposition="outside", textfont=dict(size=11, color=lib.INK), cliponaxis=False, showlegend=False,
hovertemplate="%{y}
%{x:.0%} of its catalog's total Demand Score is for titles we don't see on any rival service in the panel"))
fig.update_xaxes(range=[0, min(1.08, float(agg["excl_rate"].max()) * 1.18)], tickformat=".0%")
leader = agg.iloc[-1]
GENERAL = {"Netflix", "Prime Video", "Hulu", "Max", "Disney+", "Paramount+", "Peacock"}
gen = agg[agg["platform"].isin(GENERAL)].sort_values("excl_rate", ascending=False)
general_clause = ""
if len(gen) and gen.iloc[0]["platform"] != leader["platform"]:
g = gen.iloc[0]
named_min = float(g["excl_rate"])
# near-tie guard: don't crown one general service when the next sits within ~2 points
if len(gen) > 1 and float(g["excl_rate"]) - float(gen.iloc[1]["excl_rate"]) <= 0.025:
g2 = gen.iloc[1]
named_min = float(g2["excl_rate"])
general_clause = (f"{g['platform']} ({disp_map[g['platform']]}) and {g2['platform']} "
f"({disp_map[g2['platform']]}) lead the big general services in a near-tie")
else:
general_clause = f"{g['platform']} leads the big general services at {disp_map[g['platform']]}"
# SKIM-PROOFING the scoped claim: when a charted SPECIALIST service sits within the same
# near-tie tolerance just below the named general services, the callout says so itself
# rather than leaving the definition annotation to carry the nuance alone
close = agg[(~agg["platform"].isin(GENERAL)) & (agg["platform"] != leader["platform"])
& (agg["excl_rate"] < named_min) & (agg["excl_rate"] >= named_min - 0.025)]
if len(close):
c0 = close.sort_values("excl_rate", ascending=False).iloc[0]
general_clause += f", with specialist {c0['platform']} ({disp_map[c0['platform']]}) right behind"
# ONE-STORY HEADLINE (layman one-breath test): the leader claim only. The big-general-services
# near-tie + specialist nuance is a second story, so it moves to a bar-anchored callout on the
# chart face instead of riding the H1 as a third line.
if general_clause:
title_lesson = (f"{leader['platform']} is almost entirely locked-in - {disp_map[leader['platform']]} of its catalog's total Demand Score sits on titles "
f"we don't see anywhere else")
else:
title_lesson = f"{leader['platform']} has the most lock-in - {disp_map[leader['platform']]} of its catalog's total Demand Score sits on titles we don't see on any rival service"
what = ("For each streaming service, the share of its catalog's total Demand Score that comes from exclusive titles - content available only there. "
"High = strong subscription lock-in; low = mostly shared/licensed content also found on rivals.")
# founder terminology: never bare 'catalog demand' - the metric is the catalog's Demand Score
why = ("Exclusivity is what makes a subscription un-cancellable. A service whose catalog's Demand Score sits mostly on exclusives has real pricing power and "
"retention; one leaning on widely-shared titles competes on price. It's the clearest read on each service's moat.")
# no x-axis title: the subtitle already defines the measure, and the title row is needed for the side note
fig.update_layout(**lib.layout_v2(title_lesson, what, width=1200, height=760),
xaxis=lib.standard_xaxis("", tickformat=".0%"),
yaxis=lib.standard_yaxis(showgrid=False))
# the second story (general-services near-tie + specialist) rides a callout anchored to the top
# general service's own bar, where the eye lands on those rows - not the H1
if general_clause:
g_top = gen.iloc[0]
fig.add_annotation(x=float(g_top["excl_rate"]), y=g_top["platform"],
xanchor="left", xshift=58, showarrow=False, align="left",
text=general_clause.replace(", with ", ",
with "),
font=dict(size=10, color=lib.INK), bgcolor="rgba(255,255,255,0.88)")
# the 'big general services' definition lives HERE (a tight on-chart surface), not as a third
# footer line - the footer holds at trust + Sources; printed only when the callout uses the term
general_def = ("; 'big general services' = " + ", ".join(sorted(GENERAL))
if "general services" in general_clause else "")
fig.add_annotation(text=(f"Live-TV bundles (YouTube TV, Philo, fuboTV) excluded - they carry channels, not exclusive "
f"libraries (all under {max(1, int(bundle_max * 100) + 1)}%){general_def}"),
xref="paper", yref="paper", x=0.0, y=-0.055, xanchor="left", yanchor="top",
showarrow=False, align="left", font=dict(size=9.5, color=lib.MUTED))
lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-DP-03",
["exclusivity from observed co-availability"], freshness,
["metrics_catalog_gravity_daily"],
f"US market · top 15 by exclusivity among services whose catalog's combined daily Demand Score is {DP01_DEMAND_FLOOR:,}+"))
# sidecar carries the EXCLUDED bundle rows too, so the 'all under X%' claim is verifiable from data.csv
csv_out = pd.concat([agg.assign(row_type="drawn"),
bundles.assign(row_type="excluded_live_tv_bundle")], ignore_index=True)
lib.save_csv(csv_out, output_dir); lib.export_chart(fig, output_dir, width=1200, height=760)
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-DP-03",
intended_lesson_from_spec="Platform exclusivity / lock-in (redesigned from country-pair gravity)",
rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what,
"claim_strength": "strong",
"rules_applied": f"exclusive share per platform; demand floor {DP01_DEMAND_FLOOR}; live-TV bundles excluded; top 15 by exclusivity"},
row_count=len(agg), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(agg)}
# ===========================================================================
# SECTION 04 - Geography & Local Demand (13 charts)
# ===========================================================================