how to find mean of specific values based on values of other columns

1
votes

I have a matrix like below.

2   1   0.020000000
2   1   0.020000000
2   1   0.020000000
2   1   0.020000000
2   2   0.434776340
2   2   0.392582120
2   2   0.549031660
2   3   0.0306320700000000
2   3   0.0336107500000000
3   1   0.0200000000000000
3   1   0.0200000000000000
3   1   0.0200000000000000
3   1   0.0200000000000000
3   1   0.0200000000000000
3   2   0.301534290000000
3   2   0.381151280000000
3   2   0.227146390000000
3   2   0.402937460000000
3   3   0.0773929900000000
3   3   0.0220243800000000
3   3   0.0859914800000000

I want to check the first columns values if it's 2 then check the next column and if its value is 1 calculate the mean of all values in column3 so it would be like this:

The mean of 3nd columns values and put the it in a different matrix.

    2   1   0.020000000
    2   1   0.020000000
    2   1   0.020000000
    2   1   0.020000000

Then try this process for 2 2, 2 3, 3 1 and so on.

The values of 2nd column is either 1,2 or 3 but the values of first column are in range of 2-5000. I tried like this but it doesn't work properly:

 [ii,jj]=find((S(:,2)==1)); //S is the matrix i mentioned earlier
      out=S(ii,[1,3]);
      for i=2:3
          if out(:,1)==i
              Mean(i) = mean (out(i,2));
          end
      end

Thanks!

2
matlabmatrix
Yes, they are, however in column1 prolly we don't have all values from 1-5000. – lighting

2 Answers

2
votes

Using unique and accumarray function: 

[unique_vals, ~, idx3]=unique(a(:,[1 2]),'rows'); % find unique combinations
means = accumarray(idx3, a(:,3),[],@mean); % find means
result = [unique_vals means]; % join groups and their means
1
votes

I am not really sure if I understand the question since you write that you want to compute the mean of the values in the third column, but also make a sub-matrix.

For now I assume that you want to compute the mean of all the values in column three that have 2 1 in the first two columns. The code would look something like this

mean21 = mean(S(S(:,1) == 2 & S(:,2) == 1,3))

You first compute a colum of booleans where col1 == 2 AND col2 == 1 (S(:,1) == 2 & S(:,2) == 1) and then retrieve the corresponding values of col3.

If you automatically want to do this for arbitrarily large matrices, the following would be one solution

for c1 = unique(S(:,2))'
    for c2 = unique(S(:,2))'
        mean_val(c1,c2) = mean(S(S(:,1) == c1 & S(:,2) == c2,3));
    end
end

However, this will give you NaNs if the combination c1, c2 does not exist.