Health Insurance Marketplace Costs

Data.Healthcare.Gov released QHP cost information for various health care plans for states in the Federally-Facilitated and State-Partnership Marketplaces. The data is available in a variety of formats and lays out costs for various levels of health care plans (Gold, Silver, Bronze and Catastrophe) for different categories.

Premium Information
Premium amounts do not include tax credits that will lower premiums for the majority of those applying, specifically those with income up to 400 percent of the federal poverty level. The document shows premiums for the following example rating scenarios below:

Adult Individual Age 27 = one adult age 27
Adult Individual Age 50 = one adult age 50
Family = two adults age 30, two children
Single Parent Family = one adult age 30, two children
Couple = two adults age 40, no children
Child = one child any age

Cost Comparisons
Looking at the information, I wanted to do some comparisons across the various plans and rating scenarios to see where the highest costs where, what states had the largest variance and to look at the standard deviation across states/plans.

While I could have run this in Excel or R, I decided to write a simple Pig job to determine the maximum, minimum and average costs by plan for each state. I also then calculated the variance and standard deviations.

Show Pig Code (555 More Words)


/**
 * healthcareCosts
 */
 
/** 
 * Parameters - set default values here; you can override with -p on the command-line.
 */
 
%default INPUT_PATH '/Users/davidfauth/healthcareCosts/QHP_Individual_Medical_Landscape.csv'
%default OUTPUT_PATH '/Users/davidfauth/MortarHealthCareCostsOut'
%default OUTPUT_PATH_PLANDELTAS '/Users/davidfauth/MortarHealthCareCostsOut/PlanDeltas'
%default OUTPUT_PATH_PLANSTANDARD '/Users/davidfauth/MortarHealthCareCostsOut/PlanStandard'

/**
 * User-Defined Functions (UDFs)
 */

-- Load the input data from the CSV file
raw_data = LOAD '$INPUT_PATH' 
          USING org.apache.pig.piggybank.storage.CSVExcelStorage(',', 'NO_MULTILINE', 'NOCHANGE', 'SKIP_INPUT_HEADER')
             AS (State:chararray,
				County:chararray,
				MetalLevel:chararray,
				IssuerName:chararray,
				PlanMarketingName:chararray,
				PlanType:chararray,
				RatingArea:chararray,
				PremiumAdultIndividualAge27:double,
				PremiumAdultIndividualAge50:double,
				PremiumFamily:double,
				PremiumSingleParentFamily:double,
				PremiumCouple:double,
				PremiumChild:double);
				
-- Limit to sa subset of data				
	
noCurrencySymbol = FOREACH raw_data GENERATE State as newState,
County as newCounty,
MetalLevel as newMetalLevel,
PremiumAdultIndividualAge27 as newPremiumAdultIndividualAge27,
PremiumAdultIndividualAge50 as newPremiumAdultIndividualAge50,
PremiumFamily as newPremiumFamily,
PremiumSingleParentFamily as newPremiumSingleParentFamily,
PremiumCouple as newPremiumCouple,
PremiumChild as newPremiumChild; 
	
/* Group together identical tuples */
PlansByMetalLevel = GROUP noCurrencySymbol BY (newMetalLevel, newState);

--calculate Max, Min and Avg costs
costsByPlansPAA27 = FOREACH PlansByMetalLevel GENERATE FLATTEN(group), 
	COUNT(noCurrencySymbol) as planCount,
    AVG(noCurrencySymbol.newPremiumAdultIndividualAge27) as avgPremiumAdultAge27,
	MIN(noCurrencySymbol.newPremiumAdultIndividualAge27) as minPremiumAdultAge27,
	MAX(noCurrencySymbol.newPremiumAdultIndividualAge27) as maxPremiumAdultAge27,
	AVG(noCurrencySymbol.newPremiumAdultIndividualAge50) as avgPremiumAdultAge50,
	MIN(noCurrencySymbol.newPremiumAdultIndividualAge50) as minPremiumAdultAge50,
	MAX(noCurrencySymbol.newPremiumAdultIndividualAge50) as maxPremiumAdultAge50,
	AVG(noCurrencySymbol.newPremiumFamily) as avgPremiumAdultAgePFAM,
	MIN(noCurrencySymbol.newPremiumFamily) as minPremiumAdultAgePFAM,
	MAX(noCurrencySymbol.newPremiumFamily) as maxPremiumAdultAgePFAM,
    AVG(noCurrencySymbol.newPremiumSingleParentFamily) as avgPremiumAdultAgePSPFAM,
    MIN(noCurrencySymbol.newPremiumSingleParentFamily) as minPremiumAdultAgePSPFAM,
	MAX(noCurrencySymbol.newPremiumSingleParentFamily) as maxPremiumAdultAgePSPFAM,
    AVG(noCurrencySymbol.newPremiumCouple) as avgPremiumAdultAgePC,
    MIN(noCurrencySymbol.newPremiumCouple) as minPremiumAdultAgePC,
	MAX(noCurrencySymbol.newPremiumCouple) as maxPremiumAdultAgePC,
    AVG(noCurrencySymbol.newPremiumChild) as avgPremiumAdultAgePCh,
    MIN(noCurrencySymbol.newPremiumChild) as minPremiumAdultAgePCh,
	MAX(noCurrencySymbol.newPremiumChild) as maxPremiumAdultAgePCh;

	--calculate deltas
deltasByPlan = FOREACH costsByPlansPAA27 GENERATE newMetalLevel, newState,
avgPremiumAdultAge27 - minPremiumAdultAge27 as deltaMinAvgAge27,
avgPremiumAdultAge50 - minPremiumAdultAge50 as deltaMinAvgAge50,
avgPremiumAdultAgePFAM - minPremiumAdultAgePFAM as deltaMinAvgAgePFAM,
avgPremiumAdultAgePSPFAM - minPremiumAdultAgePSPFAM as deltaMinAvgAgePSPFAM,
avgPremiumAdultAgePC - minPremiumAdultAgePC as deltaMinAvgAgePC,
avgPremiumAdultAgePCh- minPremiumAdultAgePCh as deltaMinAvgAgePCh;

-- calculate variance and Standard Deviations
mean = foreach PlansByMetalLevel {
        sum27 = SUM(noCurrencySymbol.newPremiumAdultIndividualAge27);
        sum50 = SUM(noCurrencySymbol.newPremiumAdultIndividualAge50);
        sumPFAM = SUM(noCurrencySymbol.newPremiumFamily);
        sumPSPFAM = SUM(noCurrencySymbol.newPremiumSingleParentFamily);
        sumPC = SUM(noCurrencySymbol.newPremiumCouple);
        sumPCh = SUM(noCurrencySymbol.newPremiumChild);
		count = COUNT(noCurrencySymbol);
        generate flatten(noCurrencySymbol), sum27/count as avg27, sum50/count as avg50, 
        sumPFAM/count as avgPFAM, sumPSPFAM/count as avgPSPFAM, sumPC/count as avgPC, 
		sumPCh/count as avgPCh, count as count;
};

tmp = foreach mean {
    dif27 = (newPremiumAdultIndividualAge27 - avg27) * (newPremiumAdultIndividualAge27 - avg27) ;
    dif50 = (newPremiumAdultIndividualAge50 - avg50) * (newPremiumAdultIndividualAge50 - avg50) ;
    difPFAM = (newPremiumFamily - avgPFAM) * (newPremiumFamily - avgPFAM) ;
    difPSPFAM = (newPremiumSingleParentFamily - avgPSPFAM) * (newPremiumSingleParentFamily - avgPSPFAM) ;
    difPC = (newPremiumCouple - avgPC) * (newPremiumCouple - avgPC) ;
    difPCh = (newPremiumChild - avgPCh) * (newPremiumChild - avgPCh) ;
     generate newMetalLevel, newState, count, dif27 as dif27,
	dif50 as dif50, difPFAM as difPFAM, difPSPFAM as difPSPFAM, difPC as difPC, difPCh as difPCh;
};


grp = group tmp by (newMetalLevel, newState);
standard_tmp = foreach grp generate flatten(tmp), SUM(tmp.dif27) as sqr_sum27, SUM(tmp.dif50) as sqr_sum50,
	SUM(tmp.difPFAM) as sqr_sumPFAM, SUM(tmp.difPSPFAM) as sqr_sumPSPFAM, SUM(tmp.difPC) as sqr_sumPC, 
	SUM(tmp.difPCh) as sqr_sumPCh; 
	
standard = foreach standard_tmp generate newState, newMetalLevel,
sqr_sum27 / count as variance27, SQRT(sqr_sum27 / count) as standard27,
sqr_sum50 / count as variance50, SQRT(sqr_sum50 / count) as standard50,
sqr_sumPFAM / count as variancePFAM, SQRT(sqr_sumPFAM / count) as standardPFAM,
sqr_sumPSPFAM / count as variancePSPFAM, SQRT(sqr_sumPSPFAM / count) as standardPSPFAM,
sqr_sumPC / count as variancePC, SQRT(sqr_sumPC / count) as standardPC,
sqr_sumPCh / count as variancePCh, SQRT(sqr_sumPCh / count) as standardPCh;

distinctStandard = DISTINCT standard;

-- remove any existing data
rmf $OUTPUT_PATH;

-- store the results
STORE costsByPlansPAA27 INTO '$OUTPUT_PATH' USING PigStorage('|');
STORE deltasByPlan INTO '$OUTPUT_PATH_PLANDELTAS' USING PigStorage('|');
STORE distinctStandard INTO '$OUTPUT_PATH_PLANSTANDARD' USING PigStorage('|');

Initial Cost Analysis
There is a wide range in costs across the states with Virginia being consistently the highest average cost plan. Looking at the catastrophic costs, Virginia plans are five times (5x) more expensive than Kansas or Alabama.

For Gold plans, Virginia is again between two and three times (2-3X) more expensive to buy insurance.

Variance and Standard Deviation
It comes as no surprise that Virginia has the largest variance and standard deviation for the cost data by a large margin. Virginia’s variance on the Gold plans is 2742 times that of Alabama. New Hampshire, Alaska, Delaware and Utah all have small variances and are consistent across the rating scenarios.

Again, Virginia’s variance on the bronze plans are way out of balance compared to other states.

However, for a Platinum plan, has the ninth smallest variation across all rating scenarios. New Jersey, Michigan and Wisconsin have the largest variations.

Code and Data
The code and data is on Github. If you have questions, you can reach me at dsfauth at gmail dot com.

1 thought on “Health Insurance Marketplace Costs”

Donnie Berkholz October 7, 2013 at 6:44 am

When I was looking through scenarios myself, the more important use case was annual cost in a couple of scenarios: one person maxes out the deductible, or we max out the whole family deductible. As it turns out, the platinum plans in MN were actually the cheapest in both cases.

And obviously accounting for tax credits would be interesting too, as you note.

Reply ↓

1 thought on “Health Insurance Marketplace Costs”

Leave a Reply Cancel reply