847318_en-US

liliconcom

Need Help on JSL Scripting to Split Column Data with delimiter, and match with another Column delimiter Split Data

Hello guys 

i'm using JMP17 and really need help on JSL script to process the input data below to the desired output data 

 

Input Data Table: 

| Data|  Label | ID |

|----------|----------|----------|

| 1_1_64_5_2   | D1_DD4_FF1_FIN1_GPES2   | Apple1   |

| 1_1_2  | D2_D3_D4   | Apple1   |

| 1_2_68_5_6   | D1_DD4_FF1_FIN1_GPES2   | Orange1   |

| 1_2_2  | D2_D3_D4   | Orange1   |

| 2_1_50_3_10   | D1_DD4_FF1_FIN1_GPES2   | Grape1  |

| 2_1_2  | D2_D3_D4   | Grape1  |

 

Output Format: (column sequence arrangement does not matter)

 

| ID|  Label| Data|

|----------|----------|----------|

| Apple1  | D1   | 1   |

| Apple1  | DD4   | 1   |

| Apple1  | FF1   | 64 |

| Apple1  | FIN1  | 5   |

| Apple1  | GPES2  | 2   |

| Apple1  | D2| 1 |

| Apple1  | D3| 1   |

| Apple1  | D4   | 2   |

| Orange1| D1   | 1   |

| Orange1| DD4   | 2|

| Orange1| FF1   | 68|

| Orange1| FIN1  | 5   |

| Orange1| GPES2  | 6   |

| Orange1| D2| 1 |

| Orange1| D3| 2   |

| Orange1| D4   | 2   |

| Grape1  | D1   | 2   |

| Grape1  | DD4   | 1|

| Grape1  | FF1   | 50|

| Grape1  | FIN1  | 3   |

| Grape1  | GPES2  | 10   |

| Grape1  | D2| 2 |

| Grape1  | D3| 1   |

| Grape1  | D4   | 2   |

Automation and ScriptingBasic Data Analysis and ModelingData AccessRe: Need Help on JSL Scripting to Split Column Data with delimiter, and match with another Column delimiter Split Data

Can you provide example table? It is a bit difficult to understand the starting situation from just text.

Re: Need Help on JSL Scripting to Split Column Data with delimiter, and match with another Column delimiter Split Data

I'm assuming that all of the label and data values have the same number of items in there. If so, this would work. If not, there would need to be a little more logic added to check for that, but this would be a quick way to process your data.

 

// this would be whatever your input table name is<BR />dt0 = Data table("Input Table");

dt1 = new table("Output table");
dt1<<NEW column=""></NEW>

Re: Need Help on JSL Scripting to Split Column Data with delimiter, and match with another Column delimiter Split Data

I'm not sure what is the starting point, so I will assume you have a data table and not text file.

Pick Data and Label column and use Text to Columns on them

Remove those "old" columns (or move them to end).

Next stack your data using multiple series stack

Delete extra columns, reorder and rename columns

Drop missing rows

Verify results and take the script JMP created for you (or workflow).

 

Workflow is also attached (might require some cleanup) and cleaned script based on the workflow below (can be made much more robust depending on your needs):

View more...

Names Default To Here(1);

dt = Current Data Table();
dt << Text to Columns(columns(:" Data"n, :Label), Delimiters("_"));
dt << Delete Columns(:" Data"n, :Label);

dt_stacked = dt << Stack(
	columns(:Data 1, :Data 2, :Data 3, :Data 4, :Data 5, :Label 1, :Label 2, :Label 3, :Label 4, :Label 5),
	Number of Series(2),
	Contiguous,
	Output Table("Stacked result")
);

dt_stacked << Delete Columns(:Label, :Label 2);

Column(dt_stacked, "Data 2") << Set Name("Label");
dt_stacked << Move Selected Columns({:Label}, after(:ID));

dt_stacked << Select Where(:Label == "") << Delete Rows;

Re: Need Help on JSL Scripting to Split Column Data with delimiter, and match with another Column delimiter Split Data

Few improvements I would suggest:

1. Generally don't use Current Data Table(). In this case you could potentially pick the initial table using it but you shouldn't need to keep changing it around as you already have the references (for example current data table(dt1); is unnecessary and could cause issues)
2. Loop over specific table when using For Each Row and do not rely on what ever the current table is (for each row(dt0,...)
3. You can also use For Each + Across to loop over multiple containers. You can also force different behaviors when using Across to handle different length containers
4. If you know the column names + values, you can add values already with << Add rows but it can be a mess (usually I add row like you did and add values after)

View more...

Names Default To Here(1);

dt_input = Current Data Table(); 
// dt_input = Data table("Input Table");

dt_result = New Table("Output table");
dt_result << New Column("ID", Character, Nominal);
dt_result << New column("Label", Character, Nominal);
dt_result << New Column("Data", Character, Nominal);

For Each Row(dt_input,
	id_val = Column(dt_input, "ID")[];
	data_vals = Words(Column(dt_input, "Data")[], "_");
	label_vals = Words(Column(dt_input, "Label")[], "_");
	
	For Each({{cur_label, cur_data}}, Across(label_vals, data_vals),
		dt_result << Add Rows({ID = id_val, Label = cur_label, Data = cur_data});
	);
);