SPB Git

Repository Public

gen_scipy.py 33.0 KB 503 lines python

1"""Generates the scipy / numpy / mpmath / decimal reference fixtures (see common.py).23These references are independent of any spreadsheet: distributions come from scipy.stats, special4functions from scipy.special and mpmath, descriptive statistics and regressions from numpy and5statsmodels, unit conversions from scipy.constants and decimal rounding from Python's decimal module.6"""7import cmath8import itertools9import math10import random11from decimal import ROUND_DOWN, ROUND_HALF_UP, ROUND_UP, Decimal1213import mpmath14import numpy as np15import scipy.constants as K16import scipy.special as sp17import scipy.stats as st18import statsmodels.api as sm1920from common import write_fixture21from data import DUPS, GROW, POS, X, X2, Y, data_sheet2223D = data_sheet()24mpmath.mp.dps = 40252627def lit(v):28    if isinstance(v, bool):29        return "TRUE" if v else "FALSE"30    if isinstance(v, str):31        return '"' + v + '"'32    if isinstance(v, int):33        return str(v)34    return repr(float(v))353637def case(name, args, expected, tol=1e-12, abs_tol=None):38    e = float(expected)39    if not math.isfinite(e):40        return None41    c = {"f": f"={name}({','.join(lit(a) for a in args)})", "e": e, "kind": "num", "tol": tol}42    if abs_tol is not None:43        c["abs"] = abs_tol44    return c454647def M(v):48    return mpmath.mpf(v)495051def refine(f, target, x0):52    """Quantile reference at 40 digits. scipy's own quantile functions are only accurate to about53    1e-8 in some tails, so its estimate is checked against the 40-digit CDF and, when the residual is54    not negligible, polished with a root finder."""55    if x0 == 0 or not math.isfinite(x0):56        return x057    try:58        x = M(x0)59        fx = f(x) - target60        if abs(fx) <= min(abs(target), abs(1 - target)) * M(10) ** -15:61            return float(x)62        return float(mpmath.findroot(lambda v: f(v) - target, (x, x * (1 + M(10) ** -9)), tol=M(10) ** -30, maxsteps=40))63    except Exception:64        return float("nan")656667def t_two(t, df):68    return mpmath.betainc(M(df) / 2, M(1) / 2, 0, M(df) / (df + t * t), regularized=True)697071def t_inv2(p, df):72    return refine(lambda t: t_two(t, df), M(p), st.t.isf(p / 2, df))737475def t_inv(p, df):76    if p == 0.5:77        return 0.078    if p < 0.5:79        return -refine(lambda t: t_two(t, df), 2 * M(p), st.t.isf(p, df))80    return refine(lambda t: t_two(t, df), 2 * (1 - M(p)), st.t.ppf(p, df))818283def chi_cdf(x, df):84    return mpmath.gammainc(M(df) / 2, 0, x / 2, regularized=True)858687def chi_sf(x, df):88    return mpmath.gammainc(M(df) / 2, x / 2, mpmath.inf, regularized=True)899091def f_cdf(x, d1, d2):92    return mpmath.betainc(M(d1) / 2, M(d2) / 2, 0, d1 * x / (d1 * x + d2), regularized=True)939495def f_sf(x, d1, d2):96    return mpmath.betainc(M(d2) / 2, M(d1) / 2, 0, d2 / (d2 + d1 * x), regularized=True)979899def pois_pmf(k, lam):100    return float(mpmath.exp(-M(lam) + k * mpmath.log(M(lam)) - mpmath.loggamma(k + 1)))101102103def binom_pmf(k, n, p):104    return float(mpmath.binomial(n, k) * M(p) ** k * (1 - M(p)) ** (n - k))105106107def collect(items):108    return [c for c in items if c is not None]109110111def distributions():112    c = []113    zs = [-8.5, -5, -3.2, -1.96, -1, -0.3, 0, 1e-8, 0.5, 1, 1.6448536269514722, 2.5, 4, 6.5, 9]114    for z in zs:115        c += [case("NORM.S.DIST", (z, True), st.norm.cdf(z)), case("NORM.S.DIST", (z, False), st.norm.pdf(z)), case("NORMSDIST", (z,), st.norm.cdf(z)),116              case("PHI", (z,), st.norm.pdf(z)), case("GAUSS", (z,), float(mpmath.erf(mpmath.mpf(z) / mpmath.sqrt(2)) / 2), abs_tol=1e-300),117              case("NORM.DIST", (z, 1.5, 2.25, True), st.norm.cdf(z, 1.5, 2.25)), case("NORM.DIST", (z, 1.5, 2.25, False), st.norm.pdf(z, 1.5, 2.25)),118              case("NORMDIST", (z, -3, 0.5, True), st.norm.cdf(z, -3, 0.5))]119    ps = [1e-300, 1e-100, 1e-20, 1e-10, 1e-5, 0.001, 0.01, 0.02425, 0.025, 0.1, 0.25, 0.4, 0.5, 0.6, 0.75, 0.9, 0.975, 0.97575, 0.99, 0.999, 0.99999, 1 - 1e-10]120    for p in ps:121        with mpmath.workdps(400):122            ref = float(mpmath.sqrt(2) * mpmath.erfinv(2 * M(p) - 1))123        c += [case("NORM.S.INV", (p,), ref, abs_tol=1e-16), case("NORMSINV", (p,), ref, abs_tol=1e-16),124              case("NORM.INV", (p, 10, 2.5), 10 + 2.5 * ref, tol=1e-12, abs_tol=1e-13), case("NORMINV", (p, -1, 0.1), -1 + 0.1 * ref, tol=1e-12, abs_tol=1e-13),125              case("LOGNORM.INV", (p, 0.5, 0.75), math.exp(0.5 + 0.75 * ref)), case("LOGINV", (p, 0.5, 0.75), math.exp(0.5 + 0.75 * ref))]126    for x in [1e-6, 0.01, 0.5, 1, 2.5, 10, 150, 1e6]:127        for m, s in [(0, 1), (0.5, 0.75), (3.5, 1.2)]:128            d = st.lognorm(s, scale=math.exp(m))129            c += [case("LOGNORM.DIST", (x, m, s, True), d.cdf(x)), case("LOGNORM.DIST", (x, m, s, False), d.pdf(x)), case("LOGNORMDIST", (x, m, s), d.cdf(x))]130    # Student t131    for df in [1, 2, 3, 5, 10, 30, 100, 1000]:132        for x in [-40, -6, -2.5, -1, -0.1, 0, 0.3, 1, 1.96, 3.5, 8, 60]:133            c += [case("T.DIST", (x, df, True), st.t.cdf(x, df)), case("T.DIST", (x, df, False), st.t.pdf(x, df)), case("T.DIST.RT", (x, df), st.t.sf(x, df))]134            if x >= 0:135                c += [case("T.DIST.2T", (x, df), 2 * st.t.sf(x, df)), case("TDIST", (x, df, 1), st.t.sf(x, df)), case("TDIST", (x, df, 2), 2 * st.t.sf(x, df))]136        for p in [1e-12, 1e-6, 0.001, 0.025, 0.1, 0.3, 0.5, 0.7, 0.9, 0.975, 0.999, 0.999999]:137            c += [case("T.INV", (p, df), t_inv(p, df), abs_tol=1e-15), case("T.INV.2T", (p, df), t_inv2(p, df)), case("TINV", (p, df), t_inv2(p, df))]138    # chi-squared139    for df in [1, 2, 3, 4, 7, 10, 25, 100, 500]:140        for x in [1e-8, 0.01, 0.5, 1, 2, 3.84, 7.5, 18.307, 40, 120, 600]:141            c += [case("CHISQ.DIST", (x, df, True), st.chi2.cdf(x, df)), case("CHISQ.DIST", (x, df, False), st.chi2.pdf(x, df)),142                  case("CHISQ.DIST.RT", (x, df), st.chi2.sf(x, df)), case("CHIDIST", (x, df), st.chi2.sf(x, df))]143        for p in [1e-10, 0.001, 0.05, 0.3, 0.5, 0.8, 0.95, 0.999, 1 - 1e-9]:144            lo, hi = refine(lambda x: chi_cdf(x, df), M(p), st.chi2.ppf(p, df)), refine(lambda x: chi_sf(x, df), M(p), st.chi2.isf(p, df))145            c += [case("CHISQ.INV", (p, df), lo), case("CHISQ.INV.RT", (p, df), hi), case("CHIINV", (p, df), hi)]146    # F147    for d1, d2 in [(1, 1), (1, 5), (2, 2), (3, 7), (6, 4), (10, 20), (25, 3), (100, 100), (2, 500)]:148        for x in [1e-6, 0.01, 0.2, 0.5, 1, 1.5, 3, 15.2069, 50, 400]:149            c += [case("F.DIST", (x, d1, d2, True), st.f.cdf(x, d1, d2)), case("F.DIST", (x, d1, d2, False), st.f.pdf(x, d1, d2)),150                  case("F.DIST.RT", (x, d1, d2), st.f.sf(x, d1, d2)), case("FDIST", (x, d1, d2), st.f.sf(x, d1, d2))]151        for p in [1e-8, 0.001, 0.05, 0.3, 0.5, 0.8, 0.95, 0.999]:152            lo, hi = refine(lambda x: f_cdf(x, d1, d2), M(p), st.f.ppf(p, d1, d2)), refine(lambda x: f_sf(x, d1, d2), M(p), st.f.isf(p, d1, d2))153            c += [case("F.INV", (p, d1, d2), lo), case("F.INV.RT", (p, d1, d2), hi), case("FINV", (p, d1, d2), hi)]154    # exponential, gamma, beta, Weibull155    for lam in [0.1, 1, 2.5, 40]:156        for x in [0, 1e-9, 0.05, 0.5, 1, 3, 20]:157            d = st.expon(scale=1 / lam)158            c += [case("EXPON.DIST", (x, lam, True), d.cdf(x)), case("EXPON.DIST", (x, lam, False), d.pdf(x)), case("EXPONDIST", (x, lam, True), d.cdf(x))]159    for a, b in [(0.5, 1), (1, 2), (2.5, 0.5), (9, 2), (30, 1.5), (150, 0.1), (0.05, 3)]:160        d = st.gamma(a, scale=b)161        for x in [1e-9, 0.01, 0.3, 1, 2.5, 10, 18, 45, 100]:162            c += [case("GAMMA.DIST", (x, a, b, True), d.cdf(x)), case("GAMMA.DIST", (x, a, b, False), d.pdf(x)), case("GAMMADIST", (x, a, b, True), d.cdf(x))]163        for p in [1e-9, 0.001, 0.05, 0.3, 0.5, 0.8, 0.95, 0.999, 1 - 1e-9]:164            q = refine(lambda x: mpmath.gammainc(M(a), 0, x / M(b), regularized=True), M(p), d.ppf(p))165            c += [case("GAMMA.INV", (p, a, b), q), case("GAMMAINV", (p, a, b), q)]166    for a, b in [(0.5, 0.5), (1, 1), (2, 3), (8, 10), (0.3, 4), (50, 20), (1, 6), (120, 150)]:167        d = st.beta(a, b)168        for x in [1e-9, 0.001, 0.1, 0.25, 0.4, 0.5, 0.7, 0.9, 0.999]:169            c += [case("BETA.DIST", (x, a, b, True), d.cdf(x)), case("BETA.DIST", (x, a, b, False), d.pdf(x)), case("BETADIST", (x, a, b), d.cdf(x))]170            d2 = st.beta(a, b, loc=1, scale=2)171            c += [case("BETA.DIST", (1 + 2 * x, a, b, True, 1, 3), d2.cdf(1 + 2 * x)), case("BETA.DIST", (1 + 2 * x, a, b, False, 1, 3), d2.pdf(1 + 2 * x)),172                  case("BETADIST", (1 + 2 * x, a, b, 1, 3), d2.cdf(1 + 2 * x))]173        for p in [1e-9, 0.001, 0.05, 0.3, 0.5, 0.8, 0.95, 0.999]:174            q = refine(lambda x: mpmath.betainc(M(a), M(b), 0, x, regularized=True), M(p), d.ppf(p))175            c += [case("BETA.INV", (p, a, b), q), case("BETAINV", (p, a, b), q), case("BETA.INV", (p, a, b, -2, 5), -2 + 7 * q, abs_tol=1e-14)]176    for a, b in [(0.5, 1), (1, 1), (1.5, 3), (20, 100), (3, 0.2)]:177        d = st.weibull_min(a, scale=b)178        for x in [1e-6, 0.05, 0.5, 1, 2, 5, 105]:179            c += [case("WEIBULL.DIST", (x, a, b, True), d.cdf(x), abs_tol=1e-300), case("WEIBULL.DIST", (x, a, b, False), d.pdf(x), abs_tol=1e-300),180                  case("WEIBULL", (x, a, b, True), d.cdf(x), abs_tol=1e-300)]181    return collect(c)182183184def discrete():185    c = []186    for n, p in [(1, 0.5), (10, 0.5), (10, 0.1), (20, 0.9), (100, 0.3), (1000, 0.01), (1000, 0.5), (5000, 0.999), (60, 0.75), (15, 1e-6)]:187        ks = sorted(set([0, 1, 2, n // 4, n // 2, int(n * p), int(n * p) + 1, n - 1, n]))188        for k in ks:189            if k > n:190                continue191            c += [case("BINOM.DIST", (k, n, p, False), binom_pmf(k, n, p), abs_tol=1e-300), case("BINOM.DIST", (k, n, p, True), st.binom.cdf(k, n, p), abs_tol=1e-300),192                  case("BINOMDIST", (k, n, p, False), binom_pmf(k, n, p), abs_tol=1e-300)]193        for alpha in [0.001, 0.05, 0.25, 0.5, 0.75, 0.95, 0.999]:194            c += [case("BINOM.INV", (n, p, alpha), st.binom.ppf(alpha, n, p)), case("CRITBINOM", (n, p, alpha), st.binom.ppf(alpha, n, p))]195        lo, hi = max(0, int(n * p) - 2), min(n, int(n * p) + 3)196        c += [case("BINOM.DIST.RANGE", (n, p, lo, hi), float(sum(M(binom_pmf(k, n, p)) for k in range(lo, hi + 1)))),197              case("BINOM.DIST.RANGE", (n, p, lo), binom_pmf(lo, n, p), abs_tol=1e-300)]198    for mean in [0.01, 0.5, 1, 4.5, 20, 150, 2500]:199        for x in sorted(set([0, 1, 2, 5, int(mean), int(mean) + 1, int(mean * 1.5) + 3, int(mean * 3) + 10])):200            c += [case("POISSON.DIST", (x, mean, False), pois_pmf(x, mean), abs_tol=1e-300), case("POISSON.DIST", (x, mean, True), st.poisson.cdf(x, mean)),201                  case("POISSON", (x, mean, True), st.poisson.cdf(x, mean))]202    for n, m, N in [(4, 8, 20), (30, 25, 100), (10, 50, 60), (500, 300, 1000), (5, 5, 5), (1, 1, 2), (50, 5000, 100000)]:203        lo, hi = max(0, n - N + m), min(n, m)204        for s in sorted(set([lo, lo + 1, (lo + hi) // 2, hi - 1, hi])):205            if lo <= s <= hi:206                d = st.hypergeom(N, m, n)207                c += [case("HYPGEOM.DIST", (s, n, m, N, False), d.pmf(s), tol=1e-11, abs_tol=1e-300), case("HYPGEOM.DIST", (s, n, m, N, True), d.cdf(s), tol=1e-11),208                      case("HYPGEOMDIST", (s, n, m, N), d.pmf(s), tol=1e-11, abs_tol=1e-300)]209    for s, p in [(1, 0.5), (5, 0.25), (2, 0.7), (40, 0.9), (3, 0.01), (100, 0.5)]:210        for f in [0, 1, 3, 10, 45, 200]:211            d = st.nbinom(s, p)212            c += [case("NEGBINOM.DIST", (f, s, p, False), d.pmf(f), abs_tol=1e-300), case("NEGBINOM.DIST", (f, s, p, True), d.cdf(f)),213                  case("NEGBINOMDIST", (f, s, p), d.pmf(f), abs_tol=1e-300)]214    return collect(c)215216217def special():218    c = []219    for x in [0.001, 0.5, 1, 1.5, 2.5, 5, 10.3, 50, 100, 170.5, -0.5, -1.5, -3.75, -10.2, 1e-10]:220        c.append(case("GAMMA", (x,), float(mpmath.gamma(x)), tol=4e-13))221    for x in [1e-10, 0.001, 0.5, 1, 2, 4.5, 10, 100, 1e5, 1e10]:222        ref = float(mpmath.loggamma(x))223        c += [case("GAMMALN", (x,), ref, tol=2e-13, abs_tol=1e-15), case("GAMMALN.PRECISE", (x,), ref, tol=2e-13, abs_tol=1e-15)]224    for x in [-6, -2.5, -1, -0.1, -1e-10, 0, 1e-12, 0.05, 0.5, 1, 1.5, 3, 5.5, 10, 27]:225        c += [case("ERF", (x,), float(mpmath.erf(x)), abs_tol=1e-300), case("ERF.PRECISE", (x,), float(mpmath.erf(x)), abs_tol=1e-300),226              case("ERFC", (x,), float(mpmath.erfc(x)), abs_tol=1e-300), case("ERFC.PRECISE", (x,), float(mpmath.erfc(x)), abs_tol=1e-300),227              case("ERF", (x, 1.25), float(mpmath.erf(1.25) - mpmath.erf(x)), tol=1e-12, abs_tol=1e-15)]228    for n in [0, 1, 2, 3, 5, 10, 25]:229        for x in [0.001, 0.1, 0.5, 1, 1.9, 2, 2.5, 5, 9.75, 20, 50, 100]:230            c += [case("BESSELJ", (x, n), float(mpmath.besselj(n, x)), tol=2e-11, abs_tol=1e-15), case("BESSELY", (x, n), float(mpmath.bessely(n, x)), tol=2e-11, abs_tol=1e-15),231                  case("BESSELI", (x, n), float(mpmath.besseli(n, x)), tol=1e-12, abs_tol=1e-300), case("BESSELK", (x, n), float(mpmath.besselk(n, x)), tol=1e-12, abs_tol=1e-300)]232        for x in [-0.5, -3, -20]:233            c += [case("BESSELJ", (x, n), float(mpmath.besselj(n, x)), tol=2e-11, abs_tol=1e-15), case("BESSELI", (x, n), float(mpmath.besseli(n, x)), tol=1e-12, abs_tol=1e-300)]234    for x in [0.5, 1.75, 3]:235        c += [case("BESSELJ", (x, 2.9), float(mpmath.besselj(2, x)), tol=1e-11), case("BESSELK", (x, 1.5), float(mpmath.besselk(1, x)))]236    # trigonometry and logarithms at 40 digits237    fns = {238        "SIN": mpmath.sin, "COS": mpmath.cos, "TAN": mpmath.tan, "SINH": mpmath.sinh, "COSH": mpmath.cosh, "TANH": mpmath.tanh, "ASINH": mpmath.asinh,239        "ATAN": mpmath.atan, "SEC": mpmath.sec, "CSC": mpmath.csc, "COT": mpmath.cot, "ACOT": lambda x: mpmath.pi / 2 - mpmath.atan(x),240        "SECH": mpmath.sech, "CSCH": mpmath.csch, "COTH": mpmath.coth, "EXP": mpmath.exp, "DEGREES": mpmath.degrees, "RADIANS": mpmath.radians,241    }242    for name, fn in fns.items():243        for x in [-7.3, -1.2, -0.004, 0.31, 0.9, 1.7, 4.4, 12.6, 33.1]:244            c.append(case(name, (x,), float(fn(mpmath.mpf(x))), tol=4e-15 if name not in ("TAN", "SEC", "CSC", "COT") else 1e-13))245    for x in [1e-9, 0.02, 0.5, 0.999, 1.0001, 2, 17.5, 1e6, 1e300]:246        c += [case("LN", (x,), float(mpmath.log(x)), tol=4e-15, abs_tol=1e-18), case("LOG10", (x,), float(mpmath.log10(x)), tol=4e-15, abs_tol=1e-18),247              case("LOG", (x, 7), float(mpmath.log(x, 7)), tol=1e-14, abs_tol=1e-17), case("SQRT", (x,), float(mpmath.sqrt(x)), tol=4e-16),248              case("SQRTPI", (x,), float(mpmath.sqrt(mpmath.mpf(x) * mpmath.pi)), tol=1e-15), case("ACOSH", (x + 1,), float(mpmath.acosh(M(x + 1))), tol=1e-14, abs_tol=1e-17),249              case("ACOTH", (x + 1.5,), float(mpmath.acoth(M(x + 1.5))), tol=1e-13)]250    for x in [-0.999, -0.5, -1e-5, 0, 0.123, 0.75, 0.9999]:251        c += [case("ASIN", (x,), float(mpmath.asin(x)), tol=1e-15, abs_tol=1e-300), case("ACOS", (x,), float(mpmath.acos(x)), tol=1e-15),252              case("ATANH", (x,), float(mpmath.atanh(x)), tol=1e-14, abs_tol=1e-300), case("FISHER", (x,), float(mpmath.atanh(x)), tol=1e-14, abs_tol=1e-300),253              case("FISHERINV", (x * 5,), float(mpmath.tanh(mpmath.mpf(x) * 5)), tol=1e-15, abs_tol=1e-300)]254    for n in [0, 1, 5, 10, 20, 50, 100, 170]:255        c.append(case("FACT", (n,), float(mpmath.factorial(n)), tol=1e-15))256        c.append(case("FACTDOUBLE", (n,), float(mpmath.fac2(n)), tol=1e-14))257    for n, k in [(10, 3), (52, 5), (100, 50), (200, 100), (1000, 3), (40, 20), (60, 30), (1029, 514)]:258        c += [case("COMBIN", (n, k), float(mpmath.binomial(n, k)), tol=1e-13), case("COMBINA", (n, k), float(mpmath.binomial(n + k - 1, k)), tol=1e-13)]259        if k <= 60:260            c += [case("PERMUT", (n, k), float(mpmath.factorial(n) / mpmath.factorial(n - k)), tol=1e-13), case("PERMUTATIONA", (n, k), float(mpmath.mpf(n) ** k), tol=1e-13)]261    return collect(c)262263264def rounding():265    """Excel rounds the 15-significant-digit decimal reading of a number, half away from zero."""266    rnd = random.Random(20261121)267    values = [2.5, -2.5, 1.005, 0.285, 2.675, 1.115, 1.125, 0.615, 8.345, 5.015, 1.0049999999999999, 0.1 + 0.2, 1.15, 2.345, 1e-9, 0.5, 1.5, -0.5, 1234.5, 99.995,268              0.045, 1.255, 10.235, 3.0000000000000004, 0.499999999999999, 123456789.125, 4.35, 1.45, 7.0745, 0.00035, 1e21, 1e-300, 0.9999999999999999]269    for _ in range(60):270        mag = rnd.choice([1, 10, 100, 1000, 1e6, 1e-3])271        base = round(rnd.uniform(-1, 1) * mag, rnd.randint(0, 6))272        values.append(base)273        values.append(base + rnd.choice([0.5, 0.05, 0.005, 0.0005]) * rnd.choice([1, -1]))274    c = []275    for v in values:276        d15 = Decimal("%.15g" % v)277        for digits in [0, 1, 2, 3, -1, -2, 5]:278            q = Decimal(1).scaleb(-digits)279            for name, mode in [("ROUND", ROUND_HALF_UP), ("ROUNDUP", ROUND_UP), ("ROUNDDOWN", ROUND_DOWN), ("TRUNC", ROUND_DOWN)]:280                expected = float(d15.quantize(q, rounding=mode)) if abs(v) < 1e15 else v281                c.append({"f": f"={name}({v!r},{digits})", "e": expected, "kind": "num", "tol": 0.0})282    for n, d in [(7, 3), (-7, 3), (7, -3), (-7, -3), (5.5, 2), (-5.5, 2), (1e15, 7), (2 ** 52 + 1, 2), (123456789012, 97), (-1, 1e10), (17.25, -4.5)]:283        c.append({"f": f"=MOD({n!r},{d!r})", "e": float(n - d * math.floor(Decimal(n) / Decimal(d))), "kind": "num", "tol": 1e-15})284        c.append({"f": f"=QUOTIENT({n!r},{d!r})", "e": float(math.trunc(Decimal(n) / Decimal(d))), "kind": "num", "tol": 0.0})285    return c286287288def descriptive():289    c = []290    sets = {"D!A1:A12": X, "D!B1:B12": Y, "D!E1:E12": DUPS, "D!F1:F12": POS, "D!L1:L12": GROW, "D!K1:K12": X2}291    for ref, data in sets.items():292        a = np.array(data, dtype=float)293        n = len(a)294        c += [case("VAR", (), 0)]295        c.pop()296        def f(name, value, tol=1e-13):297            return {"f": f"={name}({ref})", "e": float(value), "kind": "num", "tol": tol}298        c += [f("VAR.S", a.var(ddof=1)), f("VAR", a.var(ddof=1)), f("VAR.P", a.var()), f("VARP", a.var()), f("STDEV.S", a.std(ddof=1)), f("STDEV", a.std(ddof=1)),299              f("STDEV.P", a.std()), f("STDEVP", a.std()), f("MEDIAN", np.median(a)), f("AVEDEV", np.mean(np.abs(a - a.mean()))), f("DEVSQ", ((a - a.mean()) ** 2).sum()),300              dict(f("SKEW", st.skew(a, bias=False)), abs=1e-14), dict(f("SKEW.P", st.skew(a, bias=True)), abs=1e-14), f("KURT", st.kurtosis(a, bias=False)), f("SUMSQ", (a * a).sum()),301              f("AVERAGEA", a.mean()), f("VARA", a.var(ddof=1)), f("STDEVPA", a.std()), f("PRODUCT", float(mpmath.fprod(data)), 1e-13)]302        if (a > 0).all():303            c += [f("GEOMEAN", st.gmean(a)), f("HARMEAN", st.hmean(a))]304        for k in [0, 0.05, 0.1, 0.25, 1 / 3, 0.5, 0.62, 0.75, 0.9, 0.99, 1]:305            c.append({"f": f"=PERCENTILE.INC({ref},{k!r})", "e": float(np.percentile(a, k * 100, method="linear")), "kind": "num", "tol": 1e-13})306            c.append({"f": f"=PERCENTILE({ref},{k!r})", "e": float(np.percentile(a, k * 100, method="linear")), "kind": "num", "tol": 1e-13})307            if 1 / (n + 1) <= k <= n / (n + 1):308                c.append({"f": f"=PERCENTILE.EXC({ref},{k!r})", "e": float(np.percentile(a, k * 100, method="weibull")), "kind": "num", "tol": 1e-13})309        for q in [0, 1, 2, 3, 4]:310            c.append({"f": f"=QUARTILE.INC({ref},{q})", "e": float(np.percentile(a, q * 25, method="linear")), "kind": "num", "tol": 1e-13})311            if 1 <= q <= 3:312                c.append({"f": f"=QUARTILE.EXC({ref},{q})", "e": float(np.percentile(a, q * 25, method="weibull")), "kind": "num", "tol": 1e-13})313        for p in [0.1, 0.2, 0.34, 0.5]:314            c.append({"f": f"=TRIMMEAN({ref},{p})", "e": float(st.trim_mean(a, int(n * p / 2) / n)), "kind": "num", "tol": 1e-13})315        for x in sorted(set(data))[:6]:316            c.append({"f": f"=RANK.EQ({x!r},{ref})", "e": float(st.rankdata(-a, method="min")[data.index(x)]), "kind": "num", "tol": 0.0})317            c.append({"f": f"=RANK.AVG({x!r},{ref},1)", "e": float(st.rankdata(a, method="average")[data.index(x)]), "kind": "num", "tol": 0.0})318            c.append({"f": f"=PERCENTRANK.INC({ref},{x!r},12)", "e": math.floor((st.rankdata(a, method="min")[data.index(x)] - 1) / (n - 1) * 1e12 + 1e-6) / 1e12, "kind": "num", "tol": 1e-11})319    pairs = [("D!B1:B12", Y, "D!A1:A12", X), ("D!F1:F12", POS, "D!K1:K12", X2), ("D!L1:L12", GROW, "D!F1:F12", POS), ("D!E1:E12", DUPS, "D!B1:B12", Y)]320    for ry, y, rx, x in pairs:321        y, x = np.array(y, dtype=float), np.array(x, dtype=float)322        lr = st.linregress(x, y)323        n = len(x)324        resid = y - (lr.intercept + lr.slope * x)325326        def g(name, value, args=None, tol=1e-12):327            return {"f": f"={name}({args or (ry + ',' + rx)})", "e": float(value), "kind": "num", "tol": tol}328        c += [g("CORREL", lr.rvalue), g("PEARSON", lr.rvalue), g("RSQ", lr.rvalue ** 2), g("SLOPE", lr.slope), g("INTERCEPT", lr.intercept),329              g("COVARIANCE.P", np.cov(x, y, bias=True)[0, 1]), g("COVAR", np.cov(x, y, bias=True)[0, 1]), g("COVARIANCE.S", np.cov(x, y)[0, 1]),330              g("STEYX", math.sqrt((resid ** 2).sum() / (n - 2))), g("FORECAST", lr.intercept + lr.slope * 6.5, f"6.5,{ry},{rx}"),331              g("FORECAST.LINEAR", lr.intercept - lr.slope * 2, f"-2,{ry},{rx}"), g("SUMPRODUCT", (x * y).sum()), g("SUMX2MY2", (y * y - x * x).sum()),332              g("SUMX2PY2", (y * y + x * x).sum()), g("SUMXMY2", ((y - x) ** 2).sum())]333        # tests334        for tails in [1, 2]:335            c += [g("T.TEST", st.ttest_rel(y, x).pvalue / (2 / tails), f"{ry},{rx},{tails},1"), g("T.TEST", st.ttest_ind(y, x).pvalue / (2 / tails), f"{ry},{rx},{tails},2"),336                  g("T.TEST", st.ttest_ind(y, x, equal_var=False).pvalue / (2 / tails), f"{ry},{rx},{tails},3"), g("TTEST", st.ttest_ind(y, x).pvalue / (2 / tails), f"{ry},{rx},{tails},2")]337        fstat = y.var(ddof=1) / x.var(ddof=1)338        pf = 2 * min(st.f.cdf(fstat, n - 1, n - 1), st.f.sf(fstat, n - 1, n - 1))339        c += [g("F.TEST", pf), g("FTEST", pf)]340        for mu, sigma in [(4, None), (6, 3), (8, 2.2)]:341            s = sigma if sigma else x.std(ddof=1)342            p = st.norm.sf((x.mean() - mu) / (s / math.sqrt(n)))343            c.append(g("Z.TEST", p, f"{rx},{mu}" + (f",{sigma}" if sigma else "")))344    for alpha, sd, n in [(0.05, 2.5, 50), (0.1, 1, 2), (0.01, 12.5, 1000), (0.5, 0.3, 7), (1e-6, 1, 30)]:345        c += [case("CONFIDENCE.NORM", (alpha, sd, n), st.norm.isf(alpha / 2) * sd / math.sqrt(n)), case("CONFIDENCE", (alpha, sd, n), st.norm.isf(alpha / 2) * sd / math.sqrt(n)),346              case("CONFIDENCE.T", (alpha, sd, n), st.t.isf(alpha / 2, n - 1) * sd / math.sqrt(n))]347    obs = np.array([[58, 35], [11, 25], [10, 23]], dtype=float)348    exp = np.array([[45.35, 47.65], [17.56, 18.44], [16.09, 16.91]])349    chi = ((obs - exp) ** 2 / exp).sum()350    c += [{"f": "=CHISQ.TEST(D!AK1:AL3,D!AM1:AN3)", "e": float(st.chi2.sf(chi, 2)), "kind": "num", "tol": 1e-12},351          {"f": "=CHITEST(D!AK1:AL3,D!AM1:AN3)", "e": float(st.chi2.sf(chi, 2)), "kind": "num", "tol": 1e-12},352          {"f": "=CHISQ.TEST(D!AK1:AK3,D!AM1:AM3)", "e": float(st.chi2.sf(((obs[:, 0] - exp[:, 0]) ** 2 / exp[:, 0]).sum(), 2)), "kind": "num", "tol": 1e-12}]353    return collect(c)354355356def regression():357    """LINEST / LOGEST / TREND / GROWTH against statsmodels OLS."""358    c = []359    y, x1, x2, grow, pos = (np.array(v, dtype=float) for v in (Y, X, X2, GROW, POS))360361    def block(prefix, model_y, cols, const, exp_coef):362        xm = np.column_stack(cols)363        design = sm.add_constant(xm) if const else xm364        res = sm.OLS(model_y, design).fit()365        k = xm.shape[1]366        params = list(res.params[1:] if const else res.params)367        bse = list(res.bse[1:] if const else res.bse)368        out = []369        for j in range(k):370            coef = params[k - 1 - j]371            out.append((1, j + 1, math.exp(coef) if exp_coef else coef))372            out.append((2, j + 1, bse[k - 1 - j]))373        if const:374            out.append((1, k + 1, math.exp(res.params[0]) if exp_coef else res.params[0]))375            out.append((2, k + 1, res.bse[0]))376        else:377            out.append((1, k + 1, 1.0 if exp_coef else 0.0))378        ss_res = float(res.ssr)379        ss_tot = float(((model_y - model_y.mean()) ** 2).sum()) if const else float((model_y ** 2).sum())380        df = len(model_y) - k - (1 if const else 0)381        out += [(3, 1, 1 - ss_res / ss_tot), (3, 2, math.sqrt(ss_res / df)), (4, 1, ((ss_tot - ss_res) / k) / (ss_res / df)), (4, 2, df), (5, 1, ss_tot - ss_res), (5, 2, ss_res)]382        return [{"f": f"=INDEX({prefix},{r},{col})", "e": float(v), "kind": "num", "tol": 1e-9, "abs": 1e-12} for r, col, v in out]383384    for const in [True, False]:385        flag = "TRUE" if const else "FALSE"386        c += block(f"LINEST(D!B1:B12,D!A1:A12,{flag},TRUE)", y, [x1], const, False)387        c += block(f"LINEST(D!B1:B12,D!J20:K31,{flag},TRUE)", y, [x1, x2], const, False)388        c += block(f"LINEST(D!F1:F12,D!K1:K12,{flag},TRUE)", pos, [x2], const, False)389        c += block(f"LOGEST(D!L1:L12,D!F1:F12,{flag},TRUE)", np.log(grow), [pos], const, True)390        c += block(f"LOGEST(D!L1:L12,D!J20:K31,{flag},TRUE)", np.log(grow), [x1, x2], const, True)391    res = sm.OLS(y, sm.add_constant(np.column_stack([x1, x2]))).fit()392    for i, (a, b) in enumerate([(1, 2), (3, 4), (5, 6)]):393        c.append({"f": f"=INDEX(TREND(D!B1:B12,D!J20:K31,{{1,2;3,4;5,6}}),{i + 1})", "e": float(res.params[0] + res.params[1] * a + res.params[2] * b), "kind": "num", "tol": 1e-10})394    lr = st.linregress(pos, np.log(grow))395    for i, v in enumerate([12, 13, 14]):396        c.append({"f": f"=INDEX(GROWTH(D!L1:L12,D!F1:F12,{{12;13;14}}),{i + 1})", "e": float(math.exp(lr.intercept + lr.slope * v)), "kind": "num", "tol": 1e-10})397    lr = st.linregress(x1, y)398    for i in [1, 6, 12]:399        c.append({"f": f"=INDEX(TREND(D!B1:B12,D!A1:A12),{i})", "e": float(lr.intercept + lr.slope * x1[i - 1]), "kind": "num", "tol": 1e-10})400    # matrices401    a = np.array([[2, 1, 0], [1, 3, 1], [0, 1, 4]], dtype=float)402    b = np.array([[1, 2, 3], [4, 5, 6], [7, 8, 10]], dtype=float)403    for r, k in itertools.product(range(3), range(3)):404        c.append({"f": f"=INDEX(MINVERSE(D!G1:I3),{r + 1},{k + 1})", "e": float(np.linalg.inv(a)[r, k]), "kind": "num", "tol": 1e-12, "abs": 1e-15})405        c.append({"f": f"=INDEX(MINVERSE(D!G5:I7),{r + 1},{k + 1})", "e": float(np.linalg.inv(b)[r, k]), "kind": "num", "tol": 1e-11, "abs": 1e-13})406        c.append({"f": f"=INDEX(MMULT(D!G1:I3,D!G5:I7),{r + 1},{k + 1})", "e": float((a @ b)[r, k]), "kind": "num", "tol": 1e-14})407    c += [{"f": "=MDETERM(D!G1:I3)", "e": float(np.linalg.det(a)), "kind": "num", "tol": 1e-12}, {"f": "=MDETERM(D!G5:I7)", "e": float(np.linalg.det(b)), "kind": "num", "tol": 1e-11}]408    hilbert = [[1 / (i + j + 1) for j in range(5)] for i in range(5)]409    text = "{" + ";".join(",".join(repr(v) for v in row) for row in hilbert) + "}"410    hinv = mpmath.matrix(hilbert) ** -1411    c.append({"f": f"=MDETERM({text})", "e": float(mpmath.det(mpmath.matrix(hilbert))), "kind": "num", "tol": 1e-9})412    c.append({"f": f"=INDEX(MINVERSE({text}),3,3)", "e": float(hinv[2, 2]), "kind": "num", "tol": 1e-8})413    return c414415416def convert():417    """CONVERT against scipy.constants (exact SI definitions)."""418    ref = {419        "mass": {"g": K.gram, "kg": 1.0, "lbm": K.pound, "ozm": K.ounce, "grain": K.grain, "stone": K.stone, "ton": K.short_ton, "uk_ton": K.long_ton, "LTON": K.long_ton,420                 "sg": K.slug, "cwt": 100 * K.pound, "shweight": 100 * K.pound, "uk_cwt": 112 * K.pound, "lcwt": 112 * K.pound, "hweight": 112 * K.pound, "brton": K.long_ton, "mg": 1e-6},421        "dist": {"m": 1.0, "mi": K.mile, "Nmi": K.nautical_mile, "in": K.inch, "ft": K.foot, "yd": K.yard, "ang": K.angstrom, "ly": K.light_year, "Picapt": K.point,422                 "Pica": K.point, "pica": K.inch / 6, "survey_mi": K.survey_mile, "ell": 45 * K.inch, "km": 1e3, "um": 1e-6, "kly": 1e3 * K.light_year},423        "time": {"sec": 1.0, "s": 1.0, "mn": K.minute, "min": K.minute, "hr": K.hour, "day": K.day, "d": K.day, "yr": K.Julian_year, "ms": 1e-3},424        "pres": {"Pa": 1.0, "p": 1.0, "atm": K.atm, "at": K.atm, "Torr": K.torr, "psi": K.psi, "kPa": 1e3, "hPa": 1e2, "matm": 1e-3 * K.atm},425        "force": {"N": 1.0, "dyn": K.dyne, "dy": K.dyne, "lbf": K.pound_force, "pond": K.gram * K.g, "kN": 1e3},426        "energy": {"J": 1.0, "e": K.erg, "c": K.calorie_th, "cal": K.calorie_IT, "Wh": K.hour, "wh": K.hour, "kWh": 1e3 * K.hour, "HPh": K.hp * K.hour, "hh": K.hp * K.hour,427                   "flb": K.foot * K.pound_force, "BTU": K.Btu_IT, "btu": K.Btu_IT, "kcal": 1e3 * K.calorie_IT, "kJ": 1e3},428        "power": {"W": 1.0, "w": 1.0, "HP": K.hp, "h": K.hp, "PS": 75 * K.g, "kW": 1e3},429        "mag": {"T": 1.0, "ga": 1e-4, "mT": 1e-3},430        "vol": {"l": 1.0, "L": 1.0, "lt": 1.0, "gal": K.gallon / K.liter, "uk_gal": K.gallon_imp / K.liter, "oz": K.fluid_ounce / K.liter, "barrel": K.bbl / K.liter,431                "qt": K.gallon / K.liter / 4, "pt": K.gallon / K.liter / 8, "us_pt": K.gallon / K.liter / 8, "cup": K.gallon / K.liter / 16, "tbs": K.gallon / K.liter / 256,432                "tsp": K.gallon / K.liter / 768, "uk_qt": K.gallon_imp / K.liter / 4, "uk_pt": K.gallon_imp / K.liter / 8, "tspm": 0.005, "m3": 1e3, "ft3": K.foot ** 3 * 1e3,433                "in3": K.inch ** 3 * 1e3, "yd3": K.yard ** 3 * 1e3, "mi3": K.mile ** 3 * 1e3, "Nmi3": K.nautical_mile ** 3 * 1e3, "ang3": K.angstrom ** 3 * 1e3,434                "ly3": K.light_year ** 3 * 1e3, "Picapt3": K.point ** 3 * 1e3, "Pica3": K.point ** 3 * 1e3, "GRT": 100 * K.foot ** 3 * 1e3, "regton": 100 * K.foot ** 3 * 1e3,435                "MTON": 40 * K.foot ** 3 * 1e3, "ml": 1e-3, "cm3": 1e-3, "km3": 1e12, "bushel": 2150.42 * K.inch ** 3 * 1e3},436        "area": {"m2": 1.0, "ha": K.hectare, "ar": 100.0, "uk_acre": K.acre, "us_acre": 43560 * (1200 / 3937) ** 2, "ft2": K.foot ** 2, "in2": K.inch ** 2, "yd2": K.yard ** 2,437                 "mi2": K.mile ** 2, "Nmi2": K.nautical_mile ** 2, "ang2": K.angstrom ** 2, "ly2": K.light_year ** 2, "Picapt2": K.point ** 2, "Pica2": K.point ** 2,438                 "Morgen": 2500.0, "km2": 1e6, "cm2": 1e-4},439        "info": {"bit": 1.0, "byte": 8.0, "kbit": 1e3, "kibit": 1024.0, "Mibyte": 8.0 * 2 ** 20, "Gbyte": 8e9},440        "speed": {"m/s": 1.0, "m/sec": 1.0, "m/h": 1 / 3600, "m/hr": 1 / 3600, "kn": K.knot, "mph": K.mph, "admkn": 6080 * K.foot / 3600, "km/h": K.kmh, "km/hr": K.kmh},441    }442    c = []443    for units in ref.values():444        names = list(units)445        for i, a in enumerate(names):446            for b in (names[0], names[(i + 3) % len(names)], names[-1]):447                c.append(case("CONVERT", (2.5, a, b), 2.5 * units[a] / units[b], tol=2e-12))448    temps = {"C": lambda k: k - 273.15, "F": lambda k: k * 9 / 5 - 459.67, "K": lambda k: k, "Rank": lambda k: k * 9 / 5, "Reau": lambda k: (k - 273.15) * 0.8}449    to_k = {"C": lambda v: v + 273.15, "F": lambda v: (v + 459.67) * 5 / 9, "K": lambda v: v, "Rank": lambda v: v * 5 / 9, "Reau": lambda v: v * 1.25 + 273.15}450    for a in temps:451        for b in temps:452            for v in [-40, 0, 36.6, 451]:453                c.append(case("CONVERT", (v, a, b), temps[b](to_k[a](v)), tol=1e-12, abs_tol=1e-11))454    return collect(c)455456457def complex_numbers():458    c = []459    zs = [3 + 4j, -2.5 - 1.5j, 1j, -1j, 5 + 0j, 0.5 - 0.25j, -3 + 0j, 1 + 1j, 12.5 + 0.001j, -0.75 + 2j]460461    def text(z):462        re, im = repr(float(z.real)), repr(float(abs(z.imag)))463        if z.imag == 0:464            return re465        return f"{re}{'+' if z.imag > 0 else '-'}{im}i"466467    def cx(name, args, value):468        v = complex(value)469        return {"f": f"={name}({','.join(args)})", "e": f"{v.real!r}{'+' if v.imag >= 0 else '-'}{abs(v.imag)!r}i", "kind": "complex", "tol": 1e-13, "abs": 1e-15}470471    for z in zs:472        t = '"' + text(z) + '"'473        c += [case("IMABS", (text(z),), abs(z), tol=1e-15), case("IMREAL", (text(z),), z.real, tol=0.0), case("IMAGINARY", (text(z),), z.imag, tol=0.0),474              case("IMARGUMENT", (text(z),), cmath.phase(z), tol=1e-15)]475        c += [cx("IMSQRT", [t], cmath.sqrt(z)), cx("IMEXP", [t], cmath.exp(z)), cx("IMLN", [t], cmath.log(z)), cx("IMLOG10", [t], cmath.log10(z)),476              cx("IMLOG2", [t], cmath.log(z) / math.log(2)), cx("IMSIN", [t], cmath.sin(z)), cx("IMCOS", [t], cmath.cos(z)), cx("IMCONJUGATE", [t], z.conjugate())]477        for p in [2, 3, 0.5, -1, -2.5, 7]:478            c.append(cx("IMPOWER", [t, repr(p)], cmath.exp(p * cmath.log(z))))479        for w in [1 - 2j, 3 + 0j, -1j, 0.25 + 0.75j]:480            u = '"' + text(w) + '"'481            c += [cx("IMSUB", [t, u], z - w), cx("IMDIV", [t, u], z / w), cx("IMSUM", [t, u], z + w), cx("IMPRODUCT", [t, u], z * w),482                  cx("IMSUM", [t, u, '"2+2i"'], z + w + 2 + 2j), cx("IMPRODUCT", [t, u, '"2+2i"'], z * w * (2 + 2j))]483    return collect(c)484485486if __name__ == "__main__":487    for i in range(12):488        D["cells"][f"J{20 + i}"] = D["cells"][f"A{1 + i}"]489        D["cells"][f"K{20 + i}"] = D["cells"][f"K{1 + i}"]490    total = 0491    for name, fn, sheets in [("scipy_distributions", distributions, []), ("scipy_discrete", discrete, []), ("scipy_special", special, []),492                             ("decimal_rounding", rounding, []), ("numpy_descriptive", descriptive, [D]), ("statsmodels_regression", regression, [D]),493                             ("scipy_convert", convert, []), ("cmath_complex", complex_numbers, [])]:494        cases = fn()495        seen, uniq = set(), []496        for cs in cases:497            if cs["f"] not in seen:498                seen.add(cs["f"])499                uniq.append(cs)500        write_fixture(name, name.split("_")[0], sheets, uniq)501        total += len(uniq)502    print("total", total)503
Files main
Menu

Theme