read alot of csv files, average certain rows and columns and output in a new file

13 views (last 30 days)
sara E
sara E on 20 Jan 2022
Commented: sara E on 24 Jan 2022
Hi, I want to read a bunch of different .csv files from a folder, average the values for 100-118 column of each individual file, for rows between 95-103, then rows 416-426, then rows 736-748, and then rows 1057-1059, and then output the averages into a new .csv file as a single coulmn. I would only like to take average of non-zero values. Any help would be highly appreciated.
  1 Comment
Rik
Rik on 20 Jan 2022
Have a read here and here. It will greatly improve your chances of getting an answer.
What did you try so far?

Sign in to comment.

Accepted Answer

_
_ on 20 Jan 2022
Edited: _ on 21 Jan 2022
This will read all the .csv files in a specified directory, do the averaging of non-zero values over those columns and rows, and write the results to another .csv file that you specify (output_file_name).
It wasn't clear to me whether you want all results for all files in one column or the results should be one column per input file, so I've included an option (do_one_column_per_file) to specify that.
This may or may not work correctly, depending on the actual content of your .csv files, but you can try it and maybe adapt it as needed.
% change this to be your directory where the csv files are:
directory = 'C:\Users\sara\csv_files\';
% change this to be the full path of the file where you want the results to go:
output_file_name = 'C:\Users\sara\csv_files\output.csv';
% use this to specify how you want the output formatted:
do_one_column_per_file = true;
cols = 100:118;
rows = {95:103 416:426 736:748 1057:1059};
files = dir(fullfile(directory,'*.csv'));
file_names = fullfile(directory,{files.name});
file_names(strcmp(file_names,output_file_name)) = [];
data_avg = NaN(numel(rows),numel(file_names));
for i = 1:numel(file_names)
[~,~,all_data] = xlsread(file_names{i});
for j = 1:numel(rows)
data = cell2mat(all_data(rows{j},cols));
data(data == 0) = NaN;
data_avg(j,i) = mean(data(:),'omitnan');
end
end
if ~do_one_column_per_file
data_avg = data_avg(:);
end
xlswrite(output_file_name,data_avg);
  4 Comments

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!

Translated by