"""V-INT · impl. 2 · todo sin DuckDB ni SQL: pyarrow + Python puro. DEFINICIONES_PREVIAS §7 (md5 fe6aa69c…). Uso (en vint-impl2): python3 -I impl2.py Etiquetas releídas de los CSV de ID2 con su propio código; casado por la columna nif de 42_m2_menores_entidad_anio.csv; percentiles con bisect; AUC por recuento de pares; logística por Newton con Cholesky; generador splitmix64.""" import csv, sys, math, os, unicodedata from bisect import bisect_left, bisect_right from collections import defaultdict from decimal import Decimal import pyarrow.parquet as pq IND_DIR, ID2, SAL, SAL_SC = sys.argv[1:5] B = 2000 def norm(s): s = unicodedata.normalize('NFKD', s.lower()) return ''.join(c for c in s if not unicodedata.combining(c)).strip() GRUPOS = [('BB', 'incumplimiento de las obligaciones de publicidad'), ('I', 'contratacion menor: se fracciona'), ('I1', 'contratacion menor: se factura por encima'), ('SE', 'no tramitacion del expediente en contratacion menor'), ('S3', 'no solicitud de al menos tres ofertas'), ('V', 'adjudicacion directa'), ('CC', 'prestaciones sin cobertura contractual'), ('FNM', 'resto de contratos no menores: se fracciona')] def grupo(txt): t = norm(txt) for g, pre in GRUPOS: if t.startswith(pre): return g return None def flo(x): try: return float(x) except (TypeError, ValueError): return None # ---------- etiquetas ---------- t4 = defaultdict(dict) # (tipo, lugar, ej) -> grupo -> max valor for r in csv.DictReader(open(os.path.join(ID2, '15_t4_irregularidades_k.csv'))): if r['entidad_texto'] == 'Total': continue g = grupo(r['irregularidad']) if g: k = (r['tipo'], r['lugar'], int(r['ejercicio'])) v = float(r['valor']) t4[k][g] = max(v, t4[k].get(g, -1)) m2 = {} for r in csv.DictReader(open(os.path.join(ID2, '44_m2_con_nombre.csv'))): m2[(r['tipo'], r['lugar'], int(r['ejercicio']))] = (flo(r['razon_menores']), flo(r['razon_con_nombre'])) nif_de = {} for r in csv.DictReader(open(os.path.join(ID2, '42_m2_menores_entidad_anio.csv'))): if r['nif']: nif_de[(r['tipo'], r['lugar'])] = r['nif'] t3 = defaultdict(list) for r in csv.DictReader(open(os.path.join(ID2, '14_t3_omision_ei_k.csv'))): if r['tipo'] != 'CCAA': t3[(r['tipo'], r['lugar'], int(r['ejercicio']))].append(float(r['valor'])) t2 = defaultdict(list) for r in csv.DictReader(open(os.path.join(ID2, '12_t2_procedimientos_k.csv'))): if r['tipo'] != 'CCAA': t2[(r['tipo'], r['lugar'], int(r['ejercicio']))].append(float(r['total'])) def tamano(tipo, lugar, ej): v = t2.get((tipo, lugar, ej)) tot = max(v) if v else 0 if tot > 0: return math.log1p(tot) otros = sorted(max(vs) for (a, b, e), vs in t2.items() if a == tipo and b == lugar and max(vs) > 0) if not otros: return None n = len(otros) med = otros[n // 2] if n % 2 else (otros[n // 2 - 1] + otros[n // 2]) / 2 return math.log1p(med) banco = [] # (etiqueta, tipo, lugar, ej, y, pct) claves = set(m2) | {k for k, d in t4.items() if 'BB' in d} for k in sorted(claves): rz, rn = m2.get(k, (None, None)) bb = t4.get(k, {}).get('BB') bbp = bb is not None and bb > 0 cand = rz is not None and rz < 0.5 and rn is not None and rn < 0.5 hueco = rz is not None and rz < 0.5 and rn is not None and rn >= 0.5 if bbp or cand: y = 1 elif hueco or (rz is None and bb is None): y = None else: y = 0 if y is not None: banco.append(('NP', *k, y, None)) if not (y == 1 and not bbp): banco.append(('NP_FIRME', *k, y, None)) if not bbp: banco.append(('NP_CAND', *k, y, None)) if bb is not None: banco.append(('NP_BB', *k, 1 if bb > 0 else 0, bb)) for k, d in sorted(t4.items()): mm = [d[g] for g in ('I', 'I1', 'SE', 'S3') if g in d] if mm: banco.append(('MEN', *k, 1 if max(mm) > 0 else 0, max(mm))) for g, et in (('I', 'FRAC'), ('I1', 'UMB'), ('S3', 'S3')): if g in d: banco.append((et, *k, 1 if d[g] > 0 else 0, d[g])) sp = defaultdict(list) for k, vs in t3.items(): sp[k] += vs for k, d in t4.items(): for g in ('CC', 'V', 'FNM'): if g in d: sp[k].append(d[g]) for k, vs in sorted(sp.items()): banco.append(('SP', *k, 1 if max(vs) > 0 else 0, None)) CLASE = {'AY': 'AY', 'DP': 'DP', 'DF': 'DP', 'CABILDO': 'insular', 'CONSELL': 'insular'} # ---------- indicadores órgano-año ---------- ent_de = {} for r in pq.read_table(os.path.join(IND_DIR, 'organos.parquet'), columns=['organo_clave', 'entidad_clave']).to_pylist(): ent_de[r['organo_clave']] = r['entidad_clave'] def leer(nombre, cols): return pq.read_table(os.path.join(IND_DIR, nombre), columns=cols).to_pylist() def D(x): return Decimal(0) if x is None else (x if isinstance(x, Decimal) else Decimal(str(x))) oa = [] # (ind, fuente, tipo, anio, organo, num, den, n_el) for r in leer('descriptores_organo_anio.parquet', ['organo_clave', 'anio', 'tipo', 'menores', 'importe', 'm1a_importe', 'm1r_importe', 'm1e_importe', 'm2_ss_n', 'm2_ss_borde100', 'm6_n', 'm6_n1', 'm6_n3mas', 'm4_elegible', 'm4_cuota_top1_pct']): o, a, t, imp, me = r['organo_clave'], r['anio'], r['tipo'], r['importe'], r['menores'] for ind, col in (('M1r', 'm1r_importe'), ('M1e', 'm1e_importe'), ('M1a', 'm1a_importe')): if imp is not None and r[col] is not None: oa.append((ind, 'PLACSP', t, a, o, D(r[col]), D(imp), me)) if r['m2_ss_n'] is not None and r['m2_ss_borde100'] is not None: oa.append(('M2', 'PLACSP', t, a, o, D(r['m2_ss_borde100']), D(r['m2_ss_n']), r['m2_ss_n'])) if r['m6_n'] is not None: if r['m6_n3mas'] is not None: oa.append(('M6', 'PLACSP', t, a, o, D(r['m6_n'] - r['m6_n3mas']), D(r['m6_n']), r['m6_n'])) if r['m6_n1'] is not None: oa.append(('M6_1', 'PLACSP', t, a, o, D(r['m6_n1']), D(r['m6_n']), r['m6_n'])) if r['m4_elegible'] and r['m4_cuota_top1_pct'] is not None and imp is not None: oa.append(('M4', 'PLACSP', t, a, o, Decimal(repr(float(r['m4_cuota_top1_pct']) * float(imp))), D(imp), me)) for r in leer('m3_organo_anio.parquet', ['organo_clave', 'anio', 'tipo', 'menores', 'm3_n']): if r['menores'] is not None and r['m3_n'] is not None: oa.append(('M3', 'PLACSP', r['tipo'], r['anio'], r['organo_clave'], D(r['m3_n']), D(r['menores']), r['menores'])) for r in leer('c1_organo_anio.parquet', ['organo_clave', 'anio', 'tipo', 'fuente', 'n', 'n_fuera_trim_sig', 'no_comparable']): if r['no_comparable'] is None and r['n'] is not None: oa.append(('C1', r['fuente'], r['tipo'], r['anio'], r['organo_clave'], D(r['n_fuera_trim_sig']), D(r['n']), r['n'])) for r in leer('c2_organo_anio.parquet', ['organo_clave', 'anio', 'tipo', 'fuente', 'poblacion', 'n', 'n_incompleta']): if r['n'] is None: continue if r['poblacion'] == 'menores': oa.append(('C2', r['fuente'], r['tipo'], r['anio'], r['organo_clave'], D(r['n_incompleta']), D(r['n']), r['n'])) if r['fuente'] == 'PLACSP_LICITADOS_PERFIL': oa.append(('C2L', r['fuente'], r['tipo'], r['anio'], r['organo_clave'], D(r['n_incompleta']), D(r['n']), r['n'])) for r in leer('documentos_organo_anio.parquet', ['organo_clave', 'anio', 'tipo', 'licitaciones', 'pcap_n', 'memoria_n', 'aprobacion_n', 'actas_n']): L = r['licitaciones'] if L is None: continue o, a, t = r['organo_clave'], r['anio'], r['tipo'] oa.append(('DOC4', 'PLACSP', t, a, o, D(r['pcap_n']) + D(r['memoria_n']) + D(r['aprobacion_n']) + D(r['actas_n']), D(4 * L), L)) for ind, col in (('PCAP', 'pcap_n'), ('MEMORIA', 'memoria_n'), ('APROB', 'aprobacion_n'), ('ACTAS', 'actas_n')): oa.append((ind, 'PLACSP', t, a, o, D(r[col]), D(L), L)) for r in leer('series_licitadas_organo_anio.parquet', ['organo_clave', 'anio', 'tipo', 'fuente', 'series', 'series_pasaron']): if r['series'] is not None: oa.append(('SERIES', r['fuente'], r['tipo'], r['anio'], r['organo_clave'], D(r['series_pasaron']), D(r['series']), r['series'])) ABAJO = {'DOC4', 'PCAP', 'MEMORIA', 'APROB', 'ACTAS'} def cociente(n, d): return round(float(n / d), 9) if d and d > 0 else None ref = defaultdict(list) # (ind, fuente, tipo, anio) -> valores for ind, f, t, a, o, n, d, ne in oa: if o not in ent_de or d is None or d == 0: continue if ind != 'SERIES' and ne is not None and ne >= 20: ref[(ind, f, t, a)].append(cociente(n, d)) for k in ref: ref[k].sort() def rango_medio(lista, v): lo, hi = bisect_left(lista, v), bisect_right(lista, v) return 100.0 * (lo + 0.5 * (hi - lo)) / len(lista) # puesta en común por entidad-año-fuente (todas las entidades) partes = defaultdict(lambda: [Decimal(0), Decimal(0), 0, defaultdict(int)]) for ind, f, t, a, o, n, d, ne in oa: e = ent_de.get(o) if e is None or d is None or d == 0: continue p = partes[(e, a, ind, f)] p[0] += n p[1] += d p[2] += ne or 0 p[3][t] = max(p[3][t], ne or 0) # tipo = el del órgano con mayor denominador PRIO = {'ayuntamiento': 9, 'local_supramunicipal': 8} def tipo_de(cnt): return max(cnt.items(), key=lambda kv: (kv[1], PRIO.get(kv[0], 1)))[0] pares_s2 = defaultdict(list) # (ind, fuente, tipo, anio) -> [(entidad, n_el, v)] for (e, a, ind, f), (n, d, ne, cnt) in partes.items(): if ind != 'SERIES' and ne >= 20: pares_s2[(ind, f, tipo_de(cnt), a)].append((e, ne, cociente(n, d))) objetivo = {(nif_de.get((tp, lg)), ej) for (_, tp, lg, ej, _, _) in banco if nif_de.get((tp, lg))} por_ea = defaultdict(list) for (e, a, ind, f), val in partes.items(): if (e, a) in objetivo: por_ea[(e, a, ind)].append((f, val)) def s2_de(ind, f, tp, a, e, ne, v): cands = [p for p in pares_s2.get((ind, f, tp, a), []) if p[0] != e] cands.sort(key=lambda p: (abs(math.log(p[1]) - math.log(ne)), p[0])) cands = cands[:50] if len(cands) < 20: return None vs = sorted(p[2] for p in cands) return rango_medio(vs, v) ea = {} for (e, a, ind), lst in por_ea.items(): N = sum(val[0] for _, val in lst) Dd = sum(val[1] for _, val in lst) NE = sum(val[2] for _, val in lst) s1n = s1d = s2n = s2d = 0.0 for f, (n, d, ne, cnt) in lst: v = cociente(n, d) if v is None or ne < 1: continue tp = tipo_de(cnt) if ind != 'SERIES': rl = ref.get((ind, f, tp, a)) if rl and len(rl) >= 20: s1n += rango_medio(rl, v) * ne s1d += ne s2 = s2_de(ind, f, tp, a, e, ne, v) if s2 is not None: s2n += s2 * ne s2d += ne ea[(e, a, ind)] = (NE, cociente(N, Dd), s1n / s1d if s1d else None, s2n / s2d if s2d else None) filas = [] for et, tp, lg, ej, y, pc in banco: nif = nif_de.get((tp, lg)) if not nif: continue for (e, a, ind), (ne, v, s1, s2) in ea.items(): if e == nif and a == ej: dr = -1 if ind in ABAJO else 1 filas.append({'etiqueta': et, 'nif': nif, 'clase': CLASE[tp], 'anio': ej, 'y': y, 'tamano': tamano(tp, lg, ej), 'pct': pc, 'ind': ind, 'n_el': ne, 'v': None if v is None else dr * v, 's1': None if s1 is None else (s1 if dr == 1 else 100 - s1), 's2': None if s2 is None else (s2 if dr == 1 else 100 - s2)}) with open(SAL_SC, 'w', newline='') as fh: w = csv.DictWriter(fh, ['etiqueta', 'nif', 'clase', 'anio', 'y', 'tamano', 'pct', 'ind', 'n_el', 'v', 's1', 's2']) w.writeheader() for f in sorted(filas, key=lambda f: (f['etiqueta'], f['ind'], f['nif'], f['anio'])): w.writerow(f) # ---------- métricas ---------- class SplitMix: def __init__(self, semilla): self.s = semilla & 0xFFFFFFFFFFFFFFFF def sig(self): self.s = (self.s + 0x9E3779B97F4A7C15) & 0xFFFFFFFFFFFFFFFF z = self.s z = ((z ^ (z >> 30)) * 0xBF58476D1CE4E5B9) & 0xFFFFFFFFFFFFFFFF z = ((z ^ (z >> 27)) * 0x94D049BB133111EB) & 0xFFFFFFFFFFFFFFFF return z ^ (z >> 31) def entero(self, n): return self.sig() % n def auc_pares(pos, neg): if not pos or not neg: return None g = 0.0 for a in pos: for b in neg: g += 1.0 if a > b else (0.5 if a == b else 0.0) return g / (len(pos) * len(neg)) def cholesky_resolver(A, b): n = len(b) L = [[0.0] * n for _ in range(n)] for i in range(n): for j in range(i + 1): s = A[i][j] - sum(L[i][k] * L[j][k] for k in range(j)) if i == j: if s <= 0: return None L[i][i] = math.sqrt(s) else: L[i][j] = s / L[j][j] z = [0.0] * n for i in range(n): z[i] = (b[i] - sum(L[i][k] * z[k] for k in range(i))) / L[i][i] x = [0.0] * n for i in reversed(range(n)): x[i] = (z[i] - sum(L[k][i] * x[k] for k in range(i + 1, n))) / L[i][i] return x def newton(X, y, cresta=1e-4): p = len(X[0]) b = [0.0] * p for _ in range(100): g = [0.0] * p H = [[0.0] * p for _ in range(p)] for xi, yi in zip(X, y): eta = max(-35.0, min(35.0, sum(bb * xx for bb, xx in zip(b, xi)))) m = 1.0 / (1.0 + math.exp(-eta)) for i in range(p): g[i] += (yi - m) * xi[i] for j in range(p): H[i][j] += m * (1 - m) * xi[i] * xi[j] for i in range(1, p): H[i][i] += cresta g[i] -= cresta * b[i] paso = cholesky_resolver(H, g) if paso is None: return None b = [bb + pp for bb, pp in zip(b, paso)] if max(abs(pp) for pp in paso) < 1e-8: return b return None def or_aj(fs): fs = [f for f in fs if f['tamano'] is not None] if len({f['y'] for f in fs}) < 2: return None X = [] for f in fs: X.append([1.0, f['s1'] / 10, f['tamano'], float(f['clase'] == 'DP'), float(f['clase'] == 'insular'), float(f['anio'] == 2022), float(f['anio'] == 2023), float(f['anio'] == 2024)]) var = [j for j in range(len(X[0])) if j < 2 or len({x[j] for x in X}) > 1] X = [[x[j] for j in var] for x in X] b = newton(X, [f['y'] for f in fs]) return None if b is None else math.exp(b[1]) def cuantil(v, q): v = sorted(v) if not v: return None k = (len(v) - 1) * q i = int(math.floor(k)) j = min(i + 1, len(v) - 1) return v[i] + (v[j] - v[i]) * (k - i) def estrat(fs): t = sorted(f['tamano'] for f in fs if f['tamano'] is not None) if not t: return None, 0 c1, c2 = t[(len(t) - 1) // 3], t[(2 * (len(t) - 1)) // 3] grupos = defaultdict(lambda: ([], [])) for f in fs: if f['tamano'] is None: continue ter = 0 if f['tamano'] <= c1 else (1 if f['tamano'] <= c2 else 2) grupos[(f['clase'], ter)][1 - f['y']].append(f['s1']) g = n = 0 for pos, neg in grupos.values(): for a in pos: for b in neg: n += 1 g += 1.0 if a > b else (0.5 if a == b else 0.0) return (g / n if n >= 30 else None), n PARES = [('NP', 'C1', 1), ('NP', 'C2', 1), ('NP', 'DOC4', 1), ('MEN', 'M1r', 1), ('MEN', 'M1e', 1), ('MEN', 'M3', 1), ('MEN', 'M2', 1), ('FRAC', 'M1r', 1), ('FRAC', 'M1e', 1), ('UMB', 'M3', 1), ('UMB', 'M2', 1), ('S3', 'M6', 1), ('S3', 'M4', 1)] PARES += [(s, i, 0) for i in ('C1', 'C2', 'DOC4') for s in ('NP_FIRME', 'NP_BB', 'NP_CAND')] PARES += [('NP', 'C2L', 0), ('NP', 'PCAP', 0), ('NP', 'MEMORIA', 0), ('NP', 'APROB', 0), ('NP', 'ACTAS', 0), ('FRAC', 'M1a', 0), ('MEN', 'M1a', 0), ('SP', 'M1a', 0), ('SP', 'M1r', 0), ('SP', 'M3', 0), ('S3', 'M6_1', 0)] salida = [] for et, ind, princ in PARES: fs = [f for f in filas if f['etiqueta'] == et and f['ind'] == ind and f['s1'] is not None and f['n_el'] >= 20] pos = [f['s1'] for f in fs if f['y'] == 1] neg = [f['s1'] for f in fs if f['y'] == 0] r = {'etiqueta': et, 'ind': ind, 'n_pos': len(pos), 'n_neg': len(neg), 'ent_pos': len({f['nif'] for f in fs if f['y'] == 1}), 'ent_neg': len({f['nif'] for f in fs if f['y'] == 0}), 'auc': auc_pares(pos, neg)} if pos and neg: rng = SplitMix(20261026 ^ sum(ord(c) * 131 ** i for i, c in enumerate(et + '|' + ind))) ents = sorted({f['nif'] for f in fs}) por = defaultdict(list) for f in fs: por[f['nif']].append(f) A, O, fallos, k = [], [], 0, 0 while k < B: m = [f for _ in ents for f in por[ents[rng.entero(len(ents))]]] if len({f['y'] for f in m}) < 2: continue k += 1 A.append(auc_pares([f['s1'] for f in m if f['y'] == 1], [f['s1'] for f in m if f['y'] == 0])) o = or_aj(m) if o is None: fallos += 1 else: O.append(o) r.update({'ic_inf': cuantil(A, 0.025), 'ic_sup': cuantil(A, 0.975), 'or': or_aj(fs), 'or_ic_inf': cuantil(O, 0.025), 'or_ic_sup': cuantil(O, 0.975), 'or_fallos': fallos}) r['auc_estrat'], r['pares_estrat'] = estrat(fs) f2 = [f for f in fs if f['s2'] is not None] r['auc_s2'] = auc_pares([f['s2'] for f in f2 if f['y'] == 1], [f['s2'] for f in f2 if f['y'] == 0]) r['auc_v'] = auc_pares([f['v'] for f in fs if f['y'] == 1], [f['v'] for f in fs if f['y'] == 0]) dec = [f for f in fs if f['s1'] >= 90] r['decil_n'], r['decil_pos'] = len(dec), sum(f['y'] for f in dec) ev = r['n_pos'] >= 10 and r['n_neg'] >= 10 and r['ent_pos'] >= 5 and r['ent_neg'] >= 5 if not ev: r['veredicto'] = 'NO EVALUABLE' else: ok = (r['auc'] >= 0.65 and r['ic_inf'] > 0.55, r['auc_estrat'] is not None and r['auc_estrat'] >= 0.60, r['or_fallos'] <= 200 and r['or_ic_inf'] is not None and r['or_ic_inf'] > 1, (r['auc_s2'] or 0) >= 0.60 and (r['auc_v'] or 0) >= 0.60) r['veredicto'] = 'PASA' if all(ok) else ('NO PASA · nulo' + (' (invertido)' if r['auc'] < 0.45 else '') if r['auc'] <= 0.55 else 'NO PASA · no concluyente') if not princ: r['veredicto'] += ' [exploratorio]' salida.append(r) firme = {r['ind']: r for r in salida if r['etiqueta'] == 'NP_FIRME'} for r in salida: if r['etiqueta'] == 'NP' and r['veredicto'] == 'PASA': f = firme.get(r['ind']) if f is None or f['veredicto'] == 'NO EVALUABLE' or f['auc'] < 0.60: r['veredicto'] = 'NO PASA · no concluyente (sin respaldo firme)' campos = ['etiqueta', 'ind', 'n_pos', 'n_neg', 'ent_pos', 'ent_neg', 'auc', 'ic_inf', 'ic_sup', 'auc_estrat', 'pares_estrat', 'or', 'or_ic_inf', 'or_ic_sup', 'or_fallos', 'auc_s2', 'auc_v', 'decil_n', 'decil_pos', 'veredicto'] with open(SAL, 'w', newline='') as fh: w = csv.DictWriter(fh, campos, extrasaction='ignore') w.writeheader() for r in salida: w.writerow({k: (f'{r[k]:.6f}' if isinstance(r.get(k), float) else r.get(k, '')) for k in campos}) print('impl2: filas', len(filas), 'pares', len(salida))