How can I find the average of certain rows in a column depending on the value of another column?

I have a matrix with several columns and rows. I need to find the average of particular rows in one column depending on if the value in the row for another column is 1 or 2. So for example, in the following matrix:

1    1
2    1
3    2
4    2
5    1

I need to calculate the average of 1, 2 and 5 giving me 3.67. If possible, I also need to check the value in each row for an additional column as well.

Thank you so much!

Answer 1

[madhan ravi]

Loop is a waste of time, try this:

splitapply(@mean,A(:,1),findgroups(A(:,2)))

Answer 2

[KSSV]

A = [1    1
2    1
3    2
4    2
5    1];

[c,ia,ib] = unique(A(:,2)) ; 
iwant = zeros(length(c),1) ; 
for i = 1:length(c)
    iwant(i) = mean(A(A(:,2)==c(i),1)) ; 
end

Answer 3

[Allen]

I may not completely understand the problem statement or the splitapply function, but it does not appear that the solution provides the correct answer. It appears that you want to use column 2 as a key for determining which rows to use when calculating the average in column 1. Ultimately using rows 1, 2, and 5 (coincidentally these are also the values used) in column 1 to calculate the mean, since those rows in column 2 all are equal to a value of 1. Expanding this to better illustrate, I have added 3 additional columns to your 5x2 matrix with random values with code to calculate the means by column.

A = [1, 1, 5, 6, 7
     2, 1, 6, 1, 5
     3, 2, 7, 2, 8
     4, 2, 3, 9, 6
     5, 1, 4, 5, 9];
% Written with additional variables to provide a clearer understanding of what is happening.
keyVal = 1;
keyCol = 2;
Cols = [1,3,4,5];
mean(A(A(:,KeyCol)==keyVal,Cols))

% The last for lines of code can be condensed to the following.
mean(A(A(:,2)==1,[1,3:5]))

This returns the answers to the mean([1,2,5]) for column 1, mean([5,6,4]) for column 3, mean([6,1,5]) for column 4, and mean([7,5,9]) for column 5, which results in the following:

ans =

    2.6667    5.0000    4.0000    7.0000

[megara_bleu]

What if you also want to take the average of other values associated with other numbers in column 2. Let's say you have:

A = [1, 1, 5, 6, 7

2, 1, 6, 1, 5

3, 2, 7, 2, 8

4, 2, 3, 9, 6

5, 2, 4, 5, 9

6, 3, 2, 8, 7

7, 3, 6, 9, 5];

where in column 2, I arranged the numbers in order, so column 2 has [1,1,2,2,2,3,3]. There are three unique numbers, {1,2,3}, where 1 appears 2 times; 2 appears 3 times; 3 appears 2 times.

I want to find the average of all numbers in column 1 that are associated with 1 in column 2; all numbers in column 1 associated with 2 in column 2; all numbersin column 1 associated with 3 in column 2. So I make a new array:

unique_column_2 = unique(A{:,2});

avg_column_1 = zeros(length(unique_column_2),1);

After this I'm stuck, I tried many if loops and I would get NaN for every cell in avg_column_1. I tried your synax up there and I still get NaN.

All I want is an array to compute the average of values that are associated with certain values on the other columns. Thank you so much if you can help!