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

JSL question: select columns and replace missing values with zero

pomemelom — Wed, 01 Mar 2017 17:50:35 GMT

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

pmroz — Wed, 01 Mar 2017 17:51:54 GMT

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

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

pomemelom — Wed, 01 Mar 2017 18:02:30 GMT

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....

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

pmroz — Wed, 01 Mar 2017 18:05:47 GMT

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 zero

r23426 — Wed, 01 Mar 2017 21:53:05 GMT

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.