topic locate value and replace them in Discussions

locate value and replace them

paul_apicella — Wed, 22 Feb 2017 15:15:42 GMT

Hi everybody,

I have this type of data table (see attached file). I want to detect all values which are outside of 2 threshold values (-0.5 and 0.5) and replace them by the ceiling values. For example: -5.30, -6.00, -11.56 must be replaced by the same value of -5.00, and 5.30, 6.00, and 11.56 replaced by 5.00.

Have you any suggestion to do that in a simple way ?

Thanking you in advance

Re: locate value and replace them

ms — Thu, 23 Feb 2017 08:28:43 GMT

If you think about doing this with JSL there are several ways.

For example, you could simply loop over the columns and conditionally replace cells

dt = Data Table("test.jmp");
For(i = 8, i <= N Col(dt), i++,
    ur = dt << get rows where(Column(dt, i)[] > 0.5);
    lr = dt << get rows where(Column(dt, i)[] < -0.5);
    Column(dt, i)[ur] = 5;
    Column(dt, i)[lr] = -5;
);

An alternative (JMP 13) is to work with data tables as matrices using subscripts. The syntax also supports assignment, i.e. replacement of cell values. Here's an example:

dt = Data Table("test.jmp");
t = 0.5;                  // Treshold value 
c = 5;                    // Ceiling value  
nr = N Row(dt);
nc = N Col(dt);

M = dt[1 :: nr, 8 :: nc];// Matrix with all cells from col 8 to end of table
U = M <= t;              // Below upper limit?
L = M >= -t;             // Above lower limit?
//Assign treshold values to data table
dt[1 :: nr, 8 :: nc] = M :* (U & L) + c * (!U - !L);

Re: locate value and replace them

paul_apicella — Thu, 23 Feb 2017 09:10:10 GMT

Thank you very much MS,

Your first option works very well !