Average multiple cells in the same lat and lon

Hello,

I have a 3422x5 (double) numeric matrix A = [Latitude Longitude Biomass lower_boundary upper_boundary]

the lower and upper boundary represent depth

I want to be able to average the biomass across any bins that are in the same exact latitude and longitude. There are several measurements that are in the same lat and lon, so I want to be able to get just one data point for each lat and lon. I also want to be able to calculate the standard deviation of each of those calculations in a different column to keep track of the statistics. Along with this, I would like to keep the lower and upper boundary columns intact with respect to the new, averaged dataset. The duplicated bins all have the same correlated upper and lower boundary

What would be the most efficient way of accomplishing this? I am attaching the matlab file for better reference as to what I am talking about

Any help is appreciated.

Thanks,

Melissa

Answer 1

[Sean de Wolski]

% Remove nans
idxKeep = ~any(isnan(A(:,1:3)),2);
Akeep = A(idxKeep,:);

% Unique combinations of lat/lon and where they are
[ulatlon,~,idx] = unique(Akeep(:,1:2),'rows');

% Mean of each combination
meanBm = accumarray(idx,Akeep(:,3),[],@mean);

% Combine unique lat/lon combos and meanBm into table
T = table(ulatlon,meanBm,'VariableNames',{'LatLon','MeanBioMass'});

% Show Results
disp(T)

[Melissa]

Thank you! This code worked perfectly. However, I am having some trouble figuring out how to get the corresponding indices for the Biome data, since it is a cell, not a double. How can I acquire the indices from the final, uniquely averaged matrix so that I can find the respective Biome, lower, and upper boundary data to match them up with?

Does that make sense?

So I want the matrix

B = [Lat Lon MeanBiomass lower_boundary upper_boundary Biome];

and that would be a matrix of dimensions 555x6

[Sean de Wolski]

If I understand you correctly, you want the min and max of my Akeep array, above.

Simply call accumarray a couple more times with it:

lowerBm = accumarray(idx,Akeep(:,3),[],@min);
upperBm = accumarray(idx,Akeep(:,3),[],@max);

Then add them to the table.

[Melissa]

Hi Sean,

Not quite. The mean is for the biomass while the upper and lower boundary relate to the depth in centimeters at which the biomass measurement was taken.

Therefore, meanBm is completely different from the lower and upper boundary.

So I want to find the indices for all of the final unique lat and lon so that I can extract those indices from the original lower and upper boundary depths.

So in the end I will have the meanBm, lower depth, and upper depth of each unique latitude and longitude.

Is there a way to get this from the idx?

[Sean de Wolski]

I guess I'm not clear. Isn't that what idx is above? idx, right now is telling you which row each unique lat/lon is?

So for a given array

 x = [1;4;4]

[ux,idxf,idxr] = unique(x)

 ux =
     1
     4
idxf =
     1
     2
idxr =
     1
     2
     2

So idxr (idx in my original) is telling me where each row came from. idxf, is the first match for the unique values. I think all you need to do is group by idxr, but I'm not completely clear.

Could you provide a small example of what you want with five rows, and just lower boundary?

[Melissa]

Ah, yes! So what I was looking for in your example is idxf! That is the one that you omitted in your original code with ~. So I switched it to

[ulatlon,idxf,idx] = unique(Akeep(:,1:2),'rows');

Now I can use idxf to do this

lowerKeep = lower_boundary(idxf);

upperKeep = upper_boundary(idxf);

Which now allows me to make

A = [Lat Lon meanBm Lower Upper]

Thank you so much for your quick responses!