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

Deleting columns that fit a numeric criteria

TRR21

I have several thousand columns in my table and would like to trim it to only those columns that mostly have non-zero numbers. ie delete or group and collapse those columns what have zeros for more than half the rows. Any tips?

Thanks!

Accepted answers

For big data tables a matrix solution may be faster than iterating over all columns.

Example:

dt = Current Data Table();
m = dt << get all columns as matrix; // Get table as matrix
// Get list of columns with > 50% zeroes
zero_cols = (dt << get column names)[(Loc(V Sum(m == 0) / N Row(m) > 0.5))];
// Delete them (or group them)
dt << Delete Columns(zero_cols);
// dt << Group Columns("zero", zero_cols);

All comments

Mark_Bailey

I did not test it but this script gives you an idea.

Names Default to Here( 1 );

// assume that data table is open and reference to it is stored in variable dt

For( c = N Col( dt ), c > 0, c--,
	n zero = dt << Get Rows Where( Column( c )[] == 0 );
	If( n zero / N Row( dt ) > 0.5,
		dt << Delete Columns( Column( c ) );
	);
);

TRR21

Thanks - the table is open, but would need some help with the synatx for how to refer to it in the dt variable.

<SPAN class="token comment">// assume that data table is open and reference to it is stored in variable dt</SPAN>

Mark_Bailey

dt = Current Data Table();

TRR21

It is rendering JMP non-responsive! Not by CPU activity -- but just can't interact with the table window for minutes...