How do I turn string "C = A*B" to "data.C = data.A.*data.B " while the equation and variables may change?

I'm building a GUI that reads simple formulas from an Excel file, then evaluate them as Matlab code. For example, the user may write "C=A*B" in an Excel cell, then Matlab will import the table and calculate a new variable C with the function eval. The problem is, the variables A and B are actually variables in a table, so the correct code should have been "data.C = data.A.*data.B ". The goal is to let the user create new variables without building a complicated calculator GUI.

The difficult part is, the names (A, B, C) are dynamic and there can be all sort of equations like "A=B+C/D" or "A=B^2-C". The name of the table itself is however fixed. How can I achieve that?

Answer 1

[Voss]

Maybe this is worth a try:

% example usage
fix_formula("C=A*B")

ans = "data.C=data.A.*data.B"

fix_formula("A=B+C/D")

ans = "data.A=data.B+data.C./data.D"

fix_formula("A=B^2-C")

ans = "data.A=data.B.^2-data.C"

% running it on a table
A = [1;2;3];
B = [4;5;6];
C = [7;8;9];
D = [10;11;12];
data = table(A,B,C,D)

data = 3×4 table
    A    B    C    D 
    _    _    _    __

    1    4    7    10
    2    5    8    11
    3    6    9    12

eval(fix_formula("C=A*B"))

data = 3×4 table
    A    B    C     D 
    _    _    __    __

    1    4     4    10
    2    5    10    11
    3    6    18    12

eval(fix_formula("A=B+C/D"))

data = 3×4 table
      A       B    C     D 
    ______    _    __    __

       4.4    4     4    10
    5.9091    5    10    11
       7.5    6    18    12

eval(fix_formula("A=B^2-C"))

data = 3×4 table
    A     B    C     D 
    __    _    __    __

    12    4     4    10
    15    5    10    11
    18    6    18    12

function str = fix_formula(str)
str = regexprep(str,'\<([A-Za-z_]+)\>','data.$1');  % prepend variable names with "data."
str = regexprep(str,'([*/^\\])','.$1');             % replace operators *, /, ^, \ with .*, ./, .^, .\ (element-wise)
end

[Walter Roberson]

There is an obscure additional operator that should really be translated: the \ operator

See also https://www.mathworks.com/help/matlab/ref/vectorize.html (which fails to do \ as well)

[Voss]

Thanks Walter. I have updated my answer.

As it sounds like these expressions will be coming from Excel formulas, I wasn't sure what exactly has to be supported.

[Shao-Yu Wang]

The regexprep function is exactly what I needed. Thanks!

[Voss]

You're welcome!

Answer 2

[dpb]

If the table name is fixed, then it's easy -- use dynamic field names as explained in accessing data in tables -- and specifically using variables for table variable names at <<Access-data-in-a-table>

[Walter Roberson]

I predict that the user is wanting to do the text substitution and then eval()

[Shao-Yu Wang]

Indeed. My problem lies in extracting the variable names from a string, adding the prefix, then piecing them back together

[Steven Lord]

There are a few functions in MATLAB that may be able to help you like symvar, vectorize, and str2func.

s = 'A=B+C/D';
sides = split(s, '=');
lhs = sides{1}

lhs = 'A'

rhs = sides{2}

rhs = 'B+C/D'

listOfVariablesOnRight = symvar(rhs);
str = "@(" + join(listOfVariablesOnRight, ', ') + ") " + vectorize(rhs)

str = "@(B, C, D) B+C./D"

fh = str2func(str)

fh = function_handle with value:
    @(B,C,D)B+C./D

Now call the function handle with the appropriate inputs and store the output in a field of a struct array.

result.(lhs) = fh(1, 2, 3)

result = struct with fields:
    A: 1.6667

check = 1 + (2/3)

check = 1.6667

[Shao-Yu Wang]

Thanks! Symvar seems useful!