topic How to determine if column is non-zero and all-zero in Discussions

How to determine if column is non-zero and all-zero

UserID16644 — Fri, 09 Jun 2023 16:05:41 GMT

Hi all,

I have a data set that has an all-zero column. However, its column value is not consistent in having an all-zero value. How can I determine if it has an all-zero and when it has normal values? I just need it to create an if condition.

Expected output:

if Col1 is all-zero => print ("Col1 is all zero"), print ("Col1 is non-zero);

Re: How to determine if column is non-zero and all-zero

txnelson — Tue, 24 Jan 2023 09:35:35 GMT

Here are a few different ways to look code he solution

Names Default To Here( 1 );

If( Col Std Dev( :col1 ) == 0 & Col Sum( :col1 ) == 0,
	Print( "Col1 is all zero" ),
	Print( "Col1 is non-zero" )
);

dt = Current Data Table();
y = If( Length( Loc( dt[0, 6], 0 ) ) == N Rows( dt ),
	Print( "Col1 is all zero" ),
	Print( "Col1 is non-zero" )
);

y = If( Length( Loc( dt:col1 << get as matrix, 0 ) ) == N Rows( dt ),
	Print( "Col1 is all zero" ),
	Print( "Col1 is non-zero" )
);

Re: How to determine if column is non-zero and all-zero

Mark_Bailey — Tue, 24 Jan 2023 17:03:36 GMT

Here is another way:

Names Default to Here( 1 );

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

// create a column with all zeros
dt << New Column( "Zero", "Numeric", "Continuous", Values( J( N Row( dt ), 1, 0 ) ) );

// determine if column contains all zeros
If( Sum( (:age << Get As Matrix) == J( N Row( dt ), 1, 0 ) ) != 0, Print( "Col is all zero" ), Print( "Col is non-zero" ) );
If( Sum( (:Zero << Get As Matrix) == J( N Row( dt ), 1, 0 ) ) != 0, Print( "Col is all zero" ), Print( "Col is non-zero" ) );

Re: How to determine if column is non-zero and all-zero

jthi — Tue, 24 Jan 2023 19:05:05 GMT

Few more.

Names Default To Here(1);

dt = New Table("table",
	Add Rows(3),
	New Column("A", Numeric, "Continuous", Format("Best", 12), Set Values([0, 1, 0])),
	New Column("B", Numeric, "Continuous", Format("Best", 12), Set Values([0, 0, 0])),
	New Column("C", Numeric, "Continuous", Format("Best", 12), Set Values([1, 0, -1])),
	New Column("D", Numeric, "Continuous", Format("Best", 12), Set Values([0, 0, .]))
);

// If you have columns with missing values (:D in this case) it might yield weird results
// you might have to use IfMZ instead of If or in some cases use different calculations
// depending on how you want to see missing values (as zero or "normal" value)

For Each({col}, {:A, :B, :C, :D},
	Show(col);
	Print("min-max");
	If(Col Max(col) == Col Min(col) & Col Max(col) == 0,
		Print("Col is all zero")
	, // else
		Print("Col is non-zero")
	);
	
	Print("all");
	If(All(col << get as matrix == 0), 
		Print("Col is all zero")
	, // else
		Print("Col is non-zero")
	);

	Print("any");
	If(Any(col << get as matrix),
		Print("Col is non-zero")
	, // else
		Print("Col is all zero")
	);
	Write("\!N");
);

This might be out of scope but different methods might handle missing values differently and if you happen to have column properties set (missing value codes for example), those might also have an effect on the results and you might have to use Col Stored Value(<dt>, col, <row>)