"""SOURCE render logic for TI-F-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_F_01(reg: dict) -> dict:
"""Reception vs Demand Quadrant - IMDb rating vs DI."""
chart_id = "TI-F-01"
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 t.imdb_id, t.demand_score::numeric AS demand,
r.average_rating::numeric AS rating, r.num_votes::integer AS votes,
(SELECT file_date FROM latest) AS freshness_date
FROM title_metrics_daily t JOIN latest l USING (file_date)
JOIN categorization_imdb_ratings r USING (imdb_id)
WHERE t.demand_score IS NOT NULL AND t.geo_code='US' AND r.average_rating IS NOT NULL
AND r.num_votes >= 1000
ORDER BY t.demand_score DESC LIMIT 500;
""")
q_sec = time.time() - t0
if df.empty:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No rating-demand data")
conn.close(); write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
freshness = str(df["freshness_date"].iloc[0])
df = df.drop(columns=["freshness_date"])
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["rating"] = df["rating"].astype(float)
med_rating = df["rating"].median(); med_di = df["demand"].median()
# Quadrant assignment (loved = above-median rating; watched = above-median demand)
def quad(r):
hi_rate = r["rating"] >= med_rating; hi_dem = r["demand"] >= med_di
if hi_rate and hi_dem: return "loved_watched"
if not hi_rate and hi_dem: return "guilty"
if hi_rate and not hi_dem: return "gem"
return "overlooked"
df["quad"] = df.apply(quad, axis=1)
# Quadrant identity is NOT a verdict: per the color contract (green = rising ONLY, red =
# falling ONLY, navy = leader ONLY, amber = NEW only) all four classes get neutral
# non-semantic hues from the teal/purple/slate/grey family; the corner captions decode them.
QC = {"loved_watched": "#3F7C8C", # teal
"guilty": lib.PURPLE, # purple
"gem": "#46606E", # dark slate
"overlooked": "#B3BAC5"} # grey field
fig = go.Figure()
# Faint quadrant tint via shapes is overkill; color the points by quadrant instead.
# No legend: the four in-plot corner captions ARE the decode (a top legend duplicated them).
for q, label in [("overlooked", "Overlooked"), ("gem", "Hidden gem"),
("guilty", "Guilty pleasure"), ("loved_watched", "Loved & watched")]:
sub = df[df["quad"] == q]
fig.add_trace(go.Scatter(x=sub["rating"], y=sub["demand"], mode="markers", name=label,
marker=dict(size=7, color=QC[q], opacity=0.72,
line=dict(width=0.5, color="white")),
text=sub["display_title"], showlegend=False,
hovertemplate="%{text}
Rating %{x:.1f} ยท Demand Score %{y:,.0f}"))
# axes BEFORE shapes/annotations so the log type is known to the log-safe helpers
title_n = len(df)
corr = float(df["rating"].corr(np.log10(df["demand"]))) # r on log demand (raw demand is heavy-tailed)
# headline claims exactly what the statistic supports (the old 'barely overlap' overstated a
# near-zero correlation - the loved-and-watched quadrant is well populated)
if abs(corr) < 0.15:
rel = "barely predicts"
elif abs(corr) < 0.4:
rel = "only loosely predicts"
else:
rel = "partly predicts"
# layered disclosure: the H1 carries the plain claim only, WITH its US geography (a stranger
# read the old headline as global); the correlation + log-scale caveat is expert detail and
# lives in the subtitle, never the headline
title_lesson = (f"Among the {title_n} most-watched US titles, how well-liked a title is {rel} how much "
f"it gets watched")
what = (f"Each dot is one of the {title_n} most-watched US titles: left-right = its IMDb rating (how well-liked), "
f"up-down = its current US Demand Score (log scale - how much it's watched now; the headline's read is a "
f"correlation of {corr:.2f} on this log scale). Dashed lines split the field at the group's medians.")
# WHY uses the SAME quadrant names printed on the plot (a 'watched anyway' alias appeared nowhere on the chart)
why = ("Quality and popularity are different bets. The top-left 'guilty pleasures' drive "
"engagement despite mixed reviews; the bottom-right 'hidden gems' are well-reviewed shows worth a marketing push to find their audience.")
fig.update_layout(**lib.layout_v2(title_lesson, what, width=1200, height=760),
xaxis=lib.standard_xaxis("IMDb rating (well-liked โ)"),
yaxis=lib.standard_yaxis("", type="log"))
lib.set_log_axis(fig, "y", "Current Demand Score (more-watched โ)")
# extra bottom headroom (min*0.55, was *0.85) so the corner captions sit clear of the lowest dots
fig.update_yaxes(range=[np.log10(float(df["demand"].min()) * 0.55), np.log10(float(df["demand"].max()) * 1.4)])
# median split lines: the vertical (linear x) ref line is fine as a shape; the horizontal
# demand median must be a DATA-coordinate trace (shapes on log axes take raw values in this
# plotly build, so the old log10 shape rendered off-plot and the promised line never showed)
# median split lines carry ANCHORED labels at the line (locked rule; model: TI-F-05's
# 'catalog average 6.91') - the split values must not live only in sublabels/footer
lib.add_ref_line(fig, x=float(med_rating))
fig.add_annotation(x=float(med_rating), y=0.995, xref="x", yref="paper",
text=f"median rating {med_rating:.1f}", showarrow=False,
xanchor="left", xshift=5, yanchor="top",
font=dict(size=10, color=lib.MUTED), bgcolor="rgba(255,255,255,0.85)")
data_refline(fig, y=float(med_di), lo=float(df["rating"].min()) - 0.15, hi=float(df["rating"].max()) + 0.15)
fig.add_annotation(x=0.005, y=np.log10(float(med_di)), xref="paper", yref="y",
text=f"median Demand Score ~{med_di:,.0f}", showarrow=False,
xanchor="left", yanchor="bottom",
font=dict(size=10, color=lib.MUTED), bgcolor="rgba(255,255,255,0.85)")
# Quadrant corner captions (paper-relative). "Cooler reviews" + the split value - a 7.8 here is NOT a weak
# rating in general, just below this group's median.
for xa, ya, xx, txt, col in [
(0.985, 0.96, "right", f"LOVED & WATCHED
rated above the group's typical {med_rating:.1f}, watched a lot", QC["loved_watched"]),
(0.015, 0.96, "left", f"GUILTY PLEASURES
rated below the group's typical {med_rating:.1f}, watched a lot", QC["guilty"]),
(0.985, 0.06, "right", "HIDDEN GEMS
great reviews, smaller audience", QC["gem"]),
(0.015, 0.06, "left", "OVERLOOKED
cooler reviews, smaller audience", lib.MUTED)]:
fig.add_annotation(x=xa, y=ya, xref="paper", yref="paper", text=txt, showarrow=False,
xanchor=xx, yanchor="top", align=("right" if xx == "right" else "left"),
font=dict(size=12, color=col, family="Arial Black"),
bgcolor="rgba(255,255,255,0.75)")
# Label one recognizable exemplar per quadrant (log-safe annotation coords). The gem exemplar
# labels BELOW its dot: gems sit just under the median-demand line, and an above-the-dot label
# landed exactly on the dashed line (the Chernobyl strike-through).
for q, ay_off in [("loved_watched", -22), ("guilty", -22), ("gem", 26)]:
sub = df[df["quad"] == q]
if sub.empty: continue
ex = sub.nlargest(1, "demand").iloc[0] if q != "gem" else sub.nlargest(1, "rating").iloc[0]
lib.log_annotation(fig, x=float(ex["rating"]), y=float(ex["demand"]), text=f"{lib.ellipsize(ex['display_title'], 26)}",
showarrow=True, arrowhead=0, arrowwidth=1, arrowcolor=lib.INK, ax=0, ay=ay_off,
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-F-01",
["CL-006 viewer panel (distinct viewers), not census"], freshness,
["title_metrics_daily", "categorization_imdb_ratings"],
f"top-{title_n} by US Demand Score, 1,000+ IMDb votes; quadrants split at the sample medians (printed at the dashed lines)"))
# 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=760)
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-F-01",
intended_lesson_from_spec="Rating vs demand quadrant (loved/watched/gem/overlooked)",
rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what,
"claim_strength": "strong",
"rules_applied": f"median split (rating {med_rating:.1f} / demand {med_di:.0f}) + quadrant naming; r computed on log10(demand)"},
row_count=len(df), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}