Replace missing values with zeros

Updated at July 22nd, 2021

Missing values can represent a quantity that is actually known. This can occur with skip logic, where [NA] values might mean zero or a quantity from a prior answer.

For example, a survey asked  "How many many energy packs did you purchased last week?" This question is asked only of respondents who indicated they made at least some purchase.   

Here the mean number of purchases was 2.57, and 10% of respondents have [NA] indicating the question was not answered. However, in this case, we know that [NA] values aren't really missing and exactly how many packs they purchased:  zero.

Recode missing values

One approach is to Recode missing values. Press the edit icon for the element and select "Recode..." from the  context menu:

This will bring up the recode dialog:

Drag the value [NA] to the "(New code)" button and enter the value 0 at the prompt. 

This will recode missing values to zero:


Voilá, the distribution now shows 10% of respondents with zero purchases, and the mean is now 2.31, a bit lower:


Note: When elements are skipped due to skip-pattern, it would be appropriate to add an element specific filter to the element or group.

Advanced alternative:  Zero-fill via data processing

If the group to be recoded is complex, you may want to try this method. Create JavaScript codes as a data process instead.  

For example, a group could be summing to 100, but respondents are not required to enter a 0 in every box:

Zero-fill data process code

the code below zero-fills when some of the children are populated, and [NA] fill for elements that are partially populated with 0, but no non-zero values are included.

var rows = data.main
// List the group and its children in an array called zero_fill
var zero_fill = [
    {"key": "Q2", "children": ["Q2_1","Q2_2","Q2_3","Q2_4","Q2_5","Q2_6"]},
    {"key": "Q3", "children": ["Q3_1","Q3_2","Q3_3","Q3_4","Q3_5","Q3_6"]}
]
// Run through zero fill code for each respondent
rows.forEach(function(row){

    zero_fill.forEach(function(entry){
          key=entry.key;  
          children = entry.children;
          children.forEach(function(child){
            row[key] = (row[key] || 0) + (+row[child] || 0);  
            })    
          if (row[key] == 0) {
              row[key]="";
              children.forEach(function(child){
                row[child] = "";    // NA fill those respondents that has 0 total
            })
          
          }
          else  // if (row[key] == 100) // Could add logic Zero fill respondents only that seen part of the question and has total = 100
          {  
            children.forEach(function(child){
                row[child] = (row[child] || 0);   
            })
          }
          // else console.log(row.respid, key)
            
        })
        
})


Was this article helpful?