# impl2: lectura xlsx con zipfile + xml.etree (stdlib) y todo lo demás en DuckDB; sin pandas ni openpyxl import zipfile, re, glob, json, xml.etree.ElementTree as ET, duckdb, csv NS={'m':'http://schemas.openxmlformats.org/spreadsheetml/2006/main','r':'http://schemas.openxmlformats.org/officeDocument/2006/relationships'} def col(ref): c=re.match(r'[A-Z]+',ref).group(); n=0 for ch in c: n=n*26+ord(ch)-64 return n-1 def hojas(path): z=zipfile.ZipFile(path); ss=[] if 'xl/sharedStrings.xml' in z.namelist(): for si in ET.fromstring(z.read('xl/sharedStrings.xml')).findall('m:si',NS): ss.append(''.join(t.text or '' for t in si.iter('{%s}t'%NS['m']))) wb=ET.fromstring(z.read('xl/workbook.xml')); rels=ET.fromstring(z.read('xl/_rels/workbook.xml.rels')) rmap={r.get('Id'):r.get('Target') for r in rels} for s in wb.find('m:sheets',NS): tgt=rmap[s.get('{%s}id'%NS['r'])]; tgt=tgt.lstrip('/'); tgt=tgt if tgt.startswith('xl/') else 'xl/'+tgt rows=[] for row in ET.fromstring(z.read(tgt)).iter('{%s}row'%NS['m']): r={} for c in row.findall('m:c',NS): v=c.find('m:v',NS); t=c.get('t') if t=='inlineStr': val=''.join(x.text or '' for x in c.iter('{%s}t'%NS['m'])) elif v is None: continue elif t=='s': val=ss[int(v.text)] elif t in ('str','b'): val=v.text else: val=float(v.text) r[col(c.get('r'))]=val rows.append(r) yield s.get('name'),rows out=open('/w/tmp/impl2_trim.csv','w',newline=''); w=csv.writer(out); w.writerow(['anio','trim','codigo','tipo_ent','pagos','pmp']) pat=re.compile(r'^\d\d-\d\d-\d\d\d-[A-Z]{1,2}-[A-Z]{1,2}-\d{3}$') for f in sorted(glob.glob('/w/datos/descargas/eell_*.xlsx')): y,m=map(int,re.findall(r'eell_(\d+)_(\d+)',f)[0]) for nm,rows in hojas(f): if nm not in ('Cesión','Variables'): continue for r in rows: c=str(r.get(3,'')).strip() if pat.match(c): w.writerow([y,m//3,c,r.get(4),r.get(7),r.get(10)]) out.close() c=duckdb.connect(); c.execute("SET temp_directory='/w/tmp/duck'") c.execute("""CREATE TABLE t AS SELECT * FROM read_csv('/w/tmp/impl2_trim.csv',header=true) WHERE NOT (anio=2018 AND trim=1)""") c.execute("""CREATE TABLE pa AS SELECT codigo, anio, any_value(tipo_ent) tipo_ent, avg(pmp) pmp_medio, sum(pagos) pagos_anio, count(*) n_trim FROM t GROUP BY 1,2 HAVING count(*)>=2""") c.execute("""CREATE TABLE pa2 AS SELECT *, round(pmp_medio,2)>60 s60, CASE WHEN tipo_ent='Ayuntamiento' THEN 'ayuntamiento' WHEN tipo_ent LIKE 'Diputaci%' THEN 'diputacion' WHEN tipo_ent LIKE 'Entidades de%' THEN 'eatim' WHEN tipo_ent LIKE 'Ciudad%' THEN 'ciudad_autonoma' ELSE 'supramunicipal' END tipo FROM pa""") c.execute("""CREATE TABLE pa3 AS SELECT *, ntile(5) OVER (PARTITION BY tipo, anio ORDER BY pagos_anio, codigo) tam FROM pa2""") c.execute("""CREATE TABLE o2c AS SELECT organo_clave, codigo_pmp codigo FROM '/w/tmp/organo_pmp.parquet' WHERE NOT regexp_matches(organo_clave,'^[0-9XYZKLM][0-9]{7}[A-Z]\\|')""") c.execute("""CREATE TABLE ou AS SELECT o.codigo, u.anio, sum(lotes_evaluables) n, sum(lotes_oferta_unica) k, sum(lotes_evaluables*coalesce(tasa_esperada,0)) E FROM read_csv('/et/oferta_unica/salidas/publicable_organo_anio_es_copia.csv',header=true) u JOIN o2c o USING(organo_clave) GROUP BY 1,2""") c.execute("""CREATE TABLE vc AS SELECT o.codigo, u.anio, sum(lotes_adjudicados) n, sum(coalesce(fondo_anulacion_T1,0)+coalesce(fondo_adjudicatario_T3,0)) k FROM read_csv('/et/versiones/salidas/publicable_organo_anio_es_copia.csv',header=true) u JOIN o2c o USING(organo_clave) GROUP BY 1,2""") c.execute("""CREATE TABLE cc AS SELECT o.codigo, p.anio_ref anio, sum(p.n_P) n, sum(coalesce(h.n_h1,0)) k FROM '/w/tmp/P_organo_anio.parquet' p LEFT JOIN '/et/em6v2/interno/organo_anio_v2_INTERNO.parquet' h USING(organo_clave,anio_ref) JOIN o2c o USING(organo_clave) GROUP BY 1,2""") res={} def rr(tab,T): return c.execute(f"""WITH d AS (SELECT p.tipo||'|'||p.anio||'|'||p.tam s, p.s60 x, h.k O, h.{T} T FROM pa3 p JOIN {tab} h USING(codigo,anio) WHERE h.{T}>0), g AS (SELECT s, sum(O) FILTER (WHERE x) O1, sum(O) FILTER (WHERE NOT x) O0, sum(T) FILTER (WHERE x) T1, sum(T) FILTER (WHERE NOT x) T0 FROM d GROUP BY s) SELECT sum(coalesce(O1,0)*coalesce(T0,0)/(coalesce(T1,0)+coalesce(T0,0)))/sum(coalesce(O0,0)*coalesce(T1,0)/(coalesce(T1,0)+coalesce(T0,0))), (SELECT count(*) FROM d) FROM g""").fetchone() res['rr_Y1']=rr('ou','E'); res['rr_Y2']=rr('vc','n'); res['rr_Y3']=rr('cc','n') # comparación con impl1 c.execute("CREATE TABLE i1 AS SELECT * FROM '/w/tmp/entidad_anio_hechos.parquet'") res['pmp_filas']=c.execute("SELECT (SELECT count(*) FROM pa3),(SELECT count(*) FROM i1)").fetchone() res['pmp_dif']=c.execute("""SELECT count(*), sum((abs(round(a.pmp_medio,2)-b.pmp_medio)>0.005)::int), sum((a.s60<>b.sobre_60)::int), sum((a.tam<>b.tam)::int) FROM pa3 a FULL JOIN i1 b USING(codigo,anio)""").fetchone() res['ou_dif']=c.execute("""SELECT count(*), sum((abs(a.k-coalesce(b.lotes_oferta_unica,0))>0)::int), sum((abs(a.E-coalesce(b.E,0))>1e-6*greatest(a.E,1))::int) FROM ou a JOIN pa3 USING(codigo,anio) LEFT JOIN i1 b USING(codigo,anio)""").fetchone() res['vc_dif']=c.execute("""SELECT count(*), sum((a.k<>coalesce(b.O13,0))::int), sum((a.n<>coalesce(b.lotes_adjudicados,0))::int) FROM vc a JOIN pa3 USING(codigo,anio) LEFT JOIN i1 b USING(codigo,anio)""").fetchone() res['cc_dif']=c.execute("""SELECT count(*), sum((a.k<>coalesce(b.n_h1,0))::int), sum((a.n<>coalesce(b.n_P,0))::int) FROM cc a JOIN pa3 USING(codigo,anio) LEFT JOIN i1 b USING(codigo,anio)""").fetchone() r1=json.load(open('/w/salidas/40_resultados.json')) for k,kk in [('rr_Y1','a_Y1_oferta_unica_sobre_60'),('rr_Y2','a_Y2_cambio_T1T3_sobre_60'),('rr_Y3','a_Y3_concurso_H1_sobre_60')]: res[k+'_impl1']=r1[kk]['rr']; res[k+'_dif_rel']=abs(res[k][0]-r1[kk]['rr'])/r1[kk]['rr'] print(json.dumps(res,indent=1,default=str)); json.dump(res,open('/w/salidas/50_impl2.json','w'),indent=1,default=str)