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

Question

mholtz 2023-7-12

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1994698-how-can-i-set-a-loop-to-enter-the-average-the-same-row-for-multiple-excel-files

评论： Image Analyst 2023-7-13

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 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Tushar 2023-7-12

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1994698-how-can-i-set-a-loop-to-enter-the-average-the-same-row-for-multiple-excel-files#answer_1271558

在 MATLAB Online 中打开

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'));