pivot

Arguments

t

table or timeseries object

colvarnames

variables to group by on the pivot table columns.

data type expected: vector of strings containing the variable names or vector of indices corresponding to the positions of the variable names in the table/timeseries.

rowvarnames

variables to group by on the pivot table rows.

data type expected: vector of strings containing the variable names or vector of indices corresponding to the positions of the variable names in the table/timeseries.

Name, Value

Name: Method, Value: function: function to apply on defined DataVariable, with prototype: y = f(x), z = f(x, ..., y)

Available methods: "count", "percentage", "sum", "mean", "median", "mode", "var" (variance), "std" (standard deviation), "min", "max", "range", "nummissing", "numunique, "nnz", and user function.

Name: DataVariable, Value: variable names or variable indices: variable to which the computation method wil be applied.

Name: ColumnsBinMethod, Value: data interval or time interval: indicates how the colvarnames is grouped.

data type expected: vector of doubles vector containing the data interval or available string among: "year", "month", "day", "hour", "minute", "second", "monthname" and "dayname". Default value: "none".

Depending on the type of the variables contained in groupvars, groupbins will be a cell of the same size as groupvars, where each element will be applied to each variable.

Name: RowsBinMethod, Value: data interval or time interval: indicates how the rowvarnames is grouped.

data type expected: vector of doubles vector containing the data interval or available string among: "year", "month", "day", "hour", "minute", "second", "monthname" and "dayname". Default value: "none".

Depending on the type of the variables contained in groupvars, groupbins will be a cell of the same size as groupvars, where each element will be applied to each variable.

Name: IncludeTotals, Value: boolean (default value: %f): when %t, the pivot table will have an additional row containing the totals for each column and an additional variable containing the totals for each row

Name: IncludedEdge, Value: "left" or "right": (default value: 'left'): this option must use only if RowsBinMethod or/and ColumnsBinMethod are specified (i.e RowsBinMethod = [0 2 4]). When IncludedEdge is equal to 'left', data will be contained in the groups [bins(1), bins(2)), [bins(2), bins(3)), ..., [bins(n-1), bins(n)]. The last bins(n) is included. If IncludedEdge is equal to 'right', the intervals will be: [bins(1), bins(2)], (bins(2), bins(3)], ... (bins(n-1), bins(n)]. In this case, the first bins(1) is included. The values in RowsBinMethod or/and ColumnsBinMethod must be in strictly increasing order.

p

the pivot table

Description

The pivot function allows to analyze the data. The pivoted table crosses the data by allowing them to be grouped, compared and combined.

p = pivot(t, Columns=colvarnames, Rows=rowvarnames) creates a pivoted table. The p columns names are generated using the unique combination between given colvarnames data. Each row corresponds to one group of data present in t(:, rowvarnames). The content of p is the unique combination of rowvarnames and colvarnames.

Specifying only colvarnames, the pivot table contains one row of group counts. And if only rowvarnames is specified, the pivot table contains one variable of group counts.

g = pivot(..., DataVariable=datavar) indicates the variable to which the method defined by Method will be applied. If Method is not specified, the applied function depends on the type of datavar ("sum" if the data are double, "count" else).

g = pivot(..., Method=method) indicates the method to apply the data. NaN values are omitted by default when the predefined methods are used.

g = pivot(..., ColumnBinMethod=val) or g = pivot(..., RowBinMethod=val) indicates how the groups for colvarnames or/and rowvarnames are grouped.

g = pivot(..., IncludeTotals=val) adds one row and one column in fonction if colvarnames and rowvarnames are defined, when %t. This additional row contains the total for each column and this additional column contains the total for each row.

Examples

g = pivot(t, Columns=colvarnames, Rows=rowvarnames)

t = readtable(fullfile(SCI, "modules", "spreadsheet", "tests", "unit_tests","meteo_data_bordeaux.csv"));
p = pivot(t, Columns="OPINION", Rows="UV_INDEX")

g = pivot(t, Columns=colvarnames, Rows=rowvarnames, Method=method, DataVariable=datavar)

t = readtable(fullfile(SCI, "modules", "spreadsheet", "tests", "unit_tests","meteo_data_bordeaux.csv"));
p = pivot(t, Columns="OPINION", Rows="UV_INDEX", Method="max", DataVariable="SUNHOUR")

With IncludedEdge

rand("seed", 0)
x1 = floor(rand(5,1)*5)-1.5;
x2 = -floor(rand(5,1)*5)+0.5;
x = ["a"; "b"; "b"; "c"; "a"];
y = ["x"; "x"; "x"; "y"; "y"];

A = table(x, y, x1, x2, "VariableNames", ["x", "y", "v1", "v2"])

// With ColumnsBinMethod and IncludedEdge = "left" (default value so no need to specify it)
P = pivot(A, Rows="x", Columns="v1", ColumnsBinMethod=[-1.5 0 1.5])

// With ColumnsBinMethod and IncludedEdge = "right"
P = pivot(A, Rows="x", Columns="v1", ColumnsBinMethod=[-1.5 0 1.5], IncludedEdge="right")

// With RowsBinMethod and IncludedEdge = "left" (default value so no need to specify it)
P = pivot(A, Rows="v1", Columns="x", RowsBinMethod=[-1.5 0 1.5])

// With RowsBinMethod and IncludedEdge = "right"
P = pivot(A, Rows="v1", Columns="x", RowsBinMethod=[-1.5 0 1.5], IncludedEdge="right")

See also

• table — create a table from variables
• timeseries — create a timeseries - table with time as index
• groupcounts — returns the number of elements for each group
• varfun — apply a function to each column of the table/timeseries
• rowfun — apply a function to each row of the table/timeseries
• groupsummary — create groups in table or timeseries and apply functions to variables within groups