Aggregate multiple CSV files as an average for each cell individually and result in one CSV file.

Dear MathWorks forum members

I want to get the average values of multiple CSV files (3,4,5 ..n number of CSV files) for each cell individually in a new CSV file with the same variables' names and structures.

Notes:

- The files are in one folder, for instance, E:\test\

- The number of files is changeable, so, maybe 2 or 3 ... n number of csv files.

The number of columns and rows is also changing but all the files have the same numbers of columns and rows at calculation time, i.e. file test1.csv, test2.csv, and test3.csv, has 15 columns and 7 rows. When the number of columns or rows is changed, all the files will be change for example 13 or 15 columns and 6 or 8 rows.

So, the numbers of column and rows are changable but it is same numbers for each file.

The files are:

The output sould be average of each cell of above pictures in Output.csv file like the followed picture:

The files are attached also:

Thanks in Advance,

Sherwan

Answer 1

[Asad (Mehrzad) Khoddam]

%
%  read folder
%
files = ls ('e:\test\*.csv');
for i = 1: size(files, 1)
    file = files(i,:)
    m = readmatrix(['e:\test\' file]);
    if i==2
        %m
    end
    for r = 1:size(m,1)
        if all(isnan(m(r,:)))
            m= m(1:r-1,2:end);
            break;
        end
    end
   
    for r = 2:size(m,2)
        if all(isnan(m(:,r)))
            m= m(1:end,1:r-1);
            break;
        end
    end
    if all(isnan(m(:,1)))
        m= m(:,2:end);
    end
    %m
    if i==1
        mp = zeros(size(m,1), size(m,2), size(files,1));
    end
    mp(:,:,i) = m;
    
end
%
%  average
%
mean(mp,3)
%
% write the average
%

[sherwan Najm]

Dear Mr. Asad (Mehrzad) Khoddam,

Thank you very much for your answer. I really appreciate your help.

I have implemented your code, and it calculates the average of the values. But if one of the source files have a different size of the name characters, it gave an error related to this line (m = readmatrix(['e:\test\' file]);). For example, if I have two files (test.csv and test1.csv), so the code does not working because the first file has four characters in the name, and the second file has five characters in his name. Because when you reach file number 10, it will be test10.csv which one character is added and so on.

I have added the following lines to the end to create a CSV file for the result:

result = array2table(mean(mp,3))

writetable(result,'e:\test\Output\Output.csv');

and the created output file is like the following picture:

Because I am a noob in MATLAB, besides that, I wrote in my question that the files columns and rows are changeable. So, kindly, I ask you if you can solve the first issue to that the file name does not affect on the code, please. Secondly, I appreciate if you can make a for loop to add the same source variables names in the header of each column to the output file based on the number of the columns, and #0, #1, #2,....n based on the number of the rows in the first column (speed_cluster), and all the other columns should be shifted one column to the right side, please, due to adding speed cluster's column in column (A) .

Thanks again for your prompt response.

Sherwan

[Asad (Mehrzad) Khoddam]

Hello,

You can use

file = strtrim(files(i,:))

in line 6 instead of the existing code.

Good luck

[Asad (Mehrzad) Khoddam]

I will answer the second question later

[sherwan Najm]

Dear Mr. Asad (Mehrzad) Khoddam,

Thanks for the addition syntax, it solved the first issue. I am looking forward to the second question answer, please.

Respectfully Yours

Answer 2

[Asad (Mehrzad) Khoddam]

%
%  read folder
%
files = ls ('d:\test\*.csv');
for i = 1: size(files, 1)
    file = strtrim(files(i,:));
    m = readmatrix(['d:\test\' file]);
    
    for r = 1:size(m,1)
        if all(isnan(m(r,:)))
            m= m(1:r-1,2:end);
            break;
        end
    end
   
    for r = 2:size(m,2)
        if all(isnan(m(:,r)))
            m= m(1:end,1:r-1);
            break;
        end
    end
    if all(isnan(m(:,1)))
        m= m(:,2:end);
    end
    %m
    if i==1
        mp = zeros(size(m,1), size(m,2), size(files,1));
        tb = readtable(['d:\test\' file]);
    end
    mp(:,:,i) = m;
    
end
tb(:,2:end) = num2cell(mean(mp,3));
%
% write table, using tblwrite
%

[sherwan Najm]

Dear Mr. Asad (Mehrzad) Khoddam,

Wow, very big thanks to you, I appreciate your help and I am grateful for your support. The code is more than excellent for me. thank you so very much.

Respectfully Yours

Sherwan