Jithender Nath Reddy

Technical Lead at Wipro

Technical Lead

Wipro

August 2011 – Present (5 years)Collierville Tennessee

Senior System Engineer

Infosys

October 2005 – August 2011 (5 years 11 months)Hyderabad Area, India

[No canvas support]

Project: Medicare Data Analysis.

Faculty Feedback

Prashanth G

Practice Lead - Healthcare at TCS

"This is to confirm that I have reviewed Jitendernath's Project and satisfied that it is complete to all the requirements we set it forth. 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

Pig Latin Scripts
-- Registering Downloaded PiggyBank jar function library 
REGISTER /usr/lib/pig/piggybank.jar;
DEFINE CSVExcelStorage org.apache.pig.piggybank.storage.CSVExcelStorage();
Pig Script for loading Plan Info table:
-- Load comma delimited, quote qualified file using PiggyBank function 

PlanInfoCountyWithoutQuotes1 = LOAD '/user/cloudera/example/PlanInfoCounty_FipsCodeLessThan30000.csv' USING CSVExcelStorage(',') AS (Contract_ID,Plan_ID,Segment_ID,A,Org_Name,Plan_Name,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,BB,CC,DD,EE,FF,GG,HH,II,JJ,KK,LL,MM,NN,OO,PP,QQ,RR,SS,TT,UU,VV,WW,XX,YY,ZZ,AAA,BBB,CCC,DDD,EEE,FFF,GGG,HHH,III,JJJ,KKK,LLL,MMM,NNN,OOO,PPP,QQQ,CountyFIPSCode);

PlanInfoCountyWithoutQuotes2 = LOAD '/user/cloudera/example/PlanInfoCounty_FipsCodeMoreThan30000.csv' USING CSVExcelStorage(',') AS (Contract_ID,Plan_ID,Segment_ID,A,Org_Name,Plan_Name,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,BB,CC,DD,EE,FF,GG,HH,II,JJ,KK,LL,MM,NN,OO,PP,QQ,RR,SS,TT,UU,VV,WW,XX,YY,ZZ,AAA,BBB,CCC,DDD,EEE,FFF,GGG,HHH,III,JJJ,KKK,LLL,MMM,NNN,OOO,PPP,QQQ,CountyFIPSCode);
-- Narrow down the number of fields required for analysis 

PlanInfoCountyReqCols1 = FOREACH PlanInfoCountyWithoutQuotes1 GENERATE Contract_ID,Plan_ID,Segment_ID,Org_Name,Plan_Name,CountyFIPSCode;

PlanInfoCountyReqCols2 = FOREACH PlanInfoCountyWithoutQuotes2 GENERATE Contract_ID,Plan_ID,Segment_ID,Org_Name,Plan_Name,CountyFIPSCode;

-- Filter the records for analysis

PlanInfoCountyFiltered1= FILTER PlanInfoCountyReqCols1 BY ((Contract_ID IS NOT NULL) AND (Plan_ID IS NOT NULL) AND (Segment_ID IS NOT NULL) AND (CountyFIPSCode IS NOT NULL));

PlanInfoCountyFiltered2= FILTER PlanInfoCountyReqCols2 BY ((Contract_ID IS NOT NULL) AND (Plan_ID IS NOT NULL) AND (Segment_ID IS NOT NULL) AND (CountyFIPSCode IS NOT NULL));

-- Combine the results of the two structurally equivalent files 

PlanInfoCountyDetails = UNION PlanInfoCountyFiltered1,PlanInfoCountyFiltered2;

-- Store the results into HDFS in comma delimited format 

STORE PlanInfoCountyDetails INTO '/user/cloudera/example/PlanInfoCountyDetailsNew1' USING PigStorage(',');
Pig Script for loading Plan Services files
Load comma delimited, quote qualified file using PiggyBank function

vwPlanServicesWithoutQuotes = LOAD '/user/cloudera/example/vwPlanServices.csv' USING CSVExcelStorage(',') as (Language,Contract_Year,Contract_ID,Plan_ID,Segment_ID,CategoryDescription,CategoryCode,Benefit,Package_Name,Package_ID,Sentences_Sort_Order);

-- Filter the records for analysis 

vwPlanServicesFilteredNew= FILTER vwPlanServicesWithoutQuotes BY ((Language != 'Spanish') AND (CategoryDescription IS NOT NULL) AND (Benefit IS NOT NULL) AND (Contract_ID IS NOT NULL) AND (Plan_ID IS NOT NULL) AND (Segment_ID IS NOT NULL)); 

-- Filter to get only Premium records 

vwPlanServicesForPremium = FILTER vwPlanServicesFilteredNew BY (CategoryCode == '1');

-- Filter to get only CoPay records 

vwPlanServicesForCoPay = FILTER vwPlanServicesFilteredNew BY (CategoryCode == '10');

-- Store the results into HDFS in comma delimited format 

STORE vwPlanServicesFilteredNew INTO '/user/cloudera/example/vwPlanServicesNew' USING PigStorage(',');

STORE vwPlanServicesForPremium INTO '/user/cloudera/example/vwPlanServicesPremium' USING PigStorage(',');

STORE vwPlanServicesForCoPay INTO '/user/cloudera/example/vwPlanServicesCoPay' USING PigStorage(',');
HIVE Scripts
-- Create Database 
CREATE DATABASE HEALTHCARE;

USE HEALTHCARE;


-- Create Plan Info Table 

CREATE EXTERNAL TABLE PlanInfoDetails(
    Contract_ID STRING
    ,Plan_ID STRING
    ,Segment_ID STRING
    ,Org_Name STRING
    ,Plan_Name STRING
    ,CountyFIPSCode STRING)COMMENT 'This is Plan Info table'
    PARTITIONED BY (FILENAME STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n' STORED AS TEXTFILE;
    

ALTER TABLE PlanInfoDetails ADD PARTITION (FILENAME = 'part-m-00000');
ALTER TABLE PlanInfoDetails ADD PARTITION (FILENAME = 'part-m-00001');
ALTER TABLE PlanInfoDetails ADD PARTITION (FILENAME = 'part-m-00002');
ALTER TABLE PlanInfoDetails ADD PARTITION (FILENAME = 'part-m-00003');
ALTER TABLE PlanInfoDetails ADD PARTITION (FILENAME = 'part-m-00004');
ALTER TABLE PlanInfoDetails ADD PARTITION (FILENAME = 'part-m-00005');
ALTER TABLE PlanInfoDetails ADD PARTITION (FILENAME = 'part-m-00006');
ALTER TABLE PlanInfoDetails ADD PARTITION (FILENAME = 'part-m-00007');
ALTER TABLE PlanInfoDetails ADD PARTITION (FILENAME = 'part-m-00008');
ALTER TABLE PlanInfoDetails ADD PARTITION (FILENAME = 'part-m-00009');



-- LoadPlan Info Data into Plan Info Table 


LOAD DATA INPATH '/user/cloudera/example/PlanInfoCountyDetailsNew1/part-m-00000' INTO TABLE PlanInfoDetails PARTITION (FILENAME = 'part-m-00000');
LOAD DATA INPATH '/user/cloudera/example/PlanInfoCountyDetailsNew1/part-m-00001' INTO TABLE PlanInfoDetails PARTITION (FILENAME = 'part-m-00001');
LOAD DATA INPATH '/user/cloudera/example/PlanInfoCountyDetailsNew1/part-m-00002' INTO TABLE PlanInfoDetails PARTITION (FILENAME = 'part-m-00002');
LOAD DATA INPATH '/user/cloudera/example/PlanInfoCountyDetailsNew1/part-m-00003' INTO TABLE PlanInfoDetails PARTITION (FILENAME = 'part-m-00003');
LOAD DATA INPATH '/user/cloudera/example/PlanInfoCountyDetailsNew1/part-m-00004' INTO TABLE PlanInfoDetails PARTITION (FILENAME = 'part-m-00004');
LOAD DATA INPATH '/user/cloudera/example/PlanInfoCountyDetailsNew1/part-m-00005' INTO TABLE PlanInfoDetails PARTITION (FILENAME = 'part-m-00005');
LOAD DATA INPATH '/user/cloudera/example/PlanInfoCountyDetailsNew1/part-m-00006' INTO TABLE PlanInfoDetails PARTITION (FILENAME = 'part-m-00006');
LOAD DATA INPATH '/user/cloudera/example/PlanInfoCountyDetailsNew1/part-m-00007' INTO TABLE PlanInfoDetails PARTITION (FILENAME = 'part-m-00007');
LOAD DATA INPATH '/user/cloudera/example/PlanInfoCountyDetailsNew1/part-m-00008' INTO TABLE PlanInfoDetails PARTITION (FILENAME = 'part-m-00008');
LOAD DATA INPATH '/user/cloudera/example/PlanInfoCountyDetailsNew1/part-m-00009' INTO TABLE PlanInfoDetails PARTITION (FILENAME = 'part-m-00009');

-- Create Plan Services Table 


CREATE EXTERNAL TABLE PlanServices(
Language STRING
,Contract_Year STRING
    ,Contract_ID STRING
    ,Plan_ID STRING
    ,Segment_ID STRING
    ,CategoryDescription STRING
    ,CategoryCode STRING
    ,Benefit STRING
    ,Package_Name STRING
    ,Package_ID STRING
    ,Sentences_Sort_Order STRING)COMMENT 'This is Plan Services table'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n' STORED AS TEXTFILE;

-- LoadPlan services Data into Plan service Table 

LOAD DATA INPATH '/user/cloudera/example/vwPlanServicesNew/part-m-00000' INTO TABLE PlanServices;

-- Create Premium Table 

CREATE EXTERNAL TABLE PlanServicesPremiumFinal(
Language STRING
,Contract_Year STRING
    ,Contract_ID STRING
    ,Plan_ID STRING
    ,Segment_ID STRING
    ,CategoryDescription STRING
    ,CategoryCode STRING
    ,Benefit STRING
    ,Package_Name STRING
    ,Package_ID STRING
    ,Sentences_Sort_Order STRING)COMMENT 'This is Premium Plan Service table'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n' STORED AS TEXTFILE;

-- LoadPremium Data into Premium Table     

LOAD DATA INPATH '/user/cloudera/example/vwPlanServicesPremium/part-m-00000' INTO TABLE PlanServicesPremiumFinal;

-- Create CoPay Table

CREATE EXTERNAL TABLE PlanServicesCoPayFinal(
    Language STRING
    ,Contract_Year STRING
    ,Contract_ID STRING
    ,Plan_ID STRING
    ,Segment_ID STRING
    ,CategoryDescription STRING
    ,CategoryCode STRING
    ,Benefit STRING
    ,Package_Name STRING
    ,Package_ID STRING
    ,Sentences_Sort_Order STRING)COMMENT 'This is CoPay Plan Service table'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n' STORED AS TEXTFILE;

-- LoadCoPay Data into CoPay Table 

LOAD DATA INPATH '/user/cloudera/example/vwPlanServicesCoPay/part-m-00000' INTO TABLE PlanServicesCoPayFinal;
Analysis for Top 5 plans with lowest premiums by County
CREATE TABLE PlanServicesPreLowFinal AS         
SELECT Contract_ID
,Plan_ID
,Segment_ID
,CategoryDescription
,regexp_extract(Benefit,'<[a-z]>[$](.*)',1) AS Premium
FROM PlanServicesPremiumFinal 
WHERE Benefit rlike '.*remium.*' 
AND regexp_extract(Benefit,'<[a-z]>[$](.*)',1) > 0;

CREATE TABLE PlanServicesLowPremResult AS
SELECT A.Contract_ID
,A.Plan_ID
,A.Segment_ID
,A.CategoryDescription
,B.Plan_Name
,B.CountyFIPSCode
,A.Premium
FROM PlanServicesPreLowFinal A
INNER JOIN PlanInfoDetails B ON (TRIM(A.Contract_ID) = TRIM(B.Contract_ID)
AND TRIM(A.Plan_ID) = TRIM(B.Plan_ID) 
AND TRIM(A.Segment_ID) = TRIM(B.Segment_ID));

ALTER TABLE PlanServicesLowPremResult CHANGE CountyFIPSCode CountyFIPSCode BIGINT;
ALTER TABLE PlanServicesLowPremResult CHANGE Premium Premium FLOAT;


SELECT RANKED.CountyFIPSCode
,RANKED.Contract_ID
,RANKED.Plan_ID
,RANKED.Segment_ID
,RANKED.Premium
,RANKED.rank FROM (
SELECT CountyFIPSCode
,Contract_ID
,Plan_ID
,Segment_ID
,Premium
,rank() over(PARTITION BY CountyFIPSCode ORDER BY Premium ASC) AS rank
FROM PlanServicesLowPremResult)RANKED
WHERE RANKED.rank <=5
ORDER BY CountyFIPSCode ,Premium ASC;
Plans with co-pays for Primary care physician visit
CREATE TABLE PlanServicesPrimarycopay AS
SELECT Contract_ID
,Plan_ID
,Segment_ID
,CategoryDescription
,regexp_extract(Benefit,'<[a-z]>[$](.*)',1) AS Copay
FROM PlanServicesCoPayFinal 
WHERE ((Benefit rlike '.*copay.*' AND Benefit rlike '.*Primary care physician visit.*' AND Benefit rlike '.*In-network.*') OR (Benefit rlike '.*copay.*' AND Benefit rlike '.*Primary care physician visit.*'))
AND regexp_extract(Benefit,'<[a-z]>[$](.*)',1) > 0;

CREATE TABLE PlanServicesPrimaryCopayResult AS
SELECT A.Contract_ID
,A.Plan_ID
,A.Segment_ID
,A.CategoryDescription
,B.Plan_Name
,B.CountyFIPSCode
,A.Copay
FROM PlanServicesPrimarycopay A
INNER JOIN PlanInfoDetails B ON (TRIM(A.Contract_ID) = TRIM(B.Contract_ID)
AND TRIM(A.Plan_ID) = TRIM(B.Plan_ID) 
AND TRIM(A.Segment_ID) = TRIM(B.Segment_ID));

ALTER TABLE PlanServicesPrimaryCopayResult CHANGE CountyFIPSCode CountyFIPSCode BIGINT;
ALTER TABLE PlanServicesPrimaryCopayResult CHANGE Copay Copay INT;


SELECT CountyFIPSCode
,Contract_ID
,Plan_ID
,Segment_ID
,Copay
FROM PlanServicesPrimaryCopayResult
ORDER BY CountyFIPSCode ,Copay DESC;
Plans with co-pays for Specialist visit
CREATE TABLE PlanServicesSpecialistcopay AS
SELECT Contract_ID
,Plan_ID
,Segment_ID
,CategoryDescription
,regexp_extract(Benefit,'<[a-z]>[$](.*)',1) AS Copay
FROM PlanServicesCoPayFinal 
WHERE ((Benefit rlike '.*copay.*' AND Benefit rlike '.*Specialist visit.*' AND Benefit rlike '.*In-network.*') OR (Benefit rlike '.*copay.*' AND Benefit rlike '.*Specialist visit.*'))
AND regexp_extract(Benefit,'<[a-z]>[$](.*)',1) > 0;

CREATE TABLE PlanServicesSpecialistCopayResult AS
SELECT A.Contract_ID
,A.Plan_ID
,A.Segment_ID
,A.CategoryDescription
,B.Plan_Name
,B.CountyFIPSCode
,A.Copay
FROM PlanServicesSpecialistcopay A
INNER JOIN PlanInfoDetails B ON (TRIM(A.Contract_ID) = TRIM(B.Contract_ID)
AND TRIM(A.Plan_ID) = TRIM(B.Plan_ID) 
AND TRIM(A.Segment_ID) = TRIM(B.Segment_ID));

ALTER TABLE PlanServicesSpecialistCopayResult CHANGE CountyFIPSCode CountyFIPSCode BIGINT;
ALTER TABLE PlanServicesSpecialistCopayResult CHANGE Copay Copay INT;


SELECT CountyFIPSCode
,Contract_ID
,Plan_ID
,Segment_ID
,Copay
FROM PlanServicesSpecialistCopayResult
ORDER BY CountyFIPSCode ,Copay DESC;
Analysis forplans that offer free ambulance services
SELECT Contract_ID
,Plan_ID
,Segment_ID
,CategoryDescription
,Benefit
FROM PlanServices
WHERE TRIM(CategoryCode) = '5' 
AND Benefit rlike '.*You pay nothing.*' 
ORDER BY Contract_ID,Plan_ID,Segment_ID ASC;
Analysis forplans that provideDiabetes services
SELECT DISTINCT Contract_ID,Plan_ID,Segment_ID,CategoryDescription
FROM PlanServices
WHERE TRIM(CategoryCode) = '8' 
ORDER BY Contract_ID,Plan_ID,Segment_ID ASC;
Analysis forplans that provideboth Diabetesand Mental Healthcare servicesin a given county
SELECT PI.CountyFIPSCode,RS.Contract_ID,RS.Plan_ID,RS.Segment_ID
FROM(SELECT A.Contract_ID,A.Plan_ID,A.Segment_ID
FROM (SELECT DISTINCT Contract_ID,Plan_ID,Segment_ID
FROM PlanServices
WHERE TRIM(CategoryCode) = '8' 
ORDER BY Contract_ID,Plan_ID,Segment_ID ASC) A 
,(SELECT DISTINCT Contract_ID,Plan_ID,Segment_ID
FROM PlanServices
WHERE TRIM(CategoryCode) = '16' 
ORDER BY Contract_ID,Plan_ID,Segment_ID ASC) B
WHERE (TRIM(A.Contract_ID) = TRIM(B.Contract_ID)
AND TRIM(A.Plan_ID) = TRIM(B.Plan_ID) 
AND TRIM(A.Segment_ID) = TRIM(B.Segment_ID)))RS
INNER JOIN PlanInfoDetails PI ON (TRIM(RS.Contract_ID) = TRIM(PI.Contract_ID)
AND TRIM(RS.Plan_ID) = TRIM(PI.Plan_ID) 
AND TRIM(RS.Segment_ID) = TRIM(PI.Segment_ID))
ORDER BY CountyFIPSCode ASC;
processing person-icon