Introduction

This document includes the steps used for ingesting EMS data from Idaho exports, and for determining what fields can be used for matching with ED data, as well as the QA/QC steps needed.

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.

library(tidyverse)
library(zipcode)
data(zipcode)
library(geosphere)
library(udunits2)
library(lubridate)

QA/QC

First we have to see if we need to convert the file format from windows. This has to be done in a terminal tab, not R. Let’s check one of the files.

file -i BioSense2_1-17-19_to_5-15-19-2.csv

If the message says UTF-16, we need to take the exported files and convert the character encoding to UTF-8, again - in a terminal tab, not in R.

iconv -f UTF-16LE -t UTF-8 BioSense1_1-29-18_to_5-15-19-2.csv -o BioSense1_1-29-18_to_5-15-19-2.UTF-8.txt
iconv -f UTF-16LE -t UTF-8 BioSense2_1-17-19_to_5-15-19-2.csv -o BioSense2_1-17-19_to_5-15-19-2.UTF-8.txt

Now, let’s read the converted data into R. In this case, there were multiple exports by date.

df.EMS.1<-read_tsv("BioSense1_1-29-18_to_5-15-19-2.UTF-8.txt", col_names = TRUE)
df.EMS.2<-read_tsv("BioSense2_1-17-19_to_5-15-19-2.UTF-8.txt", col_names = TRUE)
df.EMS<-bind_rows(df.EMS.1,df.EMS.2)

The ‘bind_rows’ function will merge the two separate export files together. If you have more than two files to load, then just use the above code as an example, copy the read.tsv line as many times as you need, and add the other file to the bind_rows function.

Clean headers

We need to rename the columns to get rid of nonword characters and spaces - this will make it easier to work with this dataset.

df.EMS<-df.EMS %>%
    rename_all(funs(gsub("([[:punct:]]|\\s)+", "_", names(df.EMS))))
df.EMS<-df.EMS %>%
    rename_all(funs(gsub("_$", "", names(df.EMS))))

Clean birth dates and add a field for birthday

Here we’re using the lubridate package to normalize birth date format.

df.EMS$Patient_Date_of_Birth <- mdy_hm(df.EMS$Patient_Date_of_Birth)

# create a birthday field
birthdays<-paste(month(as.POSIXlt(df.EMS$Patient_Date_of_Birth)),"-",day(as.POSIXlt(df.EMS$Patient_Date_of_Birth)),sep="")
birthdays[which(birthdays == "NA-NA")]<-NA
df.EMS$Birthday<-birthdays

Create age in years

# create age as if age unit is years - will handle hours, days, and months below
df.EMS$Age_In_Years<-as.integer(df.EMS$Patient_Age_E6_14)

# age unit is hours
df.EMS$Age_In_Years[which(df.EMS$Patient_Age_Units_E6_15 %in% "Hours")] <- as.integer(df.EMS$Age_In_Years[which(df.EMS$Patient_Age_Units_E6_15 %in% "Hours")]/8760)

# age unit is days
df.EMS$Age_In_Years[which(df.EMS$Patient_Age_Units_E6_15 %in% "Days")] <- as.integer(df.EMS$Age_In_Years[which(df.EMS$Patient_Age_Units_E6_15 %in% "Days")]/365)

# age unit is months
df.EMS$Age_In_Years[which(df.EMS$Patient_Age_Units_E6_15 %in% "Months")] <- as.integer(df.EMS$Age_In_Years[which(df.EMS$Patient_Age_Units_E6_15 %in% "Months")]/12)

Clean zip codes

For zip codes per patient, we need to create a temporary ID per patient so we can make sure each patient has their zip code fields populated.

# create temporary unique ID
df.EMS<-df.EMS %>% mutate(Unique_Incident_Number = gsub("([[:punct:]]|\\s)+", "_", paste(Service_Name, Incident_Call_Number, Incident_Number, Age_In_Years, Patient_Gender_E6_11, Birthday, sep = '_')))

We need to fix missing zipcodes from patients where zipcode was not consistently recorded, with some missing (-20)

df.zip.id <- distinct(df.EMS[,c("Unique_Incident_Number","Patient_Postal_Code_E6_8")])

# below are the ones that need fixing
ids.with.zipcodes.to.fix<-df.zip.id %>% 
  group_by(Unique_Incident_Number) %>% 
  filter(n()>1, Patient_Postal_Code_E6_8==-20) %>%
  select(Unique_Incident_Number) %>%
  pull()

# create lookup table for zipcode by patient
fix.zip.lookup<-df.zip.id %>%
  filter(Unique_Incident_Number %in% ids.with.zipcodes.to.fix, Patient_Postal_Code_E6_8 != -20) %>%
  rename(new_postal_code=Patient_Postal_Code_E6_8)

# and finally, fix the missing zipcodes
df.EMS<-df.EMS %>% 
  left_join(fix.zip.lookup, by = "Unique_Incident_Number") %>% 
  mutate(Patient_Postal_Code_E6_8 = ifelse(!is.na(new_postal_code), new_postal_code, Patient_Postal_Code_E6_8)) %>%
  select(-new_postal_code)

Now we can create a better unique id per patient - now including zipcode.

df.EMS<-df.EMS %>% 
  mutate(Unique_Incident_Number = gsub("([[:punct:]]|\\s)+", "_", paste(Service_Name, Incident_Call_Number, Incident_Number, Age_In_Years, Patient_Gender_E6_11, Birthday, Patient_Postal_Code_E6_8, sep = '_')))

Filter by specified pull dates

We want our EMS data dates to match the ED data dates per hospital. So we’re going to read in the crosswalk file containing the specified dates, as well as the cross-reference for PERCS destination code to BioSense facility ID.

crosswalk<-read_tsv("data_pull_dates_and_crosswalk.txt",col_names=T)

# add dates/times for EMS data based on hosp online
df.EMS<-left_join(
  df.EMS,
  select(crosswalk, PERCS_Destination_Code, C_Biosense_Facility_ID, Date_to_pull_from, Timezone),
  by=c("Destination_Code_E20_2"="PERCS_Destination_Code")
)
# convert pull date
df.EMS$Date_to_pull_from<-mdy(df.EMS$Date_to_pull_from)
df.EMS$Dates_Arrived_at_Destination_E5_10<-mdy_hm(df.EMS$Dates_Arrived_at_Destination_E5_10)

# filter EMS data based on dates
df.EMS<-df.EMS %>%
  filter(Dates_Arrived_at_Destination_E5_10 >=  Date_to_pull_from)

Flattening into one record per patient per run

Now we need to collapse fields like chief complaint from multiple rows into one, separated by “|”

# start doing the collapsing and de-selecting: create lookup tables for all the collapsable fields first
# Chief_Complaint_E9_5
df.cc.ems<-df.EMS %>% 
     select(Unique_Incident_Number, Chief_Complaint_E9_5) %>%
     distinct() %>%
     group_by(Unique_Incident_Number) %>% 
     mutate(Chief_Complaint_E9_5 = paste0(Chief_Complaint_E9_5, collapse = "|")) %>%
     distinct()
     
# Primary_Symptom_E9_13
df.ps.ems<-df.EMS %>% 
     select(Unique_Incident_Number, Primary_Symptom_E9_13) %>%
     distinct() %>%
     group_by(Unique_Incident_Number) %>% 
     mutate(Primary_Symptom_E9_13 = paste0(Primary_Symptom_E9_13, collapse = "|")) %>%
     distinct()
     
# Drug_Use_Indicators_Code_E12_19
df.du.ems<-df.EMS %>% 
     select(Unique_Incident_Number, Drug_Use_Indicators_Code_E12_19) %>%
     distinct() %>%
     group_by(Unique_Incident_Number) %>% 
     mutate(Drug_Use_Indicators_Code_E12_19 = paste0(Drug_Use_Indicators_Code_E12_19, collapse = "|")) %>%
     distinct()

# Dispatch_Complaint_Report_E3_1
df.cr.ems<-df.EMS %>% 
     select(Unique_Incident_Number, Dispatch_Complaint_Report_E3_1) %>%
     distinct() %>%
     group_by(Unique_Incident_Number) %>% 
     mutate(Dispatch_Complaint_Report_E3_1 = paste0(Dispatch_Complaint_Report_E3_1, collapse = "|")) %>%
     distinct()

# Complaint_Anatomic_Location_E9_11
df.cal.ems<-df.EMS %>% 
     select(Unique_Incident_Number, Complaint_Anatomic_Location_E9_11) %>%
     distinct() %>%
     group_by(Unique_Incident_Number) %>% 
     mutate(Complaint_Anatomic_Location_E9_11 = paste0(Complaint_Anatomic_Location_E9_11, collapse = "|")) %>%
     distinct()

# Secondary_Impression_E9_16
df.si.ems<-df.EMS %>% 
     select(Unique_Incident_Number, Secondary_Impression_E9_16) %>%
     distinct() %>%
     group_by(Unique_Incident_Number) %>% 
     mutate(Secondary_Impression_E9_16 = paste0(Secondary_Impression_E9_16, collapse = "|")) %>%
     distinct()

# Complaint_Organ_System_E9_12
df.cos.ems<-df.EMS %>% 
     select(Unique_Incident_Number, Complaint_Organ_System_E9_12) %>%
     distinct() %>%
     group_by(Unique_Incident_Number) %>% 
     mutate(Complaint_Organ_System_E9_12 = paste0(Complaint_Organ_System_E9_12, collapse = "|")) %>%
     distinct()

# Dates_Arrived_at_Destination_E5_10
df.da.ems<-df.EMS %>% 
     select(Unique_Incident_Number, Dates_Arrived_at_Destination_E5_10) %>%
     distinct() %>%
     group_by(Unique_Incident_Number) %>% 
     mutate(Dates_Arrived_at_Destination_E5_10 = paste0(Dates_Arrived_at_Destination_E5_10, collapse = "|")) %>%
     distinct()

# Injury_Present_E9_4
df.ip.ems<-df.EMS %>% 
     select(Unique_Incident_Number, Injury_Present_E9_4) %>%
     distinct() %>%
     group_by(Unique_Incident_Number) %>% 
     mutate(Injury_Present_E9_4 = paste0(Injury_Present_E9_4, collapse = "|")) %>%
     distinct()

# now create final data set, adding in collapsed fields
df.EMS<-df.EMS %>%
  select(Unique_Incident_Number, Patient_Date_of_Birth, Age_In_Years, Patient_Gender_E6_11, Birthday, C_Biosense_Facility_ID, Patient_Postal_Code_E6_8, Timezone, Agency_Time_Zone, Service_Name, Incident_Call_Number, Incident_Number) %>%
  distinct() %>%
  left_join(df.da.ems , by="Unique_Incident_Number") %>%
  left_join(df.cos.ems , by="Unique_Incident_Number") %>%
  left_join(df.si.ems , by="Unique_Incident_Number") %>%
  left_join(df.cal.ems , by="Unique_Incident_Number") %>%
  left_join(df.cr.ems , by="Unique_Incident_Number") %>%
  left_join(df.cc.ems , by="Unique_Incident_Number") %>%
  left_join(df.ps.ems , by="Unique_Incident_Number") %>%
  left_join(df.du.ems , by="Unique_Incident_Number") %>%
  left_join(df.ip.ems , by="Unique_Incident_Number")

Checking the data

number of patients

nrow(df.EMS)
## [1] 70162

Check ages

hist(df.EMS$Age_In_Years,breaks=100)

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

Check missingness

Birth dates

birth_dates<-distinct(df.EMS[,c("Unique_Incident_Number","Patient_Date_of_Birth")])
birth_dates %>%
  filter(is.na(Patient_Date_of_Birth))
## # A tibble: 254 x 2
##    Unique_Incident_Number                              Patient_Date_of_Bir…
##    <chr>                                               <dttm>              
##  1 Ada_County_City_EMS_System_E6_BFDTRAIN1022103_68_M… NA                  
##  2 Ada_County_City_EMS_System_M13_18_0015138_77_Male_… NA                  
##  3 Ada_County_City_EMS_System_M13_18_0019540_55_Male_… NA                  
##  4 Ada_County_City_EMS_System_M13_18_0026186_45_Male_… NA                  
##  5 Ada_County_City_EMS_System_M13_18_0041780_17_Femal… NA                  
##  6 Ada_County_City_EMS_System_M13_19_0007664_30_Male_… NA                  
##  7 Ada_County_City_EMS_System_M15_18_0004371_28_Male_… NA                  
##  8 Ada_County_City_EMS_System_M15_18_0005870_28_Male_… NA                  
##  9 Ada_County_City_EMS_System_M15_18_0011177_25_Male_… NA                  
## 10 Ada_County_City_EMS_System_M15_18_0012380_55_Male_… NA                  
## # … with 244 more rows
254/70162*100
## [1] 0.3620193

Default birthday

patient_birthdays<-distinct(df.EMS[,c("Unique_Incident_Number","Birthday")])
plot(table(patient_birthdays$Birthday))

There is a spike at 1/1 - we need to clean by setting these to NA.

df.EMS <- df.EMS %>% mutate(Patient_Date_of_Birth = replace(Patient_Date_of_Birth, which(Birthday == "1-1"), NA))

Patient Arrived at Destination Date/Time

filter(df.EMS, is.na(Dates_Arrived_at_Destination_E5_10))
## # A tibble: 0 x 21
## # … with 21 variables: Unique_Incident_Number <chr>,
## #   Patient_Date_of_Birth <dttm>, Age_In_Years <int>,
## #   Patient_Gender_E6_11 <chr>, Birthday <chr>,
## #   C_Biosense_Facility_ID <dbl>, Patient_Postal_Code_E6_8 <dbl>,
## #   Timezone <chr>, Agency_Time_Zone <chr>, Service_Name <chr>,
## #   Incident_Call_Number <chr>, Incident_Number <chr>,
## #   Dates_Arrived_at_Destination_E5_10 <chr>,
## #   Complaint_Organ_System_E9_12 <chr>, Secondary_Impression_E9_16 <chr>,
## #   Complaint_Anatomic_Location_E9_11 <chr>,
## #   Dispatch_Complaint_Report_E3_1 <chr>, Chief_Complaint_E9_5 <chr>,
## #   Primary_Symptom_E9_13 <chr>, Drug_Use_Indicators_Code_E12_19 <chr>,
## #   Injury_Present_E9_4 <chr>

100% compliant, and has the arrival date/time at the hospital.

Gender

patient_sexes<-distinct(df.EMS[,c("Unique_Incident_Number","Patient_Gender_E6_11")])
table(patient_sexes$Patient_Gender_E6_11)
## 
##         Female           Male Not Applicable      Not Known   Not Recorded 
##          37284          32821              1             11             17

Gender is almost 100% complete.

Chief Complaint

chief_complaints<-distinct(df.EMS[,c("Unique_Incident_Number","Chief_Complaint_E9_5")])
filter(chief_complaints, is.na(Chief_Complaint_E9_5))
## # A tibble: 0 x 2
## # … with 2 variables: Unique_Incident_Number <chr>,
## #   Chief_Complaint_E9_5 <chr>

This is 100% compliant. #### Chief Complaint Anatomic Location

chief_complaints_loc<-distinct(df.EMS[,c("Unique_Incident_Number","Complaint_Anatomic_Location_E9_11")])
filter(chief_complaints_loc, is.na(Complaint_Anatomic_Location_E9_11))
## # A tibble: 0 x 2
## # … with 2 variables: Unique_Incident_Number <chr>,
## #   Complaint_Anatomic_Location_E9_11 <chr>

100% compliant.

Chief Complaint Organ System

chief_complaints_orgs<-distinct(df.EMS[,c("Unique_Incident_Number","Complaint_Organ_System_E9_12")])
filter(chief_complaints_orgs, is.na(Complaint_Organ_System_E9_12))
## # A tibble: 0 x 2
## # … with 2 variables: Unique_Incident_Number <chr>,
## #   Complaint_Organ_System_E9_12 <chr>

100% compliant.

Drug_Use_Indicators_Code_E12_19

drug_use<-distinct(df.EMS[,c("Unique_Incident_Number","Drug_Use_Indicators_Code_E12_19")])
filter(drug_use, is.na(Drug_Use_Indicators_Code_E12_19))
## # A tibble: 0 x 2
## # … with 2 variables: Unique_Incident_Number <chr>,
## #   Drug_Use_Indicators_Code_E12_19 <chr>

100% compliant.

Destination/Transferred To, Code

dest_trans<-distinct(df.EMS[,c("Unique_Incident_Number","C_Biosense_Facility_ID")])
filter(dest_trans, is.na(C_Biosense_Facility_ID))
## # A tibble: 0 x 2
## # … with 2 variables: Unique_Incident_Number <chr>,
## #   C_Biosense_Facility_ID <dbl>

100% compliant.

Check arrival date/time is consistent per EMS run

df.EMS %>% filter(grepl("\\|", Dates_Arrived_at_Destination_E5_10)) %>% select(Dates_Arrived_at_Destination_E5_10) %>% print(n=Inf)
## # A tibble: 27 x 1
##    Dates_Arrived_at_Destination_E5_10     
##    <chr>                                  
##  1 2018-02-24 08:55:00|2018-02-24 08:50:00
##  2 2018-02-24 09:44:00|2018-02-24 09:43:00
##  3 2018-03-29 17:26:00|2018-03-29 17:30:00
##  4 2018-04-03 10:38:00|2018-04-03 10:37:00
##  5 2018-04-08 13:08:00|2018-04-08 13:15:00
##  6 2018-04-17 13:52:00|2018-04-17 13:53:00
##  7 2018-05-11 20:36:00|2018-05-11 20:42:00
##  8 2018-06-05 23:26:00|2018-06-05 22:58:00
##  9 2018-06-14 20:49:00|2018-06-14 20:48:00
## 10 2018-07-07 18:00:00|2018-07-07 17:58:00
## 11 2018-07-09 22:32:00|2018-07-09 22:31:00
## 12 2018-07-17 12:49:00|2018-07-17 12:45:00
## 13 2018-07-27 18:53:00|2018-07-27 18:15:00
## 14 2018-07-30 19:26:00|2018-07-30 19:27:00
## 15 2018-09-07 19:45:00|2018-09-07 19:38:00
## 16 2018-09-17 08:15:00|2018-09-17 07:50:00
## 17 2018-09-25 13:38:00|2018-09-25 13:07:00
## 18 2018-09-29 10:22:00|2018-09-29 10:23:00
## 19 2018-11-04 04:52:00|2018-11-04 04:42:00
## 20 2019-01-03 03:11:00|2019-01-03 02:38:00
## 21 2019-01-19 15:39:00|2019-01-19 15:45:00
## 22 2019-01-21 11:13:00|2019-01-21 11:12:00
## 23 2019-01-21 22:26:00|2019-01-21 22:29:00
## 24 2019-02-06 18:51:00|2019-02-06 19:19:00
## 25 2019-03-04 10:59:00|2019-03-04 10:20:00
## 26 2019-03-05 14:20:00|2019-03-05 13:51:00
## 27 2019-03-10 02:55:00|2019-03-10 04:10:00

We have to deal with fixing these few arrival dates that differ in the same EMS run - they’re all pretty close, so we’ll just take the average per EMS run.

time.rows_to_fix<-df.EMS %>% filter(grepl("\\|", Dates_Arrived_at_Destination_E5_10)) %>% select(Unique_Incident_Number) %>% pull()
for (i in 1:length(time.rows_to_fix)) {
  df.EMS[df.EMS$Unique_Incident_Number %in% time.rows_to_fix[i],]$Dates_Arrived_at_Destination_E5_10<-as.POSIXct(mean(as.numeric(ymd_hms(strsplit(df.EMS[df.EMS$Unique_Incident_Number %in% time.rows_to_fix[i],]$Dates_Arrived_at_Destination_E5_10,"\\|")[[1]]))), origin='1970-01-01')
}
df.EMS$Dates_Arrived_at_Destination_E5_10<-ymd_hms(df.EMS$Dates_Arrived_at_Destination_E5_10)

Now let’s check the arrival date distribution of all the EMS runs.

arrival_dates<-hist(df.EMS$Dates_Arrived_at_Destination_E5_10, "months", freq = TRUE)

This is what we expect to see given the dates in the crosswalk pull file.

Gender

We need to normalize gender to 1 letter code so it matches the ED data.

df.EMS$Patient_Gender_E6_11<-substr(df.EMS$Patient_Gender_E6_11,1,1)

Normalize zipcode

We need to make zipcode a character field, to be compatible with ED data.

df.EMS$Patient_Postal_Code_E6_8 <- as.character(df.EMS$Patient_Postal_Code_E6_8)

Drop runs with missing patient zipcodes

We found that if a patient in an EMS run is missing the zipcode, it is impossible to match this EMS run using probabilistic matching. So we need to drop these runs.

df.EMS <- df.EMS %>%
 filter((!is.na(Patient_Postal_Code_E6_8)) & Patient_Postal_Code_E6_8 != -20)

Save data

save(df.EMS, crosswalk, file="~/Idaho_EMS/EMS_cleaned_and_crosswalk.RData")