"""SOURCE render logic for TI-RC-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_RC_06(reg: dict) -> dict:
"""The Mover: Films (weekly) - the movie board's hidden momentum (share-of-panel vs trailing 4-week avg),
same panel-safe engine as TI-RC-02: catalog resurgences + cooling openers, debuts side-noted."""
chart_id = "TI-RC-06"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
df = lib.run_query(conn, """
WITH win AS (SELECT (SELECT MAX(file_date) FROM metrics_demand_rank_history) AS d0),
rk AS (SELECT r.imdb_id, r.di_rank, r.di, COALESCE(r.is_new_title, false) AS is_new
FROM metrics_demand_rank_history r
WHERE r.file_date = (SELECT MAX(file_date) FROM metrics_demand_rank_history)
AND r.geo_code='US' AND r.title_type='movie' AND r.di_rank <= 20),
tot AS (SELECT t.file_date, SUM(t.reach) AS s
FROM title_metrics_daily t JOIN categorization_imdb_title_basics b USING (imdb_id)
WHERE t.geo_code='US' AND b.title_type='movie'
AND t.file_date BETWEEN (SELECT d0 FROM win) - 28 AND (SELECT d0 FROM win)
GROUP BY t.file_date),
sh AS (SELECT t.imdb_id, t.file_date, t.reach::numeric / tot.s AS share
FROM title_metrics_daily t JOIN tot USING (file_date)
WHERE t.geo_code='US' AND t.imdb_id IN (SELECT imdb_id FROM rk)),
cur AS (SELECT imdb_id, share AS cur_share FROM sh WHERE file_date = (SELECT d0 FROM win)),
base AS (SELECT imdb_id, AVG(share) AS avg_share, COUNT(*) AS days FROM sh
WHERE file_date < (SELECT d0 FROM win) GROUP BY imdb_id)
SELECT rk.di_rank, b.primary_title AS title, rk.di::numeric AS di, rk.is_new,
COALESCE(base.days, 0) AS base_days,
(100.0*(cur.cur_share - base.avg_share)/NULLIF(base.avg_share, 0)) AS momentum,
(SELECT MAX(file_date) FROM metrics_demand_rank_history) AS freshness_date
FROM rk JOIN categorization_imdb_title_basics b USING (imdb_id)
JOIN cur USING (imdb_id) LEFT JOIN base USING (imdb_id)
ORDER BY rk.di_rank;
""")
ph = lib.run_query(conn, "SELECT degraded FROM metrics_panel_health WHERE file_date = (SELECT MAX(file_date) FROM metrics_demand_rank_history);")
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", "specs/16_recurring.md"),
reg.get("pc_id", "PC-RC-06"), "No movie momentum data")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
df["momentum"] = df["momentum"].astype(float).round(1) # max 1 decimal anywhere (hover/axis/csv)
df["di"] = df["di"].astype(float); df["is_new"] = df["is_new"].astype(bool)
df["base_days"] = df["base_days"].astype(int)
freshness = str(df["freshness_date"].iloc[0])
# Board constants - interpolated into BOTH the filter logic and rules_applied (fix 19)
RC06_EXPLOSION_PCT = 100 # a doubling vs the 4-week norm = a release-event level-shift, side-noted not bar-ranked
RC06_TIER = 20 # this week's 20 highest-demand films
RC06_MIN_BASE_DAYS = 28 # a full 4-week baseline: every bar is a TRUE 4-week comparison (shorter
# baselines read launch-window decay as 'fading' - the Jack Ryan trap)
board = df[(df["di_rank"] <= RC06_TIER) & (df["base_days"] >= RC06_MIN_BASE_DAYS) & (df["momentum"] <= RC06_EXPLOSION_PCT)].copy()
explosions = df[(df["base_days"] >= RC06_MIN_BASE_DAYS) & (df["momentum"] > RC06_EXPLOSION_PCT)].sort_values("momentum", ascending=False)
newbies = df[(df["di_rank"] <= RC06_TIER) & (df["base_days"] < RC06_MIN_BASE_DAYS)].sort_values("di_rank")
if board.empty:
lib.needs_spec_review(output_dir, chart_id, reg.get("spec_file", "specs/16_recurring.md"),
reg.get("pc_id", "PC-RC-06"), "No established-board movie momentum this week")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
board = board.sort_values("momentum").reset_index(drop=True)
ylabels, colors, texts = [], [], []
any_held = False
for r in board.itertuples():
# the '#N' suffix already carries board rank - no bold on #1 (an unexplained emphasis
# with no key read as a stray treatment)
ylabels.append(f"{lib.ellipsize(str(r.title), 52)} #{int(r.di_rank)}")
# a within-half-a-percent move is a HOLD, not a verdict: grey + the word 'held' - so a
# rounded label can never print equal to the zero line while painted on one side of it
if abs(r.momentum) < 0.5:
colors.append(lib.MUTED)
texts.append("held")
any_held = True
else:
colors.append(lib.GREEN if r.momentum >= 0 else lib.RED)
texts.append(lib.fmt_signed(r.momentum, 0, "%"))
fig = go.Figure()
fig.add_trace(go.Bar(
x=board["momentum"], y=ylabels, orientation="h",
marker=dict(color=colors, line=dict(width=0)),
text=texts, textposition="outside", textfont=dict(size=11, color=lib.INK),
cliponaxis=False, showlegend=False,
hovertemplate="%{y}
US at-home demand %{x:+.1f}% vs its 4-week average",
))
mx = float(board["momentum"].max()); mn = float(board["momentum"].min())
pad_left = max(10.0, (mx - mn) * 0.10)
pad_right = max(12.0, (mx - mn) * 0.12)
fig.update_xaxes(range=[mn - pad_left, mx + pad_right], ticksuffix="%", dtick=20)
th = board.loc[board["momentum"].idxmax()]; tc = board.loc[board["momentum"].idxmin()]
# ONE unit word per chart: 'film(s)' everywhere on this surface (never 'movie' - the locked
# films-vs-movies pair - and never 'titles')
if float(tc["momentum"]) > -0.5: # nothing is genuinely fading - don't print a 0% 'cool'
takeaway = (f"{th['title']} is the film board's biggest climber, up {th['momentum']:+.0f}% vs its 4-week "
f"norm, and nothing on the board is genuinely fading")
elif th["momentum"] >= abs(tc["momentum"]):
takeaway = (f"{th['title']} is the film board's biggest climber, up {th['momentum']:+.0f}% vs its 4-week "
f"norm, while {tc['title']} cools {abs(tc['momentum']):.0f}%")
else:
takeaway = (f"{tc['title']} is fading fastest on the film board, down {abs(tc['momentum']):.0f}% vs its "
f"4-week norm, while {th['title']} heats up {th['momentum']:+.0f}%")
held_clause = ", grey = held (within half a percent of its norm)" if any_held else ""
# the subtitle states EXACTLY how many bars render: titles excluded from the bars (too new for
# a 4-week read, or in a release-event surge) are counted here and itemized in the side note
if len(board) < RC06_TIER:
excl_bits = []
if len(newbies):
excl_bits.append(f"{len(newbies)} too new for a 4-week read")
if len(explosions):
excl_bits.append(f"{len(explosions)} in a release-event surge, noted below")
coverage = (f"across {len(board)} of this week's {RC06_TIER} highest-demand films"
+ (f" ({' and '.join(excl_bits)})" if excl_bits else ""))
else:
coverage = f"across this week's {RC06_TIER} highest-demand films"
what = ("How much each film is heating up or cooling down this week: each bar compares a film's share of US "
f"at-home film viewing against its own 4-week average, {coverage}. "
f"Green = climbing, red = fading{held_clause}.")
why = ("Films move differently than shows: openers decay, catalog films resurge on sequel news, holidays and "
"virality. The climbers here are next week's chart entries and the fade rate separates a one-weekend "
"event from a film with legs at home.")
off = []
if not explosions.empty:
off.append("release-event surges " + ", ".join(f"{lib.ellipsize(str(r.title), 32)} {r.momentum:+,.0f}%"
for r in explosions.head(3).itertuples()))
if not newbies.empty:
off.append("too new for a 4-week read: " + ", ".join(f"{lib.ellipsize(str(r.title), 44)} #{int(r.di_rank)}"
for r in newbies.head(3).itertuples()))
if off:
fig.add_annotation(text="Off the bars: " + "; ".join(off), 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))
# one compact methodology CLAUSE, no equals-sign definition (the subtitle already explains the
# 4-week comparison; the trust line only says why panel swings can't fake it)
cautions = ["measured on shares of panel viewing, so panel-size swings cancel out"]
if panel_degraded:
cautions.append("panel volume atypical on this date; read momentum with care")
footer = lib.assemble_footer(pc_id=reg.get("pc_id", "PC-RC-06"),
caution_labels=cautions,
freshness_date=freshness,
sources=["metrics_demand_rank_history", "title_metrics_daily"],
extra="United States · films watched at home · refreshed weekly")
fig.update_layout(**lib.layout_v2(takeaway, what, width=1180, height=680),
xaxis=lib.standard_xaxis("", range=[mn - pad_left, mx + pad_right], ticksuffix="%"),
yaxis=lib.standard_yaxis(showgrid=False))
fig.update_xaxes(zeroline=True, zerolinecolor=lib.MUTED, zerolinewidth=1.4)
fig.add_annotation(text="fading", xref="paper", yref="paper", x=0.0, y=1.012, xanchor="left",
yanchor="bottom", showarrow=False, font=dict(size=10, color=lib.RED))
fig.add_annotation(text="climbing", xref="paper", yref="paper", x=1.0, y=1.012, xanchor="right",
yanchor="bottom", showarrow=False, font=dict(size=10, color=lib.GREEN))
lib.add_why_and_footer(fig, why, footer)
# sidecar hygiene: internal 'di' column name never ships
lib.save_csv(df.rename(columns={"di": "current_demand"}), output_dir)
lib.export_chart(fig, output_dir, width=1180, height=680)
what_plain = re.sub(r"?b>", "", what)
lesson = {"reader_title": takeaway, "reader_subtitle": what_plain, "claim_strength": "strong",
"rules_applied": (f"M053 movie cut share-of-panel momentum vs trailing-4wk avg; established board "
f"(di_rank<={RC06_TIER}, base>={RC06_MIN_BASE_DAYS}d, <=+{RC06_EXPLOSION_PCT}%); "
f"release-events + short-baseline titles side-noted")}
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", "specs/16_recurring.md"),
pc_id=reg.get("pc_id", "PC-RC-06"),
intended_lesson_from_spec="Weekly movie-board momentum: climbing vs fading (M053 movie cut, share-of-panel)",
rendered_lesson=lesson, row_count=len(board), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(board)}