"""SOURCE render logic for TI-PC-01 (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_PC_01(reg: dict) -> dict:
"""What's carrying each major studio - the top titles by demand behind the biggest production companies."""
chart_id = "TI-PC-01"
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),
pc AS (SELECT DISTINCT imdb_id, production_co FROM categorization_production_co WHERE production_co IS NOT NULL),
cnt AS (SELECT imdb_id, COUNT(*) AS cc FROM pc GROUP BY imdb_id),
dem AS (SELECT imdb_id, demand_score::numeric AS demand FROM title_metrics_daily JOIN latest ON file_date=fd
WHERE demand_score IS NOT NULL AND geo_code='US'
AND imdb_id NOT IN (SELECT imdb_id FROM metrics_avail_match_daily
WHERE file_date = (SELECT MAX(file_date) FROM metrics_avail_match_daily)
AND country_code='US' AND flag='ORPHAN' AND imdb_id IS NOT NULL)),
co AS (
SELECT pc.production_co, SUM(dem.demand / NULLIF(cnt.cc,0)) AS credit_demand, COUNT(DISTINCT pc.imdb_id) AS titles
FROM pc JOIN cnt USING (imdb_id) JOIN dem USING (imdb_id)
GROUP BY pc.production_co HAVING COUNT(DISTINCT pc.imdb_id) >= 5
),
top_co AS (SELECT production_co, credit_demand FROM co ORDER BY credit_demand DESC LIMIT 6),
title_rank AS (
SELECT pc.production_co, pc.imdb_id, dem.demand,
ROW_NUMBER() OVER (PARTITION BY pc.production_co ORDER BY dem.demand DESC) AS r
FROM pc JOIN top_co USING (production_co) JOIN dem USING (imdb_id)
)
SELECT tr.production_co, tc.credit_demand, tr.imdb_id, tr.demand, tr.r,
(SELECT fd FROM latest) AS freshness_date
FROM title_rank tr JOIN top_co tc USING (production_co)
WHERE tr.r <= 4 ORDER BY tc.credit_demand DESC, tr.r;
""")
q_sec = time.time() - t0
if df.empty:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No production-co data")
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"])
titles = lib.resolve_titles(conn, df["imdb_id"].tolist()); conn.close()
tmap = dict(zip(titles["imdb_id"], titles["display_title"]))
df["demand"] = df["demand"].astype(float)
df["title"] = df["imdb_id"].map(tmap).fillna(df["imdb_id"]).apply(lambda s: short_title(s, 38))
companies = df.sort_values("credit_demand", ascending=False)["production_co"].drop_duplicates().tolist()
cols = 2; rows = (len(companies) + cols - 1) // cols
fig = make_subplots(rows=rows, cols=cols, subplot_titles=[f"{c}" for c in companies],
vertical_spacing=0.13, horizontal_spacing=0.28)
for i, c in enumerate(companies):
sub = df[df["production_co"] == c].sort_values("demand", ascending=True)
rr, cc = i // cols + 1, i % cols + 1
fig.add_trace(go.Bar(x=sub["demand"], y=sub["title"], orientation="h",
marker=dict(color=lib.NAVY), showlegend=False,
text=[f" {v:,.0f}" for v in sub["demand"]], textposition="outside",
textfont=dict(size=9, color=lib.MUTED), cliponaxis=False,
hovertemplate=f"{c} · %{{y}}
demand %{{x:,.0f}}"),
row=rr, col=cc)
fig.update_xaxes(range=[0, float(sub["demand"].max()) * 1.32], row=rr, col=cc)
fig.update_xaxes(showticklabels=False, showgrid=False, zeroline=False)
fig.update_yaxes(tickfont=dict(size=10), automargin=True, showgrid=False)
# HEADLINE = a finding computed from the rendered data: how top-heavy is each studio's slate?
conc_rows = []
for c in companies:
s = df[df["production_co"] == c].nlargest(4, "demand")
conc_rows.append({"co": c, "top_title": s.iloc[0]["title"],
"conc": float(s.iloc[0]["demand"] / s["demand"].sum())})
conc = pd.DataFrame(conc_rows)
n_topheavy = int((conc["conc"] >= 0.5).sum())
verb = "gets" if n_topheavy == 1 else "get"
lead = conc.sort_values("conc", ascending=False).iloc[0]
if n_topheavy >= len(conc) / 2:
title_lesson = (f"Most majors lean on one tentpole: {n_topheavy} of {len(conc)} {verb} half their top-slate demand from a "
f"single title ({lead['top_title']} is {lead['conc']:.0%} of {lead['co']}'s)")
else:
title_lesson = (f"Major studios run on benches, not tentpoles - only {n_topheavy} of {len(conc)} {verb} half their top-slate "
f"demand from one title (the most top-heavy: {lead['co']}, {lead['conc']:.0%} from {lead['top_title']})")
what = ("For the six production companies with the most total US demand, their biggest individual titles right now "
"(bar = a title's current demand; scales differ per studio - read the printed numbers, not bar lengths across panels). "
"It's the concrete slate behind each studio's headline number.")
why = ("Studio demand is abstract; this is what it's made of. It shows whether a company rides one tentpole or a deep bench - "
"which is exactly what determines how durable that demand is and what a licensing partnership actually buys.")
fig.update_layout(**lib.layout_v2(title_lesson, what, width=1180, height=860))
fig.update_annotations(font=dict(size=12.5, color=lib.INK))
# one inline unit caption (bottom margin, under the left column) so the bare printed numbers
# are identified ON the chart face; a subplot axis title renders mispositioned under kaleido
fig.add_annotation(xref="paper", yref="paper", x=0.0, y=-0.015, xanchor="left", yanchor="top",
text="bar = the title's current US Demand Score", showarrow=False,
font=dict(size=9.5, color=lib.MUTED))
lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-PC-01",
["CL-006 viewer panel (distinct viewers), not census"], freshness,
["title_metrics_daily", "categorization_production_co"],
"studios ranked by credit-shared Demand Score; panels show each title's full demand "
"(co-produced titles split across companies only for the ranking); bars use independent scales"), why_y=-0.09)
lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1180, height=860)
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-PC-01",
intended_lesson_from_spec="Top titles behind each major studio (complement to concentration)",
rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what,
"claim_strength": "strong", "rules_applied": "top 6 studios x top 4 titles; Demand Score basis"},
row_count=len(df), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}