910626_en-US

liliconcom

Python in JMP: dataframe column to datatable column best practices and computation speed implications

Background

* Using Python in JMP19 scripting environment.

* Creating dataframe (df) from pandas from existing JMP datatable (dt). 

* Running function that requires df input and returns a new df result.  Lets call it df_results.

* Transferring results back to JMP datatable column.

This works, but there are two things that make me suspect I'm not doing this properly.  For one, I get a warning in the local log output.  And two, the first time I run this on a very large number of rows it takes a long time.  My code timer said 25 seconds for 100k rows.  If I rerun it, the second time it takes 3 seconds.  I set up the timer to give me details on each step so I could find out if one line of code was taking most of the time.

This is the single line of code I'm questioning.  Took 25 seconds for 100k rows ONLY the first time the initially constant (or null - tried both) column was filled out.  Then almost 10x faster every subsequent rerun after that.

dt[ColumnName] = df_results[ColumnName]

This is the warning message in the embedded log

FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]`

Also, I did this for 4 different columns total from 2 different dataframes.  They all exhibited the same behavior (and warning).  Slow first run.  Fast every repeat.  Negligible difference between Column 1 , 2, 3 or 4.

This is only a subset of a large table with 1.3 million rows.  So, 25 seconds x 4 columns will become 250+ seconds x 4 columns if I can't figure out how to get the faster speeds on the first try.

Thanks

 

Automation and ScriptingRe: Python in JMP: dataframe column to datatable column best practices and computation speed implications

In JMP 18 it was necessary to build up a data table column by column from a data frame.  JMP 19 supports the portable data frame protocol, so it is 1 line of code to go from a dt -> df, and 1 line of code to go df -> dt in memory.

Also understand that the very first time a Python script is run in JMP, Python itself needs to be initialized.  First time you import a package it needs to be loaded, possibly including shared libraries or byte code compilation of the .py files.

The warning is coming from the pandas package.  To clear the warning you need to do their suggested ser.iloc[pos].  The JMP data table dt can be indexed by 0-based column index or by column name.  Index would be faster since it would avoid string comparisons.

Help -> Scripting Index   'Python'  category,   jmp.from_dataframe.  'JMP to Pandas' example.

import jmp
import jmputils

try:
    if not jmputils.is_installed('pandas'):
        jmputils.jpip('install', 'pandas', echo=False)
except Exception as e:
    print(f'Install failed with exception: {e}')

import pandas as pd

dt = jmp.open(jmp.SAMPLE_DATA + "Big Class.jmp")
pandas_df = (pd.api.interchange.from_dataframe(dt))
print(pandas_df)

...
# new dt_result data table from dataframe df_result
dt_result = jmp.from_dataframe( df_result ) 

# or continue to do the loop on columns if you need to update the original data table from the resulting dataframe.

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

And what is the fastest way to fill a single JMP column with new values from Python?

e.g. for a data table with 100k rows?

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

Thanks Paul, I will implement the jmp.from_dataframe to get back to datatables to avoid the error.

The other part of my question was about why there was such a huge speed difference, and in order to answer that I had to ensure I could reliably cause the SLOW calculation response.  At first I couldn't.  All runs, even the first after opening JMP and loading Python were plenty fast.  Then I realized there may be one critical difference.  If I ADD a new column (manually, hadn't put it in code yet) and run the code it will ONLY be slow updating that new column and ONLY for the first time. 

As a test I updated 4 columns, with 3 being pre-existing and the 4th being newly created.  It took 1 sec to update 100k rows of Col 1, another second to update Col 2, another to update Col 3, and then 19 seconds to update Col 4.

I made another table and another 'new' 4th column.  If I SAVE AND CLOSE the datatable with the new column,, then REOPEN, then run the code... the new 4th column updates in ~ 1 second, just as fast as ##1, 2 and 3  Critically, if I only save, but do NOT CLOSE & REOPEN the datatable with the new 4th column, then run the code, it will still take the longer ~ 19 seconds to update column 4.

This tells me the speed has to do with some memory management quirk of JMP.

I don't know if this is a problem that can/must be solved, and maybe it still is only a problem if I don't use the 'from_dataframe' step.  Just very odd behavior that I'm glad I was able to recreate.  Now I can try the better version of the code and see if the issue persists.

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

One of my colleagues pointed out in JSL there is the Start Data Update and End Data Update, so....

jmp.run_jsl( 'dt << Begin Data Update;' )

# your python code filling new column
...

jmp.run_jsl( 'dt << End Data Update;' )

Most likely we will want to do support this from Python. Probably using a with block to suspend the UI updates of the table when filling / updating large number of rows.

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

It would be setting the entire column using the dt[index] wrapped with jsl Begin Data Update

jmp.run_jsl( 'dt << Begin Data Update;' )
dt[ index ] = list_like_object       # numpy array, list, dataframe column,...
jmp.run_jsl( 'dt << End Data Update;' )

Or by creating the data table invisible until you have filled the table, then make it visible.  

import jmp
dt = jmp.DataTable('Powered By Python', 40, visibility='Invisible')

...
# fill table
...

jmp.globals['jdt'] = dt     # set jdt in JSL to reference dt
jmp.run_jsl('::jdt << Show Window(1);')

The optional visibility parameter may be JMP 19 and above.  The jmp.globals[ ] is a 19 feature. 

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

The jmp.run_jsl( 'dt << Begin Data Update;' )  is assuming dt is the JSL variable for the table in JSL and then on the Python side as well. Such as caused by a Python Send(dt); from JSL, but not necessarily the same if the table was created from Python. 

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

Many thanks for the explanation. 

So, the code:

dt[ index ] = list_like_object 

in Python fills the column step by step? Which is causing the speed issue?


It looks similar to 

dt[ 0, columnname] = [values ...]

in JSL, which fills the column in one step - and doesn't need to be wrapped by dt << Begin/end Data Update

 

Good to know. Where can I find further documentation about the details?

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

I don't know how many users know how essential 

dt << Begin Data Update;

is for writing good code. But I am sure that many users don't know.

 

Related  wish from @bswedlove  for for each row():

for each row(data update=1, ...)

Great idea to institutionalize a solution - to make the issue disappear for the user (the solution is always there).
Holds for JSL and python.

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

"The JMP data table dt can be indexed by 0-based column index or by column name.  Index would be faster since it would avoid string comparisons."

Trying to rewrite code using indices but can't seem to find the equivalent syntax for determining datatable column index from a name that I have for dataframes.

dfColumnIndex = df.get_loc['NameofDataFrameColumn']

dtColumnIndex = ???

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

@jthi , what was the name for "adding some code to fix an issue that is not existing"?

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

In the case where df was created from a dt, the assumption would be that they have the same index.  If you've added columns to the data frame or one was not the origin of the other then using the dt['col_name'] =    is probably your only recourse.  It shouldn't be that costly compared to an index but it is a string compare vs direct numeric indexing.

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

This all happens within the magic of Python's Protocol Buffers, where the data is mapped in memory between the supported blocks of memory.  It might not need bracketing with Begin | End Data Update, but if it can't map the entire column at once, it will fallback to iterating across the values so bracketing with the Data Update statements is a good safety net.

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

I ran numerous experiments and different methods.  Some threw warnings.  Some did not.  The simplest seemed to be to create a dt from the df and then use the dt to transfer data.  That never threw an error.

However, as to the speed problem... the ONLY thing that made a difference was whether I was trying to write values to a column that had been newly created, or if I was trying to write values to a column that existed when the datatable was opened (or created).  Newly created columns updated extremely slow.  Preexisting columns updated plenty fast.

Results of last text on 100k rows.  4 parameters already existed and 5 new columns added before I ran this code.  9 total columns updated.

#These 'xfers' all used dt['colname'] = dt_results['colname'] syntax. No DataFrames involved after DF was used to create dt_results.

Step df_results creation time: 0.3434 seconds
Step Param 1 xfer time: 0.1795 seconds
Step Param 2 xfer time: 0.1758 seconds
Step df_results creation time: 0.0231 seconds #This is a new set of result of different df function return.
Step Param 3 xfer time: 0.1838 seconds
Step Param 4 xfer time: 0.1890 seconds

#Code here was: dt['NewCol1Test'] = df_results[stringnameofcolumn]

FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]`
df to dt with names xfer time: 15.8251 seconds

#Code here was: dt['NewCol2Test'] = df_results.iloc[:,dfColumnIndex]

FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]`
#I don't know why that warning was still thrown when I used iloc

df to dt with iloc xfer time: 16.0874 seconds

#Code here was: dt['NewCol3Test'] = dt_results[dfColumnIndex]

dt index to dt name xfer time: 14.5659 seconds

#Code here was: dt[NewColumnIndex] = dt_results[dfColumnIndex]

dt index to dt index xfer time: 14.4699 seconds

 

#Code here was:

jmp.run_jsl('dt << Begin Data Update;')
dt['NewCol5Test'] = dt_results[dfColumnIndex]
jmp.run_jsl('dt << End Data Update;')

#Warning/Error Result:

"Name Unresolved: dt in access or evaluation of 'dt' , dt/*###*/

 

at line 1
Name Unresolved: dt in access or evaluation of 'dt' , dt/*###*/

 

at line 1"

 

#This print was actually on the same line as the second 'at line 1' error message.

dt to dt w indices wrapped in begin/end update xfer time: 14.3627 seconds

#Final summary of run time on 100k rows.

Total Execution time: 77.0904 seconds

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

save you data (*) before running this code a second time (**)  - or enable the begin/end data update!

import jmp
import numpy as np
dt = jmp.open(jmp.SAMPLE_DATA + "Wafer Stacked.jmp")

jmp.globals['jdt'] = dt

jmp.run_jsl('Graph Builder( Variables( Y( :defects ) ), Elements( Bar( Y, Label( "Label by Value" ) ) ))');

# jmp.run_jsl('jdt << Begin Data Update;')
dt[ 5 ] = [np.random.randint(1000)]*177875
# jmp.run_jsl('jdt << End Data Update;')

 

 

(*) Just kidding, you will not lose your data - just take a coffee and wait till JMP finishes.

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

---

@hogi wrote:

... a second time (**)

---

(**) TS - 00251136
@Dahlia_Watkins noticed that sometimes it takes 2 execution of the code to face the speed issue.

Looks like a timing "issue".
Maybe:  when you run the code the first time, the plot is not created till Python finishes the row-by-row-data update.

edit:
Actually, the plot is not necessary as an interactive element to make the Python data update slow.
Further examples without interactive elements can be found in the posts below.

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

I added the topic to Caution: Places where Jmp does something unexpected 

 

 

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

For the original issue: "slow data access IF there is a new column"

here is is an illustrative  example:

import jmp

dt = jmp.DataTable('dt_new', 50000)
col = dt.new_column("newCol")

# ---------   cut here to make it slow - why? ------------- 


dt[ 0 ] = [1]*50000

 

Cut into 2 steps, it will take JMP several seconds to get the 50,000 entries updated.


puzzling:
When executing both blocks in one run, JMP has no issue.

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

Besides the workarounds mentioned in @Paul_Nelson 's post, I thought there were some additional ones like:

1. Use JSL to to create the column / the data table
2. Use JSL to fill the column with dummy values
3. use JSL to save the data table, close it and re-open it

Many of them looks good at first sight - but it's  misleading.
The high speed is just  because same values are written again into the column and JMP doesn't trigger a "data update alert":

e.g.

jmp.globals['jdt'] = dt
jmp.run_jsl('''
nc = n cols(jdt);
Column(jdt,nc) << set each value(1)
''');

nc = dt.ncols
data = [1]*50000
dt[ nc - 1 ] =data

the same miracle via Python:

# 1st: slow
nc = dt.ncols
dt[ nc - 1 ] = range(50000)

# fast !   (same values!)
dt[ nc - 1 ] = range(50000)

# new values: slow again
dt[ nc - 1 ] = [5]*50000

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

One can set up a trigger to get a notification when JMP thinks that data gets updated.

import jmp
dt = jmp.open(jmp.SAMPLE_DATA + "Big Class.jmp")

jmp.run_jsl('''
New Column( "trigger",
	Formula(
		If( Row() == 1,
			Try( Caption( Char( ::i=::i+1 ) ), ::i = 1 )
		);
		:age;i
	)
)
''')


dt[ 1 ] = range(40)

dt[ 1 ] = range(40) # no data update !

dt[ 1 ] = [2]*40

# for loop without begin/end data update -> finished before JMP notices the data update
jmp.run_jsl('''
for each row(:age = row());
wait(5);
''')

# for loop without begin/end data update - and some delay to give JMP the time to notice:
jmp.run_jsl('''
for each row(:age = 2*row();wait(0))
''')

 

 

So, indeed, there is no "data update" when the original and new values are identical.
Implemented to combat infinite loops? Great!

 

Interesting, 

import jmp
dt = jmp.open(jmp.SAMPLE_DATA + "Wafer Stacked.jmp")

jmp.run_jsl('''
delete symbols(::i)
New Column( "trigger",
	Formula(
		If( Row() == 1,
			Try( Caption( Char( ::i=::i+1 ) ), ::i = 1 )
		);
		:Y_Die;i
	)
)
''')

nr = dt.nrows
dt[ 4 ] = range(nr)

takes more than 1 minute -  And i counts up to 2.

 

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

---

@Paul_Nelson wrote:

...

Most likely we will want to do support this from Python. Probably using a with block to suspend the UI updates of the table when filling / updating large number of rows.

---

 

great idea!

Please take care that you switch back on the interactivity even when Python throws an error within the "with" block.

 

As an alternative:

Please add an indicator to the data grid to show when interactivity is disabled because there was a

begin data update without an end data update.

Indicator: interactivity disabled for this data table 

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

Findings of the previous posts - compressed to: 

import jmp

# open data table and wait for the "Interactivity Manager" to get active

jmp.run_jsl('''
dt = new table("test", add rows(100000), new column ("col"));
Python send(dt);
wait(0) // disable the delay to make it fast
'''
);

nr=dt.nrows

# Python data update: slow!
dt[ 0 ] = range(nr)

# --- run step by step: -----

# same values: fast
dt[ 0 ] = range(nr)

#new values: slow
dt[ 0 ] = [1]*nr

 

Updating  a JSL column via Python is very slow.

Exceptions:

• write access right after creating/opening the data table (before some kind of "Interactivity Manager" is active)
  
  
• no update at all (values don't change)
  
  and the workarounds mentioned in @Paul_Nelson 's post:
  
  
• private data tables
  
  
• Python data update enclosed by JSL Begin/End Data Update()

 

 

Fast Python data access - just with the support of JSL ; )
I suppose the issues are significant enough to be resolved in the next ".x"  release ...

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

Actually for the second case it needs to be a private table, visibility='Private'. The 'Invisible' table still has a GUI even though it is hidden, and as such is still laggy due to the GUI updates taking place in the data table on every cell edit.

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

Yes, this is significant.  We are indeed looking at a fix.  We want the performance from Python to be such that users want to run from Python and not have to use backdoor magic methods to get performance.  

I'm surprised this didn't show up in JMP 18 or during JMP 19 EA testing.  The jmp.DataTable object at its heart is the JSL datatable.  The Python object is just a reference to the underlying JMP DataTable, and we are calling the JMP Data Table's internal methods, which is why the run_jsl( 'dt << Begin Data Update') makes a difference.  The entire reason for the jmp.run_jsl(''' code '''') was to allow calling JSL from Python for things that we have not yet implemented, or as in this case rectify an oversight on column update performance.

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

Great to hear that the issue gets fixed.

Why was it not detected before?

I guess many users use Python for data import/ data curation, before they transfer the data to JMP - where they don't touch the data via Python. If they use  Python get(dt)[JSL]  or  from_dataframe() [Python] they won't face the issue.

Caveat: column-by-column-transfer like in Getting started with Python integration in JMP 18.
Here it depends on the size of the data table. If the table is small enough, the new columns get added before the "Interactivity Manager" is activated.

When we detected the issue at our side, we had access to JMP19EA and were happy to see that we can speed up the table generation via from_dataframe() - with the additional benefit of a much easier code.

On the other hand: The issue will show up when Python is used to update individual columns of an existing JMP data table, like in   JMPyFacade: Bridging JMP and Python for Seamless Engaging Analysis .  As a JSL pro, @jthi definitely added the necessary Private-Table-merge data - workaround without wondering too much ; )

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

Info shared in today's Scripters Club:

the speed issue with

dt[ index ] = list_like_object 

 will be fixed in JMP 19.0.3     :  )

Re: Python in JMP: dataframe column to datatable column best practices and computation speed implications

Available now: