"""CPV1 · segunda implementación, métricas (pre-registro §10): las cifras de aceptación de §8 recalculadas en SQL de DuckDB desde las tablas de predicciones primarias (interno/pred/final__*.tsv y base__*.tsv), sin el código de medida de Python (py/45_medir.py), y comparadas con salidas/45_metricas.json. Y, con impl2/salidas/pred_impl2.parquet, la coincidencia de la puntuación. Uso (etl:v2): python impl2_metricas.py FINAL LOSO_PREFIJO FUENTES""" import sys, json, duckdb final, pref, fuentes = sys.argv[1], sys.argv[2], sys.argv[3].split(',') P = '/w/interno/pred'; S = '/w/salidas'; O = '/w/impl2/salidas' con = duckdb.connect() con.execute("SET memory_limit='5GB'; SET threads=3; SET temp_directory='/w/tmp/duck'; SET max_temp_directory_size='5GB'") RD = "delim='\t', header=true, quote='', escape='', all_varchar=true" def t(nombre, ruta): con.execute(f"CREATE TABLE {nombre} AS SELECT * FROM read_csv('{ruta}', {RD})") t('e', f'{P}/final__{final}__E2425.tsv'); t('v', f'{P}/final__{final}__V.tsv'); t('b', f'{P}/base__E2425.tsv') con.execute("CREATE TABLE l (src VARCHAR, id VARCHAR, y VARCHAR, d1 VARCHAR, p1 DOUBLE, emitida VARCHAR, base VARCHAR)") for s in fuentes: con.execute(f"""INSERT INTO l SELECT '{s}', f.id, f.y_div, f.d1, f.p1::DOUBLE, f.emitida, b.pred FROM read_csv('{P}/final__{pref}{s}_t0.1__E2425_{s}__fuera.tsv', {RD}) f LEFT JOIN read_csv('{P}/base__E2425_{s}.tsv', {RD}) b USING (id)""") ECE = """sum(abs(ok - conf) * n) / sum(n) FROM (SELECT least(floor(p1 * 15), 14) bin, sum(w) n, sum(w * (d1 = y)::INT) / sum(w) ok, sum(w * p1) / sum(w) conf FROM X GROUP BY 1)""" def ece(fuente_sql): return con.execute(f"SELECT {ECE.replace('X', '(' + fuente_sql + ')')}").fetchone()[0] r = {} for reg in ('en', 'fuera'): q = f"SELECT y_div y, d1, p1::DOUBLE p1, emitida, 1.0 w FROM e WHERE regimen = '{reg}'" r[f'E2425_{reg}'] = dict(zip(['n', 'top1', 'emitidas', 'exactitud_emitida'], con.execute( f"SELECT count(*), avg((d1 = y)::INT), count(*) FILTER (WHERE emitida = '1'), avg((d1 = y)::INT) FILTER (WHERE emitida = '1') FROM ({q})").fetchone())) r[f'E2425_{reg}']['ece'] = ece(q) r[f'E2425_{reg}']['base_top1'] = con.execute(f"SELECT avg((b.pred = e.y_div)::INT) FROM e JOIN b USING (id) WHERE e.regimen = '{reg}'").fetchone()[0] ql = "SELECT y, d1, p1, emitida, 1.0 / count(*) OVER (PARTITION BY src) w FROM l" r['LOSO_union'] = dict(zip(['top1', 'exactitud_emitida', 'base_top1'], con.execute(f"""SELECT sum(w * (d1 = y)::INT) / sum(w), sum(w * (d1 = y)::INT) FILTER (WHERE emitida = '1') / sum(w) FILTER (WHERE emitida = '1'), sum(w * (base = y)::INT) / sum(w) FROM (SELECT *, 1.0 / count(*) OVER (PARTITION BY src) w FROM l)""").fetchone())) r['LOSO_union']['ece'] = ece(ql) r['LOSO_por_fuente'] = {s: dict(zip(['n', 'emitidas', 'exactitud_emitida'], v)) for s, *v in con.execute( "SELECT src, count(*), count(*) FILTER (WHERE emitida = '1'), avg((d1 = y)::INT) FILTER (WHERE emitida = '1') FROM l GROUP BY 1").fetchall()} r['V_MADRID_CAM_2425'] = dict(zip(['n', 'emitidas', 'exactitud_emitida'], con.execute( "SELECT count(*), count(*) FILTER (WHERE emitida = '1'), avg((d1 = y_div)::INT) FILTER (WHERE emitida = '1') FROM v WHERE source = 'MADRID_CAM' AND anio IN ('2024', '2025')").fetchone())) # comparación con la medida primaria m = json.load(open(f'{S}/45_metricas.json')) pares = [('E2425_en.exactitud_emitida', r['E2425_en']['exactitud_emitida'], m['E2425']['en']['exactitud_emitida']), ('E2425_en.top1', r['E2425_en']['top1'], m['E2425']['en']['top1']), ('E2425_en.ece', r['E2425_en']['ece'], m['E2425']['en']['ece']), ('E2425_en.base_top1', r['E2425_en']['base_top1'], m['E2425_base_top1']['en']), ('E2425_fuera.exactitud_emitida', r['E2425_fuera']['exactitud_emitida'], m['E2425']['fuera']['exactitud_emitida']), ('LOSO.exactitud_emitida', r['LOSO_union']['exactitud_emitida'], m['LOSO']['unión']['exactitud_emitida']), ('LOSO.top1', r['LOSO_union']['top1'], m['LOSO']['unión']['top1']), ('LOSO.ece', r['LOSO_union']['ece'], m['LOSO']['unión']['ece']), ('LOSO.base_top1', r['LOSO_union']['base_top1'], m['LOSO']['base_top1_union']), ('V_MADRID_CAM.exactitud_emitida', r['V_MADRID_CAM_2425']['exactitud_emitida'], m['V']['MADRID_CAM|2024-2025']['exactitud_emitida'])] pares += [(f'LOSO.{s}.exactitud_emitida', r['LOSO_por_fuente'][s]['exactitud_emitida'], m['LOSO']['por_fuente'][s]['exactitud_emitida']) for s in fuentes] dif = {k: (a, b, abs(a - b) if a is not None and b is not None else None) for k, a, b in pares} r['comparacion_con_primaria'] = dif r['max_dif'] = max(d[2] for d in dif.values() if d[2] is not None) # puntuación independiente frente a la primaria try: con.execute(f"CREATE TABLE i2 AS SELECT * FROM '{O}/pred_impl2.parquet'") ESC = "replace(replace(replace(replace(id_canonical, '\\', '\\\\'), chr(9), '\\t'), chr(10), '\\n'), chr(13), '\\r')" con.execute(f"CREATE TABLE i2e AS SELECT *, {ESC} id FROM i2") for conj, tab in (('E', 'e'), ('V', 'v')): q = con.execute(f"""SELECT count(*), count(p.id), avg((i.d1 = p.d1)::INT), max(abs(i.p1 - p.p1::DOUBLE)), avg((i.emitida = (p.emitida = '1'))::INT), avg((i.regimen = p.regimen)::INT), avg((i.d1 = i.verdad)::INT) FILTER (WHERE i.emitida), avg((p.d1 = p.y_div)::INT) FILTER (WHERE p.emitida = '1'), quantile_cont(abs(i.p1 - p.p1::DOUBLE), 0.999) FROM i2e i LEFT JOIN {tab} p USING (id) WHERE i.conj = '{conj}'""").fetchone() r[f'impl2_{conj}'] = dict(zip(['n_impl2', 'casadas', 'top1_igual', 'max_dif_conf', 'emitida_igual', 'regimen_igual', 'exactitud_emitida_impl2', 'exactitud_emitida_primaria', 'p999_dif_conf'], q)) # misma lista de términos (la normalización de Unicode de SQL y la de Python pueden diferir): solo para COMPARAR se # recalculan los términos primarios con cpvtexto sobre los objetos de E.tsv y V.tsv de las filas de la muestra sys.path.insert(0, '/w/py') import cpvtexto as ct quiero = {i: (c, t) for i, c, t in con.execute("SELECT id, conj, terminos FROM i2e").fetchall()} prim = {} for fich in ('E.tsv', 'V.tsv'): with open(f'/w/interno/datos/{fich}', encoding='utf-8') as fh: cab = fh.readline().rstrip('\n').split('\t'); I = {c: i for i, c in enumerate(cab)} for l in fh: x = l.rstrip('\n').split('\t') if x[I['id_canonical']] in quiero: prim[x[I['id_canonical']]] = ct.terminos(x[I['objeto']]) mismos = [i for i, (c, t) in quiero.items() if prim.get(i) == list(t or [])] con.execute("CREATE TABLE mismos (id VARCHAR)"); con.executemany("INSERT INTO mismos VALUES (?)", [(i,) for i in mismos]) for conj, tab in (('E', 'e'), ('V', 'v')): q = con.execute(f"""SELECT count(*), avg((i.d1 = p.d1)::INT), max(abs(i.p1 - p.p1::DOUBLE)) FROM i2e i JOIN {tab} p USING (id) WHERE i.conj = '{conj}' AND i.id IN (SELECT id FROM mismos)""").fetchone() r[f'impl2_{conj}'].update({'mismos_terminos': q[0], 'top1_igual_mismos': q[1], 'max_dif_conf_mismos': q[2], 'distintos_terminos': sum(1 for i, (c, t) in quiero.items() if c == conj and prim.get(i) != list(t or []))}) ej = [(prim.get(i), list(t or [])) for i, (c, t) in quiero.items() if prim.get(i) is not None and prim.get(i) != list(t or [])][:15] r['ejemplos_terminos_distintos'] = ej E2, V2 = r['impl2_E'], r['impl2_V'] r['A5'] = bool(E2['top1_igual'] >= 0.999 and V2['top1_igual'] >= 0.999 and E2['max_dif_conf_mismos'] <= 1e-6 and V2['max_dif_conf_mismos'] <= 1e-6 and r['max_dif'] <= 1e-9 and abs(E2['exactitud_emitida_impl2'] - E2['exactitud_emitida_primaria']) <= 0.002 and abs(V2['exactitud_emitida_impl2'] - V2['exactitud_emitida_primaria']) <= 0.002) except duckdb.Error as ex: r['impl2_error'] = str(ex) json.dump(r, open(f'/w/impl2/salidas/impl2_metricas.json', 'w'), indent=1, default=str) print(json.dumps(r, indent=1, default=str))