"""SOURCE render logic for TI-F-02 (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_02(reg: dict) -> dict:
"""IMDb vs TMDb disagreement dumbbell - where the two audiences split hardest."""
chart_id = "TI-F-02"
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 fd FROM title_metrics_daily),
di AS (SELECT imdb_id FROM title_metrics_daily t JOIN latest ON file_date=fd
WHERE geo_code='US' AND di IS NOT NULL)
SELECT m.imdb_id, m.tmdb_score::numeric AS tmdb, r.average_rating::numeric AS imdb,
(SELECT fd FROM latest) AS freshness_date
FROM categorization_metadata m
JOIN categorization_imdb_ratings r USING (imdb_id)
JOIN di USING (imdb_id)
WHERE m.tmdb_score > 0 AND m.tmdb_score < 9.5 AND r.average_rating IS NOT NULL -- <9.5 drops data-artifact "perfect 10.0 over a few hundred votes" entries
AND m.tmdb_votes >= 500 AND r.num_votes >= 25000;
""")
q_sec = time.time() - t0
if df.empty:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No IMDb/TMDb overlap")
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
df["imdb"] = df["imdb"].astype(float); df["tmdb"] = df["tmdb"].astype(float)
df["gap"] = df["imdb"] - df["tmdb"]
corr = df["imdb"].corr(df["tmdb"])
n_all = len(df) # the full qualifying universe behind the headline correlation
# Top divergers each direction
imdb_hi = df.nlargest(8, "gap")
tmdb_hi = df.nsmallest(8, "gap")
sel = pd.concat([tmdb_hi, imdb_hi]).drop_duplicates("imdb_id")
titles = lib.resolve_titles(conn, sel["imdb_id"].tolist()); conn.close()
sel = sel.merge(titles[["imdb_id", "display_title"]], on="imdb_id", how="left")
sel["display_title"] = sel["display_title"].fillna(sel["imdb_id"]).apply(lambda s: lib.ellipsize(s, 56))
sel = sel.sort_values("gap").reset_index(drop=True) # TMDb-higher at bottom, IMDb-higher at top
y = list(range(len(sel)))
fig = go.Figure()
# connector lines
for yi, (_, r) in zip(y, sel.iterrows()):
fig.add_trace(go.Scatter(x=[r["tmdb"], r["imdb"]], y=[yi, yi], mode="lines",
line=dict(color=lib.PALE_BLUE, width=2), showlegend=False, hoverinfo="skip"))
fig.add_trace(go.Scatter(x=sel["tmdb"], y=y, mode="markers", name="TMDb crowd (broad / casual)",
marker=dict(size=12, color=lib.BLUE, line=dict(width=1, color="white")),
text=sel["display_title"],
hovertemplate="%{text}
TMDb %{x:.1f}"))
fig.add_trace(go.Scatter(x=sel["imdb"], y=y, mode="markers", name="IMDb crowd (engaged fans)",
marker=dict(size=12, color=lib.NAVY, line=dict(width=1, color="white")),
text=sel["display_title"],
hovertemplate="%{text}
IMDb %{x:.1f}"))
# gap label at the midpoint
for yi, (_, r) in zip(y, sel.iterrows()):
mid = (r["imdb"] + r["tmdb"]) / 2
fig.add_annotation(x=mid, y=yi, text=f"{abs(r['gap']):.1f}", showarrow=False,
yshift=11, font=dict(size=9, color=lib.MUTED))
kard = sel.loc[sel["gap"].idxmin()]
title_lesson = (f"IMDb and TMDb mostly agree - except where different crowds show up "
f"({kard['display_title']}: IMDb {kard['imdb']:.1f} vs TMDb {kard['tmdb']:.1f})")
what = ("Each row is one title scored two ways - IMDb (dark navy) and TMDb (lighter blue) - with the gap printed "
f"above the bar between them. Only the 16 titles where the two crowds disagree most (out of {n_all:,} qualifying) are shown.")
why = ("A rating isn't objective truth - it reflects who shows up to vote. IMDb's devoted fanbase lifts prestige TV and anime "
"but tanks reality TV and divisive reboots (a review-bombing tell); TMDb's lighter crowd is more forgiving. "
"Knowing which crowd you're reading keeps one number from misleading a greenlight or a marketing read.")
fig.update_layout(**lib.layout_v2(title_lesson, what, width=1200, height=820),
xaxis=lib.standard_xaxis("Audience rating (10-point scale)"),
yaxis=lib.standard_yaxis(showgrid=False),
legend=dict(orientation="h", yanchor="bottom", y=1.0, xanchor="left", x=0, font=dict(size=11)))
fig.update_yaxes(tickmode="array", tickvals=y, ticktext=sel["display_title"].tolist())
fig.update_xaxes(range=[max(0, float(sel[["imdb", "tmdb"]].min().min()) - 0.8),
float(sel[["imdb", "tmdb"]].max().max()) + 0.6])
# which half means what: two horizontal captions clear of the y-label gutter
fig.add_annotation(x=1.0, y=0.97, xref="paper", yref="paper", text="IMDb fans rate these higher",
showarrow=False, font=dict(size=10, color=lib.MUTED), xanchor="right")
fig.add_annotation(x=1.0, y=0.03, xref="paper", yref="paper", text="TMDb crowd rates these higher",
showarrow=False, font=dict(size=10, color=lib.MUTED), xanchor="right")
# SCOPE-AWARE trust line: this chart plots ratings only (no panel viewing data), so the
# boilerplate 'US viewer panel' line would describe a different chart.
footer_f02 = lib.assemble_footer("PC-F-02",
[], freshness,
["categorization_metadata", "categorization_imdb_ratings"],
f"25k+ IMDb votes and 500+ TMDb votes ({n_all:,} titles qualify); the 8 biggest splits each way shown; "
f"across the full universe the two sites track closely (correlation r={corr:.2f})")
footer_f02 = footer_f02.replace(lib.PANEL_SCOPE,
"public crowd ratings (IMDb / TMDb), no viewing data on this chart", 1)
lib.add_why_and_footer(fig, why, footer_f02)
lib.save_csv(sel, output_dir); lib.export_chart(fig, output_dir, width=1200, height=820)
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-02",
intended_lesson_from_spec="IMDb vs TMDb audience disagreement",
rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what,
"claim_strength": "strong",
"rules_applied": f"dumbbell, top 8 gaps each way from {n_all} qualifying titles; r={corr:.2f} on full universe"},
row_count=len(sel), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(sel)}