758400_en-US

liliconcom

Pass a JMP datatable to Python as a Pandas dataframe

In prior versions of JMP (pre-JMP 18), when you passed a JMP datatable to Python, it was a Pandas data frame by default (very nice).  Unfortunately it seems like that feature has been eliminated in JMP 18 where after passing the data table to Python with PythonSend(), you then need to loop through every column and covert to a Numpy array doing something like this:

df = pd.DataFrame()
for i in range( len(cols) ):
	df[ cols[i].name ] = np.array( cols[i] )

And this is time consuming for large data tables.  I have received this negative feedback from a customer (NVIDIA) who is heavily utilizing JMP and Python.  

Is there a way to pass data tables to Python as a Pandas data frame without having to do this conversion?  Or better yet, can we bring back the JMP 17 functionality of PythonSend() which does this by default?

Thanks,

Mark

 

Re: Pass a JMP datatable to Python as a Pandas dataframe

In JMP 18, here is a way to do it

import pandas as pd
import jmp

# open the JMP data table 
tablepath=<PATHNAME string="">
dt=jmp.open(tablepath)
# you can also use dt=jmp.current() or dt=jmp.table(<'tablename'>)

# define function to convert JMP table to pandas data frame
def jmpTableToDataFrame(dt) :
    # Creating a Pandas dataframe from a JMP DataTable directly.
    df = pd.DataFrame()
    for idx in range( len(dt) ):
        print(dt[idx].name)
        if dt[idx].dtype == jmp.DataType.Numeric:
            # creates numeric column directly using Python's Buffer Protocol
            col = np.array( dt[idx] )
            df[ dt[idx].name ] = col.tolist()    # make it a list for pandas.
        elif dt[idx].dtype == jmp.DataType.Character:
            # create a list by iterating through values
            col = list()
            for i in range ( dt.nrows ):
                col.append(dt[idx][i])
            # Build character column from list
            df[ dt[idx].name ] = col
        else:
            print( dt[idx].dtype )
    return df

#convert JMP table to pandas data frame
df = jmpTableToDataFrame(dt);
print(df)</PATHNAME>

Re: Pass a JMP datatable to Python as a Pandas dataframe

I just want to point out looping through the columns is orders of magnitude faster than JMP 17's behavior. This is all happening in-memory. JMP 14-17 writes a JMP data table to disk as a temporary CSV file, before reading the file back in from pandas as a CSV file.  One customer had a 200M .jmp file that ballooned to 3G CSV in the transfer process taking 15 minutes. The old method also had the potential for loss of precision of data since it's converting binary to text, then text back into binary.

If you really want to use the old method it's included in the SAMPLE_SCRIPTS/Python/dt2pandas.py as a function to_pandas(dt). Taking in a jmp.DataTable object and returning a pandas.DataFrame.  

We will not be making the Python Send(dt) default to sending a pandas.DataFrame.  The jmp.DataTable object gives Python code the ability to manipulate and handle JMP data tables live from Python, pandas does not.

That will be the biggest change users that were utilizing JMP 14-17's Python will need to adapt to.  But at worst it's 1 additional import statement import dt2pandas and  pd = to_pandas(dt) in the code after the send, if you want to do it the old way.  Or equivalently wrap the code looping on the columns in a function and call that to create a pandas data frame in-memory from the jmp.DataTable object.

Re: Pass a JMP datatable to Python as a Pandas dataframe

Also refer to my blog post.

https://community.jmp.com/t5/JMPer-Cable/New-in-JMP-18-Python-jmp-DataTable-and-pandas-DataFrame/ba-p/744680

Re: Pass a JMP datatable to Python as a Pandas dataframe

Thanks for the great explanation Paul!  I don't think the user paid attention to the performance difference, but rather just noticed the difference in behavior of PythonSend().  When this topic comes up (it will), important to point this performance improvement out so that it is perceived as a feature and not a bug.  I will share your great blog post with Nvidia on the topic.

Re: Pass a JMP datatable to Python as a Pandas dataframe

Hi Sam / Paul,

Is this function you shared considered the best known method for converting JMP data tables to Pandas data frames?  This is something that is going to be universally used for any JMP/Python user.  So much so that I would think it warrants including it in the JMP python library?