"""E-S1 · Segunda implementación independiente (pandas, sin SQL) del universo U1, del pool TED con la corrección de fechas (desvío D1), de las estrategias E1-E5 y de la tasa por año. Escrita a partir de DEFINICIONES_PREVIAS.md, no del SQL. Salida: /c/salidas/07_impl2_tasas.csv y /c/interno/impl2_unidades.parquet (unidad, estrategia). Uso (contenedor es1-impl2, pandas 2.2.3, sin red): python3 /c/py/impl2.py""" import re, time, sys import numpy as np, pandas as pd, pyarrow.dataset as ds, pyarrow.parquet as pq D = '/d/unified_20261018' I = '/d/unified_20261018.identidad' t0 = time.time() def log(*a): print(f'[{time.time()-t0:6.0f}s]', *a, flush=True) # ---------- identidad ---------- of = pd.read_parquet(f'{I}/organos_filas.parquet', columns=['organo_nif','organo_nombre','dir3','organo_clave','entidad_clave','organo_nombre_norm']) ot = pd.read_parquet(f'{I}/organos_tipo.parquet', columns=['organo_clave','tipo']) af = pd.read_parquet(f'{I}/adjudicatarios_filas.parquet', columns=['adjudicatario_nif_raw','adjudicatario_nif','adjudicatario_nombre','nombre_norm']) NIFRE = re.compile(r'^[A-Z][0-9]{7}[0-9A-Z]$') def nif_like(s): return s.where(s.fillna('').str.fullmatch(r'[A-Z][0-9]{7}[0-9A-Z]')) def exp_norm(s): x = s.fillna('').str.upper().str.replace(r'[^A-Z0-9]', '', regex=True) ok = (x.str.len() >= 5) & s.fillna('').str.contains(r'[0-9]') return x.where(ok) def con_identidad(df): df = df.merge(of, on=['organo_nif','organo_nombre','dir3'], how='left') df = df.merge(af, on=['adjudicatario_nif_raw','adjudicatario_nif','adjudicatario_nombre'], how='left') return df # ---------- PLACSP: universo U1 ---------- cols = ['id_canonical','tabla_origen','unidad','copia_de','anulado_en_origen','tipo_contrato','rol_am','procedimiento_native','fecha_adj', 'fecha_formalizacion','fecha_pub','valor_estimado_eur_expediente','imp_lic_eur_expediente','licitacion_id','lote_id','sara','cpv_code', 'organo_nif','organo_nombre','dir3','adjudicatario_nif_raw','adjudicatario_nif','adjudicatario_nombre','adjudicatario_nif_valid', 'imp_adj_eur','imp_adj_eur_sin_iva','imp_adj_eur_con_iva','expediente'] dsp = ds.dataset(f'{D}/source=PLACSP', format='parquet', partitioning='hive') p = dsp.to_table(columns=cols, filter=(ds.field('tabla_origen') == 'resultados_licitaciones') & (ds.field('unidad') == 'adjudicacion')).to_pandas() log('PLACSP filas', len(p)) p = p[p.copia_de.isna() & ~p.anulado_en_origen.fillna(False).astype(bool)] p = p[p.tipo_contrato.isin(['Obras','Suministros','Servicios','Concesion_obras','Concesion_servicios'])] p = p[~p.rol_am.fillna('-').isin(['basado','basado_sda','sda'])] CONPUB = ['Abierto','Restringido','Negociado con publicidad','Licitación con negociación','Diálogo competitivo','Asociación para la innovación'] p['estrato'] = np.where(p.procedimiento_native.isin(CONPUB), 'con_publicidad', np.where(p.procedimiento_native == 'Negociado sin publicidad', 'nsp', None)) p = p[p.estrato.notna()] for c in ['fecha_adj','fecha_formalizacion','fecha_pub']: p[c] = pd.to_datetime(p[c]) p['ref'] = p.fecha_adj.fillna(p.fecha_formalizacion) p = p[(p.ref >= '2018-01-01') & (p.ref <= '2025-12-31') & (p.fecha_pub >= '2018-01-01')] p['ve1'] = p.valor_estimado_eur_expediente.fillna(p.imp_lic_eur_expediente) p['ve'] = p.groupby('licitacion_id').ve1.transform('max') yp = p.fecha_pub.dt.year per = np.select([yp.between(2018,2019), yp.between(2020,2021), yp.between(2022,2023), yp.between(2024,2025)], [0,1,2,3], -1) p = p[per >= 0]; per = per[per >= 0] p = con_identidad(p).merge(ot, on='organo_clave', how='left') p['tipo_org'] = p['tipo'].fillna('sin_tipo') obras = np.array([5548000,5350000,5382000,5538000]); gen = np.array([221000,214000,215000,221000]); age = np.array([144000,139000,140000,143000]) cpv = p.cpv_code.fillna('') anexo4 = (p.tipo_contrato == 'Servicios') & cpv.str.match(r'^(55|75|7962|797|7995|80|85|92|98)') es_obra = p.tipo_contrato.isin(['Obras','Concesion_obras','Concesion_servicios']) thr = np.where(es_obra, obras[per], np.where(anexo4, 750000, np.where(p.tipo_org == 'age', age[per], gen[per]))) p['u1'] = p.ref.dt.year.between(2023, 2025) & (p.sara == True) & (p.ve >= thr) thr_gen = np.where(es_obra, obras[per], np.where(anexo4, 750000, gen[per])) POB = sys.argv[1] if len(sys.argv) > 1 else 'u1' # 'u1': solo U1 compite por los avisos; 'uf': como el SQL (U1 ∪ sobre umbral 2018-2025) p = p[p.u1 | ((POB == 'uf') & (p.ve >= thr_gen))].copy() log('población del cruce', POB) p['unit'] = p.licitacion_id + '#' + p.lote_id.fillna(p.licitacion_id) p['imp_sin'] = p.imp_adj_eur_sin_iva.fillna(p.imp_adj_eur) p['imp_con'] = p.imp_adj_eur_con_iva.fillna(p.imp_sin * 1.21) p['ent'] = nif_like(p.entidad_clave) p['exp_n'] = exp_norm(p.expediente) p['nif'] = p.adjudicatario_nif.where(p.adjudicatario_nif_valid == True) p['nom'] = p.nombre_norm.where(p.nombre_norm.fillna('').str.len() >= 8) log('U1 filas', len(p), 'unidades', p.unit.nunique()) # ---------- TED: web + versiones, fechas corregidas (desvío D1) ---------- tcols = ['id_canonical','licitacion_id','expediente','organo_nif','organo_nombre','dir3','adjudicatario_nif_raw','adjudicatario_nif','adjudicatario_nombre', 'adjudicatario_nif_valid','imp_adj_eur','fecha_pub','fecha_adj','unidad'] parts = [] for capa, root in [('web', f'{D}/source=TED'), ('versiones', '/d/unified_20261018.versiones/source=TED')]: x = ds.dataset(root, format='parquet', partitioning='hive').to_table(columns=tcols).to_pandas(); x['capa'] = capa; parts.append(x) t = pd.concat(parts, ignore_index=True) for c in ['fecha_pub','fecha_adj']: t[c] = pd.to_datetime(t[c]) t = t[t.fecha_pub >= '2017-11-01'] t['aviso'] = t.licitacion_id.str.replace('TED:', '', regex=False) raw = pq.read_table('/h/ted/ted_es_can.parquet', columns=['source','year','ted_notice_id']).to_pandas() raw = raw[(raw.source == 'csv_bulk') & raw.year.between(2020, 2023)] csv_avisos = set(raw.ted_notice_id.str[4:].str.lstrip('0') + '-' + raw.year.astype(str)) es_csv = t.aviso.isin(csv_avisos) def swap(s, m): amb = m & s.notna() & (s.dt.day <= 12) & (s.dt.day != s.dt.month) out = s.copy() out[amb] = pd.to_datetime(dict(year=s[amb].dt.year, month=s[amb].dt.day, day=s[amb].dt.month)) return out t['tpub'] = swap(t.fecha_pub, es_csv); t['tadj'] = swap(t.fecha_adj, es_csv) log('TED filas', len(t), 'avisos CSV 2020-2023 con fecha revisada', es_csv.sum()) t = con_identidad(t) t['ent'] = nif_like(t.entidad_clave); t['exp_n'] = exp_norm(t.expediente) t['nif'] = t.adjudicatario_nif.where(t.adjudicatario_nif_valid == True) t['nom'] = t.nombre_norm.where(t.nombre_norm.fillna('').str.len() >= 8) t['v'] = t.imp_adj_eur T = t[['aviso','capa','tpub','tadj','v','nif','ent','organo_nombre_norm','exp_n','nom']].rename(columns={'organo_nombre_norm':'onom'}) P = p[['unit','licitacion_id','ref','fecha_adj','fecha_formalizacion','imp_sin','imp_con','nif','ent','organo_nombre_norm','exp_n','nom']].rename(columns={'licitacion_id':'lic','organo_nombre_norm':'onom'}) W = 365 asignada = {} # unit -> (est, aviso) aviso_lic = {} # aviso -> lic def fecha_ok(c): w = (c.tpub >= c.ref - pd.Timedelta(days=30)) & (c.tpub <= c.ref + pd.Timedelta(days=W)) near = lambda a, b: (a - b).abs() <= pd.Timedelta(days=60) return w & (c.tadj.isna() | near(c.tadj, c.fecha_adj).fillna(False) | near(c.tadj, c.fecha_formalizacion).fillna(False)) def importe(c, tol): ok = (c.imp_sin > 0) & (c.v >= 1.5) & (((c.v >= (1-tol)*c.imp_sin) & (c.v <= (1+tol)*c.imp_sin)) | ((c.v >= (1-tol)*c.imp_con) & (c.v <= (1+tol)*c.imp_con))) sc = np.minimum((c.v/c.imp_sin - 1).abs(), (c.v/c.imp_con - 1).abs()) return ok, sc def resolver(c, est, unico=False): """c: unit, lic, aviso, score, dd. Aplica: solo no casadas; aviso de otra lic fuera; aviso -> una lic; unidad -> mejor aviso.""" c = c[~c.unit.isin(asignada.keys())] otra = c.aviso.map(aviso_lic) c = c[otra.isna() | (otra == c.lic)] if unico: k = c.groupby('unit').aviso.nunique(); c = c[c.unit.isin(k[k == 1].index)] c = c.sort_values(['aviso','score','dd','lic']) best_lic = c.drop_duplicates('aviso').set_index('aviso').lic c = c[c.lic == c.aviso.map(best_lic)] c = c.sort_values(['unit','score','dd','aviso']).drop_duplicates('unit') for u, a, l in zip(c.unit, c.aviso, c.lic): asignada[u] = (est, a) aviso_lic.setdefault(a, l) log(est, 'casadas', len(c)) def dd(c): return (c.tpub - c.ref).abs().dt.days def cubos(df, cols, base): # claves de cubo logarítmico (b-1, b, b+1) de cada importe en cols, para acotar la unión por entidad out = [] for col in cols: b = np.floor(np.log(df[col].clip(lower=1e-9)) / np.log(base)).astype('int64') for k in (-1, 0, 1): out.append(df.assign(cubo=b + k)) return pd.concat(out, ignore_index=True) # E1 P1 = cubos(P[P.nif.notna() & (P.imp_sin > 0)], ['imp_sin','imp_con'], 1.10) T1 = T[T.nif.notna() & (T.v >= 1.5)].copy(); T1['cubo'] = np.floor(np.log(T1.v) / np.log(1.10)).astype('int64') c = P1.merge(T1, on=['nif','cubo'], suffixes=('','_t')).drop(columns='cubo').drop_duplicates() ok, sc = importe(c, 0.10); c = c[ok & fecha_ok(c)].assign(score=sc[ok & fecha_ok(c)]); c['dd'] = dd(c) resolver(c[['unit','lic','aviso','score','dd']], 'E1') # E2 (nivel licitación) c = P[P.exp_n.notna()].merge(T[T.exp_n.notna()], on='exp_n', suffixes=('','_t')) mismo = np.where(c.ent_t.notna(), c.ent == c.ent_t, c.onom == c.onom_t) c = c[mismo & (c.tpub >= c.ref - pd.Timedelta(days=30)) & (c.tpub <= c.ref + pd.Timedelta(days=W))] c['dd'] = dd(c) cl = c.groupby(['lic','aviso'], as_index=False).dd.min() c = P[['unit','lic']].drop_duplicates().merge(cl, on='lic'); c['score'] = 0.0 resolver(c[['unit','lic','aviso','score','dd']], 'E2') # E3 (entidad + ±2 % + fecha, un solo aviso) Pe = cubos(P[P.ent.notna() & (P.imp_sin > 0)], ['imp_sin','imp_con'], 1.02) Te = T[T.ent.notna() & (T.v >= 1.5)].copy(); Te['cubo'] = np.floor(np.log(Te.v) / np.log(1.02)).astype('int64') c = Pe.merge(Te, on=['ent','cubo'], suffixes=('','_t')).drop(columns='cubo').drop_duplicates() ok, sc = importe(c, 0.02); m = ok & fecha_ok(c); c = c[m].assign(score=sc[m]); c['dd'] = dd(c) resolver(c[['unit','lic','aviso','score','dd']], 'E3', unico=True) # E4 (suma de lotes) sp = P[P.imp_sin > 0].groupby('lic').agg(ent=('ent','first'), onom=('onom','first'), rmin=('ref','min'), rmax=('ref','max'), n=('unit','size'), s_sin=('imp_sin','sum'), s_con=('imp_con','sum')).reset_index() hay_web = T.groupby('aviso').capa.apply(lambda s: (s == 'web').any()) Tv = T[(T.v >= 1.5) & ((T.capa == 'web') | ~T.aviso.map(hay_web))] st = Tv.groupby('aviso').agg(ent=('ent','first'), onom=('onom','first'), tpub=('tpub','min'), n=('v','size'), s=('v','sum')).reset_index() sp_c = cubos(sp, ['s_sin','s_con'], 1.10) st_c = st.copy(); st_c['cubo'] = np.floor(np.log(st_c.s) / np.log(1.10)).astype('int64') a = sp_c[sp_c.ent.notna()].merge(st_c[st_c.ent.notna()], on=['ent','cubo'], suffixes=('','_t')) b = sp_c.merge(st_c[st_c.ent.isna()], on=['onom','cubo'], suffixes=('','_t')).rename(columns={'ent':'ent_p'}) c = pd.concat([a, b], ignore_index=True).drop(columns='cubo').drop_duplicates(subset=['lic','aviso']) okc = ((c.n >= 2) | (c.n_t >= 2)) & (((c.s >= 0.9*c.s_sin) & (c.s <= 1.1*c.s_sin)) | ((c.s >= 0.9*c.s_con) & (c.s <= 1.1*c.s_con))) \ & (c.tpub >= c.rmin - pd.Timedelta(days=30)) & (c.tpub <= c.rmax + pd.Timedelta(days=W)) c = c[okc].copy(); c['score'] = np.minimum((c.s/c.s_sin - 1).abs(), (c.s/c.s_con - 1).abs()); c['dd'] = (c.tpub - c.rmin).abs().dt.days c = P[['unit','lic']].drop_duplicates().merge(c[['lic','aviso','score','dd']], on='lic') resolver(c, 'E4') # E5 Pn = cubos(P[P.nom.notna() & (P.imp_sin > 0)], ['imp_sin','imp_con'], 1.10) Tn = T[T.nom.notna() & (T.v >= 1.5)].copy(); Tn['cubo'] = np.floor(np.log(Tn.v) / np.log(1.10)).astype('int64') c = Pn.merge(Tn, on=['nom','cubo'], suffixes=('','_t')).drop(columns='cubo').drop_duplicates() ok, sc = importe(c, 0.10); m = ok & fecha_ok(c); c = c[m].assign(score=sc[m]); c['dd'] = dd(c) resolver(c[['unit','lic','aviso','score','dd']], 'E5') # ---------- tasas ---------- u = p.groupby('unit').agg(anio=('ref', lambda s: s.min().year), estrato=('estrato','first'), tipo=('tipo_contrato','first'), tipo_org=('tipo_org','first'), u1=('u1','max')).reset_index() u = u[u.u1] u['est'] = u.unit.map(lambda k: asignada.get(k, (None, None))[0]) u['aviso'] = u.unit.map(lambda k: asignada.get(k, (None, None))[1]) u.to_parquet(f'/c/interno/impl2_unidades_{POB}.parquet', index=False) cp = u[u.estrato == 'con_publicidad'] r = cp.groupby('anio').agg(n=('unit','size'), x_lo=('est', lambda s: s.isin(['E1','E2']).sum()), x_hi=('est', lambda s: s.notna().sum())).reset_index() r['pct_lo'] = (100*r.x_lo/r.n).round(2); r['pct_hi'] = (100*r.x_hi/r.n).round(2) r.to_csv(f'/c/salidas/07_impl2_tasas_{POB}.csv', index=False) print(r.to_string(index=False)); log('fin')