How to amend the names of all variables in all tables in the current workspace

Dear MATLAB community,

1. I have 300+ tables in my base workspace, and each of them will contain anywhere from approximately 5 to several hundred variables. Many of these variables are identically named. My eventual goal is to generate a gargantuan table in which all 300+ tables have been joined appropriately on a combination of key variables. However, this would mean that many variables will be identically named. I understand from reading MATLAB's documentation on the 'join' function that MATLAB copes with this problem by automatically appending the name of the original tables to the names of the affected variables, during the process of executing the 'join' function - e.g. if two tables named 'table1' and 'table2' both contain a variable named 'var1', in the final joined table, the resulting variables would be 'var1_table1' and 'var1_table2'.

However, I would prefer a different solution. I would like to amend the names of all variables in all tables first, before carrying out the joining. Specifically, I would like to append the name of each table as a prefix to the name of the variables in that table. Using the same example as above, this would generate the variables named 'table1_var1' and 'table2_var1'.

2. I have found a solution that works, but for only one table at a time:

N        =   length(table1.Properties.VariableNames);
varNames = cell(1,N);
 
for i=1:N
    varNames{i} = ['table1_' table1.Properties.VariableNames{i}];
end
 
table1.Properties.VariableNames = varNames;
 
clear N
clear varNames
clear i

Re-creating the above chunk of code for all 300+ tables in the workspace, substituting 'tableX' for 'table1', will lead to 3000+ lines of code, and will be really tedious.. I did consider using 'mail merge' or some similar function to automate the creation, but the end result would still be long and inelegant.

3. My question boils down to this: is there a more elegant solution?

I have tried the following:

workspace  = whos; % save all the variables in the current workspace to a new variable called 'workspace'. 
                   % This 'workspace' is of the data type structure
N          = length(workspace);

for i=1:N
    if workspace(i).class == "table"
        x = workspace(i).name; % I am trying to extract out the name of each table here
        M = length([x '.Properties.VariableNames']); % And then use that name as a dynamic variable name, 
                                                     % as inputs into arguments for example - but it doesn't work. Rather than returning
                                                     % M = <number of variables in the table named 'x'>, it just treats [x '.Properties.VariableNames']
                                                     % as a character instead and tells me how many characters there are in it
        varNames = cell(1,M);
        for j=1:M
            varNames{j} = [x '_' x.Properties.VariableNames{j}];
        end
        x.Properties.VariableNames = varNames;
    end
end

clear x
clear M
clear varNames

I think the key problem here is that whenever I type 'x' in the Command Window, it returns the character 'table1', rather than the table 'table1' itself.

I'm aware that eval(x) will solve it nicely, e.g. eval(x).Properties.VariableNames will indeed return all the names of the variables in the table named 'x'. But I've dutifully read what our experts have said against using eval when it comes to creating dynamic variable names (e.g. https://www.mathworks.com/matlabcentral/answers/304528-tutorial-why-variables-should-not-be-named-dynamically-eval#answer_236126), and am keen to avoid it if at all possible.

I've looked through all the solutions referenced by Mr Stephen Cobeldick in the link above, and unfortunately still couldn't find a way out. In particular, I tried assigning the variable names 'table1', 'table2', ..., 'tableX', to a cell, and then retrieving those table names by indexing into the cell array, but it just returns a character value rather than an actual variable name that would then pull up the desired table. (Admittedly, I'm rather new to MATLAB and thus might not have understood all of the suggested solutions enough to have tested them out properly.)

===

Apologies for the rather long post! Just wanted to explain my problem in enough detail that I hope one of the experts here can show a way out of this.

Thank you very much!

[Stephen23]

"I have 300+ tables in my base workspace..."

You did not tell us the single most important piece of information: how did you get 300+ tables into the workspace?

Did you load them? Or use some third-party tool? Or did you write out lots of code that creates 300+ tables?

No doubt someone can show you a better approach, but currently the one critical piece of information that we actually need to help you is entirely missing from your question.

[Yao Feng Chong]

Thanks a lot for your reply. Sorry that I missed out this information.

I loaded the tables into the workspace by writing out lots of code using the readtable function, e.g.

      table1 = readtable([rootdir 'table1.csv']);
      table2 = readtable([rootdir 'table2.csv']);
      % and so on

This code-writing was automated using Microsoft Excel formulas so wasn't too time-consuming.

Thank you.

[Adam Danz]

Are you sure you want 300 variables instead of 1 cell array that contains 300 tables?

For example,

tableList = ["table1.csv","table2.csv","table3.csv"];
allTables = cell(size(tableList)); 
for i = 1:numel(tableList)
    allTables{i} = readtable(fullfile(rootdir, tableList{i})); 
end

[Stephen23]

"I loaded the tables into the workspace by writing out lots of code using the readtable function, e.g."

table1 = readtable([rootdir 'table1.csv']);
table2 = readtable([rootdir 'table2.csv']);
% and so on

Numbering variable names like that is a sign that you are doing something wrong.

Repeating almost-identical code like that is a sign that you are doing something wrong.

Your difficulties all stem from that unfortunate design decision. You can easily fix the problem where it occurs by loading the tables into one array (e.g. a cell array), just like the MATLAB documentation shows (hint: use sprintf):

https://www.mathworks.com/help/matlab/import_export/process-a-sequence-of-files.html

And then you can trivially access the tables in a loop using basic indexing:

https://www.mathworks.com/help/matlab/matlab_prog/access-data-in-a-cell-array.html

Tip: use fullfile rather than concatenating strings together:

https://www.mathworks.com/help/matlab/ref/fullfile.html

[Yao Feng Chong]

Thank you very much Mr Stephen Cobeldick and Mr Adam Danz for your expert advice.

I did think about creating a cell array containing all 300+ table variables, but ran into problems doing this in a loop as I would still face my original problem of having to retrieve the table variable from the workspace by its name, and then assign that table to a cell in the cell array; the problem arises when trying to create that table's name dynamically inside this loop.

Unfortunately, I can't use Mr Danz's suggestion of using the readtable function iteratively because I have already sunken in many weeks of effort into writing over 7000 lines of code to import each table as an individual variable into the workspace using the readtable function (this is the part that could have been done within a loop, as per Mr Danz's suggestion), followed by a whole bunch of other code to set the import options correctly and clean up the data for each table, which is unique to each table (and hence, because the code is unique for each table, can't be contained within a loop).

I fully take on board Mr Cobeldick's recommendation about the tenets of sound MATLAB programming, but unfortunately I think I have invested too much effort up to this stage to fundamentally redesign the code.

However, I have still found a way to create a cell array to contain all 300+ tables that doesn't involve having to use the eval function. It just involves extracting out the names of the table variables from the workspace, and then manually creating 300+ lines of "allTables{X} = tableX" in Excel, which is admittedly foisting an inelegant solution on top of already inelegant code. But at least I can now clear all the individual table variables, have a much cleaner workspace, and make use of MATLAB's power in processing arrays.

I guess this is a cautionary tale to other readers to design code properly right from the start, to avoid having to use ugly hacks later on!

Thank you very much to you both for offering your advice on this.

P.S. Just to clarify that the table variables' names are very different; I called them 'table1', 'table2', ..., 'tableX' in my example for ease of referring to them.

[Adam Danz]

I've been there. In behavioral economics, this is called the "sunk cost fallacy" or "escalation of commitment". It's when someone faces increasingly negative outcomes from a decision but, due to the sunken investment of time, convinces him/herself to continue the behavior rather than changing course.

During the second year of my post-grad education, I decided to re-write every function and every script I wrote during the previous 2 years because I became better at coding and realized the inefficiency, lack of readability, and the susceptibility to errors of all of my existing code. It took the entire summer but it was well worth the re-investment.

Going back to your solution, grabbing variable names from the workspace is hazardous and often leads to errors that are difficult to trace. Keep in mind that later on in this project you might be faced with additional problems that share the same roots as the problems discussed here. At some point, you might reconsider the gains of replacing those 7000 lines with a dozen or so :) .

[Yao Feng Chong]

Thanks for the words of wisdom! You're absolutely right. At least for now, I don't foresee a need to return to this code once it's produced what I need. But I might be wrong.. if that day comes, I'll heed your advice and just redesign it properly from the ground up.

[Sindar]

If the import procedure for each table truly is unique, you could still transition to cells (or structs) which handle iteration better:

go from

opts_1 = struct(...);
table1 = readtable([rootdir 'table1.csv'],opts_1);
table1 = postprocess(table1);
opts_2 = struct(...);
table2 = readtable([rootdir 'table2.csv']);
table2 = postprocess(table2);

to

opts_1 = struct(...);
all_tables{1} = readtable([rootdir 'table1.csv'],opts_1);
all_tables{1} = postprocess(all_tables{1});
opts_2 = struct(...);
all_tables{2} = readtable([rootdir 'table2.csv']);
all_tables{1} = postprocess(all_tables{1});

still not elegant and tedious to change (find-replace and maybe SHIFT-ENTER variable renaming), but allows the more stable solutions above

[Yao Feng Chong]

Thank you for your comment. I will probably restructure my code along the lines you suggested, if I ever need to rewrite my code.

Answer 1

[J. Alex Lee]

You can still "bandaid" your situation up by reading the named tables in your workspace into the cell array that could have been created in the first place. I guess you cannot avoid the usage of eval, but maybe that sin can be forgiven because you are using it to undo the 300+ sequential naming sins:

for i = numTables:-1:1
    tablename = sprintf("table%d",i);
    AllTables{i} = eval(tablename);
end

Then, I would step back even further and ask why do you want to join/merge the 300+ tables?

If the table name and variable name are semantically important to you, why not index into your variables using dot notation:

AllTables{128}.Var1

This perserves the information the names carried in the first place (Var1 from the 128the table), but without unnecessary burden of renaming Var1 within that table to Table128_Var1.

If your tables are just 1-row tables, maybe the better data type is the nested structure.

Data = struct()
for i = numTables:-1:1
    tablename = sprintf('table%d',i)
    tmptable = eval(tablename);
    varnames = tmptable.Properties.VariableNames;
    Data.(tablename) = struct();
    for j = 1:length(varnames)
        Data.(tablename).(varnames{j}) = tmptable.(varnames{j});
    end
end

And you can index your variables as

Data.table128.var34

And to pontificate further on Adam Danz's wisdom: if the cost is sunk, then the further cost of learning how to do things better and faster is but a drop in the sea, and starts to look like a pretty good investment when you consider that usually your stakeholders care only about how well you have accomplished your task, and not necessarily how hard you worked on it.

Plus, it's easier to learn new methods on familiar problems than have to learn them on new problems. Now that you ahve invested time and effort to really understand this task/problem, why not capitalize further on that by learning better ways to solve it and better prepare yourself for the next challenging task?

[Yao Feng Chong]

Thanks very much for your answer. I agree with the words of wisdom you added on to Danz's!

I've already found a way to bandaid the situation. I currently have 300+ tables within a single cell array, so wouldn't have to utilise the methods you recommended. But I'll bear these methods in mind if I ever need to solve a similar problem, or re-write the code I currently have.

Just to clarify that the variables weren't named sequentially, as clarified in my earlier comment made in reply to Cobeldick's comment.

[J. Alex Lee]

the advantage of using structures and structure references is that it doesn't really matter if you identify your variables by a numeric index or [potentially the more desirable] word identifier (TableA, TableB).

not sure if dynamic referencing of structure elements suffers similar performance issues from "eval"-like "strategies".

i fully endorse all the discussion about learning proper habits for index-able data, but i suppose my main two cents is actually not about how to achieve those, but rather to advise this:

don't lose sight of the actual purpose or objective of a thing as you learn about the details of a particular tactic. your ultimate purpose appeared to be how to correctly and readably reference different values of variables that have been named similarly across different data sets. it's not clear that you really need to store everything in a single workspace, and even if so, that you need to actually rename anything.

I guess an abstract word I can try to reach for regaridng the second point (assessing the need to rename), though not sure if it is strictly appropriate in the computer science sense, is "scoping".

On the point of storage, if you just need a one-time "sanitization" of tables, for example, but otherwise don't mind the "redundant" naming to reference the variables in each table, do you consider re-storing the refactored data into more easily accessible files, such as in a .mat file?

[Yao Feng Chong]

Thanks very much for your advice. I've not used structures much so am not aware of their advantages. I'll consider using them if I ever run into problems with cell arrays that can't be overcome.

After the intractable problem with creating dynamic variable names to call a table within a workplace and assign it to a cell array - the problem that prompted me to post the original question that started this thread - I've not had further similar problems. I've avoided problems of this type by sticking purely to cell arrays: assigning data to them and retrieving them with indexing rather than names.

There were a few key objectives of this whole exercise, one of which was how to uniquely identify variables which had been given identical names in different datasets, as you correctly pointed out. The reason why all the datasets (aka tables) had to be stored within the same workspace and then subsequently joined is because I'm merging individual subjects' data which had been recorded across 300+ tables.

Thanks for your suggestion about storing the sanitised data in a mat file. I'll look into that.

[Stephen23]

'not sure if dynamic referencing of structure elements suffers similar performance issues from "eval"-like "strategies"'

Accessing structure elements or fields can be optimised by the JIT compiler, whereas evaluated strings cannot be.

Structure elements are accessed exactly like any other class array elements: using very efficient indexing.

Using indexing is usually more efficient than generating dynamic fieldnames, because commonly those fieldnames are created by converting data from numeric or concatenating other strings, both of which take time.

[J. Alex Lee]

"The reason why all the datasets (aka tables) had to be stored within the same workspace and then subsequently joined is because I'm merging individual subjects' data which had been recorded across 300+ tables."

I understand the need for having the data all in one place and accessible by some sane and simple "interface". I just challenged the additional and conceptually separate reqirement of merging the tables into a single table, or the requirement of the table data type. It's just a comment in the same vein as other advisers here: solve the actual problem, not the problems you may have created for yourself by making "bad" choices earlier in the process.

@Steven, by your last sentence I take it you just mean that accessing array elements by numeric indexes is faster than accessing accessing structure fields by dynamically resolving the fieldname because of the cost of resolving the fieldname? Otherwise that structure access by dynamic fieldnames is indeed much better than eval-ing?