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

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!

[Voss]

"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]

@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

Answer 1

[Matt J]

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))

[Neuro]

Thanks! That's exactly what I needed.

Answer 2

[Image Analyst]

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

Answer 3

[Voss]

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