How to unstack a JMP table with a many to one structure

[#1]

Hi,

I have the following "stacked" data table in JMP;

Test ID	Sample Type	Result
Test_ID_1	Sample	10
Test_ID_1	Sample	12
Test_ID_1	Sample	11
Test_ID_1	Sample	14
Test_ID_1	Sample	9
Test_ID_1	Control	11
Test_ID_2	Sample	9
Test_ID_2	Sample	8
Test_ID_2	Sample	10
Test_ID_2	Control	14
Test_ID_3	Sample	13
Test_ID_3	Control	15
Test_ID_4	Sample	9
Test_ID_4	Sample	7
Test_ID_4	Control	12

I would like to generate the following table from the above table by unstacking;

Test ID	Sample Type = Sample	Sample Type = Control
Test_ID_1	10	11
Test_ID_1	12	11
Test_ID_1	11	11
Test_ID_1	14	11
Test_ID_1	9	11
Test_ID_2	9	14
Test_ID_2	8	14
Test_ID_2	10	14
Test_ID_3	13	15
Test_ID_4	9	12
Test_ID_4	7	12

The stacked data contains a varied number of samples for a given control.  Sometimes there is a 1-to-1 correlation between sample and control but more often there is a many samples to one control. The number of samples can vary from one to n but there is always just one control.  I am using JMP 16.0.1.

Thanks,

Tim

[#2 · Accepted solution]

Here is a little script that converts your simple example table into the new format you want.  It uses a couple of subsets along with an Update to accomplish the task.

Names Default To Here( 1 );
dt = Current Data Table();
dt << select where( :Sample Type == "Sample" );
dtFinal = dt << subset(
	selected rows( 1 ),
	columns( {"Test ID", "Result"} ),
	Output Table( "Final" )
);
dtFinal:Result << set name( "Sample Type = Sample" );
dt << invert row selection;
dtTemp = dt << subset( invisible,
	selected rows( 1 ),
	columns( {"Test ID", "Result"} )
);
dtTemp:Result << set name( "Sample Type = Control" );
dtFinal <<Update(
	With( dtTemp ),
	Match Columns( :Test ID = :Test ID )
);

close( dtTemp, nosave );

txnelson_0-1669341369151.png

[#3 · Accepted solution]

You can also try to create grouping column

Col Cumulative Sum(1, :Test ID, :Sample Type)

jthi_0-1669361444208.png

Use that with Split

jthi_1-1669361476446.png

jthi_2-1669361485726.png

And then select one row from control colum and fill with previous value

jthi_3-1669361523548.png

and cleanup (remove order column, re-order columns, rename if needed)

jthi_4-1669361565167.png

This works in this fairly simple case, but it is also fairly easy to loose data when using JMP's split platform so be careful with (order column tries to prevent this).