cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
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
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
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;
	);
);
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....
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.

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.