Borrar filtros
Borrar filtros

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

1 visualización (últimos 30 días)
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.

Respuestas (1)

Tushar
Tushar el 12 de Jul. de 2023
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'));
  2 comentarios
mholtz
mholtz el 12 de Jul. de 2023
Hi Tushar,
This worked great! Thank you so much!
Image Analyst
Image Analyst el 13 de Jul. de 2023
Or you could skip the conversion to a table and write the cell array directly to a workbook with writecell

Iniciar sesión para comentar.

Etiquetas

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by