"""LOB1 · 20 · precisión del casado por nombre medida en MAD-R y CAM-R (traen NIF): DEFINICIONES §2.3.""" import sys, json; sys.path.insert(0, '/w/py'); from comun import * con = abrir(ro=True); x = con.execute from etl.v2.identidad import MACROS_IDENTIDAD pass # macros ya guardadas en la base (10) q = """SELECT r.registro, count(*) n_nombre, count(*) FILTER (WHERE n.n1 = r.nif_decl) ok, count(*) FILTER (WHERE n.n1 <> r.nif_decl) ko, count(*) FILTER (WHERE n.n1 <> r.nif_decl AND r.nif_decl IN (SELECT empresa_clave FROM emp)) ko_nif_en_contratos FROM reg r JOIN reg_nom n USING (registro, gid) WHERE r.registro IN ('MAD-R','CAM-R') AND n.n1 IS NOT NULL AND formato_nif(r.nif_decl) GROUP BY 1""" res = x(q).fetchall(); log(res) ok = sum(r[2] for r in res); tot = sum(r[1] for r in res) from math import sqrt def wilson(k, n, z=1.96): p = k/n; d = 1+z*z/n; c = (p+z*z/(2*n))/d; h = z*sqrt(p*(1-p)/n+z*z/(4*n*n))/d; return [round(c-h,4), round(c+h,4)] out = {'por_registro': [dict(registro=r[0], casados_por_nombre=r[1], nif_coincide=r[2], nif_distinto=r[3], nif_distinto_y_declarado_en_contratos=r[4]) for r in res], 'precision': round(ok/tot, 4), 'ic95_wilson': wilson(ok, tot), 'n': tot, 'recall_nombre_sobre_nif': x("""SELECT count(*) FILTER (WHERE n.n1 = r.nif_decl), count(*) FROM reg r JOIN reg_nom n USING (registro, gid) WHERE r.registro IN ('MAD-R','CAM-R') AND r.nif_decl IN (SELECT empresa_clave FROM emp)""").fetchone()} # ejemplos de discrepancia a interno (con nombres) x("""COPY (SELECT r.registro, r.nom, r.nif_decl, n.n1 nif_por_nombre, norm_empresa(r.nom) k FROM reg r JOIN reg_nom n USING (registro, gid) WHERE r.registro IN ('MAD-R','CAM-R') AND n.n1 IS NOT NULL AND formato_nif(r.nif_decl) AND n.n1 <> r.nif_decl) TO '/w/interno/20_discrepancias.csv' (HEADER)""") json.dump(out, open('/w/interno/20_precision.json', 'w'), indent=1); log(out)