"""SOURCE render logic for TI-Other-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_Other_06(reg: dict) -> dict:
"""Series Status Demand Snapshot - TV status x mean DI."""
chart_id = "TI-Other-06"
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 title_metrics_daily)
SELECT v.status, AVG(t.demand_score::numeric) AS mean_demand,
percentile_cont(0.5) WITHIN GROUP (ORDER BY t.demand_score::numeric) AS med_demand,
COUNT(DISTINCT t.imdb_id) AS titles,
(SELECT file_date FROM latest) AS file_date
FROM categorization_tv_details v
JOIN title_metrics_daily t USING (imdb_id) JOIN latest l USING (file_date)
WHERE v.status IS NOT NULL AND t.demand_score IS NOT NULL AND t.geo_code='US'
GROUP BY v.status HAVING COUNT(DISTINCT t.imdb_id) >= 20
ORDER BY mean_demand DESC;
""")
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 series status data")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
df["mean_demand"] = df["mean_demand"].astype(float); df["med_demand"] = df["med_demand"].astype(float)
df["titles"] = df["titles"].astype(int)
freshness = str(df["file_date"].iloc[0])
df = df.sort_values("mean_demand", ascending=True)
df["status"] = df["status"].str.title()
# plain-language category labels on the reader surface ('Returning Series' is TMDb status
# jargon and mismatched the title's own 'still-running shows'); raw status keys stay in the
# logic and ship in data.csv alongside the plain label
STATUS_PLAIN = {"Returning Series": "Still running", "In Production": "In production",
"Post Production": "In post-production"}
df["label"] = df["status"].map(lambda s: STATUS_PLAIN.get(s, s))
fig = go.Figure()
# bar-end labels print ONE DECIMAL: whole-number rounding (13 vs 7) implied a 1.9x gap beside a
# ~2.0x headline; 13.3 vs 6.6 reconciles with both the headline multiple and data.csv
fig.add_trace(go.Bar(x=df["mean_demand"], y=df["label"], orientation="h",
marker=dict(color=lib.NAVY),
text=[f" {v:,.1f} · {t:,} series" for v, t in zip(df["mean_demand"], df["titles"])],
textposition="outside", textfont=dict(size=10, color=lib.MUTED), cliponaxis=False, showlegend=False,
customdata=df[["med_demand", "titles"]],
hovertemplate="%{y}
Demand Score per series %{x:,.1f} (typical/median series: %{customdata[0]:,.2f})"))
# 1.16 pad = just enough room for the outside ' value · n series' label; the old 1.3 left a
# dead band beyond the longest bar
fig.update_xaxes(range=[0, float(df["mean_demand"].max()) * 1.16])
# the median is VISIBLE on the canvas (white tick + value on each bar): the caveat's evidence
# used to live only on hover, which a static-PNG reader never gets
fig.add_trace(go.Scatter(x=df["med_demand"], y=df["label"], mode="markers+text",
marker=dict(symbol="line-ns", size=15, color="white",
line=dict(width=2.5, color="white")),
text=[f" typical (median) series: {v:,.1f}" for v in df["med_demand"]],
textposition="middle right", textfont=dict(size=9.5, color="white"),
cliponaxis=False, showlegend=False,
hovertemplate="%{y}
typical (median) series: %{x:,.2f}"))
# ratios computed from the plotted per-series averages
g = df.set_index("status")
r_end = (g.loc["Returning Series", "mean_demand"] / g.loc["Ended", "mean_demand"]
if {"Returning Series", "Ended"} <= set(g.index) and g.loc["Ended", "mean_demand"] > 0 else 0)
r_can = (g.loc["Returning Series", "mean_demand"] / g.loc["Canceled", "mean_demand"]
if {"Returning Series", "Canceled"} <= set(g.index) and g.loc["Canceled", "mean_demand"] > 0 else 0)
fmt_r = lambda r: f"{r:.0f}" if r >= 3.5 else f"{r:.1f}"
# founder terminology on every surface: 'Demand Score' / 'attention', never bare 'demand'
if r_end > 0 and r_can > 0:
title_lesson = (f"Averaged over every tracked series, still-running shows carry ~{fmt_r(r_end)}x the Demand Score of ended ones - "
f"a gap driven by today's hits, not the typical show")
else:
top_status = df.iloc[-1]
title_lesson = f"Averaged over every tracked series, {top_status['label']} series carry the highest average Demand Score - a hit-driven gap"
what = ("Demand Score per series = each status group's total Demand Score spread across all its tracked series, so current hits weigh "
"heavily. The typical (median) series in every bucket is far smaller - the white tick near zero on each bar.")
med_invert = ({"Returning Series", "Canceled"} <= set(g.index)
and g.loc["Canceled", "med_demand"] > g.loc["Returning Series", "med_demand"])
# cross-reference NAMES the companion chart; its ref id lives in the footer (the figure lint
# bans PC ids outside footer provenance)
why = ("Part of the gap is freshness - running shows are newer on average (see the release-age cohort chart) - and most of it is hit-driven. "
+ ("In fact the median canceled series out-draws the median returning one (cancellation culls a catalog down to survivors "
"people still watch). " if med_invert else "")
+ "Read it as where the attention mass sits, not as the typical show's life-cycle.")
fig.update_layout(**lib.layout_v2(title_lesson, what, width=1150, height=550),
xaxis=lib.standard_xaxis("Demand Score per series (group total spread across its series)", tickformat=",d"),
yaxis=lib.standard_yaxis(showgrid=False))
# Sources humanized (the raw categorization_tv_details table name must never reach the footer)
lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-Other-06",
["CL-006 viewer panel (distinct viewers), not census"], freshness,
["title_metrics_daily", "TMDb series-status catalog"],
"≥20 series per status; latest snapshot · release-age companion: ref PC-Other-05"))
# csv: qualified metric headers + both the plain label and the raw TMDb status key
lib.save_csv(df.rename(columns={"status": "status_tmdb", "label": "status_plain",
"mean_demand": "mean_demand_score", "med_demand": "median_demand_score"}),
output_dir)
lib.export_chart(fig, output_dir, width=1150, height=520)
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-Other-06",
intended_lesson_from_spec="Series status demand (hit-driven gap, median disclosed)",
rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what,
"claim_strength": "moderate", "rules_applied": "TV status grouping; mean plotted, median on hover; hit-driven framing"},
row_count=len(df), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}
# ===========================================================================
# SECTION 10 - Demand-to-Spend (2 charts)
# ===========================================================================