Massive Data Analysis with Sparklyr – Healthcare Marketplace (2024)
Federico Sangineto Cotrina, Bayza Woldemariam
2026-01-07
Load libraries and connect to Spark
library(sparklyr)##
## Attaching package: 'sparklyr'## The following object is masked from 'package:stats':
##
## filterlibrary(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary(DBI)
library(ggplot2)
library(stringr)
library(scales) # for percent formatting
sc <- spark_connect(master = "local")Exercise 1
Study structure and variables
Load Plan_Attributes.csv
File_Name_path <- paste0("file:///", getwd(), "/plan-attributes-puf.csv")
dfPlanAttrib <- spark_read_csv(
sc,
name = "PlanAttributes",
path = File_Name_path,
infer_schema = TRUE,
memory = TRUE
)Check structure
str(dfPlanAttrib)## List of 6
## $ src :List of 1
## ..$ con:List of 13
## .. ..$ master : chr "local[8]"
## .. ..$ method : chr "shell"
## .. ..$ app_name : chr "sparklyr"
## .. ..$ config :List of 7
## .. .. ..$ spark.env.SPARK_LOCAL_IP.local : chr "127.0.0.1"
## .. .. ..$ sparklyr.connect.csv.embedded : chr "^1.*"
## .. .. ..$ spark.sql.legacy.utcTimestampFunc.enabled: logi TRUE
## .. .. ..$ sparklyr.connect.cores.local : int 8
## .. .. ..$ spark.sql.shuffle.partitions.local : int 8
## .. .. ..$ sparklyr.shell.name : chr "sparklyr"
## .. .. ..$ sparklyr.shell.driver-memory : chr "2g"
## .. .. ..- attr(*, "config")= chr "default"
## .. .. ..- attr(*, "file")= chr "/Library/Frameworks/R.framework/Versions/4.5-arm64/Resources/library/sparklyr/conf/config-template.yml"
## .. .. ..- attr(*, "class")= chr "config"
## .. ..$ state :<environment: 0x122632eb0>
## .. ..$ extensions :List of 6
## .. .. ..$ jars : chr(0)
## .. .. ..$ packages : chr(0)
## .. .. ..$ initializers : list()
## .. .. ..$ catalog_jars : chr(0)
## .. .. ..$ repositories : chr(0)
## .. .. ..$ dbplyr_sql_variant:List of 3
## .. .. .. ..$ scalar : list()
## .. .. .. ..$ aggregate: list()
## .. .. .. ..$ window : list()
## .. ..$ spark_home : chr "/Users/bayzawoldemariam/spark/spark-3.5.0-bin-hadoop3"
## .. ..$ backend : 'sockconn' int 5
## .. .. ..- attr(*, "conn_id")=<externalptr>
## .. ..$ monitoring : 'sockconn' int 6
## .. .. ..- attr(*, "conn_id")=<externalptr>
## .. ..$ gateway : 'sockconn' int 4
## .. .. ..- attr(*, "conn_id")=<externalptr>
## .. ..$ output_file : chr "/var/folders/qm/rw5r8wj54xv6_jnznc83t3mm0000gn/T//Rtmpz61c6j/file115dd3bb0b2c_spark.log"
## .. ..$ sessionId : num 95650
## .. ..$ home_version: chr "3.5.0"
## .. ..- attr(*, "class")= chr [1:3] "spark_connection" "spark_shell_connection" "DBIConnection"
## ..- attr(*, "class")= chr [1:3] "src_spark" "src_sql" "src"
## $ lazy_query :List of 5
## ..$ x : 'dbplyr_table_path' chr "`PlanAttributes`"
## ..$ vars : chr [1:151] "BusinessYear" "StateCode" "IssuerId" "IssuerMarketPlaceMarketingName" ...
## ..$ group_vars: chr(0)
## ..$ order_vars: NULL
## ..$ frame : NULL
## ..- attr(*, "class")= chr [1:3] "lazy_base_remote_query" "lazy_base_query" "lazy_query"
## $ sdf_cache_state :<environment: 0x114719a20>
## $ spark_dataframe :function (self, spark_dataframe_impl)
## $ schema_cache_state:<environment: 0x11471aa60>
## $ schema :function (self, schema_impl, expand_nested_cols, expand_struct_cols)
## - attr(*, "class")= chr [1:4] "tbl_spark" "tbl_sql" "tbl_lazy" "tbl"glimpse(dfPlanAttrib)## Rows: ??
## Columns: 151
## Database: spark_connection
## $ BusinessYear <int> 2024, 202…
## $ StateCode <chr> "AK", "AK…
## $ IssuerId <int> 21989, 21…
## $ IssuerMarketPlaceMarketingName <chr> "Delta De…
## $ SourceName <chr> "HIOS", "…
## $ ImportDate <dttm> 2023-12-…
## $ MarketCoverage <chr> "SHOP (Sm…
## $ DentalOnlyPlan <chr> "Yes", "Y…
## $ StandardComponentId <chr> "21989AK0…
## $ PlanMarketingName <chr> "Delta De…
## $ HIOSProductId <chr> "21989AK0…
## $ NetworkId <chr> "AKN001",…
## $ ServiceAreaId <chr> "AKS001",…
## $ FormularyId <chr> NA, NA, N…
## $ IsNewPlan <chr> "Existing…
## $ PlanType <chr> "Indemnit…
## $ MetalLevel <chr> "High", "…
## $ DesignType <chr> NA, NA, N…
## $ UniquePlanDesign <chr> NA, NA, N…
## $ QHPNonQHPTypeId <chr> "Off the …
## $ IsNoticeRequiredForPregnancy <chr> NA, NA, N…
## $ IsReferralRequiredForSpecialist <chr> NA, NA, N…
## $ SpecialistRequiringReferral <chr> NA, NA, N…
## $ PlanLevelExclusions <chr> NA, NA, N…
## $ IndianPlanVariationEstimatedAdvancedPaymentAmountPerEnrollee <chr> NA, NA, N…
## $ CompositeRatingOffered <chr> "No", "No…
## $ ChildOnlyOffering <chr> "Allows A…
## $ ChildOnlyPlanId <chr> NA, NA, N…
## $ WellnessProgramOffered <chr> NA, NA, N…
## $ DiseaseManagementProgramsOffered <chr> NA, NA, N…
## $ EHBPercentTotalPremium <dbl> NA, NA, N…
## $ EHBPediatricDentalApportionmentQuantity <dbl> NA, 1, 1,…
## $ IsGuaranteedRate <chr> "Guarante…
## $ PlanEffectiveDate <date> 2024-01-…
## $ PlanExpirationDate <date> 2024-12-…
## $ OutOfCountryCoverage <chr> "Yes", "Y…
## $ OutOfCountryCoverageDescription <chr> "Provider…
## $ OutOfServiceAreaCoverage <chr> "Yes", "Y…
## $ OutOfServiceAreaCoverageDescription <chr> "National…
## $ NationalNetwork <chr> "Yes", "Y…
## $ URLForEnrollmentPayment <chr> NA, "http…
## $ FormularyURL <chr> NA, NA, N…
## $ PlanId <chr> "21989AK0…
## $ PlanVariantMarketingName <chr> "Delta De…
## $ CSRVariationType <chr> "Standard…
## $ IssuerActuarialValue <chr> NA, NA, N…
## $ AVCalculatorOutputNumber <dbl> NA, NA, N…
## $ MedicalDrugDeductiblesIntegrated <chr> NA, NA, N…
## $ MedicalDrugMaximumOutofPocketIntegrated <chr> NA, NA, N…
## $ MultipleInNetworkTiers <chr> "No", "No…
## $ FirstTierUtilization <chr> "100%", "…
## $ SecondTierUtilization <chr> NA, NA, N…
## $ SBCHavingaBabyDeductible <chr> NA, NA, N…
## $ SBCHavingaBabyCopayment <chr> NA, NA, N…
## $ SBCHavingaBabyCoinsurance <chr> NA, NA, N…
## $ SBCHavingaBabyLimit <chr> NA, NA, N…
## $ SBCHavingDiabetesDeductible <chr> NA, NA, N…
## $ SBCHavingDiabetesCopayment <chr> NA, NA, N…
## $ SBCHavingDiabetesCoinsurance <chr> NA, NA, N…
## $ SBCHavingDiabetesLimit <chr> NA, NA, N…
## $ SBCHavingSimplefractureDeductible <chr> NA, NA, N…
## $ SBCHavingSimplefractureCopayment <chr> NA, NA, N…
## $ SBCHavingSimplefractureCoinsurance <chr> NA, NA, N…
## $ SBCHavingSimplefractureLimit <chr> NA, NA, N…
## $ SpecialtyDrugMaximumCoinsurance <chr> NA, NA, N…
## $ InpatientCopaymentMaximumDays <int> 0, 0, 0, …
## $ BeginPrimaryCareCostSharingAfterNumberOfVisits <int> 0, 0, 0, …
## $ BeginPrimaryCareDeductibleCoinsuranceAfterNumberOfCopays <int> 0, 0, 0, …
## $ MEHBInnTier1IndividualMOOP <chr> "Not Appl…
## $ MEHBInnTier1FamilyPerPersonMOOP <chr> "per pers…
## $ MEHBInnTier1FamilyPerGroupMOOP <chr> "per grou…
## $ MEHBInnTier2IndividualMOOP <chr> NA, NA, N…
## $ MEHBInnTier2FamilyPerPersonMOOP <chr> NA, NA, N…
## $ MEHBInnTier2FamilyPerGroupMOOP <chr> NA, NA, N…
## $ MEHBOutOfNetIndividualMOOP <chr> "Not Appl…
## $ MEHBOutOfNetFamilyPerPersonMOOP <chr> "per pers…
## $ MEHBOutOfNetFamilyPerGroupMOOP <chr> "per grou…
## $ MEHBCombInnOonIndividualMOOP <chr> "$400", "…
## $ MEHBCombInnOonFamilyPerPersonMOOP <chr> "$400 per…
## $ MEHBCombInnOonFamilyPerGroupMOOP <chr> "$800 per…
## $ DEHBInnTier1IndividualMOOP <chr> NA, NA, N…
## $ DEHBInnTier1FamilyPerPersonMOOP <chr> NA, NA, N…
## $ DEHBInnTier1FamilyPerGroupMOOP <chr> NA, NA, N…
## $ DEHBInnTier2IndividualMOOP <chr> NA, NA, N…
## $ DEHBInnTier2FamilyPerPersonMOOP <chr> NA, NA, N…
## $ DEHBInnTier2FamilyPerGroupMOOP <chr> NA, NA, N…
## $ DEHBOutOfNetIndividualMOOP <chr> NA, NA, N…
## $ DEHBOutOfNetFamilyPerPersonMOOP <chr> NA, NA, N…
## $ DEHBOutOfNetFamilyPerGroupMOOP <chr> NA, NA, N…
## $ DEHBCombInnOonIndividualMOOP <chr> NA, NA, N…
## $ DEHBCombInnOonFamilyPerPersonMOOP <chr> NA, NA, N…
## $ DEHBCombInnOonFamilyPerGroupMOOP <chr> NA, NA, N…
## $ TEHBInnTier1IndividualMOOP <chr> NA, NA, N…
## $ TEHBInnTier1FamilyPerPersonMOOP <chr> NA, NA, N…
## $ TEHBInnTier1FamilyPerGroupMOOP <chr> NA, NA, N…
## $ TEHBInnTier2IndividualMOOP <chr> NA, NA, N…
## $ TEHBInnTier2FamilyPerPersonMOOP <chr> NA, NA, N…
## $ TEHBInnTier2FamilyPerGroupMOOP <chr> NA, NA, N…
## $ TEHBOutOfNetIndividualMOOP <chr> NA, NA, N…
## $ TEHBOutOfNetFamilyPerPersonMOOP <chr> NA, NA, N…
## $ TEHBOutOfNetFamilyPerGroupMOOP <chr> NA, NA, N…
## $ TEHBCombInnOonIndividualMOOP <chr> NA, NA, N…
## $ TEHBCombInnOonFamilyPerPersonMOOP <chr> NA, NA, N…
## $ TEHBCombInnOonFamilyPerGroupMOOP <chr> NA, NA, N…
## $ MEHBDedInnTier1Individual <chr> "Not Appl…
## $ MEHBDedInnTier1FamilyPerPerson <chr> "per pers…
## $ MEHBDedInnTier1FamilyPerGroup <chr> "per grou…
## $ MEHBDedInnTier1Coinsurance <chr> NA, NA, N…
## $ MEHBDedInnTier2Individual <chr> NA, NA, N…
## $ MEHBDedInnTier2FamilyPerPerson <chr> NA, NA, N…
## $ MEHBDedInnTier2FamilyPerGroup <chr> NA, NA, N…
## $ MEHBDedInnTier2Coinsurance <chr> NA, NA, N…
## $ MEHBDedOutOfNetIndividual <chr> "Not Appl…
## $ MEHBDedOutOfNetFamilyPerPerson <chr> "per pers…
## $ MEHBDedOutOfNetFamilyPerGroup <chr> "per grou…
## $ MEHBDedCombInnOonIndividual <chr> "$50", "$…
## $ MEHBDedCombInnOonFamilyPerPerson <chr> "$50 per …
## $ MEHBDedCombInnOonFamilyPerGroup <chr> "$150 per…
## $ DEHBDedInnTier1Individual <chr> NA, NA, N…
## $ DEHBDedInnTier1FamilyPerPerson <chr> NA, NA, N…
## $ DEHBDedInnTier1FamilyPerGroup <chr> NA, NA, N…
## $ DEHBDedInnTier1Coinsurance <chr> NA, NA, N…
## $ DEHBDedInnTier2Individual <chr> NA, NA, N…
## $ DEHBDedInnTier2FamilyPerPerson <chr> NA, NA, N…
## $ DEHBDedInnTier2FamilyPerGroup <chr> NA, NA, N…
## $ DEHBDedInnTier2Coinsurance <chr> NA, NA, N…
## $ DEHBDedOutOfNetIndividual <chr> NA, NA, N…
## $ DEHBDedOutOfNetFamilyPerPerson <chr> NA, NA, N…
## $ DEHBDedOutOfNetFamilyPerGroup <chr> NA, NA, N…
## $ DEHBDedCombInnOonIndividual <chr> NA, NA, N…
## $ DEHBDedCombInnOonFamilyPerPerson <chr> NA, NA, N…
## $ DEHBDedCombInnOonFamilyPerGroup <chr> NA, NA, N…
## $ TEHBDedInnTier1Individual <chr> NA, NA, N…
## $ TEHBDedInnTier1FamilyPerPerson <chr> NA, NA, N…
## $ TEHBDedInnTier1FamilyPerGroup <chr> NA, NA, N…
## $ TEHBDedInnTier1Coinsurance <chr> NA, NA, N…
## $ TEHBDedInnTier2Individual <chr> NA, NA, N…
## $ TEHBDedInnTier2FamilyPerPerson <chr> NA, NA, N…
## $ TEHBDedInnTier2FamilyPerGroup <chr> NA, NA, N…
## $ TEHBDedInnTier2Coinsurance <chr> NA, NA, N…
## $ TEHBDedOutOfNetIndividual <chr> NA, NA, N…
## $ TEHBDedOutOfNetFamilyPerPerson <chr> NA, NA, N…
## $ TEHBDedOutOfNetFamilyPerGroup <chr> NA, NA, N…
## $ TEHBDedCombInnOonIndividual <chr> NA, NA, N…
## $ TEHBDedCombInnOonFamilyPerPerson <chr> NA, NA, N…
## $ TEHBDedCombInnOonFamilyPerGroup <chr> NA, NA, N…
## $ IsHSAEligible <chr> NA, NA, N…
## $ HSAOrHRAEmployerContribution <chr> NA, NA, N…
## $ HSAOrHRAEmployerContributionAmount <chr> NA, NA, N…
## $ URLForSummaryofBenefitsCoverage <chr> NA, NA, N…
## $ PlanBrochure <chr> NA, "http…Explanation of structure functions
str()shows a compact summary: number of observations, variables, and their typesglimpse()prints a vertical view of each variable and previews some values, which is easier to read for large datasets.
Summary of Dataset
dfPlanAttrib %>%
summarise(
n_rows = n(),
n_cols = local(ncol(dfPlanAttrib)) # evaluate in R
) %>%
collect()## # A tibble: 1 × 2
## n_rows n_cols
## <dbl> <dbl>
## 1 27374 151PlanAttributes contains 27,374 rows and 151 columns, including categorical variables (PlanType, MetalLevel) and numeric variables (DeductibleIndividual, MaxOOP).
Check missing values per column
dfPlanAttrib %>%
summarise_all(~sum(as.integer(is.na(.)))) %>%
collect()## Warning: Missing values are always removed in SQL aggregation functions.
## Use `na.rm = TRUE` to silence this warning
## This warning is displayed once every 8 hours.## # A tibble: 1 × 151
## BusinessYear StateCode IssuerId IssuerMarketPlaceMarke…¹ SourceName ImportDate
## <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 0 0 0 0 0 0
## # ℹ abbreviated name: ¹IssuerMarketPlaceMarketingName
## # ℹ 145 more variables: MarketCoverage <dbl>, DentalOnlyPlan <dbl>,
## # StandardComponentId <dbl>, PlanMarketingName <dbl>, HIOSProductId <dbl>,
## # NetworkId <dbl>, ServiceAreaId <dbl>, FormularyId <dbl>, IsNewPlan <dbl>,
## # PlanType <dbl>, MetalLevel <dbl>, DesignType <dbl>, UniquePlanDesign <dbl>,
## # QHPNonQHPTypeId <dbl>, IsNoticeRequiredForPregnancy <dbl>,
## # IsReferralRequiredForSpecialist <dbl>, SpecialistRequiringReferral <dbl>, …Missing value check helps identify columns that might require cleaning before analysis.
Exercise 2
SQL queries using DBI
View 10 rows using SQL
dbGetQuery(
sc,
"SELECT *
FROM PlanAttributes
LIMIT 10"
)## BusinessYear StateCode IssuerId IssuerMarketPlaceMarketingName SourceName
## 1 2024 AK 21989 Delta Dental of Alaska HIOS
## 2 2024 AK 21989 Delta Dental of Alaska HIOS
## 3 2024 AK 21989 Delta Dental of Alaska HIOS
## 4 2024 AK 21989 Delta Dental of Alaska HIOS
## 5 2024 AK 21989 Delta Dental of Alaska HIOS
## 6 2024 AK 21989 Delta Dental of Alaska HIOS
## 7 2024 AK 21989 Delta Dental of Alaska HIOS
## 8 2024 AK 21989 Delta Dental of Alaska HIOS
## 9 2024 AK 21989 Delta Dental of Alaska HIOS
## 10 2024 AK 21989 Delta Dental of Alaska HIOS
## ImportDate MarketCoverage DentalOnlyPlan StandardComponentId
## 1 2023-12-08 01:02:13 SHOP (Small Group) Yes 21989AK0080005
## 2 2023-12-08 01:02:13 Individual Yes 21989AK0030001
## 3 2023-12-08 01:02:13 Individual Yes 21989AK0030001
## 4 2023-12-08 01:02:13 SHOP (Small Group) Yes 21989AK0080001
## 5 2023-12-08 01:02:13 SHOP (Small Group) Yes 21989AK0080004
## 6 2023-12-08 01:02:13 Individual Yes 21989AK0050001
## 7 2023-12-08 01:02:13 Individual Yes 21989AK0050001
## 8 2023-12-08 01:02:13 SHOP (Small Group) Yes 21989AK0080002
## 9 2023-12-08 01:02:13 SHOP (Small Group) Yes 21989AK0130001
## 10 2023-12-08 01:02:13 Individual Yes 21989AK0050002
## PlanMarketingName HIOSProductId NetworkId
## 1 Delta Dental Premier, +1000, 100/80/50, 50, PF 21989AK008 AKN001
## 2 Delta Dental Premier Plan 21989AK003 AKN003
## 3 Delta Dental Premier Plan 21989AK003 AKN003
## 4 Delta Dental Premier 1000, 100/80/50, 50 21989AK008 AKN001
## 5 Delta Dental Premier, +1500, 100/80/50, 50, PF 21989AK008 AKN001
## 6 Delta Dental PPO 1000 Plan 21989AK005 AKN004
## 7 Delta Dental PPO 1000 Plan 21989AK005 AKN004
## 8 Delta Dental Premier 1500, 100/80/50, 50 21989AK008 AKN001
## 9 Delta Dental Premier, +2000, 100/80/50, 50, PF 21989AK013 AKN001
## 10 Delta Dental PPO 1500 Plan 21989AK005 AKN004
## ServiceAreaId FormularyId IsNewPlan PlanType MetalLevel DesignType
## 1 AKS001 <NA> Existing Indemnity High <NA>
## 2 AKS003 <NA> Existing Indemnity Low <NA>
## 3 AKS003 <NA> Existing Indemnity Low <NA>
## 4 AKS001 <NA> Existing Indemnity High <NA>
## 5 AKS001 <NA> Existing Indemnity High <NA>
## 6 AKS004 <NA> Existing PPO High <NA>
## 7 AKS004 <NA> Existing PPO High <NA>
## 8 AKS001 <NA> Existing Indemnity High <NA>
## 9 AKS001 <NA> Existing Indemnity High <NA>
## 10 AKS004 <NA> Existing PPO High <NA>
## UniquePlanDesign QHPNonQHPTypeId IsNoticeRequiredForPregnancy
## 1 <NA> Off the Exchange <NA>
## 2 <NA> Both <NA>
## 3 <NA> Both <NA>
## 4 <NA> Off the Exchange <NA>
## 5 <NA> Off the Exchange <NA>
## 6 <NA> Both <NA>
## 7 <NA> Both <NA>
## 8 <NA> Off the Exchange <NA>
## 9 <NA> Off the Exchange <NA>
## 10 <NA> Both <NA>
## IsReferralRequiredForSpecialist SpecialistRequiringReferral
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## PlanLevelExclusions
## 1 <NA>
## 2 <NA>
## 3 <NA>
## 4 <NA>
## 5 <NA>
## 6 <NA>
## 7 <NA>
## 8 <NA>
## 9 <NA>
## 10 <NA>
## IndianPlanVariationEstimatedAdvancedPaymentAmountPerEnrollee
## 1 <NA>
## 2 <NA>
## 3 <NA>
## 4 <NA>
## 5 <NA>
## 6 <NA>
## 7 <NA>
## 8 <NA>
## 9 <NA>
## 10 <NA>
## CompositeRatingOffered ChildOnlyOffering ChildOnlyPlanId
## 1 No Allows Adult and Child-Only <NA>
## 2 No Allows Adult and Child-Only <NA>
## 3 No Allows Adult and Child-Only <NA>
## 4 No Allows Adult and Child-Only <NA>
## 5 No Allows Adult and Child-Only <NA>
## 6 No Allows Adult and Child-Only <NA>
## 7 No Allows Adult and Child-Only <NA>
## 8 No Allows Adult and Child-Only <NA>
## 9 No Allows Adult and Child-Only <NA>
## 10 No Allows Adult and Child-Only <NA>
## WellnessProgramOffered DiseaseManagementProgramsOffered
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## EHBPercentTotalPremium EHBPediatricDentalApportionmentQuantity
## 1 NA NA
## 2 NA 1
## 3 NA 1
## 4 NA NA
## 5 NA NA
## 6 NA 1
## 7 NA 1
## 8 NA NA
## 9 NA NA
## 10 NA 1
## IsGuaranteedRate PlanEffectiveDate PlanExpirationDate OutOfCountryCoverage
## 1 Guaranteed Rate 2024-01-01 2024-12-31 Yes
## 2 Guaranteed Rate 2024-01-01 2024-12-31 Yes
## 3 Guaranteed Rate 2024-01-01 2024-12-31 Yes
## 4 Guaranteed Rate 2024-01-01 2024-12-31 Yes
## 5 Guaranteed Rate 2024-01-01 2024-12-31 Yes
## 6 Guaranteed Rate 2024-01-01 2024-12-31 Yes
## 7 Guaranteed Rate 2024-01-01 2024-12-31 Yes
## 8 Guaranteed Rate 2024-01-01 2024-12-31 Yes
## 9 Guaranteed Rate 2024-01-01 2024-12-31 Yes
## 10 Guaranteed Rate 2024-01-01 2024-12-31 Yes
## OutOfCountryCoverageDescription OutOfServiceAreaCoverage
## 1 Providers treated as out-of-network. Yes
## 2 Non-participating provider benefits Yes
## 3 Non-participating provider benefits Yes
## 4 Providers treated as out-of-network. Yes
## 5 Treated as out-of-network Yes
## 6 Out-of-network benefits Yes
## 7 Out-of-network benefits Yes
## 8 Treated as out-of-network Yes
## 9 Providers treated as out-of-network. Yes
## 10 Out-of-network benefits Yes
## OutOfServiceAreaCoverageDescription NationalNetwork
## 1 National Network Yes
## 2 Non-participating provider benefits Yes
## 3 Non-participating provider benefits Yes
## 4 National Network Yes
## 5 National network Yes
## 6 Out-of-network benefits Yes
## 7 Out-of-network benefits Yes
## 8 National network Yes
## 9 National Network Yes
## 10 Out-of-network benefits Yes
## URLForEnrollmentPayment
## 1 <NA>
## 2 https://www.deltadentalor.com/member/online-tools/member-dashboard
## 3 https://www.deltadentalor.com/member/online-tools/member-dashboard
## 4 <NA>
## 5 <NA>
## 6 https://www.deltadentalor.com/member/online-tools/member-dashboard
## 7 https://www.deltadentalor.com/member/online-tools/member-dashboard
## 8 <NA>
## 9 <NA>
## 10 https://www.deltadentalor.com/member/online-tools/member-dashboard
## FormularyURL PlanId
## 1 <NA> 21989AK0080005-00
## 2 <NA> 21989AK0030001-00
## 3 <NA> 21989AK0030001-01
## 4 <NA> 21989AK0080001-00
## 5 <NA> 21989AK0080004-00
## 6 <NA> 21989AK0050001-00
## 7 <NA> 21989AK0050001-01
## 8 <NA> 21989AK0080002-00
## 9 <NA> 21989AK0130001-00
## 10 <NA> 21989AK0050002-00
## PlanVariantMarketingName
## 1 Delta Dental Premier, +1000, 100/80/50, 50, PF
## 2 Delta Dental Premier Plan
## 3 Delta Dental Premier Plan
## 4 Delta Dental Premier 1000, 100/80/50, 50
## 5 Delta Dental Premier, +1500, 100/80/50, 50, PF
## 6 Delta Dental PPO 1000 Plan
## 7 Delta Dental PPO 1000 Plan
## 8 Delta Dental Premier 1500, 100/80/50, 50
## 9 Delta Dental Premier, +2000, 100/80/50, 50, PF
## 10 Delta Dental PPO 1500 Plan
## CSRVariationType IssuerActuarialValue
## 1 Standard High Off Exchange Plan <NA>
## 2 Standard Low Off Exchange Plan <NA>
## 3 Standard Low On Exchange Plan <NA>
## 4 Standard High Off Exchange Plan <NA>
## 5 Standard High Off Exchange Plan <NA>
## 6 Standard High Off Exchange Plan <NA>
## 7 Standard High On Exchange Plan <NA>
## 8 Standard High Off Exchange Plan <NA>
## 9 Standard High Off Exchange Plan <NA>
## 10 Standard High Off Exchange Plan <NA>
## AVCalculatorOutputNumber MedicalDrugDeductiblesIntegrated
## 1 NA <NA>
## 2 NA <NA>
## 3 NA <NA>
## 4 NA <NA>
## 5 NA <NA>
## 6 NA <NA>
## 7 NA <NA>
## 8 NA <NA>
## 9 NA <NA>
## 10 NA <NA>
## MedicalDrugMaximumOutofPocketIntegrated MultipleInNetworkTiers
## 1 <NA> No
## 2 <NA> No
## 3 <NA> No
## 4 <NA> No
## 5 <NA> No
## 6 <NA> No
## 7 <NA> No
## 8 <NA> No
## 9 <NA> No
## 10 <NA> No
## FirstTierUtilization SecondTierUtilization SBCHavingaBabyDeductible
## 1 100% <NA> <NA>
## 2 100% <NA> <NA>
## 3 100% <NA> <NA>
## 4 100% <NA> <NA>
## 5 100% <NA> <NA>
## 6 100% <NA> <NA>
## 7 100% <NA> <NA>
## 8 100% <NA> <NA>
## 9 100% <NA> <NA>
## 10 100% <NA> <NA>
## SBCHavingaBabyCopayment SBCHavingaBabyCoinsurance SBCHavingaBabyLimit
## 1 <NA> <NA> <NA>
## 2 <NA> <NA> <NA>
## 3 <NA> <NA> <NA>
## 4 <NA> <NA> <NA>
## 5 <NA> <NA> <NA>
## 6 <NA> <NA> <NA>
## 7 <NA> <NA> <NA>
## 8 <NA> <NA> <NA>
## 9 <NA> <NA> <NA>
## 10 <NA> <NA> <NA>
## SBCHavingDiabetesDeductible SBCHavingDiabetesCopayment
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## SBCHavingDiabetesCoinsurance SBCHavingDiabetesLimit
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## SBCHavingSimplefractureDeductible SBCHavingSimplefractureCopayment
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## SBCHavingSimplefractureCoinsurance SBCHavingSimplefractureLimit
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## SpecialtyDrugMaximumCoinsurance InpatientCopaymentMaximumDays
## 1 <NA> 0
## 2 <NA> 0
## 3 <NA> 0
## 4 <NA> 0
## 5 <NA> 0
## 6 <NA> 0
## 7 <NA> 0
## 8 <NA> 0
## 9 <NA> 0
## 10 <NA> 0
## BeginPrimaryCareCostSharingAfterNumberOfVisits
## 1 0
## 2 0
## 3 0
## 4 0
## 5 0
## 6 0
## 7 0
## 8 0
## 9 0
## 10 0
## BeginPrimaryCareDeductibleCoinsuranceAfterNumberOfCopays
## 1 0
## 2 0
## 3 0
## 4 0
## 5 0
## 6 0
## 7 0
## 8 0
## 9 0
## 10 0
## MEHBInnTier1IndividualMOOP MEHBInnTier1FamilyPerPersonMOOP
## 1 Not Applicable per person not applicable
## 2 Not Applicable per person not applicable
## 3 Not Applicable per person not applicable
## 4 Not Applicable per person not applicable
## 5 Not Applicable per person not applicable
## 6 $400 $400 per person
## 7 $400 $400 per person
## 8 Not Applicable per person not applicable
## 9 Not Applicable per person not applicable
## 10 $400 $400 per person
## MEHBInnTier1FamilyPerGroupMOOP MEHBInnTier2IndividualMOOP
## 1 per group not applicable <NA>
## 2 per group not applicable <NA>
## 3 per group not applicable <NA>
## 4 per group not applicable <NA>
## 5 per group not applicable <NA>
## 6 $800 per group <NA>
## 7 $800 per group <NA>
## 8 per group not applicable <NA>
## 9 per group not applicable <NA>
## 10 $800 per group <NA>
## MEHBInnTier2FamilyPerPersonMOOP MEHBInnTier2FamilyPerGroupMOOP
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## MEHBOutOfNetIndividualMOOP MEHBOutOfNetFamilyPerPersonMOOP
## 1 Not Applicable per person not applicable
## 2 Not Applicable per person not applicable
## 3 Not Applicable per person not applicable
## 4 Not Applicable per person not applicable
## 5 Not Applicable per person not applicable
## 6 Not Applicable per person not applicable
## 7 Not Applicable per person not applicable
## 8 Not Applicable per person not applicable
## 9 Not Applicable per person not applicable
## 10 Not Applicable per person not applicable
## MEHBOutOfNetFamilyPerGroupMOOP MEHBCombInnOonIndividualMOOP
## 1 per group not applicable $400
## 2 per group not applicable $400
## 3 per group not applicable $400
## 4 per group not applicable $400
## 5 per group not applicable $400
## 6 per group not applicable Not Applicable
## 7 per group not applicable Not Applicable
## 8 per group not applicable $400
## 9 per group not applicable $400
## 10 per group not applicable Not Applicable
## MEHBCombInnOonFamilyPerPersonMOOP MEHBCombInnOonFamilyPerGroupMOOP
## 1 $400 per person $800 per group
## 2 $400 per person $800 per group
## 3 $400 per person $800 per group
## 4 $400 per person $800 per group
## 5 $400 per person $800 per group
## 6 per person not applicable per group not applicable
## 7 per person not applicable per group not applicable
## 8 $400 per person $800 per group
## 9 $400 per person $800 per group
## 10 per person not applicable per group not applicable
## DEHBInnTier1IndividualMOOP DEHBInnTier1FamilyPerPersonMOOP
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## DEHBInnTier1FamilyPerGroupMOOP DEHBInnTier2IndividualMOOP
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## DEHBInnTier2FamilyPerPersonMOOP DEHBInnTier2FamilyPerGroupMOOP
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## DEHBOutOfNetIndividualMOOP DEHBOutOfNetFamilyPerPersonMOOP
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## DEHBOutOfNetFamilyPerGroupMOOP DEHBCombInnOonIndividualMOOP
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## DEHBCombInnOonFamilyPerPersonMOOP DEHBCombInnOonFamilyPerGroupMOOP
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## TEHBInnTier1IndividualMOOP TEHBInnTier1FamilyPerPersonMOOP
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## TEHBInnTier1FamilyPerGroupMOOP TEHBInnTier2IndividualMOOP
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## TEHBInnTier2FamilyPerPersonMOOP TEHBInnTier2FamilyPerGroupMOOP
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## TEHBOutOfNetIndividualMOOP TEHBOutOfNetFamilyPerPersonMOOP
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## TEHBOutOfNetFamilyPerGroupMOOP TEHBCombInnOonIndividualMOOP
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## TEHBCombInnOonFamilyPerPersonMOOP TEHBCombInnOonFamilyPerGroupMOOP
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## MEHBDedInnTier1Individual MEHBDedInnTier1FamilyPerPerson
## 1 Not Applicable per person not applicable
## 2 Not Applicable per person not applicable
## 3 Not Applicable per person not applicable
## 4 Not Applicable per person not applicable
## 5 Not Applicable per person not applicable
## 6 Not Applicable per person not applicable
## 7 Not Applicable per person not applicable
## 8 Not Applicable per person not applicable
## 9 Not Applicable per person not applicable
## 10 Not Applicable per person not applicable
## MEHBDedInnTier1FamilyPerGroup MEHBDedInnTier1Coinsurance
## 1 per group not applicable <NA>
## 2 per group not applicable <NA>
## 3 per group not applicable <NA>
## 4 per group not applicable <NA>
## 5 per group not applicable <NA>
## 6 per group not applicable <NA>
## 7 per group not applicable <NA>
## 8 per group not applicable <NA>
## 9 per group not applicable <NA>
## 10 per group not applicable <NA>
## MEHBDedInnTier2Individual MEHBDedInnTier2FamilyPerPerson
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## MEHBDedInnTier2FamilyPerGroup MEHBDedInnTier2Coinsurance
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## MEHBDedOutOfNetIndividual MEHBDedOutOfNetFamilyPerPerson
## 1 Not Applicable per person not applicable
## 2 Not Applicable per person not applicable
## 3 Not Applicable per person not applicable
## 4 Not Applicable per person not applicable
## 5 Not Applicable per person not applicable
## 6 Not Applicable per person not applicable
## 7 Not Applicable per person not applicable
## 8 Not Applicable per person not applicable
## 9 Not Applicable per person not applicable
## 10 Not Applicable per person not applicable
## MEHBDedOutOfNetFamilyPerGroup MEHBDedCombInnOonIndividual
## 1 per group not applicable $50
## 2 per group not applicable $0
## 3 per group not applicable $0
## 4 per group not applicable $50
## 5 per group not applicable $50
## 6 per group not applicable $50
## 7 per group not applicable $50
## 8 per group not applicable $50
## 9 per group not applicable $50
## 10 per group not applicable $50
## MEHBDedCombInnOonFamilyPerPerson MEHBDedCombInnOonFamilyPerGroup
## 1 $50 per person $150 per group
## 2 $0 per person $0 per group
## 3 $0 per person $0 per group
## 4 $50 per person $150 per group
## 5 $50 per person $150 per group
## 6 $50 per person $150 per group
## 7 $50 per person $150 per group
## 8 $50 per person $150 per group
## 9 $50 per person $150 per group
## 10 $50 per person $150 per group
## DEHBDedInnTier1Individual DEHBDedInnTier1FamilyPerPerson
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## DEHBDedInnTier1FamilyPerGroup DEHBDedInnTier1Coinsurance
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## DEHBDedInnTier2Individual DEHBDedInnTier2FamilyPerPerson
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## DEHBDedInnTier2FamilyPerGroup DEHBDedInnTier2Coinsurance
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## DEHBDedOutOfNetIndividual DEHBDedOutOfNetFamilyPerPerson
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## DEHBDedOutOfNetFamilyPerGroup DEHBDedCombInnOonIndividual
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## DEHBDedCombInnOonFamilyPerPerson DEHBDedCombInnOonFamilyPerGroup
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## TEHBDedInnTier1Individual TEHBDedInnTier1FamilyPerPerson
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## TEHBDedInnTier1FamilyPerGroup TEHBDedInnTier1Coinsurance
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## TEHBDedInnTier2Individual TEHBDedInnTier2FamilyPerPerson
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## TEHBDedInnTier2FamilyPerGroup TEHBDedInnTier2Coinsurance
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## TEHBDedOutOfNetIndividual TEHBDedOutOfNetFamilyPerPerson
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## TEHBDedOutOfNetFamilyPerGroup TEHBDedCombInnOonIndividual
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## TEHBDedCombInnOonFamilyPerPerson TEHBDedCombInnOonFamilyPerGroup
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## IsHSAEligible HSAOrHRAEmployerContribution
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## HSAOrHRAEmployerContributionAmount URLForSummaryofBenefitsCoverage
## 1 <NA> <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## PlanBrochure
## 1 <NA>
## 2 https://www.deltadentalak.com/-/media/deltadental/downloads/alaska/plans/2024/individual/Delta-Dental-Premier-Plan-SOB-2024-AK.pdf
## 3 https://www.deltadentalak.com/-/media/deltadental/downloads/alaska/plans/2024/individual/Delta-Dental-Premier-Plan-SOB-2024-AK.pdf
## 4 <NA>
## 5 <NA>
## 6 https://www.deltadentalak.com/-/media/deltadental/downloads/alaska/plans/2024/individual/Delta-Dental-PPO-1000-Plan-SOB-2024-AK.pdf
## 7 https://www.deltadentalak.com/-/media/deltadental/downloads/alaska/plans/2024/individual/Delta-Dental-PPO-1000-Plan-SOB-2024-AK.pdf
## 8 <NA>
## 9 <NA>
## 10 https://www.deltadentalak.com/-/media/deltadental/downloads/alaska/plans/2024/individual/Delta-Dental-PPO-1500-Plan-SOB-2024-AK.pdfThe DBI package allows SQL queries to be executed directly on Spark tables.
The dbGetQuery() function returns the result as an R data frame for inspection.
List column names
dbGetQuery(sc, "SHOW COLUMNS IN PlanAttributes")## col_name
## 1 BusinessYear
## 2 StateCode
## 3 IssuerId
## 4 IssuerMarketPlaceMarketingName
## 5 SourceName
## 6 ImportDate
## 7 MarketCoverage
## 8 DentalOnlyPlan
## 9 StandardComponentId
## 10 PlanMarketingName
## 11 HIOSProductId
## 12 NetworkId
## 13 ServiceAreaId
## 14 FormularyId
## 15 IsNewPlan
## 16 PlanType
## 17 MetalLevel
## 18 DesignType
## 19 UniquePlanDesign
## 20 QHPNonQHPTypeId
## 21 IsNoticeRequiredForPregnancy
## 22 IsReferralRequiredForSpecialist
## 23 SpecialistRequiringReferral
## 24 PlanLevelExclusions
## 25 IndianPlanVariationEstimatedAdvancedPaymentAmountPerEnrollee
## 26 CompositeRatingOffered
## 27 ChildOnlyOffering
## 28 ChildOnlyPlanId
## 29 WellnessProgramOffered
## 30 DiseaseManagementProgramsOffered
## 31 EHBPercentTotalPremium
## 32 EHBPediatricDentalApportionmentQuantity
## 33 IsGuaranteedRate
## 34 PlanEffectiveDate
## 35 PlanExpirationDate
## 36 OutOfCountryCoverage
## 37 OutOfCountryCoverageDescription
## 38 OutOfServiceAreaCoverage
## 39 OutOfServiceAreaCoverageDescription
## 40 NationalNetwork
## 41 URLForEnrollmentPayment
## 42 FormularyURL
## 43 PlanId
## 44 PlanVariantMarketingName
## 45 CSRVariationType
## 46 IssuerActuarialValue
## 47 AVCalculatorOutputNumber
## 48 MedicalDrugDeductiblesIntegrated
## 49 MedicalDrugMaximumOutofPocketIntegrated
## 50 MultipleInNetworkTiers
## 51 FirstTierUtilization
## 52 SecondTierUtilization
## 53 SBCHavingaBabyDeductible
## 54 SBCHavingaBabyCopayment
## 55 SBCHavingaBabyCoinsurance
## 56 SBCHavingaBabyLimit
## 57 SBCHavingDiabetesDeductible
## 58 SBCHavingDiabetesCopayment
## 59 SBCHavingDiabetesCoinsurance
## 60 SBCHavingDiabetesLimit
## 61 SBCHavingSimplefractureDeductible
## 62 SBCHavingSimplefractureCopayment
## 63 SBCHavingSimplefractureCoinsurance
## 64 SBCHavingSimplefractureLimit
## 65 SpecialtyDrugMaximumCoinsurance
## 66 InpatientCopaymentMaximumDays
## 67 BeginPrimaryCareCostSharingAfterNumberOfVisits
## 68 BeginPrimaryCareDeductibleCoinsuranceAfterNumberOfCopays
## 69 MEHBInnTier1IndividualMOOP
## 70 MEHBInnTier1FamilyPerPersonMOOP
## 71 MEHBInnTier1FamilyPerGroupMOOP
## 72 MEHBInnTier2IndividualMOOP
## 73 MEHBInnTier2FamilyPerPersonMOOP
## 74 MEHBInnTier2FamilyPerGroupMOOP
## 75 MEHBOutOfNetIndividualMOOP
## 76 MEHBOutOfNetFamilyPerPersonMOOP
## 77 MEHBOutOfNetFamilyPerGroupMOOP
## 78 MEHBCombInnOonIndividualMOOP
## 79 MEHBCombInnOonFamilyPerPersonMOOP
## 80 MEHBCombInnOonFamilyPerGroupMOOP
## 81 DEHBInnTier1IndividualMOOP
## 82 DEHBInnTier1FamilyPerPersonMOOP
## 83 DEHBInnTier1FamilyPerGroupMOOP
## 84 DEHBInnTier2IndividualMOOP
## 85 DEHBInnTier2FamilyPerPersonMOOP
## 86 DEHBInnTier2FamilyPerGroupMOOP
## 87 DEHBOutOfNetIndividualMOOP
## 88 DEHBOutOfNetFamilyPerPersonMOOP
## 89 DEHBOutOfNetFamilyPerGroupMOOP
## 90 DEHBCombInnOonIndividualMOOP
## 91 DEHBCombInnOonFamilyPerPersonMOOP
## 92 DEHBCombInnOonFamilyPerGroupMOOP
## 93 TEHBInnTier1IndividualMOOP
## 94 TEHBInnTier1FamilyPerPersonMOOP
## 95 TEHBInnTier1FamilyPerGroupMOOP
## 96 TEHBInnTier2IndividualMOOP
## 97 TEHBInnTier2FamilyPerPersonMOOP
## 98 TEHBInnTier2FamilyPerGroupMOOP
## 99 TEHBOutOfNetIndividualMOOP
## 100 TEHBOutOfNetFamilyPerPersonMOOP
## 101 TEHBOutOfNetFamilyPerGroupMOOP
## 102 TEHBCombInnOonIndividualMOOP
## 103 TEHBCombInnOonFamilyPerPersonMOOP
## 104 TEHBCombInnOonFamilyPerGroupMOOP
## 105 MEHBDedInnTier1Individual
## 106 MEHBDedInnTier1FamilyPerPerson
## 107 MEHBDedInnTier1FamilyPerGroup
## 108 MEHBDedInnTier1Coinsurance
## 109 MEHBDedInnTier2Individual
## 110 MEHBDedInnTier2FamilyPerPerson
## 111 MEHBDedInnTier2FamilyPerGroup
## 112 MEHBDedInnTier2Coinsurance
## 113 MEHBDedOutOfNetIndividual
## 114 MEHBDedOutOfNetFamilyPerPerson
## 115 MEHBDedOutOfNetFamilyPerGroup
## 116 MEHBDedCombInnOonIndividual
## 117 MEHBDedCombInnOonFamilyPerPerson
## 118 MEHBDedCombInnOonFamilyPerGroup
## 119 DEHBDedInnTier1Individual
## 120 DEHBDedInnTier1FamilyPerPerson
## 121 DEHBDedInnTier1FamilyPerGroup
## 122 DEHBDedInnTier1Coinsurance
## 123 DEHBDedInnTier2Individual
## 124 DEHBDedInnTier2FamilyPerPerson
## 125 DEHBDedInnTier2FamilyPerGroup
## 126 DEHBDedInnTier2Coinsurance
## 127 DEHBDedOutOfNetIndividual
## 128 DEHBDedOutOfNetFamilyPerPerson
## 129 DEHBDedOutOfNetFamilyPerGroup
## 130 DEHBDedCombInnOonIndividual
## 131 DEHBDedCombInnOonFamilyPerPerson
## 132 DEHBDedCombInnOonFamilyPerGroup
## 133 TEHBDedInnTier1Individual
## 134 TEHBDedInnTier1FamilyPerPerson
## 135 TEHBDedInnTier1FamilyPerGroup
## 136 TEHBDedInnTier1Coinsurance
## 137 TEHBDedInnTier2Individual
## 138 TEHBDedInnTier2FamilyPerPerson
## 139 TEHBDedInnTier2FamilyPerGroup
## 140 TEHBDedInnTier2Coinsurance
## 141 TEHBDedOutOfNetIndividual
## 142 TEHBDedOutOfNetFamilyPerPerson
## 143 TEHBDedOutOfNetFamilyPerGroup
## 144 TEHBDedCombInnOonIndividual
## 145 TEHBDedCombInnOonFamilyPerPerson
## 146 TEHBDedCombInnOonFamilyPerGroup
## 147 IsHSAEligible
## 148 HSAOrHRAEmployerContribution
## 149 HSAOrHRAEmployerContributionAmount
## 150 URLForSummaryofBenefitsCoverage
## 151 PlanBrochureThe dbGetQuery() function sends a SQL query to the Spark table and retrieves the results directly into R. By returning the results as a data frame, it allows us to inspect the data immediately and use all standard R functions for analysis, visualization, and further manipulation without needing to write additional Spark code.This is particularly useful for massive datasets to aggregate data before pulling it into R.
Exercise 3
Metal Level percentages by Plan Type
Adult/Child-only coverage analysis
Calculate Metal Level percentages
metal_dist <- dfPlanAttrib %>%
group_by(PlanType, MetalLevel) %>%
summarise(n = n()) %>%
group_by(PlanType) %>%
mutate(percentage = 100 * n / sum(n)) %>%
collect()## `summarise()` has grouped output by "PlanType". You can override using the
## `.groups` argument.
## `summarise()` has grouped output by "PlanType". You can override using the
## `.groups` argument.Plot
ggplot(metal_dist, aes(x = PlanType, y = percentage, fill = MetalLevel)) +
geom_bar(stat = "identity") +
theme_minimal() +
scale_y_continuous(labels = percent_format(scale = 1)) +
labs(title = "Metal Level Distribution by Plan Type",
x = "Plan Type", y = "Percentage (%)") +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
Silver and Gold plans represent the largest share across most plan types. High represents Silver, Gold, and Platinum, while low represents Bronze and Expanded Bronze
This reflects affordability considerations and eligibility for subsidies.
Adult/Child Enrollment Options
enrollment_dist <- dfPlanAttrib %>%
group_by(PlanType, ChildOnlyOffering) %>%
summarise(n = n()) %>%
group_by(PlanType) %>%
mutate(percentage = 100 * n / sum(n)) %>%
collect()## `summarise()` has grouped output by "PlanType". You can override using the
## `.groups` argument.
## `summarise()` has grouped output by "PlanType". You can override using the
## `.groups` argument.ggplot(enrollment_dist, aes(x = PlanType, y = percentage, fill = ChildOnlyOffering)) +
geom_bar(stat = "identity") +
theme_minimal() +
scale_y_continuous(labels = percent_format(scale = 1)) +
labs(title = "Enrollment Options by Plan Type",
x = "Plan Type", y = "Percentage (%)") +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ChildOnlyOffering indicates whether a plan allows enrollment for adults and/or children. Most plans allow both adults and children, while a smaller portion are child-only. This reflects market demand and plan design considerations.
enrollment_dist %>% arrange(PlanType, desc(percentage)) %>% head(10)## # A tibble: 10 × 4
## PlanType ChildOnlyOffering n percentage
## <chr> <chr> <dbl> <dbl>
## 1 EPO Allows Adult and Child-Only 6583 98.4
## 2 EPO Allows Adult-Only 96 1.43
## 3 EPO Allows Child-Only 13 0.194
## 4 HMO Allows Adult and Child-Only 13758 99.9
## 5 HMO Allows Child-Only 9 0.0654
## 6 Indemnity Allows Adult and Child-Only 44 84.6
## 7 Indemnity Allows Child-Only 8 15.4
## 8 POS Allows Adult and Child-Only 2082 99.9
## 9 POS Allows Child-Only 2 0.0960
## 10 PPO Allows Adult and Child-Only 4526 94.7The table above shows the percentage of plans that offer Adult-only, Adult and child-only, and child-only, based on the plan type.
Exercise 4
Dental Benefits by State
Load Benefits_Cost_Sharing.csv
file_path_benefits <- paste0("file:///", getwd(), "/benefits-and-cost-sharing-puf.csv")
dfBenefits <- spark_read_csv(
sc,
name = "BenefitsCostSharing",
path = file_path_benefits,
infer_schema = TRUE,
memory = TRUE
)Detect dental benefits
dfDental <- dfBenefits %>%
mutate(desc_lower = lower(BenefitName)) %>%
filter(desc_lower %like% "%dental%") %>%
group_by(StateCode) %>%
summarise(n_dental = n()) %>%
collect()ggplot(dfDental, aes(x = reorder(StateCode, -n_dental), y = n_dental)) +
geom_bar(stat = "identity", fill = "#66c2a5") +
theme_minimal() +
labs(title = "Dental Benefits Count by State", x = "State", y = "Number of Plans") +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
Based on the plot, Texas has a noticeably higher number of dental plans compared to other states, followed by Wisconsin and Florida. This trend could be influenced by several factors. First, Texas has one of the largest populations in the U.S., so the absolute number of plans is likely higher simply due to greater market demand. Additionally, regional variations in employer offerings or state-level insurance regulations may encourage a wider availability of dental benefits. It is also important to note that the dataset was filtered to include only benefits where the term ‘dental’ is mentioned in the BenefitName; the full dataset includes many other types of benefits that are not counted here. Therefore, the counts reflect the presence of dental-specific plans rather than all benefit offerings. On an other note, it is possible that Texans, might have more dental needs or awareness of dental care, which could drive the higher number of plans!
Exercise 5
Chronic vs Non-Chronic Disease Benefits
Chronic diseases list
chronic_keywords <- c(
"dialysis",
"chemotherapy",
"radiation",
"diabetes education",
"infusion",
"infusion therapy",
"specialty drugs",
"long-term",
"private-duty nursing",
"hearing aids",
"durable medical equipment",
"skilled nursing",
"home health",
"hospice",
"mental/behavioral",
"substance abuse",
"rehabilitative",
"habilitative",
"prosthetic",
"transplant"
)
chronic_pattern <- paste(chronic_keywords, collapse = "|")Categorize benefits
dfDiseases <- dfBenefits %>%
mutate(
desc_lower = lower(BenefitName),
disease_type = ifelse(
grepl(chronic_pattern, desc_lower),
"Chronic",
"Other"
)
) %>%
group_by(StateCode, disease_type) %>% # ⬅️ IMPORTANT FIX
summarise(n = n(), .groups = "drop_last") %>%
group_by(StateCode) %>%
mutate(percentage = 100 * n / sum(n)) %>%
collect()Plot
ggplot(dfDiseases, aes(x = reorder(StateCode, -percentage), y = percentage, fill = disease_type)) +
geom_bar(stat = "identity") +
theme_minimal() +
labs(title = "Chronic vs Non-Chronic Disease Benefits by State",
x = "State", y = "Percentage (%)") +
scale_y_continuous(labels = percent_format(scale = 1)) +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
Due to the fact that the dataset describes insurance benefits rather than medical diagnoses, chronic conditions are not explicitly names. Therefore, benefits associated with chronic disease management (e.g., dialysis, chemotherapy, durable medical equipment, and behavioral health services) were used as proxies for chronic conditions. It is important to note that some benefits (e.g., rehabilitation or specialty medications) may support both chronic and non-chronic conditions; however, they were classified as chronic-related when they typically indicate ongoing or long-term care.
Exercise 6
Individual Rates by Age and State (with Outlier Handling)
Median and IQR per State & Age
Load library rate-puf.csv
dfRate <- spark_read_csv(
sc,
name = "Rate",
path = paste0("file:///", getwd(), "/rate-puf.csv"),
infer_schema = TRUE,
memory = TRUE
)# Clean data: keep only numeric ages
dfRate_clean <- dfRate %>%
mutate(
Age = sql("CAST(Age AS INT)"),
IndividualRate = sql("CAST(IndividualRate AS DOUBLE)")
) %>%
filter(!is.na(Age), !is.na(IndividualRate))
# Compute IQR and cap outliers per state
dfRate_iqr <- dfRate_clean %>%
mutate(
Q1 = sql(
"percentile_approx(IndividualRate, 0.25)
OVER (PARTITION BY StateCode)"
),
Q3 = sql(
"percentile_approx(IndividualRate, 0.75)
OVER (PARTITION BY StateCode)"
),
IQR = Q3 - Q1,
lower_bound = Q1 - 1.5 * IQR,
upper_bound = Q3 + 1.5 * IQR,
IndividualRate_capped = pmin(
pmax(IndividualRate, lower_bound),
upper_bound
)
)
# Summarise mean rates per state & age
dfRate_summary <- dfRate_iqr %>%
group_by(StateCode, Age) %>%
summarise(
MeanIndividualRate = mean(IndividualRate_capped, na.rm = TRUE),
.groups = "drop"
)
dfRate_plot <- dfRate_summary %>% collect()
# Plot separate graphs per state
states <- unique(dfRate_plot$StateCode)
for (st in states) {
df_state <- dfRate_plot %>% filter(StateCode == st)
print(
ggplot(df_state, aes(x = Age, y = MeanIndividualRate)) +
geom_line(color = "steelblue") +
geom_point(size = 1) +
labs(
title = paste("Mean Individual Health Insurance Rate by Age –", st),
x = "Age",
y = "Mean Individual Rate (USD)"
) +
theme_minimal()
)
}
Individual insurance rates increase with age, reflecting higher expected healthcare utilization among older enrollees. The line graphs show the mean monthly rate for each age within each state, with outliers capped using the 1.5×IQR method to reduce the influence of extreme values. Rates generally rise steadily from younger ages to older ages across all states, demonstrating consistent age-based pricing patterns. The limited number of capped values indicates that extreme rates are rare, and repeated rate values reflect identical pricing across multiple plans rather than data errors. State-specific plots reveal small variations between states but maintain the overall upward trend with age.
Exercise 7
Tobacco vs Non-tobacco Rates
# Filter relevant rows
dfTobacco <- dfRate %>%
filter(Tobacco == "Tobacco User/Non-Tobacco User" & !is.na(Age))
# Clean age and select rates
dfTobacco <- dfTobacco %>%
mutate(
Age_numeric = as.numeric(regexp_replace(Age, " .*", "")),
Rate_Individual = IndividualRate,
Rate_Tobacco = IndividualTobaccoRate
)
# Manage outliers per state and age
iqr_table <- dfTobacco %>%
group_by(StateCode, Age_numeric) %>%
summarise(
Q1 = percentile_approx(Rate_Tobacco, 0.25),
Q3 = percentile_approx(Rate_Tobacco, 0.75)
) %>%
mutate(IQR = Q3 - Q1)## `summarise()` has grouped output by "StateCode". You can override using the
## `.groups` argument.dfTobacco <- dfTobacco %>%
left_join(iqr_table, by = c("StateCode", "Age_numeric")) %>%
mutate(
Rate_Tobacco_Capped = pmin(pmax(Rate_Tobacco, Q1 - 1.5*IQR), Q3 + 1.5*IQR),
Rate_Individual_Capped = pmin(pmax(Rate_Individual, Q1 - 1.5*IQR), Q3 + 1.5*IQR) # optional
)## `summarise()` has grouped output by "StateCode". You can override using the
## `.groups` argument.
## `summarise()` has grouped output by "StateCode". You can override using the
## `.groups` argument.
## `summarise()` has grouped output by "StateCode". You can override using the
## `.groups` argument.# Summarise mean rates per State, Tobacco, Age
dfSummary <- dfTobacco %>%
group_by(StateCode, Age_numeric) %>%
summarise(
mean_individual = mean(Rate_Individual_Capped, na.rm = TRUE),
mean_tobacco = mean(Rate_Tobacco_Capped, na.rm = TRUE)
) %>%
collect()## `summarise()` has grouped output by "StateCode". You can override using the
## `.groups` argument.
## `summarise()` has grouped output by "StateCode". You can override using the
## `.groups` argument.# Plot separate graphs for Tobacco / Non-Tobacco
unique_states <- unique(dfSummary$StateCode)
for (state in unique_states) {
df_state <- dfSummary %>% filter(StateCode == state)
p <- ggplot(df_state, aes(x = Age_numeric)) +
geom_line(aes(y = mean_individual, color = "Non-Tobacco User"), size = 1.2) +
geom_line(aes(y = mean_tobacco, color = "Tobacco User"), size = 1.2) +
labs(
title = paste("Mean Insurance Rates by Age —", state),
x = "Age",
y = "Mean Rate",
color = "User Type"
) +
theme_minimal()
print(p) # or ggsave(paste0("plot_", state, ".png"), p, width=8, height=6)
}## Warning: Using `size` aesthetic for lines was deprecated in ggplot2 3.4.0.
## ℹ Please use `linewidth` instead.
## This warning is displayed once every 8 hours.
## Call `lifecycle::last_lifecycle_warnings()` to see where this warning was
## generated.## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
## Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).
The dataset was filtered to include only insurance plans that explicitly distinguish between tobacco and non-tobacco users, with missing ages removed. Outliers in tobacco and individual rates were managed using the 1.5 × IQR method, capping extreme values within each state and age group. This approach ensures that unusually high or low rates do not distort the analysis, allowing for a clearer comparison of typical costs.
From the plots, an interesting pattern emerges: for individuals up to age 20, insurance rates for tobacco and non-tobacco users are identical across all states. After this age, the rates begin to diverge, with tobacco users consistently facing higher premiums. This difference generally increases with age, reflecting insurers’ assessment of long-term health risk. Tobacco use is well known to increase the likelihood of chronic diseases such as cardiovascular disease, respiratory conditions, and cancer. As a result, older tobacco users are considered higher-risk, leading to higher insurance rates.
Additionally, the increasing gap with age highlights how insurers incorporate cumulative risk into pricing. Early adulthood shows minimal differences because younger tobacco users have not yet experienced the long-term health consequences that significantly impact risk assessments. The consistency of this pattern across multiple states suggests that the effect of tobacco on insurance rates is systematic rather than state-specific.
Exercise 8
Family Option Rates by Metal Level
# Collect Plan Attributes into R
dfPlanAttrib_r <- dfPlanAttrib %>% collect() %>%
mutate(PlanId_clean = gsub("-\\d+$", "", PlanId)) %>%
group_by(PlanId_clean) %>%
slice(1) %>% # keep first row per plan
ungroup()
# Collect Family Option rates into R
dfFamily <- dfRate %>%
filter(Age == "Family Option") %>%
select(PlanId, StateCode, RateEffectiveDate,
PrimarySubscriberAndTwoDependents,
CoupleAndTwoDependents) %>%
mutate(
FamilyRate_OneDependent = as.numeric(PrimarySubscriberAndTwoDependents),
FamilyRate_Couple = as.numeric(CoupleAndTwoDependents)
) %>%
filter(!is.na(FamilyRate_OneDependent) & !is.na(FamilyRate_Couple)) %>%
collect()
# Join MetalLevel from Plan Attributes
dfFamily <- dfFamily %>%
inner_join(dfPlanAttrib_r %>% select(PlanId_clean, MetalLevel),
by = c("PlanId" = "PlanId_clean")) %>%
filter(!is.na(MetalLevel))
# Keep only the max rate per plan (across RateEffectiveDate)
dfFamily <- dfFamily %>%
group_by(PlanId, StateCode, MetalLevel) %>%
summarise(
FamilyRate_OneDependent = max(FamilyRate_OneDependent, na.rm = TRUE),
FamilyRate_Couple = max(FamilyRate_Couple, na.rm = TRUE),
.groups = "drop"
)
# Reshape to long format for plotting
dfFamily_long <- dfFamily %>%
tidyr::pivot_longer(
cols = c(FamilyRate_OneDependent, FamilyRate_Couple),
names_to = "CoverageType",
values_to = "Rate"
) %>%
mutate(CoverageType = recode(CoverageType,
"FamilyRate_OneDependent" = "Primary + 2 Dependents",
"FamilyRate_Couple" = "Couple + 2 Dependents"))
# Handle outliers per State and MetalLevel
dfFamily_long <- dfFamily_long %>%
group_by(StateCode, MetalLevel) %>%
mutate(
Q1 = quantile(Rate, 0.25, na.rm = TRUE),
Q3 = quantile(Rate, 0.75, na.rm = TRUE),
IQR = Q3 - Q1,
Rate_Capped = pmin(pmax(Rate, Q1 - 1.5*IQR), Q3 + 1.5*IQR)
) %>%
ungroup()
# Summarize mean rate per State, MetalLevel, CoverageType
dfSummary <- dfFamily_long %>%
group_by(StateCode, MetalLevel, CoverageType) %>%
summarise(mean_rate = mean(Rate_Capped, na.rm = TRUE), .groups = "drop")
# Plot per state
unique_states <- unique(dfSummary$StateCode)
for(state in unique_states){
df_state <- dfSummary %>% filter(StateCode == state)
p <- ggplot(df_state, aes(x = MetalLevel, y = mean_rate, fill = CoverageType)) +
geom_col(position = "dodge") +
labs(
title = paste("Family Insurance Rates by Metal Level —", state),
x = "Metal Level",
y = "Mean Family Rate (USD)",
fill = "Coverage Type"
) +
theme_minimal()
print(p)
}
This analysis examines how insurance rates vary for families (“Family Option”) across different Metal Levels. We focus on two coverage types: Primary Subscriber + Two Dependents and Couple + Two Dependents, chosen because rates differ depending on whether the primary subscriber is single or a couple, allowing for meaningful comparison.
The PlanId in the plan attributes dataset was cleaned to
match the rate-puf.csv IDs, and only the highest rate per
plan across all effective dates was kept to capture the maximum yearly
cost. Data was reshaped for plotting, and outliers were capped using the
interquartile range (IQR) method.
Mean rates were calculated per state, Metal Level, and coverage type. The resulting bar plots show that rates increase from lower-tier plans (Bronze, Expanded Bronze) to higher-tier plans (Silver, Gold, Platinum) and consistently differ between primary subscriber and couple coverage. This provides a clear view of how family insurance costs vary by plan tier and coverage type.