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:
| ID | Lab | Lab Value | Lab date and time | Medication |
| A | Hgb | 12 | 10/5/16 6:00 AM | tylenol |
| A | Cr | 1 | 10/5/16 6:00 AM | tylenol |
| A | K | 4 | 10/5/16 6:00 AM | tylenol |
| B | K | 3.7 | 11/6/02 4:00 AM | aspirin |
| B | K | 4.5 | 11/6/02 7:00 AM | aspirin |
| B | Cr | 1.2 | 11/6/02 7:00 AM | aspirin |
| B | Hgb | 14 | 11/6/02 7:00 AM | aspirin |
| C | Cr | 1.5 | 1/15/09 6:30 AM | vitamin D |
| C | Hgb | 12 | 1/15/09 6:30 AM | vitamin D |
| C | Hgb | 9 | 1/15/09 9:30 AM | vitamin 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.
| ID | Hgb1 | Lab date and time | Hgb2 | Lab date and time | Cr1 | Lab date and time | Cr2 | Lab date and time | K1 | Lab date and time | K2 | Lab date and time | Medication |
| A | 12 | 10/5/16 6:00 AM | | | 1 | 10/5/16 6:00 AM | | | 4 | 10/5/16 6:00 AM | | | tylenol |
| B | | | | | | 11/6/02 7:00 AM | | | 3.7 | 11/6/02 4:00 AM | 4.5 | 11/6/02 7:00 AM | aspirin |
I would appreciate any help.
Thank you!!!
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=""></GET>Thank you so much! once I made my ID variable not an expression this worked perfectly!
