Averaging values in column 2 if column 1 falls within a certain range

Hi,

I am pretty stuck and would really appreciate some/any help or a push in the right direction.

I have matrix "B" which has time(sec) in column 1 and Diameter in column 2.

I am trying to do something like this:

If column 1 is between n and n+0.99 seconds, average all the value in column 2 and also indicate what value 'n' was in the column next to it. Repeat this for n+1 all the way to the end.

so the intervals will be 1 - 1.99 , 2 - 2.999 , 3 - 3.99 all the way to 60 seconds.

Thank you,

Nev

Answer 1

[Alex Mcaulley]

Try this (just changing by your real B):

n = 1:60;
B = rand(1000,2);
B(:,1) = linspace(1,60,1000);

means = arrayfun(@(i) mean(B(B(:,1)>=n(i)&B(:,1)<n(i+1),2)),1:(numel(n)-1));
Nextn = B(arrayfun(@(i) find(B(:,1)>=n(i)&B(:,1)<n(i+1),1,'last'),1:(numel(n)-1))+1,1);

[Nev]

Hi Alex,

Thanks for this, however, the means given when using this code and subsituting for my real B aren't correct. I expect for example the average diameters for the time points between 2 - 2.99 seconds to be 43.82 however I am getting 45.61. I am having a go at understanding the code to see what could be changed and how it's getting the results that its providing.

Nevine

[Alex Mcaulley]

Can you upload your data in a mat file? This code works for me using several examples.

[Nev]

Sure, no problem

[Alex Mcaulley]

The code is working fine. Probably you misunderstood the result given in variable means. It is structured as follows:

means(1) = nan %Mean of the values between 1 and 1.99 (You don't have data in this interval)
means(2) = 43.8237 %Mean of the values between 2 and 2.99
means(2) = 45.1252 %Mean of the values between 3 and 3.99
%and so on

[Nev]

Hi,

I only get the mean table with the correct values nan 43.8237 etc when I use the below code (code 1). If i use code 2 the values in B(:,1) are changed and so the means from B(:,2) provided are incorrect, maybe I haven't substituted something correctly.

However, code 1 does what I need it to do, so thank you for your help! :) I would never have been able to do it without your help

%% Code 1 
n = 1:60;
B(:,1);
means = arrayfun(@(i) mean(B(B(:,1)>=n(i)&B(:,1)<n(i+1),2)),1:(numel(n)-1));

%% Code 2

n = 1:60;
B(:,1) = linspace(1,60,7841);
means = arrayfun(@(i) mean(B(B(:,1)>=n(i)&B(:,1)<n(i+1),2)),1:(numel(n)-1));
Nextn = B(arrayfun(@(i) find(B(:,1)>=n(i)&B(:,1)<n(i+1),1,'last'),1:(numel(n)-1))+1,1);

[Alex Mcaulley]

Yes, this line:

B(:,1) = linspace(1,60,7841);

was just for my example data. The correct code is

n = 1:60;
means = arrayfun(@(i) mean(B(B(:,1)>=n(i)&B(:,1)<n(i+1),2)),1:(numel(n)-1));
Nextn = B(arrayfun(@(i) find(B(:,1)>=n(i)&B(:,1)<n(i+1),1,'last'),1:(numel(n)-1))+1,1);

Answer 2

[Steven Lord]

I would use groupsummary specifying the groupbins input as either a list of bin edges or (if you can convert your time data to a duration array) 'second' and the method as 'mean'.

[Nev]

Hi Steven,

Thanks for this. I will also give this a go as I have another set of data that requires intervals that are less than n+1 (n to n+0.5 for example) which I can't seem to do with the above code as it says that "Array indices must be positive integers or logical values"

Thanks :)!

Nevine