Mapas en Excel y padrón de Santander

Siguiendo con mi faceta de comercial friki de Excel, vamos a seguir probando cosas. El Ayuntamiento de Santander tiene un portal de datos abiertos, muy interesante para el desarrollo de aplicaciones.

Vamos a utilizar uno de ellos, de población histórica, para ver hasta donde puede llegar Power Query, y posteriormente, hacer unos mapas con Power Map.

Usaremos wget para descargar datos de esta URL, cambiando el año para obtener en un fichero diferente los datos de 2010 a 2014 (este año parece estar incompleto, o ha habido una emigración masiva): http://datos.santander.es/api/rest/datasets/poblacion_pob2014.json?items=1000000

Ahora toca ver cómo cargar los datos, y esto siempre es un poco de prueba y error hasta ver cómo entiende la herramienta nuestro fichero. Cargamos uno de los ficheros:

Convertimos a tabla el contenido del value List:

Extraemos campos:

Creamos elementos de tabla, eligiendo que parta sin delimitadores. Como los datos son json, lo hará automáticamente y ya nos muestra el icono de expandir en la columna:

Y al expandir, obtenemos columnas con todos los datos del fichero:

Luego solo queda formatear algunos campos, borrar otros que no parecen de utilidad, y añadir una columna con la fecha del último día del año correspondiente al padrón, con el que luego relacionaremos los datos.

El resultado es un set de datos para el padrón de un año concreto, donde tenemos una línea por persona, y de ella, un montón de datos, edad, código postal, fecha de alta en el padrón, nivel de estudios…

Power Query genera un script con todos los pasos, que copiaremos para pegarlo en sucesivas consultas para los ficheros de datos de otros años, de forma que todos tendrán el mismo contenido y procesamiento.

De esta forma, generaremos 5 sets de datos, uno por año, que luego podremos anexar para tener en una única tabla los datos de evolución del padrón en 5 años. El anexado se puede hacer con el asistente, pero es mejor crear a mano la consulta que une todo.

let
    Origen = Table.Combine({pob2014,pob2013,pob2012,pob2011,pob2010})
in
    Origen

Con paciencia, porque son 512MB de datos y ‘duele’… Minutos de agonía, disco ssd al 100% y 770.000 filas cargadas.

Con esto podemos crear tablas dinámicas, de distribuciones por género (cuidado con 2014, datos incompletos):

O por grupos de edad:

Para hacer los mapas, usaremos Power Map, una herramienta muy potente que permite:

• colocar datos en mapas, al estilo de una tabla dinámica, usando datos diversos de geografía: coordenadas long/lat, países, provincias, calles, códigos postales…
• Presentar los datos en columnas, mapas de calor…
• Hacer animaciones en video, haciendo zoom en detalles de los datos y generando una secuencia animada entre ellos
• O hacer animaciones temporales, cogiendo una fecha que esté en los datos y generando un video con la evolución

El funcionamiento es muy sencillo, se elige la magnitud a mostrar, y si se desea, por qué categorizarla o filtrar. En este caso, elegimos el 2013:

Podemos generar una nueva escena con los mismos datos, pero cambiando la cámara, para luego hacer una animación (se pueden cambiar los tiempos y tipo de transiciones):

O elegir un campo de fecha para hacer una animación, en este caso, la evolución de personas en el censo por año:

O la evolución del censo de un año en función de la fecha de incorporación de personas al mismo.Con todo ello, podemos generar este ¿bonito? video:

Pruebas con PowerMap y datos del censo de Santander

Como tenemos un dato de ‘país de procedencia’, podemos pintar sobre el globo la procedencia de los empadronamientos para todos los no españoles:

Realmente esto da mucho mas juego, y como ejemplo, una demo ‘bien hecha’. Para conseguirlo, una tabla con centrales de generación energética, con datos de fecha, tipo y ubicación y un poco de tiempo para definir cada escena y las transiciones:

Por si alguien quiere jugar con los datos en Excel, los puede descargar desde este enlace.