JMP script for average column

[#1]

I've a VERY novice JMP script writer trying to write a command to average a set of columns into a new column with the averages.  This is equivalent to highlighting the columns, then selecting New Formula Column > Combine > Average.  However, I can't find anything in the script library or online that lets me do this.  Anyone have any suggestions?

To add an additional complication, I want the script to take columns 2-11, 12-21, 22-31, etc. and average each column group.  The columns do not have set names, so I can't just put the column names into the script (it's not possible to make the names consistent because the column names are sample names and I need to keep them that way).  The number of column groups in each JMP file is not consistent because the number of samples in each JMP file can vary widely.  Is there a simple way to do this?

I'm using JMP 17.0.0 on a Mac if that's helpful.  Any help is appreciated!

[#2]

Hello @hsjoyner,

Have you investigated the column() function? Instead of using a column name, you can use it's index.  This could be useful in a for() loop.

[#3]

If the number of column is fixed, you can hard-code the means,

if the "etc." is variable, you probably need some Insert Into and Eval(Substitute()):

dt = Open( "$SAMPLE_DATA/Semiconductor Capability.jmp" );
offset = 5;
nc = N Col( dt );
length = 10;
groups = Floor((nc - offset) / length);
For( group = 1, group <= groups, group++,
	myFormula = Expr( Mean() );
	For( i = 1, i <= length, i++,
		Insert Into( myFormula, NameExpr(As Column( offset + (group-1)*length +i ) ))
	);
	myName = "average" || Char( group );
	Eval(Substitute(Expr(new Column(__myName__,Formula(__myFormula__))),Expr(__myFormula__),NameExpr(myFormula),Expr(__myName__),NameExpr(myName) ));
);

[#4]

Here is a script that I hope creates what you are looking for.  It creates new columns that average groups of 10 columns for an undetermined number of columns.

names default to here(1);

// Create an example data table to use for illustration of how to 
// create the sets of average columns
dt  =
// Open Data Table: semiconductor capability.jmp
// → Data Table( "semiconductor capability" )
Open( "$SAMPLE_DATA/semiconductor capability.jmp" );

// Delete the first 3 columns to match the description
// of the suggested data table structure
dt << delete columns({"lot_id","wafer","wafer ID in lot ID"});

// Create new columns that have the groups of 10 columns
startingNCol = N Cols(dt);
start = 2;
groupSize = 10;
while(start+groupSize<=startingNCol,
	eval(substitute(expr(
		dt << New Column("Average " || char( start ) || "-" || char(start + groupSize),
			set each value(Mean(current data table()[row(),index(theStart,theEnd)])));
		),
		expr(theStart), start, expr(theEnd), start+groupSize
	));
	start=start+groupSize;
);

[#5]

Mean(current data table()[row(),index(theStart,theEnd)])

wow, very elegant - much nicer than filling the mean manually with the column references!

[#6]

Why does the formula column fail to produce the same same values as in column Average 2 - 12?

Mean( :NPN1, :PNP1, :PNP2, :NPN2, :PNP3, :IVP1, :PNP4, :NPN3, :IVP2, :NPN4, :SIT1 )

Edit: I was distracted when writing the comment and the above formula produces a matching value.

Follow-up: Mean() seems very sensitive to column Format properties, as I got different results in my own data table when columns were set to Fixed Dec Width 15 Dec 3 versus Best Width 12. Any recommendations on avoiding this issue? 

[#7]

Hi @hsjoyner,

If I'm understanding correctly, and as long as you want the mean for all groups of columns, you should be able to grab a list of the column groups in the table, and then loop through those group names to make formula columns that dynamically pull the columns in each group. Something like this:

dt = Current Data Table();
groupNames = dt << get column groups names;
For Each( {groupName, index}, groupNames, 
	Eval(
		Eval Expr(
			dt << New Column( groupName || " Mean",
				Formula( Mean( Current Data Table() << get column group( Expr( groupName ) ) ) )
			)
		)
	)
);

I hope this helps!

@julian 

edit: after rereading your message and the replies I've realized you are talking about groups of columns (as in, sets of columns) rather than columns that are in actual groups in the table. So, this answer won't be helpful for you, but I'll leave it here in case someone finds themselves in that situation!