"""E1c · cotejo de las dos implementaciones (criterio 9): celdas (N, X) celda a celda 2016-2026, puntuación lote a lote, AUC, deciles e IC. Lee /tmpw/impl1, /tmpw/impl2, /out/01_medida.json y /out/02_impl2.json; escribe /out/06_cotejo_impl.json.""" import json import duckdb con = duckdb.connect() r = {} con.execute("CREATE TABLE c1 AS SELECT * FROM read_parquet('/tmpw/impl1/celdas.parquet')") con.execute("CREATE TABLE c2 AS SELECT organo_clave, organo_nom organo_nombre_norm, cpv_cel cpv_div, anio, n, k FROM read_parquet('/tmpw/impl2/celdas.parquet') WHERE n > 0") r['celdas_impl1'], r['celdas_impl2'] = [con.execute(f"SELECT count(*) FROM {t}").fetchone()[0] for t in ('c1', 'c2')] r['celdas_solo_impl1'] = con.execute("""SELECT count(*) FROM c1 a ANTI JOIN c2 b ON a.organo_clave = b.organo_clave AND a.organo_nombre_norm IS NOT DISTINCT FROM b.organo_nombre_norm AND a.cpv_div = b.cpv_div AND a.anio = b.anio""").fetchone()[0] r['celdas_solo_impl2'] = con.execute("""SELECT count(*) FROM c2 a ANTI JOIN c1 b ON a.organo_clave = b.organo_clave AND a.organo_nombre_norm IS NOT DISTINCT FROM b.organo_nombre_norm AND a.cpv_div = b.cpv_div AND a.anio = b.anio""").fetchone()[0] r['celdas_n_o_k_distintos'] = con.execute("""SELECT count(*) FROM c1 a JOIN c2 b ON a.organo_clave = b.organo_clave AND a.organo_nombre_norm IS NOT DISTINCT FROM b.organo_nombre_norm AND a.cpv_div = b.cpv_div AND a.anio = b.anio WHERE a.n <> b.n OR a.k <> b.k""").fetchone()[0] r['ejemplos_solo_impl1'] = con.execute("""SELECT * FROM c1 a ANTI JOIN c2 b ON a.organo_clave = b.organo_clave AND a.organo_nombre_norm IS NOT DISTINCT FROM b.organo_nombre_norm AND a.cpv_div = b.cpv_div AND a.anio = b.anio LIMIT 5""").fetchall() r['ejemplos_solo_impl2'] = con.execute("""SELECT * FROM c2 a ANTI JOIN c1 b ON a.organo_clave = b.organo_clave AND a.organo_nombre_norm IS NOT DISTINCT FROM b.organo_nombre_norm AND a.cpv_div = b.cpv_div AND a.anio = b.anio LIMIT 5""").fetchall() con.execute("CREATE TABLE p1 AS SELECT * FROM read_parquet('/tmpw/impl1/puntuacion.parquet') WHERE anio IN (2023, 2024, 2025)") con.execute("CREATE TABLE p2 AS SELECT * FROM read_parquet('/tmpw/impl2/puntuacion.parquet')") for T in (2023, 2024, 2025): r[f'lotes_{T}'] = con.execute(f"""SELECT (SELECT count(*) FROM p1 WHERE anio = {T}), (SELECT count(*) FROM p2 WHERE anio = {T}), count(*), count(*) FILTER (WHERE a.y <> b.y), count(*) FILTER (WHERE a.via <> b.via), max(abs(a.s - b.s)) FROM p1 a JOIN p2 b USING (licitacion_id, lote_u) WHERE a.anio = {T} AND b.anio = {T}""").fetchone() m1 = json.load(open('/out/01_medida.json')); m2 = json.load(open('/out/02_impl2.json')) for T in ('2023', '2024', '2025'): d1, d2 = m1['descriptor'][T], m2['descriptor'][T] r[f'auc_{T}'] = {'impl1': d1['auc'], 'impl2': d2['auc'], 'dif': abs(d1['auc'] - d2['auc']), 'ic_impl1': d1['ic95'], 'ic_impl2': d2['ic95'], 'dif_ic': max(abs(a - b) for a, b in zip(d1['ic95'], d2['ic95'])), 'deciles_iguales': all(a['n'] == b['n'] and abs(a['predicha'] - b['predicha']) < 1e-12 and abs(a['observada'] - b['observada']) < 1e-12 for a, b in zip(d1['deciles'], d2['deciles'])) and len(d1['deciles']) == len(d2['deciles']), 'pct_celda': (d1['pct_via_celda'], d2['pct_via_celda'])} r['modelo_2025'] = {**m2['modelo_2025'], 'auc_impl1_informe': m1['modelo']['2025']['auc']} r['criterio_9'] = { 'celdas_identicas': r['celdas_solo_impl1'] == 0 and r['celdas_solo_impl2'] == 0 and r['celdas_n_o_k_distintos'] == 0, 'auc_descriptor_002': all(r[f'auc_{T}']['dif'] <= 0.002 for T in ('2023', '2024', '2025')), 'deciles': all(r[f'auc_{T}']['deciles_iguales'] for T in ('2023', '2024', '2025')), 'ic_001': all(r[f'auc_{T}']['dif_ic'] <= 0.01 for T in ('2023', '2024', '2025')), 'modelo_auc_002': abs(m2['modelo_2025']['auc_impl2'] - m1['modelo']['2025']['auc']) <= 0.002} json.dump(r, open('/out/06_cotejo_impl.json', 'w'), indent=1, default=str) print(json.dumps(r, indent=1, default=str))