"""LOB1 · 30 · pregunta (a) (DEFINICIONES §4): inscritas ante X frente a comparables del mismo mercado, tamaño y forma.""" import sys, json, statistics; sys.path.insert(0, '/w/py'); from comun import *; from comun_mh import * con = abrir(ro=True); x = con.execute REG = {'GENCAT': 'CAT-R', 'AYTO_MAD': 'MAD-R', 'CAM': 'CAM-R'} TER = {'GENCAT': 'CT', 'AYTO_MAD': 'MD', 'CAM': 'MD'} CORTE = {'GENCAT': '2020-12-31', 'AYTO_MAD': '2020-12-31', 'CAM': '2021-12-31'} def firmas(X, menores=False, anios=(2017, 2025), solo_ab=False, corte=None): filt = "" if menores else "AND NOT menor" cut = corte or '2099-12-31' q = f"""WITH t AS (SELECT emp, cpv2, x, imp FROM lot WHERE ccaa = '{TER[X]}' {filt} AND anio BETWEEN {anios[0]} AND {anios[1]}), f AS (SELECT emp, count(*) n, count(*) FILTER (WHERE x = '{X}') k, coalesce(sum(imp),0) imp_n, coalesce(sum(imp) FILTER (WHERE x = '{X}'),0) imp_k FROM t GROUP BY 1 HAVING count(*) >= 3), m AS (SELECT emp, arg_min(cpv2, (-c, cpv2)) cpv FROM (SELECT emp, coalesce(cpv2,'NA') cpv2, count(*) c FROM t GROUP BY 1,2) GROUP BY 1), ra AS (SELECT DISTINCT nif FROM casado WHERE registro = '{REG[X]}'), rc AS (SELECT DISTINCT c.nif FROM casado c JOIN reg r USING (registro, gid) WHERE c.registro = '{REG[X]}' AND r.fecha <= DATE '{cut}') SELECT f.emp, f.n, f.k, f.imp_n, f.imp_k, m.cpv, ntile(10) OVER (ORDER BY f.n, f.emp) deci, CASE WHEN left(f.emp,1) IN ('A','B') THEN left(f.emp,1) ELSE 'O' END forma, f.emp IN (SELECT nif FROM rc) reg, f.emp IN (SELECT nif FROM ra) reg_all FROM f JOIN m USING (emp)""" rows = x(q).fetchall() rows = [r[:9] for r in rows if not (r[9] and not r[8])] # S2: inscritas después del corte, fuera (sin corte no quita nada) if solo_ab: rows = [r for r in rows if r[7] in ('A', 'B')] return rows def estratos(rows, X, campo='n'): e = {} for emp, n, k, imp_n, imp_k, cpv, dec, forma, reg in rows: if campo == 'imp': k, n = imp_k, imp_n if n <= 0: continue e.setdefault((X, cpv, dec, forma), []).append((bool(reg), k, n)) return e def binario(rows, X): e = {} by = {} for r in rows: by.setdefault((r[5], r[6], r[7]), []).append(r) for s, fs in by.items(): med = statistics.median([f[2] / f[1] for f in fs]) e[(X,) + s] = [(bool(f[8]), (f[2] / f[1]) > med) for f in fs] return e def eval_estr(e): rr = rr_mh(e); lo, hi = boot_rr(e) both = {s: fs for s, fs in e.items() if any(f[0] for f in fs) and any(not f[0] for f in fs)} n1 = sum(1 for fs in both.values() for f in fs if f[0]); n0 = sum(1 for fs in both.values() for f in fs if not f[0]) K1 = sum(f[1] for fs in both.values() for f in fs if f[0]); N1 = sum(f[2] for fs in both.values() for f in fs if f[0]) K0 = sum(f[1] for fs in both.values() for f in fs if not f[0]); N0 = sum(f[2] for fs in both.values() for f in fs if not f[0]) return dict(rr=round(rr, 4), ic=[round(lo, 4) if lo else None, round(hi, 4) if hi else None], inscritas=n1, comparables=n0, estratos=len(both), k1=K1, n1=N1, k0=K0, n0=N0) res = {} def corre(nombre, **kw): E = {}; B = {}; EI = {}; por = {} for X in REG: rows = firmas(X, **kw) e = estratos(rows, X); E.update(e); B.update(binario(rows, X)); EI.update(estratos(rows, X, 'imp')) por[X] = eval_estr(e) por[X]['inscritas_todas'] = sum(1 for r in rows if r[8]); por[X]['empresas'] = len(rows) por[X]['sin_potencia'] = por[X]['inscritas'] < 30 log(nombre, X, por[X]) out = dict(por_x=por, agrupado=eval_estr(E)) out['dejar_fuera'] = {X: eval_estr({s: v for s, v in E.items() if s[0] != X}) for X in REG} o = or_mh(B); out['or_binario'] = [round(v, 4) if v else None for v in o] out['rr_importe'] = round(rr_mh(EI), 4) log(nombre, 'agrupado', out['agrupado'], 'LOO', {k: v['rr'] for k, v in out['dejar_fuera'].items()}, 'OR', out['or_binario'], 'RRimp', out['rr_importe']) res[nombre] = out corre('primaria') corre('S1_con_menores', menores=True) corre('S3_solo_AB', solo_ab=True) # S2: inscritas hasta el corte frente a no inscritas; lotes 2022-2025 E = {}; por = {} for X in REG: rows = firmas(X, anios=(2022, 2025), corte=CORTE[X]); e = estratos(rows, X); E.update(e); por[X] = eval_estr(e); log('S2', X, por[X]) res['S2_temporal'] = dict(por_x=por, agrupado=eval_estr(E)); log('S2 agrupado', res['S2_temporal']['agrupado']) # veredicto (§4) a = res['primaria']['agrupado']; loo = [v['rr'] for v in res['primaria']['dejar_fuera'].values()] if a['rr'] >= 1.25 and a['ic'][0] > 1.10 and min(loo) >= 1.15: v = 'ASOCIACION' elif a['ic'][0] <= 1 <= a['ic'][1]: v = 'NULO' elif a['rr'] <= 0.80 and a['ic'][1] < 0.90: v = 'INVERSO' else: v = 'NO CONCLUYENTE' res['veredicto'] = v; log('VEREDICTO', v) json.dump(res, open('/w/interno/30_res.json', 'w'), indent=1)