¿Pedaleas en la ciudad?: Analiza con Excel la seguridad de los ciclistas en Madrid

13 de septiembre de 2017
En este post vamos a ver un ejemplo de cómo podemos realizar unas sencillas analíticas descriptivas sobre un conjunto de datos de nuestro interés, sin necesidad de recurrir a complejas herramientas específicas. Excel es una herramienta muy extendida, pero muchas veces no somos conscientes de su gran potencia. Al igual que en un post anterior la hemos usado para tareas de depuración y preparación de datos, en este ejemplo la usaremos como herramienta analítica que nos permita responder a las preguntas que nos planteamos sobre Bicimad.
En 2014 entró en funcionamiento el servicio público de bicicletas BiciMAD. Como a muchos otros madrileños que sufrimos el día a día de atascos en Madrid, nos pareció una buena noticia contar con otras alternativas de transporte público. Pero nos preocupaba un tema: la seguridad. ¿Habría más accidentes? ¿Se transmitiría esta información de forma transparente a los ciudadanos?
Decidimos investigar el tema y buscamos en el portal de Datos Abiertos del Ayuntamiento de Madrid información sobre accidentes relacionados con bicicletas. Encontramos este dataset que recoge los ”Accidentes de Tráfico con implicación de bicicletas”. El volumen de datos no es muy grande, ya que, aunque el servicio se arrancó en 2014, sólo tenemos información a partir de Enero de 2017. Sin embargo, esta información se va actualizando mensualmente. Aunque el tamaño de la muestra sea pequeño y, por tanto, las conclusiones que obtengamos tampoco serán muy determinantes, nos interesa ver cómo podemos “hacer hablar” a estos datos para dar respuesta a las preguntas que nos preocupan.
Portal de Datos Abiertos del Ayuntamiento de Madrid. Figura 1: Portal de Datos Abiertos del Ayuntamiento de Madrid.
Desgraciadamente, en Julio de este año 2017 se produjo el primer accidente mortal de BiciMad en un punto cuya peligrosidad ya había documentado en su blog otro usuario un año antes. Es muy importante detectar estos “ puntos negros” para ver qué medidas se pueden tomar para evitar accidentes. Vamos a analizar con Excel este conjunto de datos para encontrar nosotros mismos las respuestas a estas cuestiones sobre la seguridad de BiciMAD.
En primer lugar, nos descargamos el conjunto de datos en formato Excel. En este fichero tenemos información sobre los accidentes de tráfico donde está implicada al menos una bicicleta, indicando día, hora, nº de víctimas, distrito, nombre de la vía y tipo de accidente.
Los tipos de accidente considerados son los siguientes:
  • Colisión doble: Accidente de tráfico ocurrido entre dos vehículos en movimiento.
  • Colisión múltiple: Accidente de tráfico ocurrido entre más de dos vehículos en movimiento.
  • Choque con objeto fijo: Accidente ocurrido entre un vehículo en movimiento con conductor y un objeto inmóvil que ocupa la vía o zona apartada de la misma, ya sea vehículo estacionado, árbol, farola, etc.
  • Atropello: Accidente ocurrido ente un vehículo y un peatón que ocupa la calzada o que transita por aceras, refugios, paseos o zonas de la vía pública no destinada a la circulación de vehículos.
  • Vuelco: Accidente sufrido por un vehículo con más de dos ruedas y que por alguna circunstancia sus neumáticos pierden el contacto con la calzada quedando apoyado sobre un costado o sobre el techo.
  • Caída motocicleta: Accidente sufrido por una motocicleta, que en determinado momento pierde el equilibrio, por causa del conductor o por las circunstancias de la vía.
  • Caída ciclomotor: Accidente sufrido por un ciclomotor, que en determinado momento pierde el equilibrio, por causa del conductor o por las circunstancias de la vía.
  • Caída bicicleta: Accidente sufrido por una bicicleta, que en determinado momento pierde el equilibrio, por causa del conductor o por las circunstancias de la vía.
El fichero descargado tiene este aspecto: Fichero descargado en formato Excel. Figura 2: Fichero descargado en formato Excel.
Vamos a empezar creando una tabla.
Es tan sencillo como elegir la opción “Insertar tabla” en el menú ¿Qué desea hacer?. Automáticamente nos preselecciona toda la tabla y detecta que tiene encabezados. Sólo tenemos que confirmar. (Hemos eliminado antes la primera fila porque la información que nos facilita es redundante).
Creamos una tabla. Figura 3: Creamos una tabla.
Nos queda algo así: Aspecto de la tabla. Figura 4: Aspecto de la tabla.
Podemos insertar valores totales. Para ello, activamos la casilla “Fila de totales ”, que podemos encontrar en Herramientas de tabla / Diseño Figura 5: Insertamos fila de totales. Figura 5: Insertamos fila de totales.
Después, en la columna que nos interese, desplegamos y elegimos la función (suma, promedio, máximo etc.). En este caso, elegimos ”la cuenta” de los accidentes.
Como vamos a analizar la distribución de casos por franjas horarias vamos a asignar a cada registro una etiqueta:
  • Mañana: de las 7 a las 12
  • Tarde: de las 13 a las 20
  • Noche: de las 21 a las 6
Para ello, primero quitamos los “DE” con la función “Buscar-Reemplazar” (del menú Inicio) Uso función Buscar-Reemplazar. Figura 6: Uso función Buscar-Reemplazar.
Después extraemos el dato “hora” de la columna “Tramo horario”. Como el intervalo es siempre de 1 hora, sólo nos interesa el primer valor numérico de la columna. Insertamos dos columnas adicionales, y desde el menú Datos, seleccionamos:
Datos, Texto en columnas, delimitados por “:”
Nos quedamos con la primera columna, que indica la hora y eliminamos las otras dos.
Ahora queremos asignarle una etiqueta “mañana/tarde/noche”, según el valor de esa columna. Para eso, podemos utilizar funciones SI anidadas. Como a veces resultan un poco complejas, si tenéis algún problema con ellas os serán muy útiles estos vídeos.
Insertamos una nueva columna, y con la función SI anidada asignamos las etiquetas que corresponden:
=SI(O([@[TRAMO HORARIO]]>=20;[@[TRAMO HORARIO]]<=6);"Noche"; SI([@[TRAMO HORARIO]]>=13;"Tarde";"Mañana")) Uso de la función SI anidada. Figura 7: Uso de la función SI anidada.
Una vez etiquetadas las franjas horaria, vamos a insertar la tabla dinámica. Estas tablas (pivot table) nos permitirán crear un sencillo "tablero de mandos" para analizar los datos de forma dinámica.
Antes de insertar la tabla dinámica, nos aseguramos de que los nombres de las columnas sean correctos, para facilitarnos la selección de campos en la siguiente fase. Después, seleccionamos “Insertar”-Tabla Dinámica, y pedimos que la agregue en una hoja nueva. Crear tabla dinámica. Figura 8: Crear tabla dinámica.
Ahora, tendremos que ir añadiendo los campos que nos interesan en nuestra tabla dinámica. Por ejemplo, sólo con añadir Tramo Horario y Meses en filas, y como valores, Tipo de Vehículo (que al ser todos “bicicleta” nos sirve para contar los casos): Elegimos campos. Figura 9: Elegimos campos.
Figura 10: Incidencias por mes. Figura 10: Incidencias por mes.
... podemos empezar a ver cómo el número de accidentes registrado en Enero (44), prácticamente se duplica en los meses de verano, con un valor máximo de 80 incidentes en Junio.
Recordemos que nuestro objetivo es crear un “tablero” donde podamos responder preguntas como:
  • ¿Cuáles son los accidentes más frecuentes?
  • ¿Cuándo ocurren?¿Hay más en unas épocas del año que en otras?¿en unas franjas horarias que en otras?
  • ¿Dónde suceden?¿Son más frecuentes en unos distritos que en otros?¿Hay “puntos negros” de acumulación de accidentes?
1. Empezamos creando la primera visualización. Agregamos los campos:
  • Tipo de accidente (Filas)
  • Distrito (Filtro)
  • Tramo horario (Columnas)
  • Número de incidentes (valores)
Podemos ver cuántos accidentes de cada tipo suceden según la franja horaria, en todos los distritos, o filtrando los que nos interesen. Figura 11: Accidentes por franja horaria y distrito. Figura 11: Accidentes por franja horaria y distrito.
2. Creamos una segunda tabla en la misma hoja
Recordemos que nuestra intención es crear un tablero. Para ello, al agregar la nueva tabla desde la hoja “listado de accidentes…” indicamos el nombre de la hoja y su posición (simplemente, eligiendo una celda) en la que hemos creado la primera tabla dinámica en lugar de “ hoja nueva”.
En esta segunda tabla vamos a hacer el análisis de los accidentes por distrito. Por ello, vamos a añadir los siguientes campos:
  • Distrito (Filas)
  • Meses (Filtro)
  • Número de incidentes (valores)
Obtenemos este resultado: Figura 12: Incidentes por distrito. Figura 12: Incidentes por distrito.
Ahora, vamos a hacer un filtrado por franja horaria más visual. Queremos crear unos “botones” etiquetados como “ Mañana”, “ Tarde” y “ Noche”, y colocarlos sobre cada una de las tablas dinámicas que hemos creado.
Para ello, en el selector de campos para la tabla dinámica, sobre la opción “ Tramo horario” elegimos “Agregar como segmentación de datos”(slicing) en el menú contextual. Figura 13: Agregamos segmentación por Tramo Horario.
Figura 13: Agregamos segmentación por Tramo Horario.
Con esto obtenemos los “botones”. Ahora, para que quede mejor en el panel, ponemos los botones sobre la tabla dinámica y, usando el menú “Herramientas de segmentación de Datos”, elegimos la opción “3 columnas” para visualizarlo en horizontal.
Figura 14: Configuración de columnas para "botones". Figura 14: Configuración de columnas para "botones".
Y nos queda así: Figura 15: Botones de segmentación sobre la tabla. Figura 15: Botones de segmentación sobre la tabla.
Así, simplemente pulsando el botón que nos interese, podremos ver los datos de la tabla segmentados por ese valor. Ahora queremos que esa segmentación afecte también a la otra tabla. Ya que estamos construyendo un panel, queremos que todas las tablas/diagramas estén “sincronizados” en cuanto a franja horaria.
Para ello, en el Menú “Herramientas de segmentación de datos”, seleccionamos “Conexiones de informe” y marcamos también la primera tabla (Tabla dinámica 8). Vemos que la segmentación puede sincronizarse incluso con tablas dinámicas que estén ubicadas en otras hojas, lo cual nos permite hacer informes multipágina. Figura 16: Conexiones de informe. Figura 16: Conexiones de informe.
3. Insertamos gráficos dinámicos.
Ahora que ya tenemos las dos tablas, y una segmentación que nos interesa, podemos insertar gráficos dinámicos que nos ayuden a analizar la información de forma más visual. Por ejemplo, en la primera tabla insertamos un gráfico dinámico tipo “tarta” (pie chart).
Para insertar el gráfico dinámico, nos ponemos sobre la tabla que nos interese, y seleccionamos la opción “Insertar gráfico dinámico” del menú Herramientas de tabla dinámica, Analizar Figura 17: Insertamos gráfico dinámico. Figura 17: Insertamos gráfico dinámico.
Así, según vamos seleccionando los “botones” que filtran por franja horaria, se van actualizando tablas y gráficos dinámicos. Así, podemos ver que, por las mañanas, en el conjunto de distritos, el tipo de accidente más habitual es la colisión doble. Figura 18: Gráfico dinámico tipo de incidente/mañana.
También vemos que, por las noches, se diversifica más el tipo de incidentes. Figura 19: Gráfico dinámico tipo de incidente/noche Figura 19: Gráfico dinámico tipo de incidente/noche
Ahora insertamos un segundo gráfico dinámico que nos muestre el número de casos por distrito. Desde la segunda tabla que hemos creado, añadimos un gráfico dinámico igual que hemos hecho en el caso anterior. En este caso elegiremos, por ejemplo, un diagrama de barras. Igual que en caso anterior, crearemos un nuevo menú de segmentación de datos. En este caso, segmentaremos por “tipo de accidente”. Así, tenemos ya un panel en el que podemos analizar y visualizar los datos eligiendo la segmentación que más nos interese.
Recolocaremos los gráficos y botonaduras de segmentación de la forma que nos sea más cómoda (por ejemplo así), y ya tenemos un panel de control que nos permite analizar cómodamente los datos. Al colocarlos, habrá que tener en cuenta que no se pueden superponer unas tablas con otras. Por tanto, habrá que dejar para cada tabla el espacio máximo que ocupa en cada segmentación. Este tipo de detalles son los que están optimizados en las herramientas específicas que encontramos en el mercado sobre analíticas de datos, así como tener una mayor facilidad para que el resultado sea visualmente atractivo. Pero para el objetivo de este ejemplo, Excel nos resulta más que suficiente. Figura 20: Ejemplo de panel. Figura 20: Ejemplo de panel.
Usando los dos juegos de botones, se van actualizando automáticamente las gráficas y las tablas dinámicas. Ahora podemos responder a las preguntas que nos hemos planteado al principio de este post.
  • ¿Qué accidente es el más frecuente?
  • ¿A qué horas se producen más accidentes?
  • ¿En qué distrito se producen más accidentes? ¿alguna calle en concreto?
  • ¿Varía el número de accidentes según la estación del año?
Vamos a responder a éstas preguntas, pero luego, te invito a plantear las tuyas propias.
¿Qué accidente es el más frecuente? ¿A qué horas se producen más accidentes? Seleccionando todos los tipos de accidente y todas las franjas horarias, podemos ver que, los más frecuentes son las caídas y las colisiones dobles, y que es en la franja de la tarde cuando más incidencias se producen.
Por las mañanas, el número de caídas es ligeramente inferior al de colisiones dobles, pero van aumentando conforme avanza el día, siendo la incidencia más habitual por las noches. Por las noches, los atropellos disminuyen, pero aumentan los choques con objeto fijo.
¿En qué distrito se producen más accidentes? ¿alguna calle en concreto?
Si observamos la segunda tabla dinámica y su gráfico asociado (lo hemos cambiado a barras verticales ya que se puede modificar el tipo de gráfico cuando nos interese sin mayor dificultad), vemos, sin hacer ningún filtrado previo por horario ni por distrito, que el mayor número de incidentes se concentran en el distrito de Centro, seguido, a cierta distancia por los distritos de Retiro y Arganzuela.
Curiosamente, si analizamos esta misma gráfica según la franja horaria, vemos cómo el patrón de incidentes por distrito va cambiando. El distrito Centro siempre está en primer lugar, pero el resto de posiciones varían. Este hecho seguramente tenga que ver con el hecho de que en algunos distritos haya una mayor concentración de locales de ocio, o zonas comerciales, de oficinas etc.
Por ejemplo, en conjunto, los distritos con mayor número de accidentes son Centro, Arganzuela y Chambertí. Sin embargo, por las tardes, los distritos con mayor número de incidentes son, por ese orden, Centro, Retiro ,Chamberí y Carabanchel. Y por las mañanas, son Centro, Moncloa y Arganzuela. ¿Varía el número de accidentes según la estación del año?
En la primera tabla dinámica que hemos creado ya hemos visto la respuesta a esta pregunta. El número de incidentes se ha ido incrementando gradualmente desde los 30 del mes de Enero al máximo de 58 registrados en Junio. Es lógico que conforme mejoran las condiciones climatológicas, aumente el número de incidencias, ya que con el buen tiempo más usuarios se animan a utilizar el servicio.
Nos hemos dejado una pregunta sin responder. Una pregunta que puede ser de las más importantes, ya que puede ayudar a prevenir accidentes.
¿Podemos detectar algún “punto negro”?¿Hay alguna calle donde se produzcan más accidentes?
Para responder a esta pregunta, volvemos a la tabla inicial “Listado de Accidentes Bicicletas”. Junto a la columna “ Dirección” vamos a insertar una fila nueva con una función que nos indique cuántas veces se repite un valor en una columna. La fórmula que usaremos es “CONTAR.SI”:
=CONTAR.SI([Lugar];[@Lugar]) Figura 22: Uso de ContarSi para contar registros repetidos. Figura 22: Uso de ContarSi para contar registros repetidos.
Después, en la columna resultado, podemos filtrar por aquellas calles con un número de ocurrencias mayor. Si observamos el mayor número de ocurrencias, “11”, vemos que corresponde a la calle Alcalá. Sin embargo, al comprobar la columna Número, vemos que no hay coincidencias, luego no muestra ningún punto concreto de mayor peligrosidad.
Lo mismo pasa si filtramos por los números de ocurrencia “6” y “5”. Nos encontramos con las calles Bravo Murillo y Paseo de la Castellana, que, después de la Calle Alcalá, son la segunda y tercera calles más largas de Madrid. Figura 23: Incidentes ocurridos en la Calle Alcalá. Figura 23: Incidentes ocurridos en la Calle Alcalá. En conclusión:
Habrá que seguir atentamente las incidencias que se vayan registrando en biciMAD para poder sacar conclusiones sobre estos datos, que puedan revertir en la mejora del servicio. Hoy por hoy, los datos recogidos no señalan ningún punto negro concreto en el que haya una mayor concentración de accidentes. Sin embargo, sí que sería recomendable investigar los puntos denunciados por los propios usuarios, ya que, aunque todavía no se hayan registrado incidencias en esos puntos, es posible que acaben ocurriendo en el futuro.
No te pierdas ninguno de nuestros post. Suscríbete a LUCA Data Speaks.