"""SOURCE render logic for TI-F-05 (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_05(reg: dict) -> dict:
"""Quality Over-Index by Genre - mean rating by primary genre vs overall mean."""
chart_id = "TI-F-05"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
# Multi-label genres: a title's rating counts toward EVERY genre it carries (primary_genre collapsed
# multi-genre titles and skewed per-genre averages - the GENRE-TABLE trap).
# NO LIMIT (audit, DB-verified): the old ORDER BY mean_rating DESC LIMIT 20 silently cut the 8 LOWEST-rated
# genres (Horror 5.5 among them), so the chart understated its own spread 3x and named the wrong floor.
# 'Adult' is excluded from the public dashboard.
df = lib.run_query(conn, """
WITH gen AS (SELECT DISTINCT imdb_id, genre AS primary_genre FROM categorization_imdb_genres
WHERE genre IS NOT NULL AND genre NOT IN ('NA','N/A','Unknown','','Adult'))
SELECT g.primary_genre, AVG(r.average_rating::numeric) AS mean_rating,
COUNT(*) AS n,
(SELECT MAX(last_updated)::date FROM categorization_imdb_ratings) AS freshness_date
FROM gen g
JOIN categorization_imdb_ratings r USING (imdb_id)
WHERE r.average_rating IS NOT NULL AND r.num_votes >= 100
GROUP BY g.primary_genre HAVING COUNT(*) >= 50;
""")
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 genre-rating data")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
freshness = str(df["freshness_date"].iloc[0]) # data as-of = the ratings table's last update
df = df.drop(columns=["freshness_date"])
df["mean_rating"] = df["mean_rating"].astype(float); df["n"] = df["n"].astype(int)
# title-weighted catalog average = the honest reference (not mean-of-means)
overall = float((df["mean_rating"] * df["n"]).sum() / df["n"].sum())
df = df.sort_values("mean_rating", ascending=True).reset_index(drop=True)
y = list(range(len(df)))
fig = go.Figure()
# diverging lollipop: stem from catalog average to the genre's rating
for yi, (_, r) in zip(y, df.iterrows()):
col = lib.GREEN if r["mean_rating"] >= overall else lib.RED
fig.add_trace(go.Scatter(x=[overall, r["mean_rating"]], y=[yi, yi], mode="lines",
line=dict(color=col, width=2.5), opacity=0.55, showlegend=False, hoverinfo="skip"))
dot_colors = [lib.GREEN if v >= overall else lib.RED for v in df["mean_rating"]]
text_pos = ["middle right" if v >= overall else "middle left" for v in df["mean_rating"]]
# Rounding-clash guard (locked rule): a green 6.9 above the line and a red 6.9 below it read
# as a contradiction, so any value that 1dp-rounds onto the average prints 2 decimals instead.
def f_lab(v):
return f" {v:.2f} " if abs(float(v) - overall) < 0.055 else f" {v:.1f} "
fig.add_trace(go.Scatter(x=df["mean_rating"], y=y, mode="markers+text",
marker=dict(size=13, color=dot_colors, line=dict(width=1, color="white")),
text=[f_lab(v) for v in df["mean_rating"]], textposition=text_pos,
textfont=dict(size=10, color=lib.INK), cliponaxis=False, showlegend=False,
customdata=df["n"],
hovertemplate="%{customdata[1]}
avg IMDb rating %{x:.2f}
%{customdata[0]:,} titles"))
fig.data[-1].customdata = list(zip(df["n"], df["primary_genre"]))
fig.update_yaxes(tickmode="array", tickvals=y, ticktext=df["primary_genre"].tolist())
df_desc = df.sort_values("mean_rating", ascending=False)
top = df_desc.iloc[0]; bot = df_desc.iloc[-1]
# V2: the headline gap is computed FROM the rounded values the dots display, so it always reconciles
top_r, bot_r = round(float(top["mean_rating"]), 1), round(float(bot["mean_rating"]), 1)
title_lesson = (f"Genre is a quality signal: {top['primary_genre']} titles average {top_r} on IMDb, "
f"{bot['primary_genre']} just {bot_r} - a {top_r - bot_r:.1f}-point gap")
what = ("Each dot = a genre's average IMDb rating, sorted best-rated at top. The dashed line is the catalog-wide "
"average - green sits above it, red below.")
why = ("Genre carries a built-in quality expectation. Documentary and animation skew high (devoted, self-selecting audiences); "
"horror, sci-fi and thrillers skew low. It's a fast prior for setting audience expectations and reading a new title's reviews in "
"context - though a niche genre's high average partly reflects who chooses to watch it.")
fig.update_layout(**lib.layout_v2(title_lesson, what, width=1100, height=880),
xaxis=lib.standard_xaxis("Average IMDb rating"), yaxis=lib.standard_yaxis(showgrid=False))
fig.update_xaxes(range=[float(df["mean_rating"].min()) - 0.5, float(df["mean_rating"].max()) + 0.6])
lib.add_ref_line(fig, x=overall, label=f"catalog average {overall:.2f}")
# SCOPE-AWARE trust line: ratings + genre labels only - no panel viewing data on this chart
footer_f05 = lib.assemble_footer("PC-F-05",
["CL-010 genre taxonomy v2"], freshness,
["categorization_imdb_ratings", "categorization_imdb_genres"],
f"all {len(df)} genres with 50+ rated titles shown (each title 100+ votes); multi-label genres "
"(a title counts in each of its genres); Adult genre excluded")
footer_f05 = footer_f05.replace(lib.PANEL_SCOPE,
"public IMDb ratings and genre labels, no viewing data on this chart", 1)
lib.add_why_and_footer(fig, why, footer_f05)
lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1100, height=880)
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-05",
intended_lesson_from_spec="Genre quality leaderboard (avg rating vs catalog average)",
rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what,
"claim_strength": "strong", "rules_applied": "diverging lollipop vs weighted mean; no bottom truncation (all qualifying genres); Adult excluded"},
row_count=len(df), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}
# ===========================================================================
# SECTION 07 - Engagement Share (5 charts)
# ===========================================================================