Choose Language Hide Translation Bar
jdkurtis
Community Trekker

Table rearrangement challenge

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 IDThaw DateThaw ID
17/28/0343
27/28/0312
37/28/0332
47/28/0354
49/28/0378
411/28/0395
57/28/0358
59/28/0380
67/28/0360

 

Desired Output Table:

Sample IDThaw Date 1Thaw ID 1Thaw Date 2Thaw ID 2Thaw Date 3Thaw ID 3
17/28/0343    
27/28/0312    
37/28/0332    
47/28/03549/28/037811/28/0395
57/28/03589/28/0380  
67/28/0360    
0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
jerry_cooper
Staff (Retired)

Re: Table rearrangement challenge

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.

View solution in original post

0 Kudos
4 REPLIES 4
Highlighted
txnelson
Super User

Re: Table rearrangement challenge

Once you have done your Summary on the data table, then you can use

     Tables==>Split

and Split By Thaw Date, Group on Sample ID, and having Thaw ID as the Split Columns

Jim
Highlighted
jdkurtis
Community Trekker

Re: Table rearrangement challenge

thanks so much... this got me very, very close to the desired output.. but the reply below (from jerry_cooper) got me all the way there...

I very much appreciate you taking the time to think about the problem and providing your input....

best,
jake
0 Kudos
Highlighted
jerry_cooper
Staff (Retired)

Re: Table rearrangement challenge

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.

View solution in original post

0 Kudos
Highlighted
jdkurtis
Community Trekker

Re: Table rearrangement challenge

Thank you so much for your answer- it worked perfectly...

I am not using this for an analysis- rather to reorganize a large database of stored samples to upload into a commercial freezer database that requires a specific format... I always turn to JMP for this type of work, but couldn't figure this one out.. I will certainly read up on the Col Rank function as this was the step I had not considered...

best,
jake

0 Kudos