update the column headng by readin the excel file

How to update the column heading of a cell vector which holds the combination?

Answer 1

[Star Strider]

If you want the variable names, first:

T1 = readtable('YourFile.xlsx', 'VariableNamingRule','preserve')

then:

ColumnHeadings = T1.Properties.VariableNames;

then for example:

FirstHeading = ColumnHeadings{1};

to return the first heading (variable name).

EDIT — (14 Jul 2023 at 15:27)

Use the sortrows function —

% figure
% imshow(imread('2023-07-14_13h06_04.png'))

data = readtable('Book2.xlsx', 'VariableNamingRule','preserve')

data = 6×6 table
    No    Components         DescriptioninGUI         Parameters    Value1    Value2
    __    __________    __________________________    __________    ______    ______

    1       {'bb'}      {'Length'                }      {'L' }        80       110  
    2       {'bb'}      {'Width'                 }      {'W' }       NaN       NaN  
    3       {'bb'}      {'thickness'             }      {'T' }       1.6         2  
    4       {'bb'}      {'height above the plate'}      {'HA'}       NaN       NaN  
    5       {'bb'}      {'height below the plate'}      {'HB'}       NaN       NaN  
    6       {'bb'}      {'edge width'            }      {'CE'}       NaN       NaN  

emptyRows = all(ismissing(data),2);
data(emptyRows,:) = [];
variable = data.Parameters; 
values = {};
for i = 1:size(data, 1)
    values{i} = table2array(data(i, 5:end)); 
    values{i}(all(ismissing(values{i}),2),:) = [];
end 
% remove empty cell
values = values(~cellfun(@isempty,values));
combinations = combvec(values{:})';
combinations = sortrows(combinations,1);                        % <— ADDED
Lv = ~all(ismissing([data.Value1 data.Value2]),2);              % <— ADDED
p = variable(Lv);
comitable= array2table(combinations);
comitable.Properties.VariableNames = p(1:size(combinations,2))

comitable = 4×2 table
     L      T 
    ___    ___

     80    1.6
     80      2
    110    1.6
    110      2

EDIT — (14 Jul 2023 at 15:57)

Added ‘Lv’ (and references to it) to select the correct values of ‘p’.

[PA]

Here W has no values as its value 1 and value 2 are empty. and the 1.6,2 are the values of thickness so the column heading need to be T instead of W . How can i change this?

[Image Analyst]

comitable.Properties.VariableNames{2} = 'T'

[Star Strider]

I am not exactly certain what you want to do.

I edited my previous code (adding ‘Lv’ although it does not necessarily need to exist as a separate variable unless you need it elsewhere) to select the correct elements of ‘p’.

[Star Strider]

I do not understand.

My best guess is to choose to include the appropriate other variables.

[Image Analyst]

@Star Strider I don't fully understand either. Could be because @PA won't attach the workbook despite me explicitly asking for that. All I get is that he/she is doing a Design of Experiments study and needs a full factorial combination of all possibilities. I guess you could use nested for loops but there could be other ways and I'm not going to try to explain them when so little effort was given in explaining the context of the problem.

[Star Strider]

This was not part of your original post, and the file is different. That aside, hoping to use ‘Book2.xlsx’ instead, reveals that it is no longer posted, so I cannot run the code to see what the results could be.

% figure
% imshow(imread('2023-07-14_13h06_04.png'))

data = readtable('Book2.xlsx', 'VariableNamingRule','preserve')

Error using readtable
Unable to find or open 'Book2.xlsx'. Check the path and filename or file permissions.

emptyRows = all(ismissing(data),2);
data(emptyRows,:) = [];
variable = data.Parameters; 
values = {};
for i = 1:size(data, 1)
    values{i} = table2array(data(i, 5:end)); 
    values{i}(all(ismissing(values{i}),2),:) = [];
end 
% remove empty cell
values = values(~cellfun(@isempty,values))
combinations = combvec(values{:})'
combinations = sortrows(combinations,1)                         % <— ADDED
% % Lv = ~all(ismissing([data.Value1 data.Value2]),2);              % <— ADDED
% % p = variable(Lv);
% % comitable= array2table(combinations);
% % comitable.Properties.VariableNames = p(1:size(combinations,2))

.

[Star Strider]

The ‘Book2.xlsx’ file is not that and I cannot change it to match. If you are starting from whatever the original table is (that I do not have access to, and is different from ‘Book2.xlsx’), the stack function could be worth experimenting with.

That is the best I can do. Beyond that, I have no idea what you want to do.

[PA]

thanks for the feedback

[Star Strider]

As always, my pleasure!

I might be able to experiment with the correct file, however the file provided (‘Book2.xlsx’) is not the file in your latest Comments.

[PA]

Well, i had to change before there was no allignment in the excel table and now there is allignment which holds character values. the code is same till removing the empty lines but creating combinations has change

[Star Strider]

I still do not understand.

[Image Analyst]

@PA is it solved or not? Be clear. If it's not, then (again) attach your workbook and better explanation. And answer my questions below.

[Star Strider]

I got this far by making some improvements.

The ‘nOflements’ variable is missing, replaced it with ‘numlements’ and that seems to work.

Try this —

data = readtable('Book2.xlsx', 'VariableNamingRule','preserve')

data = 5×6 table
    No    Components    Specification     Parameters    V1     V2 
    __    __________    ______________    __________    ___    ___

    1      {'bb'  }     {'length'    }      {'L'}        50     75
    2      {'bb'  }     {'width'     }      {'W'}        10     15
    3      {'bb'  }     {'height'    }      {'H'}       NaN    NaN
    4      {'bb'  }     {'thickness' }      {'T'}       NaN    NaN
    5      {'bb_1'}     {'allignment'}      {'A'}       NaN    NaN

emptyRows = any(ismissing(data),2)                          % <— CHANGED (Corrected)

emptyRows = 5×1 logical array
   0
   0
   1
   1
   1

 
% data(emptyRows,:) = []
data = data(~emptyRows,:)                                   % <— CHANGED

data = 2×6 table
    No    Components    Specification    Parameters    V1    V2
    __    __________    _____________    __________    __    __

    1       {'bb'}       {'length'}        {'L'}       50    75
    2       {'bb'}       {'width' }        {'W'}       10    15

 
%
 
 Parameters = data(:,4)

Parameters = 2×1 table
    Parameters
    __________

      {'L'}   
      {'W'}   

 
 
 
values = {};
 
for i = 1:size(data, 1)
 
    values{i} = table2array(data(i, 5:end));
 
    values{i}(all(ismissing(values{i}),2),:) = [];
 
end
 
 
   values = values(~cellfun(@isempty,values))

values = 1×2 cell array
    {[50 75]}    {[10 15]}

 
   n = numel(values);
 
  numcomb = prod(cellfun(@numel, values));
 
 
 
% Use nested loops to create combinations

combinationc = cell(numcomb, n);                                    % Preallocate
 
for a = 1:numcomb
 
    remainder = a;
 
    for b = 1:n
 
        array = values{b};
 
        numlements = numel(array);
 
       
 
%         Compute the current index for the current cell array
 
        % index = mod(remainder - 1, nOflements) + 1;
        index = mod(remainder - 1, numlements) + 1;                 % Guessing Here
 
        remainder = (remainder - index) / numlements;
 
       
 
%         Assign the current element to the combination
        

        % Check_Variable = array(index)
       combinationc{a, b} = array(index);                           % Change Name To Avoid OVershadowing The 'combinations' Function, Change Indexing
    end
end

combinationc                                                        % View Result

combinationc = 4×2 cell array
    {[50]}    {[15]}
    {[75]}    {[15]}
    {[50]}    {[10]}
    {[75]}    {[10]}

Is this the result you want?

.

[PA]

Thank You very much Star Strider , No not this one but Thanks once again

[Star Strider]

As always, my pleasure!

[PA]

do You know how can i convert this combinations from a cell array to a vector keeping the text entries .

{'50'} {'15'} {'Name'}

{'75'} {'15'} {'Name'}

{'50'} {'10'} {'Age' }

{'75'} {'10'} {'Age' }

{'50'} {'15'} {'Age' }

{'75'} {'15'} {'Age' }

{'50'} {'10'} {'Name'}

{'75'} {'10'} {'Name'}

i tried to use str2doublr but i get this

c =

50 15 NaN

75 15 NaN

50 10 NaN

75 10 NaN

50 15 NaN

75 15 NaN

50 10 NaN

75 10 NaN

[Star Strider]

Mixed numeric and text can only be combined in a cell array or a table, and a string array is another option (although I am not certain that it is much of an improvement over the cell array here, other than with respect to indexing). The cell2table function might be an option.

Perhaps one of these —

C = {{'50'}    {'15'}    {'Name'}
    {'75'}    {'15'}    {'Name'}
    {'50'}    {'10'}    {'Age' }
    {'75'}    {'10'}    {'Age' }
    {'50'}    {'15'}    {'Age' }
    {'75'}    {'15'}    {'Age' }
    {'50'}    {'10'}    {'Name'}
    {'75'}    {'10'}    {'Name'}};

T1 = cell2table(C)

T1 = 8×3 table
      C1        C2         C3   
    ______    ______    ________

    {'50'}    {'15'}    {'Name'}
    {'75'}    {'15'}    {'Name'}
    {'50'}    {'10'}    {'Age' }
    {'75'}    {'10'}    {'Age' }
    {'50'}    {'15'}    {'Age' }
    {'75'}    {'15'}    {'Age' }
    {'50'}    {'10'}    {'Name'}
    {'75'}    {'10'}    {'Name'}

Text = string(C(:,3));
T2 = array2table(cellfun(@str2double,C(:,[1 2])), 'VariableNames',{'N1','N2'});
T2 = addvars(T2, Text)

T2 = 8×3 table
    N1    N2     Text 
    __    __    ______

    50    15    "Name"
    75    15    "Name"
    50    10    "Age" 
    75    10    "Age" 
    50    15    "Age" 
    75    15    "Age" 
    50    10    "Name"
    75    10    "Name"

S = [cellfun(@str2double,C(:,[1 2])) string(C(:,3))]

S = 8×3 string array
    "50"    "15"    "Name"
    "75"    "15"    "Name"
    "50"    "10"    "Age" 
    "75"    "10"    "Age" 
    "50"    "15"    "Age" 
    "75"    "15"    "Age" 
    "50"    "10"    "Name"
    "75"    "10"    "Name"

.

Answer 2

[Image Analyst]

You forgot to attach your workbook, and forgot to say what kind of variable you want to work with in MATLAB (table, cell array, double). Cell arrays don't have column headings unless you mean the first row of the cell array. If you have the first row be a string, and subsequent rows have numbers, then you'd be best off using a table instead of a cell array.

t = readtable(yourWorkbookFileName)

If you have any more questions, then attach your data and code to read it in with the paperclip icon after you read this:

TUTORIAL: How to ask a question (on Answers) and get a fast answer