JMP Scripting- How to change the value of a "cell"

[#1]

Hi,

I am wondering how to script it to change the value of a "cell" that is a result of a calculation in the script.

Specifically, I want to be able to change a value of a cell to "0" if the calculated value is negative while leaving all other "cells" alone.

Even if it needs to be in a new column, that is fine. For example, If i have my column of calculated values and the one I care about is negative, create a new column that would just say "0" for that "cell."

Thanks in advance.

[#2 · Accepted solution]

Here is an example of how to conditionally change a cell when copying to a new column:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
col = dt << New Column( "New Column", Formula( If( :age < 14, 0, :age ) ) );

In your case, you would change the condition to be less than zero rather than 14.

[#3 · Accepted solution]

Rebecca,

I am wondering if what you are asking needs to be explained at a more basic level than how Justin showed.  His example does give you an answer, however, the way you described your problem, I can speculate on scenarios where the components of your conditional statement may be coming from sources other than from the current data table.

One of the basics in a JMP data table, is that any cell is directly addressable.  Using the data table from Justin's answer, any of the cells can be accessed by just specifying the column name and row number.

  :Age[1]

points to the first rows value for the column Age.  A more correct way to reference it, is to use the data table pointer (data table namespace).  So if the pointer to the data table as specified in Justin's response is "dt" then

    dt:Age[1]

is the more proper form.

Taking Justin's example, we could actually change the values in place and not have to create a new column.  The following code will do that

Names Default to Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
For( I = 1, I <= N Rows( dt ), I++,
     if(dt:Age < 14,
        dt:Age = 0
    );
);

[#4 · Accepted solution]

And building on Jim's reply, it's generally considered good practice to avoid loops if possible:

Names Default to Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
// Get the values of age into a vector
ageVals = dt:Age << getValues;
// Find the location of age values less than 14
ageValsLessThan14 = Loc(ageVals < 14);
// Set these values to zero
ageVals[ageValsLessThan14] = 0;
// Put the new values back into the column
dt:Age << setValues(ageVals);

[#5]

I will take this one more step.  Many time I have done similar things that Ian showed.  Taking the data out of the data table and using matrix operators.  This has an advantage of performance on large amounts of data.  However the following is a technique that I have used many, many times, because it is very fast, and it doesn't require extracting from the data table.

Names Default To Here( 1 );

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

Try( dt:Age[dt << get rows where( :Age < 14 )] = 0 );

The "Try" function simply protects against the case where the Get Rows Where could return an empty matrix, and the assignment would throw an error.

[#6]

Ian,

I like the matrix way but how to do if I want to clean out several column for which cleaning condition are difference.

for example in big class

age --> set up all cell with value < 13 as empty

height --> set up all cell with value > 100 as empty

supposing that I have some where a reference table for all column condition.

[#7]

For this scenario, you could use the 'Range Check' column property. That way the table carries the meta data, and you don't need another table:

NamesDefaultToHere(1);

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

Wait(2);
Column(dt, "age") << rangeCheck( !LT( 13 ) );
Column(dt, "weight") << rangeCheck( LT( 100 ) );

[#8]

Hi Jim,

What would be the form of this script if you had the "else" and third alternative as defined in the IF command?

Best,

K

[#9]

Thank you all.

The what I am trying to do is change the value of a cell  of a column that contains a formula.

The Big Class column "Age" is not from a formula so all these suggestions work to change the value of a cell.

I have a column, call it "Test," that uses the match formula. Is there a way to script it to change the value of a cell in that column to zero if the match formula calculates it as negative? I want to be able to force the value to zero if it is negative.

[#10]

Would it make sense to make the test part of the formula?

If(Match(...) < 0,

    0,

    Match(...)

)