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

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

CohortOpossum40 — Tue, 11 Mar 2025 14:00:57 GMT

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 |

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

jthi — Tue, 11 Mar 2025 14:18:42 GMT

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

bfoulkes — Tue, 11 Mar 2025 14:19:06 GMT

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
dt0 = Data table("Input Table");

dt1 = new table("Output table");
dt1<<New Column("ID", Character);
dt1<<New column("Label", Character);
dt1<<New Column("Data", Character);

current data table(dt0);
for each row(
	idvar = dt0:ID;
	list1 = words(dt0:data,"_");
	list2 = words(dt0:label,"_");

	current data table(dt1);
	for(i=1, i<=Nitems(list1), i++,
		dt1<<add rows(1);
		dt1:ID=idvar;

		dt1:label = list2[i];
		dt1:data = list1[i];
	);
);

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

jthi — Sun, 16 Mar 2025 08:49:30 GMT

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

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

jthi — Sun, 16 Mar 2025 09:07:41 GMT

Few improvements I would suggest:

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)
Loop over specific table when using For Each Row and do not rely on what ever the current table is (for each row(dt0,...)
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
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)

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