Stacking multiple columns, and adding repeating values to new columns

Question

Hello folks

I have a original set of data with first row as sample/location, and next rows shows values corresponding to specific iterations.

I'd like to change this data, stacking the values and creating the new column showing Sample/Location separately.

This is what I'd like to have: repeating iteration/sample/location for each values.

Alternatively, I'd like to have this; if separating the Sample/Location is difficult:

I' tried to find a way with summary table from UI, but nothing seems to be working.

Attaching the excel data file.

250417_Data.xlsx

jthi · Accepted Answer

Stack your data

Use First Word (and Last Word) quick formulas with slight modifications to create new columns

Modify Last Word to use "-" instead of default value which is " "

You can also add trim whitespace to clean the last word a bit

Trim Whitespace(Word(-1, :Label, "-"))

Is this what you are looking for? (also for the future, I would suggest sharing your data as JMP tables to avoid all sorts of issues excel files can potentially create)

jthi · Accepted Answer

And for modifying the data, you can also take a look at Recode platform (  Using JMP > Enter and Edit Your Data > Restructure Data > Recode Data in a Column ). And in some cases you can also just use Text to Columns

and of course you can combine it with Recode if necessary. JMP is also able to write script for you for this whole process either as a workflow or as a script in enhanced log

hogi · Accepted Answer

prepare some data:

//prepare: 
Names Default to Here(1);
dt0 = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt = dt0 << Split(
	Split By( :age, :sex ),
	Split( :height ),
	Remaining Columns( Drop All )
);
dt << new column ("sample", set each value(row()));

Now, let's stack it:

- use Eliminate missing rows( 1 ) to get rid of empty entries

- use text to column to split the 2 grouping variables:

if you prefer JSL:

// create stacked table
stacked = dt  <<
Stack(
	columns(
		:"12 F"n, :"12 M"n, :"13 F"n, :"13 M"n, :"14 F"n, :"14 M"n, :"15 F"n,
		:"15 M"n, :"16 F"n, :"16 M"n, :"17 F"n, :"17 M"n
	),
	Stack By Row( 0 ),
	Eliminate missing rows( 1 )
);
stacked << Text to Columns( columns( :Label ), Delimiters( " " ) );