How to create a table with data from multiple tables using function or for loop

An experiment was run over 42 days, and I have a table for each day containing multiple attempts of the experiment.

I want to create a table with the first column representing the date, and the second column representing the average of the experimental data for that day.

I know I can create this by this very slow means:

Average_06_03 = mean(Data_06_03.SurfaceArea) 
Average_06_04 = mean(Data_06_04.SurfaceArea) 
Average_06_05 = mean(Data_06_05.SurfaceArea) 
...
Average_n = mean(Data_n.SurfaceArea) 

SurfaceAreaRow = [Average_06_03; Average_06_04; Average_06_05; ..; Average_n]
DatesRow = ['06_03'; '06_04'; '06_05'; ..; 'n']

table(DatesRow, SurfaceAreaRow)

I feel like a for loop might be what I need, but I'm not sure how to apply that here. The only examples of for loops I see have an output that just displays values, and I don't know how to format that for make me a list instead. Or am I wrong and I need to create a function?

If I should make a function, how do I create a list of tables (make a matrix) that the function can reference, instead of rewriting the function again and again. It defeats the purpose of trying to make everything more efficient. To expless what I mean:

function [output]= AverageSA (data)
    output = mean(data)
end

Average_06_03 = AverageSA (Data_06_03.SurfaceArea)
Average_06_04 = AverageSA (Data_06_04.SurfaceArea)
Average_06_05 = AverageSA (Data_06_05.SurfaceArea)
...

[Stephen23]

"I feel like a for loop might be what I need, but I'm not sure how to apply that here"

Yes, a loop is a good idea. But unfortunately those variables names are getting in the way:

https://www.mathworks.com/matlabcentral/discussions/tips/849901-tutorial-why-variables-should-not-be-named-dynamically-eval

As soon as you have lots and lots and lots of separate variables in the workspace with meta-data in their variable names, then you have painted yourself into a really ugly corner. A corner that you should not be in. Because then you have forced yourself into writing code that either requires lots of copy-and-pasting (as you show above) or into writing code that requires evil string evaulation. As you point out, neither of these is efficient. Best avoided.

With your example variable names, 06_05, 06_04, 06_05 are meta-data. Do NOT have meta-data in variable names. Use one array with indexing. Strore any meta-data (which is data in its own right) in variables, not in variable names.

"how do I create a list of tables (make a matrix) that the function can reference, instead of rewriting the function again and again."

So far you have not told us the most important information: how did you get all of those structures into the workspace? That is the correct place to fix this data design. For example, if they are LOADed from MAT files then always LOAD into an output variable (which is a scalar structure):

S = load(..);

and then access its fields (e.g. using FIELDNAMES and dynamic fieldnames). Then you can merge all of your data into one array and use indexing. Using loops with indexing is simple and very efficient.

"It defeats the purpose of trying to make everything more efficient"

The data design (with meta-data in the variable names) is the cause, it forces you into writing fragile and less efficient code. If you are lucky you might be able to import that data in a better way (e.g. LOADing into an output variable).

The best solution would be to design that data without any meta-data in the variable names.

[Kristine]

Based on everyone's feedback, I think I have to at the very least change my tables. I will add a datetime column to all my tables. Like this:

But then I'm still left with the question of how do I calculate the average data on each day and turn that into a table without doing this:

SurfaceAreaRow = [Average_06_03; Average_06_04; Average_06_05; ..; Average_n]
DatesRow = ['06_03'; '06_04'; '06_05'; ..; 'n']
table(DatesRow, SurfaceAreaRow)

Do I create a row at the end of each dataset for calculated averages, and then is there a way to make a table with a bunch of indexed rows?

[Walter Roberson]

Concatenate all of the tables together, and then use groupsummary

To answer the question of how to concatenate all of the tables together: store the tables as part of a single variable (such as a cell array) instead of in seperate variables.

[Stephen23]

"But then I'm still left with the question of how do I calculate the average data on each day"

https://www.mathworks.com/help/matlab/ref/double.groupsummary.html

"Do I create a row at the end of each dataset for calculated averages"

No.

Answer 1

[Walter Roberson]

If you create a single table from the data, with one column being the date, and another column being the Surface area (sample number is optional), then you can use groupsummary

G = groupsummary(YourTable, "Date", "mean");
G = G(:,["Date", "mean_SurfaceArea"]);   %remove group count
G.Properties.VariableNames = ["Date", "Avg. SA"];   %rename variables

[Walter Roberson]

There is of course still the question of how you get from a series of tables named by date, to a single table.

The best solution is to not create the series of tables named by date in the first place. Instead, as you loop reading the relevant files, store the tables in a cell array (and the date information in a different cell array.) You can then process the cell arrays, and eventually

UnifiedTable = vertcat(YourCellArray{:});

[Kristine]

Thanks, this very nicely did what I needed.

It look me a while to understand the groupsummary syntax and a whole day to figure out how to add datetime into a complum of my tables via MATLAB. I'm new to coding, and it's been a rough time.

Answer 2

[Paul]

Hi Kristine,

It will be easier to process if the date for each table is actually stored in the data structure itself, rather than in the name of the table itself. I think you're already seeing the complications of using variable names to store data.

Not sure what the best of many possible options would be. One option might be to store the date and associated table as fields in a struct array, like so

D(1).date = 'Data_06_03';
D(1).data = table([1;2;3],[5;4;6],'VariableNames',["Sample","SurfaceArea"]);
D(2).date = 'Data_06_04';
D(2).data = table([1;2;3],[6;7;6],'VariableNames',["Sample","SurfaceArea"]);

Then a loop is simple

T = table('Size',[numel(D),2],'VariableNames',["Date","AvgSurfArea"],'VariableTypes',{'string','double'});
for ii = 1:numel(D)
    T(ii,:) = {D(ii).date,mean(D(ii).data.SurfaceArea)};
end
T

T = 2x2 table
        Date        AvgSurfArea
    ____________    ___________

    "Data_06_03"           5   
    "Data_06_04"      6.3333   

Other options are available depending on how you want to structure your data. If you do decide to store the date information in the data structure, consider using datetime to do so instead of a string variable.

If you can't reorganize the data, then it will be helpful to know how the Date_06_** variables are created in the workspace to begin with. For example if they are loaded from a .mat file, then you can use the functional form of load so that each data table is a different field in struct with the fieldnames being the table variable names. Then you can loop over the fields. But it all really depends on how the variables get into the workspace in the first place.

[Kristine]

Thank you! This helped me learn how to do some new things in MATLAB. I'm very new to coding, and just learning the terminology and syntax has been slow going. I'm still struggling with understanding for loops, so I don't understand how yours work, but I appreciate your input a lot.