Find average based on multiple columns of data structure
1 view (last 30 days)
Show older comments
I have data in the following format:
A B C
1 1 1 a
2 2 1 0
3 3 1 a
4 4 1 a
5 6 1 0
6 1 1 a
7 2 1 a
8 4 1 a
9 6 1 0
10 1 2 b
11 2 2 b
12 3 2 0
. . . .
. . . .
. . . .
99 1 20 c
100 2 20 0
101 6 20 c
I want to find the average of the values in Column-C for the unique values in Column-B for the unique values in Column-A. For example, average the non-zero values in Column-C (say, "a"), where the value of Column-B is "1" AND the value of Column-A is "1"; then do the same for Column-C values of "b" with Column-B values of "2" AND Column-A values of "1"; and so-forth and so-on through all the entries.
I have tried looping over the values in Column-A along with a loop over the values in Column-B, with no success. I have thought about "unique" or "foreach", but I have little experience with those functions, and I have not had time to attempt an implementation.
Thoughts?
3 Comments
Image Analyst
on 8 Aug 2014
Please make it easy for us to help you, not hard . Many/most of us are waiting for you to upload code to generate such a matrix, table, or cell array before we try anything.
Answers (1)
Nir Rattner
on 8 Aug 2014
Assuming your data is stored in a matrix, you can use the "unique" and "accumarray" functions. First, it seems that you want to remove all rows where the the third column is equal to zero. Next, you can pull the unique row entries considering only the first two columns to get your subscript for the "accumarray" function. Finally, you can use the "accumarray" function to take the average grouped by your unique subscripts:
M(M(:, 3) == 0, :) = [];
[i,~,j] = unique(M(:, [1,2]), 'rows');
[i, accumarray(j, M(:, 3), [], @mean)]
0 Comments
See Also
Categories
Find more on Large Files and Big Data 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!