2.2.4.2 Wtspivot


Contents

Menu Information

Restructure: Time Series Pivot...

Brief Information

Aggregate data based on a date-time column

Additional Information

Minimum Origin Version Required: Origin 2024

Command Line Usage

1. wtspivot -r 2 time:=[Book1]Sheet1!A"DATE" data:=[Book1]Sheet1!B"PRCP" rowsBucket:="3 Month: Quarter" rowsPeriod:=Year colsBucket:=Month;

2. wtspivot -r 2 time:=[Book10]Sheet1!A data:=[Book10]Sheet1!B rowsBucket:=Month rowsPeriod:=Year rowsStart:=Apr rowsEnd:=Jun colsBucket:=Week colsPeriod:=Month colsStart:=6 colsEnd:=7 method:=custom custom:="sum max";

X-Function Execution Options

Please refer to the page for additional option switches when accessing the x-function from script

Variables

Display
Name 		Variable
Name 		I/O
and
Type 		Default
Value 		Description
Source Time time

Input

Range

<unassigned>
 Specify the range that will be used as the time source of the Pivot Table.
Source Data data

Input

Range

<unassigned>
 Specify the range of data values to be summarized.
Aggregation Interval rowsBucket

Input

string

<none>
 Specify the aggregation interval of time in the rows of the pivot table. This time interval is shown in first column of the pivot table.

Option list:

<none> 10 yr: Decade Year 3 Month: Quarter
Month Week Day Hour
30 min 15 min Minute
Time Period rowsPeriod

Input

string

<none>
 Specify the time group of the aggregation interval. According to the selected Aggregation Interval, you can specify the corresponding time period.
Remove Empty rowsRemoveEmpty

Input

int

1
 Specify whether to remove the empty rows.

Option list:

  • 0=false
  • 1=true
Start rowsStart

Input

string

Enter the start time of the rows of the pivot table.

The time format of the Start is based on the specified Aggregation Interval.

End rowsEnd

Input

string

Enter the end time of the rows of the pivot table.

The time format of the End is based on the specified Aggregation Interval.

Custom Format rowsCustomFmt

Input

string

Custom the time format showing in the first column
Aggregation Interval colsBucket

Input

string

<none>
 Specify the aggregation interval of time in the columns of the pivot table. This time interval is shown in Comment label row of the pivot table.

Option list:

<none> 10 yr: Decade Year 3 Month: Quarter
Month Week Day Hour
30 min 15 min Minute
Time Period colsPeriod

Input

string

<none>
 Specify the time group of the aggregation interval. According to the selected Aggregation Interval, you can specify the corresponding time period.
Remove Empty rowsRemoveEmpty

Input

int

1
 Specify whether to remove the empty columns.

Option list:

  • 0=false
  • 1=true
Share Rows Start/End share

Input

int

0
Specify whether to share rows Start or End to the columns
Start colsStart

Input

string

Enter the start time of the columns of the pivot table.

The time format of the Start is based on the specified Aggregation Interval.

End colsEnd

Input

string

Enter the end time of the columns of the pivot table.

The time format of the End is based on the specified Aggregation Interval.

Custom Format colsCustomFmt

Input

string

Custom the time format showing in the Comment label row.
Aggregate by method

Input

int

2
 Specify the way to summarize the Source Data.

Option list:

count:Count {0} Output the count of each Row/Column pair.
sum:Sum {1} Output the sum of each Row/Column pair.
mean:Mean {2} Output the mean of each Row/Column pair.
min:Min {3} Output the minimum value of each Row/Column pair.
max:Max {4} Output the maximum value of each Row/Column pair.
median:Median {5} Output the median value of each Row/Column pair.
sd:SD {6} Output the Standard Deviation of each Row/Column pair.
se:SE {7} Output the Standard Error of each Row/Column pair.
rms:RMS {8} Output the Root Mean Square of each Row/Column pair.
ci:Confidence Interval {9} Output the confidence interval for a population mean of each Row/Column pair (using a normal distribution).
range:Range (Max - Min) {10} Output the range between maximum and minimum of each Row/Column pair.
first:First in Subgroup {11} Output the first value in each Row/Column pair.
last:Last in Subgroup {12} Output the last value in each Row/Column pair.
custom:Custom {13} Select this option to define your own summarized method in Custom edit box below.
Custom custom

Input

String

mean sd
 Available only when Aggregate by is set to Custom. You can mix them and construct your own summarized method.
Show Zeros when Empty zeros

Input

int

0
 Specify whether to show missing values in the pivot table as zeros.

Option list:

  • 0=false
  • 1=true
ReportData rd

Output

ReportData

[<input>]<new>
 Specify where to output result table.

Description

This X-Function allows you to create a pivot table to visualize data summarization (of statistics) based on a date-time column