Introduction

This document includes the steps used for investigating ED Essence data from the NSSP BioSense platform to determine what fields can be used for matching, and what will be needed to clean them.

First let’s set up the envronment. In your RStudio session enter the following code. If you have problems you may need to install these libraries. Remember to swap out your username for “username” in quotes below.

library(tidyverse)
library(lubridate)
library(zipcode)
data(zipcode)
library(geosphere)
library(udunits2)
library(knitr)
library(httr)
httr::set_config(config(ssl_verifypeer = 0L))
library(jsonlite)
library(keyring)

Introduction

The ESSENCE data from NSSP is already flattened and cleaned, so we don’t have to do a lot of cleaning to get it ready for the probabilistic linkage. For example, age is already normalized in years, so we don’t have to clean it.

Pull data

key_set(service = "essence", username = "username")

url <- "https://essence.syndromicsurveillance.org/nssp_essence/api/dataDetails/csv?endDate=22Jul19&percentParam=noPercent&geographySystem=region&datasource=va_hosp&detector=probrepswitch&startDate=29Jan18&timeResolution=daily&medicalGroupingSystem=essencesyndromes&aqtTarget=TimeSeries"
api_response <- GET(url, 
                    authenticate(key_list("essence")[1,2], 
                                 key_get("essence", 
                                 key_list("essence")[1,2])))
api_data <- content(api_response, by = "csv/text") %>%
  read_csv()
  takeFields<-c("C_Unique_Patient_ID","Visit_ID","C_Visit_Date_Time","Age","C_Patient_Age","C_Patient_Age_Units","Arrived_Date_Time","Birth_Date_Time","Chief_Complaint_Combo","ChiefComplaintMDTUpdates","ChiefComplaintOrig","ChiefComplaintParsed","ChiefComplaintUpdates","Diagnosis_Combo","DischargeDiagnosisUpdates","DischargeDisposition","DischargeDispositionUpdates","Hospital","Sex","ZipCode","Category_flat","SubCategory_flat")
# clean data
all_ED_data_cleaned<-api_data
all_ED_data_cleaned<-all_ED_data_cleaned %>%
  select(takeFields)

Let’s get the number of records.

all_ED_data_cleaned
## # A tibble: 653,248 x 22
##    C_Unique_Patien… Visit_ID C_Visit_Date_Time     Age C_Patient_Age
##    <chr>            <chr>    <dttm>              <dbl>         <dbl>
##  1 3933620          6216564… 2019-07-22 23:58:02    75            75
##  2 351291           6216564… 2019-07-22 23:57:34    63            63
##  3 3877672          6216564… 2019-07-22 23:57:29    42            42
##  4 KM00567777       KM00270… 2019-07-22 23:55:00    18            18
##  5 3864967          6216564… 2019-07-22 23:53:36    36            36
##  6 405669           6216562… 2019-07-22 23:53:36    48            48
##  7 2059641          4506496… 2019-07-22 23:53:00    33            33
##  8 11364            258038   2019-07-22 23:52:00    13            13
##  9 J049547          HA01788… 2019-07-22 23:48:00    68            68
## 10 J215640          HA01787… 2019-07-22 23:48:00    56            56
## # … with 653,238 more rows, and 17 more variables:
## #   C_Patient_Age_Units <chr>, Arrived_Date_Time <dttm>,
## #   Birth_Date_Time <chr>, Chief_Complaint_Combo <chr>,
## #   ChiefComplaintMDTUpdates <chr>, ChiefComplaintOrig <chr>,
## #   ChiefComplaintParsed <chr>, ChiefComplaintUpdates <chr>,
## #   Diagnosis_Combo <chr>, DischargeDiagnosisUpdates <chr>,
## #   DischargeDisposition <chr>, DischargeDispositionUpdates <chr>,
## #   Hospital <dbl>, Sex <chr>, ZipCode <chr>, Category_flat <chr>,
## #   SubCategory_flat <chr>

Birth date and birthday

# set missing to NA
all_ED_data_cleaned <- all_ED_data_cleaned %>% mutate(Birth_Date_Time = replace(Birth_Date_Time, which(Birth_Date_Time == "none"), NA))

# normalize format
all_ED_data_cleaned$Birth_Date <- as.Date(all_ED_data_cleaned$Birth_Date_Time)

# create field for birthday
birthdays<-paste(month(as.POSIXlt(all_ED_data_cleaned$Birth_Date)),"-",day(as.POSIXlt(all_ED_data_cleaned$Birth_Date)),sep="")
birthdays[which(birthdays == "NA-NA")]<-NA
all_ED_data_cleaned$Birthday<-birthdays

Next we need to check for default birthdays.

patient_birthdays<-distinct(all_ED_data_cleaned[,c("C_Unique_Patient_ID","Birthday")])
plot(table(patient_birthdays$Birthday))

There’s a spike at 1/1 so we need to set those to missing.

all_ED_data_cleaned <- all_ED_data_cleaned %>% mutate(Birth_Date_Time = replace(Birth_Date_Time, which(Birthday == "1-1"), NA))

Next let’s get the missingness for birth dates.

# create non-redundant table of patients and birthdates
patient_birthdates<-distinct(all_ED_data_cleaned[,c("C_Unique_Patient_ID","Birth_Date_Time")])

# compute missing %
(length(patient_birthdates$Birth_Date_Time[is.na(patient_birthdates$Birth_Date_Time)]) / length(patient_birthdates$Birth_Date_Time)) * 100
## [1] 48.83809

So it looks like there’s a very high missing % for birthdays - which means that it won’t be a suitable field for probabilistic linkage.

Zipcodes

Now we clean the zipcodes.

# clean zipcodes
all_ED_data_cleaned <- all_ED_data_cleaned %>% mutate(ZipCode = replace(ZipCode, which(ZipCode == "none"), NA))

Now we’ll check the missingness for zip codes.

# create non-redundant table of patients and ZipCode
patient_ZipCode<-distinct(all_ED_data_cleaned[,c("C_Unique_Patient_ID","ZipCode")])

# compute missing %
(length(patient_ZipCode$ZipCode[is.na(patient_ZipCode$ZipCode)]) / length(patient_ZipCode$ZipCode)) * 100
## [1] 0.9220414

Age

Now we’ll check the missingness for age.

# create non-redundant table of patients and Age
patient_Age<-distinct(all_ED_data_cleaned[,c("C_Unique_Patient_ID","Age")])

# compute missing %
(length(patient_Age$Age[is.na(patient_Age$Age)]) / length(patient_Age$Age)) * 100
## [1] 0

Let’s also check the age distribution.

hist(patient_Age$Age,breaks=100)

The distribution is what we expect to see in ED data.

Sex

Now we’ll check the missingness for sex.

# create non-redundant table of patients and Sex
patient_Sex<-distinct(all_ED_data_cleaned[,c("C_Unique_Patient_ID","Sex")])

# compute missing %
(length(patient_Sex$Sex[is.na(patient_Sex$Sex)]) / length(patient_Sex$Sex)) * 100
## [1] 0

Hospital code

Now we’ll check the missingness for hospital code.

# create non-redundant table of patients and Hospital
patient_Hospital<-distinct(all_ED_data_cleaned[,c("C_Unique_Patient_ID","Hospital")])

# compute missing %
(length(patient_Hospital$Hospital[is.na(patient_Hospital$Hospital)]) / length(patient_Hospital$Hospital)) * 100
## [1] 0

C_Visit_Date_Time

Now we’ll check the missingness for C_Visit_Date_Time, which is the closest we have to arrival time.

# compute missing %
(length(all_ED_data_cleaned$C_Visit_Date_Time[is.na(all_ED_data_cleaned$C_Visit_Date_Time)]) / length(all_ED_data_cleaned$C_Visit_Date_Time)) * 100
## [1] 0

Save data

save(all_ED_data_cleaned, file="~/Idaho_EMS/ED_cleaned.RData")