"""LOB1 · 10 · base: registros y agendas normalizados, casado registro→empresa (NIF y nombre, DEFINICIONES §2), lotes de CT y MD con administración X (§3) y mapa departamento→órganos de GENCAT (§5). Escribe /w/tmp/lob1.duckdb (nombres solo ahí y en interno/).""" import sys, os, csv, re; sys.path.insert(0, '/w/py') from comun import * csv.field_size_limit(10**9) if os.path.exists('/w/tmp/lob1.duckdb'): os.remove('/w/tmp/lob1.duckdb') con = abrir(); x = con.execute from etl.v2.identidad import MACROS_IDENTIDAD x(MACROS_IDENTIDAD) D = '/w/interno/datos/' def carga(nombre, filas, cols): p = f'/w/tmp/{nombre}.csv' with open(p, 'w', newline='', encoding='utf-8') as fh: w = csv.writer(fh); w.writerow(cols); w.writerows(filas) x(f"CREATE TABLE {nombre} AS SELECT * FROM read_csv('{p}', header=true, all_varchar=true, quote='\"', escape='\"', max_line_size=20000000)"); os.remove(p) # --- 1. registros f = [] for r in csv.DictReader(open(D + 'cat/registre_grups_interes_gwpn-de62.csv', encoding='utf-8')): f.append(('CAT-R', r['identificador'], r['nom'], r['rao_social'], None, r['data_alta'], r['tipus_grup'], r['categoria_registre'])) for r in csv.DictReader(open(D + 'mad/registro_lobbies_ayto_madrid.utf8.csv', encoding='utf-8'), delimiter=';'): f.append(('MAD-R', r['Identificador(NIF)'], r['Nombre o razón social'], None, r['Identificador(NIF)'], r['Fecha de inscripción'], r['Estado'], r['Categoría'])) for r in csv.DictReader(open(D + 'cam/registro_transparencia_extendido.csv', encoding='utf-8-sig'), delimiter=';'): f.append(('CAM-R', r['numero_registro'], r['denominacion'], None, r['nif'], r['fecha_inscripcion'], r['estado_inscripcion'], r['categoria'])) carga('reg0', f, ['registro', 'gid', 'nom', 'nom2', 'nif_decl', 'fecha_txt', 'estado', 'categoria']) x("""CREATE TABLE reg AS SELECT registro, gid, nom, nom2, upper(regexp_replace(coalesce(nif_decl,''), '[^A-Za-z0-9]', '', 'g')) nif_decl, estado, categoria, coalesce(try_strptime(fecha_txt, '%d/%m/%Y'), try_strptime(left(fecha_txt,10), '%Y-%m-%d'))::DATE fecha FROM reg0""") x("DROP TABLE reg0") log('reg', x("SELECT registro, count(*), count(DISTINCT gid), count(fecha), min(fecha), max(fecha), count(*) FILTER (WHERE nif_decl<>'') FROM reg GROUP BY 1").fetchall()) # --- 2. casado. Candidatos por nombre (§2.2) x(f"""CREATE TABLE emp AS SELECT e.empresa_clave, e.nombre_norm FROM '{I}empresas.parquet' e WHERE e.empresa_tipo = 'empresa' AND NOT forma_persona(e.empresa_clave) AND left(e.empresa_clave,1) NOT IN ('P','S')""") x(f"""CREATE TABLE cand AS WITH d AS (SELECT nombre_norm k, nif_inferido nif FROM '{I}diccionario_nombres.parquet' WHERE nif_inferido IS NOT NULL), u AS (SELECT nombre_norm k, min(empresa_clave) nif FROM emp WHERE nombre_norm IS NOT NULL GROUP BY 1 HAVING count(DISTINCT empresa_clave) = 1), a AS (SELECT * FROM d UNION SELECT * FROM u) SELECT k, min(nif) nif FROM a WHERE nif IN (SELECT empresa_clave FROM emp) AND length(k) >= 6 AND regexp_matches(k, '[A-Z]{{4,}}') GROUP BY 1 HAVING count(DISTINCT nif) = 1""") x("""CREATE TABLE reg_nom AS SELECT r.registro, r.gid, c1.nif n1, c2.nif n2 FROM reg r LEFT JOIN cand c1 ON c1.k = norm_empresa(r.nom) LEFT JOIN cand c2 ON c2.k = norm_empresa(r.nom2)""") x("""CREATE TABLE reg_nif AS SELECT r.registro, r.gid, r.nif_decl nif FROM reg r WHERE r.nif_decl IN (SELECT empresa_clave FROM emp)""") x("""CREATE TABLE casado AS SELECT registro, gid, 'nif' via, nif FROM reg_nif UNION ALL SELECT registro, gid, 'nombre' via, coalesce(n1, n2) nif FROM reg_nom WHERE coalesce(n1, n2) IS NOT NULL AND (n1 IS NULL OR n2 IS NULL OR n1 = n2)""") log('casado', x("SELECT registro, via, count(*), count(DISTINCT nif) FROM casado GROUP BY 1,2 ORDER BY 1,2").fetchall()) # --- 3. lotes de CT y MD (§3) x("CREATE TEMP TABLE vinc AS SELECT source, id_canonical, bool_and(cuenta) cuenta FROM '/d/unified_20261018.vinculos/filas.parquet' GROUP BY 1, 2") x("""CREATE TEMP TABLE l AS SELECT r.source, r.id_canonical, r.organo_nif, r.organo_nombre, r.dir3, r.ccaa, r.cpv_division cpv2, r.anio_ref anio, r.fecha_adj, r.imp_adj_eur_comparable imp, r.adjudicatario_nif_raw, r.adjudicatario_nif, r.adjudicatario_nombre, coalesce(r.es_menor, FALSE) OR coalesce(r.es_menor_dudoso, FALSE) menor FROM read_parquet('/d/unified_20261018/*/*/*.parquet', hive_partitioning=1, union_by_name=1) r LEFT JOIN vinc v ON v.source = r.source AND v.id_canonical = r.id_canonical WHERE r.unidad = 'adjudicacion' AND r.copia_de IS NULL AND r.correccion_imp_adj IS DISTINCT FROM 'copia_otra_fuente' AND coalesce(v.cuenta, TRUE) AND NOT coalesce(r.anulado_en_origen, FALSE) AND r.anio_ref BETWEEN 2017 AND 2025 AND r.ccaa IN ('CT', 'MD')""") log('l', x("SELECT ccaa, count(*) FROM l GROUP BY 1").fetchall()) x(f"""CREATE TEMP TABLE of AS SELECT f.organo_nif, f.organo_nombre, f.dir3, any_value(f.organo_clave) organo_clave, any_value(e.administracion) adm, any_value(t.tipo) tipo, any_value(coalesce(f.dir3, f.dir3_propagado)) d3, any_value(coalesce(f.organo_nif, f.organo_nif_inferido)) onif FROM '{I}organos_filas.parquet' f LEFT JOIN '{I}organos_tipo.parquet' t USING (organo_clave) LEFT JOIN '{I}entidades.parquet' e ON e.entidad_clave = f.entidad_clave GROUP BY 1, 2, 3""") x(f"""CREATE TABLE lot AS SELECT l.source, l.id_canonical, o.organo_clave, l.organo_nombre, l.ccaa, l.cpv2, l.anio, l.fecha_adj, l.imp, l.menor, a.empresa_clave emp, CASE WHEN l.ccaa = 'CT' AND (o.adm = 'Generalitat de Catalunya' OR l.source = 'CATALUNYA_GENERALITAT' OR coalesce(o.d3,'') LIKE 'A09%' OR coalesce(o.onif,'') LIKE 'S08%') AND coalesce(o.tipo,'') NOT IN ('ayuntamiento','local_supramunicipal','age') AND NOT (norm_nombre(l.organo_nombre) LIKE 'UNIVERSITAT%') THEN 'GENCAT' WHEN l.ccaa = 'MD' AND (l.source = 'MADRID_AYTO' OR coalesce(o.d3,'') LIKE 'L01280796%' OR coalesce(o.onif,'') LIKE 'P28079%') AND coalesce(o.tipo,'') <> 'age' THEN 'AYTO_MAD' WHEN l.ccaa = 'MD' AND (l.source = 'MADRID_CAM' OR o.adm = 'Comunidad de Madrid' OR coalesce(o.d3,'') LIKE 'A13%' OR coalesce(o.onif,'') LIKE 'S78%') AND (o.adm IS NULL OR o.adm = 'Comunidad de Madrid') AND coalesce(o.tipo,'') NOT IN ('ayuntamiento','local_supramunicipal','age') THEN 'CAM' END x FROM l LEFT JOIN of o ON o.organo_nif IS NOT DISTINCT FROM l.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM l.organo_nombre AND o.dir3 IS NOT DISTINCT FROM l.dir3 JOIN (SELECT adjudicatario_nif_raw, adjudicatario_nif, adjudicatario_nombre, any_value(empresa_clave) empresa_clave FROM read_parquet('{I}adjudicatarios_filas.parquet') GROUP BY ALL) a ON a.adjudicatario_nif_raw IS NOT DISTINCT FROM l.adjudicatario_nif_raw AND a.adjudicatario_nif IS NOT DISTINCT FROM l.adjudicatario_nif AND a.adjudicatario_nombre IS NOT DISTINCT FROM l.adjudicatario_nombre WHERE a.empresa_clave IN (SELECT empresa_clave FROM emp)""") log('lot', x("SELECT ccaa, x, menor, count(*), count(DISTINCT emp), count(fecha_adj) FROM lot GROUP BY ALL ORDER BY ALL").fetchall()) # --- 4. agendas (sin nombres de personas) f = [] for r in csv.DictReader(open(D + 'cat/agenda_alts_carrecs_hd8k-y28e.csv', encoding='utf-8')): f.append((r['Id'], r['Departament'], r['Data'], r["Grup d'interès"], r['Codi registre grup interès'], r['Activitat'], r['Tipologia'])) carga('cag0', f, ['id', 'dep', 'dt', 'grup', 'codi', 'act', 'tip']) x("CREATE TABLE cag AS SELECT id, dep, try_strptime(dt, '%d/%m/%Y')::DATE d, grup, nullif(trim(codi),'') codi, act, tip FROM cag0"); x("DROP TABLE cag0") f = [] for r in csv.DictReader(open(D + 'mad/agendas_ayto_madrid.csv', encoding='utf-8-sig'), delimiter=';'): if r['Reunión_con_actividad_de_lobby'] == 'Sí': f.append((r['Fecha_del_evento'], r['Unidad'], r['Nombre_del_lobby'], r['Url_del_evento'])) carga('mag0', f, ['dt', 'unidad', 'lobby', 'url']) x("CREATE TABLE mag AS SELECT try_strptime(left(dt,10), '%Y-%m-%d')::DATE d, unidad, lobby, url FROM mag0"); x("DROP TABLE mag0") log('agendas', x("SELECT count(*), count(d), min(d), max(d) FROM cag").fetchall(), x("SELECT count(*), count(d), min(d), max(d) FROM mag").fetchall()) # empresa de cada reunión x("""CREATE TABLE cag_emp AS SELECT a.*, coalesce(cr.nif, cn.nif) nif, CASE WHEN cr.nif IS NOT NULL THEN 'codi' WHEN cn.nif IS NOT NULL THEN 'nombre' END via FROM cag a LEFT JOIN (SELECT gid, min(nif) nif FROM casado WHERE registro='CAT-R' GROUP BY 1 HAVING count(DISTINCT nif)=1) cr ON cr.gid = a.codi LEFT JOIN cand cn ON a.codi IS NULL AND cn.k = norm_empresa(a.grup)""") x("""CREATE TABLE mag_emp AS SELECT a.*, cn.nif FROM mag a LEFT JOIN cand cn ON cn.k = norm_empresa(a.lobby)""") log('agenda casada', x("SELECT via, count(*), count(DISTINCT nif) FROM cag_emp GROUP BY 1").fetchall(), x("SELECT count(nif), count(DISTINCT nif), count(*) FROM mag_emp").fetchall()) # --- 5. departamento → órganos de GENCAT x("""CREATE MACRO norm_dep(s) AS trim(regexp_replace(' ' || coalesce(norm_nombre(s),'') || ' ', ' ((DE|D|DEL|DELS|LA|LES|EL|I|A|AL) )+', ' ', 'g'))""") x("""CREATE TABLE dep AS SELECT DISTINCT dep, norm_dep(dep) dn FROM cag WHERE dep IS NOT NULL""") # un órgano tiene varios nombres en organos_filas: casa si CUALQUIERA de ellos empieza por el departamento (regla determinista) x(f"""CREATE TABLE orgx AS SELECT DISTINCT f.organo_clave, norm_dep(f.organo_nombre) onn FROM '{I}organos_filas.parquet' f WHERE f.organo_clave IN (SELECT DISTINCT organo_clave FROM lot WHERE x = 'GENCAT')""") x("""CREATE TABLE dep_org AS SELECT DISTINCT d.dep, d.dn, o.organo_clave FROM dep d JOIN orgx o ON o.onn = d.dn OR o.onn LIKE d.dn || ' %'""") log('dep_org', x("SELECT count(DISTINCT dep), count(DISTINCT organo_clave) FROM dep_org").fetchall(), 'de', x("SELECT count(*) FROM dep").fetchone()) log('reuniones con dep mapeado', x("SELECT count(*) FILTER (WHERE dep IN (SELECT dep FROM dep_org)), count(*) FROM cag").fetchone()) x("CHECKPOINT"); log('fin')