How to extract data using inspectEHR

Edward Palmer

2019-11-22

Source: vignettes/extract_data.Rmd

inspectEHR contains a number of helper functions for wrangling data. In due course, these will be ported over to cleanEHR to keep the roles of the two packages clear; in theory cleanEHR = cleaning/wrangling, inspectEHR = verification/validation.

First, install inspectEHR from github, or download the dev release directly.

Establish a database connection. You will most likely be working with sqlite as the postgres instance is confined to the UCL IDHS. inspectEHR ships with a synthetic database, that you can play around with. The content of the data is more or less nonsense, but it is structurally sound.

library(inspectEHR)

# Synthetic database ships with inspectEHR
db_pth <- system.file("testdata/synthetic_db.sqlite3", package = "inspectEHR")
ctn <- connect(sqlite_file = db_pth)

Enter the fields you want to extract using HIC codes. See the cleanEHR repo for more information on these codes. Alternately, the qref object from inspectEHR contrains all the information on what data exists in CC-HIC. I write this here as a tribble to keep everything in the same document. If you want to rename column names then it is important to keep names and code names together and in the same order.

It’s best to separate out 1d (time invariant) and 2d (longitudinal) data.

We can now extact 2d data. This will return a table with 1 row per patient per time unit specified in the cadance option. For example, a cadance of 1 will return a table with 1 row per patient per hour. This does not create a row if nothing was recorded, so the rows might run: 1, 2, 4, if nothing was recorded in hour 3. Note how a summary function can be provided to coalese_rows. This determines the behaviour when collapsing dataitems down into rows below the natural cadance of recording.

Depending upon how much data you want to pull into a long form for analysis, this can take considerable time (more than 24 hours if you want to convert all 50,000 episodes).

You can expand this table to a regular row cadance, by inserting rows of NAs with expand_missing

We can also pull out data at any arbitrary temporal resolution and custom define the behaviour for information recorded at resolution higher than you are sampling. For example, if heart rates are sampled every hour, and you want to pull out data every 6 hours, we need to know what action to take to collapse those heart rates into a single row. You can supply any custom summary function to the coalese_rows argument of extract_timevarying.

You can also run extract_timevarying with the option of cadance = “timestamp” which both pull out the exact timing of events and labels them with their timestamp. I advise against this unless you have a specific requirement, as it is a very memory intensive task.