"""E-M1 · 04. Test de Carril (§6 del pre-registro), implementación 1. Lee tmp/em1.duckdb (tablas men y tipo_org de 01_base), construye los pares y los subconjuntos (a), (b), (c), agrega recuentos órgano × tramo y estima el exceso de masa (polinomio contrafactual con la ventana excluida y ficticias de importe redondo), con bootstrap de órganos (1.000 réplicas, semilla 20261020). Salidas: salidas/04_carril.md, salidas/04_carril_celdas.csv, salidas/04_carril_tramos.csv. Uso: sql/ejecutar.sh 04_carril.py """ import csv import sys import time import duckdb import numpy as np T0 = time.time() DB = "/w/tmp/em1.duckdb" OUT_MD = "/w/salidas/04_carril.md" OUT_CELDAS = "/w/salidas/04_carril_celdas.csv" OUT_TRAMOS = "/w/salidas/04_carril_tramos.csv" NREP = 1000 SEMILLA = 20261020 con = duckdb.connect(DB) for s in ("SET memory_limit='7GB'", "SET threads=6", "SET temp_directory='/w/tmp/duckdb_tmp'", "SET preserve_insertion_order=false"): con.execute(s) # ---------------------------------------------------------------- pares y subconjuntos con.execute(""" CREATE OR REPLACE TABLE carp AS SELECT m.*, coalesce(t.tipo, 'sin_tipo') AS tipo_org, coalesce(t.agente_secti, FALSE) AS secti FROM men m LEFT JOIN tipo_org t USING (organo_clave) WHERE m.cuenta AND m.anio_ref BETWEEN 2019 AND 2025 AND m.organo_clave IS NOT NULL AND m.empresa_clave IS NOT NULL AND NOT m.persona_fisica """) con.execute(""" CREATE OR REPLACE TABLE carf AS WITH np AS (SELECT organo_clave, empresa_clave, anio_ref, count(*) n_par FROM carp GROUP BY ALL), nobj AS (SELECT organo_clave, objeto_norm, anio_ref, count(*) n_obj FROM carp WHERE length(objeto_norm) >= 10 GROUP BY ALL) SELECT c.organo_clave, c.anio_ref, c.source, c.tipo_org, c.secti, c.imp, CASE WHEN c.tipo_contrato IN ('Servicios', 'Suministros') THEN 'serv' ELSE 'obras' END AS clase_t, (np.n_par = 1) AS sub_b, coalesce(nobj.n_obj = 1, FALSE) AS sub_c FROM carp c JOIN np ON np.organo_clave = c.organo_clave AND np.empresa_clave = c.empresa_clave AND np.anio_ref = c.anio_ref LEFT JOIN nobj ON nobj.organo_clave = c.organo_clave AND nobj.objeto_norm = c.objeto_norm AND nobj.anio_ref = c.anio_ref WHERE c.base = 'sin' AND coalesce(c.ccaa, '') <> 'NA' AND c.imp IS NOT NULL AND ((c.tipo_contrato IN ('Servicios', 'Suministros') AND c.imp >= 5000 AND c.imp < 15000) OR (c.tipo_contrato = 'Obras' AND c.imp >= 12000 AND c.imp < 40000)) """) resumen_pob = con.execute(""" SELECT (SELECT count(*) FROM carp) filas_pares, (SELECT count(DISTINCT organo_clave) FROM carp) organos_pares, (SELECT count(*) FROM (SELECT DISTINCT organo_clave, empresa_clave, anio_ref FROM carp)) pares_anio, (SELECT count(*) FROM carf WHERE clase_t = 'serv') serv_5_15k, (SELECT count(*) FROM carf WHERE clase_t = 'obras') obras_12_40k """).fetchone() g = con.execute(""" SELECT organo_clave, anio_ref, (source = 'PLACSP') AS placsp, tipo_org, secti, clase_t, CAST(CASE WHEN clase_t = 'serv' THEN floor(imp / 100) * 100 ELSE floor(imp / 250) * 250 END AS INTEGER) AS tramo, sub_b, sub_c, count(*) AS n FROM carf GROUP BY ALL """).fetchnumpy() for k in g: g[k] = np.asarray(g[k]) organos = np.unique(g["organo_clave"]) oidx = np.searchsorted(organos, g["organo_clave"]) NORG = len(organos) print(f"grupos {len(g['n'])}, órganos {NORG}, {time.time()-T0:.0f} s", file=sys.stderr) # ---------------------------------------------------------------- especificaciones SERV = dict(clase="serv", inicio=5000, ancho=100, ntramos=100, T=15000, ajuste=(5000, 14500), ventana=(14500, 15000), grado=3, redondeo=(500, 1000)) OBRAS = dict(clase="obras", inicio=12000, ancho=250, ntramos=112, T=40000, ajuste=(12000, 39000), ventana=(39000, 40000), grado=3, redondeo=(1000, 5000)) def matriz(esp): """Devuelve (x izquierdos, índices de ajuste, índices de ventana, g) con S_cf = g · y[ajuste].""" x = esp["inicio"] + esp["ancho"] * np.arange(esp["ntramos"]) centro = x + esp["ancho"] / 2 z = (centro - esp["T"]) / 1000.0 cols = [z ** k for k in range(esp["grado"] + 1)] + [(x % m == 0).astype(float) for m in esp["redondeo"]] X = np.column_stack(cols) aj = np.where((x >= esp["ajuste"][0]) & (x < esp["ajuste"][1]))[0] ve = np.where((x >= esp["ventana"][0]) & (x < esp["ventana"][1]))[0] P = np.linalg.pinv(X[aj]) gvec = X[ve].sum(axis=0) @ P return x, X, aj, ve, P, gvec def estima(Y, esp): """Y: (..., ntramos). Devuelve dict con B, b, e y S_cf (vectorizado sobre réplicas).""" x, X, aj, ve, P, gvec = matriz(esp) scf = Y[..., aj] @ gvec sw = Y[..., ve].sum(axis=-1) B = sw - scf nw = len(ve) with np.errstate(divide="ignore", invalid="ignore"): b = B / (scf / nw) e = B / scf return dict(B=B, b=b, e=e, scf=scf, sw=sw) def curva(y, esp): x, X, aj, ve, P, gvec = matriz(esp) beta = P @ y[aj] return x, X @ beta def razon_r(y, esp): """Razón literal del §3.7 (solo servicios): (n[14.500,15.000)/500) / (n[10.000,14.500)/4.500).""" x = esp["inicio"] + esp["ancho"] * np.arange(esp["ntramos"]) v = y[..., (x >= 14500) & (x < 15000)].sum(axis=-1) r = y[..., (x >= 10000) & (x < 14500)].sum(axis=-1) with np.errstate(divide="ignore", invalid="ignore"): return (v / 500) / (r / 4500) def hist(mask, esp): """Recuentos órgano × tramo para (a), (b), (c) con la máscara de grupos dada.""" nt = esp["ntramos"] ti = ((g["tramo"] - esp["inicio"]) // esp["ancho"]).astype(np.int64) m = mask & (g["clase_t"] == esp["clase"]) & (ti >= 0) & (ti < nt) out = [] for sub in (np.ones_like(m), g["sub_b"], g["sub_c"]): mm = m & sub.astype(bool) flat = np.bincount(oidx[mm] * nt + ti[mm], weights=g["n"][mm].astype(float), minlength=NORG * nt) out.append(flat.reshape(NORG, nt)) return out # [Ha, Hb, Hc] rng = np.random.default_rng(SEMILLA) W = rng.multinomial(NORG, np.full(NORG, 1.0 / NORG), size=NREP).astype(np.float64) print(f"W listo {W.shape}, {time.time()-T0:.0f} s", file=sys.stderr) def zona(rho): if rho >= 0.70: return "recorte" if rho <= 0.40: return "troceo" return "mixto" def resultado(rb, rc): zb, zc = zona(rb), zona(rc) if zb == zc == "recorte": return "recorte" if zb == zc == "troceo": return "troceo" if {zb, zc} == {"recorte", "troceo"}: return "mixto (discordante)" return "mixto" def firme(rho, lo, hi): z = zona(rho) return (zona(lo) == z) and (zona(hi) == z) ANIOS = g["anio_ref"] BASE_SERV = (g["tipo_org"] != "investigacion") celdas = [] # (nombre, mascara, esp) celdas.append(("principal 2019-2025", BASE_SERV, SERV)) celdas.append(("2019 (con tope 118.3)", BASE_SERV & (ANIOS == 2019), SERV)) celdas.append(("2020-2025 (sin tope)", BASE_SERV & (ANIOS >= 2020), SERV)) for a in range(2019, 2026): celdas.append((f"año {a}", BASE_SERV & (ANIOS == a), SERV)) for t in ["ayuntamiento", "autonomica", "age", "salud", "empresa_fundacion_consorcio", "local_supramunicipal", "otro", "sin_tipo"]: celdas.append((f"tipo {t}", BASE_SERV & (g["tipo_org"] == t), SERV)) celdas.append(("tipo investigacion (contraste DA 54.ª)", g["tipo_org"] == "investigacion", SERV)) celdas.append(("solo PLACSP", BASE_SERV & g["placsp"], SERV)) celdas.append(("fuera de la PLACSP", BASE_SERV & ~g["placsp"], SERV)) celdas.append(("obras 2019-2025", np.ones(len(g["n"]), bool), OBRAS)) celdas.append(("obras 2019", ANIOS == 2019, OBRAS)) celdas.append(("obras 2020-2025", ANIOS >= 2020, OBRAS)) SENS = [ ("grado 2", dict(SERV, grado=2)), ("grado 4", dict(SERV, grado=4)), ("ajuste [7.000, 14.500)", dict(SERV, ajuste=(7000, 14500))), ("ajuste [10.000, 14.500)", dict(SERV, ajuste=(10000, 14500))), ("ventana [14.000, 15.000)", dict(SERV, ajuste=(5000, 14000), ventana=(14000, 15000))), ("sin ficticias de redondeo", dict(SERV, redondeo=())), ] filas = [] reps_guardadas = {} tramos_out = [] def evalua(nombre, H, esp, guarda=None, sens=None): Ha, Hb, Hc = H ya, yb, yc = Ha.sum(0), Hb.sum(0), Hc.sum(0) Ra, Rb, Rc = W @ Ha, W @ Hb, W @ Hc pa, pb, pc = estima(ya, esp), estima(yb, esp), estima(yc, esp) qa, qb, qc = estima(Ra, esp), estima(Rb, esp), estima(Rc, esp) rho_b, rho_c = pb["b"] / pa["b"], pc["b"] / pa["b"] rb_rep, rc_rep = qb["b"] / qa["b"], qc["b"] / qa["b"] ci = lambda v: np.nanpercentile(v, [2.5, 97.5]) x = esp["inicio"] + esp["ancho"] * np.arange(esp["ntramos"]) ve = (x >= esp["ventana"][0]) & (x < esp["ventana"][1]) n_org = lambda Hm: int((Hm.sum(1) > 0).sum()) fila = dict(celda=nombre, sens=sens or "", clase=esp["clase"], 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_a=n_org(Ha), org_b=n_org(Hb), org_c=n_org(Hc), 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=rho_b, rho_c=rho_c) for k, v in (("b_a", qa["b"]), ("b_b", qb["b"]), ("b_c", qc["b"]), ("rho_b", rb_rep), ("rho_c", rc_rep)): lo, hi = ci(v) fila[k + "_lo"], fila[k + "_hi"] = lo, hi if esp["clase"] == "serv": fila["r_a"], fila["r_b"], fila["r_c"] = razon_r(ya, esp), razon_r(yb, esp), razon_r(yc, esp) insuf = (fila["n_a"] < 5000) or (fila["n_b"] < 1000) or (fila["n_c"] < 1000) hay = fila["b_a_lo"] > 0 if insuf: res = "muestra insuficiente" elif not hay: res = "sin amontonamiento que explicar" else: res = resultado(rho_b, rho_c) fb = firme(rho_b, fila["rho_b_lo"], fila["rho_b_hi"]) fc = firme(rho_c, fila["rho_c_lo"], fila["rho_c_hi"]) res += " · firme" if (fb and fc) else " · en el límite" fila["resultado"] = res filas.append(fila) if guarda: reps_guardadas[guarda] = dict(rb=rb_rep, rc=rc_rep, ba=qa["b"]) if sens is None and nombre in ("principal 2019-2025", "obras 2019-2025", "2019 (con tope 118.3)", "2020-2025 (sin tope)"): for sub, y in (("a", ya), ("b", yb), ("c", yc)): xx, cf = curva(y, esp) for xi, yi, ci_ in zip(xx, y, cf): tramos_out.append(dict(celda=nombre, sub=sub, tramo=int(xi), ancho=esp["ancho"], observado=int(yi), contrafactual=round(float(ci_), 3), en_ajuste=bool(esp["ajuste"][0] <= xi < esp["ajuste"][1]), en_ventana=bool(esp["ventana"][0] <= xi < esp["ventana"][1]))) return fila for nombre, mask, esp in celdas: H = hist(mask, esp) guarda = nombre if nombre in ("2019 (con tope 118.3)", "2020-2025 (sin tope)", "principal 2019-2025") else None evalua(nombre, H, esp, guarda=guarda) if nombre == "principal 2019-2025": for sn, se in SENS: evalua(nombre, H, se, sens=sn) evalua(nombre, hist(BASE_SERV & ~g["secti"], SERV), SERV, sens="sin órganos agente_secti") # Complementario (no pre-registrado): exceso en el complemento de (b), pares con >= 2 menores Ha, Hb, Hc = H evalua(nombre, [Ha, Ha - Hb, Ha - Hc], SERV, sens="COMPLEMENTARIO: (b)=pares con ≥2 menores, (c)=objetos repetidos") print(f"{nombre}: {time.time()-T0:.0f} s", file=sys.stderr) # Hipótesis descriptiva: rho_b(2020-2025) - rho_b(2019) d_rb = reps_guardadas["2020-2025 (sin tope)"]["rb"] - reps_guardadas["2019 (con tope 118.3)"]["rb"] d_rc = reps_guardadas["2020-2025 (sin tope)"]["rc"] - reps_guardadas["2019 (con tope 118.3)"]["rc"] f19 = [f for f in filas if f["celda"] == "2019 (con tope 118.3)" and not f["sens"]][0] f20 = [f for f in filas if f["celda"] == "2020-2025 (sin tope)" and not f["sens"]][0] hip = dict(d_rho_b=f20["rho_b"] - f19["rho_b"], d_rho_b_ci=np.nanpercentile(d_rb, [2.5, 97.5]), d_rho_c=f20["rho_c"] - f19["rho_c"], d_rho_c_ci=np.nanpercentile(d_rc, [2.5, 97.5])) # ---------------------------------------------------------------- salidas cols = ["celda", "sens", "clase", "n_a", "n_b", "n_c", "nv_a", "nv_b", "nv_c", "org_a", "org_b", "org_c", "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", "r_a", "r_b", "r_c", "rho_b", "rho_b_lo", "rho_b_hi", "rho_c", "rho_c_lo", "rho_c_hi", "resultado"] with open(OUT_CELDAS, "w", newline="") as fh: w = csv.DictWriter(fh, fieldnames=cols, extrasaction="ignore") w.writeheader() for f in filas: w.writerow({k: (f"{f[k]:.6g}" if isinstance(f.get(k), (float, np.floating)) else f.get(k, "")) for k in cols}) with open(OUT_TRAMOS, "w", newline="") as fh: w = csv.DictWriter(fh, fieldnames=list(tramos_out[0].keys())) w.writeheader() w.writerows(tramos_out) def n(v): return f"{v:,}".replace(",", ".") def d(v, k=2): return "∅" if v is None or v != v else f"{v:.{k}f}".replace(".", ",") md = ["# Resultado de `04_carril.py`\n\n", f"DuckDB {duckdb.__version__} · numpy {np.__version__} · {time.strftime('%Y-%m-%d %H:%M:%S %Z')} · réplicas {NREP} · semilla {SEMILLA}\n\n", "## Población de pares (vista única, 2019-2025, órgano y empresa identificados, sin personas físicas)\n\n", "| filas | órganos | pares órgano-empresa-año | muestra serv. 5.000-15.000 (base sin) | muestra obras 12.000-40.000 |\n|---|---|---|---|---|\n", "| " + " | ".join(n(int(v)) for v in resumen_pob) + " |\n\n", "## Exceso de masa b (en tramos de contrafactual) y razones ρ = b_sub / b_a, con IC 95 % por bootstrap de órganos\n\n", "| celda | sens. | n_a | n_b | n_c | ventana a/b/c | b_a [IC] | b_b [IC] | b_c [IC] | e_a | ρ_b [IC] | ρ_c [IC] | r_a / r_b / r_c | resultado |\n", "|---|---|---|---|---|---|---|---|---|---|---|---|---|---|\n"] for f in filas: rr = " / ".join(d(f.get(k)) for k in ("r_a", "r_b", "r_c")) if f["clase"] == "serv" else "—" md.append(f"| {f['celda']} | {f['sens'] or 'principal'} | {n(f['n_a'])} | {n(f['n_b'])} | {n(f['n_c'])} | {n(f['nv_a'])}/{n(f['nv_b'])}/{n(f['nv_c'])} | " 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)}] | {rr} | {f['resultado']} |\n") md.append("\n## Hipótesis descriptiva: ρ(2020-2025) − ρ(2019)\n\n| medida | diferencia | IC 95 % |\n|---|---|---|\n") md.append(f"| ρ_b | {d(hip['d_rho_b'], 3)} | [{d(hip['d_rho_b_ci'][0], 3)}; {d(hip['d_rho_b_ci'][1], 3)}] |\n") md.append(f"| ρ_c | {d(hip['d_rho_c'], 3)} | [{d(hip['d_rho_c_ci'][0], 3)}; {d(hip['d_rho_c_ci'][1], 3)}] |\n") md.append(f"\n_Tiempo total: {time.time()-T0:.0f} s_\n") open(OUT_MD, "w").write("".join(md)) print(f"fin {time.time()-T0:.0f} s", file=sys.stderr)