Reading Multiple Excel Files, Analyzing and Indexing

I am trying to make a program to read excel files, calculate averages and STDEV of specific parts of the table. I have the loop for reading the files written here but I don't know how to index my loop so I get an individual matrix of values for each spreadsheet read.

xlFiles = dir('*.csv') ;
N = length(xlFiles) ;
for i = 1:N
    thisFile = xlFiles(i).name ; 
    T = readtable(thisFile) ;
    average = 
     
end

[Mario Malic]

You are onto something here. Check MATLAB Onramp to learn how to extract data from table.

Try this

% for i = 1 % notice the change
%     thisFile = xlFiles(i).name ; 
%     T = readtable(thisFile); % * inspect if your table has been read correctly
%     % you have to extract array from table for which you want to calculate stddev
%     % Function is called std
% 
% end

Example

Age = [38;43;38;40;49];
Smoker = logical([1;0;1;0;1]);
Height = [71;69;64;67;64];
Weight = [176;163;131;133;119];
BloodPressure = [124 93; 109 77; 125 83; 117 75; 122 80];

T = table(Age,Smoker,Height,Weight,BloodPressure)

T = 5x5 table
    Age    Smoker    Height    Weight    BloodPressure
    ___    ______    ______    ______    _____________

    38     true        71       176       124     93  
    43     false       69       163       109     77  
    38     true        64       131       125     83  
    40     false       67       133       117     75  
    49     true        64       119       122     80  

stdDev = std(T.Height(1:3))

stdDev = 3.6056

[Stephen23]

"Reading Multiple Excel Files... "

What "Excel" files? CSV files are pure text files which exist quite independently from MS Excel.

[Correy]

@Stephen23 These are excel spreadsheets that are saved as .CSV by a software package that I used to collect data. Would it technically change the process a lot if I were to actually use .xls/x files?

[Stephen23]

"Would it technically change the process a lot if I were to actually use .xls/x files?"

If the CSV files are well formatted then the only change would be to change the file extension in the DIR call.

Answer 1

[Stephen23]

Use indexing into the structure array that you already have (i.e. the output from DIR):

P = 'absolute or relative path to where the files are saved';
S = dir(fullfile(P,'*.csv'));
for k = 1:numel(S)
    F = fullfile(P,S(k).name);
    T = readtable(F);
    .. your calculations here
    S(k).avg = .. your average
    S(k).std = .. your standard deviation
end

The data will be stored in the structure S and is easy to access using indexing. For example, the 2nd file:

S(2).name % filename
S(2).avg % average
S(2).std % standard deviation

Optionally (assuming compatible sizes and classes, suitable file order) you can easily concatenate the data together, e.g.:

avg_matrix = [S.avg]
std_matrix = [S.std]

https://www.mathworks.com/matlabcentral/discussions/tips/847976-tutorial-comma-separated-lists-and-how-to-use-them

Answer 2

[Peter Perkins]

In a fairly recent (most recent?) version of MATLAB, you can do something like the following. First create some fake CSV files:

t1 = array2table(rand(10,2));
t2 = array2table(rand(10,2));
writetable(t1,"t1.csv");
writetable(t2,"t2.csv");
clear t1 t2

Now read them in and compute means and std devs. You don't give enough information, but perhaps something like this:

xlFiles = dir('*.csv');
emptyTable = table(zeros(0,1),zeros(0,1)); % this should look like whatever the CSV files contain
Tout = table(strings(0,1),emptyTable,emptyTable,VariableNames=["File" "Mean" "Std"]);
for i = 1:length(xlFiles)
    thisFile = xlFiles(i).name; 
    T = readtable(thisFile);
    Tout.File(i) = thisFile;
    Tout.Mean(i,:) = mean(T);
    Tout.Std(i,:) = std(T);
end

Warning: The assignment added rows to the table, but did not assign values to all of the table's existing variables. Those variables are extended with rows containing default values.

Warning: The assignment added rows to the table, but did not assign values to all of the table's existing variables. Those variables are extended with rows containing default values.

Tout

Tout = 2x3 table
      File             Mean                  Std        
    ________    __________________    __________________

                 Var1       Var2       Var1       Var2  
                _______    _______    _______    _______
                                                        
    "t1.csv"    0.38466    0.43901    0.28997    0.32914
    "t2.csv"    0.35267     0.5149    0.23389    0.34535

Notice that I've just called mean and std on the tables. In earlier versions, you could not do that, so you'd need to do something like mean(T{:,:}) and pre-allocate Tout to contain 0x2 numeric matrices, not emptyTable.