topic Re: Deleting cell values based on condition in Discussions

Deleting cell values based on condition

fsanjuan — Fri, 06 Sep 2019 16:44:57 GMT

Hi!

I'm currently working on a project using JMP/JSL and on my data, I need to delete or clear values from multiple cells based on a condition. See sample raw data and required output below.

Raw data:

Test1	P/F 1	Test2	P/F 2	Test3	P/F 3	Test4	P/F 4	Test5	P/F 5
1	P	2	P	4	P	9	F	3	P
5	P	8	F	4	P	6	F	2	P
7	P	1	P	6	F	5	F	6	F
4	P	1	P	9	F	9	F	9	F
6	F	4	P	10	F	10	F	8	F

Required Output:

Test1	P/F 1	Test2	P/F 2	Test3	P/F 3	Test4	P/F 4	Test5	P/F 5
1	P	2	P	4	P	9	F	*	*
5	P	8	F	*	*	*	*	*	*
7	P	1	P	6	F	*	*	*	*
4	P	1	P	9	F	*	*	*	*
6	F	*	*	*	*	*	*	*	*

What is required here is to clear all following cells once 1st F (failure) is triggered.

I'm working on large data and I believe this can only be done through scripting.

Tried looping functions but none so far works. I'm really not sure if this is even possible on JSL.

Thank you in advance for your help!

Re: Deleting cell values based on condition

vince_faller — Fri, 06 Sep 2019 17:06:13 GMT

See if this works for you.

Names default to here(1);
dt = New Table( "Example",
	Add Rows( 5 ),
	New Column( "Test1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [1, 5, 7, 4, 6] )
	),
	New Column( "P/F 1",
		Character( 16 ),
		"Nominal",
		Set Values( {"P", "P", "P", "P", "F"} )
	),
	New Column( "Test2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [2, 8, 1, 1, 4] )
	),
	New Column( "P/F 2",
		Character( 16 ),
		"Nominal",
		Set Values( {"P", "F", "P", "P", "P"} )
	),
	New Column( "Test3",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [4, 4, 6, 9, 10] )
	),
	New Column( "P/F 3",
		Character( 16 ),
		"Nominal",
		Set Values( {"P", "P", "F", "F", "F"} )
	),
	New Column( "Test4",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [9, 6, 5, 9, 10] )
	),
	New Column( "P/F 4",
		Character( 16 ),
		"Nominal",
		Set Values( {"F", "F", "F", "F", "F"} )
	),
	New Column( "Test5",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [3, 2, 6, 9, 8] )
	),
	New Column( "P/F 5",
		Character( 16 ),
		"Nominal",
		Set Values( {"P", "P", "F", "F", "F"} )
	)
);

bad_rows = [];
for(i=1, i<=ncols(dt), i++, 
	col = Column(dt, i);
	
	if(col << Get Data Type == "Character", //this is an assumption that your P/F cols are the only char
		if(nrows(bad_rows), //check if there's anything to clear
			col[bad_rows] = ""; // clear character
		);
		bad_rows |/= loc(col <<Get Values, "F"); // add the rows that you want to clear
	, 
		if(nrows(bad_rows), //check if there's anything to clear
			col[bad_rows] = .; // clear numeric
		);
	)
);

Re: Deleting cell values based on condition

fsanjuan — Fri, 06 Sep 2019 22:46:15 GMT

This really solved the problem!!! Kudos!

I was also surprised it ran less than a second for thousands of cells.

Thanks!!