cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
The Discovery Summit 2025 Call for Content is open! Submit an abstract today to present at our premier analytics conference.
Choose Language Hide Translation Bar
View Original Published Thread

Stacking multiple variables

LauraC
Level I

I have a large dataset from a long session in which many tasks were conducted for each subject.  Many of the tasks involved the same 4-5 types and multiple ratings and behavioral outcomes from those types.  However, each outcome variable is named a bit differently in terms of how the "type" is reflected in the name.  I am trying to stack across "types" for each outcome, and combine in a single spreadsheet.  So far I have been able to stack a single variable, and I was then able to join two sheets in which different variables had been stacked by merging and matching subject ID and a recoded "type" column.  Is this the correct sequence?  Stack variable, save resulting table, stack another from another spreadsheet, save resulting table, join/merge tables, and then stack and merge more?  Can 5 tables be merged/matched at the same time?  I tried to add a new stacked variable to the result of two merged tables and the result seemed incorrect.  Thanks!

3 REPLIES 3
statman
Super User


Re: Stacking multiple variables

Have you tried multiple series stack? You can stack many columns. 

Screenshot 2024-12-11 at 1.39.01 PM.jpg

 

https://www.jmp.com/support/help/en/19.0/?os=mac&source=application#page/jmp/stack-columns-in-data-t...

"All models are wrong, some are useful" G.E.P. Box
LauraC
Level I


Re: Stacking multiple variables

I'm not clear that that function will work. Right now my data are analogous to:

Task 1 arm; Task 1 leg; Task 2 arm; Task 2 leg; Task 3 arm; Task 3 leg; Task 3 face
Except that the variable names diverge from the format above and aren't all consistent. I want to stack the Task 1 values, and also separately the Task 2 values, and also separately the Task 3 values, but all joined by the Body Site variable:
Arm       Task 1 value   Task 2 value.  (no data)

Leg       Task 1 value   Task 2 value.   (no data)

Face     Task 1 value   Task 2 value    Task 3 value

txnelson
Super User


Re: Stacking multiple variables

A script can be written that will test for the different variations in a column name, and then change the name to the standard form.  

 

Once in the standard form, as you describe the names, the following script takes the standard form

txnelson_0-1733972080882.png

And convert it into the new form for the table

txnelson_1-1733972149451.png

names default to here(1);
dt = new table("Example",
	add rows(20),
	New column("Task 1 arm", set each value(random integer(1,100))),
	New column("Task 1 leg", set each value(random integer(1,100))),
	New column("Task 2 arm", set each value(random integer(1,100))),
	New column("Task 2 leg", set each value(random integer(1,100))),
	New column("Task 3 arm", set each value(random integer(1,100))),
	New column("Task 3 leg", set each value(random integer(1,100))),
	New column("Task 3 face", set each value(random integer(1,100)))
);

dt << new column("Row", set each value(Row()));

dtStack = dt << Stack(
	columns(
		:Task 1 arm, :Task 1 leg, :Task 2 arm, :Task 2 leg, :Task 3 arm, :Task 3 leg,
		:Task 3 face
	),
	Output Table( "Stack" )
);

dtStack << new column("Body Part", character, formula( word(-1, :label, " ")));
dtStack << new column( "Task", character, formula( substr(:Label, 1 ,length(:label)-length(:body part)-1)));

dtFinal = dtStack << Split(
	Split By( :Task ),
	Split( :Data ),
	Group( :Body Part, :row ),
	Output Table( "Final" ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);

dtFinal << Sort( By(:row, :Body Part), Replace Table(1));
dtFinal << delete columns(:row);

dt << delete columns(:row);

close( dtStack, nosave );
Jim