install.packages(“tidyverse”) library(tidyverse)
bikes <- read_csv(‘C:/Users/cathb/desktop/lucy/programming/Case study bikes/Cyclicity data 2022/202201-divvy-tripdata.csv’)
bikes2 <- read_csv(‘C:/Users/cathb/desktop/lucy/programming/Case study bikes/Cyclicity data 2022/202202-divvy-tripdata.csv’)
bikes3 <- read_csv(‘C:/Users/cathb/desktop/lucy/programming/Case study bikes/Cyclicity data 2022/202203-divvy-tripdata.csv’)
bikes4 <- read_csv(‘C:/Users/cathb/desktop/lucy/programming/Case study bikes/Cyclicity data 2022/202204-divvy-tripdata.csv’)
bikes5 <- read_csv(‘C:/Users/cathb/desktop/lucy/programming/Case study bikes/Cyclicity data 2022/202205-divvy-tripdata.csv’)
bikes6 <- read_csv(‘C:/Users/cathb/desktop/lucy/programming/Case study bikes/Cyclicity data 2022/202206-divvy-tripdata.csv’)
bikes7 <- read_csv(‘C:/Users/cathb/desktop/lucy/programming/Case study bikes/Cyclicity data 2022/202207-divvy-tripdata.csv’)
bikes8 <- read_csv(‘C:/Users/cathb/desktop/lucy/programming/Case study bikes/Cyclicity data 2022/202208-divvy-tripdata.csv’)
bikes9 <- read_csv(‘C:/Users/cathb/desktop/lucy/programming/Case study bikes/Cyclicity data 2022/202209-divvy-publictripdata.csv’)
bikes10 <- read_csv(‘C:/Users/cathb/desktop/lucy/programming/Case study bikes/Cyclicity data 2022/202210-divvy-tripdata.csv’)
bikes11 <- read_csv(‘C:/Users/cathb/desktop/lucy/programming/Case study bikes/Cyclicity data 2022/202211-divvy-tripdata.csv’)
bikes12 <- read_csv(‘C:/Users/cathb/desktop/lucy/programming/Case study bikes/Cyclicity data 2022/202212-divvy-tripdata.csv’)
bikes1 <- bikes
###Merge tables into one yearly dataset ##### bikes <- rbind(bikes1,bikes2,bikes3,bikes4,bikes5,bikes6,bikes7,bikes8,bikes9, bikes10, bikes11,bikes12)
###Examine dataset ##### glimpse(bikes)
#bikes has 5,667,717 observations
view(bikes)
#1. Check for missing values #####
#Search for missing start station ids print(“Count of total missing ssi values -”) sum(is.na(bikes$start_station_id )) #833064
#Search for missing end station ids print(“Count of total missing esi values -”) sum(is.na(bikes$end_station_id )) #892742
#Search for missing start station names print(“Count of total missing ssn values -”) sum(is.na(bikes$start_station_name )) #833064
#Search for missing end station names print(“Count of total missing esn values -”) sum(is.na(bikes$end_station_name )) #833064
892742 - 833064
#There are 833,064 rides missing start and end station ids, are probably the same rides. #There are an additional 59,678 rides missing end station ids only: check. #search for missing ride_id nulls
print(“Count of total missing ride id values -”) sum(is.na(bikes$ride_id )) #0
#None of the observations are missing a ride id, start time, end
time, or rider type. #search for rideable_type nulls
print(“Count of total missing rideable_type values -”)
sum(is.na(bikes$rideable_type )) #0
#search for started_at nulls
print(“Count of total missing started_at values -”)
sum(is.na(bikes$started_at )) #0
#search for ended_at nulls
print(“Count of total missing ended_at values -”)
sum(is.na(bikes$ended_at )) #0
#search for member_casual nulls
print(“Count of total missing member_casual values -”)
sum(is.na(bikes$member_casual )) #0
#nulls as percentage of total rows is 14.7. Big number but because #1. I have no way to find/replace/recreate data with proxy data; #2 this is just an exercise and the data is big enough without the nulls, #I decided to delete them. deleting to make the database more manageable,
833064/5667717 * 100
#2. Delete rows with nulls #### #Filter for null start and end station names filter(is.na(start_station_name), is.na(end_station_name)) #returns 427,449 rows
#Remove those rows to new table (dataset?) cleanbikes <- bikes %>% na.omit(.na(start_station_name), is.na(end_station_name))
nrow(cleanbikes) #4369360
cleanerbikes <- cleanbikes %>% na.omit(.na(start_station_id), is.na(end_station_id))
nrow(cleanerbikes) #4369360
#3. Verify rows with missing values are removed with a few spot checks ####
cleanerbikes %>% sum(is.na(start_station_name))
#make sure all nulls are removed: do omit.na individually
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#Updated number of rows count(cb$ride_id)
#save table1 write_csv(cb, “cb.csv”)
#Average number of trips on weekdays cb %>% filter(day_of_week != “Saturday” & day_of_week != “Sunday”) %>% summary(cb) # A tibble: 3,011,022 rows
#average number of trips per weekend #average number of trips per month #average number of trips at morning rush hour #average number of trips at evening rush hour
max(cb$trip_duration) #Time difference of 2061244 secs
min(cb$trip_duration) #Time difference of 61 secs
mean(cb$trip_duration) #Time difference of 1043.772 secs
median(cb$trip_duration) #Time difference of 648 secs
#Average duration of trips on weekends cb %>% filter(day_of_week == “Saturday” | day_of_week == “Sunday”) %>% summarise(mean = mean(trip_duration)) #1259.028 secs
#Average duration of rides on weekdays cb %>% filter(day_of_week != “Saturday” & day_of_week != “Sunday”) %>% summarise(mean = mean(trip_duration)) #952.1977 secs
#Average duration of rides per month cb %>% group_by(month) %>% summarise(mean = mean(trip_duration))
#1 Average duration of rides per month per rider type cb %>% group_by(month, rider_type)%>% summarise(mean = mean(trip_duration)) %>% print(n = 24)
#2 Average duration of rides per month per rider type cb %>% group_by(month) %>% group_by(rider_type) %>% summarise(mean = mean(trip_duration))
#Average duration of rides per day of week per rider type cb %>% group_by(day_of_week, rider_type) %>% summarise(mean = mean(trip_duration)) %>% print(n = 14)
#Average duration of rides at rush hour per rider type #1. create start time column cb\(start_hour <- hour(cb\)started_at)
#2. check by pulling rows cb %>% filter(start_hour > 6 & start_hour < 9) %>% group_by(rider_type) %>% arrange(desc(start_hour))
#3. work out average cb %>% group_by(start_hour, rider_type) %>% filter(start_hour > 6 & start_hour < 9) %>% summarise(mean = mean(trip_duration))
#start_hour rider_type mean #
#number of rides per bike, casual riders vs members #number of rides per bike, casual vs members weekdays #number of rides per bike, casual vs members weekends #number of rides per bike, casual vs members rushhour
install.packages(“scales”) library(scales)
ggplot(data = cb) + geom_bar(mapping = aes(x = rider_type, fill =
rider_type)) + labs( title = “Total number of rides”,
x = “Riders”, y = “Number of Rides”, color = “bikecolor”) +
scale_y_continuous(labels = comma)
ggsave(“total rides.png”)
#members’ preferred bikes ggplot(data = cb) + geom_bar(mapping =
aes(x = bike_type, fill = bike_type )) + labs( title = “Preferred Type
of Bike”,
x = “Type of Bike”, y = “Number of Bikes Rented”, show.legend = FALSE) +
annotate(“text”, x = 1, y = 1000000, label = “2,600,000”, color =
“white”) + annotate(“text”, x = 2, y = 1000000, label = “1,600,000”,
color = “white”) + scale_y_continuous(labels = comma)
ggsave(“preferred_bike.png”)
#casual riders preferred bikes: 2 x 2-bar chart, one for each member
group. #could also do stacked charts? cb %>% filter(rider_type ==
“casual”) %>% ggplot (mapping = aes(x = bike_type, fill = “bike_type”
)) + geom_bar() + labs( title = “Casual Riders’ Preferred Ride”,
x = “Type of Bike”, y = “Number of Bikes Rented”) + annotate(“text”, x =
1, y = 500000, label = “1,000,000”, color = “white”) + annotate(“text”,
x = 2, y = 500000, label = “650,000”, color = “white”)
geom_text(aes(label = count), vjust = 2, colour = “white”)
ggsave(“casual_preferredbikes.png”)
#members’ preferred bikes: 2 x 2-bar chart, one for each member
group. #could also do stacked charts? cb %>% filter(rider_type ==
“member”) %>% ggplot (mapping = aes(x = bike_type, fill = “bike_type”
)) + geom_bar() + geom_text(aes(label = ..count..), stat = “count”,
vjust = 2, colour = “white”)+ labs( title = “Members’ Preferred
Ride”,
x = “Type of Bike”, y = “Number of Bikes Rented”) +
theme(legend.position = “none”)
ggsave(“member_preferredbikes.png”)
#total number of rides per month ggplot(data = cb) + geom_bar(mapping
= aes(x = month) , fill = “lightblue”) + labs( title = “Total Number of
Rides per Month”,
x = “Month”, y = “Number of Rides”) + scale_y_continuous(labels =
comma)
#STACKED BAR CHART #total number of rides per month per rider:
stacked ggplot(data = cb, aes(x = month , fill = rider_type)) +
geom_bar() labs( title = “Total Number of Rides per Month”,
x = “Month”, y = “Number of Rides”)
ggsave(“monthly_by_user_stacked.jpg”)
ggsave(“ridesPerMonth.png”)
cb %>% filter (rider_type == “casual”) %>% ggplot(mapping =
aes(x = month) ) + geom_bar(fill = “orange”) + labs( title = “Total
Number of Casual Rides per Month”,
x = “Month”, y = “Number of Rides”) + scale_y_continuous(labels =
comma)
ggsave(“Casual_ridesPerMonth.png”)
#members number of rides per month() cb %>% filter (rider_type ==
“member”) %>% ggplot(mapping = aes(x = month) ) + geom_bar(fill =
“blue”) + labs( title = “Total Number of Member Rides per Month”,
x = “Month”, y = “Number of Rides”) + scale_y_continuous(labels =
comma)
ggsave(“members_ridesPerMonth.png”)
#bike_rides %>% # group_by(usertype,days_of_week) %>% # summarise(avg_duration = mean(trip_duration)) %>% # arrange(usertype,days_of_week) %>% # ggplot(mapping = aes(x= days_of_week, y=avg_duration, fill= usertype))+ # geom_col(position=“dodge”,width = 0.5)+scale_fill_manual(values=c(“#3B5BA5”, “#E87A5D”))+ # labs(title = “Average duration vs weekdays”,x=“WEEKDAYS”,y=“AVERAGE DURATION”)
#average trip duration per month - had to replace “geombar” with
geomcol” for this to work cb %>% group_by(rider_type, month) %>%
summarise(avg_duration = mean(trip_duration)) %>% arrange(rider_type,
month) %>% ggplot(mapping = aes(x = month, y = avg_duration, fill =
rider_type)) + geom_col() + labs(title = “Average Duration of Rides
Every Month”,
x = “Month”, y = “Average Duration in Seconds”)
#average trip duration per day of week - had to replace “geombar” with geomcol” for this to work
cb %>% group_by(rider_type, day_of_week) %>%
summarise(avg_duration = mean(trip_duration)) %>% arrange(rider_type,
day_of_week) %>% ggplot(mapping = aes(x = day_of_week, y =
avg_duration, fill = rider_type)) + geom_col() + labs(title = “Average
Duration of Rides Per Day of Week”,
x = “Day of Week”, y = “Average Duration in Seconds”)
ggsave(“avg_ride_duration by rider type.png”)
#average duration of rides per day of week: both rider types on same chartr()
#average duration of rides, casual vs members rushhour
#members most used station #casual most used station
#average number of rides per day of week: both rider types on same chartr() #average number of trips per weekend #average number of trips per month #average number of trips at morning rush hour #average number of trips at evening rush hour