nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Stacking based on a CSV column

jamiesupica0

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?

Accepted answers

txnelson

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

All comments

txnelson

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

Jeff_Perkinson

@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.

jamiesupica0

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.

jamiesupica0

Thanks Jeff, I should have looked this up first.

txnelson

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.