"""D5b · Paso 1. Casado E1 v2 (DEFINICIONES_PREVIAS.md §1-§2, sellado antes de ejecutar esto): igual que E1 de D5 (D4c §4 + D5 §7) salvo la clave del código: tokens de letras y de cifras, cifras sin ceros a la izquierda, letras conservadas. Se ejecuta en el contenedor (py.sh): /d5 = d5 (ro), /d = datos (ro), /c = d5b. Salidas internas en /c/interno (fichas con ids) y recuentos sin nombres en /c/salidas. Marca: d5b-e1v2.""" import duckdb, json, re, unicodedata, collections, time, sys, hashlib sys.path.insert(0, '/d5/py') from config_d5 import CASOS T0 = time.time() def log(*a): print(*a, round(time.time() - T0), flush=True) def md5f(p): return hashlib.md5(open(p, 'rb').read()).hexdigest() # ---------- 0. entradas selladas (D5): si cambian, se aborta SELLO = {'/d5/py/config_d5.py': '158226f03ce04690d37b45736f8ddc83', '/d5/interno/citas_d5.json': 'ee49a336a09ae2398250baf51a51d2fd'} for p, h in SELLO.items(): assert md5f(p) == h, ('entrada sellada cambiada', p) con = duckdb.connect('/c/tmp/d5b.duckdb') con.execute("SET memory_limit='8GB'; SET threads=6; SET temp_directory='/c/tmp/duck'; SET max_temp_directory_size='30GB'; SET preserve_insertion_order=false;") con.execute("CREATE OR REPLACE TEMP VIEW u AS SELECT * FROM read_parquet('/d/unified_20261025/*/*/*.parquet', hive_partitioning=1, union_by_name=1)") UN = "unidad NOT LIKE 'acto:%' AND unidad NOT LIKE 'pago:%'" POS = {k: v for k, v in CASOS.items() if v['banco'] in ('positivo', 'control')} citas = [c for c in json.load(open('/d5/interno/citas_d5.json')) if c['exp_autoridad'] in POS] V1 = {r['id_cita']: r for r in json.load(open('/d5/interno/casado_d5.json'))} # ---------- normalizaciones (idénticas a casado_d5.py) 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() def ncod(s): return re.sub(r'[^0-9A-Z]', '', sa(s).upper()) G = set('ayuntamiento ajuntament concello udala consejeria conselleria departamento departament ministerio direccion general subdireccion servicio servicios gobierno govern junta de del la las los el y e i en para publica publico entidad empresarial sociedad sa sl slu sau organo contratacion mesa area unidad secretaria delegacion s a l da do xeral'.split()) # ---------- clave v2 (lo único nuevo): tokens de letras y de cifras; cifras sin ceros a la izquierda; letras conservadas def toks(s): return tuple(((t.lstrip('0') or '0') if t.isdigit() else t) for t in re.findall(r'[A-Z]+|[0-9]+', sa(s).upper())) def lam(t): return sum(len(x) for x in t) def contiene(a, b): # a es subsecuencia contigua de b n, m = len(a), len(b) return any(b[i:i + n] == a for i in range(m - n + 1)) # ---------- 1. órganos (idéntico a casado_d5.py) if not con.execute("SELECT count(*) FROM information_schema.tables WHERE table_name='onames'").fetchone()[0]: con.execute(f"CREATE TABLE onames AS SELECT organo_nombre, ccaa, source, count(*) n FROM u WHERE {UN} AND organo_nombre IS NOT NULL GROUP BY ALL") log('onames', con.execute("SELECT count(*) FROM onames").fetchone()) FUENTE_CCAA = {'AN': 'ANDALUCIA', 'GA': 'GALICIA', 'PV': 'EUSKADI', 'CB': None, 'CN': 'CANARIAS'} org_norm, org_ccaa = {}, collections.defaultdict(set) for n, cc, src in con.execute("SELECT organo_nombre, ccaa, source FROM onames").fetchall(): org_norm.setdefault(n, nn(n)); org_ccaa[n].add(cc); org_ccaa[n].add('src:' + src) inv = collections.defaultdict(set) for n, v in org_norm.items(): for t in set(v.split()) - G: inv[t].add(n) def compat_alias(clave): cfg = POS[clave] if not cfg.get('alias'): return None rxs = [re.compile(x) for x in cfg['alias']] cc = cfg.get('ccaa'); fs = FUENTE_CCAA.get(cc) if cc else None return {n for n, v in org_norm.items() if all(r.search(v) for r in rxs) and (not cc or cc in org_ccaa[n] or (fs and 'src:' + fs in org_ccaa[n]))} ALIAS = {k: compat_alias(k) for k in POS} cache = {} def compat_tokens(org): if org in cache: return cache[org] D = {t for t in nn(org).split() if t not in G}; res = set() if D: cnt = collections.Counter() for t in D: for n in inv.get(t, ()): cnt[n] += 1 res = {n for n, k in cnt.items() if k / len(D) >= 0.8} cache[org] = res; return res for c in citas: a = ALIAS[c['exp_autoridad']] c['_org'] = a if a is not None else (compat_tokens(c['organo']) if c.get('organo') else set()) c['_alias'] = a is not None log('órganos', {k: (len(v) if v is not None else None) for k, v in ALIAS.items()}) # ---------- 2. variantes (idéntico a casado_d5.py) y claves v2 (≥ 2 tokens) def variantes(cod): partes = re.split(r'\s*\(Anteriormente\s*|\)|\s+y\s+|(?<=\d)y(?=\d)', cod or '') vs = set() for p in partes: p = p.strip(' .,;') if not p: continue vs.add(p); vs.add(re.sub(r'/\d-\d{5}$', '', p)); vs.add(re.sub(r'-\d{5}$', '', p)) return [v for v in vs if len(ncod(v)) >= 5 and sum(ch.isdigit() for ch in v) >= 3] VAR = {} for c in citas: ks = {} for v in variantes(c['codigo']): t = toks(v) if len(t) >= 2: ks[t] = v VAR[c['id_cita']] = ks def nkey(t): return '|' + '|'.join(x for x in t if x.isdigit()) + '|' pat_c = sorted({nkey(t) for ks in VAR.values() for t in ks if lam(t) >= 7}) # (ii) contención: λ ≥ 7 pat_e = sorted({nkey(t) for ks in VAR.values() for t in ks if lam(t) < 7}) # (i) solo igualdad: λ < 7 log('variantes v2', sum(len(k) for k in VAR.values()), 'patrones', len(pat_c), len(pat_e)) # ---------- 3. índice de expedientes por clave numérica (filtro necesario; la verificación con letras se hace en Python) if not con.execute("SELECT count(*) FROM information_schema.tables WHERE table_name='exk'").fetchone()[0]: con.execute(f"""CREATE TABLE exk AS WITH ex AS (SELECT DISTINCT expediente FROM u WHERE {UN} AND expediente IS NOT NULL) SELECT expediente, '|' || array_to_string(list_transform(regexp_extract_all(expediente, '[0-9]+'), t -> CASE WHEN ltrim(t, '0') = '' THEN '0' ELSE ltrim(t, '0') END), '|') || '|' nk FROM ex WHERE regexp_matches(expediente, '[0-9]')""") log('exk', con.execute("SELECT count(*) FROM exk").fetchone()) con.execute("CREATE OR REPLACE TEMP TABLE ptc(p VARCHAR)"); con.executemany("INSERT INTO ptc VALUES (?)", [(p,) for p in pat_c]) con.execute("CREATE OR REPLACE TEMP TABLE pte(p VARCHAR)"); con.executemany("INSERT INTO pte VALUES (?)", [(p,) for p in pat_e]) cand_ex = [('c',) + r for r in con.execute("SELECT DISTINCT ptc.p, exk.expediente FROM ptc JOIN exk ON contains(exk.nk, ptc.p)").fetchall()] cand_ex += [('e',) + r for r in con.execute("SELECT DISTINCT pte.p, exk.expediente FROM pte JOIN exk ON exk.nk = pte.p").fetchall()] por_pat = collections.defaultdict(list) for k, p, x in cand_ex: por_pat[(k, p)].append(x) log('candidatos por clave numérica', len(cand_ex)) # verificación v2 (con letras): (i) igualdad de claves, o (ii) subsecuencia contigua con λ ≥ 7 ver = collections.defaultdict(list) # id_cita -> [(v, t, expediente)] kx_cache = {} for c in citas: for t, v in VAR[c['id_cita']].items(): for x in por_pat.get(('c' if lam(t) >= 7 else 'e', nkey(t)), ()): kx = kx_cache.get(x) if kx is None: kx = kx_cache[x] = toks(x) if kx == t or (lam(t) >= 7 and contiene(t, kx)): ver[c['id_cita']].append((v, t, x)) exps = sorted({x for L in ver.values() for _, _, x in L}) log('expedientes verificados', len(exps)) FICHA = """source, coalesce(licitacion_id, id_canonical) lic, min(expediente) expediente, min(organo_nombre) organo_nombre, min(objeto) objeto, min(fecha_pub) fpub, min(fecha_fin_ofertas) ffo, min(fecha_adj) fadj, min(fecha_formalizacion) fform, min(anio_ref) anio_ref, max(imp_lic_eur_expediente) il_exp, max(imp_lic_eur) il_max, sum(imp_adj_eur) ia_sum, count(*) FILTER (WHERE unidad='adjudicacion') n_lotes, min(cpv_division) cpv""" cols = ['source', 'lic', 'expediente', 'organo_nombre', 'objeto', 'fpub', 'ffo', 'fadj', 'fform', 'anio_ref', 'il_exp', 'il_max', 'ia_sum', 'n_lotes', 'cpv'] con.execute("CREATE OR REPLACE TEMP TABLE exv(e VARCHAR)"); con.executemany("INSERT INTO exv VALUES (?)", [(x,) for x in exps]) fe = [dict(zip(cols, r)) for r in con.execute(f"SELECT {FICHA} FROM u WHERE {UN} AND expediente IN (SELECT e FROM exv) GROUP BY 1, 2").fetchall()] por_exp = collections.defaultdict(list) for f in fe: por_exp[f['expediente']].append(f) log('fichas', len(fe)) # ---------- 4. reglas de E1 (idénticas a casado_d5.py, con λ de la clave v2 en lugar de len(ncod)) def una_licitacion(fs): if any(f['source'] != 'TED' for f in fs): fs = [f for f in fs if f['source'] != 'TED'] g = collections.defaultdict(list) for f in fs: g[(ncod(f['expediente']), nn(f['organo_nombre']))].append(f) return list(g.values()) cas, amb = {}, {} for c in citas: ok = [] for v, t, x in ver.get(c['id_cita'], []): for f in por_exp.get(x, []): if c.get('anio') and f['anio_ref'] and abs(f['anio_ref'] - c['anio']) > 3: continue if (c['_alias'] or lam(t) < 7) and f['organo_nombre'] not in c['_org']: continue ok.append(f) if not ok: continue gs = una_licitacion(ok) if len({nn(f['organo_nombre']) for g in gs for f in g}) > 1: ok2 = [f for g in gs for f in g if f['organo_nombre'] in c['_org']] if not ok2: amb[c['id_cita']] = 'E1v2 ambigua'; continue gs = una_licitacion(ok2) if len({nn(f['organo_nombre']) for g in gs for f in g}) > 1: amb[c['id_cita']] = 'E1v2 ambigua'; continue cas[c['id_cita']] = dict(fichas=[(f['source'], f['lic']) for g in gs for f in g], ficha=gs[0][0], variantes=sorted({v for v, _, _ in ver[c['id_cita']]})) log('E1v2 casadas', len(cas), 'ambiguas', len(amb)) # ---------- 5. salida out = [] for c in citas: r1 = V1.get(c['id_cita'], {}) r = {x: c.get(x) for x in ('id_cita', 'exp_autoridad', 'pagina', 'codigo', 'organo', 'objeto', 'fecha', 'anio', 'periodo')} r.update(banco=POS[c['exp_autoridad']]['banco'], v1=r1.get('estrategia'), v1_ambigua=r1.get('ambigua'), v2='E1v2' if c['id_cita'] in cas else None, v2_ambigua=amb.get(c['id_cita']), n_organos_compatibles=len(c['_org'])) r['solo_v2'] = r['v2'] == 'E1v2' and r['v1'] != 'E1' k = cas.get(c['id_cita']) if k: f = k['ficha'] r.update(fichas=k['fichas'], variantes_v2=k['variantes'], f_source=f['source'], f_lic=f['lic'], f_expediente=f['expediente'], f_organo=f['organo_nombre'], f_objeto=(f['objeto'] or '')[:400], f_fpub=str(f['fpub']), f_fadj=str(f['fadj']), f_fform=str(f['fform']), f_anio=f['anio_ref'], f_il=f['il_max'], f_ia=f['ia_sum'], f_lotes=f['n_lotes'], f_cpv=f['cpv'], n_fichas=len(k['fichas'])) out.append(r) json.dump(out, open('/c/interno/casado_v2.json', 'w'), ensure_ascii=False, indent=0, default=str) # recuentos sin nombres por resolución agg = collections.OrderedDict() for r in sorted(out, key=lambda r: r['exp_autoridad']): a = agg.setdefault(r['exp_autoridad'], collections.Counter()) a['citas'] += 1; a['v1'] += r['v1'] == 'E1'; a['v2'] += r['v2'] == 'E1v2'; a['solo_v2'] += r['solo_v2'] a['v1_no_v2'] += (r['v1'] == 'E1' and r['v2'] != 'E1v2'); a['v2_ambigua'] += bool(r['v2_ambigua']) L = ['# D5b · Casado E1 v2 por resolución (recuentos, sin nombres)\n', f'Marca d5b-e1v2 · unified_20261025 · {time.strftime("%Y-%m-%d %H:%M:%S")}\n', '| Resolución | Banco | Citas | E1 v1 (D5 sellado) | E1 v2 | Solo v2 | v1 y no v2 | v2 ambigua |', '|---|---|--:|--:|--:|--:|--:|--:|'] tot = collections.Counter() for e, a in agg.items(): L.append(f"| {e} | {POS[e]['banco']} | {a['citas']} | {a['v1']} | {a['v2']} | {a['solo_v2']} | {a['v1_no_v2']} | {a['v2_ambigua']} |"); tot.update(a) L.append(f"| **Total** | | {tot['citas']} | {tot['v1']} | {tot['v2']} | {tot['solo_v2']} | {tot['v1_no_v2']} | {tot['v2_ambigua']} |") open('/c/salidas/20_casado_v2.md', 'w').write('\n'.join(L) + '\n') # ---------- 6. muestra del cotejo (semilla nueva): hasta 20 «solo v2» por md5('d5b-cotejo-v2' || id_cita) solo = [r for r in out if r['solo_v2']] mu = sorted(solo, key=lambda r: hashlib.md5(('d5b-cotejo-v2' + r['id_cita']).encode()).hexdigest())[:20] json.dump([dict(orden=i + 1, id_cita=r['id_cita'], exp=r['exp_autoridad'], md5=hashlib.md5(('d5b-cotejo-v2' + r['id_cita']).encode()).hexdigest()) for i, r in enumerate(mu)], open('/c/interno/cotejo_v2_muestra.json', 'w'), ensure_ascii=False, indent=1) log('solo v2', len(solo), 'muestra', len(mu), 'fin')