Cyclistic Exercise Full Year Analysis

This analysis is for case study 1 from the Google Data Analytics Certificate (Cyclistic)

Ask

I will be using the Divvy dataset which can be found here from January 2022 to December 2022 to conduct analyses to help answer the key question: “In what ways do members and casual riders use Divvy bikes differently”?

Prepare

All the necessary packages will be loaded

library(tidyverse)
## Warning: package 'tidyverse' was built under R version 4.3.2
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.3     ✔ readr     2.1.4
## ✔ forcats   1.0.0     ✔ stringr   1.5.0
## ✔ ggplot2   3.4.3     ✔ tibble    3.2.1
## ✔ lubridate 1.9.2     ✔ tidyr     1.3.0
## ✔ purrr     1.0.2     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(ggplot2)
library(lubridate)
library(readr)
library(dplyr)

Step 1

Collecting Data

## Rows: 80350 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (9): ride_id, rideable_type, started_at, ended_at, start_station_name, s...
## dbl (4): start_lat, start_lng, end_lat, end_lng
## 
## ℹ 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.
## Rows: 89178 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (9): ride_id, rideable_type, started_at, ended_at, start_station_name, s...
## dbl (4): start_lat, start_lng, end_lat, end_lng
## 
## ℹ 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.
## Rows: 219406 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (9): ride_id, rideable_type, started_at, ended_at, start_station_name, s...
## dbl (4): start_lat, start_lng, end_lat, end_lng
## 
## ℹ 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.
## Rows: 272560 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (9): ride_id, rideable_type, started_at, ended_at, start_station_name, s...
## dbl (4): start_lat, start_lng, end_lat, end_lng
## 
## ℹ 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.
## Rows: 510409 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (9): ride_id, rideable_type, started_at, ended_at, start_station_name, s...
## dbl (4): start_lat, start_lng, end_lat, end_lng
## 
## ℹ 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.
## Rows: 630862 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (9): ride_id, rideable_type, started_at, ended_at, start_station_name, s...
## dbl (4): start_lat, start_lng, end_lat, end_lng
## 
## ℹ 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.
## Rows: 642680 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (9): ride_id, rideable_type, started_at, ended_at, start_station_name, s...
## dbl (4): start_lat, start_lng, end_lat, end_lng
## 
## ℹ 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.
## Rows: 605325 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (9): ride_id, rideable_type, started_at, ended_at, start_station_name, s...
## dbl (4): start_lat, start_lng, end_lat, end_lng
## 
## ℹ 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.
## Rows: 535145 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (9): ride_id, rideable_type, started_at, ended_at, start_station_name, s...
## dbl (4): start_lat, start_lng, end_lat, end_lng
## 
## ℹ 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.
## Rows: 414269 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (9): ride_id, rideable_type, started_at, ended_at, start_station_name, s...
## dbl (4): start_lat, start_lng, end_lat, end_lng
## 
## ℹ 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.
## Rows: 259125 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (9): ride_id, rideable_type, started_at, ended_at, start_station_name, s...
## dbl (4): start_lat, start_lng, end_lat, end_lng
## 
## ℹ 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.
## Rows: 137595 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (9): ride_id, rideable_type, started_at, ended_at, start_station_name, s...
## dbl (4): start_lat, start_lng, end_lat, end_lng
## 
## ℹ 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.

Step 2

Data Wrangling, by consolidating monthly data frames into one big data frame

data <-bind_rows(jan_2022, feb_2022 ,march_2022 ,april_2022, may_2022, june_2022, july_2022, aug_2022, sep_2022, oct_2022, nov_2022, dec_2022)

Step 3

Clean up and add data to prepare for analysis by removing missing values from the dataset using the drop_na funciton.

cleaned_data <-data %>%
  drop_na()
  nrow(cleaned_data)
## [1] 4369360

After removing the missing values, the data now contains 4369360 rows.

Inspecting cleaned data to make sure everything is in order

colnames(cleaned_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"
nrow(cleaned_data)
## [1] 4369360
dim(cleaned_data)
## [1] 4369360      13
head(cleaned_data)
## # A tibble: 6 × 13
##   ride_id  rideable_type started_at ended_at start_station_name start_station_id
##   <chr>    <chr>         <chr>      <chr>    <chr>              <chr>           
## 1 C2F7DD7… electric_bike 13/01/202… 13/01/2… Glenwood Ave & To… 525             
## 2 A6CF898… electric_bike 10/01/202… 10/01/2… Glenwood Ave & To… 525             
## 3 BD0F91D… classic_bike  25/01/202… 25/01/2… Sheffield Ave & F… TA1306000016    
## 4 CBB80ED… classic_bike  04/01/202… 04/01/2… Clark St & Bryn M… KA1504000151    
## 5 DDC963B… classic_bike  20/01/202… 20/01/2… Michigan Ave & Ja… TA1309000002    
## 6 A39C6F6… classic_bike  11/01/202… 11/01/2… Wood St & Chicago… 637             
## # ℹ 7 more variables: end_station_name <chr>, end_station_id <chr>,
## #   start_lat <dbl>, start_lng <dbl>, end_lat <dbl>, end_lng <dbl>,
## #   member_casual <chr>
str(cleaned_data)
## tibble [4,369,360 × 13] (S3: tbl_df/tbl/data.frame)
##  $ ride_id           : chr [1:4369360] "C2F7DD78E82EC875" "A6CF8980A652D272" "BD0F91DFF741C66D" "CBB80ED419105406" ...
##  $ rideable_type     : chr [1:4369360] "electric_bike" "electric_bike" "classic_bike" "classic_bike" ...
##  $ started_at        : chr [1:4369360] "13/01/2022 11:59" "10/01/2022 08:41" "25/01/2022 04:53" "04/01/2022 00:18" ...
##  $ ended_at          : chr [1:4369360] "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:4369360] "Glenwood Ave & Touhy Ave" "Glenwood Ave & Touhy Ave" "Sheffield Ave & Fullerton Ave" "Clark St & Bryn Mawr Ave" ...
##  $ start_station_id  : chr [1:4369360] "525" "525" "TA1306000016" "KA1504000151" ...
##  $ end_station_name  : chr [1:4369360] "Clark St & Touhy Ave" "Clark St & Touhy Ave" "Greenview Ave & Fullerton Ave" "Paulina St & Montrose Ave" ...
##  $ end_station_id    : chr [1:4369360] "RP-007" "RP-007" "TA1307000001" "TA1309000021" ...
##  $ start_lat         : num [1:4369360] 42 42 41.9 42 41.9 ...
##  $ start_lng         : num [1:4369360] -87.7 -87.7 -87.7 -87.7 -87.6 ...
##  $ end_lat           : num [1:4369360] 42 42 41.9 42 41.9 ...
##  $ end_lng           : num [1:4369360] -87.7 -87.7 -87.7 -87.7 -87.6 ...
##  $ member_casual     : chr [1:4369360] "casual" "casual" "member" "casual" ...
summary(cleaned_data)
##    ride_id          rideable_type       started_at          ended_at        
##  Length:4369360     Length:4369360     Length:4369360     Length:4369360    
##  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:4369360     Length:4369360     Length:4369360     Length:4369360    
##  Class :character   Class :character   Class :character   Class :character  
##  Mode  :character   Mode  :character   Mode  :character   Mode  :character  
##                                                                             
##                                                                             
##                                                                             
##    start_lat       start_lng         end_lat         end_lng      
##  Min.   :41.65   Min.   :-87.83   Min.   : 0.00   Min.   :-87.83  
##  1st Qu.:41.88   1st Qu.:-87.66   1st Qu.:41.88   1st Qu.:-87.66  
##  Median :41.90   Median :-87.64   Median :41.90   Median :-87.64  
##  Mean   :41.90   Mean   :-87.64   Mean   :41.90   Mean   :-87.64  
##  3rd Qu.:41.93   3rd Qu.:-87.63   3rd Qu.:41.93   3rd Qu.:-87.63  
##  Max.   :45.64   Max.   :-73.80   Max.   :42.06   Max.   :  0.00  
##  member_casual     
##  Length:4369360    
##  Class :character  
##  Mode  :character  
##                    
##                    
## 

Check if member_casual says member and casual and not “Subscriber and Customer”

table(cleaned_data$member_casual)
## 
##  casual  member 
## 1758189 2611171

Data Transformation

Creating columns for date, day, month, year, and day of week

cleaned_data$date <- as.Date(cleaned_data$started_at)
cleaned_data$month <- format(as.Date(cleaned_data$date),"%m")
cleaned_data$day <- format(as.Date(cleaned_data$date),"%d")
cleaned_data$year <- format(as.Date(cleaned_data$date),"%y")
cleaned_data$day_of_week <- format(as.Date(cleaned_data$date),"%A")

Add ride_length to calculate the trips in seconds

cleaned_data$ride_length <- difftime(cleaned_data$ended_at,cleaned_data$started_at)

Checking if the ride_length is numeric or factor. If the ride_length is factor, then i use the as.numeric() function to change it to numeric

is.factor(cleaned_data$ride_length)
## [1] FALSE
is.numeric(cleaned_data$ride_length)
## [1] FALSE
cleaned_data$ride_length <- as.numeric(cleaned_data$ride_length)
is.numeric(cleaned_data$ride_length)
## [1] TRUE

Removing bad data

Here, we have to remove the bad data. This dataframe includes a few hundred entries when bikes were taken out of docks and checked for quality by Divvy or when ride_length was negative. To do this, we check how many rows have start_station_name == HQ QR and ride_length<0

nrow(cleaned_data[cleaned_data$start_station_name == "HQ QR", ])
## [1] 0
nrow(cleaned_data[cleaned_data$ride_length<0, ])
## [1] 616

No rows where found where start_station_name == “HQ QR”, however 69 rows where found where ride_length<0 hence has to be removed.

A new dataframe will be created cleaned_data(v2) since data is being removed.

cleaned_data_v2 <- cleaned_data[!(cleaned_data$ride_length<0),]

Step 4

Conducting Descriptive Analysis. Here i find the mean, median, max and min

mean(cleaned_data_v2$ride_length)
## [1] 155897.2
median(cleaned_data_v2$ride_length)
## [1] 0
max(cleaned_data_v2$ride_length)
## [1] 757382400
min(cleaned_data_v2$ride_length)
## [1] 0

I will combine the four lines of code above to one line using summary() function

summary(cleaned_data_v2$ride_length)

I will compare the mean, median, max, and min of casuals and members

aggregate(cleaned_data_v2$ride_length~cleaned_data_v2$member_casual, FUN = mean)
##   cleaned_data_v2$member_casual cleaned_data_v2$ride_length
## 1                        casual                   261955.14
## 2                        member                    84494.66
aggregate(cleaned_data_v2$ride_length~cleaned_data_v2$member_casual, FUN = median)
##   cleaned_data_v2$member_casual cleaned_data_v2$ride_length
## 1                        casual                           0
## 2                        member                           0
aggregate(cleaned_data_v2$ride_length~cleaned_data_v2$member_casual, FUN = max)
##   cleaned_data_v2$member_casual cleaned_data_v2$ride_length
## 1                        casual                   757382400
## 2                        member                    31622400
aggregate(cleaned_data_v2$ride_length~cleaned_data_v2$member_casual, FUN = min)
##   cleaned_data_v2$member_casual cleaned_data_v2$ride_length
## 1                        casual                           0
## 2                        member                           0

See the average ride time by each day for members vs casual users

##    cleaned_data_v2$member_casual cleaned_data_v2$day_of_week
## 1                         casual                      Friday
## 2                         member                      Friday
## 3                         casual                      Monday
## 4                         member                      Monday
## 5                         casual                    Saturday
## 6                         member                    Saturday
## 7                         casual                      Sunday
## 8                         member                      Sunday
## 9                         casual                    Thursday
## 10                        member                    Thursday
## 11                        casual                     Tuesday
## 12                        member                     Tuesday
## 13                        casual                   Wednesday
## 14                        member                   Wednesday
##    cleaned_data_v2$ride_length
## 1                    266749.43
## 2                     84708.91
## 3                    271994.55
## 4                     84779.13
## 5                    265280.52
## 6                     87073.84
## 7                    272826.04
## 8                     89241.05
## 9                    276816.95
## 10                    83489.50
## 11                   252973.52
## 12                    85254.90
## 13                   223784.97
## 14                    76830.24

Order the days of the week

Now, let’s run the average ride time by each day for members vs casual users

##    cleaned_data_v2$member_casual cleaned_data_v2$day_of_week
## 1                         casual                      Sunday
## 2                         member                      Sunday
## 3                         casual                      Monday
## 4                         member                      Monday
## 5                         casual                     Tuesday
## 6                         member                     Tuesday
## 7                         casual                   Wednesday
## 8                         member                   Wednesday
## 9                         casual                    Thursday
## 10                        member                    Thursday
## 11                        casual                      Friday
## 12                        member                      Friday
## 13                        casual                    Saturday
## 14                        member                    Saturday
##    cleaned_data_v2$ride_length
## 1                    272826.04
## 2                     89241.05
## 3                    271994.55
## 4                     84779.13
## 5                    252973.52
## 6                     85254.90
## 7                    223784.97
## 8                     76830.24
## 9                    276816.95
## 10                    83489.50
## 11                   266749.43
## 12                    84708.91
## 13                   265280.52
## 14                    87073.84

Analyze ridership data by type and weekday

## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.
## # A tibble: 14 × 4
## # Groups:   member_casual [2]
##    member_casual weekday number_of_rides average_duration
##    <chr>         <ord>             <int>            <dbl>
##  1 casual        Sun              259865          272826.
##  2 casual        Mon              250822          271995.
##  3 casual        Tue              243472          252974.
##  4 casual        Wed              235728          223785.
##  5 casual        Thu              253953          276817.
##  6 casual        Fri              243838          266749.
##  7 casual        Sat              270119          265281.
##  8 member        Sun              371961           89241.
##  9 member        Mon              369615           84779.
## 10 member        Tue              366772           85255.
## 11 member        Wed              366329           76830.
## 12 member        Thu              384459           83490.
## 13 member        Fri              378178           84709.
## 14 member        Sat              373633           87074.

#SUMMARY:

*Casuals have a higher yearly average ride length whereas members have a higher median ride length.

Casuals have a higher maximum ride length whereas both members and casuals share a 0 minimum ride length.

On the weekends**, there is slight increase in the number of rides for casuals than members.

There is a significantly *higher ride** for members than casuals during the weekends.

Step 5: Share

Share. Let’s visualize the number of rides by rider type

cleaned_data_v2 %>% 
  mutate(weekday = wday(started_at, label = TRUE)) %>% 
  group_by(member_casual, weekday) %>% 
  summarise(number_of_rides = n()
            ,average_duration = mean(ride_length)) %>% 
  arrange(member_casual, weekday)  %>% 
  ggplot(aes(x = weekday, y = number_of_rides, fill = member_casual)) +
  geom_col(position = "dodge")+
  labs(title = "number of rides casual vs members")
## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.

Creating a visualization for average ride duration.

cleaned_data_v2 %>%
  mutate(weekday = wday(started_at, label=TRUE)) %>%
  group_by(member_casual, weekday) %>%
  summarise(number_of_rides = n(), average_duration = mean(ride_length)) %>%
  arrange(member_casual, weekday) %>%
  ggplot(aes(x=weekday,y=average_duration, fill = member_casual)) +
  geom_col(position = "dodge") +
  labs(title="Average duration Casuals vs. Members")
## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.

Number of rides for members and casuals in 2022.

cleaned_data_v2 %>%
  group_by(member_casual,rideable_type) %>%
  summarise(number_of_bikes=n()) %>%
  ggplot(aes(x=member_casual,y=number_of_bikes,fill=rideable_type))+geom_col(position="dodge")+
  labs(title="Rideable Types (Members vs. Casuals)")
## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.

—END—