topic Re: split a column and create multiple multiple based on split using JSL in Discussions

split a column and create multiple multiple based on split using JSL

david_sam — Sat, 10 Jun 2023 23:24:32 GMT

Hello,

I need to read existing table and find the column of interest [say the column name is "data"].

the column "data" is numeric with "dot" delimited and have same length.

My goal is to open this table, read column "data" , remove the "dot" and create new columns [as per the length of value] and each column holds each value.

Orig table:

data

------

123.56.8.342

431.02.0.567

318.00.1.816

.....

expected output:

data c1 c2 c3 c4 c5 c6 c7 c8 c9

------------------------------------------------------------------------------

123.56.8.342 1 2 3 5 6 8 3 4 2

431.02.0.567 4 3 1 0 2 0 5 6 7

318.00.1.816 3 1 8 0 0 1 8 1 6

In my first attempt [trial1.jmp] , where I have 2 for loops [inner loop for creating cols, and other loop for # of rows], it is creating more columns [ie width of the data read * no, of rows].

in my second attempt [trail2.jmp], I am getting the right # of cols, but values static.

attached is my jsl's.

Appreciate the help, burning the midnight oil on this :(

Thanks,

David

Re: split a column and create multiple multiple based on split using JSL

jthi — Thu, 31 Dec 2020 08:03:55 GMT

I only checked the version 2 of .jsl and it was quite close.

I have attached modified version.

I modified the loop part mostly. There is most likely way to manage without Eval(Parse()) with expressions but I went with this solution this time. You were using data from variable clean_DATA which stays the same for all rows. Also if you need to have formulas you will have to use Eval(Parse()) (or expressions?) to keep formulas working in the datatable. If no formulas are needed I would go with << Set Each Value instead of Formula.

For(i = 1, i <= ncols_to_add, i++, // the || operator joins two strings
	Eval(Parse("dt << New Column(\!"c\!" || Char(i),Numeric, Continous, 
		Formula(Num(Substr(Regex(:data, \!"\D\!", \!"\!", GLOBALREPLACE ), "||char(i)||", 1 )));
	)));")); // put 1 bit in each column
);

With << Set Each Value the for loop would look like this:

For(i = 1, i <= ncols_to_add, i++, // the || operator joins two strings
	dt << New Column("c" || Char(i),Numeric, Continous, 
		<< Set Each Value(Num(Substr(Regex(:data, "\D", "", GLOBALREPLACE ), i, 1 )));
	)
); // put 1 bit in each column

Edit:

Good post on using Eval Insert, Eval Expr, Parse and Substitute with formulas https://community.jmp.com/t5/JSL-Cookbook/Insert-one-expression-into-another-using-Eval-Insert-Eval-Expr/ta-p/48998

Re: split a column and create multiple multiple based on split using JSL

txnelson — Thu, 31 Dec 2020 12:31:20 GMT

Here is how I would accomplish your request. The Substitute() function allows for the formula to have the actual value of variable "i" to be in the formula rather than a reference to variable "i"

Names Default To Here( 1 );

dt = Current Data Table();

col = Column( dt, "DATA" );

colLength = Length( col[1] );

For( i = 1, i <= colLength, i++,
	If( Substr( col[1], i, 1 ) != ".",
		Eval(
			Substitute(
					Expr(
						dt << New Column( "c" || Char( i ), Numeric, Continous, Formula( Num( Substr( :data, __i__, 1 ) ) ) )
					),
				Expr( __i__ ), i,

			)
		)
	)
);

Re: split a column and create multiple multiple based on split using JSL

david_sam — Fri, 01 Jan 2021 00:04:11 GMT

Thank Jamo for letting me know what I was missing. I knew I was close ;)

dint know about about "set each value" built in function.

yup, everything works !

Thanks,

David

Re: split a column and create multiple multiple based on split using JSL

david_sam — Fri, 01 Jan 2021 00:12:02 GMT

Thanks Jim.

you have elegant solution.

based on your proposed method, I noticed see the col name are not continues, [ie. skips the Col # when ever "dot" is encountered]; little column names massaging is needed [which I took care].

Question:

Would you suggest to have column have formula and use eval/substitute or do it with "set each value" as Jamo suggested from the performance standpoint?

Thanks,

David

Re: split a column and create multiple multiple based on split using JSL

txnelson — Fri, 01 Jan 2021 05:20:22 GMT

There are advantages to both methods. The formula method will allow for the addition of new rows and the changing of the Date values, and the c1....cn columns will automatically adjust. The advantage of the set each value will provide static values, which can prove to be more efficient because the formulas do not have to run multiple times.