cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
steo45
Level II

convert columns to rows

 

 

data  and desired results

 

 i would really appreciate your help 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: JSL script to transpose columns to rows

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

View solution in original post

22 REPLIES 22
txnelson
Super User

Re: JSL script to transpose columns to rows

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 );
Jim
steo45
Level II

Re: JSL script to transpose columns to rows

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 ?

 

 

 

 

 

txnelson
Super User

Re: JSL script to transpose columns to rows

What version of JMP are you using?
Jim
steo45
Level II

Re: JSL script to transpose columns to rows

im using jmp 14

txnelson
Super User

Re: JSL script to transpose columns to rows

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 );
Jim
steo45
Level II

Re: JSL script to transpose columns to rows

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

txnelson
Super User

Re: JSL script to transpose columns to rows

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?

 

Jim
steo45
Level II

Re: JSL script to transpose columns to rows

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
txnelson
Super User

Re: JSL script to transpose columns to rows

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:

 

trans1.PNG

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.

 

Jim