topic Re: Get column names that has particular value in Discussions

Get column names that has particular value

Paul06 — Sat, 10 Jun 2023 23:24:38 GMT

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.

Re: Get column names that has particular value

ThuongLe — Tue, 05 Jan 2021 02:35:12 GMT

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( :D == 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

txnelson — Tue, 05 Jan 2021 09:19:42 GMT

My read of the initial input is that more than 1 column on a given row may have a value of 1. If that is not the case, the following 2 scripts are not the method to use.

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: In this example more than one column on a given row has a value of 1

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] ) ),
	New Column( "RESIDUAL", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [32.3201037, 20.54947] ) ),
	New Column( "TEST", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [., .] ) ),
	New Column( "IMC", Character, "Nominal", Set Values( {"IMC1", "IMC3"} ) )
);

// Generate a list of columns to be processed
colNamesList = dt << get column names( string );
Remove From( colNamesList, 13, 2 );
foundNamesList = {};

For( i = 1, 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 there are more rows than in the original table, add rows
If( N Rows( dt ) < N Items( foundNamesList ),
	dt << add rows( N Items( foundNamesList ) - N Rows( dt ) )
);

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

Re: Get column names that has particular value

jthi — Tue, 05 Jan 2021 07:47:46 GMT

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:

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
It is using very JSL like syntax with all << select wheres and such
(I think that kind of workflow presented in the solution is very JMP like)

Re: Get column names that has particular value

ian_jmp — Tue, 05 Jan 2021 11:52:15 GMT

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 value

Paul06 — Tue, 05 Jan 2021 16:13:48 GMT

Thank you so much. It worked!

Re: Get column names that has particular value

Paul06 — Tue, 05 Jan 2021 16:16:07 GMT

Thanks you for your time and help. I am sorry I did not state the problem clearly. In each row, there is only one instance of 1 under a column and I need to pick out the column name and save under a new column.

Re: Get column names that has particular value

Paul06 — Tue, 05 Jan 2021 16:21:54 GMT

Hi Ian, Thanks for your time and explanation. I can understand your code. However, when I ran the script setting dt as current table, I got empty() in the resultant expression column. I have a long way to learn here. Thanks all for your input and suggestions

Re: Get column names that has particular value

Paul06 — Tue, 05 Jan 2021 16:23:40 GMT

Thank you for your time and help. I was not able to run the script succesfully. It created a column IMC but no value in it. I will debug and try to understand the code better.

Re: Get column names that has particular value

Paul06 — Tue, 05 Jan 2021 20:08:18 GMT

Hi Ian,
Spend some time reading the help>Books section and changed the keyword from loc to contains worked now. Thank you ton!

// 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 << New Column( "Value " || Char( searchVal ) || " found in Column:", Expression );
for( r = 1, r <= N Row( dt ), r++,
    newCol[r] = colList[Contains( mat[r, 0], searchVal )]
);

Re: Get column names that has particular value

doraemengs — Tue, 18 Jun 2024 14:28:18 GMT

Hi jthi,

if my data looks like this example but all of the columns are numeric (continuous data) and I want to choose the column name with cut off such as > 3000 (in 1 row might have multiple columns > 3000) what should I do on script?
I use the following script

colNames = dt << Get Column Names("String");

For(i = N Items(colNames), i > 3000 , i--,

);

but it did not work.

Best