"""UTE1 · 30 · validaciones (DEFINICIONES_PREVIAS §6): (a) cárteles a nivel de lote, (b) sancionadas en UTE entre sí, (c) fiscalización P6 (X2), (d) estabilidad. Escribe salidas/30_validar.json (agregados) e interno/30_detalle.json.""" import json, math, random, re, time, sys, collections, duckdb sys.path.insert(0, '/w/py') from comun import auc T0 = time.time(); SEM = 20261023; B = 2000 con = duckdb.connect('/w/tmp/ute1.duckdb'); x = con.execute x("SET threads=4; SET memory_limit='6GB'; SET temp_directory='/w/tmp/duck'; SET max_temp_directory_size='5GB'; SET preserve_insertion_order=false") def log(*a): print(f"[{time.time()-T0:6.1f}s]", *a, flush=True) R = {} PER = {'S/DC/0627/18': ('2008-01-01', '2018-12-31'), 'S/DC/0614/17': ('2002-07-01', '2017-12-31'), 'S/DC/0598/16': ('2002-01-01', '2016-11-30'), 'S/DC/0565/15': ('2008-01-01', '2017-12-31'), 'S/0644/18': ('2014-06-01', '2018-11-30'), 'S/0626/18': ('2014-12-01', '2018-12-31'), 'S/0025/19': ('2016-02-01', '2019-06-30'), 'S/0011/19': ('2011-01-01', '2019-12-31'), 'S/0001/23': ('2021-10-01', '2022-10-31'), 'S/DC/0519/14': ('1999-07-01', '2014-10-31'), 'S/DC/0512/14': ('2008-01-01', '2016-12-31'), 'SAMUR/01/24': ('2019-09-01', '2019-09-30'), 'ACCO 101/2018': ('2017-07-01', '2017-09-30'), 'CGC S 8/2021': ('2021-01-01', '2021-12-31'), 'S/0013/19': ('2014-02-01', '2018-12-31'), 'S/0008/21': ('2016-01-01', '2021-11-30'), 'S/0016/21': ('2015-11-01', '2022-03-31')} x("CREATE OR REPLACE TEMP TABLE per(exp VARCHAR, a0 INT, a1 INT)") con.executemany("INSERT INTO per VALUES (?,?,?)", [(e, max(2012, int(a[:4])), min(2025, int(b[:4]))) for e, (a, b) in PER.items()]) x("""CREATE OR REPLACE TEMP TABLE lab AS SELECT DISTINCT exp, var, source, id_md5 FROM read_csv('/et/colusion/etiquetas/casado/salidas/lotes_cartel_v2.csv', header=true, all_varchar=true) WHERE var IN ('V2','V3')""") x("CREATE OR REPLACE TEMP TABLE lf AS SELECT *, md5(source || '|' || id_canonical) id_md5 FROM lot_flag") x("CREATE OR REPLACE TEMP TABLE labu AS SELECT DISTINCT lab.exp, lab.var, lf.source, lf.id_canonical, lf.cpv2 FROM lab JOIN lf USING (source, id_md5)") R['a_etiqueta_en_universo'] = x("SELECT exp, var, count(*) FROM labu GROUP BY ALL ORDER BY ALL").fetchall() x("""CREATE OR REPLACE TEMP TABLE merc AS WITH c AS (SELECT exp, cpv2, count(DISTINCT source||id_canonical) n, sum(count(DISTINCT source||id_canonical)) OVER (PARTITION BY exp) t FROM labu WHERE cpv2 <> '??' GROUP BY 1, 2) SELECT exp, cpv2 FROM c WHERE n >= 5 AND n >= 0.10 * t""") R['a_mercados'] = x("SELECT exp, string_agg(cpv2, ' ' ORDER BY cpv2) FROM merc GROUP BY 1 ORDER BY 1").fetchall() log(R['a_etiqueta_en_universo'], R['a_mercados']) # z_D de la celda-año para S4 x("CREATE OR REPLACE TEMP TABLE lfz AS SELECT lf.*, c.zd FROM lf LEFT JOIN celf c ON c.cpv2 = lf.cpv2 AND c.prov = lf.prov AND c.anio = lf.anio AND c.n >= 30") def frame(vars_): v = ','.join(f"'{s}'" for s in vars_) x(f"""CREATE OR REPLACE TEMP TABLE pos AS SELECT DISTINCT exp, source, id_canonical FROM labu WHERE var IN ({v})""") x(f"""CREATE OR REPLACE TEMP TABLE fr AS WITH srcs AS (SELECT DISTINCT exp, source FROM pos) SELECT m.exp, l.cpv2, l.source, l.id_canonical, l.imp, l.es_ute, l.es_dual, l.zd, l.empresa_clave, (p.id_canonical IS NOT NULL) y FROM merc m JOIN per USING (exp) JOIN lfz l ON l.cpv2 = m.cpv2 AND l.anio BETWEEN per.a0 AND per.a1 JOIN srcs s ON s.exp = m.exp AND s.source = l.source LEFT JOIN pos p ON p.exp = m.exp AND p.source = l.source AND p.id_canonical = l.id_canonical WHERE p.id_canonical IS NOT NULL OR NOT EXISTS (SELECT 1 FROM labu q WHERE q.source = l.source AND q.id_canonical = l.id_canonical)""") x("""CREATE OR REPLACE TEMP TABLE frq AS SELECT *, CASE WHEN imp IS NULL THEN -1 ELSE ntile(5) OVER (PARTITION BY exp, cpv2, imp IS NULL ORDER BY imp) END q FROM fr""") def strata_bin(col): return x(f"""SELECT exp, cpv2, q, count(*) FILTER (WHERE y AND {col}) a, count(*) FILTER (WHERE y AND NOT {col}) b, count(*) FILTER (WHERE NOT y AND {col}) c, count(*) FILTER (WHERE NOT y AND NOT {col}) d FROM frq GROUP BY ALL""").fetchall() def sauc_bin(st): num = den = 0.0 for e, cp, q, a, b, c, d in st: P, N = a + b, c + d if P == 0 or N == 0: continue num += a * d + 0.5 * (a * c + b * d); den += P * N return num / den if den else float('nan') def mh(st): nu = de = 0.0 for e, cp, q, a, b, c, d in st: n = a + b + c + d if n: nu += a * d / n; de += b * c / n return nu / de if de else float('nan') def boot(st, fn, key=0): cl = collections.defaultdict(list) for r in st: cl[r[key]].append(r) ks = sorted(cl); rnd = random.Random(SEM); vals = [] for _ in range(B): s = [] for _k in ks: s.extend(cl[ks[rnd.randrange(len(ks))]]) v = fn(s) if v == v and not math.isinf(v): vals.append(v) vals.sort() return [round(vals[int(.025 * len(vals))], 4), round(vals[int(.975 * len(vals)) - 1], 4)] if vals else None def veredicto(a, lo): if a != a: return 'no evaluable' if a >= .65 and lo is not None and lo > .55: return 'FUNCIONA' if a < .45: return 'invertido: no se interpreta (selección de la etiqueta)' if a <= .55: return 'NULO' return 'débil' def sauc_cont(col): rows = x(f"SELECT exp, cpv2, q, {col}, y FROM frq WHERE {col} IS NOT NULL").fetchall() g = collections.defaultdict(list) for e, cp, q, s, y in rows: g[(e, cp, q)].append((s, int(y))) def f(keys): num = den = 0.0 for k in keys: v = g[k]; P = sum(y for _, y in v); N = len(v) - P if P and N: num += auc([s for s, _ in v], [y for _, y in v]) * P * N; den += P * N return num / den if den else float('nan') keys = sorted(g); a0 = f(keys) byexp = collections.defaultdict(list) for k in keys: byexp[k[0]].append(k) ex = sorted(byexp); rnd = random.Random(SEM); vals = [] for _ in range(B): kk = [] for _e in ex: kk.extend(byexp[ex[rnd.randrange(len(ex))]]) v = f(kk) if v == v: vals.append(v) vals.sort(); ic = [round(vals[int(.025 * len(vals))], 4), round(vals[int(.975 * len(vals)) - 1], 4)] if vals else None return {'auc': round(a0, 4), 'ic95_exp': ic, 'lotes': len(rows), 'positivos': sum(r[4] for r in rows), 'veredicto': veredicto(a0, ic[0] if ic else None)} for nombre, vars_ in (('V3', ['V3']), ('V2V3', ['V2', 'V3'])): frame(vars_) base = x("""SELECT count(*) FILTER (WHERE y), count(*) FILTER (WHERE NOT y), count(DISTINCT exp) FILTER (WHERE y), round(100*avg(es_ute::INT) FILTER (WHERE y),2), round(100*avg(es_ute::INT) FILTER (WHERE NOT y),2), round(100*avg(es_dual::INT) FILTER (WHERE y),2), round(100*avg(es_dual::INT) FILTER (WHERE NOT y),2) FROM frq""").fetchone() D = {'positivos': base[0], 'negativos': base[1], 'expedientes': base[2], 'pct_ute_pos': base[3], 'pct_ute_neg': base[4], 'pct_dual_pos': base[5], 'pct_dual_neg': base[6], 'por_exp': x("""SELECT exp, count(*) FILTER (WHERE y), round(100*avg(es_ute::INT) FILTER (WHERE y),1), round(100*avg(es_ute::INT) FILTER (WHERE NOT y),1), round(100*avg(es_dual::INT) FILTER (WHERE y),1), round(100*avg(es_dual::INT) FILTER (WHERE NOT y),1) FROM frq GROUP BY 1 ORDER BY 1""").fetchall()} for sc, col in (('S1_ute', 'es_ute'), ('S3_dual', 'es_dual')): st = strata_bin(col); a0 = sauc_bin(st); ic = boot(st, sauc_bin) exps = sorted(set(r[0] for r in st)); cps = sorted(set(r[1] for r in st)) loeo = {e: round(sauc_bin([r for r in st if r[0] != e]), 4) for e in exps} lomo = {c: round(sauc_bin([r for r in st if r[1] != c]), 4) for c in cps} crude = x(f"SELECT count(*) FILTER (WHERE y AND {col}), count(*) FILTER (WHERE y), count(*) FILTER (WHERE NOT y AND {col}), count(*) FILTER (WHERE NOT y) FROM frq").fetchone() D[sc] = {'auc_estratificado': round(a0, 4), 'ic95_exp': ic, 'or_mh': round(mh(st), 3), 'or_mh_ic95_exp': boot(st, mh), 'auc_bruto': round(0.5 + (crude[0] / crude[1] - crude[2] / crude[3]) / 2, 4) if crude[1] and crude[3] else None, 'dejar_expediente_fuera': [min(v for v in loeo.values() if v == v), max(v for v in loeo.values() if v == v)], 'loeo': loeo, 'dejar_mercado_fuera': [min(v for v in lomo.values() if v == v), max(v for v in lomo.values() if v == v)], 'lomo': lomo, 'veredicto': veredicto(a0, ic[0] if ic else None)} log(nombre, sc, D[sc]['auc_estratificado'], ic, D[sc]['or_mh'], D[sc]['veredicto']) D['S4_zD'] = sauc_cont('zd'); log(nombre, 'S4', D['S4_zD']) # S2 descriptivo se calcula en (b) R[f'a_{nombre}'] = D # ── (b) sancionadas que forman UTE entre sí ──────────────────────────────────────────────── def sa(s): import unicodedata 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() FJRX = r'( (s a u|s a|s l u|s l|s c a|s coop|sociedad anonima|sociedad limitada|unipersonal|sau|slu|sa|sl|sca))+$' def nemp(s): return re.sub(FJRX, '', nn(s)).strip() fallo = json.load(open('/et/colusion/etiquetas/casado/interno/c1_fallo.json')) fe = [] for e, v in fallo.items(): for n in v['empresas']: n2 = re.sub(r'^(?:[A-Z]{2,5}\.\s*)?\d+\.\s*', '', n).strip() for cand in {n2, n2.split(', ')[-1]}: k = nemp(cand) if len(k) >= 4: fe.append((e, k)) x("CREATE OR REPLACE TEMP TABLE fe(exp VARCHAR, k VARCHAR)"); con.executemany("INSERT INTO fe VALUES (?,?)", fe) x(f"""CREATE OR REPLACE TABLE sanc AS SELECT DISTINCT fe.exp, af.empresa_clave e FROM fe JOIN ( SELECT DISTINCT empresa_clave, trim(regexp_replace(regexp_replace(lower(strip_accents(adjudicatario_nombre)), '[^0-9a-z]+', ' ', 'g'), '{FJRX}', '')) k0 FROM read_parquet('/d/unified_20261018.identidad/adjudicatarios_filas.parquet') WHERE NOT coalesce(persona_fisica, false) AND empresa_clave IS NOT NULL AND empresa_tipo = 'empresa') af ON af.k0 = fe.k""") src = open('/et/p8/sql/c06_carteles.py', encoding='utf-8').read() p8 = re.findall(r"\('(S/00(?:13/19|08/21|16/21))', '\w', '([A-Z0-9]{9})', '(\d{4}-\d\d-\d\d)', '(\d{4}-\d\d-\d\d)'", src) con.executemany("INSERT INTO sanc SELECT ?, ? WHERE NOT EXISTS (SELECT 1 FROM sanc WHERE exp = ? AND e = ?)", [(a, b, a, b) for a, b, _, _ in p8]) R['b_sancionadas_claves'] = x("SELECT exp, count(*) FROM sanc WHERE NOT exp LIKE 'AVC%' GROUP BY 1 ORDER BY 1").fetchall() # presencia: empresa con lote (solo o en UTE como miembro) en cpv2 x periodo del expediente x("""CREATE OR REPLACE TEMP TABLE pres AS SELECT m.exp, m.cpv2, l.empresa_clave e, count(*) n FROM merc m JOIN per USING (exp) JOIN lot l ON l.cpv2 = m.cpv2 AND l.anio BETWEEN per.a0 AND per.a1 WHERE NOT l.es_ute AND l.empresa_clave IS NOT NULL AND NOT l.pf GROUP BY ALL UNION ALL SELECT m.exp, m.cpv2, u.emp, count(*) FROM merc m JOIN per USING (exp) JOIN lot l ON l.cpv2 = m.cpv2 AND l.anio BETWEEN per.a0 AND per.a1 JOIN ute_m u ON u.ute_clave = l.empresa_clave WHERE l.es_ute GROUP BY ALL""") x("""CREATE OR REPLACE TEMP TABLE pres2 AS SELECT exp, cpv2, e, sum(n) n, ntile(10) OVER (PARTITION BY exp, cpv2 ORDER BY sum(n), e) dc, e IN (SELECT s.e FROM sanc s WHERE s.exp = p.exp) es_s, e IN (SELECT s.e FROM sanc s) es_s_alguna FROM pres p GROUP BY 1, 2, 3""") x("""CREATE OR REPLACE TEMP TABLE jp AS SELECT DISTINCT m.exp, m.cpv2, p.a, p.b FROM merc m JOIN per USING (exp) JOIN pair_lot p ON p.cpv2 = m.cpv2 AND p.anio BETWEEN per.a0 AND per.a1""") # estratos exp x cpv2 x (dec_a, dec_b) ordenados; casos = pares de sancionadas del exp; controles = pares sin ninguna sancionada (de ningún expediente) st = x("""WITH n AS (SELECT exp, cpv2, dc, count(*) FILTER (WHERE es_s) ns, count(*) FILTER (WHERE NOT es_s_alguna) nc FROM pres2 GROUP BY ALL), pr AS (SELECT j.exp, j.cpv2, least(a.dc, b.dc) d1, greatest(a.dc, b.dc) d2, (a.es_s AND b.es_s) ss, (NOT a.es_s_alguna AND NOT b.es_s_alguna) cc FROM jp j JOIN pres2 a ON a.exp = j.exp AND a.cpv2 = j.cpv2 AND a.e = j.a JOIN pres2 b ON b.exp = j.exp AND b.cpv2 = j.cpv2 AND b.e = j.b), pj AS (SELECT exp, cpv2, d1, d2, count(*) FILTER (WHERE ss) js, count(*) FILTER (WHERE cc) jc FROM pr GROUP BY ALL), tot AS (SELECT n1.exp, n1.cpv2, n1.dc d1, n2.dc d2, CASE WHEN n1.dc = n2.dc THEN n1.ns*(n1.ns-1)/2 ELSE n1.ns*n2.ns END tps, CASE WHEN n1.dc = n2.dc THEN n1.nc*(n1.nc-1)/2 ELSE n1.nc*n2.nc END tpc FROM n n1 JOIN n n2 ON n1.exp = n2.exp AND n1.cpv2 = n2.cpv2 AND n1.dc <= n2.dc) SELECT t.exp, t.cpv2, t.d1, t.d2, coalesce(pj.js,0) a, t.tps - coalesce(pj.js,0) b, coalesce(pj.jc,0) c, t.tpc - coalesce(pj.jc,0) d FROM tot t LEFT JOIN pj USING (exp, cpv2, d1, d2) WHERE t.tps > 0 AND t.tpc > 0""").fetchall() st2 = [(e, cp, (d1, d2), a, b, c, d) for e, cp, d1, d2, a, b, c, d in st] A = sum(r[3] for r in st2); Bc = sum(r[4] for r in st2) E = sum((r[3] + r[4]) * r[5] / (r[5] + r[6]) for r in st2 if r[5] + r[6]) ormh = mh(st2) R['b_pares_sancionadas'] = {'pares_sancionadas': A + Bc, 'con_ute_conjunta': A, 'esperado_por_controles': round(E, 2), 'O_E': round(A / E, 2) if E else None, 'or_mh': round(ormh, 2), 'or_mh_ic95_exp': boot(st2, mh), 'expedientes_con_pares': len(set(r[0] for r in st2 if r[3] + r[4])), 'expedientes_con_ute_conjunta': sorted(set(r[0] for r in st2 if r[3])), 'por_exp': {e: [sum(r[3] for r in st2 if r[0] == e), sum(r[3] + r[4] for r in st2 if r[0] == e)] for e in sorted(set(r[0] for r in st2))}, 'dejar_expediente_fuera_or': {e: round(mh([r for r in st2 if r[0] != e]), 2) for e in sorted(set(r[0] for r in st2 if r[3]))}} log('b', R['b_pares_sancionadas']) # S2 descriptivo: lotes de UTE con >= 2 sancionadas del expediente, en positivos V3 y negativos frame(['V3']) R['a_S2_ute_con_2_sancionadas'] = x("""WITH u2 AS (SELECT DISTINCT s1.exp, m1.ute_clave FROM ute_m m1 JOIN ute_m m2 ON m1.ute_clave = m2.ute_clave AND m1.emp < m2.emp JOIN sanc s1 ON s1.e = m1.emp JOIN sanc s2 ON s2.e = m2.emp AND s2.exp = s1.exp) SELECT count(*) FILTER (WHERE y AND u2.ute_clave IS NOT NULL), count(*) FILTER (WHERE y), count(*) FILTER (WHERE NOT y AND u2.ute_clave IS NOT NULL), count(*) FILTER (WHERE NOT y) FROM frq f LEFT JOIN u2 ON u2.exp = f.exp AND u2.ute_clave = f.empresa_clave""").fetchone() log('S2', R['a_S2_ute_con_2_sancionadas']) # ── (c) P6 fiscalización ────────────────────────────────────────────────────────────────── x("""CREATE OR REPLACE TEMP TABLE b6 AS SELECT DISTINCT entidad_clave, anio::INTEGER anio, etiqueta FROM '/et/p6/salidas/banco_entidades.parquet' WHERE etiqueta IN ('positivo','negativo') AND anio BETWEEN 2014 AND 2025""") x("CREATE OR REPLACE TEMP TABLE o6 AS SELECT DISTINCT organo_clave, entidad_clave FROM '/d/unified_20261018.identidad/organos.parquet' WHERE entidad_clave IN (SELECT entidad_clave FROM b6)") rows = x("""SELECT b.entidad_clave, b.anio, b.etiqueta, count(*) n, avg(lf.es_ute::INT) s1, avg(lf.es_dual::INT) s3 FROM b6 b JOIN o6 USING (entidad_clave) JOIN lot l ON l.organo_clave = o6.organo_clave AND l.anio = b.anio JOIN lot_flag lf ON lf.source = l.source AND lf.id_canonical = l.id_canonical GROUP BY 1, 2, 3 HAVING count(*) >= 10""").fetchall() def boot_units(un): a0 = auc([u[1] for u in un], [u[2] for u in un]); cl = collections.defaultdict(list) for u in un: cl[u[0]].append(u) ks = sorted(cl); rnd = random.Random(SEM); vals = [] for _ in range(B): s = [] for _k in ks: s.extend(cl[ks[rnd.randrange(len(ks))]]) v = auc([u[1] for u in s], [u[2] for u in s]) if v == v: vals.append(v) vals.sort(); ic = [round(vals[int(.025 * len(vals))], 4), round(vals[int(.975 * len(vals)) - 1], 4)] return {'auc': round(a0, 4), 'ic95': ic, 'unidades': len(un), 'positivas': sum(u[2] for u in un), 'conglomerados': len(ks), 'veredicto': veredicto(a0, ic[0])} R['c_P6'] = {'banco': x("SELECT count(*), count(*) FILTER (WHERE etiqueta='positivo') FROM b6").fetchone(), 'S1_ute': boot_units([(r[0], r[4], int(r[2] == 'positivo')) for r in rows]), 'S3_dual': boot_units([(r[0], r[5], int(r[2] == 'positivo')) for r in rows])} log('c', R['c_P6']) # ── (d) estabilidad ────────────────────────────────────────────────────────────────────── def bloque(a0, a1): rows = x(f"""SELECT cpv2, prov, sum(n), sum(k), sum(n*p), sum(n*p*(1-p)) FROM celf WHERE anio BETWEEN {a0} AND {a1} AND p IS NOT NULL GROUP BY 1, 2""").fetchall() ok = [(c, pr, n, k, Ee, V) for c, pr, n, k, Ee, V in rows if n >= 30 and V and V > 0] zs = [((k - Ee) / n) / (math.sqrt(V) / n) for c, pr, n, k, Ee, V in ok]; I = len(zs) phi = sum(z * z for z in zs) / I; w = [n * n / V for c, pr, n, k, Ee, V in ok]; s1 = sum(w); s2 = sum(v * v for v in w) t2 = max(0.0, (I * phi - (I - 1)) / (s1 - s2 / s1)) if I * phi > I - 1 else 0.0 return {(c, pr): ((k - Ee) / n) / math.sqrt(V / n / n + t2) > 3.090232 for c, pr, n, k, Ee, V in ok}, t2 h1, t1 = bloque(2016, 2020); h2, t2_ = bloque(2021, 2025) comun = set(h1) & set(h2) a = sum(1 for c in comun if h1[c] and h2[c]); b = sum(1 for c in comun if h1[c] and not h2[c]); cc = sum(1 for c in comun if not h1[c] and h2[c]); d = sum(1 for c in comun if not h1[c] and not h2[c]) def woolf(a, b, c, d): if 0 in (a, b, c, d): a, b, c, d = a + .5, b + .5, c + .5, d + .5 o = a * d / (b * c); se = math.sqrt(1/a + 1/b + 1/c + 1/d); return [round(o, 2), round(o * math.exp(-1.96 * se), 2), round(o * math.exp(1.96 * se), 2)] R['d_estabilidad'] = {'celdas_ambos_bloques': len(comun), 'tabla_si_si,si_no,no_si,no_no': [a, b, cc, d], 'P_B2_si_B1': round(a / (a + b), 3) if a + b else None, 'P_B2_no_B1': round(cc / (cc + d), 3) if cc + d else None, 'OR_woolf_ic95': woolf(a, b, cc, d), 'tau2_bloques': [round(t1, 6), round(t2_, 6)], 'veredicto': 'estable' if (a + b) >= 30 and woolf(a, b, cc, d)[1] > 2 else 'no estable'} log('d', R['d_estabilidad']) json.dump(R, open('/w/salidas/30_validar.json', 'w'), ensure_ascii=False, indent=1, default=str) log('fin')