topic Re: Adding Formula Columns based on Column Name List in Discussions

Adding Formula Columns based on Column Name List

BatteryTamer359 — Tue, 20 Feb 2024 21:32:49 GMT

Hi there! I'm trying to create a script that will generate Formula columns based on if a Column Name contains the right keyword. I think I'm misusing the way lists translate strings into other functions. Currently using JMP 17

Ptable = Current Data Table ();

col list = Ptable << Get Column Names(String);

if(debug == 1, Print(col list));

For(i=1, i<ncols(Ptable), i++,
	If( Contains( col list[i], "Pressure"),
		New Column("Column", Numeric, "Continuous", Format("Best", 12), Set Selected);
		Column("Column") << Set Formula(
			((15 / (0.016 *  /*###*/Num( :Resistance[1] ) /*###*/)) * Num( Column(col list[i]))
			-3.75) * 6.89476);
		Column("Column") << Set Name(col list[i] + " (kPa)");
	)
);

Re: Adding Formula Columns based on Column Name List

jthi — Tue, 20 Feb 2024 22:08:11 GMT

You have to evaluate column correctly there. Expr(NameExpr(AsColumn())) is one option

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");

mycol1 = "height";
mycol2 = "weight";

Eval(EvalExpr(
	new_col = dt << New Column("Col", numeric, continuous, formula(
		Expr(NameExpr(AsColumn(dt, mycol1))) * Expr(NameExpr(AsColumn(dt, mycol2)))
	))	
));


show(new_col << get formula);

Re: Adding Formula Columns based on Column Name List

txnelson — Tue, 20 Feb 2024 22:23:18 GMT

Here is a modification of your code using the Big Class data table as an example.

Names Default To Here( 1 );

Open( "$SAMPLE_DATA/big class.jmp" );
debug = 1;

Ptable = Current Data Table();

col list = Ptable << Get Column Names( String );

If( debug == 1,
	Print( col list )
);

// Columns are going to be added to the data table.  The For() clause
// will change the end point of the looping if ncols() is in the 
// termination value
ncolumns = N Cols( Ptable );

For( i = 1, i <= ncolumns, i++,
	If( Contains( col list[i], "eight" ), 
	// The formula equation needs to be fully parsed before applying it to
		// the columns definition
		Eval(
			Substitute(
					Expr(
						New Column( col List[i] || " (kPa)",
							Numeric,
							"Continuous",
							Format( "Best", 12 ),
							Set Selected,
							Formula(
								(((15 / (0.016 * Num( :age[1] ))) * Num( _col_ )) - 3.75) * 6.89476
							)
						)
					),
				Expr( _col_ ), parse(col list[i] )
			)
		)
	)
);

Re: Adding Formula Columns based on Column Name List

BatteryTamer359 — Mon, 08 Apr 2024 19:51:00 GMT

Nevermind! Issue was with my array clist not pulling the column names into a list!

@jthi Does naming a column also require an Expression Eval? The page you shared showed a Concat with Char( Array[#] ) working just fine, but I get an error trying to rename columns, code shown below. I'm not adding columns, so I'm not sure why there would be a mismatch between the N Cols and the counter in the For loop.

clist = Ptable << Get Column Names( String );
num_col = N Cols(Ptable);

For(i=3, i < num_col, i++,
	Column(i) << Set Name(Char(clist[i]) || " °C"); //Error on this line
);

"Subscript Range in access or evaluation of 'clist[ /*###*/i]' , clist[/*###*/i]"

Re: Adding Formula Columns based on Column Name List

jthi — Mon, 08 Apr 2024 19:53:32 GMT

Evaluation isn't required when setting the name. You have other issue in your code:

This part of your code doesn't really do anything

clist = {Ptable << Get Column Names( String )};

Remove {} around the << Get Column Names and then it should work

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

clist = dt << Get Column Names(String);
num_col = N Cols(dt);

For(i = 3, i < num_col, i++,
	Column(i) << Set Name(Char(clist[i]) || " °C"); //Error on this line
);