SETUP

Install and load packages

library(tidyverse)
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.4     ✔ readr     2.1.5
## ✔ forcats   1.0.0     ✔ stringr   1.5.1
## ✔ ggplot2   3.5.0     ✔ tibble    3.2.1
## ✔ lubridate 1.9.3     ✔ tidyr     1.3.1
## ✔ 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(scales)
## 
## Attaching package: 'scales'
## 
## The following object is masked from 'package:purrr':
## 
##     discard
## 
## The following object is masked from 'package:readr':
## 
##     col_factor

PROCESS

I. Fetch Data

Load Cyclicity data tables

Each month of 2022 has its own table

Load table 1

bikes <-  read_csv('C:/Users/lucyj/OneDrive/Desktop/Programming/Case study bikes/R_Cyclicity/Cyclicity data 2022/csv archive all months/202201-divvy-tripdata.csv', show_col_types = FALSE)

Examine dataset

glimpse(bikes)
## Rows: 103,770
## Columns: 13
## $ ride_id            <chr> "C2F7DD78E82EC875", "A6CF8980A652D272", "BD0F91DFF7…
## $ rideable_type      <chr> "electric_bike", "electric_bike", "classic_bike", "…
## $ started_at         <dttm> 2022-01-13 11:59:47, 2022-01-10 08:41:56, 2022-01-…
## $ ended_at           <dttm> 2022-01-13 12:02:44, 2022-01-10 08:46:17, 2022-01-…
## $ start_station_name <chr> "Glenwood Ave & Touhy Ave", "Glenwood Ave & Touhy A…
## $ start_station_id   <chr> "525", "525", "TA1306000016", "KA1504000151", "TA13…
## $ end_station_name   <chr> "Clark St & Touhy Ave", "Clark St & Touhy Ave", "Gr…
## $ end_station_id     <chr> "RP-007", "RP-007", "TA1307000001", "TA1309000021",…
## $ start_lat          <dbl> 42.01280, 42.01276, 41.92560, 41.98359, 41.87785, 4…
## $ start_lng          <dbl> -87.66591, -87.66597, -87.65371, -87.66915, -87.624…
## $ end_lat            <dbl> 42.01256, 42.01256, 41.92533, 41.96151, 41.88462, 4…
## $ end_lng            <dbl> -87.67437, -87.67437, -87.66580, -87.67139, -87.627…
## $ member_casual      <chr> "casual", "casual", "member", "casual", "member", "…
summary(bikes)
##    ride_id          rideable_type        started_at                    
##  Length:103770      Length:103770      Min.   :2022-01-01 00:00:05.00  
##  Class :character   Class :character   1st Qu.:2022-01-10 12:55:46.00  
##  Mode  :character   Mode  :character   Median :2022-01-16 11:50:04.50  
##                                        Mean   :2022-01-16 12:29:50.32  
##                                        3rd Qu.:2022-01-22 11:11:42.00  
##                                        Max.   :2022-01-31 23:58:37.00  
##                                                                        
##     ended_at                     start_station_name start_station_id  
##  Min.   :2022-01-01 00:01:48.0   Length:103770      Length:103770     
##  1st Qu.:2022-01-10 13:11:37.0   Class :character   Class :character  
##  Median :2022-01-16 12:04:35.5   Mode  :character   Mode  :character  
##  Mean   :2022-01-16 12:45:06.2                                        
##  3rd Qu.:2022-01-22 11:26:24.5                                        
##  Max.   :2022-02-01 01:46:16.0                                        
##                                                                       
##  end_station_name   end_station_id       start_lat       start_lng     
##  Length:103770      Length:103770      Min.   :41.65   Min.   :-87.83  
##  Class :character   Class :character   1st Qu.:41.88   1st Qu.:-87.66  
##  Mode  :character   Mode  :character   Median :41.89   Median :-87.64  
##                                        Mean   :41.90   Mean   :-87.65  
##                                        3rd Qu.:41.93   3rd Qu.:-87.63  
##                                        Max.   :45.64   Max.   :-73.80  
##                                                                        
##     end_lat         end_lng       member_casual     
##  Min.   :41.65   Min.   :-87.83   Length:103770     
##  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.   :42.07   Max.   :-87.52                     
##  NA's   :86      NA's   :86

II. Clean Dataset

Check for missing values (nulls)

Search for missing start station ids

print("Count of total missing ssi values - ") 
## [1] "Count of total missing ssi values - "
sum(is.na(bikes$start_station_id ))
## [1] 16260

Search for missing end station ids

print("Count of total missing esi values - ") 
## [1] "Count of total missing esi values - "
sum(is.na(bikes$end_station_id ))
## [1] 17927

Search for missing start station names

print("Count of total missing ssn values - ") 
## [1] "Count of total missing ssn values - "
sum(is.na(bikes$start_station_name ))
## [1] 16260

Search for missing end station names

print("Count of total missing esn values - ") 
## [1] "Count of total missing esn values - "
sum(is.na(bikes$end_station_name ))
## [1] 17927

There are 833,064 (892742 - 833064) rides missing start and end station ids, which probably represent the same rides.There are an additional 59,678 rides missing end station ids only.

None of the other values contain nulls.

Search for nulls in all other columns

print("Count of total missing ride id values - ") 
## [1] "Count of total missing ride id values - "
sum(is.na(bikes$ride_id ))
## [1] 0

Search for rideable_type nulls

print("Count of total missing rideable_type values - ") 
## [1] "Count of total missing rideable_type values - "
sum(is.na(bikes$rideable_type ))
## [1] 0

Search for started_at nulls

print("Count of total missing started_at  values - ") 
## [1] "Count of total missing started_at  values - "
sum(is.na(bikes$started_at  ))
## [1] 0

Search for ended_at nulls

print("Count of total missing ended_at  values - ") 
## [1] "Count of total missing ended_at  values - "
sum(is.na(bikes$ended_at  ))
## [1] 0

Search for member_casual nulls

print("Count of total missing member_casual  values - ") 
## [1] "Count of total missing member_casual  values - "
sum(is.na(bikes$member_casual  ))
## [1] 0

Rows with nulls, i.e. missing start or end stations, make up 14.7% of the data set. This is a big proportion but I decided to delete them because:
- I can’t find/replace/recreate data with proxy data
- Deleting the nulls make the database more manageable
- This is just an exercise and the data is big enough without the nulls

Delete rows with nulls

Filter for null start and end station names

bikes |> 
filter(is.na(start_station_name), is.na(end_station_name))
## # A tibble: 10,545 × 13
##    ride_id          rideable_type started_at          ended_at           
##    <chr>            <chr>         <dttm>              <dttm>             
##  1 B2147BB28A87F330 electric_bike 2022-01-04 19:09:38 2022-01-04 19:11:14
##  2 4E2C789651AB5E19 electric_bike 2022-01-04 19:24:02 2022-01-04 19:36:45
##  3 FA6CA272C9D8C2C4 electric_bike 2022-01-28 15:01:52 2022-01-28 15:11:32
##  4 CDB97BDD94D0EE29 electric_bike 2022-01-28 14:51:26 2022-01-28 14:54:48
##  5 94E09835B146AB8D electric_bike 2022-01-05 11:22:44 2022-01-05 11:27:54
##  6 AC2CFE90C607B0ED electric_bike 2022-01-05 10:06:37 2022-01-05 10:10:25
##  7 4497AB110F3C3520 electric_bike 2022-01-05 15:08:49 2022-01-05 15:12:16
##  8 9AFAAB8AE4A75912 electric_bike 2022-01-09 11:48:22 2022-01-09 11:55:52
##  9 BE37BB6C18E84506 electric_bike 2022-01-24 18:41:24 2022-01-24 18:52:14
## 10 D64B5600ACBDA3D6 electric_bike 2022-01-24 19:06:22 2022-01-24 19:11:16
## # ℹ 10,535 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>

Create new data set without null rows

cleanbikes <- bikes %>% 
  na.omit(.na(start_station_name), is.na(end_station_name))

Check

nrow(cleanbikes)
## [1] 80128

Verify all rows with nulls are removed

sum(is.na(cleanbikes$start_station_name))
## [1] 0
sum(is.na(cleanbikes$end_station_name))
## [1] 0
sum(is.na(cleanbikes$start_station_id))
## [1] 0
sum(is.na(cleanbikes$end_station_id))
## [1] 0
sum(is.na(cleanbikes$ride_id))
## [1] 0
sum(is.na(cleanbikes$rideable_type))
## [1] 0
sum(is.na(cleanbikes$started_at))
## [1] 0
sum(is.na(cleanbikes$ended_at))
## [1] 0
sum(is.na(cleanbikes$member_casual))
## [1] 0

Delete unneeded columns

As per assignment

cleanbikes <- cleanbikes %>% 
  select(-contains('lat')) %>%
  select(-contains('lng'))

III. Transform data for analysis

Make new columns for month, day of week, start hour and end hour.

A “year” column is not needed because all rows are 2022. Create a new “date” column with date format. Old column “started_at” was dttm format.

cleanbikes$date <- as.Date(cleanbikes$started_at)
cleanbikes$time <- hour(cleanbikes$started_at)
cleanbikes$month <- month(cleanbikes$started_at, label = TRUE)
cleanbikes2 <- cleanbikes |>
  mutate(day_of_week = wday(cleanbikes$started_at, label = TRUE))

Change column names for clarity

cleanbikes <- cleanbikes2 %>%
  rename(bike_type = rideable_type,
         rider_type = member_casual)

Replace ‘docked’ bike type with ‘classic’

‘Docked’ is simply a previous term for ‘classic’.

Find unique values in ‘bike_type’

cleanbikes |> 
  distinct(bike_type)
## # A tibble: 3 × 1
##   bike_type    
##   <chr>        
## 1 electric_bike
## 2 classic_bike 
## 3 docked_bike

Check number of docked bikes

cleanbikes %>%
  filter(bike_type == "docked_bike")
## # A tibble: 943 × 13
##    ride_id  bike_type started_at          ended_at            start_station_name
##    <chr>    <chr>     <dttm>              <dttm>              <chr>             
##  1 510D064… docked_b… 2022-01-15 13:47:41 2022-01-15 13:50:50 Clinton St & Lake…
##  2 651A2A8… docked_b… 2022-01-24 17:08:15 2022-01-24 17:23:23 Indiana Ave & 31s…
##  3 92C53CC… docked_b… 2022-01-27 15:07:06 2022-01-27 15:13:06 Clark St & Randol…
##  4 A96C851… docked_b… 2022-01-01 11:03:47 2022-01-01 11:27:29 Shore Dr & 55th St
##  5 CB0EEC1… docked_b… 2022-01-15 09:02:23 2022-01-15 09:20:02 Larrabee St & Div…
##  6 666A8B7… docked_b… 2022-01-20 19:16:04 2022-01-20 19:59:22 Theater on the La…
##  7 A2736FE… docked_b… 2022-01-16 05:03:36 2022-01-16 05:15:01 Avers Ave & Belmo…
##  8 335AF8E… docked_b… 2022-01-19 02:58:15 2022-01-19 03:19:14 Clinton St & Roos…
##  9 66F83C1… docked_b… 2022-01-02 18:15:21 2022-01-02 18:24:04 Damen Ave & Charl…
## 10 25C474E… docked_b… 2022-01-04 13:28:48 2022-01-04 13:59:53 Millennium Park   
## # ℹ 933 more rows
## # ℹ 8 more variables: start_station_id <chr>, end_station_name <chr>,
## #   end_station_id <chr>, rider_type <chr>, date <date>, time <int>,
## #   month <ord>, day_of_week <ord>

Replace ‘docked’ with ‘classic’

cleanbikes <- cleanbikes %>% 
  mutate(bike_type = str_replace(bike_type, "docked_bike", "classic_bike" ))

Verify

cleanbikes |> 
  distinct(bike_type)
## # A tibble: 2 × 1
##   bike_type    
##   <chr>        
## 1 electric_bike
## 2 classic_bike

Create ‘trip duration’ column

cleanbikes$trip_duration <- difftime(cleanbikes$ended_at, cleanbikes$started_at)

Remove negative and tiny trip durations

Save small durations in new table “temp2”

temp2 <- cleanbikes %>%
  filter(trip_duration < 60)
glimpse(temp2)
## Rows: 1,072
## Columns: 14
## $ ride_id            <chr> "7DD70C4AA0F3EA7B", "64CA42FCA02E5148", "22096565E8…
## $ bike_type          <chr> "classic_bike", "classic_bike", "classic_bike", "cl…
## $ started_at         <dttm> 2022-01-14 05:47:07, 2022-01-12 14:37:54, 2022-01-…
## $ ended_at           <dttm> 2022-01-14 05:47:39, 2022-01-12 14:38:07, 2022-01-…
## $ start_station_name <chr> "Lincoln Park Conservatory", "State St & Pearson St…
## $ start_station_id   <chr> "LP-", "TA1307000061", "13021", "13253", "SL-010", …
## $ end_station_name   <chr> "Lincoln Park Conservatory", "State St & Pearson St…
## $ end_station_id     <chr> "LP-", "TA1307000061", "13021", "13253", "SL-010", …
## $ rider_type         <chr> "member", "member", "member", "member", "member", "…
## $ date               <date> 2022-01-14, 2022-01-12, 2022-01-14, 2022-01-02, 20…
## $ time               <int> 5, 14, 16, 7, 7, 19, 15, 13, 13, 15, 9, 11, 14, 11,…
## $ month              <ord> Jan, Jan, Jan, Jan, Jan, Jan, Jan, Jan, Jan, Jan, J…
## $ day_of_week        <ord> Fri, Wed, Fri, Sun, Mon, Fri, Mon, Sat, Fri, Tue, T…
## $ trip_duration      <drtn> 32 secs, 13 secs, 8 secs, 4 secs, 1 secs, 2 secs, …

Remove small durations from main data set and save

cleanbikes <- cleanbikes %>%
  filter(trip_duration > 60)

Reorder of columns

Move most used columns to the left

cleanbikes <- cleanbikes |>
  relocate(starts_with("end_station"), .after = time) |>
  relocate(starts_with("start_station"), .after = time) |>
  relocate("ride_id", .after = trip_duration) |>
  relocate("month", .after = date) |>
  relocate("day_of_week", .after = month) |>
  relocate("trip_duration", .after = time)

Review cleaned dataset and save as csv

View(cleanbikes) / # ANALYSIS

Calculate duration of rides in minutes

Longest ride

max(cleanbikes$trip_duration/60)
## Time difference of 29271.1 secs

Shortest ride

min(cleanbikes$trip_duration)
## Time difference of 61 secs

Average (mean) ride

mean(cleanbikes$trip_duration/60)
## Time difference of 13.12931 secs

Average (median) ride

median(cleanbikes$trip_duration)
## Time difference of 474 secs

Numbers of Rides

Total number of rides per rider type

ggplot(data = cleanbikes, aes(rider_type, fill = rider_type)) +
  geom_bar() +
  theme(legend.position = "none") +
  geom_text(
    aes(label = ..count..),
    stat = "count",
    vjust = 2,
    colour = "white"
  ) +
  labs(
    title = "Members take 32% more rides than casual riders",
    x = NULL,
    y = "Number of Rides",
    color = "bikecolor",
    fill = "rider type"
  ) +
  scale_fill_manual(values = c("#202124",
                               "#1a73e8")) +
  scale_y_continuous(labels = label_number(suffix = " K", scale = 1e-4)) + # thousands
  theme_bw()
## Warning: The dot-dot notation (`..count..`) was deprecated in ggplot2 3.4.0.
## ℹ Please use `after_stat(count)` instead.
## This warning is displayed once every 8 hours.
## Call `lifecycle::last_lifecycle_warnings()` to see where this warning was
## generated.

ggsave("total rides.jpg")
## Saving 7 x 5 in image

Total number of rides per month

ggplot(cleanbikes, aes(x = month , fill = rider_type)) +
  geom_bar() +
  labs(
    title = "Members are better at braving the cold",
    subtitle = "39% of member rides were between October and April vs. 25% of casual rides",
    x = "Month",
    y = "Number of Rides",
    fill = "rider type"
  ) +
  scale_fill_manual(values = c("#202124",
                               "#1a73e8")) +
  scale_y_continuous(labels = label_number(suffix = " K", scale = 1e-4)) + # thousands
  theme_bw()

ggsave("monthly_by_user_stacked_winterstats.jpg")
## Saving 7 x 5 in image

Total number of rides per day of week

ggplot(cleanbikes, aes(
  x = factor(
    cleanbikes$day_of_week,
    levels = c("Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri")
  ),
  fill = rider_type
)) + #puts days of week in sequence
  geom_bar(position = "dodge") +
  labs(title = "Members ride more during the week",
       x = "Weekday", y = "Number of Rides") +
  scale_fill_manual(values = c("#202124", "#1a73e8")) +
  scale_y_continuous(labels = label_number(suffix = " K", scale = 1e-4)) + # thousands
  theme_bw()
## Warning: Use of `cleanbikes$day_of_week` is discouraged.
## ℹ Use `day_of_week` instead.

ggsave("ridesPerWeekday.jpg")
## Saving 7 x 5 in image
## Warning: Use of `cleanbikes$day_of_week` is discouraged.
## ℹ Use `day_of_week` instead.

Total number of trips throughout the day

cleanbikes |>
  ggplot(aes(x = time, group = rider_type, color = rider_type)) +
  geom_line(stat = "count", size = 1) +
  scale_color_manual(values = c("#1a73e8", "#202124")) +
  scale_y_continuous(labels = label_number(suffix = " K", scale = 1e-4)) +
  labs(title = "Members ride more during rush hour, particularly in the morning",
       x = "Start Hour", y = "Number of Rides") +
  theme_bw()
## Warning: Using `size` aesthetic for lines was deprecated in ggplot2 3.4.0.
## ℹ Please use `linewidth` instead.
## This warning is displayed once every 8 hours.
## Call `lifecycle::last_lifecycle_warnings()` to see where this warning was
## generated.

Trip Durations

Average trip duration per rider type

cleanbikes %>%
  group_by(rider_type) %>%
  summarise(avg_duration = mean(trip_duration)) %>%
  arrange(rider_type) %>%
  ggplot(mapping = aes(
    x = rider_type,
    y = (avg_duration / 60),
    fill = rider_type
  )) +
  geom_col() +
  labs(
    title = "Casual rides are twice as long as member rides: 24.4 mins vs.12.7",
    subtitle = "Casual riders ride more slowly or cover longer distances or both",
    x = NULL,
    y = "Average ride duration in minutes"
  ) +
  scale_fill_manual(values = c("#202124", "#1a73e8")) +
  theme_bw() +
  theme(legend.position = "none")
## Don't know how to automatically pick scale for object of type <difftime>.
## Defaulting to continuous.

ggsave("avg_duration_rides.jpeg")
## Saving 7 x 5 in image
## Don't know how to automatically pick scale for object of type <difftime>.
## Defaulting to continuous.

Average trip duration per month

cleanbikes %>%
  group_by(rider_type, month) %>%
  summarise(avg_duration = mean(trip_duration)) %>%
  arrange(rider_type, month) %>%
  ggplot(mapping = aes(
    x = month,
    y = (avg_duration / 60),
    fill = rider_type
  )) +
  geom_col() +
  scale_fill_manual(values = c("#202124",
                               "#1a73e8")) +
  labs(title = "Even in January, casual riders' trips last longer than members'",
       x = NULL,
       y = "Average ride duration in minutes",
       fill = "rider type") +
  theme_bw() +
  scale_x_discrete(breaks = waiver(), labels = (month.name)) +
  theme(axis.text.x = element_text(angle = 55, hjust = 1)) 
## `summarise()` has grouped output by 'rider_type'. You can override using the
## `.groups` argument.
## Don't know how to automatically pick scale for object of type <difftime>.
## Defaulting to continuous.

Average trip duration per day of week

cleanbikes %>%
  group_by(rider_type, day_of_week) %>%
  summarise(avg_duration = mean(trip_duration / 60)) %>%
  arrange(rider_type, day_of_week) %>%
  ggplot(mapping = aes(
    x = factor(
      day_of_week,
      levels = c("Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri")
    ),
    y = (avg_duration),
    fill = rider_type
  )) +
  geom_col() +
  scale_fill_manual(values = c("#202124",
                               "#1a73e8")) +
  labs(title = "Casual riders take longer rides on weekends than during the week",
       x = NULL,
       y = "Average duration in minutes",
       fill = "rider type") +
  theme_bw()
## `summarise()` has grouped output by 'rider_type'. You can override using the
## `.groups` argument.
## Don't know how to automatically pick scale for object of type <difftime>.
## Defaulting to continuous.

ggsave("weekday_avg_ride_duration by rider type.jpeg")
## Saving 7 x 5 in image
## Don't know how to automatically pick scale for object of type <difftime>.
## Defaulting to continuous.

Average trip duration per time of day

cleanbikes %>%
  group_by(rider_type, time) %>%
  summarise(avg_duration = mean(trip_duration / 60)) %>%
  arrange(rider_type, time) %>%
  ggplot(aes(
    x = time,
    y = avg_duration,
    group = rider_type,
    color = rider_type
  )) +
  geom_line(size = 1) +
  scale_color_manual(values = c("#1a73e8", "#202124")) +
  scale_x_continuous(breaks = seq(0, 24, by = 2)) +
  labs(title = "Casual ride durations fluctuate more than members",
       x = "Start Hour", y = "Avg Duration of Rides in Minutes") +
  theme_bw()
## `summarise()` has grouped output by 'rider_type'. You can override using the
## `.groups` argument.
## Don't know how to automatically pick scale for object of type <difftime>.
## Defaulting to continuous.

ggsave("by hour_avg_ride_duration by rider type.jpeg")
## Saving 7 x 5 in image
## Don't know how to automatically pick scale for object of type <difftime>.
## Defaulting to continuous.

Preferred Bikes

Preferred bike type

cleanbikes %>%
  ggplot(mapping = aes(x = bike_type, fill = rider_type, )) +
  geom_bar(position = "dodge") +
  theme(legend.position = "none") +
  #geom_text(aes(label = ..count..), stat = "count", vjust = 3, colour = "white") +
  labs(
    title = "Everyone prefers to pedal",
    subtitle =  "Members by 48%, casual by 35%. Electric bikes are more expensive",
    x = NULL,
    y = "Number of Bikes Rented"
  ) +
  scale_fill_manual(values = c("#202124", "#1a73e8")) +
  scale_y_continuous(labels = label_number(suffix = " K", scale = 1e-4)) +
  theme_bw() 

  ggsave("PrefBikesbyMember.jpg")
## Saving 7 x 5 in image