345584_en-US

liliconcom

Get column names that has particular value

Hi, 

 

I just started using jmp at work and trying to learn JSL. I am trying to write a script to get the column names where the row value =1. For example, the table shows values of 0 and 1 under different IMCX columns and I want to find out which column has the value =1 in each row and store under a new column called IMC. In the first row,  column IMC 4 has 1 so I manually enter IMC4 under IMC. There are more than 5000 rows in the tables. Thank you for the help. 

 

 

Data TableRe: Get column names that has particular value

You can do somthing like this:

 

The formula in Result column is as below:

If( :A == 0,
 "",
 Char( Column Name( 1 ) )
) || If( :B == 0,
 "",
 Char( Column Name( 2 ) )
) || If( :C == 0,
 "",
 Char( Column Name( 3 ) )
) || If(  == 0,
 "",
 Char( Column Name( 4 ) )
)

I use concatenate keyword to check each column to see if it's a 1.

Let me know if it works out for you!

Re: Get column names that has particular value

Here is one way to do this

Names Default To Here( 1 );

// Create a sample data table
dt = New Table( "Example",
 Add Rows( 2 ),
 New Column( "IMCO", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IIMC2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC4", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 0] ) ),
 New Column( "IMC5", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC6", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 1] ), Set Display Width( 46 ) ),
 New Column( "IMC7", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC8", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC9", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC10", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC11", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "RESIDUAL", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [32.3201037, 20.54947] ) ),
 New Column( "TEST", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [., .] ) )
);

// Generate a list of columns to be processed
colNamesList = dt << get column names(string);
remove from (colNamesList,13,2);

// Find the maximum( the ones ) for the columns in question
dtSum = dt<< Summary(
 Max(colNamesList),
 Freq( "None" ),
 Weight( "None" ),
 statistics column name format( "column" ),
 Link to original data table( 0 )
);

// Stack all of the summarized columns
dtStack = dtSum << Stack(
 columns(
  colNamesList
 ),
 Source Label Column( "IMC" ),
 Stacked Data Column( "Data" )
);

close( dtSum, nosave );

// Delete all data that do not have 1 
dtStack << select where(:Data !=1 );
dtStack << delete rows;

// Delete the unwanted columns from the summary table
dtStack << delete columns( {N Rows, data} );

// If there are more rows than in the original table, add rows
if( n rows( dt ) < N Rows( dtStack ),
 dt << add rows( N Rows( dtStack ) - N Rows( dt ) )
);

// Add the column names for the columns found with 1's in their data
dt << Update( With( dtStack ) );

close( dtStack, nosave );

Here is a second way to do it:

Names Default To Here( 1 );

// Create a sample data table
dt = New Table( "Example",
 Add Rows( 2 ),
 New Column( "IMCO", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IIMC2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC4", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 0] ) ),
 New Column( "IMC5", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC6", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 1] ), Set Display Width( 46 ) ),
 New Column( "IMC7", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC8", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC9", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC10", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC11", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "RESIDUAL", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [32.3201037, 20.54947] ) ),
 New Column( "TEST", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [., .] ) )
);

foundNamesList = {};

For( i = 5, i <= N Items( colNamesList ), i++,
theRows = dt << get rows where(as Column( colNamesList[i]) == 1 );
 If( N rows( theRows)  > 0, Insert Into( foundNamesList, colNamesList[i] ) )
 );

dt << New Column( "IMC", character );

If( N Items( foundNamesList ) > 0,
 dt:IMC << set values( foundNamesList )
);

Re: Get column names that has particular value

My solution (using same sample data table as txnelson):

Names Default To Here( 1 );

// Create a sample data table
dt = New Table( "Example",
 Add Rows( 2 ),
 New Column( "IMCO", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IIMC2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC4", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 0] ) ),
 New Column( "IMC5", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC6", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 1] ), Set Display Width( 46 ) ),
 New Column( "IMC7", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC8", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC9", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC10", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC11", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "RESIDUAL", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [32.3201037, 20.54947] ) ),
 New Column( "TEST", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [., .] ) )
);

//get wanted columns. Assumes that IMC columns start from column 1 and all IMC columns are one after another
colNames = dt << Get Column Names("String");
For(i = N Items(colNames), i > 0 , i--,
 If(!Starts With(colNames[i], "IMC"),
  Remove From(colNames, i),
  break(); //we can break on first occurance
 );
);

//new column with IMC with value 1
dt << New Column("IMC", Character, 
 << Set Each Value(
  rowValues = dt[Row(), 0][1::N Items(colNames)]; //get values of row as matrix
  colIndex = Contains(rowValues, 1); //get index of
  If(colIndex > 0, //handle 
   colNames[colIndex], //get column name with the index
   ""; //when no 1 found use ""
  );
 );
);

I like the first solution @txnelson  offered due to two (three) reasons:

1. It is using so many menu items directly found from JMP and you could almost script it without any JSL knowledge by just copy-pasting from datatable Sources
2. It is using very JSL like syntax with all << select wheres and such
3. (I think that kind of workflow presented in the solution is very JMP like)

Re: Get column names that has particular value

Regarding "I just started using jmp at work and trying to learn JSL" (and has been implied already) there are many ways to get the job done.

 

So, in this spirit, please find yet another slight variation. Use 'Help > Books > Scripting Guide' to figure out how it works.

Names Default To Here( 1 );

// Create a sample data table
dt = New Table( "Example",
 Add Rows( 2 ),
 New Column( "IMCO", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 0] ) ),
 New Column( "IIMC2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 1] ) ),
 New Column( "IMC4", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 0] ) ),
 New Column( "IMC5", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC6", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 1] ), Set Display Width( 46 ) ),
 New Column( "IMC7", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC8", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC9", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC10", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC11", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
);

// Get a list of the column names
colList = dt << getColumnNames("String");

// Get the '0's and '1's into a matrix
mat = dt << getAsMatrix;

// Value to search for
searchVal = 1;

// Add an 'expression' column and loop rowise . . .
newCol = dt << NewColumn("Value "||Char(searchVal)||" found in Column:", Expression);
for (r=1, r<=NRow(dt), r++,
 newCol[r] = colList[Loc(mat[r, 0], searchVal)];
);

 

Re: Get column names that has particular valueThank you so much. It worked!