CODEWITHSUNDEEP
matlab
Marouen
Marouen

Reputation: 945

Collapsing and averaging redundant entries in MATLAB table

I have the following MATLAB table

item_a item_b score
a      b      1
a      b      1
b      c      3
d      e      2
d      e      1
d      e      0

I want to average the redundant rows. The desired result is as follows:

item_a item_b score
a      b      (1+1)/2
b      c      3
d      e      (2+1+0)/3

Upvotes: 1

Views: 82

Answers (2)

Paolo
Paolo

Reputation: 26084

This is a classic scenario for the findgroups, split-apply workflow. Given your table named t:

% Find mean values.
G = findgroups(t.item_a);
meanValues = splitapply(@mean,t.score,G);

% Create new table.
[~,i] = unique(G);
newTable = t(i,:);
newTable.score = meanValues

newTable contains the desired table.

See this documentation page for more examples.

Upvotes: 3

Osman Coskun
Osman Coskun

Reputation: 121

This is what I got. You can tweak with the final results. There is a similar example on MATLAB documentation. Here are two key functions, accumarray and unique. Note that this solution works only for array inputs not cell data types. By manipulating data types, you can also find the solution for table and cell data types. Otherwise, I think for loop will be necessary.

items = ['a'      'b'   
    'a'      'b'    
    'b'      'c'    
    'd'      'e'      
    'd'      'e'   
    'd'      'e' ];
scores = [1 1 3 2 1 0]';
[items_unique,ia,ic] = unique(items,'rows');
score_mean = accumarray(ic,scores, [], @mean);
result = {items_unique score_mean};

Upvotes: 1

Related Questions