topic Re: How to make a formula that can take in a variable number of input columns? in Discussions

How to make a formula that can take in a variable number of input columns?

shampton82 — Wed, 23 Apr 2025 03:25:17 GMT

Hey everyone,

I have a script I made to center a data column by a grouping column:

Right now it only respects one centered by column but I'd like to be able to put in multiple columns. Any guidance on how to make the formula more dynamic? Currently I just use this to start a new column then go in and manually add the addition columns to center by.

Names Default To Here( 1 );
dt = current data table();


results = Column Dialog(src=ColList("Cols to be centered", Min Col(1)),trg = ColList("centered by", Min Col(1)));

for(i=1, i<= n items(eval(results[1])), i++,
	ts=expr(dt << New Column( "cent col", "Numeric",Formula(Expr(Eval( results[1] )[i]) - Col Mean( Expr(Eval( results[1] )[i]), Expr(Eval( results[2] )[1]) ))));
	eval(eval expr(TS));

	ts=expr(dt:"cent col"n<<set name(char(Eval( results[1] )[i])||" centered by "||char(Eval( results[2] )[1])));
	eval(eval expr(TS));
	
);

Thanks for any input!

Re: How to make a formula that can take in a variable number of input columns?

hogi — Wed, 23 Apr 2025 04:36:43 GMT

"centering columns" - this is needed very often. There are hundreds of combinations how to center (divide of subtract) and how to calculate the "reference":

You can have a look at https://marketplace.jmp.com/appdetails/Normalization+GUI - it adds a GUI to the JMP menu to do the job for you. It also works if rows are excluded.

If you want to set it up on you own, the basic idea is to manipulate the expressions step by step.
e.g. use Insert Into to add a dynamic number of grouping columns:

For Each( {groupByCol, idx}, currentGroupCols,
	Insert Into( secondArgumentDirect, Name Expr( As Column( dt, groupByCol ) ) )
);

Re: How to make a formula that can take in a variable number of input columns?

jthi — Wed, 23 Apr 2025 04:41:31 GMT

Do you mean you wish to add more grouping columns?

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


results = Column Dialog(
	src=ColList("Cols to be centered", Min Col(1)),
	trg = ColList("centered by", Min Col(1))
);

srcs = results["src"];
trgs = results["trg"];

For Each({src}, srcs,
	colname = (src << get name) || " centered by";
	
	mean_expr = Expr(Col Mean());
	Insert Into(mean_expr, NameExpr(src));
	For Each({trg}, trgs,
		colname = colname || " " || (trg << get name);
		Insert Into(mean_expr, NameExpr(trg));
	);

	Eval(Substitute(
		Expr(dt << New Column(colname, "Numeric", "Continuous", Formula(
			_col1_ - _mean_
		));
		),
		Expr(_col1_), Name Expr(src),
		Expr(_mean_), Name Expr(mean_expr),
	));
);

Optionally you could also let JMP do more of this by using New Formula Column and changing names after

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


results = Column Dialog(
	src=ColList("Cols to be centered", Min Col(1)),
	trg = ColList("centered by", Min Col(1))
);

srcs = results["src"];
trgs = results["trg"];

res = dt << New Formula Column(
	Operation(Category("Distributional"), "Center"),
	Columns(srcs),
	Group By(trgs)
);

Re: How to make a formula that can take in a variable number of input columns?

shampton82 — Wed, 23 Apr 2025 14:09:35 GMT

Thanks @hogi . I'll have to get that add in.

Ah "Insert Into", thats looks to be the ticket (as @jthi goes into as well).

Re: How to make a formula that can take in a variable number of input columns?

shampton82 — Wed, 23 Apr 2025 14:12:11 GMT

Hey @jthi ,

Yeah it would be able to add in more grouping columns and the first script is perfect! Thanks for the help!

Re: How to make a formula that can take in a variable number of input columns?

jthi — Wed, 23 Apr 2025 14:39:46 GMT

One option is also using Substitute to replace some expression with Col Mean .

Here is lazy example with your data

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


results = Column Dialog(
	src=ColList("Cols to be centered", Min Col(1)),
	trg = ColList("centered by", Min Col(1))
);

srcs = results["src"];
trgs = results["trg"];


For Each({src}, srcs,
	colname = (src << get name) || " centered by";
	l = Insert(trgs, Name Expr(src), 1);
	f = Substitute(l, Expr(List), Expr(Col Mean()));

	Eval(Substitute(
		Expr(dt << New Column(colname, "Numeric", "Continuous", Formula(
			_col1_ - _mean_
		));
		),
		Expr(_col1_), Name Expr(src),
		Expr(_mean_), Name Expr(f),
	));	
);

Just one note: I would build the dialog using New Window instead of using Column Dialog as it gives much more flexibility even in fairly simple cases.

Re: How to make a formula that can take in a variable number of input columns?

shampton82 — Thu, 24 Apr 2025 04:08:02 GMT

Thanks @jthi for the added options.

Yeah this was an older script where I hadn't switched over to using new window so I've fixed that bad habit now!