How to store the column names for each numerical values for respective rows in an array

I'd like to get from a raw matrix like this:

several cell arrays of strings like this:

   One = 'A'    'C'    'D'    'F'
   Two = 'B'    'G'

How could I do this?

[Jan]

What are your input data? The screen shot is not clear. Prefer to post code, which produces the input.

What is the relation between the array and the output? Why is

One = 'A'    'C'    'D'    'F'

wanted and what class is this? A cell string? Does the first column have a meaning?

[Pi Height]

My input data is an Excel file. The code to get this input data is:

    % Read Excel File
    filename = 'Name_of_Excel_File.xlsx';

    % Set Sheet and Size
    % If you want to try, use the correct sheet number
    Sheet = 3; 
    Impact = 'A1:H7';

    % Read and save to matrix
    [~,~,Impact] = xlsread(filename,Sheet,Impact);

The relation between the array and the output are the values in a row. So for each row needs to be an array/vector/list of strings (the strings are the header of the matrix/array i.e. 'A'...'G').

Yes, it should be a cell array of strings (cell string class). The first column should be the names of each cell string array.

The result for 'One' would be:

     One = 'A'    'C'    'D'    'F'

So for each row get for all numbers "1" the string of the header 'A'...'G' and store the strings to a list/vector of cell strings and name it like the first column 'One'...'Six'.

I hope I got this understandable. I really appreciate your effort and help.

Answer 1

[jonas]

You can try something like this.

data=readtable('Name.xlsx','sheet','test')
Headers=data.Properties.VariableNames(2:end);
One=Headers(data{1,2:end}==1)
Two=Headers(data{2,2:end}==1)
%etc..

[Pi Height]

Thank you very much!

[Pi Height]

I discovered an issue for the attached zip (xlsx). I get an error message for the sheet 'Test1' like this one:

Cannot concatenate the table variables 'A' and 'C', because their types are double and cell.

data.RTD=readtable('Name_of_Excel_File.xlsx','sheet','Test1');
data.Headers=data.RTD.Properties.VariableNames(4:end);
data.One=data.Headers(data.RTD{3,4:end}==1)';
data.Two=data.Headers(data.RTD{4,4:end}==1)';
data.Three=data.Headers(data.RTD{5,4:end}==1)';

The sheet 'Test' works fine, but I need to write some strings in a row under the header like in the Excel file 'Name_of_Excel_File'.

How can I solve this problem.

[jonas]

I see that you have displaced the table and inserted an 'x' in one of the columns. Matlab now reads the column with 'x' as a cell of strings instead of doubles. Unfortunately, you cannot force Matlab to read all columns as doubles, as the 'Format' option does not work for excel-files. What I have done is to only extract the part of the table that contains doubles. First, read the entire sheet and find the first row that contains the string 'One' and the last row which contains the string 'Six'. Then, extract this range and use the same code as previously.

T_raw=readtable('Name2.xlsx','sheet','Test1')
IndexTop=find(contains(T_raw.Var1,'One'))+1;
IndexBot=find(contains(T_raw.Var1,'Six'))+1;
range=['A',num2str(IndexTop),':J',num2str(IndexBot)]
T=readtable('Name2.xlsx','sheet','Test1',...
    'Range',range,...
    'readvariablenames',0)
Headers=T_raw.Properties.VariableNames(2:end);
One=Headers(T{1,2:end}==1)
Two=Headers(T{2,2:end}==1)

ugly solution but it works in this case