topic JMP Script : to copy the non empty value from multiple column to new column in Discussions

JMP Script : to copy the non empty value from multiple column to new column

jinsejoseph — Sat, 10 Jun 2023 23:38:27 GMT

Hi Team,

Currently using JMP15 ,
need help in copying the non empty value from mutiple column to a different columns,

not sure i explained issue correctly below is what i am looking for

Here the column name and count is variable

and each row will have only 2 column with value also

Input:

Expected out put

Column1 and column2 are the output column

Re: JMP Script : to copy the non empty value from multiple column to new column

txnelson — Thu, 14 Oct 2021 09:10:09 GMT

Here are a couple of solutions, both of which are modifications of my responses to your previous discussion.

The Loc() function works with missing values, just as it does with zeros. So the following hard wired JSL works. The difference is that the Delta is not being produced. Instead, the first value is being extracted for Column 12 and the second for Column 13

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

dt << New Column( "Column 12",
	Formula(
		x = :data[Row()] || :data1[Row()] || :data2[Row()] || :data3[Row()] || :data4[Row()] ||
		:data5[Row()] || :data6[Row()] || :data7[Row()] || :data8[Row()] || :data9[Row()];
		y = Loc( x );
		Try( x[y[1]], . );
	)
);

dt << New Column( "Column 13",
	Formula(
		x = :data[Row()] || :data1[Row()] || :data2[Row()] || :data3[Row()] || :data4[Row()] ||
		:data5[Row()] || :data6[Row()] || :data7[Row()] || :data8[Row()] || :data9[Row()];
		y = Loc( x );
		Try( x[y[2]], . );
	)
);

To expand this to the situation where the column names are not known, and the number of columns might be different, the below code will work

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

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

// Build the first column and formula
theExpr = "dt<<new column(\!"Column 12\!",formula(x = " || colNames[1] || "[Row()]";
For( i = 2, i <= N Items( colNames ), i++,
	theExpr = theExpr || " || " || colNames[i] || "[Row()]"
);
theExpr = theExpr || 
";y = Loc( x );
Try( x[y[1]], . );";

// Execute the Created JSL
Eval( Parse( theExpr ) );

// Build the second column and formula
theExpr = "dt<<new column(\!"Column 13\!",formula(x = " || colNames[1] || "[Row()]";
For( i = 2, i <= N Items( colNames ), i++,
	theExpr = theExpr || " || " || colNames[i] || "[Row()]"
);
theExpr = theExpr || 
";y = Loc( x );
Try( x[y[2]], . );";

// Execute the Created JSL
Eval( Parse( theExpr ) );

Re: JMP Script : to copy the non empty value from multiple column to new column

ron_horne — Thu, 14 Oct 2021 12:22:06 GMT

Hi @jinsejoseph ,

building on @txnelson s approach there may be a shorthand script using some built in features. this was achieved by maximum clicking and minimum generalization of the script.

this approach uses the two commands: Combine and Text to column. combine has the advantage that it ignores missing values. in this case it is used as concatenate without missing values.

then, text to columns comes in hand when we want the final data in separate columns.

please do let us know if it works or needs further generalization for your purposes.

ron

dt = New Table( "original data set",
	Add Rows( 36 ),
	New Column( "Data 01",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 1, 1, ., ., ., ., ., ., ., ., ., ., ., ., 11, 11, 11, ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .] )
	),
	New Column( "Data 02",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [., ., ., 2, 2, 2, ., ., ., ., ., ., ., ., ., ., ., ., 12, 12, 12, ., ., ., ., ., ., ., ., ., ., ., ., ., ., .] )
	),
	New Column( "Data 03",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., 3, 3, 3, ., ., ., ., ., ., ., ., ., ., ., ., 13, 13, 13, ., ., ., ., ., ., ., ., ., ., ., .] )
	),
	New Column( "Data 04",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., 4, 4, 4, ., ., ., ., ., ., ., ., ., ., ., ., 14, 14, 14, ., ., ., ., ., ., ., ., .] )
	),
	New Column( "Data 05",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., ., ., ., 5, 5, 5, ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .] ),
		Set Display Width( 59 )
	),
	New Column( "Data 06",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [10, 10, 10, ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .] )
	),
	New Column( "Data 07",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [., ., ., 20, 20, 20, ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., 200, 200, 200, ., ., ., ., ., ., ., ., .] )
	),
	New Column( "Data 08",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., 30, 30, 30, ., ., ., ., ., ., ., ., ., ., ., ., 300, 300, 300, ., ., ., ., ., ., ., ., ., ., ., .] )
	),
	New Column( "Data 09",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., 40, 40, 40, ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .] ),
		Set Display Width( 67 )
	),
	New Column( "Data 10",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., ., ., ., 50, 50, 50, ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .] )
	)
);
// now we get to work
// Get the columns names to combine.
colNames = dt << get column names( string, continuous );


// Combine columns using list - here is the trick, it ignores missing values
dt << Combine Columns(
	columns( eval (colNames) ),
	Column Name( "combined" ),
	Delimiter( "," ),
	Multiple Response( 0 )
);

// Text to columns - now we can uncombine the data into as many columns there is actual data. this is robust to any number of events per row.
dt << Text to Columns(
	columns( :combined ),
	Delimiters( "," )
);