You work in the (insert your state here: Florida) Public Health Department as an epidemiologist. Your boss asks you to look further into a disease in your state to determine the pattern of new cases and if it is increasing in incidence and if it is higher incidence than the national level. In order to do this, you will collect data from the Centers for Disease Control and Prevention National Notifiable Disease Surveillance System (NNDSS).
For this assignment you will be making figures in Microsoft Excel based on data you collect from the Morbidity and Mortality Weekly report published weekly by the Centers for Disease Control and Prevention (CDC). You will choose a nationally notifiable disease that is either a sexually transmitted infection (STI) or a foodborne/waterborne illness and track the number of new cases per month and calculate the incidence throughout the last 5 full years (ex., If it is currently 2018, you will use the data from the year 2017 since this is completed for the year). You will also track the number of new cases per month in the last full year to look for temporal trends in the disease.
The purpose of this assignment is for you to practice collecting and analyzing data as well as presenting data in a well formatted figure. The formatting of the graph is equally as important as having the correct data. Please note that there are two parts to the assignment.
Supporting Materials are attached: Example of Excel Spreadsheet and figures (PDFs), Excel Template (Excel), Copy of Assignment Directions (Word).
Part One: Making charts in Excel
Before you begin: Microsoft 365 is available to AMU/APU students if you do not already own it. Please work with IT if you need to download it. If you have never used Excel before, you will need to become familiar with it as you will be expected to be able to complete the assignment as instructed. Here are training guides: Excel for Windows training
1. Open the Excel “Week 3 Assignment template” attached to this assignment. In this template, you will have the data labels already set up and the charts ready for your to input data. When you enter data into the cells, the charts will automatically populate. See the attached PDF examples using Hepatitis A.
2. Go to the CDC Wonder “National Notifiable Diseases: Infectious Weekly Tables” (Weekly Statistics). Please note that web addresses may change. If the web address does not work, perform a search for it using the title given.
3. Select on the “Change year/week” button at the top of the list in the top-right of the screen. Enter the previous year (ie. if it is currently 2020, you will select 2019) and week 1. Then select “Change to selected year/week”
4. Choose one of the following diseases to collect data and Select on it in the table of contents:
- Chlamydia trachomatis infection
- Giardiasis
- Gonorrhea
- Syphilis (primary & secondary)
- Salmonellosis
- Shiga toxin-producing E. coli (STEC)
- Shigellosis
5. A table will appear that contains the number of cases reported during that week in the United States (top row). You will collect data from the “Reporting area” of the United States (top row) and the state of your choice. You will use the number in the “Cum 2019” column only for each week’s data point which is the cumulative number of cases reported during the year up until this week.
6. Fill in the attached Excel template with the data from week 1 under the columns “New cases”. Use the U.S. data to fill in the U.S. column and scroll down to the state of your choice and use this data to fill in the state column.
7. On the CDC website, use the browser back button to go back to the previous page (with all tables listed). Select on the “Change year/week” button at the top of the list. Enter the previous year (ie. if it is currently 2020, you will select 2019) and week 4. Then select “Change to selected year/week”. Go back to the table with the disease and the “Cum 2019” column will now give you all new cases for the first 4 weeks of the year.
8. Fill in the Excel spreadsheet with data from week 4. To do this, you must subtract the previous reported cases from the Cumulative total. Ex. Week 4 Cumulative total – week 1 cumulative total will give you new cases during the first month. Enter this number into the cell for week 4 data. Remember, your charts must be new cases per month so you will need to subtract the previous total cases (not just previous new cases). (You may want to add a column in the spreadsheet or you can do it by hand-this is up to you)
9. Repeat steps 7 and 8 for weeks 8, 12,16,20,24,28,32,36,40,44,48, 52. Record all data in the Excel spreadsheet.
10. As you enter the data in the column in the template, the charts showing the number of cases for the U.S. and for the state will be filled in automatically. Add appropriate titles for your graphs and for x- and y-axes (use Excel Help function to do this properly).
11. Next you will calculate incidence of the disease in the U.S. and in your state for the last 5 years. Use the cumulative total for week 52 of the last 5 years for both the U.S. and the state of your choice. Follow above directions for selecting and changing year and week to collect the last 5 years data.
12. To calculate incidence, you need to know the population of both the U.S. and the state for each year. In the Excel spreadsheet, underneath the cell “U.S. population” you will enter the U.S. population at the midpoint (July) of each year you are using data (ex. July, 2019, July 2018, etc.). This information can be found through the U.S. Census Department (Population and Housing Unit Estimates Tables). Do the equivalent procedure for your state using a credible source.
13. Underneath the “K value” cell, you will enter a number that is a multiple of 10 (ex. 1,000 or 10,000, etc.). This number multiplies the incidence rate in order to have a whole number., ex. 1 case per 1,000 people. Note, the K value must be constant in order to compare rates across years.
14. Using the columns “U.S. incidence” and “state incidence”, enter in the formula for incidence rate (use your lessons and resources to determine the appropriate formula for incidence). The data you input in these columns will automatically be entered into the chart.
15. Insert a chart title and axes titles. You may change the legend by selecting on the cells “U.S. Incidence” and “State Incidence” and changing the text.
Part Two: Assignment submission
- Use a Word document to write a report in proper APA formatting. Write a brief summary on the disease you chose using proper in-text citations and academic references (1-2 paragraphs).
- Answer the following questions given the data you collected:
- Is there a temporal pattern to the disease either nationally or statewide? Explain and refer to the charts you made
- Describe the incidence rate both in the U.S. and in the state over the last 5 years. What trend(s) do you see? Refer to the graph you made.
- How does the state incidence rate compare to the national rate? Refer to the graph you made.
- What are your conclusions on the disease in your state given the data you collected? What would your recommendations be for further inquiry?
- Insert the graphs you made into the Word doc. If using a PC, right select on the graph in the Excel spreadsheet and copy. In the Word doc, right select and paste as picture. (Apple users will do the equivalent procedure).
- Submit both the Word document and the Excel spreadsheet for grading.