locate value and replace them

paul_apicella

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

test.jmp

ms

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);