"""SOURCE render logic for TI-C-08 (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_C_08(reg: dict) -> dict: """Catalog Fragmentation Index - HHI over 12 weekly snapshots.""" chart_id = "TI-C-08" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() df = lib.run_query(conn, """ WITH weekly_dates AS ( SELECT (SELECT MAX(file_date) FROM title_metrics_daily WHERE reach IS NOT NULL AND geo_code='US') - (i * INTERVAL '7 days') AS file_date FROM generate_series(0, 25) AS i ), latest_p AS (SELECT MAX(snapshot_date) AS snapshot_date FROM categorization_title_x_platform), parents AS ( SELECT DISTINCT imdb_id, REPLACE(btrim(parent_company), '_', ' / ') AS parent_company FROM categorization_title_x_platform JOIN latest_p USING (snapshot_date) WHERE parent_company IS NOT NULL AND btrim(parent_company) <> '' ), counts AS (SELECT imdb_id, COUNT(*) AS pc FROM parents GROUP BY imdb_id), -- Basis = raw reach: demand_score exists only for the latest snapshot (no history), and HHI is built -- from WITHIN-week shares, so panel-size swings cancel (L-046-safe). di is momentum-weighted - not used. weekly AS ( SELECT w.file_date::date AS d, p.parent_company, SUM(t.reach::numeric / NULLIF(c.pc, 0)) AS credit_demand FROM weekly_dates w JOIN title_metrics_daily t ON t.file_date = w.file_date::date JOIN parents p USING (imdb_id) JOIN counts c USING (imdb_id) WHERE t.reach IS NOT NULL AND t.geo_code='US' GROUP BY w.file_date, p.parent_company ), totals AS (SELECT d, SUM(credit_demand) AS total FROM weekly GROUP BY d) SELECT w.d, 10000 * SUM(POWER(w.credit_demand / NULLIF(t.total, 0), 2)) AS hhi FROM weekly w JOIN totals t USING (d) GROUP BY w.d ORDER BY w.d; """) 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 HHI series") write_shim(output_dir, chart_id) return {"status": "needs_spec_review"} df["d"] = pd.to_datetime(df["d"]).sort_values() df = df.sort_values("d") df["hhi"] = df["hhi"].astype(float) n_weeks = len(df) cur_hhi = float(df["hhi"].iloc[-1]); first_hhi = float(df["hhi"].iloc[0]) min_hhi = float(df["hhi"].min()); max_hhi = float(df["hhi"].max()) fig = go.Figure() fig.add_trace(go.Scatter(x=df["d"], y=df["hhi"], mode="lines+markers", line=dict(color=lib.NAVY, width=2.5), marker=dict(size=6, color=lib.NAVY), showlegend=False, hovertemplate="Week of %{x|%b %d}
Concentration score %{y:,.0f}")) # the 1,500 line carries the STANDARD band name ('moderately concentrated' starts at 1,500; # 'highly concentrated' is conventionally 2,500) so an expert can't quibble with the label lib.add_ref_line(fig, y=1500) fig.add_annotation(x=df["d"].iloc[0], y=1500, text=" 1,500 = the standard 'moderately concentrated' line (crossing it would signal a tightening grip)", showarrow=False, xanchor="left", yanchor="bottom", font=dict(size=10, color=lib.MUTED)) # quantify 'steady' so the reader doesn't have to eyeball the flat line mid_d = df["d"].iloc[len(df) // 2] wiggle = (max_hhi - min_hhi) / min_hhi if min_hhi > 0 else 0 fig.add_annotation(x=mid_d, y=min_hhi, yshift=-16, yanchor="top", text=f"{n_weeks}-week range: {min_hhi:,.0f}-{max_hhi:,.0f} · a {wiggle:.0%} wiggle", showarrow=False, font=dict(size=10, color=lib.MUTED)) trend = (cur_hhi - first_hhi) / first_hhi if abs(trend) < 0.10: verdict = "holding steady - no company is tightening its grip" elif trend > 0: verdict = "creeping up - power is slowly consolidating" else: verdict = "easing - the field is fragmenting" rel = "below" if cur_hhi < 1500 else "above" # thousands separator matches every other number on the surface; the subtitle names the actual # basis (each company's share of weekly US panel viewing), not a bare 'streaming demand' title_lesson = f"The competitive balance is {verdict} (score ~{cur_hhi:,.0f}, {rel} the 'moderately concentrated' line)" what = (f"How concentrated US streaming viewing is, week by week, over {n_weeks} weeks, measured on each parent company's share of all " "panel viewing that week. The score rises if a few companies capture more of that viewing; falls if it spreads out - the dashed " "line at 1,500 is the conventional start of 'moderately concentrated'.") why = ("Practically: this is the early-warning line for a streaming monopoly. A steady, low score means no single company is becoming a " "must-have gatekeeper - good for viewers' choice and for any partner negotiating carriage. A sustained climb would be the signal to watch.") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1240, height=580), xaxis=lib.standard_xaxis(""), yaxis=lib.standard_yaxis("Concentration score (HHI)", rangemode="tozero"), ) fig.update_yaxes(range=[0, max(1700, max_hhi * 1.1)]) lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-C-08", [], str(df["d"].max().date()), ["title_metrics_daily", "categorization_title_x_platform"], "weekly full-catalog audience basis (differs from the top-1,000 daily board's reading) · descriptive, not an antitrust measure")) lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1240, height=580) 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-C-08", intended_lesson_from_spec="Weekly concentration (HHI) trend, zoomed to ~26 weeks", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": f"HHI over {n_weeks} weeks"}, row_count=len(df), freshness_date=str(df["d"].max().date()), query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)} def render_TI_C_09(reg: dict) -> dict: """Parent Breadth and Depth Radar - 5-axis radar (catalog size, genre diversity, country reach, top-decile count, top-decile share).""" chart_id = "TI-C-09" 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), latest_p AS (SELECT MAX(snapshot_date) AS snapshot_date FROM categorization_title_x_platform), active AS ( SELECT t.imdb_id, t.demand_score::numeric AS demand FROM title_metrics_daily t JOIN latest l USING (file_date) WHERE t.demand_score IS NOT NULL AND t.geo_code='US' ), thresh AS (SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY demand) AS top_decile FROM active), parents AS ( SELECT DISTINCT cxp.imdb_id, REPLACE(btrim(cxp.parent_company), '_', ' / ') AS parent_company FROM categorization_title_x_platform cxp JOIN latest_p lp USING (snapshot_date) WHERE cxp.parent_company IS NOT NULL ), -- multi-label genres (categorization_primary_genre collapses multi-genre titles; diversity needs the full -- label set). Aggregated in its OWN CTE so the one-to-many genre join cannot fan out the demand/geo sums. gen AS (SELECT DISTINCT imdb_id, genre FROM categorization_imdb_genres WHERE genre IS NOT NULL AND genre NOT IN ('NA','N/A','Unknown','')), parent_genres AS ( SELECT p.parent_company, COUNT(DISTINCT g.genre) AS genre_diversity FROM parents p JOIN gen g USING (imdb_id) GROUP BY p.parent_company ), geo AS ( SELECT g.imdb_id, g.n_effective::numeric AS n_effective FROM metrics_geographic_breadth_daily g JOIN latest l USING (file_date) ) SELECT p.parent_company, COUNT(DISTINCT p.imdb_id) AS catalog_size, COALESCE(MAX(pg.genre_diversity), 0) AS genre_diversity, AVG(COALESCE(geo.n_effective, 0)) AS mean_country_reach, COUNT(DISTINCT p.imdb_id) FILTER (WHERE active.demand >= (SELECT top_decile FROM thresh)) AS top_decile_count, SUM(active.demand) FILTER (WHERE active.demand >= (SELECT top_decile FROM thresh)) / NULLIF(SUM(active.demand), 0) AS top_decile_share FROM parents p LEFT JOIN active USING (imdb_id) LEFT JOIN geo USING (imdb_id) LEFT JOIN parent_genres pg USING (parent_company) GROUP BY p.parent_company HAVING COUNT(DISTINCT p.imdb_id) >= 50 ORDER BY SUM(active.demand) DESC NULLS LAST LIMIT 12; """) fresh_df = lib.run_query(conn, "SELECT MAX(file_date) AS d FROM title_metrics_daily;") q_sec = time.time() - t0 conn.close() freshness = str(fresh_df["d"].iloc[0]) if len(fresh_df) else None if df.empty: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No qualifying parents") write_shim(output_dir, chart_id) return {"status": "needs_spec_review"} # Invert hit-concentration → "demand spread" so all 5 axes point the same way (higher = broader / more well-rounded). # Otherwise a hit-concentrated studio would wrongly inflate the "well-rounded" score. df["demand_spread"] = 1.0 - pd.to_numeric(df["top_decile_share"], errors="coerce").fillna(0).clip(0, 1) axes = ["catalog_size", "genre_diversity", "mean_country_reach", "top_decile_count", "demand_spread"] # short single-word spoke labels (the subtitle defines all five) so they don't clip into neighbouring radars axis_labels = ["Catalog", "Genres", "Reach", "Hits", "Spread"] for c in axes: df[c] = pd.to_numeric(df[c], errors="coerce").fillna(0) mx = df[c].max() if df[c].max() > 0 else 1 df[c + "_n"] = df[c] / mx # Overall "well-rounded" score = average of the 5 normalized axes (0-100). Sort by it. df["score"] = df[[c + "_n" for c in axes]].mean(axis=1) * 100 df = df.sort_values("score", ascending=False).reset_index(drop=True) titles = [f"{r['parent_company']} · {r['score']:.0f}" for _, r in df.iterrows()] fig = make_subplots(rows=3, cols=4, specs=[[{"type": "polar"}] * 4] * 3, subplot_titles=titles, vertical_spacing=0.18, horizontal_spacing=0.09) theta = axis_labels + [axis_labels[0]] # navy = the top scorer(s) only (amber is reserved for the NEW chip); a displayed-score tie # makes BOTH leaders navy so the color never asserts a superlative the labels don't support is_tie = len(df) > 1 and f"{df.iloc[0]['score']:.0f}" == f"{df.iloc[1]['score']:.0f}" lead_n = 2 if is_tie else 1 for i, (_, row) in enumerate(df.iterrows()): r = [row[c + "_n"] for c in axes] + [row[axes[0] + "_n"]] col = lib.NAVY if i < lead_n else lib.BLUE fill = "rgba(35,62,105,0.26)" if i < lead_n else "rgba(98,115,168,0.16)" fig.add_trace(go.Scatterpolar(r=r, theta=theta, fill="toself", line=dict(color=col, width=2), fillcolor=fill, showlegend=False), row=i // 4 + 1, col=i % 4 + 1) # radial headroom (range to 1.12) keeps the max-score vertices clear of the rim spoke labels fig.update_polars(radialaxis=dict(visible=False, range=[0, 1.12]), angularaxis=dict(tickfont=dict(size=9, color=lib.MUTED))) leader = df.iloc[0]; narrow = df.iloc[-1] runner_up = df.iloc[1] if len(df) > 1 else None # ONE entity word per chart: 'parent companies' everywhere (title, subtitle, WHY, footer) - # 'studios' is loose for Netflix/Amazon/Apple and drifting between studio/parent/owner confuses # the closing score rides a non-breaking space so the wrap can never strand a bare number on its own line # the printed score is the MEAN of the 5 spokes (overall breadth/size), not shape evenness, so the # superlative says 'broadest' and the subtitle teaches what the score actually averages if runner_up is not None and is_tie: title_lesson = (f"{leader['parent_company']} and {runner_up['parent_company']} are the broadest parent companies, with the highest " f"average across the 5 spokes (both score {leader['score']:.0f}/100); {narrow['parent_company']} is the most specialized, with the lowest overall " f"score ({narrow['score']:.0f})") else: title_lesson = (f"{leader['parent_company']} is the broadest parent company, with the highest average across the 5 spokes (score {leader['score']:.0f}/100); " f"{narrow['parent_company']} is the most specialized, with the lowest overall score ({narrow['score']:.0f})") what = ("Each radar = one parent company, its 5 spokes scored 0-100 against the field: catalog size, genre variety, global reach, " "hit count, and how evenly its audience spreads across its titles. The printed score is the simple average of the 5 spokes - it rewards high spokes, not a round shape; panels are sorted by it.") why = ("It's a parent company's fingerprint at a glance: a full, round shape = a broad generalist (something for everyone); a spiky shape = a " "specialist that wins on one or two dimensions. Useful for sizing up who competes broadly vs. who owns a niche.") _lay = lib.layout_v2(title_lesson, what, width=1480, height=1080) _lay["margin"]["t"] = 160 # extra top room so the lifted row-1 subplot titles clear the chart subtitle fig.update_layout(**_lay) # subplot titles: shrink + lift just clear of each radar's top spoke label ("Genres") without hitting the subtitle fig.update_annotations(font_size=10.5, yshift=10) # Scope = US (4 of the 5 spokes are US-basis; only Reach is worldwide, disclosed in the Sources # clause) - the old global-panel phrase mismatched the chart's dominant geography. lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-C-09", ["CL-006 viewer panel (distinct viewers), not census"], freshness, ["title_metrics_daily", "categorization_title_x_platform", "categorization_imdb_genres", "metrics_geographic_breadth_daily"], f"top {len(df)} parent companies with 50+ cataloged titles · Demand Score basis (US) · Reach spoke only: worldwide viewing")) lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1480, height=1080) 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-C-09", intended_lesson_from_spec="Parent breadth-depth radar (labeled + scored, sorted)", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": "5-axis radar, scored"}, row_count=len(df), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)}