"""CRUCE-CARGOS · 60 · segunda implementación (pre-registro §10): pandas + pyarrow, sin DuckDB, imagen licitaciones-scrapers:vps. Troceado propio de los .DAT, variantes, hash, tramos (arreglo FAM-2b), clases, universo y pares reescritos. El mapa de municipios es la tabla validada de FAM-2. Salidas: interno/60_impl2_*.parquet y salidas/60_impl2.json (solo recuentos).""" import sys, re, json, glob, hashlib, unicodedata, time, gc, collections, zipfile, os, random sys.path.insert(0, '/e3') import numpy as np, pandas as pd, pyarrow.parquet as pq, pyarrow as pa from em3_impl2 import nn, borme_norm_de_nn, solo_az09 os.umask(0o077); T0_ = time.time() def log(*a): print(f"[{time.time()-T0_:7.1f}s]", *a, flush=True) R = {} COH = {'2019': dict(el='2019-05-26', T0='2019-06-15', A0='2016-06-15', D1='2022-06-15', prev='2015', W0='2019-06-15', W1='2023-06-16', pob='poblacion_2019'), '2023': dict(el='2023-05-28', T0='2023-06-17', A0='2020-06-17', D1='2026-06-17', prev='2019', W0='2023-06-17', W1='2026-06-16', pob='poblacion_2023')} def quita(s, conserva=''): return ''.join(ch if ch in conserva else ''.join(c for c in unicodedata.normalize('NFKD', ch) if unicodedata.category(c) != 'Mn') for ch in s) # NFKD, como INC1; con NFD salen 58 personas de 38.000 distintas en 2019 (ª, º) def vars_(a1, a2, n): base = ' '.join(f'{a1} {a2} {n}'.split()).upper(); out = set() for b in (base, quita(base, 'ÑÇ'), quita(base)): out.add(b); out.add(' '.join(b.replace('-', ' ').split())) return out def hh(s): return hashlib.sha256(('‹sal tapada›:' + s.strip().upper()).encode('utf-8')).hexdigest()[:16] def lee(anio, nn_): aa = str(anio)[2:]; f = f'{nn_}04{aa}05.DAT' if anio == 2023: b = zipfile.ZipFile('/el/04202305_MUNI.zip').read(f) elif anio == 2019: b = open('/fam/interno/datos/infoelectoral/2019/' + f, 'rb').read() else: b = open('/w/interno/datos/infoelectoral/2015/' + f, 'rb').read() txt = b.decode('cp1252'); ls = txt.split('\r\n') if '\r\n' in txt else txt.split('\n') if ls and ls[-1] == '': ls = ls[:-1] return f, ls def k_(s): return re.sub('[^A-Z]', '', quita(str(s).upper())) PERS = {} for anio in (2015, 2019, 2023): filas = [] for nn_, pm, el, lc in (('04', (9, 11, 12, 15), 119, (15, 21)), ('12', (9, 11, 11, 14), 122, (14, 20))): f, ls = lee(anio, nn_) for i, l in enumerate(ls): mu = l[pm[0]:pm[1]] + l[pm[2]:pm[3]]; n_, a1, a2 = l[25:50].strip(), l[50:75].strip(), l[75:100].strip() nf = hashlib.md5(f'{k_(n_)}|{k_(a1)}|{k_(a2)}'.encode()).hexdigest(); eid = hashlib.md5(f'{f}|{i}|{mu}|{nf}'.encode()).hexdigest()[:16] filas.append((eid, mu, a1, a2, n_, l[lc[0]:lc[1]], l[el] == 'S')) d = pd.DataFrame(filas, columns=['eid', 'muni', 'a1', 'a2', 'n', 'cand', 'el']); PERS[anio] = d; log(anio, len(d), d.el.sum()) def hashes(d): ok = d[(d.a1 != '') & (d.a2 != '') & (d.n != '')] return {e: [hh(v) for v in vars_(a, b, c)] for e, a, b, c in zip(ok.eid, ok.a1, ok.a2, ok.n)} HS = {a: hashes(PERS[a]) for a in PERS} for a, d in PERS.items(): d['dos'] = d.eid.isin(HS[a].keys()) # marcas def hset(d, a, sel): out = set() for e, m in zip(d.eid[sel], d.muni[sel]): for h in HS[a].get(e, ()): out.add((m, h)) return out for a, p in ((2019, 2015), (2023, 2019)): hp = hset(PERS[p], p, PERS[p].el); ha = hset(PERS[a], a, PERS[a].el) PERS[a]['antes'] = [any((m, h) in hp for h in HS[a].get(e, ())) for e, m in zip(PERS[a].eid, PERS[a].muni)] PERS[p]['repite'] = [any((m, h) in ha for h in HS[p].get(e, ())) for e, m in zip(PERS[p].eid, PERS[p].muni)] def grupos(m): a = int(m); p = int(COH[m]['prev']); d = PERS[a]; dp = PERS[p] return {'T': d[d.el & d.dos & ~d.antes], 'H': d[d.el & d.dos & d.antes], 'N': d[~d.el & d.dos & ~d.antes], 'S': dp[dp.el & dp.dos & ~dp.repite]} GR = {m: grupos(m) for m in COH} for m in COH: R[f'grupos_{m}'] = {g: len(v) for g, v in GR[m].items()}; log(R[f'grupos_{m}']) # BORME HALL = set() for a in HS: for e, l in HS[a].items(): HALL.update(l) log('hashes', len(HALL)) t = pq.read_table('/b/pub_borme3/borme_cargos_pub.parquet', columns=['fecha_borme', 'pdf_filename', 'num_entrada', 'empresa_norm', 'tipo_acto', 'cargo', 'persona_hash']) mask = pa.compute.is_in(t['persona_hash'], value_set=pa.array(sorted(HALL))); t = t.filter(mask); cg = t.to_pandas(); del t, mask; gc.collect(); log('cargos', len(cg)) e_t = pq.read_table('/b/pub_borme3/borme_empresas_pub.parquet', columns=['fecha_borme', 'pdf_filename', 'num_entrada', 'empresa_norm', 'empresa', 'domicilio']).to_pandas(); log('empresas', len(e_t)) e_t['num_entrada'] = e_t.num_entrada.astype(str); cg['num_entrada'] = cg.num_entrada.astype(str) pa_ = e_t.groupby('empresa_norm', sort=False).fecha_borme.min() ac = e_t.drop_duplicates(['pdf_filename', 'num_entrada', 'empresa_norm'])[['pdf_filename', 'num_entrada', 'empresa_norm', 'empresa']]; del e_t; gc.collect() cg = cg.merge(ac, on=['pdf_filename', 'num_entrada', 'empresa_norm'], how='left'); del ac; gc.collect() RMF = re.compile(r"(?i)\s*\(\s*R\s*\.?\s*M\s*\.?(\s[^()]*)?\)(\s*\.?\s*(SOCIEDAD|SOCIETAT) UNIPERSONAL)?\s*\.?\s*$") BNC = {} def bnde(e): if e not in BNC: BNC[e] = borme_norm_de_nn(nn(RMF.sub(lambda m_: m_.group(2) or '', e, count=1))) or '' return BNC[e] cg['bn'] = [bnde(e) if isinstance(e, str) and e else bnde(en) for e, en in zip(cg.empresa, cg.empresa_norm)] C_ = open('/fam/py/comun.py').read() ADMF = set(C_.split('ADM = """')[1].split('"""')[0].split()); APO = set(C_.split('APO = """')[1].split('"""')[0].split()); SOC_ = set(C_.split('SOC = "')[1].split('"')[0].split()) | {'SOCIONICO'} ADMIN = set('ADMUNICO ADMINUNICO ADMSOLID ADMSOLIDAR ADMINSOLID ADMMANCOM ADMINMANC ADMCONJUNTO ADMSUPL ADMSUPLENTE ADMMANSUPL ADMSOLSUPL ADMSUPMAN ADMINISTR ADMINISTRAD'.split()); DIR = set('GERENTE GERENTEUNI DGERENTE DTORGERENTE DIRGENERAL DTORGENERAL DIRECTORGEN'.split()) def clase(c): c = solo_az09(c) or '' if c in ADMIN: return 1 if c in ADMF and c not in DIR: return 2 if c in DIR: return 3 if c in APO: return 4 if c in SOC_: return 5 return None cg['code'] = cg.cargo.map(lambda c: solo_az09(c) or ''); cg['rk'] = cg.cargo.map(clase) cgm = cg.groupby('bn').fecha_borme.min() cg = cg[cg.rk.notna() & (cg.bn != '')].copy(); cg['d'] = pd.to_datetime(cg.fecha_borme); cg['op'] = cg.tipo_acto.isin(['nombramiento', 'reeleccion']); cg['nent'] = pd.to_numeric(cg.num_entrada, errors='coerce') log('cargos con clase', len(cg)) # persona: (año, eid) → sólo las usadas en grupos; h → eids H2E = collections.defaultdict(list) USA = set() for m in COH: for g, d in GR[m].items(): USA.update(d.eid) for a in HS: for e, l in HS[a].items(): if e in USA or (a in (2019, 2023)): if e in USA: for h in set(l): H2E[h].append(e) rows = [] for h, b, c, r, d, ne, o in zip(cg.persona_hash, cg.bn, cg.code, cg.rk, cg.d, cg.nent, cg.op): for e in H2E.get(h, ()): rows.append((e, b, c, int(r), d, ne, o)) ev = pd.DataFrame(rows, columns=['eid', 'bn', 'code', 'rk', 'd', 'ne', 'op']); del rows, cg; gc.collect() ev = ev.groupby(['bn', 'eid', 'code', 'rk', 'd', 'ne'], as_index=False, dropna=False).op.max(); log('eventos', len(ev)) sat = pd.read_parquet('/sat/empresas.parquet', columns=['empresa_clave', 'via', 'utilizable', 'borme_norm', 'fecha_extincion', 'f_constitucion']) e5 = pd.read_parquet('/et/em5/salidas/17_etiquetas_INTERNO.parquet', columns=['empresa_clave', 'etiqueta']); sat = sat.merge(e5, on='empresa_clave', how='left') FX0 = sat.dropna(subset=['fecha_extincion']).groupby('borme_norm').fecha_extincion.min().map(pd.Timestamp).to_dict() soc = sat[sat.via.isin(['v1_exacto', 'v2_historico']) & sat.utilizable.fillna(False).astype(bool) & (sat.etiqueta.fillna('') != 'R')] INI = pd.Timestamp('2009-01-02'); FIN = pd.Timestamp('2026-10-26') TR = collections.defaultdict(list) ev = ev.sort_values(['bn', 'eid', 'code', 'rk', 'd', 'ne']) for (b, e, c, r), g in ev.groupby(['bn', 'eid', 'code', 'rk'], sort=False): est = None; ini = None for d, o in zip(g.d, g.op): if o and est != 1: ini = d elif not o and est is None: TR[(e, b)].append((r, INI, d, d)) elif not o and est == 1: TR[(e, b)].append((r, ini, d, d)) est = 1 if o else 0 if est == 1: fx = FX0.get(b); TR[(e, b)].append((r, ini, min(fx, FIN) if fx is not None and not pd.isna(fx) else FIN, None)) del ev; gc.collect(); log('tramos', sum(len(v) for v in TR.values())) # Cargos por municipio y sociedad # universo I = '/d/unified_20261103.identidad/' AE = dict(zip(*[pd.read_parquet('/fam2/pubinc/inc3/interno/11_aeat.parquet', columns=c).iloc[:, 0] for c in (['aeat'], ['ine'])])) of = pd.read_parquet(I + 'organos_filas.parquet', columns=['organo_nif', 'organo_nombre', 'dir3', 'dir3_propagado', 'organo_nif_inferido', 'organo_clave']).merge(pd.read_parquet(I + 'organos_tipo.parquet', columns=['organo_clave', 'tipo']), on='organo_clave', how='left') pob = pd.read_csv('/ad/padron_municipios_2019_2025.csv', dtype=str) MV = set(pob.ine) | set(PERS[2015].muni) | set(PERS[2019].muni) | set(PERS[2023].muni) def s_(v): return v if isinstance(v, str) else '' def mde(r): d = s_(r.dir3) or s_(r.dir3_propagado) if re.match(r'^L01\d{5}', d): return d[3:8] n = s_(r.organo_nif) or s_(r.organo_nif_inferido) if re.match(r'^P\d{7}[A-Z]$', n) and r.tipo in ('ayuntamiento', 'empresa_fundacion_consorcio'): return AE.get(n[1:6]) return None of['muni'] = [mde(r) for r in of.itertuples()] of = of.sort_values('muni', na_position='last').drop_duplicates(['organo_nif', 'organo_nombre', 'dir3']) of = of[of.muni.isin(MV)][['organo_nif', 'organo_nombre', 'dir3', 'muni']]; log('organos', len(of)) vin = pd.read_parquet('/d/unified_20261103.vinculos/filas.parquet', columns=['source', 'id_canonical', 'cuenta']); NOC = set(zip(vin.source[vin.cuenta == False], vin.id_canonical[vin.cuenta == False])); del vin; gc.collect() # noqa 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, as_index=False).agg(empresa_clave=('empresa_clave', 'first'), pf=('pf', 'max')) us = pd.read_parquet(I + 'ute_socios.parquet', columns=['ute_clave', 'socio_nif', 'socio_persona_fisica']); us = us[us.socio_nif.notna() & ~us.socio_persona_fisica.fillna(False).astype(bool)].drop_duplicates(['ute_clave', 'socio_nif']) COLS = ['id_canonical', 'organo_nif', 'organo_nombre', 'dir3', 'adjudicatario_nif_raw', 'adjudicatario_nif', 'adjudicatario_nombre', 'unidad', 'es_menor', 'procedimiento', 'tipo_contrato', 'copia_de', 'correccion_imp_adj', 'anulado_en_origen', 'rol_am', 'fecha_adj', 'fecha_formalizacion', 'fecha_pub', 'imp_adj_eur_comparable'] P_ = [] for f in sorted(glob.glob('/d/unified_20261103/*/*/*.parquet')): src = f.split('/')[3].split('=', 1)[1]; sch = pq.read_schema(f).names t = pq.read_table(f, columns=[c for c in COLS if c in sch]).to_pandas() for c in COLS: if c not in t: t[c] = None t = t[(t.unidad == 'adjudicacion') & t.copia_de.isna() & ~t.correccion_imp_adj.fillna('').isin(['copia_otra_fuente', 'repetido', 'inverosimil']) & ~t.anulado_en_origen.fillna(False).astype(bool) & ~t.rol_am.fillna('').isin(['acuerdo', 'sda', 'basado', 'basado_sda'])] if not len(t): continue t = t.merge(of, on=['organo_nif', 'organo_nombre', 'dir3'], how='inner') if not len(t): continue t = t.merge(af, on=['adjudicatario_nif_raw', 'adjudicatario_nif', 'adjudicatario_nombre'], how='left') t['source'] = src t['f'] = pd.to_datetime(t.fecha_adj, errors='coerce').fillna(pd.to_datetime(t.fecha_formalizacion, errors='coerce')).fillna(pd.to_datetime(t.fecha_pub, errors='coerce')) t = t[(t.source + '|' + t.id_canonical).notna()] t = t[['source', 'id_canonical', 'muni', 'empresa_clave', 'pf', 'f', 'es_menor', 'procedimiento', 'tipo_contrato', 'imp_adj_eur_comparable']] P_.append(t) L = pd.concat(P_, ignore_index=True); del P_, af, of; gc.collect(); log('filas municipales', len(L)) L = L[[(s, i) not in NOC for s, i in zip(L.source, L.id_canonical)]]; del NOC; gc.collect(); log('sin vinculos no cuenta', len(L)) L['pf'] = L.pf.fillna(False).astype(bool) T_total = L[(~L.pf) & L.empresa_clave.notna() & L.f.notna() & (L.tipo_contrato.fillna('') != 'Patrimonial')][['source', 'id_canonical', 'muni', 'f']].drop_duplicates() L2 = L[(~L.pf) & L.empresa_clave.notna() & L.f.notna() & (L.tipo_contrato.fillna('') != 'Patrimonial')]; del L; gc.collect() L2 = L2.merge(us[['ute_clave', 'socio_nif']], left_on='empresa_clave', right_on='ute_clave', how='left'); L2['emp'] = L2.socio_nif.fillna(L2.empresa_clave) so = soc[['empresa_clave', 'borme_norm']].drop_duplicates().rename(columns={'empresa_clave': 'emp', 'borme_norm': 'bn'}) L2 = L2.merge(so, on='emp', how='inner') L2['ms'] = (L2.es_menor.fillna(False).astype(bool)); L2['nsp'] = L2.procedimiento == 'Negociado_sin_publicidad' g = L2.groupby(['source', 'id_canonical', 'muni', 'bn'], as_index=False, sort=False).agg(f=('f', 'min'), menor=('ms', 'max'), nsp=('nsp', 'max'), imp=('imp_adj_eur_comparable', 'max')) del L2; gc.collect(); log('ad', len(g), g.bn.nunique(), g.muni.nunique()) g = g[(g.f >= '2013-01-01') & (g.f <= '2026-11-02')] # clases onif = set(pd.read_parquet(I + 'organos_filas.parquet', columns=['organo_nif']).organo_nif.dropna()) | set(pd.read_parquet(I + 'organos_filas.parquet', columns=['organo_nif_inferido']).organo_nif_inferido.dropna()) INV = set(json.load(open('/pubinc_sal/18_invente_nif.json'))) emp_n = pd.read_parquet(I + 'empresas.parquet', columns=['empresa_clave', 'nombre', 'nombre_historico', 'nombre_norm', 'nombre_norm_historico']) def sa(s): return ''.join(c for c in unicodedata.normalize('NFKD', s.upper()) if not unicodedata.combining(c)) _c = open('/inc1/py/comun.py').read(); PUB = re.search(r"PUB_RX = r'(.*)'", _c).group(1); RX = re.compile(PUB) _FIN = re.compile(r'(^| )(BANCO|BANK|BANKIA|BANKINTER|CAIXABANK|CAJA|CAIXA|CAJAMAR|IBERCAJA|UNICAJA|KUTXA|KUTXABANK|ABANCA|CAJASUR|LIBERBANK|CAJA RURAL|SANTANDER CONSUMER|BBVA)( |$)') def tiene(v): if isinstance(v, str): return bool(RX.search(re.sub(r'[^A-Z ]', ' ', sa(v)))) if v is None: return False if hasattr(v, '__iter__'): return any(tiene(z) for z in v) return False enmap = {r.empresa_clave: any(tiene(x) for x in (r.nombre, r.nombre_historico, r.nombre_norm, r.nombre_norm_historico)) for r in emp_n.itertuples()} S0 = soc[['empresa_clave', 'borme_norm']].drop_duplicates() cla = {} for e, b in zip(S0.empresa_clave, S0.borme_norm): d = cla.setdefault(b, dict(i=False, ii=bool(RX.search(re.sub(r'[^A-Z ]', ' ', sa(b)))), iv=False)) if e in onif: d['i'] = True if e in INV: d['iv'] = True if enmap.get(e): d['ii'] = True for b in ['CETURSA SIERRA NEVADA', 'RECOLLIDA DE RESIDUS D OSONA']: if b in cla: cla[b]['i'] = True for b in ['BANCO SANTANDER', 'BANCO BILBAO VIZCAYA ARGENTARIA']: if b in cla: cla[b] = dict(i=False, ii=False, iv=False) def pub0(b): d = cla.get(b); return bool(d and (d['i'] or d['ii'] or d['iv'])) def fin(b): return bool(_FIN.search(' ' + b + ' ')) # existencia EX = collections.defaultdict(lambda: pd.Timestamp('2099-01-01')) for en, d in pa_.items(): b = borme_norm_de_nn(nn(en)) or '' if b and d < EX[b]: EX[b] = d for b, d in cgm.items(): if b and d < EX[b]: EX[b] = d for b, d in zip(sat.borme_norm, pd.to_datetime(sat.f_constitucion, errors='coerce')): if isinstance(b, str) and pd.notna(d) and d < EX[b]: EX[b] = d del pa_, cgm; gc.collect(); log('existencia', len(EX)) def existe(b, E_): fx = FX0.get(b); fx = pd.Timestamp(fx) if fx is not None and not pd.isna(fx) else None return EX[b] <= E_ and (fx is None or fx >= E_) SAL = {} for m, P in COH.items(): E_, T0, A0, D1 = pd.Timestamp(P['el']), pd.Timestamp(P['T0']), pd.Timestamp(P['A0']), pd.Timestamp(P['D1']) w = g[(g.f >= A0) & (g.f < D1)] act = w.assign(pre=(w.f < T0).astype(int), post=(w.f >= T0).astype(int)).groupby(['muni', 'bn'], as_index=False)[['pre', 'post']].sum() ACT = {(a, b): (x, y) for a, b, x, y in zip(act.muni, act.bn, act.pre, act.post)} tt = T_total[(T_total.f >= A0) & (T_total.f < D1)]; tv = tt.assign(a=(tt.f < T0).astype(int), b=(tt.f >= T0).astype(int)).groupby('muni')[['a', 'b']].sum(); EV = set(tv[(tv.a >= 5) & (tv.b >= 5)].index) # personas con cargo pv = []; iii = collections.Counter(); lnk = set(); bys = collections.defaultdict(list) for (e, b), l in TR.items(): bys[e].append((b, l)) for gr, d in GR[m].items(): for e, mu in zip(d.eid, d.muni): for b, l in bys.get(e, ()): lnk.add((mu, b)) if gr in 'TH' and any(r != 5 and de < D1 and ha >= A0 for r, de, ha, _ in l): iii[(mu, b)] += 1 rk = [r for r, de, ha, _ in l if de <= E_ and ha >= E_] if rk: pv.append((gr, e, mu, b, min(rk))) pv = pd.DataFrame(pv, columns=['grp', 'eid', 'A', 'bn', 'rango']) q = pv.groupby(['grp', 'A', 'bn'], as_index=False).rango.min() q = q[[(a, b) in ACT for a, b in zip(q.A, q.bn)]] q['pre'] = [ACT[(a, b)][0] for a, b in zip(q.A, q.bn)]; q['post'] = [ACT[(a, b)][1] for a, b in zip(q.A, q.bn)] q['publica'] = [pub0(b) or iii[(a, b)] >= 2 for a, b in zip(q.A, q.bn)]; q['financiera'] = q.bn.map(fin); q['existe'] = q.bn.map(lambda b: existe(b, E_)); q['evaluable'] = q.A.isin(EV) q.to_parquet(f'/w/interno/60_impl2_pares_{m}.parquet') u = q[(q.rango <= 4) & q.existe & q.evaluable & ~q.publica & ~q.financiera] R[f'pares_{m}'] = {g_: dict(pares=int((u.grp == g_).sum()), pre=int(u[u.grp == g_].pre.sum()), post=int(u[u.grp == g_].post.sum())) for g_ in 'THSN'}; log(m, R[f'pares_{m}']) # controles ctrl = act[act.muni.isin(EV)].copy() ctrl = ctrl[[not (pub0(b) or fin(b)) and b in cla and existe(b, E_) and (a, b) not in lnk for a, b in zip(ctrl.muni, ctrl.bn)]] ctrl.to_parquet(f'/w/interno/60_impl2_ctrl_{m}.parquet') R[f'ctrl_{m}'] = dict(filas=len(ctrl), municipios=int(ctrl.muni.nunique()), sociedades=int(ctrl.bn.nunique()), pre=int(ctrl.pre.sum()), post=int(ctrl.post.sum())); log(R[f'ctrl_{m}']) # B: adjudicaciones del mandato con electo (T+H) con cargo vigente en su fecha, privadas no financieras W0, W1 = pd.Timestamp(P['W0']), pd.Timestamp(P['W1']) ad_m = g[(g.f >= W0) & (g.f <= W1)] porA = collections.defaultdict(list) for gr in ('T', 'H'): d = GR[m][gr] for e, mu in zip(d.eid, d.muni): for b, l in bys.get(e, ()): porA[(mu, b)].append(l) iiib = collections.Counter() for (mu, b), ls in porA.items(): iiib[(mu, b)] = sum(1 for l in ls if any(r != 5 and de <= W1 and ha >= W0 for r, de, ha, _ in l)) filas_b = [] for s, i, mu, b, f, me, ns, im in zip(ad_m.source, ad_m.id_canonical, ad_m.muni, ad_m.bn, ad_m.f, ad_m.menor, ad_m.nsp, ad_m.imp): ls = porA.get((mu, b)) if not ls: continue if any(r != 5 and de <= f and ha >= f for l in ls for r, de, ha, _ in l): filas_b.append((s, i, mu, b, bool(me) or bool(ns), pub0(b) or iiib[(mu, b)] >= 2, fin(b))) fb = pd.DataFrame(filas_b, columns=['source', 'id_canonical', 'A', 'bn', 'sc', 'publica', 'financiera']); fb.to_parquet(f'/w/interno/60_impl2_b_{m}.parquet') R[f'b_{m}'] = dict(T_priv=int(((~fb.publica) & (~fb.financiera)).sum()), T_priv_sc=int(fb[(~fb.publica) & (~fb.financiera)].sc.sum())); log(R[f'b_{m}']) # E/G: por persona, pares privados (cargo vigente en la fecha de la adjudicación), en mandato y antes; F: por persona-sociedad ad_all = g[(g.f >= A0) & (g.f <= W1)] byAB = {k: (v.f.values) for k, v in ad_all.groupby(['muni', 'bn'])} def priv_ok(mu, b): return not pub0(b) and not fin(b) and iiib[(mu, b)] < 2 Gf = [] for gr in ('T', 'H', 'N'): d = GR[m][gr] for e, mu in zip(d.eid, d.muni): pm = pa_n = 0 for b, l in bys.get(e, ()): fs = byAB.get((mu, b)) if fs is None or not priv_ok(mu, b): continue vig = [(de, ha) for r, de, ha, _ in l if r != 5] fm = [f for f in pd.to_datetime(fs) if any(de <= f <= ha for de, ha in vig)] if any(f >= W0 for f in fm): pm += 1 if any(f < W0 for f in fm): pa_n += 1 if pm or pa_n: Gf.append((gr, e, mu, pm, pa_n)) gf = pd.DataFrame(Gf, columns=['grp', 'eid', 'A', 'pares_m', 'pares_a']); gf.to_parquet(f'/w/interno/60_impl2_g_{m}.parquet') R[f'g_{m}'] = {g_: dict(pares_m=int(gf[gf.grp == g_].pares_m.sum()), pares_a=int(gf[gf.grp == g_].pares_a.sum())) for g_ in 'THN'}; log(R[f'g_{m}']) # F pvF = pv[pv.grp.isin(['T', 'N']) & (pv.rango <= 4)]; lim = T0 + pd.Timedelta(days=365); Ff = [] for gr, e, mu, b, r0 in zip(pvF.grp, pvF.eid, pvF.A, pvF.bn, pvF.rango): for r, de, ha, dcl in TR[(e, b)]: if r <= 4 and de <= E_ and ha >= E_: ap = ACT.get((mu, b), (0, 0))[0] > 0 Ff.append((gr, ap and not pub0(b) and not fin(b), dcl is not None and E_ < dcl <= lim)) ff = pd.DataFrame(Ff, columns=['grp', 'g1', 'cierra']) R[f'f_{m}'] = ff.groupby(['grp', 'g1']).cierra.agg(['size', 'sum']).reset_index().astype({'g1': str}).to_dict('records'); log(R[f'f_{m}']) json.dump(R, open('/w/salidas/60_impl2.json', 'w'), indent=1, default=str) log('fin')