11 de abril de 2015

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:

image

Convertimos a tabla el contenido del value List:

image

Extraemos campos:

image

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:

image

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

image

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…

image

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.

image

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

image

O por grupos de edad:

image

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

image

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:

image

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

image

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:

image

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:

image

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.

9 de abril de 2015

Probando Power Query con SmartSantander

Power Query es un complemento de Excel que nos permite gestionar y automatizar todo lo que tiene que ver con búsqueda de información online, limpieza y carga de ficheros de datos, mezcla de datos… Se descarga desde la web de Microsoft, asegurándonos de elegir la versión correcta para nuestra instalación de office, tanto por idioma como por versión (32 o 64 bits).

Su primer uso es facilitarnos encontrar información en internet, a partir de fuentes de datos como Wikipedia, censo y gobierno USA (recordar buscad en inglés)… La herramienta localiza tablas de información en páginas web (en este caso, introducimos una url directamente). En ambos casos, los datos quedan vinculados con el origen, por lo que si estos cambian, se consiguen los datos nuevos al refrescar la conexión.

img1

Su otro modo de funcionamiento es la carga de datos desde ficheros de texto, Excel, al estilo del importador de ficheros csv, delimitados o de ancho fijo.

img2

La herramienta funciona como un asistente por pasos, en los que vamos ejecutando operaciones para ir modificando y limpiando los datos de la forma deseada. Tenemos herramientas para:

  • Dividir campos en varias columnas, a partir de delimitadores o de anchos
  • Unir columnas
  • Hacer conversiones complejas de tipos, fechas…
  • Limpiar datos que no cumplen un determinado criterio
  • Mezclar varios ficheros
  • Relacionar datos de varios ficheros, para crear un único fichero con datos de ambos
  • Realizar cálculos, fórmulas

La potencia de Power Query se pone de manifiesto en su capacidad de repetir las operaciones que realizamos en un fichero sobre otros similares, ya que todas las operaciones que realizamos en los menús generan unas acciones en un lenguaje de programación de la propia herramienta. De esta forma, podemos replicar las operaciones en otros ficheros, o si tenemos necesidades especiales, programar una operación.

Vamos a probarlo con un ejemplo de datos reales, tomados de SmartSantander. La dirección http://maps.smartsantander.eu/getdata.php nos ofrece datos en tiempo real del estado de numerosos sensores, en forma de fichero json.

Desde la pestaña de PowerQuery, seleccionamos la opción ‘De Web’ e introducimos la url anterior. Se cargaran los datos, inicialmente en un único bloque. Usaremos la capacidad de expandir tablas para, en dos pasos, extraer todos los campos del json.

image     image

A partir de aquí, hay que analizar los datos y ver cómo prepararlos para el análisis posterior. Hacemos reemplazo de . por , y conversión numérica para los datos de longitud/latitud, cambiamos de nombre columnas….

El paso más importante es extraer la información del estado de los sensores, que aparece en formato texto dentro de una celda que contiene información en HTML. La alternativa que he seguido es crear una nueva columna con fórmulas las que, usando patrones de texto, extraigan cada dato. Por ejemplo, para la fecha de última actualización:

if Text.PositionOf([content], "Last update") = -1 then Date.FromText("01/01/1900") else Date.FromText(Text.Range([content], Text.PositionOf([content], "Last update")+12, 11))

Tras realizar todos los pasos, conseguimos una tabla de datos lista para analizar, con la ventaja de estar conectada al origen de datos. Si pulsamos la opción ‘Datos –> Actualizar’ se cargarán los datos de origen, se procesarán con nuestras reglas y se actualizarán en la tabla.

image

Ahora, usando tablas dinámicas, podemos hacer diferentes análisis.

Fecha de última actualización de los sensores que parece que actualizan este dato. Parece que bastantes dispositivos están ‘muertos’ o han sido reemplazados.

image

Situación de batería de los elementos que la actualizan en 2015 (los buses no informan de esto):

datos batería

Rizando el rizo, si tenemos acceso a Power Map for Excel (disponible con Office 365 o ‘puede que’ con el nuevo PowerBI gratuito), podemos usar los datos de longitud y latitud para mostrar datos en un mapa.

Por ejemplo, un mapa de calor con los datos de temperatura (que por cierto, son muy extremos Smile). En el mapa están todos los datos, no solo los dispositivos vivos, probablemente esos datos extremos sean de dispositivos que no funcionan correctamente o que fallecieron.

image

En este enlace podéis consultar y descargar el fichero Excel: http://1drv.ms/1Czi0kf. Para que funcione la actualización de datos es necesario tener PowerQuery instalado, si no, se trabajará con la última versión guardada. Con Excel Online se puede jugar con la tabla dinámica creada.