topic Re: How do I use JSL to categorize positive and negative values in the same column? in Discussions

How do I use JSL to categorize positive and negative values in the same column?

lwx228 — Thu, 18 Jul 2019 07:54:11 GMT

According to the data of the "weight" column in document "Arrhythmia" 、and the "criterion" file in the attachment, the "level" column is obtained.

Then, according to the "level" column and "class" column, the data of "V3 QRSTA" column are classified according to the following rules:
Row basis is "level", column basis is "class", and positive value and negative value of "V3 QRSTA" column are classified and summarized respectively.

The above operations can be easily completed with excel and its functions, but the speed is slow when the data volume is large.

If the amount of data is large, how to use JMP JSL should be completed more quickly.Thank you very much!
This problem is quite complicated. I made several pictures and uploaded 2 files.

Re: How do I use JSL to categorize positive and negative values in the same column?

lwx228 — Thu, 18 Jul 2019 07:55:50 GMT

dt = Open( "$SAMPLE_DATA/Arrhythmia.jmp" );

Re: How do I use JSL to categorize positive and negative values in the same column?

lwx228 — Thu, 18 Jul 2019 08:11:01 GMT

Re: How do I use JSL to categorize positive and negative values in the same column?

lwx228 — Thu, 18 Jul 2019 08:02:06 GMT

When I did it myself, I added three columns to file Arrhythmia.jmp :"level"、"positive"、"negative".
I know it's stupid, but it's the only way I can handle it.Thank you very much!

Re: How do I use JSL to categorize positive and negative values in the same column?

lwx228 — Fri, 19 Jul 2019 13:48:37 GMT

It seems that there are many steps in my project, so I need to complete it in several steps:
Solve the first step above all: how to divide "weight" column by the "criterion" in accessory a few grades.

Re: How do I use JSL to categorize positive and negative values in the same column?

lwx228 — Fri, 19 Jul 2019 13:49:10 GMT

See screenshot for standard: The operation of this step, I can only use the way of "IF". Excel has several functions that do this. How does JSL do this quickly?Thank you very much!

Re: How do I use JSL to categorize positive and negative values in the same column?

txnelson — Fri, 19 Jul 2019 16:09:17 GMT

This can be handled easily in a JMP formula

If(weight<=30, 1
weight <= 40, 2,
weight <= 50, 3,

etc.

)

Re: How do I use JSL to categorize positive and negative values in the same column?

lwx228 — Sat, 20 Jul 2019 13:39:06 GMT

Thank Jim!

but If this grade classification reaches more than 100, such classification method code is more cumbersome.

Re: How do I use JSL to categorize positive and negative values in the same column?

Mark_Bailey — Sat, 20 Jul 2019 13:54:40 GMT

If you had the classification in a table as shown above, a script could easily create the expression for the necessary If() function. Or you could use the approach of lookup in such a table.

More than 100 classes? Would they be meaningful or useful? At this point you might want to just use the original continuous variable.

Re: How do I use JSL to categorize positive and negative values in the same column?

txnelson — Sat, 20 Jul 2019 14:02:48 GMT

JMP has many methods and functions that can be used for Classification and/or Binning. The If() structure I illustrated would be very fast and easy to implement for the Weight classifications you specified. If you could give more specifics on a classification that would have more than 100 levels, I am sure there are methods avaliable.

Re: How do I use JSL to categorize positive and negative values in the same column?

lwx228 — Sat, 20 Jul 2019 14:39:47 GMT

Use this practical example, thank you!

Re: How do I use JSL to categorize positive and negative values in the same column?

lwx228 — Sat, 20 Jul 2019 14:43:09 GMT

Thank you for your time.

Re: How do I use JSL to categorize positive and negative values in the same column?

lwx228 — Sat, 20 Jul 2019 14:44:32 GMT

Thank Jim!
See how this example is done with JSL.
<=

Re: How do I use JSL to categorize positive and negative values in the same column?

lwx228 — Sat, 20 Jul 2019 14:45:57 GMT

Standard intervals are irregular.

Re: How do I use JSL to categorize positive and negative values in the same column?

txnelson — Sat, 20 Jul 2019 16:08:37 GMT

Here is an example of one way to do the processing to set the grade for each raw data row. I have it set to have an Example data table of 50,000, which on my 4 year olf Window's pc takes about 2 seconds to create the example data and then to set the grade for each row.

Names Default To Here( 1 );

// Create a sample standards table
dtStandard = New Table( "Standards",
	Add Rows( 200 ),
	New Column( "standards", formula( Random Integer( 93500, 1000000 ) ) )
);
dtStandard << run formulas;
dtStandard:standards << delete property( "formula" );
dtStandard << sort( by( :standards ), order( ascending ), replace table( 1 ) );
dtStandard << rerun formulas;
dtStandard << New Column( "grades", formula( Row() ) );
dtStandard:grades << delete property( "formula" );

// Create an example table
dt = New Table( "Example",
	Add Rows( 50000 ),
	New Column( "Raw Data", formula( Random Integer( 93500, 1000000 ) ) )
);
dt << run formulas;
dt:Raw Data << delete property( "formula" );

// Make sure the standards table has the same range as the example table
If( dtStandard:standards[1] > Col Min( dt:raw data ),
	dtStandard:standards[1] = Col Min( dt:raw data )
);
If( dtStandard:standards[200] < Col Max( dt:raw data ),
	dtStandard:standards[200] = Col Max( dt:raw data )
);

// Set the standard
// The code below is the actual code that performs the setting of the grade

// Create a column to hold the original row positions in the data table
dt << New Column( "OrigRow", formula( Row() ) );
dt:OrigRow << delete property( "formula" );

// Add the new grades column to the Example data
dt << New Column( "grade" );

// Sort the data in order of the raw data
dt << sort( by( :Raw Data ), order( ascending ), replace table( 1 ) );

For( i = 1, i <= N Rows( dt ), i++,
	If( i == 1,
		lkupRow = Max( dtStandard << get rows where( dtStandard:standards <= dt:Raw Data[i] ) )
	);
	While( dtStandard:standards[lkupRow] < dt:Raw Data[i], lkupRow = lkupRow + 1 );
	dt:grade[i] = dtStandard:grades[lkupRow];
);

// Sort the data back to the original order
dt << sort( by( :OrigRow ), order( ascending ), replace table( 1 ) );

// Delete the origrow column
dt << delete columns( "OrigRow" );

Re: How do I use JSL to categorize positive and negative values in the same column?

lwx228 — Sun, 21 Jul 2019 03:08:14 GMT

I studied the code of Jim and understood that it was through sorting that realized this fast calculation.
Thank Jim!

If the data reaches ten million rows, it will take some time to wait.

Re: How do I use JSL to categorize positive and negative values in the same column?

txnelson — Sun, 21 Jul 2019 09:15:29 GMT

I ran my example with 1 million rows, and it took 17 seconds.

Re: How do I use JSL to categorize positive and negative values in the same column?

lwx228 — Sun, 21 Jul 2019 23:40:32 GMT

Thank Jim!

Re: How do I use JSL to categorize positive and negative values in the same column?

lwx228 — Mon, 22 Jul 2019 07:41:05 GMT

Now I want to summarize the positive value and negative value of column "V3 QRSTA" respectively by adding the weight "level" column and "class" column.

How to do this with JSL?

I have uploaded the data related to calculation in the attachment.Thanks!

Re: How do I use JSL to categorize positive and negative values in the same column?

lwx228 — Mon, 22 Jul 2019 07:39:09 GMT

This is a negative subtotal.

Thanks!