Vivek Ghildiyal

Software Engineering Team Lead at Accenture

I am Java J2EE professional and have primarily worked in IT Service Industry. I have a total experience of 8 years on various Java J2EE frameworks and hands on with DB and SQL.

I have decent experineces in almost all fields of SDLC and couple of years of experience in support and maintenance of CRM application

I am passionate for and intend to work in Application development roles with increased responsibility.


[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 Vivek's project submission and find that he has completed all the requirements set 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

Project Report

For Plan Services file following are the commands in Pig Script file
-- We register these Jars as we want to use CSVExcelStorage functions to load tphe csv file and also we have created the UDF in pig to manipulate Benefit data and associated jar is loaded
REGISTER /usr/lib/pig/piggybank.jar; 

REGISTER /usr/lib/pig/module6-dz-1.0.jar; 

-- Load the file from HDFS
records = LOAD '/user/cloudera/input/vwPlanServices.csv'  using  org.apache.pig.piggybank.storage.CSVExcelStorage() as (Language:chararray,Contract_Year:chararray,Contract_ID:chararray,Plan_ID:chararray,Segment_ID:chararray,CategoryDescription:chararray,CategoryCode:chararray,Benefit:chararray,package_name:chararray,package_id:chararray,sentences_sort_order:chararray); 

-- Clean records by null checks and Language Check
cleansedOutput = FILTER records BY Language=='English' AND (Contract_ID IS NOT NULL) AND (Plan_ID IS NOT NULL) AND (Segment_ID IS NOT NULL); 

-- Generate three more columns for each plan – Speciality type, Liability type and Liability Amount throughUDF - com.healthcare.plansservices.util.PlanServiceAmountCalulator  and pick selected columns instead of whole data for more clarity
filteredOutput = FOREACH cleansedOutput GENERATE 
Contract_Year,Contract_ID,Plan_ID,Segment_ID,CategoryDescription,CategoryCode,Benefit,FLATTEN(com.healthcare.plansservices.util.PlanServiceAmountCalulator(Benefit,CategoryCode)); 

-- Dump the output file in HDFS
STORE filteredOutput INTO '/user/cloudera/output/planServ';
For PlanInforCounty csv files following are the commands from Pig Script
-- We register this Jars as we want to use CSVExcelStorage functions to load the csv file 
REGISTER /usr/lib/pig/piggybank.jar; 

-- Load both CSV files in single command using parameters in fileName
recordsCounty = LOAD '/user/cloudera/input/PlanInfoCounty_FipsCode{Less,More}Than30000.csv' using  org.apache.pig.piggybank.storage.CSVExcelStorage() as (contract_id:chararray,plan_id:chararray,segment_id:chararray,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); 

-- Cleanse records by removing data with null values
cleansedOutputCounty = FILTER recordsCounty BY (contract_id IS NOT NULL) AND (plan_id IS NOT NULL) AND (segment_id IS NOT NULL); 

-- Pick selected data
filterCounty = FOREACH  cleansedOutputCounty GENERATE contract_id,plan_id,segment_id,contract_year,org_name,sp_plan_name,geo_name,plan_type,plan_type_desc,CountyFIPSCode; 

-- Dump output file in HDFS
STORE filterCounty INTO '/user/cloudera/output/fips';
HIVE Scripts
-- Create new Database in Hive

create database MEDICARE; 

-- Create table in hive to store plan service details
create table plan_services_details (Contract_Year String,Contract_ID String,Plan_ID String,Segment_ID String,CategoryDescription String,CategoryCode Int,Benefit String,speciality_type String,liability_type String,liability_amount float) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';  

-- create table in hive to store county plan services details
create table county_plan_services (contract_id String,plan_id String,segment_id String,contract_year String,org_name String,sp_plan_name String,geo_name  String,plan_type String,plan_type_desc String,CountyFIPSCode  INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';  

-- load county plan details
LOAD DATA INPATH '/user/cloudera/output/fips/part*' INTO TABLE county_plan_services;  

-- Load plan services details
LOAD DATA INPATH '/user/cloudera/output/planServ/part-m-00000' INTO TABLE plan_services_details;  

-- Add a jar containing UDF
add jar /usr/lib/hive/lib/module8-dz-1.0.jar;  

-- From loaded jar associate a temporary function from UDF

create temporary function rank as 'com.medicare.hive.udf.Rank';'   

-- Create view which gets the join between 2 tables for premium data grouping and then wfrite query to pick top 5 from each group . The order is based on minimum premium. Dump data in local directory
Analysis 1: Identify top 5 plans with lowest premiums for a given county across the US
-- create view to gather data joining both tables for grouping data via county and then selecting top 5 in terms of highrest copays and dumping data in local directory

create view premium_view as 
SELECT a.CountyFIPSCode as county_id ,CategoryDescription as plan_desc,liability_amount as premium_amt, rank(a.CountyFIPSCode) as fiprank,a.geo_name as geo,a.Benefit as benifit,a.org_nameas organization   FROM 
( select county_plan_services.CountyFIPSCode ,plan_services_details.CategoryDescription , plan_services_details.liability_amount,county_plan_services.geo_name,plan_services_details.Benefit, county_plan_services.org_name from county_plan_services  inner join 
plan_services_details  on (CONCAT(county_plan_services.plan_id,county_plan_services.contract_id,county_plan_services.segment_id)= CONCAT(plan_services_details.Plan_ID, plan_services_details.Contract_ID,plan_services_details.segment_id))  
where plan_services_details.CategoryCode =1  and plan_services_details.liability_amount IS NOT NULL  GROUP BY county_plan_services.CountyFIPSCode,plan_services_details.liability_amount,plan_services_details.CategoryDescription , county_plan_services.geo_name,plan_services_details.Benefit, county_plan_services.org_name
DISTRIBUTE by CountyFIPSCode 
SORT BY (CountyFIPSCode,liability_amount) 
) a
ORDER BY county_id,fiprank;  

INSERT OVERWRITE LOCAL DIRECTORY '/home/cloudera/Documents/Project/hive-Premium' row format delimited fields terminated by ',' 
select county_id,plan_desc,premium_amt,geo,benifit,organization from premium_view where fiprank<5;
Analysis 2: To find plans that have highest co-pays for doctors in a given county
create view copay_view as  
SELECT a.CountyFIPSCode as county_id ,CategoryDescription as plan_desc,liability_amount as copay_amt, rank(a.CountyFIPSCode) as fiprank,a.geo_name as geo,a.Benefit as benifit,a.org_name as organization
from
(select county_plan_services.CountyFIPSCode ,plan_services_details.CategoryDescription ,plan_services_details.liability_amount, county_plan_services.geo_name,plan_services_details.Benefit, county_plan_services.org_name from county_plan_services  inner join plan_services_details  on (CONCAT(county_plan_services.plan_id,county_plan_services.contract_id,county_plan_services.segment_id)=CONCAT(plan_services_details.Plan_ID, plan_services_details.Contract_ID,plan_services_details.segment_id))  where plan_services_details.CategoryCode =10  and liability_type = 'COPAY' and plan_services_details.liability_amount IS NOT NULL  
GROUP BY county_plan_services.CountyFIPSCode,plan_services_details.liability_amount,plan_services_details.CategoryDescription , county_plan_services.geo_name,plan_services_details.Benefit, county_plan_services.org_name 
DISTRIBUTE by CountyFIPSCode SORT BY CountyFIPSCode ASC , liability_amount DESC     
) a 
ORDER BY county_id, fiprank; 

INSERT OVERWRITE LOCAL DIRECTORY '/home/cloudera/Documents/Project/hive-Copay' row format delimited fields terminated by ',' 
select county_id,plan_desc,copay_amt,fiprank,geo,benifit,organization from copay_view where fiprank<5;
Analysis 3: To compare plans based on features like plans that offer free ambulance services
INSERT OVERWRITE LOCAL DIRECTORY '/home/cloudera/Documents/Project/hive-AmbulancBen' row format delimited fields terminated by ',' 
select * from plan_services_details where CategoryCode='5';
Analysis 4: To compare plans based on features like the benefits available for diabetes under specific planPlan that offers diabetes benefits
INSERT OVERWRITE LOCAL DIRECTORY '/home/cloudera/Documents/Project/hive-DiabetesBen' row format delimited fields terminated by ',' 
select * from plan_services_details where CategoryCode=8;
Analysis 5: To compare plan benefits on diabetes and mental healthcare offered by all companies in a particular county
INSERT OVERWRITE LOCAL DIRECTORY '/home/cloudera/Documents/Project/hive-Mental-Diab' row format delimited fields terminated by ',' 
select county_plan_services.CountyFIPSCode ,plan_services_details.CategoryDescription ,plan_services_details.liability_amount, county_plan_services.geo_name,plan_services_details.Benefit, county_plan_services.org_name from county_plan_services  inner join plan_services_details  on (CONCAT(county_plan_services.plan_id,county_plan_services.contract_id,county_plan_services.segment_id)=CONCAT(plan_services_details.Plan_ID, plan_services_details.Contract_ID,plan_services_details.segment_id))  
where plan_services_details.CategoryCode in (8,16,29)   
GROUP BY county_plan_services.CountyFIPSCode,plan_services_details.liability_amount,
plan_services_details.CategoryDescription, county_plan_services.geo_name,plan_services_details.Benefit, county_plan_services.org_name
DISTRIBUTE by CountyFIPSCode 
SORT BY CountyFIPSCode ASC;
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/    |