Caso Práctico: ¿Cómo lograr el exito rápido de un negocio de bicicletas compartidas?

Fases del análisis

Preguntar

¿En qué se diferencian los socios anuales y los ciclistas ocasionales con respecto al uso de las bicicletas de Cyclistic?

Esta es la pregunta que se nos ha asignado para guiar nuestro análisis, se utilizaran todas las fuentes que se tengan al alcance para intentar responderla.

Preparar

Se nos ha pedido trabajar con los datos recogidos durante últimos 12 meses ya que se nos ha pedido realizar el análisis del último año de operaciones, en nuestro caso, los datos abarcados son de febrero del 2023 a enero del 2024. Para revisar la fuente original de los datos revise el siguiente link: https://divvy-tripdata.s3.amazonaws.com/index.html. (Nota: Los conjuntos de datos tienen un nombre diferente porque Cyclistic es una empresa ficticia. Los datos han sido proporcionados por Motivate International Inc. bajo esta licencia.)

Los datos se encuentran comprimidos en archivos RAR, una vez que se descomprimen hemos notado que los archivos son de tipo CSV y constan de 13 columnas las cuáles son las siguientes:

ride_id
rideable_type
started_at
ended_at
start_station_name
start_station_id
end_station_name
end_station_id
start_lat
start_Ing
end_lat
end_Ing
member_casual

Los datos fueron guardados como libro de texto de Excel y almacenados en una carpeta específica, de la misma manera se les cambió el nombre utilizando la nomenclatura ‘year_numberofmonth’, ejem: ‘2023_02’.

Haciendo una revisión rápida de los datos hemos notado que en su gran mayoría los datos que nos interesan están completos y son consistentes por lo cuál podemos pasar al siguiente paso.

Procesar

Antes de dar inicio a nuestro análisis fue necesario el paso de procesar, en esta etapa se revisaron los datos tratando de hacer la mejor limpieza posible, la herramienta que se utilizó fue Microsoft Excel y PostgreSQL

Microsoft Excel

Utilizando Microsoft Excel se realizó el proceso de limpieza de nuestros datos y una vez que todos estos fueron procesados fueron importarlos a una base de datos para unir todos nuestros datos (Recordar que se están trabajando con 12 archivos de Excel, uno por cada mes).

Los pasos que se muestran a continuación se hicieron en casa uno de los 12 archivos de datos:

Ajustar las columnas
Dar un estilo uniforme a todas las celdas
Buscar duplicados con la función que incluye Excel
Asignar un tipo de dato a cada columna
Creación de las columnas:

day_trip. Se utilizó la siguiente fórmula para calcular el día que se inició el viaje:
month_trip. Se utilizó la siguiente fórmula para calcular el mes en el que se dió inicio el viaje:
year_trip. Se utilizó la siguiente fórmula para cálcular el año en el que se realizó el viaje:
weekday. Se utilizó la siguiente fórmula para calcular el día de la semana en que se inició el viaje:
duration_day. Se utilizó la siguiente formula para calcular el tiempo que las personas utilizan las bicicletas en días: =DATEDIF(C2, D2, “d”)
duration_hour. Se utilizó la siguiente formula para calcular el tiempo que las personas utilizan las bicicletas en horas: =HOUR(D2-C2)
duration_minutes. Se utilizó la siguiente formula para calcular el tiempo que las personas utilizan las bicicletas en minutos: =MINUTE(D2-C2)
duration_hour_min. Se utilizó la siguiente formula para calcular el tiempo que las personas utilizan las bicicletas en horas y minutos: =K2 & “:” & L2.

Después de crear las columnas se revisó que se hubiesen obtenido los datos correctamente utilizando la función de filtro, en algunos casos se encontró con el error de #NUM!, esto debido a que en esos casos la hora de inicio eran mayor que hora de terminación del recorrido (No se encontraron fechas de inicio mayores a las de terminación). Nota: Se tomó la decisión de corregir los valores de las horas (intercambiar la hora de inicio con la final y viceversa) si las fechas tanto de inicio como de terminación eran las mismas.

Una vez que se realizaron todos estos cálculos los datos se copiaron y volvieron a pegar en el mismo lugar, esto para obtener únicamente su valor y no depender de otras celdas, ya con todos estos pasos realizado se eliminaron las columnas started_at y ended_at, así como las columnas con muchos valores nulos o información irrelevante para nueestro análisis como son: start_lat, start_Ing, end_lat, end_Ing, start_station_name, start_station_id, end_station_name y end_station_id.

Ya con todos estos pasos realizados nos quedamos con 11 columnas: ride_id, rideable_type, member_casual, day_trip, month_trip, year_trip, weekday, duration_day, duration_hour, duration_minute, duration_hour_min.

Por último paso, se creó una carpeta llamada CSV donde guardaremos nuestros archivos ya limpios con formato csv, esto para poder exportarlos a nuestra BD.

PostgreSQL

Una vez que todos nuestros archivos fueron procesados en Excel pasamos toda la información a un solo lugar, para ello se utilizó PostgreSQL, se creó una BD con una tabla para almacenar nuestra información.

Para verificar que todos los datos fuesen exportados correctamente se hizo una lista de cuantas columnas tenía cada archivo de Excel después de la limpieza (sin incluir el encabezado)

190445
258678
426587
604819
719614
767631
771663
666341
537087
362508
224064
144866

Obteniendo un total de 5674303 columnas.

Una vez exportados nuestros datos procedimos a hacer una consulta para contar el total de columnas:

SELECT COUNT(ride_id) FROM data

obteniendo como resultado: 5674303

Una vez comprobado que todos los datos fueron exportados correctamente, se hicieron unos pasos adicionales para continuar con la limpieza de nuestros datos.

Se hizo una consulta para buscar recorridos con tiempo 0, es decir, recorridos que ni siquiera tienen un minuto, se omitió este paso en Excel porque era más fácil hacerlo en una BD una vez que todos los datos estuviesen juntos.

Para ello se utilizó la consulta: SELECT FROM data WHERE duration_hour_min = ‘0:0’;

Con esta consulta detectamos un total de 147480 filas que contaban con recorrido 0, para que esto no afectase nuestro análisis de decidió por eliminar estas filas utilizando la consulta: DELETE FROM data WHERE duration_hour_min = ‘0:0’; y obteniendo el siguiente mensaje: DELETE 147480.

Una vez realizado todos estos pasos se dió por concluído el proceso de procesamiento de los datos.

Analizar

Para el paso de analizar se decidió utilizar tanto RStudio como Postgre. Antes de dar inicio al análisis fue necesario recordar nuestra pregunta inicial para enfocarnos en dar respuesta a ella.

¿En qué se diferencian los socios anuales y los ciclistas ocasionales con respecto al uso de las bicicletas de Cyclistic?

Se realizó una consulta para conocer cuantos viajes realizaron tanto los ciclistas que son socios y los ciclistas ocasionales:

SELECT member_casual, COUNT(*) AS cantidad FROM data GROUP BY member_casual ORDER BY cantidad DESC;

Y los resultados nos mostraron que los ciclistas que son miembros realizaron más viajes durante este periodo (febrero 2023 - enero 2024)

member_casual	cantidad
member	3536067
casual	1990756

(2 filas)

Se realizó una consulta en donde se sumaron los días, horas y minutos para ver cuanto tiempo utilizó el servicio cada tipo de miembro:

SELECT member_casual, SUM(duration_day) AS total_duration_day, SUM(duration_hour) AS total_duration_hour, SUM(duration_minutes) AS total_duration_minutes, FROM data GROUP BY member_casual ORDER BY member_casual;

member_casual	total_duration_day	total_duration_hour	total_duration_minutes
casual	22928	201939	30266043
member	9405	56064	38909856

(2 filas)

Donde descubrimos que los miembros casuales, pese a no realizar tantos viajes obtuvieron cifras mayores de tiempo a las de los ciclistas anuales.

Después se realizó una consulta para ver en que día de la semana los ciclistas iniciaban sus viajes:

SELECT member_casual, weekday, SUM(duration_day) AS total_duration_day, SUM(duration_hour) AS total_duration_hour, SUM(duration_minutes) AS total_duration_minutes FROM data GROUP BY member_casual, weekday ORDER BY total_duration_day;

member_casual	weekday	total_duration_day	total_duration_hour	total_duration_minutes
casual	sßbado	6056	48177	6722380
casual	viernes	4346	28605	4516579
casual	domingo	3342	42514	5497024
casual	jueves	2640	20890	3612704
casual	miΘrcoles	2284	18621	3247620
member	sßbado	2197	9587	5514059
casual	lunes	2141	23038	3367945
casual	martes	2119	20094	3301791
member	viernes	1893	8596	5557363
member	jueves	1386	7371	6127421
member	miΘrcoles	1156	8120	6059918
member	martes	966	7503	5892648
member	domingo	930	8419	4721579
member	lunes	877	6468	5036868

(14 filas)

Donde nuevamente descubrimos el día sábado es el preferido tanto de los ciclistas casuales como de los miembros, es importante analizar que con esta consulta reafirmamos que los ciclistas casuales suelen utilizar mucho más el servicio que los propios miembros.

Consulta para análizar el aproximado de horas y minutos por día de la semana de cada ciclista:

SELECT member_casual, weekday, AVG(duration_hour) AS total_duration_hour, AVG(duration_minutes) AS total_duration_minutes FROM data GROUP BY member_casual, weekday;

member_casual	weekday	total_duration_hour	total_duration_minutes
casual	domingo	0.13163737022507222190	17.0205998829587290
casual	sßbado	0.12120853795990660977	16.9128391433862008
casual	lunes	0.10137376902023251107	14.8199183307078299
casual	viernes	0.09470192317242338265	14.9529354119972323
casual	martes	0.08494861399407295925	13.9585233974372524
casual	jueves	0.07941033360703098866	13.7331751969102576
casual	miΘrcoles	0.07713657245353206050	13.4531053880854836
member	domingo	0.02151282234737369298	12.0649115365352576
member	sßbado	0.02107565648460599932	12.1218747595546127
member	viernes	0.01681464572073525931	10.8707643074130366
member	miΘrcoles	0.01416719735499123274	10.5729130863379016
member	martes	0.01359342558672942506	10.6758992532040481
member	lunes	0.01349403220792659647	10.5082960759237508
member	jueves	0.01283750481123416647	10.6716587393782753

(14 filas)

Con esta consulta seguímos rectificando que los ciclistas casuales usan en mayor medida del servicio teniendo un mayor promedio de horas y de minutos.

Después se realizó una consulta para ver el aproximado de viajes que los ciclistas realizaban por días de semana:

SELECT member_casual, weekday, COUNT(*) AS total FROM data GROUP BY member_Casual, weekday ORDER BY total DESC;

member_casual	weekday	total
member	jueves	574177
member	miΘrcoles	573155
member	martes	551958
member	viernes	511221
member	lunes	479323
member	sßbado	454885
casual	sßbado	397472
member	domingo	391348
casual	domingo	322963
casual	viernes	302053
casual	jueves	263064
casual	miΘrcoles	241403
casual	martes	236543
casual	lunes	227258

(14 filas)

Donde revisamos que los miembros realizan la mayoría de sus viajes entre semana y los casuales en fin de semana, esto podría ser un indicativo de que los miembros utilizan este medio de transporte para trasladarse a su trabajo mientras que los ciclistas ocasionales usan las bicis como un medio recreativo.

Consulta para ver el tiempo de uso de los ciclistas a través de los meses:

SELECT member_casual, month_trip, COUNT(*) AS total FROM data GROUP BY member_casual, month_trip ORDER BY total DESC;

member_casual	month_trip	total
member	agosto	449596
member	julio	424926
member	junio	407790
member	septiembre	396296
member	mayo	359582
member	octubre	352470
casual	julio	322571
casual	agosto	303754
casual	junio	293048
member	abril	269087
member	noviembre	258872
casual	septiembre	255775
casual	mayo	227374
member	marzo	189322
casual	octubre	172965
member	diciembre	168724
member	febrero	142627
casual	abril	142521
member	enero	116775
casual	noviembre	96290
casual	marzo	60279
casual	diciembre	50587
casual	febrero	41818
casual	enero	23774

(24 filas)

Acá vemos que la mayoría de los viajes se realizan en verano y inicios de otoño, así como también vemos una dismunición de los viajes a finales e inicios de año.

Por último, vamos a realizar una comparativa entre los viajes que se realizan por bicicleta:

SELECT rideable_trip, member_casual, COUNT(*) AS total FROM data GROUP BY member_casual, rideable_trip ORDER BY total DESC;

rideable_trip	member_casual	total
classic_bike	member	1779793
electric_bike	member	1756274
electric_bike	casual	1053780
classic_bike	casual	861132
docked_bike	casual	75844

(5 filas)

Descubrimos que la bicicleta favorita para los ciclistas que son miembros son las clásicas mientras que ciclistas casuales prefieren la eléctrica. Caso curioso, no hay ningún dato de que los miembros hayan utilizado la docked_bike durante el año mientras que los casuales la tienen como última opción.

Vamos a utilizar RStudio para crear las visualizaciones de nuestro análisis

Lo primero que se tuvo que realizar fue la exportación de los datos que teníamos en nuestra BD, esto es relativamente fácil ya que la mayoria de los sistemas gestores de base de datos cuentan con la opción de exportar tus datos y el formato predeterminado es CSV.

Cargamos la librera tidyverse

## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.4     ✔ readr     2.1.5
## ✔ forcats   1.0.0     ✔ stringr   1.5.1
## ✔ ggplot2   3.4.4     ✔ tibble    3.2.1
## ✔ lubridate 1.9.3     ✔ tidyr     1.3.1
## ✔ purrr     1.0.2     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

Cargamos nuestro conjunto de datos

## Rows: 5526823 Columns: 12
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (5): ride_id, rideable_trip, member_casual, month_trip, weekday
## dbl  (6): id, day_trip, year_trip, duration_day, duration_hour, duration_min...
## time (1): duration_hour_min
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

Miramos los títulos de nuestros datos

##  [1] "id"                "ride_id"           "rideable_trip"    
##  [4] "member_casual"     "day_trip"          "month_trip"       
##  [7] "year_trip"         "weekday"           "duration_day"     
## [10] "duration_hour"     "duration_minutes"  "duration_hour_min"

Viajes realizados durante el periodo febrero 2023 - enero 2024

Los miembros realizan mas viajes que los ciclistas ocasionales.

Viajes realizados en cada tipo de bicicleta

Las miembros casuales prefieren las bicicletas eléctricas mientras que los miembros utilizan con mayor frecuencia tanto la clásica como la eléctrica, la bicicleta de tipo docked tiene un uso muy bajo.

Viajes realizados a través de los meses

Obtemos resultados como el hecho de que los finales e inicios de año es cuando el servicio disminuye en ambos tipo de ciclistas, además se muestra que los ciclistas casuales utilizan más el servicio en verano (junio-julio) lo que puede ser un indicativo de que estas son utilizadas en vacaciones. Por otra parte los ciclistas que son miembros muestran un uso del servicio similar a través del tiempo, teniendo su uso más bajo, como ya se mencionó anteriormente, a inicios y finales del año.

Viajes realizados a través de las semanas

Obtenemos que en días de la semana los miembros tienen un uso de las bicicletas promedio, mientras que los fines de semana, esto propone que el uso de las bicicletas de los miembros puede estar relacionado con sus trabajos, usando estas como medio de traslado. Por otra parte, los miembros casuales utilizan el servicio de bicicletas mucho más los fines de semana.

Tiempo de uso

Ahora vamos a mostrar el promedio de uso de las bicicletas por días y horas y minutos de acuerdo a cada tipo de bicicletas.

Obtenemos las siguientes conclusiones:

En promedio, los ciclistas que son miembros realizan más viajes tanto en días, horas y minutos.
Los ciclistas casuales, pese a no realizar tantos viajes como los miembros, tienen un mayor porcentaje de tiempo invertido por viaje ya sea en días, horas y minutos.
La bicicleta de tipo docked, pese a tener una cantidad de viaje de 5 cifras (muy por debajo de las 7 cifras de las otras bicicletas) es la más utilizada para viajes largos (el viaje más largo registrado es de 69 días).

Conclusiones

¿En qué se diferencian los socios anuales y los ciclistas ocasionales con respecto al uso de las bicicletas de Cyclistic?

Los socios anuales realizan más viajes con menos recorrido de tiempo manteniendo un uso medio durante los días de semana y teniendo un menor uso durante los fines de semana, así mismo com respecto a los meses diciembre, enero y febrero son los meses con menos uso, esto probablemente debido a las fiestas. Por otro lado los ciclistas ocasionales realizan menos viajes con mayor recorrido de tiempo teniendo su mayor uso los fines de semana y en los meses de verano como Junio-Julio-Agosto.

Recomendaciones

Obtener más datos de las rutas de traslado para poder analizar cuales son las rutas más concurridas.
Elaborar algún tipo de promoción para invitar a la gente a utilizar más la bicicleta de tipo docked.

Caso Práctico: ¿Cómo lograr el exito rápido de un negocio de bicicletas compartidas?

Jonas Custodio Ramírez

2024-02-26

Introducción

Escenario

Equipo

Fases del análisis

Preguntar

Preparar

Procesar

Microsoft Excel

PostgreSQL

Analizar

Compartir

Viajes realizados durante el periodo febrero 2023 - enero 2024

Viajes realizados en cada tipo de bicicleta

Viajes realizados a través de los meses

Viajes realizados a través de las semanas

Tiempo de uso

Conclusiones

Recomendaciones