4.4.9.2 Unstacking Worksheet Columns

This function is used to unstack grouped data into multiple columns. During unstacking, the missing values can be placed in one group. Optionally, you can include columns that are not part of the group in the output.

To use this function:

Create a new worksheet with input data.
Select Restructure: Unstack Columns from the Origin menu to open the wunstackcol dialog box.

In fact, the wunstackcol X-Function is called to start this function.

1 Dialog Controls
2 How to Unstack Columns
3 Examples

Dialog Controls

Recalculate	Specify how to recalculate the results. See details in Recalculating Analysis Results.
Data to be Unstacked	Specify the data to be unstacked.
Group Column(s)	Multiple grouping columns contains grouping information can be inserted into the Group Column(s) box. Unstacking columns is in accordance with this group. You can add, remove, order grouping columns via controlling buttons: Move Up button , Move Down button , Remove button , Select All button , Select button in toolbar . The grouping columns are set as categorical if most of the column values are text. So you can easily reorder the output columns.
Options	Include Other Column(s) Specify whether to include those non-unstack columns in the original sheet to the result sheet. Other Column(s) Specify the data range(s) which will be the non-unstack column(s) to output to the result sheet. It is available when Include Other Column(s) is checked. Extract Rule for Other Columns Values Specify how to arrange the data in Other Column(s). It is available when Include Other Column(s) is checked. Matching Longest Group Use this option when there are an equal number of individuals for each value of the grouping variable(s) (Group Column(s)) and an equal number of individuals for each value of non-grouped variables (Other Column(s)). For example, suppose that there are 6 individuals to be unstacked to two columns, G1 and G2. For our non-grouping variable there are 3 values -- g1, g2 and g3. To use this option, G1 and G2 should have three individuals each and the non-grouped variable should have 2 individuals in g1, two in g2 and two in g3. Note that before choosing this option, you should be perform a nested sort (primarily sorted by Group Column(s) and secondarily sorted by Other Column(s)). Combining All Groups This option combines the data in Group Column(s) and the data in Other Column(s) and uses it as the index for the unstacked data. So it requires the combination of the data in Group Column(s) and the data in Other Column(s) to be unique. Include Missing as One Group Specify whether to consider missing values as one group.
Output Settings	Sort Output Columns By Specify how to sort the output columns. Group Variables Sort the output columns alphanumerically by group variable value. For example, group variable "Make" has two values, "Acura" and "Buick". After unstacking, the columns appear in the order "Acura", then "Buick". Data to be Unstacked Preserve the order of the Data to be Unstacked. For example, Data to be Unstacked = [Book1]Sheet1!(3,4). When unstacked, column 3 data columns are listed first; column 4 data columns follow. If Data to be Unstacked = [Book1]Sheet1!(4,3), then column 4 data columns are listed first; column 3 data columns follow. Output Worksheet Specify where to store the output unstacked results. Put Grouping Info. to Specify where to put the grouping information. Long Name Output the grouping information to the Long Name row. Comments Output the grouping information to the Comments row. Append to Data Column's Long Name Append the grouping information to the Long Name row. User Defined Parameters Output the grouping information to the user defined parameter row. This row's name is specified by From Group Columns. Custom Fill label abbreviation for every grouping column. The number of labels should be equal to number of group columns. Information of each group column will be put in the one label row. For example, if we have two group columns in input, fill LC in the option. Info of group1 will be set as Long Name and and info. of group2 will be set as Comment From Group Columns This drop-down list is only available when Put Group Info. to is set as User Defined Parameters. It is used to name the user defined parameter row for storing grouping information. The name is from the label row of the Group column in the source worksheet. The selected label row must contain string for naming the user defined parameter row, or there will be an error message. In GUI, the possible options include (depends on which label row is shown in the source worksheet): Short Name From the Short Name of the group columns. Long Name From the Long Name row of the group columns. Units From the Units row of the group columns. Comments From the Comments row of the group columns.

How to Unstack Columns

This function is the inverse operation of stack columns. During unstacking columns, it allows including other non-unstacked columns into the unstacked result.

Unstack
Unstack grouped data into multiple columns.
Unstack and Include Other Columns
Unstack grouped data into multiple columns including ungrouped data columns.
- Using the Matching Longest Group option
- Using the Combining All Groups option

Examples

Example 1: Basic Unstacking

This example will unstack the grouped data (<Origin Program Directory>\Samples\Statistics\body.dat) into multiple columns.

Import the file <Origin Program Directory>\Samples\Statistics\body.dat into a new workbook.
Highlight column D, E, then select the menu Worksheet: Unstack Columns (or type wunstackcol -d; in Command Window) to bring up the wunstackcol dialog.
Click the triangular button next to the Group Column(s) and choose C(Y): gender.
In the Options tree, check the box next to the Include Other Column(s). Use the default range in the Other Column(s).
Select Combining All Groups from the dropdown list of Extract Rule for Other Columns Values.
In the Output Settings tree, select User Defined Parameters for Put Grouping Info. to, and then set From Group Columns as Long Name. Keep other settings as the default values.
Click the OK button to unstack columns. Results are shown below.

Example 2: Unstacking by Date or Time

This example groups data by keying on a single component of a complex date-time string.

Start with a fresh workbook, choose Data: Import from File: Single ASCII and import <Origin Program Directory>\Samples\Import and Export\Custom Date and Time.dat.
Click on the A(X) column heading to select the column, then right-click and choose Properties.
On the Properties tab, set Format to Date, set Display to Custom Display and in the Custom Display combo box, enter the following and click OK.
```
dd.MM.yyyy HH:mm:ss.##
```
What this does is to convert what Origin "sees" as a text string, to a date-time string that is treated internally as a numeric value that can be used for math operations, etc.
Scroll the data sheet and note that the time portion of the string (HH:mm:ss.##) shows readings were taken at close intervals beginning at 09 hours. Suppose that we wanted to break B(Y) column data readings out into groups by hour and minute? The way to do this is to re-select column A(X), right-click and choose Properties and on the Properties tab, set Format to Time and select HH:mm for Display drop-down list.
Your worksheet should now look like this.

Highlight both columns in the worksheet and select Worksheet: Unstack Columns: Open Dialog.
Set Group Column(s) to A(X). Accept other defaults and click OK. Data are grouped by HH:mm and grouping info is saved in the Comments label row of a new worksheet.

Example 3: Reorder Unstacked Columns

This example will unstack Power column of <Origin Program Directory>\Samples\Statistics\automobile.dat by Make, and reorder the output.

Import the file <Origin Program Directory>\Samples\Statistics\automobile.dat into a new workbook.
Highlight column C, then select the menu Worksheet: Unstack Columns to bring up the wunstackcol dialog.
Click the triangular button next to the Group Column(s) and choose B(Y): Make.
Change Recalculate to Auto and accept the default settings.
Click the OK button to unstack columns. Note that col(B) is set as categorical.
Highlight all data in UnstackCols1 sheet and select from menu Plot> Multi-Panel/Axis: Multiple Panels by Label....
Set Group by as Comments. Click OK to plot a multi-panel graph. Note that the panel order follows the column order in the UnstackCol1 sheet.
If you want to adjust the panel order, e.g. the alphabetical order of the Makers, the best way is to adjust the categorical order in the source worksheet. Go back to the automobile sheet. Double click the header cell in the Categories row of col(B).
In the Categories dialog, check Customize Categories(Add, Delete, Set Aribitrary Order). Click Category header in the table to sort it alphabetically. Click OK.
Both column order in UnstackCols1 and panels in the graph get updated.