Inconsistent acceptable syntaxAllCols = 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?
Automation and ScriptingData Blending and CleanupWindowsRe: Inconsistent acceptable syntaxWhat 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 syntaxToolbars 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 syntaxtoolbar 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 syntaxThanks 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 syntaxAlso, 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 syntaxMaybe, 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 syntaxCraige,
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 syntaxActually, 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 syntaxStrangely, if it goes to the "else" condition that delete statement works fine.
Re: Inconsistent acceptable syntax 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 syntaxI 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 syntaxJarmo,
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
@pcarroll1 wrote:
As a rule, is As Column() used for formulas?
yes, either as column(columnname) or :column(dt, columnname)[]
Re: Inconsistent acceptable syntaxFor 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.
