How can I set a loop to enter the average the same row for multiple excel files?
3 ビュー (過去 30 日間)
古いコメントを表示
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 件のコメント
回答 (1 件)
Tushar
2023 年 7 月 12 日
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 件のコメント
Image Analyst
2023 年 7 月 13 日
Or you could skip the conversion to a table and write the cell array directly to a workbook with writecell
参考
カテゴリ
Help Center および File Exchange で Spreadsheets についてさらに検索
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!