JSL to generate matching column difference and average

[#1]

Hello,

I am hoping to get some help on the following:

Attached JMP file with the following table.

My typical/starting data set contain:

1. an identifier "Type"

2. a set of unknown number of columns with same prefix and various number suffix, e.g. A num1, A num2, A num3...

3. a second set of the same number of columns as in #2 above, with same prefix and matching suffix as in #2, e.g. B num1, B num2, B num3...

May I get some help to write a jsl script to generate the columns to pair up the set A and set B column to calculate:

   Formula( (:B 1.0 - :B 0.5) / (:A 1.0 - :A 0.5) ) ),

   Formula( (:B 1.3 - :B 0.5) / (:A 1.3 - :A 0.5) ) ),

   Formula( (:B 1.3 - :B 1.0) / (:A 1.3 - :A 1.0) ) ),

Then, generate an Average column ("B/A Avg") to take the mean of all columns created. Noted that the generate columns above may be any number. It could be 3, or 2, or 6...

At the end of the day, I am mostly interested to get the final column of "B/A Avg", the interim columns are optional if that simplify the script.  

Please let me know if I may clarify anything in my questions. Thanks a lot in advance. 

[#2]

What does determine the pairs of columns used for calculation?

[#3]

Having the same suffix (number). For example, 0.5 to 0.5, 1.0 to 1.0, 1.3 to 1.3, etc...

[#4]

If I have columns A0.5, A1.0, A1.3, B0.5, B1.0 and B1.3, how would I know I need exactly (B1.0-B0.5)/(A1.0-A0.5), (B1.3-B0.5)/(A1.3-A0.5) and (B1.3-B1.0)/(A1.3-A1.0) columns?

[#5]

Thank you very much for patiently working with me and apologize for not being 100% clear of the intention. 

For the equation, it will always be: ( B num1 - B num2 ) / ( A num1 - A num2 )

Since we will always get the same set of suffix between the A-group and B-group, my goal is to get all possible combinations among the A-group, which can be limited to only higher value suffix - lower value suffix.

Alternatively, if it simplify the script, we may find the difference between any pair, say including both (B1.0-B0.5)/(A1.0-A0.5) and (B0.5-B1.0)/(A0.5-A1.0). This way, the average will still be the same as higher-lower only. 

[#6 · Accepted solution]

This can also give some ideas

Names Default To Here(1);

dt = Open("$DOWNLOADS/Question - Auto Col Diff and Avg.jmp");
dt << Clear Column Selection << Clear Select;
col_list = dt << Get Column Names("String");

// Get column types
col_rgx_pattern = "^(.?) (\d+.\d+)";
aa = Associative Array();
For Each({col_name}, col_list,
	rgx_result = Regex(col_name, col_rgx_pattern);
	If(!IsMissing(rgx_result),
		parts = Words(rgx_result);
		If(N Items(parts) == 2,
			If(!Contains(aa, parts[1]),
				aa[parts[1]] = {};
			);
			Insert Into(aa[parts[1]], parts[2]);
		);
	);
);

// Check that all different columns have same number parts (ignored in this case)


// Create combinations
ordered_num_parts = Reverse(Sort List(aa[aa << first])); // sort from largest to smallest, works in this simple case
pairs = {};
For(i = 1, i <= N Items(ordered_num_parts) - 1, i++,
	For(j = i + 1, j <= N Items(ordered_num_parts), j++,
		cur_pair = Eval List({ordered_num_parts[i], ordered_num_parts[j]});
		Insert Into(pairs, Eval List({cur_pair}));
	);
);


// Create new columns
avg_expr = Expr(Mean());
For Each({pair}, pairs,
	f_expr = Substitute(
		Expr(
			(_last_numerator_ - _first_numerator_) / (_last_denominator_ - _first_denominator_)
		),
		Expr(_last_numerator_), Name Expr(AsColumn(dt, "B " || pair[1])),
		Expr(_first_numerator_), Name Expr(AsColumn(dt, "B " || pair[2])),
		Expr(_last_denominator_), Name Expr(AsColumn(dt, "A " || pair[1])),
		Expr(_first_denominator_), Name Expr(AsColumn(dt, "A " || pair[2]))
	);
	Eval(EvalExpr(new_col = dt << New Column("B/A " || pair[1] || "-" || pair[2], Numeric, Continuous, Formula(
		Expr(Name Expr(f_expr))
	))));
	Insert Into(avg_expr, Name Expr(AsColumn((new_col << get name))));
);

Eval(EvalExpr(
	dt << New Column("B/A AVG", Numeric, Continuous, Formula(
		Expr(Name Expr(avg_expr))
	));
));

[#7]

Thank you very much Jarmo. Learned so many new ways of scripting from this example. Appreciate your help. 

[#8 · Accepted solution]

Here is my method that creates the new columns you want.

names default to here(1);
dt=current data table();

// Set Prefixes
pre1 = "A";
pre2 = "B";

// Get all levels using Pre1 columns to find the levels
theColumnNames = dt << get column names( string, continuous );
levels = {};
For Each( {col, i}, theColumnNames,
	if(word(1,col," ")==pre1,
		insert into(levels,trim(substr(col,length(pre1)+1)))
	)
);

// Create new columns.  
// This is accomplished by creating a character string that is the JSL statement that
// is required to create the new column and it's formula
meanColsList = {};
For( i = 2, i <= N Items( levels ), i++,
	For( k = 1, k <= i - 1, k++,
		Eval(
			Parse(
				"dt << New Column( \!"" || pre2 || "/" || pre1 || " " || levels[i] || "-" || levels[k] || 
				"\!", formula( (:" || pre2 || " " || levels[i] || " - :" || pre2 || " " || levels[k] || ") 
				/ (:" || pre1 || " " || levels[i] || " - :" || pre1 || " " || levels[k] || ") ) );"
			)
		);
	insert into( meanColsList, ":\!"" || pre2 || "/" || pre1 || " " || levels[i] || "-" || levels[k] || "\!"n");
	);	
);

Eval(
	Parse(
		"dt << New Column( \!"" || pre2 || "/" || pre1 || " Avg\!", formula( Sum(" || 
		Concat Items( meanColsList, "," ) || ") / Number(" ||
		Concat Items( meanColsList, "," ) || ")));"
	)
);

[#9]

Thank you very much, Jim, for your help. Very straight forward and easy to understand. Appreciate it!