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
- :
- Discussions
- :
- Remove rows with one or more empty values using JSL

Topic Options

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

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

May 15, 2012 1:50 PM
(14985 views)

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 =

Measurement | T1 | T2 | T3 |
---|---|---|---|

Measurement A | 5 | 12 | 89 |

Measurement B | 13 | 8 | |

Measurement C | 52 | 57 | |

Measurement D | 22 | 21 | |

Measurement E | 8 | ||

Measurement F | 15 | ||

Measurement G | 54 |

I need to produce:

dtClean =

Measurement | T1 | T2 | T3 |
---|---|---|---|

Measurement A | 5 | 12 | 89 |

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

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

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;`

8 REPLIES

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

Re: Remove rows with one or more empty values using JSL

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} ) ) ) );`

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

Re: Remove rows with one or more empty values using JSL

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
);
```

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

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;`

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

Re: Remove rows with one or more empty values using JSL

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.

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

Re: Remove rows with one or more empty values using JSL

Great discussion. We had similar issue, where importing from sensor files we had a lot of empty rows at the end of the file. We wanted to remove rows where no sensor data was located. However, did not want to remove rows where there still was some information. Below was written in JMP14, but think it will work for previous versions. In JMP14, I know there is Row Selection > Select Duplicate Rows. But could not find a similar function that would sum all the numeric columns across one row. In the script below, Sum() would return null across the row in the matrix, but I wanted a zero. Found the output of V Sum() would return 0 for all null. To make work, just had to transpose the row vector into a column vector. This works for no matter how many numeric columns you have, as long as they are all empty. Still interested if someone has a better solution.

```
dt = Current Data Table();
// Get all numeric data
m = dt << Get As Matrix;
// for each row, test if the V Sum is 0 or non-zero
// V Sum returns 0 for all null
// Sum() returns null for all null
rowlist = {};
For(i= N Rows(m), i>=1,i--,
If(V Sum(Transpose(m[i,0]))[1] == 0,
Insert Into(rowlist,i)
)
);
dt << Delete Rows(rowlist);
```

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

Re: Remove rows with one or more empty values using JSL

Hi,

I am trying to do slightly the same.

I need to check if a cell is empty and then delete the row if yes.

I tried the following -

dt << select where( Is Missing( Column( dt,"Value" )[7] ) ) << delete rows;

But it deletes all rows in the table.

Looking for ideas.

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

Re: Remove rows with one or more empty values using JSL

Your code is only checking for Row 7. You need to test each row

`dt << select where( Is Missing( Column( dt,"weight" )[row()] ) ) << delete rows;`

Jim

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

Re: Remove rows with one or more empty values using JSL

If you're only checking a single cell:

```
if ( Is Missing( Column( dt,"Value" )[7] ),
dt << delete rows([7]);
);
```