In [1]:
import pandas as pd
from matplotlib import pyplot as plt
import re
Dataset Benefits¶
In [227]:
benefits = pd.read_csv("../../data/BenefitsCostSharing.csv",dtype={'BenefitName':'str',
'CoinsInnTier1':'str',
'CopayInnTier1':'str',
'ImportDate':'str'})
In [228]:
def eda(dataframe):
missing_value = pd.DataFrame(dataframe.isnull().sum(), columns = ['IsNull'])
typevals = pd.DataFrame(dataframe.dtypes, columns = ['DType'])
print "dataframe index \n", dataframe.index
print "dataframe shape \n", dataframe.shape
print "dataframe describe \n", dataframe.describe()
nunique = []
for i in dataframe:
nunique.append([i,dataframe[i].nunique()])
nunique = pd.DataFrame(nunique, columns = ['index','NUnique'])
nunique.set_index('index', inplace = True)
return pd.concat([missing_value, typevals, nunique], axis=1)
eda(benefits)
Out[228]:
In [49]:
busrules = pd.read_csv("../../data/BusinessRules.csv")
In [52]:
eda(busrules)
Out[52]:
In [53]:
planatts = pd.read_csv("../../data/PlanAttributes.csv")
In [54]:
eda(planatts)
Out[54]:
Let's start looking into the datasets¶
- Replace all NA data with zeros
- Try to remove all % signs and see the coverage
- Check frequency of benefits
In [206]:
benefits.fillna('0', inplace = True)
print benefits.shape
benefits.head()
Out[206]:
In [245]:
# def extractor(i):
# try:
# return int(re.findall(r'\d+', i))
# except:
# return 0
# benefits['CoinsInnTier1'].apply(extractor, converty_type = 'int')
In [246]:
benefits['Deductible'] = benefits.CoinsInnTier1.str.contains('deductible', case = False)
In [247]:
benefits.Deductible.value_counts()
Out[247]:
In [282]:
#The number of records for each year
benefits.groupby('BusinessYear')['BenefitName'].count()
Out[282]:
In [392]:
print '- Number of individual benefit plans: ', len(benefits.groupby('PlanId')['BusinessYear'].count())
print '\n- Average number of benefits per plan: ', benefits.shape[0]/benefits.PlanId.nunique()
benefits.groupby('PlanId')['BusinessYear'].count().hist(figsize = (12,8), color = 'green')
plt.title('How many plans coverting a certain number of benefits, \n Years 2014 - 2016')
_ = plt.xlabel('Number of benefits covered by insurance')
_ = plt.ylabel('Frequencey of insurance plans with a certain coverage')
plt.show()
In [251]:
# Checking a cross-section with a single state (Missour in this case)
benefits.groupby(['StateCode','IssuerId'])['IssuerId'].count().xs('MO',axis = 0)
Out[251]:
In [327]:
z = pd.DataFrame(benefits.groupby(['StateCode','IssuerId'])['IssuerId'].count())
# z.to_csv('../../data/groupbystateissuerid.csv')
In [328]:
benefits.groupby(['StateCode','IssuerId'])['IssuerId'].count().to_frame(name = 'z').reset_index(inplace = True)
z.reset_index(level=0, inplace=True)
In [329]:
z.head()
Out[329]:
In [330]:
z_1 = pd.DataFrame(benefits.groupby(['StateCode'])['IssuerId'].count())
z_1.reset_index(inplace=True)
z_1.head()
Out[330]:
In [331]:
type(z_1)
Out[331]:
In [332]:
z_concat = z.merge(z_1, how = 'outer', left_on='StateCode', right_on='StateCode')
In [333]:
z_concat['Share'] = z_concat.IssuerId_x/z_concat.IssuerId_y
z_concat.head()
Out[333]:
In [592]:
a = z_concat[z_concat.Share>.10].groupby('StateCode').count()
a.to_csv('../../data/numberofinscons.csv')
a.Share.head()
Out[592]:
In [593]:
rates_1.head(3)
Out[593]:
In [608]:
meanrates = pd.DataFrame(rates_1.groupby('StateCode')['IndividualRate'].mean())
meanrates.head()
Out[608]:
In [609]:
c = pd.concat([a.Share,meanrates], axis = 1)
c.head()
Out[609]:
In [610]:
plt.figure(figsize = (12,8))
plt.scatter(c.Share, c.IndividualRate, color = 'green')
_ = plt.title("Number of Major Insurers per State vs Individual Premium ")
plt.show()
In [106]:
# benefits.fillna(0, inplace = True)
Note: </br> Check what share of all observations do take the first 100 benefits.
In [337]:
benefits.shape
Out[337]:
In [338]:
benefits.BenefitName.value_counts(sort=True)[:100].sum()
Out[338]:
In [391]:
benefits.BenefitName.value_counts().plot(figsize = (16,10),color = 'green')
plt.xticks(rotation=45, fontsize = 14)
plt.title("Frequency of Medical Procedures covered by Insurance")
plt.show()
The following dataset describes rates of monthly premiums for each signed insurance in the dataset¶
In [341]:
rates = pd.read_csv('../../data/Rate.csv')
In [342]:
rates.shape
Out[342]:
In [343]:
rates.head()
Out[343]:
In [344]:
rates.groupby(rates.StateCode).IndividualRate.mean().head(4)
Out[344]:
In [345]:
rates.groupby([rates.StateCode, rates.BusinessYear]).IndividualRate.mean()
Out[345]:
In [346]:
# export_.to_csv('../data/groupbystateindrates.csv')
In [347]:
rates_1 = rates[rates.IndividualRate < 9000]
rates_1.IndividualRate.value_counts().head(5)
Out[347]:
In [348]:
rates_1 = rates[rates.IndividualRate < 9000]
rates_1.IndividualRate.value_counts().head(5)
Out[348]:
In [349]:
rates_1.columns
Out[349]:
In [350]:
rates_1[rates_1.CoupleAndTwoDependents.isnull() == False].head()
Out[350]:
The mean Individual Rates for each state across 2014 - 2016¶
In [353]:
export_2 = rates_1.groupby([rates_1.StateCode, rates_1.BusinessYear]).IndividualRate.mean()
In [354]:
export_2.to_csv('../../data/export_2.csv')
In [355]:
rates_1.IndividualTobaccoRate.unique()
Out[355]:
In [356]:
rates_1.IndividualRate.count()
Out[356]:
In [357]:
rates_1.IndividualTobaccoRate.count()
Out[357]:
In [358]:
rates_1.shape
Out[358]:
Difference for smokers and non-smokers¶
Additional information can be found here: https://www.healthmarkets.com/content/smoking-and-health-insurance
In [384]:
d.head()
Out[384]:
In [390]:
d = pd.DataFrame(rates_1[rates_1.IndividualTobaccoRate.isnull() != True][[
'StateCode','IndividualRate',
'IndividualTobaccoRate']].groupby('StateCode').sum())
d['SurCharge'] = d.IndividualTobaccoRate/d.IndividualRate
d['SurCharge'].sort_values().plot(figsize = (16,12),kind = 'bar', color = 'green')
plt.title('Average surcharge on individual medical insurance premiums for tobacco smokers, \n Years 2014 - 2016')
plt.xlabel('State', fontsize = 14)
plt.ylabel('Surcharge coefficient Smoker/Non-Smoker, x times')
plt.show()
Adding more data to check possible correlations¶
In [364]:
pop_df = pd.read_csv('../../data/US_Population.csv')
In [363]:
pop_df.head()
Out[363]:
Kaiser Family Foundation Has Got Very Good Data Sets¶
In [89]:
HICovs_2013 = pd.read_csv('../../data/HICoverage_2013.csv')
HICovs_2014 = pd.read_csv('../../data/HICoverage_2014.csv')
HICovs_2015 = pd.read_csv('../../data/HICoverage_2015.csv')
HICovs_2016 = pd.read_csv('../../data/HICoverage_2016.csv')
In [90]:
HICovs_2013.head(3)
Out[90]:
In [67]:
HICovs_2014.head(3)
Out[67]:
In [68]:
HICovs_2015.head(3)
Out[68]:
In [69]:
HICovs_2016.head(3)
Out[69]:
In [91]:
HICovs = pd.concat([HICovs_2013, HICovs_2014, HICovs_2015, HICovs_2016], axis=1, join='inner')
HICovs.drop('Location', axis = 1, inplace=True)
HICovs['State'] = HICovs_2014.Location
HICovs.dropna(inplace = True)
In [92]:
print HICovs.shape
HICovs.head(3)
Out[92]:
In [93]:
HICovs['UnInsured_Pct_2013'] = HICovs.Uninsured/HICovs.Total
HICovs['UnInsured_Pct_2014'] = HICovs.Uninsured_2014/HICovs.Total_2014
HICovs['UnInsured_Pct_2015'] = HICovs.Uninsured_2014/HICovs.Total_2015
HICovs['UnInsured_Pct_2016'] = HICovs.Uninsured_2014/HICovs.Total_2016
HICovs.head(3)
Out[93]:
In [94]:
HICovs[['State','UnInsured_Pct_2013']].sort_values('UnInsured_Pct_2013',ascending=False).head(5)
Out[94]:
In [77]:
HICovs[['State','UnInsured_Pct_2014']].sort_values('UnInsured_Pct_2014',ascending=False).head(5)
Out[77]:
In [76]:
HICovs[['State','UnInsured_Pct_2015']].sort_values('UnInsured_Pct_2015a',ascending=False).head(5)
Out[76]:
In [75]:
HICovs[['State','UnInsured_Pct_2016']].sort_values('UnInsured_Pct_2016',ascending=False).head(5)
Out[75]:
In [99]:
print HICovs.Uninsured.sum()
print HICovs.Total.sum()
HICovs.Uninsured.sum()/HICovs.Total.sum()
Out[99]:
In [134]:
print 'Percent of uninsured people in the US '
print '2013 ', round(HICovs.Uninsured.sum()/float(HICovs.Total.sum()),3)*100,'%'
print '2014 ', round(HICovs.Uninsured_2014.sum()/HICovs.Total_2014.sum(),3)*100,'%'
print '2015 ', round(HICovs.Uninsured_2015.sum()/HICovs.Total_2015.sum(),3)*100,'%'
print '2016 ', round(HICovs.Uninsured_2016.sum()/HICovs.Total_2016.sum(),3)*100,'%'
print '\n Overall number of uninsured 2013 vs 2016'
print HICovs.Uninsured.sum(), '- in 2013 vs', int(HICovs.Uninsured_2016.sum()), '- in 2016'
In [135]:
HICovs['Change_2010_16'] = 2*(HICovs.Uninsured_2016-HICovs.Uninsured)/(HICovs.Total_2016+HICovs.Total)
print '\n The top 7 states where the number of uninsured \n people has significantly decreased between 2010 - 2016'
HICovs[['State', 'Uninsured', 'Uninsured_2016','Change_2010_16']].sort_values('Change_2010_16', ascending = True).head(7)
Out[135]:
In [137]:
print '\n The states where the number of uninsured people \n has increased between 2010 - 2016'
HICovs[['State', 'Uninsured', 'Uninsured_2016','Change_2010_16']].sort_values('Change_2010_16', ascending = False).head(5)
Out[137]:
In [166]:
medcosts_2012 = pd.read_csv('../../data/Medical_costs_2012.csv', dtype = {'Hospital Care':'int',
'Physician and Clinical Services':'int',
'Prescription Drugs and Other Medical Nondurables':'int'})
medcosts_2012['Year'] = 2012
medcosts_2013 = pd.read_csv('../../data/Medical_costs_2013.csv', dtype = {'Hospital Care':'int',
'Physician and Clinical Services':'int',
'Prescription Drugs and Other Medical Nondurables':'int'})
medcosts_2013['Year'] = 2013
medcosts_2014 = pd.read_csv('../../data/Medical_costs_2014.csv', dtype = {'Hospital Care':'int',
'Physician and Clinical Services':'int',
'Prescription Drugs and Other Medical Nondurables':'int'})
medcosts_2014['Year'] = 2014
In [167]:
medcosts = pd.concat([medcosts_2012, medcosts_2013, medcosts_2014], ignore_index=True)
medcosts.columns = medcosts.columns.str.strip()
medcosts
Out[167]:
In [168]:
eda(medcosts)
Out[168]:
In [169]:
medcosts['Total'] = medcosts['Hospital Care'] + medcosts['Physician and Clinical Services'] + medcosts['Other Professional Services'] + medcosts['Prescription Drugs and Other Medical Nondurables'] + medcosts['Nursing Home Care'] + medcosts['Dental Services'] + medcosts['Home Health Care'] + medcosts['Medical Durables'] + medcosts['Other Health, Residential, and Personal Care']
medcosts[medcosts.Year == 2012]['Total'].head(3)
Out[169]:
In [170]:
print '\n Top 5 most expensive states for medical costs per capita 2012'
medcosts[medcosts.Year == 2012].sort_values('Total', ascending = False).head(5)
Out[170]:
In [171]:
print '\n Top 5 most expensive states for medical costs per capita 2014'
medcosts[medcosts.Year == 2014].sort_values('Total', ascending = False).head(5)
Out[171]:
In [172]:
US_medcosts = medcosts[medcosts['Location'].str.contains('United')]
medcosts = medcosts.drop(medcosts['Location'].str.contains('United'))
medcosts.head(5)
Out[172]:
In [173]:
medcosts.shape
Out[173]:
In [174]:
US_medcosts
Out[174]:
In [175]:
for i in US_medcosts.columns[1:-2]:
US_medcosts[i+'_pct'] = US_medcosts[i]/US_medcosts.Total
US_medcosts
Out[175]:
In [203]:
# for i in range(US_medcosts.shape[0]):
#US_medcosts[0,2:12]#.plot(figsize = (16,8),kind = 'pie', subplots=True)
#plt.show()
US_medcosts.mean()[:9].plot.pie(fontsize = 20,figsize = (12,12), subplots = True, autopct = '%.1f' )
plt.show()
In [473]:
mean_rates = pd.DataFrame(rates_1.groupby('StateCode')['IndividualRate'].mean())
In [522]:
mean_rates.head()
Out[522]:
In [534]:
State_Codes = pd.read_csv('../../data/US_codes.csv')
State_Codes.set_index('StateCode',inplace=True)
State_Codes.State.str.strip()
State_Codes.head()
Out[534]:
In [630]:
ind_ann_rates = pd.concat([mean_rates, State_Codes], axis = 1, join='inner', ignore_index=True)
ind_ann_rates.columns = ['MeanIndPremium','State']
ind_ann_rates['MeanAnnualPremium'] = ind_ann_rates.MeanIndPremium*12
print ind_ann_rates.shape
ind_ann_rates.head()
Out[630]:
In [631]:
ind_ann_rates.set_index('State', inplace = True)
ind_ann_rates.head()
Out[631]:
In [632]:
meanmedcosts = pd.DataFrame(medcosts.groupby('Location')['Total'].mean())
meanmedcosts.head()
Out[632]:
In [633]:
ind_ann_rates = ind_ann_rates.merge(meanmedcosts, left_index=True, right_index=True, how='inner')
ind_ann_rates.drop('MeanIndPremium', axis = 1, inplace = True)
ind_ann_rates.rename(columns={'Total':'HealthCosts'}, inplace = True)
In [676]:
plt.figure(figsize = (12,8))
plt.scatter(ind_ann_rates.HealthCosts, ind_ann_rates.MeanAnnualPremium, color = 'green')
plt.title('Mean Annual Premiums vs Mean Annual Health Costs Per Capita')
plt.xlabel('Mean Annual Health Costs Per Capita')
plt.ylabel('Mean Annual Premiums')
plt.show()
In [635]:
ind_ann_rates.corr('pearson')
Out[635]:
Let's mix in other stats¶
In [667]:
stats = pd.read_csv('../../data/states.csv')
In [668]:
stats = stats[['State','MeanHHIncome_15']]
stats.State = stats.State.str.strip()
stats.set_index('State', inplace = True)
In [671]:
ind_ann_rates_1 = ind_ann_rates.merge(stats, left_index=True, right_index=True, how='left')
ind_ann_rates_1.head()
Out[671]:
In [672]:
ind_ann_rates_1.corr('pearson')
Out[672]:
In [677]:
plt.figure(figsize = (12,8))
plt.scatter(ind_ann_rates_1.MeanHHIncome_15, ind_ann_rates_1.MeanAnnualPremium, color = 'green')
plt.title('Mean Annual Premiums vs Mean Annual Household Incomes')
plt.xlabel('Mean Annual Household Income')
plt.ylabel('Mean Annual Premium')
plt.show()
