USING REDCap APIs TO REPORT ED CENSUS DATA

Elizabeth Jump, MPH

Agenda

• Workflow overview
• Workflow details
• Lessons learned
• Questions
• Additional resources

Workflow overview

Motivation

Each day, we report the ED Census for each of our six hospitals and the census overall. The report includes:

• Census values
• An indication if the values are higher than expected
• A PDF report with a trend of the last 30 days of values for context

Initial workflow

Workflow with REDCap

Workflow details

Key workflow components

REDCap

• Gives us a stable place to store our data
  
• Allows us to create new records for new data, instead of overwriting an excel file each day

R

• A statistical programming language that is also used for data management and data science
  
• R processes, moves and formats the data in and out of REDCap

Key workflow components

Windows Task Scheduler

• Default application on Windows for running tasks automatically
  
• Allows the process to be fully automated and run without user intervention

Application Programming Interfaces (API)

• A method of sending and receiving data
  
• Can run APIs in and out of REDCap and other systems

Workflow steps

This whole workflow runs from a single R script. The R script has four main components:

1. Fetch data: retrieve and process source data
   
2. REDCap APIs: send data in and out of REDCap
   
3. R Markdown: format data into a PDF report
   
4. Automated email: package everything together for the end-user
   

Back-up: manual data entry

1. Fetch data

• First we need to get source data into REDCap
  
• Ideally this should be automated, meaning no human interference or manual downloading
  
• Best options:
  • API (application programming interface) – this is what we use for this workflow
    
  • ODBC connection to a cloud database
    
  • sFTP (secure file transfer protocol)

The main gist is that you want to use automated methods to access your data wherever possible. This not only allows you to schedule scripts, but it also ensures data are pulled using the same rules/processes each time.

1. Fetch data

library(tidyverse)
library(redcapAPI)
library(httr) 
library(jsonlite)

# fetch ED census data from source ----
api_response <- GET(url = url,
                    add_headers(Authorization = key),
                    encode = "json")

# convert from json to 2x2 and do basic cleaning ----
edvol_today <- jsonlite::fromJSON(rawToChar(api_response$content)) %>%
  jsonlite::flatten() %>%
  select(name, matches("edvol")) %>%
  rename(edvol = edVolume.value,
         edvol_update = edVolume.updateTime) %>%
  mutate(record_id = as.numeric(Sys.Date() - as.Date("2023-01-01")),
         edvol = case_when(as.Date(edvol_update) == Sys.Date() ~ edvol,
                           as.Date(edvol_update) < Sys.Date() ~ NA_integer_),
         edvol_update = case_when(!is.na(edvol) ~ edvol_update, 
                                  TRUE ~ NA_character_),
         across(matches("edvol"), ~ as.character(.x))) %>%
  pivot_longer(names_to = "var",
               values_to = "val",
               cols = matches("edvol")) %>%
  mutate(new_var = paste0(var, "_", name_new)) %>%
  select(-c(name_new, var)) %>%
  pivot_wider(names_from = new_var, values_from = val) %>%
  select(record_id, date, everything()) 

# send into REDCap ----
importRecords(redcap_con,
              data = edvol_today,
              overwriteBehavior = "normal")

2. REDCap APIs

2. REDCap APIs in R

There are several different R packages you can use to get data from REDCap into R with an API

• httr: this is what is used in the API playground in REDCap
• redcapAPI: this is what we typically use
• REDCapR: we’ve used this in the past when we couldn’t get what we needed from REDCapAPI

For a comparison of different packages, see here (note: this might be biased as it was written by the REDCapAPI package developers)

2. Sending data into REDCap

library(redcapAPI)

# set up connection ----
url <- ""
token <- ""
con <- redcapConnection(url = url, 
                        token = token) 

# send data into REDCap ----
importRecords(con,
              data = ed_data,
              overwriteBehavior = "normal",
              returnContent = "count")

url: you can find this in the sample code in the API Playground application in REDCap

token: you generate this from the API application in REDCap

2. Getting data out of REDCap

library(redcapAPI)

# set up connection ----
url <- ""
token <- ""
con <- redcapConnection(url = url, 
                        token = token) 

# get all fields for all records ----
ed_census <- exportRecordsTyped(con)

# get all fields from a certain form ----
ed_census_form <- exportRecordsTyped(rcon = con,
                                     forms = "dsat")

# get only certain fields ----
ed_census_fields <- exportRecordsTyped(rcon = con,
                                       fields = c("record_id", "date", "edvol_tot"))

Storing data in REDCap

3. R Markdown

• R Markdown and Quarto allow you to render R code into a document (PDF, HTML, Word, PPT, etc)
• You can render an R Markdown or Quarto file from another script and specify where you want the file to be saved

today <- str_replace_all(Sys.Date(), "-", "_")
output_file <- paste0("A:/path/ed_census_", today, ".pdf")

rmarkdown::render(input = "A:/source-path/ed_census_markdown_pdf.Rmd",
                  output_file = output_file) 

Note - you render Quarto docs with quarto_render()

3. R Markdown

library(ggplot2)

colors <- c("  ED Census" = "blue", 
            " Normal Range Limits" = "yellow", 
            "Danger Zone Limits" = "red")

edvol %>%
  ggplot(aes(x = date)) + 
  geom_line(aes(y = high_thresh3, color = "Danger Zone Limits"), size = line_width) +
  geom_line(aes(y = high_thresh2, color = " Normal Range Limits"), size = line_width) +
  geom_line(aes(y = edvol_tot, color = "  ED Census"), size = line_width) +
  geom_point(aes(y = edvol_tot), color = "blue", size = 1, shape = 15) + 
  geom_line(aes(y = low_thresh2, color = " Normal Range Limits"), size = line_width) +
  geom_line(aes(y = low_thresh3, color = "Danger Zone Limits"), size = line_width) + 
  ggtitle("ED Census") +
  xlab("Date") + 
  ylab("Daily Census") + 
  labs(subtitle = paste0(subtitle_dates, "\n", "San Mateo County Faciities Only"),
       colour = "",
       caption = "    The limits of the normal range are determined by adding and subtracting twice the standard deviation from the average ED count for each month. 
          The limits of the 'Danger Zone' are determined by adding and subtracting three times the standard deviation from the average ED count for each month.") +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5, face = "bold", size = 9),
        plot.subtitle = element_text(hjust = 0.5, size = 8),
        plot.caption = element_text(hjust = 0.5, size = 6),
        axis.title = element_text(face = "bold", size = 8),
        axis.text.x = element_text(size = 7),
        axis.text.y = element_text(size = 7),
        legend.text = element_text(size = 7),
        legend.position = "top") + 
  scale_color_manual(values = colors)

3. R Markdown

4. Automated email

• To make things as easy as possible for the end user, the last step of the R script sends an email with all the information and attaches the PDF rendered in step 3

• R has a couple packages for sending emails:

  • sendmailR: for plain text emails
  • blastula: for rich text emails

4. Automated email

library(sendmailR)

# inputs ----
body <- "text here"
final_file <- output_file
attach_name <- "text here"
  
weekday <- as.character(lubridate::wday(Sys.Date()-1, label = T, abbr = F))
date <- Sys.Date()-1
subject <- paste0("ED Census Update for ", weekday, " ", date)
  
attach_obj <- mime_part(x = final_file,
                        name = attach_name)
body_with_attach <- list(body, 
                         attach_obj)
  
from <- sprintf("email@email.com")
to <- sprintf(c("email@email.com"))

# send email ----
sendmail(from = from,
         to = to,
         subject = subject,
         msg = body_with_attach,
         control = list(smtpServer = "server"))

4. Automated email

Back-up: manual entry

• If the API to get the data is down and/or if data are missing and need to be entered manually, the person sending the report will manually enter data in REDCap and render the PDF themselves
• This is a huge pro of storing the data in REDCap instead of in an Excel or database without a front end

Lessons learned

Lessons learned

• When choosing workflows to automate, it’s best to choose a mature workflow with defined business rules
  
• Depending on your workflow, you might want to save a copy of the data you pull for record keeping
  
• If you send an email, include explanations of the workflow to foster trust among end-users

Questions

Additional resources

Additional resources

There are some additional resources on the .README in GitHub. The resources include guides and additional information for:

• Windows Task Scheduler
• Sending emails from R
• APIs
• R Markdown and Quarto