cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
paul_apicella
Level III

locate value and replace them

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

1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

Re: locate value and replace them

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

View solution in original post

2 REPLIES 2
ms
Super User (Alumni) ms
Super User (Alumni)

Re: locate value and replace them

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);
paul_apicella
Level III

Re: locate value and replace them

Thank you very much MS,

 

Your first option works very well !

 

 

PA