topic Re: JSL script to transpose columns to rows in Discussions

convert columns to rows

steo45 — Sat, 10 Jun 2023 23:24:17 GMT

data and desired results

i would really appreciate your help

Re: JSL script to transpose columns to rows

txnelson — Sun, 27 Dec 2020 22:42:44 GMT

The script below is a good start on creating the output table you requested

Names Default To Here( 1 );
dt = Current Data Table();

// Transpose the Demographics
dtTrans1 = dt << Transpose(
	columns( :SBIN, :HBIN, :Alarms, :Device, :Site ),
	Transpose selected rows only( 1 ),
	Output Table( "Transpose 1" )
);

// Change the name of the first column
dtTrans1:Label << set name( "Column 1" ) << Set Display Width( 150 );

// Change the new column names
For( i = 2, i <= N Cols( dtTrans1 ), i++,
	Column( dtTrans1, i ) << set name( "Column " || Char( i + 4 ) ) << Set Display Width( 100 )
);

// Change the detail name of the Alarms
dtTrans1:Column 1[(dtTrans1 << get rows where( dtTrans1:Column 1 == "Alarms" ))[1]] = "Pass/Fail/Alarm";

// Create the Devce values
deviceRow = (dtTrans1 << get rows where( dtTrans1:Column 1 == "Device" ))[1];
For( i = 2, i <= N Cols( dtTrans1 ), i++,
	Column( dtTrans1, i )[deviceRow] = i - 1
);

// Now Find all of the columns that are parameters
parameterColNamesList = {};
For( i = 1, i <= N Cols( dt ), i++,
	If( Is Missing( Num( Word( 1, Column( dt, i ) << get name, ":" ) ) ) == 0,
		Insert Into( parameterColNamesList, Column( dt, i ) << get name )
	)
);

// Transpose the parameter columns
dtTrans2 = dt << Transpose(
	columns( Eval( parameterColNamesList ) ),
	Transpose selected rows only( 0 ),
	Output Table( "Transpose 2" )
);

// Add in the new columns to be created
dtTrans2 << Add Multiple Columns( "Column", 5, before first, Character );

// Add the values to the new columns
For( i = 1, i <= N Rows( dtTrans2 ), i++,
	dtTrans2:Column 1[i] = Word( 1, dtTrans2:Label[i], ":" );
	dtTrans2:Column 2[i] = Word( 2, dtTrans2:Label[i], ":" );
	specs = Column( dt, dtTrans2:Label[i] ) << get property( "spec limits" );
	If( Is Missing( specs["LSL"] ) == 0,
		dtTrans2:Column 3[i] = specs["LSL"]
	);
	If( Is Missing( specs["USL"] ) == 0,
		dtTrans2:Column 4[i] = specs["USL"]
	);
	If( Column( dt, dtTrans2:Label[i] ) << get property( "Units" ) != "",
		dtTrans2:Column 5[i] = Column( dt, dtTrans2:Label[i] ) << get property( "Units" )
	);
);

// Get rid of the label column which is no longer needed
dtTrans2 << delete columns( :Label );

// Change the names of the Trans2 table to match the names in the Trans1 table
For( i = 6, i <= N Cols( dtTrans2 ), i++,
	Column( dtTrans2, i ) << set name( "Column " || Char( i ) )
);

// Combine the 2 Transformed data tables
dtFinal = dtTrans1 << concatenate( dtTrans2 );

// Cleanup the no longer needed data tables
close( dtTrans1, nosave );
close( dtTrans2, nosave );

Re: JSL script to transpose columns to rows

steo45 — Fri, 08 Jan 2021 23:47:10 GMT

Thank You! this seems to work just fine. But for some reason the test name Lo Limit and Hi limits columns are put last. any way we can rearranged them and label them in the script ?

Re: JSL script to transpose columns to rows

txnelson — Tue, 29 Dec 2020 18:47:44 GMT

What version of JMP are you using?

Re: JSL script to transpose columns to rows

steo45 — Fri, 08 Jan 2021 23:18:37 GMT

im using jmp 14

Re: JSL script to transpose columns to rows

txnelson — Sat, 09 Jan 2021 03:39:01 GMT

Here is a corrected version of the script, that positions the spec columns, etc. at the correct location and also adds in the column labels

Names Default To Here( 1 );
dt = Current Data Table();

// Transpose the Demographics
dtTrans1 = dt << Transpose(
	columns( :SBIN, :HBIN, :Alarms, :Device, :Site ),
	Transpose selected rows only( 1 ),
	Output Table( "Transpose 1" )
);

// Change the name of the first column
dtTrans1:Label << set name( "Column 1" ) << Set Display Width( 150 );

// Change the new column names
For( i = 2, i <= N Cols( dtTrans1 ), i++,
	Column( dtTrans1, i ) << set name( "Column " || Char( i + 4 ) ) << Set Display Width( 100 )
);

// Change the detail name of the Alarms
dtTrans1:Column 1[(dtTrans1 << get rows where( dtTrans1:Column 1 == "Alarms" ))[1]] = "Pass/Fail/Alarm";

// Create the Devce values
deviceRow = (dtTrans1 << get rows where( dtTrans1:Column 1 == "Device" ))[1];
For( i = 2, i <= N Cols( dtTrans1 ), i++,
	Column( dtTrans1, i )[deviceRow] = i - 1
);


// Now Find all of the columns that are parameters
parameterColNamesList = {};
For( i = 1, i <= N Cols( dt ), i++,
	If( Is Missing( Num( Word( 1, Column( dt, i ) << get name, ":" ) ) ) == 0,
		Insert Into( parameterColNamesList, Column( dt, i ) << get name )
	)
);

// Transpose the parameter columns
dtTrans2 = dt << Transpose(
	columns( Eval( parameterColNamesList ) ),
	Transpose selected rows only( 0 ),
	Output Table( "Transpose 2" )
);

// Add in the new columns to be created
dtTrans2 << Add Multiple Columns( "Column", 5, before first, Character );

// Add the columns 2-5 in the trans data table
dtTrans1 << Add Multiple Columns( "xColumn", 4,after(:Column 1), Character );
for(i=2,i<=5,i++,
	column(dtTrans1,i)<<set name("Column " || char(i));
);

// Add a row at the end of the table, and add the labels 
dtTrans1 << add rows(1);
dtTrans1:Column 1[6] = "# Test Number";
dtTrans1:Column 2[6] = "Test Name";
dtTrans1:Column 3[6] = "Lo Limit";
dtTrans1:Column 4[6] = "Hi Limit";
dtTrans1:Column 5[6] = "Unit";

// Add the values to the new columns
For( i = 1, i <= N Rows( dtTrans2 ), i++,
	dtTrans2:Column 1[i] = Word( 1, dtTrans2:Label[i], ":" );
	dtTrans2:Column 2[i] = Word( 2, dtTrans2:Label[i], ":" );
	specs = Column( dt, dtTrans2:Label[i] ) << get property( "spec limits" );
	If( Is Missing( specs["LSL"] ) == 0,
		dtTrans2:Column 3[i] = specs["LSL"]
	);
	If( Is Missing( specs["USL"] ) == 0,
		dtTrans2:Column 4[i] = specs["USL"]
	);
	If( Column( dt, dtTrans2:Label[i] ) << get property( "Units" ) != "",
		dtTrans2:Column 5[i] = Column( dt, dtTrans2:Label[i] ) << get property( "Units" )
	);
);

// Get rid of the label column which is no longer needed
dtTrans2 << delete columns( :Label );

// Change the names of the Trans2 table to match the names in the Trans1 table
For( i = 6, i <= N Cols( dtTrans2 ), i++,
	Column( dtTrans2, i ) << set name( "Column " || Char( i ) )
);

// Combine the 2 Transformed data tables
dtFinal = dtTrans1 << concatenate( dtTrans2 );

// Cleanup the no longer needed data tables
close( dtTrans1, nosave );
close( dtTrans2, nosave );

Re: JSL script to transpose columns to rows

steo45 — Sat, 09 Jan 2021 16:59:43 GMT

thanks Jim! the format is perfect. however it doesnt work on with other similar dataset for some reason. any reason why?

Re: JSL script to transpose columns to rows

txnelson — Sat, 09 Jan 2021 17:47:59 GMT

Are there any error messages in the log?

What are the differences between the 2 data tables?

Is the script stopping before it's completion?

Re: JSL script to transpose columns to rows

steo45 — Mon, 11 Jan 2021 20:56:27 GMT

there is no log error. when i run the script, it just doesnt do anything. the only difference is the test names... they are different. but all others headers are the same

Re: JSL script to transpose columns to rows

txnelson — Mon, 11 Jan 2021 21:20:40 GMT

Try running the code for line 1 through line 26. All you have to do, is to select(highlight) those rows in your script, and then click on the Run Icon.

Names Default To Here( 1 );
dt = Current Data Table();

// Transpose the Demographics
dtTrans1 = dt << Transpose(
	columns( :SBIN, :HBIN, :Alarms, :Device, :Site ),
	Transpose selected rows only( 1 ),
	Output Table( "Transpose 1" )
);

// Change the name of the first column
dtTrans1:Label << set name( "Column 1" ) << Set Display Width( 150 );

// Change the new column names
For( i = 2, i <= N Cols( dtTrans1 ), i++,
	Column( dtTrans1, i ) << set name( "Column " || Char( i + 4 ) ) << Set Display Width( 100 )
);

// Change the detail name of the Alarms
dtTrans1:Column 1[(dtTrans1 << get rows where( dtTrans1:Column 1 == "Alarms" ))[1]] = "Pass/Fail/Alarm";

// Create the Devce values
deviceRow = (dtTrans1 << get rows where( dtTrans1:Column 1 == "Device" ))[1];
For( i = 2, i <= N Cols( dtTrans1 ), i++,
	Column( dtTrans1, i )[deviceRow] = i - 1
);

See if the Transpose 1 data table looks like:

The run the lines 30 - 35

parameterColNamesList = {};
For( i = 1, i <= N Cols( dt ), i++,
	If( Is Missing( Num( Word( 1, Column( dt, i ) << get name, ":" ) ) ) == 0,
		Insert Into( parameterColNamesList, Column( dt, i ) << get name )
	)
);

and see if the parameterColNamesList contains the names of all of the tests in the data table.

Then keep checking the different sections of code, until you find what is not running correctly.

If you can provide to me a copy of the data table you are trying to read in, I would be willing to do the checking myself.

Re: JSL script to transpose columns to rows

steo45 — Wed, 13 Jan 2021 22:39:58 GMT

ok sure here it is

Re: JSL script to transpose columns to rows

steo45 — Thu, 14 Jan 2021 20:53:15 GMT

here is a random sample data

Re: JSL script to transpose columns to rows

steo45 — Thu, 14 Jan 2021 20:52:58 GMT

disregard the previous sample, please use this one

Re: JSL script to transpose columns to rows

txnelson — Thu, 14 Jan 2021 02:11:42 GMT

I ran the script against the subset sample data you provided, and it ran without error and produced an transposed data table. I did find one small issue, which was the Test Names did not contain the complete name from the input data table. But other than that, it seems to have run correctly.

Here is the corrected JSL

Names Default To Here( 1 );
dt = Current Data Table();

// Transpose the Demographics
dtTrans1 = dt << Transpose(
	columns( :SBIN, :HBIN, :Alarms, :Device, :Site ),
	Transpose selected rows only( 1 ),
	Output Table( "Transpose 1" )
);

// Change the name of the first column
dtTrans1:Label << set name( "Column 1" ) << Set Display Width( 150 );

// Change the new column names
For( i = 2, i <= N Cols( dtTrans1 ), i++,
	Column( dtTrans1, i ) << set name( "Column " || Char( i + 4 ) ) << Set Display Width( 100 )
);

// Change the detail name of the Alarms
dtTrans1:Column 1[(dtTrans1 << get rows where( dtTrans1:Column 1 == "Alarms" ))[1]] = "Pass/Fail/Alarm";

// Create the Devce values
deviceRow = (dtTrans1 << get rows where( dtTrans1:Column 1 == "Device" ))[1];
For( i = 2, i <= N Cols( dtTrans1 ), i++,
	Column( dtTrans1, i )[deviceRow] = i - 1
);


// Now Find all of the columns that are parameters
parameterColNamesList = {};
For( i = 1, i <= N Cols( dt ), i++,
	If( Is Missing( Num( Word( 1, Column( dt, i ) << get name, ":" ) ) ) == 0,
		Insert Into( parameterColNamesList, Column( dt, i ) << get name )
	)
);

// Transpose the parameter columns
dtTrans2 = dt << Transpose(
	columns( Eval( parameterColNamesList ) ),
	Transpose selected rows only( 0 ),
	Output Table( "Transpose 2" )
);

// Add in the new columns to be created
dtTrans2 << Add Multiple Columns( "Column", 5, before first, Character );

// Add the columns 2-5 in the trans data table
dtTrans1 << Add Multiple Columns( "xColumn", 4,after(:Column 1), Character );
for(i=2,i<=5,i++,
	column(dtTrans1,i)<<set name("Column " || char(i));
);

// Add a row at the end of the table, and add the labels 
dtTrans1 << add rows(1);
dtTrans1:Column 1[6] = "# Test Number";
dtTrans1:Column 2[6] = "Test Name";
dtTrans1:Column 3[6] = "Lo Limit";
dtTrans1:Column 4[6] = "Hi Limit";
dtTrans1:Column 5[6] = "Unit";

// Add the values to the new columns
For( i = 1, i <= N Rows( dtTrans2 ), i++,
	dtTrans2:Column 1[i] = Word( 1, dtTrans2:Label[i], ":" );
	dtTrans2:Column 2[i] = Substr( dtTrans2:Label[i], length( dtTrans2:Column 1[i] ) +3 );
	specs = Column( dt, dtTrans2:Label[i] ) << get property( "spec limits" );
	If( Is Missing( specs["LSL"] ) == 0,
		dtTrans2:Column 3[i] = specs["LSL"]
	);
	If( Is Missing( specs["USL"] ) == 0,
		dtTrans2:Column 4[i] = specs["USL"]
	);
	If( Column( dt, dtTrans2:Label[i] ) << get property( "Units" ) != "",
		dtTrans2:Column 5[i] = Column( dt, dtTrans2:Label[i] ) << get property( "Units" )
	);
);

// Get rid of the label column which is no longer needed
dtTrans2 << delete columns( :Label );

// Change the names of the Trans2 table to match the names in the Trans1 table
For( i = 6, i <= N Cols( dtTrans2 ), i++,
	Column( dtTrans2, i ) << set name( "Column " || Char( i ) )
);

// Combine the 2 Transformed data tables
dtFinal = dtTrans1 << concatenate( dtTrans2 );

// Cleanup the no longer needed data tables
close( dtTrans1, nosave );
close( dtTrans2, nosave );

Re: JSL script to transpose columns to rows

txnelson — Thu, 14 Jan 2021 02:23:13 GMT

This sample data table has an issue with the Device column. It is set as a Character Data Type. If you change it to Numeric, the script works fine.

Re: JSL script to transpose columns to rows

steo45 — Thu, 14 Jan 2021 20:53:57 GMT

thanks jim

Re: JSL script to transpose columns to rows

steo45 — Wed, 24 Feb 2021 17:14:45 GMT

hello jim

i still find issue with other data table for some reasons i checked the data type and make sure they are all the same. but its not working

Re: JSL script to transpose columns to rows

txnelson — Wed, 24 Feb 2021 18:11:18 GMT

The error message that is displayed indicates that the data table that "dtTrans2" points to is not being created. "dtTrans2" is created from a Transpose(), which requires a list of parameters from your original data table. The "parameterColNamesList" is created by finding all columns in your original data table that starts with a number followed by a ":". I suspect your parameters are not named like that in your original data table. But without seeing the data table, I am only guessing. If what I am guessing is true, then something needs to be found that identifies the parameter columns.

Re: JSL script to transpose columns to rows

steo45 — Wed, 24 Feb 2021 19:06:29 GMT

here is one

Re: JSL script to transpose columns to rows

txnelson — Thu, 25 Feb 2021 03:08:42 GMT

The issue was that one or more of the Parameters were input as character data. The Transpose can not handle mixed data. So I have added a check to make sure that all detected parameters are numeric.

Names Default To Here( 1 );
dt = Current Data Table();

// Transpose the Demographics
dtTrans1 = dt << Transpose(
	columns( :SBIN, :HBIN, :Alarms, :Device, :Site ),
	Transpose selected rows only( 1 ),
	Output Table( "Transpose 1" )
);

// Change the name of the first column
dtTrans1:Label << set name( "Column 1" ) << Set Display Width( 150 );

// Change the new column names
For( i = 2, i <= N Cols( dtTrans1 ), i++,
	Column( dtTrans1, i ) << set name( "Column " || Char( i + 4 ) ) << Set Display Width( 100 )
);

// Change the detail name of the Alarms
dtTrans1:Column 1[(dtTrans1 << get rows where( dtTrans1:Column 1 == "Alarms" ))[1]] = "Pass/Fail/Alarm";

// Create the Devce values
deviceRow = (dtTrans1 << get rows where( dtTrans1:Column 1 == "Device" ))[1];
For( i = 2, i <= N Cols( dtTrans1 ), i++,
	Column( dtTrans1, i )[deviceRow] = i - 1
);


// Now Find all of the columns that are parameters
parameterColNamesList = {};
For( i = 1, i <= N Cols( dt ), i++,
	If( Is Missing( Num( Word( 1, Column( dt, i ) << get name, ":" ) ) ) == 0 &
		Column( dt, i ) << get modeling type == "Continuous",
		Insert Into( parameterColNamesList, Column( dt, i ) << get name )
	)
);

// Transpose the parameter columns
dtTrans2 = dt << Transpose(
	columns( Eval( parameterColNamesList ) ),
	Transpose selected rows only( 0 ),
	Output Table( "Transpose 2" )
);

// Add in the new columns to be created
dtTrans2 << Add Multiple Columns( "Column", 5, before first, Character );

// Add the columns 2-5 in the trans data table
dtTrans1 << Add Multiple Columns( "xColumn", 4,after(:Column 1), Character );
for(i=2,i<=5,i++,
	column(dtTrans1,i)<<set name("Column " || char(i));
);

// Add a row at the end of the table, and add the labels 
dtTrans1 << add rows(1);
dtTrans1:Column 1[6] = "# Test Number";
dtTrans1:Column 2[6] = "Test Name";
dtTrans1:Column 3[6] = "Lo Limit";
dtTrans1:Column 4[6] = "Hi Limit";
dtTrans1:Column 5[6] = "Unit";

// Add the values to the new columns
For( i = 1, i <= N Rows( dtTrans2 ), i++,
	dtTrans2:Column 1[i] = Word( 1, dtTrans2:Label[i], ":" );
	dtTrans2:Column 2[i] = Substr( dtTrans2:Label[i], length( dtTrans2:Column 1[i] ) +3 );
	specs = Column( dt, dtTrans2:Label[i] ) << get property( "spec limits" );
	If( Is Missing( specs["LSL"] ) == 0,
		dtTrans2:Column 3[i] = char(specs["LSL"])
	);
	If( Is Missing( specs["USL"] ) == 0,
		dtTrans2:Column 4[i] = char(specs["USL"])
	);
	If( Column( dt, dtTrans2:Label[i] ) << get property( "Units" ) != "",
		dtTrans2:Column 5[i] = Column( dt, dtTrans2:Label[i] ) << get property( "Units" )
	);
);

// Get rid of the label column which is no longer needed
dtTrans2 << delete columns( :Label );

// Change the names of the Trans2 table to match the names in the Trans1 table
For( i = 6, i <= N Cols( dtTrans2 ), i++,
	Column( dtTrans2, i ) << set name( "Column " || Char( i ) )
);

// Combine the 2 Transformed data tables
dtFinal = dtTrans1 << concatenate( dtTrans2 );

// Cleanup the no longer needed data tables
close( dtTrans1, nosave );
close( dtTrans2, nosave );