How do you determine the average values in second column of an excel data corresponding to a particular range of values in first column ?

1 次查看（过去 30 天）
Duphrin
Duphrin 2019-10-21
回答： Joe Vinciguerra 2019-10-21
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
2.2060 0.3120
2.2140 0.3138
2.2180 0.3146
2.2260 0.3164
3.8920 0.2611
3.9000 0.259
3.9070 0.2571
3.8840 0.2632
3.9600 0.2431
4.0010 0.2322
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
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
% ...OR...
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];
https://www.mathworks.com/help/matlab/ref/double.discretize.html
https://www.mathworks.com/help/matlab/ref/accumarray.html

更多回答（1 个）

Nicolas B.
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.
  2 个评论
Duphrin
Duphrin 2019-10-21
Thanks for the answer. But I need help with changing my range in steps of 1. Because I have values inside the range upto 30. Do I need to use loops?
Nicolas B.
Nicolas B. 2019-10-21
Then, I think that would be the easiest solution to simply loop with your criterias. I could imagine solutions based on the function discretize(), but it wouldn't avoid a loop:
mylim = [-inf, 1:30, inf]; % list of your limits
y = discretize(t, mylim); % you get the indexes
m = NaN(size(myLim)); % your output vector
for n = 1:numel(m) % loop to get the mean value
m(n) = mean(t(y == n), 'omitnan');
end

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Loops and Conditional Statements 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by