BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
jswislar
Community Trekker

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

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.

0 Kudos
ron_horne
Super User

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

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

Jeff_Perkinson
Community Manager Community Manager

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

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

 

JMPScreenSnapz058.png

 

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. 

JMPScreenSnapz059.png

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.

JMPScreenSnapz060.png

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

 

JMPScreenSnapz061.png

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.

JMPScreenSnapz062.png

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.

 

-Jeff
jswislar
Community Trekker

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

Thanks, Jeff! That worked!
0 Kudos