topic How do I count number of non-blank rows across specific rows? in Discussions

How do I count number of non-blank rows across specific rows?

ConfusedUser123 — Mon, 12 Aug 2024 16:51:29 GMT

I have the table displayed here and would like to make a new column that contains "N" which is the number of columns that have numbers but only for the columns labelled "DecodeCount_BW**". For example for the first row, it should return "5", for the second row it should return "2", etc

In Excel I do this with "counta", but trying to learn JMP for improved statistical analysis, but I've getting stuck at simple things like this maybe due to my excel-formatted brain... Sorry if this is super basic but I can't find a formula on the list of formulas that does this..

Thanks

JMP17 user

Re: How do I count number of non-blank rows across specific rows?

jthi — Mon, 12 Aug 2024 17:27:35 GMT

Formulas generally do not work in this direction that easily (calculating something by row instead of column). Depending what you are doing stacking your data first might be beneficial.

You can use formula for that (it might be a bit finicky), but generally I would use scripting. Below is one example

Names Default To Here(1);

dt = New Table("Untitled 3",
	Add Rows(3),
	Compress File When Saved(1),
	New Column("Column 1", Numeric, "Continuous", Format("Best", 12), Set Values([., ., .])),
	New Column("Column 2", Numeric, "Continuous", Format("Best", 12), Set Values([., ., .])),
	New Column("AB1", Numeric, "Continuous", Format("Best", 12), Set Values([2, ., .])),
	New Column("AB2", Numeric, "Continuous", Format("Best", 12), Set Values([., 2, 1])),
	New Column("AB3", Numeric, "Continuous", Format("Best", 12), Set Values([., 5, .]))
);

dt << New Column("N", Numeric, Continuous);

cols_of_interest = Filter Each({colname}, dt << Get Column Names("String"),
	Starts With(colname, "AB");
);

For Each Row(Current Data Table(),
	:N = Sum(!Is Missing(dt[Row(), cols_of_interest]));
);

and if you really want to use formula you might be able to use something like this

As Constant(cols_of_interest = Filter Each({colname}, Current Data Table() << Get Column Names("String"),
	Starts With(colname, "AB");
));
Sum(!Is Missing(dt[Row(), cols_of_interest]));

You have to figure your own comparison to filter down to columns of interest (Starts With should work for your data also)

Re: How do I count number of non-blank rows across specific rows?

hogi — Mon, 12 Aug 2024 17:52:27 GMT

Tables/Summary can count for you. To make it count over multiple columns, you have to fist stack the data:

dt = New Table( "test",
	Add Rows( 6 ),
	New Column( "W8",Set Values( [2, 5, ., 5, ., 6] )),
	New Column( "W9",Set Values( [2, ., 3, 3, 1, 2] )),
	New Column( "W1",Set Values( [2, ., ., ., ., .] )),
	New Column( "W2",Set Values( [3, ., ., ., ., .] )),
	New Column( "W3",Set Values( [3, ., ., ., ., .] )),
	New Column( "W4",Set Values( [., ., 4, ., 2, .] )),
	New Column( "W5",Set Values( [., 2, ., ., ., .] )),
	New Column( "W6",Set Values( [., ., 5, ., 3, .] )));

dt << New Column( "row",Formula( Row() ));
dtstack =dt << Stack(	columns( :W8, :W9, :W1, :W2, :W3, :W4, :W5, :W6 ),"Non-stacked columns"n( Keep( :row ) ));
dtstack << Summary(	Group( :row ),	N( :Data )	);

btw:

"Non-stacked columns"n

a named argument which needs to be written as "..."n. Argh!

How good that enhanced log knows these details ...