38036_en-US

liliconcom

Stacking based on a CSV column

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

 

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

 

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?

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.

Re: Stacking based on a CSV column

Thanks Jeff, I should have looked this up first.

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.

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.

 

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