"""INC2 · 01 · universo (sin cruces con electos): adjudicaciones de órganos municipales (regla de INC1 §4 + enmienda 1) a adjudicatarios persona física; inventario por fuente, año y vía; formas del nombre. Base en tmp/inc2.duckdb (nombres en claro solo aquí).""" import sys, os, json; sys.path.insert(0, '/inc1/py') import duckdb os.makedirs('/w/tmp/duck', exist_ok=True) if os.path.exists('/w/tmp/inc2.duckdb'): os.remove('/w/tmp/inc2.duckdb') con = duckdb.connect('/w/tmp/inc2.duckdb'); x = con.execute x("SET threads=4; SET memory_limit='6GB'; SET temp_directory='/w/tmp/duck'; SET max_temp_directory_size='5GB'; SET preserve_insertion_order=false") I = '/d/unified_20261018.identidad/'; R = {} W0, W1 = '2019-06-15', '2023-06-16' x("CREATE TABLE elm AS SELECT DISTINCT muni FROM '/inc1/interno/10_electos.parquet'") x("CREATE TABLE pob AS SELECT ine muni, TRY_CAST(poblacion_2019 AS INTEGER) p, municipio FROM read_csv('/ad/padron_municipios_2019_2025.csv', header=true, all_varchar=true)") x("CREATE TEMP TABLE vinc AS SELECT source, id_canonical, bool_and(cuenta) cuenta, any_value(grupo_contrato) grupo_contrato FROM '/d/unified_20261018.vinculos/filas.parquet' GROUP BY 1, 2") x(f"""CREATE TABLE ofm AS SELECT f.organo_nif, f.organo_nombre, f.dir3, any_value(f.organo_clave) organo_clave, any_value(f.entidad_clave) entidad_clave, any_value(t.tipo) tipo, any_value(CASE WHEN regexp_matches(coalesce(f.dir3, f.dir3_propagado, ''), '^L01[0-9]{{5}}') THEN substr(coalesce(f.dir3, f.dir3_propagado), 4, 5) WHEN regexp_matches(coalesce(f.organo_nif, f.organo_nif_inferido, ''), '^P[0-9]{{7}}[A-Z]$') AND substr(coalesce(f.organo_nif, f.organo_nif_inferido), 7, 2) = '00' AND t.tipo IN ('ayuntamiento', 'empresa_fundacion_consorcio') THEN substr(coalesce(f.organo_nif, f.organo_nif_inferido), 2, 5) END) muni_ayto, any_value(CASE WHEN regexp_matches(coalesce(f.organo_nif, f.organo_nif_inferido, ''), '^P[0-9]{{7}}[A-Z]$') AND substr(coalesce(f.organo_nif, f.organo_nif_inferido), 7, 2) <> '00' AND t.tipo IN ('ayuntamiento', 'empresa_fundacion_consorcio') THEN substr(coalesce(f.organo_nif, f.organo_nif_inferido), 2, 5) END) muni_ente FROM '{I}organos_filas.parquet' f LEFT JOIN '{I}organos_tipo.parquet' t USING (organo_clave) GROUP BY 1, 2, 3""") x("""CREATE TABLE ofm2 AS SELECT *, coalesce(muni_ayto, muni_ente) muni, CASE WHEN muni_ayto IS NOT NULL THEN 'ayuntamiento' ELSE 'ente' END organo_kind FROM ofm WHERE coalesce(muni_ayto, muni_ente) IN (SELECT muni FROM elm UNION SELECT muni FROM pob)""") R['organos_municipales'] = x("SELECT organo_kind, count(*), count(DISTINCT muni) FROM ofm2 GROUP BY 1").fetchall() x("""CREATE TABLE af AS SELECT adjudicatario_nif_raw, adjudicatario_nif, adjudicatario_nombre, any_value(empresa_clave) empresa_clave, bool_or(coalesce(persona_fisica, FALSE)) pf, any_value(clase_nif) clase_nif, any_value(tipo_nif) tipo_nif FROM read_parquet('/d/unified_20261018.identidad/adjudicatarios_filas.parquet') GROUP BY 1, 2, 3""") # todas las filas municipales del universo NOT es_copia, con marca pf x(f"""CREATE TABLE l AS SELECT r.source, r.id_canonical, o.muni, o.organo_kind, o.organo_clave, o.entidad_clave, r.cpv_division cpv2, r.anio_ref anio, r.adjudicatario_nif_raw, r.adjudicatario_nif, r.adjudicatario_nombre, r.adjudicatario_nombre_raw, coalesce(r.es_menor, FALSE) OR coalesce(r.es_menor_dudoso, FALSE) menor, TRY_CAST(r.imp_adj_eur_comparable AS DOUBLE) imp, coalesce(TRY_CAST(r.fecha_adj AS DATE), TRY_CAST(r.fecha_formalizacion AS DATE), TRY_CAST(r.fecha_pub AS DATE)) f, v.grupo_contrato FROM read_parquet('/d/unified_20261018/*/*/*.parquet', hive_partitioning=1, union_by_name=1) r JOIN ofm2 o ON o.organo_nif IS NOT DISTINCT FROM r.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM r.organo_nombre AND o.dir3 IS NOT DISTINCT FROM r.dir3 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 2018 AND 2024""") x("DROP TABLE vinc"); x("CHECKPOINT") x("""CREATE TABLE lf AS SELECT l.*, m.fm, a.pf, a.clase_nif, a.tipo_nif, a.empresa_clave FROM l JOIN (SELECT source, id_canonical, min(f) fm FROM l GROUP BY 1, 2) m USING (source, id_canonical) LEFT JOIN af 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""") x("DROP TABLE l"); x("CHECKPOINT") x(f"ALTER TABLE lf ADD COLUMN mandato BOOLEAN; UPDATE lf SET mandato = fm BETWEEN DATE '{W0}' AND DATE '{W1}'") x("""ALTER TABLE lf ADD COLUMN forma_nif VARCHAR; UPDATE lf SET forma_nif = CASE WHEN regexp_matches(coalesce(adjudicatario_nif,''), '^[0-9]{8}[A-Z]$') THEN 'DNI' WHEN regexp_matches(coalesce(adjudicatario_nif,''), '^[XYZ][0-9]{7}[A-Z]$') THEN 'NIE' WHEN regexp_matches(coalesce(adjudicatario_nif_raw,''), '\\*') THEN 'enmascarado' WHEN adjudicatario_nif IS NULL AND adjudicatario_nif_raw IS NULL THEN 'sin_nif' ELSE 'otro' END""") x("""ALTER TABLE lf ADD COLUMN forma_nombre VARCHAR; UPDATE lf SET forma_nombre = CASE WHEN adjudicatario_nombre IS NULL OR trim(adjudicatario_nombre) = '' THEN 'vacio' WHEN regexp_matches(adjudicatario_nombre, '\\*') THEN 'asteriscos' WHEN regexp_matches(upper(adjudicatario_nombre), 'PERSONA F[IÍ]SICA|PARTICULAR|ANONIMIZ|DATOS PROTEGIDOS|PROTECCI[OÓ]N DE DATOS|LOPD|CONFIDENCIAL|PERSONA NATURAL') THEN 'rotulo_anonimo' WHEN regexp_matches(trim(adjudicatario_nombre), '^([A-ZÁÉÍÓÚÑa-z]\\.? ?){1,4}$') THEN 'iniciales' WHEN regexp_matches(adjudicatario_nombre, '(^|\\s)[A-ZÁÉÍÓÚÑ]\\.(\\s|$)') THEN 'con_inicial' WHEN len(string_split(trim(regexp_replace(replace(adjudicatario_nombre, ',', ' '), '\\s+', ' ', 'g')), ' ')) < 3 THEN 'menos_de_3_palabras' WHEN regexp_matches(adjudicatario_nombre, ',') THEN 'completo_con_coma' ELSE 'completo' END""") x("CHECKPOINT") M = "mandato AND coalesce(pf, FALSE)" R['universo_municipal'] = x("SELECT count(*), count(DISTINCT (source, id_canonical)), count(*) FILTER (WHERE mandato), count(DISTINCT (source,id_canonical)) FILTER (WHERE mandato), count(DISTINCT muni) FILTER (WHERE mandato) FROM lf").fetchone() R['pf_mandato'] = x(f"SELECT count(*), count(DISTINCT (source, id_canonical)), count(DISTINCT muni), round(sum(imp)) FROM lf WHERE {M}").fetchone() R['pf_por_fuente'] = x(f"""SELECT source, count(DISTINCT (source,id_canonical)) adjs, count(DISTINCT muni), count(*) FILTER (WHERE forma_nombre IN ('completo','completo_con_coma')) con_nombre_completo, round(100.0 * count(*) FILTER (WHERE forma_nombre IN ('completo','completo_con_coma')) / count(*), 1) pct_nombre, round(100.0*count(*) FILTER (WHERE menor)/count(*),1) pct_menor, (SELECT count(DISTINCT (source,id_canonical)) FROM lf l2 WHERE l2.source = lf.source AND l2.mandato) adjs_total_fuente FROM lf WHERE {M} GROUP BY source ORDER BY 2 DESC""").fetchall() R['pf_por_anio'] = x(f"SELECT year(fm), count(DISTINCT (source,id_canonical)), round(sum(imp)) FROM lf WHERE {M} GROUP BY 1 ORDER BY 1").fetchall() R['pf_por_via'] = x(f"SELECT menor, count(DISTINCT (source,id_canonical)), round(sum(imp)) FROM lf WHERE {M} GROUP BY 1").fetchall() R['pf_forma_nif'] = x(f"SELECT forma_nif, count(*) FROM lf WHERE {M} GROUP BY 1 ORDER BY 2 DESC").fetchall() R['pf_forma_nombre'] = x(f"SELECT forma_nombre, count(*) FROM lf WHERE {M} GROUP BY 1 ORDER BY 2 DESC").fetchall() R['pf_forma_nombre_por_fuente'] = x(f"SELECT source, forma_nombre, count(*) FROM lf WHERE {M} GROUP BY ALL ORDER BY 1, 3 DESC").fetchall() R['no_pf_sin_nif_mandato'] = x("SELECT forma_nombre, count(*) FROM lf WHERE mandato AND NOT coalesce(pf, FALSE) AND forma_nif = 'sin_nif' GROUP BY 1 ORDER BY 2 DESC").fetchall() R['pf_organo_kind'] = x(f"SELECT organo_kind, count(DISTINCT (source,id_canonical)) FROM lf WHERE {M} GROUP BY 1").fetchall() R['pf_con_grupo_otra_fuente'] = x(f"SELECT count(DISTINCT (source,id_canonical)) FROM lf WHERE {M} AND grupo_contrato IS NOT NULL").fetchone() json.dump(R, open('/w/salidas/01_inventario_municipal.json', 'w'), indent=1, default=str, ensure_ascii=False) for k, v in R.items(): print(k, v)