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

[#1]

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

[#2 · Accepted solution]

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-...

[#3]

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

[#4 · Accepted solution]

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,

			)
		)
	)
);

[#5]

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

[#6]

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.