Activity: Orientation to Data Analytics
Link
to Activity: https://web21.ehrgo.com/rd/?courseActivityId=13691
Learning objectives
1. Analyze
data to identify trends (4)
2. Utilize
technology for data collection, storage, analysis, and reporting of information
(3)
3. Analyze
statistical data for decision making (4)
Prerequisites
1.
Use of Microsoft
Excel® is required to complete this activity
2.
This activity is
the first activity in a sequential 5-activity series.
Student instructions
1.
If you have
questions about this activity, please contact your instructor for assistance.
2.
You will review the
de-identified chart that accompanies this activity. Your instructor has
provided you with a link to the Orientation
to Data Analytics I (BS) activity. Click on 2: Launch EHR to review the patient chart and begin this activity.
Refer to the patient chart and any suggested
resources to complete this activity.
Document your answers directly on this activity
document as you complete the activity. When you are finished, you will
save this activity document to your device and upload this activity
document with your answers to your Learning Management System (LMS).
Introduction
This activity will evaluate a microbiology report of culture and
sensitivity results for various microbes and antibiotics. Microsoft Excel® will
be used to analyze and compile the data to draw meaningful conclusions. You do not need to turn in the pivot tables
you will create in this activity to your instructor. You will only turn in your
answers to the questions below.
The activity
Foundational learning
Review the resource, About Culture &
Sensitivity Screening (found
under 1: Overview & Resources along with this activity document) and answer the following questions.
1. Why is a sensitivity analysis done?
2. What is needed for a sensitivity
analysis?
3. How is the sensitivity analysis
performed?
4. What does susceptible mean in a
sensitivity analysis?
5. What does intermediate mean in a
sensitivity analysis?
6. What does resistant mean in a sensitivity
analysis?
Application
Review the Culture & Sensitivity
(C&S) Report on the
Notes Tab of the de-identified EHR that accompanies this activity under 2:
Launch EHR and answer the following questions.
7. What was the specimen used in this
C&S?
8. What organism was cultured from this
specimen?
9. Which antibiotics were tested against
this specimen?
10. Based on the Culture & Sensitivity
(C&S) Report in the EHR, which antibiotic(s) should the provider prescribe for this patient, and
why?
Data organization & analysis
The General Hospital is conducting an internal review of the culture
and sensitivity results from patients treated for infections of the urine,
blood, and sputum over the last year. A report has been generated of all
cultures positive for either E. coli, Streptococcus, or K. pneumoniae bacteria
and the antibiotics these bacteria were sensitive to. Open the resource Mass Sensitivities Export (found under 1: Overview &
Resources along with this activity document) in Microsoft Excel®. A file with 5
columns of data will open. Follow the steps described below to analyze the
data, then answer the related questions.
Notice that it’s difficult to see trends in the data just by viewing
the data points (rows of data in the file). Pivot tables will be used to
compile and summarize the data for each variable.
§ Click and drag from on field 1A to field
1100E to highlight and select all of the data.
§ Go to Insert then PivotTable. Leave
the default settings and select OK.
The pivot table building tool will open in a new tab.
First, determine which antibiotic is most effective (Susceptible) for each
type of specimen. To do so, look at the number of susceptible results for each
antibiotic by specimen type. A table with each specimen as a row and each antibiotic
as the column is one way to view these data. To make this table:
§ Click and drag the Susceptibilities field
to the Filters area. This will allow view of only the Susceptible (effective)
results.
§ Click and drag the Specimen field to the
Rows area. This will display each specimen as a row in the table.
§ Click and drag the Antibiotics field to
the Columns area. This will display each antibiotic as a column in the table.
§ Click and drag the Susceptibilities field
to the ∑ Values area. It should then appear as ‘Count of Susceptibilities’.
This will populate the table with the total number of susceptibilities for each
combination of specimen and antibiotic.
The resulting table should appear as follows:
Only display the results that have a Susceptibilities result of
‘Susceptible’ (meaning the antibiotic was effective). To do so:
§
Select the dropdown
arrow in cell B1 where it currently says ‘(All)’.
§
Choose Select Multiple Items and remove the
checks so that only ‘Susceptible’ is checked. Then OK.
Based on the resulting pivot table, answer questions 11-13 below by
determining which antibiotic has the highest susceptibility count for each
specimen type. Then answer questions 14-15 based on the Grand Total for each
antibiotic.
Questions
11. Which antibiotic is most effective in
urine-based infections? Explain.
12. Which antibiotic is most effective in
blood-based infections? Explain.
13. Which antibiotic is most effective in
sputum-based infections? Explain.
14. Which antibiotic is most effective overall?
Explain.
15. Which antibiotic is least effective overall?
Explain.
Now, determine which specimen has the most resistance for each type of
microbe. To do so, change the Susceptibilities filter in cell B1 from
‘Susceptible’ to ‘Resistant’. Answer question 16 below.
Question
16. Which type of specimen has the most resistance?
Explain.
Next, assess which antibiotic is most effective for the various types
of infections. Return to the Culture & Sensitivity Data tab and create a
new pivot table. If your columns aren’t already selected, repeat that step and then
choose Insert and PivotTable and OK.
·
Click and
drag the ‘Susceptibilities’ field to the Filters area. This will allow the
susceptible (effective) results to be isolated.
·
Click and
drag the ‘Specimen’ field to the Filters area. This will allow the type of
specimen for the results to also be isolated.
·
Click and
drag the ‘Antibiotics’ field to the Columns area. This will display each
antibiotic type as a column in the table.
·
Click and
drag the ‘Microbes’ field to the Rows area. This will display each microbe type
as a row in the table.
·
Click and
drag the ‘Susceptibilities’ field to the ∑ Values area. This will populate the
table with the total number of susceptibilities for each microbe and antibiotic
combination.
·
Specify the
susceptibility of ‘Susceptible’ in cell B1 to display only the effective
results.
·
Specify the
specimen of ‘Urine’ in cell B2 to display only the results for urine.
Use the results in
the pivot table to answer question 17 below.
Question
17. Which antibiotic would you recommend be tried
with a urine-based E. coli infection? Explain.
·
Change the
specimen filter (cell B2) from Urine to Blood
and answer question 18.
Question
18. Which antibiotic would you recommend be tried
with a blood-based Streptococcus infection? Explain.
Lastly, determine
if overall age plays a role in susceptibilities. Return to the original Culture
& Sensitivity Data set tab and insert a new pivot table again. Follow the
steps below:
·
Click and
drag the ‘Susceptibilities’ field to the Filters area. This will allow the susceptible
(effective) results to be specified.
·
Click and
drag the ‘Age’ field to the Rows area. This will display each age as a row in
the table.
·
Click and
drag the ‘Susceptibilities’ field to the ∑ Values area. It should show as
‘Count of Susceptibilities’. This will populate the table with the total number
of susceptibilities for each age.
·
In the Susceptibilities
filter (cell B1), specify ‘Susceptible’. The resulting table should appear as
follows.
When analyzing ages and/or dates, it’s often helpful to group them by
ranges. In this case, group the ages by 10 to display the results for people in
their 20s, 30s, 40s, etc. To do so:
·
Click on
one of the ages in Column A. For example, age 18 in cell A4. Choose Analyze and Group Field as demonstrated in the screenshot below.
·
Group by 10
starting at 20 and ending at 100.
Answer question 19 below based on the results. Disregard the data for
<20 as that segment only includes 2 years (18 and 19) whereas the rest
includes 10 years.
Question
19. Does age make a difference in susceptibilities?
Explain.
Critical Thinking Question
20. Summarize your findings in 100 words or less to
share with the General Hospital administration.
Submit your work
Document your answers directly on this activity document as you
complete the activity. When you are finished, save this activity document to
your device and upload this activity document with your answers to your
Learning Management System (LMS). If you have any questions about submitting
your work to your LMS, please contact your instructor.
References
Healthline. Sensitivity Analysis: Purpose, Procedure, and Results. Medically reviewed by Steve Kim, MD on
February 24, 2016 — Written by Christine Case-Lo and Brian Wu. Available at https://www.healthline.com/health/sensitivity-analysis
