Data analysis Project using Excel and R (Health Information Management)Paper details:<\/p>\n
Step 1: Business Understanding<\/p>\n
1. There are wide discrepancies of charges and payments between institutions<\/p>\n
a. Larger hospitals charge more and receive higher payments<\/p>\n
b. Urban hospitals charge more, but do not receive higher payments<\/p>\n
2. Are the variations due to excessive charging or lower payments <\/p>\n
a. Excess Charge = Charge\/Payment<\/p>\n
b. Cost-to-charge ratio = Payment\/Charge<\/p>\n
Step 2: Data Understanding<\/p>\n
1. IPPS Data<\/p>\n
a. Medicare Provider Utilization and Payment Data: Inpatient<\/p>\n
i. Total Discharges<\/p>\n
ii. Average Covered Charges<\/p>\n
iii. Average Total Payments<\/p>\n
2. Census Data- because of the size of this file this has been limited to NY ONLY \u2013 this file is text -CSV so it will have to be opened in EXCEL first.<\/p>\n
a. 2010 ZCTA to Metropolitan and Micropolitan Statistical Areas Relationship File<\/p>\n
i. Zipcode<\/p>\n
ii. CBSA<\/p>\n
Step 3: Data Preparation<\/p>\n
Filter the IPPS file to only include NY
\nAdd the CBSA from the Census Data file to the IPPS Data fileCopy the CBSA column and Paste Special as values only
\nUse VLOOKUP
\nRemove #N\/A values- Use Find\/Replace
\nInsert a new column
\nIn the new column, use the IF function to recategorize the hospital geography
\nIf the hospital has an identified CBSA, recategorize that hospital as urban
\nIf the hospital does not have a CBSA, recategorize that hospital as rural
\nCopy the Geography column and Paste Special as values only
\nCalculate Excessive charges= Charge-Payment
\nCalculate Cost-to-charge Ratio (CRR) = Payment\/Charge
\nCopy the Excess Charge and CCR columns and Paste Special as values only
\nSave the file as a .csv
\nAlso, save a version of the file as a .xlsx
\nIn the .xlsx version, click in any of the cells, format as a table (HOME \u2013 \u201cFormat as Table\u201d)
\nIn the .xlsx version, name the table (DESIGN \u2013 \u201cTable Name\u201d \u2013 enter \u201cDRG\u201d)
\nSave<\/p>\n
Step 4: Modeling<\/p>\n
1. Create a PIVOT TABLE of the count of hospitals for each geographic region (INSERT- PivotTable). REMEMBER: click the checkbox \u201cAdd this data to the Data Model\u201d<\/p>\n
2. Create a PIVOT TABLE to calculate the following for each geographic region:<\/p>\n
a. Average Total discharges<\/p>\n
b. Average Covered charges<\/p>\n
c. Average Total Payments<\/p>\n
d. Average Medicare Payments<\/p>\n
e. Average Excess charges<\/p>\n
f. Average Cost-to-charge ratio (CCR)<\/p>\n
3. Use COUNTIF to count the number of rural and urban hospitals (compare these results to what is provided in a PIVOT TABLE<\/p>\n
=COUNTIF(DRG[Geo],\u201dUrban\u201d)<\/p>\n
=COUNTIF(DRG[Geo],\u201dRural\u201d)<\/p>\n
4. Use SUMPRODUCT to count the number of rural and urban hospitals that have a cost-to-charge ratio greater than or equal to 0.5 and those less than 0.5 (How should we normalize these results Calculate the proportion!).<\/p>\n
=SUMPRODUCT((DRG[Geo]=\u201dUrban\u201d)*(DRG[CCR]<0.5))<\/p>\n
=SUMPRODUCT((DRG[Geo]=\u201dUrban\u201d)*(DRG[CCR]>=0.5))<\/p>\n
=SUMPRODUCT((DRG[Geo]=\u201dRural\u201d)*(DRG[CCR]<0.5))<\/p>\n
=SUMPRODUCT((DRG[Geo]=\u201dRural\u201d)*(DRG[CCR]>=0.5))<\/p>\n
5. Create a PIVOT TABLE of the count of each MS-DRG<\/p>\n
6. Create graphs to depict the above information (INSERT \u2013 CHARTS)<\/p>\n
7. Open R<\/p>\n
8. Open R commander<\/p>\n
a. Type the following into R:<\/p>\n
library(Rcmdr)<\/p>\n
9. Import the data into R Commander using the following script:<\/p>\n
dataset<- read.csv(file.choose())<\/p>\n
Locate the IPPS csv data file and click \u201cOK\u201d<\/p>\n
10. Activate the dataset in R commander<\/p>\n
a. Click <No active dataset> and find \u201cdataset\u201d<\/p>\n
b. Confirm the number of rows and columns as compared to the original dataset<\/p>\n
11. Obtain a summary of the following numeric data (Statistics \u2013 Summaries \u2013 Numeric Summaries \u2013 Hold down Ctrl and click the variable names shown below \u2013 Click OK):<\/p>\n
a. Average Covered charges<\/p>\n
b. Average Total Payments<\/p>\n
c. Average Medicare Payments<\/p>\n
d. Excess charges<\/p>\n
e. Cost-to-charge ratio (CCR)<\/p>\n
12. Create two graphs of the \u201cPlot of means\u201d to compare Total Average Charges, Total Average Payment, Excess Charge, and CRR by geographic location<\/p>\n
13. Use a two-sample T-test to determine if there are significant differences in the following data between rural and urban hospitals:<\/p>\n
a. Count of hospitals<\/p>\n
b. Total discharges<\/p>\n
c. Covered charges<\/p>\n
d. Total Payments<\/p>\n
e. Medicare Payments<\/p>\n
f. Excess charges<\/p>\n
g. Cost-to-charge ratio (CCR)<\/p>\n
Step 5: Evaluation<\/p>\n
1. Summarize the findings<\/p>\n
a. Are there confounding variables that we should have considered in our analysis <\/p>\n
i. Hint: Frequency of MS-DRG codes for each geographic location<\/p>\n
Step 6: Deployment<\/p>\n
1. How would these findings be relevant to your organization and what might your organization do with this sort of information <\/p>\n","protected":false},"excerpt":{"rendered":"
Data analysis Project using Excel and R (Health Information Management)Paper details: Step 1: Business Understanding 1. There are wide discrepancies of charges and payments between institutions a. Larger hospitals charge more and receive higher payments b. Urban hospitals charge more, Read More …<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-94284","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/94284","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/comments?post=94284"}],"version-history":[{"count":0,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/94284\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/media?parent=94284"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/categories?post=94284"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/tags?post=94284"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}