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)