"""PH1 · 60 · segunda implementación (§6 c): pandas + pyarrow, SIN DuckDB. Clave BORME de la impl2 de E-M3; tramos, salidas, constituciones y pares con bucles de Python. Compara pares (A, D, fuente, B) con banderas mo/mm y ternas (A, D, fuente, B, O).""" import sys, os, re, json, time, glob, datetime as dt, collections sys.path.insert(0, '/e3'); sys.path.insert(0, '/w/py') import numpy as np, pandas as pd, pyarrow as pa, pyarrow.parquet as pq, pyarrow.compute as pc from em3_impl2 import nn, borme_norm_de_nn, solo_az09 from comun_roles import ADM, SOC, APO T0 = time.time() def log(*a): print(f"[{time.time()-T0:7.1f}s]", *a, flush=True) INI = dt.date(2009, 1, 2); FINC = dt.date(2026, 2, 17); FIND = dt.date(2026, 10, 16) def menos_anios(d, n): try: return d.replace(year=d.year - n) except ValueError: return d.replace(year=d.year - n, day=28) cache_bn = {} def bn_de(e): if e not in cache_bn: cache_bn[e] = borme_norm_de_nn(nn(e)) or '' return cache_bn[e] def a_fecha(s): return pd.to_datetime(s, errors='coerce').dt.date # ---- celda sat = pd.read_parquet('/et/em3/salidas/unified_20261018.borme/empresas.parquet') e5 = pd.read_parquet('/et/em5/salidas/17_etiquetas_INTERNO.parquet', columns=['empresa_clave', 'etiqueta']) sat = sat.merge(e5, on='empresa_clave', how='left') soc = sat[sat.via.isin(['v1_exacto', 'v2_historico']) & sat.utilizable.fillna(False) & (sat.etiqueta.fillna('') != 'R')] emp2bn = dict(zip(soc.empresa_clave, soc.borme_norm)); BN = set(soc.borme_norm) fext = {k: (v.date() if hasattr(v, 'date') else v) for k, v in soc.dropna(subset=['fecha_extincion']).groupby('borme_norm').fecha_extincion.min().to_dict().items()} log('soc', len(soc), len(BN)) # ---- cargos y tramos (como RS1 impl2) t = pq.read_table('/b/parse/borme_cargos.parquet', columns=['empresa', 'fecha_borme', 'tipo_acto', 'cargo', 'persona_hash']) t = t.filter(pc.is_valid(t['persona_hash'])) cg = t['cargo'].combine_chunks().dictionary_encode() clase_de = {} for v in cg.dictionary.to_pylist(): c = solo_az09(v) if v is not None else '' clase_de[v] = 'adm' if c in ADM else 'soc' if c in SOC else 'apo' if c in APO else None clase = pc.take(pa.array([clase_de[v] for v in cg.dictionary.to_pylist()]), cg.indices) t = t.append_column('clase', clase).filter(pc.is_valid(clase)) em = t['empresa'].combine_chunks().dictionary_encode() bn_dict = [bn_de(v) if v is not None else '' for v in em.dictionary.to_pylist()] df = pd.DataFrame({'bn': np.array(bn_dict, dtype=object)[em.indices.to_numpy(zero_copy_only=False)], 'h': t['persona_hash'].to_pandas(), 'tipo': t['tipo_acto'].to_pandas(), 'clase': t['clase'].to_pandas(), 'd': pd.to_datetime(t['fecha_borme'].to_pandas()).dt.date}) del t, em, cg nemp = df[df.bn != ''].groupby('h').bn.nunique().to_dict() df = df[df.bn.isin(BN)].copy() df['op'] = df.tipo.isin(['nombramiento', 'reeleccion']); df['cl'] = df.tipo.isin(['cese', 'revocacion', 'cancelacion']) ev = df.groupby(['bn', 'h', 'clase', 'd'], sort=True).agg(op=('op', 'any')).reset_index(); del df tramos = [] for (bn, h, c), g in ev.groupby(['bn', 'h', 'clase'], sort=False): estado = None; ini = None for d, op in zip(g.d, g.op): st = 1 if op else 0 if st == 1 and estado != 1: ini = d elif st == 0 and estado is None: tramos.append([bn, h, c, INI, d]) elif st == 0 and estado == 1: tramos.append([bn, h, c, ini, d]); ini = None estado = st if estado == 1: tramos.append([bn, h, c, ini, min(fext.get(bn, FINC) or FINC, FINC)]) del ev log('tramos', len(tramos)) # ---- BORME de empresas: domicilios, salidas, sucesiones, constituciones rx = re.compile(r'rdida del car.*cter de unipersonalidad', re.I) perd = collections.defaultdict(list); sal = collections.defaultdict(list); suc = collections.defaultdict(list); cons = collections.defaultdict(list) rxf = re.compile(r'^\s*(\d{1,2})\.(\d{1,2})\.(\d{2,4})') k2bn = {}; domf = []; nsoc_s = collections.defaultdict(set) pf = pq.ParquetFile('/b/parse/borme_empresas.parquet') for bt in pf.iter_batches(batch_size=500_000, columns=['empresa', 'fecha_borme', 'actos', 'domicilio', 'fecha_constitucion', 'pdf_filename', 'num_entrada']): b = bt.to_pandas() b['bn'] = [bn_de(e) if e is not None else '' for e in b.empresa] b['d'] = pd.to_datetime(b.fecha_borme).dt.date for bn, d, a, fc, dom, pdf, num in zip(b.bn, b.d, b.actos, b.fecha_constitucion, b.domicilio, b.pdf_filename, b.num_entrada): if isinstance(dom, str) and dom != '': k = solo_az09(dom) if len(k) >= 8: nsoc_s[k].add(bn) if bn not in BN: continue k2bn[(pdf, num)] = bn if isinstance(dom, str) and dom != '': domf.append((bn, d, solo_az09(dom))) if not a: continue st = set(a.split('|')) if rx.search(a): perd[bn].append(d) if st & {'Disolución', 'Extinción', 'Auto de apertura de la fase de liquidación'}: sal[bn].append(d) if st & {'Fusión por absorción', 'Fusión', 'Escisión'}: suc[bn].append(d) if 'Constitución' in st: f = None; m = rxf.match(fc if isinstance(fc, str) else '') if m: y = int(m.group(3)); y = 2000 + y if len(m.group(3)) == 2 else y try: f = dt.date(y, int(m.group(2)), int(m.group(1))) except ValueError: f = None cons[bn].append(f if (f is not None and f <= d and (d - f).days <= 365) else d) del b nsoc = {k: len(v) for k, v in nsoc_s.items()}; del nsoc_s log('BORME empresas leído', len(domf)) socini = collections.defaultdict(list) for r in tramos: if r[2] == 'soc': socini[r[0]].append((r[3], r[1])) for r in tramos: if r[2] != 'soc': continue c = [d for d, h in socini[r[0]] if h != r[1] and d > r[3]] + [d for d in perd.get(r[0], []) if d > r[3]] if c: r[4] = min(r[4], min(c)) pg = pd.read_csv('/w/tmp/pg_hechos.csv', dtype=str, skiprows=1) # 1.ª línea «SET» de psql pg = pg[(pg.tipo == 'SUC') | ((pg.tipo == 'EXT') & (pg.subtipo == 'sucesion'))] for p, n, f in zip(pg.pdf_filename, pg.num_entrada, pg.fecha): b = k2bn.get((p, n)) if b in BN: suc[b].append(dt.date.fromisoformat(f)) # domicilios dd = pd.DataFrame(domf, columns=['bn', 'd', 'k']); del domf pd1 = dd.groupby(['bn', 'd']).k.max().reset_index().sort_values(['bn', 'd']) dtr = [] for bn, g in pd1.groupby('bn', sort=False): seq = [] for d, k in zip(g.d, g.k): if not seq or seq[-1][1] != k: seq.append([d, k]) for i, (d, k) in enumerate(seq): dtr.append((bn, k, d, seq[i + 1][0] if i + 1 < len(seq) else min(fext.get(bn, FIND) or FIND, FIND))) del dd, pd1 log('dtramos', len(dtr)) # salida y constitución por sociedad salida = {} for bn, ds in sal.items(): D = min(ds) if dt.date(2012, 1, 1) <= D <= dt.date(2025, 12, 31) and not any(abs((s - D).days) <= 365 for s in suc.get(bn, [])): salida[bn] = D constit = {} for bn, fs in cons.items(): if max(fs) - min(fs) > dt.timedelta(days=60): continue f = min(fs) if any(abs((s - f).days) <= 365 for s in suc.get(bn, [])): continue constit[bn] = f log('salidas', len(salida), 'constituciones', len(constit)) # ---- universo de adjudicaciones (desde los parquet, fichero a fichero) sanc0 = pd.read_csv('/w/interno/59_sanc.csv', dtype=str) RELEV = set(salida) | set(constit) | {emp2bn[e] for e in sanc0.emp if e in emp2bn} # solo sociedades que pueden ser A o B log('sociedades relevantes', len(RELEV)) I = '/d/unified_20261018.identidad/' of = pd.read_parquet(I + 'organos_filas.parquet', columns=['organo_nif', 'organo_nombre', 'dir3', 'organo_clave']).drop_duplicates(['organo_nif', 'organo_nombre', 'dir3']) af = pd.read_parquet(I + 'adjudicatarios_filas.parquet', columns=['adjudicatario_nif_raw', 'adjudicatario_nif', 'adjudicatario_nombre', 'empresa_clave', 'persona_fisica']) af['pf'] = af.persona_fisica.fillna(False).astype(bool) af = af.groupby(['adjudicatario_nif_raw', 'adjudicatario_nif', 'adjudicatario_nombre'], dropna=False).agg(empresa_clave=('empresa_clave', 'first'), pf=('pf', 'any')).reset_index() us = pd.read_parquet(I + 'ute_socios.parquet') us = us[us.socio_nif.notna() & ~us.socio_persona_fisica.fillna(False).astype(bool)][['ute_clave', 'socio_nif']].drop_duplicates() nsocios = us.groupby('ute_clave').size().to_dict() vin = pd.read_parquet('/d/unified_20261018.vinculos/filas.parquet', columns=['source', 'id_canonical', 'cuenta']) nocuenta = set(zip(vin.source[vin.cuenta == False], vin.id_canonical[vin.cuenta == False])); del vin COLS = ['id_canonical', 'organo_nif', 'organo_nombre', 'dir3', 'cpv_division', 'anio_ref', 'adjudicatario_nif_raw', 'adjudicatario_nif', 'adjudicatario_nombre', 'unidad', 'es_menor', 'es_menor_dudoso', 'copia_de', 'correccion_imp_adj', 'anulado_en_origen', 'fecha_adj', 'fecha_formalizacion', 'imp_adj_eur_comparable', 'imp_adj_eur_sin_iva', 'imp_adj_eur'] partes = [] for f in sorted(glob.glob('/d/unified_20261018/*/*/*.parquet')): src = f.split('/')[3].split('=', 1)[1] sch = pq.read_schema(f).names tb = pq.read_table(f, columns=[c for c in COLS if c in sch]).to_pandas() for c in COLS: if c not in tb: tb[c] = None def fb(c): return tb[c].fillna(False).astype(bool) m = (tb.unidad == 'adjudicacion') & ~fb('es_menor_dudoso') & tb.copia_de.isna() & (tb.correccion_imp_adj.fillna('') != 'copia_otra_fuente') \ & ~fb('anulado_en_origen') & tb.anio_ref.between(2012, 2025) tb = tb[m].copy() if not len(tb): continue tb['source'] = src tb = tb[[(src, i) not in nocuenta for i in tb.id_canonical]] tb = tb.merge(of, on=['organo_nif', 'organo_nombre', 'dir3'], how='left').merge(af, on=['adjudicatario_nif_raw', 'adjudicatario_nif', 'adjudicatario_nombre'], how='left') tb = tb[tb.empresa_clave.notna() & ~tb.pf.fillna(False).astype(bool) & tb.organo_clave.notna()] tb = tb.merge(us, left_on='empresa_clave', right_on='ute_clave', how='left') tb['emp'] = tb.socio_nif.where(tb.ute_clave.notna(), tb.empresa_clave) tb['bn'] = tb.emp.map(emp2bn); tb = tb[tb.bn.isin(RELEV)] if not len(tb): continue F = pd.to_datetime(tb.fecha_adj, errors='coerce').fillna(pd.to_datetime(tb.fecha_formalizacion, errors='coerce')) tb['F'] = F.dt.date imp = pd.to_numeric(tb.imp_adj_eur_comparable, errors='coerce').fillna(pd.to_numeric(tb.imp_adj_eur_sin_iva, errors='coerce')).fillna(pd.to_numeric(tb.imp_adj_eur, errors='coerce')) imp[tb.correccion_imp_adj.fillna('').isin(['repetido', 'inverosimil'])] = np.nan tb['imp'] = imp / tb.ute_clave.map(nsocios).fillna(1) tb['es_menor'] = tb.es_menor.fillna(False).astype(bool) tb = tb[['source', 'id_canonical', 'organo_clave', 'cpv_division', 'anio_ref', 'es_menor', 'F', 'imp', 'bn']].drop_duplicates() for c in ('source', 'organo_clave', 'cpv_division', 'bn'): tb[c] = tb[c].astype('category') partes.append(tb) for p_ in partes: for c in ('source', 'organo_clave', 'cpv_division', 'bn'): p_[c] = p_[c].astype(object) U = pd.concat(partes, ignore_index=True); del partes U['anio_ref'] = U.anio_ref.astype(int) # pd.NaT es instancia de datetime: se comprueba con pd.notna (bug de la 1.ª pasada, 22-oct) U['F_lo'] = [f if pd.notna(f) else dt.date(y, 1, 1) for f, y in zip(U.F, U.anio_ref)] U['F_hi'] = [f if pd.notna(f) else dt.date(y, 12, 31) for f, y in zip(U.F, U.anio_ref)] log('universo celda', len(U)) porbn = {b: g for b, g in U.groupby('bn', sort=False)} del U # ---- A: eventos sanc = pd.read_csv('/w/interno/59_sanc.csv', dtype=str); resol = pd.read_csv('/w/interno/59_resol.csv', dtype=str) R_ = {e: dt.date.fromisoformat(r) for e, r in zip(resol.exp, resol.R)} eventos = [(bn, D, 'borme') for bn, D in salida.items()] for e, emp in zip(sanc.exp, sanc.emp): if emp in emp2bn and e in R_: eventos.append((emp2bn[emp], R_[e], e)) eventos = sorted(set(eventos)) def pre(bn, D): g = porbn.get(bn) if g is None: return None m = (g.F_hi <= D) & (g.F_lo >= menos_anios(D, 5)) return g[m] # índices de vínculos tr_h = collections.defaultdict(list); tr_bn = collections.defaultdict(list) for bn, h, c, a, b in tramos: if nemp.get(h, 0) <= 20: tr_h[h].append((bn, a, b)); tr_bn[bn].append((h, a, b)) dt_k = collections.defaultdict(list); dt_bn = collections.defaultdict(list) for bn, k, a, b in dtr: if nsoc.get(k, 99) < 10: dt_k[k].append((bn, a, b)); dt_bn[bn].append((k, a, b)) bcan = {b: f for b, f in constit.items() if b in porbn} pares = {}; ternas = {} for A, D, fu in eventos: gA = pre(A, D) if gA is None or not len(gA): continue w0 = menos_anios(D, 3); cand = set() for idx_bn, idx_key in ((tr_bn, tr_h), (dt_bn, dt_k)): for key, a0, a1 in idx_bn.get(A, []): if not (a0 <= D and a1 >= w0): continue for B, b0, b1 in idx_key[key]: if B == A or B not in bcan: continue fcB = bcan[B] if not ((D - dt.timedelta(days=730)) <= fcB <= D + dt.timedelta(days=365)): continue if b0 <= D + dt.timedelta(days=365) and b1 >= fcB: cand.add(B) if not cand: continue OA = set(gA.organo_clave); MA = set(gA.cpv_division.dropna()) for B in cand: g = porbn[B]; post = g[g.F_lo > D] mo_rows = post[post.organo_clave.isin(OA)] pares[(A, D, fu, B)] = (len(mo_rows) > 0, bool(post.cpv_division.isin(MA).any())) for O, n in mo_rows.groupby('organo_clave').size().items(): ternas[(A, D, fu, B, O)] = int(n) log('impl2 pares', len(pares), 'ternas', len(ternas)) p1 = pd.read_parquet('/w/interno/59_par_impl1.parquet'); p1['D'] = pd.to_datetime(p1.D).dt.date k1 = {(a, d, f, b): (bool(o), bool(m)) for a, d, f, b, o, m in zip(p1.A, p1.D, p1.fuente, p1.B, p1.mo, p1.mm)} t1 = pd.read_parquet('/w/interno/59_terna_impl1.parquet'); t1['D'] = pd.to_datetime(t1.D).dt.date kt1 = {(a, d, f, b, o): int(n) for a, d, f, b, o, n in zip(t1.A, t1.D, t1.fuente, t1.B, t1.O, t1.n_adj)} claves = set(k1) | set(pares) dif_p = [k for k in claves if k1.get(k) != pares.get(k)] claves_t = set(kt1) | set(ternas) dif_t = [k for k in claves_t if kt1.get(k) != ternas.get(k)] R = {'motor': f'pandas {pd.__version__}, pyarrow {pa.__version__}, sin DuckDB', 'pares_impl1': len(k1), 'pares_impl2': len(pares), 'pares_union': len(claves), 'dif_pares': len(dif_p), 'pct_dif_pares': round(100 * len(dif_p) / max(1, len(claves)), 3), 'mo_impl1_impl2': [sum(v[0] for v in k1.values()), sum(v[0] for v in pares.values())], 'mm_impl1_impl2': [sum(v[1] for v in k1.values()), sum(v[1] for v in pares.values())], 'ternas_impl1': len(kt1), 'ternas_impl2': len(ternas), 'dif_ternas': len(dif_t), 'pct_dif_ternas': round(100 * len(dif_t) / max(1, len(claves_t)), 3), 'ejemplos_dif_pares': [[str(x) for x in k] + [str(k1.get(k)), str(pares.get(k))] for k in sorted(dif_p, key=str)[:20]], 'ejemplos_dif_ternas': [[str(x) for x in k] + [kt1.get(k), ternas.get(k)] for k in sorted(dif_t, key=str)[:20]]} R['veredicto'] = 'PASA' if R['pct_dif_pares'] <= 0.5 and R['pct_dif_ternas'] <= 0.5 else 'NO PASA (ver diferencias)' json.dump(R, open('/w/interno/60_impl2.json', 'w'), indent=1, default=str); log({k: v for k, v in R.items() if not k.startswith('ejemplos')})