Function to Count Rows Within Spec

[#1]

I want to create a function that reads in a column, checks the number in each row on whether it is within spec, and output the passing count/total count/percentages.

This is what I have:

count_in_spec = Function( { col, lsl, usl },
	a=0; //pass count
	b=0; //all count
	for( i = 1, i <= N Rows(col), i++,
		if( col[i] >= lsl & col[i] <= usl, a++ );
		if( not(ismissing(col[i])), b++ );
	);
	pctpass = 100*a/b;
	pctfail = 100 - pctpass;
	
	evallist({a, b, pctpass, pctfail});
);


dt0 = current data table();
colp = dt0:name("param") << get as matrix;

results = count_in_spec(colp, 0.15, 0.30 );
	countpass = results[1];
	countall = results[2];
	pctpass = results[3];
	pctfail = results[4];

While this works, I don't like the fact that I need to use "get as matrix". It's a bit clunky.

I would like a function that works simply by writing:

results = count_in_spec( "param name", lsl, usl);

How would I modify my function so it can work this way?

[#2 · Accepted solution]

EDIT: I can no longer recommend this solution--use MS's: it shows a way to do this using a column formula; not only should it run faster, but column formulas also have the benefit of supporting grouping variables.

-Brady

Hi,

You can use the built-in matrix functions for this. I use <<get as matrix within the function, but I'm not sure how you'd avoid that without making the script more cumbersome.

Cheers,

Brady

Names Default To Here(1);

dt = astable(J(1000, 1, randomnormal(0,3)), <<column names ({x}));
	
count_in_spec = function({col, lsl, usl},
	{default local},
	vals = column(dt, col) << get as matrix;
	vals = vals[loc(!is missing (vals))];		//remove missing rows
	nr = nrow(vals);							//get number of rows
	nPass = sum(lsl <= vals <= usl);			
	evallist({nPass, nr, 100*nPass/nr, 100*(nr-nPass)/nr})  //be sure to EVAL the list!
);

//test it out...
{countpass, countall, pctpass, pctfail} = count_in_spec("x", -3, 4);

[#3 · Accepted solution]

Using the Get Rows Where() function should get you want you want

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/semiconductor capability.jmp" );

count_in_spec = Function( {col, lsl, usl},
	result = N Rows( dt << get rows where( lsl <= As Column( dt, col ) <= usl ) )
);

colNames = dt << get column names(string, continuous);

resultColList = {};
resultCountList = {};

For(i=1,i<= 10 /*Nitems(colNames)*/,i++,
	specs = column(dt,colNames[i]) << get property("spec limits");
	If(isMissing(try(specs["LSL"],.))==0 & isMissing(try(specs["USL"],.))==0,
		result = count_in_spec(colNames[i],specs["LSL"],specs["USL"]);
		insert into(resultColList,colNames[i]);
		insert into(resultCountList,result)
	)
);

new window("Results",
	outline box("In Spec Report",
		Table Box(
			string col box("Column", resultColList),
			number col box("Count", resultCountList)
		)
	)
);

[#4 · Accepted solution]

The Col Stat() functions are often efficient for directly summarizing table data, also with conditionals involved. Here's an example without the need for a matrix step.

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

count_in_spec = Function( {col, lsl, usl},
	{default local},
	a = Col Sum( lsl < As Column( col ) < usl );
	b = Col Number( col );
	pctpass = 100 * a / b;
	pctfail = 100 - pctpass;
	Eval List( {a, b, pctpass, pctfail} );
);

results = count_in_spec( Column( dt, "height" ), 55, 70 );
countpass = results[1];
countall = results[2];
pctpass = results[3];
pctfail = results[4];

[#5]

Thank you everyone. I have explored all three categories of methods. I then realized I could have made things a bit easier for myself by using something like this:

dt0 = current data table();
	dt0 << clear row states;

count_in_spec = Function( { col, lsl, usl },
	a=0; //pass count
	b=0; //all count
	
	cola = dt0:col << get as matrix;
	for( i = 1, i <= N Rows( cola ), i++,
			if( cola[i] >= lsl & cola[i] <= usl, a++ );
			if( not(ismissing( cola[i] )), b++ );
	);

	pctpass = 100*a/b;
	pctfail = 100 - pctpass;
	
	evallist({a, b, pctpass, pctfail});
);



colp = "param1";

results = count_in_spec(colp, 0.15, 0.30 );
	countpass = results[1];
	countall = results[2];
	pctpass = results[3];
	pctfail = results[4];

show(countpass);
show(countall);
show(pctpass);
show(pctfail);


colp = "param2";

results = count_in_spec(colp, 0.15, 0.30);
	countpass = results[1];
	countall = results[2];
	pctpass = results[3];
	pctfail = results[4];

show(countpass);
show(countall);
show(pctpass);
show(pctfail);

The syntax for extracting LSL and USL properties from a column will be useful.