Wpivot

1 Menu Information
2 Brief Information
3 Additional Information
4 Command Line Usage
5 Variables
6 Description
7 Examples
- 7.1 Example 1
- 7.2 Example 2

Menu Information

Pivot Table

Brief Information

Create a pivot table to visualize data summarization

Additional Information

Minimum Origin Version Required: 8.1 SR0

Command Line Usage

wpivot row:=col(4) col:=col(6) data:=col(11) method:=sum total:=1 sort_total:=descend sum:=1;

Variables

Display Name	Variable Name	I/O and Type	Default Value	Description
Pivot Table Row Source	row	Input Range	<active>	Specify the range that will be used as the row source of the Pivot Table. Every distinct value of the selected range will correspond to a row in the pivot table. For example, if the table row source contains 5 cells and the values are "East", "South", "West", "South" and "East", there will be 3 rows in the pivot table.
Pivot Table Column Source	col	Input Range		Specify the range that will be used as the column source of the Pivot Table. Every distinct value of the selected range will correspond to a column in the pivot table.
Pivot Table Data Source	data	Input Range		This is available only when Count is not selected for Summarize by (the Method variable). It provides the data values to be summarized.
Summarize by	method	Input int	0	Specify the way to summarize the data. Option list: count:Count Output the count of the original data. sum:Sum Output the sum of the original data. mean:Mean Output the mean of the original data. min:Min Output the minimum value of the original data. max:Max Output the maximum value of the original data.
Combine Direction	dir	Input int	0	With this drop-down list, you can choose whether to combine the count/sum/mean/min/max for smaller values into an "others" column/row. Option list: none:None Do not combine any values col:Column Combine the columns for the smaller values. row:Row Combine the rows for the smaller values.
Mode	vmode	Input int	0	Specify the mode for qualifying smaller values. Option list: perTotal:By Percent of Grand Total Merge the columns/rows whose total value is less than a percent (specified below) of the grand total. perRefRow:By Percent of Reference Row/Col Merge the columns/rows whose value is less than a percent (specified below) of the total of the reference row/column (specified below). limitValTotal:Top n of Grand Total Merge columns/rows expect those whose grand total values are among the top N in the Grand Total row/column. limitValRefRow:Top n of Reference Row/Col Merge columns/rows expect those whose values in the reference row/column are among the top N.
Reference Row/Col	threshold	Input double		Specify the reference row/column when either By Percent of Reference Row/Column or Top N of Reference Row/Column is selected for Mode.
Threshold	label	Input string	Others	Specify the percent for qualifying the smaller values when either By Percent of Grand Total or By Percent of Reference Row/Column is selected for Mode
Value Label	total	Input int	0	Specify the label for the column/row which is formed by merging the smaller value columns/rows.
Totals for Rows	sort_total	Input int	1	Specify whether to add a Row Total column. Each cell in this column contains the sum of the data in the same row.
Sort Output Rows	sum	Input int	0	Specify the method used to sort the rows in the pivot table by the Row Total column. Option list: no:Row Labels Ascending Sort the rows according to the Row Labels in ascending order. descend:Descending by Row Totals Sort Row Totals in descending order. ascend:Ascending by Row Totals Sort Row Totals in ascending order. ldescend:Row Labels Descending Sort the rows according to the Row Labels in descending order. none:None Do not sort the rows.
Totals for Columns	sort_col	Input int	0	Specify whether to add a Column Total row. Each cell in this row contains the sum of the data in the same column.
Sort Output Columns	normalize	Input int	0	Specify the way to sort the output rows. Option list: lasc:Column Labels Ascending Sort the columns according to the Column Labels in ascending order. ldes:Column Labels Descending Sort the columns according to the Column Labels in descending order. asc:Ascending by Column Totals Sort Column Totals in ascending order. des:Descending by Column Totals Sort Column Totals in descending order. no:None Do not sort the columns.
Normalize by Column Totals	zero	Input int	1	Specify whether to normalize the output data by the Column Totals. Option list: no:None Do not normalize the data. frac:Fraction Normalize the data in each column by the column total and show them in fraction notation. perc:Percent Normalize the data in each column by the column total and show them in percent notation.
Show Zeros when Empty	exrow	Input string	<unassigned>	Specify whether to show missing values in the pivot table as zeros.
Row Source Extra Values	excol	Input string	<unassigned>	Specify additional values for row source.
Column Source Extra Values	rd	Output ReportData	[<input>]<new>	Specify additional values for column source.

Description

This X-Function allows you to select two columns to create a pivot table to visualize data summarization, which is a easy way to present information.

Examples

Example 1

A supermarket supplies different brands of products which are produced by different makes. The manager wants to know the number of products in shelf for different brands and different makes.

Select File: Import: Excel(xls, xlsx) from the Origin menu.
Browse to <Origin Program Folder>\Samples\Statistics to select HouseholdCareSamples.xls. Click the Add File(s) button. Make sure that the Show Options check box is selected. Click the OK button to open the impExcel dialog.
Under File Info and Data Selection, expand the HouseholdCareSamples.xls branch. Under File Sheet(s), select the check box next to HQ Family Mart and clear other check boxes in this branch.
In the Column Headers branch, select 1 with the Index of Rows for Long Name drop-down list. Click OK to import the data into Origin.
Highlight the Make column and select Worksheet: Pivot Table from the main menu to open the dialog.
Select Column F for Pivot Table Column Sources.
Select Sum with the Summarize by drop-down list. Then select column K for Pivot Table Data Source.
Then set the dialog options as the following image shows.
Click the OK button to create the pivot table. The table should like this:

Example 2

If the manager is only interested in the Brands which have large number of products in the shelf, then we need to use the Combine feature of Pivot table to combine smaller values of the result table above. In the following example, we only keep the brands columns in the result table above if their column total values are bigger than 10% percent of the Grand Total value.

Click the Lock icon in the upper-left corner of the Pivot1 worksheet to open the wpivot dialog again.
Then set the dialog as the following screenshots shows.
Click the OK button to update the result. We can find that the table has fewer columns.Only the columns with column total values bigger than 161.3 (10% of the Grand Total as we specified) has been kept and other columns are merged into a single Others column.