Stack survey data loops

Updated at September 12th, 2023

Programmatically stack survey data loops (e.g. patient cases) by adding the code below as a project data process.

Data process code

The example code below selectively stacks data columns that are looped for patient chart. The objective is to modify the data from a file where each row represents a respondent into a file where each row represents a patient chart. 

Identify the number of patient loops

Declare a variable called suffixes and set it as an array with the numbers of each patient chart loop. Then, declare a variable called removed_suffixes and with all the columns that we will delete. The idea here is that all the patient data will be stacked into the first iteration of the patient chart loop. For instance, the flattened patient chart loop has questions q300_1, q300_2, q300_3, q300_4, and q300_5, but once we stack the data all the q300 value will be under one column q300_1 and columns with all other suffixes can be deleted. 

Identify which columns you want to stack

Modify the stacked_elements to specify the looped survey data columns (minus the suffix that represents the loop number). 

Weights and new rows

You will also need to adjust the conditional statement to work for your survey in order to create the weights and create a new row for each patient chart. In this case we use a column called q3a which asked HCPs for their patient's initials at the start of the patient chart section. If q3a_5 exists, that means the HCP entered 5 patients charts and their weight should be 1/5, if q3a_5 doesn't exist, the code will look to q3a_4 and so forth. 

Similarly within the suffixes loop, we will only create a patient chart row for each iteration of the loop an HCP answered.

Delete obsolete columns

After all the patient rows of created, the code runs through the stacked_elements and deletes every column with suffixes that are included in our removed_suffixes list. Since all the data is stacked into the first iteration of the patient chart loop, the columns that originally represented the 2nd, 3rd, etc. loop are no longer necessary. 

Code

var rows = data["main"]   //Define rows
  
var rows = []; //Define rows as an empty array
var suffixes = ["1","2","3","4","5"]  
var removed_suffixes = ["2","3","4","5"]  

// Stack patient case data
// This first code block specifies questions looped for patient chart
var stacked_elements = [
"q3a",
"q300",
"q300a",
"q301",
"q301html_old",
"q301a",
"q301b",
"q302",
"q303html",
"Q304_1",
"Q304_2",
"Q304_3",
"Q304_4",
"Q304_5",
"Q304_6",
"Q304_7",
"Q304_10",
"Q304_11",
"Q304_12",
"Q304_8",
"Q304_9",
"q304_8_other"
];
 
rows.forEach(function(row) {
    if (row.hidstatus == 'COMPLETE') {
        
        //Weight is 1/(# of charts)
        if (row.q3a_5) weight =      +1/(5) 
        else if (row.q3a_4) weight = +1/(4) 
        else if (row.q3a_3) weight = +1/(3) 
        else if (row.q3a_2) weight = +1/(2) 
        else if (row.q3a_1) weight = +1/(1) 
        else weight = 0;
    
        suffixes.forEach(function(c){
        
        if(row["q3a_"+c]) {
            var new_row = _.clone(row);
            new_row.patient = c;
            new_row.md_weight = weight;
            new_row.pt_weight = 1;
        
            stacked_elements.forEach(function(e){
                new_row[e+"_1"]=row[e+"_"+c];
                removed_suffixes.forEach(function(x){
                    delete new_row[e+"_"+x];
                })
            })
            rows.push(new_row);
      }
      })
    };
   
})
 
return rows;                                                
 


Delete
Reminder: For data processes, "Save" and "Run" the process after you are done editing the code view. To use the result of the process as the primary data for the project, you will need to set it as "Primary".
Data processes are specific to each project, and your code may not look identical to our example.

Was this article helpful?