Deepak Jain

Data Integration and Development Lead

Solution-focused and result oriented individual, highly experienced in IT application development and effectively managing IT application development teams. Customer focused with strengths in change management, problem solving and very hands on management style

Proven experience in successful resource management for completion of IT Application Development projects, identifying improvements, new ideas, creating plans and strategies that deliver results

Proven experience in building & managing Medium to large scale data migration/integration projects across different database technologies.

Core Expertise:

 IT Application development/Support & Management

 ETL Architecture and Frameworks building, Automated Data Testing

 Dimensional Modeling, Data Vault Modeling, Data Analytics & Data Warehousing

 Data Analysis,Data Modeling, Data Architecture, Data Quality and Profiling

 Onsite/Offshore Delivery Management,

 Vendor management,

 Resource Management

[No canvas support]

Project: Medicare Data Analysis.

Faculty Feedback

Prashanth G

Practice Lead - Healthcare at TCS

This is to inform you that I have reviewed Deepak Jain's Project, I find that he has completed all the requirements set out for the Project, You may proceed with IBM certification process

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 Data in Pig
REGISTER /opt/cloudera/parcels/CDH-5.6.0-1.cdh5.6.0.p0.45/jars/piggybank.jar;

plan_info = LOAD '/user/hdfs/medicare/{PlanInfoCounty_FipsCode1.csv, PlanInfoCounty_FipsCode2.csv }' using org.apache.pig.piggybank.storage.CSVExcelStorage() ;

plan_info_r = FOREACH plan_info GENERATE $0 as (Contract_ID:chararray),$1 as (Plan_ID:chararray),$2 as (Segment_ID:chararray),$4 as (Org_Name:chararray),
$5 as (Plan_Name:chararray),$26 as (Plan_Important_Note:chararray),$74 as (CountyFIPSCode:chararray); 

plan_info_n = FILTER plan_info_r BY (Contract_ID is not null) and (Plan_ID is not null) and (Segment_ID is not null) and (CountyFIPSCode is not null);

plan_info_u = DISTINCT(FOREACH plan_info_n GENERATE Contract_ID,Plan_ID,Segment_ID,CountyFIPSCode,Plan_Important_Note);

service_info = LOAD '/user/hdfs/medicare/{vwPlanServices.csv}' using org.apache.pig.piggybank.storage.CSVExcelStorage();

service_info_r = FOREACH service_info GENERATE $0 as (Language:chararray), $1 as (Contract_Year:chararray), $2 as (Contract_ID:chararray), $3 as (Plan_ID:chararray),
$4 as (Segment_ID:chararray), $6 as (CategoryCode:chararray), $7 as (Benefit:chararray), $8 as (Package_Name:chararray)  ;
service_info_r_eng = FILTER service_info_r BY EqualsIgnoreCase(Language, 'English');

service_info_r_engn = FILTER service_info_r_eng BY (Contract_ID is not null) and (Plan_ID is not null) and (Segment_ID is not null) and (CategoryCode is not null) ;

service_info_u = DISTINCT(FOREACH service_info_r_engn GENERATE Contract_ID, Plan_ID, Segment_ID, CategoryCode,  Benefit, Package_Name);

plan_service = join plan_info_u by (Contract_ID,Plan_ID,Segment_ID),
service_info_u by (Contract_ID,Plan_ID,Segment_ID);

plan_service_r = FOREACH plan_service GENERATE $0 as (Contract_ID:chararray),$1 as (Plan_ID:chararray),$2 as (Segment_ID:chararray), $3 as (CountyFIPSCode:chararray),  
$4 as (Plan_Important_Note:chararray), $8 as (CategoryCode:chararray), $9 as (Benefit:chararray), $10 as (Package_Name:chararray) ;

STORE plan_service_r INTO '/user/hdfs/medicare/output/plan_service_info';
Top 5 plans with lowest premiums for a given county across the US
select distinct a.countyfipscode, a.contract_id, a.segment_id, a.plan_id, b.premium, b.rank
fromplan_service_infoa  inner join
(SELECT countyfipscode, contract_id,plan_id,segment_id,categorycode,
 cast(regexp_replace(regexp_extract(benefit,'(Additional\\s\\$\\d{0,5}\\.\\d{0,2})', 0), '(Additional\\s\\$)' , '') as double) +  cast(regexp_replace(regexp_extract(benefit,'(\\d{0,5}\\.{0,1}\\d{0,2}\\<\\/b>\\smonthly\\splan\\spremium)', 0),'(\\<\\/b>\\smonthly\\splan\\spremium)' , '') as double)
as premium,                                     
rank() over (PARTITION BY countyfipscode ORDER BY 
 cast(regexp_replace(regexp_extract(benefit,'(Additional\\s\\$\\d{0,5}\\.\\d{0,2})', 0), '(Additional\\s\\$)' , '') as double) +  cast(regexp_replace(regexp_extract(benefit,'(\\d{0,5}\\.{0,1}\\d{0,2}\\<\\/b>\\smonthly\\splan\\spremium)', 0),'(\\<\\/b>\\smonthly\\splan\\spremium)' , '') as double)
 ASC) as rank,                                              
benefit
 FROM plan_service_info
where length(regexp_extract(benefit,'(monthly plan premium)', 0)) > 1) b
on  a.countyfipscode = b.countyfipscode
anda.contract_id = b.contract_id
anda.plan_id = b.plan_id
anda.segment_id = b.segment_id
anda.categorycode = b.categorycode
whereb.rank< 6
ORDER BY a.countyfipscode, b.rank;
To find plans that have highest co-pays for doctors in a given county
select distinct c.countyfipscode, c.contract_id, c.plan_id, c.segment_id, c.copayment, c.rank
from
(select distinct a.countyfipscode, a.contract_id, a.plan_id, a.segment_id, b.copayment,
rank() over (PARTITION BY a.countyfipscode ORDER BY b.copaymentdesc) as rank
fromplan_service_info a join
(selectcountyfipscode, contract_id, plan_id, segment_id,
sum(cast(regexp_replace(regexp_extract(benefit,'(\\d{0,5}\\.{0,1}\\d{0,2}\\<\\/b>\\scopay)',0), '(\\<\\/b>\\scopay)', '') as double)) as copayment
FROM plan_service_info
where (categorycode == 10) and (cast(regexp_replace(regexp_extract(benefit,'(\\d{0,5}\\.{0,1}\\d{0,2}\\<\\/b>\\scopay)',0), '(\\<\\/b>\\scopay)', '') as double) is not NULL)
Group By countyfipscode, contract_id, plan_id,  segment_id
order by countyfipscode, contract_id, plan_id, segment_id) b
on
a.countyfipscode == b.countyfipscode
anda.contract_id == b.contract_id
anda.plan_id == b.plan_id
anda.segment_id == b.segment_id
order by a.countyfipscodeasc, b.copaymentdesc) c
wherec.rank< 6 
order by c.countyfipscode, c.rank;
To compare plans based on features like plans that offer free ambulance services
select  c.contract_id, c.plan_id, c.segment_id, 
collect_set(CASE   WHEN Length(regexp_replace(regexp_extract(benefit, '(Out-of-network:  \\${0,}\\d{0,}\\%{0,1})', 0), '(:  )', '')) > 0 then 
	regexp_replace(regexp_extract(benefit, '(Out-of-network:  \\${0,}\\d{0,}\\%{0,1})', 0), '(:  )', ' Copayment:')
       WHEN Length(regexp_replace(regexp_extract(benefit, '(In-network:  \\${0,}\\d{0,}\\%{0,1})', 0), '(:  )', '')) > 0 then 
	regexp_replace(regexp_extract(benefit, '(In-network:  \\${0,}\\d{0,}\\%{0,1})', 0), '(:  )', ' Copayment:')
       WHEN Length(regexp_extract(benefit, '(cost  depending on the service)', 0)) > 0 then  regexp_extract(benefit, '(cost  depending on the service)', 0)
       WHEN Length(regexp_replace(regexp_extract(benefit, '(\\$\\d{1,}\\<\\/b\\>\\scopay)', 0), '(<\\/b\\>\\scopay)', '')) > 0 then 
	regexp_replace(regexp_extract(benefit, '(\\$\\d{1,}\\<\\/b\\>\\scopay)', 0), '(<\\/b\\>\\scopay)', ' Copayment:')
       WHEN Length(regexp_replace(regexp_extract(benefit, '(or \\;\\\\d{1,}\\%{1,}\\<\\/b>\\sof\\sthe\\scost)', 0), '(\\<\\/b>\\sof\\sthe\scost)', '')) > 0 then 
	regexp_replace(regexp_extract(benefit, '(or \\;\\\\d{1,}\\%{1,}\<\\/b>\\sof\\sthe\\scost)', 0), '(\\<\\/b>\\sof\\sthe\\scost)', ' Copayment:')

       ELSE ''
END ) as ambulance_service_cost
fromplan_service_info cwhere categorycode == 5
group by c.contract_id, c.plan_id, c.segment_id
order by c.contract_id, c.plan_id, c.segment_id;
To compare plans based on features like the benefits available for diabetes under specific plan
selectc.contract_id, c.plan_id, c.segment_id, 
collect_set(CASE   WHEN Length(regexp_extract(benefit, '(Diabetes screenings)', 0)) > 0 then 
	regexp_extract(benefit, '(Diabetes screenings)', 0)
	   WHEN Length(regexp_extract(benefit, '(Diabetes monitoring supplies)', 0)) > 0 then 
	regexp_extract(benefit, '(Diabetes monitoring supplies)', 0)
       WHEN Length(regexp_extract(benefit, '(Diabetes self-management training)', 0)) > 0 then 
	regexp_extract(benefit, '(Diabetes self-management training)', 0)
       ELSE ''
END ) as Diabetes_services
fromplan_service_info c 
where benefit like '%Diabetes%' 
group by  c.contract_id, c.plan_id, c.segment_id
order by  c.contract_id, c.plan_id, c.segment_id;
To compare plan benefits on diabetes and mental healthcare offered by all companies in a particular county
selecta.countyfipscode, a.contract_id, a.plan_id, a.segment_id, a.mental_services, b.diabetes_service
from
(selectcountyfipscode, contract_id, plan_id, segment_id, collect_set(benefit) as mental_services
fromplan_service_info
where Upper(benefit) like '%MENTAL%'
group by countyfipscode, contract_id, plan_id, segment_id) a
JOIN
(selectcountyfipscode, contract_id, plan_id, segment_id, collect_set(benefit) as diabetes_service
fromplan_service_info
where Upper(benefit) like '%DIABETES%'
group by countyfipscode, contract_id, plan_id, segment_id) b
ON 
a.countyfipscode = b.countyfipscode
AND a.contract_id = b.contract_id
AND a.plan_id = b.plan_id
AND a.segment_id = b.segment_id
order by a.countyfipscode, a.contract_id, a.plan_id, a.segment_id;
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/    |