nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

64614_en-US

liliconcom

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 <SPAN class="token operator">=</SPAN> <SPAN class="token function">Current Data Table</SPAN><SPAN class="token punctuation">(</SPAN><SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">;</SPAN>
nc <SPAN class="token operator">=</SPAN> dt <SPAN class="token operator"><</SPAN><SPAN class="token operator"><</SPAN><SPAN class="token messages"> get column names</SPAN><SPAN class="token punctuation">(</SPAN> Numeric <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">;</SPAN>
<SPAN class="token function">For</SPAN><SPAN class="token punctuation">(</SPAN> i <SPAN class="token operator">=</SPAN> <SPAN class="token number">1</SPAN><SPAN class="token punctuation">,</SPAN> i <SPAN class="token operator"><=</SPAN> <SPAN class="token function">N Items</SPAN><SPAN class="token punctuation">(</SPAN> nc <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,</SPAN> i<SPAN class="token operator">++</SPAN><SPAN class="token punctuation">,</SPAN>
  nc<SPAN class="token punctuation">[</SPAN>i<SPAN class="token punctuation">]</SPAN><SPAN class="token punctuation">[</SPAN>dt <SPAN class="token operator"><</SPAN><SPAN class="token operator"><</SPAN><SPAN class="token messages"> get rows where</SPAN><SPAN class="token punctuation">(</SPAN> <SPAN class="token function">Is Missing</SPAN><SPAN class="token punctuation">(</SPAN> nc<SPAN class="token punctuation">[</SPAN>i<SPAN class="token punctuation">]</SPAN><SPAN class="token punctuation">[</SPAN><SPAN class="token punctuation">]</SPAN> <SPAN class="token punctuation">)</SPAN> <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">]</SPAN> <SPAN class="token operator">=</SPAN> <SPAN class="token number">0</SPAN>
<SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">;</SPAN>

Thanks!

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

Re: Replace missing values with 0

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

Re: Replace missing values with 0

Dear Ian and Craige,

Thank you so much for your submissions. I ran some preliminary tests and Ian's solution is more than 4 times faster than Recode for this and Craige's solution is almost 10 times faster. If anyone would like to compare all 3 solutions, try the following:

//Recode solution

nr = 10000;  // Number of rows
nc = 10000;  // Number of columns
nm = 0.9;  // Proportion of missing values

// Make a table and inject some missing values
mat = J(nr*nc, 1, RandomNormal());
mat[RandomIndex(nr*nc, nr*nc*nm)] = .;
dt = AsTable(Shape(mat, nr, nc));
dt << setName("Data with some missingness");

replaceMissingStart = HP Time();

// replace missings with zeros
nc = dt << get column names( Numeric );

For( i = 1, i <= N Items( nc ), i++,
 col = Column(dt,nc[i]);
 colname = col << getname();

    Eval(Parse(EvalInsert("\[
  dt << Recode Column(
   :Name("^colname^"),
   {Map Value( _rcOrig, {., 0}, Unmatched( _rcNow ) )},
   Update Properties( 1 ),
   Target Column( :Name("^colname^") )
  );
 ]\")));
 
 //Code from the Community
 //Column( nc[i] )[dmdt << get rows where( Is Missing( As Column( nc[i] ) ) )] = 0;

 // standardize column attributes, change all to nominal
 //col << Set Modeling Type( "Nominal" ) 
);

replaceMissingStop = HP Time();
totalReplaceTime = replaceMissingStop-replaceMissingStart;
show(totalReplaceTime/1000000);

Russ Wolfinger had mentioned he thought matrices would solve the problem more quickly, I guess that was what you two were thinking as well!

Re: Replace missing values with 0

Combining solutions, with data table subscripting...

nr = 10000;  // Number of rows
nc = 10000;  // Number of columns
nm = 0.9;  // Proportion of missing values

// Make a table and inject some missing values
mat = J(nr*nc, 1, RandomNormal());
mat[RandomIndex(nr*nc, nr*nc*nm)] = .;
dt = AsTable(Shape(mat, nr, nc));
dt << setName("Data with some missingness");


replaceMissingStart = HP Time();

nc = dt << get column names( Numeric );
mat = dt[0,nc];
mat[Loc(mat, .)] = 0;
dt[0,nc] = mat;


replaceMissingStop = HP Time();
totalReplaceTime = replaceMissingStop-replaceMissingStart;
show(totalReplaceTime/1000000);

About 10 seconds.

Re: Replace missing values with 0

FWIW, this sort of approach might be better. For 10,000 by 10,000 it took about 10 seconds on my laptop (including making the starting table):

NamesDefaultToHere(1);

// Size the problem . . .
nr = 10000;  // Number of rows
nc = 10000;  // Number of columns
nm = 0.1;  // Proportion of missing values

// Make a table and inject some missing values
mat = J(nr*nc, 1, RandomNormal());
mat[RandomIndex(nr*nc, nr*nc*nm)] = .;
dt = AsTable(Shape(mat, nr, nc));
dt << setName("Data with some missingness");

// Start here: Get values into a matrix (assumes contiguous numeric columns)
mat = dt << getAsMatrix;
// Reshape as a vector
mat = Shape(mat, nr*nc, 1);
// Replace missing
mat[Loc(mat, .)] = 0;
// Reshape back to a matrix
mat = Shape(mat, nc, nc);
// Make a new table
dt2 = AsTable(Shape(mat, nr, nc));
dt2 << setName("Data with missing set to zero values");

Re: Replace missing values with 0

Hi all,

I have been working on this exact problem with a hundreds of columns with hundreds of missing rows. I wasn't happy with the performance and tried many approaches. It seems that Recode solves this problem about 150 times faster than the current solution. Sharing the code with anyone else that might care about speed. Please let me know if you find something faster. Every millisecond counts!

</CODE> <PRE><CODE class=" language-jsl">replaceMissingStart = HP Time(); // replace missings with zeros nc = dmdt << get column names( Numeric ); For( i = 1, i <= N Items( nc ), i++, col = Column(dt,nc[i]); colname = col << getname(); Eval(Parse(EvalInsert("\[ dmdt << Recode Column( :Name("^colname^"), {Map Value( _rcOrig, {., 0}, Unmatched( _rcNow ) )}, Update Properties( 1 ), Target Column( :Name("^colname^") ) ); ]\"))); //Code from the Community //Column( nc[i] )[dmdt << get rows where( Is Missing( As Column( nc[i] ) ) )] = 0; // standardize column attributes, change all to nominal col << Set Modeling Type( "Nominal" ) ); replaceMissingStop = HP Time(); totalReplaceTime = replaceMissingStop-replaceMissingStart; show(totalReplaceTime/1000000);

</CODE> Thanks to Rebecca Lyzinski for the idea!!!</LINGO-BODY><LINGO-SUB id="lingo-sub-228122" slang="en-US" mode="NONE">Re: Replace missing values with 0</LINGO-SUB><LINGO-BODY id="lingo-body-228122" slang="en-US" mode="NONE">I use this script constantly.  Can't thank you enough for providing it.</LINGO-BODY><LINGO-SUB id="lingo-sub-65695" slang="en-US" mode="NONE">Re: Replace missing values with 0</LINGO-SUB><LINGO-BODY id="lingo-body-65695" slang="en-US" mode="NONE">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.</LINGO-BODY><LINGO-SUB id="lingo-sub-65039" slang="en-US" mode="NONE">Re: Replace missing values with 0</LINGO-SUB><LINGO-BODY id="lingo-body-65039" slang="en-US" mode="NONE">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.</LINGO-BODY><LINGO-SUB id="lingo-sub-64972" slang="en-US" mode="NONE">Re: Replace missing values with 0</LINGO-SUB><LINGO-BODY id="lingo-body-64972" slang="en-US" mode="NONE">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?  </LINGO-BODY><LINGO-SUB id="lingo-sub-64943" slang="en-US" mode="NONE">Re: Replace missing values with 0</LINGO-SUB><LINGO-BODY id="lingo-body-64943" slang="en-US" mode="NONE">Hi <A href="https://community.jmp.com/t5/user/viewprofilepage/user-id/12425" target="_blank">@nshivan</A>,I ran Jim's code in versions 13 and 14 and got no errors.  Maybe restart JMP and run it again?</LINGO-BODY><LINGO-SUB id="lingo-sub-64931" slang="en-US" mode="NONE">Re: Replace missing values with 0</LINGO-SUB><LINGO-BODY id="lingo-body-64931" slang="en-US" mode="NONE">Hi Jim,    I tried the above, but getting the folliowing error.      <PRE><CODE class=" language-jsl">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,

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

Comments

There are no comments yet