Choose Language Hide Translation Bar
Highlighted
CC86
Level I

rearrange dataset help with script

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 timeMedication
AHgb1210/5/16 6:00 AMtylenol
ACr110/5/16 6:00 AMtylenol
AK410/5/16 6:00 AMtylenol
BK3.711/6/02 4:00 AMaspirin
BK4.511/6/02 7:00 AMaspirin
BCr1.211/6/02 7:00 AMaspirin
BHgb1411/6/02 7:00 AMaspirin
CCr1.51/15/09 6:30 AMvitamin D
CHgb121/15/09 6:30 AMvitamin D
CHgb91/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 timeMedication
A1210/5/16 6:00 AM  110/5/16 6:00 AM  410/5/16 6:00 AM  tylenol
B     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!!!

2 REPLIES 2
Highlighted
txnelson
Super User

Re: rearrange dataset help with script

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

labvalue.PNG

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

Jim
Highlighted
CC86
Level I

Re: rearrange dataset help with script

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