topic Re: JSL Help to split/stack data in Discussions

JSL Help to split/stack data

SDF1 — Fri, 09 Jun 2023 18:12:10 GMT

Hello All,

I'm running JMP Pro 16.1.0 and in need of some JSL help.

I have a data table that is generated from importing a CSV file and has the following structure (the actual number of columns is much larger, but the table maintains this structure):

I am trying to figure out how to correctly change the structure into the typical format for a specifications table to be like this:

Eventually, the columns names will be changed to the JMP format for a specs data table, namely: "Column 1"n, "_LSL"n, "_Target"n, and "_USL"n. Each row will be a specification with the corresponding values.

The problem I am having is how to do this with JSL to automate the process. I've tried different combinations of Split or Stack, but neither actually gets me where I want to go -- at least not in how I've used them. I'm sure there is an easy way to do this, but I'm missing it somehow.

Any suggestions are welcome.

Thanks!,

Re: JSL Help to split/stack data

jthi — Thu, 03 Mar 2022 14:36:34 GMT

I think stack should be able to do this when using Multiple series stack. Data types should be same for all columns (at least in this method)

and then remove label columns. Changing the options in stack platform might make it even more clear, but I haven't used multiple series stack, but it is very powerful. I'll tag @Jeff_Perkinson here, because I have seen him use it multiple times and might have better settings for it than I do.

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(1),
	New Column("Column 1", Character, "Nominal", Set Values({"x1"})),
	New Column("Column 2", Character, "Nominal", Set Values({"1"})),
	New Column("Column 3", Character, "Nominal", Set Values({"1.1"})),
	New Column("Column 4", Character, "Nominal", Set Values({"1.11"})),
	New Column("Column 5", Character, "Nominal", Set Values({"x2"})),
	New Column("Column 6", Character, "Nominal", Set Values({"2"})),
	New Column("Column 7", Character, "Nominal", Set Values({"2.2"})),
	New Column("Column 8", Character, "Nominal", Set Values({"2.22"})),
	New Column("Column 9", Character, "Nominal", Set Values({"x3"})),
	New Column("Column 10", Character, "Nominal", Set Values({"3"})),
	New Column("Column 11", Character, "Nominal", Set Values({"3.3"})),
	New Column("Column 12", Character, "Nominal", Set Values({"3.33"}))
);

// data types must be same for stack, so you might have to script it
dt_stacked = dt << Stack(
	columns(
		:Column 1, :Column 2, :Column 3, :Column 4, :Column 5, :Column 6, :Column 7,
		:Column 8, :Column 9, :Column 10, :Column 11, :Column 12
	),
	Source Label Column("Label"),
	Stacked Data Column("Data"),
	Stack By Row(0),
	Number of Series(4),
	Contiguous
);

For Each({col_name}, dt_stacked << get column names("String"),
	If(Contains(col_name, "Label"),
		dt_stacked << Delete Columns(col_name)
	);
);

Re: JSL Help to split/stack data

Jeff_Perkinson — Thu, 03 Mar 2022 14:47:30 GMT

This is a Multiple Series Stack using Tables->Stack.

Starting with this table.

And using Tables->Stack to with the Multiple Series Stack option.

Will get you this table.

Just delete the Label columns and you're all set.

Here's the JSL I got from the Enhanced Log.

// Stack data table
// → Data Table( "untitled 13" )
Data Table( "untitled 11" ) << Stack(
	columns(
		:c00000 01, :c00000 02, :c00000 03, :c00000 04, :c00000 05, :c00000 06,
		:c00000 07, :c00000 08, :c00000 09, :c00000 10, :c00000 11, :c00000 12
	),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" ),
	Number of Series( 4 )
);


// Delete columns
Data Table( "untitled 13" ) << Delete Columns(
	:Label, :Label 2, :Label 3, :Label 4
);

Re: JSL Help to split/stack data

SDF1 — Thu, 03 Mar 2022 15:18:13 GMT

Hi @Jeff_Perkinson ,

Thanks for the quick response, yes, that is exactly what I was hoping to get to work.

@jthi , it appears that from Jeff's reply, if you group them into the right number of series, then as long as the column data structure remains the same, then it can stack them in the way intended and the columns don't need to all have the same data type. Which for my case means the first column is always a text column followed by three numerical columns.

Thanks for the help!,