gather() múltiple

27 de noviembre de 2018

Herramientas: tidyr, dplyr

En un post anterior revisé algunos de los casos de uso de la función gather() del paquete dplyr.

En este ejemplo vamos a reestructurar un dataset que contiene dos pares de coordenadas en una única fila; pero, además de las coordenadas propiamente dichas, hay otras dos variables duplicadas que tendremos que separar por filas: tiempo de inicio / tiempo de final y estación de inicio / estación de final. El hecho de que los nombres de las variables se diferencien por “inicio” y “final” ya nos da una pista de que estas variabales tienen que ser pivotadas.

Los archivos que vamos a usar están disponibles en la web de Metro Bike Share Los Ángeles.

Tareas previas: combinar archivos y arreglar columnas

Antes de cambiar la estructura de los datos propiamente dicha, vamos a dar unos pasos para combinar los diversos archivos con datos trimestrales; además, vamos a tener que hacer un poco de limpieza, ya que el nombre de algunas variables cambia de unos archivos a otros.

library(tidyverse)
library(plyr)

# obtenemos la lista de archivos CSV
temp = list.files(pattern="*.csv")

# cargamos cada archivo en un único objeto
archivos = lapply(temp, read_csv)

# Usamos rbind.fill porque las columnas no coinciden en todos los archivos, y no queremos perder ningún dato
datos <- rbind.fill(archivos) %>%
  unite(start_station_def, start_station, start_station_id) %>% # combinamos las variables que tienen nombres distintos
  unite(end_station_def, end_station, end_station_id) %>%
  mutate(end_station_def = gsub("_?NA_?","", end_station_def)) %>% # limpiamos el contenido de las nuevas variables
  mutate(start_station_def = gsub("_?NA_?","", start_station_def))

str(datos)

## 'data.frame':    565919 obs. of  14 variables:
##  $ trip_id            : int  17059131 17059130 17059129 17059128 17059127 17059126 17059125 17061379 17061378 17063646 ...
##  $ duration           : int  480 720 1020 300 300 1200 720 2880 2820 1500 ...
##  $ start_time         : chr  "1/1/2017 0:15" "1/1/2017 0:24" "1/1/2017 0:28" "1/1/2017 0:38" ...
##  $ end_time           : chr  "1/1/2017 0:23" "1/1/2017 0:36" "1/1/2017 0:45" "1/1/2017 0:43" ...
##  $ start_station_def  : chr  "3030" "3028" "3027" "3007" ...
##  $ start_lat          : num  34.1 34.1 34 34.1 34.1 ...
##  $ start_lon          : num  -118 -118 -118 -118 -118 ...
##  $ end_station_def    : chr  "3029" "3028" "3018" "3031" ...
##  $ end_lat            : chr  "34.048851" "34.058319" "34.043732" "34.044701" ...
##  $ end_lon            : chr  "-118.246422" "-118.246094" "-118.260139" "-118.252441" ...
##  $ bike_id            : chr  "6220" "6351" "5836" "6142" ...
##  $ plan_duration      : int  30 0 0 30 30 0 0 0 0 0 ...
##  $ trip_route_category: chr  "One Way" "Round Trip" "One Way" "One Way" ...
##  $ passholder_type    : chr  "Monthly Pass" "Walk-up" "Walk-up" "Monthly Pass" ...

El dataset original tiene 14 variables y 565919 observaciones (en este caso, recorridos en bici).

Pivotar datos con gather()

Antes de ponernos, vamos a ver la lógica:

  • Tenemos tres pares de variables que hay que pivotar: coordenadas, tiempo y código de estación
  • Asimismo, las coordenadas están dadas en dos variables (por lo que en total tenemos cuatro variables)
  • Como resultado de la transformación, cada fila original (un recorrido) tiene que convertirse en dos filas (puntos del recorrido), que tendrán las siguientes variables: “inicio/final”, “time”, “coordenadas” (que daremos en “latitud” y “longitud”), “station_id” (estas variables pueden tener los mismos datos para cada “trip_id”, pero la mayoría tendrá valores distintos); y el resto de variables del dataset original, en las que se replicarán los datos de la fila pivotada.
  • Como el resultado de la transformación son dos filas por cada fila original, a la hora de usar la función gather() únicamente podremos pivotar dos columnas (con esta función, obtendremos una fila nueva por cada columna o variable pivotada).

Debido a esto, vamos a crear tres subconjuntos de datos en los que retendremos únicamente las variables necesarias (lo hacemos de esta forma para no tener columnas triplicadas al final de todo el proceso), y una vez pivotado cada subconjunto de forma independiente, volveremos a unir los tres subconjuntos.

Preparamos los 3 subconjuntos

para_pivotar_coordenadas <- datos %>%
  unite(coordenada_inicio, start_lat,start_lon,sep=";") %>%
  unite(coordenada_final, end_lat,end_lon,sep=";") %>%
  select(-one_of(c("start_station_def","end_station_def", "start_time","end_time")))

para_pivotar_tiempos <- datos %>%
  select(-one_of(c("duration","bike_id","plan_duration","trip_route_category","passholder_type","start_station_def","end_station_def","start_lat","start_lon","end_lat","end_lon")))

para_pivotar_estacion <- datos %>%
  select(-one_of(c("duration","bike_id","plan_duration","trip_route_category","passholder_type","start_time","end_time","start_lat","start_lon","end_lat","end_lon")))

Pivotamos cada uno de los subconjuntos

coordenadas_pivotadas <- para_pivotar_coordenadas %>%
  gather(key = inicio_fin_coord, value = coordenadas, coordenada_inicio,coordenada_final) %>%
  mutate(inicio_fin_coord = gsub("coordenada_","",inicio_fin_coord))

estaciones_pivotadas <- para_pivotar_estacion %>%
  gather(key = inicio_fin_estacion, value = estacion, start_station_def,end_station_def) %>%
  mutate(inicio_fin_estacion = gsub("start_station_def","inicio",inicio_fin_estacion)) %>%
  mutate(inicio_fin_estacion = gsub("end_station_def","final",inicio_fin_estacion))

tiempos_pivotados <- para_pivotar_tiempos %>%
  gather(key = inicio_fin_tiempo, value = time, start_time,end_time) %>%
  mutate(inicio_fin_tiempo = gsub("start_time","inicio",inicio_fin_tiempo)) %>%
  mutate(inicio_fin_tiempo = gsub("end_time","final",inicio_fin_tiempo))

str(coordenadas_pivotadas)

## 'data.frame':    1131838 obs. of  8 variables:
##  $ trip_id            : int  17059131 17059130 17059129 17059128 17059127 17059126 17059125 17061379 17061378 17063646 ...
##  $ duration           : int  480 720 1020 300 300 1200 720 2880 2820 1500 ...
##  $ bike_id            : chr  "6220" "6351" "5836" "6142" ...
##  $ plan_duration      : int  30 0 0 30 30 0 0 0 0 0 ...
##  $ trip_route_category: chr  "One Way" "Round Trip" "One Way" "One Way" ...
##  $ passholder_type    : chr  "Monthly Pass" "Walk-up" "Walk-up" "Monthly Pass" ...
##  $ inicio_fin_coord   : chr  "inicio" "inicio" "inicio" "inicio" ...
##  $ coordenadas        : chr  "34.051941;-118.24353" "34.058319;-118.246094" "34.04998;-118.247162" "34.05048;-118.254593" ...

str(estaciones_pivotadas)

## 'data.frame':    1131838 obs. of  3 variables:
##  $ trip_id            : int  17059131 17059130 17059129 17059128 17059127 17059126 17059125 17061379 17061378 17063646 ...
##  $ inicio_fin_estacion: chr  "inicio" "inicio" "inicio" "inicio" ...
##  $ estacion           : chr  "3030" "3028" "3027" "3007" ...

str(tiempos_pivotados)

## 'data.frame':    1131838 obs. of  3 variables:
##  $ trip_id          : int  17059131 17059130 17059129 17059128 17059127 17059126 17059125 17061379 17061378 17063646 ...
##  $ inicio_fin_tiempo: chr  "inicio" "inicio" "inicio" "inicio" ...
##  $ time             : chr  "1/1/2017 0:15" "1/1/2017 0:24" "1/1/2017 0:28" "1/1/2017 0:38" ...

Volver a unir el dataset

Ahora ya podemos volver a unir el dataset, para lo que usaremos la función inner_join(). Normalmente combinaríamos las tablas por un campo clave, pero en este caso no disponemos de tal campo.

  • Los valores de la variable trip_id aparecen duplicados. Por eso, al cruzar las tablas solo por trip_id obtendremos cuatro filas en lugar de dos. Las cuatro filas tendrían este aspecto:
    • ID1;inicio;inicio
    • ID1;inicio;final
    • ID1;final:inicio
    • ID1;final;final

Las únicas filas correctas son aquellas en las que coinciden el valor de trip_id y que en ambas tablas sea la fila de inio (inicio;inicio) o de final (final;final). Por eso, a la hora aplicar inner_join() usaremos dos condiciones para identificar las filas que deben unirse.

NOTA: en el segundo inner_join() solo indicamos una tabla, porque la tabla izquierda viene dada por la concatenación de operaciones de %>%. Además, volvemos a separar las variables “latitud” y “longitud”.

datos <- inner_join(coordenadas_pivotadas, estaciones_pivotadas, by=c("trip_id" = "trip_id", "inicio_fin_coord" = "inicio_fin_estacion")) %>%
  inner_join(tiempos_pivotados, by=c("trip_id" = "trip_id","inicio_fin_coord" = "inicio_fin_tiempo")) %>%
  separate(coordenadas, c("latitud", "longitud"), sep=";") %>%
  arrange(trip_id)

datos$latitud <- as.double(datos$latitud)
datos$longitud <- as.double(datos$longitud)

str(datos)

## 'data.frame':    1131838 obs. of  11 variables:
##  $ trip_id            : int  1912818 1912818 1919661 1919661 1933383 1933383 1940317 1940317 1943980 1943980 ...
##  $ duration           : int  180 180 1980 1980 300 300 420 420 540 540 ...
##  $ bike_id            : chr  "6281" "6281" "6281" "6281" ...
##  $ plan_duration      : int  30 30 30 30 365 365 0 0 30 30 ...
##  $ trip_route_category: chr  "Round Trip" "Round Trip" "Round Trip" "Round Trip" ...
##  $ passholder_type    : chr  "Monthly Pass" "Monthly Pass" "Monthly Pass" "Monthly Pass" ...
##  $ inicio_fin_coord   : chr  "inicio" "final" "inicio" "final" ...
##  $ latitud            : num  34.1 34.1 34.1 34.1 34.1 ...
##  $ longitud           : num  -118 -118 -118 -118 -118 ...
##  $ estacion           : chr  "3014" "3014" "3014" "3014" ...
##  $ time               : chr  "7/7/2016 4:17" "7/7/2016 4:20" "7/7/2016 6:00" "7/7/2016 6:33" ...

Más posts