"""LOB1 · 60 · segunda implementación (pandas + pyarrow, sin DuckDB; normalización reescrita en Python). Imagen licitaciones-scrapers:vps.""" import sys, json, re, csv, unicodedata, time import 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:7.1f}s]', *a, flush=True) csv.field_size_limit(10**9) I = '/d/unified_20261018.identidad/'; D = '/w/interno/datos/' def sa(s): return ''.join(c for c in unicodedata.normalize('NFKD', s) if not unicodedata.combining(c)) def norm_nombre(s): if s is None or (isinstance(s, float) and s != s): return None t = re.sub(r'[^A-Z0-9]+', ' ', sa(str(s)).upper()).strip(); return t or None FJ = re.compile(r'( (SOCIEDAD|SOCIETAT) (LIMITADA|ANONIMA|COOPERATIVA|CIVIL)( (UNIPERSONAL|LABORAL|PROFESIONAL))?| S ?L ?U| S ?A ?U| S ?L ?L| S ?L ?P| S ?L ?N ?E| S ?C ?C ?L| S ?C ?P| S ?C ?L| S ?A ?L| S ?L| S ?A| S ?C| S ?COOP( AND)?| COOP| SOCIEDAD UNIPERSONAL| SOCIETAT UNIPERSONAL| UNIPERSONAL| A ?I ?E| U ?T ?E)+$') def norm_empresa(s): t = norm_nombre(s) if t is None: return None t = FJ.sub('', t); t = re.sub(r'^(UTE|U T E|UNION TEMPORAL DE EMPRESAS) ', 'UTE ', t).strip(); return t or None PERS = re.compile(r'^([0-9]{8}|[XYZKLM][0-9]{7})[A-Z]$') # empresas e = pd.read_parquet(I + 'empresas.parquet', columns=['empresa_clave', 'empresa_tipo', 'nombre_norm']) e = e[(e.empresa_tipo == 'empresa') & ~e.empresa_clave.str.match(PERS) & ~e.empresa_clave.str[:1].isin(['P', 'S'])] EMP = set(e.empresa_clave) dic = pd.read_parquet(I + 'diccionario_nombres.parquet', columns=['nombre_norm', 'nif_inferido']).dropna() uni = e.dropna(subset=['nombre_norm']).groupby('nombre_norm').empresa_clave.agg(['nunique', 'min']).reset_index() uni = uni[uni['nunique'] == 1][['nombre_norm', 'min']].rename(columns={'min': 'nif_inferido'}) cand = pd.concat([dic, uni]).drop_duplicates() cand = cand[cand.nif_inferido.isin(EMP) & (cand.nombre_norm.str.len() >= 6) & cand.nombre_norm.str.contains(r'[A-Z]{4,}')] g = cand.groupby('nombre_norm').nif_inferido.agg(['nunique', 'min']); CAND = g[g['nunique'] == 1]['min'].to_dict() log('cand', len(CAND)) # registros (lectura reescrita) regs = {'CAT-R': set(), 'MAD-R': set(), 'CAM-R': set()}; catgid = {} for r in csv.DictReader(open(D + 'cat/registre_grups_interes_gwpn-de62.csv', encoding='utf-8')): a = CAND.get(norm_empresa(r['nom'])); b = CAND.get(norm_empresa(r['rao_social'])) if (a or b) and (a is None or b is None or a == b): regs['CAT-R'].add(a or b); catgid.setdefault(r['identificador'], set()).add(a or b) for fn, R, col_nif, col_nom, kw in ((D + 'mad/registro_lobbies_ayto_madrid.utf8.csv', 'MAD-R', 'Identificador(NIF)', 'Nombre o razón social', dict(encoding='utf-8')), (D + 'cam/registro_transparencia_extendido.csv', 'CAM-R', 'nif', 'denominacion', dict(encoding='utf-8-sig'))): for r in csv.DictReader(open(fn, **kw), delimiter=';'): nif = re.sub(r'[^A-Za-z0-9]', '', r[col_nif] or '').upper() if nif in EMP: regs[R].add(nif) a = CAND.get(norm_empresa(r[col_nom])) if a: regs[R].add(a) log({k: len(v) for k, v in regs.items()}) # lotes cols = ['source', 'id_canonical', 'organo_nif', 'organo_nombre', 'dir3', 'ccaa', 'cpv_division', 'anio_ref', 'fecha_adj', 'adjudicatario_nif_raw', 'adjudicatario_nif', 'adjudicatario_nombre', 'es_menor', 'es_menor_dudoso', 'unidad', 'copia_de', 'correccion_imp_adj', 'anulado_en_origen'] dset = ds.dataset('/d/unified_20261018', format='parquet', partitioning='hive') flt = (pc.field('unidad') == 'adjudicacion') & pc.field('ccaa').isin(['CT', 'MD']) & (pc.field('anio_ref') >= 2017) & (pc.field('anio_ref') <= 2025) partes = [] KEEP = ['source', 'id_canonical', 'organo_nif', 'organo_nombre', 'dir3', 'ccaa', 'fecha_adj', 'adjudicatario_nif_raw', 'adjudicatario_nif', 'adjudicatario_nombre', 'menor'] for frag in dset.get_fragments(filter=flt): sch = frag.physical_schema.names t = frag.to_table(columns=[c for c in cols if c in sch], filter=flt) df = t.to_pandas() for c in cols: if c not in df.columns: df[c] = None df['source'] = re.search(r'source=([^/]+)', frag.path).group(1) df = df[df.copia_de.isna() & (df.correccion_imp_adj.fillna('') != 'copia_otra_fuente') & ~df.anulado_en_origen.fillna(False).astype(bool)] df['menor'] = df.es_menor.fillna(False).astype(bool) | df.es_menor_dudoso.fillna(False).astype(bool) partes.append(df.loc[~df.menor, KEEP]) # la comparación es sobre el universo primario (sin menores) l = pd.concat(partes, ignore_index=True); del partes v = pd.read_parquet('/d/unified_20261018.vinculos/filas.parquet', columns=['source', 'id_canonical', 'cuenta']) v = v[v.source.isin(l.source.unique())] v = v.groupby(['source', 'id_canonical']).cuenta.all().reset_index() l = l.merge(v, on=['source', 'id_canonical'], how='left'); l = l[l.cuenta.fillna(True).astype(bool)].drop(columns=['cuenta', 'id_canonical']) log('l', l.ccaa.value_counts().to_dict()) a = pd.read_parquet(I + 'adjudicatarios_filas.parquet', columns=['adjudicatario_nif_raw', 'adjudicatario_nif', 'adjudicatario_nombre', 'empresa_clave']) a = a.drop_duplicates(['adjudicatario_nif_raw', 'adjudicatario_nif', 'adjudicatario_nombre']) l = l.merge(a, on=['adjudicatario_nif_raw', 'adjudicatario_nif', 'adjudicatario_nombre'], how='inner') # pandas casa NaN con NaN l = l[l.empresa_clave.isin(EMP)].rename(columns={'empresa_clave': 'emp'}) of = pd.read_parquet(I + 'organos_filas.parquet', columns=['organo_nif', 'organo_nombre', 'dir3', 'organo_clave', 'entidad_clave', 'dir3_propagado', 'organo_nif_inferido']) of0 = of[['organo_clave', 'organo_nombre']].copy() of['d3'] = of.dir3.fillna(of.dir3_propagado); of['onif'] = of.organo_nif.fillna(of.organo_nif_inferido) of = of.merge(pd.read_parquet(I + 'organos_tipo.parquet', columns=['organo_clave', 'tipo']), on='organo_clave', how='left') of = of.merge(pd.read_parquet(I + 'entidades.parquet', columns=['entidad_clave', 'administracion']), on='entidad_clave', how='left') of = of.drop_duplicates(['organo_nif', 'organo_nombre', 'dir3']) l = l.merge(of[['organo_nif', 'organo_nombre', 'dir3', 'organo_clave', 'd3', 'onif', 'tipo', 'administracion']], on=['organo_nif', 'organo_nombre', 'dir3'], how='left') log('lot', l.ccaa.value_counts().to_dict()) d3 = l.d3.fillna(''); onif = l.onif.fillna(''); tipo = l.tipo.fillna(''); adm = l.administracion univ = l.organo_nombre.map(lambda s: (norm_nombre(s) or '').startswith('UNIVERSITAT')) gen = (l.ccaa == 'CT') & ((adm == 'Generalitat de Catalunya') | (l.source == 'CATALUNYA_GENERALITAT') | d3.str.startswith('A09') | onif.str.startswith('S08')) & ~tipo.isin(['ayuntamiento', 'local_supramunicipal', 'age']) & ~univ aym = (l.ccaa == 'MD') & ((l.source == 'MADRID_AYTO') | d3.str.startswith('L01280796') | onif.str.startswith('P28079')) & (tipo != 'age') cam = (l.ccaa == 'MD') & ((l.source == 'MADRID_CAM') | (adm == 'Comunidad de Madrid') | d3.str.startswith('A13') | onif.str.startswith('S78')) & (adm.isna() | (adm == 'Comunidad de Madrid')) & ~tipo.isin(['ayuntamiento', 'local_supramunicipal', 'age']) l['x'] = None; l.loc[cam, 'x'] = 'CAM'; l.loc[aym, 'x'] = 'AYTO_MAD'; l.loc[gen, 'x'] = 'GENCAT' REG = {'GENCAT': 'CAT-R', 'AYTO_MAD': 'MAD-R', 'CAM-R' and 'CAM': 'CAM-R'}; TER = {'GENCAT': 'CT', 'AYTO_MAD': 'MD', 'CAM': 'MD'} out = {'casado': {k: sorted(v) for k, v in regs.items()}, 'c': {}, 'a': {}, 'n_lot': sorted(l.ccaa.value_counts().items())} nm = l[~l.menor] for X, R in REG.items(): s = nm[nm.x == X]; out['c'][X] = [int(s.emp.nunique()), int(s[s.emp.isin(regs[R])].emp.nunique()), int(len(s))] t = nm[nm.ccaa == TER[X]]; f = t.groupby('emp').agg(n=('x', 'size'), k=('x', lambda z: int((z == X).sum()))); f = f[f.n >= 3] f['r'] = f.index.isin(regs[R]); out['a'][X] = [[bool(r), int(len(g)), int(g.k.sum()), int(g.n.sum())] for r, g in f.groupby('r')] log({k: v for k, v in out.items() if k != 'casado'}) # (b) CAT primaria ag = [] for r in csv.DictReader(open(D + 'cat/agenda_alts_carrecs_hd8k-y28e.csv', encoding='utf-8')): codi = (r['Codi registre grup interès'] or '').strip() if codi: s_ = catgid.get(codi, set()); nif = next(iter(s_)) if len(s_) == 1 else None else: nif = CAND.get(norm_empresa(r["Grup d'interès"])) try: dd = pd.Timestamp(pd.to_datetime(r['Data'], format='%d/%m/%Y')) except Exception: dd = None ag.append((nif, r['Departament'], dd)) ag = pd.DataFrame(ag, columns=['nif', 'dep', 'd']) SW = {'DE', 'D', 'DEL', 'DELS', 'LA', 'LES', 'EL', 'I', 'A', 'AL'} def nd(s): t = norm_nombre(s) or ''; return ' '.join(w for w in t.split() if w not in SW) gl = nm[nm.x == 'GENCAT'] orgs = of0[of0.organo_clave.isin(set(gl.organo_clave.dropna()))][['organo_clave', 'organo_nombre']].drop_duplicates(); orgs['onn'] = orgs.organo_nombre.map(nd) deps = pd.DataFrame({'dep': ag.dep.dropna().unique()}); deps['dn'] = deps.dep.map(nd) mp = sorted({(d, o) for d, dn in zip(deps.dep, deps.dn) for o, on in zip(orgs.organo_clave, orgs.onn) if on == dn or on.startswith(dn + ' ')}) dep_org = pd.DataFrame(mp, columns=['dep', 'organo_clave']); MAPPED = set(dep_org.organo_clave) pares = ag.dropna(subset=['nif', 'd']) pares = pares[pares.dep.isin(set(dep_org.dep))].groupby(['nif', 'dep']).d.min().reset_index() pares = pares[(pares.d >= '2018-01-01') & (pares.d <= '2024-12-31')] met = ag.dropna(subset=['nif']).groupby('nif').dep.apply(set).to_dict() orgs_of = dep_org.groupby('dep').organo_clave.apply(set).to_dict() gl = gl.dropna(subset=['fecha_adj']).copy(); gl['fa'] = pd.to_datetime(gl.fecha_adj) by = {k: g for k, g in gl.groupby('emp')} pre = post = cpre = cpost = 0 for nif, dep, t0 in pares.itertuples(index=False): g = by.get(nif) if g is None: continue dd = (g.fa - t0).dt.days; w = (dd.abs() <= 365) & (dd != 0) own = g.organo_clave.isin(orgs_of[dep]); excl = set().union(*[orgs_of.get(d, set()) for d in met.get(nif, set())]) ctl = g.organo_clave.isin(MAPPED) & ~g.organo_clave.isin(excl) pre += int((w & own & (dd < 0)).sum()); post += int((w & own & (dd > 0)).sum()); cpre += int((w & ctl & (dd < 0)).sum()); cpost += int((w & ctl & (dd > 0)).sum()) out['b'] = [pre, post, cpre, cpost, int(len(pares))] log('b', out['b']) json.dump(out, open('/w/interno/60_impl2.json', 'w'))