How To Extract Data from Multiple CSV Files and Run Analysis?

Hello,

I am working on a project which involves performing calculations from specific collumns and rows in a CSV file. I have multiple CSV files, and would like the MATLAB code to repeat the same analysis for each file, and then output the results from each file's calculations. I started writing a "for loop" to repeat the calculations for each file, but don't know how to reference a specific column/row in the spreadheet. Below is a screenshot of the current code, and attached is a CSV file of one of the datasets.

Any advice would be much appreciated.

Thanks,

Brandon

Answer 1

[Yongjian Feng]

Unlike an excel doc, a CSV file doesn't have concept like H157.

Use readtable to read the CSV file into a matlab table, and go from there.

[Brandon Eberl]

Thank you! Would you be able to provide a brief example of what the code would look like?

Answer 2

[Simon Chan]

Read files and use readtable to retrieve the data into a cell array.

The first row of the csv file contains the headers but readtable would not count the header as row 1. So if you would like to do calculation from row 157 in the csv file, this data is actually has the row index number 156 after retrieved by function readtable.

myFolder = 'C:\Users\Simon';                                    % Working directory
nfiles = dir(fullfile(myFolder,'*.csv'));                       % Read *.csv files
filename = {nfiles.name};                                       % Retrieve the file name
T = cellfun(@readtable,filename,'UniformOutput',false);         % Use readtable to read all the files                                           
%
for k = 1:size(nfiles,1)
    Q = cumtrapz(T{k}.pressure1(156:274),T{k}.time(156:274));   % pressure1 is column2 & time is column1
    R = Q.*T{k}.flow2(156:274);                                 % flow2 is column 5
    result(k) = trapz(T{k}.pressure1(156:274), R);              % Final result
end

Answer 3

[Star Strider]

Try something like this —

T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/693589/data1.csv')

T1 = 10×5 table
    time    pressure1    pressure2    flow1    flow2
    ____    _________    _________    _____    _____

      0         2            3          50       72 
    0.1         4            8          62       81 
    0.2         6           10          74       90 
    0.3         8           15          86       99 
    0.4        10           20          98      108 
    0.5        12           25         110      117 
    0.6        14           30         122      126 
    0.7        16           35         134      135 
    0.8        18           40         146      144 
    0.9        20           45         158      153 

T1.Var6 = cumtrapz(T1{:,2},T1{:,1});
T1.Var7 = T1.Var6.*T1{:,5};
T1.Var8 = cumtrapz(T1{:,2},T1.Var7)

T1 = 10×8 table
    time    pressure1    pressure2    flow1    flow2    Var6     Var7      Var8 
    ____    _________    _________    _____    _____    ____    ______    ______

      0         2            3          50       72       0          0         0
    0.1         4            8          62       81     0.1        8.1       8.1
    0.2         6           10          74       90     0.4         36      52.2
    0.3         8           15          86       99     0.9       89.1     177.3
    0.4        10           20          98      108     1.6      172.8     439.2
    0.5        12           25         110      117     2.5      292.5     904.5
    0.6        14           30         122      126     3.6      453.6    1650.6
    0.7        16           35         134      135     4.9      661.5    2765.7
    0.8        18           40         146      144     6.4      921.6    4348.8
    0.9        20           45         158      153     8.1     1239.3    6509.7

Since table arrays have to have all columns of the same length, ‘Var8’ has to use cumtrapz to create it, so just refer to the last element to get the equivalent trapz result.

.

[Brandon Eberl]

Does this code go in a "for loop"? I don't want to have to manually enter in file names.

[Star Strider]

Yes. You could very easily put it in a for loop.

For example, something like this:

csvfiles = dir('*.csv');
for k = 1:size(csvfiles,1)
    csvnames{k,:} = csvfiles(k).name;
    T1 = readtable(csvnames{k});
    T1.Var6 = cumtrapz(T1{:,2},T1{:,1});
    T1.Var7 = T1.Var6.*T1{:,5};
    T1.Var8 = cumtrapz(T1{:,2},T1.Var7);
    [~,csvfile] = fileparts(csvnames{k});
    writetable(T1, sprintf('%s_new.csv'));
end

This processes each file, appends ‘_new.csv’ to the existing file name, and writes it back with that file name to the original directory that it was read from, so that it does not overwrite the original file. (This is partially tested, since I was experimenting with the files on my own system, and your provided file. You may want to experiment with the dir call so that it only reads the files you want. Right now, it finds all files with the .csv suffix.)

.