Choose Language Hide Translation Bar
Highlighted
pomemelom
Level I

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
Highlighted
pmroz
Super User

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

View solution in original post

4 REPLIES 4
Highlighted
pmroz
Super User

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

View solution in original post

Highlighted
pomemelom
Level I

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
pmroz
Super User

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
r23426
Level II

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.

Article Labels

    There are no labels assigned to this post.