Craig Allen

IT Management Carrollton, Texas

IT Management

CamBren

2015 – Present (1 year)Carrollton, TX

Manager, IT Product Support

CONEXIS, a division of WageWorks, Inc.

2015 – 2015 (less than a year)Irving, TX

Managed a team of contracted data integration engineers accountable for non-conformance investigation, root cause analysis, corrective and preventative actions of escalated billing and eligibility issues. Mentored team members in effective approaches to data analysis and reporting. Provided technical expertise on the development of complex procedures.

[No canvas support]

Project: Medicare Data Analysis.

Faculty Feedback

Prashanth G

Practice Lead - Healthcare at TCS

I have reviewed the Project and recommend that this project is complete and has met all the required objectives for the project and recommend the student be issued the certificate

Problem Statement:

Several Medicare plans are available for senior citizens and other qualified members to enrol into every year, that are offered by different health insurance companies offering these plans. While a lot of information regarding individual plans exist, it is difficult to compare plans based on various criteria to make an informed choice to suit unique situations of individual members as well as for plan benefit designers to compare plans and design benefits that meets unique requirements and are competitive in different markets. The purpose of this document is to detail the analysis of the medicare plans data across US and provide useful summary details.

  1. The primary purpose of this project is to facilitate analysis of Medicare plans to provide meaningful insights that help in choosing appropriate medicare plans by comparing all relevant details regarding these plans that are available in each countries throughout the country.
  2.  While CMS provides rich details on all the plans that are offered county wise, it makes better analysis when each plans that are offered are compared  on the finer descriptions of its cost and coverage details
  3. To implement an efficient system to extract , load and transform all data related to Medicare plans to perform analytics.
  4. Analysis of Medicare plans to compare plan offerings by various criteria’s to select suitable plan for the Members.
  5. Analysis of Medicare plans to compare plan offerings to design suitable benefit plan for different regions


Dataset:

The medicare data can be downloaded from medicare government website from the following location:

https://www.medicare.gov/download/downloaddb.asp

Load Plan Services Data
-- Registering Downloaded PiggyBank jar function library
REGISTER '/home/hadoop/piggybank-0.12.0.jar';

-- Using PiggyBank function to load quote qualified CSV file
vNoQuotes  = LOAD '/data/Medicare/Extract/PlanServices.csv' USING org.apache.pig.piggybank.storage.CSVExcelStorage(',', 'NO_MULTILINE', 'UNIX', 'SKIP_INPUT_HEADER') AS (Language:chararray,Contract_Year:chararray,Contract_ID:chararray,Plan_ID:int,Segment_ID:int,CategoryDescription:chararray,CategoryCode:chararray,Benefit:chararray,Package_Name:chararray,Package_ID:chararray,Sentences_Sort_Order:int);

-- Filtering out non-english records
vNoQuoteEnglishOnly = FILTER vNoQuotes BY Language=='English';

-- Limiting the number of fields from the original file
vRequiredFields = FOREACH vNoQuoteEnglishOnly GENERATE Contract_ID, Plan_ID, Segment_ID, Sentences_Sort_Order, CategoryCode, CategoryDescription, Benefit;

-- Defining a sort order, this also generates only one output file
vRequiredFieldsOrdered = ORDER vRequiredFields BY Contract_ID ASC, Plan_ID ASC, Segment_ID ASC, Sentences_Sort_Order ASC;

-- Load the results into HDFS
STORE vRequiredFieldsOrdered INTO '/data/Medicare/Transform/PlanServices';
Load Plan County Info Data
-- Registering Downloaded PiggyBank jar function library
REGISTER '/home/hadoop/piggybank-0.12.0.jar';

-- Load comma delimited, quote qualified file into relation
vCountyInfoNoQuotesLessThan30000 = LOAD '/data/Medicare/Extract/PlanInfo_LessThan30000.csv' USING org.apache.pig.piggybank.storage.CSVExcelStorage(',','NO_MULTILINE','UNIX','SKIP_INPUT_HEADER') as (contract_id:chararray,plan_id:int,segment_id:int,contract_year:chararray,org_name:chararray,plan_name:chararray,sp_plan_name:chararray,geo_name:chararray,tax_stus_cd:chararray,tax_status_desc:chararray,sp_tax_status_desc:chararray,plan_type:chararray,plan_type_desc:chararray,web_address:chararray,partd_wb_adr:chararray,frmlry_wbst_adr:chararray,phrmcy_wbst_adr:chararray,fed_approval_status:chararray,sp_fed_approval_status:chararray,pos_available_flag:chararray,mail_ordr_avlblty:chararray,cvrg_gap_ofrd:chararray,cvrg_gap_ind:chararray,cvrg_gap_desc:chararray,contract_important_note:chararray,sp_contract_important_note:chararray,plan_important_note:chararray,sp_plan_important_note:chararray,segment_important_note:chararray,sp_segment_important_note:chararray,legal_entity_name:chararray,trade_name:chararray,network_english:chararray,network_spanish:chararray,contact_person:chararray,street_address:chararray,city:chararray,state_code:chararray,zip_code:chararray,email_prospective:chararray,local_phone_prospective:chararray,tollfree_phone_prospective:chararray,local_tty_prospective:chararray,tollfree_tty_prospective:chararray,email_current:chararray,local_phone_current:chararray,tollfree_phone_current:chararray,local_tty_current:chararray,tollfree_tty_current:chararray,contact_person_pd:chararray,street_address_pd:chararray,city_pd:chararray,state_code_pd:chararray,zip_code_pd:chararray,email_prospective_pd:chararray,local_phone_prospective_pd:chararray,tollfree_phone_prospective_pd:chararray,local_tty_prospective_pd:chararray,tollfree_tty_prospective_pd:chararray,email_current_pd:chararray,local_phone_current_pd:chararray,tollfree_phone_current_pd:chararray,local_tty_current_pd:chararray,tollfree_tty_current_pd:chararray,ma_pd_indicator:chararray,ppo_pd_indicator:chararray,snp_id:chararray,snp_desc:chararray,sp_snp_desc:chararray,lis_100:chararray,lis_75:chararray,lis_50:chararray,lis_25:chararray,regional_indicator:chararray,CountyFIPSCode:chararray);

-- Load comma delimited, quote qualified file into relation
vCountyInfoNoQuotesMoreThan30000 = LOAD '/data/Medicare/Extract/PlanInfo_MoreThan30000.csv' USING org.apache.pig.piggybank.storage.CSVExcelStorage(',','NO_MULTILINE','UNIX','SKIP_INPUT_HEADER') as (contract_id:chararray,plan_id:int,segment_id:int,contract_year:chararray,org_name:chararray,plan_name:chararray,sp_plan_name:chararray,geo_name:chararray,tax_stus_cd:chararray,tax_status_desc:chararray,sp_tax_status_desc:chararray,plan_type:chararray,plan_type_desc:chararray,web_address:chararray,partd_wb_adr:chararray,frmlry_wbst_adr:chararray,phrmcy_wbst_adr:chararray,fed_approval_status:chararray,sp_fed_approval_status:chararray,pos_available_flag:chararray,mail_ordr_avlblty:chararray,cvrg_gap_ofrd:chararray,cvrg_gap_ind:chararray,cvrg_gap_desc:chararray,contract_important_note:chararray,sp_contract_important_note:chararray,plan_important_note:chararray,sp_plan_important_note:chararray,segment_important_note:chararray,sp_segment_important_note:chararray,legal_entity_name:chararray,trade_name:chararray,network_english:chararray,network_spanish:chararray,contact_person:chararray,street_address:chararray,city:chararray,state_code:chararray,zip_code:chararray,email_prospective:chararray,local_phone_prospective:chararray,tollfree_phone_prospective:chararray,local_tty_prospective:chararray,tollfree_tty_prospective:chararray,email_current:chararray,local_phone_current:chararray,tollfree_phone_current:chararray,local_tty_current:chararray,tollfree_tty_current:chararray,contact_person_pd:chararray,street_address_pd:chararray,city_pd:chararray,state_code_pd:chararray,zip_code_pd:chararray,email_prospective_pd:chararray,local_phone_prospective_pd:chararray,tollfree_phone_prospective_pd:chararray,local_tty_prospective_pd:chararray,tollfree_tty_prospective_pd:chararray,email_current_pd:chararray,local_phone_current_pd:chararray,tollfree_phone_current_pd:chararray,local_tty_current_pd:chararray,tollfree_tty_current_pd:chararray,ma_pd_indicator:chararray,ppo_pd_indicator:chararray,snp_id:chararray,snp_desc:chararray,sp_snp_desc:chararray,lis_100:chararray,lis_75:chararray,lis_50:chararray,lis_25:chararray,regional_indicator:chararray,CountyFIPSCode:chararray);

-- Combine the results of the two structurally equivalent files 
vPlanInfoCountyUnion = UNION vCountyInfoNoQuotesLessThan30000, vCountyInfoNoQuotesMoreThan30000;

-- Narrow down the number of fields required for analysis 
vPlanInfoCounty = FOREACH vPlanInfoCountyUnion GENERATE CountyFIPSCode,zip_code,contract_id, plan_id, segment_id, org_name;

-- Sort the records (this allows for a single file to be generated in HDFS) 
vPlanInfoCountyOrdered = ORDER vPlanInfoCounty BY CountyFIPSCode ASC, zip_code ASC, contract_id ASC, plan_id ASC, segment_id ASC;  

-- Store the results into HDFS in comma delimited format 
STORE vPlanInfoCountyOrdered INTO '/data/Medicare/Transform/PlanInfo' USING PigStorage(',','-schema');
Load Plan Premiums
-- Get the results of the transformed Plan Services table (from step 5.1.1) 
vTransformedPlanServices = LOAD '/data/Medicare/Transform/PlanServices/part-r-00000' USING PigStorage('\t') AS (Contract_ID:chararray, Plan_ID:chararray, Segment_ID:chararray, Sentences_Sort_Order:chararray, CategoryCode:chararray, CategoryDescription:chararray, Benefit:chararray);

-- Filter the Benefits from the Plan Service table to get distinct list of Premium based records 
vFilteredPremiums = FILTER vTransformedPlanServices BY (CategoryCode=='1' AND UPPER(Benefit) matches '.*PER MONTH.*' AND UPPER(Benefit) matches '.*IN ADDITION.*' and UPPER(Benefit) matches '.*.*');

-- Parse out the Premium amount from qualifying records 
vGetPremiumString = FOREACH vFilteredPremiums GENERATE Contract_ID, Plan_ID,Segment_ID,SUBSTRING(Benefit,INDEXOF(Benefit,'',0)+3,INDEXOF(Benefit,'',0)) as PremiumString;

-- Convert Premium String value to Double 
vGetPremiumasDouble = FOREACH vGetPremiumString GENERATE Contract_ID, Plan_ID, Segment_ID, PremiumString, (double)SUBSTRING(TRIM(PremiumString),1,10) as Premium;

-- Sort Results  
vSortedResults = ORDER vGetPremiumasDouble BY Contract_ID ASC, Plan_ID ASC, Segment_ID ASC;

-- Store Results in HDFS table  
STORE vSortedResults INTO '/data/Medicare/Transform/PlanPremiums' USING PigStorage(',','-schema');
Create PlanServices Table
CREATE EXTERNAL TABLE PlanServices(Contract_ID STRING, Plan_ID String, Segment_ID STRING, Sentences_Sort_Order STRING, CategoryCode STRING, CategoryDescription STRING, Benefit STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
LOCATION 'hdfs:/data/Medicare/Transform/PlanServices/';
Create PlanInfo Table
CREATE EXTERNAL TABLE PlanInfo(CountyFIPSCode STRING, Zip_Code STRING, Contract_ID STRING, Plan_ID String, Segment_ID STRING, Org_Name STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
LOCATION 'hdfs:/data/Medicare/Transform/PlanInfo/';
Create PlanPremiums Table
CREATE EXTERNAL TABLE PlanPremiums(Contract_ID STRING, Plan_ID String, Segment_ID STRING, PremiumStr STRING, Premium DECIMAL)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
LOCATION 'hdfs:/data/Medicare/Transform/PlanPremiums/';
Top 5 plans with lowest premiums by County
SELECT Ranked.CountyFIPSCode, Ranked.Contract_ID, Ranked.Plan_ID, Ranked.Segment_ID, Ranked.Premium, Ranked.Rnk
FROM
(
SELECT Sub.CountyFIPSCode, Sub.Contract_ID, Sub.Plan_ID, Sub.Segment_ID, Sub.Premium, rank() over (Partition By Sub.CountyFIPSCode order by Sub.Premium ASC) Rnk 
FROM
(
SELECT pi.CountyFIPSCode, pi.Contract_ID, pi.Plan_ID, pi.Segment_ID, pp.Premium
FROM PlanInfo pi 
JOIN PlanPremiums pp on pi.Contract_ID = pp.Contract_ID and pi.Plan_ID = pp.Plan_ID and pi.Segment_ID = pp.Segment_ID
WHERE pp.Premium > 0
ORDER BY pi.CountyFIPSCode ASC, pp.Premium ASC
) Sub
) Ranked
WHERE Ranked.Rnk <=5
Doctor CoPay Query
SELECT pi.CountyFIPSCode, pi.Zip_Code, pi.Contract_ID, pi.Plan_ID, pi.Segment_ID, PlanCoPays.CoPay
FROM PlanInfo pi
JOIN
(
SELECT contract_ID, Plan_ID, Segment_ID, 
Max(substr(substr(Benefit,(instr(benefit,'')+3), instr(benefit,'')-(instr(Benefit,'')+3)),2)) as CoPay
FROM PlanServices  
WHERE Benefit rlike '.*copay.*' 
AND  Benefit rlike '.*Primary care physician visit.*' 
AND Benefit rlike '.*In-network.*'
AND CategoryCode=10 
AND cast(substr(substr(Benefit,(instr(benefit,'')+3), instr(benefit,'')-(instr(Benefit,'')+3)),2) as double) is not null
GROUP BY Contract_ID, Plan_ID, Segment_ID
) PlanCoPays on pi.Contract_ID = PlanCoPays.Contract_ID and pi.Plan_ID = PlanCoPays.Plan_ID and pi.Segment_ID = PlanCoPays.Segment_ID
ORDER BY pi.CountyFIPSCode, pi.Contract_ID, pi.Plan_ID, pi.Segment_ID, Cast(PlanCoPays.CoPay as Double) DESC;
Compare plans with free ambulance services
SELECT ps.Contract_ID, ps.Plan_ID, ps.Segment_ID, ps.CategoryCode, ps.CategoryDescription, Substring(Benefit,1,10)
FROM	PlanServices ps
JOIN (
SELECT Contract_ID, Plan_ID, Segment_ID
FROM PlanServices
WHERE categorycode = 5
and Benefit rlike '.*You pay nothing.*'
GROUP BY Contract_ID, Plan_ID, Segment_ID 
) a on ps.Contract_ID = a.Contract_ID and ps.Plan_ID = a.Plan_ID and ps.Segment_ID = a.Segment_ID
ORDER BY
ps.Contract_ID, ps.Plan_ID, ps.Segment_ID, ps.categorycode
Compare plans with benefits for diabetes
SELECT Contract_ID, Plan_ID, Segment_ID
FROM PlanServices
WHERE
	CategoryCode = 8
Group by Contract_ID, Plan_ID, Segment_ID
Order by Contract_ID, Plan_ID, Segment_ID
plans with benefits for diabetes and Mental Health by County and Company
Select pi.CountyFIPSCode, pi.contract_id, pi.plan_id, pi.segment_ID, pi.Org_Name
from planInfo pi
Join 
(
Select Contract_ID, PLan_ID, Segment_ID
from PlanServices
where (categoryCode = 16 or categoryCode = 8)
) AssocPlans on pi.contract_id = AssocPlans.Contract_ID and pi.plan_id = assocPlans.Plan_ID and pi.segment_id = AssocPlans.Segment_ID
Group by
pi.CountyFIPSCode, 
pi.contract_id,
pi.plan_id,
pi.segment_ID,
pi.Org_Name	
Order by
pi.CountyFIPSCode, 
pi.contract_id,
pi.plan_id,
pi.segment_ID,
pi.Org_Name
best-it-exam-    | for-our-work-    | hottst-on-sale-    | it-sale-    | tast-dumps-us-    | test-king-number-    | pass-do-it-    | just-do-it-    | pass-with-us-    | passresults-everything-    | passtutor-our-dumps-    | realtests-us-exam-    | latest-update-source-for-    | cbtnuggets-sale-exam    | experts-revised-exam    | certguide-sale-exam    | test4actual-sale-exam    | get-well-prepared-    | certkiller-sale-exam    | buy-discount-dumps    | how-to-get-prepared-for-the    | in-an-easy-way    | brain-dumps-sale    | with-pass-exam-guarantee    | accurate-study-material    | at-first-try    | 100%-successful-rate    | get-certification-easily    | material-provider-exam    | real-exam-practice    | with-pass-score-guarantee    | certification-material-provider    | for-certification-professionals    | get-your-certification-successfully    | 100%-Pass-Rate    | in-pdf-file    | practice-exam-for    | it-study-guides    | study-material-sku    | study-guide-pdf    | prep-guide-demo    | certification-material-id    | actual-tests-demo    | brain-demos-test    | best-pdf-download    | our-certification-material    | best-practice-test    | leading-provider-on    | this-course-is-about    | the-most-reliable    | high-pass-rate-of    | money-back-guarantee    | high-pass-rate-demo    | recenty-updated-key    | only-for-students-free-download    | courseware-plus-kit-for    | accurate-answers-of    | the-most-reliable-id    | provide-training-for    | welcome-to-buy    | material-for-success-pass    | provide-free-support    | best-book-for-pass    | accuracy-of-the-answers    | pass-guarantee-id    |
http://forensics.sch.ac.kr/    | http://forensics.sch.ac.kr/    |