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

Remove rows with one or more empty values using JSL

mwyant — Tue, 15 May 2012 20:50:55 GMT

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

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

ms — Thu, 14 Jun 2018 14:10:15 GMT

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

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

ms — Thu, 14 Jun 2018 14:10:55 GMT

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

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

pmroz — Thu, 14 Jun 2018 14:09:28 GMT

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;

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

mwyant — Wed, 16 May 2012 15:51:59 GMT

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.

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

Vball247 — Thu, 09 Aug 2018 18:20:09 GMT

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

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

ENTHU — Tue, 20 Nov 2018 00:14:05 GMT

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.

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

txnelson — Tue, 20 Nov 2018 01:08:29 GMT

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;

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

pmroz — Tue, 20 Nov 2018 17:14:45 GMT

If you're only checking a single cell:

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

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

new_2_JMP — Sat, 30 Mar 2019 00:43:13 GMT

This is my first post and not sure if the attached Excel file goes through or not. If not,

I am also attaching the PNG of the sample data file
I am very new to JMP (I am using JMP 14) and need helps with the row delete script.
My data table contains data of many different devices where each device is test for
four different failures (sensitivity, mask, EVM and power) across a wide frequency ranges
from 10 MHz to 1000 Mhz. Passing devices are the ones that have empty cells "" for all four
test across frequency ranges and I would like to remove them from the data table.
(delete the rows in green of the attached Excel file - devices C, G, H, I, J, K, and N)

1st Problem: How to delete empty rows (with columns 10 MHz to 1000 MHz = empty)
Below command I found is similar but not useful because I have 100 different columns and "and"
them all out is quite lengthy and not scalable if we later decided to increase the frequency
beyound 1000 MHz

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

I also tried this command:
For( i = 2, i <= 101, i++,
dtSplit << select where( Is Missing( Column( i )[] ) ) << delete rows
);
This command gives me an error:
In the following script, error marked by /*###*/
dt = Current Data Table();
For( i = 2, i <= 100, i++,
dtSplit/*###*/ <<select where( Is Missing( Column( i )[] ) ) << delete rows
);

2nd Problem: Deleted rows must in the multiple of 4 since each device is being tested 4 times.
I am not exactly sure how to do that

would really appreciate your helps on this

Thanks
new_2_JMP

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

txnelson — Sat, 30 Mar 2019 03:05:08 GMT

You need to look into the Missing Data Pattern platform. It will allow you to identify all of the rows that have all missing values, and then you can choose to delete the rows, etc.

Tables==>Missing Data Pattern

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

new_2_JMP — Thu, 04 Apr 2019 00:28:10 GMT

Thanks for the input. Checked out the Missing Data Pattern you recommended and it sort of works but not fully.

In my data table, each name has 4 different rows. Each of the 4 row of a name is a result of 1 test (sens, mask, EVM and power)

In my data table example, device A fails the first test "Sens" and passes the 2nd, 3rd and 4th test. Because device A fails "Sens", this is a fail device even though it passes the 2nd, 3rd and 4th test.

If I use Tables==>Missing Data Pattern, then 2nd, 3rd and 4th rows of A will be deleted. Which is not what I am looking for. I want to delete 4 rows of each name when there are no data on them ( no data on 10 MHz, 20 MHz, 30 MHz and 1000 MHz). In my data table, names C, G, H, I, J K and N will be deleted.

Thanks for your helps

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

SpannerHead — Fri, 18 Apr 2025 21:41:04 GMT

This script will start working after the column titled "Names" and continue to the end of the table deleting empty rows.

// Get the current data table
dt = Current Data Table();

// Get all column names as strings
cnmes = dt << Get Column Names("string");

// Initialize Start and End indices
Start = .;
End = N Items(cnmes);

// Find the index of the "Device 2" column
For(k = 1, k <= N Items(cnmes), k++,
    If(Contains(cnmes[k], "Names"),
        Start = k + 1;
        Break(); // Exit loop once found
    );
);

// Loop through the columns from Start to End and delete rows with missing values
For(i = Start, i <= End, i++,
    dt << Select Where(Is Missing(Column(i)[])) << Delete Rows;
);