"""DAT-CRUCE · H-GRP · segunda implementación (PREREG_GRP §5b): csv + gzip sobre los CSV crudos de GLEIF y pandas + pyarrow sobre la identidad, sin DuckDB. Reescrita desde el pre-registro (norm_empresa portada a Python desde etl/v2/identidad.py).""" import csv, gzip, re, unicodedata, json, sys import pandas as pd, pyarrow.parquet as pq csv.field_size_limit(10**8) I = '/v2/unified_20261103.identidad/' CIF = re.compile(r'^[ABCDEFGHJNPQRSUVW][0-9]{7}[0-9A-J]$'); DNI = re.compile(r'^([0-9]{8}[A-Z]|[XYZKLM][0-9]{7}[A-Z])$') FORMA = 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_nombre(s): if s is None: return None s = unicodedata.normalize('NFKD', str(s).upper()); s = ''.join(c for c in s if not unicodedata.combining(c)) s = re.sub('[^A-Z0-9]+', ' ', s).strip() return s or None def norm_empresa(s): s = norm_nombre(s) if s is None: return None s = FORMA.sub('', s) s = re.sub(r'^(UTE|U T E|UNION TEMPORAL DE EMPRESAS) ', 'UTE ', s).strip() return s or None def forma_persona(n): return bool(re.match(r'^([0-9]{8}|[XYZKLM][0-9]{7})[A-Z]$', n or '')) # GLEIF entidades g = {} with gzip.open('/gl/raw/lei2_es.csv.gz', 'rt', encoding='utf-8', newline='') as fh: for r in csv.DictReader(fh): ra = re.sub('[^A-Za-z0-9]', '', r.get('Entity.RegistrationAuthority.RegistrationAuthorityEntityID') or '').upper() if r['Entity.EntityCategory'] == 'SOLE_PROPRIETOR' or r['Registration.RegistrationStatus'] in ('ANNULLED', 'DUPLICATE', 'RETIRED') or DNI.match(ra): continue es = r['Entity.LegalAddress.Country'] == 'ES' or r['Entity.HeadquartersAddress.Country'] == 'ES' or (r['Entity.LegalJurisdiction'] or '').startswith('ES') g[r['LEI']] = {'nif': ra if CIF.match(ra) else None, 'k1': norm_empresa(r['Entity.LegalName']), 'k2': norm_empresa(r['Entity.OtherEntityNames.OtherEntityName.1'] or None), 'issued': r['Registration.RegistrationStatus'] == 'ISSUED', 'es': es} emp = pq.read_table(I + 'empresas.parquet', columns=['empresa_clave', 'empresa_tipo', 'nombre_norm']).to_pandas() emp = emp[(emp['empresa_tipo'] == 'empresa') & emp['empresa_clave'].str.match(CIF.pattern) & ~emp['empresa_clave'].map(forma_persona)] nifs = set(emp['empresa_clave']) dic = pq.read_table(I + 'diccionario_nombres.parquet', columns=['nombre_norm', 'nif_inferido']).to_pandas().dropna() u = emp.dropna(subset=['nombre_norm']).groupby('nombre_norm')['empresa_clave'].agg(lambda s: sorted(set(s))) u = {k: v[0] for k, v in u.items() if len(v) == 1} cand0 = {} for k, n in list(zip(dic['nombre_norm'], dic['nif_inferido'])) + list(u.items()): cand0.setdefault(k, set()).add(n) cand = {k: next(iter(v)) for k, v in cand0.items() if len(v) == 1 and next(iter(v)) in nifs and len(k) >= 6 and re.search('[A-Z]{4,}', k)} cas0 = [] for lei, e in g.items(): if not e['es']: continue if e['nif']: if e['nif'] in nifs: cas0.append((e['nif'], lei, 'nif')) continue a, b = cand.get(e['k1']) if e['k1'] else None, cand.get(e['k2']) if e['k2'] else None if (a or b) and (a is None or b is None or a == b): cas0.append((a or b, lei, 'nombre')) best = {} for nif, lei, via in cas0: key = ((0 if g[lei]['issued'] else 1) * 10 + (0 if via == 'nif' else 1), lei) if nif not in best or key < best[nif][0]: best[nif] = (key, lei, via) cas = {nif: (lei, via) for nif, (k, lei, via) in best.items()} # relaciones ult, dir_ = {}, {} with gzip.open('/gl/raw/rr_es.csv.gz', 'rt', encoding='utf-8', newline='') as fh: for r in csv.DictReader(fh): if r['Relationship.RelationshipStatus'] != 'ACTIVE' or r['Relationship.EndNode.NodeIDType'] != 'LEI' or r['Registration.RegistrationStatus'] not in ('PUBLISHED', 'LAPSED'): continue d = ult if r['Relationship.RelationshipType'] == 'IS_ULTIMATELY_CONSOLIDATED_BY' else dir_ if r['Relationship.RelationshipType'] == 'IS_DIRECTLY_CONSOLIDATED_BY' else None if d is None: continue h, p = r['Relationship.StartNode.NodeID'], r['Relationship.EndNode.NodeID'] if h not in d or p < d[h]: d[h] = p mu = {} for nif, (lei, via) in cas.items(): d = dir_.get(lei) m = ult.get(lei) or (ult.get(d) if d else None) or d mu[nif] = (lei, via, d, m) matrices = {m for (_, _, _, m) in mu.values() if m} grupo = {} for nif, (lei, via, d, m) in mu.items(): gr = m or (lei if lei in matrices else None) if gr: grupo[nif] = (lei, via, d, m, gr) miembros = {} for nif, t in grupo.items(): miembros.setdefault(t[4], set()).add(nif) a = pq.read_table('/dc/interno/grp_empresas.parquet', columns=['nif', 'lei', 'matriz_directa', 'matriz_ultima', 'grupo']).to_pandas() nn = lambda v: None if v is None or (isinstance(v, float) and v != v) else v a1 = {r.nif: (nn(r.lei), nn(r.matriz_directa), nn(r.matriz_ultima), nn(r.grupo)) for r in a.itertuples()} dif = [n for n in set(a1) | set(grupo) if n not in a1 or n not in grupo or a1[n] != (grupo[n][0], grupo[n][2], grupo[n][3], grupo[n][4])] m1 = a.groupby('grupo')['nif'].agg(lambda s: frozenset(s)).to_dict() dif_m = [gr for gr in set(m1) | set(miembros) if m1.get(gr) != frozenset(miembros.get(gr, set()))] R = {'casados_impl2': len(cas), 'con_grupo_impl2': len(grupo), 'con_grupo_impl1': len(a1), 'diferencias_empresa': len(dif), 'diferencias_lista_de_miembros': len(dif_m), 'ejemplos': [(n, a1.get(n), grupo.get(n)) for n in sorted(dif)[:10]]} json.dump(R, open('/dc/salidas/grp_impl2.json', 'w'), indent=1, default=str); print(json.dumps(R, indent=1, default=str))