cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
SDF1
Super User

JSL Help to split/stack data

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):

SDF1_0-1646316350757.png

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

SDF1_1-1646316453221.png

  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!,

DS

1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Perkinson
Community Manager Community Manager

Re: JSL Help to split/stack data

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

 

Starting with this table.

2022-03-03_09-25-13.994.png

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

2022-03-03_09-25-01.967.png

Will get you this table.

2022-03-03_09-29-39.784.png

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
);
-Jeff

View solution in original post

3 REPLIES 3
jthi
Super User

Re: JSL Help to split/stack data

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)

 

jthi_1-1646317909414.png

jthi_0-1646317891299.png

jthi_2-1646317916586.png

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)
	);
);
-Jarmo
Jeff_Perkinson
Community Manager Community Manager

Re: JSL Help to split/stack data

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

 

Starting with this table.

2022-03-03_09-25-13.994.png

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

2022-03-03_09-25-01.967.png

Will get you this table.

2022-03-03_09-29-39.784.png

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
);
-Jeff
SDF1
Super User

Re: JSL Help to split/stack data

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!,

DS