These files come from Centene Corporation’s Transparency in Coverage site.
library(DiagrammeR)
mermaid("graph TB
A[TOC URLs]-->B[TOC URL Responses]
B-->C[OON URLs]
B-->D[INN URLs]
C-->E[OON Responses]
D-->F[INN Responses]
G{NPI Results}-->E
H{NPI Results}-->F
")Build the TOC URL Table
# Index of TOC URLs
centene_toc_urls <- data.frame(
entity = c("Centene"),
sub_entity = c("Ambetter",
"Fidelis",
"QualChoice",
"HealthNet",
"MHN",
"WellCare_NC"),
type = c("toc"),
origin = c("https://www.centene.com/content/dam/centene/Centene%20Corporate/json/DOCUMENT/2022-08-30_ambetter_index.json",
"https://www.centene.com/content/dam/centene/Centene%20Corporate/json/DOCUMENT/2022-08-30_fidelis_index.json",
"https://www.centene.com/content/dam/centene/Centene%20Corporate/json/DOCUMENT/2022-08-30_qualchoice_index.json",
"https://www.centene.com/content/dam/centene/Centene%20Corporate/json/DOCUMENT/2022-08-30_healthnet_index.json",
"https://www.centene.com/content/dam/centene/Centene%20Corporate/json/DOCUMENT/2022-08-30_mhn_index.json",
"https://www.centene.com/content/dam/centene/Centene%20Corporate/json/DOCUMENT/2022-08-30_wellcarenc_index.json"))
# Sub-Index of QualChoice TOC URls
centene_toc_qualchoice_subs <- data.frame(
entity = c("Centene"),
sub_entity = c("QualChoice"),
sub_index = c("Alf",
"Arnold_Bevins",
"Hot_Springs",
"Dillards",
"JV_Manufacturing",
"Royal_Financial"),
type = c("toc"),
origin = c("https://www.centene.com/content/dam/centene/Centene%20Corporate/json/DOCUMENT/2022-08-30_qualchoice_alf_index.json",
"https://www.centene.com/content/dam/centene/Centene%20Corporate/json/DOCUMENT/2022-08-30_qualchoice_arnold_and_blevins_index.json",
"https://www.centene.com/content/dam/centene/Centene%20Corporate/json/DOCUMENT/2022-08-30_qualchoice_city_of_hot_springs_index.json",
"https://www.centene.com/content/dam/centene/Centene%20Corporate/json/DOCUMENT/2022-08-30_qualchoice_dillards_index.json",
"https://www.centene.com/content/dam/centene/Centene%20Corporate/json/DOCUMENT/2022-08-30_qualchoice_jv_manufacturing_index.json",
"https://www.centene.com/content/dam/centene/Centene%20Corporate/json/DOCUMENT/2022-08-30_qualchoice_royal_financial_index.json"))
# Join the two data frames
toc_url_centene <- dplyr::full_join(
centene_toc_urls,
centene_toc_qualchoice_subs,
by = c("entity",
"sub_entity",
"type",
"origin"),
copy = TRUE) |>
dplyr::relocate(sub_index,
.after = "sub_entity")Build the Response Table
toc_resp_centene <- toc_url_centene |>
dplyr::select(origin) |>
dplyr::distinct() |>
tibble::deframe() |>
purrr::map_dfr(defog_toc)
#> Error in `map()`:
#> ℹ In index: 1.
#> Caused by error in `httr2::req_perform()`:
#> ! HTTP 404 Not Found.
toc_resp_centene <- toc_resp_centene |>
dplyr::rename(type = rate_type) |>
dplyr::mutate(entity = dplyr::case_when(
entity == "Centene Management Company LLC" ~ "Centene",
TRUE ~ entity))
#> Error in eval(expr, envir, enclos): object 'toc_resp_centene' not found#> Error in eval(expr, envir, enclos): object 'toc_resp_centene' not foundBuild the Out-of-Network Table
oon_urls_centene <- toc_resp_centene |>
dplyr::filter(type == "out_of_network") |>
dplyr::select(location) |>
dplyr::distinct() |>
tibble::deframe()
#> Error in eval(expr, envir, enclos): object 'toc_resp_centene' not found
oon_resp_centene <- oon_urls_centene |>
purrr::map_dfr(defog_oon)
#> Error in eval(expr, envir, enclos): object 'oon_urls_centene' not found
oon_resp_centene <- oon_resp_centene |>
filter(!is.na(billing_code_type))
#> Error in eval(expr, envir, enclos): object 'oon_resp_centene' not found#> Error in eval(expr, envir, enclos): object 'oon_resp_centene' not foundBuild the In-Network Table
inn_urls_centene <- toc_resp_centene |>
dplyr::filter(type == "in_network") |>
dplyr::select(location) |>
dplyr::distinct() |>
tibble::deframe()
#> Error in eval(expr, envir, enclos): object 'toc_resp_centene' not found#> Error in eval(expr, envir, enclos): object 'inn_urls_centene' not found
inn_resp_cent.2 <- inn_urls_centene[2] |>
purrr::map_dfr(defog_inn)
#> Error in eval(expr, envir, enclos): object 'inn_urls_centene' not found
inn_resp_cent.2
#> Error in eval(expr, envir, enclos): object 'inn_resp_cent.2' not foundCreating a Relational Database with dm
You can build a relational database in R with the {dm} package, using only data frames in your R environment. Once your data model is complete, you can deploy it to a range of database management systems (DBMS). First, we need to create a dm object:
dm_centene_no_keys <- dm::dm(toc_url_centene, toc_resp_centene)
#> Error in `map()`:
#> ℹ In index: 2.
#> Caused by error:
#> ! object 'toc_resp_centene' not found#> Error in eval(expr, envir, enclos): object 'dm_centene_no_keys' not foundNext, we need to define the primary keys:
dm::dm_enum_pk_candidates(dm = dm_centene_no_keys, table = toc_url_centene)
#> Error in eval(expr, envir, enclos): object 'dm_centene_no_keys' not found
dm_centene_only_pks <- dm_centene_no_keys |> dm::dm_add_pk(table = toc_url_centene, columns = origin)
#> Error in eval(expr, envir, enclos): object 'dm_centene_no_keys' not found#> Error in eval(expr, envir, enclos): object 'dm_centene_only_pks' not foundNow, we need to link the two tables by a foreign key:
dm::dm_enum_fk_candidates(dm = dm_centene_only_pks, table = toc_resp_centene, ref_table = toc_url_centene)
#> Error in eval(expr, envir, enclos): object 'dm_centene_only_pks' not found
dm_centene_all_keys <- dm_centene_only_pks |> dm::dm_add_fk(table = toc_resp_centene, columns = origin, ref_table = toc_url_centene)
#> Error in eval(expr, envir, enclos): object 'dm_centene_only_pks' not found#> Error in eval(expr, envir, enclos): object 'dm_centene_all_keys' not foundVisualizing the Data Model
dm_centene_no_keys |> dm::dm_draw(rankdir = "TB", view_type = "all")
#> Error in eval(expr, envir, enclos): object 'dm_centene_no_keys' not found
dm_centene_only_pks |> dm::dm_draw(rankdir = "TB", view_type = "all")
#> Error in eval(expr, envir, enclos): object 'dm_centene_only_pks' not found
dm_centene_all_keys |> dm::dm_draw(rankdir = "LR", view_type = "all")
#> Error in eval(expr, envir, enclos): object 'dm_centene_all_keys' not found