How can I identify the maximum value in a column and then grab specific cells in the same row as that value?

40 views (last 30 days)
In the script below, I am bringing in data from the attached spreadsheet and reorganizing it as necessary. After getting to the point where I have the load cases (LC1-LC7) the way I need, I need to identify the maximum resultant in the "Resultant" column, then pull out the cells associated w/ that value, in the same row that correspond with the columns titled: Structure Loads Vert. (lbs), Structure Loads Trans. (lbs) and Structure Loads Long. (lbs). In essence, I should end up with seven (7) 1x3 matrices (or tables). I will then need to save each of the 7 matrices/tables for later with a specific name (i.e. LC1_1_1, LC2_1_1, etc.). I'll then want to repeat when the SetNo.= 3 and Phase No.=1 (result matrices LC1_3_1, LC2_3_1, etc.) and so on.
data = readtable('34231_Future Loads.xlsx','VariableNamingRule','preserve');
data.resultant = sqrt(data.('Structure Loads Vert. (lbs)').^2 + data.('Structure Loads Trans. (lbs)').^2 + data.('Structure Loads Long. (lbs)').^2);
SetNo=[1 3 9 9 9 10 10 10 45 46 49 49 49 50 50 50 51 51 51 52 52 52 53 54 57 58 59 60];
PhaseNo=[1 1 1 2 3 1 2 3 1 1 1 2 3 1 2 3 1 2 3 1 2 3 1 1 1 1 1 1];
SetNo=[1 3 9 9 9 10 10 10 45 46 49 49 49 50 50 50 51 51 51 52 52 52 53 54 57 58 59 60];
PhaseNo=[1 1 1 2 3 1 2 3 1 1 1 2 3 1 2 3 1 2 3 1 2 3 1 1 1 1 1 1];
%NOTE: I could try to use a for loop here to loop through all set & phases
chosenSetNo = 1;
chosenPhaseNo = 1;
idx = data.('Set No.') == chosenSetNo & data.('Phase No.') == chosenPhaseNo;
data_1 = data(idx,:);
LC1 = data_1([],:);
LC2 = data_1([],:);
LC3 = data_1([],:);
LC4 = data_1([],:);
LC5 = data_1([],:);
LC6 = data_1([],:);
LC7 = data_1([],:);
%METHOD 1: LOGICAL INDEXING METHOD | This method helps save memory and
%possible errors.
LC1 = data_1(contains(data_1{:,4},"NESC 250B"|"NESC Insul"),:);
LC2 = data_1(contains(data_1{:,4},"Hurr"|"RULE 250C Insul"),:);
LC3 = data_1(contains(data_1{:,4},"Rule 250D"|"RULE 250D Insul NA+"|"RULE 250D Insul NA-"),:);
LC4 = data_1(contains(data_1{:,4},"Normal"),:);
LC5 = data_1(contains(data_1{:,4},"2%"),:);
LC6 = data_1(contains(data_1{:,4},"NESC 250B NL-, DE"|"NESC 250B NR-,DE"|"NESC 250B BI-, DE"),:);
LC7 = data_1(contains(data_1{:,4},"Rule 250D BI-, DE"|"Rule 250D NL-, DE"|"Rule 250D NR-, DE"),:);
%Delete the extra rows in LC1 & LC3 that apply to the full break cases for NESC 250B & Rule 250D
LC1([9,10,11],:) = [];
LC3([7,8,9],:) = [];

Accepted Answer

Jeremy Hughes
Jeremy Hughes on 7 Feb 2022
Sounds like, in MATLAB table nomenclature, you want to find the row of the table that contains the maximum value of a particular variable. E.g.
T = array2table(rand(10,5));
[~,idx] = max(T.Var1);
maxRow = T(idx,:)
maxRow = 1×5 table
Var1 Var2 Var3 Var4 Var5 _______ _______ _______ _______ _______ 0.95635 0.83718 0.98332 0.54921 0.19906
Or if you just want the values:
maxRow = T{idx,:}
maxRow = 1×5
0.9564 0.8372 0.9833 0.5492 0.1991
BTW: You really don't need any of these lines, you're overwriting them in the next block anyway:
LC1 = data_1([],:);
...
LC7 = data_1([],:);
Also, this is slightly faster access pattern:
LC1 = data_1(contains( data_1.(4) ,"NESC 250B"|"NESC Insul"),:);
  1 Comment
dpb
dpb on 7 Feb 2022
Edited: dpb on 10 Feb 2022
...
[~,idx] = max(T.Var1);
maxRow = T(idx,:);
The above idiom is so commonly needed in MATLAB owing to not being able to return other than the first argument inside a function argument list that I built my own wrapper for the purpose--
function idx=imax(varargin)
% Return max() indices as first argument
if nargin==2 & nargout>1
error('MATLAB:imax', ...
'IMAX with two matrices to compare and two output arguments is not supported.')
end
[~,idx]=max(varargin{:});
With it, one can write
maxRowVal=T(imax(T.Var1),:);
directly without the intermediate temporray idx.
I've always wondered why MATLAB didn't package the functionality either similarly as above or as an optional named parameter asking for the index to be returned instead of the value.

Sign in to comment.

More Answers (0)

Categories

Find more on Resizing and Reshaping Matrices 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!

Translated by