In meerapatelmd/polyester2: ETL Synthea into OMOP CDM

knitr::opts_chunk$set(echo = TRUE,
                      cache = TRUE,
                      message = 'hide')

library(tidyverse)
library(pg13)

conn <- local_connect(dbname = "polyester")

person <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'PERSON')

observation_period <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'OBSERVATION_PERIOD')

visit_occurrence <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'VISIT_OCCURRENCE')

visit_detail <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'VISIT_DETAIL')

condition_occurrence <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'CONDITION_OCCURRENCE')

drug_exposure <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'DRUG_EXPOSURE')

procedure_occurrence <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'PROCEDURE_OCCURRENCE')

device_exposure <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'DEVICE_EXPOSURE')

measurement <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'MEASUREMENT')

observation <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'OBSERVATION')

note <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'NOTE')

specimen <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'SPECIMEN')

fact_relationship <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'FACT_RELATIONSHIP')

survey_conduct <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'SURVEY_CONDUCT')

location <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'LOCATION')

location_history <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'LOCATION_HISTORY')

care_site <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'CARE_SITE')

provider <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'PROVIDER')

payer_plan_period <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'PAYER_PLAN_PERIOD')

cost <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'COST')

drug_era <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'DRUG_ERA')

dose_era <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'DOSE_ERA')

condition_era <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'CONDITION_ERA')

metadata <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'METADATA')

cdm_source <- summarize_table(conn = conn,
                           schema = 'omop_cdm',
                           table = 'CDM_SOURCE')

objNames <-
c('person', 'observation_period', 'visit_occurrence', 'visit_detail', 'condition_occurrence', 'drug_exposure', 'procedure_occurrence', 'device_exposure', 'measurement', 'observation', 'note', 'specimen', 'fact_relationship', 'survey_conduct', 'location', 'location_history', 'care_site', 'provider', 'payer_plan_period', 'cost', 'drug_era', 'dose_era', 'condition_era', 'metadata', 'cdm_source')

for (i in seq_along(objNames)) {

        x <- get(objNames[i])
        x <-
                x %>%
                purrr::map(function(x) x %>% reduce(bind_cols)) %>%
                purrr::map(function(x) as_tibble(x)) %>%
                purrr::map(function(x) x %>% 
                                        rename_at(., names(x), funs(c("TOTAL_ROWS", "VALUESET_LENGTH", "VALUESET", "NULL_COUNT")))) %>%
                purrr::map(function(x) x %>% mutate_all(as.character)) %>%
                dplyr::bind_rows(.id = "FIELD") %>%
                group_by_at(vars(c(FIELD, TOTAL_ROWS, VALUESET_LENGTH, NULL_COUNT))) %>%
                summarize_at(vars(VALUESET), ~ paste(unique(.), collapse = "|")) %>%
                ungroup()

        assign(x = objNames[i],
               value = x)

}

final <- 
        list(
                PERSON = person,
                OBSERVATION_PERIOD = observation_period,
                VISIT_OCCURRENCE = visit_occurrence,
                VISIT_DETAIL = visit_detail,
                CONDITION_OCCURRENCE = condition_occurrence,
                DRUG_EXPOSURE = drug_exposure,
                PROCEDURE_OCCURRENCE = procedure_occurrence,
                DEVICE_EXPOSURE = device_exposure,
                MEASUREMENT = measurement,
                OBSERVATION = observation,
                NOTE = note,
                SPECIMEN = specimen,
                FACT_RELATIONSHIP = fact_relationship,
                SURVEY_CONDUCT = survey_conduct,
                LOCATION = location,
                LOCATION_HISTORY = location_history,
                CARE_SITE = care_site,
                PROVIDER = provider,
                PAYER_PLAN_PERIOD = payer_plan_period,
                COST = cost,
                DRUG_ERA = drug_era,
                DOSE_ERA = dose_era,
                CONDITION_ERA = condition_era,
                METADATA = metadata,
                CDM_SOURCE = cdm_source
        ) %>%
        dplyr::bind_rows(.id = "TABLE")
final