How do I Transpose data from Long to Wide format?

jswislar

I'm a new JMP user.

My data is currently in long format. In other words, subjects have multiple lines of data each. However, I need the data to be in wide format for survival analysis - 1 line of data per subject. When I try to use Transpose, JMP tells me "Cannot transpose. Data columns have different data types". The variables are a mix of continiuous and nominal. So, how am I supposed to do this? I was able to convert a different data set from wide to long without difficulty, but can't seem to do the reverse.

 

Current data is set up like this (fake data):

 

ID     Var1     Var2    Outcome

001   1          1          0

001   1          1          0

001   1          0          1

002   0          1           0

002   0          1           1

003   1          1            0

 

I want it like this:

ID     Var1_time1  Var2_time1 Var1_time2 Var2_time2.... Outcome_timex

001   1                  1                  1                  1                     1

002   0                  1                  0                  1                      1

003   1                  1                  .                    .                      0       

 

Thanks!                   

Jeff_Perkinson

@ron_horne has the right solution but perhaps some pictures will help.

 

Tables -> Split will rearrange the data the way you want but it requires a column that identifies the new columns. In your case you want to create columns for each row within ID so you need a column which counts rows within ID. @ron_horne called this column Index.

 

You'll also need to make sure that your data is sorted by ID for this to work.

 

Here's the data that you presented in your first post in the form of a JMP data table.

 

 

To this data we need add the Index column. @ron_horne used a different formula for his Index column but I like something a little more straightforward. 

The formula above is:

 

If( :ID != Lag( :ID, 1 ), 1, Lag( :Index, 1 ) + 1)

 

In prose this says that if the ID value in the current row isn't the same as the ID in the previous row (i.e. Lag ( :ID, 1)) then put a 1 in the Index column, otherwise take the value of the Index column from the previous row (i.e. Lag (:Index, 1))

 

As you can see, the Index column in the data table now counts the rows within ID.

Now we can use Tables->Split to split your Var1, Var2 and Outcome columns by Index and group the rows by ID.

 

That will give you this data table, which I think is what you want. It has new columns for each row within ID and one row per ID.

It might be easier to see a simpler example where we don't have the create the Split By column. Here's a video that shows that.

 

 

Let me know if I've missed the mark or you're still confused.