Transposing from WIDE to LONG and a formula question

Question

Hi--two somewhat related questions

I have used SPLIT to transform a long file to a wide file, but I have a slightly more complicated situation where I want to STACK/transpose a wide file to a long file.

I have a dataset with a unique subject ID, and then several hundred columns/variables. The variable names are the exact same with the exception that Year 1 ends '2', Year 2 ends in '3', etc. Is there a relatively simple way to make this file long, such that there is a time variable (1,2,3,4) and the variable names are only in there once with no number at the end?

Related but approaching the above in a different way, in such a dataset, is there a way to create a formula in JMP to tell it for all XXX hundred columns, to remove the # (e.g., 1, 2) from the end of the column/variable name? Obviously I could do that manually but it would take forever.

Thanks so much!

txnelson · Answer

I will guide you through generating the stacked data table, using the below data table

Go to

Tables=>Stack

Fill out the dialog window by placing all of the columns you want to stack into the Stack Columns selection box.

It will create a table that looks like this

Create a new column in the data table called "Column Name"

Right click on the column header for the Column Name column, and select Formula

A formula needs to be specified that takes everything before the numbers at the end of the values in the column Label and keeps that as the Column Name value.  So this can be done several ways, depending upon the actual data.  In our example, the formula will find everything before any numbers are seen and take that as the Column Name value.

The formula finds the 1st word in the :Label column, stopping whenever it finds any numeric value "0123456789"

The result is

Now create another new column and call it "Year".  In this case, we want to take the last character in the column as the value of year formula is

Which gives you your final data table

Concerning the renaming of the columns, I do not know an interactive way of doing that interactively.  So I think you will have to run the script that I provided earlier

plfazeli · Answer

I really appreciate your reply.  If memory serves you also helped me out with a question last year.

Unfortunately I am not savvy with JMP script, only point and click.  And even if I could figure it out, since I have a lot of variables, I would have to run that code for each wouldn't I?

txnelson · Answer

Here is a script that will generate a stacked data table with 3 columns

Here is a script that will strip off the numerical number from the end of columns in a data table.  Remember, when renaming columns, if the end result is a column name that is the same as a previous column's name, JMP will add a numeric value to make the new column name a unique name

Names Default To Here( 1 );
dt = Current Data Table();
For( i = 1, i <= N Cols( dt ), i++,
	if(
		is missing(
			Num(
				word(
					-1,
					Lowercase( Column( dt, i ) << get name )
				,
				"abcdefghijklmnopqrstuvwxyz;:, -_)(")
			)
		) == 0,
		Column( dt, i ) <<
		set name(
			substr(
				Column(dt,i)<<get name,
				1,
				Length( Column(dt,i)<<get name )
				-Length(
					Word(
						-1,
						Lowercase( Column(dt,i)<<get name ),
						"abcdefghijklmnopqrstuvwxyz;:, -_)("
					)
				)
			)
		)
	)
);

Names Default To Here( 1 );
dt = new table( "Example",
	add rows( 10 ),
	New Column( "Year2", formula( Random Integer( 1, 100 ) ) ),
	New Column( "Year3", formula( Random Integer( 1, 100 ) ) ),
	New Column( "Year4", formula( Random Integer( 1, 100 ) ) )
);

dtStack = Data Table( "Example" ) << Stack(
	columns( :Year2, :Year3, :Year4 ),
	Source Label Column( Column ),
	Stacked Data Column( "Data" )
);

dtStack << New Column( "Year",
	formula(
		Num(
			Word(
				-1,
				Lowercase( :Column ),
				"abcdefghijklmnopqrstuvwxyz;:, -_)("
			)
		)
	)
);
dtStack:Year << delete formula;
for each row(
	:Column = substr(
		:Column,
		1,
		Length( :Column ) - Length(
			Word(
				-1,
				Lowercase( :Column ),
				"abcdefghijklmnopqrstuvwxyz;:, -_)("
			)
		)
	)
);