Documentation Center

  • Trial Software
  • Product Updates

unstack

Unstack data from single variable into multiple variables

Syntax

  • W = unstack(T,vars,ivar) example
  • W = unstack(T,vars,ivar,Name,Value)
  • [W,it] = stack(___) example

Description

example

W = unstack(T,vars,ivar) converts the tall table, T, to an equivalent table, W, that is in wide format. unstack unstacks a single variable in T, specified by vars, into multiple variables in W. In general, W contains more variables, but fewer rows, than T.

An indicator variable in T, specified by ivar, determines which variable in W contains each value in var after it is unstacked. unstack treats the remaining variables in T as grouping variables. Each unique combination of values in the grouping variables identifies a group of rows in T that will be unstacked into a single row of W.

W = unstack(T,vars,ivar,Name,Value) converts the table T to wide format with additional options specified by one or more Name,Value pair arguments.

For example, you can specify how unstack converts variables from T to variables in W.

example

[W,it] = stack(___) also returns an index vector, it, indicating the correspondence between rows in W and rows in T. You can use any of the previous input arguments.

Examples

expand all

Separate One Variable into Three Variables

Create a table indicating the amount of snowfall in various towns for various storms.

Storm = [3;3;1;3;1;1;4;2;4;2;4;2];
Town = {'T1';'T3';'T1';'T2';'T2';'T3';...
    'T2';'T1';'T3';'T3';'T1';'T2'};
Snowfall = [0;3;5;5;9;10;12;13;15;16;17;21];

T = table(Storm,Town,Snowfall)
T = 

    Storm    Town    Snowfall
    _____    ____    ________

    3        'T1'     0      
    3        'T3'     3      
    1        'T1'     5      
    3        'T2'     5      
    1        'T2'     9      
    1        'T3'    10      
    4        'T2'    12      
    2        'T1'    13      
    4        'T3'    15      
    2        'T3'    16      
    4        'T1'    17      
    2        'T2'    21      

T contains three snowfall entries for each storm, one for each town.

Separate the variable Snowfall into three variables, one for each town specified in the variable, Town.

W = unstack(T,'Snowfall','Town')
W = 

    Storm    T1    T2    T3
    _____    __    __    __

    3         0     5     3
    1         5     9    10
    4        17    12    15
    2        13    21    16

Each row in W contains data from rows in T that have the same value in the grouping variable, Storm. The order of the unique values in Storm determines the order of the data in W.

Apply Aggregation Function to Each Group

Unstack data and apply an aggregation function to multiple rows in the same group that have the same values in the indicator variable.

Create a table containing data on the price of two stocks over 2 days.

Date = [repmat({'4/12/2008'},6,1);...
    repmat({'4/13/2008'},5,1)];
Stock = {'Stock1';'Stock2';'Stock1';'Stock2';...
    'Stock2';'Stock2';'Stock1';'Stock2';...
    'Stock2';'Stock1';'Stock2'};
Price = [60.35;27.68;64.19;25.47;28.11;27.98;...
    63.85;27.55;26.43;65.73;25.94];

T = table(Date,Stock,Price)
T = 

       Date         Stock      Price
    ___________    ________    _____

    '4/12/2008'    'Stock1'    60.35
    '4/12/2008'    'Stock2'    27.68
    '4/12/2008'    'Stock1'    64.19
    '4/12/2008'    'Stock2'    25.47
    '4/12/2008'    'Stock2'    28.11
    '4/12/2008'    'Stock2'    27.98
    '4/13/2008'    'Stock1'    63.85
    '4/13/2008'    'Stock2'    27.55
    '4/13/2008'    'Stock2'    26.43
    '4/13/2008'    'Stock1'    65.73
    '4/13/2008'    'Stock2'    25.94

T contains two prices for STOCK1 during the first day and four prices for STOCK2 during the first day.

Create a table containing separate variables for each stock and one row for each day. Use Date as the grouping variable and apply the aggregation function, @mean, to the numeric values from the variable, Price, for each group.

[W,it] = unstack(T,'Price','Stock',...
    'AggregationFunction',@mean)
W = 

       Date        Stock1    Stock2
    ___________    ______    ______

    '4/12/2008'    62.27     27.31 
    '4/13/2008'    64.79     26.64 


it =

     1
     7

W contains the average price for each stock grouped by date.

it identifies the index of the first value for each group of rows in T. The first value for the group '4/13/2008' is in the seventh row of T.

Input Arguments

expand all

T — Tall tabletable

Tall table, specified as a table. T must contain data variables to unstack, vars, and an indicator variable, ivars. The remaining variables in T are either grouping variables or constant variables.

vars — Variables in T to unstackpositive integer | vector of positive integers | variable name | cell array of variable names | logical vector

Variables in T to unstack, specified as a positive integer, vector of positive integers, variable name, cell array of variable names, or logical vector.

ivar — Indicator variable in Tpositive integer | variable name

Indicator variable in T, specified as a positive integer or a variable name. The variable specified by ivar indicates which variable in W each value in var is unstacked into.

ivar can be a numeric vector, logical vector, character array, cell array of strings, or categorical vector.

Name-Value Pair Arguments

Specify optional comma-separated pairs of Name,Value arguments. Name is the argument name and Value is the corresponding value. Name must appear inside single quotes (' '). You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

Example: 'AggregationFunction',@mean applies the aggregation function @mean to the values in vars.

'GroupingVariables' — Grouping variables in T that define groups of rowspositive integer | vector of positive integers | variable name | cell array of variable names | logical vector

Grouping variables in T that define groups of rows, specified as the comma-separated pair consisting of 'GroupingVariables' and a positive integer, vector of positive integers, variable name, cell array of variable names, or logical vector. Each group of rows in T becomes one row in W.

The default is all the variables in T not listed in vars or ivar.

'ConstantVariables' — Variables constant within a grouppositive integer | vector of positive integers | variable name | cell array of variable names | logical vector

Variables constant within a group, specified as the comma-separated pair consisting of 'ConstantVariables' and a positive integer, vector of positive integers, variable name, cell array of variable names, or logical vector. The default is no variables.

The values for these variables in W are taken from the first row in each group in T.

'NewDataVariableNames' — Names for new data variables in Wcell array of strings

Names for new data variables in W, specified as the comma-separated pair consisting of 'NewDataVariableNames' and a cell array of strings. The default is strings based on the values of the grouping variable specified in ivar.

'AggregationFunction' — Aggregation function from values in vars to single valuefunction handle

Aggregation function from values in vars to single value, specified as the comma-separated pair consisting of 'AggregationFunction' and a function handle. unstack applies this function to rows from the same group that have the same value in ivar. The function must aggregate the data values into a single value.

For a numeric data variable, the default is @sum. For nonnumeric variables, there is no default function, and you must specify the 'AggregationFunction' name-value pair argument if multiple rows in the same group have the same value in ivar.

Output Arguments

expand all

W — Wide tabletable

Wide table, returned as a table. W contains the unstacked data variables, the grouping variables, and the first value of each group from any constant variables.

The order of the data in W is based on the order of the unique values in the grouping variables.

You can store additional metadata such as descriptions, variable units, variable names, and row names in the table. For more information, see Table Properties.

it — Index to Tcolumn vector

Index to T, returned as a column vector. For each row in W, the index vector, it, identifies the index of the first value in the corresponding group of rows in T.

More About

expand all

Grouping Variables

Grouping variables are utility variables used to group, or categorize, data. Grouping variables are useful for summarizing or visualizing data by group. You can define groups in your table by specifying one or more grouping variables.

A grouping variable can be any of the following:

  • Categorical vector

  • Cell array of strings

  • Character array

  • Numeric vector, typically containing positive integers

  • Logical vector

Rows that have the same grouping variable value belong to the same group. If you use multiple grouping variables, rows that have the same combination of grouping variable values belong to the same group.

Tips

  • You can specify more than one data variable in T, and each variable becomes a set of unstacked data variables in W. Use a vector of positive integers, a cell array containing multiple variable names, or a logical vector to specify vars. The one indicator variable, specified by the input argument, ivar, applies to all data variables specifies by vars.

See Also

|

Was this topic helpful?