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
- :
- JSL question: select columns and replace missing v...

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

Mar 1, 2017 9:34 AM
(1016 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

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

Mar 1, 2017 9:51 AM
(1368 views)

Solution

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

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

Mar 1, 2017 9:51 AM
(1369 views)

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

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

Mar 1, 2017 10:02 AM
(1008 views)

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

Mar 1, 2017 10:05 AM
(1005 views)

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.

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

Mar 1, 2017 1:53 PM
(992 views)

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.