36587_en-US

liliconcom

JSL question: select columns and replace missing values with zero

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

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.

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.

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

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