Choose Language Hide Translation Bar
jamiesupica0
Community Trekker

Stacking based on a CSV column

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?

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Stacking based on a CSV column

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

View solution in original post

5 REPLIES 5
txnelson
Super User

Re: Stacking based on a CSV column

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

View solution in original post

jamiesupica0
Community Trekker

Re: Stacking based on a CSV column

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.

0 Kudos
Jeff_Perkinson
Community Manager Community Manager

Re: Stacking based on a CSV column

@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

Re: Stacking based on a CSV column

Thanks Jeff, I should have looked this up first.

0 Kudos
txnelson
Super User

Re: Stacking based on a CSV column

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