381360_en-US

liliconcom

Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

A recent Community Discussion script to find and replace a numeric value with a missing value in the whole tablehad a very nice entry provided by @SDF1 that was selected as the solution

 

names default to here(1);
dt=current data table();
For( i = 1, i <= N Rows( dt ), i++,
 For( l = 1, l <= N Cols( dt ), l++,
  If( Column( l )[i] == 999,
   Column( l )[i] = .
  )
 )
);

 

When I viewed the solution, an old teaching popped into my brain that roughly stated is, "Don't loop through a data table.  Looping is slow. Use the JMP built in capabilities."

 

So I started out to modify @SDF1 code. 

 

I determined that my fallback standard of "Get Rows Where()" would be faster.  After all, it allows all found rows to be set with one assignment statement, after the "very fast" Get Rows Where() has found the rows.

 

Another solution, that I have seen pushed recommended by the community is the Recode facility in JMP.

 

So:

 

Hypothesis: The built in JMP functions of Get Rows Where and Recode will be faster than Looping down and across an entire data table.

 

I selected the Semiconductor Capability data table as a good sized table, 132 columns and 1455 rows

 

I wrote a little script that sets 10 random values in each column to 999.

 

names default to here(1);
dt=current data table();
for(i=5,i<=ncols(dt),i++,
 if(col stddev(ascolumn(dt,i))!=0,
 for(k=1,k<=10,k++,
  column(dt,i)[randominteger(1,1455)]=999;
 )
));

 

Names Default To Here( 1 );
dt = Current Data Table();
start = HP Time();
For( i = 5, i <= N Cols( dt ), i++,
 dt << Recode Column(
  As Column( dt, i ),
  {If( _rcNow == 999, ., _rcNow )},
  Target Column( As Column( dt, i ) )
 )
);
show(hptime()-start)

This is the original value to be changed in the Discussion  mentioned above.  The 999 is to be changed to missing (.) by the scripts.

Method:

1. Startup JMP 16.
2. Read in the Semiconductor Capability data table
3. Run the script to set the 999 values in each column
4. Repeat the above 2 steps for each of the 3 methods and recode the time down to the millisecond.
5. Repeat the above steps for JMP15

Here is the script for the Recode

Names Default To Here( 1 );
dt = Current Data Table();
start = HP Time();
For( i = 5, i <= N Cols( dt ), i++,
 dt << Recode Column(
  As Column( dt, i ),
  {If( _rcNow == 999, ., _rcNow )},
  Target Column( As Column( dt, i ) )
 )
);
show(hptime()-start)

The code for the Get Rows Where

names default to here(1);
dt=current data table();
start=hptime();
for(i=5,i<= ncols(dt),i++,
 column(dt,i)[dt<<GET rows="" where=""></GET>

And here are the surprising results

The For Loop wins hands down.  And from the results, the Get Rows Where should be used sparingly.

Comments and corrections are welcomed......

 

Data Blending and CleanupQuality and Process EngineeringRe: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

---

@jthi wrote:

Went maybe a bit too deep on this (but at least got some kind of an idea how to do this type of testing later if I have to).

 

I wrote a some sort of testing script with different expressions to test different ways of replacing the 999 with missing.
....

---

I just used @jthi 's script:
https://community.jmp.com/t5/Discussions/Should-you-Loop-through-a-data-table-or-use-Recode-or-use-Get/m-p/381766/highlight/true#M63232 
to reproduce the performance of the different approaches on a system with Jmp (16.2 & ) 17.1.

 

I highlighted some approaches (incl. loc) which nowadays have to be inside begin/end data update *)
 to get rid of a severe time increase with increasing percentage fraction of 999s.

 

Differences by factor 2 could be caused by the different computers.
Who has a system with Jmp 15 and 17 - for a direct comparison?

*) although the subset was opened "invisible".

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

@hogi wrote:

...
If a user is aware if the issue, a very cheap solution is to add an Eval()

---

 

cheap regarding complexity of the JSL code vs. time penalty.

But for longer tables Eval(i) loses signifantliy agains Substitute(... ,NameExpr(As Column()))).

So: still quite fast (compared to the original version , but not as fast as I would have expected.

 

Why? What's the fundamental difference?

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

Hm, perhaps it's possible to switch JSL to a user-friendly mode (which pre-evaluates variables if this leads to better performance)?
wish: get rows where: speed issue 

 

If a user is aware if the issue, a very cheap solution is to add an Eval():

start = HP Time();<BR />for(i=5,i<= ncols(dt),i++,<BR />dt << get rows where( As Column( dt, Eval(i) ) == 999 );<BR />);<BR />Show( (HP Time() - start) / 1000000 );start = HP Time();
i = 5;
dt << get rows where( As Column( dt, Eval(i) ) == 999 );
Show( (HP Time() - start) / 1000000 );

 

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

hi @ron_horne , the problem is with the get rows where, so speed should be/ is independent of such settings.

 

On the other hand, it makes a huge difference if the column index is explicit or if it's a variable (even if the variable has an assigned value).

This was not clear to me - how good that I found this conversation!

I don't know how many jsl codes out there could be faster by a factor of ~ N(rows of data table)  ...

Can the issue be fixed  by what goes on in the background of JSL?

Or is there a fundamental something in the JSL architecture which specifies that such a behavior is intended and cannot be changed?

If so: a warning message could help coders not to be trapped by this issue ...

 

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Semiconductor Capability.jmp" );

For( k = 1, k <= 4, k++,
 dt << Concatenate( dt, dt, dt, Append to first table )
);

nuke = Function( {},
 For( k = 1, k <= 10, k++,
  Column( dt, 5 )[Random Integer( 1, 1455 )] = 999
 )
);

//dt << show window(0);
//dt<< Begin Data Update();

nuke();

start = HP Time();
i = 5;
dt << get rows where( As Column( dt, i ) == 999 );
Show( (HP Time() - start) / 1000000 );

nuke();

start = HP Time();
dt << get rows where( As Column( dt, 5 ) == 999 );
Show( (HP Time() - start) / 1000000 );


//dt << End Data Update();

 

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

thank you @txnelson 
could you please estimate the effect of using invisible and private tables on the different methods? they must be the fastest way to amend any script for boosting performance.

 

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

Wow, what a wonderful trick

 

I feared that problems with Missing Value Code will start as soon as you try:

 

Col Maximum(:NPN3)

[As we know that excluded rows have to be excluded in Col aggregation by hand (or better: by GroupBy argument).]

But the surprise: Col aggregations respect Missing Value Codes

 

To be picky: the OP question of the original post was quite implicit:

And from the results, the Get Rows Where should be used sparingly.

Comments and corrections are welcomed......

---

translated - with an uneasy feeling in my belly - as: "why on earth?!?"

 

Thanks to @ErraticAttack for solving the puzzle

 

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

This has been such a fun and informative discussion to follow! Brilliant answers. I am truly not qualified to add anything of value here so I'm going to resort to "cheating."

 

The OP question was about replacing values of 999 in the data with missing because the 999 are missing value codes and we wouldn't want those included in calculations. It's not at all in the spirit of this very interesting line of questioning, but my contribution is that exploiting the missing value code column property is faster than doing any replacement on the data and achieves the same thing for *most* purposes  

 

 

start = HP Time();
For( i = 5, i <= N Cols( dt ), i++,
 Column(dt,i) << Set Property( "Missing Value Codes", {999} )
);
Show( HP Time() - start);

~ 0.007 seconds on my m1 mac.

 

 

 

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

Ah, I didn't know:

:name() acts like As Column()? 

Not so clear from the Scripting index

 

So, with Substitute and As column() instead of Eval(Parse(Eval Insert(JSL Quote(name("^names[i]^")it is:

 

start=hptime();
for(i=5,i<= ncols(dt),i++,
 Eval(Substitute(Expr(column(dt,i)[dt<<GET rows="" where=""></GET>

 

And after this surprising finding I am almost surprised here that the get rows where is also fast if the number comparison is replaced by a character comparison

start=hptime();
for(i=5,i<= ncols(dt),i++,
 Eval(Substitute(Expr(column(dt,i)[dt<<GET rows="" where=""></GET>

 

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

Yes, I understand @brady_brady . One could do this, however:

NamesDefaultToHere(1);

// Make an example table with some rogue '999' values
nr = 10;  // Number of rows
nc = 10;  // Number of columns
p = 0.1;  // Fraction of rogue values
val = 999;  // Rogue value
m = J(nr, nc, RandomInteger(1, 10));
m = Shape(m, nr*nc, 1);
m[RandomIndex(nr*nc, Floor(nr*nc*p))] = val;
m = Shape(m, nr, nc);
dt = AsTable(m);
dt << setName("Rogue Values");
Wait(3);

// Set rogue values to '.'
mask = J(nr*nc, 1, 1);
mask[Loc(dt[0,0] == val)] = .;  
mask = Shape(mask, nr, nc);
dt[0,0] = mask :*</img> dt[0,0];

 But I very much doubt this would be quicker than what you already have, not least because of the element wise multiplication.

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

You've got the comparison a bit wrong here -- the "Get Rows Where" functionality will evaluate it's expression on each row, thus you need to minimize any superfluous lookups before running the loop -- in particular, your iterator is external and it will have to do some scope walking to find it on each row.  Also, it will evaluate the AsColumn function on each row, looking up both 'dt' and 'i' each time.  This is _very_ expensive.  The proper way to to this would be as follows:

 

names default to here(1);
dt=current data table();
start=hptime();
names = dt << Get Column Names( "string" );
for(i=5,i<= ncols(dt),i++,
 Eval( Parse( Eval InserT( JSL Quote(
 column(dt,i)[dt<<GET rows="" where=""></GET>

You will notice that this executes on the order of 3000x faster, which should put it comparable to if not better than the other methods you have

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

Yes... the hurdle is that while matrices can be accessed with a single index (which presumes row-major layout), data tables cannot. Thus, there is sadly no analog to

 

m[loc(m, 9999)] = .

 

where m is a pxq matrix, in the data table world... hence the lines preceding and following this one in the code above. While the code is still fast, it would be REALLY fast if single-index access were available for tables.

 

 

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

For the 'overhead in creating a matrix' I suspect that data table subscripting might help. @Craige_Hales would know.

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

Here is a way to avoid looping, although there is overhead in creating a matrix and assigning back to the table after. This approach performs quickly on this data, about the same as the loc_2d, but as the table becomes huge, I would expect its relative performance to suffer, but I've not verified this.

 

expr_mat = expr(
 m = dt[0, 5 :: N Col( dt )];
 m[Loc( m, 999 )] = .;
 dt[0, 5 :: N Col( dt )] = m;
);

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

Went maybe a bit too deep on this (but at least got some kind of an idea how to do this type of testing later if I have to).

 

I wrote a some sort of testing script with different expressions to test different ways of replacing the 999 with missing. I tested with two datasets (Semiconductor Capability.jmp and Probe.jmp) and changing the amount of missing values in columns.

Loc() is fastest at replacing values (no surprise there). Missing Value Codes() is "always" fast but it has a bit more limited use cases when compared to Loc() and It is a bit more of a hassle to use if there would be multiple values to replace.

 

 

Fastest ended up being loc_2d (didn't bother changing variable names from this: Using Loc With a 2D Matrix ). Still have to loop to get values set (at least to my knowledge), so most likely the reason for this being faster than just Loc() is that it will skip some columns. Below are expr_loc2d and expr_loc:

expr_loc = Expr(
 for(i=5,i<= ncols(testData),i++,
  column(testData,i)[Loc(testData[0,i], 999)]=.;
 );
);

expr_loc_2d = Expr(
 mat = testData[0,5::NCols(testData)]; 
 nr = N Rows(mat);
 nc = N Cols(mat);
 blacklocs = Loc(mat, 999) - 1;
 rowlocs = Floor((blacklocs) / nc ) + 1;
 collocs = Mod((blacklocs), nc ) + 1 + 4;
 uniqCols = Associative Array(collocs) << get keys; 
 For(i_2d = 1, i_2d <= N Items(uniqCols), i_2d++,
  testData[rowlocs[Loc(collocs, uniqCols[i_2d])], uniqCols[i_2d]] = .; 
 );
);

 

 

Also it seems to be important to use AsColumn(Column(dt, i)) like @Ryan_Gilmore said (didn't even bother testing <<  get rows where without it, because it will be very slow), but I did test recode with and without Column(). Also just noticed that I missed one Column() from recode, so run couple of quick tests (not attached in data or script):

Fairly big difference between these three:

 

expr_recode_ascolumn = Expr(
 For(i = 5, i <= N Cols(testData), i++,
  testData << Recode Column(
   As Column(dt, i),
   {If( _rcNow == 999, ., _rcNow)},
   Target Column(As Column(testData, i))
  )
 );
);

expr_recode_ascolumn_column = Expr(
 For(i = 5, i <= N Cols(testData), i++,
  testData << Recode Column(
   As Column(dt, i),
   {If( _rcNow == 999, ., _rcNow)},
   Target Column(As Column(Column(testData, i)))
  )
 );
);<BR />
expr_recode_ascolumn_column_column = Expr(
 For(i = 5, i <= N Cols(testData), i++,
  testData << Recode Column(
   As Column(Column(dt, i)),
   {If( _rcNow == 999, ., _rcNow)},
   Target Column(As Column(Column(testData, i)))
  )
 );
);

 

 

 

The test script and some result data attached. Script will mostly likely have some mistakes here and there. Should be fairly easy to add new expressions if someone wants to do so. One thing I didn't test at all is using << Begin/End data update.

 

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

Interesting discussion! The data table matrix notation, which I think first appeared in JMP 13, is quite efficient for assignment.

For this particular problem it's almost 500 times faster than the for loop (0,003 vs 1.5 seconds, JMP 15 Mac laptop).

 

start = HP Time();
For( i = 5, i <= N Cols( dt ), i++,
 Try( dt[Loc( dt[0, i], 999 ), i] = . )
);
Show( HP Time() - start);

The try() is needed to avoid an error for columns without "999". 

 

 

Looping over columns is still necessary because direct assignment for the whole matrix, which I for a moment thought would be even faster, does not work. Error: "Multiple subscripts require single subscripts at each inner level in access or evaluation...", which on second thought makes sense.

 

// The left Data Table matrix syntax is OK on its own, but not for assignment. 
dt[0, 5 :: N Col( dt )][Loc( dt[0, 5 :: N Col( dt )], 999 )] = .;

 

 

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

I might have been wary of using loops at some point but after taking one algorithm course (which I never did finish..) a little less so. You just have to be efficient with them, quite often you loop way too much (time complexity and  Big O notation). Also using loops is way easier than trying to solve problems with pure mathematics.

 

With JMP I usually just go with the safe options I have learned at some point, as the speed is rarely an issue. But when the time is an issue (one application took over 6 minutes to open at work) I will start looking for ways to make it faster (managed to make it start in less than 2 minutes). In this case I managed to make it faster by using Loc() with different combinations of other functions (delete row, set row state).

In addition to Loc(), using Summarize() seems to be "way" faster when compared to my long favorite option to getting unique values in column (associative array() << get keys). When the row count increases the Associative array() << get keys gets slower and slower and Summarize() doesn't seem to suffer from same issue, at least not as fast. So I have tried to start using the Summarize() more (never know how tall the datatables get).

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

So we skip AsColumn() with (going more with a hunch and I have not tested that this will replace all correct values):

start=hptime();
for(i=5,i<= ncols(dt),i++,
 column(dt,i)[Loc(dt[0,i], 999)]=.;
 if(mod(i,10)==0,show(i));
);
show(hptime()-start);

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

I agree that Loc() is the best method.

 

For me, the slow down seems to occur because of As Column. I see a significant difference between,

start = HP Time();
For( i = 5, i <= N Cols( dt ), i++,
    As Column( column(dt, i) );
);
Show( HP Time() - start );

 

and

 

start = HP Time();
For( i = 5, i <= N Cols( dt ), i++,
    As Column( dt, i );
);
Show( HP Time() - start );

Changing the As Column syntax in the code from @txnelson original question, the For loop is still the fastest but the others are more comparable. 

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

Hi @txnelson ,

 

  Very interesting comparison, and not the results I would have expected, either. I remember when I first started with learning programming in college and was also taught something similar: be wary of loops because they're slow.

 

  For sure, with some calculations and in some programs, I have found For loops to be very slow -- slower than the alternatives. Not being all that great of a programmer, though, I tend to fall back on what I know how to do, which isn't always quick or elegant. Sometimes for me, the logic of a loop allows me to write a functioning code to do what I want it to do, which allows me to move on to the next steps.

 

  I find it interesting that the For Loop and Recode options are slightly slower for JMP 16 versus 15, but the Get Rows Where improved by cutting the time almost in half, that's a substantial change.

 

Thanks!,

DS

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

Maybe @Craige_Hales could have us even faster solution. I have gotten the idea to use Matrix calculations (especially Loc()) from his community posts/blog.

 

I have made one tool at work faster which opens dataset and then deletes old data (based on user selection). Originally it used << Select Where and Delete rows. I replaced it with some Loc() magic and it got also way faster. I have been really liking Loc() a lot lately.

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

@jthi ,

Your matrix based code came in as the fastest.

0.299795 seconds........

Re: Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data table

I have been doing some optimization at work to some old scripts we have. Some of the stuff that that I had to change were related to get rows where. I have started to use more and more matrix operations (mostly Loc()) if I need to have fast JSL code.

 

For example you could change the get rows where to following (didn't test if it replaces correct values or all of them):

start=hptime();
for(i=5,i<= ncols(dt),i++,
 column(dt,i)[Loc(ascolumn(dt,i) << get as matrix, 999)]=.;
 if(mod(i,10)==0,show(i));
);
show(hptime()-start);

 

There is also option of using column properties Value Labels and Missing Value Codes which might work in some cases. I like using them when possible, because then you won't be changing the data.