"""BU1 · 30 · segunda implementación (pandas + pyarrow + numpy, SIN DuckDB), escrita desde DEFINICIONES_PREVIAS §1-§4 y la enmienda ANTERIOR 1, sin importar nada de la principal. Escribe interno/impl2_*.parquet.""" import time, json import numpy as np, pandas as pd, pyarrow.dataset as ds, pyarrow.compute as pc, pyarrow as pa T0 = time.time() def log(*a): print(f"[{time.time()-T0:6.1f}s]", *a, flush=True) 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'] import os partes = [] for d in sorted(os.listdir('/d/unified_20261018')): if not d.startswith('source='): continue src = d.split('=', 1)[1] dset = ds.dataset(f'/d/unified_20261018/{d}', format='parquet', partitioning='hive') have = set(dset.schema.names); cols = [c for c in COLS if c in have] if 'es_menor' not in have or 'imp_adj_eur_comparable' not in have: continue f = (ds.field('es_menor') == True) & (ds.field('unidad') == 'adjudicacion') & (ds.field('vigente') == True) & (ds.field('es_contrato') == True) \ & (ds.field('imp_adj_eur_comparable') > 0) & ds.field('tipo_contrato').isin(['Servicios', 'Suministros', 'Obras']) \ & (ds.field('anio_ref') >= 2019) & (ds.field('anio_ref') <= 2025) t = dset.to_table(columns=cols, filter=f).to_pandas() t['source'] = src; partes.append(t) 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'))] n0 = len(df) df = df[~((df['source'] == 'ARAGON') & (df['iva_base'] == 'sin_medido'))] log('filas', n0, len(df)) of = pd.read_parquet('/d/unified_20261018.identidad/organos_filas.parquet', columns=['organo_nif', 'organo_nombre', 'dir3', 'organo_clave']) df = df.merge(of, on=['organo_nif', 'organo_nombre', 'dir3'], how='left') # pandas casa NaN con NaN, como IS NOT DISTINCT FROM assert len(df) == n0 - (n0 - len(df)) ot = pd.read_parquet('/d/unified_20261018.identidad/organos_tipo.parquet', columns=['organo_clave', 'agente_secti']) df = df.merge(ot, on='organo_clave', how='left') df = df[df['organo_clave'].notna()].copy() df['cents'] = np.round(df['imp_adj_eur_comparable'].to_numpy() * 100).astype(np.int64) secti = df['agente_secti'].fillna(False).astype(bool) df['grupo'] = np.where(df['tipo_contrato'] == 'Obras', 'O', np.where(secti, 'SSI', 'SS')) df['u'] = np.where(df['grupo'] == 'O', 40000, 15000) df['anio'] = df['anio_ref'].astype(int) log('con órgano', len(df)) # deduplicado 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) cnt = cnt.sort_values(['organo_clave', 'anio', 'grupo', 'n', 'pri', 'source'], ascending=[True, True, True, False, True, True]) el = cnt.drop_duplicates(['organo_clave', 'anio', 'grupo'])[['organo_clave', 'anio', 'grupo', 'source']] m = df.merge(el, on=['organo_clave', 'anio', 'grupo', 'source'], how='inner') log('M deduplicado', len(m)) uc = m['u'].to_numpy() * 100; ce = m['cents'].to_numpy() sel = (ce >= 70 * m['u'].to_numpy() * 1) & False # marcador lo = 70 * m['u'].to_numpy() # 0,70u en céntimos = u*100*0,70 w = m['u'].to_numpy() # w = 1 % de u, en céntimos = u en = (ce >= lo) & (ce < uc) mb = m[en].copy(); mb['b'] = (ce[en] - lo[en]) // w[en]; mb['r500'] = (mb['cents'] % 50000) != 0 assert mb['b'].between(0, 29).all() XS = 0.70 + (np.arange(30) + 0.5) * 0.01 A = np.column_stack([np.ones(25), XS[:25]]) def mide(g, rng, nmin): c = np.bincount(g.loc[g['r500'], 'b'], minlength=30).astype(float); nv = len(g) coef, *_ = np.linalg.lstsq(A, c[:25], rcond=None) E = np.clip(coef[0] + coef[1] * XS[25:], 0, None).sum(); E = 0.0 if E < 1e-9 else E; O = c[25:].sum(); X = O / E if E > 0 else np.nan lo_ = hi_ = np.nan if nv >= nmin and E > 0: N = int(c.sum()); mm = rng.multinomial(N, c / N, size=1000).astype(float) cf = np.linalg.lstsq(A, mm[:, :25].T, rcond=None)[0] Eb = np.clip(cf[0][:, None] + cf[1][:, None] * XS[None, 25:], 0, None).sum(1); Eb = np.where(Eb < 1e-9, 0.0, Eb); Ob = mm[:, 25:].sum(1) Xb = np.where(Eb > 0, Ob / np.where(Eb > 0, Eb, 1), np.inf); lo_, hi_ = np.percentile(Xb, [2.5, 97.5]) pos = 'muestra insuficiente' if nv < nmin else ('no estimable' if not E > 0 else ('hecho' if lo_ > 1.5 else 'sin hecho')) return dict(n_v=nv, O=O, E=E, X=X, ic_inf=lo_, ic_sup=hi_, posicion=pos) def nivel(claves, nmin, nombre): rng = np.random.default_rng(20261023); filas = [] for k, g in mb.groupby(claves, sort=True): filas.append({**dict(zip(claves, k)), **mide(g, rng, nmin)}) out = pd.DataFrame(filas); out.to_parquet(f'/w/interno/impl2_{nombre}.parquet'); log(nombre, len(out), out['posicion'].value_counts().to_dict()); return out nivel(['organo_clave', 'anio', 'grupo'], 100, 'oa') mb['bloque'] = np.where(mb['anio'] <= 2021, 'B1', 'B2') nivel(['organo_clave', 'bloque', 'grupo'], 100, 'ob') mm_ = mb[mb['grupo'].isin(['SS', 'O'])] mb_save = mb; mb = mm_; nivel(['tipo_contrato', 'anio'], 1, 'mercado'); mb = mb_save