stack multiple series data with single label and keep original title

Question

I have a table like this:

ListApple redApple yellowBanana redBanana yellowa1112131b2122232c3132333

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:

ListAppleBananagroupa121reda1131yellowb222redb1232yellowc323redc1333yellow

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

txnelson · Answer

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

Jeff_Perkinson · Answer

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.

You'll end up with this:

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.

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

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

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