Stacking in JSL indexed columns by group (sensor data and assets)

[#1]

In manufacturing, is common to extract sensor data in the following form, where A and B correspond to similar devices (known as assets).

In this format, the user needs to create a combined formula for each parent group. For example:

(Input: Sensor data table needs specific columns per parent group)

When handling several assets, this becomes cumbersome, so a better way is to stack the data per group asset.

To do this programmatically, we need contextual data containing the hierarchy. For example:

(Input: asset hierarchy)

In JSL, how can you move from the sensor data and hierarchy (inputs) to the asset table (desired output)?

(output: asset data table)

All tables and scripts are attached. Here is the input table script (metadata containing parent-child is added as column properties).

Names Default To Here( 1 );

// New data table
dt = New Table( "sensor table" );

n_rows = 10;

n_additional_rows = 0;

// New column: Index
dt << New Column( "Index", Numeric, "Continuous", Format( "Best", 12 ) ) <<
	Begin Data Update << Add Rows( n_rows );

dt:Index << Set Formula( Row() );

dt << End Data Update;


// A sensor

dt << New Column( "A sensor 1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected
	) << New Column( "A sensor 2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected
	) << New Column( "A sensor 3",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected
	) << Group Columns( "A sensors", {:A sensor 1, :A sensor 2, :A sensor 3} )
	 << Begin Data Update;

For Each Row(
		dt,
		:A sensor 1 = Random Normal( 0, 1 );
		:A sensor 2 = Random Normal( 0, 1 );
		:A sensor 3 = Random Normal( 0, 1 );
	);
	
dt << End Data Update;

// B sensors

dt << Add Rows( n_additional_rows );


dt << New Column( "B sensor 1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected
	) << New Column( "B sensor 2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected
	/*) << New Column( "B sensor 3",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected*/
	) << Group Columns( "B sensors", {:B sensor 1, :B sensor 2}) //, :B sensor 3} )
	 << Begin Data Update;

For Each Row(
		dt,
		:B sensor 1 = Random Normal( 0, 3 );
		:B sensor 2 = Random Normal( 0, 2 );
		//:B sensor 3 = Random Normal( 0, 3.5 );
	);
	
dt << End Data Update;

colnames = dt << Get Column Names( String );

for each({col, index}, colnames,
		column(dt, col) << Set Property( "child", col ) );

// Calculations.

// New formula column: Stdev[A sensor ...r 2,A sensor 3]
dt << New Column( "Stdev[A sensors]",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( Std Dev( :A sensor 1, :A sensor 2, :A sensor 3 ) ));
	
// Move selected column: Stdev[A sensors]
dt <<Move Selected Columns( {:"Stdev[A sensors]"n}, after( :A sensor 3 ) );
	
// New formula column: Stdev[A sensor ...r 2,A sensor 3]
dt << New Column( "Stdev[B sensors]",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( Std Dev( :B sensor 1, :B sensor 2) ));//, :B sensor 3 ) ));
	
// Move selected column: Stdev[A sensors]
dt <<Move Selected Columns( {:"Stdev[B sensors]"n}, after( :B sensor 2 ) );

// Create metadata

For Each( {col, index}, {:A sensor 1, :A sensor 2, :A sensor 3},
		col << Set Property( "parent", "A" ) << Set Property( "parent_alias", "asset" )
	);


For Each( {col, index}, {:B sensor 1, :B sensor 2}, //, :B sensor 3},
		col << Set Property( "parent", "B" ) << Set Property( "parent_alias", "asset" )
	);

For Each( {col, index}, {:A sensor 1, :B sensor 1},
		col << Set Property( "child_alias", "sensor 1" ) 
	);

For Each( {col, index}, {:A sensor 2, :B sensor 2},
		col << Set Property( "child_alias", "sensor 2" ) 
	);


For Each( {col, index}, {:A sensor 3},//, :B sensor 3},
		col << Set Property( "child_alias", "sensor 3" ) 
	);

[#2]

Response removed to avoid future reader's confusion

[#3]

Maybe something like this:

Names Default To Here( 1 );

//dt=open("input_example.jmp");

//Stack data table
Data Table( "input_example.jmp" ) << Stack(
	columns( Column Group( "A sensors" ), Column Group( "B sensors" ) ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" ),
	Output Table( "Stacked Data" )
);

//Text to columns
Data Table( "Stacked Data" ) << Text to Columns(
	columns( :Label ),
	Delimiters( " " )
);

//Delete column: Label 2
Data Table( "Stacked Data" ) << Delete Columns( :Label 2 );

//Change column name: Label 1 → Asset
Data Table( "Stacked Data" ):Label 1 << Set Name( "Asset" );

//Change column name: Label 3 → Sensor
Data Table( "Stacked Data" ):Label 3 << Set Name( "Sensor" );

//New column: Column 6
Data Table( "Stacked Data" ) << New Column( "Stddev of Sensor by Asset by Index",
	Numeric,
	"Continuous",
	Format( "Best", 12 )
);

//Change column formula: Stddev of Sensor by Asset by Index
Data Table( "Stacked Data" ):Stddev of Sensor by Asset by Index <<
Set Formula( Col Std Dev( :Data, :Asset, :Index ) );


//Report snapshot: Stacked Data - Graph Builder
Data Table( "Stacked Data" ) << Graph Builder(
	Size( 517, 450 ),
	Show Control Panel( 0 ),
	Variables(
		X( :Index ),
		Y( :Stddev of Sensor by Asset by Index ),
		Overlay( :Asset )
	),
	Elements( Line( X, Y, Legend( 7 ) ), Points( X, Y, Legend( 8 ) ) )
);

I used Worflow Builder in JMP 17 to "write" this script. 

The key is using the "by" arguments in the formula.  

This lets me completly stack the data to have the complete hierarcy with the data all in one column. 

Then I get the stddev of the whole column of data by asset and by index. That lets me make this graph:

[#4]

This is a good approach. Yet, a fully stacked table has the issue of being limited to those formulas that have a "by" argument.

For example, if I wanted to do a formula that is: (sensor 1 - sensor 2) / sensor 3, the by-stacked table will be needed.

[#5]

One option is to go pure JSL route if you do have the hierarchy table. Code might be able to explain itself:

Names Default To Here(1);

dt_hier = Open("$DOWNLOADS/hierarchy_example.jmp");
dt_input = Open("$DOWNLOADS/input_example.jmp");

// some assumptions:
	// both parents will have same amount of columns
new_col = dt_hier << New Column("Data Cols", Character, Nominal, << Set Each Value(
	Regex(:Child column, "sensor \d")
));
data_cols = Associative Array(new_col) << get keys;
dt_hier << Delete Column(new_col);

dt_collector = New Table("data",
	New Column("Index", Numeric, Ordinal),
	New Column("Group", Character, Nominal)
);
For Each({data_col}, data_cols,
	dt_collector << New Column(data_col, Numeric, Continuous)
);

Summarize(dt_hier, parents = by(:Parent));
For Each({parent}, parents,
	parent_rows = Loc(dt_hier[0, "Parent"], parent);
	parent_cols = dt_hier[parent_rows, "Child column"];
	parent_data = dt_input[0, parent_cols]; 
	non_missing_idx = Loc(parent_data);
	row_count =  N Items(non_missing_idx) / n cols(parent_data);
	
	parent_data_clean = Shape(parent_data[non_missing_idx], row_count);
	
	first_row = N Rows(dt_collector) + 1;
	last_row = first_row + row_count - 1;
	dt_collector << Add Rows(row_count);
	
	dt_collector[Index(first_row, last_row), "Index"] = Index(1, row_count)`;
	dt_collector[Index(first_row, last_row), "Group"] = Repeat({parent}, row_count);
	dt_collector[Index(first_row, last_row), data_cols] = parent_data_clean
);

Quite a few assumptions made here and no proper error handling

[#6]

The hierarchy can be already in the table as metadata (column property). 

I have updated the example so you have parent (group), parent alias, child, and child alias embedded in the table.

I think the most intuitive approach will be to stack the table by an asset (subset), split data by label, and then concatenate tables together.

[#7]

I have also simplified the requirements (no more empty values) and found a solution (stack, split, concat) that needs to be done iteratively (per asset). The hierarchy data is now embedded in the main table (progress can be found in the input_example).

[#8 · Accepted solution]

The most straightforward operation I found was by automating these data steps.

1) Stack

 2) Split

3) Concatenate