"""SOURCE render logic for TI-AN-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_AN_03(reg: dict) -> dict:
"""Where Anime Lives (monthly) - carriage vs lock-in across US streaming services: bar length =
share of the lane's weekly viewing (reach_7d x lane cred) available on each service, saturated
segment = the slice EXCLUSIVE to that service, muted = shared. Whole anime lane incl. films and
finished catalog (no recurrent rule - the Ghibli vault is movies, Evangelion is ended); the
per-(title,platform) flatrate/free/ads attribution IS the streaming gate. Within-lane
percentages only ship (founder display rule: nothing cross-comparable)."""
chart_id = "TI-AN-03"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
df = lib.run_query(conn, f"""
WITH d AS (SELECT MAX(file_date) AS d0 FROM title_metrics_daily WHERE geo_code = 'US'),
lane AS (
SELECT t.imdb_id, AVG(t.reach)::numeric AS reach_7d
FROM title_metrics_daily t, d
WHERE t.geo_code = 'US' AND t.reach > 0 AND t.file_date BETWEEN d.d0 - 6 AND d.d0
GROUP BY t.imdb_id
),
anime AS (
SELECT l.imdb_id, l.reach_7d, b.primary_title
FROM lane l JOIN categorization_imdb_title_basics b USING (imdb_id)
WHERE {_AN_CLASS_WHERE}
),
votes AS (
SELECT a.imdb_id,
COALESCE(NULLIF((SELECT MAX(r.num_votes) FROM categorization_imdb_ratings r
WHERE r.imdb_id = a.imdb_id), 0)::numeric,
42 * NULLIF((SELECT MAX(m.tmdb_votes) FROM categorization_metadata m
WHERE m.imdb_id = a.imdb_id), 0)::numeric) AS v
FROM anime a
),
plat AS ( -- per-(title, canonical platform) US streaming homes, fanout-deduped
SELECT DISTINCT pa.imdb_id, mpc.canonical
FROM categorization_platforms_available pa
JOIN metrics_platform_canonical mpc ON mpc.raw_name = pa.platforms_available
JOIN anime a ON a.imdb_id = pa.imdb_id
WHERE pa.country_code = 'US' AND pa.watch_type IN ('flatrate','free','ads')
)
SELECT a.imdb_id, a.primary_title, a.reach_7d, v.v, p.canonical, (SELECT d0 FROM d) AS d0
FROM anime a
LEFT JOIN votes v ON v.imdb_id = a.imdb_id
LEFT JOIN plat p ON p.imdb_id = a.imdb_id;
""")
dep = lib.run_query(conn, """
SELECT DISTINCT e.file_date, e.imdb_id, COALESCE(mpc.canonical, e.platform) AS canonical
FROM metrics_platform_events_daily e
LEFT JOIN metrics_platform_canonical mpc ON mpc.raw_name = e.platform
WHERE e.country_code = 'US' AND e.event_type = 'DEPARTURE'
AND e.watch_type IN ('flatrate','free','ads')
AND e.file_date >= (SELECT MAX(file_date) FROM title_metrics_daily
WHERE geo_code = 'US') - %(days)s;
""", {"days": AN_PLAT_DEPART_DAYS})
consumer = lib.run_query(conn, """SELECT canonical, BOOL_OR(is_consumer) AS is_consumer
FROM metrics_platform_canonical GROUP BY canonical;""")
ph = lib.run_query(conn, """SELECT degraded FROM metrics_panel_health
WHERE file_date = (SELECT MAX(file_date) FROM title_metrics_daily
WHERE geo_code = 'US');""")
panel_degraded = bool(ph["degraded"].iloc[0]) if len(ph) else False
q_sec = time.time() - t0
conn.close()
if df.empty:
lib.needs_spec_review(output_dir, chart_id, reg.get("spec_file", "_anime_section_mock/mock_platforms.md"),
reg.get("pc_id", "PC-AN-03"), "No anime-lane weekly data")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
n_blocked = df.loc[df["imdb_id"].isin(ANIME_SOURCE_BLOCKLIST), "imdb_id"].nunique()
df = df[~df["imdb_id"].isin(ANIME_SOURCE_BLOCKLIST)].copy() # content policy (see constant)
df["reach_7d"] = df["reach_7d"].astype(float)
df["v"] = pd.to_numeric(df["v"], errors="coerce")
df["w"] = df["reach_7d"] * df["v"] / (df["v"] + K_ANIME) # internal weight only - never ships
freshness = str(df["d0"].iloc[0])
titles = df.drop_duplicates("imdb_id")[["imdb_id", "primary_title", "w"]]
homes = df[df["canonical"].notna() & df["w"].notna()].copy()
if homes.empty:
lib.needs_spec_review(output_dir, chart_id, reg.get("spec_file", "_anime_section_mock/mock_platforms.md"),
reg.get("pc_id", "PC-AN-03"), "No US streaming homes resolved for the anime lane")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
n_plats = homes.groupby("imdb_id")["canonical"].nunique()
tw = titles.set_index("imdb_id")["w"]
lane_total = float(tw.reindex(list(n_plats.index)).sum()) # gated, weighted denominator
n_null_v_gated = int(df[df["canonical"].notna() & df["w"].isna()]["imdb_id"].nunique())
homes["excl"] = homes["imdb_id"].map(n_plats).eq(1)
agg = (homes.assign(excl_w=homes["w"].where(homes["excl"], 0.0))
.groupby("canonical", as_index=False)
.agg(n_titles=("imdb_id", "nunique"), carried_w=("w", "sum"), excl_w=("excl_w", "sum")))
agg["carried_pct"] = 100.0 * agg["carried_w"] / lane_total
agg["excl_pct"] = 100.0 * agg["excl_w"] / lane_total
agg["excl_of_own_pct"] = 100.0 * agg["excl_w"] / agg["carried_w"]
agg = agg.sort_values("carried_pct", ascending=False).reset_index(drop=True)
named = agg.head(AN_PLAT_BARS).copy()
named["label"] = named["canonical"]
named["is_rollup"] = False
# "Smaller services" rollup = one pseudo-platform: carried = lane share reachable on ANY of
# them; exclusive = lane share reachable ONLY through them (the group analogue of n_plats=1).
top_set = set(named["canonical"])
rest_pairs = homes[~homes["canonical"].isin(top_set)]
rest_ids = set(rest_pairs["imdb_id"])
only_rest = rest_ids - set(homes.loc[homes["canonical"].isin(top_set), "imdb_id"])
n_rest = int(agg["canonical"].nunique() - len(top_set))
roll = {"canonical": "(rollup)", "label": f"{n_rest} smaller services",
"n_titles": len(rest_ids), "carried_w": float(tw.reindex(list(rest_ids)).sum()),
"excl_w": float(tw.reindex(list(only_rest)).sum()), "is_rollup": True}
roll["carried_pct"] = 100.0 * roll["carried_w"] / lane_total
roll["excl_pct"] = 100.0 * roll["excl_w"] / lane_total
roll["excl_of_own_pct"] = (100.0 * roll["excl_w"] / roll["carried_w"]) if roll["carried_w"] else 0.0
# --- Rule-based annotation chips (at most one per bar; no-spotlight titles never named) ---
top10 = titles[titles["w"].notna()].nlargest(10, "w")
leader = named.iloc[0]
chips: dict[str, str] = {}
lead_set = set(homes.loc[homes["canonical"] == leader["canonical"], "imdb_id"])
n_lead_excl = int(sum(1 for i in top10["imdb_id"] if i in lead_set and n_plats.get(i, 0) == 1))
if float(leader["excl_of_own_pct"]) < 50.0: # carriage-without-control paradox
# chip names its own basis (the lane's 10 biggest titles this week - NOT the Anime 10
# board, which gates to series only) and the count ships in data.csv
chips[leader["canonical"]] = (f"only {n_lead_excl} of the lane's 10 biggest titles "
f"{'streams' if n_lead_excl == 1 else 'stream'} only here")
vault = named.iloc[1:]
vault = vault[(vault["excl_of_own_pct"] >= AN_PLAT_VAULT_MIN_OWN)
& (vault["carried_pct"] >= AN_PLAT_VAULT_MIN_CARRY)]
vault = vault.sort_values("excl_of_own_pct", ascending=False).head(1)
vault_row = vault.iloc[0] if len(vault) else None
if vault_row is not None:
vx = homes[(homes["canonical"] == vault_row["canonical"]) & homes["excl"]
& ~homes["imdb_id"].isin(ANIME_NO_SPOTLIGHT)].sort_values("w", ascending=False)
led_by = f", led by {short_title(str(vx.iloc[0]['primary_title']), 22)}" if len(vx) else ""
# Chip names the within-shelf basis ('of its own anime') so the reader does not have to estimate
# the exclusive ratio off this (often short) bar's geometry: the bar LENGTH is share-of-lane
# carriage, but this number is the proportion of THIS service's own carriage that streams only here.
chips[vault_row["canonical"]] = (f"{vault_row['excl_of_own_pct']:.0f}% of its own anime "
f"streams only here{led_by}")
dep_chip = None
if len(dep):
cur_pairs = set(zip(homes["imdb_id"], homes["canonical"]))
dd = dep[dep["canonical"].isin(top_set - set(chips))
& dep["imdb_id"].isin(set(top10["imdb_id"]))
& ~dep["imdb_id"].isin(ANIME_NO_SPOTLIGHT)].copy()
dd = dd[[(i, c) not in cur_pairs for i, c in zip(dd["imdb_id"], dd["canonical"])]]
dd = dd.merge(titles, on="imdb_id").sort_values("w", ascending=False)
if len(dd):
r = dd.iloc[0]
ts = pd.Timestamp(r["file_date"])
dep_chip = f"{short_title(str(r['primary_title']), 24)} left {ts.strftime('%b')} {ts.day}"
chips[r["canonical"]] = dep_chip
plot = pd.concat([pd.DataFrame([roll]), named.sort_values("carried_pct")], ignore_index=True)
excl_x = plot["excl_pct"].astype(float)
shared_x = (plot["carried_pct"] - plot["excl_pct"]).astype(float)
carried = plot["carried_pct"].astype(float)
# Color contract: NAVY = the leader ONLY. The leader service's exclusive slice carries the navy
# claim-accent; every other service's exclusive slice is BLUE; the shared stretch is PALE_BLUE.
# So the one navy block names the leader without any cross-comparable score.
excl_colors = [lib.NAVY if (str(c) == str(leader["canonical"])) else lib.BLUE
for c in plot["canonical"]]
fig = go.Figure()
fig.add_trace(go.Bar(
x=excl_x, y=plot["label"], orientation="h", marker=dict(color=excl_colors, line=dict(width=0)),
text=[f"{e:.0f}% exclusive" if e >= 12 else "" for e in excl_x],
textposition="inside", insidetextanchor="middle",
textfont=dict(size=10.5, color="#FFFFFF"), showlegend=False,
hovertemplate="%{y}
%{x:.1f}% of weekly US anime panel viewing streams only here",
))
fig.add_trace(go.Bar(
x=shared_x, y=plot["label"], orientation="h",
marker=dict(color=lib.PALE_BLUE, line=dict(width=0)),
text=[f"{c:.0f}%" for c in carried], textposition="outside",
textfont=dict(size=11, color=lib.INK), cliponaxis=False, showlegend=False,
customdata=np.stack([carried], axis=-1),
hovertemplate="%{y}
carries %{customdata[0]:.1f}% of weekly US anime panel viewing "
"in total; this stretch also streams elsewhere",
))
fig.update_layout(barmode="stack")
# Headroom past the LEADER so its outside carried-total label sits inside the plot field with clear
# space to its right, exactly like every shorter bar's label. At *1.2 the leader bar reached the
# axis max and its label was the only one crowded into the outer margin (a label-placement
# asymmetry a strict eye catches); *1.38 gives the longest bar genuine in-field headroom.
fig.update_xaxes(range=[0, float(carried.max()) * 1.38])
for _, r in plot.iterrows():
chip = chips.get(r["canonical"])
if not chip:
continue
if float(r["carried_pct"]) >= AN_PLAT_CHIP_INSIDE_PCT: # long bar: chip inside the muted stretch
fig.add_annotation(x=float(r["excl_pct"]) + (float(r["carried_pct"]) - float(r["excl_pct"])) / 2.0,
y=r["label"], yref="y", text=chip, showarrow=False,
xanchor="center", font=dict(size=10.5, color=lib.INK))
else: # short bar: chip after the value label
fig.add_annotation(x=float(r["carried_pct"]), y=r["label"], yref="y", text=chip,
showarrow=False, xanchor="left", xshift=46,
font=dict(size=10.5, color=lib.MUTED))
lead_name = str(leader["canonical"])
# denominator scoped honestly: shares are computed over the STREAMING-TRACKED slice of the
# lane (titles with a tracked US subscription/free service), not all anime on the US panel.
# the vault superlative is SCOPED to the major services drawn (data.csv legitimately contains
# tiny shelves at higher exclusive ratios, e.g. a 1-title service at 100%), and phrased on the
# metric's own viewing-weighted basis ('of the anime viewing it carries', never 'of its shelf',
# which invited a title-count reading)
# Vault superlative scoped to services that carry a MEANINGFUL share of the lane (the vault chip
# requires carried_pct >= AN_PLAT_VAULT_MIN_CARRY). This is stated on the face so the qualifier is
# self-justifying: a one-title shelf can be 100% exclusive but is not a vault, which is exactly why
# tiny shelves in the sidecar (e.g. a single-title service at 100%) are not the headline.
# "real share of the lane" is the vault-candidate gate (carried_pct >= AN_PLAT_VAULT_MIN_CARRY), which
# also happens to be the cutoff that yields the rendered named services (DB-verified: the 7th bar Hoopla
# carries 3.28% and the first off-board service carries 2.50%, so >= 3% draws exactly the named set).
# State the cutoff PLAINLY so "real share" is a defined number, not an undefined qualifier.
real_share_phrase = f"services carrying at least {AN_PLAT_VAULT_MIN_CARRY:.0f}% of US anime viewing"
if vault_row is not None and float(leader["excl_of_own_pct"]) < 50.0:
# The vault number is a WITHIN-SHELF ratio (of the service's OWN anime), not a share of the lane the
# bar length encodes - say 'of its own anime' so it is never read off the (short) bar's geometry. The
# '{real_share_phrase}' qualifier is load-bearing (tiny shelves in the sidecar run higher), so it sits
# INSIDE the superlative clause, not as a trailing aside a skimming reader can drop.
takeaway = (f"{lead_name} carries {leader['carried_pct']:.0f}% of the streaming-tracked anime watched on the US viewer panel, "
f"but among {real_share_phrase}, {vault_row['canonical']} runs the deepest vault: "
f"{vault_row['excl_of_own_pct']:.0f}% of its own anime streams nowhere else")
else:
takeaway = (f"{lead_name} carries {leader['carried_pct']:.0f}% of the streaming-tracked anime watched on the US viewer panel "
f"and, among {real_share_phrase}, keeps the largest share of its own anime viewing exclusive")
what = ("Each bar is the share of weekly US anime panel viewing available on that service (among titles with a "
"tracked US streaming service); the solid block is the slice that streams only there. "
"Bars overlap because most anime is on several services at once.")
why = ("Carriage is not control. The muted stretch of every bar is licensed anime a rival can also "
"carry, while the solid blocks are the moats that decide which subscription an anime fan keeps.")
cautions = [] # the bars-overlap disclosure lives in the subtitle (footer budget is 4 lines)
lib_named = sorted(str(c) for c in named["canonical"]
if not bool(consumer.set_index("canonical")["is_consumer"].get(c, True)))
if lib_named:
cautions.append(f"{', '.join(lib_named)}: a free library service")
if panel_degraded:
cautions.append("panel volume atypical on this date; shares use a 7-day basis")
sources = ["title_metrics_daily", "categorization_platforms_available",
"categorization_imdb_genres", "categorization_imdb_ratings"]
if dep_chip:
sources.append("PeerLogix platform arrival and departure feed")
footer = lib.assemble_footer(pc_id=reg.get("pc_id", "PC-AN-03"),
caution_labels=cautions, freshness_date=freshness, sources=sources,
extra=f"anime series and films on the US panel, past 7 days · subscription "
f"and free streaming only, never rentals · shares cover the {len(n_plats):,} of "
f"{titles['imdb_id'].nunique():,} lane titles with a tracked US streaming service "
f"· refreshed monthly · metrics decoded at METH-03")
fig.update_layout(**lib.layout_v2(takeaway, what, width=1180, height=640),
barmode="stack",
xaxis=lib.standard_xaxis("", showticklabels=False, showgrid=False),
yaxis=lib.standard_yaxis(showgrid=False))
# Legend disambiguates the TWO exclusive hues by WHOSE exclusive they are (the prior wording made
# both swatches read as the same 'exclusivity' concept): navy = the LEADER service's exclusive slice,
# blue = ANOTHER service's exclusive slice, pale = shared (also on other services).
lib.add_color_key(fig, f"■ {lead_name}'s exclusive slice "
f"■ another service's exclusive slice "
f"■ shared (also on other services)")
lib.add_why_and_footer(fig, why, footer)
roll_csv = {k: roll[k] for k in ("canonical", "n_titles", "carried_pct", "excl_pct",
"excl_of_own_pct", "is_rollup")}
roll_csv["rendered"] = True
out = pd.concat([agg.assign(is_rollup=False, rendered=agg["canonical"].isin(top_set)),
pd.DataFrame([roll_csv])], ignore_index=True)
out = out.rename(columns={"canonical": "platform", "carried_pct": "carried_share_pct",
"excl_pct": "exclusive_share_pct",
"excl_of_own_pct": "exclusive_pct_of_own_shelf"})
out = out[["platform", "n_titles", "carried_share_pct", "exclusive_share_pct",
"exclusive_pct_of_own_shelf", "is_rollup", "rendered"]]
# the on-chart chips' facts ship in the sidecar (count of the lane's 10 biggest titles that
# are exclusive to the leader; the vault's biggest exclusive title), so no chip number or
# name exists only on the rendered face
out["chip_note"] = out["platform"].map(chips).fillna("")
out["top10_lane_titles_exclusive_here"] = out["platform"].map(
lambda p: n_lead_excl if (p == leader["canonical"] and leader["canonical"] in chips) else None)
vault_led_by = (short_title(str(vx.iloc[0]["primary_title"]), 40)
if (vault_row is not None and len(vx)) else None)
out["vault_top_exclusive_title"] = out["platform"].map(
lambda p: vault_led_by if (vault_row is not None and p == vault_row["canonical"]) else None)
out["freshness_date"] = freshness
lib.save_csv(out, output_dir)
lib.export_chart(fig, output_dir, width=1180, height=640)
what_plain = re.sub(r"?b>", "", what)
lesson = {"reader_title": takeaway, "reader_subtitle": what_plain, "claim_strength": "strong",
"rules_applied": (f"anime class lane, ALL title types, no recurrent rule (catalog films belong); "
f"weight reach_7d x cred(K_anime={int(K_ANIME)}); per-(title,canonical) "
f"flatrate/free/ads attribution = the gate; shares of the gated weighted lane; "
f"top {AN_PLAT_BARS} platforms + rollup; NAVY accents the leader's exclusive slice "
f"(color contract: navy = leader only), BLUE = others' exclusive, PALE_BLUE = shared; "
f"chips rule-based (leader paradox / "
f"vault >= {AN_PLAT_VAULT_MIN_OWN:.0f}% of own shelf / top-10 departure "
f"within {AN_PLAT_DEPART_DAYS}d, streaming events only); blocklist removed "
f"{n_blocked}; within-lane percentages only ship")}
lib.write_lesson_design(output_dir, chart_id, f"# {chart_id}\n\n{takeaway}\n\n{what_plain}\n")
write_shim(output_dir, chart_id)
lib.write_manifest(output_dir, chart_id, "ok", reg.get("spec_file", "_anime_section_mock/mock_platforms.md"),
pc_id=reg.get("pc_id", "PC-AN-03"),
intended_lesson_from_spec="Monthly carriage-vs-lock-in platform map for the US anime lane",
rendered_lesson=lesson, row_count=len(plot), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec,
extra={"blocklist_removed": int(n_blocked), "chips": chips,
"lane_titles": int(titles["imdb_id"].nunique()),
"gated_titles": int(len(n_plats)), "null_cred_gated": n_null_v_gated,
"rollup_platforms": n_rest})
return {"status": "ok", "rows": len(plot)}
# The Season Report Card constants (quarterly; the most recently COMPLETED cour)
AN_RC_FLOOR_BPS = 2.0 # premiere-week measurement floor (mock-validated); below = listed, not graded
AN_RC_MIN_GRADED = 4 # fewer gradable seasons than this = no chart this quarter
AN_RC_EPS_RANGE = (8, 30) # full single-cour season shape