"""MK1 · 30 · SEGUNDA IMPLEMENTACIÓN (pandas + pyarrow, sin DuckDB, sin importar 01/11/comun). Rehace la base desde los parquet de unified y los screens con código propio, mismo contrato de generador (§3). Escribe interno/impl2_unidades.parquet.""" import hashlib, time, json import numpy as np, pandas as pd, pyarrow.dataset as ds, pyarrow.compute as pc, pyarrow.parquet as pq T0 = time.time(); D = '/d/unified_20261018'; B = 200 def log(*a): print(f"[{time.time()-T0:6.0f}s]", *a, flush=True) NUL = '\x00' cols = ['id_canonical', 'source', 'tabla_origen', 'licitacion_id', 'lote_id', 'organo_nif', 'organo_nombre', 'dir3', 'adjudicatario_nif_raw', 'adjudicatario_nif', 'adjudicatario_nombre', 'procedimiento', 'cpv_division', 'num_ofertas', 'imp_lic_eur', 'imp_lic_reparto', 'imp_adj_eur', 'anio_ref', 'fecha_adj', 'fecha_pub', 'copia_de', 'correccion_imp_adj', 'anulado_en_origen', 'rol_am', 'tramitacion', 'es_menor'] dset = ds.dataset(f'{D}/source=PLACSP', format='parquet', partitioning='hive') have = set(dset.schema.names); cols2 = [c for c in cols if c in have] flt = (pc.field('unidad') == 'adjudicacion') & (pc.field('es_contrato') == True) & (pc.field('vigente') == True) & \ (pc.field('anio_ref') >= 2012) & (pc.field('anio_ref') <= 2025) df = dset.to_table(columns=cols2, filter=flt).to_pandas() if 'source' not in df: df['source'] = 'PLACSP' df = df[(df.source == 'PLACSP') & df.tabla_origen.isin(['resultados_licitaciones', 'resultados_agregacion'])] df = df[~df.es_menor.fillna(False).astype(bool)] df = df[df.procedimiento.isin(['Abierto', 'Restringido', 'Negociado_publicidad', 'Dialogo_competitivo', 'Asociacion_innovacion'])] df = df[~df.rol_am.fillna('').isin(['basado', 'basado_sda']) & (df.tramitacion.fillna('') != 'emergencia')] log('filas', len(df)) v = pq.read_table(f'{D}.vinculos/filas.parquet', columns=['source', 'id_canonical', 'cuenta']).to_pandas() v = v[v.source == 'PLACSP'].drop(columns='source') df = df.merge(v, on='id_canonical', how='left') copia = df.copia_de.notna() | (df.cuenta == False) | (df.correccion_imp_adj == 'copia_otra_fuente') df = df[~copia & ~df.anulado_en_origen.fillna(False).astype(bool)].copy() of = pq.read_table(f'{D}.identidad/organos_filas.parquet', columns=['organo_nif', 'organo_nombre', 'dir3', 'organo_clave']).to_pandas().drop_duplicates() for k in ['organo_nif', 'organo_nombre', 'dir3']: of[k] = of[k].fillna(NUL); df[k] = df[k].fillna(NUL) df = df.merge(of, on=['organo_nif', 'organo_nombre', 'dir3'], how='left'); df = df[df.organo_clave.notna()] af = pq.read_table(f'{D}.identidad/adjudicatarios_filas.parquet', columns=['adjudicatario_nif_raw', 'adjudicatario_nif', 'adjudicatario_nombre', 'empresa_clave']).to_pandas() for k in ['adjudicatario_nif_raw', 'adjudicatario_nif', 'adjudicatario_nombre']: af[k] = af[k].fillna(NUL); df[k] = df[k].fillna(NUL) df = df.merge(af, on=['adjudicatario_nif_raw', 'adjudicatario_nif', 'adjudicatario_nombre'], how='left') nif = df.adjudicatario_nif.where(df.adjudicatario_nif != NUL).str.strip().replace('', np.nan) nom = df.adjudicatario_nombre.where(df.adjudicatario_nombre != NUL).str.strip().str.upper().replace('', np.nan) df['ek'] = df.empresa_clave.fillna('X:' + nif).fillna('Y:' + nom) df['feed'] = np.where(df.tabla_origen == 'resultados_licitaciones', 'p', 'a') df['lote_id'] = df.lote_id.fillna('') df['lote_key'] = df.feed + '|' + df.licitacion_id + '|' + df.lote_id log('universo filas', len(df), 'lotes', df.lote_key.nunique()) # lote df = df.sort_values('id_canonical') g = df.groupby('lote_key', sort=False) lot = g.first()[['organo_clave', 'cpv_division', 'anio_ref', 'fecha_adj', 'fecha_pub', 'imp_lic_eur', 'imp_adj_eur', 'imp_lic_reparto', 'licitacion_id']] lot['nof'] = g.num_ofertas.max(); lot['nfil'] = g.size() nadj = df.groupby('licitacion_id').size() lot['nadjlic'] = nadj.reindex(lot.licitacion_id).values okb = (lot.nfil == 1) & (lot.imp_lic_eur > 0) & (lot.imp_adj_eur > 0) & ((lot.imp_lic_reparto == 'por_lote') | (lot.nadjlic == 1)) lot['baja'] = np.where(okb, np.clip(1 - lot.imp_adj_eur / lot.imp_lic_eur, -0.5, 1), np.nan) lot['cpv'] = lot.cpv_division.fillna('??'); lot = lot[lot.cpv != '??'].copy(); lot['anio'] = lot.anio_ref.astype(int) lot = lot.sort_index() # lote_key # ganadores con UTE descompuesta us = pq.read_table(f'{D}.identidad/ute_socios.parquet', columns=['ute_clave', 'socio_nif']).to_pandas().dropna().drop_duplicates() r = df[['lote_key', 'id_canonical', 'ek']].copy(); r['w'] = 1.0 / r.groupby('lote_key').lote_key.transform('size') r = r.merge(us, left_on='ek', right_on='ute_clave', how='left') r['ent'] = r.socio_nif.fillna(r.ek); r['w'] = r.w / r.groupby(['lote_key', 'id_canonical']).id_canonical.transform('size') gan = r.groupby(['lote_key', 'ent'], as_index=False).w.sum() gan = gan[gan.lote_key.isin(lot.index)] log('ganadores', len(gan)) # estructuras keys = lot.index.values; kpos = pd.Series(np.arange(len(keys)), index=keys) eid, enames = pd.factorize(gan.ent, sort=True) gan = gan.assign(i=kpos.loc[gan.lote_key].values, e=eid).sort_values(['i', 'e']) WIN = {} # i -> (ents, ws) gi = gan.i.values; ge = gan.e.values; gw = gan.w.values cut = np.flatnonzero(np.diff(gi)) + 1; parts_e = np.split(ge, cut); parts_w = np.split(gw, cut); first = gi[np.r_[0, cut]] ENTS = [None] * len(keys); WS = [None] * len(keys) for a, pe, pw in zip(first, parts_e, parts_w): ENTS[a] = pe; WS[a] = pw setkey = np.array([','.join(map(str, e)) for e in ENTS]); SETID = pd.factorize(setkey)[0] org = lot.organo_clave.values; cpv = lot.cpv.values; anio = lot.anio.values; nof = lot.nof.values.astype(float); baja = lot.baja.values fa = pd.to_datetime(lot.fecha_adj, errors='coerce'); fp = pd.to_datetime(lot.fecha_pub, errors='coerce') o_df = pd.DataFrame({'na': fa.isna().values, 'fa': fa.values, 'nb': fp.isna().values, 'fp': fp.values, 'k': keys}) orden = o_df.sort_values(['na', 'fa', 'nb', 'fp', 'k'], kind='mergesort').index.values; rango = np.empty(len(keys), int); rango[orden] = np.arange(len(keys)) NEN = len(enames) def gen(key, st): return np.random.default_rng(int(hashlib.md5(f'{key}|{st}'.encode()).hexdigest()[:15], 16)) def zz(obs, nul): sd = np.std(nul, ddof=1) mu = np.mean(nul) return 0.0 if np.isnan(sd) or sd <= 1e-9 * max(1.0, abs(mu)) else (obs - mu) / sd def idisp(M): M = np.atleast_2d(M); n = M.shape[1]; mu = M.sum(1) / n return ((M - mu[:, None]) ** 2).sum(1) / (n - 1) / mu def neff(idx2): out = np.empty(idx2.shape[0]); n = idx2.shape[1] for r_ in range(idx2.shape[0]): e = np.concatenate([ENTS[i] for i in idx2[r_]]); w = np.concatenate([WS[i] for i in idx2[r_]]) o = np.argsort(e, kind='mergesort'); e = e[o]; w = w[o] b = np.r_[0, np.flatnonzero(np.diff(e)) + 1]; p = np.add.reduceat(w, b) / n out[r_] = np.exp(-(p * np.log(p)).sum()) return out def vshare(lots, Y): e = np.concatenate([ENTS[i] for i in lots]); w = np.concatenate([WS[i] for i in lots]); li = np.concatenate([np.full(len(ENTS[i]), j) for j, i in enumerate(lots)]) ue, el = np.unique(e, return_inverse=True); n = len(lots) pbar = np.zeros(len(ue)); np.add.at(pbar, el, w); pbar /= n res = [] for y in Y: ny = np.array([(y == k).sum() for k in range(3)], float) S = np.zeros((len(ue), 3)); np.add.at(S, (el, y[li]), w); S /= ny res.append(float((pbar * ((S - S.mean(1, keepdims=True)) ** 2).mean(1)).sum())) return np.array(res) out = [] for t in range(2014, 2026): inw = np.flatnonzero((anio >= t - 2) & (anio <= t)) for dv in sorted(set(cpv[inw])): m = inw[cpv[inw] == dv] for o in pd.unique(org[m]): L_ = m[org[m] == o] if len(L_) < 15: continue key = f'{o}|{dv}|{t}'; rec = {'unidad': key, 'n': len(L_)}; otro = m[org[m] != o] x = L_[nof[L_] >= 2]; p1 = otro[nof[otro] >= 2] if len(x) >= 15 and len(p1) >= 50: rec['D'] = float(idisp(nof[x])[0]); d = gen(key, 'S1').integers(0, len(p1), size=(B, len(x))); rec['z1'] = zz(rec['D'], idisp(nof[p1][d])) rec['Neff'] = float(neff(L_[None, :])[0]) if len(otro) >= 50: d = gen(key, 'S2a').integers(0, len(otro), size=(B, len(L_))); rec['z2a'] = zz(rec['Neff'], neff(otro[d])) sq = L_[np.argsort(rango[L_], kind='mergesort')]; ids = SETID[sq]; n = len(L_) rec['Sw'] = int(np.sum(ids[1:] != ids[:-1])); gg = gen(key, 'S2b') nul = [np.sum(ids[pp][1:] != ids[pp][:-1]) for pp in (gg.permutation(n) for _ in range(B))]; rec['z2b'] = zz(rec['Sw'], np.array(nul, float)) y = anio[L_] - (t - 2) if min((y == k).sum() for k in range(3)) >= 5: rec['V'] = float(vshare(L_, [y])[0]); gg = gen(key, 'S3'); rec['z3'] = zz(rec['V'], vshare(L_, [y[gg.permutation(n)] for _ in range(B)])) bb = L_[~np.isnan(baja[L_])]; p4 = otro[~np.isnan(baja[otro])] if len(bb) >= 15 and len(p4) >= 50: d = gen(key, 'S4').integers(0, len(p4), size=(B, len(bb))); vv = baja[p4][d] rec['bm'] = float(np.mean(baja[bb])); rec['bs'] = float(np.std(baja[bb], ddof=1)) rec['z4a'] = zz(rec['bm'], vv.mean(1)); rec['z4b'] = zz(rec['bs'], vv.std(1, ddof=1)) out.append(rec) log('t', t, len(out)) R = pd.DataFrame(out); R['t'] = R.unidad.str.rsplit('|', n=1).str[1].astype(int) ori = {'a1': -R.z1, 'a2a': -R.z2a, 'a2b': R.z2b, 'a3': -R.z3, 'a4a': -R.z4a, 'a4b': -R.z4b} for k, s in ori.items(): med = s.groupby(R.t).transform('median'); mad = (s - med).abs().groupby(R.t).transform('median') * 1.4826 sd = s.groupby(R.t).transform('std'); R[k] = (s - med) / mad.where(mad > 0, sd) R['T1'] = R.a1; R['T2'] = (R.a2a + R.a2b) / 2; R['T3'] = R.a3; R['T4'] = (R.a4a + R.a4b) / 2 T = R[['T1', 'T2', 'T3', 'T4']]; nn = T.notna().sum(1); R['Cc'] = T.mean(1).where(nn >= 3); R['hh'] = (nn >= 3) & ((T >= 2.326).sum(1) >= 2) R.to_parquet('/w/interno/impl2_unidades.parquet', index=False); log('fin', len(R))