#!/usr/bin/env python
import sys
import numpy as np
import pandas as pd
import pdb
from utility_modules.get_parameters import get_params
from policy_modules.premium_calculate import CalculatePremium as CalculatePremium
[docs]class Exchange:
# Borg singleton config object
__shared_state = {}
def __init__(self, policy_dict = None):
# implement borg pattern
self.__dict__ = self.__shared_state
if policy_dict != None:
# read in affordibility definitions (include parameter for it)
age_rating = policy_dict['age_rating']
xc_prem_plans = policy_dict['calc_xc_prem']
self.xc_prem_plans= get_params(xc_prem_plans)
self.age_table = get_params(age_rating)
self.min_age = min(self.age_table.age)
self.max_age = max(self.age_table.age)
gid_subsidy = policy_dict['prem_sub']
self.prem_sub = get_params(gid_subsidy)
[docs] def get_reg_prem(self):
return self.curr_rp
[docs] def get_state_prem_ratio(self):
return [self.slv_brz_ratio,self.cat_brz_ratio]
[docs] def set_year(self, curr_year, subsidy_to_undoc, afford_table, func_params={'xc_inflator': None, 'av_threshold': .6, 'pmc_flag': None}, region_parameters = None):
"""
return slice of pertinent year
allows this to be reset at begining of each simulation year
"""
self.pmc_flag = func_params['pmc_flag']
# getting the inflator
self.xc_inflator = func_params['xc_inflator']
self.av_threshold = func_params['av_threshold']
self.bronze_subsidy = func_params['bronze_subsidy']
self.bronze_subsidy_year = func_params['bronze_subsidy_year']
self.policy_num = func_params['policy_num']
if (curr_year >= self.bronze_subsidy_year) & (self.bronze_subsidy == True):
self.calc_bronze_sub_amount = True
else:
self.calc_bronze_sub_amount = False
self.min_value = afford_table.Minimum_Value_Thresh.values[0]
self.esi_thresh = afford_table.Esi_Affordable_Thresh.values[0]
self.esi_thresh_family = afford_table.Esi_Family_Thresh.values[0]
self.max_fpl = afford_table.fpl_limit.values[0]
if curr_year not in self.prem_sub.year.values:
return ## should we do more than just return ? [xc]
else:
#statewide annual premium
self.cat_prem = self.xc_inflator*float(self.xc_prem_plans['cat_21'][self.xc_prem_plans.plan_type==curr_year])
self.brz_prem = self.xc_inflator*float(self.xc_prem_plans['bronze_21'][self.xc_prem_plans.plan_type==curr_year])
self.slv_prem = self.xc_inflator*float(self.xc_prem_plans['silver_21'][self.xc_prem_plans.plan_type==curr_year])
#subset to only values in designated year
self.premium_sub = self.prem_sub[self.prem_sub.year==curr_year]
self.premium_sub= self.premium_sub.reset_index(drop=True)
self.premium_sub_silver = self.premium_sub[self.premium_sub['metal_tier']=='silver']
self.premium_sub_bronze = self.premium_sub[self.premium_sub['metal_tier']=='bronze']
#thresholds for sliver and for bronze
self.p_lower_limit = self.premium_sub.p_lower.min()
self.fpl_limit = self.premium_sub.fpl_upper.max() # for eligibility for subsidy under default policy
self.p_lower_limit_sliver = self.premium_sub_silver.p_lower.min()
self.fpl_limit_silver = self.premium_sub_silver.fpl_upper.max()
self.fpl_limit=self.premium_sub.fpl_upper.max()
# for calculating family cap using interpolation
self.fpl_bracket = self.premium_sub.fpl_lower.tolist()
self.p_limit= self.premium_sub.p_lower.tolist()
self.fpl_bracket = [self.fpl_bracket[0]] + [self.fpl_bracket[1]] + self.fpl_bracket[1:]
self.fpl_bracket = self.fpl_bracket + [self.fpl_bracket[-1]]
#pdb.set_trace()
self.p_limit = [self.p_limit[0]] + [self.p_limit[0]] + self.p_limit[1:]
#self.p_limit = self.p_limit[:-1] + [self.p_limit[-2]] + [self.p_limit[-1]]
self.p_limit = self.p_limit + [self.p_limit[-1]]
self.p_limit[-2]=self.premium_sub.p_higher.tolist()[-2]
if 'p_lower_default' in self.premium_sub.columns:
self.p_limit_alt = self.premium_sub.p_lower_default.tolist()
self.p_limit_alt = [self.p_limit_alt[0]] + [self.p_limit_alt[0]] + self.p_limit_alt[1:]
self.p_limit_alt = self.p_limit_alt + [self.p_limit_alt[-1]]
self.p_limit_alt[-2] = self.premium_sub.p_higher_default.tolist()[-2]
self.p_limit_alt = pd.Series(self.p_limit_alt).fillna(100).tolist()
self.subsidy_to_undoc = subsidy_to_undoc
# threshold for maximum fpl
if region_parameters is not None:
types = ['silver_21','bronze_21','cat_21','silver_21_uns','bronze_21_uns','cat_21_uns']
self.curr_rp = region_parameters
self.curr_rp[types] = self.curr_rp[types]*self.xc_inflator
xc_mat = [(6, self.slv_prem), (7, self.brz_prem),(8, self.cat_prem)]
xc_prem = pd.DataFrame(xc_mat, columns=['type', 'prem'])
xc_prem = xc_prem.set_index('type')
"""
Create multiplier dataframe 4*17
4: silver_21_unloaded, silver_21_loaded, bronze_21_unloaded, bronze_21_loaded
17: for each of the 17 regions
Each multiplier uses the statewide bronze premium as the reference
"""
self.curr_rp['mult_slv'] = self.curr_rp['silver_21']/self.brz_prem
self.curr_rp['mult_brz'] = self.curr_rp['bronze_21']/self.brz_prem
self.curr_rp['mult_cat'] = self.curr_rp['cat_21']/self.brz_prem
self.curr_rp['mult_slv_uns'] = self.curr_rp['silver_21_uns']/self.brz_prem
self.curr_rp['mult_brz_uns'] = self.curr_rp['bronze_21_uns']/self.brz_prem
self.curr_rp['mult_cat_uns'] = self.curr_rp['cat_21_uns']/self.brz_prem
self.slv_brz_ratio = self.slv_prem/self.brz_prem #ratio between statewide silver and bronze
self.cat_brz_ratio = self.cat_prem/self.brz_prem #ratio between statewide catastrophic and bronze
[docs] def dynamic_exchange_update(self, xc_prem, iter):
"""
first updates the statewide premiums and then uses the updated statewide
premiums in order to calculate the regional premiums using the multiplier
iter: indicates which xc iteration the dynamic exchange is running on
"""
self.brz_prem = xc_prem.loc[7].iloc[0]
self.curr_rp['bronze_21'] = self.curr_rp['mult_brz'] * self.brz_prem
self.curr_rp['silver_21'] = self.curr_rp['mult_slv'] * self.brz_prem
self.curr_rp['cat_21'] = self.curr_rp['mult_cat'] * self.brz_prem
self.curr_rp['bronze_21_uns'] = self.curr_rp['mult_brz_uns']* self.brz_prem
self.curr_rp['silver_21_uns'] = self.curr_rp['mult_slv_uns']* self.brz_prem
self.curr_rp['cat_21_uns'] = self.curr_rp['mult_cat_uns']* self.brz_prem
self.curr_rp['iteration'] = iter
return self.curr_rp
[docs] def subsidy_elig(self, pop_df, wrk_df, Curr_esi, affordability_type):
# need to have the updated medicaid eligibility: mcaid_elig
# subsidy_elig: all legal requirement
# subsidy_elig_no_fpl: subsidy_elig minus the fpl requirement
# step 1: creating affordability column back to pop_df also Creating family_type
#Family type: 1: single 2:sp 3:ch 4:fam 5:not eligible
pop_df=pd.merge(pop_df,Curr_esi[['firm_id','offer','pt']],on='firm_id',how='left')
pop_df=pd.merge(pop_df,wrk_df[['person_id','esi_not_elig']],on='person_id',how='left')
pop_df['esi_elig']=((pop_df['full_time']==1)&(pop_df['offer']==1))|((pop_df['full_time']==0)&(pop_df['pt']==1)&(pop_df['esi_not_elig']==False))
loc_ind_adult_child = np.where((pop_df['person_type'] > 3) & (pop_df['tax_dependent'] == 0))[0]
loc_real_family=pop_df.index.difference(loc_ind_adult_child)
#for shaping purpose
temp_df=pop_df.loc[loc_real_family]
temp_df_columns = temp_df.columns.values
fake_hieu_shape = pd.DataFrame(data=np.nan, index=range(0, 8), columns=temp_df_columns)
fake_hieu_shape.hieu_id = 4000000
for x in range(0, 8):
fake_hieu_shape.loc[[x], 'person_type'] = x
fake_hieu_shape.family_id = 0
temp_df = pd.concat([temp_df, fake_hieu_shape], axis=0, ignore_index=True)
family_type_df=pd.pivot_table(temp_df,index='hieu_id',columns='person_type',values=['mcaid_elig','mcare','esi_elig','person_id'])
new_columns=['mcaid_elig_' + str(i) for i in range(8)]+['mcare_' + str(i) for i in range(8)]+['esi_elig_' + str(i) for i in range(8)]+['pid_' + str(i) for i in range(8)]
family_type_df.columns = new_columns
family_type_df=family_type_df.reset_index()
family_type_df.fillna(2,inplace=True)
##for p0
#if family_size is 1 or p0 not esi eligible fam_type_0 is just 1
family_type_df.loc[family_type_df['esi_elig_0'] != 1, 'family_type_for_afford_esi0'] = 5
#first determine family has spouse and mark it as type2 temporarily
loc_atleast_sp0=np.where((family_type_df['esi_elig_0']==1)&(family_type_df['mcaid_elig_1']==0)&(family_type_df['mcare_1']==0))[0]
family_type_df.loc[loc_atleast_sp0,'family_type_for_afford_esi0']=2
#determine family_type3 single parent+child
loc_ch_0=np.where((family_type_df['esi_elig_0']==1)&((family_type_df['mcaid_elig_1']!=0)|(family_type_df['mcare_1']!=0))& \
(((family_type_df['mcaid_elig_2']==0)&(family_type_df['mcare_2']==0))|((family_type_df['mcaid_elig_3']==0)&(family_type_df['mcare_3']==0))))[0]
family_type_df.loc[loc_ch_0, 'family_type_for_afford_esi0'] = 3
#determine family_type4 spouse+spouse+child
loc_fam_0=np.where((family_type_df['family_type_for_afford_esi0']==2)&(((family_type_df['mcaid_elig_2']==0)&(family_type_df['mcare_2']==0))|((family_type_df['mcaid_elig_3']==0)&(family_type_df['mcare_3']==0))))[0]
family_type_df.loc[loc_fam_0, 'family_type_for_afford_esi0'] = 4
#while esi eligible but still has family_type 1
loc_single_0 = np.where((family_type_df['esi_elig_0'] == 1) & ((family_type_df['mcaid_elig_1'] != 0) | (family_type_df['mcare_1'] != 0)) & \
(((family_type_df['mcaid_elig_2'] != 0) | (family_type_df['mcare_2'] != 0)) & ( (family_type_df['mcaid_elig_3'] != 0) | (family_type_df['mcare_3'] != 1))))[0]
family_type_df.loc[loc_single_0, 'family_type_for_afford_esi0'] = 1
##For p1
# if family_size is 1 or p0 not esi eligible fam_type_0 is just 1
family_type_df.loc[family_type_df['esi_elig_1'] != 1, 'family_type_for_afford_esi1'] = 5
# first determine family has spouse and mark it as type2 temporarily
loc_atleast_sp1 = np.where((family_type_df['esi_elig_1'] == 1) & (family_type_df['mcaid_elig_0'] == 0) & (family_type_df['mcare_0'] == 0))[0]
family_type_df.loc[loc_atleast_sp1, 'family_type_for_afford_esi1'] = 2
# determine family_type3 single parent+child
loc_ch_1 = np.where((family_type_df['esi_elig_1'] == 1) & ((family_type_df['mcaid_elig_0'] != 0) | (family_type_df['mcare_0'] != 0)) & \
(((family_type_df['mcaid_elig_2'] == 0) & (family_type_df['mcare_2'] == 0)) | ((family_type_df['mcaid_elig_3'] == 0) & (family_type_df['mcare_3'] == 0))))[0]
family_type_df.loc[loc_ch_1, 'family_type_for_afford_esi1'] = 3
# determine family_type4 spouse+spouse+child
loc_fam_1 = np.where((family_type_df['family_type_for_afford_esi1'] == 2) & (
((family_type_df['mcaid_elig_2'] == 0) & (family_type_df['mcare_2'] == 0)) | ((family_type_df['mcaid_elig_3'] == 0) & (family_type_df['mcare_3'] == 0))))[0]
family_type_df.loc[loc_fam_1, 'family_type_for_afford_esi1'] = 4
# while esi eligible but still has family_type 1
loc_single_1 = np.where((family_type_df['esi_elig_1'] == 1) & ((family_type_df['mcaid_elig_0'] != 0) | (family_type_df['mcare_0'] != 0)) & \
(((family_type_df['mcaid_elig_2'] != 0) | (family_type_df['mcare_2'] != 0)) & ((family_type_df['mcaid_elig_3'] != 0) | (family_type_df['mcare_3'] != 0))))[0]
family_type_df.loc[loc_single_1, 'family_type_for_afford_esi1'] = 1
#set family_type to be the minimum of these two
family_type_df.loc[family_type_df['family_type_for_afford_esi0']<=family_type_df['family_type_for_afford_esi1'],'family_type_for_afford_esi']=family_type_df['family_type_for_afford_esi0']
family_type_df.loc[family_type_df['family_type_for_afford_esi0'] <= family_type_df['family_type_for_afford_esi1'], 'person_id'] = family_type_df['pid_0']
family_type_df.loc[family_type_df['family_type_for_afford_esi0'] > family_type_df['family_type_for_afford_esi1'], 'family_type_for_afford_esi'] = family_type_df['family_type_for_afford_esi1']
family_type_df.loc[family_type_df['family_type_for_afford_esi0'] > family_type_df['family_type_for_afford_esi1'], 'person_id'] = family_type_df['pid_1']
#merge back and let tax_independent child has family_type
pop_df=pd.merge(pop_df,family_type_df[['hieu_id','person_id','family_type_for_afford_esi']],on=['hieu_id','person_id'],how='left')
pop_df.loc[loc_ind_adult_child, 'family_type_for_afford_esi'] = 1
loc_unelig_adult_child=np.where((pop_df['person_type'] > 3) & (pop_df['tax_dependent'] == 0)&(pop_df['esi_elig']!=1))[0]
pop_df.loc[loc_unelig_adult_child, 'family_type_for_afford_esi'] = 5
pop_df.family_type_for_afford_esi.fillna(5,inplace=True)
to_drop = ['offer', 'pt', 'esi_elig','esi_not_elig']
pop_df.drop(to_drop,inplace=True,axis=1)
pop_df_temp = self.affordability(pop_df, wrk_df, Curr_esi, affordability_type)
# step 2: exchange subsidy eligibility
if (self.subsidy_to_undoc == 0):
# pmc_flag = 1 <=> predicted medicaid eligibility is used in subsidy eligibility
if (self.pmc_flag == 0):
pop_df_temp['subsidy_elig_no_fpl'] = ((pop_df_temp['doc_status']< 3) & (pop_df_temp['affordability'] == False) & \
(pop_df_temp['mcaid_elig'] == 0) & (pop_df_temp['coverage'] != 3) & \
(pop_df_temp['coverage'] != 5)).astype(int)
pop_df_temp['adultkid_subsidy_elig_no_fpl'] = ((pop_df_temp['doc_status'] < 3) & (pop_df_temp['self_affordability'] == False) & \
(pop_df_temp['mcaid_elig'] == 0) & (pop_df_temp['coverage'] != 3) & \
(pop_df_temp['coverage'] != 5)).astype(int)
else:
pop_df_temp['subsidy_elig_no_fpl'] = ((pop_df_temp['doc_status']< 3) & (pop_df_temp['affordability'] == False) & \
(pop_df_temp['mcaid_elig'] == 0) & (pop_df_temp['coverage'] != 3) & \
(pop_df_temp['coverage'] != 5) & (pop_df_temp['predicted_elig'] == 0)).astype(int)
pop_df_temp['adultkid_subsidy_elig_no_fpl'] = ((pop_df_temp['doc_status'] < 3) & (pop_df_temp['self_affordability'] == False) & \
(pop_df_temp['mcaid_elig'] == 0) & (pop_df_temp['coverage'] != 3) & \
(pop_df_temp['coverage'] != 5) & (pop_df_temp['predicted_elig'] == 0)).astype(int)
else:
if (self.pmc_flag == 0):
pop_df_temp['subsidy_elig_no_fpl'] = ( (pop_df_temp['affordability'] == False) & (pop_df_temp['mcaid_elig'] == 0) & \
(pop_df_temp['coverage'] != 3) & (pop_df_temp['coverage'] != 5)).astype(int)
pop_df_temp['adultkid_subsidy_elig_no_fpl'] = ( (pop_df_temp['self_affordability'] == False) & (pop_df_temp['mcaid_elig'] == 0) & \
(pop_df_temp['coverage'] != 3) & (pop_df_temp['coverage'] != 5)).astype(int)
else:
pop_df_temp['subsidy_elig_no_fpl'] = ((pop_df_temp['affordability'] == False) & (pop_df_temp['mcaid_elig'] == 0) & \
(pop_df_temp['coverage'] != 3) & (pop_df_temp['coverage'] != 5) & \
(pop_df_temp['predicted_elig'] == 0)).astype(int)
pop_df_temp['adultkid_subsidy_elig_no_fpl'] = ( (pop_df_temp['self_affordability'] == False) & (pop_df_temp['mcaid_elig'] == 0) & \
(pop_df_temp['coverage'] != 3) & (pop_df_temp['coverage'] != 5) & \
(pop_df_temp['predicted_elig'] == 0)).astype(int)
pop_df_temp['subsidy_elig'] = ((pop_df_temp['subsidy_elig_no_fpl'] == 1) & (pop_df_temp['fpl']>138.0) & \
(pop_df_temp['fpl'] <= self.fpl_limit)).astype(int)
#redefine loc_ind_adult_child
loc_ind_adult_child = np.where((pop_df_temp['person_type'] > 3) & (pop_df_temp['tax_dependent'] == 0))[0]
#tax independent adult_child
pop_df_temp.loc[loc_ind_adult_child, 'subsidy_elig'] = ((pop_df_temp.loc[loc_ind_adult_child, 'subsidy_elig_no_fpl'] == 1) & (pop_df_temp.loc[loc_ind_adult_child, 'ind_fpl'] > 138.0) & \
(pop_df_temp.loc[loc_ind_adult_child, 'ind_fpl'] <= self.fpl_limit)).astype(int)
pop_df_temp['adultkid_subsidy_elig']=((pop_df_temp['adultkid_subsidy_elig_no_fpl'] == 1) & (pop_df_temp['fpl']>138.0) & \
(pop_df_temp['fpl'] <= self.fpl_limit)).astype(int)
pop_df_temp['bronze_only'] = ((pop_df_temp['fpl']>self.fpl_limit_silver) & (pop_df_temp['subsidy_elig_no_fpl'] == 1)).astype(int)
pop_df_temp.loc[loc_ind_adult_child, 'bronze_only'] = ((pop_df_temp.loc[loc_ind_adult_child, 'ind_fpl'] > self.fpl_limit_silver) &(pop_df_temp.loc[loc_ind_adult_child, 'subsidy_elig_no_fpl'] ==1)).astype(int)
# step 3: family_cap on exchange premium
pop_df_temp['threshold']=np.interp(pop_df_temp['fpl'], self.fpl_bracket, self.p_limit)/100
pop_df_temp['family_cap']=pop_df_temp['fam_income']*pop_df_temp['threshold']
pop_df_temp['ind_threshold'] = np.interp(pop_df_temp['ind_fpl'], self.fpl_bracket, self.p_limit) / 100
pop_df_temp.loc[loc_ind_adult_child, 'family_cap'] = pop_df_temp.loc[loc_ind_adult_child, 'ind_income'] * pop_df_temp.loc[loc_ind_adult_child, 'ind_threshold']
pop_df_temp = pd.merge(pop_df_temp, self.age_table, on='age', how='left')
pop_df_temp = pd.merge(pop_df_temp, self.curr_rp[['region', 'silver_21', 'bronze_21']], on='region', how='left')
#based on national overall premium to calculate subsidy amount
pop_df_temp['age_rated_slv_prem'] = pop_df_temp['silver_21']*pop_df_temp['rating']
age_rated_slv_df = pop_df_temp.loc[pop_df_temp['subsidy_elig'] == True].groupby('hieu_id').agg('sum').reset_index()[['hieu_id','age_rated_slv_prem']]
age_rated_slv_df.rename(index=str, columns={'age_rated_slv_prem':'age_rated_temp'}, inplace=True)
pop_df_temp = pd.merge(pop_df_temp, age_rated_slv_df, on='hieu_id', how='left')
pop_df_temp['age_rated_temp'].fillna(0, inplace=True)
pop_df_temp['subsidy_amount_total'] = pop_df_temp['age_rated_temp'] - pop_df_temp['family_cap']
pop_df_temp['subsidy_amount_total']= pop_df_temp['subsidy_amount_total'].apply(lambda value: 0 if value != value or value < 0 else value)
if 'p_lower_default' in self.premium_sub.columns:
pop_df_temp['family_cap_default'] = pop_df_temp['fam_income'] * np.interp(pop_df_temp['fpl'],self.fpl_bracket, self.p_limit_alt) / 100
pop_df_temp['ind_threshold_default'] = np.interp(pop_df_temp['ind_fpl'], self.fpl_bracket, self.p_limit_alt) / 100
pop_df_temp.loc[loc_ind_adult_child, 'family_cap_default'] = pop_df_temp.loc[loc_ind_adult_child, 'ind_income'] * pop_df_temp.loc[loc_ind_adult_child, 'ind_threshold_default']
pop_df_temp.drop(['ind_threshold_default'], inplace=True, axis=1)
pop_df_temp['subsidy_amount_total_default'] = pop_df_temp['age_rated_temp'] - pop_df_temp['family_cap_default']
pop_df_temp['subsidy_amount_total_default'] = pop_df_temp['subsidy_amount_total_default'].apply(lambda value: 0 if value != value or value < 0 else value)
pop_df_temp['age_rated_bronze_prem'] = pop_df_temp['bronze_21'] * pop_df_temp['rating']
age_rated_bronze_df = pop_df_temp.loc[pop_df_temp['bronze_only'] == True].groupby('hieu_id').agg('sum').reset_index()[ ['hieu_id', 'age_rated_bronze_prem']]
age_rated_bronze_df.rename(index=str, columns={'age_rated_bronze_prem': 'age_rated_bronze_temp'},
inplace=True)
pop_df_temp = pd.merge(pop_df_temp, age_rated_bronze_df, on='hieu_id', how='left')
pop_df_temp['age_rated_bronze_temp'].fillna(0, inplace=True)
pop_df_temp['subsidy_bronze_total'] = pop_df_temp['age_rated_bronze_temp'] - pop_df_temp['family_cap']
pop_df_temp['subsidy_bronze_total'] = pop_df_temp['subsidy_bronze_total'].apply(
lambda value: 0 if value != value or value < 0 else value)
pop_df_temp.loc[pop_df_temp['bronze_only']==True,'subsidy_amount_total']=pop_df_temp.loc[pop_df_temp['bronze_only']==1,'subsidy_bronze_total']
pop_df_temp.drop(['age_rated_bronze_temp', 'age_rated_temp'], inplace=True, axis=1)
return pop_df_temp # returns both subsidy eligibility and family cap
[docs] def xc_premium_amount(self, pop_df, wrk_df, xc_prem, Curr_esi, choices, affordability_type, curr_av_value):
'''
returns choice table with extra columns for exchange premiums based on the choices
'''
# manifest constants
people = ['adult_1', 'adult_2', 'child_1', 'child_2', 'adult_child_1', 'adult_child_2', 'adult_child_3','adult_child_4']
# step 1: creating subsidy eligibility variable
my_pop_df =pop_df.copy()
mod_hieu = my_pop_df[['hieu_id', 'person_type', 'subsidy_elig', 'bronze_only', 'family_cap','age']]
if 'family_cap_default' in my_pop_df:
mod_hieu = my_pop_df[['hieu_id', 'person_type', 'subsidy_elig', 'bronze_only', 'family_cap', 'family_cap_default','age']]
# step 1.5 make age rating df
age_rating_df = pd.merge(mod_hieu, self.age_table, on='age')
# step 2.1: converting bronze_only to wide and merge to choice table
bronze_cols = ['bronze_only_' + str(i) for i in range(8)]
part1 = mod_hieu.pivot('hieu_id', 'person_type','bronze_only')
part1.columns = bronze_cols
part1 = part1.reset_index()
my_choices = pd.merge(choices, part1, on='hieu_id', how='left')
# step 2.2: converting subsidy_elig_ to wide and merge to choice table
subsidy_cols = ['subsidy_elig_' + str(i) for i in range(8)]
part2 = mod_hieu.pivot('hieu_id', 'person_type', 'subsidy_elig')
part2.columns = subsidy_cols
part2 = part2.reset_index()
my_choices = pd.merge(my_choices, part2, on='hieu_id', how='left')
# step 2.3: converting family_cap to wide and merge to choice table
cap_cols = ['family_cap_' + str(i) for i in range(8)]
part3 = mod_hieu.pivot('hieu_id', 'person_type', 'family_cap')
part3.columns = cap_cols
part3 = part3.reset_index()
my_choices = pd.merge(my_choices, part3, on='hieu_id', how='left')
my_choices = my_choices.fillna(0.0).astype(int)
# step 2.4(optional): converting family_cap_default to wide and merge to choice table
if 'family_cap_default' in my_pop_df:
cap_cols = ['family_cap_default_' + str(i) for i in range(8)]
part4 = mod_hieu.pivot('hieu_id', 'person_type', 'family_cap_default')
part4.columns = cap_cols
part4 = part4.reset_index()
my_choices = pd.merge(my_choices, part4, on='hieu_id', how='left')
my_choices = my_choices.fillna(0.0).astype(int)
# step 3: merging it with choice table
if 'subsidy_amount_total_default' in my_pop_df:
right = my_pop_df[['hieu_id', 'affordability','self_affordability','subsidy_amount_total','subsidy_amount_total_default','subsidy_bronze_total']]
else:
right = my_pop_df[['hieu_id', 'affordability','self_affordability','subsidy_amount_total', 'subsidy_bronze_total']]
right = right.drop_duplicates(['hieu_id'], keep='last')
# step 3.5 add age rating data to my_choices
my_choices = pd.merge(my_choices, right, on='hieu_id')
my_choices.index = my_choices.hieu_id
age_rating_df.index = age_rating_df.hieu_id
for i in range(8):
my_choices['rating_' + str(i)] = age_rating_df.loc[age_rating_df.person_type == i].rating
my_choices.reset_index(drop=True)
age_rating_df.reset_index(drop=True)
# step 4: preparing data for assigning exchange premiums
df_choices = my_choices[people]
# step 5: create a lambda function to easily look up the premium value,
# attempt regionals first, if fail proceed with statewide
#pdb.set_trace()
try:
df_region = my_choices['region'] - 1
#don't think we need the next two lines
# subsidy_mask = (my_choices[['subsidy_elig_%d' % i for i in range(8)]]).astype(bool)
# subsidy_mask.columns = people
sub_slv = (df_choices==6).astype(int).mul(df_region.map(self.curr_rp['silver_21']), axis=0)
sub_brz = (df_choices==7).astype(int).mul(df_region.map(self.curr_rp['bronze_21']), axis=0)
sub_cat = (df_choices==8).astype(int).mul(df_region.map(self.curr_rp['cat_21']), axis=0)
# finding unsubsidized premium for subsidized people to choose the minimum
unsub_slv = (df_choices==6).astype(int).mul(df_region.map(self.curr_rp['silver_21_uns']), axis=0)
unsub_brz = (df_choices==7).astype(int).mul(df_region.map(self.curr_rp['bronze_21_uns']), axis=0)
unsub_cat = (df_choices==8).astype(int).mul(df_region.map(self.curr_rp['cat_21_uns']), axis=0)
prem_lookup_sub = sub_slv + sub_brz + sub_cat
prem_lookup_un_sub = unsub_slv + unsub_brz + unsub_cat
except:
prem_lookup_sub = (df_choices==6).astype(int)*float(xc_prem.loc[6]) + \
(df_choices==7).astype(int)*float(xc_prem.loc[7]) + \
(df_choices==8).astype(int)*float(xc_prem.loc[8])
prem_lookup_un_sub = prem_lookup_sub
#print prem_lookup.notnull().describe()
# step 6: go through people 1-6 and add base premium column and apply age rating to calculation
for index, person in enumerate(people):
my_choices['temp_un_sub'] = prem_lookup_un_sub[person]
my_choices['temp_sub'] = prem_lookup_sub[person]
#my_choices['temp_prem'] = np.minimum( my_choices['temp_un_sub'],my_choices['temp_sub'])
# my_choices['xc_diff_' + str(index)] = my_choices['temp_sub'] > my_choices['temp_un_sub']
my_choices.loc[my_choices[person] == 6, 'age_rated_premium_' + str(index)] = \
my_choices.loc[my_choices[person]==6, 'temp_sub']* my_choices.loc[my_choices[person]== 6, 'rating_' + str(index)]
my_choices.loc[my_choices[person] == 6, 'xc_prem_un_sub_' + str(index)] = \
my_choices.loc[my_choices[person] == 6, 'temp_un_sub'] * my_choices.loc[my_choices[person] == 6, 'rating_' + str(index)]
my_choices.loc[my_choices[person] == 7, 'age_rated_premium_' + str(index)] = \
my_choices.loc[my_choices[person]==7, 'temp_sub']* my_choices.loc[my_choices[person]== 7, 'rating_' + str(index)]
my_choices.loc[my_choices[person] == 7, 'xc_prem_un_sub_' + str(index)] = \
my_choices.loc[my_choices[person] == 7, 'temp_un_sub'] * my_choices.loc[my_choices[person] == 7, 'rating_' + str(index)]
my_choices.loc[my_choices[person] == 8, 'age_rated_premium_' + str(index)] = \
my_choices.loc[my_choices[person]==8, 'temp_sub']* my_choices.loc[my_choices[person]== 8, 'rating_' + str(index)]
my_choices.loc[my_choices[person] == 8, 'xc_prem_un_sub_' + str(index)] = \
my_choices.loc[my_choices[person] == 8, 'temp_un_sub'] * my_choices.loc[my_choices[person] == 8, 'rating_' + str(index)]
my_choices['age_rated_premium_' + str(index)] = my_choices['age_rated_premium_' + str(index)].fillna(0)
my_choices['xc_prem_un_sub_' + str(index)] = my_choices['xc_prem_un_sub_' + str(index)].fillna(0)
# step 7 get correct premium depending on conditions: if eligible (minimum of family cap and age_rated), else age_rated
## temp columns are for "proxy premium for calculating wage change when firm add a person to ESI who are not on ESI ex-ante"
temp_cols = ['silver_premium_' + str(i) for i in range(8)]
# fixing exchange using family cap correctly
# calculating the total amount of subsidy a combined choice will get
# this corresponds to silver plan
# example, if one person chooses IM/Silver, the age_rated_premium = 5000 and family cap is 1000, then the total amount of
# subsidy will be 5000-1000 = 4000. For silver plan, this person will pay 1000.
# for bronze plan of total premium of 2500, say, this person will pay max(2500-4000, 0) = 0
# for the same family, if two people are on IM, let's say their age_rated_premiums are 5000 and 4000
# then for silver plan, person 1 will pay 1000*5/9 and second 1000*4/9, the subsidy amount for person 1 will be 5000-1000*5/9 = 4444
# and for the second person 4000-1000*4/9 = 3556
# at this point, for bronze plan of preimum 2500 and 2000, each person will pay max(bronze_age_rated_premium -subsidy_amount, 0)
# for person 1: max(2500-4444, 0) = 0 and person 2: max(2000-3556, 0) = 0 [xc, 4/19/2018]
# figuring out subsidy amount for each pattern and each person in the pattern, sliver first
silver_pattern = my_choices[my_choices['all_silver'] ==1]
silver_pattern.drop_duplicates(inplace=True)
silver_pattern.reset_index(inplace=True, drop=True)
# only collect subsidy eligible cases
for index, person in enumerate(people):
silver_pattern['age_sub_' + str(index)] = silver_pattern['age_rated_premium_' + str(index)] * silver_pattern['subsidy_elig_' + str(index)]
age_sub_cols = ['age_sub_' + str(i) for i in [0,1,4,5,6,7]]
silver_pattern['age_rated_prem_hieu_total'] = silver_pattern[age_sub_cols].sum(axis=1)
# children group 1, documented
silver_pattern['age_rated_prem_hieu_total'] += silver_pattern['age_sub_2'] * \
np.where(silver_pattern['n_person_2'] > 3, 3, silver_pattern['n_person_2'])
# children group 2, undocumented
silver_pattern['age_rated_prem_hieu_total'] += silver_pattern['age_sub_3'] * \
np.where(silver_pattern['n_person_3'] > 3, 3, silver_pattern['n_person_3'])
for index, person in enumerate(people):
silver_pattern['indiv_fam_cap_'+ str(index)] = 0
silver_pattern['indiv_fam_cap_' + str(index)][silver_pattern['subsidy_elig_'+str(index)]==1] = \
(silver_pattern['age_rated_premium_' + str(index)][silver_pattern['subsidy_elig_'+str(index)]==1] / \
silver_pattern['age_rated_prem_hieu_total'][silver_pattern['subsidy_elig_'+str(index)]==1]) * silver_pattern['family_cap_'+str(0)][silver_pattern['subsidy_elig_'+str(index)]==1]
if index>3:
loc_tax_indpendent_i = np.where((silver_pattern['tax_dependent_' + str(index)] == 0) &(silver_pattern['subsidy_elig_' + str(index)] == 1))[0]
silver_pattern.loc[loc_tax_indpendent_i,'indiv_fam_cap_'+ str(index)]= silver_pattern.loc[loc_tax_indpendent_i,'family_cap_'+ str(index)]
silver_pattern['sub_amount_' + str(index)] = silver_pattern['age_rated_premium_' + str(index)] - silver_pattern['indiv_fam_cap_' + str(index)]
silver_pattern['sub_amount_' + str(index)][silver_pattern['subsidy_elig_' + str(index)]==0] = 0
silver_pattern['sub_amount_' + str(index)] = silver_pattern['sub_amount_' + str(index)].apply(lambda value: 0 if value != value or value < 0 else value)
if 'p_lower_default' in self.premium_sub.columns:
silver_pattern['indiv_fam_cap_default' + str(index)] = 0
silver_pattern['indiv_fam_cap_default' + str(index)][silver_pattern['subsidy_elig_' + str(index)] == 1] = \
(silver_pattern['age_rated_premium_' + str(index)][silver_pattern['subsidy_elig_' + str(index)] == 1] / \
silver_pattern['age_rated_prem_hieu_total'][silver_pattern['subsidy_elig_' + str(index)] == 1]) * silver_pattern['family_cap_default_'+str(0)][silver_pattern['subsidy_elig_' + str(index)] == 1]
if index > 3:
loc_tax_indpendent_i = np.where((silver_pattern['tax_dependent_' + str(index)] == 0) & ( silver_pattern['subsidy_elig_' + str(index)] == 1))[0]
silver_pattern.loc[loc_tax_indpendent_i, 'indiv_fam_cap_default' + str(index)] = silver_pattern.loc[loc_tax_indpendent_i, 'family_cap_default_' + str(index)]
silver_pattern['sub_amount_default' + str(index)] = silver_pattern['age_rated_premium_' + str(index)] - silver_pattern['indiv_fam_cap_default' + str(index)]
silver_pattern['sub_amount_default' + str(index)][silver_pattern['subsidy_elig_' + str(index)] == 0] = 0
silver_pattern['sub_amount_default' + str(index)] = silver_pattern['sub_amount_default' + str(index)].apply(lambda value: 0 if value != value or value < 0 else value)
if 'p_lower_default' in self.premium_sub.columns:
sub_cols = ['sub_amount_' + str(i) for i in range(8)] + ['sub_amount_default' + str(i) for i in range(8)]+['indiv_fam_cap_' + str(i) for i in range(8)]+['indiv_fam_cap_default' + str(i) for i in range(8)]
else:
sub_cols = ['sub_amount_' + str(i) for i in range(8)]+['indiv_fam_cap_' + str(i) for i in range(8)]
#pdb.set_trace()
silver_pattern[sub_cols[index]].fillna(0, inplace=True)
silver_pattern = silver_pattern[sub_cols + ['hieu_id', 'xc_pattern']]
silver_pattern.drop_duplicates(inplace=True)
if (self.calc_bronze_sub_amount == True):
bronze_pattern = my_choices[my_choices['all_bronze'] ==1]
bronze_pattern.drop_duplicates(inplace=True)
bronze_pattern.reset_index(inplace=True, drop=True)
# only collect bronze subsidy eligible cases
for index, person in enumerate(people):
bronze_pattern['age_sub_' + str(index)] = bronze_pattern['age_rated_premium_' + str(index)] * bronze_pattern['bronze_only_' + str(index)]
age_sub_cols = ['age_sub_' + str(i) for i in [0,1,4,5,6,7]]
bronze_pattern['age_rated_prem_hieu_total'] = bronze_pattern[age_sub_cols].sum(axis=1)
# children group 1, documented
bronze_pattern['age_rated_prem_hieu_total'] += bronze_pattern['age_sub_2'] * \
np.where(bronze_pattern['n_person_2'] > 3, 3, bronze_pattern['n_person_2'])
# children group 2, undocumented
bronze_pattern['age_rated_prem_hieu_total'] += bronze_pattern['age_sub_3'] * \
np.where(bronze_pattern['n_person_3'] > 3, 3, bronze_pattern['n_person_3'])
for index, person in enumerate(people):
bronze_pattern['indiv_fam_cap_bronze_'+ str(index)] = 0
bronze_pattern['indiv_fam_cap_bronze_' + str(index)][bronze_pattern['bronze_only_'+str(index)]==1] = \
(bronze_pattern['age_rated_premium_' + str(index)][bronze_pattern['bronze_only_'+str(index)]==1] / \
bronze_pattern['age_rated_prem_hieu_total'][bronze_pattern['bronze_only_'+str(index)]==1]) * \
bronze_pattern['family_cap_'+str(0)][bronze_pattern['bronze_only_'+str(index)]==1]
if index > 3:
loc_tax_indpendent_i = np.where((bronze_pattern['tax_dependent_' + str(index)] == 0) & ( bronze_pattern['bronze_only_' + str(index)] == 1))[0]
bronze_pattern.loc[loc_tax_indpendent_i, 'indiv_fam_cap_bronze_' + str(index)] = bronze_pattern.loc[loc_tax_indpendent_i, 'family_cap_' + str(index)]
bronze_pattern['sub_amount_bronze_' + str(index)] = bronze_pattern['age_rated_premium_' + str(index)] - bronze_pattern['indiv_fam_cap_bronze_' + str(index)]
bronze_pattern['sub_amount_bronze_' + str(index)][bronze_pattern['bronze_only_' + str(index)]==0] = 0
bronze_pattern['sub_amount_bronze_' + str(index)] = bronze_pattern['sub_amount_bronze_' + str(index)].apply(lambda value: 0 if value != value or value < 0 else value)
if 'p_lower_default' in self.premium_sub.columns:
bronze_pattern['indiv_fam_cap_default_bronze' + str(index)] = 0
bronze_pattern['indiv_fam_cap_default_bronze' + str(index)][
bronze_pattern['bronze_only_' + str(index)] == 1] = (bronze_pattern['age_rated_premium_' + str(index)][bronze_pattern['bronze_only_' + str(index)] == 1] / \
bronze_pattern['age_rated_prem_hieu_total'][bronze_pattern['bronze_only_' + str(index)] == 1]) * \
bronze_pattern['family_cap_default_'+str(0)][bronze_pattern['bronze_only_' + str(index)] == 1]
if index > 3:
loc_tax_indpendent_i = np.where((bronze_pattern['tax_dependent_' + str(index)] == 0) & ( bronze_pattern['bronze_only_' + str(index)] == 1))[0]
bronze_pattern.loc[loc_tax_indpendent_i, 'indiv_fam_cap_bronze_' + str(index)] = bronze_pattern.loc[loc_tax_indpendent_i, 'family_cap_default_' + str(index)]
bronze_pattern['sub_amount_default_bronze' + str(index)] = bronze_pattern['age_rated_premium_' + str(index)] - bronze_pattern['indiv_fam_cap_default_bronze' + str(index)]
bronze_pattern['sub_amount_default_bronze' + str(index)][bronze_pattern['bronze_only_' + str(index)] == 0] = 0
bronze_pattern['sub_amount_default_bronze' + str(index)] = bronze_pattern['sub_amount_default_bronze' + str(index)].apply(lambda value: 0 if value != value or value < 0 else value)
if 'p_lower_default' in self.premium_sub.columns:
sub_cols = ['sub_amount_bronze_' + str(i) for i in range(8)] + ['sub_amount_default_bronze' + str(i) for i in range(8)]+['indiv_fam_cap_bronze_' + str(i) for i in range(8)]+['indiv_fam_cap_default_bronze' + str(i) for i in range(8)]
else:
sub_cols = ['sub_amount_bronze_' + str(i) for i in range(8)]+['indiv_fam_cap_bronze_' + str(i) for i in range(8)]
bronze_pattern[sub_cols[index]].fillna(0, inplace=True)
bronze_pattern = bronze_pattern[sub_cols + ['hieu_id', 'xc_pattern']+['bronze_only_' + str(i) for i in range(8)]]
bronze_pattern.drop_duplicates(inplace=True)
silver_and_bronze=pd.merge(silver_pattern,bronze_pattern,on=['hieu_id', 'xc_pattern'], how='left')
for index, person in enumerate(people):
silver_and_bronze['sub_amount_' + str(index)] =silver_and_bronze['sub_amount_' + str(index)]*(1-silver_and_bronze['bronze_only_'+ str(index)]) \
+silver_and_bronze['sub_amount_bronze_' + str(index)]*silver_and_bronze['bronze_only_'+ str(index)]
silver_and_bronze['indiv_fam_cap_' + str(index)] = silver_and_bronze['indiv_fam_cap_' + str(index)] * ( 1 - silver_and_bronze['bronze_only_' + str(index)]) \
+ silver_and_bronze['indiv_fam_cap_bronze_' + str(index)] * silver_and_bronze['bronze_only_' + str(index)]
#if 'p_lower_default' in self.premium_sub.columns:
# silver_and_bronze['sub_amount_default' + str(index)] = silver_and_bronze['sub_amount_default' + str(index)] * (1 - silver_and_bronze['bronze_only_' + str(index)]) \
#+ silver_and_bronze['sub_amount_default_bronze' + str(index)] * silver_and_bronze['bronze_only_' + str(index)]
#silver_and_bronze['indiv_fam_cap_default' + str(index)] = silver_and_bronze[ 'indiv_fam_cap_default' + str(index)] * (1 - silver_and_bronze[ 'bronze_only_' + str(index)]) \
#+ silver_and_bronze['indiv_fam_cap_default_bronze' + str(index)] * silver_and_bronze['bronze_only_' + str(index)]
if 'p_lower_default' in self.premium_sub.columns:
sub_cols = ['sub_amount_' + str(i) for i in range(8)] + ['sub_amount_default' + str(i) for i in range(8)] + ['indiv_fam_cap_' + str(i) for i in range(8)] + ['indiv_fam_cap_default' + str(i) for i in range(8)]
else:
sub_cols = ['sub_amount_' + str(i) for i in range(8)] + ['indiv_fam_cap_' + str(i) for i in range(8)]
silver_and_bronze = silver_and_bronze[sub_cols + ['hieu_id', 'xc_pattern']]
else:
silver_and_bronze = silver_pattern
my_choices = pd.merge(my_choices, silver_and_bronze, on=['hieu_id', 'xc_pattern'], how='left')
my_choices[sub_cols]=my_choices[sub_cols].fillna(0)
# applying sub_amount to age_rated_premiums
for index, person in enumerate(people):
#eligible people
my_choices['xc_prem_' + str(index)] = my_choices['age_rated_premium_' + str(index)] - my_choices['sub_amount_' + str(index)]
#creating an indicator column only for people who are eligibile for subsidy
#if indicatorB-indicatorA = 1, loaded subsidized premium is cheaper than unloaded premium
#if indicatorB-indicatorA = -1, unloaded premium is cheaper
indicatorA = my_choices[my_choices['sub_amount_' + str(index)] > 0]['xc_prem_' + str(index)] > my_choices[my_choices['sub_amount_' + str(index)] > 0]['xc_prem_un_sub_' + str(index)]
indicatorA = indicatorA.astype(int)
indicatorB = my_choices[my_choices['sub_amount_' + str(index)] > 0]['xc_prem_' + str(index)] < my_choices[my_choices['sub_amount_' + str(index)] > 0]['xc_prem_un_sub_' + str(index)]
indicatorB = indicatorB.astype(int)
indicator = indicatorB - indicatorA
#if unloaded, unsubsized cost = loaded, subsidized cost
# then we replace '0' with '2'
indicator = indicator.replace(0, 2)
indicator = indicator.reset_index()
indicator.columns = ['unique_id', 'sub_chosen_' + str(index)]
#generating indicator values for each row
#if row (hieu_id + choice) isnt eligible for subsidy
#then we generate 0 in that column
if(not indicator.empty):
my_choices = pd.merge(my_choices, indicator, how = 'left', left_index=True, right_on = 'unique_id')
my_choices = my_choices.set_index('unique_id')
del my_choices.index.name
my_choices['sub_chosen_' + str(index)] = (my_choices['sub_chosen_' + str(index)].fillna(0)).astype(int)
else:
my_choices['sub_chosen_' + str(index)] = 0
my_choices['xc_prem_' + str(index)]=np.minimum(my_choices['xc_prem_' + str(index)],my_choices['xc_prem_un_sub_' + str(index)])
# either zero or > 0
my_choices['xc_diff_' + str(index)] = my_choices['xc_prem_' + str(index)] > my_choices['xc_prem_un_sub_' + str(index)]
my_choices['xc_prem_' + str(index)] = my_choices['xc_prem_' + str(index)].apply(lambda value: 0 if value != value or value < 0 else value)
# proxy premium
temp = my_choices[my_choices[person]==6][['hieu_id', 'xc_prem_'+str(index)]]
temp[temp_cols[index]] = temp['xc_prem_'+str(index)] * (curr_av_value if curr_av_value else .7)/.7
temp_p = temp[['hieu_id', temp_cols[index]]].groupby('hieu_id').max().reset_index()
my_choices = pd.merge(my_choices, temp_p, on='hieu_id', how='left')
my_choices[temp_cols[index]].fillna(0, inplace=True)
#pdb.set_trace()
my_choices['exchange_prem'] = my_choices[['xc_prem_'+ str(i) for i in range(8)]].sum(axis=1)
return my_choices
[docs] def proxy_premium(self, choices):
'''
appending 8 extra columns to choioce table for AV value adjusted exchange preimum (subsidized)
the 8 columns are not choice specific, since it will be used only for calculating esi premiums
'''
people = ['adult_1', 'adult_2', 'child_1', 'child_2', 'adult_child_1', 'adult_child_2', 'adult_child_3','adult_child_4']
temp_cols = ['silver_premium_' + str(i) for i in range(8)]
mychoices = choices
for index, person in enumerate(people):
# step 1: getting premium for choice = 6
# step 2: adjusting to av value
# step 3: assign it to each person, regardless choices
temp = choices[choices[person]==6][['hieu_id', 'av_value', 'xc_prem_'+str(index)]]
temp[temp_cols[index]] = temp['xc_prem_'+str(index)] * temp['av_value'].fillna(.7)/.7
temp_p = temp[['hieu_id', temp_cols[index]]].groupby('hieu_id').max().reset_index()
mychoices = pd.merge(mychoices, temp_p, on='hieu_id', how='left')
mychoices[temp_cols[index]].fillna(0, inplace=True)
return mychoices
[docs] def affordability(self, pop_df, wrk_df, Curr_esi, affordability_type):
"""
based on affordability definition
affordability_type = 0 --> single
affordability_type = 1 --> family
affordability_type = 2 --> hybrid
input:
df of population; must include person_id
wrk_df; must include person_id, part_time status, and firm_id
Curr_esi; must part_time; av_value
self.esi_thresh is the threshold for affordability (0.0956)
output:
8 by n matrix;
at least 1 adult (not adult kid) or that individual has affordable esi
"""
if (affordability_type == 0):
return(self.esi_afford_single(pop_df,wrk_df, Curr_esi))
elif (affordability_type==1):
return(self.esi_afford_family(pop_df,wrk_df, Curr_esi))
elif (affordability_type==2):
return(self.esi_afford_hybrid(pop_df,wrk_df, Curr_esi))
[docs] def esi_afford_single(self, pop_df, wrk_df, Curr_esi):
"""
input:
pop_df:
wrk_df: person_id, part_time status, and firm_id
Curr_esi: must have part_time; av_value
self.esi_thresh is the threshold for affordability (0.0956)
output:
pop_df with one extra column: if a person has affordable esi coverage
programmer:
xiao chen, March 19, 2017
"""
# step 1: if any individual worker has affordable esi
#merge worker table with Curr_esi
wrk_esi = pd.merge(wrk_df[['person_id','full_time','firm_id', 'hieu_id','esi_not_elig']],\
Curr_esi[['firm_id','pt', 'av_value', 'premium_self_actual', 'contr_p_self']], on = 'firm_id')
#merge family income
df = pd.merge(wrk_esi, pop_df[['person_id', 'fam_income','person_type','ind_income']], on = 'person_id', how = 'left')
df['afford'] = False
df['self_affordability']=np.nan
df['afford_part1'] = np.where((np.isnan(df['av_value'])==False) & (df['av_value']>=self.av_threshold) & \
(self.esi_thresh*df['fam_income'] >= df['premium_self_actual']*df['contr_p_self'])&(df['esi_not_elig'] ==False) & \
(df['full_time'] ==1), True, False)
df['afford_part2'] = np.where((np.isnan(df['av_value'])==False) & (df['av_value']>=self.av_threshold) & \
(self.esi_thresh*df['fam_income'] >= df['premium_self_actual']*df['contr_p_self'])&(df['esi_not_elig'] ==False) & \
(df['full_time'] ==0) & (df['pt']==1), True, False)
df['self_afford_part1']=np.where((np.isnan(df['av_value'])==False) & (df['av_value']>=self.av_threshold) & \
(self.esi_thresh*df['ind_income'] >= df['premium_self_actual']*df['contr_p_self'])&(df['esi_not_elig'] ==False) & \
(df['full_time'] == 1) & (df['person_type'] > 3) , True, False)
df['self_afford_part2'] = np.where((np.isnan(df['av_value']) == False) & (df['av_value'] >= self.av_threshold) & \
(self.esi_thresh * df['ind_income'] >= df['premium_self_actual'] * df[ 'contr_p_self'])&(df['esi_not_elig'] ==False) & \
(df['full_time'] == 0) & (df['pt'] == 1)& (df['person_type'] > 3) , True, False)
df['afford'] = df['afford'] | df['afford_part1'] | df['afford_part2']
df['self_affordability']= df['self_afford_part1'] | df['self_afford_part2']
list = ['afford_part1','afford_part2','self_afford_part1','self_afford_part2']
df.drop(list,inplace=True,axis=1)
# step 2: at hieu level, if any adult_1 or adult_2 has afford == True
# if so, children will also have affordable ESI, this should be discussed [xc, 03/2017]
my_pop_df = pd.merge(pop_df, df[['person_id', 'afford']], on = 'person_id', how = 'left')
my_pop_df['affordability'] = False
my_pop_df = my_pop_df.drop_duplicates(['hieu_id','person_type'])
parent_afford = my_pop_df.pivot('hieu_id', 'person_type', 'afford')
parent_afford.fillna(value=np.nan, inplace=True)
parent_afford.columns = ['afford_' + str(i) for i in range(8)]
parent_afford = parent_afford.reset_index()
parent_afford['affordability'] = np.where((parent_afford['afford_0']==True) | (parent_afford['afford_1']==True), True, False)
my_pop_df = pd.merge(pop_df, parent_afford[['hieu_id', 'affordability']], on = 'hieu_id', how = 'left')
loc_adult_child=np.where((my_pop_df['person_type']>3)&(my_pop_df['tax_dependent']==0))[0]
my_pop_df = pd.merge(my_pop_df, df[['person_id', 'self_affordability']], on='person_id', how='left')
my_pop_df.loc[loc_adult_child,'affordability']=my_pop_df.loc[loc_adult_child].self_affordability
return(my_pop_df)
[docs] def esi_afford_family(self, pop_df, wrk_df, Curr_esi):
wrk_esi = pd.merge(wrk_df[['person_id', 'full_time', 'firm_id', 'family_id','esi_not_elig']], \
Curr_esi[['firm_id', 'pt', 'av_value', 'premium_self_actual', 'contr_p_self',
'premium_fam_actual', 'contr_p_fam', 'contr_p_sp', 'contr_p_ch',
'premium_sp_actual', 'premium_ch_actual']], on='firm_id')
df = pd.merge(wrk_esi, pop_df[['person_id', 'fam_income', 'person_type', 'prem_type','family_type_for_afford_esi','ind_income']], on='person_id',
how='left')
df['afford'] = False
df['afford_family_type']=False
df['fam_afford_part1'] = np.where((np.isnan(df['av_value']) == False) & (df['av_value'] >= self.av_threshold)&(df['esi_not_elig'] ==False) & \
(self.esi_thresh * df['fam_income'] >= df['premium_fam_actual'] * df['contr_p_fam']) & (df['full_time'] == 1), True, False)
df['fam_afford_part2'] = np.where((np.isnan(df['av_value']) == False) & (df['av_value'] >= self.av_threshold)&(df['esi_not_elig'] ==False) & \
(self.esi_thresh * df['fam_income'] >= df['premium_fam_actual'] * df[
'contr_p_fam']) & (df['full_time'] == 0) & (df['pt'] == 1), True, False)
df['self_afford_part1'] = np.where((np.isnan(df['av_value']) == False) & (df['av_value'] >= self.av_threshold)&(df['esi_not_elig'] ==False) & \
(self.esi_thresh * df['fam_income'] >= df['premium_self_actual'] * df[
'contr_p_self']) & (df['full_time'] == 1), True, False)
df['self_afford_part2'] = np.where((np.isnan(df['av_value']) == False) & (df['av_value'] >= self.av_threshold)&(df['esi_not_elig'] ==False) & \
(self.esi_thresh * df['fam_income'] >= df['premium_self_actual'] * df[
'contr_p_self']) & (df['full_time'] == 0) & (df['pt'] == 1), True, False)
df['sp_afford_part1'] = np.where((np.isnan(df['av_value']) == False) & (df['av_value'] >= self.av_threshold) &(df['esi_not_elig'] ==False)& \
(self.esi_thresh * df['fam_income'] >= df['premium_sp_actual'] * df[
'contr_p_sp']) & (df['full_time'] == 1), True, False)
df['sp_afford_part2'] = np.where((np.isnan(df['av_value']) == False) & (df['av_value'] >= self.av_threshold)&(df['esi_not_elig'] ==False) & \
(self.esi_thresh * df['fam_income'] >= df['premium_sp_actual'] * df[
'contr_p_sp']) & (df['full_time'] == 0) & (df['pt'] == 1), True, False)
df['ch_afford_part1'] = np.where((np.isnan(df['av_value']) == False) & (df['av_value'] >= self.av_threshold)&(df['esi_not_elig'] ==False) & \
(self.esi_thresh * df['fam_income'] >= df['premium_ch_actual'] * df[
'contr_p_ch']) & (df['full_time'] == 1), True, False)
df['ch_afford_part2'] = np.where((np.isnan(df['av_value']) == False) & (df['av_value'] >= self.av_threshold)&(df['esi_not_elig'] ==False) & \
(self.esi_thresh * df['fam_income'] >= df['premium_ch_actual'] * df[
'contr_p_ch']) & (df['full_time'] == 0) & (df['pt'] == 1), True, False)
df['adult_child_afford_part1'] = np.where((np.isnan(df['av_value']) == False) & (df['av_value'] >= self.av_threshold) & (df['esi_not_elig'] == False) & \
(self.esi_thresh * df['ind_income'] >= df['premium_self_actual'] * df[
'contr_p_self']) & (df['full_time'] == 1), True, False)
df['adult_child_afford_part2'] = np.where((np.isnan(df['av_value']) == False) & (df['av_value'] >= self.av_threshold) & (df['esi_not_elig'] == False) & \
(self.esi_thresh * df['ind_income'] >= df['premium_self_actual'] * df[
'contr_p_self']) & (df['full_time'] == 0) & (df['pt'] == 1), True, False)
df['fam_afford'] = df['fam_afford_part1'] | df['fam_afford_part2']
df['self_afford'] = df['self_afford_part1'] | df['self_afford_part2']
df['sp_afford'] = df['sp_afford_part1'] | df['sp_afford_part2']
df['ch_afford'] = df['ch_afford_part1'] | df['ch_afford_part2']
df['adult_child_afford'] = df['adult_child_afford_part1'] | df['adult_child_afford_part2']
#temproray code
loc_fam_temp = np.where((df['family_type_for_afford_esi'] == 4))[0]
loc_self_temp = np.where((df['family_type_for_afford_esi'] == 1))[0]
loc_sp_temp = np.where((df['family_type_for_afford_esi'] == 2))[0]
loc_ch_temp = np.where((df['family_type_for_afford_esi'] == 3))[0]
loc_not_elig=np.where((df['family_type_for_afford_esi'] == 5))[0]
df.loc[loc_fam_temp, 'afford'] = df.loc[loc_fam_temp].fam_afford
df.loc[loc_self_temp, 'afford'] = df.loc[loc_self_temp].self_afford
df.loc[loc_sp_temp, 'afford'] = df.loc[loc_sp_temp].sp_afford
df.loc[loc_ch_temp, 'afford'] = df.loc[loc_ch_temp].ch_afford
df.loc[loc_not_elig, 'afford'] = False
list = ['fam_afford_part1', 'fam_afford_part2', 'self_afford_part1', 'self_afford_part2', 'sp_afford_part1',
'sp_afford_part2', 'ch_afford_part1', 'ch_afford_part2', 'fam_afford', 'self_afford', 'sp_afford',
'ch_afford']
df.drop(list, inplace=True, axis=1)
afford_temp = pd.merge(pop_df, df[['person_id', 'afford']], on='person_id', how='left')
afford_temp = afford_temp.drop_duplicates(['hieu_id', 'person_type'])
parent_afford = afford_temp.pivot('hieu_id', 'person_type', 'afford')
parent_afford.columns = ['afford_' + str(i) for i in range(8)]
parent_afford = parent_afford.reset_index()
parent_afford['affordability'] = np.where(
(parent_afford['afford_0'] == True) | (parent_afford['afford_1'] == True), True, False)
my_pop_df = pd.merge(pop_df, parent_afford[['hieu_id', 'affordability']], on='hieu_id', how='left')
loc_ind_adult_child = np.where((my_pop_df['person_type'] > 3) & (my_pop_df['tax_dependent'] == 0))[0]
my_pop_df = pd.merge(my_pop_df, df[['person_id', 'adult_child_afford']], on='person_id', how='left')
my_pop_df.loc[loc_ind_adult_child,'affordability']= my_pop_df.loc[loc_ind_adult_child].adult_child_afford
my_pop_df.affordability.fillna(False,inplace=True)
list = ['adult_child_afford']
my_pop_df.drop(list, inplace=True, axis=1)
my_pop_df['self_affordability'] = np.nan
my_pop_df.affordability.fillna(False,inplace=True)
return (my_pop_df)
[docs] def esi_afford_hybrid(self, pop_df, wrk_df, Curr_esi):
esi_afford_family_offer = self.esi_afford_family(pop_df, wrk_df, Curr_esi)
esi_afford_family_offer.rename(columns={'affordability':'family_affordability'},inplace=True)
wrk_esi = pd.merge(wrk_df[['person_id', 'full_time', 'firm_id', 'family_id','esi_not_elig']],Curr_esi[['firm_id', 'pt', 'av_value', 'premium_self_actual', 'contr_p_self','premium_fam_actual', 'contr_p_fam']], on='firm_id')
df = pd.merge(wrk_esi, pop_df[['person_id', 'fam_income', 'person_type','ind_income']], on='person_id', how='left')
# step 2: for all worker if they have affordable self_esi
df['self_afford'] = False
df['adult_child_afford']=False
df['self_afford_part1'] = np.where((np.isnan(df['av_value']) == False) & (df['av_value'] >= self.av_threshold)&(df['esi_not_elig'] ==False) & \
(self.esi_thresh * df['fam_income'] >= df['premium_self_actual'] * df[
'contr_p_self']) & (df['full_time'] == 1), True, False)
df['self_afford_part2'] = np.where((np.isnan(df['av_value']) == False) & (df['av_value'] >= self.av_threshold)&(df['esi_not_elig'] ==False) & \
(self.esi_thresh * df['fam_income'] >= df['premium_self_actual'] * df[
'contr_p_self']) & (df['full_time'] == 0) & (df['pt'] == 1), True, False)
df['adult_child_afford_part1'] = np.where((np.isnan(df['av_value']) == False) & (df['av_value'] >= self.av_threshold)&(df['esi_not_elig'] ==False) & \
(self.esi_thresh * df['ind_income'] >= df['premium_self_actual'] * df[
'contr_p_self']) & (df['full_time'] == 1), True, False)
df['adult_child_afford_part2'] = np.where((np.isnan(df['av_value']) == False) & (df['av_value'] >= self.av_threshold)&(df['esi_not_elig'] ==False) & \
(self.esi_thresh * df['ind_income'] >= df['premium_self_actual'] * df[
'contr_p_self']) & (df['full_time'] == 0) & (df['pt'] == 1), True, False)
df['self_afford'] = df['self_afford'] | df['self_afford_part1'] | df['self_afford_part2']
df['adult_child_afford']=df['adult_child_afford'] | df['adult_child_afford_part1'] | df['adult_child_afford_part2']
list = ['self_afford_part1', 'self_afford_part2','adult_child_afford_part1','adult_child_afford_part2']
df.drop(list, inplace=True, axis=1)
my_pop_df = pd.merge(pop_df, df[['person_id', 'self_afford','adult_child_afford']], on='person_id', how='left')
my_pop_df['affordability']=False
#step 3 : merge affordable family_esi and affordable self_esi information
my_pop_df = pd.concat([my_pop_df, esi_afford_family_offer['family_affordability']], axis=1)
loc_parents=np.where((my_pop_df['person_type']==0)|(my_pop_df['person_type']==1))[0]
loc_children=np.where((my_pop_df['person_type']==2)|(my_pop_df['person_type']==3))[0]
loc_adultchild=np.where(my_pop_df['person_type']>3)[0]
#step4 :For different kinds of person types, calculate their affordablity according to algorithm
my_pop_df.loc[loc_parents,'affordability']= (my_pop_df.loc[loc_parents].family_affordability)|(my_pop_df.loc[loc_parents].self_afford)
my_pop_df.loc[loc_children, 'affordability']=my_pop_df.loc[loc_children].family_affordability
my_pop_df.tax_dependent = my_pop_df.tax_dependent.astype('bool')
my_pop_df.loc[loc_adultchild, 'dep_affordability']=((my_pop_df.loc[loc_adultchild].tax_dependent) &(my_pop_df.loc[loc_adultchild].family_affordability))
my_pop_df.loc[loc_adultchild, 'indep_affordability'] = ((my_pop_df.loc[loc_adultchild].tax_dependent==False) & (my_pop_df.loc[loc_adultchild].adult_child_afford))
my_pop_df.loc[loc_adultchild, 'affordability']= (my_pop_df.loc[loc_adultchild].dep_affordability)|(my_pop_df.loc[loc_adultchild].indep_affordability)
list = ['dep_affordability', 'indep_affordability']
my_pop_df.drop(list, inplace=True, axis=1)
my_pop_df['self_affordability'] = np.nan
return (my_pop_df)