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

Question

sherwan Najm 2020-9-1

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/587366-aggregate-multiple-csv-files-as-an-average-for-each-cell-individually-and-result-in-one-csv-file

评论： sherwan Najm 2020-9-2

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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Asad (Mehrzad) Khoddam 2020-9-1

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/587366-aggregate-multiple-csv-files-as-an-average-for-each-cell-individually-and-result-in-one-csv-file#answer_488033

在 MATLAB Online 中打开

%
%  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
%

4 个评论
显示 2更早的评论隐藏 2更早的评论

sherwan Najm 2020-9-1

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 2020-9-1

I will answer the second question later

sherwan Najm 2020-9-2

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 2020-9-2

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/587366-aggregate-multiple-csv-files-as-an-average-for-each-cell-individually-and-result-in-one-csv-file#answer_488525

在 MATLAB Online 中打开

%
%  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
%

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

sherwan Najm 2020-9-2

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

请先登录，再进行评论。