Foundation

This is an analysis for Cyclistic bike-share company in Chicago with a fleet of 5,824 bicycles that are geotracked and locked across 692 stations. With the ability of the bikes being able to be unlocked from one station and returned to any other of the stations in the system at anytime.

Cyclistic’s pricing plans are as follow; single-ride passes, full-day passes and annual memberships.Casual riders are those who purchase single-ride or full-day passes and Members are those who purchase annual memberships. The company’s finance analysts have concluded that annual members are more profitable than casual riders.

Target Goal

Analyze how annual members and casual riders use bikes differently.And to understand possible conversion for casual riders to become members. While also identifying how digital media could affect Cyclistic’s marketing tactics.

Data Information

Location

All data is obtained directly from here:

divvy data

this is data for the complete year of 2023

Organization

(CSV) format is used to organize and compile all stored data.The data is compromised using the following column variables:

  • ride_id
  • rideable_type
  • started_at
  • ended_at
  • start_station_name
  • end_station_id
  • member_casual
  • day_of_week
  • date
  • year
  • month
  • week
  • day
  • start_time
  • start_time_hour
  • end_time
  • length

Process

Import Data into R

library(tidyverse)
library(janitor)
library(lubridate)
library(dplyr)
library(readxl)
Jan_2023 <- readxl::read_excel("/Volumes/ALMA/Data Analytics/coursera_files/12_month_case_study_1/update_csv_2023/xls_2023/202301-divvy-tripdata.xls")
Feb_2023 <- readxl::read_excel("/Volumes/ALMA/Data Analytics/coursera_files/12_month_case_study_1/update_csv_2023/xls_2023/202302-divvy-tripdata.xls")
Mar_2023 <- readxl::read_excel("/Volumes/ALMA/Data Analytics/coursera_files/12_month_case_study_1/update_csv_2023/xls_2023/202303-divvy-tripdata.xls")
Apr_2023 <- readxl::read_excel("/Volumes/ALMA/Data Analytics/coursera_files/12_month_case_study_1/update_csv_2023/xls_2023/202304-divvy-tripdata.xls")
May_2023 <- readxl::read_excel("/Volumes/ALMA/Data Analytics/coursera_files/12_month_case_study_1/update_csv_2023/xls_2023/202305-divvy-tripdata.xls")
Jun_2023 <- readxl::read_excel("/Volumes/ALMA/Data Analytics/coursera_files/12_month_case_study_1/update_csv_2023/xls_2023/202306-divvy-tripdata.xls")
Jul_2023 <- readxl::read_excel("/Volumes/ALMA/Data Analytics/coursera_files/12_month_case_study_1/update_csv_2023/xls_2023/202307-divvy-tripdata.xls")
Aug_2023 <- readxl::read_excel("/Volumes/ALMA/Data Analytics/coursera_files/12_month_case_study_1/update_csv_2023/xls_2023/202308-divvy-tripdata.xls")
Sep_2023 <- readxl::read_excel("/Volumes/ALMA/Data Analytics/coursera_files/12_month_case_study_1/update_csv_2023/xls_2023/202309-divvy-tripdata.xls")
Oct_2023 <- readxl::read_excel("/Volumes/ALMA/Data Analytics/coursera_files/12_month_case_study_1/update_csv_2023/xls_2023/202310-divvy-tripdata.xls")
Nov_2023 <- readxl::read_excel("/Volumes/ALMA/Data Analytics/coursera_files/12_month_case_study_1/update_csv_2023/xls_2023/202311-divvy-tripdata.xls")
Dec_2023 <- readxl::read_excel("/Volumes/ALMA/Data Analytics/coursera_files/12_month_case_study_1/update_csv_2023/xls_2023/202312-divvy-tripdata.xls")

Combine Datasets

#Check that the datasets are mergeable
compare_df_cols_same(Jan_2023, Feb_2023, Mar_2023, Apr_2023, May_2023, Jun_2023, Jul_2023, Aug_2023, Sep_2023, Oct_2023, Nov_2023, Dec_2023)
#Merge the datasets
trips_2023 <- bind_rows(Jan_2023, Feb_2023, Mar_2023, Apr_2023, May_2023, Jun_2023, Jul_2023, Aug_2023, Sep_2023, Oct_2023, Nov_2023, Dec_2023)
#View the new dataframe
str(trips_2023)
## tibble [786,420 × 12] (S3: tbl_df/tbl/data.frame)
##  $ ride_id           : chr [1:786420] "F96D5A74A3E41399" "13CB7EB698CEDB88" "BD88A2E670661CE5" "C90792D034FED968" ...
##  $ rideable_type     : chr [1:786420] "electric_bike" "classic_bike" "electric_bike" "classic_bike" ...
##  $ started_at        : POSIXct[1:786420], format: "2023-01-21 20:05:00" "2023-01-10 15:37:00" ...
##  $ ended_at          : POSIXct[1:786420], format: "2023-01-21 20:16:00" "2023-01-10 15:46:00" ...
##  $ start_station_name: chr [1:786420] "Lincoln Ave & Fullerton Ave" "Kimbark Ave & 53rd St" "Western Ave & Lunt Ave" "Kimbark Ave & 53rd St" ...
##  $ start_station_id  : chr [1:786420] "TA1309000058" "TA1309000037" "RP-005" "TA1309000037" ...
##  $ end_station_name  : chr [1:786420] "Hampden Ct & Diversey Ave" "Greenwood Ave & 47th St" "Valli Produce - Evanston Plaza" "Greenwood Ave & 47th St" ...
##  $ end_station_id    : chr [1:786420] "202480" "TA1308000002" "599" "TA1308000002" ...
##  $ member_casual     : chr [1:786420] "member" "member" "casual" "member" ...
##  $ ride_length       : POSIXct[1:786420], format: "1899-12-31 00:10:51" "1899-12-31 00:08:29" ...
##  $ day_of_week       : num [1:786420] 7 3 2 1 5 3 1 4 4 6 ...
##  $ 6788              : chr [1:786420] NA NA NA NA ...

Clean and Manipulate Data

checking valid string values

# Check that only valid string values are in 'rideable_type' & 'member_casual'
valid_types <- c('electric_bike', 'classic_bike', 'docked_bike')
valid_members <- c('member', 'casual')
all(trips_2023$rideable_type %in% valid_types)
all(trips_2023$member_casual %in% valid_members)

Convert date and time format

#Convert date and time format
trips_2023$started_at = ymd_hms(trips_2023$started_at)
trips_2023$ended_at = ymd_hms(trips_2023$ended_at)

Convert new columns

#Convert new columns
trips_2023$date <- as.Date(trips_2023$started_at)
trips_2023$year <- format(as.Date(trips_2023$date), format = "%Y") # Create column for year
trips_2023$month <- format(as.Date(trips_2023$date), format = "%m") # Create column for month
trips_2023$week <- format(as.Date(trips_2023$date), format = '%W') # Create column for week
trips_2023$day <- format(as.Date(trips_2023$date), format = '%A') # Create column for day
trips_2023$start_time <- format(as.POSIXct(trips_2023$started_at), format = '%H:%M') # Create column for start time
trips_2023$start_time_hour <- format(as.POSIXct(trips_2023$started_at), format = '%H') # Create   column for start time hour 
trips_2023$end_time <- format(as.POSIXct(trips_2023$ended_at), format = '%H:%M') # Create column for end time 
trips_2023$length <- difftime(trips_2023$ended_at, trips_2023$started_at, units='mins') # Create for ride length in minutes
trips_2023$length <- as.numeric(as.character(trips_2023$length))
trips_2023 <- trips_2023 %>% 
  filter(!(length <= 0))

#Order the data by the day of the week 
trips_2023$day  <- as.factor(trips_2023$day)
trips_2023$day <- ordered(trips_2023$day, levels = c("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"))

Analyze

Summary

#View Summary
summary(trips_2023$length)
##     Min.  1st Qu.   Median     Mean  3rd Qu.     Max. 
##     1.00     5.00     9.00    16.73    16.00 92570.00
# Compare members and casual users
aggregate(trips_2023$length ~ trips_2023$member_casual, FUN = mean)
##   trips_2023$member_casual trips_2023$length
## 1                   casual          27.19704
## 2                   member          12.18129
aggregate(trips_2023$length ~ trips_2023$member_casual, FUN = median)
##   trips_2023$member_casual trips_2023$length
## 1                   casual                11
## 2                   member                 8
aggregate(trips_2023$length ~ trips_2023$member_casual, FUN = max)
##   trips_2023$member_casual trips_2023$length
## 1                   casual             92570
## 2                   member              1560
aggregate(trips_2023$length ~ trips_2023$member_casual, FUN = min)
##   trips_2023$member_casual trips_2023$length
## 1                   casual                 1
## 2                   member                 1
# See the average ride time by each day for members vs casual users
aggregate(trips_2023$length ~ trips_2023$member_casual + trips_2023$day,
FUN = mean)
##    trips_2023$member_casual trips_2023$day trips_2023$length
## 1                    casual         Sunday          31.15674
## 2                    member         Sunday          13.95954
## 3                    casual         Monday          26.78992
## 4                    member         Monday          11.56918
## 5                    casual        Tuesday          24.50192
## 6                    member        Tuesday          11.71441
## 7                    casual      Wednesday          20.78694
## 8                    member      Wednesday          11.68443
## 9                    casual       Thursday          25.19867
## 10                   member       Thursday          11.71719
## 11                   casual         Friday          27.14479
## 12                   member         Friday          11.96372
## 13                   casual       Saturday          31.52079
## 14                   member       Saturday          13.41781

New Dataframe summaries

#another dataframe for trips_2023

trips_2023 %>%
  group_by(member_casual, day) %>% 
  summarise(number_of_rides = n(), 
  average_duration = mean(length)) %>% 
  arrange(member_casual, day)
## # A tibble: 14 × 4
## # Groups:   member_casual [2]
##    member_casual day       number_of_rides average_duration
##    <chr>         <ord>               <int>            <dbl>
##  1 casual        Sunday              38184             31.2
##  2 casual        Monday              26856             26.8
##  3 casual        Tuesday             28975             24.5
##  4 casual        Wednesday           29484             20.8
##  5 casual        Thursday            31091             25.2
##  6 casual        Friday              35217             27.1
##  7 casual        Saturday            44127             31.5
##  8 member        Sunday              59590             14.0
##  9 member        Monday              74196             11.6
## 10 member        Tuesday             87344             11.7
## 11 member        Wednesday           88289             11.7
## 12 member        Thursday            86424             11.7
## 13 member        Friday              77396             12.0
## 14 member        Saturday            65542             13.4

CSVs for Tableau

# Average Length
average_length <-  aggregate(trips_2023$length ~ trips_2023$member_casual + trips_2023$day,
FUN = mean)
write.csv(average_length, "/Volumes/ALMA/Data Analytics/coursera_files/average_length.csv")
#Count/Number of Rides
count <- trips_2023 %>%
  group_by(member_casual) %>%
  summarise(number_of_rides = n())

write.csv(count, "/Volumes/ALMA/Data Analytics/coursera_files/count.csv")

#Count per Day
count_per_day <- trips_2023 %>%
  group_by(member_casual, day) %>%
  summarise(number_of_rides = n())

write.csv(count_per_day,"/Volumes/ALMA/Data Analytics/coursera_files/count_per_day.csv" )

#Ride_per_month
rides_per_month <- trips_2023 %>%
  group_by(member_casual, month) %>%
  summarise(number_of_rides = n()) %>%
  arrange(member_casual, month)

write.csv(rides_per_month, "/Volumes/ALMA/Data Analytics/coursera_files/rides_per_month.csv")

#Rides_per_Time
rides_per_time <- trips_2023 %>%
  group_by(member_casual, start_time_hour) %>%
  summarise(number_of_rides = n())  

write.csv(rides_per_time, "/Volumes/ALMA/Data Analytics/coursera_files/rides_per_time.csv")

#Rides_per_biketype
rides_per_type <- trips_2023 %>%
  group_by(rideable_type, member_casual) %>%
  filter(rideable_type != 'docked_bike') %>%
  summarise(number_of_rides =n())

write.csv(rides_per_type, "/Volumes/ALMA/Data Analytics/coursera_files/rides_per_type.csv")

#Most_Used_Station_Map
most_used_station <- trips_2023 %>%
  group_by(start_station_name, member_casual) %>%
  summarise(total_count = length(na.omit(start_station_name))) %>%
  arrange(desc(total_count))

write.csv(most_used_station, "/Volumes/ALMA/Data Analytics/coursera_files/most_used_station.csv")

#Most Used Stations by Member Riders
most_used_stations_member <- trips_2023 %>%
  filter(member_casual == 'member')

most_used_stations_member <- most_used_stations_member %>%
  group_by(start_station_name) %>% 
  filter(start_station_name != 'NA') %>%
  summarise(total_count = length(start_station_name)) %>%
  arrange(desc(total_count)) %>%
  top_n(10)

write.csv(most_used_stations_member, "/Volumes/ALMA/Data Analytics/coursera_files/most_used_stations_member.csv")

#Most Used Stations by Casual Riders
most_used_stations_casual <- trips_2023 %>%
  filter(member_casual == 'casual')

most_used_stations_casual <- most_used_stations_casual %>%
  group_by(start_station_name) %>% 
  filter(start_station_name != 'NA') %>%
  summarise(total_count = length(start_station_name)) %>%
  arrange(desc(total_count)) %>%
  top_n(10)

write.csv(most_used_stations_casual, "/Volumes/ALMA/Data Analytics/coursera_files/most_used_stations_casual.csv")

Visualizations

#Visualization of the number of rides by rider type per day

trips_2023 %>%
  group_by(member_casual, day) %>% 
  summarise(number_of_rides = n()) %>%
  ggplot(aes(x = day, y = number_of_rides, fill = member_casual)) +
  geom_col(position = "dodge") + 
  labs(title ="Number of Rides per Day ")
## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.

#Visualization for average duration per day
trips_2023 %>%
  group_by(member_casual, day) %>%
  summarise(average_duration_minutes = mean(length)) %>%
  arrange(member_casual, day) %>%
  ggplot(aes(x = day, y = average_duration_minutes, fill = member_casual)) +
  geom_col(position = "dodge") +
  labs(title ="Average Duration of Ride Length")
## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.

#Visualization for Number of Rides by Month
trips_2023 %>%
  group_by(member_casual, month) %>%
  summarise(number_of_rides = n()) %>%
  arrange(member_casual, month) %>%
  ggplot(aes(x = month, y = number_of_rides, fill = member_casual)) +
  geom_col(position = "dodge") + 
  labs(title ="Number of Rides per Month")
## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.

#Number of Rides per Time of Day

trips_2023 %>%
  group_by(member_casual, start_time_hour) %>%
  summarise(number_of_rides = n()) %>%
  ggplot(aes(x = start_time_hour, y = number_of_rides, fill = member_casual)) +
  geom_col(position = "dodge") + 
  labs(title ="Number of Rides per Time of Day")
## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.

#Number of Rides per Bike Type

trips_2023 %>% 
  group_by(rideable_type, member_casual) %>%
  filter(rideable_type != 'docked_bike') %>%
  summarise(number_of_rides =n()) %>%
  ggplot(aes(x = rideable_type, y = number_of_rides, fill = member_casual)) +
  geom_col(position = "dodge") + 
  labs(title ="Number of Rides per Bike Type")
## `summarise()` has grouped output by 'rideable_type'. You can override using the
## `.groups` argument.

#Most Used Stations by Member Riders
most_used_stations_member %>%
  ggplot(aes(x = reorder(start_station_name, -total_count), y = total_count, fill = 
  start_station_name)) +
  geom_col(position = "dodge") + 
  theme(axis.text.x = element_text(angle=90, vjust=.5, hjust=1)) +
  theme(legend.position = "none") +
  labs(title ="Most Used Stations by Member Riders", x = 'start_station_name')

#Most Used Stations by Casual Members
most_used_stations_casual %>%
  ggplot(aes(x = reorder(start_station_name, -total_count), y = total_count, fill = 
  start_station_name)) +
  geom_col(position = "dodge") + 
  theme(axis.text.x = element_text(angle=90, vjust=.5, hjust=1)) +
  theme(legend.position = "none") +
  labs(title ="Most Used Stations by Casual Riders", x = 'start_station_name')

Share

Visualizations via Tableau

-Total Number of Rides

Total number of rides by both casual and member riders is 772,715. In which Member rides were far more with 538,781 almost 40% more than casual with 233,934 rides.

-Number of Rides per Day

Based on this chart, Member rides have a greater number of rides per day as expected by total number of rides shown. Member rides highest number of rides is seen to be on Wednesday with 88,289 rides while their lowest day of the week is is Sunday with 59,590 rides. And Casual highest day of the week shows to be on Saturday with 44,217 rides while their lowest day is Monday with 26,856 rides.

-Average Length of Rides per Day

Compared to the last visualizations, Casual riders are ranking highest in ride length daily. For instance, Saturday being their highest (31.52 minutes) and Sunday following closely behind with 31.16 minutes as well. And Member riders highest duration is on Sunday as well but with 13.96 minutes with Saturday following closely behind with 13.42 minutes as well. Both categories averaging higher on the weekend days.

-Number of Rides by Bike Type

Classic bikes rank righer with a number of 302,600 taken by member riders, unlike electric bikes rank higher for casual riders with 112,460.

-Number of Rides per Time of Day

Starting time hour of 3am ranks the lowest with a number of 1,047 rides for both member and casual riders. The starting time hour with the highest number of rides for member riders is at 17:00 with 57,394 rides, while for casual’s is at 17:00 as well but with a number of 22,588 rides.

-Number of Rides per Month

Member riders results shown their top 3 months with highest number of rides to be January (50,517), December (50,147), and February (49,730). Casual riders top 3 months were April (24,754), July (24,428), and June (22,267).

-Top Stations

Overall top stations used between both groups:

  1. Streeter Dr & Grand Ave
  2. Wells St & Concord Ln
  3. Kingsbury St & Kinzie St
  4. Desplaines St & Kinzie St
  5. Wells St & Hubbard St
  6. Millenium Park
  7. Clinton St & Madison St
  8. Kingsbury St & Erie St
  9. Wilton Ave & Belmont Ave
  10. LaSalle St & Illinois St

Act

Final Conclusion

After an extensive analysis made with the data on Cyclistic bike share company, we can better understand how annual and casual rider differ.

Starting with time frames, day of the week and month segments, that these groups ride in. Members show to ride more during the weekday (Monday-Friday) and less on the weekends. While Casual riders ride more during the weekend (Saturday-Sunday) than the weekdays. Month-wise, Members rides decrease during the months of April-August, whereas Casual rides increase at those certain months.

Another area these two differ is the stations they share in common. Casual’s top 10 stations are not similar as to member’s top 10 stations, but have a few in common.

Applying Insights

Annual memberships can definitely benefit casual riders, for these main reasons:

Marketing Strategies aimed to target Casual Riders into Annual Members