topic Re: JSL script to filter out entire rows where a particular column contain missing values in Discussions

JSL script to filter out entire rows where a particular column contain missing values

Michal — Sun, 24 Mar 2024 06:37:17 GMT

Dear Community,

i am a beginner user and would like to request your kind help with my following code. The argument of the "is missing" function seems incorrect.

I d like the script to show a table where rows that contains values in certain ranges are displayed. Also, the rows with empty cells in the two columns used for the filtering are to be excluded.

Here goes the code:

dt = Current Data Table(); // Assumes you have a data table already opened

// Convert Column1 to numeric data type
dt << As Numeric("Temperatur");

// Convert Column2 to numeric data type
dt << As Numeric("Menge");

// Create the conditions to filter the rows
condition1 = Column(dt, "Temperatur") << Get Values() > 25;
condition2 = Column(dt, "Menge") << Get Values() > 0;

// Create a condition to remove empty cells
condition3 = Any(Is Missing(dt << Get Values("Temperatur"))) == 0 | Any(Is Missing(dt << Get Values("Menge"))) == 0;


// Apply all three conditions to filter the data table
filtered_dt = dt << Select Where(condition1 & condition2 & condition3);

// Show the filtered data table
filtered_dt << Show();

Thank you very much upfront for any navigation. I m using JMP version 17

Michal

Re: JSL script to filter out entire rows where a particular column contain missing values

hogi — Sat, 23 Mar 2024 21:36:06 GMT

Hi Michal.

The cool thing in JMP: jou don't have to know any scripting, you can just use the GUI and your mouse.

Then after executing the steps, you can go to the advanced log and check the code ...

Rows/ Row Selection/ Select Where (Ctrl + W)
open data view for selected rows:

- check the log:

Jmp understands what you want - and even combines the two steps to a single line of code:

If you want to construct the command on your own, you can define some strings, concatenate them and then parse and evaluate the final code:

cond1 = "Is Missing( :age )";
cond2 = ":age > 16";

Eval(Parse("dt << Select where( "|| cond1 || " | " || cond2 || " ) << Data View;"))

My suggestion: invest some time to learn and understand Expression Handling
A great tutorial: by @joseph_morgan

You will see, actually it's quite similar to your approach:
store some conditions as JSL "symbols" condition 1, condition 2 ... then use them to construct your final expression and evaluate it.

With some code fixes and Expr() and Name Expr() here and there (to prevent Jmp from evaluating the conditions before they can be plugged into the final line of code) the code should look like this:

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

condition 1 = Expr(Is Missing( :age ));
condition 2 = Expr(:age > 16 );

combined condition=Expr(or());
Insert into(combined condition, Name Expr(condition1));
Insert into(combined condition, Name Expr(condition2));

Eval(Substitute(dt << Select where( _cond_ ) << Data View),
 Expr(_cond_),Name Expr(combined condition)));

Re: JSL script to filter out entire rows where a particular column contain missing values

jthi — Sun, 24 Mar 2024 06:51:07 GMT

You are mixing matrix comparison and selection from data table, in this case you most likely want to just use either << Select Where or << Get Rows Where and not << Get Values (you might want to use << Get Rows Where instead of << Select Where but this depends what you wish to do).

You can combine the conditions directly into a single Select Where statement (if you want to separate them, I would do it with comments unless you wish to reuse exactly the same statements)

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");
dt[[1 2 3 20 30], "height"] = .;
dt[[10 15 16 30], "weight"] = .;


// Comparison
dt << Select Where((:height > 60) & (:weight > 90));
selrows = dt << get selected rows; // to get a matrix of selected row numbers

r = dt << Get Rows Where((:height > 60) & (:weight > 90));

and checking for those missing values is unnecessary in this case

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");
dt[[1 2 3 20 30], "height"] = .;
dt[[10 15 16 30], "weight"] = .;

// Comparison
r1 = dt << Get Rows Where((:height > 60) & (:weight > 90) & (!IsMissing(:height) | !Is Missing(:weight)));
r2 = dt << Get Rows Where((:height > 60) & (:weight > 90));
r1 == r2;

Re: JSL script to filter out entire rows where a particular column contain missing values

Michal — Sun, 24 Mar 2024 13:35:36 GMT

Thank you very much, Hogi, with your help, i made it running!

Re: JSL script to filter out entire rows where a particular column contain missing values

Michal — Sun, 24 Mar 2024 13:36:05 GMT

Thank you very much, Jthi, with your help, i made it running!