topic Re: Inconsistent acceptable syntax in Discussions

Inconsistent acceptable syntax

pcarroll1 — Thu, 14 Nov 2024 16:08:57 GMT

AllCols = dt << Get Column Names();
AllColNames = dt << Get Column Names(string);
for(i=3, i<=n items(AllCols), i++, 
	colcol = AllCols[i];
	cname = AllColNames[i];
	nnm1 = Col Number(colcol);
	Fstr = "num(:\!""||cname||"\!"n)";
	Fexpr = parse(Fstr);
	newcol = dt << new column("Temp", numeric, formula(eval(evalexpr(Fexpr))));
	nnm2 = Col Number(newcol);
	if(nnm2 == nnm1, 
		newcol << delete formula();
		dt << Delete Columns(colcol);
		newcol << set name(cname),  //else
		dt << Delete Columns(newcol);
	);
);

I am using JMP18 on Windows. When I write code that I want to use often, I like to make a toolbar button for it. But sometimes I find that code that is acceptable to run from an open script window is not acceptable when run from a toolbar icon. Here is an example, where I am looping through columns and changing them from character to numeric if all entries are numeric. When running from a toolbar the objection is for the line:

newcol = dt << new column("Temp", numeric, formula(eval(evalexpr(Fexpr))));

This syntax is acceptable when I run from a script window!

Why would the jsl interpreter be different in each situation?

What syntax for this would be universally acceptable?

For very large datasets and columns this can be slow. Is there a better way to do this?

Re: Inconsistent acceptable syntax

Craige_Hales — Thu, 14 Nov 2024 16:39:01 GMT

What error message did you get? I think the script window has a current data table but the toolbar script may not. Maybe just adding

currentDatatable(dt);

at the start of the script would be enough.

You could

print(currentDatatable());

at the start of the script to find out what the toolbar script knows.

Re: Inconsistent acceptable syntax

jthi — Thu, 14 Nov 2024 16:49:08 GMT

Toolbars might be adding Names Default To Here(1) (which I say you should always have) to your script

which can then potentially show some errors which wouldn't be visible if everything is in global scope (but still they are usually errors). I build my formulas using either Eval(EvalExpr()) or Eval(Substitute())

Eval(EvalExpr(
	newcol = dt << New Column("Temp", numeric, formula(Num(Expr(NameExpr(AsColumn(dt, i))))));
));

For large datasets you might have to consider sampling for something like this.There are also non-formula solutions which might be faster but it might also depend on your data.

For some reason I haven't published this to JMP community (or atleast I couldn't find it), Character Columns to Numeric (github.com) , you can take a look at the code to see how I have performed this task from https://github.com/jthi-jmp-tools/character-columns-to-numeric/blob/main/bin/bin/character_columns_to_numeric.jsl. I have since redone this script and if I remember correctly I do use a bit different method nowadays.

Re: Inconsistent acceptable syntax

hogi — Thu, 14 Nov 2024 18:08:53 GMT

toolbar icons know the current data table():
The problem is rather the inverse one.

Please define "dt=current data table()" before the

dt << Get Column Names();

Re: Inconsistent acceptable syntax

pcarroll1 — Thu, 14 Nov 2024 20:38:41 GMT

Thanks Craige.

The error message is:

Name Unresolved: 2PCS_X(um) at row 1 in access or evaluation of '2PCS_X(um)' , "2PCS_X(um)"n /*###*/

"2PCS_X(um)" is the name of the first column to be evaluated.

So the expression for the first column is supposed to be:

newcol = dtTOSA << new column("Temp", numeric, formula(Num( :"2PCS_X(um)"n )));

Re: Inconsistent acceptable syntax

pcarroll1 — Thu, 14 Nov 2024 21:01:40 GMT

Also, in the actual script I don't use dt as a variable. I use a much more descriptive variable, like dtTOSA. (see above) Everytime I want to address a particular data table I always explicitly state it to avoid confusion, as I did in this statement. I don't see how the interpreter could think I'm referring to a different table.

Re: Inconsistent acceptable syntax

Craige_Hales — Thu, 14 Nov 2024 23:35:15 GMT

Maybe, but the error message suggests JMP is not looking in the right place for "2PCS_X(um)"n. I'm not sure what context the toolbar script can use.

Re: Inconsistent acceptable syntax

pcarroll1 — Fri, 15 Nov 2024 15:14:02 GMT

Craige,

I used your suggestion of using a print statement and found that it never got to the line I thought was the problem.

newcol = dt << new column("Temp", numeric, formula(eval(evalexpr(Fexpr))));

The problem was at this line.

nnm1 = Col Number(colcol);

So I changed it to this and that worked.

nnm1 = dtTOSA << Col Number(colcol);

I found that I also needed to remind JMP again what the current table was later in the code and I had to use {} in Delete Columns before it worked. For some reason JMP doesn't care if those curly brackets are there if run from a script window.

The code snippet is now this. By the way this is a snippet of a longer code that also creates this and another table.

AllCols = dtTOSA << Get Column Names();
AllColNames = dtTOSA << Get Column Names(string);
currentDatatable(dtTOSA);
for(i=3, i<=n items(AllCols), i++, 
	colcol = AllCols[i];
	cname = AllColNames[i];
	nnm1 = dtTOSA << Col Number(colcol);
	Fstr = "num(:\!""||cname||"\!"n)";
	Fexpr = parse(Fstr);
	newcol = dtTOSA << new column("Temp", numeric, set formula(eval(evalexpr(Fexpr))));
	nnm2 = dtTOSA << Col Number(newcol);
	if(nnm2 == nnm1, 
		currentDatatable(dtTOSA);
		newcol << delete formula();
		dtTOSA << Delete Columns({colcol});
		wait(0);
		newcol << set name(cname),  //else
		dtTOSA << Delete Columns({newcol});
	);
);

However, now there is a different problem. Even though, I delete the old column before renaming the new column, JMP puts an unwanted 2 after the column name! Again it does not do this running from a script window. I tried including a wait(0) statement but that didn't help.

How can this be fixed?

Re: Inconsistent acceptable syntax

pcarroll1 — Fri, 15 Nov 2024 15:28:07 GMT

Actually, it is not performing the delete column operation.

dtTOSA << Delete Columns({colcol});

So it's keeping the original character column and the new numeric column.

Re: Inconsistent acceptable syntax

pcarroll1 — Fri, 15 Nov 2024 15:29:43 GMT

Strangely, if it goes to the "else" condition that delete statement works fine.

Re: Inconsistent acceptable syntax

pcarroll1 — Fri, 15 Nov 2024 16:22:59 GMT

Although, the column is referred to in 2 different ways (one as the text string name, the other as a column type) this finally worked. I've been writing code in jsl for 18 years and still find referencing columns as variables in jsl to be inconsistent and confusing.

AllCols = dtTOSA << Get Column Names();
AllColNames = dtTOSA << Get Column Names(string);
currentDatatable(dtTOSA);
for(i=3, i<=n items(AllCols), i++, 
	colcol = AllCols[i];
	cname = AllColNames[i];
	nnm1 = dtTOSA << Col Number(colcol);
	Fstr = "num(:\!""||cname||"\!"n)";
	Fexpr = parse(Fstr);
	newcol = dtTOSA << new column("Temp", numeric, set formula(eval(evalexpr(Fexpr))));
	nnm2 = dtTOSA << Col Number(newcol);
	if(nnm2 == nnm1, 
		currentDatatable(dtTOSA);
		newcol << delete formula();
		dtTOSA << Delete Columns(eval(cname));
		currentDatatable(dtTOSA);
		newcol << set name(cname),  //else
		dtTOSA << Delete Columns({newcol});
	);
);

Re: Inconsistent acceptable syntax

jthi — Fri, 15 Nov 2024 17:00:04 GMT

I always use strings as my column base and then build my references from them how ever I need. I have found this to be the most flexible way of handling different types of referencing.

For example, if I take your script and make some assumptions of its purpose (starting from third column go through all of them, create a new formula column in which you change values to numeric, compare missing values and perform actions based on the result) I would write it like this in JMP18:

Names Default To Here(1);

dtTOSA = New Table("Untitled",
	Add Rows(4),
	Compress File When Saved(1),
	New Column("Column 1",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([1, 2, 3, 4])
	),
	New Column("Column 2", Character, "Nominal", Set Values({"A", "1", "2", "3"})),
	New Column("Column 3", Character, "Nominal", Set Values({"1", "2", "3", "4"})),
	New Column("Column 4", Character, "Nominal", Set Values({"A", "B", "C", "D"}))
);
wait(1); // demo purposes

cols = dtTOSA << Get Column Names("String");
// Remove From(cols, 1, 2); // skipping this for demo purposes

For Each({colname}, cols,
	// colname = cols[3]; // for testing
	Eval(EvalExpr(
		newcol = dtTOSA << New Column("Temp", numeric, Formula(
			Num(Expr(Name Expr(As Column(dtTOSA, colname))))
		));
	));
	newcol << Run Formulas;
	newcol << delete formula();
	
	orig_vals = Col Number(Column(dtTOSA, colname));
	new_vals = Col Number(Column(dtTOSA, (newcol << get name)));
	
	If(orig_vals == new_vals,
		dtTOSA << Delete Columns(colname);
		newcol << set name(colname);
	, 
		dtTOSA << Delete Columns(newcol)
	);
);

And if I were to do this from the beginning but still using a bit similar idea as you have, I would (at least at this second) do something like this

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(4),
	Compress File When Saved(1),
	New Column("Column 1",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([1, 2, 3, 4])
	),
	New Column("Column 2", Character, "Nominal", Set Values({"A", "1", "2", "3"})),
	New Column("Column 3", Character, "Nominal", Set Values({"1", "2", "3", "4"})),
	New Column("Column 4", Character, "Nominal", Set Values({"A", "B", "C", "D"}))
);
wait(1); // demo purposes

cols = dt << Get Column Names("Character", "String"); // We only need to check character columns

For Each({colname}, cols,
	dt_summary = dt << Summary(
		Group(Eval(colname)),
		Freq("None"),
		Weight("None"),
		Link to original data table(0),
		output table name("Summar of " || colname),
		Private
	);
	
	orig_vals = Col Number(Column(dt_summary, 1));
	Column(dt_summary, 1) << Set Data Type("Numeric");
	new_vals = Col Number(Column(dt_summary, 1));
	Close(dt_summary, no save);
	
	If(orig_vals == new_vals, // we can change
		Column(dt, colname) << Set Data Type("Numeric");
		Column(dt, colname) << Set Modeling Type("Continuous");
	);
);

I tend to do these utilizing Transform Each and Num, but sometimes it might be enough if you let JMP do the conversions (there are case where you might not want to do this though..., see Num() from scripting index and how it behaves with Num("5%") for example. << Restrict avoids this behaviour)

Re: Inconsistent acceptable syntax

pcarroll1 — Fri, 15 Nov 2024 19:11:45 GMT

Jarmo,

Thanks for the thoughts. I sometimes have thousands of columns, and tens of thousands of rows, and speed can be an issue. The way I do it now actually reduced the time to ~1/3 of the way I did this previously, but that still can be many minutes. I could experiment with your method sometime, but I wonder if you think making a summary table each time would be slower or faster.

Regarding dealing with columns references, I've tried using Column(dt, colname) but it doesn't always work. But in your script you also use As Column(). As a rule, is As Column() used for formulas?

Re: Inconsistent acceptable syntax

hogi — Fri, 15 Nov 2024 19:31:27 GMT

@pcarroll1 wrote:
As a rule, is As Column() used for formulas?

yes, either as column(columnname) or :column(dt, columnname)[]

Re: Inconsistent acceptable syntax

jthi — Fri, 15 Nov 2024 20:18:25 GMT

For first "question": if you try to optimize for speed for specific use cases, you just have to try out which works best as there are many different methods and the fastest might depend a lot on your data. In this case I used summary table (or Summarize) to avoid checking for replicate values but it like I mentioned, depends on your data, on your JMP versions, and possibly on other things...

For column references; my rule is: always use column name and table reference as the starting point, and build the final reference from them. What I consider a reference depends where and how I'm using it. For example if I'm building a formula I want to have my formula to reference columns as :mycol (and not AsColumn(mycol) or Column(mycol)[], which are usually also fine). And I can get the :mycol format using the fairly long style of NameExpr(AsColumn(dt,colname)) with expression evaluation. Generally AsColumn(dt, colname) (OR dt:colname) is the same as Column(dt, colname)[Row()] (or Column(dt, colname)[]), which will get the value of current row (in my mind this isn't always like this. For example in platforms I consider :mycol being a reference instead of a specific value on a row...).

As an example this will return missing value as mycol will be evaluated immediately and the initial Row() value is 0 in JMP (not an issue inside a formula as the Row() will be updated all the time)

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");
colname = "age";

mycol = NameExpr(AsColumn(dt,colname));

show(mycol)

This is the most difficult difference to understand with column references, As Column() (value of the current row) vs Column() (reference to a column) in JMP. It is very difficult to get your head around how these work... I have learned it basically by failing many many times. And now I have a good idea which to use initially when building an expression (I might still be incorrect from time to time). This topic is something I wish to write a blog post at some point as my opinions (currently) might differ from others and I consider myself pretty good JMP scripter without being a JMP (JSL) developer.

One thing to note here: I'm not a JMP developer and I don't really even care how developers think JSL works (I do disagree from time to time with developers). All my answers are always based on my experiences with the JMP versions I have used.

I'm very sorry if this post is bit confusing (I might have had few beers this evening :) ). If you wish to have more explanation on my opinions on this topics, you can post to this topic (but tag me with @ so I will notice it more easily). You can also send me a private message if necessary.