Cyclistic Tripdata Case Study

Installing and loading required packages

if(!require(tidyverse)) install.packages("tidyverse")

## Loading required package: 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

if(!require(hms)) install.packages("hms")

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

if(!require(janitor)) install.packages("janitor")

## Loading required package: janitor
## 
## Attaching package: 'janitor'
## 
## The following objects are masked from 'package:stats':
## 
##     chisq.test, fisher.test

if(!require(lubridate)) install.packages("lubridate")

library(tidyverse)
library(hms)
library(janitor)
library(lubridate)

Processing Data

In the Processing Phase, I cleaned, transformed, and prepared the data to ensure accuracy and consistency before analysis using R studio as the main tool.

Importing the Past 12 Months’ Cyclist Data

Jan2023 <- read_csv("Tripdata/2023_01.csv")

## Rows: 190301 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.

Feb2023 <- read_csv("Tripdata/2023_02.csv")

## Rows: 190445 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.

Mar2023 <- read_csv("Tripdata/2023_03.csv")

## Rows: 258678 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.

Apr2023 <- read_csv("Tripdata/2023_04.csv")

## 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.

May2023 <- read_csv("Tripdata/2023_05.csv")

## 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.

Jun2023 <- read_csv("Tripdata/2023_06.csv")

## 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.

Jul2023 <- read_csv("Tripdata/2023_07.csv")

## 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.

Aug2023 <- read_csv("Tripdata/2023_08.csv")

## 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.

Sep2023 <- read_csv("Tripdata/2023_09.csv")

## 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.

Oct2023 <- read_csv("Tripdata/2023_10.csv")

## 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.

Nov2023 <- read_csv("Tripdata/2023_11.csv")

## 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.

Dec2023 <- read_csv("Tripdata/2023_12.csv")

## 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.

Combine All the Data into a Single Data Frame

merged_df <- bind_rows(
  Jan2023, Feb2023, Mar2023, Apr2023, May2023, Jun2023,
  Jul2023, Aug2023, Sep2023, Oct2023, Nov2023, Dec2023
)

Filter Invalid Rows

# Keep rows where end time is after or equal to start time
merged_df <- merged_df %>% filter(started_at <= ended_at)

Calculate Ride Duration

# Convert start and end times to POSIXct format
merged_df <- merged_df %>%
  mutate(
    started_at = as.POSIXct(started_at, format = "%Y-%m-%dT%H:%M:%S", tz = "UTC"),
    ended_at = as.POSIXct(ended_at, format = "%Y-%m-%dT%H:%M:%S", tz = "UTC")
  )

# Calculate ride duration in hms format
merged_df <- merged_df %>%
  mutate(ride_length = hms(seconds = as.numeric(difftime(ended_at, started_at, units = "secs"))))

Calculate Day of the Week

# Add a day_of_week column (1 = Sunday, 7 = Saturday)
merged_df <- merged_df %>%
  mutate(day_of_week = wday(started_at, week_start = 7))

Handling Duplicates

duplicates <- merged_df %>% filter(duplicated(ride_id))
print(duplicates)

## # A tibble: 0 × 15
## # ℹ 15 variables: ride_id <chr>, rideable_type <chr>, started_at <dttm>,
## #   ended_at <dttm>, 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>,
## #   ride_length <time>, day_of_week <dbl>

# remove duplicates
merged_df <- merged_df %>% distinct(ride_id, .keep_all = TRUE)

Remove empty columns and rows

merged_df <- merged_df %>% janitor::remove_empty(c("rows", "cols"))

Analyze Data

In this phase, I examined the processed data to uncover trends, patterns, and insights related to ride behavior across different user types. I conducted summary statistics, such as calculating the mean, median, and total ride length, to understand the overall usage. I also analyzed ride patterns by grouping the data by variables like user type, day of the week, and month to identify differences in behavior between members and casual riders.

Ride Length Statistics by User Type

merged_df %>%
group_by(member_casual) %>%  
summarise(
  mean_duration = hms(seconds_to_period(mean(as.numeric(ride_length), na.rm = TRUE))), 
  max_duration = hms(seconds_to_period(max(as.numeric(ride_length), na.rm = TRUE))),    
  min_duration = hms(seconds_to_period(min(as.numeric(ride_length), na.rm = TRUE))),   
  median_duration = hms(seconds_to_period(median(as.numeric(ride_length), na.rm = TRUE))),  
  total_duration = hms(seconds_to_period(sum(as.numeric(ride_length), na.rm = TRUE))),  
  ) %>%
  
  print()

## # A tibble: 2 × 6
##   member_casual mean_duration max_duration min_duration median_duration
##   <chr>         <time>        <time>       <time>       <time>         
## 1 casual        28'14.895033" 1641:29:04   00'00"       11'51"         
## 2 member        12'31.548014"   25:59:40   00'00"       08'31"         
## # ℹ 1 more variable: total_duration <time>

Daily Ride Length Statistics by User Type (Sunday = 1)

merged_df %>%
group_by(member_casual, day_of_week) %>% 
summarise(
  mean_duration = hms(seconds_to_period(mean(as.numeric(ride_length), na.rm = TRUE))),  
  max_duration = hms(seconds_to_period(max(as.numeric(ride_length), na.rm = TRUE))),    
  min_duration = hms(seconds_to_period(min(as.numeric(ride_length), na.rm = TRUE))),    
  median_duration = hms(seconds_to_period(median(as.numeric(ride_length), na.rm = TRUE))), 
  total_duration = hms(seconds_to_period(sum(as.numeric(ride_length), na.rm = TRUE))),  
  ) %>%
  
  print()

## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.

## # A tibble: 14 × 7
## # Groups:   member_casual [2]
##    member_casual day_of_week mean_duration max_duration min_duration
##    <chr>               <dbl> <time>        <time>       <time>      
##  1 casual                  1 32'51.809690" 1047:47:06   00'00"      
##  2 casual                  2 27'42.886154" 1389:42:35   00'00"      
##  3 casual                  3 25'05.079517" 1069:31:42   00'00"      
##  4 casual                  4 24'18.269810" 1641:29:04   00'00"      
##  5 casual                  5 24'43.586908" 1542:49:55   00'00"      
##  6 casual                  6 27'15.767995" 1329:35:01   00'00"      
##  7 casual                  7 32'08.351158" 1066:49:15   00'00"      
##  8 member                  1 13'59.421903"   25:00:31   00'00"      
##  9 member                  2 11'54.142915"   24:59:56   00'00"      
## 10 member                  3 12'00.768696"   24:59:56   00'00"      
## 11 member                  4 11'56.857473"   24:59:56   00'00"      
## 12 member                  5 12'01.188277"   24:59:57   00'00"      
## 13 member                  6 12'28.723352"   24:59:57   00'00"      
## 14 member                  7 13'56.290181"   25:59:40   00'00"      
## # ℹ 2 more variables: median_duration <time>, total_duration <time>

Monthly Ride Duration Statistics by User Type

  merged_df %>%
  group_by(month = format(as.Date(started_at), "%B"), member_casual) %>%  
  summarise(
    mean_duration = hms(seconds_to_period(mean(as.numeric(ride_length), na.rm = TRUE))), 
    max_duration = hms(seconds_to_period(max(as.numeric(ride_length), na.rm = TRUE))),    
    min_duration = hms(seconds_to_period(min(as.numeric(ride_length), na.rm = TRUE))),  
    median_duration = hms(seconds_to_period(median(as.numeric(ride_length), na.rm = TRUE))),  
    total_duration = hms(seconds_to_period(sum(as.numeric(ride_length), na.rm = TRUE))),  
  ) %>%
  arrange(match(month, month.name))  %>%
  
print(n = Inf)

## `summarise()` has grouped output by 'month'. You can override using the
## `.groups` argument.

## # A tibble: 24 × 7
## # Groups:   month [12]
##    month   member_casual mean_duration max_duration min_duration median_duration
##    <chr>   <chr>         <time>        <time>       <time>       <time>         
##  1 January casual        22'54.890397"  560:03:44   00'00"       07'46"         
##  2 January member        10'21.705855"   24:59:56   00'00"       06'57"         
##  3 Februa… casual        23'11.550981"  314:25:46   00'00"       08'41"         
##  4 Februa… member        10'42.861912"   24:59:56   00'00"       07'05"         
##  5 March   casual        21'24.736532"  280:08:04   00'00"       08'40"         
##  6 March   member        10'26.533274"   25:59:40   00'00"       07'09"         
##  7 April   casual        27'40.376680"  306:35:29   00'00"       11'17"         
##  8 April   member        11'41.634034"   24:59:56   00'00"       07'59"         
##  9 May     casual        28'31.211873"  486:50:31   00'00"       12'47"         
## 10 May     member        13'02.413089"   25:00:31   00'00"       08'54"         
## 11 June    casual        29'24.416285"  491:05:58   00'00"       12'55"         
## 12 June    member        13'12.016234"   24:59:56   00'00"       09'20"         
## 13 July    casual        32'20.070425"  857:41:24   00'00"       13'25"         
## 14 July    member        13'41.499177"   24:59:57   00'00"       09'30"         
## 15 August  casual        35'14.829830" 1641:29:04   00'00"       12'54"         
## 16 August  member        13'46.360880"   24:59:57   00'00"       09'28"         
## 17 Septem… casual        25'11.226572"   25:07:46   00'00"       12'20"         
## 18 Septem… member        13'08.725026"   24:59:57   00'00"       09'04"         
## 19 October casual        22'52.323527"   24:59:57   00'00"       10'36"         
## 20 October member        12'09.064488"   24:59:56   00'00"       08'12"         
## 21 Novemb… casual        19'54.021086"   25:00:25   00'00"       09'11"         
## 22 Novemb… member        11'34.406521"   24:59:56   00'00"       07'43"         
## 23 Decemb… casual        19'56.222237"   24:59:57   00'00"       08'27"         
## 24 Decemb… member        11'26.796633"   24:59:56   00'00"       07'30"         
## # ℹ 1 more variable: total_duration <time>

Most Common Day of the Week for Rides by User Type

calculate_mode <- function(x) {
  unique_x <- unique(x)
  unique_x[which.max(tabulate(match(x, unique_x)))]
}


merged_df %>%
  group_by(member_casual) %>%  
  summarise(
    mode_day_of_week = calculate_mode(weekdays(as.Date(started_at))) 
  ) %>%
  print()

## # A tibble: 2 × 2
##   member_casual mode_day_of_week
##   <chr>         <chr>           
## 1 casual        Saturday        
## 2 member        Thursday

Most Common Month for Rides by User Type

calculate_mode <- function(x) {
  unique_x <- unique(x)
  unique_x[which.max(tabulate(match(x, unique_x)))]
}

merged_df %>%
  group_by(member_casual) %>% 
  summarise(
    mode_month = calculate_mode(format(as.Date(started_at), "%B")) 
  )  %>%
  
  print()

## # A tibble: 2 × 2
##   member_casual mode_month
##   <chr>         <chr>     
## 1 casual        July      
## 2 member        August

Total Ride Count by User Type

user_total_rides <- merged_df %>%
  group_by(member_casual) %>%  
  summarise(
    total_ride_count = n()  
  ) %>%
  arrange(desc(total_ride_count))  

print(user_total_rides)

## # A tibble: 2 × 2
##   member_casual total_ride_count
##   <chr>                    <int>
## 1 member                 3660568
## 2 casual                 2059037

Daily Ride Count by User Type (Sunday = 1)

merged_df %>%
  group_by(member_casual, day_of_week) %>%  
  summarise(
    ride_count = n()  
  ) %>%
  arrange(day_of_week, member_casual,) %>%

  print(n = Inf)  

## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.

## # A tibble: 14 × 3
## # Groups:   member_casual [2]
##    member_casual day_of_week ride_count
##    <chr>               <dbl>      <int>
##  1 casual                  1     335668
##  2 member                  1     408829
##  3 casual                  2     234818
##  4 member                  2     494558
##  5 casual                  3     246211
##  6 member                  3     576743
##  7 casual                  4     249153
##  8 member                  4     586438
##  9 casual                  5     270596
## 10 member                  5     589572
## 11 casual                  6     311907
## 12 member                  6     531582
## 13 casual                  7     410684
## 14 member                  7     472846

Monthly Ride Count by User Type

merged_df %>%
  mutate(started_at = as.POSIXct(started_at, format = "%d/%m/%Y %H:%M:%S")) %>%
  mutate(month = format(started_at, "%B")) %>%
  group_by(month, member_casual) %>%
  summarise(count_of_rides = n()) %>%  
  arrange(match(month, month.name)) %>%
  print(n = Inf)

## `summarise()` has grouped output by 'month'. You can override using the
## `.groups` argument.

## # A tibble: 24 × 3
## # Groups:   month [12]
##    month     member_casual count_of_rides
##    <chr>     <chr>                  <int>
##  1 January   casual                 40008
##  2 January   member                150293
##  3 February  casual                 43016
##  4 February  member                147428
##  5 March     casual                 62201
##  6 March     member                196477
##  7 April     casual                147284
##  8 April     member                279302
##  9 May       casual                234178
## 10 May       member                370639
## 11 June      casual                301226
## 12 June      member                418385
## 13 July      casual                331344
## 14 July      member                436276
## 15 August    casual                311095
## 16 August    member                460538
## 17 September casual                261603
## 18 September member                404718
## 19 October   casual                177055
## 20 October   member                360022
## 21 November  casual                 98357
## 22 November  member                264097
## 23 December  casual                 51670
## 24 December  member                172393

Visualizations

In the Visualization Phase, I created a series of visual representations to effectively communicate the insights derived from the analysis. Using tools like R and Tableau, I developed various charts and graphs to illustrate key trends and patterns in ride behavior among different user types.

Using Rstudio

# Define colors to match the pie chart
  custom_colors <- c("member" = "#FF9F3A",  # Matching orange for members
                     "casual" = "#2077B4")  # Matching blue for casual riders

summary_df <- merged_df %>%
  mutate(day_of_week = format(as.Date(started_at), "%a")) %>%  # Extract the full name of the day of the week
  group_by(day_of_week = factor(day_of_week, levels = c("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")), member_casual) %>%  # Group by day of the week and member type
  summarise(ride_count = n(), .groups = 'drop')  # Count the number of rides


ggplot(summary_df, aes(x = day_of_week, y = ride_count, fill = member_casual)) +
  geom_bar(stat = "identity", position = position_dodge(width = 0.8), width = 0.6) +  # Space between bars
  geom_vline(xintercept = seq(1.5, 6.5, by = 1), color = "gray", linetype = "solid") +  # Vertical lines between days
  scale_y_continuous(
    labels = scales::comma,  # Format y-axis with commas
    breaks = seq(0, max(summary_df$ride_count, na.rm = TRUE), by = 100000)  # Increment y-axis by 100k
  ) +  
  scale_fill_manual(values = custom_colors) +  # Apply custom colors
  labs(
    title = "Number of Rides by User Type on Each Day",
    y = "Number of Rides"  # Omit x to remove x-axis label
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 15, face = "bold", margin = margin(b = 15, t= 15)),
    axis.text.x = element_text(size = 10, margin = margin(b=15)),   # Adjust x-axis text size
    axis.text.y = element_text(size = 10),   # Adjust y-axis text size
    axis.title.x = element_blank(),          
    axis.title.y = element_text(size = 12, margin = margin(r = 15, l=15)),
    legend.title = element_blank(),          # Remove legend title
    panel.grid.major.x = element_blank(),     
    panel.border = element_rect(color = "gray", fill = NA, size = 1)
  )

 # Modify your data to extract the abbreviated month name from 'started_at'
  summary_month <- merged_df %>%
  mutate(month = format(as.Date(started_at), "%b")) %>%  # Extract the abbreviated month name
  group_by(month = factor(month, levels = month.abb), member_casual) %>%  # Group by abbreviated month and member type
  summarise(ride_count = n(), .groups = 'drop')  # Count the number of rides

# Create the plot
summary_month %>%
  ggplot(aes(x = month, y = ride_count, fill = member_casual)) +
  geom_bar(stat = "identity", position = position_dodge(width = 0.8), width = 0.6) +  # Space between bars
  geom_vline(xintercept = seq(1.5, 6.5, by = 1), color = "gray", linetype = "solid") +
  scale_y_continuous(
    labels = scales::comma,  # Format y-axis with commas
    breaks = seq(0, max(summary_month$ride_count, na.rm = TRUE), by = 100000)  # Increment y-axis by 100k
  ) +  
  scale_fill_manual(values = custom_colors) +  # Apply custom colors 
  labs(
    title = "Number of Rides by User Type on Each Month",
    y = "Number of Rides",
    x = NULL  # Remove x-axis label
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 15, face = "bold", margin = margin(b = 15, t= 15)),
    axis.text.x = element_text(size = 12, margin = margin(b=15)),   # Adjust x-axis text size
    axis.text.y = element_text(size = 10),   # Adjust y-axis text size
    axis.title.x = element_blank(),          
    axis.title.y = element_text(size = 12, margin = margin(r = 15, l=15)),
    legend.title = element_blank(),          # Remove legend title
    panel.grid.major.x = element_blank(),     
    panel.border = element_rect(color = "gray", fill = NA, size = 1)
  )

Using Tableau

Distribution of Ride Count by User Type

Distribution of Bike Types Used