topic Re: How to apply a formula to all columns in another table? in Discussions

How to apply a formula to all columns in another table?

GroupEdgeColt67 — Fri, 12 Jul 2024 04:02:17 GMT

I am trying to take a table and apply a moving average to every function to every column. The script below is what I am trying, based on what I saw in another thread here. The goal is to make a new table and then apply the function while referencing the original. However, I keep getting a scoped data table access error.

Test = Open("C:\Users\abrokaw\Desktop\JMP MA Test Table.jmp"); //open value data table
Testcols = Test << Get Column Names( string ); //get column names
 
TestMA = New Table( "Test Table Smoothed" ); //make new table
TestMA << add rows(nrows(Test)); //make new table match rows with raw table
 
For( i = 1, i <= N Cols( Test ), i++,
Eval(
Parse(
"TestMA << New Column( \!"MA_" || Testcols[i] || "\!",
numeric,
continuous,
formula( test:" ||
Testcols[i] || "Col Moving Average( :Value, 1, 2, 2 ) ) )'"
)
)
);
TestMA << delete columns("column 1");

Re: How to apply a formula to all columns in another table?

txnelson — Thu, 11 Jul 2024 20:36:43 GMT

You have placed the reference to the column in the Test table in the wrong place. Also, I changed from using a format to using Set Each Value. The end resulting calculation is the same, however the column is not a formula column. This results in having the new data table being independent from the Test table. I also changed the ending value for the For() loop. Since you are including 2 rows both before and after, you can not calculate beyond nrows-2

names default to here(1);
Test = Open( "$SAMPLE_DATA/blood pressure.jmp" ); //open value data table
Testcols = Test << Get Column Names( string, continuous ); //get column names
 
TestMA = New Table( "Test Table Smoothed" ); //make new table
TestMA << add rows( N Rows( Test ) ); //make new table match rows with raw table
 
For( i = 1, i <= N Cols( Test )-2, i++,
	Eval(
		Parse(
			"TestMA << New Column( \!"MA_" || Testcols[i] ||
			"\!",
numeric,
continuous,
set each value( Col Moving Average( test:" || Testcols[i] || ", 1, 2, 2 ) ) )"
		)
	);
);
TestMA << delete columns( "column 1" );

Finally, please use the JSL Icon at the top of the preview screen to enter your code. It makes for much easier reading.

The code actually does not require the complexity of the Eval(Parse()) code. It can just use open code

Names Default To Here( 1 );
Test = Open( "$SAMPLE_DATA/blood pressure.jmp" ); //open value data table
Testcols = Test << Get Column Names( string, continuous ); //get column names
 
TestMA = New Table( "Test Table Smoothed" ); //make new table
TestMA << add rows( N Rows( Test ) ); //make new table match rows with raw table
 
For( i = 1, i <= N Cols( Test ) - 2, i++,
	TestMA << New Column( "MA_" || Testcols[i],
		numeric,
		continuous,
		set each value( Col Moving Average( test:Testcols[i], 1, 2, 2 ) )
	)
);
TestMA << delete columns( "column 1" );

Re: How to apply a formula to all columns in another table?

jthi — Fri, 12 Jul 2024 06:46:55 GMT

You can also use Subset to copy the columns over (this will copy also table scripts and such, which can be a benefit or a problem). Remember to use Copy Formula(0) if you go with the Subset option and use formula columns for calculations

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp"); 
cont_cols = dt << Get Column Names(string, continuous); 

ma_cols = {};
For(i = 1, i <= N Items(cont_cols), i++, // could use For Each but I try to avoid ... Each loops nowadays
	//Eval(EvalExpr()) isn't necessary here but makes debugghing much easier
	new_col = Eval(EvalExpr(
		dt << New Column("MA_" || cont_cols[i], Numeric, Continuous, Formula( // I also try to avoid Set Each Value in cases like this so I use Formula here
			Col Moving Average(Expr(NameExpr(AsColumn(dt, cont_cols[i]))), 1, 2, 2)
		));
	));
	Insert Into(ma_cols, new_col << get name);
);

dt_new = dt << Subset(All Rows, Columns(ma_cols), Copy Formula(0), Output Table("Moving Averages"));
dt << Delete Columns(ma_cols);

Re: How to apply a formula to all columns in another table?

GroupEdgeColt67 — Mon, 15 Jul 2024 16:02:04 GMT

Thanks, Jim. This change works for a test data set that I made. However, I'm still running into an issue with the same error message on my real data set. I think it has something to do with the column names because when I copy the column names over from my real data set to my test set, I get the same error there, as well. Are there certain characters in a column name that could cause this issue?