How do you determine the average values in second column of an excel data corresponding to a particular range of values in first column ?
3 ビュー (過去 30 日間)
I have an excel file which containes more than 300000 in both column a and b. If the values in first column is less than 1 ,I need to average the corrosponding values in the second column and then increasing the range to 1 to 2 and so on up to 30..Any help would be greatly appreciated. Thank you.
My file looks like this
If the values in first column is less than 1 ,I need to average the corrosponding values in the second column and then increasing the range to 1 to 2 and so on up to 30..Any help would be greatly appreciated. Thank you.
Joe Vinciguerra 2019 年 10 月 21 日
Here's another approach. Because of the size of your dataset I would recommend avoiding loops. the accumarray function applies similar logic as suggested by Nicolas, but can run faster.
yourData(:,3) = floor(yourData(:,1)); % create a new column by rounding column 1 down to nearest integer
yourData(:,3) = discretize(yourData(:,1),0:30,'IncludedEdge','left'); % this is SIMILAR floor, but gives you more control in defining how to handle the edges of your data if you need to.
% choose only one of the above based on your needs.
[C,~,IC] = unique(yourData(:,3)); % C is a list of all unique values in the new 3rd column (integers between 0 and 30). IC associates the row numbers in your data to the row numbers of C.
avg = accumarray(IC, yourData(:, 2), , @mean); % calculate the mean in your data of column 2 for rows in IC with identical elements
Result = [C avg];
その他の回答 (1 件)
Nicolas B. 2019 年 10 月 21 日
For that situation, the easiest way is to select data based on a criteria. For example, if your table is named t:
indRows = t(:,1) < 1; % get row indexes for values below 1
myMean = mean(t(indRows, 2)); % compute the mean
Of course, you can also merge the 2 line of codes to avoid using indRows.