"""BU2 · 30 · segunda implementación (pandas + pyarrow + numpy, SIN DuckDB), escrita desde DEFINICIONES_PREVIAS §1-§5. No importa comun.py. Escribe salidas/30_impl2.json.""" import os, json, math, time import numpy as np, pandas as pd, pyarrow.dataset as ds T0 = time.time() def log(*a): print(f"[{time.time()-T0:6.1f}s]", *a, flush=True) U = '/d/unified_20261018'; I = '/d/aggregations_20261018am/indicadores/' COLS = ['id_canonical', 'tipo_contrato', 'anio_ref', 'imp_adj_eur_comparable', 'organo_nif', 'organo_nombre', 'dir3', 'iva_base', 'fecha_adj', 'unidad', 'vigente', 'es_contrato', 'copia_de', 'es_menor', 'correccion_imp_adj'] def lee(src, filtro, cols=COLS): d = ds.dataset(f'{U}/source={src}', format='parquet', partitioning='hive'); have = set(d.schema.names) t = d.to_table(columns=[c for c in cols if c in have], filter=filtro).to_pandas() for c in cols: if c not in t.columns: t[c] = None t['source'] = src; return t base = (ds.field('unidad') == 'adjudicacion') & (ds.field('vigente') == True) & (ds.field('es_contrato') == True) \ & ds.field('tipo_contrato').isin(['Servicios', 'Suministros', 'Obras']) & (ds.field('anio_ref') >= 2019) & (ds.field('anio_ref') <= 2025) partes = [] for d in sorted(os.listdir(U)): src = d.split('=', 1)[1] sch = set(ds.dataset(f'{U}/{d}', format='parquet', partitioning='hive').schema.names) if 'es_menor' not in sch or 'imp_adj_eur_comparable' not in sch: continue partes.append(lee(src, base & (ds.field('es_menor') == True) & (ds.field('imp_adj_eur_comparable') > 0))) df = pd.concat(partes, ignore_index=True); del partes df = df[df.copia_de.isna()] fa = pd.to_datetime(df.fecha_adj); df = df[fa.isna() | (fa >= pd.Timestamp('2018-03-09'))] df = df[~((df.source == 'ARAGON') & (df.iva_base == 'sin_medido'))] of = pd.read_parquet('/d/unified_20261018.identidad/organos_filas.parquet', columns=['organo_nif', 'organo_nombre', 'dir3', 'organo_clave']) ot = pd.read_parquet('/d/unified_20261018.identidad/organos_tipo.parquet', columns=['organo_clave', 'agente_secti']) def organo(t): t = t.merge(of, on=['organo_nif', 'organo_nombre', 'dir3'], how='left').merge(ot, on='organo_clave', how='left') return t[t.organo_clave.notna()].copy() df = organo(df) df['cents'] = np.round(df.imp_adj_eur_comparable.to_numpy() * 100).astype(np.int64) sec = df.agente_secti.fillna(False).astype(bool) df['grupo'] = np.where(df.tipo_contrato == 'Obras', 'O', np.where(sec, 'SSI', 'SS')); df['u'] = np.where(df.tipo_contrato == 'Obras', 40000, 15000) df['anio'] = df.anio_ref.astype(int) cnt = df[df.cents < df.u * 100].groupby(['organo_clave', 'anio', 'grupo', 'source']).size().rename('n').reset_index() cnt['pri'] = (cnt.source != 'PLACSP').astype(int) el = cnt.sort_values(['organo_clave', 'anio', 'grupo', 'n', 'pri', 'source'], ascending=[True, True, True, False, True, True]).drop_duplicates(['organo_clave', 'anio', 'grupo']) m = df.merge(el[['organo_clave', 'anio', 'grupo', 'source']], on=['organo_clave', 'anio', 'grupo', 'source']) log('M', len(m)); del df nocuenta = set(pd.read_parquet('/d/unified_20261018.vinculos/filas.parquet', columns=['id_canonical', 'cuenta']).query('cuenta == False').id_canonical) p = m[(m.source == 'PLACSP') & m.anio.isin([2020, 2022, 2023, 2024]) & (m.cents >= 70 * m.u) & (m.cents < 100 * m.u) & m.grupo.isin(['SS', 'O'])].copy() p = p[~(p.id_canonical.isin(nocuenta) | (p.correccion_imp_adj == 'copia_otra_fuente'))] p['franja'] = p.cents >= 95 * p.u # exposición ms = pd.read_parquet(I + 'm1r_series.parquet', columns=['serie_m1r', 'anio_fin', 'suma']) sl = pd.read_parquet(I + 'series_licitadas_series.parquet'); sl = sl[sl.fuente == 'PLACSP'][['serie_m1r', 'parejas_candidatas', 'paso_a_licitacion_misma']] ms = ms.merge(sl, on='serie_m1r', how='left', indicator=True) val = ms.anio_fin.isin([2020, 2022, 2023, 2024]) pm = ms.paso_a_licitacion_misma ms['estado'] = 'NO' desc = (ms._merge == 'left_only') | pm.isna() | (~val & (ms.parejas_candidatas.fillna(0) > 0)) ms.loc[desc, 'estado'] = 'DESCONOCIDO' ms.loc[val & (pm == True), 'estado'] = 'MISMA' # MISMA tiene precedencia (pm no nulo) mc = pd.read_parquet(I + 'm1r_contratos.parquet', columns=['id_canonical', 'serie_m1r']) p = p.merge(mc, on='id_canonical', how='left').merge(ms[['serie_m1r', 'estado', 'suma']], on='serie_m1r', how='left') p['estado'] = p.estado.fillna('SIN_SERIE') log('universo PLACSP', len(p), p.estado.value_counts().to_dict()) p = p[p.estado != 'DESCONOCIDO'].copy(); p['e'] = p.estado == 'MISMA' def t22(g): return (int((g.franja & g.e).sum()), int((g.franja & ~g.e).sum()), int((~g.franja & g.e).sum()), int((~g.franja & ~g.e).sum())) def mh2(ts): num = den = 0.0; a1 = a2 = a3 = 0.0 for a, b, c, d in ts: n = a + b + c + d; R_ = a * d / n; S_ = b * c / n if R_ + S_ == 0: continue num += R_; den += S_; a1 += (a + d) / n * R_; a2 += ((a + d) / n * S_ + (b + c) / n * R_); a3 += (b + c) / n * S_ o = num / den; se = math.sqrt(a1 / (2 * num ** 2) + a2 / (2 * num * den) + a3 / (2 * den ** 2)) return [o, o * math.exp(-1.96 * se), o * math.exp(1.96 * se)] p['tipo'] = p.tipo_contrato est = {f'{k[0]}-{k[1]}': t22(g) for k, g in p.groupby(['anio', 'tipo'])} R = {'estratos': est, 'principal_mh': mh2(est.values()), 'loo': {str(y): mh2([v for k, v in est.items() if not k.startswith(str(y))]) for y in (2020, 2022, 2023, 2024)}, 'dentro_organo_mh': mh2([t22(g) for _, g in p.groupby(['organo_clave', 'anio', 'tipo'])])} piv = p.e & ((p.suma.astype(float) - p.cents / 100) <= p.u) R['sin_pivote_mh'] = mh2([t22(g) for _, g in p[~piv].groupby(['anio', 'tipo'])]) log(json.dumps(R)[:800]) # hueco del m, p, mc, ms; import gc; gc.collect() hb = base & (ds.field('imp_adj_eur_comparable') >= 10500) & (ds.field('imp_adj_eur_comparable') < 52000) h = lee('PLACSP', hb) h = h[h.copia_de.isna() & (h.correccion_imp_adj != 'copia_otra_fuente') & ~h.id_canonical.isin(nocuenta)] fa = pd.to_datetime(h.fecha_adj); h = h[fa.isna() | (fa >= pd.Timestamp('2018-03-09'))] h = organo(h); h = h[~(h.agente_secti.fillna(False).astype(bool) & (h.tipo_contrato != 'Obras'))] h['u'] = np.where(h.tipo_contrato == 'Obras', 40000, 15000); h['cents'] = np.round(h.imp_adj_eur_comparable.to_numpy() * 100).astype(np.int64) h = h[(h.cents >= 70 * h.u) & (h.cents < 130 * h.u)].copy(); h['anio'] = h.anio_ref.astype(int); h['menor'] = h.es_menor.fillna(False).astype(bool) cel = h[h.menor & (h.cents < 100 * h.u)][['organo_clave', 'anio', 'tipo_contrato']].drop_duplicates() h = h.merge(cel.assign(enc=True), on=['organo_clave', 'anio', 'tipo_contrato'], how='left'); h = h[h.enc.fillna(False).astype(bool) & (h.cents != 100 * h.u)] h = h[(h.cents % 50000) != 0]; h['b'] = (h.cents - 70 * h.u) // h.u XS = 0.70 + (np.arange(60) + 0.5) * 0.01; A = np.column_stack([np.ones(25), XS[:25]]) hu = {} for (t, y), g in h.groupby(['tipo_contrato', 'anio']): c = np.bincount(g.b, minlength=60).astype(float); co, *_ = np.linalg.lstsq(A, c[:25], rcond=None); r = np.clip(co[0] + co[1] * XS, 0, None) hu[f'{t}-{y}'] = {'bins': c.astype(int).tolist(), 'E_f': float(r[25:30].sum()), 'E_e': float(r[30:].sum()), 'O_f': float(c[25:30].sum()), 'O_e': float(c[30:].sum())} R['hueco'] = hu json.dump(R, open('/w/salidas/30_impl2.json', 'w'), indent=1); log('fin')