"""05. Segunda implementación (DEFINICIONES_PREVIAS §7c): pandas + pyarrow, SIN DuckDB (imagen licitaciones-scrapers:vps). Escrita desde el pre-registro sin importar 02/03 ni comun.py. Clave del BORME con nn/borme_norm_de_nn de la impl2 de E-M3. Al final compara con los internos de la principal (interno/ev.parquet, interno/oa_j12.parquet). Salida: salidas/05_cotejo_impl2.json.""" import datetime as dt, json, math, os, re, sys, time import numpy as np, pandas as pd, pyarrow as pa, pyarrow.compute as pc, pyarrow.dataset as ds, pyarrow.parquet as pq sys.path.insert(0, "/af/em3/impl2") from em3_impl2 import nn, borme_norm_de_nn # noqa: E402 T0 = time.time(); D = "/d/unified_20261018"; R = {} def log(m): print(f"[{time.time() - T0:6.0f}s] {m}", flush=True) # 1. Satélite (celda v1-v2 no homónima) y E-M5 sat = pq.read_table("/af/em3/salidas/unified_20261018.borme/empresas.parquet", columns=["empresa_clave", "via", "borme_norm", "homonima"]).to_pandas() sat = sat[sat.via.isin(["v1_exacto", "v2_historico"]) & (sat.homonima == False)] # noqa: E712 em5 = pq.read_table("/af/em5/salidas/17_etiquetas_INTERNO.parquet", columns=["empresa_clave", "etiqueta"]).to_pandas() R_set = set(em5.empresa_clave[em5.etiqueta == "R"]) celda = dict(zip(sat.empresa_clave, sat.borme_norm)) log(f"celda {len(celda)}") # 2. Constituciones del parse t = pq.read_table("/bh/parse/borme_empresas.parquet", columns=["fecha_borme", "empresa", "actos", "fecha_constitucion"]) A_ = pc.fill_null(t.column("actos"), "") t = t.filter(pc.or_(pc.or_(pc.match_substring(A_, "|Constitución|"), pc.starts_with(A_, "Constitución|")), pc.or_(pc.ends_with(A_, "|Constitución"), pc.equal(A_, "Constitución")))) co = t.to_pandas(); del t log(f"filas de constitución {len(co)}") cache = {} def clave(e): if e is None: return None k = cache.get(e) if k is None: k = borme_norm_de_nn(nn(e)); cache[e] = k if k is not None else "" return k or None co["k"] = [clave(e) for e in co.empresa] co = co[co.k.notna()] co["d_pub"] = pd.to_datetime(co.fecha_borme).dt.date RX = re.compile(r"^\s*(\d{1,2})\.(\d{1,2})\.(\d{2,4})") def leer_fc(s): if not isinstance(s, str): return None m = RX.match(s) if not m: return None d, mo, y = int(m.group(1)), int(m.group(2)), m.group(3) y = 2000 + int(y) if len(y) == 2 else int(y) try: return dt.date(y, mo, d) except ValueError: return None co["fc"] = [leer_fc(s) for s in co.fecha_constitucion] def fconst(fc, dp): if fc is not None and fc <= dp and (dp - fc).days <= 365: return fc return dp co["f"] = [fconst(a, b) for a, b in zip(co.fc, co.d_pub)] g = co.groupby("k").f.agg(["min", "max"]) f_const = g["min"].to_dict() ambigua = {k for k, (a, b) in zip(g.index, zip(g["min"], g["max"])) if (b - a).days > 60} log(f"sociedades con constitución {len(f_const)}; ambiguas {len(ambigua)}") del co, cache # 3. Población (todas las filas para F0; 2015-2025 para P) af = pq.read_table(f"{D}.identidad/adjudicatarios_filas.parquet", columns=["adjudicatario_nif_raw", "adjudicatario_nif", "adjudicatario_nombre", "empresa_clave", "persona_fisica"]).to_pandas() of = pq.read_table(f"{D}.identidad/organos_filas.parquet", columns=["organo_nif", "organo_nombre", "dir3", "organo_clave"]).to_pandas() ot = pq.read_table(f"{D}.identidad/organos_tipo.parquet", columns=["organo_clave", "tipo"]).to_pandas() R["organos_tipo_duplicados"] = int(ot.organo_clave.duplicated().sum()) tipo_org = ot.drop_duplicates("organo_clave").set_index("organo_clave").tipo.to_dict() vf = pq.read_table(f"{D}.vinculos/filas.parquet", columns=["source", "id_canonical", "cuenta"]).to_pandas() no_cuenta = set(zip(vf.source[vf.cuenta == False], vf.id_canonical[vf.cuenta == False])) # noqa: E712 del vf COLS = ["id_canonical", "unidad", "es_menor_dudoso", "anulado_en_origen", "copia_de", "correccion_imp_adj", "anio_ref", "fecha_adj", "fecha_formalizacion", "es_menor", "imp_adj_eur_comparable", "imp_adj_eur_sin_iva", "imp_adj_eur", "cpv_division", "tipo_contrato", "adjudicatario_nif_raw", "adjudicatario_nif", "adjudicatario_nombre", "organo_nif", "organo_nombre", "dir3"] dset = ds.dataset(D, format="parquet", partitioning="hive") F0 = {}; partes = []; EMP, ORG, CPV = {}, {}, {}; TC = {'Obras': 0, 'Servicios': 1, 'Suministros': 2} def cod(d, vals): return np.fromiter((d.setdefault(v, len(d)) for v in vals), dtype=np.int32, count=len(vals)) def a_fecha(s): return pd.to_datetime(s, errors="coerce").dt.date for tz in sorted({os.path.dirname(p) for p in dset.files}): src = tz.split("source=")[1].split("/")[0] sub = ds.dataset(tz, format="parquet") cols = [c for c in COLS if c in sub.schema.names] df = sub.to_table(columns=cols, filter=(ds.field("unidad") == "adjudicacion")).to_pandas() for c in COLS: if c not in df.columns: df[c] = None df["source"] = src m = (~df.es_menor_dudoso.fillna(False).astype(bool) & ~df.anulado_en_origen.fillna(False).astype(bool) & df.copia_de.isna() & ~df.correccion_imp_adj.isin(["copia_otra_fuente", "repetido", "inverosimil"])) df = df[m] if no_cuenta and len(df): df = df.loc[np.array([(s, i) not in no_cuenta for s, i in zip(df.source, df.id_canonical)], dtype=bool)] df = df.merge(af, how="left", on=["adjudicatario_nif_raw", "adjudicatario_nif", "adjudicatario_nombre"]) fa, ff = a_fecha(df.fecha_adj), a_fecha(df.fecha_formalizacion) df["F"] = [a if a is not None and a == a else b for a, b in zip(fa, ff)] df["F"] = [None if (x is None or x != x) else x for x in df.F] lo_, hi_ = dt.date(2000, 1, 1), dt.date(2026, 10, 16) for e, f in zip(df.empresa_clave, df.F): if e is None or e != e or f is None or not (lo_ <= f <= hi_): continue if e not in F0 or f < F0[e]: F0[e] = f df = df[(df.anio_ref >= 2015) & (df.anio_ref <= 2025) & df.empresa_clave.notna() & ~df.persona_fisica.fillna(False).astype(bool)] df = df.merge(of, how="left", on=["organo_nif", "organo_nombre", "dir3"]) df = df[df.organo_clave.notna() & df.empresa_clave.isin(celda.keys())] claves = np.asarray((df.id_canonical.astype(object).astype(str) + "|" + src).to_numpy(), dtype=object) partes.append(dict(key=pd.util.hash_array(claves), F=pd.to_datetime(pd.Series(list(df.F), dtype=object), errors="coerce").to_numpy().astype("datetime64[D]"), menor=(df.es_menor == True).to_numpy(), # noqa: E712 cpv=cod(CPV, [v if isinstance(v, str) else "??" for v in df.cpv_division]), tc=np.array([TC.get(v, 3) for v in df.tipo_contrato], dtype=np.int8), org=cod(ORG, df.organo_clave.tolist()), emp=cod(EMP, df.empresa_clave.tolist()), anio=df.anio_ref.to_numpy().astype(np.int16))) log(f"{src}: {len(df)} filas de la celda") if os.environ.get("PRUEBA") and len(partes) >= 40: break del af, of # 4. Evaluables y joven (vectorizado, con códigos enteros) A = {k: np.concatenate([p_[k] for p_ in partes]) for k in partes[0]} del partes emp_l = [None] * len(EMP) for e, i_ in EMP.items(): emp_l[i_] = e fc_e = pd.to_datetime(pd.Series([f_const.get(celda.get(e)) for e in emp_l], dtype=object), errors="coerce") f0_e = pd.to_datetime(pd.Series([F0.get(e) for e in emp_l], dtype=object), errors="coerce") excl_e = np.array([(e in R_set) or (celda.get(e) in ambigua) for e in emp_l], dtype=bool) fc12_e = (fc_e + pd.DateOffset(months=12)).to_numpy().astype("datetime64[D]") fc6_e = (fc_e + pd.DateOffset(months=6)).to_numpy().astype("datetime64[D]") refut_e = (fc_e.notna() & f0_e.notna() & (f0_e < fc_e - pd.Timedelta(days=30))).to_numpy() tiene_fc = fc_e.notna().to_numpy() F = A["F"]; e_ = A["emp"] m_ = ~np.isnat(F) & (F >= np.datetime64("2009-01-02")) & (F <= np.datetime64("2026-10-16")) & ~excl_e[e_] & ~refut_e[e_] org_l = [None] * len(ORG) for o_, i_ in ORG.items(): org_l[i_] = o_ TO = {} to_o = cod(TO, [tipo_org.get(o_) or "sin_tipo" for o_ in org_l]) ev = pd.DataFrame({"key": A["key"][m_], "anio": A["anio"][m_], "estrato": np.where(A["menor"][m_], "menor", "no_menor"), "cpv": A["cpv"][m_], "tc": A["tc"][m_], "to": to_o[A["org"][m_]], "organo_clave": A["org"][m_], "joven": tiene_fc[e_[m_]] & (F[m_] < fc12_e[e_[m_]]), "joven6": tiene_fc[e_[m_]] & (F[m_] < fc6_e[e_[m_]])}) del A, F, e_, m_ del no_cuenta, dset, sat, em5, g, ot import gc; gc.collect() for c_ in ("estrato",): ev[c_] = ev[c_].astype("category") log(f"evaluables impl2 {len(ev)}; jóvenes {int(ev.joven.sum())}") # 5. Tasa esperada y embudo def embudo(ev, col): ev["y"] = ev[col].astype(np.int8) T = ev.groupby(["tc", "to", "anio", "estrato"], observed=True).y.mean().rename("pT") M = ev.groupby(["cpv", "tc", "to", "anio", "estrato"], observed=True).y.agg(nm="size", km="sum") MO = ev.groupby(["cpv", "tc", "to", "anio", "estrato", "organo_clave"], observed=True).y.agg(nmo="size", kmo="sum").reset_index() MO = MO.join(M, on=["cpv", "tc", "to", "anio", "estrato"]).join(T, on=["tc", "to", "anio", "estrato"]) MO["p"] = (MO.km - MO.kmo + 50 * MO.pT) / (MO.nm - MO.nmo + 50) MO["E"] = MO.nmo * MO.p; MO["V"] = MO.nmo * MO.p * (1 - MO.p) oa = MO.groupby(["organo_clave", "anio", "estrato"], observed=True).agg(n=("nmo", "sum"), k=("kmo", "sum"), E=("E", "sum"), V=("V", "sum")).reset_index() oa["r"] = oa.k / oa.n; oa["e"] = oa.E / oa.n; oa["s"] = np.sqrt(oa.V) / oa.n taus = {} for (a, est), g in oa[oa.n >= 30].groupby(["anio", "estrato"]): g = g[g.s > 0]; z = ((g.r - g.e) / g.s).to_numpy(); I = len(z) if I < 2: taus[(a, est)] = 0.0; continue zw = np.clip(z, np.percentile(z, 10), np.percentile(z, 90)); phi = float(np.mean(zw ** 2)) w = 1 / g.s.to_numpy() ** 2 taus[(a, est)] = max((I * phi - (I - 1)) / (w.sum() - (w ** 2).sum() / w.sum()), 0.0) if I * phi > I - 1 else 0.0 oa["tau2"] = [taus.get((a, e), 0.0) for a, e in zip(oa.anio, oa.estrato)] den = np.sqrt(oa.s ** 2 + oa.tau2) oa["zd"] = np.where(den > 0, (oa.r - oa.e) / den.replace(0, np.nan), 0.0) oa["hecho"] = (oa.n >= 30) & (oa.zd > 3.090232) return oa oa2 = embudo(ev, "joven") R["impl2"] = {"evaluables": len(ev), "jovenes": int(ev.joven.sum()), "jovenes6": int(ev.joven6.sum()), "organo_anio": len(oa2), "n30": int((oa2.n >= 30).sum()), "hecho": int(oa2.hecho.sum()), "hecho_por_estrato": {k: int(v) for k, v in oa2.groupby("estrato").hecho.sum().items()}} log(R["impl2"]) # 6. Guardar (el cotejo con la principal va en 05b, en otro proceso, por memoria) SUF = "_prueba" if os.environ.get("PRUEBA") else "" ev[["key", "joven", "joven6"]].to_parquet(f"/w/interno/impl2_ev{SUF}.parquet", index=False) oa2["organo_clave"] = [org_l[i_] for i_ in oa2.organo_clave] oa2[["organo_clave", "anio", "estrato", "n", "k", "E", "zd", "hecho"]].to_parquet(f"/w/interno/impl2_oa{SUF}.parquet", index=False) json.dump(R, open(f"/w/tmp/05_impl2_recuentos{SUF}.json", "w"), ensure_ascii=False, indent=1, default=str) log("guardado")