Summary Data Table by Category

Lino — Fri, 09 Jun 2023 21:59:35 GMT

Hi,

I have data with around 5000 rows.

There three columns (Group, Score and Count)

Value for Group: A, B

Value for Score ranging 0.5 - 1

Value for Count is 1 for each row.

I would like to summary the data by group A and B and group the Score value in few category and then sum the count for each score category , as below format.

I am seeking help from the community how to create JSL script to perform this task?

I attach data here as well.

Thanks

Re: Summary Data Table by Category

Michael_MXAK — Wed, 02 Dec 2020 15:46:25 GMT

I would use an interactive approach to capture a script for this. First, let's start with the data table:

I left Count off because we won't need it but it won't hurt if it's there.

I selected the Score column, then navigated to Cols, Utilities, Make Binning Formula:

And set Offset to 0.5, and bin width to 0.1:

Right click the new column (Score Binned) and Copy Column Properties:

Then open a new script window and paste (ctrl-V), which results in this JSL:

Add Column Properties(
	Value Labels(
		{-0.5 = "-0.5 — -0.4", -0.4 = "-0.4 — -0.3", -0.3 = "-0.3 — -0.2", -0.2 =
		"-0.2 — -0.1", -0.1 = "-0.1 — 0", 0 = "0 — 0.1", 0.1 = "0.1 — 0.2", 0.2 =
		"0.2 — 0.3", 0.3 = "0.3 — 0.4", 0.4 = "0.4 — 0.5", 0.5 = "0.5 — 0.6", 0.6 =
		"0.6 — 0.7", 0.7 = "0.7 — 0.8", 0.8 = "0.8 — 0.9", 0.9 = "0.9 — 1", 1 =
		"1 — 1.1", 1.1 = "1.1 — 1.2", 1.2 = "1.2 — 1.3", 1.3 = "1.3 — 1.4", 1.4 =
		"1.4 — 1.5", 1.5 = "1.5 — 1.6", 1.6 = "1.6 — 1.7", 1.7 = "1.7 — 1.8", 1.8 =
		"1.8 — 1.9", 1.9 = "1.9 — 2"}
	),
	Use Value Labels( 1 ),
	Formula( 0.5 + 0.1 * Floor( (:Score - 0.5) / 0.1 ) )
)

Two things here: we don't care about any scores less than 0.5 (they don't exist in your description) or greater than 1, so we can change the properties a bit and edit the label names to match what you want. I've also added an If statement to capture the edge case where a score is exactly 1

Add Column Properties(
	Value Labels(
		{0.5 = "0.5 — 0.59", 0.6 = "0.6 — 0.69", 0.7 = "0.7 — 0.79", 0.8 = "0.8 — 0.89", 0.9 =
		"0.9 — 1"}
	),
	Use Value Labels( 1 ),
	Formula(
		If(
			(0.5 + 0.1 * Floor( (:Score - 0.5) / 0.1 )) == 1, 0.9,
			0.5 + 0.1 * Floor( (:Score - 0.5) / 0.1 )
		)
	)
);

(BTW those labels aren't technically accurate, since 0.599 would be labeled as between 0.5 and 0.59)

We can wrap some code around this snippet to make it reusable, where we use the current data table, add a new column called Score Binned, and add these properties to that column:

dt = Current Data Table();

dt << New Column("Score Binned", Character);

dt:"Score Binned"n << Add Column Properties(
	Value Labels(
		{0.5 = "0.5 — 0.59", 0.6 = "0.6 — 0.69", 0.7 = "0.7 — 0.79", 0.8 = "0.8 — 0.89", 0.9 =
		"0.9 — 1"}
	),
	Use Value Labels( 1 ),
	Formula(
		If(
			(0.5 + 0.1 * Floor( (:Score - 0.5) / 0.1 )) == 1, 0.9,
			0.5 + 0.1 * Floor( (:Score - 0.5) / 0.1 )
		)
	)
);

Now, back on our table, Tables > Summary takes us where we want to go. We put Group and Score Binned in Group, and that's all we actually need:

Now, N Rows is what we want, it's just named wrong, so let's double-click the word "Source" on the left and copy the table creation script and replace the named table reference with "dt", our working table, and catch the new table in the variable "summ":

dt = Current Data Table();

dt << New Column("Score Binned", Character);

dt:"Score Binned"n << Add Column Properties(
	Value Labels(
		{0.5 = "0.5 — 0.59", 0.6 = "0.6 — 0.69", 0.7 = "0.7 — 0.79", 0.8 = "0.8 — 0.89", 0.9 =
		"0.9 — 1"}
	),
	Use Value Labels( 1 ),
	Formula(
		If(
			(0.5 + 0.1 * Floor( (:Score - 0.5) / 0.1 )) == 1, 0.9,
			0.5 + 0.1 * Floor( (:Score - 0.5) / 0.1 )
		)
	)
);

summ = dt << Summary(
	Group( :Group, :Score Binned ),
	Freq( "None" ),
	Weight( "None" )
);

Finally, rename N Rows to "Total_Count" as desired:

dt = Current Data Table();

dt << New Column("Score Binned", Character);

dt:"Score Binned"n << Add Column Properties(
	Value Labels(
		{0.5 = "0.5 — 0.59", 0.6 = "0.6 — 0.69", 0.7 = "0.7 — 0.79", 0.8 = "0.8 — 0.89", 0.9 =
		"0.9 — 1"}
	),
	Use Value Labels( 1 ),
	Formula(
		If(
			(0.5 + 0.1 * Floor( (:Score - 0.5) / 0.1 )) == 1, 0.9,
			0.5 + 0.1 * Floor( (:Score - 0.5) / 0.1 )
		)
	)
);

summ = dt << Summary(
	Group( :Group, :Score Binned ),
	Freq( "None" ),
	Weight( "None" )
);

summ:"N Rows"n << Set Name("Total_Count");

Now, close the summary table, delete the Score Binned column from the source table, and test the script:

Final JSL again:

dt = Current Data Table();

dt << New Column("Score Binned", Character);

dt:"Score Binned"n << Add Column Properties(
	Value Labels(
		{0.5 = "0.5 — 0.59", 0.6 = "0.6 — 0.69", 0.7 = "0.7 — 0.79", 0.8 = "0.8 — 0.89", 0.9 =
		"0.9 — 1"}
	),
	Use Value Labels( 1 ),
	Formula(
		If(
			(0.5 + 0.1 * Floor( (:Score - 0.5) / 0.1 )) == 1, 0.9,
			0.5 + 0.1 * Floor( (:Score - 0.5) / 0.1 )
		)
	)
);

summ = dt << Summary(
	Group( :Group, :Score Binned ),
	Freq( "None" ),
	Weight( "None" )
);

summ:"N Rows"n << Set Name("Total_Count");

Tested on your attached data, which I literally just noticed :flushed_face:

Re: Summary Data Table by Category

txnelson — Wed, 02 Dec 2020 16:15:25 GMT

Here are a couple of ways to do this. But since the example below is really just based upon primary JMP functionality, I strongly suggest that you read the Discovering JMP, and the Using JMP documents, from the JMP Documentation Library, available under the Help pull down menu.

Names Default To Here( 1 );
dt = Current Data Table();

dt << New Column( "Score Category",
	character,
	formula(
		If(
			:Score < .6, "0.50-0.59",
			:Score < .8, "0.60-0.79",
			:Score < .9, "0.80-0.89",
			"0.90-1.00",
		)
	)
);

dtSum = dt << Summary(
	Group( :Group, :Score Category ),
	Sum( :Count ),
	Freq( "None" ),
	Weight( "None" ),
	Link to original data table( 0 )
);

dtSum << delete columns( :N Rows );
dtSum:Name( "Sum(Count)" ) << set name( "Total_Count" );


// or

dt:score << set property(
	"value labels",
	{0.5 <= "0.50-0.59" < 0.6, 0.6 <= "0.60-0.79" < 0.8, 0.8 <= "0.80-0.89" < 0.9, 0.9
	 <= "0.90-1.00" <= 1}
);
	
dtSum = dt << Summary(
	Group( :Group, :Score ),
	Sum( :Count ),
	Freq( "None" ),
	Weight( "None" ),
	Link to original data table( 0 )
);

dtSum << delete columns( :N Rows );
dtSum:Name( "Sum(Count)" ) << set name( "Total_Count" );

Re: Summary Data Table by Category

Lino — Thu, 03 Dec 2020 13:12:06 GMT

Thanks Jim and Michael.

topic Re: Summary Data Table by Category in Discussions

Summary Data Table by Category

Re: Summary Data Table by Category

Re: Summary Data Table by Category

Re: Summary Data Table by Category