Divya Reddy Narahari

Software Engineer Intern CommerceHub and Computer Instructor at Albany Public Library

 Actively seeking Full time opportunities in roles : Software Developer or Software Engineer or Hadoop Developer.

- Computer Science Master’s student with 2.5 years experience in Software Development.Currently interning at CommerceHub which is a provider of hosted integration, drop ship fulfillment, and product content management for multi-channel e-commerce merchants.

- Previously worked at New York State Mesonet as Software Engineer Intern, New York State Education Department as a Java Developer Intern and Standard and Poor's Capital IQ as ETL Quality Engineer.


Please Note

- Expected Graduation Date:12th August 2016

- Will require H-1B Sponsorship.

- Willing to relocate anywhere in United States.

[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 Divya Reddys project submission and find it complete and meets all the requirements set out for the IBM certification, You may proceed with IBM certification.

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 script to perform cleansing of data
REGISTER /home/hadoop/pig-0.11.1/lib/piggybank-0.12.0.jar;
vwPlanServices = LOAD '/Data/medicare/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:long,benefit:chararray,package_name:chararray,package_id:chararray,sentences_sort_order:int);
vwPlanServicesEnglish = FILTER vwPlanServices by language=='English' and (plan_id is not null and segment_id is not null and contract_id is not null);
STORE vwPlanServicesEnglish INTO '/Output/vwPlanServicesEnglish';
pig script to perform cleansing of data
REGISTER /home/hadoop/pig-0.11.1/lib/piggybank-0.12.0.jar;

PlanInfoCounty_FipsCodeMoreThan30000=LOAD '/Data/medicare/PlanInfoCounty_FipsCodeMoreThan30000.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_Status_Code:chararray,Tax_status_desc:chararray,SP_tax_status_desc:chararray,Plan_Type:long,Plan_type_desc:chararray,Web_Address:chararray,PartD_Wb_Address: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:long,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,StateCode_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:long);

PlanInfoCounty_FipsCodeLessThan30000= LOAD '/Data/medicare/PlanInfoCounty_FipsCodeLessThan30000.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_Status_Code:chararray,Tax_status_desc:chararray,SP_tax_status_desc:chararray,Plan_Type:long,Plan_type_desc:chararray,Web_Address:chararray,PartD_Wb_Address: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:long,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,StateCode_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:long);
PlanInfo= UNION PlanInfoCounty_FipsCodeMoreThan30000,PlanInfoCounty_FipsCodeLessThan30000;
PlanInfo = FILTER PlanInfo by (Plan_ID is not null and Segment_ID is not null and Contract_ID is not null);
STORE PlanInfo INTO '/Output/PlanInfo';
Identify top 5 plans with lowest premiums
USE medicare;
DROP TABLE Premiums_tbl;
CREATE TABLE Premiums_tbl AS
SELECT S.contract_id,S.plan_id,S.segment_id,S.categoryCode,S.categoryDescription,I.plan_name,
	   I.CountyFIPSCode,regexp_extract(S.benefit,'<[a-z]>[$](.*)',1) as premium
FROM planServices S
INNER JOIN PlanInfo I ON S.contract_id=I.Contract_ID
	AND S.plan_id=I.Plan_ID 
	AND S.segment_id=I.Segment_ID
WHERE S.benefit LIKE '%remium%'
	AND S.categoryCode="1"
	AND regexp_extract(S.benefit,'<[a-z]>[$](.*)',1)>0;

ALTER TABLE Premiums_tbl CHANGE premium premium FLOAT;
To find the 5 lowest premiums for a county:
SELECT * FROM Premiums_tbl WHERE COUNTYFIPSCODE=40019 ORDER BY premium LIMIT 5;
To find plans that have highest co-pays for doctors in a given county
USE medicare;
DROP TABLE Doctors_tbl;
CREATE TABLE Doctors_tbl AS
SELECT S.contract_id,S.plan_id,S.segment_id,S.categoryCode,S.categoryDescription,I.plan_name,
	   I.CountyFIPSCode,regexp_extract(S.benefit,'<[a-z]>[$](.*) copay',1) as copay
FROM planServices S
INNER JOIN PlanInfo I ON S.contract_id=I.Contract_ID
	AND S.plan_id=I.Plan_ID 
	AND S.segment_id=I.Segment_ID
WHERE S.categoryDescription LIKE '%Doctor\'s%' 
	AND (S.benefit LIKE '%Primary%' OR S.benefit LIKE '%Specialist%')
	AND S.categoryCode="10"
	AND regexp_extract(S.benefit,'[$](.*) copay',1)>0;
	
ALTER TABLE Primary_Doctors_tbl CHANGE copay copay FLOAT;
To find the doctors with highest copay
SELECT * FROM doctors_tbl where countyfipscode=17017 ORDER BY copay DESC LIMIT 5;
To compare plans based on features like plans that offer free ambulance services
USE medicare;
DROP TABLE Plans_FreeAmbulance_tbl;
CREATE TABLE Plans_FreeAmbulance_tbl AS
SELECT S.contract_id,S.plan_id,S.segment_id,S.categoryCode,S.categoryDescription,I.plan_name,
	   I.CountyFIPSCode,regexp_extract(S.benefit,'<[a-z]>(.*?)',1) as Payment
FROM planServices S
INNER JOIN PlanInfo I ON S.contract_id=I.Contract_ID
	AND S.plan_id=I.Plan_ID 
	AND S.segment_id=I.Segment_ID
WHERE S.categoryDescription LIKE '%Ambulance Services%'
	AND S.categoryCode="5"
	AND regexp_extract(S.benefit,'<[a-z]>(.*?)',1) LIKE 'You pay nothing';
Get top 5 free ambulance plans from medicare plans
SELECT * FROM plans_freeambulance_tbl LIMIT 5;
To compare plans based on features like the benefits available for diabetes under specific plan
USE medicare;
DROP TABLE DiabetesPlans_tbl;
CREATE TABLE DiabetesPlans_tbl AS
SELECT S.contract_id,S.plan_id,S.segment_id,S.categoryCode,S.categoryDescription,I.plan_name,I.CountyFIPSCode,S.Benefit
FROM planServices S
INNER JOIN PlanInfo I ON S.contract_id=I.Contract_ID
	AND S.plan_id=I.Plan_ID 
	AND S.segment_id=I.Segment_ID
WHERE (S.categoryDescription LIKE '%Diabetes%' AND S.categoryCode="8")
	OR 
	S.benefit LIKE '%diabetes%';
The below script can be used to find the benefits for diabetes for a specific plan 004
SELECT DISTINCT contract_id,plan_id,segment_id,categoryCode,categoryDescription,plan_name,CountyFIPSCode,Benefit
 from diabetesplans_tbl  
where plan_id="004" LIMIT 15;
5. To compare plan benefits on diabetes and mental healthcare offered by all companies in a particular county
USE medicare;
DROP TABLE Diabetes_Mental_Plans_tbl;
CREATE TABLE Diabetes_Mental_Plans_tbl AS
SELECT S.contract_id,S.plan_id,S.segment_id,S.categoryCode,S.categoryDescription,I.plan_name,
	   I.CountyFIPSCode,S.Benefit
FROM planServices S
INNER JOIN PlanInfo I ON S.contract_id=I.Contract_ID
	AND S.plan_id=I.Plan_ID 
	AND S.segment_id=I.Segment_ID
WHERE (S.categoryDescription LIKE '%Diabetes%' AND S.categoryCode="8")
	OR (S.categoryDescription LIKE '%Mental Health%' AND S.categoryCode="16")
	OR S.benefit LIKE '%diabetes%';

Query to retrieve plan benefits on diabetes and mental healthcare for a particular county=15003
SELECT DISTINCT  plan_id,benefit from diabetes_mental_plans_tbl where countyfipscode=15003 LIMIT 5;
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/    |