Cyclistic Bike-Ride Company - A Comprehensive Case Study

About this project

This project is case study and part of the “Capstone” course within the Google Data Analytics Professional Certificate program.

The scenario

You are a junior data analyst working on the marketing analytics team at a fictional company called “Cyclistic”, a bike-share company based in Chicago. The director of marketing believes the company´s future success depends on maximizing the number of annual memberships.Therefore, your team want to understand how casual riders and annual members use Cyclistic bikes differently.

The goal

The aim of this analysis process is to provide comprehensive insights into the differing behaviors of casual riders and annual members when using Cyclistic bikes.

The data

The datasets analyzed in this project have been imported from the Index of bucket “divvy-tripdata” on April 29, 2024. These datasets have been publicly available under this license.

The process

In order to address this objective, the analysis process will be divided into the following phases:

1. Ask

2. Prepare

3. Process

4. Analyze

5. Share

Phase 1. ASK

Objectives:

• Identify relevant facts and stakeholders.

• Articulate the business task or question.

• Define guiding questions for the analysis.

Facts and Stakeholders

Facts:

• Cyclistic bike-share company wants to improve its earnings. Customers of this company are categorized into casual riders and annual members. Finance analysts have concluded that annual members are much more profitable than casual riders.

• Lily Moreno, the director of marketing has set a clear goal for the marketing team: Design marketing strategies aimed at converting casual riders into annual members. Three questions will guide the marketing program and they need to be answered in the following order:

  1. How do annual members and casual riders use Cyclistic bikes differently? (Moreno has assigned you this question)

  2. Why would casual riders buy Cyclistic annual memberships?

  3. How can Cyclistic use digital media to influence casual riders to become annual members?

• The executive team will decide whether to approve the recommended marketing program.

Stakeholders: the director of marketing, the marketing team, and the executive team.

The Business task

Identify differences in behavioral patterns between Cyclistic casual riders and annual members in order to design a focused marketing campaign that could motivate casual riders to buy annual memberships.

Guiding questions for the analysis

In alignment with the business task, the following guiding questions will be answered for all customers, as well as separately for each customer type.

• How many rides were taken on Cyclistic bikes during the last twelve months?

• What type of bike was the most popular?

• Which months present the highest and the lowest demand for Cyclistic bikes?

• What days of the week have the highest and the lowest demand for Cyclistic bikes?

• How long, in terms of distance and time, do customers usually ride their bikes?

Phase 2. PREPARE

Objectives:

• Determine the credibility of the data.

• Conduct exploratory data analysis to and understand the content and structure of the original dataset.

Credibility of the data.

The data should be reliable as it is provided by the company that operates the bike-sharing company. Also data can be considered relevant as it contains information regarding the last 12 months of customer activity (April 2023 to March 2024).

However, we must be aware for any data inaccuracies caused by biases, errors, missing values, and or outliers.

Exploratory Data Analysis (EDA).

We begin this step by preparing the environment loading the library(tidyverse).

library(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.5.1     ✔ 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

library(skimr)
library(ggplot2)
library(lubridate)

lista_datasets <- list()

archivos <- c(
  "Cyclistic_202304-tripdata.csv",
  "Cyclistic_202305-tripdata.csv",
  "Cyclistic_202306-tripdata.csv",
  "Cyclistic_202307-tripdata.csv",
  "Cyclistic_202308-tripdata.csv",
  "Cyclistic_202309-tripdata.csv",
  "Cyclistic_202310-tripdata.csv",
  "Cyclistic_202311-tripdata.csv",
  "Cyclistic_202312-tripdata.csv",
  "Cyclistic_202401-tripdata.csv",
  "Cyclistic_202402-tripdata.csv",
  "Cyclistic_202403-tripdata.csv"
)

for (archivo in archivos) {
  ruta <- file.path("C:\\Users\\pc\\OneDrive\\Documents", archivo)
  df <- read_csv(ruta)
  lista_datasets[[archivo]] <- df
}

## Rows: 426590 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ 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.
## Rows: 604827 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ 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.
## Rows: 719618 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ 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.
## Rows: 767650 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ 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.
## Rows: 771693 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ 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.
## Rows: 666371 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ 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.
## Rows: 537113 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ 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.
## Rows: 362518 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ 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.
## Rows: 224073 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ 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.
## Rows: 144873 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ 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.
## Rows: 223164 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ 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.
## Rows: 301687 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ 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.

df_merged <- do.call(rbind, lista_datasets)

glimpse(df_merged)

## Rows: 5,750,177
## Columns: 13
## $ ride_id            <chr> "8FE8F7D9C10E88C7", "34E4ED3ADF1D821B", "5296BF07A2…
## $ rideable_type      <chr> "electric_bike", "electric_bike", "electric_bike", …
## $ started_at         <dttm> 2023-04-02 08:37:28, 2023-04-19 11:29:02, 2023-04-…
## $ ended_at           <dttm> 2023-04-02 08:41:37, 2023-04-19 11:52:12, 2023-04-…
## $ start_station_name <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
## $ start_station_id   <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
## $ end_station_name   <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
## $ end_station_id     <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
## $ start_lat          <dbl> 41.80, 41.87, 41.93, 41.92, 41.91, 41.91, 41.93, 42…
## $ start_lng          <dbl> -87.60, -87.65, -87.66, -87.65, -87.65, -87.63, -87…
## $ end_lat            <dbl> 41.79, 41.93, 41.93, 41.91, 41.91, 41.92, 41.91, 41…
## $ end_lng            <dbl> -87.60, -87.68, -87.66, -87.65, -87.63, -87.65, -87…
## $ member_casual      <chr> "member", "member", "member", "member", "member", "…

head(df_merged, 3)

## # A tibble: 3 × 13
##   ride_id          rideable_type started_at          ended_at           
##   <chr>            <chr>         <dttm>              <dttm>             
## 1 8FE8F7D9C10E88C7 electric_bike 2023-04-02 08:37:28 2023-04-02 08:41:37
## 2 34E4ED3ADF1D821B electric_bike 2023-04-19 11:29:02 2023-04-19 11:52:12
## 3 5296BF07A2F77CB5 electric_bike 2023-04-19 08:41:22 2023-04-19 08:43:22
## # ℹ 9 more variables: start_station_name <chr>, start_station_id <chr>,
## #   end_station_name <chr>, end_station_id <chr>, start_lat <dbl>,
## #   start_lng <dbl>, end_lat <dbl>, end_lng <dbl>, member_casual <chr>

skim(df_merged)

Data summary

Name	df_merged
Number of rows	5750177
Number of columns	13
_______________________
Column type frequency:
character	7
numeric	4
POSIXct	2
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
ride_id	0	1.00	16	16	0	5750177	0
rideable_type	0	1.00	11	13	0	3	0
start_station_name	874450	0.85	3	64	0	1601	0
start_station_id	874450	0.85	3	35	0	1532	0
end_station_name	929226	0.84	3	64	0	1614	0
end_station_id	929226	0.84	3	36	0	1541	0
member_casual	0	1.00	6	6	0	2	0

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
start_lat	0	1	41.90	0.04	41.63	41.88	41.90	41.93	42.07	▁▁▆▇▁
start_lng	0	1	-87.65	0.03	-87.94	-87.66	-87.64	-87.63	-87.46	▁▁▅▇▁
end_lat	7566	1	41.90	0.05	0.00	41.88	41.90	41.93	42.18	▁▁▁▁▇
end_lng	7566	1	-87.65	0.07	-88.16	-87.66	-87.64	-87.63	0.00	▇▁▁▁▁

Variable type: POSIXct

skim_variable	n_missing	complete_rate	min	max	median	n_unique
started_at	0	1	2023-04-01 00:00:02	2024-03-31 23:59:11	2023-08-15 15:05:34	4845051
ended_at	0	1	2023-04-01 00:03:10	2024-04-01 02:35:26	2023-08-15 15:23:36	4857025

sum(is.na(df_merged))

## [1] 3622484

Phase 3. PROCESS

Objectives:

• Organize data in order to facilitate analysis.

• Handle erroneous and missing values accordingly while ensuring the integrity of the data.

df_merged <- df_merged %>% 
  rename(customer_type = member_casual)

observaciones_nulas <- sum(is.na(df_merged))

porcentaje_nulas <- (observaciones_nulas / nrow(df_merged)) * 100

cat("Número de filas con observaciones nulas:", observaciones_nulas, "\n")

## Número de filas con observaciones nulas: 3622484

cat("Porcentaje de filas con observaciones nulas:", porcentaje_nulas, "%")

## Porcentaje de filas con observaciones nulas: 62.99778 %

Data removal 1

A significant percentage of rows (over 60%) have missing data, particularly in columns regarding stations’ names and id’s. Considering that, at the time, it is not possible to gather that information elsewhere, it has been decided to filter out the following columns: * start_station_name * start_station_id * end_station_name * end_station_id

Additionally, the following columns will be filtered

columnas_excluir <- c(
  "start_station_name",
  "start_station_id",
  "end_station_name",
  "end_station_id"
)

df_filtered <- select(df_merged, -one_of(columnas_excluir))

glimpse(df_filtered)

## Rows: 5,750,177
## Columns: 9
## $ ride_id       <chr> "8FE8F7D9C10E88C7", "34E4ED3ADF1D821B", "5296BF07A2F77CB…
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", "elec…
## $ started_at    <dttm> 2023-04-02 08:37:28, 2023-04-19 11:29:02, 2023-04-19 08…
## $ ended_at      <dttm> 2023-04-02 08:41:37, 2023-04-19 11:52:12, 2023-04-19 08…
## $ start_lat     <dbl> 41.80, 41.87, 41.93, 41.92, 41.91, 41.91, 41.93, 42.00, …
## $ start_lng     <dbl> -87.60, -87.65, -87.66, -87.65, -87.65, -87.63, -87.66, …
## $ end_lat       <dbl> 41.79, 41.93, 41.93, 41.91, 41.91, 41.92, 41.91, 41.99, …
## $ end_lng       <dbl> -87.60, -87.68, -87.66, -87.65, -87.63, -87.65, -87.65, …
## $ customer_type <chr> "member", "member", "member", "member", "member", "membe…

Calculated fields

Considering the type of available data at this point of the process, it has been noted that it is possible to add columns with calculated measures. The following columns will be added to the dataframe:

• ride_month: this column will display the month on which each ride was started.
• ride_day: this column will show the day of the week on which each ride was
• ride_distance: it will specify the distance in kilometers of each ride.started.
• ride_duration: it will specify the time duration of each ride.

Add ride_month column

#Set locale to English
Sys.setlocale("LC_TIME", "English_United States")

## [1] "English_United States.1252"

# Copy information from started_at to ride_month
df_filtered <- df_filtered %>%
  mutate(ride_month = started_at)

# Modify ride_month to only show the name of the month
df_filtered <- df_filtered %>%
  mutate(ride_month = format(as.Date(ride_month), "%B"))

unique_months <- unique(df_filtered$ride_month)
print(unique_months)

##  [1] "April"     "May"       "June"      "July"      "August"    "September"
##  [7] "October"   "November"  "December"  "January"   "February"  "March"

null_ride_month <- df_filtered$ride_month[is.na(df_filtered$ride_month)]
print(null_ride_month)

## character(0)

Add ride_day column

To create the “ride_day” column, the information will be extracted from the “started_at” column, so this new column will show the day of the week on which each ride was started.

#Apply the weekdays() function to extract the day of the week from the column "started_at"
df_filtered$ride_day <- weekdays(df_filtered$started_at)

unique_days <- unique(df_filtered$ride_day)
print(unique_days)

## [1] "Sunday"    "Wednesday" "Tuesday"   "Thursday"  "Monday"    "Saturday" 
## [7] "Friday"

null_ride_day <- df_filtered$ride_day[is.na(df_filtered$ride_day)]
print(null_ride_day)

## character(0)

Add ride_distance column

The “ride_distance” column will display the distance of each ride in kilometers. To create this column, first we need to establish a function that applies the Haversine formula, which is a widely used formula in different industries to determine the distance between two points on Earth based on their coordinates.

calculate_distance <- function (lat1, lon1, lat2, lon2) {
  ER <- 6371 #Earth radius in kilometers
  #Convert degrees to radians
  lat1_rad <- degrees_to_radians(lat1)
  lon1_rad <- degrees_to_radians(lon1)
  lat2_rad <- degrees_to_radians(lat2)
  lon2_rad <- degrees_to_radians(lon2)
  
  #Differences in coordinates
  d_lat <- lat2_rad - lat1_rad
  d_lon <- lon2_rad - lon1_rad
  
  #Haversine formula
  a <- sin(d_lat/2)^2 + cos(lat1_rad) * cos(lat2_rad) * sin(d_lon/2)^2
  c <- 2 * atan2(sqrt(a), sqrt(1 - a))
  distance <- ER * c
  
  return(round(distance, 2))
}

#Function to convert degrees to radians 
degrees_to_radians <- function(degrees) {
  return(degrees * pi / 180)
}

#Function to calculate distance between starting and ending points.
calculate_ride_distance <- function(start_lat, start_lng, end_lat, end_lng) {
  distance <- calculate_distance(start_lat, start_lng, end_lat, end_lng)
  return(distance)
}

#Add ride_distance column to the dataframe applying the "calculate_ride_distance" function. 
df_filtered <- df_filtered %>% 
  mutate(ride_distance = calculate_ride_distance(start_lat, start_lng, end_lat, end_lng))

glimpse(df_filtered)

## Rows: 5,750,177
## Columns: 12
## $ ride_id       <chr> "8FE8F7D9C10E88C7", "34E4ED3ADF1D821B", "5296BF07A2F77CB…
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", "elec…
## $ started_at    <dttm> 2023-04-02 08:37:28, 2023-04-19 11:29:02, 2023-04-19 08…
## $ ended_at      <dttm> 2023-04-02 08:41:37, 2023-04-19 11:52:12, 2023-04-19 08…
## $ start_lat     <dbl> 41.80, 41.87, 41.93, 41.92, 41.91, 41.91, 41.93, 42.00, …
## $ start_lng     <dbl> -87.60, -87.65, -87.66, -87.65, -87.65, -87.63, -87.66, …
## $ end_lat       <dbl> 41.79, 41.93, 41.93, 41.91, 41.91, 41.92, 41.91, 41.99, …
## $ end_lng       <dbl> -87.60, -87.68, -87.66, -87.65, -87.63, -87.65, -87.65, …
## $ customer_type <chr> "member", "member", "member", "member", "member", "membe…
## $ ride_month    <chr> "April", "April", "April", "April", "April", "April", "A…
## $ ride_day      <chr> "Sunday", "Wednesday", "Wednesday", "Wednesday", "Wednes…
## $ ride_distance <dbl> 1.11, 7.12, 0.00, 1.11, 1.66, 1.99, 2.37, 1.11, 1.11, 0.…

null_ride_distance <- sum(is.na(df_filtered$ride_distance))
print(null_ride_distance)

## [1] 7566

Data removal 2

A total of 7,566 rows where identified to have NA values on column ride_distance which was probably caused by error in the coordinates data which was taken as input to create this column.

percentage_NA_rdistance <- (null_ride_distance / nrow(df_filtered)) * 100

cat("Percentage of rows with NA values on ride_distance column:", percentage_NA_rdistance, "%")                         

## Percentage of rows with NA values on ride_distance column: 0.1315786 %

The percentage or rows with NA values on row ride_distance are considered of no statistical significance, representing less than 1% of total rows, ergo these rows will be removed from the dataframe in order to proceed with the Process stage.

df_filtered <- df_filtered[!is.na(df_filtered$ride_distance), ]

null_ride_distance <- sum(is.na(df_filtered$ride_distance))
print(null_ride_distance)

## [1] 0

Add ride_duration column

The ride_duration column will display the duration for each ride in hours, minutes, and seconds. This column will take as input the values in the “started_at” and “ended_at” columns and will estimate the difference between starting and ending times.

library(hms)

## 
## Attaching package: 'hms'

## The following object is masked from 'package:lubridate':
## 
##     hms

# Calculate ride duration using difftime
ride_duration <- difftime(df_filtered$ended_at, df_filtered$started_at, units = "secs")

# Convert ride duration to numeric value in seconds
ride_duration_seconds <- as.numeric(ride_duration)

# Extract hours, minutes, and seconds from ride duration
hours <- as.integer(ride_duration_seconds / 3600)
minutes <- as.integer((ride_duration_seconds %% 3600) / 60)
seconds <- as.integer(ride_duration_seconds %% 60)

# Format ride duration as HH:MM:SS
ride_duration_formatted <- sprintf("%02d:%02d:%02d", hours, minutes, seconds)

# Add ride_duration column to df_filtered
df_filtered$ride_duration <- ride_duration_formatted

glimpse(df_filtered)

## Rows: 5,742,611
## Columns: 13
## $ ride_id       <chr> "8FE8F7D9C10E88C7", "34E4ED3ADF1D821B", "5296BF07A2F77CB…
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", "elec…
## $ started_at    <dttm> 2023-04-02 08:37:28, 2023-04-19 11:29:02, 2023-04-19 08…
## $ ended_at      <dttm> 2023-04-02 08:41:37, 2023-04-19 11:52:12, 2023-04-19 08…
## $ start_lat     <dbl> 41.80, 41.87, 41.93, 41.92, 41.91, 41.91, 41.93, 42.00, …
## $ start_lng     <dbl> -87.60, -87.65, -87.66, -87.65, -87.65, -87.63, -87.66, …
## $ end_lat       <dbl> 41.79, 41.93, 41.93, 41.91, 41.91, 41.92, 41.91, 41.99, …
## $ end_lng       <dbl> -87.60, -87.68, -87.66, -87.65, -87.63, -87.65, -87.65, …
## $ customer_type <chr> "member", "member", "member", "member", "member", "membe…
## $ ride_month    <chr> "April", "April", "April", "April", "April", "April", "A…
## $ ride_day      <chr> "Sunday", "Wednesday", "Wednesday", "Wednesday", "Wednes…
## $ ride_distance <dbl> 1.11, 7.12, 0.00, 1.11, 1.66, 1.99, 2.37, 1.11, 1.11, 0.…
## $ ride_duration <chr> "00:04:09", "00:23:10", "00:02:00", "00:03:39", "00:04:5…

null_ride_duration <- sum(is.na(df_filtered$ride_duration))
print(null_ride_duration)

## [1] 0

Add sub-columns for ride duration

For an easier exploration of the data regarding ride durations, three subcolumns will be derived from the “ride_duration” column. Each of these new columns will display ride duration for each ride in different time units: seconds, minutes and hours.

# Define a function to convert "HH:MM:SS" format to seconds
hhmmss_to_seconds <- function(hhmmss) {
  time_parts <- as.numeric(unlist(strsplit(hhmmss, ":")))
  seconds <- time_parts[1] * 3600 + time_parts[2] * 60 + time_parts[3]
  return(seconds)
}

# Apply the function to the "ride_duration" column
df_filtered$rd_secs <- sapply(df_filtered$ride_duration, hhmmss_to_seconds)

# Convert rd_secs to time format
df_filtered$rd_time <- as_hms(df_filtered$rd_secs)

# Add rd_mins column
df_filtered$rd_mins <- df_filtered$rd_secs / 60

# Add rd_hours column
df_filtered$rd_hours <- df_filtered$rd_secs / 3600

Review data integrity

Before ending the Process phase, a review of the data in all columns will be performed to ensure the integrity of the data in all current columns.

Checking for nulls in all the dataframe

null_dataframe <- sum(is.na(df_filtered))
print(null_dataframe)

## [1] 0

Checking “chr” datatypes

unique_ride_id <- df_filtered %>%
  distinct(ride_id) %>%
  nrow()

unique_rideable_type <- df_filtered %>%
  distinct(rideable_type) %>%
  nrow()

unique_customer_type <- df_filtered %>%
  distinct(customer_type) %>%
  nrow()

unique_ride_day<- df_filtered %>%
  distinct(ride_day) %>%
  nrow()

unique_ride_month<- df_filtered %>%
  distinct(ride_month) %>%
  nrow()

cat("Unique values in ride_id column:", unique_ride_id, "\n")

## Unique values in ride_id column: 5742611

cat("Unique values in rideable_type column:", unique_rideable_type, "\n")

## Unique values in rideable_type column: 3

cat("Unique values in customer_type column:", unique_customer_type, "\n")

## Unique values in customer_type column: 2

cat("Unique values in ride_day column:", unique_ride_day, "\n")

## Unique values in ride_day column: 7

cat("Unique values in ride_month column:", unique_ride_month, "\n")

## Unique values in ride_month column: 12

Exploration of the ride_distance column

min_ride_distance <- min(df_filtered$ride_distance)
max_ride_distance <- max(df_filtered$ride_distance)

cat("Minimum value in the ride_distance column:", min_ride_distance, "\n")

## Minimum value in the ride_distance column: 0

cat("Maximum value in the ride_distance column:", max_ride_distance, "\n")

## Maximum value in the ride_distance column: 9815.43

Data removal 3

Now we can observe that the minimum distance in ride_distance is zero kilometers and the maximum value is 9815.43.

In this context 0.0 is a conceivable value for ride distance, considering that this field was estimated by calculating the distance between the starting and the ending stations of each ride by applying the Haversine formula with the given coordinates for each station. With this in consideration, a value of 0.0 may appear for those customers who started and ended their rides at the same station.

To corroborate or reject this hypothesis further exploration is needed to confirm if there is, or isn’t, a time lapse for this rides and also to confirm if their starting and ending coordinates match with each other.

zero_distance_rows <- df_filtered[df_filtered$ride_distance == 0, ]

print(zero_distance_rows)

## # A tibble: 310,835 × 17
##    ride_id       rideable_type started_at          ended_at            start_lat
##    <chr>         <chr>         <dttm>              <dttm>                  <dbl>
##  1 5296BF07A2F7… electric_bike 2023-04-19 08:41:22 2023-04-19 08:43:22      41.9
##  2 81E158FE63D9… electric_bike 2023-04-19 17:35:40 2023-04-19 17:36:11      41.9
##  3 D0851F635767… electric_bike 2023-04-20 11:31:46 2023-04-20 11:33:53      41.8
##  4 47CCDCDB305A… electric_bike 2023-04-11 07:08:14 2023-04-11 07:22:51      42.0
##  5 843BDBA1059F… electric_bike 2023-04-15 19:09:33 2023-04-15 19:16:04      41.9
##  6 F4320AC8615C… electric_bike 2023-04-22 03:52:35 2023-04-22 03:53:43      41.9
##  7 4508213936D6… electric_bike 2023-04-22 04:18:01 2023-04-22 04:19:04      41.9
##  8 FE299FE26DC2… electric_bike 2023-04-04 14:20:05 2023-04-04 14:23:11      42.0
##  9 3196891443F7… electric_bike 2023-04-04 14:36:26 2023-04-04 14:38:58      42.0
## 10 D1DE4C2765E6… electric_bike 2023-04-19 11:01:25 2023-04-19 11:01:56      41.8
## # ℹ 310,825 more rows
## # ℹ 12 more variables: start_lng <dbl>, end_lat <dbl>, end_lng <dbl>,
## #   customer_type <chr>, ride_month <chr>, ride_day <chr>, ride_distance <dbl>,
## #   ride_duration <chr>, rd_secs <dbl>, rd_time <time>, rd_mins <dbl>,
## #   rd_hours <dbl>

The previews step confirms the hypothesis regarding rides with ride_distance equal to zero.

With regards to the maximum value found in ride_distance, 9815.43 kilometers, we need to confirm if this is an outlier and also if there are other extreme values within this dataframe.

top_10_distance <- df_filtered %>% 
  arrange(desc(ride_distance)) %>% 
  head(10)

# Print the resulting dataframe
print(top_10_distance)

## # A tibble: 10 × 17
##    ride_id       rideable_type started_at          ended_at            start_lat
##    <chr>         <chr>         <dttm>              <dttm>                  <dbl>
##  1 873D50153BBC… electric_bike 2023-06-15 12:38:05 2023-06-15 12:38:41      41.9
##  2 43107577DF9B… classic_bike  2023-08-21 18:43:22 2023-08-21 22:05:55      41.9
##  3 ADFF57D27B5B… classic_bike  2023-06-15 09:38:07 2023-06-15 09:42:57      41.8
##  4 500867C89116… electric_bike 2023-06-15 01:33:07 2023-06-15 03:12:36      41.9
##  5 587C8B39F4D4… electric_bike 2023-10-27 23:29:00 2023-10-27 23:31:36      41.9
##  6 EFE648FE9A56… electric_bike 2023-06-18 11:36:41 2023-06-18 13:34:42      41.7
##  7 BF9ED170B88E… electric_bike 2023-04-13 17:09:46 2023-04-13 19:39:45      41.8
##  8 5EE1CFF94051… classic_bike  2023-06-01 11:58:36 2023-06-01 14:08:58      41.8
##  9 D1D23BC89760… electric_bike 2023-08-25 11:55:10 2023-08-25 13:25:15      42.0
## 10 363C12D84961… electric_bike 2024-03-30 14:27:35 2024-03-30 16:02:49      41.8
## # ℹ 12 more variables: start_lng <dbl>, end_lat <dbl>, end_lng <dbl>,
## #   customer_type <chr>, ride_month <chr>, ride_day <chr>, ride_distance <dbl>,
## #   ride_duration <chr>, rd_secs <dbl>, rd_time <time>, rd_mins <dbl>,
## #   rd_hours <dbl>

Data removal 4

The previews exploration confirms that the row with a value of 9815.43 on its ride_distance column is an outlier and that there are other two rows with such extreme values. These three values are possibly the result of errors during the data entry and/or data calculation.

Three rows are not considered statistically significant in a dataframe with over 5 million rows. With this in mind and after careful consideration, these rows will be filtered out to prevent them from skewing further analysis.

df_filtered <- df_filtered %>%
  filter(ride_distance < 9810.76)

min_ride_distance_filtered <- min(df_filtered$ride_distance)
max_ride_distance_filtered <- max(df_filtered$ride_distance)

cat("Minimum value in the ride_distance column:", min_ride_distance_filtered, "\n")

## Minimum value in the ride_distance column: 0

cat("Maximum value in the ride_distance column:", max_ride_distance_filtered, "\n")

## Maximum value in the ride_distance column: 48.93

Now the exploration will continue with the data regarding ride duration.

summary(df_filtered[, c("ride_duration", "rd_secs", "rd_mins", "rd_hours")])

##  ride_duration         rd_secs             rd_mins          
##  Length:5742608     Min.   :-995791.0   Min.   :-16596.517  
##  Class :character   1st Qu.:    329.0   1st Qu.:     5.483  
##  Mode  :character   Median :    577.0   Median :     9.617  
##                     Mean   :    920.6   Mean   :    15.343  
##                     3rd Qu.:   1023.0   3rd Qu.:    17.050  
##                     Max.   : 728178.0   Max.   : 12136.300  
##     rd_hours         
##  Min.   :-276.60861  
##  1st Qu.:   0.09139  
##  Median :   0.16028  
##  Mean   :   0.25572  
##  3rd Qu.:   0.28417  
##  Max.   : 202.27167

The statistical summary of data within ride duration related columns reveals certain anomalies, including both incoherent and extreme values.

• Incoherent values: some rides exhibit negative time durations, which are implausible in this context.

• Extreme values: some rides have exceptionally long or short durations, with some exceeding 200 hours.

To address this issue, further investigation will focus on the “rd_mins” variable, representing ride durations. Before any data cleaning procedures are applied, it is essential to determine the statistical significance of the anomalies within the dataset.

Given that all rides are expected to occur within the city of Chicago, ride durations less than or equal to 1 minute and above 480 minutes (equivalent to 8 hours), will be considered extreme. These thresholds are based on the assumption that such short or long ride durations are highly improbable in this context.

total_extreme_ride_durations <- sum(df_filtered$rd_mins <= 1 | df_filtered$rd_mins > 480)
percentage_extreme_ride_durations <- round((total_extreme_ride_durations / nrow(df_filtered)) * 100, 2)

cat("Total rows with extreme ride durations:", total_extreme_ride_durations, "\n")

## Total rows with extreme ride durations: 148804

cat("Percentage of rows with extreme ride durations:", percentage_extreme_ride_durations,"%")

## Percentage of rows with extreme ride durations: 2.59 %

Data removal 5

After thorough examination of the dataset, it was observed that less than 2.6% of rows contain extreme values in the “rd_mins” column. These extreme values, likely resulting from errors in data entry, could potentially skew the analysis and have minimal statistical significance.

To ensure the integrity of the analysis, it has been decided to remove these rows from the dataframe. By eliminating rows with extreme values, we aim to mitigate the impact of erroneous data on the validity of our findings.

# Remove rows with extreme values in rd_mins
df_filtered <- df_filtered %>%
  filter(rd_mins > 1 & rd_mins <= 480)

Generate new dataframe with the clean data and reorganized columns.

# Rearrange the order of columns and save a new dataframe as "clean_cyclistic_data"
df <- df_filtered %>%
  select(ride_id, rideable_type, customer_type, ride_month, ride_day, ride_distance, ride_duration, rd_time, rd_secs, rd_mins, rd_hours, started_at, ended_at, start_lat, start_lng, end_lat, end_lng)

Check for missing values and errors in cleaned dataframe.

null_counts <- colSums(is.na(df))

print(null_counts[null_counts > 0])

## named numeric(0)

Overview of clean dataset before exporting:

glimpse(df)

## Rows: 5,593,804
## Columns: 17
## $ ride_id       <chr> "8FE8F7D9C10E88C7", "34E4ED3ADF1D821B", "5296BF07A2F77CB…
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", "elec…
## $ customer_type <chr> "member", "member", "member", "member", "member", "membe…
## $ ride_month    <chr> "April", "April", "April", "April", "April", "April", "A…
## $ ride_day      <chr> "Sunday", "Wednesday", "Wednesday", "Wednesday", "Wednes…
## $ ride_distance <dbl> 1.11, 7.12, 0.00, 1.11, 1.66, 1.99, 2.37, 1.11, 1.11, 6.…
## $ ride_duration <chr> "00:04:09", "00:23:10", "00:02:00", "00:03:39", "00:04:5…
## $ rd_time       <time> 00:04:09, 00:23:10, 00:02:00, 00:03:39, 00:04:50, 00:04…
## $ rd_secs       <dbl> 249, 1390, 120, 219, 290, 244, 552, 298, 659, 1204, 127,…
## $ rd_mins       <dbl> 4.150000, 23.166667, 2.000000, 3.650000, 4.833333, 4.066…
## $ rd_hours      <dbl> 0.06916667, 0.38611111, 0.03333333, 0.06083333, 0.080555…
## $ started_at    <dttm> 2023-04-02 08:37:28, 2023-04-19 11:29:02, 2023-04-19 08…
## $ ended_at      <dttm> 2023-04-02 08:41:37, 2023-04-19 11:52:12, 2023-04-19 08…
## $ start_lat     <dbl> 41.80, 41.87, 41.93, 41.92, 41.91, 41.91, 41.93, 42.00, …
## $ start_lng     <dbl> -87.60, -87.65, -87.66, -87.65, -87.65, -87.63, -87.66, …
## $ end_lat       <dbl> 41.79, 41.93, 41.93, 41.91, 41.91, 41.92, 41.91, 41.99, …
## $ end_lng       <dbl> -87.60, -87.68, -87.66, -87.65, -87.63, -87.65, -87.65, …

Export clean dataset “clean_cyclistic_data” as a CSV file write.csv(df, "clean_cyclistic_data.csv", row.names = FALSE)

Process phase summary

DATA EXPLORATION

• Through this work, the data in the original dataset was sorted and filtered for exploration. Four columns were found to have a significant amount of missing values, more than 60% of their rows had NA values. The rows in question were: “start_station_name”, “start_station_id”, “end_station_name”, and “end_station_id”.
• Additionally, incoherent and problematic values were identified in different steps of the Process stage. Specifics of this matter are mentioned below.

DATA MANIPULATION

Missing and erroneous data

• “member_casual” column was renamed as “customer_type”

• In this situation it was not possible to gather the missing information from columns: start_station_name, start_station_id, end_station_name, and end_station_id, it was decided that analysis could still be performed without this information at all, so this columns were removed.

• Incoherent and problematic data was found and removed from the dataset, specifically in columns that were added to the original dataset.

• While deciding what information to keep and what information to remove from the dataset, it always ensured that the information that was removed was not statistically significant.

• A total of 156,373 rows, constituting less than 2.8% of the original dataset (5,750,177 rows), were removed due to missing or problematic data.

Calculated fields

Originally available data was leveraged to generate new meaningful information. A total of eight new columns were introduced, utilizing key data from the original dataset to enhance analytical insights.

• ride_month column: this column mentions the month on which each bike ride was started. This data was extracted from the “started_at” column.

• ride_day column: this column displays the day of the week on which each bike ride was started. This data was also extracted from the “started_at” column.

• ride_distance column: this column shows the distance in kilometers for each ride. The data for this column was obtained applying the Haversine formula on the coordinates from columns: start_lat, start_lng, end_lat, and end_lng.

• ride_duration column: this column shows the duration of each ride in an “HH:MM:SS” format. The data for this column was obtained applying the difftime() function to estimate the difference between the times stated in columns “started_at” and “ended_at”.

• rd_time column: this column shows the same information as the “ride_duration” column but in an “hms” data format.

• Finally, columns “rd_secs”, “rd_mins”, and “rd_hours” show the time duration of each ride in seconds, minutes, and hours respectively. All this columns are formatted with a “dbl” data type.

Phase 4. ANALYZE

Objectives:

• Conduct a descriptive analysis to identify current behavioral patterns and trends among Cyclistic customers regarding the use of company’s bikes.

• Extract objective and accurate findings, insights, and conclusions.

Note: The descriptive analysis should approach all guiding questions defined during the “Ask” phase. Additionally, if other relevant questions show up during this phase regarding Cyclistic customer use of company’s bikes, these questions should be addressed during this phase as well.

Guiding Question 1

How many rides were taken on Cyclistic bikes during the last twelve months?

# Count the number of rides for each customer type
customer_counts <- table(df$customer_type)

# Calculate total number of rides
# Count the number of rides for each customer type
customer_counts <- table(df$customer_type)

# Create a data frame from the counts
customer_df <- data.frame(customer_type = names(customer_counts),
                          count = as.numeric(customer_counts))

# Calculate total number of rides
total_rides <- sum(customer_counts)

# Calculate percentage of each customer type
customer_df$percentage <- round((customer_df$count / total_rides) * 100, 2)

# Plot the pie chart
pie_chart_total_rides <- ggplot(customer_df, aes(x = "", y = count, fill = customer_type)) +
  geom_bar(stat = "identity", width = 1) +
  coord_polar("y", start = 0) +
  scale_fill_manual(values = c("casual" = "tomato", "member" = "steelblue")) +
  labs(title = "Visual #1. Proportion of Rides by Customer Type",
       subtitle = paste("Total Rides:", total_rides)) +  
  geom_text(aes(label = paste(count, "(", percentage, "%)")),  
            position = position_stack(vjust = 0.5), size = 5)

# Display the pie chart
print(pie_chart_total_rides)

GQ-1. FINDINGS

Visual 1

• Between April 2023 and March 2024, a total of 5,593,804 bike-rides were registered.

• During that same period, approximately 64% of bike-rides were taken by annual members, and around 36% were taken by casual riders.

Guiding Question 2

What type of bike was the most popular (for all customers and by customer type)?

df_casualriders <- df %>% 
  filter(df$customer_type == "casual")

df_members <- df %>%
  filter(df$customer_type == "member")

#Create a function to generate each pie chart
generate_pie_chart <- function(data, title, total_rides, subtitle) {
  #Count the number of rides for each rideable type
  rideable_counts <- table(data$rideable_type)
  #Create a dataframe from the counts
  rideable_df <- data.frame(rideable_type = names(rideable_counts),
                            count = as.numeric(rideable_counts))
  #Calculate total number of rides
  total_rides <- sum(rideable_counts)
  
  #Calculate percentage of rides by each rideable type
  rideable_df$percentage <- round((rideable_df$count / total_rides) * 100, 2)
  
  #Plot the pie chart
  pie_chart <- ggplot(rideable_df, aes(x = "", y = count, fill = rideable_type)) +
    geom_bar(stat = "identity", width = 1) + 
    coord_polar("y", start = 0) +
    scale_fill_manual(values = c("electric_bike" = "gold", "classic_bike" = "peru", "docked_bike" = "plum")) + 
    labs(title = title,
         subtitle = paste(subtitle, total_rides)) + 
    geom_text(aes(label = paste(count, "(", percentage, "%)")),
              position = position_stack(vjust = 0.5), size = 3.5)
  
  return(pie_chart)
}

#Generate pie charts 
pie_chart_all_customers <- generate_pie_chart(df, "Visual #2. Proportion of Rides by Rideable Type (all customers)", total_rides, "Total Rides:")

pie_chart_casualriders <- generate_pie_chart(df_casualriders, "Visual #2A. Proportion of Casual Riders Bike-Rides by Rideable Type", total_rides_casual, "Total Rides:")

pie_chart_annualmembers <- generate_pie_chart(df_members, "Visual #2B. Proportion of Annual Members Bike-Rides by Rideable Type", total_rides_members, "Total Rides:")

#Display pie charts
print(pie_chart_all_customers)

print(pie_chart_casualriders)

print(pie_chart_annualmembers)

GQ-2. FINDINGS

Visuals 2, 2A and 2B * classic-bikes and electric-bikes were the most popular models with roughly 49% and 50% of bike-rides taken on them respectively. * Docked-bikes were the least popular model with approximately 1% of bike-rides taken on this model. * There´s no significant difference when this variable is analyzed by customer type.

Guiding Question 3

Which months present the highest and the lowest demand for Cyclistic bikes?

# Define order of months
months_order <- c("April", "May", "June", "July", "August", "September", 
                  "October", "November", "December", "January", "February", "March")

# Convert ride_month to factor with desired levels
df$ride_month <- factor(df$ride_month, levels = months_order)

# Calculate total rides for each month and customer type
total_rides_per_month <- df %>% 
  group_by(ride_month) %>% 
  summarise(total_rides = n())

total_rides_per_month_customer <- df %>% 
  group_by(ride_month, customer_type) %>% 
  summarise(total_rides = n(), .groups = "drop")

# Calculate average monthly rides
average_monthly_rides <- mean(total_rides_per_month$total_rides)

# Plot the graph with all bars in "aquamarine4" color and add the average line
bargraph_month <- ggplot(total_rides_per_month, aes(x = ride_month, y = total_rides)) +
  geom_bar(stat = "identity", fill = "aquamarine4") + 
  geom_hline(yintercept = average_monthly_rides, linetype = "dashed", color = "firebrick") +
  labs(title = "Visual #3. Bike-rides by month April 2023 to March 2024",
       subtitle = paste("Total Rides:", sum(total_rides_per_month$total_rides), "\nAverage Monthly Rides:", round(average_monthly_rides, 2))) +
  theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
  geom_text(aes(label = total_rides), position = position_stack(vjust = 0.5), size = 3)



linegraph_month_customer <- ggplot(total_rides_per_month_customer, aes(x = ride_month, y = total_rides, color = customer_type, group = customer_type)) +
  geom_line() + 
  labs(title = "Visual #4A. Bike-rides by month and customer",
       subtitle = paste("Total Rides:", sum(total_rides_per_month_customer$total_rides))) +
  theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
  scale_color_manual(values = c("casual" = "tomato", "member" = "steelblue"))



stackedbars_month_customer <- ggplot(total_rides_per_month_customer, aes(x = ride_month, y = total_rides, fill = customer_type)) +
  geom_bar(stat = "identity") + 
  labs(title = "Visual #4B. Bike-rides by month and customer",
       subtitle = paste("Total Rides:", sum(total_rides_per_month$total_rides))) +
  theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
  geom_text(aes(label = total_rides), position = position_stack(vjust = 0.5), size = 3) +
  scale_fill_manual(values = c("casual" = "tomato", "member" = "steelblue"))


#Display graphs 
print(bargraph_month)

print(linegraph_month_customer)

print(stackedbars_month_customer)

GQ-3. FINDINGS

Visual #3

• August boasted the highest number of bike-rides,totaling 751,223, closely followed by July and June with 745,418, and 699,239 rides, respectively.

• The average monthly rides between April 2023 and March 2024 amounted to 466,150.

• Notably, May, September, and October also surpassed the monthly average in terms of bike-rides during this period.

Visual #4A

• While both customer types exhibit a similar pattern in bike ride frequency throughout the 12 months, this visualization highlights distinct peaks and valleys. Notably, both groups experienced their lowest bike usage in January. However, their peak months differ, with members reaching their highest number of rides in August, while casual riders peaked in July.

Visual #4B

• Across the months from April to October, each bar in the graph closely resembles the proportion observed in Visual #1, with annual members constituting approximately 64% and casual riders 36% of the total.

• During the colder months (November through February), both customer groups exhibit significant decreased participation. Notably, the disparity between casual riders and annual members widens during this period, with casual riders representing an estimated 15% and annual members accounting for around 85% of total rides.

Guiding Question 4

What days of the week have the highest and the lowest demand for Cyclistic bikes?

# Create a sequence of dates from April 2, 2023, to March 12, 2024
dates <- seq(ymd("2023-04-02"), ymd("2024-03-12"), by = "day")

# Get the day of the week for each date and count occurrences
day_counts <- table(weekdays(dates))

# Display the counts for each day of the week
print(day_counts)

## 
##    Friday    Monday  Saturday    Sunday  Thursday   Tuesday Wednesday 
##        49        50        49        50        49        50        49

# Define order of days
days_order <- c("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")

# Convert ride_day to factor with desired levels
df$ride_day <- factor(df$ride_day, levels = days_order)

# Calculate total rides for each day and customer type
allyear_total_rides_per_day <- df %>% 
  group_by(ride_day) %>% 
  summarise(allyear_days_total_rides = n())

# Total occurrences of each day between June and August 2023
allyear_day_occurrences <- c(Monday = 50, Tuesday = 50, Wednesday = 49, Thursday = 49, Friday = 49, Saturday = 49, Sunday = 50)

# Calculate average rides per day
allyear_total_rides_per_day <- allyear_total_rides_per_day %>%
  mutate(allyear_average_rides = allyear_days_total_rides / allyear_day_occurrences[ride_day])

#Define palette of colors
contrast_palette <- c("#FF5733", "#FFC300", "#36DBCA", "#FF7F50", "#6A5ACD", "#20B2AA", "#FF6347")

# Plot bar graph with average rides per day
allyear_bargraph_days <- ggplot(allyear_total_rides_per_day, aes(x = ride_day, y = allyear_average_rides)) +
  geom_bar(stat = "identity", fill = contrast_palette) + 
  labs(title = "Visual #5. Average Bike-rides per day of the week throughout the 12 months)",
       subtitle = paste("Total Rides:", sum(allyear_total_rides_per_day$allyear_days_total_rides))) +
  theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
  geom_text(aes(label = round(allyear_average_rides)), position = position_stack(vjust = 0.5), size = 4)



# Calculate total rides for each day and customer type
allyear_total_rides_per_day_customer <- df %>%
  group_by(ride_day, customer_type) %>%
  summarise(allyear_days_total_rides = n(), .groups = "drop")

# Calculate the average number of rides per day of the week
allyear_total_rides_per_day_customer <- allyear_total_rides_per_day_customer %>%
  mutate(allyear_average_rides = allyear_days_total_rides / allyear_day_occurrences[ride_day])

# Plot bar graph with average number of rides per day of the week
allyear_bargraph_days_customer <- ggplot(allyear_total_rides_per_day_customer, aes(x = ride_day, y = allyear_average_rides, fill = customer_type)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Visual #6. Average Bike-rides per day of the week and customer type") +
  theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
  geom_text(aes(label = round(allyear_average_rides)), position = position_dodge(width = 0.9), vjust = -0.5, size = 3) +
  scale_fill_manual(values = c("casual" = "tomato", "member" = "steelblue"))


#Display plots
print(allyear_bargraph_days)

print(allyear_bargraph_days_customer)

GQ-4. FINDINGS

Visual #5

• In this visualization, we can appreciate that Saturday stands out as the day with the highest average bike rides, totaling 17,809 rides on average, followed closely by Thursday with the second-highest average of 17,378 rides.

• Conversely, Monday has the lowest average bike ride count, amounting to 14,351 rides, while Sunday follows with the second-lowest average of 14,453 rides.

Visual #6

• This graph provides a clear view of the fluctuations in Cyclistic bike usage by customer type across the different days of the week. Annual members consistently maintain a notably higher average number of rides compared to casual riders through all seven days, but particularly from Monday to Thursday.

• The peak average bike rides for annual members occur on Thursdays (11,971), while for casual riders, the peak falls on Saturdays (8,274). Conversely, Sundays mark the lowest average bike ride count for annual members (7,940), whereas MOndays represent the lowest for casual riders (4,587 rides on average).

Guiding Question 5

How long, in terms of distance and time, do customers usually ride their bikes?

summary(df[, c("ride_distance", "rd_mins")])

##  ride_distance       rd_mins       
##  Min.   : 0.000   Min.   :  1.017  
##  1st Qu.: 0.920   1st Qu.:  5.750  
##  Median : 1.590   Median :  9.867  
##  Mean   : 2.161   Mean   : 15.004  
##  3rd Qu.: 2.800   3rd Qu.: 17.317  
##  Max.   :48.930   Max.   :480.000

# Create separate boxplots for each column
boxplot_ride_distance <- ggplot(df, aes(y = ride_distance)) +
  geom_boxplot() +
  labs(title = "Boxplot of Ride Distance")


boxplot_rd_mins <- ggplot(df, aes(y = rd_mins)) +
  geom_boxplot() +
  labs(title = "Boxplot of Ride Duration in Minutes")


# Display the boxplots
print(boxplot_ride_distance)

print(boxplot_rd_mins)

Observation #2 Upon examining the boxplots showing the distribution of distance and duration in minutes for bike rides, outliers are evident, which may skew results.

Before proceeding with the analysis, it´s prudent to determine whether the outliers are statistically significant. Only after confirming this should we consider removing any data from the dataset.

# Define function to detect outliers
detect_outliers <- function(x) {
  q1 <- quantile(x, 0.25)
  q3 <- quantile(x, 0.75)
  iqr <- q3 - q1
  lower_bound <- q1 - 1.5 * iqr
  upper_bound <- q3 + 1.5 * iqr
  outliers <- x[x < lower_bound | x > upper_bound]
  return(outliers)
}

# Function to count outliers and calculate percentages
count_and_percentage <- function(df) {
  outliers_ride_distance <- detect_outliers(df$ride_distance)
  outliers_rd_mins <- detect_outliers(df$rd_mins)
  
  count_ride_distance <- length(outliers_ride_distance)
  count_rd_mins <- length(outliers_rd_mins)
  
  # Count rows with outliers in both columns
  overlapping_outliers <- sum(df$ride_distance %in% outliers_ride_distance & df$rd_mins %in% outliers_rd_mins)
  
  # Calculate percentages
  percentage_ride_distance <- round(count_ride_distance / nrow(df) * 100, 2)
  percentage_rd_mins <- round(count_rd_mins / nrow(df) * 100, 2)
  percentage_overlapping <- round(overlapping_outliers / nrow(df) * 100, 2)
  
  return(list(count_ride_distance = count_ride_distance, 
              count_rd_mins = count_rd_mins, 
              count_overlapping = overlapping_outliers,
              percentage_ride_distance = percentage_ride_distance,
              percentage_rd_mins = percentage_rd_mins,
              percentage_overlapping = percentage_overlapping))
}

# Apply the function to count outliers and calculate percentages
outliers_summary <- count_and_percentage(df)

# Display results
cat("Outliers in ride_distance:", outliers_summary$count_ride_distance, "\n")

## Outliers in ride_distance: 329959

cat("Percentage of outliers in ride_distance:", outliers_summary$percentage_ride_distance, "\n")

## Percentage of outliers in ride_distance: 5.9

cat("\n")

cat("Outliers in rd_mins:", outliers_summary$count_rd_mins, "\n")

## Outliers in rd_mins: 408136

cat("Percentage of outliers in rd_mins:", outliers_summary$percentage_rd_mins, "\n")

## Percentage of outliers in rd_mins: 7.3

cat("\n")

cat("Rows with outliers in both columns:", outliers_summary$count_overlapping, "\n")

## Rows with outliers in both columns: 124908

cat("Percentage of rows with outliers in both columns:", outliers_summary$percentage_overlapping, "\n")

## Percentage of rows with outliers in both columns: 2.23

Critical decision

The total number of rows with outliers accounts for 10.97% of the current data. After careful consideration, it has been decided that rows with outliers will not be removed.

In order to proceed with the analysis without removing rows with outliers, a new dataframe will be generated. This new dataframe will exclude rows with outliers and will be utilized to evaluate measurements of central tendency, ensuring that outliers do not skew the results.

# Apply the detect_outliers function to each column of interest
outliers_ride_distance <- detect_outliers(df$ride_distance)
outliers_rd_mins <- detect_outliers(df$rd_mins)

# Filter out rows containing outliers from the original dataframe
df_no_outliers <- df %>%
  filter(!(ride_distance %in% outliers_ride_distance) &
         !(rd_mins %in% outliers_rd_mins))

summary(df_no_outliers[, c("ride_distance", "rd_mins")])

##  ride_distance      rd_mins      
##  Min.   :0.000   Min.   : 1.017  
##  1st Qu.:0.900   1st Qu.: 5.367  
##  Median :1.500   Median : 8.833  
##  Mean   :1.819   Mean   :10.578  
##  3rd Qu.:2.480   3rd Qu.:14.117  
##  Max.   :5.610   Max.   :34.667

#Create a new dataframe for "casual" customers without outliers
outliers_ride_distance_casualriders <- detect_outliers(df_casualriders$ride_distance)
outliers_rd_mins_casualriders <- detect_outliers(df_casualriders$rd_mins)

df_no_outliers_casualriders <- df_casualriders %>%
  filter(!(ride_distance %in% outliers_ride_distance_casualriders) &
         !(rd_mins %in% outliers_rd_mins_casualriders))

#Create a new dataframe for "members" customers without outliers
outliers_ride_distance_members <- detect_outliers(df_members$ride_distance)
outliers_rd_mins_members <- detect_outliers(df_members$rd_mins)

df_no_outliers_members <- df_members %>%
  filter(!(ride_distance %in% outliers_ride_distance_members) &
         !(rd_mins %in% outliers_rd_mins_members))

# Create objects with summary statistics of the ride_distance column for each type of customer. 
ride_distance_wo_summary_casual <- summary(df_no_outliers_casualriders$ride_distance)
ride_distance_wo_summary_members <- summary(df_no_outliers_members$ride_distance)

# Create a data frame for annotations for each customer type
annotation_df_casual <- data.frame(
  x = 1.1,  # X coordinate for annotations
  y = rep(ride_distance_wo_summary_casual[c(1, 2, 3, 4, 5, 6)], each = 2) + 0.05 * diff(range(df_no_outliers_casualriders$ride_distance)),  # Y coordinates for annotations
  label = c(
    paste("Min.:", round(ride_distance_wo_summary_casual[1], 2)),
    "",
    paste("1st Qu.:", round(ride_distance_wo_summary_casual[2], 2)),
    "",
    paste("Median:", round(ride_distance_wo_summary_casual[3], 2)),
    "",
    paste("Mean:", round(ride_distance_wo_summary_casual[4], 2)),
    "",
    paste("3rd Qu.:", round(ride_distance_wo_summary_casual[5], 2)),
    "",
    paste("Max.:", round(ride_distance_wo_summary_casual[6], 2)),
    ""
  )  # Text labels for annotations
)

annotation_df_member <- data.frame(
  x = 1.1,  # X coordinate for annotations
  y = rep(ride_distance_wo_summary_members[c(1, 2, 3, 4, 5, 6)], each = 2) + 0.05 * diff(range(df_no_outliers_members$ride_distance)),  # Y coordinates for annotations
  label = c(
    "",
    paste("Min.:", round(ride_distance_wo_summary_members[1], 2)),
    "",
    paste("1st Qu.:", round(ride_distance_wo_summary_members[2], 2)),
    "",
    paste("Median:", round(ride_distance_wo_summary_members[3], 2)),
    "",
    paste("Mean:", round(ride_distance_wo_summary_members[4], 2)),
    "",
    paste("3rd Qu.:", round(ride_distance_wo_summary_members[5], 2)),
    "",
    paste("Max.:", round(ride_distance_wo_summary_members[6], 2))
  )  # Text labels for annotations
)

# Create boxplot for casual customers
boxplot_ride_distance_wo_casual <- ggplot(df_no_outliers_casualriders, aes(x = "", y = ride_distance)) +
  geom_boxplot(color = "tomato") +
  labs(title = "Visual #7A. Ride distance (in kilometers) boxplot for Casual Riders") +
  theme_minimal() +
  theme(
    panel.grid.major = element_line(color = "darkgray"),
    panel.grid.minor = element_line(color = "darkgray")
  ) +
  geom_text(data = annotation_df_casual, aes(x = x, y = y, label = label),
            hjust = 0, vjust = 0.5, size = 4) +  # Adjust vjust for vertical positioning
  ylim(0, max(df_no_outliers_casualriders$ride_distance) * 1.2)  # Adjust ylim to accommodate annotations

# Create boxplot for member customers
boxplot_ride_distance_wo_member <- ggplot(df_no_outliers_members, aes(x = "", y = ride_distance)) +
  geom_boxplot(color = "steelblue") +
  labs(title = "Visual #7B. Ride distance (in kilometers) boxplot for Annual Members") +
  theme_minimal() + 
  theme(
    panel.grid.minor = element_line(color = "darkgray"),
    panel.grid.major = element_line(color = "darkgray")
  ) +
  geom_text(data = annotation_df_member, aes(x = x, y = y, label = label),
            hjust = 0, vjust = 0.5, size = 4) +  # Adjust vjust for vertical positioning
  ylim(0, max(df_no_outliers_members$ride_distance) * 1.2)  # Adjust ylim to accommodate annotations

# Display the plots
print(boxplot_ride_distance_wo_casual)

print(boxplot_ride_distance_wo_member)

# Create objects with summary statistics of the ride_distance column for each type of customer. 
rd_mins_wo_summary_casual <- summary(df_no_outliers_casualriders$rd_mins)
rd_mins_wo_summary_members <- summary(df_no_outliers_members$rd_mins)

# Create a data frame for annotations for each customer type
rd_mins_annotation_df_casual <- data.frame(
  x = 1.1,  # X coordinate for annotations
  y = rep(rd_mins_wo_summary_casual[c(1, 2, 3, 4, 5, 6)], each = 2) + 0.05 * diff(range(df_no_outliers_casualriders$rd_mins)),
  label = c(
    paste("Min.:", round(rd_mins_wo_summary_casual[1], 2)),
    "",
    paste("1st Qu.:", round(rd_mins_wo_summary_casual[2], 2)),
    "",
    paste("Median:", round(rd_mins_wo_summary_casual[3], 2)),
    "",
    paste("Mean:", round(rd_mins_wo_summary_casual[4], 2)),
    "",
    paste("3rd Qu.:", round(rd_mins_wo_summary_casual[5], 2)),
    "",
    paste("Max.:", round(rd_mins_wo_summary_casual[6], 2)),
    ""
  )
)

rd_mins_annotation_df_members <- data.frame(
  x = 1.1,  # X coordinate for annotations
  y = rep(rd_mins_wo_summary_members[c(1, 2, 3, 4, 5, 6)], each = 2) + 0.05 * diff(range(df_no_outliers_members$rd_mins)),
  label = c(
    "",
    paste("Min.:", round(rd_mins_wo_summary_members[1], 2)),
    "",
    paste("1st Qu.:", round(rd_mins_wo_summary_members[2], 2)),
    "",
    paste("Median:", round(rd_mins_wo_summary_members[3], 2)),
    "",
    paste("Mean:", round(rd_mins_wo_summary_members[4], 2)),
    "",
    paste("3rd Qu.:", round(rd_mins_wo_summary_members[5], 2)),
    "",
    paste("Max.:", round(rd_mins_wo_summary_members[6], 2))
  ) 
)

# Create boxplot for casual customers
boxplot_rd_mins_wo_casual <- ggplot(df_no_outliers_casualriders, aes(x = "", y = rd_mins)) +
  geom_boxplot(color = "tomato") +
  labs(title = "Visual #8A. Ride duration (in minutes) boxplot for Casual Riders") +
  theme_minimal() +
  theme(
    panel.grid.major = element_line(color = "darkgray"),
    panel.grid.minor = element_line(color = "darkgray")
  ) +
  geom_text(data = rd_mins_annotation_df_casual, aes(x = x, y = y, label = label),
            hjust = 0, vjust = 0.5, size = 4) + 
  ylim(0, max(df_no_outliers_casualriders$rd_mins) * 1.2)  

# Create boxplot for member customers
boxplot_rd_mins_wo_member <- ggplot(df_no_outliers_members, aes(x = "", y = rd_mins)) +
  geom_boxplot(color = "steelblue") +
  labs(title = "Visual #8B. Ride duration (in minutes) boxplot for Annual Members") +
  theme_minimal() + 
  theme(
    panel.grid.minor = element_line(color = "darkgray"),
    panel.grid.major = element_line(color = "darkgray")
  ) +
  geom_text(data = rd_mins_annotation_df_members, aes(x = x, y = y, label = label),
            hjust = 0, vjust = 0.5, size = 4) + 
  ylim(0, max(df_no_outliers_members$rd_mins) * 1.2)  

# Display the plots
print(boxplot_rd_mins_wo_casual)

print(boxplot_rd_mins_wo_member)

GQ-5. FINDINGS

Visuals 7A and 7B

• Both casual riders and annual members exhibit a minimum ride distance of 0.0 kilometers, consistent with the computation method mentioned earlier in this report.

• While the maximum ride distance is slightly higher for annual members (5.63km), compared to casual riders (5.57km), the variance is marginal.

• Across other statistical measures such as median, mean, and quartiles, casual riders tend to have slightly higher values than annual members, although these differences lack statistical significance.

Visuals 8A and 8B

• In terms of ride duration, both customer groups demonstrate a minimum value of 1.02 minutes. It´s noteworthy that entries with durations below and equal to 1.0 (representing less than 2.6% of the original data) were excluded from the dataset during the Processing stage of this project, due to unlikelihood of such short ride periods.

• Notable disparities exist in other statistical parameters between casual riders and annual members. Casual riders consistently exhibit longer ride durations compared to annual members. For instance, the interquartile range for casual riders spans from 6.47 to 17.6 minutes, whereas for annual members, this range is narrower, extending from 4.97 to 12.75 minutes.

Analyze phase summary

Guiding questions

1. How many rides were taken on Cyclistic bikes during the last twelve months?

2. What type of bike was the most popular?

3. Which months present the highest and the lowest demand for Cyclistic bikes?

4. What days of the week have the highest and the lowest demand for Cyclistic bikes?

5. How long, in terms of distance and time, do customers usually ride their bikes?

FINDINGS SUMMARY:

1. Total Bike Rides Between April 2023 and March 2024

   • During this period, Cyclistic recorded 5,593,804 rides. Annual members accounted for 64% of these rides, while casual riders made up 36%

2. Most Popular Bike Models

   • Both casual riders and annual members favored classic and electric bikes, with approximately 49% and 50% of rides, respectively.

   • The least popular model for both groups was the docked-bike with about 1% of rides.

3. Peak and Off-peak Months

   • Ride frequency followed similar patterns for casual riders and annual members, peaking in summer months (June, July, and August) and dropping in winter, particularly in January.

4. Peak and Off-peak Days

   • Across the 12 months from April 2023 to March 2024, when looking at both customer types combined, Saturdays saw the highest average rides, while Mondays had the lowest.

   • For annual members alone, ride average per day of the week forms a curve that peaks on Thursdays and gradually declines to both sides, reaching its lowest points with Monday on the left and Sunday on the right.

   • Casual riders have their lowest average on Monday, but show a steady increase in ride average from Tuesday to Saturday, peaking on Saturday and dropping again on Sunday.

5. Riding Distances and Durations

   • Both casual riders and annual members exhibit similar ride distances.

   • Casual riders tended to have longer ride durations compared to annual members.

INSIGHTS SUMMARY:

• Between April 2023 and March 2024, annual members accounted for 64% of rides with Cyclistic bikes, while casual riders represented 36% of rides. This suggests that annual members, possibly motivated by their paid memberships, tend to utilize the bikes more frequently.

• Both customer types exhibit a strong preference for the classic and electric bike models, while docked bikes are rarely used. Further investigation is required to understand the reasons behind this preference.

• Ride frequency for both casual riders and annual members follows a seasonal pattern, peaking in summer and declining in winter. Weather conditions likely play a significant role, with customers favoring biking during warmer seasons.

• Annual members maintain a consistent ride frequency from Monday to Friday, with lower averages on weekends, particularly Sundays. This suggests that their bike usage may be more work or school-related. In contrast, casual riders tend to use the bikes more on weekends, indicating leisure-oriented usage.

• The current data only allows for calculating the distance between Cyclistic stations, which may not accurately reflect the actual distance covered by each customer. Additional data is necessary for more precise insights into customer ride distances.

• Casual riders consistently record longer ride durations compared to annual members. This may indicate that casual riders primarily use Cyclistic bikes for recreational purposes, while annual members may use them more for commuting or professional activities.

CONCLUSIONS:

The aim of this analysis was to provide comprehensive insights into the differing behaviors of casual riders and annual members when using Cyclistic bikes.

Based on the findings of this analysis, the following conclusions can be drawn:

• Annual members demonstrate a significantly higher frequency of bike rides compared to casual riders, indicating a higher level of engagement with Cyclistic bike services.
• Both customer segments display a strong preference for electric and classic bike models, and their bike usage follows a seasonal trend, peaking during the warmer months.
• The data suggests that casual riders predominantly use Cyclistic bikes for leisure or recreational purposes, while annual members likely utilize them for commuting or professional activities.
• To further enhance our understanding of customer behaviors, additional data is required to accurately assess ride distances and other related metrics.

Phase 5. SHARE

Objectives:

• Create clear, concise and compelling visualizations.

• Summarize the analysis process and results into an effective presentation for stakeholders.

The summary and outcomes of this project have been compiled into a presentations using Google Slides. Stakeholders can access the presentation in “reading” mode by clicking here.