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

How to replace Missing Values w/ '0' using JSL

ambs

How do I replace Missing Values w/ '0' using JSL?

I can do this manually using 'Find and Replace All', but I need to incorporate it in a code that runs automatically everyday.

Accepted answers

Yet another way to do it but with a single loop (Numeric columns only).

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

All comments

exj

Hi,

This is based on book 'Jump into scripting'. I replace all the missing values with z.

allCols = dt2 << Get Column Names( String );
For( i = 1, i <= N Items( allCols ), i++,
/* Loop through each row. */
For( j = 1, j <= N Rows( dt2 ), j++,
/* The IsMissing function will return a ‘1’ or true if the
value is missing. If true, cell is assigned zero. */
  If( Is Missing( Column( dt2, allCols ) ),
   Column( dt2, allCols ) = "z"
  )
)
);

Hope this helps.

pmroz

I would avoid using j as a looping variable because it is also a JSL function.

J(nrows, <ncols>, <value>)

Function: Creates a matrix of identical values.

Returns: The matrix.

Arguments:

nrows: Number of rows in matrix. If ncols is not specified, nrows is also used as ncols.

ncols: Number of columns in matrix.

value: The value used to populate the matrix. If value is not specified, 1 is used.

jpkim

This is essentially the same as exj's response but a little cleaner. It does; however, only change missing in Numeric columns.

dt = current data table();
for(ij = 1, ij <= NCol(dt), ij +=1,
                CurCol = Column(dt, ij);
                If(CurCol << get data type == "Numeric",
                                For Each Row(
                                                IF(IsMissing(CurCol[]), CurCol[] = 0)
                                );
                );
);

Yet another way to do it but with a single loop (Numeric columns only).

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

ambs

thanks everone!

cheers!

jkwiggins

Another way...

clsMat = dt << Get As Matrix( {"colA", "colB"} );

clsMat[Loc( Is Missing( clsMat ) )] = 0;

:colA << Set Values( clsRej[0, 1] );
:colB << Set Values( clsRej[0, 2] );

etc.

logical indexing is often faster than loops

masakit

cool! ... in the last 2 lines, clsRej should be replaced to clsMat, right?

meanwhile, do we have any similar solutions for character column?

msharp

Since no one else answered, the solution is the same, just grab character columns instead and set the rows equal to the character you find fitting--"N/A" in the below case.

Just be careful, setting it =0 will change the column to numeric and will remove all the character data, however ="0" should be fine.

dt = Current Data Table();
nc = dt << get column names( Character );

For( i = 1, i <= N Items( nc ), i++,
&nbsp; nc[i][dt << get rows where( Is Missing( nc[i][] ) )] = "N/A"
);

Kevin_Anderson

Sorry to be late to this party, but I feel compelled to interject: Setting missing values to zero (or the mean or any other single imputation method) has serious drawbacks. Most of these approaches produce biased parameter estimates, even in ideal conditions where the data are Missing Completely At Random. There are much better ways to deal with missing data.

RaginCajun

This works when I am running it from a scripting window using play button, but not within a debugger or launching it by autorun of the .jsl.

dtLongestWaitStates << set name( "LongestWaitStates" );
dtLongestWaitStates = Current Data Table();
nc = dtLongestWaitStates << get column names( numeric );
For( i = 1, i <= N Items( nc ), i++,
	nc[i][dtLongestWaitStates << get rows where( Is Missing( nc[i][] ) )] = 0
);

RaginCajun

JMP13.2.1