Extracting All of Us survey and EHR data

The Researcher Workbench, available to registered All of Us researchers, provides tools for creating datasets using the All of Us database. Researchers first use the Workbench to create a cohort defined by values of survey variables (e.g., gender, race, age), observations from the electronic health record (e.g., diagnosis codes, lab values), data from wearable devices (e.g., fitbit), genomic data, and/or physical measurements. Data – either predefined or user-specified concept sets – from each of the database tables can then be extracted for the cohort.

The allofus package takes a programmatic approach to extracting data from the All of Us database. One advantage to this approach is that it is easier to document and share the process. This is especially important for reproducibility and transparency. To demonstrate, this vignette will walk through some examples of extracting data from the All of Us database.

Suppose we want a cohort of the female All of Us participants who weren’t born in the US. For this cohort, we want to extract data on their history of diabetes, their A1C lab values, and their use of metformin after joining All of Us.

Survey data

The first step is extracting gender and country of birth from the survey data. We can retrieve these variables either by concept code or concept id. Using the allofus searchable codebook, we find that the concept id for gender is 1585838 and for birthplace is 1586135. We can use the aou_survey function to extract these variables from the All of Us database. This function takes a vector of concept ids or codes, and returns a dataset with one row per participant and one column per variable. The question_output argument allows you to specify the names of the column in the output dataset. Alternatively, you can specify “concept_id” or “concept_code” to name the variables according to one of those options.


svy_vars <- aou_survey(questions = c(1585838, 1586135), question_output = c("gender", "birthplace"))
#> Warning: No cohort provided.
#> → Pulling survey data for entire All of Us cohort.

Note that the aou_survey function takes a cohort as an argument, with the only requirement that it contains a column called person_id. If no cohort is provided (e.g., if you are using survey data to define your cohort), the function will pull data for the entire All of Us cohort.

Because aou_survey() defaults to collect = FALSE, the query isn’t fully executed yet, and we can continue to perform analyses, like cross-tabulating the two variables, on the database.

count(svy_vars, gender, birthplace)
#> # Source:   SQL [?? x 3]
#> # Database: BigQueryConnection
#>    gender            birthplace       n
#>    <chr>             <chr>      <int64>
#>  1 Skip              Skip          4449
#>  2 NA                NA              97
#>  3 Woman             USA         205440
#>  4 Woman             Other        40348
#>  5 Man               Skip          1484
#>  6 Transgender       USA            504
#>  7 PreferNotToAnswer USA            475
#>  8 PreferNotToAnswer Other          112
#>  9 Man               USA         131698
#> 10 Man               Other        21059
#> # ℹ more rows

To create our cohort, we can subset the data to the participants matching our criteria.

cohort <- svy_vars %>%
  filter(gender == "Woman", birthplace == "Other")

#> # Source:   SQL [?? x 5]
#> # Database: BigQueryConnection
#>    person_id birthplace gender birthplace_date gender_date
#>      <int64> <chr>      <chr>  <date>          <date>     
#>  1   xxxxxxx Other      Woman  2019-08-xx      2019-08-xx 
#>  2   xxxxxxx Other      Woman  2018-06-xx      2018-06-xx 
#>  3   xxxxxxx Other      Woman  2019-05-xx      2019-05-xx 
#>  4   xxxxxxx Other      Woman  2019-02-xx      2019-02-xx 
#>  5   xxxxxxx Other      Woman  2018-06-xx      2018-06-xx 
#>  6   xxxxxxx Other      Woman  2019-03-xx      2019-03-xx 
#>  7   xxxxxxx Other      Woman  2019-04-xx      2019-04-xx 
#>  8   xxxxxxx Other      Woman  2019-04-xx      2019-04-xx 
#>  9   xxxxxxx Other      Woman  2019-05-xx      2019-05-xx 
#> 10   xxxxxxx Other      Woman  2020-09-xx      2020-09-xx 
#> # ℹ more rows

Note that the aou_survey() function automatically creates an additional column for every survey variable extracted, which contains the date the participant answered the question. These are automatically named by adding “_date” as a suffix to the column names.

EHR data

Now that we have our cohort, we want to create a dataset with data from these participants’ medical records. We first want to find the concept ids of the data we want to extract. There are a number of tools we could use to do so:

  1. Athena: a web-based tool for searching the entire OMOP vocabulary. The concept id for Type 2 diabetes mellitus can be found under ID. There are many results for this search, but we want only standard concepts when searching for EHR data.

  2. Data Browser: a tool for exploring data in the All of Us database. Finding the concept id for Type 2 diabetes mellitus requires searching for that concept and then clicking “Sources”:

  3. Researcher Workbench: Creating a concept set can allow you to find a number of concept ids quickly. Add concepts to your concept set, and then review the concept set to see them all.

Here are some concepts we may want to include. Keep in mind that this is an example, and a real analysis would have a more thorough search for relevant concepts.

Concept ID Concept Name Domain
201826 Type 2 diabetes mellitus Condition
4193704 Type 2 diabetes mellitus without complication Condition
40164929 metformin hydrochloride 500 MG Oral Tablet Drug
40164897 metformin hydrochloride 1000 MG Oral Tablet Drug
3004410 Hemoglobin A1c/Hemoglobin.total in Blood Measurement
3005673 Hemoglobin A1c/Hemoglobin.total in Blood by HPLC Measurement

First, we’ll extract data on diabetes diagnoses. We can use the aou_concept_set function to extract data from the All of Us database. This function takes a cohort, a vector of concepts, and a domain or set of domains. You can actually extract data from multiple domains at once – the default is to search all domains. However, it is helpful to specify the domain(s) when you can, for speed and clarity. The output = argument specifies the type of data to extract. Here, we’ll extract an indicator variable for whether the participant has a diabetes diagnosis at any point in their medical record. We’ll call that variable “t2dm”.

t2dm <- aou_concept_set(cohort,
  concepts = c(201826, 4193704),
  domains = "condition", output = "indicator",
  concept_set_name = "t2dm"

We can also extract data within a certain time window. For example, if we only want to extract metformin use after joining All of Us, we can specify a start date as the date the participant answered the gender question (on the Basics survey). We’ll count how many times the participant was prescribed metformin after that time point.

metformin <- aou_concept_set(cohort,
  concepts = c(40164929, 40164897),
  domains = "drug", output = "count",
  start_date = "gender_date", concept_set_name = "metformin"

For a concept set in the measurement domain, we likely want to set output = "all" because we are interested in the values of the measurements, not whether they had any or how many they had.

a1c <- aou_concept_set(cohort,
  concepts = c(3004410, 3005673),
  domains = "measurement", output = "all", start_date = "gender_date"

The output to this is a dataset with one row per measurement. We can see that the second participant in the output has had 4 A1C measurements since joining All of Us, while the fifth had 1 and the rest had none.

#> # Source:   SQL [?? x 9]
#> # Database: BigQueryConnection
#>    person_id concept_date concept_id concept_name concept_domain value_as_number
#>      <int64> <date>          <int64> <chr>        <chr>                    <dbl>
#>  1   xxxxxx1 NA                   NA NA           NA                        NA  
#>  2   xxxxxx2 2022-03-xx      3004410 Hemoglobin … Measurement                5.6
#>  3   xxxxxx2 2021-07-xx      3004410 Hemoglobin … Measurement                5.6
#>  4   xxxxxx2 2021-05-xx      3004410 Hemoglobin … Measurement                5.5
#>  5   xxxxxx2 2020-10-xx      3004410 Hemoglobin … Measurement                6  
#>  6   xxxxxx3 NA                   NA NA           NA                        NA  
#>  7   xxxxxx4 NA                   NA NA           NA                        NA  
#>  8   xxxxxx5 2019-04-xx      3005673 Hemoglobin … Measurement                5.9
#>  9   xxxxxx6 NA                   NA NA           NA                        NA  
#> 10   xxxxxx7 NA                   NA NA           NA                        NA  
#> # ℹ more rows
#> # ℹ 3 more variables: value_as_concept_id <int64>, value_source_value <chr>,
#> #   gender_date <date>

Health history survey data

Suppose we want to compare the EHR data to self-reported survey data on diabetes diagnoses. Returning again to the aou_survey() function, we need to first find the concept id for the question asking about diabetes diagnoses. Wuestions from the health history surveys are more complex than others like gender and birthplace: there are branching questions and complex skip patterns for each question, which were also asked about family members, and groups of diagnoses were asked about together. The searchable health history codebook is a useful tool for finding the concept id for these questions. We can search for “diabetes” and find the concept id for the question asking about type 2 diabetes diagnoses for the respondent: 43529932. We can also use the Data Browser to find the same concept id: