"""SOURCE render logic for TI-I-08 (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_I_08(reg: dict) -> dict:
"""How Old Is What America Watches - the latest CLEAN panel week's share of total weekly Demand Score
by premiere-era band, as ONE horizontal 100%-stacked tape (no time axis). De-skewed lane (demand_score);
within-week shares cancel panel-size swings (L-046 safe) and degraded panel days are excluded. Reworked
from a 13-week stacked area to a single-week snapshot (founder 2026-06-19, thread fe8859d9): the vintage
mix is empirically static month to month (every band moves <=1.5pt Mar-Jun 2026), so the time axis carried
no signal yet was the sole surface that had to whiten/label 'not measured' weeks - a panel-internals leak.
Removing the time axis removes the leak by construction (no omitted weeks to disclose)."""
chart_id = "TI-I-08"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
# ANCHOR + 7-clean-day set: the latest clean panel day, plus the six prior clean days. No 13-window
# machinery, no wk_idx - a single clean week is all the snapshot needs. Degraded days are excluded
# silently (no 'not measured' disclosure: just a clean week of data).
df = lib.run_query(conn, """
WITH anchor AS (
SELECT MAX(file_date) AS fd FROM metrics_panel_health WHERE degraded = false
),
days AS (
SELECT file_date FROM metrics_panel_health
WHERE degraded = false AND file_date <= (SELECT fd FROM anchor)
ORDER BY file_date DESC LIMIT 7
)
SELECT CASE WHEN tb.start_year IS NULL OR tb.start_year = 0 THEN 'unknown'
WHEN EXTRACT(YEAR FROM d.file_date) - tb.start_year <= 1 THEN 'b0'
WHEN EXTRACT(YEAR FROM d.file_date) - tb.start_year <= 4 THEN 'b1'
WHEN EXTRACT(YEAR FROM d.file_date) - tb.start_year <= 9 THEN 'b2'
WHEN EXTRACT(YEAR FROM d.file_date) - tb.start_year <= 19 THEN 'b3'
ELSE 'b4' END AS band,
MIN(d.file_date) AS wk_start, MAX(d.file_date) AS wk_end,
COUNT(DISTINCT d.file_date) AS days_used,
SUM(t.demand_score::numeric) AS band_score
FROM title_metrics_daily t
JOIN days d USING (file_date)
LEFT JOIN categorization_imdb_title_basics tb USING (imdb_id)
WHERE t.geo_code = 'US' AND t.demand_score IS NOT NULL
GROUP BY 1;
""")
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 vintage-mix rows")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
df["band_score"] = df["band_score"].astype(float)
days_used = int(df["days_used"].max())
# Guard: a full clean week is required (>=5 of 7 clean days). Below that, do NOT fabricate a week -
# mark for spec review and shim out (panel-safe: never invent a snapshot from a thin day-base).
if days_used < 5:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"],
"Fewer than 5 clean panel days available - no full clean week to snapshot")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
wk_start = str(df["wk_start"].min())
wk_end = str(df["wk_end"].max())
freshness = wk_end # data-as-of = MAX(clean file_date), never render time
total_all = float(df["band_score"].sum())
# 'unknown' band: if it holds >=0.1% of the week's weight, warn and DROP it before re-normalizing the
# five displayed bands to sum to 100 (live unk = 0.00%, so it never shows). Re-normalizing on the kept
# bands keeps the 100% tape honest after any drop.
unk_pct = 100.0 * df.loc[df["band"] == "unknown", "band_score"].sum() / max(total_all, 1e-9)
if unk_pct >= 0.1:
lib.add_warning(f"unknown-premiere-year titles hold {unk_pct:.2f}% of weight (excluded)")
disp = df[df["band"] != "unknown"].copy()
kept_total = float(disp["band_score"].sum())
score_by = {row["band"]: float(row["band_score"]) for _, row in disp.iterrows()}
share_by = {b: 100.0 * score_by.get(b, 0.0) / max(kept_total, 1e-9) for b in ("b0", "b1", "b2", "b3", "b4")}
yr = int(freshness[:4])
yy = lambda y: f"{y % 100:02d}"
# Bands ordered OLDEST -> NEWEST so the eye reads "archive first". Fills = the sanctioned monochrome
# SEQUENTIAL ORDINAL ramp (lib contract note, blessed 2026-06-12): darkest = oldest pole, lightest =
# newest. NAVY here is the ordinal-oldest pole of a sequential ramp, explicitly sanctioned for ordered
# age cohorts - NOT a per-bar leader highlight. No green/red/amber anywhere on this chart. txtcol keeps
# the inside-label legible against each fill (dark fills -> white text, pale fills -> ink text).
BANDS = [("b4", f"{yr - 20} or earlier", lib.SEQ_RAMP[0], "#FFFFFF"),
("b3", f"{yr - 19}-{yy(yr - 10)}", lib.SEQ_RAMP[1], "#FFFFFF"),
("b2", f"{yr - 9}-{yy(yr - 5)}", lib.SEQ_RAMP[2], "#FFFFFF"),
("b1", f"{yr - 4}-{yy(yr - 2)}", lib.SEQ_RAMP[3], lib.INK),
("b0", f"{yr - 1}-{yy(yr)}", lib.SEQ_RAMP[4], lib.INK)]
# LAYOUT REBALANCE (I-08 2026-06-19): the redundant chip that used to sit in the lower white space is
# gone (it just restated the headline; Tufte data-ink). With nothing below it, the tape is now CENTRED
# in the plot and made TALLER so it fills the frame intentionally instead of floating at the top over a
# band of dead white space. The plot height is also trimmed below so the chart reads as a deliberate
# single-tape graphic, not an empty canvas with a bar at the top.
BAR_Y, BAR_H = 0.50, 0.80
fig = go.Figure()
for key, label, col, txtcol in BANDS:
v = float(share_by[key])
# inside label: band name + percentage at 1dp; suppress the in-bar text for a sliver under ~6%
# where it could not physically fit, but every live band clears that (min b1 = 12.4%).
txt = f"{label}
{v:.1f}%" if v >= 6 else (f"{v:.1f}%" if v >= 3 else "")
fig.add_trace(go.Bar(
y=[BAR_Y], x=[v], orientation="h", width=BAR_H, name=f"premiered {label}",
marker=dict(color=col, line=dict(width=0.8, color="white")),
text=[txt], textposition="inside", insidetextanchor="middle", textangle=0,
textfont=dict(size=12, color=txtcol), showlegend=False,
hovertemplate=f"premiered {label}
%{{x:.1f}}% of this week's attention"))
b4_pct, b0_pct = float(share_by["b4"]), float(share_by["b0"])
# five = b2+b3+b4 = everything that premiered (yr-5) or earlier; the year cutoff is exactly the band
# math (the 2017-21 band holds late-2021 titles that are calendar-year 5 but not yet five full years
# old, so naming the cutoff YEAR is unfaultable where 'five or more years old' could be faulted).
five = float(share_by["b2"] + share_by["b3"] + share_by["b4"])
# Rank the bands so the chip + headline name the two LARGEST single bands directly (no claim that the
# newest band is the smallest - it is often the runner-up, a non-monotonic shape a careful reader sees).
band_pct = {"b4": b4_pct, "b3": float(share_by["b3"]), "b2": float(share_by["b2"]),
"b1": float(share_by["b1"]), "b0": b0_pct}
band_year_label = {"b4": f"{yr - 20} or earlier", "b3": f"{yr - 19}-{yy(yr - 10)}",
"b2": f"{yr - 9}-{yy(yr - 5)}", "b1": f"{yr - 4}-{yy(yr - 2)}",
"b0": f"{yr - 1}-{yy(yr)}"}
BAND_ORDER = {"b4": 0, "b3": 1, "b2": 2, "b1": 3, "b0": 4} # oldest -> newest, for chart-order phrasing
ranked_bands = sorted(band_pct, key=lambda k: band_pct[k], reverse=True)
top1, top2 = ranked_bands[0], ranked_bands[1]
# Does the NEWEST two-year cohort (b0) sit at/near the top of the band ranking? It is often the
# runner-up or a near-tie for it (a non-monotonic shape a careful reader sees).
b0_is_top2 = (top2 == "b0")
# CLAIM-ACCURACY FIX (I-08 2026-06-19): the old headline called the newest band "a close second"
# whenever it ROUNDED to the runner-up's percent. But by VALUE the runner-up band can edge the newest
# band (this week 2007-16 at 20.91% vs 2025-26 at 20.81%), so the newest is actually ~3rd, effectively
# TIED for second. Crowning it "a close second" mis-ranked two near-equal bands. New rule: when the
# newest band and the true runner-up band round to the SAME whole percent but the newest is NOT
# literally the runner-up, treat it as a TIE and say both "each take about " rather than
# asserting a clean second place for either. b0_ties_top2 = a genuine near-tie the headline must not
# mis-rank; b0_clear_second = the newest band really is the runner-up (state it as such, honestly).
b0_ties_top2 = (not b0_is_top2) and (round(b0_pct) == round(band_pct[top2]))
b0_clear_second = b0_is_top2
# Approximate-fraction word for a percentage, so a near-tie can be stated as "about a fifth" (the
# plain-English read the bars give) instead of two decimals the eye treats as equal. Only the common
# whole fractions are named; anything off-grid falls back to "about N%". Rule-based, no hardcoded band.
def _approx_fraction(pct: float) -> str:
for denom, word in ((4, "a quarter"), (5, "a fifth"), (3, "a third"), (6, "a sixth"), (10, "a tenth")):
if abs(pct - 100.0 / denom) <= 2.0:
return f"about {word}"
return f"about {pct:.0f}%"
# The two near-tied bands, named oldest-first so the phrase reads in chart order; used by both the
# headline tie-branch below. (top2 is the mid runner-up band; b0 is the newest cohort.)
tie_low, tie_high = sorted((top2, "b0"), key=lambda k: BAND_ORDER[k])
# PRACTICAL human title (takeaway, rule-based from live values so it never drifts from the bars).
# LEADS with the single biggest band (each band is drawn on the chart, so the headline reconstructs
# from individual visual elements with no mental addition). It does NOT assert "most is old" as a
# single-segment claim (that is the b2+b3+b4 sum, carried in WHY). lead band named from the live
# ranking (top1), so the headline can never mis-state which slice is biggest; 'oldest' qualifier added
# only when the biggest band IS the oldest (b4), the stable case.
lead_band_phrase = (f"The oldest titles ({band_year_label[top1]})" if top1 == "b4"
else f"Titles that premiered {band_year_label[top1]}")
if top1 == "b0":
# degenerate case: the newest cohort itself is the biggest slice (would flip the whole thesis);
# state it plainly rather than forcing the old-vs-new frame.
takeaway = (f"The newest {band_year_label['b0']} releases are the single biggest slice of US panel viewing "
f"this week at {b0_pct:.0f}%")
elif b0_ties_top2:
# NEAR-TIE for second between the mid runner-up band and the newest cohort (they round to the same
# percent and the newest is NOT literally ahead). Do NOT crown either as "the close second" - that
# mis-ranks two bands the eye reads as equal. State both as each taking about the same share, named
# in chart order (oldest of the pair first), with the plain-English fraction the bars show.
frac = _approx_fraction((b0_pct + band_pct[top2]) / 2.0)
takeaway = (f"{lead_band_phrase} are the single biggest slice of US panel viewing this week at "
f"{band_pct[top1]:.0f}%, with the {band_year_label[tie_low]} and newest "
f"{band_year_label[tie_high]} titles each taking {frac}")
elif b0_clear_second:
takeaway = (f"{lead_band_phrase} are the single biggest slice of US panel viewing this week at "
f"{band_pct[top1]:.0f}%, but the newest {yr - 1}-{yy(yr)} releases run a clear second at {b0_pct:.0f}%")
else:
takeaway = (f"{lead_band_phrase} are the single biggest slice of US panel viewing this week at "
f"{band_pct[top1]:.0f}%, ahead of every newer band including the {yr - 1}-{yy(yr)} cohort at {b0_pct:.0f}%")
what = ("This is one week's snapshot: each segment is a premiere-era band's share of the US viewer panel's total "
"weekly Demand Score. The newest band counts titles premiered in the last two calendar years.")
# WHY carries the aggregate (most viewing is years-old): stated as a rounded total so the reader is not
# asked to add three on-chart bands to verify it - the number lives here, named as a sum of the older bands.
why = (f"The premiere calendar gets the headlines, but the archive holds the audience: titles {yr - 5} or earlier "
f"add up to {five:.0f}% of the week's viewing, which is what a deep back catalog is actually worth.")
footer = drop_census(lib.assemble_footer("PC-I-08",
caution_labels=[
"vintage = premiere year, new seasons of returning series count by their original premiere",
"excludes TV movies, shorts and specials"],
freshness_date=freshness,
sources=["title_metrics_daily", "categorization_imdb_title_basics"],
extra=("Demand Score basis (panel viewing, weighted to reflect the broad US streaming "
"audience). One week.")))
# Height trimmed (was 560) now the lower-chip white space is reclaimed: a centred, taller single tape
# (BAR_H raised) fills the plot, so the chart no longer shows a dead band of empty canvas below the bar.
# Height kept high enough that the two-line headline never clips against the top edge.
lo_i08 = lib.layout_v2(takeaway, what, width=1240, height=500)
# Single 100%-stacked tape: every visible slice carries its own labeled percent, so a 0-100% axis would
# only re-print numbers the in-bar labels already give. Drop the x-axis ink and let the labeled slices be
# the scale. The y axis is a fixed 0..1 frame (no ticks): the tape rides the upper band and the chip the
# lower white space, both inside the plot.
fig.update_layout(**lo_i08, barmode="stack",
xaxis=lib.standard_xaxis("", range=[0, 100], showticklabels=False, showgrid=False),
yaxis=lib.standard_yaxis("", range=[0, 1], showgrid=False, showticklabels=False))
lib.add_why_and_footer(fig, why, footer)
# Deterministic watermark: a 100%-stacked tape leaves no interior white space, so pin the mark in the
# (empty) top-right paper margin where it cannot collide with the tape or the chip.
fig.add_annotation(name="brand_watermark", text=lib.WATERMARK_TEXT, xref="paper", yref="paper",
x=1.0, y=0.99, xanchor="right", yanchor="top", showarrow=False,
font=dict(family="Arial, sans-serif", size=12.5, color="#8d94a1"), opacity=0.8)
# CSV/manifest sidecar: one row per displayed band + the clean-week dates + clean_panel_days (sidecar/
# manifest only, NEVER printed on the face).
band_names = {k: f"premiered {label}" for k, label, _, _ in BANDS}
csv_df = pd.DataFrame([
{"premiere_band": band_names[b],
"share_of_week_demand_score_pct": round(float(share_by[b]), 2),
"week_start": wk_start, "week_end": wk_end, "clean_panel_days": days_used}
for b in ("b4", "b3", "b2", "b1", "b0")])
lib.save_csv(csv_df, output_dir)
lib.export_chart(fig, output_dir, width=1240, height=int(fig.layout.height or 560))
lib.write_lesson_design(output_dir, chart_id, f"# {chart_id}\n\n{takeaway}\n\n{what}\n")
write_shim(output_dir, chart_id)
lib.write_manifest(output_dir, chart_id, "ok", reg["spec_file"], pc_id="PC-I-08",
intended_lesson_from_spec="Latest clean-week US attention split by title vintage (de-skewed lane), single-week snapshot",
rendered_lesson={"reader_title": takeaway, "reader_subtitle": what,
"claim_strength": "strong",
"rules_applied": ("demand_score weights, geo US; within-week shares (panel-safe, L-046); "
"single clean week (latest 7 clean panel days), no time axis; "
"vintage = IMDb start_year; newest band labeled as a two-calendar-year "
"cohort; SEQ_RAMP monochrome ordinal fills (navy oldest pole), no valent hues")},
row_count=len(csv_df), freshness_date=freshness,
extra={"unknown_vintage_pct": round(float(unk_pct), 3),
"week_start": wk_start, "week_end": wk_end, "clean_panel_days": days_used},
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(csv_df)}
# ===========================================================================
# SECTION 09 - Calendar & Seasonality (13 charts)
# ===========================================================================