topic Select Multiple Columns and Update Missing Column Value in Discussions

Select Multiple Columns and Update Missing Column Value

Nata007 — Wed, 29 May 2019 05:21:14 GMT

Hello,

I am new to JMP scripting. I am working on a script to slelect multiple columns that contain the word "Censor" and update missing value. below is a set of sample data. I've listed three columns Censor A, Censor B, and Censor C. but in real world, the number of columns can be different every time. so I will have to select columns that contain the work " Censor", then update all missing value with "S". Can I do that in JSL? I am stil learning scripting but could not find a way in JMP Help.

Censor A	Censor B	Censor C
F		F
F	F
F
		F
	F	F
F

Re: Select Multiple Columns and Update Missing Column Value

txnelson — Wed, 29 May 2019 05:48:36 GMT

Here is a simple script that will accomplish the filling in of the missing cells

Names Default To Here( 1 );

// Create a sample data table
dt = New Table( "Example",
	Add Rows( 6 ),
	New Column( "Censor A",
		Character,
		"Nominal",
		Set Values( {"F", "F", "F", "", "", "F"} )
	),
	New Column( "Censor B",
		Character,
		"Nominal",
		Set Values( {"", "F", "", "", "F", ""} )
	),
	New Column( "Censor C",
		Character,
		"Nominal",
		Set Values( {"F", "", "", "F", "F", ""} )
	)
);

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

// Loop across all columns and if a Censor column
// then process
For( i = 1, i <= N Items( colNames ), i++,
	If( Left( colNames[i], 6 ) == "Censor",
		dt << select where( As Column( colNames[i] ) == "" );
		// If blanks are found then process
		If( N Rows( dt << get selected rows ) > 0,
			As Column( colNames[i] )[dt << get selected Rows] = "S"
		);
	)
);

Re: Select Multiple Columns and Update Missing Column Value

Nata007 — Wed, 29 May 2019 05:52:46 GMT

Thank you!

Re: Select Multiple Columns and Update Missing Column Value

Nata007 — Wed, 29 May 2019 05:57:32 GMT

Thanks for your help! Here is another questions. for above case, how do I have update missing value with value in another column?

Re: Select Multiple Columns and Update Missing Column Value

txnelson — Wed, 29 May 2019 06:10:57 GMT

Here is a modification to my initial script to do what I think you want.....

Names Default To Here( 1 );

// Create a sample data table
dt = New Table( "Example 2",
	Add Rows( 6 ),
	New Column( "Censor A",
		Character,
		"Nominal",
		Set Values( {"F", "F", "F", "", "", "F"} )
	),
	New Column( "Censor B",
		Character,
		"Nominal",
		Set Values( {"", "F", "", "", "F", ""} )
	),
	New Column( "Censor C",
		Character,
		"Nominal",
		Set Values( {"F", "", "", "F", "F", ""} )
	),
	New Column( "Setting Column",
		Character,
		"Nominal",
		Set Values( {"Z", "X", "Y", "W", "P", "Q"} )
	)
);

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

// Loop across all columns and if a Censor column
// then process
For( i = 1, i <= N Items( colNames ), i++,
	If( Left( colNames[i], 6 ) == "Censor",
		dt << select where( As Column( colNames[i] ) == "" );
		// If blanks are found then process
		rowMat = dt << get selected rows;
		If( N Rows( rowMat ) > 0,
			For( k = 1, k <= N Rows( rowMat ), k++,
				Column( colNames[i] )[rowMat[k]] = :Setting Column[rowMat[k]]
			)
		);
	)
);