The spreadsheet stack menu allows multiple columns to be "stacked" on top of one another to create a new spreadsheet.
If N columns are stacked, the new spreadsheet contains N times more rows, and many fewer columns. A new factor is created
within the spreadsheet which indexes which column the new row came from.
Often data from another data source may have multiple measurements of the same item in separate columns. So for example in
an animal trial the live weights of the animals on 4 different dates may have been entered as 4 columns. For most repeated
measurement statistical analyses we require the live weights to be in a single column with a factor (e.g. date) indexing the
different weighing dates. The stack menu allows the 4 columns to be put into a single column, and data that is recorded only
once per animal (e.g. tag, sex etc.) to be repeated for every corresponding row in the new spreadsheet.
Number of Columns to Stack together
Specifies 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
Specifies the name of a column 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.
This lists columns in the currently selected spreadsheet. Double clicking columns in this list adds them to the Columns to Stack
list. Using the Control or Shift key with the mouse allows the selection of multiple columns.
The <Empty Cells> item is a place holder that will effectively 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> place
holder in the second position will ensure that the rows line up.
The button places the selected Available
columns into either the Stack Columns or Repeat Column list, depending on which of these last had focus. Similarly,
the removes the selected Columns from either
the Stack Columns or Repeat Column list.
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. Multiple stacked columns can be created by
entering groups of columns into this list. Also, the same source column can be stacked multiple times, if required.
Provides you with a space to specify which columns are to be repeated. The number of times a column is repeated is specified by
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
This list contains the names of the columns that will be created from
the columns entered into the Stack Column list. Double clicking a name
in this list will present a small edit dialog that will allow you to
specify the column names in the new spreadsheet.
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
If selected and the spreadsheet contains some filtered/restricted data then
the columns will be stacked excluding the rows within the current
filter/restriction. By default columns are stacked ignoring any
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
using the button once.
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
Sets the newly created spreadsheet to be the active spreadsheet.
See setting an active spreadsheet for more details.
Add to Book
This provides a list of books that the new
spreadsheet can be added to. The default book that spreadsheets are added to
can be specified using the
Spreadsheet Options - Books tab menu.
|OK||Create the new stacked spreadsheet and close the dialog.|
|Cancel||Close the dialog.|
|Clear||Clear all lists and edit boxes.|
The following example shows a spreadsheet containing a factor column identifying animals (Tag),
a factor column specifying a treatment applied to each animal (Treat) and two variate
columns containing measurements for live weights recorded at two different dates (Lwt1, Lwt2).
The dialog below shows how the stack dialog can be used to stack the two live weights
columns together and repeat the two factor columns. The number of columns to stack together
is 2 and the source factor has been named Date. The variates Lwt1 and Lwt2 are copied into
the Stacked Column names list and the two factors are placed in the Repeat Columns list.
Clicking OK on the dialog above produces the spreadsheet shown below. Note that the Create unique Column names
option was selected to avoid clashes between the old and new column names. The
Use names from First Columns for Factor labels
option was selected, so that the factor Date was created with the labels Lwt1 and
The name for the new stacked column, Lwt1_1, could have been changed by double-clicking
on the name in the Stacked column name list (see below).