aggregation by grouping unique values

I have a matrix (14252*2) dimensions. Column one elements represents numerical value-identifiers that may be duplicated and appear several times across the rows. Column 2 elements represent measured values. I need to aggregate the values from column 2 across the same identifiers from column 1, then get the average ( dividing the Sum of column2 values, by how many times the unique identifier from column 1 appears). This is what I tried, but not sure if I have wrote it correctly:

[a,~,c] = unique(COST(:,1));  %extract unique values of the indicator column
out(:,1)=a; % assign the unique values extracted to the first column of the output table
% loop for all the columns to compute summary statistics according to the unique  values 
    by_col = [a, accumarray(c,COST(:,2))]; % extract (unique values in first column and summary statistics in the second column for each column
    out(:,2)=by_col(:,2); % extract the summary statistics for i column to the output table;

Acc_COST=[out]; %The matrix containing the aggregated data at the CBG level. (758*102)
%Count how many 1km*1km Pixels in each census block group.
[x,y]=hist(COST(:,1),unique(COST(:,1)));
CBGCount=x';
%Calculate the average of carbon sequestrated at each CBG level. 

for i=1:14652;
        AVG_COST(i,:)= [Acc_COST(i,2)/CBGCount(i,:)];
end

Answer 1

[Star Strider]

I can’t follow our code, or what you’re doing. It appears that you may not be using accumarray to its full potential.

See if this does what you want:

[a,~,c] = unique(COST(:,1));
AvgCost = [COST(a,1) accumarray(c, COST(:,2), [], @mean)]

NOTE — This worked for me in a similar application, but since I don’t have your data to test it with, I am labeling this as UNTESTED CODE.

[Amine Ben Ayara]

Dear Star lord! ;) I cannot possibly thank you for your feedback. Would you tell me, how I will need to modify this code you wrote if I have one more column to COST, and I need to aggregate the values based on unique identifiers as well.

[Star Strider]

My pleasure!

It seems accumarray likes vectors, not matrices, for its second argument, so we have to make two calls to it, one for each column:

COST = [1 2 3; 1 3 4; 2 3 5; 2 4 7; 3 5 9; 3 1 7];              % Test Matrix
[a,~,c] = unique(COST(:,1));
AvgCost = [a  accumarray(c, COST(:,2), [], @mean)  accumarray(c, COST(:,3), [], @mean)]

That works with my simple (6x3) test matrix.