CODEWITHSUNDEEP
matlabpivot-table
mjeppesen
mjeppesen

Reputation: 1099

How to make pivot tables in matlab from a table

Is there an easy way to make pivot tables in matlab from a matlab table? As in as Excel, or pandas.pivot_table in Python? I've found pivottable.m on the file exchange, but it doesn't work with tables.

Here is an example, if I have a table t:

name     value
_____    _____

'Foo'     0   
'Bar'    -1   
'Bar'     5   
'Foo'     1

And I would like to aggregate on the name column, using the @sum function, to get: 

name     sum_of_value
_____    ________

'Bar'    4       
'Foo'    1

Is there easy way to do this?

Upvotes: 6

Views: 7686

Answers (3)

Lola D
Lola D

Reputation: 31

As of R2023a, you can use the "pivot" function: https://www.mathworks.com/help/matlab/ref/pivot.html

P = pivot(T, Rows="name", DataVariable="value");

The pivot function can also do 2d pivots by specifying a "Columns" value.

Prior to 23a, you can use groupsummary:

P = groupsummary(T,"name","sum","value");

With groupsummary, you can calculate multiple stats on multiple variables at once.

Upvotes: 3

Giuseppe
Giuseppe

Reputation: 507

You can use grpstats for simple summary tables. However, use unstack to get more complex pivots.

% Example: Create columns from Var1, summing the values for each date:

T = 
    date        item      value
    ________    _______   _______
    2015.2      a         1
    2015.2      a         1
    2015.2      b         1
    2015.2      c         1
    2015.4      a         2
    2015.4      b         2
    2015.4      c         2
    2015.4      d         2
    2016.2      a         3
    2016.2      b         3
    2016.2      c         3
    2016.2      d         3


T2 = unstack(T, 'value', 'item', 'GroupingVariables', 'date', 'AggregationFunction', @sum);

T2 = 
    date        a        b        c        d   
    ________    _____    _____    _____    _____
    2015.2      2        1        1        NaN
    2015.4      2        2        2        2
    2016.2      3        3        3        3

Upvotes: 6

mjeppesen
mjeppesen

Reputation: 1099

I found a way using accumarray (there may be a better way): 

[C,ia,ic] = unique(t.name);
pivot_table = table;
pivot_table.name = C;
pivot_table.sum_of_value = accumarray(ic, t.value, [], @sum)

pivot_table = 

name     sum_of_value
_____    ____________

'Bar'    4           
'Foo'    1

EDIT: I expanded this into a function and added it to the Matlab file exchange

Upvotes: 5

Related Questions