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?

Can you provide an example of how exactly your cell array is stored? Does it have headings? Is the entire key column one array, sales one array etc? — Stewie Griffin
– Stewie Griffin, Commented Jan 15, 2014 at 9:44

Dan · Accepted Answer · 2014-01-15 13:46:56Z

3

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]

edited Jan 15, 2014 at 13:46

answered Jan 15, 2014 at 11:27

Dan

45.8k20 gold badges98 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3193435 Over a year ago

Works like a charm. Thank you. I will have a look at the new table and categorical array that Matlab introduced!

Daniel · Accepted Answer · 2014-01-15 11:33:23Z

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.

edited Jan 15, 2014 at 11:33

answered Jan 15, 2014 at 10:22

Daniel

36.8k3 gold badges38 silver badges73 bronze badges

5 Comments

Dan Over a year ago

I think you mean y=[x(b,2), num2cell(sparse(1,c,[x{:,3}]))', num2cell(sparse(1,c,[x{:,4}]))', x(b,[5])]? Why does sparse sum btw?

Daniel Over a year ago

@Dan: You are right, I summed only the sales, not the weight. Check sparse([1,1],[1,1],[2,3]) to understand how sparse can be used to calculate a sum.

Dan Over a year ago

I'm afraid I still don't understand it. I think it might be worth adding an explanation to your answer.

Daniel Over a year ago

If you assign two values to the same index, sparse uses the sum. I think user3193435 should use your answer, accumarray it's propably easier to understand.

user3193435 Over a year ago

I tested it on my table and it worked! Thank you a lot.

Collectives™ on Stack Overflow

Matlab using accumarray with cell array

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related