JSL script for binning

[#1]

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

 

[#2]

Here is a sample script that should do what you want. 

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA\Semiconductor Capability.jmp" );

For( i = 5, i <= N Cols( dt ), i++,
	Eval(
		Substitute(
				Expr(
					RowsP0_20 = dt << get rows where( __col__ <= Col Quantile( __col__, .2 ) );
					RowsP20_40 = dt << get rows where(
						__col__ > Col Quantile( __col__, .2 ) & __col__ <=
						Col Quantile( __col__, .4 )
					);
					RowsP40_60 = dt << get rows where(
						__col__ > Col Quantile( __col__, .4 ) & __col__ <=
						Col Quantile( __col__, .6 )
					);
					RowsP60_80 = dt << get rows where(
						__col__ > Col Quantile( __col__, .6 ) & __col__ <=
						Col Quantile( __col__, .8 )
					);
					RowsP80_100 = dt << get rows where( __col__ > Col Quantile( __col__, .8 ) );

					__col__[RowsP0_20] = 1;
					__col__[RowsP20_40] = 2;
					__col__[RowsP40_60] = 3;
					__col__[RowsP60_80] = 4;
					__col__[RowsP80_100] = 1;
				),
			Expr( __col__ ), Parse( ":" || Char( Column( dt, i ) << get name ) )
		)
	)
);

The script is more complex than it would have to be.  That is, it could have used the As Column() to replace each of the points in the script that pointed to a column, however, this compute overhead made the running of the code, about a minute per column for processing.  The Substitute version of the code, has the whole thing processing in a few seconds.

[#3 · Accepted solution]

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

[#4 · Accepted solution]

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

[#5]

Thanks Jim. I will manually recode those column names. Thanks once again for your help!.