737626_en-US

liliconcom

How to create a data table of 3 columns per row from numeric string

I'm new to JSL. I have a text string that contains values separated by a "#". The numbers are to be grouped in 3 columns

I know I can write a code to put the values in columns being separated by the "#", but how do I then create a table of 3 values in each row, until the table is complete. I know I'll also probably need to remove the first "#" to prevent issues as first 3 numbers would be in 2nd to 4th column.

 

For example:

#1#0.93#0.99#2#1.92#1.99#3#2.9#2.99#4#3.86#3.98#5#4.85#4.98

can go to this:

1

0.93

0.99

2

1.92

1.99

3

2.9

2.99

4

3.86

3.98

5

4.85

4.98

But how do I write the JSL code to get it to this:

Value1	Value2	Value3
1	0.93	0.99
2	1.92	1.99
3	2.9	2.99
4	3.86	3.98
5	4.85	4.98

 

In this example there are 5 rows in table but the number of rows could vary.

Data Blending and CleanupRe: How to create a data table of 3 columns per row from numeric string

Use Text To Columns to get this

Then stack your data with multiple series stack

and finally remove extra columns, rename column headers and fix column data/modelling types

JMP created script is a good starting point for the final JSL

// Text to columns
Data Table("Untitled 6") << Text to Columns(columns(:Column 1), Delimiters("#"));


// Stack data table
// → Data Table("Untitled 11")
Data Table("Untitled 6") << Stack(
	columns(
		:Column 1 1, :Column 1 2, :Column 1 3, :Column 1 4, :Column 1 5, :Column 1 6,
		:Column 1 7, :Column 1 8, :Column 1 9, :Column 1 10, :Column 1 11,
		:Column 1 12, :Column 1 13, :Column 1 14, :Column 1 15
	),
	Source Label Column("Label"),
	Stacked Data Column("Data"),
	Drop All Other Columns(1),
	Number of Series(3),
	Output Table("Untitled 11.jmp")
);


// Delete columns
Data Table("Untitled 11") << Delete Columns(:Label, :Label 2, :Label 3);


// Recode column names
Local({dt = Data Table("Untitled 11"), names},
	names = Recode(
		dt << Get Column Names(String),
		{Map Value(
			_rcOrig,
			{"Data", "Value1", "Data 2", "Value2", "Data 3", "Value3"},
			Unmatched(_rcNow)
		)}
	);
	For Each({name, i}, names, Column(dt, i) << Set Name(name));
);


// Standardize column attributes
Local({old dt = Current Data Table()},
	Current Data Table(Data Table("Untitled 11"));
	For Each({col, index}, {:Value1, :Value2, :Value3},
		col << Data Type(Numeric, Format("Best", 12)) <<
		Set Modeling Type("Continuous")
	);
	Current Data Table(old dt);
);

Re: How to create a data table of 3 columns per row from numeric string

Here is one approach

Names Default To Here( 1 );
theString = "#1#0.93#0.99#2#1.92#1.99#3#2.9#2.99#4#3.86#3.98#5#4.85#4.98";

dt = New Table( "Start",
	add rows( 1 ),
	New Column( "string", character, set each value( theString ) )
);
dt << Text To Columns( delimiter( "#" ), columns( :string ) );
dt << delete columns( :string );
colNames = dt << get column names( string );
For Each( {col}, colNames,
	Column( dt, col ) << Data Type( Numeric, Format( "Best", 12 ) ) <<
	Set Modeling Type( "Continuous" )
);

dt << Stack(
	columns( colNames ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Value" ),
	Number of Series( 3 ),
	Output Table( "Final" )
);

dtFinal = Current Data Table();
dtFinal << delete columns( {:Label, :Label2, :Label3} );
dtFinal:value << set name( "Value 1" );

close( dt, nosave);