En el post anterior presentaba el dataset del Presupuesto de Gastos 2012 de Sevilla, y su visualización comprensiva en la plataforma OpenSpending. En este post pretendo explicar las claves del proceso de trasformación de los datos desde el PDF a formato CSV (valores separados por comas) y su carga en The Data Hub y OpenSpending.
Todos los ficheros y transformaciones intermedias que se describen en este post están disponibles para descarga desde el site del proyecto en Github:
https://github.com/fontanon/seville-spending
Personalmente creo que, si tuviera que volver a repetir el trabajo tardaría mucho menos, pues en todo el proceso he dedicado más tiempo a formarme en las herramientas empleadas que en el diseño del dataset y la propia transformación de los datos. Estoy seguro que este tutorial puede ser de ayuda a terceros. Empezamos.
Extracción: de PDF a hoja de cálculo
El primer paso consiste en la extracción de los datos desde el PDF a hoja de cálculo. Al final de este paso tendremos los datos, con cualquier estructura. Este punto es poco mecanicista y exige de la inteligencia del extractor para determinar cual es la mejora forma de hacerlo:
- Seleccionar el texto deseado en el PDF y copiarlo directamente a hoja de cálculo.
- Emplear herramientas de extracción de texto en PDF, por ejemplo la suite Apache PDFBox.
- Utilizar conversores de PDF a hoja de cálculo.
Todas las opciones aquí presentadas se desarrollan magníficamente en la presentación From PDFs to Usable Data, expuesta por Dan Nguyen en el International Journalism Festival 2012. Mi elección fue la herramienta Cometdocs, un conversor online que realizó un gran trabajo transformando el PDF completo en una hoja de cálculo. Aquí una captura del resultado:
¡Nada mal la transformación! ¿Verdad? Tengo que confesar que llegado aquí tuve que hacer alguna operación manual: no siempre las cuantías aparecían en la columna D, otras veces el título de los conceptos figuraba en la columna B, etcétera. Cuestión de media hora de paciencia y buena vista.
Transformación(I): Diseño del dataset
El primer paso hacia la transformación fue imaginar la estructura del documento final que quería conseguir, dígase, había que diseñar el dataset. El objetivo era contar con un set de datos que permitiera fácilmente la búsqueda y agregación de resultados. Afortunadamente el Presupuesto de Gastos publicado por el Ayuntamiento de Sevilla no está organizado de forma arbitraria, sino que se presenta en programas de gasto, agrupados por áreas. Los programas de gasto son gestionados por organismos dentro del Ayuntamiento y se descomponen en varios conceptos con una cuantía fija asignada. Repaso:
- Área de gasto: Primer nivel de agrupación de los gastos: vivienda, economía, deuda, hacienda …
- Entidad competente: organismo dentro del Ayto. encargado de gestionar los programas de gasto.
- Programa de gasto: Conjunto de gastos con una misión específica.
- Concepto: la partida de gasto aprobada,
- Cuantía: la cantidad de € asignada de forma fija al concepto.
Con esta estructura previa, no era dificil imaginar la mejor forma de estructurar el dataset resultante: cada registro (cada fila) debe contener una traza completa desde el área de gasto al concepto:
id, área, entidad, programa, concepto, cuantía
Transformación (II): De hoja de cálculo cruda a refinado CSV
Realmente creo que no ha habido magia alguna: ha sido más cuestión de hacerse con unas buenas herramientas y encontrar los mejores patrones a la hora de transformar la hoja de cálculo original en el dataset con la estructura que nos hemos dado. ¿Cuales han sido estas herramientas? Google Refine y algunas transformaciones menores con la shell linux, el tandem awk/sed y VIM.
Google Refine es una potente suite de Google para trabajar con datos sucios: tiene una serie de utilidades para segmentar columnas, rellenar filas y automatizar operaciones con expresiones regulares. Creo que casi todo lo que se puede hacer con Refine lo sabe hacer un experto con comandos y editores clásicos Linux, la ventaja no obstante, la cuidada interfaz Google Refine, la posibilidad de hacer búsquedas facetadas sobre los datos, el historial de operaciones realizadas que se almacena y la exportación de resultados distintos formatos. Chapó.
Explicar aquí el proceso de transformaciones completo con Refine me llevaría otro post. Me reduzco a comentar la más relevante transformación que pude realizar. Para quien seguir profundizando, pongo a su disposición el proyecto en Google Refine para que se lo pueda importar, donde podrá consultar el histórico de operaciones realizadas de forma completa. Lo encontrarás en el site en github del proyecto: https://github.com/fontanon/seville-spending
Básicamente las operaciones que describo fueron las que tuve que realizar de forma repetida a través de varias columnas:
- Partimos de columnas en las que hay id’s y títulos mezcladas: aplicamos operación de crear nueva columna a partir de (create a new column based in). Aquí se pueden introducir operaciones como las que figuran en la imagen para distinguir números de literales.
- Con id’s y títulos en columnas separadas podemos emplear la opción de columna ‘rellenar’ (fill down) para completar hacia abajo en cada fila por el resultado obtenido en el paso anterior.
- Borrar todas las filas sobrantes que no tienen cuantías.
Realizadas las transformaciones oportunas ya contábamos con el dataset refinado. Exportar a CSV y ¡listo!
Carga: The Data Hub
The Data Hub es un repositorio de datasets de uso general. Permite crear un dataset a partir de un CSV y proporciona búsquedas facetadas y acceder a los datos vía API (devuelve objetos JSON).
Generar el dataset fue bastante sencillo, tan solo seguir el asistente.
Visualización: OpenSpending
OpenSpending, la plataforma de visualización comprensiva de presupuestos, es capaz de realizar visualizaciones desde ficheros CSV. Declarar qué dataset usar (el CSV) en la plataforma fue tan sencillo como seguir el asistente. Una vez declarado el dataset (el contenido no está cargado aun) hay que realizar un mapeo entre las columnas del CSV y las variables que OpenSpending entiende: dimensiones, medidas, atributos …. Hay cuatro variables reservadas en OpenSpending que conviene mapear:
- amount: La cuantía. La tenemos.
- time: Fecha de la transacción. Tuve que generar una columna nueva en el CSV rellena con 2012
- from: Desde donde parte la transacción. Hice un mapeo de ‘entidad’ a ‘from’.
- to: Hacia donde va la transacción. Hice un mapeo de ‘concepto’ a ‘to’.
El gran consejo aquí, pues está poco documentado, es que las columnas área, entidad, programa y concepto necesité declararlas como variables de tipo compuesto (Compound variables) para poder después hacer visualizaciones que exploren en profundidad (desglosando por área, entidad, programa, concepto). Esta duda me la resolvieron en la lista de correo de OpenSpending. Agradecimientos a Friedrich Lindenberg de la OKFN por sus atenciones 
Realizado el mapeo procedí a realizar la carga de los datos desde el CSV: Resultado OK!
Para la visualización principal no existe asistente aún. Friedrich también me echó una mano aquí. Pongo el código que tuve que introducir en el cuadro de administración para generar la visualización principal:
[
{
"name": "default",
"label": "Spending by area",
"cuts": {},
"drilldown": "area",
"entity": "dataset",
"dimension": "dataset"
},
{
"name": "default",
"label": "Spending by program",
"cuts": {},
"drilldown": "program",
"entity": "compound",
"dimension": "area"
}
]
Llamamiento “a las armas”
Personalmente creo que la tecnología está lo suficientemente avanzada como para que una “armada” cívica nos encarguemos de transformar a Opendata todo presupuesto de gastos. Imaginad el potencial para el ciudadano de a pie, o para el periodista, el poder realizar por si mismo comparaciones de gastos entre distintos ayuntamientos o comparar el de su mismo municipio año a año.
