Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Hide, Exclude, Delete Rows based on Missing Values

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Hide, Exclude, Delete Rows based on Missing Values

Jun 5, 2020 10:10 AM
(298 views)

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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