"""LOB1 · 40 · pregunta (b) (DEFINICIONES §5): primera reunión en agenda (f, D) y adjudicaciones de D a f 12 meses antes/después, frente a los departamentos con los que f no se reunió (DiD) y con placebo de fechas (t0 − 730 días).""" import sys, json, random; sys.path.insert(0, '/w/py'); from comun import * con = abrir(ro=True); x = con.execute x("SET temp_directory='/w/tmp/duck'") def pares_cat(): return x("""SELECT nif, dep, min(d) t0 FROM cag_emp WHERE nif IS NOT NULL AND dep IN (SELECT dep FROM dep_org) AND d IS NOT NULL GROUP BY 1,2""").fetchall() def medir(pares, menores, desplaz=0, ambito='CAT'): """devuelve lista de (firma, pre, post, cpre, cpost, rel_meses_met{m:n}, rel_meses_ctrl{m:n}).""" filt = "" if menores else "AND NOT l.menor" con2 = x con2("DROP TABLE IF EXISTS pr"); con2("CREATE TEMP TABLE pr (nif VARCHAR, dep VARCHAR, t0 DATE)") con2("INSERT INTO pr VALUES " + ",".join(f"('{a}', '{b.replace(chr(39), chr(39)*2)}', DATE '{c}')" for a, b, c in pares)) con2("DELETE FROM pr WHERE t0 < DATE '2018-01-01' OR t0 > DATE '2024-12-31'") # pares de la primaria if desplaz: # placebo: t0' = t0 − 730, solo pares con t0' ≥ 1-ene-2018 (DEFINICIONES §5) con2(f"UPDATE pr SET t0 = CAST(t0 - INTERVAL {desplaz} DAY AS DATE)"); con2("DELETE FROM pr WHERE t0 < DATE '2018-01-01'") if ambito == 'CAT': own = f"""SELECT p.nif, p.dep, p.t0, l.fecha_adj - p.t0 dd FROM pr p JOIN dep_org o ON o.dep = p.dep JOIN lot l ON l.organo_clave = o.organo_clave AND l.emp = p.nif WHERE l.x = 'GENCAT' {filt} AND l.fecha_adj BETWEEN p.t0 - 365 AND p.t0 + 365""" ctrl = f"""SELECT p.nif, p.dep, p.t0, l.fecha_adj - p.t0 dd FROM pr p JOIN lot l ON l.emp = p.nif WHERE l.x = 'GENCAT' {filt} AND l.fecha_adj BETWEEN p.t0 - 365 AND p.t0 + 365 AND l.organo_clave IN (SELECT organo_clave FROM dep_org) AND l.organo_clave NOT IN (SELECT o.organo_clave FROM dep_org o JOIN (SELECT DISTINCT nif, dep FROM cag_emp WHERE nif IS NOT NULL) m ON m.dep = o.dep WHERE m.nif = p.nif)""" else: own = f"""SELECT p.nif, p.dep, p.t0, l.fecha_adj - p.t0 dd FROM pr p JOIN lot l ON l.emp = p.nif WHERE l.x = 'AYTO_MAD' {filt} AND l.fecha_adj BETWEEN p.t0 - 365 AND p.t0 + 365""" ctrl = f"""SELECT p.nif, p.dep, p.t0, l.fecha_adj - p.t0 dd FROM pr p JOIN lot l ON l.emp = p.nif WHERE l.ccaa = 'MD' AND l.x IS DISTINCT FROM 'AYTO_MAD' {filt} AND l.fecha_adj BETWEEN p.t0 - 365 AND p.t0 + 365""" pares2 = x("SELECT nif, dep, t0 FROM pr").fetchall() d = {(a, b, c): [a, 0, 0, 0, 0, {}, {}] for a, b, c in pares2} for q, i in ((own, 1), (ctrl, 3)): for a, b, c, dd in x(q).fetchall(): if dd == 0: continue r = d[(a, b, c)]; r[i + (1 if dd > 0 else 0)] += 1 m = (dd // 30.44) if dd > 0 else -((-dd) // 30.44) - 1 # mes relativo: −12..−1, 0..11 m = int(max(-12, min(11, m))); h = r[5] if i == 1 else r[6]; h[m] = h.get(m, 0) + 1 return list(d.values()) def did(rows): pre = sum(r[1] for r in rows); post = sum(r[2] for r in rows); cpre = sum(r[3] for r in rows); cpost = sum(r[4] for r in rows) if pre == 0 or cpre == 0 or cpost == 0: return float('nan'), pre, post, cpre, cpost return (post / pre) / (cpost / cpre), pre, post, cpre, cpost def boot(rows, B=1000, seed=22): by = {} for r in rows: by.setdefault(r[0], []).append(r) fs = sorted(by); rnd = random.Random(seed); out = [] for _ in range(B): rr = [p for _ in fs for p in by[rnd.choice(fs)]] v = did(rr)[0] if v == v: out.append(v) out.sort(); return [round(out[int(.025 * len(out))], 4), round(out[int(.975 * len(out)) - 1], 4)] if out else [None, None] def resumen(rows): v, pre, post, cpre, cpost = did(rows) return dict(pares=len(rows), empresas=len({r[0] for r in rows}), pares_con_lotes_propios=sum(1 for r in rows if r[1] + r[2] > 0), pre=pre, post=post, ctrl_pre=cpre, ctrl_post=cpost, R_met=round(post / pre, 4) if pre else None, R_ctrl=round(cpost / cpre, 4) if cpre else None, did=round(v, 4) if v == v else None, ic=boot(rows)) res = {} pc = pares_cat(); log('pares CAT (todas las fechas)', len(pc)) mad = x("SELECT nif, 'AYTO' dep, min(d) FROM mag_emp WHERE nif IS NOT NULL AND d IS NOT NULL GROUP BY 1").fetchall() hist = {} for amb, pares in (('CAT', pc), ('MAD', mad)): for nombre, men, des in (('primaria', False, 0), ('placebo_730', False, 730), ('S1_con_menores', True, 0)): rows = medir(pares, men, des, amb) if des: # placebo: solo pares cuyo t0 real permite t0' ≥ 2018 (ya filtrado al desplazar) pass res[f'{amb}_{nombre}'] = resumen(rows); log(amb, nombre, res[f'{amb}_{nombre}']) if nombre == 'primaria': hm = {}; hc = {} for r in rows: for m, n in r[5].items(): hm[m] = hm.get(m, 0) + n for m, n in r[6].items(): hc[m] = hc.get(m, 0) + n hist[amb] = dict(met={str(k): hm.get(k, 0) for k in range(-12, 12)}, ctrl={str(k): hc.get(k, 0) for k in range(-12, 12)}, pares=len(rows)) # lotes sin fecha_adj (excluidos) res['lotes_gencat_sin_fecha'] = x("SELECT count(*) FILTER (WHERE fecha_adj IS NULL), count(*) FROM lot WHERE x='GENCAT' AND NOT menor").fetchone() res['lotes_aytomad_sin_fecha'] = x("SELECT count(*) FILTER (WHERE fecha_adj IS NULL), count(*) FROM lot WHERE x='AYTO_MAD' AND NOT menor").fetchone() def veredicto(p, pl): if p['pares_con_lotes_propios'] < 30: return 'NO CONCLUYENTE (sin potencia)' if p['ic'][0] is None: return 'NO CONCLUYENTE' if p['did'] >= 1.5 and p['ic'][0] > 1.2 and pl['ic'][0] is not None and pl['ic'][0] <= 1 <= pl['ic'][1]: return 'ASOCIACION' if p['ic'][0] <= 1 <= p['ic'][1]: return 'NULO' return 'NO CONCLUYENTE' res['veredicto_CAT'] = veredicto(res['CAT_primaria'], res['CAT_placebo_730']) res['veredicto_MAD'] = veredicto(res['MAD_primaria'], res['MAD_placebo_730']) log('VEREDICTOS', res['veredicto_CAT'], res['veredicto_MAD']) json.dump(res, open('/w/interno/40_res.json', 'w'), indent=1); json.dump(hist, open('/w/interno/40_hist.json', 'w'), indent=1)