Create a multiple new columns based on formula

Question

Hi jmp community

I have 19000 col x 88 rows containing data continous data values from biomarker expression.

I want to iscreate a new column for each marker containing a formula substracting the biomarker expression value - reference value.

No need to select any rows as I want all rows from both the biomarker and ref.

So it seems pretty straightforward, but I am struggling just to insert the biomarker data into the new column (I have tested the formula but doesnt work either)

The code doesnt contain any iterations as I just want to see first if it works for one single column first

txnelson · Answer

There seems to be some confusion on how to manipulate columns in a data table vs. working with JSL variables.  Below is a script that uses 2 methods to create a subtraction of the values in column "BP 6M" with the values in column "BP 8M"

Method 1 creates column Norm_BP 6M, and method 2 creates column Norm2_BP 6M

I have annotated the JSL in hopes to help you understand what the 2 different approaches are doing

names default to here(1);

// Open a sample data table to use for illustration
dt =
// Open Data Table: Blood Pressure.jmp
// → Data Table( "Blood Pressure" )
Open( "$SAMPLE_DATA/Blood Pressure.jmp" );
// The data table has the following columns in it
// Subject, Dosec BP 8M, BP 12M, BP 6M, BP 8W, BP 12W, BP 6W, 
// BP 8F, BP 12F, BP 6F, Norm_BP 6M

// Get all of the numeric continuous column names
col_Names = dt << get column names( string, continuous );
// Get Column Names returns a JMP List of column names
// of the columns that have a Modeling Type of Continuous
// {"BP 8M", "BP 12M", "BP 6M", "BP 8W", "BP 12W", "BP 6W", 
//  "BP 8F", "BP 12F", "BP 6F"}
// Note that the first 2 column in the data table, Subject and Dose
// are not in the list

// Note: Specifying
//   Column( 1 ) 
// references the 1st column in the data table, which is Subject
// Specifying
//     Column( col_Names[1] )
// references the 1st column in the col_Names List, which is BP 8M

// Method 1
// Create the new column. Set Each Value loops through each
// row in the data table and applies the specified formula
// which is to subtract the value in the 1st column in the 
// col_Names list from the value in the 3rd column in the 
// col_Names list 
dt << New Column( "Norm_" || col_names[3],
	numeric,
	continuous,
	Format( "Fixed Dec", 3, 2 ),
	set each value( As Column( col_Names[3] ) - As Column( col_Names[1] ) )
);

// Method 2
// Perform the same subtraction, however do it by pulling all of the 
// values from each of the 2 target columns into 2 matrices and then
// use matrix math to do the subtraction into an additional matrix
// and then take those values and put them into the new column

// Create the new column with no values
dt << New Column( "Norm2_" || col_names[3],
	numeric,
	continuous,
	Format( "Fixed Dec", 3, 2 )
);

// Pull the data from the 2 columns in the data table
matCola = column( col_Names[1]) << get values;
matColb = column( col_Names[3]) << get values;
// Subtract the values in matCola from matColb
matResults = matColb - matCola;

// Put the results into the new column
column( "Norm2_" || col_names[3] ) << set values( matResults);

BioJmPCoder · Answer

Hi Jim, Thanks for the input, I am trying to normalize all data point of column by substracting to to the values in column 1. Here I get the values from Column 3, but I get the error showed below. Maybe I need to vectorize the values and make them not as strings? before the difference can be executed? dt = Current data table(); col_names = dt << get column names(string, continuous); markerCount = Column(3); normCount = markerCount - Column(1); dt << New Column( "Norm_" || col_names[3], numeric, continuous, format( "Fixed Dec", 3, 2 ), set each value( as column(normCount)) ); //set each value( as column(Column(3)) - column(Column(1)))

txnelson · Answer

Here is an example that creates all of the new columns in a data table, using the Blood Pressure sample data table.  The script uses 2 different methods to create the new columns to show that different methods can be used to come up with the same results.

names default to here(1);

// Open a sample data table to use for illustration
dt =
// Open Data Table: Blood Pressure.jmp
// → Data Table( "Blood Pressure" )
Open( "$SAMPLE_DATA/Blood Pressure.jmp" );

// Get all of the numeric continuous column names
colNames = dt << get column names( string, continuous );

// Method 1
// Cycle through all Numeric, Continuous columns and create
// a new column with the values from the original column,
// using "set each value()"
For Each( {col}, colNames,
	dt << New Column( col || " Dif1", numeric, continuous,
		format( "Fixed Dec", 3, 0 ),
		set each value( as column(col))
	)
);

// Method 2
// Cycle through all Numeric, Continuous columns and create
// a new column with the values from the original column,
// pulling all of the values for a column into a matrix,
// and then creating a new column and populating the new
// column with the values from the matrix
For Each( {col}, colNames,
	theValues = column( col ) << get values;
	dt << New Column( col || " Dif2", numeric, continuous,
		format( "Fixed Dec", 3, 0 )
	);
	column( dt, nCols(dt) ) << set values( theValues );
);