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

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.

[Rik]

Have a read here and here. It will greatly improve your chances of getting an answer.

What did you try so far?

Answer 1

[Voss]

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

[sara E]

Hi Thank you so much for the code. However when I m running it, its giving me error "Reference to non-existent field 'folder'." for the line "file_names = fullfile({files.folder},{files.name});". i=Is there anyway to fix this. I really appreciate your help.

[Rik]

What release are you using? This syntax was introduced in R2016b. It might be worth mentioning if you're using a version from 5 years ago...

Since you don't use the recursive search, you can replace the folder part with the variable directory.

file_names = fullfile(directory,{files.name});

[Voss]

I have updated my answer to use the syntax @Rik suggests, for compatibility with versions before R2016b. @sara E Does that work for you now?

[sara E]

Yes, this is great, thank you so much for your help. I really appreciate it.