How to extract minimum values in a row and replace them with their variable?

I have four colums and 928 rows in a table. I would like to extract the smallest number in each row and replace it with the variable (title/name) at the top of the column in which the value came from.

I have managed to extract the smallest number by changing the table into an array. However, I was not able to change that small value into the name of the column. Moreover, I do not prefer changing the table into arrays becasue I would like to have my final result in a table format.

How may I do so? Find the smallest value in a row and replace it with its title in a table?

[madhan ravi]

upload your table as .mat file

[Alayt Abraham Issak]

I did that using the following code.

My excel sheet is known as Placement_data_scrubbed.

files = dir('*xlsx.');
for k = 1:numel(files)
[num,text,raw] = xlsread(files(k).Placement_data_scrubbed) [dummy,Placement Info] = fileparts(files(k).Placement_data_scrubbed); 
save(myName) 
end

However I was not able to load it

load (Data_Placement.mat)

[Kevin Phung]

its load (‘file name’), so :

load(’Data_Placement.mat’)

if therr are multiple variables then you can pass specific variables in the second arg

[madhan ravi]

what is your goal ? illustrate with an example of the output

[Alayt Abraham Issak]

Subset is the table which I got my data from. The numbers in columns S107-S112 symbolize the semester students took their math classes. Ex: for the first row, 1 in S111 shows that the student took the S111 math class in thier first semester.

Subset = Placement(:,[4 7:10])

My goal is to replace the smallest number (the first math class) students took with the name of the class. Ex: for row 8, I would like to replace 1 in column 2 with 'S107'.

Here is what I have done so far.

First_class = table2array( Subset );
ABtable = table(First_class(:,1),First_class(:,2),First_class(:,3),First_class(:,4),First_class(:,5));
tableVarNames = {'Plmt','S107','S108','S11','S112'};
ABtable.Properties.VariableNames = tableVarNames;
%ABtable is similar to Subset but in array form. 

for i = 1:size(ABtable,1)
    [M,I] = min(cell2mat(ABtable{i,:})) %M is the minimum value, I is the indice
    ABtable{i,I} = tableVarNames(I); 
end

The loop does not work. Here is the error I keep on getting

"Brace indexing is not supported for variables of this type.Error in cell2mat (line 42) cellclass = class(c{1});"

My overall goal is to have two columns: One with 'Plmt' and one with the names of the classes that correspond to the first class (smallest number).

[madhan ravi]

See if the below does what you want , it calculates the minimum of the entire table:

Minimum=min(min(table2array(T),[],'omitnan')) % T your table

Note:Upload you table as .mat file , it's the second time I am asking.

[Alayt Abraham Issak]

I'm quite new and did not know how to do that, that's why I changed it directly to array.

[Alayt Abraham Issak]

Also, the code outputs "1".

Answer 1

[Kevin Phung]

If you want to have both numeric and strings in your table, those table elements will have to be cell arrays.

for example, if you have :

a = {1 2 3 4 5}; % your data values, stored in cells.

table1 = table(a',a',a')
tableVarNames = {'a','b','c'}; %your column names
table1.Properties.VariableNames = tableVarNames

to change a table element from numeric to a string, running :

table1{1,3} = tableVarNames(1)

will give you:

table1 =
  5×3 table

     a      b      c 
    ___    ___    ___

    [1]    [1]    'a'
    [2]    [2]    [2]
    [3]    [3]    [3]
    [4]    [4]    [4]
    [5]    [5]    [5]

So something like this will find the minimum for each row and replace it with variable names:

for i = 1:size(table1,1)
    [M,I] = min(cell2mat(table1{i,:})) %M is the minimum value, I is the indice
    table1{i,I} = tableVarNames(I); %changes that value to a variableName
end

[Alayt Abraham Issak]

Is there a reason you concatenate the table? I do not want to do that, and when I take it out I get all the columns in under one variable name.

[Kevin Phung]

I was just making a trivial table, and how to change the contents of a table with different data types . Did the for loop that I provided not resolve your issue?

[Alayt Abraham Issak]

No, the loop did not work. I've written a detailed repsonse on the comment above.

[Kevin Phung]

You got the error because your AB table needs to be cell arrays.

Can you try table2cell for your ABTable.

so:

ABTable = table2cell(Subset) % this is now a matrix of cells, NOT a table
tableVarNames = {'Plmt','S107','S108','S11','S112'} % your variable names
for i = 1:size(ABtable,1)
    [M,I] = min(cell2mat(ABtable{i,:})) %M is the minimum value, I is the indice
    Subset{i,I} = tableVarNames(I); %makes changes to your main table, Subset
end