Max of cells. Keep whole Column

Hello Buddies, I have a table that looks like the one I attached. I would like to find maximum of a specific cell (horsepower), for each second Column. And then Return the Best 3 (or sometimes best 4, 5, 6... as well) of all the Columns to a result table. It should contain the left info column as well to each of the 'winners' (max's). The table can have more columns (even numbers since it pairs two) and do not necessarily have to have exact number of rows (as shown).. The table looks exact as attached. Anyone have some idea to handle this problem?

Hope to hear from someone, -Best Martin

Answer 1

[Geoff Hayes]

Martin - you could try iterating over each pair of columns and store the value of the horsepower variable (if it exists) along with the column index in a separate array then do some sort of sort afterwards to extract the top three, four, or five variables.

The following isn't tested but you should be able to adopt it for your needs

 % an array for the horsepower value and column index
 hpValueIdx = [];

 for k=1:2:size(myData,2)
     % determine the rows of the kth column that have the horsepower string
     hpIdx = strcmp(myData(:,k),'horsepower');

     if any(hpIdx)
         hpValue = myData{hpIdx,k+1};
         hpValueIdx = [hpValueIdx ; [str2num(hpValue) k]];
     end
 end

The above code assumes that there is only one horsepower row per column and the horsepower values are strings (which your attached image seems to suggest).

 After the *for* loop has completed its iterations, you can then sort the *hpValueIdx* 2-D array on the horsepower column as

 sortrows(hpValueIdx,1)

The result will be sorted in ascending order so you can just flip it (see flipud) to get the first column sorted in descending order. You can then choose how many (top three, four or five) that you wish to extract to create your results array.

[Martin]

Hi Geoff thank for the answer! and it is great help.. However there is still a problem when it comes to the sort part though, where I want to sort for one of the columns. Here the issue is to make the paired column follow... e.g. I change one of your last lines as follow:

if true
  % code
hpValueIdx = [hpValueIdx ; [str2num(hpValue) myData(:,k+1)' ; ... 
'text' mydata(:,k)']];
end

This give me hpValueIdx with great HorsePower values because of your help. If I use rot90()) it will look great as the myData. It is unfortunately still hard to sort it because of its double row (or double column)..

[Martin]

Okay, I think I fixed it! Splitted it up in two hpValueIdx's, left and right, and put it together later. Thx for the help again!

[Geoff Hayes]

Glad that it worked out, Martin!