How can I average one column based on the bins created for the other column?

8 views (last 30 days)
Marmar on 8 Feb 2019
Edited: Adam Danz on 11 Feb 2019
Hi All,
I have two columns as a matrix they show the elevaiton (m) of data points (1st column) and thier snow depth (Second column). I want to make bins for elevaiton and see what is the average of the snow depth in that elevation range. I want the elevation data to go to bins of every 12 meter and see i each of this bins how much was the average of the snow depth. So at the end I need two columns one shows the bins (elevation: 0, 12, 24, ...) and the other shows average of snowdepth in that elevation range.

Adam Danz on 8 Feb 2019
Edited: Adam Danz on 9 Feb 2019
This example creates a fake dataset to work with. It then creates bins for the elevation data in steps of 12. Using splitapply, it finds the mean of snow depth for each bin and then puts the data into a table.
% Create fake data [elevation, snowDepth]
data = [randi(100, 1000, 1) + rand(1000,1), randn(1000,1) + 10];
% Create bins
elevationBins = 0:12:max(data(:,1)+11);
% Determine bin membership
[~, bins, binID] = histcounts(data(:,1), elevationBins);
% average snow depth per bin
binMeans = splitapply(@mean, data(:,2), findgroups(binID));
% Put results into table
table(unique(bins(binID))', binMeans, 'VariableNames', {'BinMin', 'BinMean'})
Marmar on 9 Feb 2019
my data is not integer. It gives an error that:
Error using splitapply: Group numbers must be a vector of positive integers, and cannot be a sparse vector.
:(
The real data looks like this:
Adam Danz on 9 Feb 2019
Edited: Adam Danz on 11 Feb 2019
You might not be applying the example correctly to your data. Another possibility is that outliers are messing with the bins (for example, if 1 of your elevations is 100x larger than the rest).
I edited my solution so that it handles outliers better. The change I made was
binMeans = splitapply(@mean, data(:,2), binID); %OLD
binMeans = splitapply(@mean, data(:,2), findgroups(binID)); %NEW
Try to apply your data again to the updated solution. If you continue to have problems, please provide a short sample of your data that I can either copy or download the code.

Marmar on 8 Feb 2019
ANY OTHER IDEASE WILL BE GREATLY APPRECIATED!