BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
paul_apicella
Community Trekker

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 ms
Super User

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);
2 REPLIES 2
ms
Super User ms
Super User

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
Community Trekker

Re: locate value and replace them

Thank you very much MS,

 

Your first option works very well !

 

 

PA

0 Kudos