JSL script for binning

Question

Hi,

I have a sparse matrix with 1248 rows and 2435 columns. My first column is CustomerID and remaining are products with quantity ordered by each customer as a value. Each column has a different range. I want to standardize the columns values to 1 - 5 by having cut points at 20 percentile. I found interactive binning addin can help me to accomplish if the number of columns are less. I am new to JMP and would appreciate if anyone could help me with JSL script to achieve this. Binning should exclude the missing value.

I have attached my data table for reference. Thanks in Advance.

Regards,

Tara

Tara · Accepted Answer

Hi Jim,

Thank you for your time and reply. The script worked fine for the first 7 columns. I could not figure out why it does not proceed further. I have attached the jmp file for your reference. I made minor changes to your script: dt = Current Data Table (); , i = 2 and _col_[RowP80_100]  = 5. Rest remains the same.

Thanks

Tara

txnelson · Accepted Answer

Note:  I just found another issue....well same issue more complex.  Some of the columns have double quotes in the names....that causes the script to error out.......

This could be worked around......but I think the easiest solution is to change the double quotes in the names to single quotes(manually) and then run the script with the change noted below.

This is a common issue, and I should have protected for the issue.

The 7th column is named:

BONNE JAM JAR  T-LIGHT HOLDER

Unless guarded for, JMP will interpret the name not as a single column name, but rather 2 columns,

BONNE JAM JAR  T

and

LIGHT HOLDER

JMP will then try to subtract one from the other, because it finds a minus sign, "-" between the 2 names.  To protect against these complex column names, JMP has a function called :Name().  So if you replace the following line in the code:

Expr( __col__ ), Parse( ":" || Char( Column( dt, i ) << get name ) )

with:

Expr( __col__ ), Parse( ":Name(\!"" || Char( Column( dt, i ) << get name )  || "\!")")

it will work