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?
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]