"""SOURCE render logic for TI-D-13 (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_D_13(reg: dict) -> dict:
"""What Travels - genre lollipops of the median rank gap for the SAME shows on the US vs the
global show chart (top 150 of either, latest common date). Animation carriers are excluded:
that exclusion is LOAD-BEARING for validity (it is what makes the two charts measure on the
same basis), never ship a no-exclusion variant. Direction is spelled out in words, no
reliance on +/- signs; n shown per genre; no decimals."""
chart_id = "TI-D-13"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
pool = lib.run_query(conn, """
WITH common AS (
SELECT MAX(file_date) AS fd FROM metrics_demand_rank_history
WHERE geo_code = 'WORLD' AND title_type = 'shows'
AND file_date IN (SELECT file_date FROM metrics_demand_rank_history
WHERE geo_code = 'US' AND title_type = 'shows')
), us_all AS (
SELECT imdb_id, di_rank AS us_rank FROM metrics_demand_rank_history, common
WHERE geo_code = 'US' AND title_type = 'shows' AND file_date = common.fd AND di_rank IS NOT NULL
), wd_all AS (
SELECT imdb_id, di_rank AS world_rank FROM metrics_demand_rank_history, common
WHERE geo_code = 'WORLD' AND title_type = 'shows' AND file_date = common.fd AND di_rank IS NOT NULL
)
SELECT (SELECT fd FROM common) AS fd, COALESCE(u.imdb_id, w.imdb_id) AS imdb_id,
b.primary_title, u.us_rank, w.world_rank,
EXISTS (SELECT 1 FROM categorization_imdb_genres g
WHERE g.imdb_id = COALESCE(u.imdb_id, w.imdb_id) AND g.genre = 'Animation') AS is_animation
FROM us_all u FULL OUTER JOIN wd_all w USING (imdb_id)
JOIN categorization_imdb_title_basics b ON b.imdb_id = COALESCE(u.imdb_id, w.imdb_id)
WHERE u.us_rank <= 150 OR w.world_rank <= 150;
""")
genres = lib.run_query(conn, """
SELECT DISTINCT imdb_id, genre FROM categorization_imdb_genres
WHERE genre IS NOT NULL AND genre NOT IN ('NA','N/A','Unknown','','Adult','Animation');
""")
q_sec0 = time.time() - t0
if pool.empty:
conn.close()
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No common US/WORLD board date")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
fd = pool["fd"].iloc[0]
ph = lib.run_query(conn, "SELECT degraded FROM metrics_panel_health WHERE file_date = %s;", (fd,))
panel_degraded = bool(ph["degraded"].iloc[0]) if len(ph) else False
q_sec = time.time() - t0
conn.close()
freshness = str(fd)
# Drop-and-footnote titles absent from one chart (never impute a rank).
one_sided = pool[pool["us_rank"].isna() | pool["world_rank"].isna()]
pool = pool.dropna(subset=["us_rank", "world_rank"]).copy()
n_anim = int(pool["is_animation"].astype(bool).sum())
pool = pool[~pool["is_animation"].astype(bool)].copy()
pool["us_rank"] = pool["us_rank"].astype(int); pool["world_rank"] = pool["world_rank"].astype(int)
pool["gap"] = pool["world_rank"] - pool["us_rank"] # positive = ranks higher (better) in the US
m = pool.merge(genres, on="imdb_id")
agg = (m.groupby("genre").agg(median_gap=("gap", "median"), n=("gap", "size"))
.reset_index())
agg = agg[agg["n"] >= 8].sort_values("median_gap", ascending=True).reset_index(drop=True)
if len(agg) < 4:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"],
f"Only {len(agg)} genres clear the n>=8 floor")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
# two NON-VALENT poles: PURPLE (US-leaning) vs STEEL slate (global-leaning). STEEL replaces the
# old teal #3E8A77, whose green cast risked reading as a green=good verdict on a direction chart;
# direction here is taste, not better/worse, so neither pole may carry a built-in valence.
GLOBAL_HUE = lib.STEEL
def gap_color(v):
if v >= 1:
return lib.PURPLE
if v <= -1:
return GLOBAL_HUE
return lib.MUTED
y = list(range(len(agg)))
fig = go.Figure()
for yi, r in zip(y, agg.itertuples()):
fig.add_trace(go.Scatter(x=[0, r.median_gap], y=[yi, yi], mode="lines",
line=dict(color=gap_color(r.median_gap), width=2.5), opacity=0.55,
showlegend=False, hoverinfo="skip"))
lab_pos = ["middle right" if r.median_gap >= 0 else "middle left" for r in agg.itertuples()]
# dot labels print the median EXACTLY as data.csv carries it ({:g} keeps the half-place
# medians a same-genre median can land on: 7.5 stays 7.5, never rounds to 8); hover spells
# the direction in words and the unsigned size, matching the words-not-signs rule
dir_word = ["higher in the US" if v >= 1 else ("higher globally" if v <= -1 else "about even")
for v in agg["median_gap"]]
# an about-even dot (|median gap| < 1) labels itself '~even' inline, so its grey needs no
# separate legend swatch; the directional dots keep the unsigned size
dot_text = [" ~even " if abs(v) < 1 else f" {abs(v):g} " for v in agg["median_gap"]]
fig.add_trace(go.Scatter(
x=agg["median_gap"], y=y, mode="markers+text",
marker=dict(size=13, color=[gap_color(v) for v in agg["median_gap"]], line=dict(width=1, color="white")),
text=dot_text, textposition=lab_pos,
textfont=dict(size=11, color=lib.INK), cliponaxis=False, showlegend=False,
customdata=list(zip(agg["genre"], agg["n"], dir_word, agg["median_gap"].abs())),
hovertemplate="%{customdata[0]}
median gap %{customdata[3]:g} places (%{customdata[2]})
%{customdata[1]} shows"))
fig.update_yaxes(tickmode="array", tickvals=y,
ticktext=[f"{r.genre} n={r.n}" for r in agg.itertuples()])
span = max(8.0, float(agg["median_gap"].abs().max()) * 1.45)
step = 5 if span <= 30 else 10
# ticks built FROM ZERO OUTWARD, symmetric, with the 0 tick ON the dashed divider (the old
# range(-span..span) walk produced 24,19,14,9,4 | 1,6,11,16,21 - no zero, asymmetric)
n_steps = int(span // step)
tickvals = [i * step for i in range(-n_steps, n_steps + 1)]
fig.update_xaxes(range=[-span, span], tickmode="array", tickvals=tickvals,
ticktext=[f"{abs(v)}" for v in tickvals])
lib.add_ref_line(fig, x=0)
top = agg.iloc[-1]; bot = agg.iloc[0] # most US-leaning / most global-leaning
# Headline names the cohort size on each pole inline, so the thinnest evidence base (the smaller-n
# pole) is visible right next to the number it anchors, not buried in the axis n-labels. Direction
# stays in plain words ('travels worldwide' / 'stays a US favorite'), never a +/- sign convention.
takeaway = (f"What travels: {bot['genre']} shows travel worldwide, ranking a median {abs(bot['median_gap']):g} places "
f"higher globally than at home (across {int(bot['n'])} shows), while {top['genre']} stays a US favorite, "
f"ranking {abs(top['median_gap']):g} places higher here than abroad (across {int(top['n'])} shows)")
what = ("The same top shows sit on both our US chart and our global chart; each dot is a genre's median gap "
"between those two ranks, with n the shows behind it. Genres are sorted from most global-leaning at "
"the bottom to most US-leaning at the top.")
why = ("A show that tops the US chart but stalls globally is a different asset from one the whole world ranks "
"alike, and the gap is genre-shaped before it is title-shaped. For anyone weighing what exports, this is "
"the week's map of which tastes stop at the border.")
# 'about even' is usually a lone member; fold it into the key as a grey PARENTHETICAL (not a
# third full swatch) and label the dot itself inline, so it does not earn equal legend weight
n_even = int((agg["median_gap"].abs() < 1).sum())
# Color is BACKED BY POSITION so a colorblind/low-contrast reader never depends on telling the two
# pole hues apart: the key ties each hue to the chart SIDE it always sits on (right = US, left =
# global), and the dots already sit left/right of the zero divider. Larger swatch glyphs (◆, 15px)
# widen the visual gap between the violet US pole and the slate-blue global pole.
key = (f"◆ dots to the RIGHT rank higher in the US "
f"◆ dots to the LEFT rank higher globally")
if n_even:
key += (f" (grey = about even, median gap under 1 place)")
fig.update_layout(**lib.layout_v2(takeaway, what, width=1120, height=700),
xaxis=dict(**lib.standard_xaxis("places apart on the two charts"),
range=[-span, span], tickmode="array", tickvals=tickvals,
ticktext=[f"{abs(v)}" for v in tickvals]),
yaxis=lib.standard_yaxis(showgrid=False))
lib.add_color_key(fig, key)
# Inline DIRECTION CUE co-located with the axis numbers: a one-breath reader sees the arrow words
# at the axis ends and instantly knows left = ranks higher globally, right = ranks higher in the US,
# without hunting in the axis title. Placed just under the axis line at each extreme, color-tied to
# the matching pole hue so the cue, the dots, and the legend all agree (TI-D-13 path-to-10 fix).
fig.add_annotation(x=-span, y=0, xref="x", yref="paper", xanchor="left", yanchor="top",
yshift=-22, xshift=2, showarrow=False, align="left",
text=f"← ranks higher globally",
font=dict(size=10.5))
fig.add_annotation(x=span, y=0, xref="x", yref="paper", xanchor="right", yanchor="top",
yshift=-22, xshift=-2, showarrow=False, align="right",
text=f"ranks higher in the US →",
font=dict(size=10.5))
# Example titles, rule-derived: the WIDEST single-show gap inside each pole genre. Each note is
# ANCHORED with a connector arrow to its own genre's dot (the old free-floating notes sat on the
# opposite x-side from the dot they described, so the mapping was guesswork), and each names the
# genre + 'widest gap' so a reader cannot mistake the one show for the driver of the genre median.
# The note also states the genre MEDIAN beside the exemplar's own gap: the widest show is an extreme
# (e.g. a +110 outlier next to a +14 median), so printing both makes plain the dot, not the example,
# carries the genre's claim and the example is the tail, not the typical case.
us_ex = m[m["genre"] == top["genre"]].sort_values("gap", ascending=False).head(1)
gl_ex = m[m["genre"] == bot["genre"]].sort_values("gap").head(1)
if len(us_ex):
e = us_ex.iloc[0]
fig.add_annotation(x=float(top["median_gap"]), y=len(agg) - 1, xref="x", yref="y",
showarrow=True, arrowhead=2, arrowwidth=1, arrowcolor=lib.MUTED,
ax=-16, ay=-18, xanchor="right", align="right",
text=f"widest {top['genre']} gap (an outlier, not the median): "
f"{short_title(e['primary_title'], 24)}
"
f"#{int(e['us_rank'])} US, #{int(e['world_rank'])} global, "
f"gap {int(e['gap'])} vs the {top['genre']} median of {abs(top['median_gap']):g}",
font=dict(size=10, color=lib.MUTED))
if len(gl_ex):
e = gl_ex.iloc[0]
fig.add_annotation(x=float(bot["median_gap"]), y=0, xref="x", yref="y",
showarrow=True, arrowhead=2, arrowwidth=1, arrowcolor=lib.MUTED,
ax=16, ay=18, xanchor="left", align="left",
text=f"widest {bot['genre']} gap (an outlier, not the median): "
f"{short_title(e['primary_title'], 24)}
"
f"#{int(e['world_rank'])} global, #{int(e['us_rank'])} US, "
f"gap {abs(int(e['gap']))} vs the {bot['genre']} median of {abs(bot['median_gap']):g}",
font=dict(size=10, color=lib.MUTED))
# Footer kept to <=4 wrapped lines (spec budget): cautions phrased compactly and the
# conservative-gap note folded into the global-chart caution rather than a 5th trailing line.
cautions = ["ranks reflect weekly Demand Score (methodology: METH-03)",
"one show counts in every genre it carries",
"animation excluded so both charts measure on the same basis",
"the global chart includes US viewing, so gaps are conservative"]
# Flag the thinnest evidence base on the chart when a headline pole rests on a small cohort, so a
# reader weighs the pole medians against the count behind them (the n-labels show every cohort,
# but the pole genres carry the claim and the smallest one deserves an explicit hedge).
min_pole_n = min(int(top["n"]), int(bot["n"]))
if min_pole_n < 20:
thin_genre = top["genre"] if int(top["n"]) <= int(bot["n"]) else bot["genre"]
cautions.append(f"the {thin_genre} pole rests on {min_pole_n} shows; read its median as directional")
if len(one_sided):
cautions.append(f"{len(one_sided)} shows on only one chart were dropped, never estimated")
if panel_degraded:
cautions.append("panel volume atypical on this date; ranks use a 7-day basis")
# metric NAMED in the source label (founder terminology; matches the trust line's 'weekly
# Demand Score', never the library's near-bare 'weekly demand rankings' default)
footer = lib.assemble_footer(pc_id=reg.get("pc_id", "PC-D-13"), caution_labels=cautions,
freshness_date=freshness, sources=["PeerLogix weekly Demand Score rankings",
"categorization_imdb_genres"],
extra="top 150 shows on either chart, present on both · genres with 8+ shows")
footer = footer.replace(lib.PANEL_SCOPE, "US and global viewer panels (distinct viewers), not a census", 1)
lib.add_why_and_footer(fig, why, footer)
# data.csv ships the EXEMPLAR title rows alongside the genre medians: the on-chart example
# ranks (#US / #global) must be reconstructable from the shipped sidecar, not internal-only
csv = agg.rename(columns={"median_gap": "median_rank_gap_world_minus_us", "n": "n_shows"}).assign(row_type="genre_median")
ex_rows = []
for sub, rt in [(us_ex, "exemplar_us_pole"), (gl_ex, "exemplar_global_pole")]:
for _, r in sub.iterrows():
ex_rows.append({"row_type": rt, "genre": str(r["genre"]), "show": str(r["primary_title"]),
"us_rank": int(r["us_rank"]), "world_rank": int(r["world_rank"]),
"rank_gap_world_minus_us": int(r["gap"])})
if ex_rows:
csv = pd.concat([csv, pd.DataFrame(ex_rows)], ignore_index=True)
lib.save_csv(csv, output_dir)
lib.export_chart(fig, output_dir, width=1120, height=700)
what_plain = re.sub(r"?b>", "", what)
lesson = {"reader_title": takeaway, "reader_subtitle": what_plain, "claim_strength": "strong",
"rules_applied": (f"median (global rank minus US rank) per genre, n>=8, top-150 pool of either board "
f"on {freshness}; {n_anim} animation carriers excluded (load-bearing for "
f"comparability); direction in words, no +/- reliance; 'about even' = |median gap| < 1 "
f"(disclosed in the color key); examples = widest gap in each pole genre, shipped as "
f"data.csv exemplar rows; zero-anchored symmetric ticks")}
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["spec_file"], pc_id=reg.get("pc_id", "PC-D-13"),
intended_lesson_from_spec="Genre-shaped US-vs-global rank gaps: what travels and what stays home",
rendered_lesson=lesson, row_count=len(agg), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec,
extra={"pool_titles": len(pool) + n_anim, "animation_excluded": n_anim,
"analyzed": len(pool), "one_sided_dropped": len(one_sided),
"us_pole": {"genre": top["genre"], "median_gap": float(top["median_gap"])},
"global_pole": {"genre": bot["genre"], "median_gap": float(bot["median_gap"])}})
return {"status": "ok", "rows": len(agg)}