ETL Step 1 - Raw Data Refresh

Published

June 17, 2024

This notebook uses the bikeHelpR package to update a database with the latest bike share data. The package pulls data from https://capitalbikeshare.com which provides an API to access bike share data. The raw data is written to the Content DB database to the bike_raw_data table.

Get data from API

Use the the bikeHelpR package to get the latest data from https://capitalbikeshare.com.

Station status

feeds_station_status <- 
  bikeHelpR::feeds_urls() %>% 
  filter(name == "station_status") %>% 
  pull(url) %>% 
  bikeHelpR::get_data() 

station_status <- 
  feeds_station_status %>%
  magrittr::extract2("data") %>%
  dplyr::mutate(time = feeds_station_status$last_updated) %>%
  dplyr::select(
    is_installed, 
    num_bikes_available, 
    last_reported, 
    is_renting, 
    eightd_has_available_keys, 
    num_docks_available, 
    num_docks_disabled, 
    is_returning, 
    station_id, num_ebikes_available, 
    num_bikes_disabled, 
    time
  )

glimpse(station_status)
Rows: 783
Columns: 12
$ is_installed              <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
$ num_bikes_available       <int> 2, 5, 7, 11, 16, 1, 3, 2, 9, 17, 6, 15, 9, 1…
$ last_reported             <int> 1718611028, 1718611029, 1718611032, 17186110…
$ is_renting                <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
$ eightd_has_available_keys <lgl> FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FA…
$ num_docks_available       <int> 14, 14, 7, 8, 6, 8, 12, 16, 3, 2, 9, 0, 5, 1…
$ num_docks_disabled        <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ is_returning              <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
$ station_id                <chr> "d34d2639-ee43-4892-818f-f01116813744", "12b…
$ num_ebikes_available      <int> 0, 0, 1, 0, 9, 0, 0, 2, 1, 0, 0, 0, 1, 0, 0,…
$ num_bikes_disabled        <int> 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 2, 2, 0,…
$ time                      <dttm> 2024-06-17 07:59:29, 2024-06-17 07:59:29, 2…

Station info

# The station information endpoint.
station_information_url <- 
  bikeHelpR::feeds_urls() %>% 
  filter(name == "station_information") %>% 
  pull(url)

# Call the endpoint to obtain the JSON data.
request <- httr2::request(station_information_url)
response <- httr2::req_perform(request)
json_data <- httr2::resp_body_json(response)

# Convert the JSON data into a tibble.
station_info <- 
  json_data$data %>%
  as_tibble() %>%
  tidyr::unnest_wider(stations) %>%
  select(station_id, name, lat, lon) %>%
  distinct() %>%
  mutate(
    last_updated = as.POSIXct(
      json_data$last_updated,
      origin = "1970-01-01 00:00:00 UTC"
    )
  )

glimpse(station_info)
Rows: 783
Columns: 5
$ station_id   <chr> "0825e1e5-1f3f-11e7-bf6b-3863bb334450", "7c46ff05-4fca-44…
$ name         <chr> "1st & H St NW", "Joliet St & MLK Ave SW/Bald Eagle Rec C…
$ lat          <dbl> 38.90036, 38.81909, 38.87482, 38.96378, 38.95369, 38.9305…
$ lon          <dbl> -77.01211, -77.01033, -77.02079, -77.34976, -77.35972, -7…
$ last_updated <dttm> 2024-06-17 08:00:29, 2024-06-17 08:00:29, 2024-06-17 08:…

Update database

Write the new data from the API to the database.

con <- dbConnect(odbc(), "Content DB", timeout = 10)
dbWriteTable(con, "bike_raw_data", station_status, append = TRUE)
dbWriteTable(con, "bike_station_info", station_info, overwrite = TRUE)
dbDisconnect(con)
print("Raw data updated 🎉")
[1] "Raw data updated 🎉"

Update the pin

Station info will also be written to a pin. This pin will be accessed by the shiny app so that it can easily get the bike station info without connecting to the database.

board <- pins::board_rsconnect(
  server = Sys.getenv("CONNECT_SERVER"),
  key = Sys.getenv("CONNECT_API_KEY"),
  versioned = TRUE
)

# Write the model to the board.
pins::pin_write(
  board,
  x = station_info,
  type = "csv",
  name = "bike-predict-r-station-info-pin",
  title = "Bike Predict - ETL - Pinned Station Info",
  description = "Bike station info from https://capitalbikeshare.com."
)

print("Pin 🎉")
[1] "Pin 🎉"