BookmarkSubscribeSubscribe to RSS Feed
pomemelom

Community Trekker

Joined:

Jan 31, 2017

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);
1 ACCEPTED SOLUTION

Accepted Solutions
pmroz

Super User

Joined:

Jun 23, 2011

Solution

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;
	);
);
4 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

Solution

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

Community Trekker

Joined:

Jan 31, 2017

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

Super User

Joined:

Jun 23, 2011

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.

r23426

Community Trekker

Joined:

Jul 3, 2014

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.