How can I set a loop to enter the average the same row for multiple excel files?

I'm very new to MATLAB and have only begun using it for a specific analysis tool. The output includes an excel sheet per sample, and sometimes I have over 50 samples. In order to make the analysis a lot easier, I would like some help setting up a loop that compiles the individual sample data into one file. All the individual excel sheets are named with the sample name and have the same ending ie sample1_features.xlsx

This is how I want the data to look like:

Column 1: name of the sample

Column 2: average of row 3 from that sample's file

Column 3: average of row 8

Column 4: number of columns for the sample (number of recorded timepoints)

Please feel free to ask clarifying questions and thank you in advance for your help.

Answer 1

[Tushar]

Hi mholltz,

I am assuming that you have the output files for each sample and are now trying to combine their data together in a single file. Considering that, I think we can do it like this,

directory = 'path/to/sample/files/'; % please replace this with your path where you have stored the output files


fileList = dir(fullfile(directory, 'sample*_features.xlsx'));% next we can grab a list of all output files

compiledData = cell(numel(fileList), 4); % Initialize a cell array to store the compiled data

for i = 1:numel(fileList)
    
    [~, sampleName, ~] = fileparts(fileList(i).name);
    
    data = readmatrix(fullfile(directory, fileList(i).name));
    
    
    avgRow3 = mean(data(3, :)); % taking average for 3 row all columns
    avgRow8 = mean(data(8, :)); % taking average for 8 row all columns
    numColumns = size(data, 2);
    
    compiledData{i, 1} = sampleName;
    compiledData{i, 2} = avgRow3;
    compiledData{i, 3} = avgRow8;
    compiledData{i, 4} = numColumns;
end

% Convert the compiledData to a table
compiledTable = cell2table(compiledData, 'VariableNames', {'Sample', 'AvgRow3', 'AvgRow8', 'NumColumns'});

% Write the compiled data to an Excel file
writetable(compiledTable, fullfile(directory, 'compiled_data.xlsx'));

[mholtz]

Hi Tushar,

This worked great! Thank you so much!

[Image Analyst]

Or you could skip the conversion to a table and write the cell array directly to a workbook with writecell