"""SOURCE render logic for TI-C-06 (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_C_06(reg: dict) -> dict:
"""Platform Demand Rotation Bump Chart - parent ranks over weekly snapshots."""
chart_id = "TI-C-06"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
# Weekly samples of credit-normalized audience size by parent, zoomed out to 18 weekly snapshots.
# Basis = raw reach (distinct panel viewers): demand_score only exists for the LATEST snapshot, so a
# history series cannot use it; reach is the sanctioned raw audience-size basis, and because ranks are
# computed WITHIN each week, panel-size swings cancel (L-046-safe). di is momentum-weighted - not used.
df = lib.run_query(conn, """
WITH weekly_dates AS (
SELECT (SELECT MAX(file_date) FROM title_metrics_daily) - (i * INTERVAL '7 days') AS file_date
FROM generate_series(0, 17) AS i
),
latest_p AS (SELECT MAX(snapshot_date) AS snapshot_date FROM categorization_title_x_platform),
parents AS (
SELECT DISTINCT imdb_id, REPLACE(btrim(parent_company), '_', ' / ') AS parent_company
FROM categorization_title_x_platform JOIN latest_p USING (snapshot_date)
WHERE parent_company IS NOT NULL AND btrim(parent_company) <> ''
),
counts AS (SELECT imdb_id, COUNT(*) AS pc FROM parents GROUP BY imdb_id),
joined AS (
SELECT w.file_date::date AS d, p.parent_company, SUM(t.reach::numeric / NULLIF(c.pc, 0)) AS credit_demand
FROM weekly_dates w
JOIN title_metrics_daily t ON t.file_date = w.file_date::date
JOIN parents p USING (imdb_id) JOIN counts c USING (imdb_id)
WHERE t.reach IS NOT NULL AND t.geo_code='US'
GROUP BY w.file_date, p.parent_company
)
SELECT d, parent_company, credit_demand,
RANK() OVER (PARTITION BY d ORDER BY credit_demand DESC) AS week_rank
FROM joined;
""")
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 weekly parent data")
write_shim(output_dir, chart_id)
return {"status": "needs_spec_review"}
df["d"] = pd.to_datetime(df["d"])
df["week_rank"] = df["week_rank"].astype(int)
df["credit_demand"] = df["credit_demand"].astype(float)
# Keep parents that were ever in top 12
top_parents = df[df["week_rank"] <= 12]["parent_company"].unique().tolist()
plot_df = df[df["parent_company"].isin(top_parents)].copy()
plot_df = plot_df.sort_values("d")
n_weeks = plot_df["d"].nunique()
last_d = plot_df["d"].max()
fig = go.Figure()
# Color semantics respected: NAVY = the current #1 only; other top-5 parents get NON-semantic
# identity hues drawn strictly from the blue/purple/slate/grey family (no green/red/amber, which
# the house contract reserves for verdicts/chips - and nothing red- or green-ADJACENT either: the
# old maroon/brown entries read as a falling verdict on a slipping company); the inert bottom
# half is muted grey.
sat_palette = ["#6273A8", "#7E69A8", "#5A9FB0", "#46606E", "#5A78B8", "#A08CC0", "#717D8F", "#9097A3"]
latest_rank = plot_df[plot_df["d"] == last_d].set_index("parent_company")["week_rank"].to_dict()
min_rank = plot_df.groupby("parent_company")["week_rank"].min()
sat_i = 0
for parent in sorted(top_parents[:12], key=lambda p: latest_rank.get(p, 99)):
sub = plot_df[plot_df["parent_company"] == parent].sort_values("d")
if sub.empty: continue
is_top = int(min_rank.get(parent, 99)) <= 5
if int(latest_rank.get(parent, 99)) == 1:
col = lib.NAVY
elif is_top:
col = sat_palette[sat_i % len(sat_palette)]; sat_i += 1
else:
col = lib.MUTED
fig.add_trace(go.Scatter(
x=sub["d"], y=sub["week_rank"], mode="lines+markers+text" if is_top else "lines+text",
name=parent, line=dict(color=col, width=2.5 if is_top else 1.5),
marker=dict(size=6),
text=[parent if d == last_d else "" for d in sub["d"]],
textposition="middle right", textfont=dict(size=11 if is_top else 10, color=col),
cliponaxis=False, # labels live in the right margin; default clipping cut them mid-word
hovertemplate=f"{parent}
Week of %{{x|%b %d}}
Rank #%{{y}}",
))
# ONE metric gloss everywhere, in plain words (the lib lint flags 'credit-split' as jargon):
# the audience basis divides a shared title's viewers between owners - 'panel audience' alone
# implied a distinct-viewer count
fig.update_yaxes(autorange="reversed", title_text="Rank among parent companies (1 = biggest weekly audience; shared titles' viewers divided between owners)", gridcolor=lib.GRID, dtick=1)
fig.update_xaxes(title_text="", gridcolor=lib.GRID)
# name the churn honestly: SET stability and ORDER stability are different claims - a #2/#3
# swap inside a stable podium must not be described as 'the same 3 hold the podium throughout'
top3_ordered = (plot_df[plot_df["week_rank"] <= 3].sort_values(["d", "week_rank"])
.groupby("d")["parent_company"].apply(tuple))
podium_order_stable = top3_ordered.nunique() == 1
top3_stable = top3_ordered.apply(frozenset).nunique() == 1
if podium_order_stable:
mid_text = "the only real churn: the mid-pack trades places; the same 3 companies hold the podium in the same order throughout"
elif top3_stable:
mid_text = "the same 3 companies keep the podium but swap places; the mid-pack churns below them"
else:
mid_text = "the mid-pack trades places, and the podium itself changes hands"
mid_date = sorted(plot_df["d"].unique())[n_weeks // 2]
fig.add_annotation(x=mid_date, y=5.5, text=mid_text, showarrow=False,
font=dict(size=10, color=lib.MUTED), bgcolor="rgba(255,255,255,0.8)")
rank_changes = {}
for parent in top_parents:
sub = plot_df[plot_df["parent_company"] == parent].sort_values("d")
if len(sub) >= 2:
rank_changes[parent] = int(sub["week_rank"].iloc[0]) - int(sub["week_rank"].iloc[-1])
biggest_climber = max(rank_changes, key=rank_changes.get) if rank_changes else None
climber_delta = rank_changes.get(biggest_climber, 0)
leader_now = plot_df[plot_df["d"] == last_d].sort_values("week_rank").iloc[0]["parent_company"]
# Count the weeks the current leader ACTUALLY held #1 (the old headline asserted "held #1 across N weeks"
# from the last week alone - now phrased from the data).
weekly_leaders = plot_df[plot_df["week_rank"] == 1].drop_duplicates("d")
weeks_led = int((weekly_leaders["parent_company"] == leader_now).sum())
if weeks_led >= n_weeks:
lead_phrase = f"{leader_now} has held #1 in every one of the last {n_weeks} weeks"
else:
lead_phrase = f"{leader_now} led {weeks_led} of the last {n_weeks} weeks"
max_abs_move = max((abs(v) for v in rank_changes.values()), default=0)
if biggest_climber and climber_delta >= 2:
title_lesson = f"The pecking order barely moves: {lead_phrase}; biggest climber is {biggest_climber} (+{climber_delta})"
elif max_abs_move <= 1:
title_lesson = f"The pecking order barely moves: {lead_phrase}; nobody moved more than one spot"
else:
title_lesson = f"The pecking order barely moves: {lead_phrase}"
what = (f"Each line = one parent company's weekly rank by audience: each title's panel viewers are divided between the companies carrying it, "
f"then summed across the company's entire catalog, not just the top-1,000 titles (which is why the order differs from the top-1,000 boards). "
f"The top line = #1 (the biggest weekly audience on that basis); {n_weeks} weekly snapshots.")
why = ("This is the parent-company view, not the single-app view: a company's audience is summed across every service it runs, "
"so a parent with several strong apps can outrank the single biggest service. Stable ranks mean the giants' grip on attention is durable, not fluid.")
footer = lib.assemble_footer(pc_id="PC-C-06",
caution_labels=["any title on the company's services, original or licensed"],
freshness_date=str(last_d.date()),
sources=["title_metrics_daily", "categorization_title_x_platform"],
extra="full-catalog audience basis (broader universe than the top-1,000 charts)")
fig.update_layout(**lib.layout_v2(title_lesson, what, width=1280, height=720),
showlegend=False)
fig.update_layout(margin_r=215) # room for the full right-edge parent labels (Sony / Crunchyroll)
lib.add_why_and_footer(fig, why, footer)
lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1280, 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-C-06",
intended_lesson_from_spec="Parent rank rotation over time (parent POV, zoomed to 18 weeks)",
rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what,
"claim_strength": "strong", "rules_applied": f"{n_weeks}-week bump"},
row_count=len(df), freshness_date=str(last_d.date()),
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}