Divvy Exercise R Script
#library(tidyverse) #helps wrangle data
library(tidyverse)
library(janitor)
Sys.setlocale("LC_ALL", "en_US.UTF-8")## [1] "LC_COLLATE=en_US.UTF-8;LC_CTYPE=en_US.UTF-8;LC_MONETARY=en_US.UTF-8;LC_NUMERIC=C;LC_TIME=en_US.UTF-8"STEP 1: COLLECT DATA
Upload Divvy datasets (csv files) here
q1_2019 <- read_csv("d:/datasets/Divvy_Trips_2019_Q1.csv")
q1_2020 <- read_csv("d:/datasets/Divvy_Trips_2020_Q1.csv")STEP 2: WRANGLE
compare_df_cols(q1_2019,q1_2020) # compare columns and data type## column_name q1_2019 q1_2020
## 1 bikeid numeric <NA>
## 2 birthyear numeric <NA>
## 3 end_lat <NA> numeric
## 4 end_lng <NA> numeric
## 5 end_station_id <NA> numeric
## 6 end_station_name <NA> character
## 7 end_time POSIXct, POSIXt <NA>
## 8 ended_at <NA> POSIXct, POSIXt
## 9 from_station_id numeric <NA>
## 10 from_station_name character <NA>
## 11 gender character <NA>
## 12 member_casual <NA> character
## 13 ride_id <NA> character
## 14 rideable_type <NA> character
## 15 start_lat <NA> numeric
## 16 start_lng <NA> numeric
## 17 start_station_id <NA> numeric
## 18 start_station_name <NA> character
## 19 start_time POSIXct, POSIXt <NA>
## 20 started_at <NA> POSIXct, POSIXt
## 21 to_station_id numeric <NA>
## 22 to_station_name character <NA>
## 23 trip_id numeric <NA>
## 24 tripduration numeric <NA>
## 25 usertype character <NA>Rename columns we can copy the label names into spreadsheet to build this code , it can reduce typo.
q1_2019 <- rename(q1_2019, ride_id=trip_id, rideable_type=bikeid, start_station_id=from_station_id, start_station_name=from_station_name, end_station_id=to_station_id, end_station_name=to_station_name, started_at=start_time, ended_at=end_time, member_casual=usertype)Compare again, looks better now
compare_df_cols(q1_2019,q1_2020)## column_name q1_2019 q1_2020
## 1 birthyear numeric <NA>
## 2 end_lat <NA> numeric
## 3 end_lng <NA> numeric
## 4 end_station_id numeric numeric
## 5 end_station_name character character
## 6 ended_at POSIXct, POSIXt POSIXct, POSIXt
## 7 gender character <NA>
## 8 member_casual character character
## 9 ride_id numeric character
## 10 rideable_type numeric character
## 11 start_lat <NA> numeric
## 12 start_lng <NA> numeric
## 13 start_station_id numeric numeric
## 14 start_station_name character character
## 15 started_at POSIXct, POSIXt POSIXct, POSIXt
## 16 tripduration numeric <NA>Convert ride_id and rideable_type to character so that they can stack correctly
q1_2019$ride_id <- as.character(q1_2019$ride_id)
q1_2019$rideable_type<- as.character(q1_2019$rideable_type)Stack individual quarter’s data frames into one big data frame
all_trips <- bind_rows(q1_2019,q1_2020)Remove lat, long, birthyear, and gender fields as this data was dropped beginning in 2020
all_trips <- all_trips %>%
select(-c(tripduration,gender,birthyear,start_lat,start_lng,end_lat,end_lng))STEP 3: CLEAN
Dimensions of the data frame?
dim(all_trips) ## [1] 791956 9See the first 6 rows of data frame. Also tail(all_trips) str(all_trips) See list of columns and data types (numeric, character, etc)
summary(all_trips) ## ride_id started_at
## Length:791956 Min. :2019-01-01 00:04:37.00
## Class :character 1st Qu.:2019-02-28 17:04:04.75
## Mode :character Median :2020-01-07 12:48:50.50
## Mean :2019-09-01 11:58:08.35
## 3rd Qu.:2020-02-19 19:31:54.75
## Max. :2020-03-31 23:51:34.00
##
## ended_at rideable_type start_station_id
## Min. :2019-01-01 00:11:07.00 Length:791956 Min. : 2.0
## 1st Qu.:2019-02-28 17:15:58.75 Class :character 1st Qu.: 77.0
## Median :2020-01-07 13:02:50.00 Mode :character Median :174.0
## Mean :2019-09-01 12:17:52.17 Mean :204.4
## 3rd Qu.:2020-02-19 19:51:54.50 3rd Qu.:291.0
## Max. :2020-05-19 20:10:34.00 Max. :675.0
##
## start_station_name end_station_id end_station_name member_casual
## Length:791956 Min. : 2.0 Length:791956 Length:791956
## Class :character 1st Qu.: 77.0 Class :character Class :character
## Mode :character Median :174.0 Mode :character Mode :character
## Mean :204.4
## 3rd Qu.:291.0
## Max. :675.0
## NA's :1##Statistical summary of data. Mainly for numerics There are a few problems we will need to fix:
In the “member_casual” column, there are two names for members (“member” and “Subscriber”) and two names for casual riders (“Customer” and “casual”). We will need to consolidate that from four to two labels.
The data can only be aggregated at the ride-level, which is too granular. We will want to add some additional columns of data – such as day, month, year – that provide additional opportunities to aggregate the data.
We will want to add a calculated field for length of ride since the 2020Q1 data did not have the “tripduration” column. We will add “ride_length” to the entire dataframe for consistency.
There are some rides where tripduration shows up as negative, including several hundred rides where Divvy took bikes out of circulation for Quality Control reasons. We will want to delete these rides.
In the “member_casual” column, replace “Subscriber” with “member” and “Customer” with “casual”
Before 2020, Divvy used different labels for these two types of riders … we will want to make our dataframe consistent with their current nomenclature
table(all_trips$member_casual)##
## casual Customer member Subscriber
## 48480 23163 378407 341906Reassign to the desired values (we will go with the current 2020 labels)
all_trips <- all_trips %>% mutate(member_casual = recode(member_casual, "Subscriber" = "member", "Customer" = "casual"))Check to make sure the proper number of observations were reassigned
table(all_trips$member_casual)##
## casual member
## 71643 720313Add columns that list the date, month, day, and year of each ride
This will allow us to aggregate ride data for each month, day, or year … before completing these operations we could only aggregate at the ride level
all_trips$date <- as.Date(all_trips$started_at)
all_trips$month <- format(as.Date(all_trips$date),"%m")
all_trips$day <- format(as.Date(all_trips$date),"%d")
all_trips$year <- format(as.Date(all_trips$date),"%Y")
all_trips$day_of_week <- format(as.Date(all_trips$date),"%A")Add a “ride_length” calculation to all_trips (in seconds)
all_trips$ride_length <- difftime(all_trips$ended_at,all_trips$started_at)Inspect the structure of the columns
str(all_trips)## tibble [791,956 × 15] (S3: tbl_df/tbl/data.frame)
## $ ride_id : chr [1:791956] "21742443" "21742444" "21742445" "21742446" ...
## $ started_at : POSIXct[1:791956], format: "2019-01-01 00:04:37" "2019-01-01 00:08:13" ...
## $ ended_at : POSIXct[1:791956], format: "2019-01-01 00:11:07" "2019-01-01 00:15:34" ...
## $ rideable_type : chr [1:791956] "2167" "4386" "1524" "252" ...
## $ start_station_id : num [1:791956] 199 44 15 123 173 98 98 211 150 268 ...
## $ start_station_name: chr [1:791956] "Wabash Ave & Grand Ave" "State St & Randolph St" "Racine Ave & 18th St" "California Ave & Milwaukee Ave" ...
## $ end_station_id : num [1:791956] 84 624 644 176 35 49 49 142 148 141 ...
## $ end_station_name : chr [1:791956] "Milwaukee Ave & Grand Ave" "Dearborn St & Van Buren St (*)" "Western Ave & Fillmore St (*)" "Clark St & Elm St" ...
## $ member_casual : chr [1:791956] "member" "member" "member" "member" ...
## $ date : Date[1:791956], format: "2019-01-01" "2019-01-01" ...
## $ month : chr [1:791956] "01" "01" "01" "01" ...
## $ day : chr [1:791956] "01" "01" "01" "01" ...
## $ year : chr [1:791956] "2019" "2019" "2019" "2019" ...
## $ day_of_week : chr [1:791956] "Tuesday" "Tuesday" "Tuesday" "Tuesday" ...
## $ ride_length : 'difftime' num [1:791956] 390 441 829 1783 ...
## ..- attr(*, "units")= chr "secs"Convert “ride_length” from Factor to numeric so we can run calculations on the data
all_trips$ride_length <- as.numeric(as.character(all_trips$ride_length))Remove “bad” data The dataframe includes a few hundred entries when bikes were taken out of docks and checked for quality by Divvy or ride_length was negative
We will create a new version of the dataframe (v2) since data is being removed
all_trips_v2 <- all_trips[!(all_trips$start_station_name == "HQ QR" | all_trips$ride_length < 0),]STEP 4: CONDUCT DESCRIPTIVE ANALYSIS
Descriptive analysis on ride_length (all figures in seconds)
summary(all_trips_v2$ride_length)## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 1 331 539 1189 912 10632022Compare members and casual users
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual, FUN = mean) ## all_trips_v2$member_casual all_trips_v2$ride_length
## 1 casual 5372.7839
## 2 member 795.2523aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual, FUN = median) ## all_trips_v2$member_casual all_trips_v2$ride_length
## 1 casual 1393
## 2 member 508aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual, FUN = max) ## all_trips_v2$member_casual all_trips_v2$ride_length
## 1 casual 10632022
## 2 member 6096428aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual, FUN = min)## all_trips_v2$member_casual all_trips_v2$ride_length
## 1 casual 2
## 2 member 1# Combine data into one dataframe
combined_data <- rbind(
cbind(mean_ride_length, statistic = "Mean"),
cbind(max_ride_length, statistic = "Max"),
cbind(median_ride_length, statistic = "Median"),
cbind(min_ride_length,statistic = "Min")
)
ggplot(combined_data, aes(x = member_casual, y = ride_length, fill = member_casual)) +
geom_bar(stat = "identity") +
facet_wrap(~ statistic, scales = "free_y") + # Free y-axis scales
ggtitle("Ride Length by Member Type and Statistic") +
labs(x = "Member Type", y = "Ride Length")
# Try density chart
See the average ride time by each day for members vs casual users
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual +
all_trips_v2$day_of_week, FUN = mean)## all_trips_v2$member_casual all_trips_v2$day_of_week all_trips_v2$ride_length
## 1 casual Friday 6090.7373
## 2 member Friday 796.7338
## 3 casual Monday 4752.0504
## 4 member Monday 822.3112
## 5 casual Saturday 4950.7708
## 6 member Saturday 974.0730
## 7 casual Sunday 5061.3044
## 8 member Sunday 972.9383
## 9 casual Thursday 8451.6669
## 10 member Thursday 707.2093
## 11 casual Tuesday 4561.8039
## 12 member Tuesday 769.4416
## 13 casual Wednesday 4480.3724
## 14 member Wednesday 711.9838idk1 <- aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual +
all_trips_v2$day_of_week, FUN = mean)Notice that the days of the week are out of order. Let’s fix that.
all_trips_v2$day_of_week <- ordered(all_trips_v2$day_of_week, levels=c( "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday","Sunday"))Now, let’s run the average ride time by each day for members vs casual users
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual +
all_trips_v2$day_of_week,FUN =mean)## all_trips_v2$member_casual all_trips_v2$day_of_week all_trips_v2$ride_length
## 1 casual Monday 4752.0504
## 2 member Monday 822.3112
## 3 casual Tuesday 4561.8039
## 4 member Tuesday 769.4416
## 5 casual Wednesday 4480.3724
## 6 member Wednesday 711.9838
## 7 casual Thursday 8451.6669
## 8 member Thursday 707.2093
## 9 casual Friday 6090.7373
## 10 member Friday 796.7338
## 11 casual Saturday 4950.7708
## 12 member Saturday 974.0730
## 13 casual Sunday 5061.3044
## 14 member Sunday 972.9383analyze ridership data by type and weekday
all_trips_v2 %>% mutate(weekday = wday(started_at, label = TRUE)) %>% #creates weekday field using wday()
group_by(member_casual, weekday) %>% #groups by usertype and weekday
summarise(number_of_rides = n() ,average_duration =
mean(ride_length)) %>% #calculates the number of rides and average duration# calculates the average duration
arrange(member_casual, weekday) # sorts## `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 18652 5061.
## 2 casual Mon 5591 4752.
## 3 casual Tue 7311 4562.
## 4 casual Wed 7690 4480.
## 5 casual Thu 7147 8452.
## 6 casual Fri 8013 6091.
## 7 casual Sat 13473 4951.
## 8 member Sun 60197 973.
## 9 member Mon 110430 822.
## 10 member Tue 127974 769.
## 11 member Wed 121902 712.
## 12 member Thu 125228 707.
## 13 member Fri 115168 797.
## 14 member Sat 59413 974.all_trips_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)## `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 18652 5061.
## 2 casual Mon 5591 4752.
## 3 casual Tue 7311 4562.
## 4 casual Wed 7690 4480.
## 5 casual Thu 7147 8452.
## 6 casual Fri 8013 6091.
## 7 casual Sat 13473 4951.
## 8 member Sun 60197 973.
## 9 member Mon 110430 822.
## 10 member Tue 127974 769.
## 11 member Wed 121902 712.
## 12 member Thu 125228 707.
## 13 member Fri 115168 797.
## 14 member Sat 59413 974.Let’s visualize the number of rides by rider type
all_trips_v2 %>% group_by(member_casual,day_of_week) %>%
summarise(number_of_rides=n(),average_duration = mean(ride_length)) %>%
arrange(member_casual,day_of_week) %>%
ggplot(aes(x=day_of_week, y= number_of_rides, fill = member_casual)) +
geom_col(position = "dodge")+ggtitle("Rider type vs day of week")## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.
Let’s create a visualization for average duration
all_trips_v2 %>% group_by(member_casual, day_of_week) %>% summarise(number_of_rides = n() ,average_duration = mean(ride_length)) %>% arrange(member_casual, day_of_week) %>% ggplot(aes(x = day_of_week, y = average_duration, fill = member_casual)) + geom_col(position = "dodge")+ggtitle("Average Duration")## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.
Year over year comparison
all_trips_v2 %>% group_by(member_casual,year) %>%
summarise(number_of_rides = n(), average_duration = sum(ride_length)) %>%
group_by(member_casual,year) %>%
ggplot(aes(x=year, y = number_of_rides,fill=member_casual))+ geom_col(position = "dodge")+ geom_smooth(method = "lm")+ggtitle("Year over year")## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.
## `geom_smooth()` using formula = 'y ~ x'