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:

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

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

Dialog Controls

Data to be Unstacked	Specify the data to be unstacked.
Group Column(s)	Specify column(s) containing grouping information by which Data to be Unstacked columns are unstacked. Multiple columns can be added into the Group Column(s) box by the Select button . Use the toolbar to reorder or remove column(s). Note:Group columns are set as categorical if most of the column values are text.
Options	Include Other Column(s) Specify whether to include those non-unstack columns in the source worksheet to the result sheet. Other Column(s) Specify the non-unstack column(s) to output to be output to the result sheet when Include Other Column(s) is checked. Extract Rule for Other Columns Values Specify how to arrange the data in Other Column(s) 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 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 perform a (primarily sorted by Group Column(s) and secondarily sorted by Other Column(s)). Combining All Groups Combines data in Group Column(s) and data in Other Column(s) and uses it as the index for the unstacked data. When the combination of the data in Group Column(s) and in Other Column(s) have duplicates, the order follows the appreance in the source worksheet. Include Missing as One Group Specify whether to consider missing values as one group.
Output	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. Output to Data Table Check this checkbox to output unstacked results to a . Available only when one of the following conditions is met: number of Group Column(s) <= 2, Include Other Column(s) has 1 column selected, ⟾ output to a ; number of Data to be Unstacked = 1 , number of Group Column(s) = 1, Include Other Column(s) is unchecked, ⟾ output to a ; number of Data to be Unstacked = 1, number of Group Column(s) = 3, Include Other Column(s) has 1 column selected, ⟾ output to a . Put Grouping Info. to Specify where to put the grouping information. Long Name Comments Append to Data Column's Long Name User Defined Parameters Output the grouping information to the user defined parameter row. Specify the user defined parameter name in From Group Columns. Custom Fill for every grouping column. The number of labels should be equal to that of Group Columns. Information of each group column will be put into one label row. For example, suppose there are two group columns, entering LC in Custom will put Info of group1 to Long Name and info. of group2 to Comment From Group Columns/Dataset Identifier Select a label row (Short Name, Long Name, Units, or Comments) to: Name the user defined parameter row for grouping information when Put Group Info. to = User Defined Parameters; Name replicates when Output to Data Table is checked

How to Unstack Columns

This function is the inverse operation of . 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.

1. Import the file <Origin Program Directory>\Samples\Statistics\body.dat into a new workbook.

2. Highlight column D, E, then select the menu Worksheet: Unstack Columns (or type wunstackcol -d; in Command Window) to bring up the wunstackcol dialog.
3. Click the triangular button next to the Group Column(s) and choose C(Y): gender.
4. In the Options tree, check the box next to the Include Other Column(s). Use the default range in the Other Column(s).
5. Select Combining All Groups from the dropdown list of Extract Rule for Other Columns Values.
6. 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.

7. 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.

1. 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.
2. Click on the A(X) column heading to select the column, then right-click and choose Properties.
3. 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.

4. 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.




5. Highlight both columns in the worksheet and select Worksheet: Unstack Columns: Open Dialog.
6. 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.

1. Import the file <Origin Program Directory>\Samples\Statistics\automobile.dat into a new workbook.
2. Highlight column C, then select the menu Worksheet: Unstack Columns to bring up the wunstackcol dialog.
3. Click the triangular button next to the Group Column(s) and choose B(Y): Make.
4. Change Recalculate to Auto and accept the default settings.

5. Click the OK button to unstack columns. Note that col(B) is set as categorical.
6. Highlight all data in UnstackCols1 sheet and select from menu Plot> Multi-Panel/Axis: Multiple Panels by Label....
7. 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.
8. 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).
9. 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.