903135_en-US

liliconcom

robust column reference

After a few minutes coding with JSL, users wonder about strange objects like :sex in 

 

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

x = :sex

 

 

Expert users have some experience with such column references and use them all the time.
But what is :sex ? Is  dt:sex  more robust? Where do I find a documentation ?

Let's collect and share some insights. Please add your thoughts and correct my posts if you find errors ...

my 5 cents:

Automation and ScriptingRe: robust column reference

In JSL, one and the same column reference :sex (or dt:sex) can be used in two ways: 

# 1) as a reference to a column

:sex << get name;
Data Table( "Big Class Families" ) << Distribution(	Nominal Distribution( Column( :sex ) ));

 

 ... and #2
 to retrieve the entry of the current row in this column, like in:

[edit]: no!

"To be used in column formulas - and can usually be thought of as the 'value of the current row'.
But there are many more cases which also require this type of :sex    

</CODE></PRE>
<P>&nbsp;</P>
<P>Please note: in column formulas, the following approaches are equivalent</P>
<PRE><CODE class=" language-jsl">new column("s", formula (:sex));
new column("s", formula (:sex[]));
new column("s", formula (:sex[row()]))
row()=5;
s=:sex;&nbsp;//&nbsp;value&nbsp;of&nbsp;the&nbsp;current&nbsp;row

// other cases:
lag(:sex,1); // value of the previous row
Col Mean(:height, :age));&nbsp;//&nbsp;mean :height&nbsp;of&nbsp;rows&nbsp;with&nbsp;same&nbsp;:age

To create such an object from a string, one has 2 options:

column name = "sex";
col= Column (column name); // for the first one (accessing the column)

val = As Column (column name) // for the second one (accessing the column in formulas)

Actually, there is a very cool 3rd option, which I learned from @jthi :

the universal column reference = Name Expr(As Column(column name))

it can be use like :sex for both approaches. referencing the column and accessing the entry of the current row.

Column() vs As Column() vs datable:column vs dt:As name("column") 

Re: robust column reference

:sex looks fragile - what happens when the user changes the column name?

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

x = Name Expr(As column("name"));
Show(Name Expr(x)); // : name 

:name << set name("The name");

Show(Name Expr(x)); // :The name

wow, great! Not fragile at all!

Re: robust column reference

And when the user changes the data table?

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

x = Name Expr(As column("name"));
Show(Name Expr(x));
x[1] = "Marker";

:name << set name("The name");

dt = Open( "$SAMPLE_DATA/Big Class Families.jmp" );

Show(Name Expr(x));
Show(x[1])

The innocent :sex even knows which data table it actually belongs to. Wonderful!

Re: robust column reference

Now, let's go from 1 column to 2 columns:

myList1 = {:name,:sex,:age};

A very complicated and useless way to create this list is:

myList2 = Eval List({Name Expr(As column(:name)),Name Expr(As column(:sex)),Name Expr(As column(:age))});

 

 

Re: robust column reference

useless?

you already notice the big difference when you try to execute the above code without a 

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

The first line does not care that there is no open data table. When you run the second line, JMP will complain:

Even better: when the list is created with the second approach, the column references are very robust.
Independent of the current data table, JMP will go to dt and and use the columns from there:

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

myList1 = {:name,:sex,:age};

myList2 = Eval List({Name Expr(As column(:name)),Name Expr(As column(:sex)),Name Expr(As column(:age))});

dt2= Open( "$SAMPLE_DATA/Big Class Families.jmp" );

dt << Select Rows( [1] ) << Delete Rows; // now dt starts with Louise - there is no Katie anymore!

Show(Eval List(myList1), // Katie? why Katie!!!
Eval List(myList2)); // Louise&nbsp;->&nbsp;works!

How does it work?

Show(Type(myList1[1]), Type(myList2[1]))

provides more insight:

The simple list (myList1) contains "Names" , *)

the complicated list (myList2) contains "Columns" **)

 

*) When a name is evaluated, JMP follows a specific rule to determine what this name means:
 https://www.jmp.com/support/help/en/19.0/#page/jmp/rules-for-resolving-names.shtml 

Among the roles, JMP will search for a column in the current data table (dt2).

 

**) For a  Column, JMP just has to go to THE data table ("dt") and access the column.
Very robust!

Re: robust column reference

Name Expr(As column(:col))

is soooo cool!

Re: robust column reference

The Column() works in a lot of situations and its pretty flexible with respect to the format of the column name.

 

Distribution(
	Continuous Distribution( Column( :Random Normal )), 
	Continuous Distribution( Column( "Random Normal" )),
	Continuous Distribution( Column( :"Random Normal"n )),
	Continuous Distribution( Column( "Random Normal"n )),
	 Process Capability( 0 ) )

Re: robust column reference

interesting: when asked for lists of columns, JSL functions return lists of names, not lists of  columns!

 

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );


colList = Eval List({Name Expr(As column("sex"))});
write(Name Expr(colList[1]), " is a  ", // :sex
Type(Name Expr(colList[1])), "\!n"); // Column -> universal & robust


selectedCols = dt << select columns (:sex) << get selected columns;
write(Name Expr(selectedCols[1])," is a ", // : sex
Type(Name Expr(selectedCols[1])), "\!n"); // Name -> fragile

dt << Group Columns(Item Range( :sex, :height ),"myColGroup");
colsFromColumnGroup = dt << get column group("myColGroup");
Write(Name Expr(colsFromColumnGroup[1]), " is a  ", // :sex
Type(Name Expr(colsFromColumnGroup[1])), "\!n"); // Name -> fragile

 

The difference between  Get Column Names() and Get Column References():

ColNames = dt << get column names; // default setting: returns names, not Strings 
Write(Name Expr(ColNames[3]), " is a  ", // sex (more or less the same as :sex)
Type(Name Expr(ColNames[3])), "\!n"); // Name -> fragile


ColRefs = dt << get column references;
Write(Name Expr(ColRefs[3]), " is a  ", // Column("sex"
Type(Name Expr(ColRefs[3])), "\!n"); // Column -> robust, but not :sex, see below

 

and now let's play with the column and then ask for the value of  :sex in row 1:

//hide the column
:sex <<  set name("hidden");

// who finds it?
row()=1;
Try(Show(Eval(ColList[1])), print(exception_msg));
Try(Show(Eval(colsFromColumnGroup[1])), print(exception_msg));
Try(Show(Eval(selectedCols[1])), print(exception_msg));
Try(Show(Eval(ColNames[3])), print(exception_msg));
Try(Show(Eval(ColRefs[3])), print(exception_msg));

Re: robust column reference

Take home message:

With the learning from 

myList2 = Eval List({Name Expr(As column(:name)),Name Expr(As column(:sex)),Name Expr(As column(:age))});

we can convert any list provided by JMP into a robust list of columns via

ColNames = dt << get column names;
colList = Transform Each({col}, ColNames, Name Expr(As Column (col)));

 

Re: robust column reference

---

@Byron_JMP wrote:

The Column() works in a lot of situations and its pretty flexible with respect to the format of the column name.

 

Distribution(
	Continuous Distribution( Column( :Random Normal )), 
	Continuous Distribution( Column( "Random Normal" )),
	Continuous Distribution( Column( :"Random Normal"n )),
	Continuous Distribution( Column( "Random Normal"n )),
	 Process Capability( 0 ) )

---

Hi @Byron_JMP , one can have a lot of fun with Column() inside distribution()

 

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

Distribution(Continuous Distribution( Column( :height )) );
Distribution(Continuous Distribution( Column( "height")) );

A Column reference, a name - even a String!   Everything works.

... till you try to use a  column that is defined outside of the Distribution command:

col = Column("height");
Show(col); // shows: it's a column
Distribution(Continuous Distribution(col));
Distribution(Continuous Distribution(Eval(col)));
Distribution(Continuous Distribution(Name Expr(col)));

One can try all such esoteric "Let's just try all of them - one of them should work"-Expression Handling-Tricks, none of them works!

Always the same hint, but what does it mean?!?

 

The solution is very easy - but it can take hours when you search for it at the wrong spot. 
Find the solution on your own before you open  

:mano_con_el_dedo_índice_apuntando_hacia_abajo:

View more...

Column( :height ) inside Continuous Distribution() looks like Column("height") in
col = Column("height");
But it is a named argument - like Formula() in New Column(Formula(...)).

So the "obvious" solution is: keep the name of the named argument and place the column inside:

Distribution(Continuous Distribution(Column(col)));

 

 

Re: robust column reference

my approach is much more simplistic. I'm careful to build my list of columns for the specific purpose I have in mind. Sometimes I have multiple lists of the columns in different formats.

 

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
text = dt << Get Column Names();
quote = dt << Get Column Names( String );
colformat = dt << get column reference();
quote = dt << Get Column Names( String );
show(text, quote, colformat);

which returns:

text = {name, age, sex, height, weight};

quote = {"name", "age", "sex", "height", "weight"};

colformat = {Column("name"), Column("age"), Column("sex"), Column("height"), Column("weight")};

 

one of the three generally works, even if I have to resort to eval(parse())

Re: robust column reference

Basically, I am with you. After taking some precaution, names are not very dangerous:

One can argue if it's clever to rename a column after creating a column list - and then to wonder/complain that the old name doesn't work anymore.

 

And when working with two tables, one has to ensure that the correct data table is the current data table().

 

Same with column names:
One can assume that JMP handles column names correctly -  even with capital letters and spaces. [find the link]
Or one can be cautious and follow some rules. (no collisions of names, variables and column names; caution with space, ...) and most of the day live safe without issues.

Re: robust column reference

Hi @Byron_JMP , to be picky ...

---

get column names() returns a list of  names ; )

Re: robust column reference

Important finding - I just edited one of the first posts!

 

I hope my thoughts are understandable - at least to some users like @Craige_Hales .
I guess, these users will also share my astonishment/enlightenment/joy and/or :help out by confirming that it's like this, provide an explanation, or make corrections.

Up to now I thought about As Column(col) as a way to retrieve the value of the current row - especially compared to Column(col) for "column access"  (you know, like in Column(col) << get name).

With the inverse implication:
Whenever I use :col for anything other than the "value of the current row",then when I want to replace it with a variable, then I don't have to use As column(),  I have to use the other one instead, namely Column(col).

 

[I must admit that I was also puzzled by the name:

As column() - for the function that does the inverse, get not the column, but the value of the current row? you are kidding?

another thought:
If As Column(col) works in such cases, then it's just because JMP corrects the error of the user automatically - or because of an esoteric effect that everybody uses but nobody explains - like in Name expr(As column(col))

So, I feel very comfortable now after finding out:  ....]

 

This interpretation must be wrong. It is triggered by an automatism in JMP which might follow the idea:

As column(col)gives access to the column.
If it it evaluated (Eval(As Column() ...))i, it converts to  column(col)[] aka column(col)[row()] which can be used to access the value of the current row. If it is not evaluated, but used in other functions, it gives access to the column.

 

Why do I think that it's like this?

 

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class Families.jmp" );

row()=10;

Lag(:height);                           // 60
Show(lag(Column ("height"),1));         // Column("height") !!!
Show(lag(As Column ("height"),1));     // 60 !!!

Col Mean(:height);                             // 6255
Col Mean(:height, :age);                       // 60.285...
Show(Col Mean(Column ("height")));             // 62.55 (why does it work? another automatism because it is used so often?)
Try(Show(Col Mean(Column ("height"), :age)),Print(exception_msg)); //  !!!
Show(Col Mean(As Column ("height"), :age));    // 60.285...

[ triggered by https://community.jmp.com/t5/Discussions/Using-numeric-objects-in-a-column-equation/m-p/903939/highlight/true#M106270 ]

Re: robust column reference

Based on this learning, I have a specific question:

 

Is there a comprehensive overview of

 

1. which functions act in the spirit of
   Eval(:col), Where(:col=1) and  Type(:col) ?
   i.e. taking the column :col  and interpreting it as :col[row()] ?
   
   View more...

   rw = Row();
   Where( :age == Column( "age" )[rw] );

2. , and which functions simply treat the column as a column - without "adding [row] for free"?
   like Col median(:col), Lag(:col,1)

 

Which group has the most members?

Re: robust column reference

I don't think Col Mean works in a script very well.

 

However, "60.285714286" is the value of row 10, if the column formula, Col Mean(:height,:age) were applied in a formula column in the data table.

And "62.55" is the value of row 10 if it were in a formula column with Col Mean(;height). 

 

This would be a really great way to obscure looking up values in another table, especially if the table were private.

Re: robust column reference

Sorry, I’m not sure I get the point.

 

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" , Private);

col median(dt:height);

vals= dt:height << get values();
vals=dt[0,"height"];
Median(vals);