"""SOURCE render logic for TI-RC-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_RC_05(reg: dict) -> dict: """The Movies 10 (weekly) - top-10 US FILMS by demand score (M053 movie cut): what America watched AT HOME.""" chart_id = "TI-RC-05" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() df = lib.run_query(conn, """ SELECT r.di_rank, b.primary_title, r.demand_score_7d::numeric AS score, r.reach_7d_avg::numeric AS viewers, r.cred::numeric AS cred, r.rank_delta_7d, r.prev_week_rank, r.weeks_in_top10, COALESCE(r.catalog_surge, false) AS catalog_surge, COALESCE(r.is_new_title, false) AS is_new, b.start_year, r.file_date AS freshness_date FROM metrics_demand_rank_history r JOIN categorization_imdb_title_basics b USING (imdb_id) WHERE r.file_date = (SELECT MAX(file_date) FROM metrics_demand_rank_history) AND r.geo_code = 'US' AND r.title_type = 'movie' ORDER BY r.di_rank LIMIT 10; """) side = lib.run_query(conn, """ SELECT b.primary_title, t.reach, t.cred::numeric AS cred, COALESCE(ws.sub_stream, false) AS sub_stream FROM title_metrics_daily t JOIN categorization_imdb_title_basics b USING (imdb_id) LEFT JOIN (SELECT imdb_id, bool_or(watch_type IN ('flatrate','free','ads')) AS sub_stream FROM categorization_platforms_available WHERE country_code = 'US' GROUP BY imdb_id) ws USING (imdb_id) WHERE t.geo_code = 'US' AND b.title_type = 'movie' AND t.file_date = (SELECT MAX(file_date) FROM metrics_demand_rank_history) ORDER BY t.reach DESC LIMIT 10; """) 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-05"), "No movie demand-rank history") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} df["score"] = df["score"].astype(float); df["viewers"] = df["viewers"].astype(float) df["di_rank"] = df["di_rank"].astype(int); df["is_new"] = df["is_new"].astype(bool) freshness = str(df["freshness_date"].iloc[0]) def chip(delta, prev, debut): if debut: return "NEW", lib.ACCENT # first week on the board only (rc_is_debut) if prev is None or pd.isna(prev): return "BACK", lib.GREEN d = int(delta) if pd.notna(delta) else 0 if d > 0: return f"+{d}", lib.GREEN if d < 0: return f"{d}", lib.RED return "hold", lib.MUTED plot = df.sort_values("di_rank", ascending=False).reset_index(drop=True) plot["adj_factor"] = plot["score"] / plot["viewers"].clip(lower=1) # combined adjustment (corroboration x composition) ticks, texts, colors = [], [], [] for _, r in plot.iterrows(): rk = int(r["di_rank"]); title = str(r["primary_title"]) debut = rc_is_debut(r["is_new"], r["weeks_in_top10"]) ch, cc = chip(r["rank_delta_7d"], r["prev_week_rank"], debut) is_lead = rk == 1 ticks.append(f"{rk}. {title}" if (is_lead or debut) else f"{rk}. {title}") badge = f" CATALOG" if bool(r.get("catalog_surge", False)) else "" wk = int(r["weeks_in_top10"]) if pd.notna(r["weeks_in_top10"]) else 0 wk_tag = f" wk {wk}" if (bool(r["is_new"]) and not debut) else "" texts.append(f"{r['score']:,.0f}  {ch}{wk_tag}{badge}") # score numeral + movement chip colors.append(lib.NAVY if is_lead else (lib.ACCENT if debut else lib.PALE_BLUE)) fig = go.Figure() fig.add_trace(go.Bar( x=plot["score"].astype(float), y=ticks, orientation="h", marker=dict(color=colors, line=dict(width=0)), text=texts, textposition="outside", textfont=dict(size=11, color=lib.MUTED), cliponaxis=False, showlegend=False, customdata=plot[["viewers", "adj_factor"]].values, hovertemplate="%{y}
Demand Score %{x:,.0f} · %{customdata[0]:,.0f} avg daily panel viewers", )) fig.update_xaxes(range=[0, float(plot["score"].max()) * 1.22]) leader = df.iloc[0] debuts = df[df.apply(lambda r: rc_is_debut(r["is_new"], r["weeks_in_top10"]), axis=1)] n_new = len(debuts) if n_new >= 2: takeaway = (f"{leader['primary_title']} is America's most-watched film at home this week, " f"with {n_new} fresh releases storming the Top 10") elif n_new == 1: nw = debuts.iloc[0] takeaway = (f"{leader['primary_title']} is America's most-watched film at home this week, " f"while {nw['primary_title']} debuts at #{int(nw['di_rank'])}") else: takeaway = f"{leader['primary_title']} is America's most-watched film at home this week" what = ("The 10 most-watched films in US homes, ranked by Demand Score, our measure of weekly at-home " "audience. Films you can only buy or rent are listed in the note below; tags show each film's move since last week.") why = ("The box office counts tickets; this counts attention at home, where most movie-watching actually happens. " "A theatrical hit that tanks at home, or a streaming-only film outdrawing the multiplex slate, is exactly " "the gap this chart exposes.") cautions = ["Demand Score: panel viewing, weighted to reflect the broad US streaming audience (methodology: METH-03)"] if panel_degraded: cautions.append("panel volume atypical on this date; scores use a 7-day basis") footer = lib.assemble_footer(pc_id=reg.get("pc_id", "PC-RC-05"), caution_labels=cautions, freshness_date=freshness, sources=["metrics_demand_rank_history"], extra="films at home, not tickets sold · US · streaming-eligible only · weekly") fig.update_layout(**lib.layout_v2(takeaway, what, width=1180, height=750), xaxis=lib.standard_xaxis("", showticklabels=False, showgrid=False), yaxis=lib.standard_yaxis(showgrid=False)) # two transparency lanes (no silent exclusions): purchase-window titles + thin-corroboration titles. # Names only - raw panel-reach numbers next to Demand Score bars would clash units (8x bigger numbers # on a different yardstick); the raw figures live in hover/csv. notes = [] ahead = side[~side["sub_stream"].astype(bool)] if len(ahead): notes.append("Huge at home but purchase/rental only, so not yet chart-eligible: " + ", ".join(lib.ellipsize(str(r.primary_title), 24) for r in ahead.head(3).itertuples())) pend = side[side["sub_stream"].astype(bool) & ((side["cred"].isna()) | (side["cred"].astype(float) < 0.5))] if len(pend): notes.append("Watched a lot in our panel but not yet confirmed by outside signals: " + ", ".join(lib.ellipsize(str(r.primary_title), 24) for r in pend.head(3).itertuples())) for _i, _txt in enumerate(notes): fig.add_annotation(text=_txt, xref="paper", yref="paper", x=0.0, y=-0.045 - 0.026 * _i, xanchor="left", yanchor="top", showarrow=False, align="left", font=dict(size=9.5, color=lib.MUTED)) lib.add_why_and_footer(fig, why, footer) lib.save_csv(df, output_dir) lib.export_chart(fig, output_dir, width=1180, height=750) what_plain = re.sub(r"", "", what) lesson = {"reader_title": takeaway, "reader_subtitle": what_plain, "claim_strength": "strong", "rules_applied": f"M053 movie cut top-10 (K_movie=3000); debuts={n_new}; Billboard display"} 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-05"), intended_lesson_from_spec="Weekly Movies 10: at-home film demand with WoW movement (M053 movie cut)", rendered_lesson=lesson, row_count=len(df), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)}