jueves, 5 de septiembre de 2013

Aplicación libre para recuento de votos / escrutinio: recuento_web2py


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:
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:
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).

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

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
             )


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)


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:


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.

1 comentario:

  1. these all projects have you completed by you ?? we are also a digital marketing companyif we can discuss in more deeply so i can explain to you my work and we can work together

    ResponderEliminar

Licencia y Aviso Legal

CC BY-SACopyright (C) 2009 - 2013 Mariano Reingart reingart@gmail.com - Visite el sitio http://reingart.blogspot.com/ para novedades. Salvo indicación contraria, el texto de esta página está disponible bajo la Licencia Creative Commons Atribución Compartir Igual 3.0 Unported; podrían ser aplicables cláusulas adicionales.

Se solicita mantener el aviso de copyright (enlazando al texto original), compartir las obras derivadas bajo la misma licencia, y si es, posible comunicando al autor cualquier mejora. No utilizar logotipos ni marcas sin la debida autorización de sus titulares.

Toda información es proporcionada a Titulo Informativo y se entrega como está, sin garantías explícitas ni implicitas de ningún tipo, incluyendo sin limitación, pérdida de ganancias, interrupción de negocios, pérdida de programas u otros datos en sistemas de computación o cualquier otro reclamo. Al usarla acepta hacerlo bajo su propia responsabilidad, conociendo la normativa y reglamentaciones existentes. En caso de controversias respecto del presente, usted acepta presentarlas y resolverlas exclusivamente en la jurisdicción de los Tribunales Ordinarios Civiles y Comerciales del Departamento Judicial de Morón, Provincia de Buenos Aires, Argentina, renunciando expresamente a cualquier otro fuero que pudiere corresponder.