Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
Nata007
Level I

Select Multiple Columns and Update Missing Column Value

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 ACensor BCensor C
F F
FF 
F  
  F
 FF
F  
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Select Multiple Columns and Update Missing Column Value

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"
		);
	)
);
Jim

View solution in original post

4 REPLIES 4
Highlighted
txnelson
Super User

Re: Select Multiple Columns and Update Missing Column Value

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"
		);
	)
);
Jim

View solution in original post

Highlighted
Nata007
Level I

Re: Select Multiple Columns and Update Missing Column Value

Thank you!
Highlighted
Nata007
Level I

Re: Select Multiple Columns and Update Missing Column Value

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

Re: Select Multiple Columns and Update Missing Column Value

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]]
			)
		);
	)
);
Jim
Article Labels

    There are no labels assigned to this post.