How to Automatically Stack a Data Set

The process for adding a stacked data set to your project using R code is:

1. Select File > Data Sets > Add to Project > From R.
2. Enter the R Code which brings in the data and performs the stacking. Our example below is used to stack our example data file, Technology 2018.sav
3. Modify part (1) of the code to specify the location of your file. This will either need to be a location on your computer or a publicly accessible URL - see the example Modify part (1) - Specifying the location of the unstacked data file.
4. Modify part (2) of the code to refer to the variable name of the variable which contains your respondent IDs in the unstacked file - see the example Modify part (2) - The respondent ID variable.
5. Modify part (3) of the code to specify sets of variables that are to be stacked - see Modify part (3) - Specifying stacked variables. Note, you will more than likely want to use our handy Excel template to generate the code of the list of variables to stack together - see Modify part (3) - Excel template for generating R Code.
6. Enter a Name for the data set.
7. Click the play icon.
8. Click Add Data Set.
9. See Additional Notes below for other things to consider after importing your stacked data set.

Your code window will look a bit like this.

Note that when developing the code for stacking your data, you may find it convenient to first run and test the code in an R Output.

Example Code

This example is from a study on technology brands. The example uses Technology 2018.sav. The purpose of this stacking is to stack the data for all of the brands so that there is a single NPS score, and a single set of image variables for a driver analysis.

# (1) SPECIFY the file location - if using local file change \ to \\
location = "https://wiki.q-researchsoftware.com/images/3/35/Technology_2018.sav"

# (2) NOMINATE your ID (Case) variable
id.variable = "RESPNUM"

variables.to.stack = list(
# (3) REPLACE the below with a copy/paste from BLUE (first) column of Excel
    ## !!!! be sure to remove the last comma before the closing round bracket !!!! 
'Q3_01' = c('Q3_01', 'Q3_02', 'Q3_03', 'Q3_04', 'Q3_05', 'Q3_06', 'Q3_07', 'Q3_08', 'Q3_09', 'Q3_10', 'Q3_11', 'Q3_12', 'Q3_13'),
'Q4a_01' = c('Q4a_01', 'Q4a_02', 'Q4a_03', 'Q4a_04', 'Q4a_05', 'Q4a_06', 'Q4a_07', 'Q4a_08', 'Q4a_09', 'Q4a_10', 'Q4a_11', 'Q4a_12', 'Q4a_13'),
'Q4b_01' = c('Q4b_01', 'Q4b_02', 'Q4b_03', 'Q4b_04', 'Q4b_05', 'Q4b_06', 'Q4b_07', 'Q4b_08', 'Q4b_09', 'Q4b_10', 'Q4b_11', 'Q4b_12', 'Q4b_13'),
'Q4c_01' = c('Q4c_01', 'Q4c_02', 'Q4c_03', 'Q4c_04', 'Q4c_05', 'Q4c_06', 'Q4c_07', 'Q4c_08', 'Q4c_09', 'Q4c_10', 'Q4c_11', 'Q4c_12', 'Q4c_13'),
'Q4d_01' = c('Q4d_01', 'Q4d_02', 'Q4d_03', 'Q4d_04', 'Q4d_05', 'Q4d_06', 'Q4d_07', 'Q4d_08', 'Q4d_09', 'Q4d_10', 'Q4d_11', 'Q4d_12', 'Q4d_13'),
'Q4e_01' = c('Q4e_01', 'Q4e_02', 'Q4e_03', 'Q4e_04', 'Q4e_05', 'Q4e_06', 'Q4e_07', 'Q4e_08', 'Q4e_09', 'Q4e_10', 'Q4e_11', 'Q4e_12', 'Q4e_13'),
'Q4f_01' = c('Q4f_01', 'Q4f_02', 'Q4f_03', 'Q4f_04', 'Q4f_05', 'Q4f_06', 'Q4f_07', 'Q4f_08', 'Q4f_09', 'Q4f_10', 'Q4f_11', 'Q4f_12', 'Q4f_13'),
'Q4g_01' = c('Q4g_01', 'Q4g_02', 'Q4g_03', 'Q4g_04', 'Q4g_05', 'Q4g_06', 'Q4g_07', 'Q4g_08', 'Q4g_09', 'Q4g_10', 'Q4g_11', 'Q4g_12', 'Q4g_13'),
'Q4h_01' = c('Q4h_01', 'Q4h_02', 'Q4h_03', 'Q4h_04', 'Q4h_05', 'Q4h_06', 'Q4h_07', 'Q4h_08', 'Q4h_09', 'Q4h_10', 'Q4h_11', 'Q4h_12', 'Q4h_13'),
'Q4i_01' = c('Q4i_01', 'Q4i_02', 'Q4i_03', 'Q4i_04', 'Q4i_05', 'Q4i_06', 'Q4i_07', 'Q4i_08', 'Q4i_09', 'Q4i_10', 'Q4i_11', 'Q4i_12', 'Q4i_13')
)

# The following does the stacking for you
library(foreign)
datafile = suppressWarnings(read.spss(location, use.value.labels = FALSE, to.data.frame = TRUE))
library(reshape)

all.names = names(datafile)

variables.to.exclude = all.names[!all.names %in% c(unlist(variables.to.stack), id.variable)]
stacked.tech = reshape(data = datafile, 
    idvar = id.variable, direction = "long",
    drop = variables.to.exclude,
    varying = variables.to.stack)

Modify part (1) - Specifying the location of the unstacked data file

In the example above, the function read.spss from the R package foreign is used to read in the unstacked data file prior to the stacking procedure. The location in the example is a URL - a location on the web. You can also set the location to be a path on your computer. For example, the following location will obtain a file from my Desktop:

location = "C:\\Users\\Chris\\Desktop\\Cola Tracking - January to September 2017.sav"

library(foreign)
datafile = suppressWarnings(read.spss(location, use.value.labels = FALSE, to.data.frame = TRUE))

Note the use of double backslashes "\\" to separate the folders on my system.

Other functions should be used in place of read.spss() if you are using a different format. For example, read.csv is the equivalent if using a CSV file.

Modify part (2) - The respondent ID variable

You need to specify an ID variable so that the stacked cases can be identified with the respondents in the original, unstacked file. This enables matching between the stacked and unstacked data, but it is also just good housekeeping.

Modify part (3) - Specifying stacked variables

The main part of the code is the lines following variables.to.stack, which specify the groups of variables which are to be stacked.

For example,

'Q3_01' = c('Q3_01', 'Q3_02', 'Q3_03', 'Q3_04', 'Q3_05', 'Q3_06', 'Q3_07', 'Q3_08', 'Q3_09', 'Q3_10', 'Q3_11', 'Q3_12', 'Q3_13')

indicates that the variables with names Q3_01, Q3_02, to Q3_13 should be stacked in order to for a variable called Q3_01. In this example, these variables correspond to the Likelihood to recommend scores for the brands in our study.

Modify part (3) - Excel template for generating R Code

You can use the following Excel file to assist in generating your R Code: https://wiki.q-researchsoftware.com/images/a/a5/Stacking_code_generator.xlsx

To use this template:

• Copy the list of variable names from the Variables and Questions tab. You need not copy every single variable, just the ones you wish to eventually stack. In the example data set, the variables Q1, Q2 and Rec_Age were omitted because these are not meant to be stacked. You can copy by selecting the cells in the Name column of the Variables and Questions tab, and pressing CTRL-C.
• Paste them into Column C in the Stacking Code Generator tab.
• In Columns D and on-going, rearrange your variable names as you would if you were using Tools > Stack SPSS file. In the worked example, this resulted in 13 observation columns, but you may not have that many columns (or you may have more).
• Tip: use the “Transpose” paste feature in Excel (if you need to) and delete any unnecessary rows in the generator. Some stacking does not require transpose pasting.
• Column A (the blue column) automatically generates some code for each resultant stacked variable. Copy all the cells in the blue column and paste it into the appropriate spot in the R Code template.

Important notes:

• When pasting the code, be sure to remove the last comma before closing the round brackets.
• Be careful using Excel to copy, paste, delete and transpose data. All of the above requires some common-sense using Excel.

Additional Notes

Tidy the metadata

R data sets do not have the ability to include metadata, like variable labels and value labels. You will need to input this manually in the Variables and Questions tab for the stacked data set after adding it to Q. You only need to do this once - updating the data set later on will preserve the metadata you have specified. See How to Set Up Files With No Metadata for the typical workflow.

Data matching

This can be very handy if you ever want to cross-tabulate data between the unstacked datafile and the stacked data set. See How to Set Up and Manage Data File Relationships for more on creating relationships between data sets. You will create a One-to-Many relationship from the unstacked data set (one) to the stacked data set (many).

In the example data set, RESPNUM is used as the linking variable (ie: the Case ID variable). If you wanted to cross-tabulate between the stacked version of question 4 and the age of respondents (variable Rec_Age), having a One-to-Many datafile relationship will permit this (without needing to stack Rec_Age).

Updating

When the time comes to update your Q project with a newer version of the stacked data, you need to:

1. Determine the file path or URL for your new unstacked file.
2. Open your Q Project.
3. Select File > Data Sets > Update and chose your stacked data set.
4. (Optional) If the file path or URL has changed, modify your code to refer to the new file. Similarly, if there are new or different variables to stack, modify the code accordingly.
5. Push the play button again. The new data set will be calculated, and when it is finished the new data will appear in the preview.
6. Select Update Data Set. The old stacked data will now be replaced with the new version of the stacked data.