Stacking and Unstacking Columns

Stacking lets you place several columns on top of each other to form a single column of values. Stacking can be useful when you have gathered repeated measurements from an experiment: you can put all the measurements into a single variate by stacking them, create a corresponding factor column to categorize and identify each group of measurements, then perform a combined analysis on the whole set.

Unstacking lets you separate grouped data into multiple columns based on the levels of an unstacking factor.

Stacking columns

The spreadsheet below show the results of a study into the effects of sex and diet on the growth of pigs over time. In the following example we’ll stack the Weight columns together into a new column called Weights and create a factor column Month so that we can identify each set of observations.

From the menu select Spread | Manipulate | Stack.
Set the Number of columns to stack together.This tells Genstat how many columns you want to stack together at a time. In our example we want to stack all 9 weight columns into one column, so we’ll enter ‘9’.
Type a factor name in Record column source in factor. This creates a new column containing a factor where each level will represent a column that has been stacked. In our example the weight measurements were taken over a number of months, so we’ll type ‘Month‘ as our factor.
Move items from Available data into Stack columns by double-clicking them. You can select multiple items by holding down Ctrl while clicking with the mouse, then click to move them all across in one action.In our example, Genstat prefixes the names in Stack columns with a ‘1’. The ‘1’ indicates the columns we want to stack together in our stacked column.

Using a different example for a moment, if you want to create two stacked columns, type ‘2’ in Number of columns to stack together.

When adding columns to the Stack columns list, the first two columns will be prefixed by the number ‘1’ to indicate that they will be placed into the first stacked column. The second two columns will be prefixed by the number ‘2’, to indicate that they will be placed into the second stacked column.

If you want to include repeated columns, move items from Available data into Repeat columns by double-clicking them. You can select multiple items by holding down Ctrl while clicking with the mouse, then click to move them all across in one action.
To change the default name that Genstat will assign to your stacked column, double-click the name in Stacked column names, type a new name then click OK.
Set options as required (see Stacking options below) then click OK.

The spreadsheet opens to display your data. The first column contains your source factor while the final column contains your stacked data.

Stacking options

Number of columns to stack together	Enter the number of columns that are to be stacked together to form a single column in the new spreadsheet. For example, a value of 3 will stack the first 3 columns into one column, the next 3 into the next column, and so on. When the columns are copied into the Stack columns list, the columns are prefixed with the new column number they will be stacked within. For example, if there are 7 columns and the number of columns to stack together is 3, the prefixes will be 1, 1, 1, 2, 2, 2, 3. Note that in this example the last two thirds of the third new column will be missing values as only one column has been specified to stack.
Record column source in factor	Enter a column name to store the source of each column in a factor. Each level of the factor represents the different columns stacked together. For example, if 2 columns are stacked together then level 1 will represent the first column and level 2 will represent the second column.
Available data	This lists data structures appropriate to the current input field. The contents of this field will change as you move from one field to the next. The <Empty cells> item is a placeholder that will insert the equivalent of a column of blank cells into the stacked column. So for example, if you had four columns of one measurement (X1,X2,X3,X4) and three columns of another corresponding to columns X1, X3 and X4, then inserting an <Empty cells> placeholder in the second position will ensure that the rows line up.
Stack columns	This specifies which columns are to be stacked together. The columns are stacked in the order that they appear in the list. The first n (= number of columns to stack together) will go into the first stacked column, the next n into the second column and so on. Each name is preceded by a number which indicates what columns are to be stacked together. You can create multiple stacked columns by entering groups of columns into this list. The same source column can be stacked multiple times if required.
Repeat columns	Specify which columns are to be repeated. The number of times a column is repeated is set in the number in the Number of columns to stack together field. This is equivalent to putting the same column n times into the Stack columns list.
Stacked column names	Genstat will create default names for the stacked columns. You can enter your own name by double-clicking the names shown in this list.
Create unique column names	If selected the columns created in the stacked spreadsheet will be given new unique names. By default the columns created are given the same name as the first column in each stack.
Use names from first stacked column for factor labels	If selected, the names from the first stacked columns will be used as labels for the source factor. The names from the first stacked column will be the names in the Stack columns list which are preceded by 1:.
Remove excluded rows	This option only appears if the spreadsheet contains filtered/restricted data. If selected the columns will be stacked excluding the rows within the current filter/restriction.
Stack column order interleaved	When selected, the columns in the Stack columns list will be put into stacked columns using an interleaved order. This means that the first column will be placed at the start of stacked column 1, the second column at the start of stacked column 2, and so on; until the end of the number of stacked columns are reached, and then the subsequent columns will be put as the second sections of the stacked columns 1…n, and so on. For example, if there are 12 columns being stacked into 3 columns (4 columns stacked into each) then using an interleaved order for the list C1,C2,C3,C4…C12 will create 3 stacked columns containing (C1,C4,C7,C10), (C2,C5,C8,C11) and (C3,C6,C9,C12). If this option was not set, then the stacked columns in this example would be (C1,C2,C3,C4), (C5,C6,C7,C8) and (C9,C10,C11,C12). This option is useful if the columns in the spreadsheet are ordered by sampling time, e.g. Grass1,Clover1,Weeds1, Grass2,Clover2,Weeds2, Grass3,Clover3,Weeds3, as then the column names can be added to the list in a single operation by selecting the group of columns and clicking to move them all across in one action. Note: When using this option the default stacked column names may not be appropriate and can be changed by double-clicking the current column names in the Stacked column names list.
Set as active sheet	This sets the new spreadsheet that is created as the active spreadsheet. Setting an active spreadsheet provides a method of avoiding multiple data updates to the server when more than one spreadsheet is open within Genstat. When a spreadsheet is set as the active spreadsheet only data from that spreadsheet is automatically updated to the Genstat server. Another advantage of specifying an active spreadsheet is that the Spread menu options will always be enabled even if your spreadsheet does not have the cursor focus.
Add to book	Any open spreadsheets will be displayed in this dropdown list. Select a spreadsheet to add your data to or leave it at the default setting New book to create a new workbook.

Unstacking columns

The spreadsheet below show the results of a study into the effects of sex and diet on the growth of pigs over time. The Weight column contains nearly 300 observations taken during 9 time periods. In the following example we’ll unstack the Weight column using the factor levels in the Time column to identify each group. The end result will be 9 individual Weight columns.

From the menu select Spread | Manipulate | unstack.
In the Available data field, double-click a column to move it to the Unstacking factor field.
If you want to specify one or more ID Factors, move items from Available data into ID factors by double-clicking them. You can select multiple items by holding down Ctrl while clicking with the mouse, then click to move them all across in one action.
Repeat the instruction above to select and move columns to the Unstack columns field.
Set options as required (see Unstacking options below) then click OK.

The spreadsheet opens to display your unstacked columns.

Unstacking options

Available data	This lists data structures appropriate to the current input field. The contents of this field will change as you move from one field to the next.
Unstacking factor	Enter a factor to use to unstack the columns. The columns will be unstacked using each level of the factor. For example, if the factor has 2 levels then all values corresponding to level 1 will be unstacked in one column and all values corresponding to level 2 will be unstacked into a second column.
ID factors	Enter one or more ID factors to be used to unstack the columns. The combination of ID factors should uniquely identify a row within an unstacking group. In the unstacked spreadsheet, each row is made up of items with the same combination of ID factors as the original spreadsheet. The values of the unstacking factor and ID factors decide the column and row respectively from the items in original spreadsheet to go into in the unstacked spreadsheet. Example – unstacking the spreadsheet below using UF as the unstacking factor, and ID as the ID factor: This will give an unstacked spreadsheet of: The UF factor specifies the column the value goes into and the ID factor specifies the row the item goes into. A warning is given if there are rows with duplicate IDs.
Unstack columns	Specify the column names to unstack.
Sort rows on IDs	If selected and an ID factor has been specified, the unstacked columns will be sorted by the levels of the ID factor.
Create suffixed column names	If selected, the names given to the new unstacked columns will be created as a pointer with a numerical suffix e.g. Column X will be unstacked to X[1], X[2], and so on.
Use factor labels in column names	If selected the labels (or levels if there are no labels for the factor) from the unstacking factor will be used to create the unique column names. For example, if the factor labels are ‘Low‘, ‘Medium‘ and ‘High‘, the names for the new columns from an unstacked column will be formed by attaching _Low, _Medium and _High to the original name, rather than the default _1, _2 and _3.
Remove excluded rows	This option only appears if the spreadsheet contains filtered/restricted data. If selected the columns will be stacked excluding the rows within the current filter/restriction.
Add to book	Any open spreadsheets will be displayed in this dropdown list. Select a spreadsheet to add your data to or leave it at the default setting New book to create a new workbook.
Set as active sheet	This sets the new spreadsheet that is created as the active spreadsheet. Setting an active spreadsheet provides a method of avoiding multiple data updates to the server when more than one spreadsheet is open within Genstat. When a spreadsheet is set as the active spreadsheet only data from that spreadsheet is automatically updated to the Genstat server. Another advantage of specifying an active spreadsheet is that the Spread menu options will always be enabled even if your spreadsheet does not have the cursor focus.

Updated on May 16, 2019

Was this article helpful?

Yes No