SPB Git

Repository Public

generate_fixtures.py 24.3 KB 468 lines python

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
Files main
Menu

Theme