rearrange dataset help with script

Question

Hello, I am brand new to Jmp and using the script to try to rearrange a dataset. Currently I have a dataset that is like follows:

IDLabLab ValueLab date and timeMedicationAHgb1210/5/16 6:00 AMtylenolACr110/5/16 6:00 AMtylenolAK410/5/16 6:00 AMtylenolBK3.711/6/02 4:00 AMaspirinBK4.511/6/02 7:00 AMaspirinBCr1.211/6/02 7:00 AMaspirinBHgb1411/6/02 7:00 AMaspirinCCr1.51/15/09 6:30 AMvitamin DCHgb121/15/09 6:30 AMvitamin DCHgb91/15/09 9:30 AMvitamin D

I would like each ID to be a single row with columns for each individual lab. For any ID there may be duplicates of different labs, but I would like to keep all values for each lab and make them a separate column. I was thinking of making a count variable after sorting by ID and lab so that I could combine and make a variable that would be Hgb1, Hgb2, but I do not know how to do this. I also do not know how to transpose as I am picturing.

IDHgb1Lab date and timeHgb2Lab date and timeCr1Lab date and timeCr2Lab date and timeK1Lab date and timeK2Lab date and timeMedicationA1210/5/16 6:00 AM  110/5/16 6:00 AM  410/5/16 6:00 AM  tylenolB     11/6/02 7:00 AM  3.711/6/02 4:00 AM4.511/6/02 7:00 AMaspirin

I would appreciate any help.

Thank you!!!

CC86 · Answer

Thank you so much! once I made my ID variable not an expression this worked perfectly!

txnelson · Answer

This took a bunch of thinking, and a little scripting, but I think it will give you at least a good start on what you need.  I wasn't sure what you really wanted to do with the medication values, so I added them to each lab grouping....you may want to change that.  Also, JMP does not like columns with the same names, so I left the names with unique names

names default to here(1);
dt=current data table();

// Add a rowNum column to capture the original data table order
dt << New Column("rowNum", formula(Row()));
dt:rowNum << delete formula;

// Order the data so that a count column can be calculated
dt << sort(by(:Lab, :ID), order(ascending,ascending),replace table(1));

// Add the count value, to count the number of duplicate labs for each ID
dt << new column("count", formula(If( Row() == 1, mycount = 1 );
If( Lag( :ID ) == :ID & Lag( :Lab ) == :Lab,
	mycount++,
	mycount = 1
);
mycount;));

// Split the data into columns
dtSplit = dt << Split(
	Split By( :Lab, :count ),
	Split( :Lab Value, :Lab date and time, :Medication ),
	Group( :ID ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);

// Clean up the original data table
dt << sort( by(:rowNum),order(ascending), replace table(1));
dt << delete columns({"count", "rowNum"});

// Order the columns
// Get the list of columns that start with "Lab Value", which
// will be used to order the columns based on them
labValueList = {};
for(i=1,i<=ncols(dtSplit),i++,
	If(uppercase(left(column(i)<<get name,9))=="LAB VALUE",
		insert into(labValueList, column(i)<<get name)
););

// Loop through the Lab Value columns and add the correct columns 
// following each
for(i=1,i<=nitems(labValueList),i++,
	key = substr(labValueList[i],11);
	dtSplit << goto (column( "Medication " || key));
	dtSplit << Move Selected Columns( After(column(labValueList[i])));
	dtSplit << goto (column( "Lab date and time " || key));
	dtSplit << Move Selected Columns( After(column(labValueList[i])));
	dtSplit << goto (column( "Lab date and time " || key));
	dtSplit << Move Selected Columns( After(column(labValueList[i])));
);