Data analysis and visualization activity

Data Analysis and Visualization Activity

Purpose

The following activity is designed to provide you with the opportunity to practice analyzing data and creating data visualizations.

Objectives

· Using Excel, analyze the data provided

· Summarize your findings including the average age, gender distribution, and the prevalence of falls, CAUTIs, medication errors, and MRSA in your dataset

· Using Excel, create data visualizations including a pivot table, bar chart, pie chart, line chart, and a stacked column chart

Overview

This semester you will be completing a Data Storytelling Hallmark project.

Part of that project includes analyzing data and creating data visualizations.

This activity is designed for practice. You will be provided with a sample dataset and will be guided through the steps for analyzing data, interpreting/summarizing data, and creating data visualizations.

To receive credit for this activity, please complete (instructions below) and submit this document to Canvas.

Data Analysis

Dataset

This dataset includes patient information such as age, gender, falls, catheter-associated urinary tract infections (CAUTIs), medication errors, and methicillin-resistant Staphylococcus aureus (MRSA) infections.

Table

Patient ID	Age	Gender	Falls (Yes/No)	CAUTIs (Yes/No)	Medication Errors (Yes/No)	MRSA (Yes/No)
1	65	Female	Yes	No	Yes	No
2	70	Male	No	Yes	No	Yes
3	55	Female	Yes	No	No	No
4	80	Male	No	Yes	Yes	No
5	60	Female	Yes	No	Yes	Yes
6	75	Male	No	Yes	No	No

Instructions

1. Open the Excel workbook and save it to your computer.

· Open the Excel dataset.

Note: The following instructions will walk you step-by-step through the process of calculating descriptive statistics, creating a pivot table, visualizing, analyzing, and interpreting the date.

Excel has created a quicker/easier way to complete these steps with the “Analyze Data” button: Analyze Data in Excel - Microsoft Support, but we will be walking through the step by step process for this assignment and your Hallmark assignment.

2. Calculate Descriptive Statistics:

· Mean Age: Use the AVERAGE function to calculate the mean age of the patients.

· In cell J2 place =AVERAGE(B2:B7)

· Gender Distribution: Use the COUNTIF function to count the number of males and females.

· In cell J5 place =COUNTIF(C2:C7, "Male")

· In cell K5 place =COUNTIF(C2:C7, "Female")

· Falls, CAUTIs, Medication Errors, and MRSA Prevalence: Use the COUNTIF function to count the number of patients with each condition.

· In cell L2 place =COUNTIF(D2:D7, "Yes")

· In cell M2 place cell M2 place =COUNTIF(E2:E7, "Yes")

· In cell N2 place =COUNTIF(F2:F7, "Yes")

· In cell O2 place =COUNTIF(G2:G7, "Yes")

3. Create a Pivot Table:

· Look at the bottom of the excel sheets to locate “Table 2”. This represents the number of patients by gender who encountered one or more of the criteria listed in the original data set.

· Highlight the entire dataset.

· Go to the Insert tab and select PivotTable.

· Place the PivotTable in a new worksheet. It will be located in “Sheet3” at the bottom.

· Drag Gender to the Rows (Axis, Categories) area and Falls, CAUTIs, Medication Errors, and MRSA to the Values area to see the counts by gender.

Example:

4. Visualize the Data:

· Create a bar chart to visualize the prevalence of falls, CAUTIs, medication errors, and MRSA by gender.

· Highlight the PivotTable data.

· Go to the Insert tab and select Bar Chart.

Example:

5. Analyze the Data:

· Calculate the percentage of patients with each condition.

· In cell B12 place =COUNTIF(D2:D7, "Yes")/COUNTA(A2:A7)*100

· In cell B13 place =COUNTIF(E2:E7, "Yes")/COUNTA(A2:A7)*100

· In cell B14 place =COUNTIF(F2:F7, "Yes")/COUNTA(A2:A7)*100

· In cell B15 place =COUNTIF(G2:G7, "Yes")/COUNTA(A2:A7)*100

6. Interpret the Results:

· Summarize your findings. Include the average age, gender distribution, and the prevalence of falls, CAUTIs, medication errors, and MRSA in your dataset.

Step-by-Step Instructions for Data Visualization

Note: Take a screenshot of each visualization as you create them and paste it on the page below.

1. Open Your Dataset:

· Ensure your dataset is entered into an Excel worksheet, with each column representing a different variable.

2. Create a Pivot Table:

· Highlight the entire dataset.

· Go to the Insert tab and select PivotTable.

· Choose to place the PivotTable in a new worksheet.

· Drag Gender to the Rows area and Falls, CAUTIs, Medication Errors, and MRSA to the Values area to see the counts by gender.

3. Create a Bar Chart:

· Highlight the data in your PivotTable.

· Go to the Insert tab and select Bar Chart.

· Choose the type of bar chart you prefer (e.g., clustered bar chart).

· Customize the chart by adding titles, labels, and adjusting colors as needed.

Note: Customize chart by using the + icon to add labels and the paintbrush icon to change colors/styles (icons located at the top, right corner of your chart).

Example:

4. Create a Pie Chart for Gender Distribution:

· Highlight the gender data in your dataset.

· Go to the Insert tab and select Pie Chart.

· Choose the type of pie chart you prefer.

· Customize the chart by adding titles and labels.

5. Create a Line Chart for Age Distribution:

· Highlight the age data in your dataset.

· Go to the Insert tab and select Line Chart.

· Choose the type of line chart you prefer.

· Customize the chart by adding titles and labels.

6. Create a Stacked Column Chart for Condition Prevalence:

· Highlight the data for Falls, CAUTIs, Medication Errors, and MRSA.

· Go to the Insert tab and select Stacked Column Chart.

· Customize the chart by adding titles, labels, and adjusting colors.

Tips for Effective Data Visualization

· Keep it Simple: Avoid cluttering your charts with too much information.

· Use Colors Wisely: Use contrasting colors to differentiate between data points.

· Label Clearly: Ensure all axes, titles, and data points are clearly labeled.

· Add Legends: Include legends to explain what different colors or symbols represent.

Activity Submission

Instructions: Fill in the information below. Save your document, and upload and submit it to Canvas along with your Excel workbook.

A. Data Visualizations

Using your Excel workbook, take a screenshot of each of the data visualizations you created.

Paste screenshots of each data visualization below (pivot table, bar chart, pie chart, line chart, stacked column chart).

Pivot Table:

Bar Chart:

Pie Chart:

Line Chart:

Stacked Column Chart:

B. Interpret the Results (Summary of Data)

Using the information from your data analysis, fill in each category with a clear and accurate analysis of the data. Include insightful observations.

Hints:

Look closely at each category (average age, gender distribution, prevalence of falls, prevalence of CAUTIs, prevalence of medication errors, prevalence of MRSA).

· What is the data telling you?

· What do you notice?

· Do you see any similarities?

· Do you spot any differences?

· Do you notice anything unusual?

Average Age:

Gender Distribution:

Prevalence of Falls:

Prevalence of CAUTIs:

Prevalence of Medication Errors:

Prevalence of MRSA:

C. Summary (Pulling it all together)

Provide a comprehensive and accurate interpretation of results with a detailed summary.

Hints: What does the combined data show? Using the analysis from section B., create a summary of the entire data.