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

31 views (last 30 days)
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

Voss
Voss on 20 Jan 2022
Edited: Voss 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!