Divvy Bikes Study 2024 - Google Data Analytics Capstone Project
Abid
2025-05-26
Scenario
You are a junior data analyst working on the marketing analyst team at Cyclistic, a bike-share company in Chicago. The director of marketing believes the company’s future success depends on maximizing the number of annual memberships. Therefore, your team wants to understand how casual riders and annual members use Cyclistic bikes differently. From these insights, your team will design a new marketing strategy to convert casual riders into annual members. But first, Cyclistic executives must approve your recommendations, so they must be backed up with compelling data insights and professional data visualizations.
Characters and teams
● Cyclistic: A bike-share program that features more than 5,800 bicycles and 600 docking stations. Cyclistic sets itself apart by also offering reclining bikes, hand tricycles, and cargo bikes, making bike-share more inclusive to people with disabilities and riders who can’t use a standard two-wheeled bike. The majority of riders opt for traditional bikes; about 8% of riders use the assistive options. Cyclistic users are more likely to ride for leisure, but about 30% use the bikes to commute to work each day.
● Lily Moreno: The director of marketing and your manager. Moreno is responsible for the development of campaigns and initiatives to promote the bike-share program. These may include email, social media, and other channels.
● Cyclistic marketing analytics team: A team of data analysts who are responsible for collecting, analyzing, and reporting data that helps guide Cyclistic marketing strategy. You joined this team six months ago and have been busy learning about Cyclistic’s mission and business goals—as well as how you, as a junior data analyst, can help Cyclistic achieve them.
● Cyclistic executive team: The notoriously detail-oriented executive team will decide whether to approve the recommended marketing program.
About the company
The scenario of the company, case study, characters are completely fictional and are only used as a backstory for this Capstone Project.
In 2016, Cyclistic launched a successful bike-share offering with bikes that are geotracked across a network of 692 stations across Chicago, where bikes can be unlocked from one station and returned to any other station.
Divvy is Chicagoland’s bike share system across Chicago and Evanston. Divvy provides residents and visitors with a convenient, fun and affordable transportation option for getting around and exploring Chicago.
Divvy, like other bike share systems, consists of a fleet of specially-designed, sturdy and durable bikes that are locked into a network of docking stations throughout the region. The bikes can be unlocked from one station and returned to any other station in the system. People use bike share to explore Chicago, commute to work or school, run errands, get to appointments or social engagements, and more.
Divvy is available for use 24 hours/day, 7 days/week, 365 days/year, and riders have access to all bikes and stations across the system.
Main approach Cyclistic marketing strategy has always been to rely on general awareness and appeal to broad consumer segments with the approach of flexible pricing plans.
Cyclistic’s finance analysts and Moreno has concluded that we need to maximise annual memberships for better growth and profit.
Since there is already an existing casual bike rider customer base, pivoting to convert casual riders into members with their next marketing strategies would benefit the company the most.
Therefore, the team need to better understand how members and casual riders differ and what would most likely make a casual rider buy a membership through analysing the Cyclistic historical bike trip data to identify trends.
Ask
Business Task
Three questions will guide the future marketing program as required
by our stakeholders:
1. How do annual members and casual
riders use Cyclistic bikes differently?
2. Why
would casual riders buy Cyclistic annual memberships? (We
will focus on answering question 1 as our primary business task in this
capstone project and question 2 as the supplementary question)
3. How can Cyclistic use digital media to influence casual riders to
become members?
Prepare
For this capstone project, I sourced the data directly from divvybikes.com via public licensing. I am defining the scope of this project to be confined to 2024 only. Therefore, 2024 data was downloaded as separate CSV files for each month in 2024, from January to Dec 2024.
The monthly data consist of the following columns:
| Column Name | Data Type | Description |
|---|---|---|
| ride_id | Character | Ride Identification number |
| rideable_type | Character | Type of bike |
| started_at | Datetime | The date and time the ride began at |
| ended_at | Datetime | The date and time the ride ended at |
| start_station_name | Character | Start Station Name |
| start_station_id | Character | The alphanumeric name given to identify the start station |
| end_station_name | Character | End station name |
| end_station_id | Character | The alphanumeric name given to identify the start station |
| start_lat | Double | Starting latitude |
| start_lng | Double | Starting longitude |
| end_lat | Double | Ending latitude |
| end_lng | Double | Ending longitude |
| member_casual | Character | The type of rider |
About the Data set
The data was downloaded as 12 separate CSV files directly from the Divvy website mentioned above. It is organized by the month, covering the year 2024, from January to December 2024. There are also 2 separate Excel files created to illustrate pricing talking points for both Classic and Electric bikes respectively.
Divvy Bikes website refers to three different bike models, the Ebike (electric), Classic and the Scooter. I have included the Scooter in some of the graphs but the main focus for the analysis will be on the Electric bike and Classic.
Licensing
License: Bikeshare hereby grants to you a non-exclusive, royalty-free, limited, perpetual license to access, reproduce, analyze, copy, modify, distribute in your product or service and use the Data for any lawful purpose (“License”). Source
Data Credibility and Integrity
Is the data considered to be ROCCC?
Reliable: The data is reliable, it’s unbiased and
has not been modified from its original source.
Original: The data is produced by the owning company
and the raw data is not altered by anyone else.
Comprehensive: Mainly, dataset used could answer the
main question(s), however more detailed information such as the types of
trips the casual riders took would make it more complete. (i.e., single
ride or day pass)
Current: The data was collected
over the past calendar year so it is current at the time of writing.
Cited: Data is sourced directly from the company
site.
Yes, it ROCCCs!
Process
Before we can proceed with the fancy part of analysis of a data analyst, we can not forget about cleaning, checking and transforming the raw data if necessary to ensure a smooth analysis. Remember that Garbage in = Garbage out, no matter how good your analysis is.
I’ll import the data into R, removing blank spaces, checking data types and removing any unnecessary data.
R
I chose to utilise the R Programming Language in RStudio for the Process and Analysis phase of this project.
Installing and loading the required libraries
| Package | Description |
|---|---|
| tidyverse | A meta-package bundling core data-science tools (ggplot2, dplyr, tidyr, readr, purrr, tibble, stringr, forcats, etc.) with a consistent “tidy” philosophy. |
| ggtext | Enables rich text (Markdown/HTML) in ggplot2 plot text elements via
element_markdown(). |
| gt | Builds highly customizable, publication-quality display tables (HTML, LaTeX, RTF) from R data frames. |
| janitor | Simple functions for examining and cleaning dirty data
(e.g. clean_names(), remove_empty()). |
| scales | Provides common scaling transformations and formatting functions for visualizations (percentages, dollar, date breaks). |
| lubridate | Simplifies parsing, manipulating, and doing arithmetic with dates and times. |
| ggplot2 | Implements the Grammar of Graphics for declarative, layered plotting in R. |
| ggpubr | “Publication-ready” ggplot2 extension: easy functions for common statistical plots and annotations. |
| leaflet | Creates interactive web maps by binding R data to the Leaflet.js library. |
| sp | Foundation for spatial data in R: classes and methods for vectors, grids, and projections (older spatial package). |
| sf | Modern spatial data package based on “simple features”; integrates tidy data frames with geometry columns. |
| dplyr | Grammar of data manipulation (filter, select, mutate, summarize, join) with a clear, pipe-friendly syntax. |
As always, best practice is to check for any missing packages, then install if necessary.
Loading all the required libraries
library(tidyverse)
library(ggtext) # for element_markdown()
library(gt) # creates HTML table
library(janitor)
library(scales)
library(lubridate)
library(ggplot2)
library(ggpubr)
library(leaflet)
library(sp)
library(sf)
library(dplyr)Importing Data from the .csv files
Using the read_csv from the readr package
to import data from each month in 2024.
jan24 <- read_csv("202401-divvy-tripdata.csv")## 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.feb24 <- read_csv("202402-divvy-tripdata.csv")## 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.mar24 <- read_csv("202403-divvy-tripdata.csv")## 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.apr24 <- read_csv("202404-divvy-tripdata.csv")## Rows: 415025 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.may24 <- read_csv("202405-divvy-tripdata.csv")## Rows: 609493 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.jun24 <- read_csv("202406-divvy-tripdata.csv")## Rows: 710721 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.jul24 <- read_csv("202407-divvy-tripdata.csv")## Rows: 748962 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.aug24 <- read_csv("202408-divvy-tripdata.csv")## Rows: 755639 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.sep24 <- read_csv("202409-divvy-tripdata.csv")## Rows: 821276 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.oct24 <- read_csv("202410-divvy-tripdata.csv")## Rows: 616281 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.nov24 <- read_csv("202411-divvy-tripdata.csv")## Rows: 335075 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.dec24 <- read_csv("202412-divvy-tripdata.csv")## Rows: 178372 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.Preview of data and look out for NAs or potential cleaning
Glimpse
We’ll have a quick look at the freshly imported data using the
glimpse function:
glimpse(jan24)## Rows: 144,873
## Columns: 13
## $ ride_id <chr> "C1D650626C8C899A", "EECD38BDB25BFCB0", "F4A9CE7806…
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", …
## $ started_at <dttm> 2024-01-12 15:30:27, 2024-01-08 15:45:46, 2024-01-…
## $ ended_at <dttm> 2024-01-12 15:37:59, 2024-01-08 15:52:59, 2024-01-…
## $ start_station_name <chr> "Wells St & Elm St", "Wells St & Elm St", "Wells St…
## $ start_station_id <chr> "KA1504000135", "KA1504000135", "KA1504000135", "TA…
## $ end_station_name <chr> "Kingsbury St & Kinzie St", "Kingsbury St & Kinzie …
## $ end_station_id <chr> "KA1503000043", "KA1503000043", "KA1503000043", "13…
## $ start_lat <dbl> 41.90327, 41.90294, 41.90295, 41.88430, 41.94880, 4…
## $ start_lng <dbl> -87.63474, -87.63444, -87.63447, -87.63396, -87.675…
## $ end_lat <dbl> 41.88918, 41.88918, 41.88918, 41.92182, 41.88918, 4…
## $ end_lng <dbl> -87.63851, -87.63851, -87.63851, -87.64414, -87.638…
## $ member_casual <chr> "member", "member", "member", "member", "member", "…glimpse(feb24)## Rows: 223,164
## Columns: 13
## $ ride_id <chr> "FCB05EB1758F85E8", "7FB986AD5D3DE9D6", "40CA13E15B…
## $ rideable_type <chr> "classic_bike", "classic_bike", "electric_bike", "c…
## $ started_at <dttm> 2024-02-03 14:14:18, 2024-02-05 21:10:06, 2024-02-…
## $ ended_at <dttm> 2024-02-03 14:21:00, 2024-02-05 21:15:44, 2024-02-…
## $ start_station_name <chr> "Clark St & Newport St", "Michigan Ave & Washington…
## $ start_station_id <chr> "632", "13001", "TA1309000029", "13235", "KA1503000…
## $ end_station_name <chr> "Southport Ave & Waveland Ave", "Wabash Ave & Grand…
## $ end_station_id <chr> "13235", "TA1307000117", "13243", "13229", "KA15030…
## $ start_lat <dbl> 41.94454, 41.88398, 41.91760, 41.94815, 41.83078, 4…
## $ start_lng <dbl> -87.65468, -87.62468, -87.68250, -87.66394, -87.632…
## $ end_lat <dbl> 41.94815, 41.89147, 41.91262, 41.93948, 41.83846, 4…
## $ end_lng <dbl> -87.66394, -87.62676, -87.68139, -87.66375, -87.635…
## $ member_casual <chr> "member", "member", "member", "member", "casual", "…glimpse(mar24) #has some NA data in start_station_name, start_station_id, end_station_name, end_station_id ## Rows: 301,687
## Columns: 13
## $ ride_id <chr> "64FBE3BAED5F29E6", "9991629435C5E20E", "E5C9FECD5B…
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", …
## $ started_at <dttm> 2024-03-05 18:33:11, 2024-03-06 17:15:14, 2024-03-…
## $ ended_at <dttm> 2024-03-05 18:51:48, 2024-03-06 17:16:04, 2024-03-…
## $ 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.94, 41.91, 41.91, 41.90, 41.93, 41.93, 41.94, 41…
## $ start_lng <dbl> -87.65, -87.64, -87.64, -87.63, -87.70, -87.70, -87…
## $ end_lat <dbl> 41.96, 41.91, 41.92, 41.89, 41.93, 41.95, 41.95, 41…
## $ end_lng <dbl> -87.65, -87.64, -87.64, -87.63, -87.72, -87.68, -87…
## $ member_casual <chr> "member", "member", "member", "member", "member", "…glimpse(apr24)## Rows: 415,025
## Columns: 13
## $ ride_id <chr> "743252713F32516B", "BE90D33D2240C614", "D47BBDDE7C…
## $ rideable_type <chr> "classic_bike", "electric_bike", "classic_bike", "c…
## $ started_at <dttm> 2024-04-22 19:08:21, 2024-04-11 06:19:24, 2024-04-…
## $ ended_at <dttm> 2024-04-22 19:12:56, 2024-04-11 06:22:21, 2024-04-…
## $ start_station_name <chr> "Aberdeen St & Jackson Blvd", "Aberdeen St & Jackso…
## $ start_station_id <chr> "13157", "13157", "TA1307000107", "13157", "TA13070…
## $ end_station_name <chr> "Desplaines St & Jackson Blvd", "Desplaines St & Ja…
## $ end_station_id <chr> "15539", "15539", "13249", "15539", "TA1308000029",…
## $ start_lat <dbl> 41.87773, 41.87772, 41.96167, 41.87773, 41.96161, 4…
## $ start_lng <dbl> -87.65479, -87.65496, -87.65464, -87.65479, -87.654…
## $ end_lat <dbl> 41.87812, 41.87812, 41.95606, 41.87812, 41.88683, 4…
## $ end_lng <dbl> -87.64395, -87.64395, -87.66884, -87.64395, -87.622…
## $ member_casual <chr> "member", "member", "member", "member", "member", "…glimpse(may24)## Rows: 609,493
## Columns: 13
## $ ride_id <chr> "7D9F0CE9EC2A1297", "02EC47687411416F", "101370FB2D…
## $ rideable_type <chr> "classic_bike", "classic_bike", "classic_bike", "el…
## $ started_at <dttm> 2024-05-25 15:52:42, 2024-05-14 15:11:51, 2024-05-…
## $ ended_at <dttm> 2024-05-25 16:11:50, 2024-05-14 15:22:00, 2024-05-…
## $ start_station_name <chr> "Streeter Dr & Grand Ave", "Sheridan Rd & Greenleaf…
## $ start_station_id <chr> "13022", "KA1504000159", "13022", "13022", "KA15040…
## $ end_station_name <chr> "Clark St & Elm St", "Sheridan Rd & Loyola Ave", "W…
## $ end_station_id <chr> "TA1307000039", "RP-009", "TA1309000010", "TA130700…
## $ start_lat <dbl> 41.89228, 42.01059, 41.89228, 41.89227, 41.90349, 4…
## $ start_lng <dbl> -87.61204, -87.66241, -87.61204, -87.61195, -87.643…
## $ end_lat <dbl> 41.90297, 42.00104, 41.87077, 41.93625, 41.90297, 4…
## $ end_lng <dbl> -87.63128, -87.66120, -87.62573, -87.65266, -87.631…
## $ member_casual <chr> "casual", "casual", "member", "member", "casual", "…glimpse(jun24)## Rows: 710,721
## Columns: 13
## $ ride_id <chr> "CDE6023BE6B11D2F", "462B48CD292B6A18", "9CFB6A858D…
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", …
## $ started_at <dttm> 2024-06-11 17:20:06, 2024-06-11 17:19:21, 2024-06-…
## $ ended_at <dttm> 2024-06-11 17:21:39, 2024-06-11 17:19:36, 2024-06-…
## $ 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.89, 41.89, 41.93, 41.88, 41.94, 41.94, 41.94, 41…
## $ start_lng <dbl> -87.65, -87.65, -87.65, -87.64, -87.64, -87.64, -87…
## $ end_lat <dbl> 41.89000, 41.89000, 41.94000, 41.88000, 41.94000, 4…
## $ end_lng <dbl> -87.65000, -87.65000, -87.65000, -87.64000, -87.640…
## $ member_casual <chr> "casual", "casual", "casual", "casual", "casual", "…glimpse(jul24)## Rows: 748,962
## Columns: 13
## $ ride_id <chr> "2658E319B13141F9", "B2176315168A47CE", "C2A9D33DF7…
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", …
## $ started_at <dttm> 2024-07-11 08:15:14, 2024-07-11 15:45:07, 2024-07-…
## $ ended_at <dttm> 2024-07-11 08:17:56, 2024-07-11 16:06:04, 2024-07-…
## $ start_station_name <chr> NA, NA, NA, NA, NA, NA, NA, NA, "California Ave & M…
## $ start_station_id <chr> NA, NA, NA, NA, NA, NA, NA, NA, "13084", NA, NA, NA…
## $ end_station_name <chr> NA, NA, NA, NA, NA, NA, NA, NA, "California Ave & M…
## $ end_station_id <chr> NA, NA, NA, NA, NA, NA, NA, NA, "13084", NA, NA, NA…
## $ start_lat <dbl> 41.80000, 41.79000, 41.79000, 41.88000, 41.95000, 4…
## $ start_lng <dbl> -87.59000, -87.60000, -87.59000, -87.64000, -87.640…
## $ end_lat <dbl> 41.79000, 41.80000, 41.79000, 41.90000, 41.91000, 4…
## $ end_lng <dbl> -87.59000, -87.59000, -87.60000, -87.67000, -87.620…
## $ member_casual <chr> "casual", "casual", "casual", "casual", "casual", "…glimpse(aug24)## Rows: 755,639
## Columns: 13
## $ ride_id <chr> "BAA154388A869E64", "8752245932EFF67A", "44DDF9F57A…
## $ rideable_type <chr> "classic_bike", "electric_bike", "classic_bike", "e…
## $ started_at <dttm> 2024-08-02 13:35:14, 2024-08-02 15:33:13, 2024-08-…
## $ ended_at <dttm> 2024-08-02 13:48:24, 2024-08-02 15:55:23, 2024-08-…
## $ start_station_name <chr> "State St & Randolph St", "Franklin St & Monroe St"…
## $ start_station_id <chr> "TA1305000029", "TA1309000007", "TA1309000007", "TA…
## $ end_station_name <chr> "Wabash Ave & 9th St", "Damen Ave & Cortland St", "…
## $ end_station_id <chr> "TA1309000010", "13133", "TA1307000039", "TA1306000…
## $ start_lat <dbl> 41.88462, 41.88032, 41.88032, 41.90297, 41.96640, 4…
## $ start_lng <dbl> -87.62783, -87.63519, -87.63519, -87.63128, -87.688…
## $ end_lat <dbl> 41.87077, 41.91598, 41.90297, 41.89259, 41.95606, 4…
## $ end_lng <dbl> -87.62573, -87.67733, -87.63128, -87.61729, -87.668…
## $ member_casual <chr> "member", "member", "member", "member", "casual", "…glimpse(sep24)## Rows: 821,276
## Columns: 13
## $ ride_id <chr> "31D38723D5A8665A", "67CB39987F4E895B", "DA61204FD2…
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", …
## $ started_at <dttm> 2024-09-26 15:30:58, 2024-09-26 15:31:32, 2024-09-…
## $ ended_at <dttm> 2024-09-26 15:30:59, 2024-09-26 15:53:13, 2024-09-…
## $ 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.91, 41.91, 41.90, 41.91, 41.90, 41.90, 41.89, 41…
## $ start_lng <dbl> -87.63, -87.63, -87.62, -87.63, -87.69, -87.63, -87…
## $ end_lat <dbl> 41.91, 41.91, 41.90, 41.90, 41.90, 41.90, 41.89, 41…
## $ end_lng <dbl> -87.63, -87.63, -87.63, -87.62, -87.63, -87.69, -87…
## $ member_casual <chr> "member", "member", "member", "member", "member", "…glimpse(oct24)## Rows: 616,281
## Columns: 13
## $ ride_id <chr> "4422E707103AA4FF", "19DB722B44CBE82F", "20AE2509FD…
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", …
## $ started_at <dttm> 2024-10-14 03:26:04, 2024-10-13 19:33:38, 2024-10-…
## $ ended_at <dttm> 2024-10-14 03:32:56, 2024-10-13 19:39:04, 2024-10-…
## $ 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.96, 41.98, 41.97, 41.95, 41.98, 41.88, 41.89, 41…
## $ start_lng <dbl> -87.65, -87.67, -87.66, -87.65, -87.67, -87.65, -87…
## $ end_lat <dbl> 41.98, 41.97, 41.95, 41.96, 41.98, 41.89, 41.88, 41…
## $ end_lng <dbl> -87.67, -87.66, -87.65, -87.65, -87.67, -87.64, -87…
## $ member_casual <chr> "member", "member", "member", "member", "member", "…glimpse(nov24)## Rows: 335,075
## Columns: 13
## $ ride_id <chr> "578DDD7CE1771FFA", "78B141C50102ABA6", "1E794CF363…
## $ rideable_type <chr> "classic_bike", "classic_bike", "classic_bike", "cl…
## $ started_at <dttm> 2024-11-07 19:21:58, 2024-11-22 14:49:00, 2024-11-…
## $ ended_at <dttm> 2024-11-07 19:28:57, 2024-11-22 14:56:15, 2024-11-…
## $ start_station_name <chr> "Walsh Park", "Walsh Park", "Walsh Park", "Clark St…
## $ start_station_id <chr> "18067", "18067", "18067", "TA1307000039", "TA13070…
## $ end_station_name <chr> "Leavitt St & North Ave", "Leavitt St & Armitage Av…
## $ end_station_id <chr> "TA1308000005", "TA1309000029", "13133", "TA1307000…
## $ start_lat <dbl> 41.91461, 41.91461, 41.91461, 41.90297, 41.93650, 4…
## $ start_lng <dbl> -87.66797, -87.66797, -87.66797, -87.63128, -87.647…
## $ end_lat <dbl> 41.91053, 41.91781, 41.91598, 41.93125, 41.89228, 4…
## $ end_lng <dbl> -87.68231, -87.68244, -87.67733, -87.64434, -87.612…
## $ member_casual <chr> "member", "member", "member", "member", "casual", "…glimpse(dec24)## Rows: 178,372
## Columns: 13
## $ ride_id <chr> "6C960DEB4F78854E", "C0913EEB2834E7A2", "848A37DD47…
## $ rideable_type <chr> "electric_bike", "classic_bike", "classic_bike", "e…
## $ started_at <dttm> 2024-12-31 01:38:35, 2024-12-21 18:41:26, 2024-12-…
## $ ended_at <dttm> 2024-12-31 01:48:45, 2024-12-21 18:47:33, 2024-12-…
## $ start_station_name <chr> "Halsted St & Roscoe St", "Clark St & Wellington Av…
## $ start_station_id <chr> "TA1309000025", "TA1307000136", "TA1307000107", "13…
## $ end_station_name <chr> "Clark St & Winnemac Ave", "Halsted St & Roscoe St"…
## $ end_station_id <chr> "TA1309000035", "TA1309000025", "13137", "chargings…
## $ start_lat <dbl> 41.94363, 41.93650, 41.96167, 41.87773, 41.87306, 4…
## $ start_lng <dbl> -87.64908, -87.64754, -87.65464, -87.65479, -87.669…
## $ end_lat <dbl> 41.97335, 41.94363, 41.93758, 41.88360, 41.86662, 4…
## $ end_lng <dbl> -87.66786, -87.64908, -87.64410, -87.64863, -87.694…
## $ member_casual <chr> "member", "member", "member", "member", "member", "…It seems like we see some NA data for March 2024 data, this will not be an issue as we will ignore any uncompleted data for the analysis.
Janitor
Check if vector names and data types are the same for all these dataframes before combining them, since the data vectors and types appear to be the same.
By using the R function of compare_df_cols_same, we can
verify this.
If it returns TRUE, it means that the data sets share the same column names with the same data type, which is the prerequisite of joining data sets into a single dataframe.
compare_df_cols_same(jan24, feb24, mar24, apr24, may24, jun24, jul24, aug24, sep24, oct24, nov24, dec24)## [1] TRUEJoining the data sets
We will be joining the data sets by using the bind_rows
function from the dplyr package of the
tidyverse into the data frame called
bike_data_24.
bike_data_24 <- bind_rows(jan24, feb24, mar24, apr24, may24, jun24, jul24, aug24, sep24, oct24, nov24, dec24)
summary(bike_data_24)## ride_id rideable_type started_at
## Length:5860568 Length:5860568 Min. :2024-01-01 00:00:39.00
## Class :character Class :character 1st Qu.:2024-05-20 19:47:53.00
## Mode :character Mode :character Median :2024-07-22 20:36:16.27
## Mean :2024-07-17 07:55:47.61
## 3rd Qu.:2024-09-17 20:14:22.56
## Max. :2024-12-31 23:56:49.84
##
## ended_at start_station_name start_station_id
## Min. :2024-01-01 00:04:20.00 Length:5860568 Length:5860568
## 1st Qu.:2024-05-20 20:07:54.75 Class :character Class :character
## Median :2024-07-22 20:53:59.16 Mode :character Mode :character
## Mean :2024-07-17 08:13:06.54
## 3rd Qu.:2024-09-17 20:27:46.02
## Max. :2024-12-31 23:59:55.70
##
## end_station_name end_station_id start_lat start_lng
## Length:5860568 Length:5860568 Min. :41.64 Min. :-87.91
## Class :character Class :character 1st Qu.:41.88 1st Qu.:-87.66
## Mode :character Mode :character Median :41.90 Median :-87.64
## Mean :41.90 Mean :-87.65
## 3rd Qu.:41.93 3rd Qu.:-87.63
## Max. :42.07 Max. :-87.52
##
## end_lat end_lng member_casual
## Min. :16.06 Min. :-144.05 Length:5860568
## 1st Qu.:41.88 1st Qu.: -87.66 Class :character
## Median :41.90 Median : -87.64 Mode :character
## Mean :41.90 Mean : -87.65
## 3rd Qu.:41.93 3rd Qu.: -87.63
## Max. :87.96 Max. : 152.53
## NA's :7232 NA's :7232Main thing to check for is to check for any obvious outliers like
negative time values or lat/lng values being out of reasonable range. We
can observe that the Min. and Max. value for end_lat and
end_lng are clearly out of range from the rest of the
data.
We will need to drop the out of range rides.
Dropping the out-of-range rides
We start by defining a reasonable range for Chicago, let’s use:
Latitude ≈ 41.6 – 42.1
Longitude ≈ −88.0 – −87.5
based on the start_lat and start_lng
values.
By using the filter function from dplyr, we
can specify the range we want and drop others outside of that range.
Then confirm and recheck it worked.
bike_data_24_clean <- bike_data_24 %>%
filter(
between(end_lat, 41.6, 42.1),
between(end_lng, -88.0, -87.5)
)
# confirm it worked
summary(bike_data_24_clean$end_lat)## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 41.61 41.88 41.90 41.90 41.93 42.10summary(bike_data_24_clean$end_lng)## Min. 1st Qu. Median Mean 3rd Qu. Max.
## -87.99 -87.66 -87.64 -87.65 -87.63 -87.50Trim blank spaces
If the ride_id values contain extra blank spaces, they
might appear unique until those spaces are trimmed. Then, if we remove
duplicates before trimming, we might be keeping rows that later become
duplicates once the spaces are removed. To avoid this, you should trim
the blank spaces first before removing duplicates using
str_squish function:
bike_data_24 <- bike_data_24 %>%
mutate(across(where(is.character), str_squish))Next, checking the unique options of each column to see there are no different versions of the same but with different blank spaces.
#better way to check unique options of each column
bike_data_24 %>% distinct(rideable_type, member_casual) #both## # A tibble: 6 × 2
## rideable_type member_casual
## <chr> <chr>
## 1 electric_bike member
## 2 classic_bike member
## 3 classic_bike casual
## 4 electric_bike casual
## 5 electric_scooter member
## 6 electric_scooter casualbike_data_24 %>% distinct(rideable_type) #one by one, this is clearer## # A tibble: 3 × 1
## rideable_type
## <chr>
## 1 electric_bike
## 2 classic_bike
## 3 electric_scooterbike_data_24 %>% distinct(member_casual) #one by one, this is clearer## # A tibble: 2 × 1
## member_casual
## <chr>
## 1 member
## 2 casualRemoving duplicates
Checking for duplicate rows.
bike_data_24 %>%
get_dupes(ride_id)## # A tibble: 422 × 14
## ride_id dupe_count rideable_type started_at ended_at
## <chr> <int> <chr> <dttm> <dttm>
## 1 011C8EF97AB… 2 classic_bike 2024-05-31 19:45:38 2024-06-01 20:45:33
## 2 011C8EF97AB… 2 classic_bike 2024-05-31 19:45:38 2024-06-01 20:45:33
## 3 01406457A85… 2 electric_bike 2024-05-31 23:54:59 2024-06-01 00:01:47
## 4 01406457A85… 2 electric_bike 2024-05-31 23:54:59 2024-06-01 00:01:47
## 5 02606FBC7F8… 2 classic_bike 2024-05-31 17:55:01 2024-06-01 18:54:53
## 6 02606FBC7F8… 2 classic_bike 2024-05-31 17:55:01 2024-06-01 18:54:53
## 7 0354FD07563… 2 electric_bike 2024-05-31 23:34:36 2024-06-01 00:14:29
## 8 0354FD07563… 2 electric_bike 2024-05-31 23:34:36 2024-06-01 00:14:29
## 9 048C715F1DE… 2 electric_bike 2024-05-31 23:53:44 2024-06-01 00:12:26
## 10 048C715F1DE… 2 electric_bike 2024-05-31 23:53:44 2024-06-01 00:12:26
## # ℹ 412 more rows
## # ℹ 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>Since it shows that there are duplicates of 422 rows, removing the
duplicates based on their ride_id by using the
distinct function, since it is our primary key to identify
individual rides.
#since there are duplicates of 422 rows, remove duplicates based on ride_id
bike_data_24 <- bike_data_24 %>%
distinct(ride_id, .keep_all = TRUE)Verifying now there should be 0 row of duplicates.
#double check for duplicates again
bike_data_24 %>%
get_dupes(ride_id)## No duplicate combinations found of: ride_id## # A tibble: 0 × 14
## # ℹ 14 variables: ride_id <chr>, dupe_count <int>, 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>Adding ride length and day of week columns
After cleaning our data from duplicate rows, now it is safe to add
both ride_length and day_of_week as below:
#Add ride length and day of week
bike_data_24 <- bike_data_24 %>%
mutate(
ride_length = ended_at - started_at,
day_of_week = wday(started_at)
)This is a crucial step for the analysis we will be doing later in terms of examining the behaviour of riders in terms of their ride length and day of the week trends.
Then let’s verify if ride length and day of week were added correctly.
#checking if ride length and day of week were correctly added
glimpse(bike_data_24)## Rows: 5,860,357
## Columns: 15
## $ ride_id <chr> "C1D650626C8C899A", "EECD38BDB25BFCB0", "F4A9CE7806…
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", …
## $ started_at <dttm> 2024-01-12 15:30:27, 2024-01-08 15:45:46, 2024-01-…
## $ ended_at <dttm> 2024-01-12 15:37:59, 2024-01-08 15:52:59, 2024-01-…
## $ start_station_name <chr> "Wells St & Elm St", "Wells St & Elm St", "Wells St…
## $ start_station_id <chr> "KA1504000135", "KA1504000135", "KA1504000135", "TA…
## $ end_station_name <chr> "Kingsbury St & Kinzie St", "Kingsbury St & Kinzie …
## $ end_station_id <chr> "KA1503000043", "KA1503000043", "KA1503000043", "13…
## $ start_lat <dbl> 41.90327, 41.90294, 41.90295, 41.88430, 41.94880, 4…
## $ start_lng <dbl> -87.63474, -87.63444, -87.63447, -87.63396, -87.675…
## $ end_lat <dbl> 41.88918, 41.88918, 41.88918, 41.92182, 41.88918, 4…
## $ end_lng <dbl> -87.63851, -87.63851, -87.63851, -87.64414, -87.638…
## $ member_casual <chr> "member", "member", "member", "member", "member", "…
## $ ride_length <drtn> 452 secs, 433 secs, 480 secs, 1789 secs, 1572 secs…
## $ day_of_week <dbl> 6, 2, 7, 2, 4, 1, 6, 5, 2, 4, 4, 4, 4, 6, 1, 4, 7, …Cancelled Column
We want to exclude cancelled rides from our analysis, let’s define it
as rides shorter than 60 seconds and then returned to the start station.
Creating a new column using the mutate function and the
case_when function to specify the conditions, to give us
either ‘Cancelled’ or ‘Not Cancelled’ to identify the rows which we can
filter out in the next step.
#cancelled column to check if ride was cancelled, defining it as rides shorter than 60 seconds and returned to start station
bike_data_24 <-
bike_data_24 %>%
mutate(cancellation = case_when(
start_station_name == end_station_name & ride_length < 60 ~"Cancelled",
TRUE ~ "Not Cancelled"
))Verifying if rows that fit the conditions specified above were labelled as “Cancelled” and vice-versa.
#checking if cancellation was correctly added which fits the conditions we set above
bike_data_24 %>%
filter(start_station_name == end_station_name, ride_length < 60) %>%
select(start_station_name, end_station_name, ride_length, cancellation) %>%
head(10)## # A tibble: 10 × 4
## start_station_name end_station_name ride_length cancellation
## <chr> <chr> <drtn> <chr>
## 1 Loomis St & Lexington St Loomis St & Lexington St 33 secs Cancelled
## 2 Halsted St & Wrightwood Ave Halsted St & Wrightwood… 19 secs Cancelled
## 3 Ashland Ave & Augusta Blvd Ashland Ave & Augusta B… 28 secs Cancelled
## 4 Halsted St & Wrightwood Ave Halsted St & Wrightwood… 20 secs Cancelled
## 5 Loomis St & Lexington St Loomis St & Lexington St 7 secs Cancelled
## 6 Loomis St & Lexington St Loomis St & Lexington St 40 secs Cancelled
## 7 Halsted St & Wrightwood Ave Halsted St & Wrightwood… 29 secs Cancelled
## 8 Halsted St & Wrightwood Ave Halsted St & Wrightwood… 2 secs Cancelled
## 9 Halsted St & Wrightwood Ave Halsted St & Wrightwood… 14 secs Cancelled
## 10 Halsted St & Wrightwood Ave Halsted St & Wrightwood… 2 secs Cancelled#checking for vice-versa
bike_data_24 %>%
filter(!(start_station_name == end_station_name & ride_length < 60)) %>%
select(start_station_name, end_station_name, ride_length, cancellation) %>%
head(10)## # A tibble: 10 × 4
## start_station_name end_station_name ride_length cancellation
## <chr> <chr> <drtn> <chr>
## 1 Wells St & Elm St Kingsbury St & Kinzie St 452 secs Not Cancell…
## 2 Wells St & Elm St Kingsbury St & Kinzie St 433 secs Not Cancell…
## 3 Wells St & Elm St Kingsbury St & Kinzie St 480 secs Not Cancell…
## 4 Wells St & Randolph St Larrabee St & Webster Ave 1789 secs Not Cancell…
## 5 Lincoln Ave & Waveland Ave Kingsbury St & Kinzie St 1572 secs Not Cancell…
## 6 Wells St & Elm St Kingsbury St & Kinzie St 519 secs Not Cancell…
## 7 Wells St & Elm St Kingsbury St & Kinzie St 534 secs Not Cancell…
## 8 Wells St & Elm St Kingsbury St & Kinzie St 491 secs Not Cancell…
## 9 Wells St & Elm St Kingsbury St & Kinzie St 609 secs Not Cancell…
## 10 Clark St & Ida B Wells Dr Kingsbury St & Kinzie St 537 secs Not Cancell…#explanation: filter the rows which are equal to the condition set above, select shows only the relevant column
#explanation: adding "!" just means the conditions outside of what is specified. Negation just swaps TRUE to FALSE and FALSE to TRUE
#data looks correctNow that we have identified cancelled rides, we need to exclude them and also to remove rows that have null value for start and end station (incomplete rides). This is to ensure we work with a ROCCC data set.
popular_rides <- bike_data_24 %>%
filter(cancellation != "Cancelled") %>%
select(c(start_station_name, end_station_name, ride_length, member_casual, start_lat, start_lng)) %>%
drop_na(start_station_name | end_station_name)
glimpse(popular_rides)## Rows: 4,171,791
## Columns: 6
## $ start_station_name <chr> "Wells St & Elm St", "Wells St & Elm St", "Wells St…
## $ end_station_name <chr> "Kingsbury St & Kinzie St", "Kingsbury St & Kinzie …
## $ ride_length <drtn> 452 secs, 433 secs, 480 secs, 1789 secs, 1572 secs…
## $ member_casual <chr> "member", "member", "member", "member", "member", "…
## $ start_lat <dbl> 41.90327, 41.90294, 41.90295, 41.88430, 41.94880, 4…
## $ start_lng <dbl> -87.63474, -87.63444, -87.63447, -87.63396, -87.675…Checking again to make sure no null rows are left, should expect 0 for both columns.
popular_rides %>%
summarise(
missing_start = sum(is.na(start_station_name)),
missing_end = sum(is.na(end_station_name))
)## # A tibble: 1 × 2
## missing_start missing_end
## <int> <int>
## 1 0 0Adding time and date columns
Creating time and date columns, extracting from
started_at column, dropping excess column not needed for
analysis, by using the %m, %d and
%H for Month, Day and Hour respectively.
bike_data_24 <-
bike_data_24 %>%
select(rideable_type, day_of_week, started_at, ride_length, member_casual, cancellation) %>%
mutate(
Month = as.integer(format(started_at, "%m")),
Day = as.integer(format(started_at, "%d")),
Hour = as.integer(format(started_at, "%H")),
member_casual = factor(member_casual, levels = c("casual", "member"),
labels = c("Casual", "Member"))
)Quick check if the code above worked as intended.
glimpse(bike_data_24) #correct## Rows: 5,860,357
## Columns: 9
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", "clas…
## $ day_of_week <dbl> 6, 2, 7, 2, 4, 1, 6, 5, 2, 4, 4, 4, 4, 6, 1, 4, 7, 4, 7,…
## $ started_at <dttm> 2024-01-12 15:30:27, 2024-01-08 15:45:46, 2024-01-27 12…
## $ ride_length <drtn> 452 secs, 433 secs, 480 secs, 1789 secs, 1572 secs, 519…
## $ member_casual <fct> Member, Member, Member, Member, Member, Member, Member, …
## $ cancellation <chr> "Not Cancelled", "Not Cancelled", "Not Cancelled", "Not …
## $ Month <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
## $ Day <int> 12, 8, 27, 29, 31, 7, 5, 4, 1, 3, 3, 3, 10, 12, 7, 24, 1…
## $ Hour <int> 15, 15, 12, 16, 5, 11, 14, 18, 14, 19, 7, 17, 17, 12, 8,…Clean the data frame
Now, let’s clean the data frame to exclude stolen bikes (bikes held longer than 24 hours according to the Divvy Bike website) and cancelled rides.
bike_data_clean <- bike_data_24 %>%
filter( cancellation == "Not Cancelled" & ride_length < 86400)
glimpse(bike_data_clean)## Rows: 5,816,407
## Columns: 9
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", "clas…
## $ day_of_week <dbl> 6, 2, 7, 2, 4, 1, 6, 5, 2, 4, 4, 4, 4, 6, 1, 4, 7, 4, 7,…
## $ started_at <dttm> 2024-01-12 15:30:27, 2024-01-08 15:45:46, 2024-01-27 12…
## $ ride_length <drtn> 452 secs, 433 secs, 480 secs, 1789 secs, 1572 secs, 519…
## $ member_casual <fct> Member, Member, Member, Member, Member, Member, Member, …
## $ cancellation <chr> "Not Cancelled", "Not Cancelled", "Not Cancelled", "Not …
## $ Month <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
## $ Day <int> 12, 8, 27, 29, 31, 7, 5, 4, 1, 3, 3, 3, 10, 12, 7, 24, 1…
## $ Hour <int> 15, 15, 12, 16, 5, 11, 14, 18, 14, 19, 7, 17, 17, 12, 8,…glimpse(bike_data_24)## Rows: 5,860,357
## Columns: 9
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", "clas…
## $ day_of_week <dbl> 6, 2, 7, 2, 4, 1, 6, 5, 2, 4, 4, 4, 4, 6, 1, 4, 7, 4, 7,…
## $ started_at <dttm> 2024-01-12 15:30:27, 2024-01-08 15:45:46, 2024-01-27 12…
## $ ride_length <drtn> 452 secs, 433 secs, 480 secs, 1789 secs, 1572 secs, 519…
## $ member_casual <fct> Member, Member, Member, Member, Member, Member, Member, …
## $ cancellation <chr> "Not Cancelled", "Not Cancelled", "Not Cancelled", "Not …
## $ Month <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
## $ Day <int> 12, 8, 27, 29, 31, 7, 5, 4, 1, 3, 3, 3, 10, 12, 7, 24, 1…
## $ Hour <int> 15, 15, 12, 16, 5, 11, 14, 18, 14, 19, 7, 17, 17, 12, 8,…And as always, let’s verify it with a different code. Using
unique to check the distinct values in the
cancellation column and the number of count of rides that
lasted longer than 24 hours using the sum function
respectively.
#verify
unique(bike_data_clean$cancellation)## [1] "Not Cancelled"sum(bike_data_clean$ride_length >= 86400) ## [1] 0Adding ride length in minutes
Adding ride_length_min to the current
bike_data_clean data frame.
#correct code to add ride_length_min, also rounding it off to 2 d.p.
bike_data_clean <- bike_data_clean %>%
mutate(
ride_length_min = as.difftime(round(as.numeric(ride_length, units = "secs") / 60, 2), units = "mins")
)
glimpse(bike_data_clean) #quick check## Rows: 5,816,407
## Columns: 10
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", "cl…
## $ day_of_week <dbl> 6, 2, 7, 2, 4, 1, 6, 5, 2, 4, 4, 4, 4, 6, 1, 4, 7, 4, …
## $ started_at <dttm> 2024-01-12 15:30:27, 2024-01-08 15:45:46, 2024-01-27 …
## $ ride_length <drtn> 452 secs, 433 secs, 480 secs, 1789 secs, 1572 secs, 5…
## $ member_casual <fct> Member, Member, Member, Member, Member, Member, Member…
## $ cancellation <chr> "Not Cancelled", "Not Cancelled", "Not Cancelled", "No…
## $ Month <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
## $ Day <int> 12, 8, 27, 29, 31, 7, 5, 4, 1, 3, 3, 3, 10, 12, 7, 24,…
## $ Hour <int> 15, 15, 12, 16, 5, 11, 14, 18, 14, 19, 7, 17, 17, 12, …
## $ ride_length_min <drtn> 7.53 mins, 7.22 mins, 8.00 mins, 29.82 mins, 26.20 mi…#note on how to think about nested functions
#write: outmost function 1st = as.difftime(....., units = "mins")
#then, innermost function which is converting column as numeric 1st and divide by 60
# ride_length_min <- as.numeric(ride_length, units = "secs") / 60
#then want to round it off, ride_length_min_rounded <- round(ride_length_min, 2)
#finally put this into as.difftime aboveAnalysis
Focusing on our first business task: How do annual members and casual riders use Cyclistic bikes differently?
Annual overview
Total rides and time
Let’s have a quick check on the current data frame before proceeding to give us a frame of reference.
glimpse(bike_data_clean)## Rows: 5,816,407
## Columns: 10
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", "cl…
## $ day_of_week <dbl> 6, 2, 7, 2, 4, 1, 6, 5, 2, 4, 4, 4, 4, 6, 1, 4, 7, 4, …
## $ started_at <dttm> 2024-01-12 15:30:27, 2024-01-08 15:45:46, 2024-01-27 …
## $ ride_length <drtn> 452 secs, 433 secs, 480 secs, 1789 secs, 1572 secs, 5…
## $ member_casual <fct> Member, Member, Member, Member, Member, Member, Member…
## $ cancellation <chr> "Not Cancelled", "Not Cancelled", "Not Cancelled", "No…
## $ Month <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
## $ Day <int> 12, 8, 27, 29, 31, 7, 5, 4, 1, 3, 3, 3, 10, 12, 7, 24,…
## $ Hour <int> 15, 15, 12, 16, 5, 11, 14, 18, 14, 19, 7, 17, 17, 12, …
## $ ride_length_min <drtn> 7.53 mins, 7.22 mins, 8.00 mins, 29.82 mins, 26.20 mi…Then summarising the data, counting number of rides using
n() and then the sum of time for each rider type
respectively.
#summarise 1st
df_summary <- bike_data_clean %>%
group_by(member_casual) %>%
summarise(
total_rides = n(),
total_time = sum(as.numeric(ride_length_min))) #ride length in min
glimpse(df_summary)## Rows: 2
## Columns: 3
## $ member_casual <fct> Casual, Member
## $ total_rides <int> 2130345, 3686062
## $ total_time <dbl> 44869116, 45188555Summarising the data first to see if using a pie chart in percentage will let us understand the data easier.
data_counts <- bike_data_clean %>%
group_by(member_casual) %>%
summarise(count = n()) %>%
mutate(percent = count / sum(count) * 100,
label = paste0(member_casual, " (", round(percent, 1), "%)"))
glimpse(data_counts) #quick check## Rows: 2
## Columns: 4
## $ member_casual <fct> Casual, Member
## $ count <int> 2130345, 3686062
## $ percent <dbl> 36.62648, 63.37352
## $ label <chr> "Casual (36.6%)", "Member (63.4%)"Plotting the pie chart. We can see that there are more Members than Casual riders at 63.4% vs 36.6%.
Plotting a bar chart to see both the total rides and total ride time by rider type.
Total ride time for both rider types are close to each other, however Member have more number of rides versus Casual riders. Our average ride length should show us that next that Casual riders have higher average ride length vs Member.
Average ride length
This time, summarise by using group_by for both
member_casual and rideable_type.
# summarise
df_summary2 <- bike_data_clean %>%
group_by(member_casual, rideable_type) %>%
summarise(
total_rides = n(),
total_time = sum(as.numeric(ride_length_min)) #ride length in min
)
glimpse(df_summary2)## Rows: 6
## Columns: 4
## Groups: member_casual [2]
## $ member_casual <fct> Casual, Casual, Casual, Member, Member, Member
## $ rideable_type <chr> "classic_bike", "electric_bike", "electric_scooter", "cl…
## $ total_rides <int> 965035, 1080547, 84763, 1750752, 1876545, 58765
## $ total_time <dbl> 27995355.8, 15856693.6, 1017066.3, 23497589.8, 21203949.…Calculating average ride length using the data frame above.
df_avg_ride_length2 <- df_summary2 %>%
group_by(member_casual, rideable_type) %>%
summarise(
average_ride_length = total_time / total_rides) #average ride length
as_tibble(df_avg_ride_length2)## # A tibble: 6 × 3
## member_casual rideable_type average_ride_length
## <fct> <chr> <dbl>
## 1 Casual classic_bike 29.0
## 2 Casual electric_bike 14.7
## 3 Casual electric_scooter 12.0
## 4 Member classic_bike 13.4
## 5 Member electric_bike 11.3
## 6 Member electric_scooter 8.29Plotting the graph using the facet_wrap function to show
both the type of member and rideable type for a more concise graph. We
were correct in our previous expectations, with Casual riders having
more than double the average ride length vs Members for Classic
bike.
##plot correct
ggplot(df_avg_ride_length2, aes(x = member_casual, y = average_ride_length, fill = member_casual)) +
geom_col(width = .8) +
geom_text(aes(label = comma(average_ride_length)),
vjust = -0.5,
size = 3) + # data labels on top of each bar
labs(
title = "Average Ride Length by Rider Type",
x = "Type of member",
y = "Minutes",
caption = "Data from divvybikes.com",
subtitle = "2024"
) +
facet_wrap(~rideable_type,
labeller = as_labeller(c(
classic_bike = "Classic bike",
electric_bike = "Electric bike",
electric_scooter = "Electric scooter"
)))+ #added facet wrap with rideable_type to see
scale_y_continuous(labels = comma) + # Format y-axis with commas
scale_fill_manual(values = c("Casual" = "#9ecae1", "Member" = "#3182bd")) + #colour
theme_minimal(base_size = 14) +
theme(legend.position = "none") # Remove legend if x-axis already shows the categories
Monthly analysis
Total rides
Moving on to monthly analysis, group the data by month and summarise. Let’s see if there are any noticeable trends from this initial plot. The summer and autumn months have significantly more rides than the other months.
Highlighting the months (May - Oct) where the rides are significantly higher for clarity.
## Rows: 12
## Columns: 3
## $ Month <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
## $ total_rides <int> 141848, 220472, 297900, 409865, 601433, 706607, 744800, 75…
## $ highlight <chr> "Normal", "Normal", "Normal", "Normal", "Highlight", "High…
Rides by rider type and month
The general trend for both Casual and Member is similar throughout the year.
Alternative stacked bar chart for clarity.
Day of week analysis
Total rides
Summarising to a new data frame for total rides vs day of the week.
#now check how rides vs Day of the week
df_rides_by_day <- bike_data_clean %>%
group_by(member_casual, day_of_week) %>%
summarise(total_rides = n()) %>%
ungroup()As seen, Casual peak on the weekends (convex-shaped) while Member riders peak in the middle of the week and drops in the weekend (concave-shaped), presumably Member riders primarily use the bike service to commute to work.
Plotting a heatmap for a more visual analysis. Again, using
facet_wrap to directly compare Casual and Member throughout
the year and throughout the week.
We can observe the trend of Casual peaks on weekends especially
Saturday, from May to Oct, being above the 20,000 rides baseline during
winter months.
While for Members, the trend is that rides peak in the middle of the week on Wednesdays and Thursdays, between June and Oct.
Time of day
Rides by hour
Focusing on just the trend between both Casual and Members, they both follow the same trend, only that Members having more number of rides vs Casual.
Plotting with quadrant lines for better clarity, we can see that the
general trend is that very little rides are happening in the first
quadrant from 0000 to 0600 hours, slowly increasing in the morning
quadrant from 0600 to 1200 hours, which there is a spike from 0700 to
0800 hours for Members commuting to work.
Both peak in the third quadrant, with a slight difference in trend:
Casual rides gradually increase and peak between 1600 to 1800 hours (47
%) while Members show a sudden spike from 1600 to 1700 hours (43 %),
most likely correlating with off-work hours.
After that, in the evening as it starts to get dark, the number of rides gradually decreases to a minimum.
## # A tibble: 8 × 5
## member_casual quadrant total_rides_quadrant percent_quadrant `2`
## <fct> <fct> <int> <dbl> <dbl>
## 1 Casual 0 - 6< hrs 99346 5 2
## 2 Casual 6 - 12< hrs 445536 21 2
## 3 Casual 12 - 18< hrs 1001414 47 2
## 4 Casual 18 - 23< hrs 584049 27 2
## 5 Member 0 - 6< hrs 114660 3 2
## 6 Member 6 - 12< hrs 1059761 29 2
## 7 Member 12 - 18< hrs 1591970 43 2
## 8 Member 18 - 23< hrs 919671 25 2Stolen bike data
Stolen bike data set
Creating a new data frame bike_data_stolen by using the
filter function to filter for ride_length
>= 86400 seconds or 24 hours.
#Stolen Bike Data,
bike_data_stolen <- bike_data_24 %>%
filter(ride_length >= 86400)
glimpse(bike_data_stolen) #quick check## Rows: 7,553
## Columns: 9
## $ rideable_type <chr> "classic_bike", "classic_bike", "classic_bike", "classic…
## $ day_of_week <dbl> 1, 4, 2, 4, 5, 4, 5, 3, 4, 4, 3, 3, 6, 5, 7, 3, 4, 3, 2,…
## $ started_at <dttm> 2024-01-21 09:01:01, 2024-01-17 08:44:40, 2024-01-29 07…
## $ ride_length <drtn> 88343 secs, 87901 secs, 86657 secs, 87749 secs, 87625 s…
## $ member_casual <fct> Member, Member, Member, Casual, Member, Member, Casual, …
## $ cancellation <chr> "Not Cancelled", "Not Cancelled", "Not Cancelled", "Not …
## $ Month <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
## $ Day <int> 21, 17, 29, 17, 18, 17, 25, 16, 17, 10, 16, 16, 26, 25, …
## $ Hour <int> 9, 8, 7, 9, 13, 7, 18, 8, 7, 13, 7, 12, 10, 15, 17, 22, …Total stolen bikes
Surprisingly, Casual has about 4.2 times more stolen bikes when compared to Member.
Stolen Bikes by Month
Looking at the breakdown by rider type and by month, it corresponds to the number of rides throughout the year, peaking between May and Oct.
## Rows: 24
## Columns: 4
## $ member_casual <fct> Casual, Casual, Casual, Casual, Casual, Casual, Casual, …
## $ Month <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6,…
## $ total_stolen <int> 126, 226, 300, 408, 681, 1016, 1021, 856, 640, 519, 215,…
## $ `factor(...)` <fct> Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, D…
Cancelled rides
Verifying again that the data frame bike_data_24 indeed
has both “Not Cancelled” and “Cancelled” in the
cancellation column.
unique(bike_data_24$cancellation) #check, has both "Not Cancelled" and "Cancelled"## [1] "Not Cancelled" "Cancelled"When we look at the percentage of rides cancelled, Casual is slightly higher at 0.7% vs 0.58% for Member.
## Rows: 2
## Columns: 5
## $ member_casual <fct> Casual, Member
## $ cancellation <chr> "Cancelled", "Cancelled"
## $ total_rides <int> 15067, 21330
## $ total_rides_type <int> 2151526, 3708831
## $ percent_cancelled <dbl> 0.7002937, 0.5751138
Now to check whether this is statistically significant by using the
prop.test() function (two-sample test). The p-value is
practically 0, <0.05, therefore rejecting the null hypothesis,
therefore there is a statistically significant difference in the
cancelled ride rates. However, we should still get stakeholders view on
this if the absolute value difference is practically meaningful (about
6000).
#test to show 0.7 and 0.6% is not statistically significant
glimpse(cancelled_data)## Rows: 2
## Columns: 5
## $ member_casual <fct> Casual, Member
## $ cancellation <chr> "Cancelled", "Cancelled"
## $ total_rides <int> 15067, 21330
## $ total_rides_type <int> 2151526, 3708831
## $ percent_cancelled <dbl> 0.7002937, 0.5751138n_casual_cancelled <- 15067
n_casual_total <-2151526
n_member_cancelled <- 21330
n_member_total <- 3708831
prop.test(x = c(n_casual_cancelled, n_member_cancelled),
n = c(n_casual_total, n_member_total))##
## 2-sample test for equality of proportions with continuity correction
##
## data: c(n_casual_cancelled, n_member_cancelled) out of c(n_casual_total, n_member_total)
## X-squared = 345.49, df = 1, p-value < 2.2e-16
## alternative hypothesis: two.sided
## 95 percent confidence interval:
## 0.001116012 0.001387585
## sample estimates:
## prop 1 prop 2
## 0.007002937 0.005751138# pvalue smaller than than 0.05, statistically significant, but practically (might) not be significant; depends on stakeholders viewTrip data
Round off the latitude and longitudes to the 4th decimal place which is accurate within ~9 m, which will merge slightly different GPS dumps to the same station (which is what we care about, rather than the specific, more detailed coordinates.)
#Trip data
popular_rides$start_lat <-
round(popular_rides$start_lat, digits = 4)
popular_rides$start_lng <-
round(popular_rides$start_lng, digits = 4)
head(popular_rides)## # A tibble: 6 × 6
## start_station_name end_station_name ride_length member_casual start_lat
## <chr> <chr> <drtn> <chr> <dbl>
## 1 Wells St & Elm St Kingsbury St & … 452 secs member 41.9
## 2 Wells St & Elm St Kingsbury St & … 433 secs member 41.9
## 3 Wells St & Elm St Kingsbury St & … 480 secs member 41.9
## 4 Wells St & Randolph St Larrabee St & W… 1789 secs member 41.9
## 5 Lincoln Ave & Waveland A… Kingsbury St & … 1572 secs member 41.9
## 6 Wells St & Elm St Kingsbury St & … 519 secs member 41.9
## # ℹ 1 more variable: start_lng <dbl>#Popular Start Locations
popular_start <- popular_rides %>%
group_by(member_casual, start_station_name) %>% # Group by both rider type and station name
summarise(n = n(), .groups = "drop") %>%
mutate(member_casual = recode(member_casual, "casual" = "Casual", "member" = "Member")) %>%
group_by(member_casual) %>%
slice_max(order_by = n, n = 5, with_ties = FALSE) %>%
mutate(start_station_name = fct_reorder(start_station_name, n, .desc = TRUE)) %>%
ungroup()
print(popular_start, n= 20)## # A tibble: 10 × 3
## member_casual start_station_name n
## <chr> <fct> <int>
## 1 Casual Streeter Dr & Grand Ave 47856
## 2 Casual DuSable Lake Shore Dr & Monroe St 31834
## 3 Casual Michigan Ave & Oak St 23123
## 4 Casual DuSable Lake Shore Dr & North Blvd 21202
## 5 Casual Millennium Park 20639
## 6 Member Kingsbury St & Kinzie St 26602
## 7 Member Clinton St & Washington Blvd 24982
## 8 Member Clinton St & Madison St 22497
## 9 Member Clark St & Elm St 22237
## 10 Member Clinton St & Jackson Blvd 18521
Mapping popular start stations
Mapping Popular Start Stations
#checking stations with multiple coordinates, because doing it based on lat/lng, doesn't show the exact same top5
popular_rides %>%
filter(member_casual == "member") %>%
group_by(start_station_name) %>%
summarise(
rides = n(),
n_coords = n_distinct(paste(start_lat, start_lng))
) %>%
arrange(desc(rides)) %>%
filter(n_coords > 1) %>%
head(10) # show which of the busiest stations have multiple coords; yes there are## # A tibble: 10 × 3
## start_station_name rides n_coords
## <chr> <int> <int>
## 1 Kingsbury St & Kinzie St 26602 142
## 2 Clinton St & Washington Blvd 24982 233
## 3 Clinton St & Madison St 22497 308
## 4 Clark St & Elm St 22237 148
## 5 Clinton St & Jackson Blvd 18521 201
## 6 Wells St & Concord Ln 18034 87
## 7 Wells St & Elm St 17831 161
## 8 Dearborn St & Erie St 17433 110
## 9 University Ave & 57th St 17343 34
## 10 Canal St & Madison St 16979 377#so we need to pull the top 5 exactly from the previous top 5 we have plotted
popular_start <- popular_rides %>%
group_by(member_casual, start_station_name) %>%
summarise(n = n(), .groups = "drop") %>%
mutate(member_casual = recode(member_casual,
"casual" = "Casual",
"member" = "Member")) %>%
group_by(member_casual) %>%
slice_max(n, n = 5, with_ties = FALSE) %>%
ungroup()
#Now pulling one canonical coord per station, 1st one seen
station_coords <- popular_rides %>%
distinct(start_station_name, start_lat, start_lng) %>%
group_by(start_station_name) %>%
# here “first” just picks the first lat/lng encountered for each name
summarise(
start_lat = first(start_lat),
start_lng = first(start_lng),
.groups = "drop"
)
#Join these 2 so map data exactly mirrors our top-5 list:
popular_start_map <- popular_start %>%
left_join(station_coords, by = "start_station_name")
popular_start_map_sf <- popular_start_map %>%
st_as_sf(coords = c("start_lng", "start_lat"), crs = 4326)
glimpse(popular_start_map_sf) #quick check## Rows: 10
## Columns: 4
## $ member_casual <chr> "Casual", "Casual", "Casual", "Casual", "Casual", "…
## $ start_station_name <chr> "Streeter Dr & Grand Ave", "DuSable Lake Shore Dr &…
## $ n <int> 47856, 31834, 23123, 21202, 20639, 26602, 24982, 22…
## $ geometry <POINT [°]> POINT (-87.612 41.8923), POINT (-87.6167 41.8…Casual rides
## Rows: 5
## Columns: 5
## $ member_casual <chr> "Casual", "Casual", "Casual", "Casual", "Casual"
## $ start_station_name <chr> "Streeter Dr & Grand Ave", "DuSable Lake Shore Dr &…
## $ n <int> 47856, 31834, 23123, 21202, 20639
## $ start_lat <dbl> 41.8923, 41.8810, 41.9012, 41.9117, 41.8812
## $ start_lng <dbl> -87.6120, -87.6167, -87.6233, -87.6268, -87.6240## Rows: 5
## Columns: 3
## $ lon <dbl> -87.6120, -87.6167, -87.6233, -87.6268, -87.6240
## $ lat <dbl> 41.8923, 41.8810, 41.9012, 41.9117, 41.8812
## $ start_station <chr> "Streeter Dr & Grand Ave", "DuSable Lake Shore Dr & Monr…## lon lat start_station
## 1 -87.6120 41.8923 Streeter Dr & Grand Ave
## 2 -87.6167 41.8810 DuSable Lake Shore Dr & Monroe St
## 3 -87.6233 41.9012 Michigan Ave & Oak St
## 4 -87.6268 41.9117 DuSable Lake Shore Dr & North Blvd
## 5 -87.6240 41.8812 Millennium ParkMember rides
## Rows: 5
## Columns: 5
## $ member_casual <chr> "Member", "Member", "Member", "Member", "Member"
## $ start_station_name <chr> "Kingsbury St & Kinzie St", "Clinton St & Washingto…
## $ n <int> 26602, 24982, 22497, 22237, 18521
## $ start_lat <dbl> 41.8892, 41.8831, 41.8828, 41.9027, 41.8781
## $ start_lng <dbl> -87.6385, -87.6414, -87.6412, -87.6317, -87.6416## Rows: 5
## Columns: 3
## $ lon_m <dbl> -87.6385, -87.6414, -87.6412, -87.6317, -87.6416
## $ lat_m <dbl> 41.8892, 41.8831, 41.8828, 41.9027, 41.8781
## $ start_station <chr> "Kingsbury St & Kinzie St", "Clinton St & Washington Blv…## lon_m lat_m start_station
## 1 -87.6385 41.8892 Kingsbury St & Kinzie St
## 2 -87.6414 41.8831 Clinton St & Washington Blvd
## 3 -87.6412 41.8828 Clinton St & Madison St
## 4 -87.6317 41.9027 Clark St & Elm St
## 5 -87.6416 41.8781 Clinton St & Jackson BlvdTop 5 trips by rider type
Checking Top 5 trips by rider type to see if the trend of start and end station for both Casual and Members.
Observation: Among the Casual rider type, 4 out of 5 of the most popular start station are also the top 5 destination. The only one exception: DuSable Lake Shore Dr & Monroe St to Streeter Dr & Grand Ave, likely reflects a more deliberate journey, example being going to the Jane Addams Memorial Park nearby instead of just a casual stroll in vicinity. In contrast, the other four stations are nearby major park areas hence riders might be more inclined to loop back to their starting point just for leisure cycling around the park.
While for Member riders, it is clear that they consistently travel back and forth between stations as part of their daily commute.
In conclusion, the patterns reveal that Casual riders use the bikes primarily for recreation, whereas Members rely on them as a reliable mode of transportation to and from work.
#Popular Start and End Locations
popular_start2 <- popular_rides %>%
group_by(member_casual, start_station_name, end_station_name) %>% # Group by both rider type and station name
summarise(n = n(), .groups = "drop") %>%
mutate(member_casual = recode(member_casual, "casual" = "Casual", "member" = "Member")) %>%
group_by(member_casual) %>%
slice_max(order_by = n, n = 5, with_ties = FALSE) %>%
mutate(start_station_name = fct_reorder(start_station_name, n, .desc = TRUE)) %>%
ungroup()
as_tibble(popular_start2)## # A tibble: 10 × 4
## member_casual start_station_name end_station_name n
## <chr> <fct> <chr> <int>
## 1 Casual Streeter Dr & Grand Ave Streeter Dr & Grand Ave 8413
## 2 Casual DuSable Lake Shore Dr & Monroe St DuSable Lake Shore Dr … 6877
## 3 Casual DuSable Lake Shore Dr & Monroe St Streeter Dr & Grand Ave 5265
## 4 Casual Michigan Ave & Oak St Michigan Ave & Oak St 4447
## 5 Casual Millennium Park Millennium Park 3177
## 6 Member State St & 33rd St Calumet Ave & 33rd St 5587
## 7 Member Calumet Ave & 33rd St State St & 33rd St 5506
## 8 Member Ellis Ave & 60th St University Ave & 57th … 3988
## 9 Member Ellis Ave & 60th St Ellis Ave & 55th St 3976
## 10 Member University Ave & 57th St Ellis Ave & 60th St 3942#For member: Besides the one way Ellis Ave & 60th St to Ellis Ave & 55th St, it is obvious that these trips
#are working people commuting back back and forth most likely. Interestingly, they don't share any same station as the
#previous oneRides returned to start station
Adding new column status using the mutate
function to indicate whether the bikes were returned to the start
station or not.
#Mutating the table to add column "status" to indicate if bikes were returned to the start station and dropping those missing station names.
return_status <- popular_rides %>%
mutate(status = case_when(
start_station_name == end_station_name ~ "Returned",
start_station_name != end_station_name ~ "Not Returned"))
print(head(return_status[c("start_station_name", "end_station_name", "status")])) #quick check## # A tibble: 6 × 3
## start_station_name end_station_name status
## <chr> <chr> <chr>
## 1 Wells St & Elm St Kingsbury St & Kinzie St Not Returned
## 2 Wells St & Elm St Kingsbury St & Kinzie St Not Returned
## 3 Wells St & Elm St Kingsbury St & Kinzie St Not Returned
## 4 Wells St & Randolph St Larrabee St & Webster Ave Not Returned
## 5 Lincoln Ave & Waveland Ave Kingsbury St & Kinzie St Not Returned
## 6 Wells St & Elm St Kingsbury St & Kinzie St Not ReturnedPercentage of Returned Bikes to Start Station
Casual riders unsurprisingly returned to their starting station at about 2x more frequently versus Member riders, implying that their rides were driven more by park-strolling as discussed before than a destination. This may also help to offer an explanation as to why their average ride times were longer on average as well.
## Rows: 2
## Columns: 4
## $ member_casual <chr> "Casual", "Member"
## $ status <chr> "Returned", "Returned"
## $ n <int> 130113, 65032
## $ percent <dbl> 3.118876, 1.558851
Price breakdown
Divvy Bikes offer annual memberships for USD 143.90, which includes unlimited 45-min Classic bike rides, 50% off Electric bikes vs Casual rides.
Other mentioned benefits, being able to earn Bike Angels points and rewards plus being able to exchange with Ebike credits, Lyft credit and membership extension.
Preparing the Excel files
Creating both the electric.csv and
classic.csv file in Excel with the columns shown below.
Electric bike
Pricing starts at USD 1 to unlock plus USD 0.44/minute (0 to unlock plus USD 0.18/minute for members).
Since the average ride Length for Casual and Member is 14.67 min and 11.30 min respectively, taking the average at 13 min for our calculation model.
For Electric bikes, calculating price with the Excel
formula, where annual_membership is 143.90 for
Member and 0 for Casual riders:
annual_membership + unlock_fee +
minutes*(ppm)
Classic bike
Pricing starts at USD 1 to unlock + USD 0.18/minute (USD 0 below 45 min for members)
Average ride length for Casual and Member is 29.01 min and 13.42 min respectively, but since for any ride below 45 min for Members is free, we will take use the Casual average ride length of 29.01 min for the calculation instead of the average of the two like the above.
For Casual riders, calculating price with the Excel
formula same as above where annual_membership is 0:
annual_membership + unlock_fee +
minutes*(ppm)
However, for Member riders, since it costs USD 0 below 45 min, using ride length of 29.01 min means it is effectively 0, with the only cost being the annual membership of USD 143.90
electric <- read.csv("electric.csv")
classic <- read.csv("classic.csv")
glimpse(electric) #quick check## Rows: 120
## Columns: 7
## $ rides <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17,…
## $ member_type <chr> "Member", "Member", "Member", "Member", "Member", "Member"…
## $ bike_type <chr> "electric", "electric", "electric", "electric", "electric"…
## $ unlock_fee <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
## $ ppm <dbl> 0.18, 0.18, 0.18, 0.18, 0.18, 0.18, 0.18, 0.18, 0.18, 0.18…
## $ minutes <int> 13, 13, 13, 13, 13, 13, 13, 13, 13, 13, 13, 13, 13, 13, 13…
## $ price <dbl> 146.24, 148.58, 150.92, 153.26, 155.60, 157.94, 160.28, 16…glimpse(classic) #quick check## Rows: 120
## Columns: 7
## $ rides <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17,…
## $ member_type <chr> "Member", "Member", "Member", "Member", "Member", "Member"…
## $ bike_type <chr> "classic", "classic", "classic", "classic", "classic", "cl…
## $ unlock_fee <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
## $ ppm <dbl> 0.18, 0.18, 0.18, 0.18, 0.18, 0.18, 0.18, 0.18, 0.18, 0.18…
## $ minutes <dbl> 13, 13, 13, 13, 13, 13, 13, 13, 13, 13, 13, 13, 13, 13, 13…
## $ price <dbl> 143.9, 143.9, 143.9, 143.9, 143.9, 143.9, 143.9, 143.9, 14…Price model analysis
Electric bike
With the annual membership, Casual riders will save when they take Electric bike rides at above 33 rides or more in a year, which saving gets bigger with more rides afterwards.
Classic Bike
Meanwhile, for Classic bike rides, it only takes 23 rides to break even. Any further rides with an annual membership is cheaper versus not having it.
Findings
Rider volume & ride duration
Members take more total rides annually, but Casual riders average over twice the ride length of members, especially on Classic bikes which indicates leisure usage.
Seasonal & weekly trends
Both rider types peak May–October, with members exceeding 60,000 rides per month at their peaks.
Casual usage surges on weekends while Member usage is highest on weekdays Monday–Friday.
Hourly patterns
Members show clear commuting peaks: 7:00–8:00 AM and 4:00–6:00 PM. This clearly correlates with the beginning and the end of office work hours.
Casual ridership climbs after noon, peaking 4:00–6:00 PM, consistent with recreational afternoon outings.
Theft & cancellation
Casual riders incur bike‐theft charges roughly 4 times higher than members, with thefts peaking alongside summer ridership (May–September).
Cancellation rates are close at 0.70 % (Casual) vs. 0.58 % (Member). A two-sample proportion test yields p < 0.05, indicating statistical significance, though the absolute difference (~0.12 %) may have limited practical significance.
Geographic usage
Casual trips frequently start and end near attractions or parks (e.g., Millennium Park, Lincoln Park) and often form loops back to their origin station.
Member trips are concentrated within the urban core, reflecting consistent daily work commuting.
Act
Recommendations
Promote membership savings
We should highlight to Casual riders that after 23 Classic-bike rides or 33 Electric-bike rides per year, a membership becomes cost-effective. Incorporate this breakpoint into marketing materials and in-app notifications as the main point.
University outreach campaign
Target University of Chicago students and staff with a limited-time offer (e.g., discounted first-year membership or referral bonus). Their high campus density and routine travel patterns make them ideal converts.
Optimize station placement
Expand capacity and improve amenities (e.g., more docks, lighting, signage) at stations adjacent to the top Casual-use areas such as Millennium Park, Lincoln Park, neighborhood attractions to better serve recreational users and encourage repeat trips.
Future notes
Enrich user demographics
Collect more data like on users’ residency status (local vs. visitor) and ride-pass type. This will enable tailored promotions (e.g., tourist day passes vs. annual memberships) and improve targeting for local campaigns.
Enhance station metrics
Track station-level details such as average dwell time, dock availability, and peak congestion periods. These insights will guide more precise station upgrades and dynamic rebalancing strategies.