Subscribe Bookmark RSS Feed

Remove rows with one or more empty values using JSL

mwyant

Community Trekker

Joined:

May 15, 2012

I have a table where each row contains a single measurement type at three temperatures, each stored in its own column. Ultimately, I want to calculate the deltas between these measurements and then stack that data into a new table. However, not all measurements are taken at all three temperatures, and I need to exclude those measurements (rows) where one or more of the data columns are empty. I need to do this using a JMP 9.0 script.

Given:

dtSplit =

MeasurementT1T2T3
Measurement A51289
Measurement B138
Measurement C5257
Measurement D2221
Measurement E8
Measurement F15
Measurement G54

I need to produce:

dtClean =

MeasurementT1T2T3
Measurement A51289

I was trying to use something like:

dtSplit << Select Where(
     Is Empty(:T1) ||
     Is Empty(:T2) ||
     is Empty(:T3)
) << Delete Rows;

However, any Select Where(Is Empty(:COL)) always selects all rows.

I'm sure I am missing something simple, your help is greatly appreciated, thanks,

- Matt

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

You were using the string concatenation operator || instead of the OR operator |.  As MS showed you need to use the Is Missing function and not Is Empty.  Your code becomes:

dtsplit << Select Where(Is missing(:T1) | Is missing(:T2) | is missing(:T3)) << Delete Rows;

4 REPLIES
ms

Super User

Joined:

Jun 23, 2011

This can be accomplished in different ways. For numeric columns matrix functions often are effective.

Here is an example deleting all rows with missing cells in columns 2, 3 & 4:

keeprows = Loc Nonmissing( dtSplit << get as matrix( {2, 3, 4} ) );

dtSplit << select rows( keeprows ) << invert Row Selection << delete rows;

Here is an alternative approach making a subset of the columns with no missing cells (keeping the original table unchanged):

dtClean = dtSplit << Clear Column Selection() << subset( rows( Loc Nonmissing( dt << get as matrix( {2, 3, 4} ) ) ) );

ms

Super User

Joined:

Jun 23, 2011

Sorry, I missed the JMP9 requirement. The function Loc Nonmissing() appears to be new to JMP 10 so the above do not work in JMP 9.

Heres an alternative approach that works in JMP 9, similar to your code but using a loop.

For( i = 2, i <= 4, i++,

  dtSplit << select where( Is Missing( Column( i )[] ) ) << delete rows

);

Solution

You were using the string concatenation operator || instead of the OR operator |.  As MS showed you need to use the Is Missing function and not Is Empty.  Your code becomes:

dtsplit << Select Where(Is missing(:T1) | Is missing(:T2) | is missing(:T3)) << Delete Rows;

mwyant

Community Trekker

Joined:

May 15, 2012

Using the concatenation operator was a transcription error--old habits die hard.

turns out the problem was my column names, which were actually '88', '95' and '105', and using Is Missing(:88) selected all rows.


dtsplit << Select Where(Is missing(:Name("T1")) | Is missing(:Name("T2")) | is missing(:Name("T3"))) << Delete Rows;


Works great.