Appendix G: Analysis Cleaning Instructions,Codebooks, & Analysis Sheets

The following provides instructions for cleaning and analyzing data, as well as sample analysis sheets that can be used to tabulate or report responses.

 

Coding/analysis instructions

The following provides instruction on how to clean and analyze data. If you have a small amount of data, you may be able to quickly read through the responses to obtain the information you need instead of going through a more formal data cleaning and analysis process. Frequency scores and percentages should be calculated for quantitative data and qualitative data should be coded for themes. If analyzing results across regions (multiple counties, multiple clusters), such as for the In-depth approach, you may want to analyze results for each individual region (e.g., by each cluster). You can use the “Cluster” variable identified in the Excel codebooks to split results if analyzing data in Excel or in a statistical program such as SPSS.

Quantitative data (Closed-ended questions)

1. Download your data from your online survey platform or log data into the Excel template provided in this toolkit.
2. Check that responses are entered as the numerical answers, also known as “reporting values”, as shown in the questionnaires and in the codebooks. If you are using an online survey program, you may be able to enter the numerical values when you are building the survey or after you have collected the data so that it will automatically download in this format. The image below is an example of how to program the values in an online survey program (SurveyGizmo).

Continued on following page Note: If you have a small amount of data or you prefer to see the text responses options (“Yes”, “No”), skip to step 3.  

Quantitative data (Closed-ended questions)

Continued from the previous page.  

1. Once the data has been entered, thoroughly review or “clean” to check for inconsistencies, implausible entries, missing data, and skip pattern errors.
2. Next, the clean data can be analyzed. Data analysis will consist of calculating the counts (or “frequency scores”) and percentages for each response option. There are many ways to calculate frequency scores and percentages.

 

If using Excel:

• To calculate frequency scores: You can use COUNTIF formula to calculate how often a certain value appears in a list of data. The following example shows how to use a COUNTIF formula:
  • In a row below your data, begin writing the formula “=COUNTIF(”
  • Next, it will ask you to highlight the cells that you want it to look at (called “range”)
  • Then specify the value that you want it to count how many times it appears (“criteria”)
  • Click enter

  You will see in the following image, 4 people selected “Difficult” (value of 1) in response to this question. Repeat these steps for all responses options and quantitative questions. To calculate frequency scores: You can use a PivotTable to calculate frequency scores.

• • • To create a PivotTable, highlight the data that you want included in your PivotTable. The following example shows only one question, but you can include data from multiple questions in your PivotTable. Be sure to also highlight the question or variable name.
    • Under the “Insert” tab, click the PivotTable button.

 

• It will automatically be set to create your PivotTable in a new tab, but you can select to place your PivotTable in an existing tab. Click “Ok” to move forward.

In the PivotTable field pane, select the question(s) that you want to include in the table. You can include data from multiple questions in a PivotTable. Drag the question into the “Rows” area and the update the Values to show “Count”. See the following example. To calculate percentages: Divide the number of a specific response option by the total number of responses to that question. Check out the following links for additional resources on how to use Excel to calculate sums and percentages or to create pivot tables.

• Use COUNTIF formula
• Calculate SUM
• Calculate percentages
• Create PivotTable

  If using a statistical program: Note: The following is an example using SPSS. You can also use other programs such as Microsoft Access, STATA, R, and Epi Info. These programs also offer many online resources on how to analyze data.

• Import data into the program and clean variable names, labels, and values as needed.
• Calculate the frequency scores and percentages.
• • • In SPSS, go to “Analyze”-> “Descriptive Statistics” -> “Frequencies” and then select the variables that you want to analyze. You can analyze multiple variables at the same time.

 

• Once you select “Ok” an output file will display the analyzed data.

  You can also use the Analysis Sheets to manually tally the responses. If you are analyzing results across regions (multiple counties, multiple clusters) you may want to create a table of results for each region or add addition columns for each region. Statistical programs, such as this one on grouping data in SPSS from Kent State, have online guidance on how to split results by groups. You can the “Cluster” variable identified in the codebooks to split results if analyzing data in Excel or in a statistical program such as SPSS. Reliability considerations: Reliability measures how likely a respondent is to give a consistent answer to a question when asked multiple times. If you are interested in assessing the reliability of your survey, you can ask identical questions in two separate sections, which is known as “Internal Consistency Reliability”. If the respondent gives the same response each time the question is asked, it shows that these questions produce valid, repeatable results. A statistical test for reliability is Cronbach’s alpha, which ranges in value from 0 to 1. A score of 0.8 or above means that there is good reliability, while anything less than 0.6 is not acceptable.  

Quantitative data (Open-ended questions)

Qualitative data are from the open-ended comments or open-ended interview/focus group responses. There are also examples of qualitative analysis software referenced in the toolkit that can be used to analyze open-ended data. Use the following steps as a guide for analyzing qualitative data.

1. Download the open-ended responses into a Word document or into Excel.
2. Read through all responses to a question to get a sense of what everyone is saying.
3. Read through the responses for a second time and make note of potential themes. Themes are categories or high-level summaries that multiple comments will fit under.
4. Write down the list of themes and calculate how many comments fit under multiple themes. If it is helpful to manually move comments around, copy the question and responses into a separate document so that you can do this without changing the original data.
   1. If a comment fits under multiple themes, you can count it multiple times. In your write up or in the table you use to report your themes, add a footnote or other type of note that states “comments that include multiple themes have been double-coded.”

Example:

Please specify which vaccines your infant needs.

(Theme 1) Coronavirus vaccine – 4 responses “Coronavirus” “Covid” “COVID19” “Coronavirus” (Theme 2) Other vaccines for flu, polio, and Hepatitis A – 3 responses total (1 each) “Flu” “Polio” “Hepatitis A” Reliability considerations: If you have multiple people analyzing qualitative data, have the analysts first try coding a few open-ended questions on their own. Then meet as a group to talk through the themes that they used to code the data to identify the similarities and differences in the themes. Discuss any differences in themes until the group comes to an agreement on how the data should be coded. Have the analysts try coding a few more questions and review again to ensure data is coded similarly. This process will help enhance reliability.

Considerations for questions that have both Quantitative and Qualitative data

• Several questions in the toolkit ask the respondent to specify if they give a certain response. Analyze the quantitative and qualitative as recommended above but keep analyzed data close together so that you can easily see how the qualitative answers support the quantitative responses.
• For the following question, consider analyzing the “weeks” and “months” open-ended response data by the following methods. Select the method that is most appropriate for how you would use the data.
  • Recalculate months into weeks (e.g., 1 month and 1 week = 5 weeks) and then calculate the mean average, minimum, and maximum number of weeks [range].
  • Categorize responses into predetermined categories, such as trimesters.

  Question: How many weeks or months pregnant were you when you had your first visit for prenatal care? Do not count a visit that was only for a pregnancy test or only for WIC. Prenatal care includes visits to a doctor, nurse, or other health care worker before your baby was born to get checkups and advice about pregnancy. (Enter months and weeks OR weeks)

• Months [ | ] and Weeks [ | ]
• Weeks [ | ]
• I have not seen anyone for prenatal care
• Don’t know
• No Response