nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

shampton82

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!

Accepted answers

jthi

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)
);

All comments

hogi

"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 ) ) )
);

jthi

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)
);

shampton82

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).

shampton82

Hey @jthi ,

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

jthi

One option is also using Substitute to replace some expression with Col Mean Using list of columns in formulas .

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.

shampton82

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!