cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Yngeinstn
Level IV

Hide, Exclude, Delete Rows based on Missing Values

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 );
		);
	);		
);
2 REPLIES 2
txnelson
Super User

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

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);
Jim
Yngeinstn
Level IV

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

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..

 

Yngeinstn_1-1591383512091.png