"""REL1 · 30 · Segunda implementación (DEFINICIONES_REL1.md §10). Python puro; pyarrow solo para leer Parquet. Sin el SQL de impl1. Recalcula desde interno/lic.parquet, interno/lotes.parquet y los ficheros crudos de vínculos (satélite BORME, ute_socios, red de UTE1): pares por lote y sus banderas, V, n_pares_vinc, G, k_vinc; tasas por fuente; O/E de quién gana; pares de la red por mercado-fuente (w, ganadas, N1-N3) y N4; AUC de la validación (estimador puntual). Compara con impl1 y escribe salidas/30_cotejo_impl.json.""" import pyarrow.parquet as pq, json, hashlib, itertools, collections, math, bisect, time T0 = time.time() def log(*a): print(f'[{time.time()-T0:6.1f}s]', *a, flush=True) def rows(path, cols=None): t = pq.read_table(path, columns=cols).to_pydict(); ks = list(t) return [dict(zip(ks, v)) for v in zip(*[t[k] for k in ks])] lic = rows('/r/interno/lic.parquet', ['fuente', 'lote_k', 'bid_k', 'es_pj', 'es_ute', 'es_ganadora', 'importe']) lot = {(r['fuente'], r['lote_k']): r for r in rows('/r/interno/lotes.parquet')} by_lot = collections.defaultdict(list) for r in lic: by_lot[(r['fuente'], r['lote_k'])].append(r) pj_keys = {r['bid_k'] for r in lic if r['es_pj']} # vínculos crudos adm, dom = set(), set() for r in rows('/bs/enlaces.parquet', ['empresa_clave_a', 'empresa_clave_b', 'tipo', 'n_personas']): a, b = r['empresa_clave_a'], r['empresa_clave_b'] if a not in pj_keys or b not in pj_keys: continue k = (min(a, b), max(a, b)) if r['tipo'] == 'administrador' and (r['n_personas'] or 0) >= 1: adm.add(k) if r['tipo'] == 'domicilio': dom.add(k) grupo = collections.defaultdict(set) for r in rows('/bs/grupos.parquet', ['empresa_clave', 'grupo_20']): if r['grupo_20'] is not None: grupo[r['empresa_clave']].add(r['grupo_20']) socios = collections.defaultdict(set) # ute_clave -> socios (todas las filas válidas, para UTEM) for r in rows('/d/unified_20261025.identidad/ute_socios.parquet', ['ute_clave', 'socio_nif', 'via', 'socio_persona_fisica']): if r['via'] == 'lista' or r['socio_persona_fisica'] or r['socio_nif'] is None: continue socios[r['ute_clave']].add(r['socio_nif']) ute_pub = set() for u, ss in socios.items(): if 2 <= len(ss) <= 6: for a, b in itertools.combinations(sorted(ss), 2): if a in pj_keys and b in pj_keys: ute_pub.add((a, b)) red = json.load(open('/ute/salidas/red_anonima.json')) h2k = collections.defaultdict(list) for k in pj_keys: h2k[hashlib.md5(('ute1-red|' + k).encode()).hexdigest()[:12]].append(k) ute_red = set() for e in red['aristas']: for a in h2k.get(e['s'], []): for b in h2k.get(e['t'], []): if a != b: ute_red.add((min(a, b), max(a, b))) def flags(a, b): k = (a, b) g = bool(grupo.get(a, set()) & grupo.get(b, set())) um = (b in socios.get(a, ())) or (a in socios.get(b, ())) return {'adm': k in adm, 'dom': k in dom, 'grp': g, 'ute': k in ute_pub or k in ute_red, 'utem': um} # ---------- por lote LM = {} pairs_lot = [] sin_lote = sum(len(v) for k, v in by_lot.items() if k not in lot) # ofertas del piloto sin lote_clave: no son de ningún lote for key, bs in by_lot.items(): lo = lot.get(key) if lo is None or (lo['n_pj'] or 0) < 2: continue sob = any(bool(lo.get(s)) for s in ('sob_s0004_25', 'sob_s0015_25', 'sob_aereos', 'sob_sueros')) pj = sorted({r['bid_k'] for r in bs if r['es_pj']}) gan = {r['bid_k'] for r in bs if r['es_ganadora']} vk = set(); npv = 0; vb = False for a, b in itertools.combinations(pj, 2): f = flags(a, b); v = any(f.values()); vb = vb or f['adm'] or f['dom'] or f['grp'] pairs_lot.append((key, a, b, v, a in gan, b in gan)) if v: npv += 1; vk.update((a, b)) LM[key] = {'v': npv > 0, 'v_borme': vb, 'n_pares_vinc': npv, 'k_vinc': len(vk), 'g': bool(vk & gan), 'pop': not sob, 'n_bid': lo['n_bid'], 'n_gan': lo['n_gan'], 'mercado': lo['mercado'], 'organo': lo['organo_clave'], 'lic_k': lo['lic_k'], 'anio': lo['anio']} log('lotes', len(LM), 'pares', len(pairs_lot)) # ---------- comparación por lote con impl1 i1 = {(r['fuente'], r['lote_k']): r for r in rows('/r/interno/lote_metricas.parquet', ['fuente', 'lote_k', 'v', 'n_pares_vinc', 'k_vinc', 'g', 'pop', 'v_borme'])} dif = collections.Counter() for k, m in LM.items(): r = i1.get(k) if r is None: dif['falta_en_impl1'] += 1; continue for c in ('v', 'n_pares_vinc', 'k_vinc', 'g', 'pop', 'v_borme'): if bool(r[c]) != bool(m[c]) if c in ('v', 'g', 'pop', 'v_borme') else r[c] != m[c]: dif[c] += 1 dif['falta_en_impl2'] = len(set(i1) - set(LM)) OUT = {'lotes': len(LM), 'ofertas_sin_lote': sin_lote, 'diferencias_por_lote': dict(dif)} # tasas por fuente tas = collections.defaultdict(lambda: [0, 0]) for (f, _), m in LM.items(): if m['pop']: tas[f][0] += 1; tas[f][1] += m['v'] h = json.load(open('/r/salidas/20_hecho.json')) OUT['tasas'] = {f: {'impl2': v, 'impl1': next(([r[1], r[2]] for r in h['por_fuente'] if r[0] == f), None)} for f, v in sorted(tas.items())} # quién gana (O/E puntual) qg = collections.defaultdict(lambda: [0, 0.0]) for (f, _), m in LM.items(): if m['pop'] and m['v'] and m['n_gan'] == 1: qg[f][0] += m['g']; qg[f][1] += m['k_vinc'] / m['n_bid'] OUT['quien_gana'] = {f: {'impl2_O': v[0], 'impl2_E': round(v[1], 3), 'impl1_O': h['quien_gana'][f]['O'], 'impl1_E': h['quien_gana'][f]['E']} for f, v in sorted(qg.items())} # ---------- red por mercado-fuente (lotes pop; «ganadora conocida» = n_gan == 1) nlot = collections.Counter(); nfirm = collections.Counter(); gfirm = collections.Counter() sin_mercado = 0 # lotes sin fila de unified (sin tipo ni CPV): sin mercado, fuera de la red (como en impl1, donde el mercado es NULL) for key, bs in by_lot.items(): m = LM.get(key) if not m or not m['pop']: continue if m['mercado'] is None: sin_mercado += 1; continue f = key[0]; mk = (f, m['mercado']); nlot[mk] += 1 for k in {r['bid_k'] for r in bs if r['es_pj']}: nfirm[mk + (k,)] += 1 for k in {r['bid_k'] for r in bs if r['es_pj'] and r['es_ganadora']}: if m['n_gan'] == 1: gfirm[mk + (k,)] += 1 RP = collections.defaultdict(lambda: [0, 0, 0, 0, False]) for key, a, b, v, ga, gb in pairs_lot: m = LM[key] if not m['pop'] or m['mercado'] is None: continue t = RP[(key[0], m['mercado'], a, b)]; t[0] += 1 if m['n_gan'] == 1: t[1] += 1; t[2] += ga; t[3] += gb t[4] = t[4] or v def scr(f, mm, a, b, t): w, wg, wa, wb, v = t; na, nb, N = nfirm[(f, mm, a)], nfirm[(f, mm, b)], nlot[(f, mm)] lift = w / (na * nb / N) n1 = w >= 3 and lift >= 5 n2 = wg >= 3 and wa >= 1 and wb >= 1 and (wa + wb) / wg >= 0.8 n3 = wg >= 3 and ((wa >= 2 and wb == 0 and gfirm[(f, mm, b)] - wb >= 1) or (wb >= 2 and wa == 0 and gfirm[(f, mm, a)] - wa >= 1)) return n1, n2, n3 i1r = {(r['fuente'], r['mercado'], r['a'], r['b']): r for r in rows('/r/interno/red_pares.parquet', ['fuente', 'mercado', 'a', 'b', 'w', 'w_g', 'wa', 'wb', 'n1', 'n2', 'n3', 'vinc'])} dr = collections.Counter(); cnt = collections.Counter() for k, t in RP.items(): n1, n2, n3 = scr(*k, t); cnt['n1'] += n1; cnt['n2'] += n2; cnt['n3'] += n3 r = i1r.get(k) if r is None: dr['falta_en_impl1'] += 1; continue for c, val in (('w', t[0]), ('w_g', t[1]), ('wa', t[2]), ('wb', t[3])): if r[c] != val: dr[c] += 1 for c, val in (('n1', n1), ('n2', n2), ('n3', n3), ('vinc', t[4])): if bool(r[c]) != bool(val): dr[c] += 1 dr['falta_en_impl2'] = len(set(i1r) - set(RP)) OUT['red'] = {'pares': len(RP), 'lotes_sin_mercado': sin_mercado, 'n1_n2_n3': dict(cnt), 'diferencias': dict(dr)} # N4 (órgano × mercado): pares con n >= 3 en la celda, sin co-licitar en ella, esperado >= 3 ncell = collections.Counter(); nci = collections.Counter(); wcell = set() for key, bs in by_lot.items(): m = LM.get(key) if not m or not m['pop'] or m['organo'] is None: continue c = (key[0], m['organo'], m['mercado']); ncell[c] += 1 ks = sorted({r['bid_k'] for r in bs if r['es_pj']}) for k in ks: nci[c + (k,)] += 1 for a, b in itertools.combinations(ks, 2): wcell.add(c + (a, b)) firms_cell = collections.defaultdict(list) for (f, o, mm, k), n in nci.items(): if n >= 3: firms_cell[(f, o, mm)].append(k) n4 = 0 for c, ks in firms_cell.items(): ks.sort(); N = ncell[c] for a, b in itertools.combinations(ks, 2): if c + (a, b) not in wcell and nci[c + (a,)] * nci[c + (b,)] / N >= 3: n4 += 1 OUT['n4'] = {'impl2': n4, 'impl1': sum(r[1] for r in json.load(open('/r/salidas/22_red.json'))['n4']['por_fuente'])} # ---------- validación (AUC puntual de V y R por fuente con potencia), desde etiquetas_lote y la R de impl2 et = {(r['fuente'], r['lote_k']): r for r in rows('/r/interno/etiquetas_lote.parquet')} R_lot = collections.defaultdict(bool) for key, a, b, v, ga, gb in pairs_lot: m = LM[key] if not m['pop'] or m['mercado'] is None: continue t = RP[(key[0], m['mercado'], a, b)] if any(scr(key[0], m['mercado'], a, b, t)): R_lot[key] = True L = {} for key, m in LM.items(): if not m['pop']: continue e = et[key]; lk = (key[0], m['lic_k']) d = L.setdefault(lk, {'v': 0, 'r': 0, 'anio': m['anio'], 'mercado': m['mercado'], 'banco': False, 'pos': False, 'ctl': False, 'exp': None}) d['v'] = max(d['v'], int(m['v'])); d['r'] = max(d['r'], int(R_lot[key])) d['anio'] = min(x for x in (d['anio'], m['anio']) if x is not None) if (d['anio'] is not None or m['anio'] is not None) else None d['banco'] |= e['var_banco'] is not None; d['pos'] |= e['grupo_piloto'] == 'positivo'; d['ctl'] |= e['grupo_piloto'] == 'control' d['exp'] = d['exp'] or e['exp_piloto'] or e['exp_banco'] for (f, lk), d in L.items(): d['rol'] = 'pos' if (d['banco'] or d['pos']) else ('ctl' if f == 'ACTAS_A' and d['ctl'] else None) for (f, lk), d in L.items(): if d['rol'] is None and f != 'ACTAS_A': if any(q['rol'] == 'pos' and g == f and q['mercado'] == d['mercado'] and q['anio'] is not None and d['anio'] is not None and abs(q['anio'] - d['anio']) <= 1 for (g, _), q in L.items()): d['rol'] = 'ctl' def auc(items): by = collections.defaultdict(lambda: ([], [])) for s, rol, sc in items: by[s][0 if rol == 'pos' else 1].append(sc) num = den = 0 for p, c in by.values(): for u in p: for w in c: num += 1 if u > w else (0.5 if u == w else 0) den += len(p) * len(c) return num / den if den else None OUT['validacion'] = {} v1 = json.load(open('/r/salidas/23_validar.json')) for f in sorted({k[0] for k in L}): it = [(f + '|' + str(d['exp'] or '?') + '|' + str(d['anio'] if d['anio'] is not None else '?'), d['rol'], d) for (g, _), d in L.items() if g == f and d['rol']] npos = sum(1 for t in it if t[1] == 'pos'); nctl = len(it) - npos o = {'positivas': npos, 'controles': nctl} if npos >= 10 and nctl >= 10: o['auc_V'] = auc([(s, r, d['v']) for s, r, d in it]); o['auc_R'] = auc([(s, r, d['r']) for s, r, d in it]) o['impl1_auc_V'] = v1['por_fuente'][f].get('auc_V'); o['impl1_auc_R'] = v1['por_fuente'][f].get('auc_R') else: o['impl1'] = {k: v1['por_fuente'].get(f, {}).get(k) for k in ('positivas', 'controles')} OUT['validacion'][f] = o log(json.dumps(OUT, default=str)) json.dump(OUT, open('/r/salidas/30_cotejo_impl.json', 'w'), default=str, indent=1)