"""REL1 · 23 · Validación frente a etiquetas (P7; P1 y P6 como indicador), impl1. DEFINICIONES_REL1.md §12 (+ E2). Unidad: licitación (máximo sobre sus lotes evaluables de la población). Puntuaciones: V, s_vinc, R, n_scr. Positivas: banco (V3 ∪ V2) o grupo «positivo» del piloto; controles: piloto «control» (ACTAS_A) o mismo mercado-fuente y año ± 1 (TED, CANT). Potencia: >= 10 positivas y >= 10 controles. AUC estratificada (fuente × expediente × año), IC bootstrap de licitaciones dentro del estrato (2.000, semilla 20261026) y, como sensibilidad, de órganos. Lift MH de V y de R. Secundaria por pares: parejas que co-licitan con las dos en el fallo del mismo expediente. Salidas: salidas/23_validar.json, interno/23_validar_lic.parquet.""" import duckdb, json, random, re, time, collections, math, os, unicodedata T0 = time.time() def log(*a): print(f'[{time.time()-T0:6.1f}s]', *a, flush=True) SEED = 20261026; B = 2000 SUF = '_ge1' if os.environ.get('GAN') == 'ge1' else '' con = duckdb.connect(); x = con.execute x("SET threads=6; SET memory_limit='8GB'") x("CREATE TABLE lm AS SELECT * FROM read_parquet('/r/interno/lote_metricas.parquet') WHERE pop") x(f"CREATE TABLE lr AS SELECT * FROM read_parquet('/r/interno/lote_red{SUF}.parquet')") x("CREATE TABLE et AS SELECT * FROM read_parquet('/r/interno/etiquetas_lote.parquet')") x("""CREATE TABLE lot AS SELECT lm.*, coalesce(lr.r, false) r, coalesce(lr.n_scr, 0) n_scr, et.exp_banco, et.var_banco, et.grupo_piloto, et.exp_piloto FROM lm LEFT JOIN lr USING (fuente, lote_k) LEFT JOIN et USING (fuente, lote_k)""") x("""CREATE TABLE lic AS SELECT fuente, lic_k, min(anio) anio, any_value(mercado) mercado, any_value(organo_clave) organo, max(v::INT) v, max(s_vinc) s, max(r::INT) r, max(n_scr) nscr, bool_or(var_banco IS NOT NULL) banco, bool_or(grupo_piloto = 'positivo') piloto_pos, bool_or(grupo_piloto = 'control') piloto_ctl, coalesce(any_value(exp_piloto), any_value(exp_banco)) exp FROM lot GROUP BY 1, 2""") # positivas y controles x("ALTER TABLE lic ADD COLUMN rol VARCHAR") x("UPDATE lic SET rol = 'pos' WHERE banco OR piloto_pos") x("UPDATE lic SET rol = 'ctl' WHERE rol IS NULL AND fuente = 'ACTAS_A' AND piloto_ctl") x("""UPDATE lic SET rol = 'ctl' WHERE rol IS NULL AND fuente <> 'ACTAS_A' AND EXISTS ( SELECT 1 FROM lic p WHERE p.rol = 'pos' AND p.fuente = lic.fuente AND p.mercado = lic.mercado AND abs(p.anio - lic.anio) <= 1)""") # estrato: fuente × expediente × año (controles del piloto: su expediente; TED/CANT: el de la positiva de su mercado) x("ALTER TABLE lic ADD COLUMN estrato VARCHAR") x("UPDATE lic SET estrato = fuente || '|' || coalesce(exp, '?') || '|' || coalesce(anio::VARCHAR, '?') WHERE rol IS NOT NULL") x("COPY (SELECT * FROM lic WHERE rol IS NOT NULL) TO '/r/interno/23_validar_lic.parquet' (FORMAT PARQUET)") R = {'seed': SEED, 'recuento': x("""SELECT fuente, rol, count(*), count(DISTINCT exp), sum(v), sum(r) FROM lic WHERE rol IS NOT NULL GROUP BY 1, 2 ORDER BY 1, 2""").fetchall()} def auc_strat(rows): """rows: (estrato, rol, score). AUC = sum de pares dentro del estrato (empates 1/2) / nº de pares.""" by = collections.defaultdict(lambda: ([], [])) for s, rol, sc in rows: by[s][0 if rol == 'pos' else 1].append(sc) num = den = 0.0 for s, (p, c) in by.items(): if not p or not c: continue cs = sorted(c) import bisect for v in p: lo = bisect.bisect_left(cs, v); hi = bisect.bisect_right(cs, v) num += lo + 0.5 * (hi - lo) den += len(p) * len(c) return num / den if den else None def lift_mh(rows): by = collections.defaultdict(lambda: [0, 0, 0, 0]) for s, rol, sc in rows: t = by[s] if rol == 'pos': t[0] += 1; t[1] += sc > 0 else: t[2] += 1; t[3] += sc > 0 num = den = 0.0 for n1, a, n0, c in by.values(): N = n1 + n0 if n1 == 0 or n0 == 0: continue num += a * n0 / N; den += c * n1 / N return num / den if den else (float('inf') if num else None) def boot(rows, fn, unit_rows=None, seed=SEED): rg = random.Random(seed) by = collections.defaultdict(list) for r in rows: by[(r[0], r[1])].append(r) vals = [] for _ in range(B): s = [] for k, v in by.items(): s.extend(v[rg.randrange(len(v))] for _ in v) e = fn(s); vals.append(e if e is not None else float('nan')) vals = sorted(v for v in vals if v == v) return (vals[int(0.025 * len(vals))], vals[int(0.975 * len(vals)) - 1]) if vals else None def boot_org(rows4, fn, seed=SEED + 7): """bootstrap de órganos (todas sus licitaciones): rows4 = (estrato, rol, score, organo).""" rg = random.Random(seed); og = collections.defaultdict(list) for r in rows4: og[r[3]].append(r[:3]) ks = list(og); vals = [] for _ in range(B): s = [] for _k in ks: s.extend(og[ks[rg.randrange(len(ks))]]) e = fn(s); vals.append(e if e is not None else float('nan')) vals = sorted(v for v in vals if v == v) return (vals[int(0.025 * len(vals))], vals[int(0.975 * len(vals)) - 1]) if vals else None def evalua(where, nombre): rows4 = x(f"SELECT estrato, rol, v, s, r, nscr, coalesce(organo, lic_k) FROM lic WHERE rol IS NOT NULL AND {where} ORDER BY fuente, lic_k").fetchall() npos = sum(1 for r in rows4 if r[1] == 'pos'); nctl = len(rows4) - npos out = {'positivas': npos, 'controles': nctl, 'potencia': npos >= 10 and nctl >= 10} if not out['potencia']: return out for j, sc in ((2, 'V'), (3, 's_vinc'), (4, 'R'), (5, 'n_scr')): rows = [(r[0], r[1], r[j]) for r in rows4] a = auc_strat(rows); out[f'auc_{sc}'] = a; out[f'ic_{sc}'] = boot(rows, auc_strat) out[f'ic_org_{sc}'] = boot_org([(r[0], r[1], r[j], r[6]) for r in rows4], auc_strat) if sc in ('V', 'R'): out[f'lift_{sc}'] = lift_mh(rows); out[f'ic_lift_{sc}'] = boot(rows, lift_mh, seed=SEED + 1) out[f'tasa_pos_{sc}'] = sum(r[j] > 0 for r in rows4 if r[1] == 'pos') / npos out[f'tasa_ctl_{sc}'] = sum(r[j] > 0 for r in rows4 if r[1] == 'ctl') / nctl return out R['por_fuente'] = {f: evalua(f"fuente = '{f}'", f) for (f,) in x("SELECT DISTINCT fuente FROM lic ORDER BY 1").fetchall()} con_pot = [f for f, v in R['por_fuente'].items() if v['potencia']] R['conjunta'] = evalua("fuente IN (" + ','.join(f"'{f}'" for f in con_pot) + ")", 'conjunta') if con_pot else {'potencia': False} R['por_expediente'] = {} for f, e in x("SELECT DISTINCT fuente, exp FROM lic WHERE rol = 'pos' ORDER BY 1, 2").fetchall(): R['por_expediente'][f'{f}|{e}'] = evalua(f"fuente = '{f}' AND exp = '{e}'", e) log('lic', json.dumps(R, default=str)[:3000]) # ---------- secundaria por pares def sa(s): return ''.join(ch for ch in unicodedata.normalize('NFKD', s or '') if not unicodedata.combining(ch)).lower() def nn(s): return re.sub(r'[^0-9a-z]+', ' ', sa(s)).strip() FJ = r'( (s a u|s a|s l u|s l p|s l|s a l|s c a|s coop|sociedad anonima|sociedad limitada|sociedad cooperativa andaluza|unipersonal|sau|slu|sa|sl|sca|sal))+$' fe = [] # (exp, via, clave) # P8 EMP: solo las filas de S/0016/21, S/0008/21 y S/0013/19 (las demás no se leen: se filtran por la línea) RX = re.compile(r"^\s*\('(S/0016/21|S/0008/21|S/0013/19)',\s*'[^']*',\s*'([A-Z0-9]+)'") for line in open('/p8sql/c06_carteles.py', encoding='utf-8'): m = RX.match(line) if m: fe.append((m.group(1), 'nif', m.group(2))) for f in ('/col/d5/interno/fallo_d5.json', '/col/d5b/interno/fallo_acco.json'): for e, v in json.load(open(f)).items(): for n in v.get('nifs', []): fe.append((e, 'nif', n.upper())) if not v.get('nifs'): for n in v.get('empresas', []): k = re.sub(FJ, '', nn(n)).strip() if len(k) >= 4: fe.append((e, 'nombre', k)) for e, v in json.load(open('/col/etiquetas/casado/interno/c1_fallo.json')).items(): for n in v.get('empresas', []): n2 = re.sub(r'^(?:[A-Z]{2,5}\.\s*)?\d+\.\s*', '', n).strip() k = re.sub(FJ, '', nn(n2)).strip() if len(k) >= 4: fe.append((e, 'nombre', k)) x("CREATE TABLE fe (exp VARCHAR, via VARCHAR, k VARCHAR)") x("INSERT INTO fe SELECT * FROM (VALUES " + ','.join('(?,?,?)' for _ in fe) + ")", [t for r in fe for t in r]) x(f"""CREATE TABLE femp AS SELECT DISTINCT fe.exp, a.empresa_clave FROM fe JOIN ( SELECT DISTINCT empresa_clave, upper(adjudicatario_nif) nif, trim(regexp_replace(regexp_replace(lower(strip_accents(adjudicatario_nombre)), '[^0-9a-z]+', ' ', 'g'), '{FJ}', '')) k5 FROM read_parquet('/d/unified_20261025.identidad/adjudicatarios_filas.parquet') WHERE NOT coalesce(persona_fisica, false) AND empresa_clave IS NOT NULL) a ON (fe.via = 'nombre' AND a.k5 = fe.k) OR (fe.via = 'nif' AND (a.nif = fe.k OR a.empresa_clave = fe.k)) UNION SELECT DISTINCT exp, k FROM fe WHERE via = 'nif'""") R['sancionadas'] = {'filas_fallo': len(fe), 'claves': x("SELECT count(DISTINCT empresa_clave), count(DISTINCT exp) FROM femp").fetchone()} x(f"CREATE TABLE rp AS SELECT * FROM read_parquet('/r/interno/red_pares{SUF}.parquet')") x("""CREATE TABLE sp AS SELECT fa.empresa_clave a, fb.empresa_clave b, string_agg(DISTINCT fa.exp, '+' ORDER BY fa.exp) exp_par FROM femp fa JOIN femp fb ON fa.exp = fb.exp AND fa.empresa_clave < fb.empresa_clave GROUP BY 1, 2""") x("CREATE TABLE sk AS SELECT DISTINCT empresa_clave k FROM femp") x("""CREATE TABLE pp AS SELECT r.*, sp.exp_par, (r.a IN (SELECT k FROM sk) OR r.b IN (SELECT k FROM sk)) alguna_sanc FROM rp r LEFT JOIN sp ON sp.a = r.a AND sp.b = r.b""") x("ALTER TABLE pp ADD COLUMN rol VARCHAR") x("UPDATE pp SET rol = 'pos' WHERE exp_par IS NOT NULL") x("""UPDATE pp SET rol = 'ctl' WHERE rol IS NULL AND NOT alguna_sanc AND fuente || '|' || mercado IN (SELECT DISTINCT fuente || '|' || mercado FROM pp WHERE exp_par IS NOT NULL)""") rows = x("SELECT fuente || '|' || mercado, rol, vinc::INT, n1::INT, n2::INT, n3::INT, w, exp_par FROM pp WHERE rol IS NOT NULL ORDER BY fuente, mercado, a, b").fetchall() npos = sum(1 for r in rows if r[1] == 'pos'); nexp = len({r[7] for r in rows if r[1] == 'pos'}) R['pares'] = {'positivas': npos, 'expedientes': nexp, 'controles': len(rows) - npos, 'por_exp': x("SELECT exp_par, fuente, count(*) FROM pp WHERE rol = 'pos' GROUP BY 1, 2 ORDER BY 1, 2").fetchall(), 'potencia': npos >= 10 and nexp >= 2} if R['pares']['potencia']: for j, sc in ((2, 'VINC'), (3, 'N1'), (4, 'N2'), (5, 'N3'), (6, 'w')): rr = [(r[0], r[1], r[j]) for r in rows] R['pares'][f'auc_{sc}'] = auc_strat(rr); R['pares'][f'ic_{sc}'] = boot(rr, auc_strat, seed=SEED + 3) if sc != 'w': R['pares'][f'lift_{sc}'] = lift_mh(rr); R['pares'][f'ic_lift_{sc}'] = boot(rr, lift_mh, seed=SEED + 4) R['pares'][f'tasa_pos_{sc}'] = sum(r[j] > 0 for r in rows if r[1] == 'pos') / npos R['pares'][f'tasa_ctl_{sc}'] = sum(r[j] > 0 for r in rows if r[1] == 'ctl') / (len(rows) - npos) # por expediente (criterio «no depende de uno»): AUC de cada puntuación con las positivas de un expediente frente a los # controles de sus mismos mercados-fuente, con >= 10 pares positivos R['pares']['por_expediente'] = {} for e in sorted({r[7] for r in rows if r[1] == 'pos'}): mk = {r[0] for r in rows if r[1] == 'pos' and r[7] == e} sub = [r for r in rows if (r[1] == 'pos' and r[7] == e) or (r[1] == 'ctl' and r[0] in mk)] npe = sum(1 for r in sub if r[1] == 'pos') if npe >= 10: R['pares']['por_expediente'][e] = {'positivas': npe, **{f'auc_{sc}': auc_strat([(r[0], r[1], r[j]) for r in sub]) for j, sc in ((2, 'VINC'), (3, 'N1'), (6, 'w'))}} log('pares', json.dumps(R['pares'], default=str)) json.dump(R, open(f'/r/salidas/23_validar{SUF}.json', 'w'), default=str, indent=1)