topic Re: How do I parse data to column then stack them with multiple special characters in Discussions

How do I parse data to column then stack them with multiple special characters

ducthinh279 — Sun, 07 Apr 2024 09:38:28 GMT

I have a raw data need to text to column with many special character, then stack them. Could you please help provide solution by JSL script for generic case. Please help to refer my excel file for detail

Re: How do I parse data to column then stack them with multiple special characters

txnelson — Sun, 07 Apr 2024 11:27:21 GMT

Here is the script that will take the original data table and split out a target column and then stack the results. Given that some of the split out columns are made up of just string data, and some appear to be numeric values, the stacking of all of the columns does not seem to be what you probably want.

Names Default To Here( 1 );
dt = Current Data Table();

targetCol = "domain_frequency_core";

// Text to columns
Data Table( "Book1 (4)_Sheet1" ) <<
Text to Columns( columns( Column( targetCol ) ), Delimiters( "@" ) );

// Get list of all target columns except original column
stackList = Filter Each( {name}, dt << get column names( string ),
	Contains( name, targetCol ) & name != targetCol
);

// Stack the new columns
dt << Stack(
	columns( stackList ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" ),
	Output Table( "Final Stacked Data Table" )
);

Please take the time to study the script. All of the functions and platforms used are documented in the Scripting Index and in the JMP Help screens.

Re: How do I parse data to column then stack them with multiple special characters

ducthinh279 — Thu, 11 Apr 2024 07:12:07 GMT

Hi Jim,

Look like you misunderstand my data, the code you were wrote is the output.

Kindly help to refer sheet "Raw data"

Output expected in sheet "Data after parse"

Thank you in advance.

Re: How do I parse data to column then stack them with multiple special characters

txnelson — Thu, 11 Apr 2024 10:29:33 GMT

Here is my first pass script. The approach should give you what you need to see what might need to be adjusted with your complete data table

Names Default To Here( 1 );
dt = Current Data Table();

// Create the new columns
dt << New Column( "domain_frequency_core", character );
dt << New Column( "Data" );

// Create the static part for the column domain_frequency_core
startString = "Data@" || Word( 1, :string_result[1], ":" );

// Read into a memory variable the entire string, minus the startString component
theString = Word( 2, :string_result[1], ":_" );

// Initialize the looping variables
outerLoop = 1;
theRow = 0;

// Loop through the major groupings in the data
While( Word( outerLoop, theString, "%" ) != "",
	// Strip off the subpart of the string
	majorWord = Word( outerLoop, theString, "%" );
	// Get the second component of the start string
	startStringSuffix = "@" || word(1,majorWord,"^") || "@";
	
	// Initialize the inner loop value, starting with word 2
	innerLoop=2;
	// Loop through the subgroup finding each numeric component
	While( word(innerLoop, majorWord, "^V") != "",
		// Add a new row
		if(theRow !=0, dt<<add rows(1));
		theRow++;
		// Add the new column values
		:unit[theRow]=:unit[1];
		:domain_frequency_core[theRow]= startString || startStringSuffix || char(innerLoop-2) ;
		:Data[theRow]=num(word(innerLoop, majorWord, "^V"));
		innerLoop++;
	);
	outerLoop++;
);

Re: How do I parse data to column then stack them with multiple special characters

ducthinh279 — Fri, 19 Apr 2024 08:25:24 GMT

Thanks Jim a lot for your solution.

Thinh