cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
ducthinh279
Level II

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

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

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++;
);

txnelson_0-1712831359209.png

 

Jim

View solution in original post

4 REPLIES 4
txnelson
Super User

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

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.

Jim
ducthinh279
Level II

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

Hi Jim,

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

Kindly help to refer sheet "Raw data"

ducthinh279_0-1712818894475.png

Output expected in sheet "Data after parse"

ducthinh279_1-1712818955541.png

Thank you in advance.

txnelson
Super User

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

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++;
);

txnelson_0-1712831359209.png

 

Jim
ducthinh279
Level II

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

Thanks Jim a lot for your solution.

Thinh