Repository Public
1#!/usr/bin/env python32# =============================================================================3# SP BOUCHER | SOFTWARE & RESEARCH4# ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING5# =============================================================================6#7# PROJECT METADATA8# -----------------------------------------------------------------------------9# Project : ORIX Sheet10# File : Packages/SheetData/Tests/SheetDataTests/Generators/generate_fixtures.py11# Description : Generates the SheetData test fixtures and the pandas12# reference results.13# Module : SheetDataTests14# Repository : https://git.spboucher.ai/orix-sheet15# Created : 2026-11-2116# Last Modified : 2026-11-2117#18# =============================================================================19# AUTHOR & PROFESSIONAL AFFILIATION20# =============================================================================21#22# Author : Simon-Pierre Boucher23# Position : Professor of Finance24# Institution : Université du Québec en Outaouais (UQO)25# Department : Département des sciences administratives26# Organization : SP Boucher27#28# Website : https://spboucher.ai29# Git : https://git.spboucher.ai30#31# =============================================================================32# OFFICIAL CONTACT DIRECTORY33# =============================================================================34#35# General : info@spboucher.ai36# Professional : contact@spboucher.ai37# Direct : simon@spboucher.ai38# Research : research@spboucher.ai39# Development : dev@spboucher.ai40# Support : support@spboucher.ai41# Security/Admin : admin@spboucher.ai42# Notifications : notifications@spboucher.ai43#44# Preferred Contact: support@spboucher.ai45#46# =============================================================================47# COPYRIGHT & INTELLECTUAL PROPERTY48# =============================================================================49#50# Copyright (c) 202651# Simon-Pierre Boucher52#53# All rights reserved where applicable.54#55# This file is governed by the applicable project license,56# copyright notices, institutional agreements and relevant law.57#58# Unless expressly authorized by the applicable license or59# rights holder, copying, redistribution, sublicensing,60# publication and commercial exploitation are not permitted.61#62# Existing third-party rights and notices remain unaffected.63#64# =============================================================================65# CONFIDENTIALITY & SECURITY66# =============================================================================67#68# Classification : Public69# Distribution : Public repository; use is governed by the project license70# Security Contact : admin@spboucher.ai71#72# Access to this source does not independently grant permission73# to redistribute or disclose confidential materials.74#75# Do not embed credentials, private keys, access tokens,76# certificates or confidential personal information.77#78# =============================================================================79# LICENSE & MAINTENANCE80# =============================================================================81#82# License : None specified (no license file in this repository)83# Maintainer : Simon-Pierre Boucher84# Technical Contact: dev@spboucher.ai85#86# =============================================================================87# END OF SP BOUCHER SOURCE HEADER88# =============================================================================8990"""Generates the SheetData test fixtures and the pandas reference results.9192 /opt/homebrew/bin/python3 Tests/SheetDataTests/Generators/generate_fixtures.py9394Writes Tests/SheetDataTests/Fixtures/{sales,customers,messy,...} and expected.json. The data is95synthetic and seeded: re-running reproduces the same files. Every expected value is computed with96pandas/numpy only (DuckDB is not used here), so the Swift tests compare the engine against an97independent implementation.98"""99import json, math, os, sqlite3, datetime as dt100import numpy as np101import pandas as pd102import pyarrow as pa103import pyarrow.parquet as pq104import pyarrow.feather as feather105106HERE = os.path.dirname(os.path.abspath(__file__))107OUT = os.path.join(HERE, "..", "Fixtures")108os.makedirs(OUT, exist_ok=True)109rng = np.random.default_rng(20261121)110EXPECTED = {}111112# ---------------------------------------------------------------- sales113N = 2000114regions = ["North", "South", "East", "West"]115categories = ["Tools", "Garden", "Kitchen", "Toys", "Office"]116products = ["Hammer Pro", "Saw", "Rake", "Hose pro max", "Pan", "Kettle Élite", "Robot", "Puzzle", "Stapler", "Desk Lamp", "Ciseaux «Pro»", "Wok"]117start = dt.date(2024, 1, 1)118sales = pd.DataFrame({119 "order_id": np.arange(1, N + 1),120 "region": rng.choice(regions, N, p=[0.4, 0.3, 0.2, 0.1]),121 "category": rng.choice(categories, N),122 "product": rng.choice(products, N),123 "order_date": [start + dt.timedelta(days=int(d)) for d in rng.integers(0, 730, N)],124 "quantity": rng.integers(1, 21, N).astype("float"),125 "unit_price": np.round(rng.uniform(2, 400, N), 2),126 "discount": np.round(rng.choice([0, 0.05, 0.1, 0.25], N), 2),127 "customer_id": rng.integers(1, 151, N),128 "is_online": rng.choice([True, False], N),129 "note": rng.choice(["ok", "rush, fragile", 'said "thanks"', "multi\nline", "späť"], N),130})131sales.loc[rng.choice(N, 60, replace=False), "quantity"] = np.nan132sales.loc[rng.choice(N, 150, replace=False), "discount"] = np.nan133sales.loc[rng.choice(N, 300, replace=False), "note"] = None134sales.loc[rng.choice(N, 25, replace=False), "region"] = None135sales["quantity"] = sales["quantity"].astype("Int64")136137sales.to_csv(os.path.join(OUT, "sales.csv"), index=False)138table = pa.Table.from_pandas(sales, preserve_index=False)139pq.write_table(table, os.path.join(OUT, "sales.parquet"), row_group_size=500)140j = sales.copy()141j["order_date"] = j["order_date"].astype(str)142j.to_json(os.path.join(OUT, "sales.jsonl"), orient="records", lines=True, force_ascii=False)143feather.write_feather(table.slice(0, 50), os.path.join(OUT, "sales.arrow"), compression="uncompressed")144145customers = pd.DataFrame({146 "customer_id": list(range(1, 121)) + list(range(200, 211)),147 "name": [f"Customer {i:03d}" for i in range(1, 121)] + [f"Prospect {i}" for i in range(200, 211)],148 "segment": rng.choice(["Retail", "Wholesale", "Online"], 131),149 "region": rng.choice(regions, 131),150})151customers.to_csv(os.path.join(OUT, "customers.csv"), index=False)152153db = os.path.join(OUT, "sales.sqlite")154if os.path.exists(db):155 os.remove(db)156con = sqlite3.connect(db)157con.execute("CREATE TABLE sales (order_id INTEGER PRIMARY KEY, region TEXT, category TEXT, product TEXT, order_date DATE, "158 "quantity INTEGER, unit_price REAL, discount REAL, customer_id INTEGER, is_online BOOLEAN, note TEXT)")159rows = []160for r in sales.itertuples(index=False):161 rows.append((int(r.order_id), r.region, r.category, r.product, r.order_date.isoformat(),162 None if pd.isna(r.quantity) else int(r.quantity), float(r.unit_price),163 None if pd.isna(r.discount) else float(r.discount), int(r.customer_id), 1 if r.is_online else 0, r.note))164con.executemany("INSERT INTO sales VALUES (?,?,?,?,?,?,?,?,?,?,?)", rows)165con.execute("CREATE TABLE customers (customer_id INTEGER, name TEXT, segment TEXT, region TEXT)")166con.executemany("INSERT INTO customers VALUES (?,?,?,?)", [tuple(x.item() if hasattr(x, "item") else x for x in r) for r in customers.itertuples(index=False)])167con.execute("CREATE TABLE mixed (k INTEGER, v, payload BLOB)")168con.executemany("INSERT INTO mixed VALUES (?,?,?)", [(1, 10, b"\x00\x01"), (2, 2.5, None), (3, "text", b"\xff"), (4, None, None)])169con.commit()170con.close()171172# ---------------------------------------------------------------- dialects173dialect = pd.DataFrame({174 "id": [1, 2, 3, 4, 5],175 "libellé": ["Été; chaud", "Hiver", 'Dit "bonjour"', "Ligne\nbrisée", "Fin"],176 "montant": ["1234,50", "0,75", "-12,00", "", "99,99"],177 "date": ["31/12/2024", "01/02/2025", "15/08/2023", "28/02/2024", ""],178})179dialect.to_csv(os.path.join(OUT, "dialect_semicolon.csv"), index=False, sep=";")180dialect_num = pd.DataFrame({"id": [1, 2, 3, 4, 5], "montant": [1234.5, 0.75, -12.0, None, 99.99]})181tsv = pd.DataFrame({"id": [1, 2, 3], "name": ["a b", "c,d", "e|f"], "score": [1.5, None, 3.25]})182tsv.to_csv(os.path.join(OUT, "dialect_tab.tsv"), index=False, sep="\t")183with open(os.path.join(OUT, "dialect_pipe_noheader.txt"), "w") as f:184 f.write("1|alpha|2024-01-05|10.5\n2|beta|2024-02-06|\n3|'gamma|x'|2024-03-07|30\n")185with open(os.path.join(OUT, "types.csv"), "w") as f:186 f.write("i,big,f,b,d,ts,t,zip,txt,empty\n")187 f.write("1,9007199254740993,1.5,true,2024-01-31,2024-01-31 13:45:10,08:30:00,01234,hello,\n")188 f.write("-2,12345678901,2e3,false,1999-12-31,1999-12-31 23:59:59,23:59:59,90210,NULL,\n")189 f.write("3,,-0.25,,2000-02-29,2000-02-29 00:00:00,00:00:01,00501,\"x,y\",\n")190odd = pd.DataFrame({191 "Unit Price ($)": [1.5, 2.5, 4.0],192 'naïve "quoted" col': ["a", "b", "c"],193 "日本語": [10, 20, 30],194 "select": ["x", "y", "z"],195 "x; DROP TABLE sales;--": [1, 2, 3],196})197odd.to_csv(os.path.join(OUT, "odd names.csv"), index=False)198199# ---------------------------------------------------------------- messy200M = 60201first = ["Jane", "John", "Ana", "Li", "Omar", "Zoë"]202last = ["Doe", "Smith", "García", "Wang", "Haddad"]203messy = pd.DataFrame({204 "id": np.arange(1, M + 1),205 "full_name": [f"{rng.choice(last)}, {rng.choice(first)}" for _ in range(M)],206 "code": [rng.choice(["AB-123-x", "CD-45", "EF-6789-yy", "GH-1-z"]) for _ in range(M)],207 "date_text": [rng.choice(["31/12/2024", "01/02/2025", "15/08/2023", "n/a"]) for _ in range(M)],208 "amount_text": [rng.choice(["12.5", "7", "n/a", "1e2", "abc"]) for _ in range(M)],209 "score": np.round(rng.uniform(0, 100, M), 1),210 "grp": rng.choice(["a", "b", "c"], M),211 "padded": [rng.choice([" hello ", "World", " MiXed Case"]) for _ in range(M)],212})213messy.loc[rng.choice(M, 18, replace=False), "score"] = np.nan214messy.loc[rng.choice(M, 9, replace=False), "grp"] = None215messy.loc[[0, 1], "score"] = [np.nan, 50.0] # leading NULL for fill-previous216messy.loc[M - 1, "score"] = np.nan # trailing NULL for fill-next217messy.to_csv(os.path.join(OUT, "messy.csv"), index=False)218219extra = pd.DataFrame({"order_id": [9001, 9002], "region": ["North", "Mars"], "bonus": [1.5, 2.5]})220extra.to_csv(os.path.join(OUT, "sales_extra.csv"), index=False)221222# ---------------------------------------------------------------- helpers223def canon(v):224 if v is None or v is pd.NA or v is pd.NaT:225 return None226 if isinstance(v, (bool, np.bool_)):227 return bool(v)228 if isinstance(v, (int, np.integer)):229 return float(v)230 if isinstance(v, (float, np.floating)):231 return None if math.isnan(v) else float(v)232 if isinstance(v, (pd.Timestamp, dt.datetime)):233 return v.strftime("%Y-%m-%d %H:%M:%S")234 if isinstance(v, dt.date):235 return v.isoformat()236 return str(v)237238def tab(df):239 return {"columns": [str(c) for c in df.columns], "rows": [[canon(v) for v in row] for row in df.itertuples(index=False, name=None)]}240241def put(name, value):242 EXPECTED[name] = value243244revenue = (sales["quantity"].astype("float") * sales["unit_price"] * (1 - sales["discount"].fillna(0)))245sales_r = sales.assign(revenue=revenue)246247# ---------------------------------------------------------------- basic aggregates / profile248put("sales_basic", {249 "rows": int(len(sales)),250 "sum_quantity": float(sales["quantity"].sum()),251 "mean_unit_price": float(sales["unit_price"].mean()),252 "null_quantity": int(sales["quantity"].isna().sum()),253 "null_note": int(sales["note"].isna().sum()),254 "null_region": int(sales["region"].isna().sum()),255 "distinct_customers": int(sales["customer_id"].nunique()),256 "online": int(sales["is_online"].sum()),257 "min_date": sales["order_date"].min().isoformat(),258 "max_date": sales["order_date"].max().isoformat(),259})260flt = sales_r[(sales_r["region"] == "North") & (sales_r["unit_price"] > 100)]261put("filtered_agg", tab(flt.groupby("category", as_index=False).agg(n=("order_id", "count"), qty=("quantity", "sum"), rev=("revenue", "sum"))))262prof = {}263for c in ["quantity", "unit_price", "discount", "customer_id"]:264 s = sales[c].astype("float")265 prof[c] = {"nulls": int(s.isna().sum()), "min": float(s.min()), "max": float(s.max()), "mean": float(s.mean()), "std": float(s.std()),266 "q25": float(s.quantile(0.25)), "median": float(s.median()), "q75": float(s.quantile(0.75)), "distinct": int(s.nunique())}267prof["region"] = {"nulls": int(sales["region"].isna().sum()), "min": sales["region"].dropna().min(), "max": sales["region"].dropna().max(),268 "distinct": int(sales["region"].nunique()),269 "top": [[k, int(v)] for k, v in sales["region"].value_counts().sort_index().sort_values(ascending=False, kind="stable").items()]}270put("profile", prof)271put("dialect_semicolon", tab(dialect_num))272put("preview_sorted", [float(x) for x in sales.sort_values(["region", "unit_price", "order_id"], na_position="last")["order_id"]])273274# ---------------------------------------------------------------- transforms275S = sales276put("t_filter_all", tab(S[S["region"].isin(["North", "South"]) & (S["quantity"] > 5)]))277put("t_filter_any", tab(S[(S["category"] == "Tools") | S["unit_price"].between(100, 200)]))278put("t_filter_text", tab(S[S["product"].str.lower().str.contains("pro")]))279put("t_filter_ne", tab(S[(S["quantity"] != 3).fillna(True)]))280put("t_filter_notin", tab(S[~S["region"].isin(["North", "East"])]))281put("t_filter_null", tab(S[S["note"].isna() & S["discount"].notna()]))282put("t_filter_starts", tab(S[S["product"].str.startswith("H") & S["note"].fillna("").str.endswith("e")]))283put("t_filter_regex", tab(S[S["product"].str.contains(r"^[HS]a", regex=True)]))284put("t_sort_limit", tab(S.sort_values(["unit_price", "order_id"], ascending=[False, True]).head(25)))285r = S.rename(columns={"unit_price": "price"}).drop(columns=["note", "discount"])286r = r[["region", "order_id"] + [c for c in r.columns if c not in ("region", "order_id")]]287put("t_columns", tab(r.head(200)))288put("t_select", tab(S[["product", "order_id"]].head(50)))289290m = messy291ct = m[["id"]].assign(amount=pd.to_numeric(m["amount_text"], errors="coerce"), id_text=m["id"].astype(str),292 d=pd.to_datetime(m["date_text"], format="%d/%m/%Y", errors="coerce").dt.date)293put("t_changetype", tab(ct))294parts = m["code"].str.split("-", expand=True)295put("t_split_delim", tab(m[["id"]].assign(p1=parts[0], p2=parts[1], p3=parts[2])))296put("t_split_positions", tab(m[["id"]].assign(a=m["code"].str[0:2], b=m["code"].str[2:3], c=m["code"].str[3:])))297rx = m["full_name"].str.extract(r"^([^,]+), (.+)$")298put("t_split_regex", tab(m[["id"]].assign(last=rx[0], first=rx[1])))299merged = S[["region", "category"]].apply(lambda row: " / ".join([x for x in row if x is not None and not pd.isna(x)]), axis=1)300put("t_merge", tab(S[["order_id"]].assign(rc=merged).head(300)))301302C = customers303def join_cols(df):304 return df[["order_id", "customer_id", "region", "quantity", "name", "segment", "region_c"]]305L = S[["order_id", "customer_id", "region", "quantity"]]306for how in ["inner", "left", "right", "outer"]:307 jn = L.merge(C, on="customer_id", how=how, suffixes=("", "_c"))308 put("t_join_" + ("full" if how == "outer" else how), tab(join_cols(jn)))309put("t_join_semi", tab(L[L["customer_id"].isin(C["customer_id"])]))310put("t_join_anti", tab(L[~L["customer_id"].isin(C["customer_id"])]))311312g = S.groupby(["region", "category"], dropna=False).agg(313 sum_q=("quantity", "sum"), cnt_q=("quantity", "count"), avg_p=("unit_price", "mean"), min_p=("unit_price", "min"),314 max_p=("unit_price", "max"), sd_p=("unit_price", "std"), var_p=("unit_price", "var"), med_p=("unit_price", "median"),315 dc=("customer_id", "nunique"), n=("order_id", "size")).reset_index()316put("t_groupby", tab(g))317so = S.sort_values("order_id")318put("t_groupby_firstlast", tab(so.groupby("category").agg(first_p=("product", "first"), last_p=("product", "last")).reset_index()))319put("t_groupby_global", tab(pd.DataFrame({"total": [float(S["quantity"].sum())], "n": [len(S)]})))320pv = S.dropna(subset=["region"]).pivot_table(index="region", columns="category", values="quantity", aggfunc="sum").reset_index()321pv.columns.name = None322put("t_pivot", tab(pv))323up = S[["order_id", "quantity", "unit_price"]].head(40)324put("t_unpivot", tab(up.assign(quantity=up["quantity"].astype("float")).melt(id_vars=["order_id"], value_vars=["quantity", "unit_price"], var_name="metric", value_name="val")))325put("t_dedupe_cols", tab(m.drop_duplicates(subset=["full_name"], keep="first")))326put("t_dedupe_all", tab(m[["grp", "code"]].drop_duplicates()))327put("t_fill_const", tab(m[["id"]].assign(score=m["score"].fillna(-1.0), grp=m["grp"].fillna("?"))))328put("t_fill_prev", tab(m[["id"]].assign(score=m["score"].ffill())))329put("t_fill_next", tab(m[["id"]].assign(score=m["score"].bfill())))330put("t_fill_mean", tab(m[["id"]].assign(score=m["score"].fillna(m["score"].mean()))))331put("t_fill_median", tab(m[["id"]].assign(score=m["score"].fillna(m["score"].median()))))332ms = m.sort_values(["grp", "id"], na_position="last")333put("t_fill_prev_sorted", tab(ms[["id", "grp"]].assign(score=ms["score"].ffill())))334put("t_drop_any", tab(m.dropna(subset=["score", "grp"], how="any")))335put("t_drop_all", tab(m.dropna(subset=["score", "grp"], how="all")))336put("t_drop_everything", tab(m.dropna(how="any")))337put("t_replace_whole", tab(S[["order_id"]].assign(region=S["region"].replace({"North": "N", "South": "S"})).head(300)))338put("t_replace_sub", tab(S[["order_id"]].assign(product=S["product"].str.replace("Pro", "Professional", regex=False).str.replace(" ", "_", regex=False)).head(300)))339put("t_parsedate", tab(m[["id"]].assign(date_text=pd.to_datetime(m["date_text"], format="%d/%m/%Y", errors="coerce").dt.date)))340ex = m[["id"]].assign(341 digits=m["code"].str.extract(r"-(\d+)")[0],342 before=m["code"].str.split("-").str[0],343 after=m["code"].str.split("-", n=1).str[1],344 sub=m["code"].str[1:4],345 length=m["full_name"].str.len().astype("float"),346 trimmed=m["padded"].str.strip(),347 upper=m["padded"].str.upper(),348 lower=m["padded"].str.lower())349put("t_extract", tab(ex))350put("t_calc", tab(S[["order_id"]].assign(revenue=revenue).head(400)))351app = pd.concat([S[S["region"] == "West"][["order_id", "region", "quantity"]], extra], ignore_index=True)352put("t_append", tab(app))353# multi-step: filter → calc → group → sort → limit354ms2 = sales_r[sales_r["is_online"] & sales_r["quantity"].notna()]355ms2 = ms2.groupby(["region", "category"], dropna=False).agg(rev=("revenue", "sum"), orders=("order_id", "count")).reset_index()356ms2 = ms2.sort_values(["rev", "region", "category"], ascending=[False, True, True]).head(7)357put("t_multi", tab(ms2))358359# ---------------------------------------------------------------- pivots360def key(vals):361 out = []362 for v in vals:363 c = canon(v)364 if c is None:365 out.append("(null)")366 elif isinstance(c, float):367 out.append(repr(int(c)) if c == int(c) else repr(c))368 else:369 out.append(str(c))370 return "¦".join(out)371372def cube(df, rows, cols, value, func):373 """Aggregates at every (row prefix, column prefix) level, like GROUPING SETS; keys "r¦r||c¦c"."""374 out = {}375 for i in range(len(rows) + 1):376 for jx in range(len(cols) + 1):377 by = rows[:i] + cols[:jx]378 if by:379 grouped = df.groupby(by, dropna=False)[value]380 res = func(grouped)381 for k, v in res.items():382 k = k if isinstance(k, tuple) else (k,)383 if v is None or (isinstance(v, float) and math.isnan(v)):384 continue385 out[key(k[:i]) + "||" + key(k[i:])] = float(v)386 else:387 v = func(df.groupby(lambda _: 0)[value]).iloc[0] if len(df) else None388 if v is not None and not (isinstance(v, float) and math.isnan(v)):389 out["||"] = float(v)390 return out391392F = sales_r.assign(qf=sales_r["quantity"].astype("float"), year=pd.to_datetime(sales_r["order_date"]).dt.year,393 quarter=pd.to_datetime(sales_r["order_date"]).dt.quarter, month=pd.to_datetime(sales_r["order_date"]).dt.month)394aggs = {395 "sum": lambda g: g.sum(min_count=1), "count": lambda g: g.count(), "average": lambda g: g.mean(), "min": lambda g: g.min(),396 "max": lambda g: g.max(), "stddevSample": lambda g: g.std(ddof=1), "stddevPopulation": lambda g: g.std(ddof=0),397 "varianceSample": lambda g: g.var(ddof=1), "variancePopulation": lambda g: g.var(ddof=0), "median": lambda g: g.median(),398 "distinctCount": lambda g: g.nunique(), "product": lambda g: g.prod(min_count=1),399}400pivots = {}401for name, fn in aggs.items():402 col = "customer_id" if name == "distinctCount" else ("discount1" if name == "product" else "qf")403 src = F.assign(discount1=1 + F["discount"]) if name == "product" else F404 if name == "count":405 res = cube(src, ["region"], ["category"], col, fn)406 res = {k: v for k, v in res.items()}407 else:408 res = cube(src, ["region"], ["category"], col, fn)409 # count() of an empty group is 0 in pandas only for existing groups: same as SQL410 pivots["agg_" + name] = res411pivots["two_by_two"] = cube(F, ["region", "category"], ["year", "is_online"], "revenue", aggs["sum"])412pivots["date_parts"] = cube(F, ["year", "quarter"], ["month"], "qf", aggs["sum"])413bins = F.assign(bin=np.floor(F["unit_price"] / 100) * 100)414pivots["bins"] = cube(bins, ["bin"], ["region"], "qf", aggs["sum"])415pivots["no_columns"] = cube(F, ["category", "product"], [], "revenue", aggs["average"])416pivots["no_rows"] = cube(F, [], ["region"], "qf", aggs["sum"])417inc = F[F["region"].isin(["North", "West"]) & (F["category"] != "Toys") & (F["unit_price"] >= 50)]418pivots["filtered"] = cube(inc, ["region"], ["category"], "qf", aggs["sum"])419top3 = F.groupby("product")["revenue"].sum().sort_values(ascending=False).head(3).index420pivots["top3"] = cube(F[F["product"].isin(top3)], ["product"], ["region"], "revenue", aggs["sum"])421put("top3_order", list(F[F["product"].isin(top3)].groupby("product")["revenue"].sum().sort_values(ascending=False).index))422# calculated field: sum(revenue) / sum(quantity) at every level423num = cube(F, ["region"], ["category"], "revenue", aggs["sum"])424den = cube(F.assign(q2=F["qf"]), ["region"], ["category"], "q2", aggs["sum"])425pivots["calculated"] = {k: num[k] / den[k] for k in num if k in den and den[k] != 0}426# show-as on region × category sum(qf)427base = pivots["agg_sum"]428grand = base["||"]429pivots["pct_grand"] = {k: v / grand for k, v in base.items()}430pivots["pct_row"] = {k: v / base[k.split("||")[0] + "||"] for k, v in base.items()}431pivots["pct_col"] = {k: v / base["||" + k.split("||")[1]] for k, v in base.items()}432put("pivots", pivots)433434# order-dependent show-as: year × month running total / difference along month, and sort by value435ym = F.groupby(["year", "month"])["qf"].sum()436run, diff, pdiff, dfrom = {}, {}, {}, {}437for y in sorted(F["year"].unique()):438 acc, prev = 0.0, None439 jan = float(ym[(y, 1)])440 for mth in range(1, 13):441 v = float(ym[(y, mth)])442 acc += v443 run[f"{y}||{mth}"] = acc444 if prev is not None:445 diff[f"{y}||{mth}"] = v - prev446 pdiff[f"{y}||{mth}"] = (v - prev) / abs(prev)447 if mth != 1:448 dfrom[f"{y}||{mth}"] = v - jan449 prev = v450put("showas_running", run)451put("showas_diff_prev", diff)452put("showas_pctdiff_prev", pdiff)453put("showas_diff_item", dfrom)454by_cat = F.groupby("category")["revenue"].sum().sort_values(ascending=False)455put("sort_by_value_desc", list(by_cat.index))456put("sort_label_desc", sorted(F["category"].unique(), reverse=True))457458# drill-down row sets459put("drill_cell", [float(x) for x in sorted(F[(F["region"] == "South") & (F["category"] == "Garden")]["order_id"])])460put("drill_row_total", [float(x) for x in sorted(F[F["region"] == "West"]["order_id"])])461put("drill_null_region", [float(x) for x in sorted(F[F["region"].isna() & (F["category"] == "Toys")]["order_id"])])462put("drill_date_part", [float(x) for x in sorted(F[(F["year"] == 2025) & (F["quarter"] == 2) & (F["month"] == 5)]["order_id"])])463put("drill_grand", float(len(F)))464465with open(os.path.join(OUT, "expected.json"), "w") as f:466 json.dump(EXPECTED, f, ensure_ascii=False, allow_nan=False)467print("fixtures written to", os.path.normpath(OUT), "-", len(EXPECTED), "reference sets")468