Matlab using accumarray with cell array

Question

I am quite new to Matlab, but I have some experience with other programming languages. I have a very large table imported from MySQL in Matlab. It is given as cell array that looks something like this:

 date   key     sales    weight  name
 12/11  101     1200     20      blue
 12/11  178     1200     70      purple
 13/11  209     1300     15      purple
 12/11  101     1200     10      blue
 14/11  678     1200     10      yellow
 12/11  340     1500     30      green
 17/11  178     1900     50      purple

And I want the output to be this:

 key     sales    weight  name
 101     2400     30      blue
 178     3100     120     purple
 209     1300     15      purple
 678     1200     10      yellow
 340     1500     30      green

So I would like to combine the rows which have the same number in the column 'key'. Meanwhile I would like to sum the column 'sales' and 'weight' and keep the column 'name' (each 'key' has the same 'name', but each 'name' can have multiple 'keys')

I know this is possible with a for loop, but as I am having to manipulate a lot of tables in similar but different ways this is computational intensive.

I have read in similar problems this can be solved using accumarray, but can this be done with accumarray with a sub as cell array? And how would that look like?

Answer 1

Here is one method using accumarray, however it might be worth your while to consider the new table data structure instead of a cell matrix (I'm sure you can easily convert to it)

T = {  101     1200     20      'blue'
       178     1200     70      'purple'
       209     1300     15      'purple'
       101     1200     10      'blue'
       678     1200     10      'yellow'
       340     1500     30      'green'
       178     1900     50      'purple'};

[u, ind, x] = unique([T{:,1}])

key = T(ind,1)
sales = accumarray(x', [T{:,2}])
weight = accumarray(x', [T{:,3}])
name = T(ind,4)

[key, num2cell(sales), num2cell(weight), name]

Answer 2

x={ '12/11'  101     1200     20      'blue'
 '12/11'  178     1200     70      'purple'
 '13/11'  209     1300     15      'purple'
 '12/11'  101     1200     10      'blue'
 '14/11'  678     1200     10      'yellow'
 '12/11'  340     1500     30      'green'
 '17/11'  178     1900     50      'purple'};

[~,b,c]=unique([x{:,2}]);
y=[x(b,2),...
    num2cell(sparse(1,c,[x{:,3}]))',...
    num2cell(sparse(1,c,[x{:,4}]))',...
    x(b,[5])];

unique is used to get the indices of duplicate keys. sparse is used to get the sum.