How can I get the average of one column from multiple Excel files and save the data as a new Excel file?

16 次查看（过去 30 天）
Kiwanee Smith
Kiwanee Smith 2020-7-23
评论： Kiwanee Smith 2020-7-29
Good day,
I had been trying to create a code that would look into seven files and average out the first column over the seven files, then compile the results into a new Excel file. The issue I'm having is that I want to show all three columns of data (column 1 is insolation data, column 2 is latitude, and column 3 is longitude) in my results. Also, my results come out as an overall average of all the numbers. Is there a way for me to get the average to work (ie. average the first number (column 1) in each of the seven files, the the second number (column 1) in each file,etc.) so that my result comes out as the same spreadsheet size (the seven files are 1103x3 and I'm tryiny to get my results to be 1103x3)?
This is what I had so far.
>> csvfiles = dir(fullfile(folder, '*.csv'));
>> numfiles = length(csvfiles);
>> average = zeros(1,numfiles);
>>
>> for k = 1:numfiles
M = csvread(fullfile(folder, csvfiles(k).name));
average(k) = mean(M(:,1));
end
>> csvwrite(fullfile(cd, 'Feb 2013 avg.csv'), average);
>> csvwrite(fullfile(folder, 'Feb 2013 avg.csv'), average);

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

采纳的回答

Harsha Priya Daggubati
Hi,
From your question I get that, you want to find the mean of each cell from all the excel files and then populate a new excel file with the averages calculated.
  • It is suggested to use 'readmatrix' instead of 'csvread'.
I can suggest the following:
  1. Loop through all the csv files.
  2. Use 'readmatrix' to read the data from csv, store it in a variable 'M'. Add the read data in each iteration to M.
  3. After the data is read, divide M by number of csv files. This will give the average of each cell.
  4. Then write into a file using 'csvwrite'.
for k = 1:numfiles %1
M = M + readmatrix(fullfile(folder, csvfiles(k).name)); %2
M = M/numfiles; %3
csvwrite(fullfile(cd, 'Feb 2013 avg.csv'), M); %4
end

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by