How can I set a loop to enter the average the same row for multiple excel files?
3 views (last 30 days)
Show older comments
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.
0 Comments
Answers (1)
Tushar
on 12 Jul 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 Comments
Image Analyst
on 13 Jul 2023
Or you could skip the conversion to a table and write the cell array directly to a workbook with writecell
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!