Mean of values in acolumn depending on VARYING values of another column

Hi, I would like to calculate the mean of values in a coulmn, based on the values in another column. However, different from other questions asked previously on this forum, the values in the second column I need to use are not specific, but change. Please see attached file and hopefully it will be easier to get what I mean. Maybe I need to use a for loop running through all the values of the second column? Thank you!

Answer 1

[Dyuman Joshi]

in =readmatrix('Book2.xlsx');
disp(in)

     1     1
     2     2
     3     1
     4     2
     5     3
     6     1
     7     2
     8     4
     9     1
    10     5
    11     6
    12     5
    13     5
    14     4
    15     6
    16     3
    17     6
    18     2
    19     1
    20     5
    21     3
    22     2
    23     4

out = accumarray(in(:,2),in(:,1),[],@mean)

out = 6×1

7.6000 10.6000 14.0000 15.0000 13.7500 14.3333

[Freya]

Thank you! However, I get a "out" column which is way more longer than the input columns, and I would expect to get a shorter output since it is calculating the means. Thanks for your help!

[Torsten]

If you split the first column in 6 groups (according to the indices in the second column) and calculate the mean within these groups, you get a 6x1 vector as output as in @Dyuman Joshi 's code. Or is this not what you asked for ?

[Freya]

Hi, yes this is exactly was I asked for. However, when I run the code with my data (that before was just an example), I get an output vector that is way bigger than what i started with. In my data, I have 2 columns with 13354 rows each. When I run that code, I get a coulmn with 1000607508 rows, which should obviously not be. Any ideas? Thanks!

[Dyuman Joshi]

Please attach the data you are working with. Use the paperclip button to attach.

[Torsten]

Are you sure that your second column only has integer numbers <= 13354 and no Inf or NaN values ?

Please check it:

any(in(:,2)>13354)
any(isinf(in(:,2)))
any(isnan(in(:,2)))

Results should be false (0)

[Freya]

Hi @Torsten, Yes, both columns have integers numbers.

Hi @Dyuman Joshi, attached the data.

Thanks both for the help!

[Torsten]

If you look at row 1, the number in the second column is 1000227087. This is much bigger than the number of rows of your matrix and makes no sense for grouping in the way @Dyuman Joshi 's code works.

[Voss]

in = readmatrix('Book3.xlsx')

in = 13354×2

1.0e+09 * 0.0000 1.0002 0.0000 1.0002 0.0000 1.0006 0.0000 1.0006 0.0000 1.0006 -0.0000 1.0006 0.0000 1.0006 0.0000 1.0006 -0.0000 1.0006 -0.0000 1.0006

[~,~,idx] = unique(in(:,2));

out = accumarray(idx,in(:,1),[],@mean)

out = 535×1

0.4535 0.2406 0.3664 0.1558 0.3591 0.3346 0.2348 0.3358 -0.0250 0.2608

[Freya]

@Torsten, this is because the second columns are cell IDs basically. But regardless, the values of the second columns represent just the grouping method, using that code I still should get a smaller outpt, right?

[Freya]

@Voss thank you, it worked now!

[Voss]

@Elisabeth Spiritosanto You're welcome!

Don't forget to Accept @Dyuman Joshi's answer.

[Freya]

Hi @Dyuman Joshi, @Torsten, @Voss and sorry to bother you again. How can I get in the output another column displaying the group ID per each row of the output values in the (first) column? Thanks!!

[Dyuman Joshi]

Use the first output of unique()

format longg

in = readmatrix('Book3.xlsx');

[out1,~,idx] = unique(in(:,2))

out1 = 535×1

1.0e+00 * 1000227087 1000227106 1000594972 1000594973 1000594974 1000594975 1000594976 1000594977 1000594978 1000594979

idx = 13354×1

1 2 3 4 5 6 7 8 9 10

out2 = accumarray(idx,in(:,1),[],@mean)

out2 = 535×1

0.453491780924332 0.240638995346782 0.366433792273591 0.155783195173162 0.359111503883272 0.334567544249371 0.234826867085867 0.335793779361822 -0.0249592514078918 0.260758590555741

Concatenate them horizontally to get them in the same array

out = [out1 out2];

[Freya]

Great! Thanks again @Dyuman Joshi!

Answer 2

[Ken Garrard]

You do not need a for loop.

Assumming that your data is in an Nx2 variable named, mydata.

% Select rows matching criteria in column 2
% For example, values in column 2 greater than one and less than five
hits = mydata(:,2) > 1 & mydata(:,2) < 5;
% Mean of values from column 1
mean(mydata(hits,1))

ans =

12.7273