Recientemente he liberado una aplicación web, usada originalmente para el Recuento definitivo de las elecciones provinciales de Rio Negro 2011:
En el Primer Hackaton de Datos Públicos, que incluyó juegos de datos de elecciones pasadas, trabajé en dicha aplicación para armar y publicar el código de la misma, a modo de demostración que sirviera para procesar los resultados de distintas elecciones.
La aplicación es básica y simple, permite consultar/cargar datos de las planillas (actas / telegramas de cada mesa) y tiene una página de resultados totalizados. Está escrita en el lenguaje de programación Python (framework web2py) y utiliza PostgreSQL como base de datos.
El esquema de base de datos ha sido generalizado para que pueda ser utilizado en cualquier tipo de elección, ya que soporta diferentes cargos, listas internas / partidos, y un árbol jerárquico de ubicaciones (soportando clasificaciones variadas, por ej. en País, Provincia, Departamento, Circuito y Mesa).
Mi idea de publicar el código fuente, fue más que nada por una cuestión educativa / experimental, para poder compartirlo con alumnos y otros interesados, ya que quizás sea de utilidad para tomarlo como base para analizar resultados de elecciones, hacer pruebas de carga y rendimiento, optimización de bases de datos, etc.
Hasta el momento, se procesaron los datos públicos de dos elecciones nacionales:
- Presidente, Senadores y Diputados 2007: http://www.web2py.com.ar/recuento2007/
- Primarias Abiertas Simultáneas y Obligatorias 2013: http://www.web2py.com.ar/recuento2013paso
Esta subido a un repositorio público bajo licencia AGPLv3+ (licencia de software libre similar a la de Linux pero con consideraciones adicionales para aplicaciones web): https://github.com/MSA-Argentina/recuento_web2py
Instalación Rápida:
- Descargar web2py desde: http://www.web2py.com/
- Descargar la aplicación en un archivo comprimido desde GitHub: master.zip
- Descomprimir la aplicaión en la carpeta application de web2py (por ej. bajo el nombre recuento)
- Crear una base de datos en PostgreSQL y ejecutar el esquema.sql para crear las tablas
- Seguir los pasos siguientes de cada juego de datos (ver carga.sql y migracion.sql)
- Configurar la cadena de conexión a la base de datos en el modelo app_settings.py
Juegos de Datos Públicos
Ultimamente trabajé en procesar los datos de las P.A.S.O. 2013 -elección primarias para senadores y diputados nacionales en las 24 provincias-, publicados en el Portal de Datos Públicos
Para más información ver carpeta 2013-primarias:
- Info sobre el Dataset
- Script SQL de carga de datos públicos -para PostgreSQL-
- Script SQL de migración a la aplicación -para PostgreSQL-
El juego de datos es bastante interesante ya que está a nivel de mesa (fuente de la información), por lo que incluye 88.463 mesas procesadas con 1.846.369 registros de resultado (votos por mesa, por cada lista para los cargos de diputados + sendadores).
Recordar que es un recuento provisorio por lo que los datos no son definitivos y no están todas las planillas cargadas (90.670 mesas habilitadas en total). El total de electores habilitado fue de 30.573.183.
Para ver los datos oficiales, dirigirse a http://www.elecciones.gov.ar/ (recuento definitivo) y http://www.resultados.gob.ar (recuento provisional).
.png)
Manejo de Imágenes de actas de mesa (telegramas / faxes):
La página de Consulta de Telegrama / Planilla permite revisar, cargar y corregir los votos por cargo por lista en cada mesa escrutada.
Para el procesamiento de las imágenes se desarrollaron dos programas auxiliares (scripts):
- faxes.sh: para extraer las imágenes del PDF y convertirlas al formato TIFF optimizado (CCITT grupo 4) se utilizaron las herramientas pdfimages y ppm2tiff
- faxes.py: para incorporar las imágenes a PostgreSQL (campo BYTEA), usando python, psycopg2 (adaptador BINARY) y una consulta para determinar la mesa según el nombre de archivo.
NOTA: si se utiliza una versión antigua de PIL (Python Imaging Library), es necesario convertir las imágenes a PNG, perdiendo un poco de eficiencia en la compresión, pero ganando compatibilidad.
Para mostrar las imágenes, se obtiene la misma desde la base de datos y se genera una miniatura con PIL (ver thumbnail en definitivo.py) para ser descargada por el navegador web.
El tamaño total de la base de datos, incluyendo las imágenes de las planillas, es de 6.4 GB
El tamaño total de la base de datos, incluyendo las imágenes de las planillas, es de 6.4 GB
Consultas no triviales con el "ORM" de web2py
Gracias a características declarativas del framework web2py, se pueden realizar consultas relativamente complejas y arbitrarias, en este caso con agrupaciones, tablas anidadas y criterios de búsquedas particulares, de manera programada en Python, sin necesidad de recurrir a escribir SQL puro.
A continuación se extrae un ejemplo de código para totalizar los votos dada una ubicación (por la URL se especifican los identificadores de ubicación, cargo y estado de las planillas):
# constantes (jerarquía de ubicaciones)
CLASES = ['Pais', 'Provincia', 'Departamento', 'Circuito', 'Mesa']
# alias de tablas:
p = msa.planillas
d = msa.planillas_det
l = msa.listas
# armo la consulta base con los criterios de búsqueda
query = p.id_planilla == d.id_planilla
query &= d.id_lista == l.id_lista
query &= p.id_estado == request.args[3]
query &= d.id_cargo == request.args[0]
query &= l.positivo == True
# armo la consulta "recursiva" (árbol) para tener las ubicaciones y planillas
# (up -> ubicación padre, uh -> ubicación hija)
# p/ el alias de cada tabla se usa el nombre de la clase (depto, mesa, etc.)
up = msa.ubicaciones.with_alias(ubicacion.clase)
query &= up.id_ubicacion == id_ubicacion
for clase in CLASES[CLASES.index(ubicacion.clase)+1:]:
uh = msa.ubicaciones.with_alias(clase)
query &= uh.id_ubicacion_padre == up.id_ubicacion
up = uh
query &= p.id_ubicacion == up.id_ubicacion
# campo suma total:
suma_votos = d.votos_definitivos.sum()
# ejecuto la consulta:
resultado = msa(query).select(
d.id_lista.with_alias("id_lista"),
l.nro_lista.with_alias("nro_lista"),
l.descripcion.with_alias("descripcion"),
l.idx_fila.with_alias("idx_fila"),
l.descripcion_corta.with_alias("descripcion_corta"),
l.color.with_alias("color"),
suma_votos.with_alias("suma_votos"),
groupby=(d.id_lista |
l.nro_lista |
l.descripcion |
l.idx_fila |
l.descripcion_corta |
l.color),
orderby= ~suma_votos | l.idx_fila
)
+resultado+CABA.png)
La salida puede observarse en la Página de Resultados por Ubicación que permite obtener la suma total de votos agrupada por lista y cargo, para un País / Dpto / Mesa):
Esta consulta en Python generará una consulta SQL similar a la siguiente:
SELECT planillas_det.id_lista, listas.nro_lista, listas.descripcion,
SUM(planillas_det.votos_definitivos) AS suma_votos
FROM planillas, planillas_det, listas,
ubicaciones AS provincia,
ubicaciones AS departamento,
ubicaciones AS circuito,
ubicaciones AS mesa
WHERE planillas.id_planilla = planillas_det.id_planilla
AND planillas_det.id_lista = listas.id_lista
AND planillas.id_estado = 'Publicada'
AND planillas_det.id_cargo = 1
AND listas.positivo = 'T'
AND provincia.id_ubicacion = 1
AND departamento.id_ubicacion_padre = provincia.id_ubicacion
AND circuito.id_ubicacion_padre = departamento.id_ubicacion
AND mesa.id_ubicacion_padre = circuito.id_ubicacion
AND planillas.id_ubicacion = mesa.id_ubicacion
GROUP BY planillas_det.id_lista, listas.nro_lista, listas.descripcion
ORDER BY SUM(planillas_det.votos_definitivos) DESC, listas.nro_lista;
Consultas Recursivas con PostgreSQL:
La consulta anterior, podría escribirse de manera más flexible en PostgreSQL utilizando Expresiones de Tablas Comunes (CTE) para consultas recursivas (clausula WITH).
En el siguiente ejemplo, se utiliza un termino no recursivo para obtener la ubicación base (id_ubicacion =1), unido luego con el termino recursivo que busca las ubicaciones hijas de cada padre:
WITH RECURSIVE U(id_ubicacion, descripcion, clase) AS (
SELECT UP.id_ubicacion, UP.descripcion, UP.clase
FROM ubicaciones UP
WHERE UP.id_ubicacion = 1
UNION ALL
SELECT UH.id_ubicacion, UH.descripcion, UH.clase
FROM U, ubicaciones UH
WHERE UH.id_ubicacion_padre = U.id_ubicacion
)
SELECT L.id_lista, P.id_partido, L.nro_lista, L.descripcion, P.descripcion,
SUM(PD.votos_definitivos) AS suma_lista
FROM U, planillas PL, planillas_det PD, listas L, partidos P
WHERE PL.id_ubicacion = U.id_ubicacion
AND PL.id_planilla = PD.id_planilla
AND PD.id_lista = L.id_lista
AND L.id_partido = P.id_partido
AND PD.id_cargo = 2
GROUP BY P.id_partido, L.id_lista, L.nro_lista, L.descripcion, P.descripcion
ORDER BY 6 DESC;
Si bien en este caso no cambia el rendimiento ni el resultado, podría ser útil para otras elecciones en que las jerarquías de ubicaciones no tengan los mismos niveles de agrupación (por ej., hay provincias que podrían estar divididas por secciones electorales, municipios, comunas, departamentos, con mayor o menor de detalle, generalmente hasta el establecimiento y mesa)
Es interesante ver con el comando EXPLAIN el análisis del plan de ejecución de la consulta para detectar posibles optimizaciones , como podría ser agregar el siguiente indice:
CREATE INDEX planillas_det_id_cargo_id_idx ON planillas_det(id_cargo, id_planilla)
Otra característica de PostgreSQL que puede ser útil en estos casos, son las funciones que calculan en base a una partición de los datos (similar a un agrupamiento).
Por ejemplo, para obtener la cantidad de votos por cada partido (además de la suma de cada lista interna), se puede agregar la expresión SUM(SUM(PD.votos_definitivos)) OVER (PARTITION BY L.id_partido) AS suma_partido a la consulta anterior:
WITH RECURSIVE U(id_ubicacion, descripcion, clase) AS (
SELECT UP.id_ubicacion, UP.descripcion, UP.clase
FROM ubicaciones UP
WHERE UP.id_ubicacion = 1
UNION ALL
SELECT UH.id_ubicacion, UH.descripcion, UH.clase
FROM U, ubicaciones UH
WHERE UH.id_ubicacion_padre = U.id_ubicacion
)
SELECT L.id_lista, P.id_partido, L.nro_lista, L.descripcion, P.descripcion,
SUM(PD.votos_definitivos) AS suma_lista,
SUM(SUM(PD.votos_definitivos)) OVER (PARTITION BY L.id_partido) AS suma_partido
FROM U, planillas PL, planillas_det PD, listas L, partidos P
WHERE PL.id_ubicacion = U.id_ubicacion
AND PL.id_planilla = PD.id_planilla
AND PD.id_lista = L.id_lista
AND L.id_partido = P.id_partido
AND PD.id_cargo = 2
GROUP BY P.id_partido, L.id_lista, L.nro_lista, L.descripcion, P.descripcion
ORDER BY 7 DESC, 6 DESC;
Funciones de Ventana con PostgreSQL:
Otra característica de PostgreSQL que puede ser útil en estos casos, son las funciones que calculan en base a una partición de los datos (similar a un agrupamiento).
Por ejemplo, para obtener la cantidad de votos por cada partido (además de la suma de cada lista interna), se puede agregar la expresión SUM(SUM(PD.votos_definitivos)) OVER (PARTITION BY L.id_partido) AS suma_partido a la consulta anterior:
WITH RECURSIVE U(id_ubicacion, descripcion, clase) AS (
SELECT UP.id_ubicacion, UP.descripcion, UP.clase
FROM ubicaciones UP
WHERE UP.id_ubicacion = 1
UNION ALL
SELECT UH.id_ubicacion, UH.descripcion, UH.clase
FROM U, ubicaciones UH
WHERE UH.id_ubicacion_padre = U.id_ubicacion
)
SELECT L.id_lista, P.id_partido, L.nro_lista, L.descripcion, P.descripcion,
SUM(PD.votos_definitivos) AS suma_lista,
SUM(SUM(PD.votos_definitivos)) OVER (PARTITION BY L.id_partido) AS suma_partido
FROM U, planillas PL, planillas_det PD, listas L, partidos P
WHERE PL.id_ubicacion = U.id_ubicacion
AND PL.id_planilla = PD.id_planilla
AND PD.id_lista = L.id_lista
AND L.id_partido = P.id_partido
AND PD.id_cargo = 2
GROUP BY P.id_partido, L.id_lista, L.nro_lista, L.descripcion, P.descripcion
ORDER BY 7 DESC, 6 DESC;
Cuyo resultado es:
Esta aplicación fue posible gracias a la colaboración de la empresa MSA y al grupo de trabajo de datos públicos / software libre del foro de la Agenda Digital, por haber hecho posible la publicación del código fuente y el acceso a los datasets respectivamente.
En el futuro posiblemente publicaremos un análisis más exhaustivo, extendiendo el apunte sobre optimización de consultas y estadísticas en el sitio del Grupo de Usuarios de PostgreSQL Argentina: http://www.postgresql.org.ar/trac/wiki/OptimizarRendimiento
Seguramente se presentará el trabajo en la próxima jornada PostgreSQL: PgDay Argentina 2013, donde se preparará un taller para experimentar con los datos, analizar el rendimiento y proponer mejoras.
Agradecimientos y trabajo a futuro
Esta aplicación fue posible gracias a la colaboración de la empresa MSA y al grupo de trabajo de datos públicos / software libre del foro de la Agenda Digital, por haber hecho posible la publicación del código fuente y el acceso a los datasets respectivamente.
En el futuro posiblemente publicaremos un análisis más exhaustivo, extendiendo el apunte sobre optimización de consultas y estadísticas en el sitio del Grupo de Usuarios de PostgreSQL Argentina: http://www.postgresql.org.ar/trac/wiki/OptimizarRendimiento
Seguramente se presentará el trabajo en la próxima jornada PostgreSQL: PgDay Argentina 2013, donde se preparará un taller para experimentar con los datos, analizar el rendimiento y proponer mejoras.
