Stacking multiple variables

Question

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!

txnelson · Answer

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

And convert it into the new form for the table

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

LauraC · Answer

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

statman · Answer

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

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