"""SOURCE render logic for TI-J-01 (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_J_01(reg: dict) -> dict:
"""Demand-to-Spend Efficiency Scatter - budget vs DI for movies."""
chart_id = "TI-J-01"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
# FULL budget-having universe (no top-500-by-demand truncation: restriction of range invalidated the old r);
# a small demand floor (>=5) drops dead catalog without selecting on the y variable's interesting range.
df = lib.run_query(conn, """
WITH latest AS (SELECT MAX(file_date) AS file_date FROM title_metrics_daily
WHERE demand_score IS NOT NULL AND geo_code='US')
SELECT t.imdb_id, t.demand_score::numeric AS demand, md.budget::numeric AS budget,
md.revenue::numeric AS revenue, l.file_date
FROM title_metrics_daily t JOIN latest l USING (file_date)
JOIN categorization_movie_details md USING (imdb_id)
WHERE t.demand_score IS NOT NULL AND t.geo_code='US' AND t.demand_score >= 5
AND md.budget > 1000000 AND md.budget IS NOT NULL;
""")
q_sec = time.time() - t0
if df.empty:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No budget+demand overlap")
conn.close(); write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
df = lib.drop_demand_orphans(df, conn) # hide in-theaters / not-yet-streaming demand-orphans
titles = lib.resolve_titles(conn, df["imdb_id"].tolist())
conn.close()
df = df.merge(titles[["imdb_id", "display_title"]], on="imdb_id", how="left")
df["display_title"] = df["display_title"].fillna(df["imdb_id"])
df["demand"] = df["demand"].astype(float); df["budget"] = df["budget"].astype(float)
df = df[df["demand"] > 0]
freshness = str(df["file_date"].iloc[0])
# fit demand ~ budget in log-log space; titles ABOVE the line punch above their budget weight
lx = np.log10(df["budget"]); ly = np.log10(df["demand"])
slope = ((lx - lx.mean()) * (ly - ly.mean())).sum() / ((lx - lx.mean()) ** 2).sum()
intercept = ly.mean() - slope * lx.mean()
df["resid"] = ly - (slope * lx + intercept)
df["over"] = df["resid"] >= 0
# trendline at the 5th/95th percentile budgets (min/max endpoints once blew the autorange to 1e38)
bx0, bx1 = float(df["budget"].quantile(0.05)), float(df["budget"].quantile(0.95))
xs = [bx0, bx1]
ys = [10 ** (slope * np.log10(x) + intercept) for x in xs]
fig = go.Figure()
if np.isfinite(ys).all():
fig.add_trace(go.Scatter(x=xs, y=ys, mode="lines", showlegend=False, hoverinfo="skip",
line=dict(color=lib.INK, width=1.5, dash="dash")))
# over/under the fitted line is a static residual STATE, not a rising/falling movement, so the
# hues are the library's neutral diverging pair (blue above / tan below, as on the genre maps).
# Founder terminology on every surface: 'Demand Score', never bare 'demand' (legend + hover were
# both violations, and the hover also disagreed with the y-axis's own 'Demand Score today').
for keep, col, nm in [(True, lib.BLUE, "Over-delivers (a higher Demand Score than budget predicts)"),
(False, "#C2A878", "Under-delivers")]:
sub = df[df["over"] == keep]
fig.add_trace(go.Scatter(x=sub["budget"], y=sub["demand"], mode="markers", name=nm,
marker=dict(size=5, color=col, opacity=0.45, line=dict(width=0)),
text=sub["display_title"],
hovertemplate="%{text}
budget $%{x:,.0f} · Demand Score that day %{y:,.0f}"))
corr = float(np.corrcoef(lx, ly)[0, 1])
n_over = int(df["over"].sum())
# the variance-explained figure is from a LOG-LOG fit: the headline says so (an expert holding
# us to R-squared must see the basis on the surface, not just r in the footer)
title_lesson = (f"Money barely buys attention: a movie's budget explains only ~{corr ** 2:.0%} of the variation in how much "
f"it gets watched today (measured on the log scales shown)")
what = ("Each dot = a movie: its production budget (left-right) vs its current Demand Score (a point-in-time measure of how "
"much it's watched now, not a cumulative total). The dashed line is the expected Demand Score for a given budget; "
"blue sits above it (over-delivers), tan below.")
why = (f"Spend is a weak predictor of a film's current Demand Score: {n_over:,} of {len(df):,} budget-reporting films draw more audience than their spend "
"predicts. The blue over-deliverers are the efficient bets (a lot of audience per dollar); the tan ones are expensive "
"disappointments - the core read on where money actually converts into attention.")
fig.update_layout(**lib.layout_v2(title_lesson, what, width=1200, height=740),
legend=dict(orientation="h", yanchor="bottom", y=1.0, xanchor="left", x=0, font=dict(size=11)))
lib.set_log_axis(fig, "x", "Production budget", dollar=True,
data_min=float(df["budget"].min()), data_max=float(df["budget"].max()))
lib.set_log_axis(fig, "y", "Demand Score today",
data_min=float(df["demand"].min()), data_max=float(df["demand"].max()))
fig.update_yaxes(range=[float(np.log10(df["demand"].min() * 0.7)), float(np.log10(df["demand"].max() * 1.4))])
# the trendline's deliberate truncation is SAID on the surface (it is fit and drawn across the
# middle 90% of budgets so sparse extremes cannot anchor it; unexplained, the stub read as a bug)
if np.isfinite(ys).all():
lib.log_annotation(fig, x=float(bx1), y=float(ys[1]),
text="expected Demand Score for the budget,
fit drawn across the middle 90% of budgets",
showarrow=True, arrowhead=0, arrowwidth=1, arrowcolor=lib.MUTED,
ax=-14, ay=42, xanchor="right", align="right",
font=dict(size=9.5, color=lib.MUTED), bgcolor="rgba(255,255,255,0.85)")
# exemplars AFTER the axes are log (log_annotation converts data coords to log10 exponents).
# The single HIGHEST-Demand-Score dot is always named too: the topmost mark is the first thing
# a skimming reader asks about, and an unlabeled top dot beside a labeled #2 invited
# mis-transferring the neighbor's callout onto it.
eff = df[df["over"]].nlargest(1, "resid")
flop = df[~df["over"]].nlargest(1, "budget")
top_dot = df.nlargest(1, "demand")
if not top_dot.empty and not eff.empty and top_dot.iloc[0]["imdb_id"] == eff.iloc[0]["imdb_id"]:
top_dot = top_dot.iloc[0:0] # already labeled as the efficiency exemplar
for ex, dx, dy in [(eff, 0, -20), (flop, 0, 18), (top_dot, -26, -14)]:
if not ex.empty:
r = ex.iloc[0]
lib.log_annotation(fig, x=float(r["budget"]), y=float(r["demand"]), text=f"{r['display_title'][:24]}",
showarrow=True, arrowhead=0, arrowwidth=1, arrowcolor=lib.INK, ax=dx, ay=dy,
xanchor="right" if dx < 0 else "center",
font=dict(size=10, color=lib.INK), bgcolor="rgba(255,255,255,0.7)")
lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-J-01",
["CL-006 viewer panel (distinct viewers), not census", "CL-014 external data (TMDb)"], freshness,
["title_metrics_daily", "TMDb film-budget catalog"],
# compact: the longer clause wrapped '· ref PC-J-01' onto a stray third line
f"TMDb budgets (spend proxy); movies over $1M with Demand Score 5+; n = {len(df):,}; log-log r = {corr:.2f}"))
# csv header carries the qualified metric name ('demand' never bare, csv headers included)
lib.save_csv(df.rename(columns={"demand": "demand_score"}), output_dir)
lib.export_chart(fig, output_dir, width=1200, height=740)
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-J-01",
intended_lesson_from_spec="Demand-to-budget efficiency (trendline + over/under)",
rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what,
"claim_strength": "moderate",
"rules_applied": f"log-log fit on the FULL budget universe (n={len(df)}, r={corr:.2f}); residual valence; trendline at 5th/95th pct budgets"},
row_count=len(df), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}