topic Re: Split string with certain delimiters and make a new row each element (each element will further split in to columns). in Discussions

Split string with certain delimiters and make a new row each element (each element will further split in to columns).

GR — Sat, 10 Jun 2023 23:29:07 GMT

Hi, I am new to jmp scripting. Trying to automate split "Data" column (Input.jmp) each cell value in to individual chunks of strings using space as the delimiter and each individual chuck of string will make a new row with including row A,B,C,D,E (Output_intermediate.jmp). For the final output (Output_Final.jmp), need to further split string "Data_Coulmn_split_by_space_delimiter" to it's individual components using ":" as delimiter and an additional column (:Data1) with the 1st element first letter. I used the intermediate data set to explain how the original string needs to split and processed, not necessary to follow that order as long as result is equivalent to Output_Final.jmp. Please kindly help me. Thanks in advance !

Re: Split string with certain delimiters and make a new row each element (each element will further split in to columns).

GR — Mon, 26 Apr 2021 02:20:40 GMT

Attaching Output_Final.jmp

Re: Split string with certain delimiters and make a new row each element (each element will further split in to columns).

txnelson — Mon, 26 Apr 2021 03:41:48 GMT

Here is a script that will create the data table you are asking for, with one exception. I am at a loss on how to construct your column E.

Names Default To Here( 1 );
dt = Data Table( "Input" );
dtOut = New Table( "Final Output",
	New Column( "A" ),
	New Column( "B", character ),
	New Column( "C" ),
	New Column( "D" ),
	New Column( "Data_1", character ),
	New Column( "Data_2", character ),
	New Column( "Data_3", character ),
	New Column( "Data_4", character ),
	New Column( "Data_5", character ),
	New Column( "Data_6", character ), 

);
For( i = 1, i <= N Rows( dt ), i++,
	If( i == 1,
		theRow = 0;
		theRow = N Rows( dtOut );
	);
	If( dt:Data_Column[i] != "X",
		dtOut << Add Rows( 3 );
		For( k = 1, k <= 3, k++,
			theRow++;
			dtOut:A[theRow] = dt:A[i];
			dtOut:B[theRow] = dt:B[i];
			dtOut:C[theRow] = dt:C[i];
			dtOut:D[theRow] = dt:D[i];
			dtOut:Data_1[theRow] = Substr( Word( k, dt:Data_Column[i], " " ), 1, 1 );
			dtOut:Data_2[theRow] = Substr( Word( 1, Word( k, dt:Data_Column[i], " " ), ":" ), 2 );
			dtOut:Data_3[theRow] = Word( 2, Word( k, dt:Data_Column[i], " " ), ":" );
			dtOut:Data_4[theRow] = Word( 3, Word( k, dt:Data_Column[i], " " ), ":" );
			dtOut:Data_5[theRow] = Word( 4, Word( k, dt:Data_Column[i], " " ), ":" );
			dtOut:Data_6[theRow] = Word( 5, Word( k, dt:Data_Column[i], " " ), ":" );
		);
	);
);

P.S. You have a typo in the data table column name called Data_Column.......it is incorrectly spelled Data_Coulmn

Re: Split string with certain delimiters and make a new row each element (each element will further split in to columns).

GR — Mon, 26 Apr 2021 04:50:52 GMT

Hi txnelson, it works perfect !
Regarding the column E - it is just need to be copied from the original table. I have added in "New Column( "E", character )," & "dtOut:E[theRow] = dt:E[i];" to copy column E as well. Really appreciate it, thanks alot !