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: JSL question: select columns and replace missing values with zero

- 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

Mar 1, 2017 9:34 AM
(6157 views)

Hi,

There are many numeric columns in the data table, and all of them have missing values in them.

I want to select only few of them (considering that I know the column names already), and replace the missing values in them with zero.

I wrote the code as follows, but it doesn't work.

Could you help?

Thanks!

dt = data table("Untitled 9"); col_list = {"A","B"}; For( i = 1, i <= N Items( col_list ), i++, col_list[i][dt << get rows where( Is Missing( col_list[i][]) )] = 0);

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

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

Here's one way to do it:

dt = New Table( "Test Zeroes", Add Rows( 3 ), New Column( "Column 1", Character, "Nominal", Set Values( {"a", "b", "c"} ) ), New Column( "Column 2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, ., 3] ) ), New Column( "Column 3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [., 2, .] ) ) ); col_list = dt << get column names(string, numeric); For( i = 1, i <= N Items( col_list ), i++, missing_rows = dt << get rows where (is missing(as column(dt, col_list[i]))); if (nrows(missing_rows) > 0, column(dt, col_list[i])[missing_rows] = 0; ); );

4 REPLIES 4

Highlighted

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

Here's one way to do it:

dt = New Table( "Test Zeroes", Add Rows( 3 ), New Column( "Column 1", Character, "Nominal", Set Values( {"a", "b", "c"} ) ), New Column( "Column 2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, ., 3] ) ), New Column( "Column 3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [., 2, .] ) ) ); col_list = dt << get column names(string, numeric); For( i = 1, i <= N Items( col_list ), i++, missing_rows = dt << get rows where (is missing(as column(dt, col_list[i]))); if (nrows(missing_rows) > 0, column(dt, col_list[i])[missing_rows] = 0; ); );

Highlighted
##

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

Re: JSL question: select columns and replace missing values with zero

Thanks. But in your method you still replace missing values in all columns. What I am trying to do is: say there are 10 numeric columns, and all of them have missing values. But I only want to replace missing values in certain columns ("A" and "B" in my example). That's why in my original message I used col_list = {"A","B"}, but for some reason that doesn't work....

Highlighted
##

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

Re: JSL question: select columns and replace missing values with zero

My method will replace missing values in all __numeric__ columns. If you just want to replace values in your specific list, just provide

col_list = {"A", "B"}

in place of my definition of col_list.

Highlighted
##

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

Re: JSL question: select columns and replace missing values with zero

I used in the past the search/replace option:

Select the column you want to replace missing by zero

Search for . and replace by 0, be sure to check the "search only in selected column" option.