How to create an average column with JSL based on random columns

[#1]

I want to make a column list based on some selection criteria. Column names would not be known in advance. Then I use that column list to make a formula column that takes the mean (or sd or number) of those columns.

Simple example script which takes all numeric columns:

dt = Open( "$SAMPLE_DATA/Cars.jmp" );
ColList = {};
For(i=1, i<=NCols(), i++,
	If(Column(i)<<GetDataType=="Numeric", InsertInto(ColList, Column(i)))
);
MeanExpr = NameExpr(Mean());
For(i=1, i<=NItems(ColList), i++,
	InsertInto(MeanExpr, ColList[i])
);
Eval(
	EvalExpr(
		dt << NewColumn("Average of numerics",
			Formula(
				Expr(NameExpr(MeanExpr))
			)
		)
	)
);

MeanExpr2 = NameExpr(Mean());
For(i=1, i<=NItems(ColList), i++,
	InsertInto(MeanExpr2, NameExpr(AsColumn(ColList[i])))
);
Eval(
	EvalExpr(
		dt << NewColumn("Average of numerics 2",
			Formula(
				Expr(NameExpr(MeanExpr2))
			)
		)
	)
);

Notice that ColList is a valid list of columns. When this gets inserted into the first column formula, the preview in the formula editor shows correct evaluation but the column remains empty. Apparently we need to insert scalars, list or matrix into the Mean() function (but the preview will work regardless).

The second formula column adds a AsColumn() around the column reference...which is a bit counter intuitive as it is already a column. But now it creates a list, scalar or matrix and the mean() formula will work.

It feels like i'm making this way to complicated. Is there a simpler way to procedurally generate such a summary column?

[#2]

Usually in simple cases like this I use Substitute to replace list with some other expression

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Cars.jmp");
collist = dt << Get Column Names("Numeric", "String");

colrefs = Transform Each({colname}, collist,
	Name Expr(As Column(dt, colname))
);

mexpr = Substitute(colrefs, Expr(List), Expr(Mean));

Eval(EvalExpr(
	dt << New Column("AVG", Numeric, Continuous, Formula(
		Expr(Name Expr(mexpr))
	));	
));

Write();

[#3]

Thanks but this still is an advanced level solution for a simple problem. Still interested to see if somebody comes up with a simpler way to add column references to a column formula.

[#4]

Assume we have column named "TEST" in table dt. There are differences between the "basic"

• Column(dt, "TEST")
• AsColumn(dt, "TEST")
• "TEST"
• Name Expr(As Column(dt, "TEST"))

View more...

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Cars.jmp");
Row() = 0; // default row value

col = "Size";

Show(col);
Show(Column(dt, col));
Show(AsColumn(dt, col));
Show(Name Expr(AsColumn(dt, col)));
Show(dt:col);
Show(AsName(col));

Write("\!N\!N");

Row() = 1;
Show(Column(dt, col)[Row()]);
Show(AsColumn(dt, col));
Show(Name Expr(AsColumn(dt, col)));
Show(dt:col);
Show(AsName(col));

What type of solution are you looking for? You do not add column references to formulas unless you use [] or [Row()] with the reference. If you are getting your columns as references (or strings), generally you will have to convert them into "column accesses" to use them in formulas. 

[#5 · Accepted solution]

I submitted a bug report to JMP. 

The bug is that the column formula does not accept Column(“name”) arguments, but instead they have to be wrapped in an As Column() expression first. When using the visual formula editor, you do drop columns in the argument fields, so JSL should reflect the same behavior. Adding this to JSL would greatly simplify creation of column formula's from JSL.

Apparently, the preview window in the formula editor does interpret this correctly as it shows a preview.

[#6]

Column() and AsColumn()/:colname are not same and should not be used in similar manner.

In my opinion, the bug here here is with the formula editor and how it shows the formulas when built using JSL. It does miss incorrect formulas (like in this case) and it doesn't show error messages when it should. 

In this case it should be showing this in the column properties

and this in formula editor

And if you try to apply it error like this

If you fix it to be Column()[] it does show correctly and it will return correct results

But it is up to JMP developers to decide how they think this should behave

[#7]

Jarmo makes good points and provided an alternative solution of using Column("name")[].  As mentioned by Jarmo, using Column("name")[] doesn't look as nice in the Formula Editor.  My solution below does not go into that option, but it explains why this is confusing for a lot of users.  It does repeat a lot of what Jarmo has already posted, but since I already had it typed...

Actually what the OP script illustrates is not a bug.  This is expected behavior, and it illustrates a confusion that a lot of users have with referencing columns and how column formula evaluation works.

At the crux of the confusion is understanding the difference between Column("name") syntax and :name syntax.  Both are legitimate ways to refer to columns in some circumstances, but what they return is different.  Specifically, Column("name") returns a reference to a column object while :name returns the value in the name column on the current row.  By the way, :name is the operator form of the function As Column("name").  In other words, those are exactly the same thing.

Consider the following example script to illustrate.  This script assumes you have the Cars.jmp data table open and current in your JMP session.

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

// sending messages to a column; either syntax works
Column(dt, "Year") << Set Property("Range Check", LELE(87, 97));
dt:Head IC << Set Property("Spec Limits", {LSL( 150 ), Show Limits( 1 )});

// Return values

// returns a column object reference shown as Column( "Year" ) in log
Column(dt, "Year"); // returns Column( "Year" )

// returns value in Year column on the current row.  
// Since current row is 0 (the default invalid row number), returns missing (.)
:Year; // returns .

// Set the current row to the first row
Row() = 1;
:Year; // returns 87

With that information in mind, it should be clear that JMP cannot calculate the mean of a set of column references.  This is why the first column formula does not evaluate successfully.  The Mean() function needs values - which are provided in the second column formula.  As a data table evaluates the column formulas, it sets the current row to 1 then evaluates the expression stored in the Formula property.  It places the return value in the column's cell in the current row.  Then it sets the current row to the next value and repeats.  These steps are repeated until the formula is evaluated for the last row.  Consequently, the syntax of the column formula must be one that can evaluate for each row of the data table.

To me, there is one unexpected result from your example script.  Namely that the syntax As Column(Column("name")) evaluates the same as As Column("name").  I expected that it would throw an error, but JMP accepts it and interprets the intent correctly.  That is the (sometimes) advantage of a scripting language over a formal programming language.  However, I would not recommend employing this syntax as it may fail in the future.

I think the best way to accomplish the goal given the example script is to drop the first approach at a column formula and use the second approach.  The additional change is to define ColList to be a list of column names instead of column references.  Here is a modified version of the OP script example that should be robust.

dt = Open( "$SAMPLE_DATA/Cars.jmp" );
ColList = {};
For( i = 1, i <= N Cols(), i++,
	If( Column( i ) << GetDataType == "Numeric",
		Insert Into( ColList, Column( i ) << Get Name )
	)
);
 
MeanExpr2 = Name Expr( Mean() );
For( i = 1, i <= N Items( ColList ), i++,
	Insert Into( MeanExpr2, Name Expr( As Column( ColList[i] ) ) )
);
Eval( Eval Expr( dt << New Column( "Average of numerics 2", Formula( Expr( Name Expr( MeanExpr2 ) ) ) ) ) );

[#8]

While from a theoretical and as-built-mechanics stand point you are probably absolutely right....it is not how the user interface behaves. I select columns from the table and drag those onto the mean() operator and it calculates. However in scripting I cannot select columns, but I have to select string objects (that correspond to column names) and then tell JSL to interpret those as column references, which I can then inject into the mean operator. Wouldn't it be an easy fix to augment the jsl operators to treat Column("Name") as column objects which can be indexed to the row? That would make much more intuitive sense and mirror user interface behavior and therefor user thinking. AS you pointed out, there is a lot of user confusion about this one.

And to Jarmo's points: the error messaging in the formula editor is in need of some clarity and consistency upgrades. Again: do note that the preview shows correct evaluation while the formula does not evaluate when using the Column("Name") syntax.