Subscribe Bookmark RSS Feed

Stacking based on a CSV column

jamiesupica0

Community Trekker

Joined:

Jul 15, 2016

I have a list of unique item numbers, each with a corresponding csv list of (20+) categories.

before.PNG

 

Is there a way to stack based on each csv to get the resulting table:

after.PNG

 

My current workaround is to use the "Text to Columns" in Excel, import the file, and then stack based on the columns.  Is there an easy way to do this programatically in JMP?

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Here is a little script that will do the deed

Names Default To Here( 1 );
dt = Current Data Table();
dtstack = New Table( "stacked", New Column( "item" ), New Column( "Category List", character ) );

For( i = 1, i <= N Rows( dt ), i++,
	cnt = 1;
	While( Word( cnt, dt:category list[i], ", " ) != "",
		dtstack << add rows( 1 );
		dtstack:item[N Rows( dtstack )] = dt:item[i];
		dtstack:Category List[N Rows( dtstack )] = Word( cnt, dt:category list[i], ", " );
		cnt = cnt + 1;
	);
);
Jim
5 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Here is a little script that will do the deed

Names Default To Here( 1 );
dt = Current Data Table();
dtstack = New Table( "stacked", New Column( "item" ), New Column( "Category List", character ) );

For( i = 1, i <= N Rows( dt ), i++,
	cnt = 1;
	While( Word( cnt, dt:category list[i], ", " ) != "",
		dtstack << add rows( 1 );
		dtstack:item[N Rows( dtstack )] = dt:item[i];
		dtstack:Category List[N Rows( dtstack )] = Word( cnt, dt:category list[i], ", " );
		cnt = cnt + 1;
	);
);
Jim
jamiesupica0

Community Trekker

Joined:

Jul 15, 2016

Thanks very much Jim, that did it!

 

I did have to remove the space in the ", ".  My data set uses only a comma with no space.

Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

@txnelson gave you the programmatic solution you asked for.

 

If you want to do it interactively (a la Excel), JMP has Text to Columns in the Col -> Utilities menu. If you've got an earlier version of JMP without Text to Columns you can download an add-in from the File Exchange.

 

JMPScreenSnapz081.png

-Jeff
jamiesupica0

Community Trekker

Joined:

Jul 15, 2016

Thanks Jeff, I should have looked this up first.

txnelson

Super User

Joined:

Jun 22, 2012

I thank Jeff for the heads up on the Text to Columns too.  Also, I sent to JMP a request to support Tables==>Stack to directly support multiple response columns.  That would also have been a nice solution if that support was in place.

Jim