Introducción
Sirva el presente documento como manual de explotación de los datos abiertos (OpenData) publicados en el Portal Web de actuaciones del MEDI.
En este documento se muestra, a modo de ejemplo, como importar los datos en una de las 3 herramientas comerciales de Business Intelligence (Inteligencia de Negocio) más utilizadas (Microsoft PowerBI), en uno de los Sistemas de Información Geográfica (SIG) de Código Abierto más implantados (QGIS) y en una de las soluciones de hoja de cálculo comerciales más implantadas en la ofimática (Excel).
Todos los datos que incluyen información geográfica se exportan en formato GeoJSON (IETF RFC 7946), un formato de estándar abierto diseñado para representar elementos geográficos sencillos, junto con sus atributos no espaciales, basado en JavaScript Object Notation. Las coordenadas espaciales siguen la codificación EPSG 4326 de la European Petroleum Survey Group o su equivalente WSG 84 (World Geodetic System 1984).
Los indicadores se exportan en formato json.
Datos Abiertos - OpenData
¿Qué son los datos abiertos?
El concepto de datos abiertos (open data, en inglés) persigue que determinados tipos de datos estén disponibles de forma libre para todo el mundo, sin restricciones de derechos de autor, de patentes o de otros mecanismos de control. Los datos abiertos en las administraciones públicas están ligados a los conceptos de Buen Gobierno, Transparencia y a la planificación y evaluación de las políticas públicas (art.26, referente a la información de la planificación y programación, de la Ley 12/2014, de 26 de diciembre, de transparencia y de acceso a la información pública).
¿Qué ventajas ofrecen?
El objetivo de proporcionar un acceso fácil y gratuito a los datos es ayudar a los ciudadanos a utilizarlos de forma innovadora y a aprovechar su potencial económico.
Permitir que el público en general reutilice los datos del MEDI del Cabildo Insular de Tenerife fomenta:
- El desarrollo económico en la isla de Tenerife.
- La transparencia en la gestión de la administración pública y, así, la transparencia en la planificación y evaluación de las políticas públicas.
Introducción a los conjuntos de datos
Contenido
Dentro del apartado OpenData de este portal podremos realizar la descarga de toda la información representada en el mismo y relacionada con el MEDI (Marco Estratégico de Desarrollo Insular).
- Mapas: Muestra información referente a proyectos realizados por el Cabildo Insular de Tenerife y enmarcados dentro del MEDI. Contiene la misma información que es representada en los mapas interactivos de la Web.
- Indicadores: Información estadística referente a los proyectos ejecutados enmarcados dentro del MEDI. Contiene la misma información que es representada en la sección indicadores de la web.
Formato de los Ficheros
Los ficheros que se encuentran disponibles en este apartado de OpenData siguen la misma estructura que define el contenido, cuyo formato sería:
<Tipo>-<Eje>-<Ejercicio>.(json|geojson)
| TIPO | ||
|---|---|---|
Mapas | ACT | Actuaciones |
Indicadores
| IMP-EST | Importe por estado |
EVO | Evolución presupuestaria por ejercicio (Solo presente para Todos los años) | |
EJE | Ejecución Presupuestaria (No presente para ejercicios (años) Abiertos) | |
ADJ-E | Importe Adjudicado por Eje/Programa | |
ADJ-P | Importe Adjudicado por Política de Gasto | |
| EJE | |
|---|---|
E0 | Datos referentes a todos los Ejes MEDI |
E1 … E5 | Datos referentes a un Eje MEDI en concreto |
| EJERCICIO | |
|---|---|
Todos | Datos referentes a todos los Ejercicios del MEDI |
2016 … | Datos referentes a un Ejercicio MEDI en concreto |
Mapas
El contenido de los ficheros de mapa está formado por la información representada en los Mapas de esta web. Los ficheros cuentan con múltiples campos de los que obtenemos diversa información, y cuyo contenido es el siguiente:
Geometría (Geometry)
El GeoJSON cuenta con una propiedad para representar los registros en un mapa, se encuentran en EPSG 4326 o WSG 84. Este sistema es uno de los más estandarizados, dado que es utilizado por Google para la representación de puntos en su servicio de Mapas Google Maps.
Coordinates | Coordenadas en EPSG 4326 |
Type | Tipo Geométrico, siempre es un punto |
Propiedades (Properties)
En este apartado aparece el resto de atributos disponibles para las actuaciones.
Proyecto | |
|---|---|
codproy | Código identificativo del Proyecto de Inversión |
Proyecto | Texto descriptivo del proyecto de inversión |
CodTerritorio | Código identificativo del Territorio (Municipio) en el que se realiza el proyecto |
DesTerritorio | Texto descriptivo del Territorio (Municipio) en el que se realiza el proyecto |
INFO MEDI | |
|---|---|
CodEje | Código identificativo del Eje dentro del contexto MEDI |
DesEje | Texto descriptivo del Eje dentro del contexto MEDI |
CodPrograma | Código identificativo del programa dentro del contexto MEDI |
DesPrograma | Texto descriptivo del programa dentro del contexto MEDI |
CodSubPrograma | Código identificativo del subprograma dentro del contexto MEDI |
DesSubPrograma | Texto descriptivo del subprograma dentro del contexto MEDI |
CodLineaAct | Código identificativo de la línea de actuación dentro del contexto MEDI |
DesLineaAct | Texto descriptivo de la línea de actuación dentro del contexto MEDI |
INFO Cabildo | |
|---|---|
CodGrupProgG | Código identificativo del Grupo/s de Programa |
DesGrupProgG | Texto descriptivo del Grupo/s de Programa |
CodPolGasto | Código identificativo de la/s política/s de Gasto asociadas al proyecto |
DesPolGasto | Texto descriptivo de la/s política/s de Gasto asociadas al proyecto |
CodAreaGasto | Código identificativo de la/s Área/s de Gasto asociadas al proyecto |
DesAreaGasto | Texto descriptivo de la/s Área/s de Gasto asociadas al proyecto |
min_anualidad | Anualidad de inicio del Proyecto |
max_anualidad | Anualidad de fin del Proyecto |
estado_s | Código identificativo del estado del proyecto |
estado_l | Texto descriptivo del estado del proyecto |
PerfilContratante | Enlace a la página del perfil de contratante |
Noticias | Enlace al portal de noticias |
Importes | |
|---|---|
Importe | Importe del proyecto |
Adjudicado | Importe Adjudicado al proyecto |
ejec | Porcentaje de Ejecución del Proyecto |
ahorro | Porcentaje de Ahorro de Licitación |
| FDCAN | |
|---|---|
FDCAN | Texto descriptivo del código FDCAN |
FDCANProg | Texto descriptivo del programa FDCAN |
FDCANLI | Texto descriptivo de la línea FDCAN |
Indicadores
El apartado de los Indicadores muestra información de los indicadores de ejecución del MEDI e incluidos en la sección de indicadores de la web. Se presentan a nivel de Ejercicio fiscal y Eje. A continuación presentamos la descripción de los campos presentes en cada uno de los tipos de indicadores.
Tabla de variables para Sección MEDI
Distribución del Importe por estado del proyecto (imp_est)
| codeje | Código identificativo del Eje dentro del contexto MEDI |
imp_adj | Importe asociado a proyectos en estado "Adjudicado" | |
imp_final | Importe asociado a proyectos en estado "Finalizado" | |
imp_prev | Importe asociado a proyectos en estado "Previsto" | |
imp_lic | Importe asociado a proyectos en estado "Licitado" | |
imp_eje | Importe asociado a proyectos en estado "En Ejecución" | |
Evolución del presupuesto por ejercicio (evo)
| Eje | Código identificativo del Eje dentro del contexto MEDI |
Presupuesto | Importe referente al presupuesto | |
cd_ejercicio | Ejercicio presupuestario | |
Ejecución Presupuestaria (eje)
| Eje | Código identificativo del Eje dentro del contexto MEDI |
presupuesto | Importe referente al presupuesto | |
ejecucion | Porcentaje de ejecución del presupuesto | |
obligaciones | Importe referente a las obligaciones reconocidas | |
%Importe Adjudicado por Eje (adj-E)
| cd_medi_eje | Código identificativo del Eje dentro del contexto MEDI |
adjudicado | Importe adjudicado a un eje | |
totadjudicado | Importe adjudicado total | |
perc_eje | Porcentaje de total adjudicado | |
%Importe Adjudicado por Política de Gasto (adj-P)
| cd_politica_gasto | Código identificativo de la política de Gasto |
ds_politica_gasto | Texto descriptivo de la política de Gasto | |
adjudicado | Importe adjudicado a un eje | |
totadjudicado | Importe adjudicado total | |
perc_politica | Porcentaje de total adjudicado |
Sección Ejes
Distribución del Importe por estado del proyecto (imp_est)
| codeje | Código identificativo del Eje dentro del contexto MEDI |
codprograma | Código identificativo del programa dentro del contexto MEDI | |
imp_adj | Importe asociado a proyectos en estado "Adjudicado" | |
imp_final | Importe asociado a proyectos en estado "Finalizado" | |
imp_prev | Importe asociado a proyectos en estado "Previsto" | |
imp_lic | Importe asociado a proyectos en estado "Licitado" | |
imp_eje | Importe asociado a proyectos en estado "En Ejecución" | |
Evolución del presupuesto por ejercicio (evo)
| Eje | Código identificativo del Eje dentro del contexto MEDI |
cd_medi_programa | Código identificativo del programa dentro del contexto MEDI | |
programa | Texto descriptivo del programa dentro del contexto MEDI | |
Presupuesto | Importe referente al presupuesto | |
cd_ejercicio | Ejercicio | |
Ejecución Presupuestaria (eje)
| Eje | Código identificativo del Eje dentro del contexto MEDI |
cd_medi_programa | Código identificativo del programa dentro del contexto MEDI | |
programa | Texto descriptivo del programa dentro del contexto MEDI | |
presupuesto | Importe referente al presupuesto | |
ejecucion | Porcentaje de ejecución del presupuesto | |
obligaciones | Importe referente a las obligaciones reconocidas | |
%Importe Adjudicado por Eje (adj-E)
| cd_medi_eje | Código identificativo del Eje dentro del contexto MEDI |
Cod_programa | Código identificativo del programa dentro del contexto MEDI | |
programa | Texto descriptivo del programa dentro del contexto MEDI | |
adjudicado | Importe adjudicado a un eje | |
totadjudicado | Importe adjudicado total | |
perc_eje | Porcentaje de total adjudicado | |
%Importe Adjudicado por Política de Gasto (adj-P)
| cd_politica_gasto | Código identificativo de la política de Gasto |
ds_politica_gasto | Texto descriptivo de la política de Gasto | |
Cd_medi_eje | Código identificativo del Eje dentro del contexto MEDI | |
Ds_medi_eje | Texto descriptivo del Eje dentro del contexto MEDI | |
adjudicado | Importe adjudicado a un eje | |
totadjudicado | Importe adjudicado total | |
perc_adjudicado | Porcentaje de total adjudicado |
Importación y Explotación de los datos abiertos del MEDI
Importación en Microsoft PowerBi
Para poder explotar los datos disponibles en la WEB del MEDI en la herramienta de BI Microsoft PowerBI, es requisito indispensable contar con la aplicación Power Bi Desktop instalada en el ordenador, disponible en el siguiente enlace, o contar con una licencia en Azure de Microsoft PowerBI (por ejemplo una licencia Premium).
Los pasos a seguir para la importación de los datos en dicha herramienta son los siguientes:
- Para poder importar los conjuntos de datos como orígenes de datos en Microsoft PowerBi, debemos descargar los ficheros de indicadores y/o mapas a un directorio local de nuestro ordenador, y abrir la aplicación Power Bi Desktop, disponible en el siguiente enlace.
- Una vez en la aplicación Power Bi Desktop, seleccionamos "Obtener Datos" y seleccionamos el tipo Json.
Seleccionamos el fichero Json en el explorador de archivos.
Nota: en caso de requerir GeoJson, cambiar el filtro en el explorador a Todos los Archivos.
Ficheros Mapas
A continuación deberá seguir los siguientes pasos para preparar los datos a través de la ETL de PowerBI para su explotación.
| Paso | Descripción | Pantalla |
|---|---|---|
| 1 | Una vez tengamos los resultados de la carga de datos, habrá que transformarlos hasta obtener una información legible. El primer paso será crear una tabla pulsando sobre la opción del menú superior. |
|
| 2 | Desagrupamos los valores dentro de la primera fila de la tabla creada, Botón derecho > Rastrear desagrupando datos. |
|
| 3 | Nuevamente convertimos a Tabla los resultados. Seleccionando <ninguno> como carácter delimitador. |
|
| 4 | Ahora procederemos a Expandir las columnas. |
|
| 5 | Y volvemos a expandir el campo properties y geometry. |
|
| 6 | Ahora, para obtener el valor de las coordenadas geográficas, debemos extraer los valores usando el delimitador "=", posteriormente dividiremos el campo resultante con la X e Y usando este mismo delimitador. |
|
| 7 | Ahora podemos cambiar los nombres a cada una de las variables, borrar las que no veamos necesarias para nuestro estudio y utilizarlas en los gráficos del informe. Para guardar los avances, seleccionaremos el botón Cerrar y Aplicar, situado en la cinta superior. |
|
| 8 | Una vez cargados, volveremos automáticamente al editor de informes de PowerBi Desktop. |
|
| 9 | Arrastramos un nuevo gráfico de Mapas, en este ejemplo utilizaremos el gráfico de mapas nativo de Power Bi. |
|
| 10 | Arrastramos el contenido de las variables de las coordenadas hacia los campos Latitud y Longitud, según corresponda. |
|
| 11 | Ahora podremos jugar con estas visualizaciones para representar otros datos, agregando nuevas variables a los diferentes campos presentes en la configuración del gráfico. |
|
Ficheros Indicadores
El procesado de este tipo de ficheros es inferior, dado que no necesitamos procesar las coordenadas geográficas. Por lo que simplemente seleccionamos el fichero json que queremos cargar y, posteriormente:
| Paso | Descripción | Pantalla |
|---|---|---|
| 1 | Convertimos a tabla. |
|
| 2 | Extraer valores en nuevas filas. |
|
| 3 | Extraer Valores o Expandir columnas. |
|
| 4 | Aplicar la Transformación de Datos. |
|
| 5 | Crear visualizaciones basadas en los datos del fichero cargado. Podremos cargar más orígenes de datos y relacionarlos, si así lo necesitáramos. |
|
Importación en QGis
Para importar los ficheros GeoJson dentro de QGis debemos seguir los siguientes pasos:
- Lo primero que necesitamos es tener instalada la herramienta QGis. En el caso de que no la tuviésemos instalada podremos descargarla desde el siguiente enlace.
- Debemos buscar la opción de agregar nueva capa vectorial.
Capa > Añadir Capa > Añadir Capa Vectorial
3. Una vez añadida la capa a QGis, nos mostrará en pantalla el resultado de la carga. Podremos añadir la capa base que deseemos.
Importación Microsoft Excel
Para realizar la importación de archivos Json en Excel necesitamos previamente instalar el complemento Power Query para Microsoft Excel, que nos abre un abanico enorme de posibilidades para importar archivos de diferentes fuentes e, incluso, desde Webs. Convierte Excel en un motor de consultas, al puro estilo Power Bi. Podremos descargar el complemento desde el siguiente enlace.
Si queremos conocer en profundidad la herramienta, el siguiente enlace nos muestra qué es y cómo utilizarla para otros casos de uso.
| Paso | Descripción | Pantalla |
|---|---|---|
| 1 | Abrimos una nueva hoja de cálculo vacía y seleccionaremos la pestaña Power Query. |
|
| 2 | Seleccionamos la opción de Desde otros orígenes > Consulta en blanco. |
|
| 3 | Seleccionamos Editor Avanzado e introducimos en el cuadro la siguiente consulta, modificando la ruta del fichero: letSource = Json.Document(File.Contents("C:\Users\Name\Desktop\JSONTest.json")), #"Converted to Table" = Record.ToTable(Source)in#"Converted to Table" |
|
| 4 | Seleccionamos de la vista el valor List y nos mostrará una lista de valores. |
|
| 5 | Convertiremos a Tabla los valores, utilizando la Opción Transformar > A la Tabla. |
|
| 6 | Mantenemos las opciones tal cual se muestran en pantalla, sin modificarlas. |
|
| 7 | Desplegamos la columna column 1, pulsando el icono que se muestra junto al nombre. |
|
| 8 | Desplegamos la columna properties, pulsando el icono que se muestra junto al nombre. |
|
| 9 | Desplegamos la columna geometry, pulsando el icono que se muestra junto al nombre. |
|
| 10 | Extraemos el valor de la columna coordinates, pulsando el icono que se muestra junto al nombre. |
|
| 11 | Como delimitador seleccionaremos el signo de igualdad =. |
|
| 12 | Para dividir el campo de las coordenadas seleccionaremos la opción Dividir Columnas, pulsando en el nombre de la columna con el botón derecho. Como delimitador seleccionaremos el signo de igualdad =. |
|
| 13 | Ya tendríamos Lista nuestra tabla de datos, ahora eliminamos las columnas que no queramos en nuestro estudio. Para ello pulsamos con el botón derecho sobre el nombre de la columna, y Quitar. También podremos cambiar el nombre de las columnas para que tengan nombres más leíbles con la opción de Cambiar Nombre. Una vez listo pulsamos en el botón superior Cerrar y Cargar. |
|
| 14 | El Resultado ahora se muestra como una hoja de cálculo tradicional de Excel. |
|
Este apartado documenta el proceso de carga de un fichero GeoJson, pero podremos realizar el mismo proceso para los ficheros Json de los Indicadores, los cuales serán más sencillos dada su mayor simpleza. Deberemos de ejecutar los pasos del 1 al 8 y el 13 y 14.