Cyclistic_Casual_vs_Member_Annual_Report
Albert Hill
2024-03-23
Cyclistic Membership Drive Report
How can Cyclistic convert casual riders into annual members?
Case Study 1A
This is a report on how annual members and casual riders use
Cyclistic bikes differently. With the goal of converting casual riders
into annual members, Cyclistic first wants to answer how the two types
of riders use the bikes differently.
This insight into behaviour can help us figure out why casual riders
might want to convert to an annual membership, or if there are changes
we could make to the membership to encourage that transition.
Data Sources
The last 12 months of data available (January - December 2023) were downloaded from Cyclistic/Divvy’s data bucket on January 27th 2024. Each month’s data was unzipped locally using WinRAR and stored locally in a file structure for this project. This data is automatically collected by Cyclistic bikes and docking stations and should include all rides on bikes with no bias. This data has been made available by Motivate International Inc. (or Lyft Bikes and Scooters, LLC) under this license.
Data Cleaning
I want to use R for my data analysis and processing, so I’ll be importing the data into R and using some tools to clean and process the data.
Setting up our environment
First we’ll install (if needed)…
if(!require(tidyverse)) install.packages("tidyverse",repos = "http://cran.us.r-project.org")## Loading required package: tidyverse## Error: package or namespace load failed for 'tidyverse':
## .onAttach failed in attachNamespace() for 'tidyverse', details:
## call: NULL
## error: package or namespace load failed for 'readr':
## .onLoad failed in loadNamespace() for 'readr', details:
## call: loadNamespace(x)
## error: there is no package called 'tzdb'## Warning: package 'tidyverse' is in use and will not be installed…and load the tidyverse default packages plus lubridate for working with date-time data.
library(tidyverse)
library(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary(lubridate)##
## Attaching package: 'lubridate'## The following objects are masked from 'package:base':
##
## date, intersect, setdiff, unionImporting Data
Now let’s import each CSV into an R data frame.
jan_2023 <- read.csv("Data/202301-divvy-tripdata.csv")
feb_2023 <- read.csv("Data/202302-divvy-tripdata.csv")
mar_2023 <- read.csv("Data/202303-divvy-tripdata.csv")
apr_2023 <- read.csv("Data/202304-divvy-tripdata.csv")
may_2023 <- read.csv("Data/202305-divvy-tripdata.csv")
jun_2023 <- read.csv("Data/202306-divvy-tripdata.csv")
jul_2023 <- read.csv("Data/202307-divvy-tripdata.csv")
aug_2023 <- read.csv("Data/202308-divvy-tripdata.csv")
sep_2023 <- read.csv("Data/202309-divvy-tripdata.csv")
oct_2023 <- read.csv("Data/202310-divvy-tripdata.csv")
nov_2023 <- read.csv("Data/202311-divvy-tripdata.csv")
dec_2023 <- read.csv("Data/202312-divvy-tripdata.csv")Next we can check out some features of our data - the columns, the data types, and the first few entries in each column.
colnames(jan_2023) #tells us the names of the columns ## [1] "ride_id" "rideable_type" "started_at"
## [4] "ended_at" "start_station_name" "start_station_id"
## [7] "end_station_name" "end_station_id" "start_lat"
## [10] "start_lng" "end_lat" "end_lng"
## [13] "member_casual"glimpse(jan_2023) #shows us the columns and the first entries that fit on screen## Rows: 190,301
## Columns: 13
## $ ride_id <chr> "F96D5A74A3E41399", "13CB7EB698CEDB88", "BD88A2E670…
## $ rideable_type <chr> "electric_bike", "classic_bike", "electric_bike", "…
## $ started_at <chr> "2023-01-21 20:05:42", "2023-01-10 15:37:36", "2023…
## $ ended_at <chr> "2023-01-21 20:16:33", "2023-01-10 15:46:05", "2023…
## $ start_station_name <chr> "Lincoln Ave & Fullerton Ave", "Kimbark Ave & 53rd …
## $ start_station_id <chr> "TA1309000058", "TA1309000037", "RP-005", "TA130900…
## $ end_station_name <chr> "Hampden Ct & Diversey Ave", "Greenwood Ave & 47th …
## $ end_station_id <chr> "202480.0", "TA1308000002", "599", "TA1308000002", …
## $ start_lat <dbl> 41.92407, 41.79957, 42.00857, 41.79957, 41.79957, 4…
## $ start_lng <dbl> -87.64628, -87.59475, -87.69048, -87.59475, -87.594…
## $ end_lat <dbl> 41.93000, 41.80983, 42.03974, 41.80983, 41.80983, 4…
## $ end_lng <dbl> -87.64000, -87.59938, -87.69941, -87.59938, -87.599…
## $ member_casual <chr> "member", "member", "casual", "member", "member", "…glimpse(dec_2023) #Compare to January to make sure we have the same columns and data types## Rows: 224,073
## Columns: 13
## $ ride_id <chr> "C9BD54F578F57246", "CDBD92F067FA620E", "ABC0858E52…
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", …
## $ started_at <chr> "2023-12-02 18:44:01", "2023-12-02 18:48:19", "2023…
## $ ended_at <chr> "2023-12-02 18:47:51", "2023-12-02 18:54:48", "2023…
## $ start_station_name <chr> "", "", "", "", "", "", "", "", "", "", "", "", "",…
## $ start_station_id <chr> "", "", "", "", "", "", "", "", "", "", "", "", "",…
## $ end_station_name <chr> "", "", "", "", "", "", "", "", "", "", "", "", "",…
## $ end_station_id <chr> "", "", "", "", "", "", "", "", "", "", "", "", "",…
## $ start_lat <dbl> 41.92, 41.92, 41.89, 41.95, 41.92, 41.91, 41.99, 42…
## $ start_lng <dbl> -87.66, -87.66, -87.62, -87.65, -87.64, -87.63, -87…
## $ end_lat <dbl> 41.92, 41.89, 41.90, 41.94, 41.93, 41.88, 42.00, 41…
## $ end_lng <dbl> -87.66, -87.64, -87.64, -87.65, -87.64, -87.65, -87…
## $ member_casual <chr> "member", "member", "member", "member", "member", "…#as_tibble(jan_2023) #Shows us as much of the table as will fit on the screen and up to 10 rows of dataEverything looks good and ready to bring together. We’ll get all twelve months of data into a single data frame so we can analyze the whole year at the same time.
all_2023 <- dplyr::bind_rows(jan_2023, feb_2023, mar_2023, apr_2023, may_2023, jun_2023, jul_2023, aug_2023, sep_2023, oct_2023, nov_2023, dec_2023)Clean up the data
We prepare for analysis by adding some additional columns of data that will be useful for later aggregation. We will also delete some rides what indicate bikes were taken out of circulation as these were not taken by actual riders.
First let’s use our Ride Started data to extract the year, month, day, and hour each ride started. We’ll also calculate the day of the week and duration of each ride. Each of these will be stored in its own column.
#all_2023$date <- as.Date(all_2023$started_at)
#all_2023$year <- format(as.Date(all_2023$date), "%Y")
#all_2023$month <- format(as.Date(all_2023$date), "%m")
#all_2023$day <- format(as.Date(all_2023$date), "%d")
#all_2023$day_of_week <- format(as.Date(all_2023$date), "%A")
#all_2023$start_hour <- format(as.POSIXct(all_2023$started_at), "%H")
# Oh wait, let's use lubridate instead! Much easier code to read,
# plus we get to practice using the tidyverse more.
# Store the components of the date in their own columns
all_2023$date <- date(all_2023$started_at)
all_2023$year <- year(all_2023$started_at)
all_2023$month <- month(all_2023$started_at)
all_2023$day <- day(all_2023$started_at)
all_2023$start_hour <- hour(all_2023$started_at)
# Calculate and store the weekday of each ride
all_2023$day_of_week <- wday(all_2023$started_at)# Calculate and store the duration of each ride...
all_2023$ride_length <- difftime(all_2023$ended_at, all_2023$started_at)
# ... and convert that duration to a number
all_2023$ride_length <- as.numeric(all_2023$ride_length)Next we’ll remove some “bad” data, which consists of bikes being taken out of docks and checked for quality or rides with a negative length. We’ll store this cleaned data in a new frame.
all_2023_v2 <- all_2023[!(all_2023$start_station_name == "HQ QR" | all_2023$ride_length<0),]Other data considerations
We could consider cutting out some of the extremely long rides, but
without knowing the circumstances for them, this would be irresponsible.
Given the number of rides we have data for, we hope they will not skew
our averages much. Maybe we could repeat this analysis later with rides
over 24 hours removed or something similar.
There are also lots of rides missing start and end stations. This could
be for a number of reasons we may not be privy to in how Divvy operates,
so for now we will take all of the data into account for our next
step.
Data Analysis
Now that we have a unified, cleaned, and organized data set, let’s take a look at what information we can pull out of that data.
We’ll start with a quick summary of ride length averages, quantiles, and end points. Remember that these values are in seconds.
summary(all_2023_v2$ride_length)## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 0 325 572 1091 1015 5909344Next we’ll break this down by casual users and members, working towards our goal. This is a little hard to read though.
aggregate(all_2023_v2$ride_length ~ all_2023_v2$member_casual, FUN = mean)## all_2023_v2$member_casual all_2023_v2$ride_length
## 1 casual 1694.9597
## 2 member 751.5815aggregate(all_2023_v2$ride_length ~ all_2023_v2$member_casual, FUN = median)## all_2023_v2$member_casual all_2023_v2$ride_length
## 1 casual 711
## 2 member 511aggregate(all_2023_v2$ride_length ~ all_2023_v2$member_casual, FUN = min)## all_2023_v2$member_casual all_2023_v2$ride_length
## 1 casual 0
## 2 member 0aggregate(all_2023_v2$ride_length ~ all_2023_v2$member_casual, FUN = max)## all_2023_v2$member_casual all_2023_v2$ride_length
## 1 casual 5909344
## 2 member 90031We can also look at the ridership on each day. Note that 1 = Sunday, 2 = Monday, …, 7 = Saturday.
aggregate(all_2023_v2$ride_length ~ all_2023_v2$member_casual + all_2023_v2$day_of_week, FUN = mean) ## all_2023_v2$member_casual all_2023_v2$day_of_week all_2023_v2$ride_length
## 1 casual 1 1972.1743
## 2 member 1 839.6244
## 3 casual 2 1662.8862
## 4 member 2 714.1429
## 5 casual 3 1505.0795
## 6 member 3 720.7687
## 7 casual 4 1458.2698
## 8 member 4 716.8575
## 9 casual 5 1483.5869
## 10 member 5 721.1883
## 11 casual 6 1635.7334
## 12 member 6 748.7234
## 13 casual 7 1928.4038
## 14 member 7 836.3739Let’s glance at how ridership varies over the year, broken down by month.
aggregate(all_2023_v2$ride_length ~ all_2023_v2$member_casual + all_2023_v2$month, FUN = mean) ## all_2023_v2$member_casual all_2023_v2$month all_2023_v2$ride_length
## 1 casual 1 1374.8904
## 2 member 1 621.7059
## 3 casual 2 1391.5510
## 4 member 2 642.8619
## 5 casual 3 1283.4632
## 6 member 3 626.3867
## 7 casual 4 1660.3767
## 8 member 4 701.6340
## 9 casual 5 1711.2119
## 10 member 5 782.4131
## 11 casual 6 1764.4163
## 12 member 6 792.0162
## 13 casual 7 1940.0704
## 14 member 7 821.4992
## 15 casual 8 2114.8298
## 16 member 8 826.3609
## 17 casual 9 1511.2266
## 18 member 9 788.7250
## 19 casual 10 1372.3235
## 20 member 10 729.0645
## 21 casual 11 1196.1806
## 22 member 11 694.9790
## 23 casual 12 1196.2222
## 24 member 12 686.7966Summary Table and Visuals
So we can generally see that casual users took longer rides than members every day of the week. Now let’s also look at the number of rides each group took.
all_2023_v2 %>%
group_by(member_casual, day_of_week) %>%
summarize(number_of_rides=n(), average_duration = mean(ride_length)) %>%
arrange(member_casual,day_of_week)## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.## # A tibble: 14 × 4
## # Groups: member_casual [2]
## member_casual day_of_week number_of_rides average_duration
## <chr> <dbl> <int> <dbl>
## 1 casual 1 335668 1972.
## 2 casual 2 234818 1663.
## 3 casual 3 246211 1505.
## 4 casual 4 249153 1458.
## 5 casual 5 270596 1484.
## 6 casual 6 311907 1636.
## 7 casual 7 410684 1928.
## 8 member 1 408829 840.
## 9 member 2 494558 714.
## 10 member 3 576743 721.
## 11 member 4 586438 717.
## 12 member 5 589572 721.
## 13 member 6 531582 749.
## 14 member 7 472846 836.# Let's store that output for later use
summary_2023 <- all_2023_v2 %>%
group_by(member_casual, day_of_week) %>%
summarize(number_of_rides=n(), average_duration = mean(ride_length)) %>%
arrange(member_casual, day_of_week) ## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.From the resulting table we can see members took a lot more rides every day of the week. Let’s get some visuals for number of rides and average ride duration.
ggplot(data = summary_2023, mapping = aes(x = day_of_week, y = number_of_rides)) +
geom_col(position = "dodge", mapping = aes(fill = member_casual)) +
#geom_label(mapping = aes(label = number_of_rides)) +
labs(title = "Number of Rides per Weekday", x = "Day of the Week", y = "Number of Rides", fill = "Rider Type", alt = "A graph with columns showing the number of rides per weekday. Each weekday has one column for members and another for casual riders." ) +
annotate("text", x = 1, y = -2000, label = "Sun" ) +
annotate("text", x = 2, y = -2000, label = "Mon" ) +
annotate("text", x = 3, y = -2000, label = "Tues" ) +
annotate("text", x = 4, y = -2000, label = "Wed" ) +
annotate("text", x = 5, y = -2000, label = "Thurs" ) +
annotate("text", x = 6, y = -2000, label = "Fri" ) +
annotate("text", x = 7, y = -2000, label = "Sat" ) 
ggplot(data = summary_2023, mapping = aes(x = day_of_week, y = average_duration)) +
geom_col(position = "dodge", mapping = aes(fill = member_casual)) +
labs(title = "Average length of ride per Weekday", x = "Day of the Week", y = "Average Duration of Ride (in seconds)", fill = "Rider Type", alt = "A graph with columns showing the average duration of a ride per weekday. Each weekday has one column for members and another for casual riders." ) +
annotate("text", x = 1, y = -100, label = "Sun" ) +
annotate("text", x = 2, y = -100, label = "Mon" ) +
annotate("text", x = 3, y = -100, label = "Tues" ) +
annotate("text", x = 4, y = -100, label = "Wed" ) +
annotate("text", x = 5, y = -100, label = "Thurs" ) +
annotate("text", x = 6, y = -100, label = "Fri" ) +
annotate("text", x = 7, y = -100, label = "Sat" ) 
When do rides start?
Now let’s take a look at when riders are using the bikes by summarizing based on the hour each ride starts.
starting_hour_summary <- all_2023_v2 %>%
group_by(member_casual, day_of_week, start_hour) %>%
summarize(number_of_rides=n(), average_duration = mean(ride_length)) %>%
arrange(member_casual, day_of_week, start_hour) ## `summarise()` has grouped output by 'member_casual', 'day_of_week'. You can
## override using the `.groups` argument.With rides starting each hour combined on a single graph, we can see the commute spikes, especially for members.
ggplot(data = starting_hour_summary, mapping = aes(x = start_hour, y = number_of_rides)) +
geom_col(position = "dodge", mapping = aes(fill = member_casual)) +
#geom_label(mapping = aes(label = number_of_rides)) +
labs(title = "Number of Rides starting each hour", x = "Hour", y = "Number of Rides", fill = "Rider Type", alt = "A graph with columns showing the number of rides starting each hour of the day. Each hour has one column for members and another for casual riders." ) 
Here is how the number of rides breaks down by starting hour on each day of the week. Remember that day 1 is Sunday.
ggplot(data = starting_hour_summary, mapping = aes(x = start_hour, y = number_of_rides)) +
geom_col(position = "dodge", mapping = aes(fill = member_casual)) +
#geom_label(mapping = aes(label = number_of_rides)) +
labs(title = "Number of Rides starting each hour", x = "Hour", y = "Number of Rides", fill = "Rider Type", alt = "A collections of graphs with columns showing the number of starting each hour of the day, one graph for each weekday. Each hour has one column for members and another for casual riders." ) +
facet_wrap(~day_of_week)
We can also look at behaviour of how long rides are based on what hour they started.
ggplot(data = starting_hour_summary, mapping = aes(x = start_hour, y = average_duration)) +
geom_col(position = "dodge", mapping = aes(fill = member_casual)) +
#geom_label(mapping = aes(label = number_of_rides)) +
labs(title = "Average duration of rides starting each hour", x = "Hour", y = "Average duration (in seconds)", fill = "Rider Type", alt = "A graph with columns showing the average duraion of rides starting each hour of the day. Each hour has one column for members and another for casual riders." ) 
Honestly this makes me think that casual riders who use a bike to get home after a night out fail to close out their ride properly which skews our average data.
Summary of findings
We see that members take a lot more rides than casual riders (between
2 and 3 times the amount), and casual riders take longer rides on
average (which may be skewed by some extremely long outliers, but the
median supports this general trend).
When we break it down by day, we can see that on weekends the casual
riders take more rides and members take fewer, bringing their numbers
almost together, and both are taking longer rides on average than
weekdays. And when we break rides down by hour started, we can see the
biggest difference is the member rides spiking around commuting hours.
There is a definite possibility that casual riders who take bikes home
after a late night out do not park them correctly and thus skew the
average ride length with outliers that do not reflect how long the bike
was actually ridden (rides that start midnight - 4am).
Further Analysis
What bike types did riders use? Are there locations where casual rides tend to start/end?
Ad idea
All the benefits of owning a bike (Show commuting, exercise outside in beautiful weather, meeting friends without needing to park, could segue into downsides of cars - parking, gas, check engine light, pollution…) with none of the hassle (show: bike storage, oiling the chain, bike stolen, mechanic?)