Hide, Exclude, Delete Rows based on Missing Values

Yngeinstn — Fri, 09 Jun 2023 23:28:17 GMT

I have a table that has ~ 4,000,000 rows of data that include a very large variety of test conditions. I use the Explore Outlier function within JMP to change cell values that meet the outlier criteria to missing. I then want to take the row column id associated with the missing values and exclude them from the main data set. I have tried Get Rows Where( Is Missing( :col ) but i can't figure a way to effectively translate it to the main table. This is the best i can come up with at this moment. Any and all help is always appreciated.

Try( If( Is Scriptable( dt_screen ), Close( dt_Screen, No Save ) ) );

Current Data Table( ::dt_testdata );

::dt_testdata = Current Data Table();

input_1 = {8, 8.25, 8.5, 8.75, 9, 9.25, 9.5, 9.75, 10, 10.25, 10.5, 10.75, 11, 14.75, 15, 15.25, 15.5, 15.75, 16, 16.25, 16.5, 16.75, 17, 17.25, 17.5, 17.75};

setid = {1100};

i = 1;

For( i = 1, i <= N Items( setid ), i++,
	id = setid[i];
	
	dt_Screen = New Table( "Screened" );
	
	For( j = 1, j <= N Items( input_1 ), j++,
		input_2 = input_1[j];
		
		a = ::dt_testdata << Get Rows Where( :set_id == id & :teststate == "6" & :Input == input_2 );
		
		If( j == 1,
			b = Column(::dt_testdata, "wafer_number")[a];
			Wait( 0 );
			c = Column(::dt_testdata, "part_number")[a];
			Wait( 0 );
			d = Column(::dt_testdata, "channel")[a];
			Wait( 0 );
			e = Column(::dt_testdata, "RowCol_Chnl_PartNo" )[a];
			Wait( 0 );
			dt_Screen << New Column( "wafer_number", Character, Values( b ) );
			Wait( 0 );
			dt_Screen << New Column( "part_number", Character, Values( c) );
			Wait( 0 );
			dt_Screen << New Column( "channel", Character, Values( d ) );
			Wait( 0 );
			dt_Screen << New Column( "RowCol_Chnl_PartNo", Character, Values( e ) );
		);
		
		f = Column( ::dt_testdata, Eval( "Output" ) )[a];
			
		Wait( 0 );
			
		dt_Screen << New Column( Eval Insert( "Output @ ^input_2^"), Numeric, Values( f ) );
	);
	
	colsToScreen = dt_Screen << Get Column Names( Numeric );
	
	Show( colsToScreen );
	
	z = 1;
		
	For( z = 1, z <= N Items( colsToScreen ), z++,
		cols = Char( colsToScreen[z] );
		test = colsToScreen[z];
		
		eo = dt_Screen << Explore Outliers(
			Y( Column( cols ) ),
			K Sigma( 3 ),
			Robust Fit Outliers( 1 ),
			Show Only Columns With Outliers( 1 ),
			Invisible
		);

		eoRep = Report( eo );
		
		table = eoRep[Table Box( 1 )];
		
		colListEO = eoRep[String Col Box( 1 )];
			
		nCols = N Items( colListEO << get );
		
		For( c = 1, c <= nCols, c++, 
		
			CMD = Expr(
				table << Set Selected Rows( {colTBD} )
			);
			
			Substitute Into( CMD, Expr( colTBD ), Eval( c ) );
			CMD;

			eo << ColorCells( 1 );
			
			eo << ChangeToMissing( 1 );
			
		);
	);
	
	b = 1;
	
	For( b = 2, b <= N Items( colsToScreen ), b++,
		cols = colsToScreen[b];
		
		RowColToDelete = dt_screen << Get Rows Where( Is Missing( cols ) );
		
		c = 1;
		
		For( c = 1, c <= N Items( RowColToDelete ), c++,
			row = RowColToDelete[c];
			
			::dt_testdata << Select Where( :RowCol_Chnl_PartNo == row ) << Hide( 1 ) << Exclude( 1 );
			
			Wait( 0 );
		);
	);		
);

Re: Hide, Exclude, Delete Rows based on Missing Values

txnelson — Fri, 05 Jun 2020 17:23:54 GMT

If I am understanding your issue, the examples below might give you some insight into how to find the missing value rows

names default to here(1);
dt=open("$SAMPLE_DATA/big class.jmp");

dt:weight[3]=.;
dt:weight[5]=.;

col= dt<<get column names(continuous);
z=col[2];
x=dt<<get rows where(isMissing(ascolumn(z))==1);

// or


names default to here(1);
dt=open("$SAMPLE_DATA/big class.jmp");

dt:weight[3]=.;
dt:weight[5]=.;

col="weight";
x=dt<<get rows where(isMissing(ascolumn(z))==1);

Re: Hide, Exclude, Delete Rows based on Missing Values

Yngeinstn — Fri, 05 Jun 2020 18:58:44 GMT

Thanks for the reply Mr. Nelson. The problem isn't finding the missing rows. The problem is the time it takes to get the row column id's that are missing and Hiding and Excluding them in the main table... If you were to plot x = Input and Y = Output as you see in the example. The remove outliers will find the data that is outside the distribution and change it to missing on the dt_screen. I want to be able to take row column id's and hide / exclude them in the main table to clean up this plot..

topic Hide, Exclude, Delete Rows based on Missing Values in Discussions

Hide, Exclude, Delete Rows based on Missing Values

Re: Hide, Exclude, Delete Rows based on Missing Values

Re: Hide, Exclude, Delete Rows based on Missing Values