"""SOURCE render logic for TI-DP-06 (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_DP_06(reg: dict) -> dict: """DV90 Stability Lens - DI vs DV_90 quadrant scatter.""" chart_id = "TI-DP-06" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() # Demand Score basis for the top-200 selection (cred baked in - keeps low-vote artifact titles off a # title-labeled surface); volatility stays the 90-day measure. 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, t.dv_90::numeric AS dv_90, l.file_date AS freshness_date FROM title_metrics_daily t JOIN latest l USING (file_date) WHERE t.demand_score IS NOT NULL AND t.dv_90 IS NOT NULL AND t.geo_code = 'US' ORDER BY t.demand_score DESC LIMIT 200; """) q_sec = time.time() - t0 if df.empty: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No DV_90 rows") conn.close() 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"]) freshness = str(df["freshness_date"].iloc[0]) df["di_f"] = df["demand"].astype(float) df["dv_f"] = df["dv_90"].astype(float) median_di = df["di_f"].median() median_vol = df["dv_f"].median() # For log-y, clip near-zero values to 0.01 df["dv_clipped"] = df["dv_f"].clip(lower=0.01) n_low_vol = int((df["dv_f"] < median_vol).sum()) # purple class = a DECLARED cut: everything above the 95th percentile of volatility (the old # head(8) cap drew an arbitrary, unexplained boundary between near-identical titles) high_vol_outliers = df[df["dv_f"] > df["dv_f"].quantile(0.95)].sort_values("dv_f", ascending=False) fig = go.Figure() fig.add_trace(go.Scatter( x=df["di_f"], y=df["dv_clipped"], mode="markers", marker=dict(size=8, color=lib.PALE_BLUE, opacity=0.6, line=dict(width=0)), text=df["display_title"], showlegend=False, hovertemplate="%{text}
Demand Score %{x:,.0f}
demand volatility (90d) %{y:.2f}", )) # Highlight high-volatility outliers in NEUTRAL purple (color contract: navy = the leader # ONLY; 'most erratic' is a class, so it gets a non-semantic emphasis hue); labels are added # AFTER the axes go log, as annotations with leader lines (bare top-right text labels drifted # onto NEIGHBORING dots in the crowded top cluster - the Euphoria label read as The Boys') fig.add_trace(go.Scatter( x=high_vol_outliers["di_f"], y=high_vol_outliers["dv_f"].clip(lower=0.01), mode="markers", marker=dict(size=10, color=lib.PURPLE, line=dict(width=1, color="white")), text=high_vol_outliers["display_title"], cliponaxis=False, showlegend=False, hovertemplate="%{text}
Demand Score %{x:,.0f}
demand volatility (90d) %{y:.2f}", )) max_vol = float(df["dv_f"].max()) max_vol_title = df.loc[df["dv_f"].idxmax(), "display_title"] # NB: share-below-median is ~50% by construction, so don't headline it. Lead with the real signal: # the distribution is right-skewed - the bulk cluster low, a few outliers are extreme. vol_ratio = max_vol / median_vol if median_vol > 0 else 0 # the headline multiple describes the whole highlighted CLUSTER, not just its single most extreme # member: print the cluster's span (low-to-high), collapsing to one number only when they round equal clus_lo_ratio = (float(high_vol_outliers["dv_f"].min()) / median_vol) if (len(high_vol_outliers) and median_vol > 0) else vol_ratio if round(clus_lo_ratio) == round(vol_ratio): span_phrase = f"~{vol_ratio:.0f}×" else: span_phrase = f"~{clus_lo_ratio:.0f}× to ~{vol_ratio:.0f}×" title_lesson = (f"Demand stability is the norm among top titles - a small cluster of exceptions, led by " f"{max_vol_title}, runs {span_phrase} the typical title's volatility") what = ("Each dot = a top title: right = more in-demand (Demand Score), up = more volatile - its daily Demand Score " "swings more around its own typical level over the last 90 days. Purple dots = the most erratic (the top 5% by volatility).") why = ("Steady, high-demand titles (bottom-right) are dependable anchors - reliable for scheduling and ad guarantees. High up = " "feast-or-famine titles that spike and crash, harder to plan around. It separates the workhorses from the fireworks.") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1200, height=720)) lib.set_log_axis(fig, "x", "How in-demand the title is (Demand Score, log scale) →") lib.set_log_axis(fig, "y", "Volatility of its daily Demand Score, steady → erratic (log scale)", tickvals=[0.01, 0.03, 0.1, 0.3, 1, 3], ticktext=["0.01", "0.03", "0.1", "0.3", "1", "3"]) # explicit log10 ranges: a ref-line shape at a negative log10 coord (median vol < 1) makes # plotly's autorange blow out, squashing all dots into a sliver fig.update_xaxes(range=[np.log10(float(df["di_f"].min()) * 0.85), np.log10(float(df["di_f"].max()) * 1.2)]) fig.update_yaxes(range=[np.log10(max(0.008, float(df["dv_clipped"].min()) * 0.7)), np.log10(float(df["dv_clipped"].max()) * 1.8)]) # Outlier labels with leader lines, fanned alternately so each label visibly belongs to ITS dot hv = high_vol_outliers.sort_values("di_f").reset_index(drop=True) fan = [(-64, -34), (58, -40), (-58, 36), (64, 30), (0, -48), (0, 42), (-80, -20), (80, 22)] for k, r in hv.iterrows(): ax_o, ay_o = fan[k % len(fan)] fig.add_annotation(x=np.log10(float(r["di_f"])), y=np.log10(max(float(r["dv_f"]), 0.01)), text=lib.ellipsize(str(r["display_title"]), 40), showarrow=True, arrowhead=0, arrowwidth=1, arrowcolor=lib.MUTED, ax=ax_o, ay=ay_o, font=dict(size=9, color=lib.INK), bgcolor="rgba(255,255,255,0.8)") # Median marker drawn as a DATA-coordinate dashed line (shape-based ref lines on this log # axis land at the wrong y - raw-vs-log10 quirk - leaving only a floating label). med_y = float(median_vol) if median_vol > 0.01 else 0.01 data_refline(fig, y=med_y, lo=float(df["di_f"].min()) * 0.85, hi=float(df["di_f"].max()) * 1.2) fig.add_annotation(x=0.99, y=np.log10(med_y), xref="paper", yref="y", text=f"median volatility ({median_vol:.2f})", showarrow=False, xanchor="right", yanchor="bottom", font=dict(size=10, color=lib.MUTED), bgcolor="rgba(255,255,255,0.85)") # quadrant cues so 'bottom-right' in the WHY is visible on the plot itself fig.add_annotation(xref="paper", yref="paper", x=0.96, y=0.06, text="steady workhorses", showarrow=False, font=dict(size=10, color=lib.MUTED)) fig.add_annotation(xref="paper", yref="paper", x=0.5, y=0.97, text="feast-or-famine", showarrow=False, font=dict(size=10, color=lib.MUTED)) n_dropped = 200 - len(df) drop_note = (f" ({len(df)} drawn; {n_dropped} in-theaters / not-yet-streaming titles excluded)" if n_dropped > 0 else "") lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-DP-08", ["volatility measures how widely a title's daily demand swings around its own 90-day typical level"], freshness, ["title_metrics_daily"], f"top 200 US titles by Demand Score{drop_note} · log scale on both axes")) lesson = {"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": f"log-x, log-y; Demand Score basis; median 90d demand volatility = {median_vol:.2f}"} lib.save_csv(df, output_dir) lib.export_chart(fig, output_dir, width=1200, height=720) 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-DP-08", intended_lesson_from_spec="DI vs DV_90 quadrant scatter", 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)}