how extract data from .mat files into a excel sheet and create column headers row header from cell array data

i have a list of .mat files (135 files), each file has a cell array by the name description which has information as follows -

1@12345/@raw12345/tfd_456_relative.mat del

1@12345/@raw12345/tfd_456_relative.mat the

1@12345/@raw12345tfd_456_relative.mat alp

1@12345/@raw12345/tfd_456_relative.mat gam

1@12345/@raw12345/tfd_456_relative.mat gams

1@12345/@raw12345/tfd_456_relative.mat wol

2@12345/@raw12345/tfd_456_relative.mat del

2@12345/@raw12345/tfd_456_relative.mat the

2@12345/@raw12345/tfd_456_relative.mat alp

2@12345/@raw12345/tfd_456_relative.mat gam

2@12345/@raw12345/tfd_456_relative.mat gams

2@12345/@raw12345/tfd_456_relative.mat wol

and a cell array by the name Val which has data as 200x2 double for example as follows

0.2215 0.2215

0.2451 0.2451

0.2689 0.2689

0.2215 0.2215

0.2451 0.2451

0.2689 0.2689

0.2275 0.2275

0.2460 0.24560

0.2689 0.2689

0.2215 0.2215

0.2451 0.2451

0.2689 0.2689

I want to extract data from just 1st coloumn in Val into excel, but create different sheets for del the alp gam gams wol (mentioned in description cell array example: 1@12345/@raw12345/tfd_456_relative.mat del) and make coloumn headers in each sheet as subjectid (which will be 12345) 1 2 all from description cell array. so it should appear like this-

sheet del

subjectid 1 2

12345 0.2215 0.2275

sheet the

subjectid 1 2

12345 0.2451 0.2460

thanks in advance, am new to matlab so struggling to create this complex loop

Answer 1

[Ayush Modi]

Hi,

I understand you would like to store the values from the cell array “Val” and “description” fields into different sheets according to the values in the “description” field. You can achieve this by using “dictionary” objects. Please find the below solution to your query.

(Assumption: The input file I have used is named as ‘file1.mat’ and the output spreadsheet is named as ‘data.xlsx’.)

matFiles = {'file1.mat', ‘file2.mat’}; % Input filenames
filename = 'data.xlsx'; % Output spreadsheet name
sheetNames = {'del', 'the', 'alp', 'gam', 'gams', 'wol'};
header = {'subjectid', '1', '2'};

for i = 1:numel(matFiles)
    data = load(matFiles{i});
    description = data.description;
    val = data.Val(:, 1);
    
    for j = 1:numel(sheetNames)
        
        M = dictionary();
        matchingRows = contains(description, sheetNames{j});
        values = val(matchingRows,1);
        
        desc = description(matchingRows,:);
        
        for k = 1:size(values)
            res = strsplit(desc{k});
            res = strsplit(res{1},'/');
            res = strsplit(res{1},'@');
            sid = str2double(res{2});
            tab = str2double(res{1});
            
            if M.isConfigured() && isKey(M,sid)
                temp = M(sid);
            else
                temp = {[NaN NaN]};
            end
            temp{1}(tab) = values(k);
            M(sid) = temp;
        end
        
        key = keys(M,"cell")
        for l = 1:size(key)
            temp = M(key{l})
            temp = temp{1}
            
            z = {key{l} temp(1) temp(2)}
            
            if i == 1
                writecell([header; z], filename, 'Sheet', sheetNames{j});
            else
                writecell(z, filename, 'Sheet', sheetNames{j}, 'WriteMode', 'append');
            end
        end
    end
end

For more information on this, you can refer to the following MathWorks documentations:

For “dictionary” function:

https://www.mathworks.com/help/matlab/dictionary.html

For “writecell” function:

https://www.mathworks.com/help/matlab/ref/writecell.html

Hope this helps!