"""harness/transforms.py — governed ANALYTICS TRANSFORMS over query results (OM-4). The model never touches SQL or raw math: it requests named transforms on a result_id (tools.transform_result) and the platform computes them here, deterministically, over the exact rows the governed query returned. The chain is RECORDED on the derived result ("transforms"), so a saved view replays query -> transforms at render time (harness/views.run_view) and stays live. This is the AIOS Analyst's TABLE-CALCULATION + ANALYTICS library — the open-source Tableau-class capability set (Tableau table calcs + Analytics pane, Power BI DAX quick measures, pandas/polars window ops, scipy/statsmodels stats) mapped onto ONE governed dispatcher. Grounding brief: AutoWiki/research/analytics-tools.md. Design laws (2026-07-18): 1. ONE tool, many ops. Every capability is a pure function rows->rows (or rows->summary) in the OPS registry, dispatched through tools.transform_result — so the small model's prompt carries ONE tool + a skill file, never 50 signatures. Ops CHAIN. 2. ADDITIVITY IS LAW (no-unverifiable-aggregates). Additivity comes from the metric's `agg` in the semantic model, NOT the column name: sum/count are additive; count_distinct and ratio are NOT. Accumulating ops (running_total, cum_share, share_of_total, moving_sum) REFUSE a known non-additive measure with a readable error; collapse ops (top_n's Other, add_total, pivot) land it as None, never a fabricated sum. A cumulative/rolling DISTINCT count must re-run the governed query per widening window (the ytd/rolling ops) — never sum displayed values. 3. RE-AGGREGATION belongs in the QUERY, not here. Transforms are row-wise / window / reshape. To regroup or re-aggregate, run a new run_semantic_query with a different group_by — that keeps the parity proof. pivot is a pure RESHAPE (collision -> error, never a hidden sum). 4. NO black-box models. Trend/forecast are transparent least-squares / seasonal-naive, fully hand-computable and unit-tested; we do not pull in Prophet/Merlion/k-means (our own AVOID briefs). Every op has a hand-computed unit test (_demo_analytics.py) — that suite is the ship gate, not the provider-flaky eval gate. 5. Reference/trend/forecast LINES are delivered as ADDED COLUMNS and drawn with the existing combo/line chart kinds — the renderer stays untouched. """ import ast import datetime as dt import math import core.periods as PERIODS import harness.semantic as SEM # ================================================================== additivity (law #2) # Column-name suffixes that are inherently non-additive regardless of the base metric (ratios, # indices, standardized/derived series that must never be re-summed). _NON_ADDITIVE_SUFFIXES = ( "_pct", "_ratio", "_share", "_share_pct", "_yoy_pct", "_cum_pct", "_pct_change", "_pct_of_max", "_rank_pct", "_zscore", "_norm", "_idx", "_index100", "_wavg", "_running_avg", "_ref", "_band_lo", "_band_hi", "_trend", "_ytd", ) _NON_ADDITIVE_NAMES = {"aov", "margin_pct"} _INHERIT_SUFFIXES = ("_ly", "_delta") # _ly / _delta inherit 's additivity def _metric_agg(key): try: m = SEM.metrics().get(key) except Exception: m = None return m.get("agg") if m else None def _base_metric(col): base = col for suf in _INHERIT_SUFFIXES: if base.endswith(suf): return base[: -len(suf)] return base def known_non_additive(col): """True when we KNOW a column must not be summed across rows/groups: a non-additive suffix, a registered ratio/count_distinct metric, or a ratio/count_distinct-derived _ly/_delta. Unknown numeric columns default to additive (we only block what we can prove wrong).""" if col.endswith(_NON_ADDITIVE_SUFFIXES) or col.endswith("_z") or col in _NON_ADDITIVE_NAMES: return True return _metric_agg(_base_metric(col)) in ("count_distinct", "ratio") def _is_additive(col): return not known_non_additive(col) # ================================================================== small helpers def _numeric(v): return isinstance(v, (int, float)) and not isinstance(v, bool) def _num_cols(rows): return [k for k in (rows[0] if rows else {}) if any(_numeric(r.get(k)) for r in rows)] def _text_cols(rows): nums = set(_num_cols(rows)) return [k for k in (rows[0] if rows else {}) if k not in nums] def _need_col(rows, col, what): if not rows: raise SEM.ModelError("cannot transform an empty result") if col not in rows[0]: raise SEM.ModelError(f"{what}={col!r} not in result columns {sorted(rows[0])}") def _need_additive(rows, of, op): _need_col(rows, of, "of") if known_non_additive(of): raise SEM.ModelError( f"{op} sums {of!r} across rows, but {of!r} is a distinct-count or ratio measure and is " f"NOT additive; summing it would over-count. For a cumulative/rolling distinct count " f"use the 'ytd' or 'rolling' op (they re-run the query per window); otherwise pick an " f"additive measure (revenue, units, margin, orders, cogs).") def _vals(rows, col): return [r.get(col) for r in rows if _numeric(r.get(col))] def _mean(v): return (sum(v) / len(v)) if v else None def _median(v): s = sorted(v) n = len(s) if not n: return None return s[n // 2] if n % 2 else (s[n // 2 - 1] + s[n // 2]) / 2.0 def _std(v): """Sample standard deviation (ddof=1); None for n<2.""" n = len(v) if n < 2: return None m = sum(v) / n return math.sqrt(sum((x - m) ** 2 for x in v) / (n - 1)) def _percentile(v, p): """Linear-interpolation percentile, p in [0,100] (the numpy 'linear' method).""" s = sorted(v) n = len(s) if not n: return None if n == 1: return float(s[0]) k = (n - 1) * (p / 100.0) lo = math.floor(k) hi = math.ceil(k) if lo == hi: return float(s[int(k)]) return s[lo] + (s[hi] - s[lo]) * (k - lo) # ================================================================== ordering / rank def sort_rows(rows, by, direction="desc"): """Sort rows by a column, Nones last either way; works for numeric and text columns.""" _need_col(rows, by, "by") present = [r for r in rows if r.get(by) is not None] absent = [r for r in rows if r.get(by) is None] return sorted(present, key=lambda r: r[by], reverse=(direction != "asc")) + absent def head(rows, n=20): """Keep the FIRST n rows in the current order — pairs with sort for 'most negative first' asks, where top_n (largest-by-value) is the wrong shape.""" return rows[: max(1, min(int(n or 20), 500))] def bottom_n(rows, by, n=10): """The N SMALLEST rows by `by` (sort ascending + take N) — Tableau's 'Bottom N'.""" _need_col(rows, by, "by") return sort_rows(rows, by, "asc")[: max(1, min(int(n or 10), 500))] def rank_rows(rows, by, direction="desc", out="rank"): """Sort by `by` and add a 1-based rank column (ties keep row order — 'first' method).""" ordered = sort_rows(rows, by, direction) return [{**r, out: i + 1} for i, r in enumerate(ordered)] def rank_pct(rows, by, direction="desc", out="rank_pct"): """Percentile rank (0-100): position among the rows after sorting by `by`. Top row = 100 (desc) — 'this row beats X% of the rest'.""" ordered = sort_rows(rows, by, direction) n = len([r for r in ordered if r.get(by) is not None]) res = [] for i, r in enumerate(ordered): pr = (100.0 * (n - 1 - i) / (n - 1)) if (n > 1 and r.get(by) is not None) else ( 100.0 if r.get(by) is not None else None) res.append({**r, out: pr}) return res def ntile(rows, by, tiles=4, direction="asc", out="ntile"): """Assign each row to one of `tiles` equal-count buckets by `by` (quartile=4, decile=10). direction='asc' -> tile 1 is the smallest values (Tableau/pandas qcut convention).""" _need_col(rows, by, "by") tiles = max(2, min(int(tiles or 4), 100)) ordered = sort_rows(rows, by, direction) present = [r for r in ordered if r.get(by) is not None] n = len(present) res = [] for i, r in enumerate(ordered): if r.get(by) is None: res.append({**r, out: None}) else: res.append({**r, out: min(tiles, int(i * tiles / n) + 1)}) return res # ================================================================== top-N / composition def top_n(rows, by, n=10, other=True, other_label="Other"): """Keep the N largest rows by `by`; the rest collapse into ONE labelled bucket (additive columns summed, non-additive columns None — never a fake average). other=False just truncates, and the caller must surface the cut (the tool notes it).""" _need_col(rows, by, "by") n = max(1, min(int(n or 10), 500)) ordered = sort_rows(rows, by, "desc") head_rows, tail = ordered[:n], ordered[n:] if not tail or not other: return head_rows bucket = _collapse(rows, tail, f"{other_label} ({len(tail)})") return head_rows + [bucket] def add_total(rows, label="Total"): """Append a grand-total row: additive columns summed, non-additive columns None (honest), the first text column = `label`. Same discipline as make_table's totals row, but in-data so a chart can show it.""" if not rows: raise SEM.ModelError("cannot total an empty result") return rows + [_collapse(rows, rows, label)] def _collapse(all_rows, subset, label): bucket = {} for k in all_rows[0]: vals = [t.get(k) for t in subset if _numeric(t.get(k))] if vals and _is_additive(k): bucket[k] = sum(vals) elif vals: bucket[k] = None else: bucket[k] = "" texts = _text_cols(all_rows) if texts: bucket[texts[0]] = label return bucket # ================================================================== part-to-whole / cumulative def share_of_total(rows, of, out=None): """Add `_share_pct` (0-100): each row's share of the column total across THESE rows.""" _need_additive(rows, of, "share_of_total") out = out or f"{of}_share_pct" total = sum(r.get(of) or 0 for r in rows) return [{**r, out: (100.0 * (r.get(of) or 0) / total) if total else None} for r in rows] def cum_share(rows, of, out=None): """Pareto prep: sort desc by `of`, add cumulative share % (0-100). The 'top X carry Y%' read.""" _need_additive(rows, of, "cum_share") out = out or f"{of}_cum_pct" ordered = sort_rows(rows, of, "desc") total = sum(r.get(of) or 0 for r in ordered) run, res = 0.0, [] for r in ordered: run += r.get(of) or 0 res.append({**r, out: (100.0 * run / total) if total else None}) return res # ================================================================== running / moving (window) def running_total(rows, of, out=None): """Add `_running`: cumulative sum in the rows' current order (sort first if needed).""" _need_additive(rows, of, "running_total") out = out or f"{of}_running" run, res = 0.0, [] for r in rows: run += r.get(of) or 0 res.append({**r, out: run}) return res def running_avg(rows, of, out=None): """Add `_running_avg`: expanding (cumulative) mean in row order — a smoothing, valid on any numeric column (it never claims a total).""" _need_col(rows, of, "of") out = out or f"{of}_running_avg" tot, cnt, res = 0.0, 0, [] for r in rows: v = r.get(of) if _numeric(v): tot += v cnt += 1 res.append({**r, out: (tot / cnt) if cnt else None}) return res def running_extreme(rows, of, kind="max", out=None): """Add `_running_max` / `_running_min`: the cumulative max/min so far, in row order.""" _need_col(rows, of, "of") out = out or f"{of}_running_{kind}" best, res = None, [] for r in rows: v = r.get(of) if _numeric(v): best = v if best is None else (max(best, v) if kind == "max" else min(best, v)) res.append({**r, out: best}) return res def moving_average(rows, of, window=3, out=None): """Add `_ma`: trailing moving average in row order; the first window-1 rows get None (a partial-window average reads as a level change and lies). A smoothing — any numeric.""" _need_col(rows, of, "of") window = max(2, min(int(window or 3), 24)) out = out or f"{of}_ma{window}" vals = [r.get(of) or 0 for r in rows] res = [] for i, r in enumerate(rows): ma = sum(vals[i - window + 1:i + 1]) / window if i >= window - 1 else None res.append({**r, out: ma}) return res def moving_sum(rows, of, window=3, out=None): """Add `_msum`: trailing moving SUM (additive measures only) — the first window-1 rows get None.""" _need_additive(rows, of, "moving_sum") window = max(2, min(int(window or 3), 24)) out = out or f"{of}_msum{window}" vals = [r.get(of) or 0 for r in rows] res = [] for i, r in enumerate(rows): ms = sum(vals[i - window + 1:i + 1]) if i >= window - 1 else None res.append({**r, out: ms}) return res def moving_median(rows, of, window=3, out=None): """Add `_mmed`: trailing moving MEDIAN (robust smoothing; outlier-resistant).""" _need_col(rows, of, "of") window = max(2, min(int(window or 3), 24)) out = out or f"{of}_mmed{window}" res = [] for i, r in enumerate(rows): if i >= window - 1: win = [rows[j].get(of) for j in range(i - window + 1, i + 1) if _numeric(rows[j].get(of))] res.append({**r, out: _median(win) if win else None}) else: res.append({**r, out: None}) return res def rolling_std(rows, of, window=3, out=None): """Add `_rstd`: trailing-window SAMPLE standard deviation — demand/sales VOLATILITY over time (feeds control-limit / safety-stock work). First window-1 rows None.""" _need_col(rows, of, "of") window = max(2, min(int(window or 3), 24)) out = out or f"{of}_rstd{window}" res = [] for i, r in enumerate(rows): if i >= window - 1: win = [rows[j].get(of) for j in range(i - window + 1, i + 1) if _numeric(rows[j].get(of))] res.append({**r, out: _std(win)}) else: res.append({**r, out: None}) return res def running_count(rows, out="running_count"): """Add `running_count`: cumulative 1-based ROW count in the current order (cumulative # of orders/SKUs/whatever the rows are). Counts rows — always safe (never a distinct-measure sum).""" return [{**r, out: i + 1} for i, r in enumerate(rows)] # ================================================================== period-over-period (row-wise) def diff(rows, of, out=None): """Add `_diff`: value minus the previous row's value (first row None). Row-wise — safe on any numeric column (period-over-period change, incl. of a ratio).""" _need_col(rows, of, "of") out = out or f"{of}_diff" res, prev = [], None for r in rows: v = r.get(of) res.append({**r, out: (v - prev) if _numeric(v) and _numeric(prev) else None}) prev = v return res def pct_change(rows, of, out=None): """Add `_pct_change` (0-100 signed): percent change from the previous row. prev=0 -> None.""" _need_col(rows, of, "of") out = out or f"{of}_pct_change" res, prev = [], None for r in rows: v = r.get(of) pc = (100.0 * (v - prev) / abs(prev)) if _numeric(v) and _numeric(prev) and prev else None res.append({**r, out: pc}) prev = v return res def lag(rows, of, k=1, out=None): """Add `_lag`: the value from k rows earlier (Tableau LOOKUP / pandas shift).""" _need_col(rows, of, "of") k = max(1, min(int(k or 1), 100)) out = out or f"{of}_lag{k}" vals = [r.get(of) for r in rows] return [{**r, out: (vals[i - k] if i - k >= 0 else None)} for i, r in enumerate(rows)] def lead(rows, of, k=1, out=None): """Add `_lead`: the value from k rows later.""" _need_col(rows, of, "of") k = max(1, min(int(k or 1), 100)) out = out or f"{of}_lead{k}" vals = [r.get(of) for r in rows] n = len(rows) return [{**r, out: (vals[i + k] if i + k < n else None)} for i, r in enumerate(rows)] def diff_from_first(rows, of, out=None): """Add `_vs_first`: value minus the FIRST row's value (Tableau 'difference from first').""" _need_col(rows, of, "of") out = out or f"{of}_vs_first" base = next((r.get(of) for r in rows if _numeric(r.get(of))), None) return [{**r, out: (r.get(of) - base) if _numeric(r.get(of)) and _numeric(base) else None} for r in rows] def index_to_100(rows, of, out=None): """Add `_idx`: rebase the series to 100 at the first value (index-to-100 — compare shapes of series at different levels). first=0 -> None.""" _need_col(rows, of, "of") out = out or f"{of}_idx" base = next((r.get(of) for r in rows if _numeric(r.get(of))), None) return [{**r, out: (100.0 * r.get(of) / base) if _numeric(r.get(of)) and base else None} for r in rows] def percent_of_max(rows, of, out=None): """Add `_pct_of_max` (0-100): each row as a % of the largest value ('how far below best').""" _need_col(rows, of, "of") out = out or f"{of}_pct_of_max" vals = _vals(rows, of) mx = max(vals) if vals else None return [{**r, out: (100.0 * r.get(of) / mx) if _numeric(r.get(of)) and mx else None} for r in rows] def compare(rows, a, b, how="diff", out=None): """Column-wise comparison of two EXISTING columns per row (e.g. revenue vs revenue_ly already in the result): how='diff' (a-b), 'pct' (100*(a-b)/|b|), or 'ratio' (a/b). Distinct from `diff` (which is cross-row on ONE column). Safe on any columns; b=0 -> None for pct/ratio.""" _need_col(rows, a, "a") _need_col(rows, b, "b") if how not in ("diff", "pct", "ratio"): raise SEM.ModelError("how must be diff|pct|ratio") out = out or f"{a}_vs_{b}_{how}" def _cmp(x, y): if not (_numeric(x) and _numeric(y)): return None if how == "diff": return x - y if not y: return None return (100.0 * (x - y) / abs(y)) if how == "pct" else (x / y) return [{**r, out: _cmp(r.get(a), r.get(b))} for r in rows] # ================================================================== distribution / stats def bin_values(rows, of, bins=10): """Equal-width histogram buckets over `of` across these rows -> one row per bucket: {bucket, bucket_lo, bucket_hi, count, _sum}. Empty buckets kept (an honest gap).""" _need_col(rows, of, "of") bins = max(2, min(int(bins or 10), 50)) vals = _vals(rows, of) if not vals: raise SEM.ModelError(f"no numeric values in {of!r} to bin") lo, hi = min(vals), max(vals) if lo == hi: return [{"bucket": f"{lo:,.4g}", "bucket_lo": lo, "bucket_hi": hi, "count": len(vals), f"{of}_sum": sum(vals)}] width = (hi - lo) / bins out = [] for i in range(bins): b_lo, b_hi = lo + i * width, lo + (i + 1) * width hit = [v for v in vals if (b_lo <= v < b_hi) or (i == bins - 1 and v == hi)] out.append({"bucket": f"{b_lo:,.4g} to {b_hi:,.4g}", "bucket_lo": b_lo, "bucket_hi": b_hi, "count": len(hit), f"{of}_sum": sum(hit)}) return out def describe(rows, of): """Summary statistics of `of` -> ONE row: count, mean, median, std, min, p25, p75, max (+ sum when the measure is additive). The pandas .describe() of a column.""" _need_col(rows, of, "of") v = _vals(rows, of) if not v: raise SEM.ModelError(f"no numeric values in {of!r} to describe") row = {"stat_of": of, "count": len(v), "mean": _mean(v), "median": _median(v), "std": _std(v), "min": min(v), "p25": _percentile(v, 25), "p75": _percentile(v, 75), "max": max(v)} if _is_additive(of): row["sum"] = sum(v) return [row] def zscore(rows, of, out=None): """Add `_zscore`: standardized value (v-mean)/std (sample std). Constant column -> None.""" _need_col(rows, of, "of") out = out or f"{of}_zscore" v = _vals(rows, of) m, s = _mean(v), _std(v) return [{**r, out: ((r.get(of) - m) / s if _numeric(r.get(of)) and s else None)} for r in rows] def outliers(rows, of, method="zscore", k=None, out=None): """Add `_outlier` (bool): flag statistical outliers. method='zscore' (|z|>k, default 3) or 'iqr' (outside [Q1-k*IQR, Q3+k*IQR], default k=1.5 — Tukey's fences).""" _need_col(rows, of, "of") out = out or f"{of}_outlier" v = _vals(rows, of) if method == "iqr": k = 1.5 if k is None else float(k) q1, q3 = _percentile(v, 25), _percentile(v, 75) iqr = (q3 - q1) if (q1 is not None and q3 is not None) else None lo = (q1 - k * iqr) if iqr is not None else None hi = (q3 + k * iqr) if iqr is not None else None return [{**r, out: (bool(r.get(of) < lo or r.get(of) > hi) if _numeric(r.get(of)) and lo is not None else None)} for r in rows] k = 3.0 if k is None else float(k) m, s = _mean(v), _std(v) return [{**r, out: (abs((r.get(of) - m) / s) > k if _numeric(r.get(of)) and s else None)} for r in rows] def winsorize(rows, of, p=5, out=None): """Add `_winsor`: `of` clipped to its [p, 100-p] percentiles (tame outliers before a mean or chart without dropping rows).""" _need_col(rows, of, "of") p = max(0.0, min(float(p or 5), 49.0)) out = out or f"{of}_winsor" v = _vals(rows, of) lo, hi = _percentile(v, p), _percentile(v, 100 - p) return [{**r, out: (min(max(r.get(of), lo), hi) if _numeric(r.get(of)) else None)} for r in rows] def clip(rows, of, lo=None, hi=None, out=None): """Add `_clip`: `of` clamped to [lo, hi] (either bound optional).""" _need_col(rows, of, "of") if lo is None and hi is None: raise SEM.ModelError("clip needs lo and/or hi") out = out or f"{of}_clip" def _c(x): if not _numeric(x): return None if lo is not None: x = max(x, lo) if hi is not None: x = min(x, hi) return x return [{**r, out: _c(r.get(of))} for r in rows] def normalize(rows, of, out=None): """Add `_norm` (0-1): min-max scale of `of`. Constant column -> 0.0 for all.""" _need_col(rows, of, "of") out = out or f"{of}_norm" v = _vals(rows, of) lo, hi = (min(v), max(v)) if v else (None, None) span = (hi - lo) if (lo is not None) else None return [{**r, out: ((r.get(of) - lo) / span if span else 0.0) if _numeric(r.get(of)) else None} for r in rows] def correlate(rows, x, y): """Pearson correlation between two columns -> ONE row {x, y, pearson_r, n}. r in [-1,1]; 'do these two measures move together?'. Zero-variance column -> r None.""" _need_col(rows, x, "x") _need_col(rows, y, "y") pairs = [(r[x], r[y]) for r in rows if _numeric(r.get(x)) and _numeric(r.get(y))] n = len(pairs) if n < 2: return [{"x": x, "y": y, "pearson_r": None, "n": n}] xs, ys = [p[0] for p in pairs], [p[1] for p in pairs] mx, my = _mean(xs), _mean(ys) cov = sum((a - mx) * (b - my) for a, b in pairs) sx = math.sqrt(sum((a - mx) ** 2 for a in xs)) sy = math.sqrt(sum((b - my) ** 2 for b in ys)) r = (cov / (sx * sy)) if (sx and sy) else None return [{"x": x, "y": y, "pearson_r": r, "n": n}] def weighted_average(rows, of, weight): """Weighted mean of `of` by `weight` -> ONE row {_wavg, weight_col, n}. The correct way to average a per-unit figure (e.g. price weighted by units) — never a mean of means.""" _need_col(rows, of, "of") _need_col(rows, weight, "weight") num = sum((r[of] * r[weight]) for r in rows if _numeric(r.get(of)) and _numeric(r.get(weight))) den = sum(r[weight] for r in rows if _numeric(r.get(of)) and _numeric(r.get(weight))) return [{f"{of}_wavg": (num / den) if den else None, "weight_col": weight, "n": sum(1 for r in rows if _numeric(r.get(of)) and _numeric(r.get(weight)))}] def safe_ratio(rows, numerator, denominator, out="ratio"): """Add a row-wise ratio `numerator/denominator` (denominator 0 -> None). Build an ad-hoc rate the semantic layer doesn't predefine, correctly guarded.""" _need_col(rows, numerator, "numerator") _need_col(rows, denominator, "denominator") return [{**r, out: ((r[numerator] / r[denominator]) if _numeric(r.get(numerator)) and r.get(denominator) else None)} for r in rows] def product(rows, a, b, out="product"): """Add a row-wise product `a*b` (e.g. price * quantity).""" _need_col(rows, a, "a") _need_col(rows, b, "b") return [{**r, out: (r[a] * r[b] if _numeric(r.get(a)) and _numeric(r.get(b)) else None)} for r in rows] # ================================================================== business templates def abc_classify(rows, of, a=80, b=95): """Pareto ABC classification: sort desc by `of`, add `_cum_pct` and `abc_class` (A = the vital few up to a% of the total, B up to b%, C the long tail). The classic 80/20 inventory / customer / SKU segmentation.""" _need_additive(rows, of, "abc_classify") a, b = float(a), float(b) ordered = sort_rows(rows, of, "desc") total = sum(r.get(of) or 0 for r in ordered) run, res = 0.0, [] for r in ordered: run += r.get(of) or 0 cum = (100.0 * run / total) if total else None cls = None if cum is None else ("A" if cum <= a else ("B" if cum <= b else "C")) res.append({**r, f"{of}_cum_pct": cum, "abc_class": cls}) return res def concentration(rows, of): """Concentration statistics of `of` across these rows -> ONE row: HHI (Herfindahl-Hirschman Index, 0-10000), Gini (0-1), and top-1/5/10 share %. 'How concentrated is the book?'.""" _need_additive(rows, of, "concentration") vals = [r.get(of) or 0 for r in rows if _numeric(r.get(of))] vals = [v for v in vals if v > 0] n = len(vals) total = sum(vals) if not total: return [{"of": of, "n": n, "hhi": None, "gini": None, "top1_share_pct": None, "top5_share_pct": None, "top10_share_pct": None}] shares = [v / total for v in vals] hhi = sum(s * s for s in shares) * 10000.0 asc = sorted(vals) gini = (2.0 * sum((i + 1) * x for i, x in enumerate(asc))) / (n * total) - (n + 1.0) / n desc = sorted(vals, reverse=True) def topk(k): return 100.0 * sum(desc[:k]) / total return [{"of": of, "n": n, "hhi": hhi, "gini": gini, "top1_share_pct": topk(1), "top5_share_pct": topk(5), "top10_share_pct": topk(10)}] def contribution_to_change(rows, of): """Given a `_delta` column (run yoy first), add `_delta_share_pct`: each row's share of the TOTAL change (who drove the movement — the bridge / contribution decomposition).""" delta = f"{of}_delta" _need_col(rows, delta, "of (expected _delta from yoy)") total = sum(r.get(delta) or 0 for r in rows) return [{**r, f"{of}_delta_share_pct": (100.0 * (r.get(delta) or 0) / total) if total else None} for r in rows] def rfm(rows, recency, frequency, monetary, tiles=5): """RFM scoring: quintile-score each customer on Recency (LOWER days = better), Frequency and Monetary (higher = better), 1..tiles. Adds r_score/f_score/m_score, rfm_cell ('545'), rfm_score (sum) and rfm_segment (Champions / Loyal / Potential / At Risk / Lost / Others).""" for c, nm in ((recency, "recency"), (frequency, "frequency"), (monetary, "monetary")): _need_col(rows, c, nm) tiles = max(2, min(int(tiles or 5), 10)) def _score(col, reverse): # reverse=True -> smaller value scores higher (recency). Bucket by sorted position. ordered = sort_rows(rows, col, "asc") present = [r for r in ordered if r.get(col) is not None] n = len(present) sc = {} for i, r in enumerate(ordered): if r.get(col) is None: sc[id(r)] = None else: t = min(tiles, int(i * tiles / n) + 1) sc[id(r)] = (tiles + 1 - t) if reverse else t return sc rs, fs, ms = _score(recency, True), _score(frequency, False), _score(monetary, False) out = [] for r in rows: rr, ff, mm = rs[id(r)], fs[id(r)], ms[id(r)] seg = _rfm_segment(rr, ff, mm, tiles) out.append({**r, "r_score": rr, "f_score": ff, "m_score": mm, "rfm_cell": (f"{rr}{ff}{mm}" if None not in (rr, ff, mm) else None), "rfm_score": (rr + ff + mm if None not in (rr, ff, mm) else None), "rfm_segment": seg}) return out def _rfm_segment(r, f, m, tiles): if None in (r, f, m): return None hi = tiles - 1 lo = 2 if r >= hi and f >= hi: return "Champions" if f >= hi: return "Loyal" if r >= hi: return "Recent / Promising" if r <= lo and f >= 3: return "At Risk" if r <= lo and f <= lo: return "Lost" return "Others" def funnel_rates(rows, of): """Stage conversion: over ordered stage rows carrying a count `of`, add `_step_pct` (vs the previous stage) and `_overall_pct` (vs the first stage). The funnel drop-off read.""" _need_col(rows, of, "of") first = next((r.get(of) for r in rows if _numeric(r.get(of))), None) res, prev = [], None for r in rows: v = r.get(of) step = (100.0 * v / prev) if _numeric(v) and _numeric(prev) and prev else None overall = (100.0 * v / first) if _numeric(v) and first else None res.append({**r, f"{of}_step_pct": step, f"{of}_overall_pct": overall}) prev = v return res # ================================================================== modeling (transparent) def _ols(xs, ys): """Ordinary least squares y = slope*x + intercept over paired numerics -> (slope, intercept, r2). None,None,None if degenerate.""" pts = [(x, y) for x, y in zip(xs, ys) if _numeric(x) and _numeric(y)] n = len(pts) if n < 2: return None, None, None mx = sum(p[0] for p in pts) / n my = sum(p[1] for p in pts) / n sxx = sum((p[0] - mx) ** 2 for p in pts) sxy = sum((p[0] - mx) * (p[1] - my) for p in pts) if not sxx: return None, None, None slope = sxy / sxx intercept = my - slope * mx syy = sum((p[1] - my) ** 2 for p in pts) ss_res = sum((y - (slope * x + intercept)) ** 2 for x, y in pts) r2 = (1 - ss_res / syy) if syy else None return slope, intercept, r2 def trend_line(rows, of, out=None): """Add `_trend`: the linear least-squares fitted value (a straight trend over the rows' order) — draw it over `of` with a combo/line chart. The Analytics-pane trend line.""" _need_col(rows, of, "of") out = out or f"{of}_trend" xs = list(range(len(rows))) ys = [r.get(of) for r in rows] slope, intercept, _ = _ols(xs, ys) if slope is None: return [{**r, out: None} for r in rows] return [{**r, out: slope * i + intercept} for i, r in enumerate(rows)] def regression(rows, x, y): """Linear regression of `y` on `x` -> ONE row {slope, intercept, r2, n}. 'Is there a relationship, how strong?' (r2 near 1 = tight fit).""" _need_col(rows, x, "x") _need_col(rows, y, "y") slope, intercept, r2 = _ols([r.get(x) for r in rows], [r.get(y) for r in rows]) n = sum(1 for r in rows if _numeric(r.get(x)) and _numeric(r.get(y))) return [{"x": x, "y": y, "slope": slope, "intercept": intercept, "r2": r2, "n": n}] def cagr(rows, of): """Compound annual (per-period) growth rate first->last -> ONE row {cagr_pct, periods}. ((last/first)^(1/periods) - 1) * 100. Needs first > 0.""" _need_col(rows, of, "of") vals = [r.get(of) for r in rows if _numeric(r.get(of))] if len(vals) < 2: return [{"of": of, "cagr_pct": None, "periods": max(0, len(vals) - 1)}] first, last = vals[0], vals[-1] p = len(vals) - 1 c = ((last / first) ** (1.0 / p) - 1) * 100.0 if first > 0 and last > 0 else None return [{"of": of, "cagr_pct": c, "periods": p}] def growth_rate(rows, of): """Total growth first->last -> ONE row {growth_pct, first, last}. (last-first)/|first| * 100.""" _need_col(rows, of, "of") vals = [r.get(of) for r in rows if _numeric(r.get(of))] if len(vals) < 2: return [{"of": of, "growth_pct": None, "first": (vals[0] if vals else None), "last": (vals[-1] if vals else None)}] first, last = vals[0], vals[-1] g = (100.0 * (last - first) / abs(first)) if first else None return [{"of": of, "growth_pct": g, "first": first, "last": last}] # ================================================================== reference annotations (columns) def reference_line(rows, of, stat="mean", out=None): """Add a constant column = a summary stat of `of` (mean|median|min|max), so a combo/line chart can draw the reference line. The Analytics-pane average/median/constant line.""" _need_col(rows, of, "of") out = out or f"{of}_ref" v = _vals(rows, of) val = {"mean": _mean(v), "median": _median(v), "min": (min(v) if v else None), "max": (max(v) if v else None)}.get(stat) if stat not in ("mean", "median", "min", "max"): raise SEM.ModelError("stat must be mean|median|min|max") return [{**r, out: val} for r in rows] def reference_band(rows, of, method="stddev", k=1): """Add `_band_lo`/`_band_hi` constant columns for a shaded reference band. method 'stddev' -> mean ± k·std; method 'percentile' -> the k-th and (100-k)-th percentiles.""" _need_col(rows, of, "of") v = _vals(rows, of) if method == "percentile": lo, hi = _percentile(v, float(k)), _percentile(v, 100 - float(k)) elif method == "stddev": m, s = _mean(v), _std(v) lo = (m - float(k) * s) if (m is not None and s is not None) else None hi = (m + float(k) * s) if (m is not None and s is not None) else None else: raise SEM.ModelError("method must be stddev|percentile") return [{**r, f"{of}_band_lo": lo, f"{of}_band_hi": hi} for r in rows] def target_line(rows, value, out="target"): """Add a constant `target` column = value — for a bullet chart (actual vs target) or a goal line on a combo chart.""" if not _numeric(value): raise SEM.ModelError("target_line needs a numeric value") return [{**r, out: value} for r in rows] def xmr_limits(rows, of): """Wheeler XmR (process-behaviour) control limits on `of` in row order, as constant columns: `_center` (mean), `_ucl`/`_lcl` (center ± 2.66·mR-bar, mR-bar = mean moving range), and `_signal` (bool: this point is outside the limits). The HONEST, business-native alternative to mean±k·std for spotting real signals in a noisy monthly series — the same XmR the warehouse/expenses modules use. Draw center/ucl/lcl as reference lines over `of`.""" _need_col(rows, of, "of") vals = [r.get(of) for r in rows] nums = [v for v in vals if _numeric(v)] center = _mean(nums) mr = [abs(vals[i] - vals[i - 1]) for i in range(1, len(vals)) if _numeric(vals[i]) and _numeric(vals[i - 1])] mrbar = _mean(mr) ucl = (center + 2.66 * mrbar) if (center is not None and mrbar is not None) else None lcl = (center - 2.66 * mrbar) if (center is not None and mrbar is not None) else None out = [] for r in rows: v = r.get(of) sig = (bool(v > ucl or v < lcl) if _numeric(v) and ucl is not None else None) out.append({**r, f"{of}_center": center, f"{of}_ucl": ucl, f"{of}_lcl": lcl, f"{of}_signal": sig}) return out # ================================================================== reshape (pure, no hidden agg) def pivot(rows, index, column, value): """Long -> wide: one row per `index`, one column per distinct `column` value, cell = `value`. A PURE reshape — if two source rows share an (index, column) pair it RAISES (never a hidden sum; regroup in the query instead). Missing cells are None.""" for c, nm in ((index, "index"), (column, "column"), (value, "value")): _need_col(rows, c, nm) cols, order, out_map = set(), [], {} for r in rows: idx, col = r.get(index), r.get(column) cols.add(col) if idx not in out_map: out_map[idx] = {index: idx} order.append(idx) cell = str(col) if cell in out_map[idx]: raise SEM.ModelError( f"pivot collision: {index}={idx!r} has two rows for {column}={col!r}; pivot cannot " f"aggregate (that would hide a sum); regroup the query so (index, column) is unique") out_map[idx][cell] = r.get(value) col_names = [str(c) for c in sorted(cols, key=lambda z: (z is None, z))] return [{index: out_map[idx][index], **{cn: out_map[idx].get(cn) for cn in col_names}} for idx in order] def unpivot(rows, keep, columns, var_name="metric", value_name="value"): """Wide -> long (melt): for each row, emit one output row per `columns` entry carrying the `keep` columns plus (`var_name`, `value_name`). The inverse of pivot.""" keep = keep if isinstance(keep, (list, tuple)) else [keep] columns = columns if isinstance(columns, (list, tuple)) else [columns] for c in list(keep) + list(columns): _need_col(rows, c, "column") out = [] for r in rows: base = {k: r.get(k) for k in keep} for c in columns: out.append({**base, var_name: c, value_name: r.get(c)}) return out def filter_rows(rows, col, cmp, value): """Keep rows where `col` `cmp` `value`. cmp in >, >=, <, <=, ==, !=, contains. The 'having' clause over a result (e.g. keep customers with revenue_yoy_pct < 0). (Named `cmp`, not `op`, to avoid colliding with the transform dispatch key.)""" _need_col(rows, col, "col") ops = {">": lambda a, b: a > b, ">=": lambda a, b: a >= b, "<": lambda a, b: a < b, "<=": lambda a, b: a <= b, "==": lambda a, b: a == b, "!=": lambda a, b: a != b, "contains": lambda a, b: str(b).lower() in str(a).lower()} if cmp not in ops: raise SEM.ModelError(f"cmp must be one of {sorted(ops)}") fn = ops[cmp] out = [] for r in rows: v = r.get(col) try: if cmp in (">", ">=", "<", "<=") and not _numeric(v): continue if fn(v, value): out.append(r) except TypeError: continue return out def dedupe(rows, by=None): """Keep the FIRST row per distinct key. by = a column or list of columns (default: the whole row). Distinct rows, order-preserving.""" if by is None: keys = list(rows[0]) if rows else [] else: keys = by if isinstance(by, (list, tuple)) else [by] for k in keys: _need_col(rows, k, "by") seen, out = set(), [] for r in rows: key = tuple(r.get(k) for k in keys) if key not in seen: seen.add(key) out.append(r) return out def resample(rows, grain): """Reindex a time series to a COMPLETE period spine (grain=month|week|day) from the first to the last period, inserting a row for every MISSING period with None measures (never interpolated). Run this BEFORE running_total / moving_* / diff on a sparse or filtered date spine — otherwise those window ops silently skip the gaps and lie. The single most-flagged correctness guard in the tool surveys (asfreq/reindex).""" if grain not in ("month", "week", "day"): raise SEM.ModelError("resample grain must be month|week|day") _need_col(rows, "period", "period") present = {str(r.get("period"))[:10]: r for r in rows if r.get("period") is not None} if not present: return rows other_cols = [c for c in rows[0] if c != "period"] keys = sorted(present) p = keys[0] last = keys[-1] out, guard = [], 0 while True: norm = _period_start(p, grain) if grain == "month" else str(p)[:10] if norm in present: out.append(present[norm]) else: out.append({"period": norm, **{c: None for c in other_cols}}) if norm >= last: break p = _step_period(p, grain, 1) guard += 1 if guard > 5000: # bounded (loop-library discipline) break return out # ================================================================== re-query family (widening window) def _period_start(period, grain): if grain == "month": return f"{str(period)[:7]}-01" return str(period)[:10] def _period_end(period, grain): p = str(period) if grain == "month": y, m = int(p[:4]), int(p[5:7]) ny, nm = (y + 1, 1) if m == 12 else (y, m + 1) return (dt.date(ny, nm, 1) - dt.timedelta(days=1)).isoformat() if grain == "week": return (dt.date.fromisoformat(p[:10]) + dt.timedelta(days=6)).isoformat() return p[:10] def _step_period(period, grain, n): p = str(period) if grain == "month": idx = int(p[:4]) * 12 + (int(p[5:7]) - 1) + n return f"{idx // 12:04d}-{idx % 12 + 1:02d}-01" if grain == "week": return (dt.date.fromisoformat(p[:10]) + dt.timedelta(weeks=n)).isoformat() return (dt.date.fromisoformat(p[:10]) + dt.timedelta(days=n)).isoformat() def _require_series(res, op): grain = res.get("grain") if not grain: raise SEM.ModelError(f"{op} needs a time grain (run the query with grain=month|week|day)") if res.get("group_by"): raise SEM.ModelError(f"{op} works on a SINGLE time series; drop group_by (per-group " "widening windows need one query per group)") return grain def _scalar(result, col): rows = result.get("rows") or [] return rows[0].get(col) if rows else None def ytd(res, run_query, of): """CORRECT cumulative year-to-date for ANY measure (incl. distinct counts / ratios): re-runs the governed query over [Jan 1 .. each period end] and reads `of`. Never sums displayed values (which would over-count a distinct customer count). Adds `_ytd`.""" rows = res.get("rows") or [] grain = _require_series(res, "ytd") _need_col(rows, "period", "period") _need_col(rows, of, "of") q = dict(res.get("query") or {}) ordered = sorted(rows, key=lambda r: str(r.get("period") or "")) out = [] for r in ordered: p = r["period"] wq = {**q, "grain": None, "group_by": None, "date_from": f"{str(p)[:4]}-01-01", "date_to": _period_end(p, grain)} out.append({**r, f"{of}_ytd": _scalar(run_query(wq), of)}) return out def rolling(res, run_query, of, window=3): """CORRECT trailing-window value for ANY measure (incl. distinct counts): re-runs the governed query over the trailing `window` periods and reads `of` (trailing-N distinct customers, T12M revenue, ...). Adds `_roll`.""" rows = res.get("rows") or [] grain = _require_series(res, "rolling") window = max(2, min(int(window or 3), 36)) _need_col(rows, "period", "period") _need_col(rows, of, "of") q = dict(res.get("query") or {}) ordered = sorted(rows, key=lambda r: str(r.get("period") or "")) out = [] for r in ordered: p = r["period"] start = _period_start(_step_period(p, grain, -(window - 1)), grain) wq = {**q, "grain": None, "group_by": None, "date_from": start, "date_to": _period_end(p, grain)} out.append({**r, f"{of}_roll{window}": _scalar(run_query(wq), of)}) return out def forecast(res, run_query, of, periods=3, method="linear", season=12): """Append `periods` future rows with a `_forecast` value. method='linear' extends the least-squares trend; 'seasonal_naive' repeats the value from `season` periods ago. Transparent and deterministic (no ML lib). Needs a time grain (to label future periods).""" rows = res.get("rows") or [] grain = _require_series(res, "forecast") periods = max(1, min(int(periods or 3), 24)) _need_col(rows, "period", "period") _need_col(rows, of, "of") ordered = sorted(rows, key=lambda r: str(r.get("period") or "")) hist = [r.get(of) for r in ordered] out = [{**r, f"{of}_forecast": None} for r in ordered] if out: # bridge: last actual seeds the line out[-1][f"{of}_forecast"] = ordered[-1].get(of) last_p = ordered[-1]["period"] if ordered else None if method == "seasonal_naive": season = max(1, int(season or 12)) for i in range(1, periods + 1): src = len(hist) - season + (i - 1) val = hist[src] if 0 <= src < len(hist) else None out.append({"period": _step_period(last_p, grain, i), f"{of}_forecast": val}) else: slope, intercept, _ = _ols(list(range(len(hist))), hist) for i in range(1, periods + 1): val = (slope * (len(hist) - 1 + i) + intercept) if slope is not None else None out.append({"period": _step_period(last_p, grain, i), f"{of}_forecast": val}) return out # ================================================================== yoy (re-query) def yoy_compare(res, run_query): """Same-period-last-year compare under `periods.yoy_window`: re-run the source GOVERNED query over the prior window and join each row to its prior period (day and week 364 days back, coarser grains the same calendar dates) and group dims. Adds `period_ly` on a series, `yoy_rule`, and per measure `_ly`, `_delta` ($ change) and `_yoy_pct` (0-100). Sort by `_delta` asc for 'who dropped the most'. Unmatched periods keep None: a partial-vs-full compare is never faked.""" q = dict(res.get("query") or {}) if not (q.get("date_from") and q.get("date_to")): raise SEM.ModelError("yoy needs an explicit date_from/date_to on the source query") grain = res.get("grain") try: ly_from, ly_to, rule = PERIODS.yoy_window(q["date_from"], q["date_to"], grain) except ValueError as exc: raise SEM.ModelError(str(exc)) ly_q = {**q, "date_from": ly_from, "date_to": ly_to} ly_rows = run_query(ly_q)["rows"] gb = list(res.get("group_by") or []) period_col = "period" if grain else None keys = ([period_col] if period_col else []) + gb measures = [m for m in (res.get("measures") or []) if res["rows"] and m in res["rows"][0]] def _prior_period(v): # The current row's period mapped by the same rule that chose the prior window. v = v[:10] try: return PERIODS.yoy_window(v, v, grain)[0] except ValueError: return None def _key(row, current): parts = [] for k in keys: v = str(row.get(k) or "") if k == period_col: v = (_prior_period(v) or "") if current else v[:10] parts.append(v) return tuple(parts) ly_map = {} for r in ly_rows: ly_map[_key(r, False)] = r out = [] for r in res["rows"]: prev = ly_map.get(_key(r, True), {}) row = dict(r) if period_col: row["period_ly"] = _prior_period(str(r.get(period_col) or "")) or None row["yoy_rule"] = rule for m in measures: pv = prev.get(m) row[f"{m}_ly"] = pv cur = r.get(m) row[f"{m}_delta"] = (cur - pv) if _numeric(pv) and _numeric(cur) else None row[f"{m}_yoy_pct"] = (100.0 * (cur - pv) / abs(pv) if _numeric(pv) and pv and _numeric(cur) else None) out.append(row) return out, ly_q def _yoy(res, run_query): out, _ = yoy_compare(res, run_query) return out # ================================================================== registry + replay def _op(fn, params=(), required=(), needs_query=False): return {"fn": fn, "params": set(params), "required": set(required), "needs_query": needs_query} OPS = { # ordering / rank / top-N "sort": _op(sort_rows, {"by", "direction"}, {"by"}), "head": _op(head, {"n"}), "bottom_n": _op(bottom_n, {"by", "n"}, {"by"}), "rank": _op(rank_rows, {"by", "direction", "out"}, {"by"}), "rank_pct": _op(rank_pct, {"by", "direction", "out"}, {"by"}), "ntile": _op(ntile, {"by", "tiles", "direction", "out"}, {"by"}), "top_n": _op(top_n, {"by", "n", "other", "other_label"}, {"by"}), "add_total": _op(add_total, {"label"}), # part-to-whole / cumulative "share_of_total": _op(share_of_total, {"of", "out"}, {"of"}), "cum_share": _op(cum_share, {"of", "out"}, {"of"}), # running / moving windows "running_total": _op(running_total, {"of", "out"}, {"of"}), "running_avg": _op(running_avg, {"of", "out"}, {"of"}), "running_max": _op(lambda rows, of, out=None: running_extreme(rows, of, "max", out), {"of", "out"}, {"of"}), "running_min": _op(lambda rows, of, out=None: running_extreme(rows, of, "min", out), {"of", "out"}, {"of"}), "moving_average": _op(moving_average, {"of", "window", "out"}, {"of"}), "moving_sum": _op(moving_sum, {"of", "window", "out"}, {"of"}), "moving_median": _op(moving_median, {"of", "window", "out"}, {"of"}), "rolling_std": _op(rolling_std, {"of", "window", "out"}, {"of"}), "running_count": _op(running_count, {"out"}), # period-over-period (row-wise) "diff": _op(diff, {"of", "out"}, {"of"}), "pct_change": _op(pct_change, {"of", "out"}, {"of"}), "lag": _op(lag, {"of", "k", "out"}, {"of"}), "lead": _op(lead, {"of", "k", "out"}, {"of"}), "diff_from_first": _op(diff_from_first, {"of", "out"}, {"of"}), "index_to_100": _op(index_to_100, {"of", "out"}, {"of"}), "percent_of_max": _op(percent_of_max, {"of", "out"}, {"of"}), "compare": _op(compare, {"a", "b", "how", "out"}, {"a", "b"}), # distribution / stats "bin": _op(bin_values, {"of", "bins"}, {"of"}), "describe": _op(describe, {"of"}, {"of"}), "zscore": _op(zscore, {"of", "out"}, {"of"}), "outliers": _op(outliers, {"of", "method", "k", "out"}, {"of"}), "winsorize": _op(winsorize, {"of", "p", "out"}, {"of"}), "clip": _op(clip, {"of", "lo", "hi", "out"}, {"of"}), "normalize": _op(normalize, {"of", "out"}, {"of"}), "correlate": _op(correlate, {"x", "y"}, {"x", "y"}), "weighted_average": _op(weighted_average, {"of", "weight"}, {"of", "weight"}), "safe_ratio": _op(safe_ratio, {"numerator", "denominator", "out"}, {"numerator", "denominator"}), "product": _op(product, {"a", "b", "out"}, {"a", "b"}), # business templates "abc_classify": _op(abc_classify, {"of", "a", "b"}, {"of"}), "concentration": _op(concentration, {"of"}, {"of"}), "contribution_to_change": _op(contribution_to_change, {"of"}, {"of"}), "rfm": _op(rfm, {"recency", "frequency", "monetary", "tiles"}, {"recency", "frequency", "monetary"}), "funnel_rates": _op(funnel_rates, {"of"}, {"of"}), # modeling (transparent) "trend_line": _op(trend_line, {"of", "out"}, {"of"}), "regression": _op(regression, {"x", "y"}, {"x", "y"}), "cagr": _op(cagr, {"of"}, {"of"}), "growth_rate": _op(growth_rate, {"of"}, {"of"}), # reference annotations (added columns) "reference_line": _op(reference_line, {"of", "stat", "out"}, {"of"}), "reference_band": _op(reference_band, {"of", "method", "k"}, {"of"}), "target_line": _op(target_line, {"value", "out"}, {"value"}), "xmr_limits": _op(xmr_limits, {"of"}, {"of"}), # reshape (pure) "pivot": _op(pivot, {"index", "column", "value"}, {"index", "column", "value"}), "unpivot": _op(unpivot, {"keep", "columns", "var_name", "value_name"}, {"keep", "columns"}), "filter_rows": _op(filter_rows, {"col", "cmp", "value"}, {"col", "cmp", "value"}), "dedupe": _op(dedupe, {"by"}), "resample": _op(resample, {"grain"}, {"grain"}), # re-query family (widening / trailing windows, correct for non-additive) "yoy": _op(_yoy, needs_query=True), "ytd": _op(ytd, {"of"}, {"of"}, needs_query=True), "rolling": _op(rolling, {"of", "window"}, {"of"}, needs_query=True), "forecast": _op(forecast, {"of", "periods", "method", "season"}, {"of"}, needs_query=True), } def apply(res, ops, run_query=None): """Apply an op CHAIN to a result dict; returns (rows, applied_ops). Ops are validated against the registry (whitelisted names + params only). `run_query(query_dict) -> result` powers the re-query family (yoy/ytd/rolling/forecast); both the live tool and the saved-view replay inject their own.""" if not isinstance(ops, list) or not ops: raise SEM.ModelError("transforms must be a non-empty list of {op, ...} objects") rows = list(res.get("rows") or []) applied = [] for spec in ops: if not isinstance(spec, dict) or "op" not in spec: raise SEM.ModelError(f"each transform needs an 'op' key: {spec!r}") name = spec["op"] entry = OPS.get(name) if not entry: raise SEM.ModelError(f"unknown transform {name!r} (transforms: {sorted(OPS)})") params = {k: v for k, v in spec.items() if k != "op"} bad = set(params) - entry["params"] if bad: raise SEM.ModelError(f"{name}: unknown params {sorted(bad)} " f"(allowed: {sorted(entry['params'])})") missing = entry["required"] - set(params) if missing: raise SEM.ModelError(f"{name}: missing required params {sorted(missing)}") if entry["needs_query"]: if run_query is None: raise SEM.ModelError(f"{name} is unavailable here (no query runner)") # every re-query op takes (res_with_current_rows, run_query, **params) rows = entry["fn"]({**res, "rows": rows}, run_query, **params) else: rows = entry["fn"](rows, **params) applied.append({"op": name, **params}) return rows, applied