About the company

In 2016, Cyclistic launched a successful bike-share offering. Since then, the program has grown to a fleet of 5,824 bicycles that are geotracked and locked into a network of 692 stations across Chicago. Lily Moreno is the director of marketing. Annual members are much more profitable than casual riders and Moreno believes that maximizing the number of annual members will be key to future growth of the company.

Phase 1: Ask:

  • Create a business objective that aligns with Stakeholders desired outcomes and expectations

Business Task

Draw insights from data to identify trends in use of bike sharing to help convert Casual riders to Annual members.

Phase 2: Prepare:

  • Exploratory data analysis to check basic characteristics of dataset.
  • Check dataset for ROCCC.
  • Check data licensing, privacy, security, accessibility and integrity.
  • What are problems with data and how will data help answer the question.

Loading Packages

library(tidyverse)
library(lubridate)
library(janitor)
library(dplyr)
library(ggpubr)
library(skimr)
library(hydroTSM)
library(reshape2)
library(patchwork)
library(rmarkdown)
library(scales)

Uploading Datasets

jan22 <- read_csv("r/New folder/202201-divvy-tripdata.csv")
feb22 <- read_csv("r/New folder/202202-divvy-tripdata.csv")
mar22 <- read_csv("r/New folder/202203-divvy-tripdata.csv")
apr22 <- read_csv("r/New folder/202204-divvy-tripdata.csv")
may22 <- read_csv("r/New folder/202205-divvy-tripdata.csv")
jun22 <- read_csv("r/New folder/202206-divvy-tripdata.csv")
jul22 <- read_csv("r/New folder/202207-divvy-tripdata.csv")
aug22 <- read_csv("r/New folder/202208-divvy-tripdata.csv")
sep22 <- read_csv("r/New folder/202209-divvy-publictripdata.csv")
oct22 <- read_csv("r/New folder/202210-divvy-tripdata.csv")
nov22 <- read_csv("r/New folder/202211-divvy-tripdata.csv")
dec22 <- read_csv("r/New folder/202212-divvy-tripdata.csv")

Merging datasets:

Checking datasets for correct column names in order to merge

colnames(jan22)
##  [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"      "ride_length"
colnames(feb22)
##  [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"      "ride_length"
colnames(mar22)
##  [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"      "ride_length"
colnames(apr22)
##  [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"      "ride_length"
colnames(may22)
##  [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"      "ride_length"
colnames(jun22)
##  [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"      "ride_length"
colnames(jul22)
##  [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"      "ride_length"
colnames(aug22)
##  [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"      "ride_length"
colnames(sep22)
##  [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"      "ride_length"
colnames(oct22)
##  [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"      "ride_length"
colnames(nov22)
##  [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"      "ride_length"
colnames(dec22)
##  [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"      "ride_length"

Merging Datasets

bike_data <- bind_rows(jan22, feb22, mar22, apr22, may22, jun22, jul22, aug22, sep22, oct22, nov22, dec22)

Exploratory Data Analysis of dataset

colnames(bike_data)
##  [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"      "ride_length"
str(bike_data)
## spc_tbl_ [5,667,717 × 14] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
##  $ ride_id           : chr [1:5667717] "C2F7DD78E82EC875" "A6CF8980A652D272" "BD0F91DFF741C66D" "CBB80ED419105406" ...
##  $ rideable_type     : chr [1:5667717] "electric_bike" "electric_bike" "classic_bike" "classic_bike" ...
##  $ started_at        : chr [1:5667717] "13-01-2022 11:59" "10-01-2022 08:41" "25-01-2022 04:53" "04-01-2022 00:18" ...
##  $ ended_at          : chr [1:5667717] "13-01-2022 12:02" "10-01-2022 08:46" "25-01-2022 04:58" "04-01-2022 00:33" ...
##  $ start_station_name: chr [1:5667717] "Glenwood Ave & Touhy Ave" "Glenwood Ave & Touhy Ave" "Sheffield Ave & Fullerton Ave" "Clark St & Bryn Mawr Ave" ...
##  $ start_station_id  : chr [1:5667717] "525" "525" "TA1306000016" "KA1504000151" ...
##  $ end_station_name  : chr [1:5667717] "Clark St & Touhy Ave" "Clark St & Touhy Ave" "Greenview Ave & Fullerton Ave" "Paulina St & Montrose Ave" ...
##  $ end_station_id    : chr [1:5667717] "RP-007" "RP-007" "TA1307000001" "TA1309000021" ...
##  $ start_lat         : num [1:5667717] 42 42 41.9 42 41.9 ...
##  $ start_lng         : num [1:5667717] -87.7 -87.7 -87.7 -87.7 -87.6 ...
##  $ end_lat           : num [1:5667717] 42 42 41.9 42 41.9 ...
##  $ end_lng           : num [1:5667717] -87.7 -87.7 -87.7 -87.7 -87.6 ...
##  $ member_casual     : chr [1:5667717] "casual" "casual" "member" "casual" ...
##  $ ride_length       : 'hms' num [1:5667717] 00:02:00 00:04:00 00:04:00 00:14:00 ...
##   ..- attr(*, "units")= chr "secs"
##  - attr(*, "spec")=
##   .. cols(
##   ..   ride_id = col_character(),
##   ..   rideable_type = col_character(),
##   ..   started_at = col_character(),
##   ..   ended_at = col_character(),
##   ..   start_station_name = col_character(),
##   ..   start_station_id = col_character(),
##   ..   end_station_name = col_character(),
##   ..   end_station_id = col_character(),
##   ..   start_lat = col_double(),
##   ..   start_lng = col_double(),
##   ..   end_lat = col_double(),
##   ..   end_lng = col_double(),
##   ..   member_casual = col_character(),
##   ..   ride_length = col_time(format = "")
##   .. )
##  - attr(*, "problems")=<externalptr>
head(bike_data)
## # A tibble: 6 × 14
##   ride_id        ridea…¹ start…² ended…³ start…⁴ start…⁵ end_s…⁶ end_s…⁷ start…⁸
##   <chr>          <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>     <dbl>
## 1 C2F7DD78E82EC… electr… 13-01-… 13-01-… Glenwo… 525     Clark … RP-007     42.0
## 2 A6CF8980A652D… electr… 10-01-… 10-01-… Glenwo… 525     Clark … RP-007     42.0
## 3 BD0F91DFF741C… classi… 25-01-… 25-01-… Sheffi… TA1306… Greenv… TA1307…    41.9
## 4 CBB80ED419105… classi… 04-01-… 04-01-… Clark … KA1504… Paulin… TA1309…    42.0
## 5 DDC963BFDDA51… classi… 20-01-… 20-01-… Michig… TA1309… State … TA1305…    41.9
## 6 A39C6F6CC0586… classi… 11-01-… 11-01-… Wood S… 637     Honore… TA1305…    41.9
## # … with 5 more variables: start_lng <dbl>, end_lat <dbl>, end_lng <dbl>,
## #   member_casual <chr>, ride_length <time>, and abbreviated variable names
## #   ¹​rideable_type, ²​started_at, ³​ended_at, ⁴​start_station_name,
## #   ⁵​start_station_id, ⁶​end_station_name, ⁷​end_station_id, ⁸​start_lat

bike_data Dataset has 14 variables and 4369291 observations describing about ride type, ride start time, end time, rider type and ride length

Upon checking it is identified that some of the observations are blank therefore data is not Reliable but since for practice purposes data is analysed. Data is provided by 2nd party “Bikeshare” and as per the License users personal information has been excluded hence clearing the dataset for Licensing, Privacy, security and accessibility.

Dataset structure has certain impurities which needs to be cleaned.

Phase 3: Process:

  • Data Cleaning
  • Data transformation

Data Cleaning

Removing Outliers

bike_data <- bike_data %>% 
  select(-start_lat, -start_lng, -end_lat, -end_lng)

Removing variables that are unnecessary for analysis purpose

Eliminating Duplicates

bike_data <- bike_data %>% 
  distinct()

Fixing Null values

sum(is.null(bike_data))
## [1] 0
bike_data <- bike_data %>% 
  drop_na()

Data Transformation

Standardizing Column names

bike_data <- rename_with(bike_data, tolower)

Creating date column with date format

bike_data$date <- dmy_hm(bike_data$started_at)

Creating month, weekday and season Attributes

bike_data$month <- format(as.Date(bike_data$date), "%B")

bike_data$weekday <- format(as.Date(bike_data$date), "%A")

bike_data$season <- time2season(bike_data$date, out.fmt = "seasons")

As per SeasonsYear.com “Seasons of the year” project [https://seasonsyear.com/USA/Illinois/Chicago]

Phase 4: Analyze and Share

Dataset Summary

bike_data %>% 
  summary()
##    ride_id          rideable_type       started_at          ended_at        
##  Length:4369291     Length:4369291     Length:4369291     Length:4369291    
##  Class :character   Class :character   Class :character   Class :character  
##  Mode  :character   Mode  :character   Mode  :character   Mode  :character  
##                                                                             
##                                                                             
##                                                                             
##  start_station_name start_station_id   end_station_name   end_station_id    
##  Length:4369291     Length:4369291     Length:4369291     Length:4369291    
##  Class :character   Class :character   Class :character   Class :character  
##  Mode  :character   Mode  :character   Mode  :character   Mode  :character  
##                                                                             
##                                                                             
##                                                                             
##  member_casual      ride_length            date                       
##  Length:4369291     Length:4369291    Min.   :2022-01-01 00:00:00.00  
##  Class :character   Class1:hms        1st Qu.:2022-05-29 10:29:00.00  
##  Mode  :character   Class2:difftime   Median :2022-07-20 21:22:00.00  
##                     Mode  :numeric    Mean   :2022-07-19 14:05:57.00  
##                                       3rd Qu.:2022-09-14 18:22:00.00  
##                                       Max.   :2022-12-31 23:59:00.00  
##     month             weekday             season         
##  Length:4369291     Length:4369291     Length:4369291    
##  Class :character   Class :character   Class :character  
##  Mode  :character   Mode  :character   Mode  :character  
##                                                          
##                                                          
##

Data summary help identify any statistical insights from data

Weeekly analysis

bike_data_weekday_casual <- bike_data %>% 
  group_by(weekday) %>% 
  filter(member_casual == "casual") %>% 
  summarise(weekdaycasual = mean(ride_length))

bike_data_weekday_member <-bike_data %>% 
  group_by(weekday) %>% 
  filter(member_casual == "member") %>% 
  summarise(weekdaymember = mean(ride_length))

bike_data_weekday <- merge(bike_data_weekday_casual, bike_data_weekday_member, by = c("weekday"))
bike_data_weekday$weekday <- factor(bike_data_weekday$weekday, levels = c("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")) 
bike_data_weekday <- bike_data_weekday %>% 
  rename("casual riders" = weekdaycasual, "member riders" = weekdaymember) %>% 
  melt(id.vars = c("weekday"))

ggplot(bike_data_weekday, aes(x = weekday, y = value, color = variable, group = variable))+
  geom_line()+
  geom_point()+
  labs(x = "Weekday", y = "Average ride length", title = "Average ride length per day")+
  scale_color_manual(values = c("steelblue", "darkred"),
                     labels = c("casual riders", "member riders"))

Above line graph helps demonstrate Ride length change in riders throughout the week. With average ridelength increasing by the weekend.

Monthly analysis

bikedata_monthcasual <- bike_data %>% 
  group_by(month) %>% 
  filter(member_casual == "casual") %>% 
  summarise("casual riders" = mean(ride_length))

bikedata_monthmember <- bike_data %>% 
  group_by(month) %>% 
  filter(member_casual == "member") %>% 
  summarise("member riders" = mean(ride_length))

bike_data_monthly_avg_ridelength <- merge(bikedata_monthcasual, bikedata_monthmember, by = c("month"))
bike_data_monthly_avg_ridelength$month <-  factor(bike_data_monthly_avg_ridelength$month, levels = c("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"))
bike_data_monthly_avg_ridelength <- melt(bike_data_monthly_avg_ridelength, id.vars = c("month"))

  ggplot(bike_data_monthly_avg_ridelength, aes(x = month, y = value, fill = variable))+
  geom_bar(stat = 'identity', position = 'dodge')+
  theme(axis.text.x = element_text(angle = 90))+
  labs(x = "Month", y = "Average ride length", title = "Average ride length per month")

Above Bar graph demonstrates monthly average ride length of the members throughout the year. The visual shows gradual increase till month may and gradual decrease.

bike_data_monthly_casual_riders <- bike_data %>% 
  group_by(month) %>% 
  filter(member_casual == "casual") %>% 
  summarise(riders = n())

bike_data_monthly_casual_riders$month <-  factor(bike_data_monthly_casual_riders$month, levels = c("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"))
  
  ggplot(bike_data_monthly_casual_riders, aes(x = month, y = riders))+
  geom_bar(fill = "#3593C8", stat = 'identity')+ scale_y_continuous(labels = comma)+
  theme(axis.text.x = element_text(angle = 90))+
  labs(x = "Month", y = "Number of rides", title = "Number of rides by casual riders per month")

Above bar graph shows number of monthly casual riders throughout the year. The visualization demonstrates how casual riders increases from may with highest being in july and then decreases till december. Most of the casual riders use the company product from month May till October month.

Seasonal analysis

seasonal_bike_data <- bike_data %>% 
  group_by(season) %>% 
  filter(member_casual == "casual") %>% 
  summarise(casual_seasonal_riders = n())

  ggplot(seasonal_bike_data, aes(x = season, y = casual_seasonal_riders))+
  geom_bar(fill = "#3593C8", stat = 'identity')+
  labs(x = "Season", y = "Number of rides", title = "Number of rides by casual riders per season")

The bar graph above demonstrates number of casual riders throughout the four seasons of the year. As can be seen from the visual it is the summer season when most prefer to use the bikes followed by autumn and then spring season. To convert casual riders to members key categories that has been identified can be season and months.

To identify most stations where casual riders use bikes

bike_data_station <- bike_data %>% 
  group_by(start_station_id, start_station_name) %>%
  filter(member_casual == "casual") %>%
  summarise(total = n()) %>% 
  arrange(-total) %>% 
  filter(total >= 10000)

bike_data_station
## # A tibble: 22 × 3
## # Groups:   start_station_id [22]
##    start_station_id start_station_name                 total
##    <chr>            <chr>                              <int>
##  1 13022            Streeter Dr & Grand Ave            55060
##  2 13300            DuSable Lake Shore Dr & Monroe St  30262
##  3 13008            Millennium Park                    23950
##  4 13042            Michigan Ave & Oak St              23761
##  5 LF-005           DuSable Lake Shore Dr & North Blvd 22157
##  6 15544            Shedd Aquarium                     19421
##  7 TA1308000001     Theater on the Lake                17333
##  8 TA1308000050     Wells St & Concord Ln              14834
##  9 KA1503000064     Dusable Harbor                     13271
## 10 13146            Clark St & Armitage Ave            12779
## # … with 12 more rows

It is identified that station id 13022 (Streeter Dr & Grand Ave) has had highest number of casual riders throughout 2022

ggplot(bike_data_station,aes(x = start_station_id, y = total))+
  geom_point(fill = "#3593C8", shape = 21, size = 3)+
  theme(axis.text.x = element_text(angle = 90))+
  labs(x = "Station Id", y = "Number of rides", title = "Number of rides by casual riders per station")

From the visualization it can be understood that 22 stations (out of 166) had more than 10000 casual riders over the year 2022. This visualization helps identify stations that can be more focused for marketing.

Phase 5 : Action:

Key Recommendations

  1. Offer can be introduced for summer season to attract casual riders to take up memberships
  2. Advertising and marketing can be intensively focused on the users from month may till october.
  3. Stations with large number of customers as compared to other stations could be more targeted which would lead to focus on larger population of casual users.
  4. Marketing efforts can be decreased during winter season which could also help reduce operational costs.