El #makingOf del mapa de la evolución del Dióxido de Nitrógeno en Madrid del #hack4good

Del 12 al 14 de septiembre de 2014, se desarrolló el #hack4good de Geeklist, un hackathon global contra el cambio climático, que aquí en Madrid lo organizamos la gente de Hackathon Lovers junto con la gente de KUNlabori, y cuyo patrocinador principal fue CartoDB.

A mi se me ocurrió hacer un mapa con la evolución de la contaminación del aire de Madrid, y conseguí realizar un mapa de la evolución del Dióxido de Nitrógeno en Madrid, que creo que ha quedado bastante chulo:

¿Cómo lo he hecho?

1. Búsqueda de datos

Lo primero fue buscar un conjunto de datos sobre la contaminación del aire en Madrid.

Lo encontré en la página web de datos abiertos del Ayuntamiento de Madrid, exactamente en el apartado sobre calidad del aire con los datos desde el 2003 al 2014.

Desde el último enlace descargué todos los datos que están en la carpeta datos.

2. Descifrar datos

Una vez descargados los datos, había que descifrar su contenido.

Eso lo encontré en la página web de medioambiente del municipio de Madrid, exactamente me descargué un fichero llamado INTPHORA-DIA.pdf, que también lo tenéis en datos/INTPHORA-DIA.pdf.

3. Geo-coordenadas de las estaciones

En los datos del Ayuntamiento de Madrid están los códigos de las estaciones atmosféricas, pero no sus geo-coordenadas.

Gracias a Félix Pedrera (@fpedrera) encontré las Geo-Coordenadas de las estaciones atmosféricas en AirBase - The European air quality database, exactamente me descargué el fichero estaciones/AirBase_v8_stations.csv.

4. Filtrar y parsear estaciones

Con los datos en bruto, tenía que filtrar y parsear los datos de las estaciones atmosféricas.

Para ello cree el script estaciones-madrid-toarray.sh, en donde primero filtro las estaciones de madrid y luego parseo los datos para poder utilizarlos en otro script:

# filtro las estaciones de Madrid
cat estaciones/AirBase_v8_stations.csv | grep -i madrid > estaciones/AirBase_v8_stations.madrid.csv

# exporto las estaciones de Madrid a un array de Shell Script
awk -F "\t" '{print "estaciones["$2"]=\"" $13 "_" $14 "\""}' estaciones/AirBase_v8_stations.madrid.csv > estaciones/estaciones-madrid-toarray.txt

El resultado es este bonito fichero con las geo-posiciones de las estaciones atmosféricas de Madrid.

5. Parsear datos

Ahora toca parsear los datos que están en la carpeta datos.

Para ello cree el script parsea.sh, en donde primero parseo los datos:

function parsea() {

    # ESTACION (8) PARAMETROS (2-2-2) AÑO (2) MES (2) DATOS...

    echo $1
    sed 's/V/;/g' datos/$1.txt | awk -F ";" '{print substr($1,1,8)";"substr($1,9,2)";20"substr($1,15,2)"-"substr($1,17,2)"-01;"$2}' >> datos/datos.csv

    # longitud;latitud;parametro;anio-mes-dia;valor
}

echo "longitud;latitud;parametro;anio-mes-dia;valor" > datos/datos.csv

parsea datos03
# ...
parsea datos14

Y luego remplazo los códigos de las estaciones atmosféricas de Madrid por sus geo-coordenadas:

estaciones[28079001]="-3.691945;40.422501"
# ...
estaciones[28079022]="-3.715884;40.404648"

# cambio 'estacion' por 'longitud' y 'latitud'
for index in ${!estaciones[*]}
do
    value=${estaciones[$index]}
    echo $index"="$value
    sed -i "s/$index/$value/g" datos/datos.csv
done

# quita la estación que hace de media de todas las estaciones y las estaciones sin geoposición
echo "longitud;latitud;parametro;anio-mes-dia;valor" > datos-ok.csv
awk  -F ";" '$1<0 data-blogger-escaped-datos.csv="" data-blogger-escaped-datos="" data-blogger-escaped-print="">> datos-ok.csv

El resultado es este bonito fichero con los datos de la calidad del aire de Madrid desde el 2003 al 2014.

6. Filtrado de datos

Una vez aquí, lo que tenemos que hacer es filtrar los datos, pues hay medidas de distintos contaminantes, cada uno con unas medidas distintas.

Para ello cree el script filtra.sh, en donde filtro los datos por contaminante, y de paso soluciono algunos problemas de formato existentes:

function filtra() {

    # longitud;latitud;parametro;anio-mes-dia;valor

    echo $2 $3
    echo "longitud;latitud;parametro;abrebiatura;descripcion;anio-mes-dia;valor;categoria" > filtrados/$2-$3.csv
    cat datos-ok.csv | awk -v parametro=$1 -v abrebiatura=$2 -v descripcion=$3 -F ";" '{ if ($3==parametro) { print $1 ";" $2 ";" $3 ";" abrebiatura ";" descripcion ";" $4 ";" $5 } }' >> filtrados/$2-$3.csv

    sed -i "s/0.000N//g" filtrados/$2-$3.csv
    sed -i "s/00.00N//g" filtrados/$2-$3.csv
    sed -i "s/1;0/1;/g" filtrados/$2-$3.csv
    sed -i "s/1;00/1;/g" filtrados/$2-$3.csv
    sed -i "s/1;000/1;/g" filtrados/$2-$3.csv

    # longitud;latitud;parametro;abrebiatura;descripcion;anio-mes-dia;valor
}

filtra 01 SO2   DIOXIDO_DE_AZUFRE
# ...
filtra 08 NO2   DIOXIDO_DE_NITROGENO
# ...
filtra 92 LLA   LLUVIA_ACIDA

El resultado esta en la carpeta filtrados.

7. Categorizar valores

Ahora el problema es que los datos son numéricos, y yo quiero tener categorías para poder poner un color a cada una de las categorías.

Para ello cree el script categoriza-no2.sh, en donde categorizo los valores numéricos, en este caso para el Dióxido de Nitrógeno (habría que realizar un filtro específico para cada uno de los contaminantes):

function categoriza() {

    # longitud;latitud;parametro;abrebiatura;descripcion;anio-mes-dia;valor

    echo "longitud;latitud;parametro;abrebiatura;descripcion;anio-mes-dia;valor;categoria" > NO2-Nitrogen-Dioxide-Madrid-Evolution.csv

    cat filtrados/NO2-DIOXIDO_DE_NITROGENO.csv | awk -F ";" '{ if ($7<25 data-blogger-escaped-print="">> NO2-Nitrogen-Dioxide-Madrid-Evolution.csv
    cat filtrados/NO2-DIOXIDO_DE_NITROGENO.csv | awk -F ";" '{ if ($7>24 && $7<50 data-blogger-escaped-print="">> NO2-Nitrogen-Dioxide-Madrid-Evolution.csv
    cat filtrados/NO2-DIOXIDO_DE_NITROGENO.csv | awk -F ";" '{ if ($7>49 && $7<75 data-blogger-escaped-print="">> NO2-Nitrogen-Dioxide-Madrid-Evolution.csv
    cat filtrados/NO2-DIOXIDO_DE_NITROGENO.csv | awk -F ";" '{ if ($7>74 && $7<100 data-blogger-escaped-print="">> NO2-Nitrogen-Dioxide-Madrid-Evolution.csv
    cat filtrados/NO2-DIOXIDO_DE_NITROGENO.csv | awk -F ";" '{ if ($7>99 && $7<125 data-blogger-escaped-print="">> NO2-Nitrogen-Dioxide-Madrid-Evolution.csv
    cat filtrados/NO2-DIOXIDO_DE_NITROGENO.csv | awk -F ";" '{ if ($7>124 && $7<150 data-blogger-escaped-print="">> NO2-Nitrogen-Dioxide-Madrid-Evolution.csv

    # longitud;latitud;parametro;abrebiatura;descripcion;anio-mes-dia;valor;categoria
}

categoriza

El resultado es este bonito fichero con los datos del Dióxido de Nitrógeno de Madrid desde el 2003 al 2014.

8. Crear tabla en CartoDB

Con los datos del Dióxido de Nitrógeno ya filtrados y parseados, lo que hice fue darme de alta en CartoDB y crearme una tabla a partir de dicho fichero:

9. Cambiar a tipo 'date' en CartoDB

Todos los datos se importaron bien, excepto el campo 'anio-mes-dia' que tuve que cambiarlo a tipo 'date' de forma manual:

10. Parámetros en el 'Torque Cat' de CartoDB

Luego, para realizar la presentación, seleccioné uno de los wizards, en este caso 'Torque Cat', que nos permite ver la evolución de datos categorizados a lo largo del tiempo.

Tuve que cambiar la columna 'Time Column' y poner 'anio-mes-dia'; y también tuve que cambiar la columna 'Category Column' y poner 'categoria':

Para que se visualizara bien con mis datos, cambié los 'Steps' a '128'', el 'Blend Mode' a 'multiply' y el 'Trails' a '5':

Para 'ver' la contaminación, puse colores a las categorías de menos a más contaminación: azul, verde, amarillo, naranja, rojo y marrón:

11. Vista mapa en CartoDB

Y poco más, ya tenemos nuestro precioso mapa de la evolución del Dióxido de Nitrógeno en Madrid: