How can I find average of a range of values of the first column in a two column matrix

I have a two column matrix, with the first column having distances r and the second column, the value of a physical quantity measured at that r. The r values are not equally spaced, though they are in the ascending order. I need to take the average of the r values that are nearby (say from 1. 2000 to 1.2999) and the corresponding average of the quantity in the second column and generate a new matrix with the average values

r Q r_new Q_new

1.253048 0.939051 1.27 0.9197

1.257249 0.900010 1.3596 0.8023

1.299932 0.920000 1.45338 0.8188

1.305562 0.892354 ............ ............

1.356277 0.785555 ............ ..............

1.386665 0.635889 50.226 0.5684

1.389999 0.895554

1.420385 0.852316

1.486392 0.785361

................ ...............

................ ...............

50.02786 0.567852

50.42389 0.568992

Answer 1

[Cris LaPierre]

Try using groupsummary or grpstats. In both, you can specify bins to use for grouping data in one variable, and use that grouping to compute the mean value of a second variable of equal size.

You might also consider looking into findgroups and splitapply, but my preference is groupsummary.

Consider this example:

% Sales data in a table
Profit = [2032 3071 1185 2587 1998 2899 3112 909 2619 3085]';
TotalItemsSold = [14 13 8 5 10 16 8 6 7 11]';
sales = table(Profit,TotalItemsSold)

sales = 10×2 table
    Profit    TotalItemsSold
    ______    ______________

     2032           14      
     3071           13      
     1185            8      
     2587            5      
     1998           10      
     2899           16      
     3112            8      
      909            6      
     2619            7      
     3085           11      

% Compute mean number of items sold by Profit grouped into bins of 1000
avg = groupsummary(sales,"Profit",[0:1000:4000],"mean","TotalItemsSold")

avg = 4×3 table
    disc_Profit     GroupCount    mean_TotalItemsSold
    ____________    __________    ___________________

    [0, 1000)           1                    6       
    [1000, 2000)        2                    9       
    [2000, 3000)        4                 10.5       
    [3000, 4000]        3               10.667       

[MERIN JOSE]

Thanks a lot Cris!

Infact I wanted the mean of the r values as well. So, I made the matrix into a three column matrix (additional column is the same as first column of r values itself) and gave the following code:

avg = groupsummary(G,"r",[1.2:0.1:50.5],"mean");

where G is the three column matrix (first two columns are same r values, third column has the Q values).

[Cris LaPierre]

Rather than duplicating the first column, you could use the 'datavars' input.

avg = groupsummary(G,"r",[1.2:0.1:50.5],"mean",["r","Q"]);

Using the example above:

% Sales data in a table
Profit = [2032 3071 1185 2587 1998 2899 3112 909 2619 3085]';
TotalItemsSold = [14 13 8 5 10 16 8 6 7 11]';
sales = table(Profit,TotalItemsSold);

% Compute mean profit & number of items sold by Profit grouped into bins of 1000
avg = groupsummary(sales,"Profit",[0:1000:4000],"mean",["Profit","TotalItemsSold"])

avg = 4×4 table
    disc_Profit     GroupCount    mean_Profit    mean_TotalItemsSold
    ____________    __________    ___________    ___________________

    [0, 1000)           1              909                  6       
    [1000, 2000)        2           1591.5                  9       
    [2000, 3000)        4           2534.2               10.5       
    [3000, 4000]        3           3089.3             10.667       

[MERIN JOSE]

Thanks again Cris!

Answer 2

[Voss]

If A is your two column matrix, then A_new below is the average values of the rows where the first column of A is greater than or equal to 1.2 and less than 1.3:

idx = A(:,1) >= 1.2 & A(:,1) < 1.3;
A_new = mean(A(idx,:),1);