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
nshivan
Level I

Replace missing values with 0

Hello,

I'm trying to replace all missing values in a data table with 0.   The below script doesn't work.  I use JMP 13.  Is there any other way to do this?

 

dt = Current Data Table();
nc = dt << get column names( Numeric );
For( i = 1, i <= N Items( nc ), i++,
  nc[i][dt << get rows where( Is Missing( nc[i][] ) )] = 0
);

 Thanks! 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Replace missing values with 0

Here is the correct syntax for what you are trying to do

dt = Current Data Table();
nc = dt << get column names( Numeric );
For( i = 1, i <= N Items( nc ), i++,
	Column( nc[i] )[dt << get rows where( Is Missing( As Column( nc[i] ) ) )] = 0
);
Jim

View solution in original post

13 REPLIES 13
txnelson
Super User

Re: Replace missing values with 0

Here is the correct syntax for what you are trying to do

dt = Current Data Table();
nc = dt << get column names( Numeric );
For( i = 1, i <= N Items( nc ), i++,
	Column( nc[i] )[dt << get rows where( Is Missing( As Column( nc[i] ) ) )] = 0
);
Jim
nshivan
Level I

Re: Replace missing values with 0

Hi Jim, 

 

I tried the above, but getting the folliowing error. 

 

 

could not find column in access or evaluation of 'Column' , Column/*###*/(nc[i])

 

Any idea what's going on?   I use JMP 13, I'm not sure if the syntax isn't working for this version.   

 

Thanks,

N

cwillden
Super User (Alumni)

Re: Replace missing values with 0

Hi @nshivan,

I ran Jim's code in versions 13 and 14 and got no errors.  Maybe restart JMP and run it again?

-- Cameron Willden
nshivan
Level I

Re: Replace missing values with 0

Hi Cameron,

 

I restarted JMP and the script works fine on the specific data table.   The missing values get replaced with 0s. But when I integrate to the rest of my script, it gives me the below error:

could not find column in access or evaluation of 'Column' , Column/*###*/(nc[i])

 

Prior to this piece of script,  I have an original data table which I subset based on selected columns, stack the data, summarize anf then tabulate into the final data table.  I'm trying to replace missing values in the final data table with 0s.   

 

I'm fairly new to JMP scripts, not sure why the integration is giving problems.  Any idea why this is happening?

 

 

cwillden
Super User (Alumni)

Re: Replace missing values with 0

Something like this is hard to diagnose from a description.  You'd probably have to post your script (or at least enough of it to replicate the problem) for us to help with it.

-- Cameron Willden
Byron_JMP
Staff

Re: Replace missing values with 0

try being a little more specific in your script.

give the data table that has the specific column in it a handle.  Like dt=table(); or something like that.

then refer to the column in the table,  dt::nameOFCol

 

It is likely that your script is trying to modify a column that doesn't exist in the table that is current when this step runs.

JMP Systems Engineer, Health and Life Sciences (Pharma)
Evan_Morris
Level IV

Re: Replace missing values with 0

I use this script constantly.  Can't thank you enough for providing it.

JMPy-Mike
Level I

Re: Replace missing values with 0

in Can I replace missing values in Numeric column with "N/A" string?

mikedriscoll
Level VI

Re: Replace missing values with 0

>> Can I replace missing values in Numeric column with "N/A" string?

Use value labels to do that. Right click on column header, go to column properties, select Value Labels. Then put a period in the value and N/A in the label field as shown below. Then click add, and then ok.

 

To script it, follow the log recorder of above steps...

Data Table( "DT name" ):Column 904 << Value Labels( {"." = "N/A"} ) << Use Value Labels( 1 );

 

mikedriscoll_0-1673446010531.png