Link to data: https://divvy-tripdata.s3.amazonaws.com/index.html
Download libraries
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.1.4 ✔ purrr 1.0.2
## ✔ forcats 1.0.0 ✔ stringr 1.5.1
## ✔ ggplot2 3.4.4 ✔ tibble 3.2.1
## ✔ lubridate 1.9.3 ✔ tidyr 1.3.1
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::between() masks data.table::between()
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::first() masks data.table::first()
## ✖ lubridate::hour() masks data.table::hour()
## ✖ lubridate::isoweek() masks data.table::isoweek()
## ✖ dplyr::lag() masks stats::lag()
## ✖ dplyr::last() masks data.table::last()
## ✖ lubridate::mday() masks data.table::mday()
## ✖ lubridate::minute() masks data.table::minute()
## ✖ lubridate::month() masks data.table::month()
## ✖ lubridate::quarter() masks data.table::quarter()
## ✖ lubridate::second() masks data.table::second()
## ✖ purrr::transpose() masks data.table::transpose()
## ✖ lubridate::wday() masks data.table::wday()
## ✖ lubridate::week() masks data.table::week()
## ✖ lubridate::yday() masks data.table::yday()
## ✖ lubridate::year() masks data.table::year()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
Importing csv files
X202302_divvy_tripdata <- read_csv("C:/Users/ecox9/OneDrive/Desktop/Divvy_months/202302-divvy-tripdata.csv", show_col_types = FALSE)
X202303_divvy_tripdata <- read_csv("C:/Users/ecox9/OneDrive/Desktop/Divvy_months/202303-divvy-tripdata.csv", show_col_types = FALSE)
X202304_divvy_tripdata <- read_csv("C:/Users/ecox9/OneDrive/Desktop/Divvy_months/202304-divvy-tripdata.csv", show_col_types = FALSE)
X202305_divvy_tripdata <- read_csv("C:/Users/ecox9/OneDrive/Desktop/Divvy_months/202305-divvy-tripdata.csv", show_col_types = FALSE)
X202306_divvy_tripdata <- read_csv("C:/Users/ecox9/OneDrive/Desktop/Divvy_months/202306-divvy-tripdata.csv", show_col_types = FALSE)
X202307_divvy_tripdata <- read_csv("C:/Users/ecox9/OneDrive/Desktop/Divvy_months/202307-divvy-tripdata.csv", show_col_types = FALSE)
X202308_divvy_tripdata <- read_csv("C:/Users/ecox9/OneDrive/Desktop/Divvy_months/202308-divvy-tripdata.csv", show_col_types = FALSE)
X202309_divvy_tripdata <- read_csv("C:/Users/ecox9/OneDrive/Desktop/Divvy_months/202309-divvy-tripdata.csv", show_col_types = FALSE)
X202310_divvy_tripdata <- read_csv("C:/Users/ecox9/OneDrive/Desktop/Divvy_months/202310-divvy-tripdata.csv", show_col_types = FALSE)
X202311_divvy_tripdata <- read_csv("C:/Users/ecox9/OneDrive/Desktop/Divvy_months/202311-divvy-tripdata.csv", show_col_types = FALSE)
X202312_divvy_tripdata <- read_csv("C:/Users/ecox9/OneDrive/Desktop/Divvy_months/202312-divvy-tripdata.csv", show_col_types = FALSE)
X202401_divvy_tripdata <- read_csv("C:/Users/ecox9/OneDrive/Desktop/Divvy_months/202401-divvy-tripdata.csv", show_col_types = FALSE)
Merging into one file
df = rbind(X202302_divvy_tripdata, X202303_divvy_tripdata, X202304_divvy_tripdata, X202305_divvy_tripdata, X202306_divvy_tripdata, X202307_divvy_tripdata, X202308_divvy_tripdata, X202309_divvy_tripdata, X202310_divvy_tripdata, X202311_divvy_tripdata, X202312_divvy_tripdata, X202401_divvy_tripdata)
Make a copy for backup.
df_copy = copy(df)
Preview data
head(df)
## # A tibble: 6 × 13
## ride_id rideable_type started_at ended_at
## <chr> <chr> <dttm> <dttm>
## 1 CBCD0D7777F0E45F classic_bike 2023-02-14 11:59:42 2023-02-14 12:13:38
## 2 F3EC5FCE5FF39DE9 electric_bike 2023-02-15 13:53:48 2023-02-15 13:59:08
## 3 E54C1F27FA9354FF classic_bike 2023-02-19 11:10:57 2023-02-19 11:35:01
## 4 3D561E04F739CC45 electric_bike 2023-02-26 16:12:05 2023-02-26 16:39:55
## 5 0CB4B4D53B2DBE05 electric_bike 2023-02-20 11:55:23 2023-02-20 12:05:48
## 6 C67EB62172C472EB classic_bike 2023-02-24 18:50:16 2023-02-24 18:56:40
## # ℹ 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>
colnames(df)
## [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"
dim(df)
## [1] 5674449 13
This shows there are originally 5674449 data points
Remove duplicate entries.
df <- df %>% distinct()
Check for number of blank/’NA’ values in each column
colSums(is.na(df))
## ride_id rideable_type started_at ended_at
## 0 0 0 0
## start_station_name start_station_id end_station_name end_station_id
## 868160 868292 922111 922252
## start_lat start_lng end_lat end_lng
## 0 0 7151 7151
## member_casual
## 0
There are a large number of blank values in station ids and names. This is OK - according to the Divvy website it is possible to end a trip at a location that is not an official station, and presumably a trip could also be started from such a location. But I will delete rows that are missing any latitudes or longitudes as each ride should have been geo-located, so this suggests an error in data collection. Similarly, I will delete rows missing started_at and ended_at times as each ride should have a timestamp.
df <- df %>% filter(!is.na(df$started_at))
df <- df %>% filter(!is.na(df$end_lat))
This removes 7152 entries
Check for unique values
unique(df$rideable_type)
## [1] "classic_bike" "electric_bike" "docked_bike"
We see there is a “docked_bike” category in addition to “classic_bike” and “electric_bike”. This doesn’t tell us which type of bike was chosen, so we will delete rows with “docked_bike” for rideable_type.
df <- df %>% filter(rideable_type != "docked_bike")
This removes 74442 rows
To ensure all the start dates are between 2/23 and 1/24, I used the sort button on the table in R Studio for this. The dates are all legitimate. Sorting through latitudes and longitudes, I notice that there are 3 end_lat values of 0.000. This is clearly an error and we will delete these.
df <- filter(df, end_lat != 0.0000)
Create a “ride_time” column displaying the length of each ride
df$ride_time <- df$ended_at - df$started_at
I noticed there are some ride times in the negatives and equal to 0. Per the website, rides are rounded up to the nearest minute, but it seems that rides totaling 0 seconds are errors, so I will delete these and the rides with negative times.
df <- filter(df, ride_time > 0)
This removes 1358 rows
Look at station names.
unique_start = unique(df$start_station_name)
unique_start = sort(unique_start)
There are over 1500 stations (can count using length(unique()), and some appear to be duplicates except for “Public Rack” at the start of the name. We can remove this text.
df$start_station_name = gsub("Public Rack - ", "",as.character(df$start_station_name))
df$end_station_name = gsub("Public Rack - ", "",as.character(df$end_station_name))
This cuts down the number of different stations slightly, but there are still 1571 starting station names, much greater than the number of 800-some stations as stated online… Were this a real project, I would bring this up as a point of confusion and inquire as to how exactly station names are generated. I’m going to keep all of the data related to this but be wary that there may be some issues if analyzing by station name.
Making a weekday column
df$weekday = weekdays(df$started_at)
Making a new column that sorts data into 5 groups based on starting latitudes for analyzing geographic patterns
df$geographic_group = cut(df$start_lat, breaks=5, labels=FALSE)
Check out dimensions of cleaned datafile
dim(df)
## [1] 5591495 16
The final file has 5591495 data points, making it 98.5% the size of the original
Writing final file to CSV to use in Tableau for visualizations
df_csv = write.csv(df,"C:/Users/ecox9/divvy_updated.csv", row.names=FALSE)