aggregation by grouping unique values
11 views (last 30 days)
Show older comments
Amine Ben Ayara
on 9 Mar 2016
Edited: Image Analyst
on 9 Mar 2016
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
0 Comments
Accepted Answer
Star Strider
on 9 Mar 2016
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.
2 Comments
Star Strider
on 9 Mar 2016
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.
More Answers (0)
See Also
Categories
Find more on Array and Matrix Mathematics in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!