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
- :
- Re: How to replace Missing Values w/ '0' 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
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 27, 2012 11:51 PM
(21235 views)

How do I replace Missing Values w/ '0' using JSL?

I can do this manually using 'Find and Replace All', but I need to incorporate it in a code that runs automatically everyday.

1 ACCEPTED SOLUTION

Accepted Solutions

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

Yet another way to do it but with a single loop (Numeric columns only).

```
dt = Current Data Table();
nc = dt << get column names( Numeric );
For( i = 1, i <= N Items( nc ), i++,
nc[i][dt << get rows where( Is Missing( nc[i][] ) )] = 0
);
```

13 REPLIES 13

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

Re: How to replace Missing Values w/ '0' using JSL

Hi,

This is based on book 'Jump into scripting'. I replace all the missing values with z.

```
allCols = dt2 << Get Column Names( String );
For( i = 1, i <= N Items( allCols ), i++,
/* Loop through each row. */
For( j = 1, j <= N Rows( dt2 ), j++,
/* The IsMissing function will return a ‘1’ or true if the
value is missing. If true, cell is assigned zero. */
If( Is Missing( Column( dt2, allCols ) ),
Column( dt2, allCols ) = "z"
)
)
);
```

Hope this helps.

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

Re: How to replace Missing Values w/ '0' using JSL

I would avoid using j as a looping variable because it is also a JSL function.

Function: Creates a matrix of identical values.

nrows: Number of rows in matrix. If `ncols`

is not specified, `nrows`

is also used as `ncols`

.

ncols: Number of columns in matrix.

value: The value used to populate the matrix. If `value`

is not specified, 1 is used.

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

Re: How to replace Missing Values w/ '0' using JSL

This is essentially the same as exj's response but a little cleaner. It does; however, only change missing in Numeric columns.

```
dt = current data table();
for(ij = 1, ij <= NCol(dt), ij +=1,
CurCol = Column(dt, ij);
If(CurCol << get data type == "Numeric",
For Each Row(
IF(IsMissing(CurCol[]), CurCol[] = 0)
);
);
);
```

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

Yet another way to do it but with a single loop (Numeric columns only).

```
dt = Current Data Table();
nc = dt << get column names( Numeric );
For( i = 1, i <= N Items( nc ), i++,
nc[i][dt << get rows where( Is Missing( nc[i][] ) )] = 0
);
```

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

Re: How to replace Missing Values w/ '0' using JSL

thanks everone!

cheers!

Highlighted
##

Sorry to be late to this party, but I feel compelled to interject: Setting missing values to zero (or the mean or any other single imputation method) has serious drawbacks. Most of these approaches produce biased parameter estimates, even in ideal conditions where the data are Missing Completely At Random. There are much better ways to deal with missing data.

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

Re: How to replace Missing Values w/ '0' using JSL

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

Re: How to replace Missing Values w/ '0' using JSL

While what you say is true, it makes assumptions about the data. For example, maybe his data isn't continuous? Sometimes 0 isn't recorded and instead left as missing. If someone were to try and do a Oneway analysis using 0 as false and 1 as true they'd have a problem if their zero's ended up displaying as missing, since Oneway analysis ignores missing rows in JMP. They'd end up with ONLY ONE category!

There are other reasons 0 could show up as missing. Usually storing 0 in a large database is a waste of space. Since Pokemon Go is big right now, I'll use it as an example. I might want to record all the pokemon I caught in a day, 12 ratattas and 6 pidgeys. I wouldn't however, want to then store 0 for the other 148 pokemon I didn't catch that day. If I did I'd quickly fill up my database with useless information. Thus, if I queried my data I'd get missing for most of my pokemon at any given time, but missing is = to 0.

In similar situations you could get missing data that are equivalent to 0 b/c of data conversion issues, poorly designed queries, ect.

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

Re: How to replace Missing Values w/ '0' using JSL

*can* do something in JMP doesn't always mean we *should*.

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

Re: How to replace Missing Values w/ '0' using JSL

Absolutely, I appreciate the word of caution. I for sure, didn't think about it.

It's also important to remember that missing data can be valuable data, and sometimes it's best to leave it as is!