"""LOC1 · 30 · segunda implementación (pandas + pyarrow, sin DuckDB), escrita desde DEFINICIONES_PREVIAS §3-§4; no importa nada de la principal. Rehace s_{P,m}, cajones, p (dejando fuera al órgano), E, V, τ² sin winsorizar por tipo × año, z_D y hecho; y n, k del CSV provincial. Al final compara con interno/oa.parquet y salidas/publicable_provincia_mercado_anio.csv. Escribe salidas/30_impl2.json.""" import json, math, numpy as np, pandas as pd, pyarrow.parquet as pq A = 50.0; NMIN = 20; Z998 = 3.090232 CORTES = [0.005, 0.01, 0.02, 0.04, 0.08, 0.16, 0.32] cols = ['organo_clave', 'tipo_organo', 'via', 'tipo_c', 'cpv_div', 'anio', 'prov_org', 'prov_emp'] t = pq.read_table('/w/interno/base.parquet', columns=cols).to_pandas() t = t[t.prov_org.notna() & t.prov_emp.notna()].copy() for k in ['organo_clave', 'tipo_organo', 'via', 'tipo_c', 'cpv_div', 'prov_org', 'prov_emp']: t[k] = t[k].astype('category') t['y'] = (t.prov_emp.astype(str) == t.prov_org.astype(str)).astype(np.int8) t['vg'] = np.where(t.via.astype(str) == 'menor', 'menor', 'no_menor') M = ['cpv_div', 'tipo_c', 'anio'] nm = t.groupby(M, observed=True).size().rename('n_m').reset_index() npm = t.groupby(M + ['prov_emp'], observed=True).size().rename('n_pm').reset_index().rename(columns={'prov_emp': 'prov_org'}) npm['prov_org'] = npm.prov_org.astype(str); t['prov_org_s'] = t.prov_org.astype(str) t = t.merge(nm, on=M, how='left').merge(npm.rename(columns={'prov_org': 'prov_org_s'}), on=M + ['prov_org_s'], how='left') t['s'] = t.n_pm.fillna(0) / t.n_m t['b'] = np.searchsorted(np.array(CORTES), t.s.values, side='right') C = ['tipo_organo', 'vg', 'cpv_div', 'tipo_c', 'anio', 'b']; T = ['tipo_organo', 'vg', 'tipo_c', 'anio', 'b']; G = ['vg', 'tipo_c', 'anio', 'b'] co = t.groupby(['organo_clave'] + C, observed=True).agg(n_co=('y', 'size'), k_co=('y', 'sum')).reset_index() cc = t.groupby(C, observed=True).agg(n_c=('y', 'size'), k_c=('y', 'sum')).reset_index() ct = t.groupby(T, observed=True).agg(n_t=('y', 'size'), k_t=('y', 'sum')).reset_index() cto = t.groupby(['organo_clave'] + T, observed=True).agg(n_to=('y', 'size'), k_to=('y', 'sum')).reset_index() cg = t.groupby(G, observed=True).agg(n_g=('y', 'size'), k_g=('y', 'sum')).reset_index() cgo = t.groupby(['organo_clave'] + G, observed=True).agg(n_go=('y', 'size'), k_go=('y', 'sum')).reset_index() q = co.merge(cc, on=C).merge(ct, on=T).merge(cto, on=['organo_clave'] + T).merge(cg, on=G).merge(cgo, on=['organo_clave'] + G) assert len(q) == len(co) dt = q.n_t - q.n_to; dg = q.n_g - q.n_go q['p_t'] = np.where(dt > 0, (q.k_t - q.k_to) / dt.where(dt > 0, 1), np.where(dg > 0, (q.k_g - q.k_go) / dg.where(dg > 0, 1), np.nan)) q['p'] = (q.k_c - q.k_co + A * q.p_t) / (q.n_c - q.n_co + A) q = q[q.p.notna()] q['E'] = q.n_co * q.p; q['V'] = q.n_co * q.p * (1 - q.p) oa = q.groupby(['organo_clave', 'tipo_organo', 'anio'], observed=True).agg(n=('n_co', 'sum'), k=('k_co', 'sum'), E=('E', 'sum'), V=('V', 'sum')).reset_index() oa['r'] = oa.k / oa.n; oa['e'] = oa.E / oa.n; oa['s'] = np.sqrt(oa.V) / oa.n def tau2_sin_w(g): g = g[(g.n >= NMIN) & (g.V > 0)] I = len(g) if I < 2: return 0.0 z = (g.r - g.e) / g.s; phi = float((z * z).mean()); w = 1 / g.s ** 2 if I * phi <= I - 1: return 0.0 return max((I * phi - (I - 1)) / (w.sum() - (w * w).sum() / w.sum()), 0.0) tt = {k: tau2_sin_w(g) for k, g in oa.groupby(['tipo_organo', 'anio'], observed=True)} oa['tau2'] = [tt.get((a, b), 0.0) for a, b in zip(oa.tipo_organo, oa.anio)] den = oa.V / oa.n ** 2 + oa.tau2 oa['zd'] = np.where(den > 0, (oa.r - oa.e) / np.sqrt(den.where(den > 0, 1)), np.nan) oa['hecho'] = (oa.n >= NMIN) & (oa.zd > Z998) # comparación p1 = pq.read_table('/w/interno/oa.parquet', columns=['organo_clave', 'anio', 'n', 'k', 'E', 'zd', 'hecho']).to_pandas() p1['organo_clave'] = p1.organo_clave.astype(str); oa['organo_clave'] = oa.organo_clave.astype(str) m = p1.merge(oa[['organo_clave', 'anio', 'n', 'k', 'E', 'zd', 'hecho']], on=['organo_clave', 'anio'], how='outer', suffixes=('_1', '_2'), indicator=True) R = {'filas_1': len(p1), 'filas_2': len(oa), 'solo_en_una': int((m._merge != 'both').sum())} b = m[m._merge == 'both'] R['n_distinto'] = int((b.n_1 != b.n_2).sum()); R['k_distinto'] = int((b.k_1 != b.k_2).sum()) R['E_dif_rel_max'] = float((abs(b.E_1 - b.E_2) / b.E_1.where(b.E_1 > 0, 1)).max()) R['E_dif_rel_gt_0_5pct'] = int((abs(b.E_1 - b.E_2) / b.E_1.where(b.E_1 > 0, 1) > 0.005).sum()) dz = abs(b.zd_1 - b.zd_2); R['zd_dif_gt_0_01'] = int((dz > 0.01).sum()); R['zd_dif_max'] = float(dz.max()) R['zd_dif_gt_0_01_pct'] = 100 * R['zd_dif_gt_0_01'] / len(b) n20 = b[b.n_1 >= NMIN]; R['hecho_distinto'] = int((n20.hecho_1.astype(bool) != n20.hecho_2.astype(bool)).sum()); R['organos_anio_n20'] = len(n20) R['hecho_distinto_pct'] = 100 * R['hecho_distinto'] / max(1, len(n20)); R['hecho_1'] = int(n20.hecho_1.astype(bool).sum()); R['hecho_2'] = int(n20.hecho_2.astype(bool).sum()) R['tau2_impl2'] = {f"{k[0]}|{k[1]}": v for k, v in sorted(tt.items(), key=lambda z: str(z[0]))} # CSV provincial: n, k por provincia × tipo × vía × año pv = t.groupby(['prov_org_s', 'tipo_c', 'via', 'anio'], observed=True).agg(n=('y', 'size'), k=('y', 'sum')).reset_index() cs = pd.read_csv('/w/salidas/publicable_provincia_mercado_anio.csv', dtype=str) cs = cs[(cs.tipo_contrato != 'todos') & (cs.via != 'todas') & (cs.anio != 'todos')] cs['anio'] = cs.anio.astype(int); cs['n'] = cs.adjudicaciones_evaluables.astype(int); cs['k'] = cs.adjudicaciones_locales.astype(int) pv['tipo_c'] = pv.tipo_c.astype(str); pv['via'] = pv.via.astype(str) mm = cs.merge(pv, left_on=['provincia_ine', 'tipo_contrato', 'via', 'anio'], right_on=['prov_org_s', 'tipo_c', 'via', 'anio'], how='outer', suffixes=('_1', '_2'), indicator=True) R['csv_prov'] = {'celdas_1': len(cs), 'celdas_2': len(pv), 'solo_en_una': int((mm._merge != 'both').sum()), 'n_distinto': int((mm.n_1 != mm.n_2).sum()), 'k_distinto': int((mm.k_1 != mm.k_2).sum())} R['pasa'] = (R['solo_en_una'] == 0 and R['n_distinto'] == 0 and R['k_distinto'] == 0 and R['E_dif_rel_gt_0_5pct'] == 0 and R['zd_dif_gt_0_01_pct'] <= 0.5 and R['hecho_distinto_pct'] <= 0.5 and R['csv_prov']['n_distinto'] == 0 and R['csv_prov']['k_distinto'] == 0 and R['csv_prov']['solo_en_una'] == 0) R['versiones'] = {'pandas': pd.__version__, 'numpy': np.__version__} json.dump(R, open('/w/salidas/30_impl2.json', 'w'), ensure_ascii=False, indent=1, default=str) print(json.dumps({k: v for k, v in R.items() if k != 'tau2_impl2'}, ensure_ascii=False, default=str))