Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
jasongao
Level II

stack multiple series data with single label and keep original title

I have a table like this: 

ListApple redApple yellowBanana redBanana yellow
a1112131
b2122232
c3132333

I would like to stack the data with multiple series (here is apple and banana), but only want 1 label for all series (here is red and yellow). I also want to keep the left of the title (here is apple and banana). The output is like:

ListAppleBananagroup
a121red
a1131yellow
b222red
b1232yellow
c323red
c1333yellow

I tried stack function but I think this needs some special script. Thank you so much! 

2 REPLIES 2
Highlighted
Jeff_Perkinson
Community Manager Community Manager

Re: stack multiple series data with single label and keep original title

This can be done in three operations with no scripting.

 

First, use Tables->Stack to stack your four columns (Apple red, Apple yellow, Banana red, Banana yellow) into a single column.

2020-02-22_08-55-38.950.png

You'll end up with this:

2020-02-22_08-58-15.533.png

Now, use Cols->Utlities->Text to Columns to separate the fruit from the color in your Label column. Put a space in the delimiter field.

2020-02-22_09-00-45.117.png

You'll end up with two new columns. Rename them Fruit and Color. You can delete the Label column.

2020-02-22_09-02-54.120.png

Now use Tables->Split to split the Data column by Fruit.

2020-02-22_09-03-37.114.png

Now you'll get the data table you want with, an Apple column and Banana column.

2020-02-22_09-06-38.159.png

 

-Jeff
Highlighted
txnelson
Super User

Re: stack multiple series data with single label and keep original title

If you Stack the data, then create a new column for the Fruit, and another one for the Color, you can then Split the data into the required data table.

The script below illustrates how to accomplish this.  However, doing the steps interactively is how I build the script, so you can easily repeat the interactive steps.

Names Default To Here( 1 );

// Create the sample data table
dt = New Table( "Example",
	Add Rows( 3 ),
	New Script(
		"Source",
		Data Table( "Untitled 67" ) << Split(
			Split By( :Column 2 ),
			Split( :Column 1 ),
			Sort by Column Property
		)
	),
	New Column( "List", Character, "Nominal", Set Values( {"a", "b", "c"} ) ),
	New Column( "Apple red", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3] ) ),
	New Column( "Apple yellow", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [11, 12, 13] ) ),
	New Column( "Banana red", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [21, 22, 23] ) ),
	New Column( "Banana yellow", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [31, 32, 33] ) )
);

// Stack the columns
dtStack = dt << Stack(
	columns( :Apple red, :Apple yellow, :Banana yellow, :Banana red ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" )
);

// Create 2 New Columns separating the fruit from the color
dtStack << New Column("Fruit", character, formula(word(1,:Label," ")));
dtStack << New Column("Color", character, formula(word(2,:Label," ")));

// Split the data back into the required new columns
dtFinal = dtStack << Split(
	Split By( :Fruit ),
	Split( :Data ),
	Remaining Columns( Drop( :Label ) ),
	Sort by Column Property
);

banana.PNG

Jim
Article Labels

    There are no labels assigned to this post.