topic Re: How do I Transpose data from Long to Wide format? in Discussions

How do I Transpose data from Long to Wide format?

jswislar — Thu, 17 Nov 2016 15:07:22 GMT

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 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!

Re: How do I Transpose data from Long to Wide format?

txnelson — Thu, 17 Nov 2016 15:15:41 GMT

What you want to do is to use
Tables==>Split
You can Split By ID and specify var1,var2 and outcome as the Split Columns

Re: How do I Transpose data from Long to Wide format?

ron_horne — Thu, 17 Nov 2016 17:15:30 GMT

Jim is right. Yet, if you are doing this just to produce summaries of the data per ID you can save this step by using Tables >>> summary.

best,

Ron

Re: How do I Transpose data from Long to Wide format?

jswislar — Thu, 17 Nov 2016 19:42:39 GMT

Thanks, Jim. That partly worked. However, instead of getting one row for each sbject ID (n=15,000), I ended up with 14 rows of data and each variable name has the SubjectID appended to it.

My data are organized SubjectID. Within that, subjects can have multiple lines of data (treatment codes) for each visit. In long format, there are 1-13 lines of data per subject.

Should multiple "split by" variables be used? Should "group by" be used?

Thanks,

Joe

Re: How do I Transpose data from Long to Wide format?

jswislar — Thu, 17 Nov 2016 19:44:01 GMT

Thanks, Ron, but I'm not doing simple summaries. I'm working on a survival analysis. My first step is to get the data into wide format.
Joe

Re: How do I Transpose data from Long to Wide format?

txnelson — Thu, 17 Nov 2016 20:18:59 GMT

Oops.....I think you can solve the issue if you use the Group and place your ID column in there too

If your data are sorted by ID, (all of the data for a given ID are together). If not sorted, then go to Tables==>Sort, and order the data by ID. Then selecting it as your "Split By" column, and your Var1, Var2, Outcome as your "Split Columns" you will get a data table with one row per value of Split By column, and then you will get Var1 001, Var1 002......Var1 013, Var2 001, Var2 002.......Var2 013, Outcome 001, Outcome 002......Outcome 013.
Given the fact that you can have up to 13 levels.

Re: How do I Transpose data from Long to Wide format?

jswislar — Thu, 17 Nov 2016 20:29:26 GMT

I'm not sure what's happening then. The data are sorted by ID. When I split by ID and use my vars as "split columns", I end up with 14 lines of data and a column for each id/variable combination.

I'm trying to get:

ID visit1 visit 2 .... visit13 outcome1 outcome2 ... outcome 13

001 9/13 10/20 ... 3/2 0 0 ... 1

002 7/04 8/12 ... 12/5 0 1 ... 1

What I'm getting is

Date1-ID001 Date1-ID002

Line1 9/13 7/04

Line2 10/20 8/12

Thanks,

Joe

Re: How do I Transpose data from Long to Wide format?

ron_horne — Fri, 18 Nov 2016 00:22:04 GMT

Sorry for not double checking first.

Try the following

names default to here(1);

// source data table

dt = New Table( "source",

Add Rows( 10 ),

New Column( "ID ",

Numeric,

Continuous,

Format( "Best", 5 ),

Set Values( [1, 1, 1, 2, 2, 3, 3, 4, 4, 4] )

New Column( "Var1 ",

Numeric,

Continuous,

Format( "Best", 12 ),

Set Values( [1, 1, 1, 0, 0, 1, 1, 0, 1, 0] )

New Column( "Var2 ",

Numeric,

Continuous,

Format( "Best", 12 ),

Set Values( [1, 1, 0, 1, 1, 1, 0, 1, 1, 0] )

New Column( "Outcome",

Numeric,

Continuous,

Format( "Best", 12 ),

Set Values( [0, 0, 1, 1, 1, 0, 0, 1, 1, 1] )

)

);

// add index column for splitting - no need to sort the table first

dt << new column ("index", formula(Sum( :ID[Index( 1, Row() )] == :ID ) ) );

// now split by index and group by ID

dt << Split(

Split By( :index ),

Split( :Var1, :Var2, :Outcome ),

Group( :ID )

);

Re: How do I Transpose data from Long to Wide format?

jswislar — Fri, 18 Nov 2016 19:27:17 GMT

OK, I'm new to JMP and have never used JSL, although I've done plenty of programming in SPSS, SAS and a few others. I'm not following the scripting at all in this example, especially the "set values". I think the next block of commands is trying to create new index variables for grouping prior to splitting.

I created a few variables when going from wide to long in another dataset, but in that case, every subject had the same number of observations.

Re: How do I Transpose data from Long to Wide format?

ron_horne — Fri, 18 Nov 2016 20:39:44 GMT

if you ran the whole script as is and the result is what you are looking for then you can reproduce it on your data set with just a few clicks.

1) add a new column and call it "index".

2) set its' formula to the following: Sum( :ID[Index( 1, Row() )] == :ID )

3) Tabls >> Split and fill it as so.

click OK and you should be done.

Best,

Ron

Re: How do I Transpose data from Long to Wide format?

jswislar — Mon, 21 Nov 2016 15:20:57 GMT

This code gave me 2 tables. The first was called "Source" and only had 10 rows of data and 5 columns. The ID column goes from 1-4 (not my IDs) and ID appears on 3 rows. I don't really know what this was supposed to do. All the data are binary, even my date vars. The second table "untitled" had 4 rows of data and 4 columns. ID goes from 1-4 down the rows and the column are all labeled as missing.

I don't think this is doing what I want.

Re: How do I Transpose data from Long to Wide format?

ron_horne — Mon, 21 Nov 2016 17:30:09 GMT

Last chance,

1) the first command just makes an original data source in long format.

2) the second command introduces an index for the number of the event for each ID. the number of events does not need to be equal for each ID. but, if some ID's have less events they will have missing values in the wide table format.

3) the third command takes advantage of the index and the ID columns in order to split the table the way you asked for it. one column for each event and variable and each row represents just one ID.

on your data you just need to run the last two command.

names default to here(1);
// source data table
dt = New Table( "Long",
	Add Rows( 7 ),
	New Column( "ID",
		Numeric,
		Continuous,
		Format( "Best", 5 ),
		Set Values( [1, 1, 1, 2, 2, 3, 3] )
	),
	New Column( "Var1",
		Numeric,
		Continuous,
		Format( "Best", 12 ),
		Set Values( [1, 1, 1, 0, 0, 1, 1] )
	),
	New Column( "Var2",
		Numeric,
		Continuous,
		Format( "Best", 12 ),
		Set Values( [1, 1, 0, 1, 1, 1, 0] )
	),
	New Column( "Outcome",
		Numeric,
		Continuous,
		Format( "Best", 12 ),
		Set Values( [0, 0, 1, 1, 1, 0, 0] )
	)
);


// add index column for spliting - no need to sort the table first
dt << new column ("index", formula(Sum( :ID[Index( 1, Row() )] == :ID ) ) );

// now split by index and group by ID
dt << Split(
	Split By( :index ),
	Split( :Var1, :Var2, :Outcome ),
	Group( :ID ),
	Output Table( "Wide" )
);

Re: How do I Transpose data from Long to Wide format?

Jeff_Perkinson — Mon, 21 Nov 2016 21:47:53 GMT

@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.

Re: How do I Transpose data from Long to Wide format?

jswislar — Fri, 16 Dec 2016 21:15:05 GMT

Thanks, Jeff! That worked!

Re: How do I Transpose data from Long to Wide format?

AutoScoresDingo — Tue, 16 Apr 2024 14:31:20 GMT

Hello Jeff,

Thank you for this explanation it was helpful but I have a quick question:

Current sorting: Each variable is listed with its corresponding event identifier.

Desired sorting: I want all variables for the first event to be grouped together, followed by all variables for the second event, and so on. Within each event group, I want the variables sorted by their names.

Original: var1_1 var1_2 var1_3 var2_1 var2_2 var2_3 var3_1 var3_2 var3_3 Desired: var1_1 var2_1 var3_1 var1_2 var2_2 var3_2 var1_3 var2_3 var3_3 Is there a way to do that ?

Re: How do I Transpose data from Long to Wide format?

txnelson — Tue, 16 Apr 2024 17:51:03 GMT

Other than writing a script to order the columns, I don't believe there is a built in way to interactively sort the columns in the order you want them to have.

Of course, you can manually move the columns.

Re: How do I Transpose data from Long to Wide format?

bellhasaring2it — Fri, 21 Feb 2025 21:25:29 GMT

I know this is an old thread, but I have a question. This worked for me, except that when I try to create the index column, it is blank for the first ID. This is what it looks like.

Does anyone know what might be causing this?

Re: How do I Transpose data from Long to Wide format?

txnelson — Fri, 21 Feb 2025 23:57:10 GMT

Here is one approach to get what you want

If( Row() == 1,
	theVal = 1,
	If( Lag( :yourColumn ) != :yourColumn,
		theVal
		++)
);
theVal;

Re: How do I Transpose data from Long to Wide format?

bellhasaring2it — Mon, 24 Feb 2025 16:45:17 GMT

Hi there,

Where am I supposed to put this formula? In the same Index column? Do I need to change "yourColumn" to something?

Re: How do I Transpose data from Long to Wide format?

txnelson — Mon, 24 Feb 2025 17:07:28 GMT

The formula I specified would be placed in the Index column.

The "yourColumn" needs to be change to the name of the column you what to increment the Index column's value when the column is changed.