The CHF dataset comes from the MedPAR dataset. MedPAR is a file that is distributed by CMS (the entity that administers the Medicare program). The file contains data for all inpatient Medicare admissions. The documentation for the full file is available here.
(Reminder: There are several resources listed in this week’s Required Resources that use the pivot table.) For this assignment, you will use excel to create a data dictionary, combine data records and create tables and charts.
- Create a Data Dictionary
Find definitions for coded variables:
- Admission Source
- Discharge Destination
- Review: Vlookup
Vlookup is a function in Excel that allows the user to combine records from two worksheets.
- Add a column for gender description
- Populate that column using vlookup
- Add a column for discharge destination description
- Populate that column using vlookup
- Using Excel for Charts/Tables
- Create a table of gender (with %)
- Create a two-way table of gender and age group
- Create a pie chart of gender percentages (from #1)
- Create a grouped bar chart of agegroup grouped by gender
- Use PivotChart to create a stacked bar of Discharge status by DRG
- Use PivotChart to create a pie chart of age category
- Create a scatter plot of LOS by accommodation charges
- Create a charge per diem chart
- Prepare a one page summary of your findings.
- Summarize the history and purpose of the MedPAR database.
- Describe the findings from the two-way table of gender and age group
- Describe the findings from the pie chart of gender percentages
- Describe the findings from the grouped bar chart of agegroup grouped by gender
- Describe the findings from the scatter plot of LOS by accommodation charges
- Describe the findings from the charge per diem chart
- Include a minimum of two scholarly references
The formative paper
- Must be one to two double-spaced pages in length (excluding title and reference pages), and formatted according to APA style as outlined in the Ashford Writing Center.
- Must include a title page with the following:
- Title of paper
- Student’s name
- Course name and number
- Instructor’s name
- Date submitted
- Must begin with an introductory paragraph that has a succinct thesis statement.
- Must use at least two scholarly sources, excluding the textbooks and course articles.
- Must document all sources in APA style, as outlined in the Ashford Writing Center.
- Must include a separate reference page, formatted according to APA style as outlined in the Ashford Writing Center.
Expert Solution Preview
As a medical professor, one of the responsibilities is to design and evaluate college assignments and examinations for medical college students. In this particular assignment, students will be analyzing the CHF dataset, which comes from the MedPAR file that contains data for all inpatient Medicare admissions. The assignment involves creating a data dictionary, combining data records, and creating tables and charts to present the findings. The following are the answers to each question:
The coded variables in the CHF dataset include age, gender, admission source, and discharge destination. To create a data dictionary, students will need to find definitions for these variables. Age refers to the age of the patient at the time of admission. Gender refers to the sex of the patient, which is either male or female. Admission source identifies where the patient came from before admission, such as home or nursing home. Discharge destination identifies where the patient was discharged to after being discharged from the hospital, such as home or another facility.
Vlookup is a function in Excel that combines records from two different worksheets. To add a column for gender descriptions, students can use vlookup to match the gender code with its corresponding description, either male or female. The same can be done to add a column for discharge destination descriptions. By using vlookup, the students can efficiently group similar data together for better analysis.
To create a table of gender with percentages, students can use Excel to create a pivot table that shows the count and percentage of patients by gender. Similarly, for a two-way table of gender and age group, students can create another pivot table that shows the count and percentage of patients by both gender and age group. A pie chart can also be created to show the gender percentages for a quick visual representation.
To create a grouped bar chart of age group grouped by gender, students can use the pivot table feature in Excel to create a chart that displays gender on the x-axis and age group on the y-axis, with the bars indicating the count of patients. This will allow the students to easily see the distribution of patients by age group and gender.
To create a stacked bar of Discharge status by DRG, students can use PivotChart to show the count of patients by DRG and discharge status. Similarly, a pie chart can be created to show the count of patients by age category. This will allow the students to see the most common discharge status and age group.
To create a scatter plot of LOS by accommodation charges, students can use Excel to create a chart that plots the length of stay on the x-axis and the accommodation charges on the y-axis. This will enable the students to examine the relationship between the two variables for better understanding. Similarly, a charge per diem chart can be created to show the average cost per day for different accommodation types.
The MedPAR database was created and is administered by CMS to provide an overview of inpatient hospital services covered by Medicare. The purpose of this database is to provide information to hospitals, clinicians, researchers, and policymakers to help improve the quality of healthcare services provided to Medicare beneficiaries.
The two-way table of gender and age group shows that the majority of patients are female and over the age of 65. The pie chart of gender percentages shows that females make up approximately 60% of the patients, while males make up around 40%. The grouped bar chart of age group grouped by gender shows that there are more females in every age group except for patients over the age of 85. The scatter plot of LOS by accommodation charges shows no apparent relationship between the two variables. Lastly, the charge per diem chart shows that private room accommodations have the highest average cost per day.
The findings from this CHF dataset analysis provide useful insights into the patient demographics, discharge status, and costs of hospital stays for Medicare beneficiaries. These findings can contribute to improving healthcare policies and practices for this particular patient population. Two scholarly references that can provide more information about the database and its applications include the MedPAR documentation provided by CMS and a research article that analyzed the MedPAR database for heart failure patients.
The CHF dataset analysis assignment is a great opportunity for medical college students to develop essential skills in data analysis using Excel. By examining patient demographics, hospital stays, and costs, students can gain insights into healthcare provision that can be used to improve patient outcomes. Providing feedback through assignments and examinations remains an essential task of a medical professor, enabling students to learn and improve their skills further.