Table rearrangement challenge

jdkurtis

Hi-

I am trying to rearrange a table and am struggling to get the desired output. I have tried using the Summary Table, Join or Update menu functions in various permutations but have failed... any help would be extreamly appreciated- the actual table has 80,000 rows, so really can't do this manually...

 

I know there are no more than 10 thaw dates/thaw IDs for any given Sample ID in the real data set.

 

The Summary Table function quickly gets me to a table with each Sample ID listed only once (desired)- but I cannot figure out how to get the additional Thaw Date and Thaw ID columns created and populated..

 

thx

jake

 

Starting Table:

Sample ID	Thaw Date	Thaw ID
1	7/28/03	43
2	7/28/03	12
3	7/28/03	32
4	7/28/03	54
4	9/28/03	78
4	11/28/03	95
5	7/28/03	58
5	9/28/03	80
6	7/28/03	60

 

Desired Output Table:

Sample ID	Thaw Date 1	Thaw ID 1	Thaw Date 2	Thaw ID 2	Thaw Date 3	Thaw ID 3
1	7/28/03	43
2	7/28/03	12
3	7/28/03	32
4	7/28/03	54	9/28/03	78	11/28/03	95
5	7/28/03	58	9/28/03	80
6	7/28/03	60

jerry_cooper

Hi @jdkurtis ,

It might be helpful to know what analysis you're hoping to do, as it is very likely that your data are already in an analysis-ready format. Most JMP platforms play very nicely with "tall" or "stacked" data. However, the easiest way to do what you are asking is to create a new formula column containing the rank for each date within Sample ID:

Col Rank( :Thaw Date, :Sample ID )

 

Then just use the Tables->Split function with the following roles defined:

Split By: The new formula column

Split Columns: Thaw ID, Thaw Date

Group: Sample ID

 

This should give you what you're after. Hope this helps.