Calculate average between min and max numbers in column 3 depending on values in column 2, for each value in column 1

4 views (last 30 days)
Hello. I was wondering if someone could help me with this:
Let's say you have a 2000 x 3 matrix, where all the elements are integers. In column 1, the values can be between 1 and 16. How can we select one element in column 1 (e.g. 7), then for those rows only, select the rows with the highest 5 values in column 2, and for these rows only, select their elements in column 3 and calculate the average between the min and max values (output).
Thanks!
  2 Comments
Voss
Voss on 19 Jan 2022
"select the rows with the highest 5 values in column 2"
Does this mean, "select the 5 rows with the highest value in column 2"?
Or does it mean, "select all rows whose value in column 2 is among the 5 highest unique values in column 2"?
(It's understood that we're talking about only those rows that match the selected element in column 1 already.)
Voss
Voss on 20 Jan 2022
Edited: Voss on 20 Jan 2022
@Neuro FYI, the accepted answer here selects the 5 rows with the highest value in column 2, whereas the accepted answer in another question you posted selects all rows whose value in column 2 is among the 5 highest unique values in column 2. You should be aware of this distinction because it seems likely that these questions are related.
To illustrate the distinction:
X = [1 2 3 4 5 5 5 6 6 6 6];
maxk(X,5) % 5 highest values
ans = 1×5
6 6 6 6 5
uX = unique(X);
X(X >= uX(end-4)) % among the 5 highest unique values
ans = 1×10
2 3 4 5 5 5 6 6 6 6

Sign in to comment.

Accepted Answer

Matt J
Matt J on 19 Jan 2022
Edited: Matt J on 19 Jan 2022
A=yourMatrix;
idx=A(:,1)==7;
B=A(idx,:);
[~,idx]=maxk(B(:,2),5);
C=B(idx,3);
result=0.5*(max(C)+min(C))

More Answers (2)

Image Analyst
Image Analyst on 19 Jan 2022
Just take it one step at a time. Try this.
% Let's say you have a 2000 x 3 matrix, where all the elements are integers.
% In column 1, the values can be between 1 and 16. How can we select one element in column 1 (e.g. 7),
% then for those rows only, select the rows with the highest 5 values in column 2,
% and for these rows only, select their elements in column 3 and
% calculate the average between the min and max values (output).
m = randi([1, 16], 2000, 3);
% Find rows with column 1 = 7
rows1Equals7 = m(:, 1) == 7;
% Get values from column 2 in those rows
col2 = m(rows1Equals7, 2)
col2 = 109×1
7 11 6 3 5 12 7 12 8 1
% Select the rows with the highest 5 values
[highValues, rowsWithHighValues] = maxk(col2, 5)
highValues = 5×1
16 16 16 16 16
rowsWithHighValues = 5×1
33 35 41 44 46
% Get the values from column 3 in those 5 rows.
col3 = m(rowsWithHighValues, 3)
col3 = 5×1
11 4 5 4 15
% Get the average "between" the max and min. Not sure what "between" means
% so I'm taking the average of the min and max.
max3a = mean([min(col3), max(col3)])
max3a = 9.5000
% Alternative: Get the average of ALL 5 values
max3b = mean(col3)
max3b = 7.8000

Voss
Voss on 19 Jan 2022
matrix = randi([1 16],2000,3);
search_number = 7;
idx_1 = matrix(:,1) == search_number;
col2_vals = unique(matrix(idx_1,2));
idx_2 = idx_1 & matrix(:,2) >= col2_vals(end-4);
col3_min = min(matrix(idx_2,3));
col3_max = max(matrix(idx_2,3));
col3_avg = (col3_min+col3_max)/2;
disp(col3_min);
1
disp(col3_max);
16
disp(col3_avg);
8.5000

Categories

Find more on Introduction to Installation and Licensing in Help Center and File Exchange

Products


Release

R2021b

Community Treasure Hunt

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

Start Hunting!