nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

david_sam

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

output_of_trail_2.jmp

trail_2.jsl

output_of_trial_1.jmp

Trial_1.jsl

orig_data.jmp

Accepted answers

jthi

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

trail_2_jthi_mod.jsl

txnelson

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,

			)
		)
	)
);

All comments

jthi

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

I have attached modified version.

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

trail_2_jthi_mod.jsl

txnelson

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,

			)
		)
	)
);

david_sam

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

david_sam

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

txnelson

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.