"""E-M1b · 04. (c) Test de Carril adaptado a licitaciones (§5 del pre-registro), implementación 1. Lee tmp/em1b.duckdb (tabla lic). Por celda: bins alineados con T, (a) todas, (b) n_cpv = 1, (c) objeto válido y n_obj = 1; polinomio de grado 3 + 2 ficticias de redondeo ajustado fuera de la ventana; exceso de masa b; ρ = b_sub / b_a; bootstrap de órganos (1.000 réplicas, semilla 20261021, generador nuevo por celda). Sensibilidades y «dentro de órgano» (§5.6). Salidas: salidas/04_carril.md, 04_carril_celdas.csv, 04_carril_tramos.csv. Uso: sql/ejecutar.sh 04_carril.py """ import csv import math import time import duckdb import numpy as np T0 = time.time() con = duckdb.connect("/w/tmp/em1b.duckdb", read_only=True) con.execute("SET threads=6; SET memory_limit='7GB'") NREP, SEMILLA = 1000, 20261021 P2425 = "trimestre BETWEEN '2024T1' AND '2025T4'" P2426 = "trimestre BETWEEN '2024T1' AND '2026T3'" P26 = "trimestre BETWEEN '2026T1' AND '2026T3'" SINNA = "coalesce(ccaa, '') <> 'NA'" CELDAS = [ # nombre, filtro, T, estricto, w, L, nventana, R1, R2, s ("C1 SARA y simplificado, AGE (143.000, 2024-2025)", f"feed='propio' AND clase='ss' AND age AND {P2425}", 143000, True, 1000, 105000, 3, 5000, 10000, 10000), ("C1r réplica 2026, AGE (140.000, 2026T1-T3)", f"feed='propio' AND clase='ss' AND age AND {P26}", 140000, True, 1000, 105000, 3, 5000, 10000, 10000), ("C2 simplificado, resto (143.000, 2024-2025)", f"feed='propio' AND clase='ss' AND NOT age AND {SINNA} AND {P2425}", 143000, True, 1000, 105000, 3, 5000, 10000, 10000), ("C3 SARA, resto (221.000, 2024-2025)", f"feed='propio' AND clase='ss' AND NOT age AND {P2425}", 221000, True, 1000, 150000, 5, 5000, 10000, 10000), ("C3b SARA, resto, agregación (221.000, 2024-2025)", f"feed='agregacion' AND clase='ss' AND NOT age AND {P2425}", 221000, True, 1000, 150000, 5, 5000, 10000, 10000), ("C4 recurso, sum./serv. (100.000, 2024-2026T3)", f"feed='propio' AND clase='ss' AND {SINNA} AND {P2426}", 100000, False, 500, 65000, 6, 5000, 10000, 10000), ("C5 simplificado, obras (2.000.000, 2024-2026T3)", f"feed='propio' AND clase='obras' AND {SINNA} AND {P2426}", 2000000, False, 25000, 1050000, 4, 50000, 100000, 100000), ("C6 recurso, obras y concesiones (3.000.000, 2024-2026T3)", f"feed='propio' AND clase IN ('obras','conc') AND {SINNA} AND {P2426}", 3000000, False, 50000, 2100000, 3, 100000, 500000, 100000), ("C7 SARA, obras y concesiones (5.538.000, 2024-2025)", f"feed='propio' AND clase IN ('obras','conc') AND {P2425}", 5538000, True, 100000, 3238000, 2, 500000, 1000000, 1000000), ] def datos(filtro, T, estricto, w, L, fecha="fecha_ref", extra=""): nb = round((T - L) / w) if fecha != "fecha_ref": filtro = filtro.replace("trimestre", "(year(fecha_pub) || 'T' || quarter(fecha_pub))") cond = f"ve >= {L} AND ve < {T}" if estricto else f"ve > {L} AND ve <= {T}" idx = f"CAST(floor((ve - {L}) / {w}) AS INTEGER)" if estricto else f"CAST(ceil((ve - {L}) / {w}) AS INTEGER) - 1" q = f"""SELECT organo_clave, {idx} AS i, (n_cpv = 1) AS sb, coalesce(objeto_valido AND n_obj = 1, FALSE) AS sc, count(*) n FROM lic WHERE {filtro} {extra} AND {cond} AND organo_clave IS NOT NULL AND cpv_division IS NOT NULL GROUP BY ALL""" g = con.execute(q).fetchnumpy() g = {k: np.asarray(v) for k, v in g.items()} orgs = np.unique(g["organo_clave"]) if len(g["n"]) else np.array([]) oi = np.searchsorted(orgs, g["organo_clave"]) H = [] for sub in (np.ones(len(g["n"]), bool), g["sb"].astype(bool), g["sc"].astype(bool)): flat = np.bincount(oi[sub] * nb + g["i"][sub], weights=g["n"][sub].astype(float), minlength=len(orgs) * nb) H.append(flat.reshape(len(orgs), nb)) return H, orgs, nb def disenio(T, estricto, w, L, nb, nv, R1, R2, s, grado=3, ficticias=True, nv_fit=None): a = L + w * np.arange(nb) cen = a + w / 2 z = (cen - T) / s cols = [z ** k for k in range(grado + 1)] if ficticias: for R in (R1, R2): if estricto: cols.append((np.ceil(a / R) * R < a + w).astype(float)) else: cols.append((np.floor((a + w) / R) * R > a).astype(float)) X = np.column_stack(cols) ve = np.arange(nb - nv, nb) aj = np.arange(0, nb - nv) P = np.linalg.pinv(X[aj]) gvec = X[ve].sum(0) @ P return a, X, aj, ve, P, gvec def estima(Y, D): a, X, aj, ve, P, gvec = D scf = Y[..., aj] @ gvec sw = Y[..., ve].sum(-1) B = sw - scf with np.errstate(divide="ignore", invalid="ignore"): return dict(B=B, b=B / (scf / len(ve)), e=B / scf, scf=scf, sw=sw) def zona(r): return "recorte" if r >= 0.70 else ("troceo" if r <= 0.40 else "mixto") def resultado(rb, rc): zb, zc = zona(rb), zona(rc) if zb == zc == "recorte": return "ajuste del importe" if zb == zc == "troceo": return "fraccionamiento" if {zb, zc} == {"recorte", "troceo"}: return "mixto (discordante)" return "mixto" filas, tramos = [], [] def evalua(nombre, H, orgs, D, sens="principal", guarda_tramos=False): Ha, Hb, Hc = H ya, yb, yc = Ha.sum(0), Hb.sum(0), Hc.sum(0) rng = np.random.default_rng(SEMILLA) NORG = len(orgs) W = rng.multinomial(NORG, np.full(NORG, 1.0 / NORG), size=NREP).astype(float) if NORG else np.zeros((NREP, 0)) pa, pb, pc = estima(ya, D), estima(yb, D), estima(yc, D) qa, qb, qc = estima(W @ Ha, D), estima(W @ Hb, D), estima(W @ Hc, D) with np.errstate(divide="ignore", invalid="ignore"): rb, rc = pb["b"] / pa["b"], pc["b"] / pa["b"] rbr, rcr = qb["b"] / qa["b"], qc["b"] / qa["b"] ci = lambda v: np.nanpercentile(v, [2.5, 97.5]) if np.isfinite(v).any() else (np.nan, np.nan) ve = D[3] f = dict(celda=nombre, sens=sens, n_a=int(ya.sum()), n_b=int(yb.sum()), n_c=int(yc.sum()), nv_a=int(ya[ve].sum()), nv_b=int(yb[ve].sum()), nv_c=int(yc[ve].sum()), org_boot=NORG, b_a=pa["b"], b_b=pb["b"], b_c=pc["b"], e_a=pa["e"], e_b=pb["e"], e_c=pc["e"], rho_b=rb, rho_c=rc) for k, v in (("b_a", qa["b"]), ("b_b", qb["b"]), ("b_c", qc["b"]), ("rho_b", rbr), ("rho_c", rcr)): f[k + "_lo"], f[k + "_hi"] = ci(v) if f["n_a"] < 2000 or f["n_b"] < 500 or f["n_c"] < 500: res = "muestra insuficiente" elif not f["b_a_lo"] > 0: res = "sin amontonamiento que explicar" else: res = resultado(rb, rc) firme = all(zona(lo) == zona(r) == zona(hi) for r, lo, hi in ((rb, f["rho_b_lo"], f["rho_b_hi"]), (rc, f["rho_c_lo"], f["rho_c_hi"]))) res += " · firme" if firme else " · en el límite" f["resultado"] = res filas.append(f) if guarda_tramos: a, X, aj = D[0], D[1], D[2] for sub, y in (("a", ya), ("b", yb), ("c", yc)): cf = X @ (D[4] @ y[aj]) for i in range(len(a)): tramos.append(dict(celda=nombre, sub=sub, tramo_inf=int(a[i]), ancho=int(a[1] - a[0]), observado=int(y[i]), contrafactual=round(float(cf[i]), 3), en_ventana=bool(i in set(ve.tolist())))) return f def dentro_organo(nombre, H, orgs, D): """Descriptivo §5.6: (b) o (c) de cada órgano reescalado a la masa de (a) del órgano; (a) limitado a órganos con sub > 0.""" Ha = H[0] rng = np.random.default_rng(SEMILLA) NORG = len(orgs) W = rng.multinomial(NORG, np.full(NORG, 1.0 / NORG), size=NREP).astype(float) out = {} for lab, Hs in (("b", H[1]), ("c", H[2])): ms, ma = Hs.sum(1), Ha.sum(1) keep = ms > 0 with np.errstate(divide="ignore", invalid="ignore"): Hs_r = np.where(keep[:, None], Hs * (ma / np.where(ms > 0, ms, 1))[:, None], 0) Ha_k = Ha * keep[:, None] pa, ps = estima(Ha_k.sum(0), D), estima(Hs_r.sum(0), D) qa, qs = estima(W @ Ha_k, D), estima(W @ Hs_r, D) with np.errstate(divide="ignore", invalid="ignore"): rr = qs["b"] / qa["b"] out[lab] = (ps["b"] / pa["b"], *np.nanpercentile(rr, [2.5, 97.5]), int(keep.sum())) return out dentro = {} for (nombre, filtro, T, est, w, L, nv, R1, R2, s) in CELDAS: H, orgs, nb = datos(filtro, T, est, w, L) D = disenio(T, est, w, L, nb, nv, R1, R2, s) evalua(nombre, H, orgs, D, guarda_tramos=True) for g in (2, 4): evalua(nombre, H, orgs, disenio(T, est, w, L, nb, nv, R1, R2, s, grado=g), sens=f"grado {g}") evalua(nombre, H, orgs, disenio(T, est, w, L, nb, 2 * nv, R1, R2, s), sens="ventana doble") evalua(nombre, H, orgs, disenio(T, est, w, L, nb, nv, R1, R2, s, ficticias=False), sens="sin ficticias") H2, o2, _ = datos(filtro, T, est, w, L, extra="AND proc_exante") evalua(nombre, H2, o2, D, sens="procedimientos de EXANTE") H3, o3, _ = datos(filtro, T, est, w, L, fecha="fecha_pub") evalua(nombre, H3, o3, D, sens="fecha_pub") if nombre.startswith("C1 "): H4, o4, _ = datos(filtro.replace("AND age", "AND age_amp"), T, est, w, L) evalua(nombre, H4, o4, D, sens="AGE ampliada") if len(orgs): dentro[nombre] = dentro_organo(nombre, H, orgs, D) print(f"{nombre}: {time.time()-T0:.0f} s", flush=True) cols = ["celda", "sens", "n_a", "n_b", "n_c", "nv_a", "nv_b", "nv_c", "org_boot", "b_a", "b_a_lo", "b_a_hi", "b_b", "b_b_lo", "b_b_hi", "b_c", "b_c_lo", "b_c_hi", "e_a", "e_b", "e_c", "rho_b", "rho_b_lo", "rho_b_hi", "rho_c", "rho_c_lo", "rho_c_hi", "resultado"] with open("/w/salidas/04_carril_celdas.csv", "w", newline="") as fh: wr = csv.DictWriter(fh, fieldnames=cols, extrasaction="ignore"); wr.writeheader() for f in filas: wr.writerow({k: (f"{f[k]:.6g}" if isinstance(f[k], (float, np.floating)) else f[k]) for k in cols}) with open("/w/salidas/04_carril_tramos.csv", "w", newline="") as fh: wr = csv.DictWriter(fh, fieldnames=list(tramos[0].keys())); wr.writeheader(); wr.writerows(tramos) def n(v): return f"{v:,}".replace(",", ".") def d(v, k=2): return "∅" if v is None or not np.isfinite(v) else f"{v:.{k}f}".replace(".", ",") md = [f"# Resultado de `04_carril.py`\n\nDuckDB {duckdb.__version__} · numpy {np.__version__} · {time.strftime('%Y-%m-%d %H:%M:%S %Z')} · réplicas {NREP} · semilla {SEMILLA}\n\n", "b = exceso de masa en bins de contrafactual; e = exceso relativo en la ventana; ρ = b_sub / b_a; IC 95 % por bootstrap de órganos.\n\n", "| celda | sens. | n_a / n_b / n_c | ventana a/b/c | órganos | b_a [IC] | b_b [IC] | b_c [IC] | e_a | ρ_b [IC] | ρ_c [IC] | resultado |\n|---|---|---|---|---|---|---|---|---|---|---|---|\n"] for f in filas: md.append(f"| {f['celda']} | {f['sens']} | {n(f['n_a'])} / {n(f['n_b'])} / {n(f['n_c'])} | {n(f['nv_a'])}/{n(f['nv_b'])}/{n(f['nv_c'])} | {n(f['org_boot'])} | " f"{d(f['b_a'])} [{d(f['b_a_lo'])}; {d(f['b_a_hi'])}] | {d(f['b_b'])} [{d(f['b_b_lo'])}; {d(f['b_b_hi'])}] | {d(f['b_c'])} [{d(f['b_c_lo'])}; {d(f['b_c_hi'])}] | " f"{d(f['e_a'])} | {d(f['rho_b'], 3)} [{d(f['rho_b_lo'], 3)}; {d(f['rho_b_hi'], 3)}] | {d(f['rho_c'], 3)} [{d(f['rho_c_lo'], 3)}; {d(f['rho_c_hi'], 3)}] | {f['resultado']} |\n") md.append("\n## Dentro de órgano (descriptivo, §5.6)\n\n| celda | ρ_b dentro [IC] (órganos con b) | ρ_c dentro [IC] (órganos con c) |\n|---|---|---|\n") for k, v in dentro.items(): md.append(f"| {k} | {d(v['b'][0], 3)} [{d(v['b'][1], 3)}; {d(v['b'][2], 3)}] ({n(v['b'][3])}) | {d(v['c'][0], 3)} [{d(v['c'][1], 3)}; {d(v['c'][2], 3)}] ({n(v['c'][3])}) |\n") md.append(f"\n_Tiempo total: {time.time()-T0:.0f} s_\n") open("/w/salidas/04_carril.md", "w").write("".join(md))