JSL code needed to generate dynamic formula column that sums columns from a list

[#1]

Hi All,

   This question is in relation to a post here that nicely generates a non-dynamic column for summing up a list of other columns.

  The problem I am having is how to sum up the columns (using JSL) when you have a list of N other columns and have it be a dynamic formula.

  I have a list of columns in my data table, say ColList, and I want to generate a new column with a formula that simply sums the N items in the ColList. Something I was thinking is like this:

Names Default to Here (1);
dt = Open("$Sample_Data/2D Gaussian Process Example.jmp");
ColList = dt << Get Column Names;
dt  << New Column("Sum", Formula(Sum(ColList)));

  This clearly doesn't work because ColList is a list of columns: ColList = {"col1", "col2", ..., "colN"}. I have tried to make a string and using the Eval(Parse()) and Eval Insert() like such:

Names Default to Here (1);
dt = Current Data Table();

str = Eval Insert("dt  << New Column(\!"Sum\!", Formula(Sum(Eval(ColList))));");
Eval(Parse(str));

  But still no luck. The JSL code will generate the column with a formula, but will have empty row values because it still shows Eval(ColList) in the formula instead of it being the actual formula, which should look like: Sum(:col1, :col2, :col3, ..., :colN).

  I can't figure out what I'm doing wrong in going from the list {""} of columns to the reference to the columns with the colon :.

  Any help is much appreciated.

Thanks!,

DS

[#2 · Accepted solution]

There are several ways to do this. I prefer to create a variable for desired expression using EvalExpr() and then use NameExpr() to transfer the (non-evaluated) expession into the Formula() argument. 

Names Default To Here( 1 );
dt = Open( "$Sample_Data/2D Gaussian Process Example.jmp" );
ColList = dt << Get Column Names;
F = Eval Expr( Sum( Expr( ColList ) ) );
dt << New Column( "Sum", Formula( Name Expr( F ) ) );

[#3]

Hi @ms ,

  That's a perfect solution, thank you! I like how simple and elegant it is.

  I did have to change my code a little bit because doing it your way still has a problem when the list is structured like: {"A", "B", "C", ...}. But, if the list is structured like {A, B, C, ...} then it evaluates the expression just fine. The two lists are generated slightly differently and the quotations really throw the Eval() off.

Thanks!,

DS

[#4]

Hi @ms ,

  I have a related question (hence not another post) that I'm hoping your scripting method can help out with. It has to do with the post here. What I'm trying to do now is basically generalize what was put in the original post from a 4x4 correlation matrix to an arbitrary NxN matrix when "feeding" information to the Simulator in the profile.

  From the other original post, there is script that can generate the NxN correlation matrix, and from your input, can generate a "prediction" column (the sum of the other N columns). Where I'm having a hard time is the following: when calling the Simulator() function, you have the following to pass it:

Simulator(state=0|1, <Factors(column << Random()|Fixed|(constant)|Expression()|Multivariate())>, <Responses(column)<<No Noise | Add Random Noise| Add Random Weighted Noise| Add Multivariate Noise), X Correlations ( state=0|1, {factor1, factor2, ..., factorN}, [NxN correlations]) >)

  So, from the other original post, the solution to pass the correct list of factors ("tpListY") and correlation matrix ("corr.mat"), the JSL code needed to have the Eval Insert() wrapped around the call to send Simulator() to the Profiler report and then run an Eval(Parse()) around that.

  The challenge now is how to correctly generalize this so that when calling Simulator() and defining the Factors, you can do this on that ColList.

ColList might be something like: ColList = {A, B, C, D, ...} and there is an associated mean and standard deviation for each column A, B, C, ....

  What is the right syntax to call the Simulator so that it inserts the right mean and Std Dev for each factor? Example:

sim_design = Profiler(Y(:Pred), Invisible);

new = Eval Insert(
"sim_design << Simulator(
  1, 
  Factors(
    :A << mutlivariate(Mmean[1], MSD[1])),
    :B << mutlivariate(Mmean[2], MSD[2])),
    :C << mutlivariate(Mmean[3], MSD[3])), //etc. for the N columns in the made up table.
  ),
  X Correlations(
    1,
    Eval(ColList),
    Eval(corr_mat)
  ),
  NRuns(1e6)
);");
Eval(Parse(new));

  From the original data table, a second mock up is made with the number of columns (N) that were used to generate the correlation matrix (corr_mat). That mock up table is the one that sums the columns to make the :Pred column which is used as a dummy function to send to the Profiler. The variables Mmean and MSD are vectors that contain the mean and standard deviation of the N columns A through whatever.

  Without having to explicitly write in each column and their corresponding mean, I'd like to have this more automated so that it can handle any number of columns.

  What would be a good way to go about this?

  I'm also having trouble with the Eval(corr_mat) actually entering the values of the correlation matrix in the X Correlations entries.

  The attached data table and script should help to understand what I'm attempting.

Thanks!,

DS

Update, sorry added the wrong file, has correct data table now.

[#5 · Accepted solution]

Here's one approach. First iteratively build the Factors() and then in a second step make the complete Simulator() expression including evaluated arguments for the X Correlations(). 

// Iteratively build the Factors expression
fs = Expr( Factors() );
For( i = 1, i <= N Items( ColList ), i++,
	Insert Into(
		fs,
		Eval Expr( Expr( collist[i] ) << multivariate( Expr( MMean[i] ), Expr( MSD[i] ) ) )
	)
);

// Make the complete Simulator expression
sim = Eval Expr(
	sim_design << Simulator(
		1,
		Expr( Name Expr( fs ) ),
		X Correlations( 1, Expr( ColList ), Expr( corr_mat ) ),
		N Runs( 1e4 )
	)
);

// Name Expr( sim ) // Run this line to show the expression in the log

// Launch Profiler and run the Simulator expression
sim_design = Profiler( Y( :Pred ) );
sim;

However, the X correlations matrix seems (in most cases) to be ignored. Only zeros in the report.

Also when correlations are entered manually in the profiler and saving and running the script, the correlations are lost. Even if they are preserved in the saved script. Not sure why. 

[#6]

Hi @ms ,

  Thank you very much for your solution. It works just fine for my data tables I'm working with. I did need to modify things a little to account for the fact that many of the data tables I work with have other data types, so I only need to consider the numerical continuous data. Anyway, it works correctly and fills in the multivariate means, std devs, and the X Correlations correctly. I'm using JMP 16 EA, if that matters.

  Thanks for your help! I also posted in the other thread of the original request regarding the correlation matrix so that person can hopefully benefit from the code to scale up to N dimensions.

Thanks!,

DS