topic Exclude rows in Col Quantile Calculation in Discussions

Exclude rows in Col Quantile Calculation

midori555 — Fri, 09 Jun 2023 23:28:57 GMT

Hi,

I was trying to get Col Quantile( :height, 0.5, :class) but it looks like the calculation would still take the excluded rows into consideration.

Is there a way to reject the excluded rows into Col Quantile calculation?

Thanks,

Midori

Re: Exclude rows in Col Quantile Calculation

txnelson — Fri, 12 Jun 2020 09:51:21 GMT

You can check for the row being excluded within the Col Quantile() function. Below is an example

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

dt << New Column( "Col Quantile Height by Age",
	numeric,
	continuous,
	formula( Col Quantile(If(excluded(rowstate(Row())),., :height), 0.5, :age ) )
);

Whenever the excluded rowstate changes for the rows in the data table, the formula will calculate new values

Re: Exclude rows in Col Quantile Calculation

LatentDataTiger — Thu, 21 Mar 2024 23:02:26 GMT

I tried to create a loop to collect the 50th percentile of many parameters using the following code, but both elements in "test_list" are the same (they are both 50% of weight). However, if I run the lines manually, it will work. Can I not use the Col Quantile() function in the for loop?

Names Default To Here( 1 );

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

plot_param={

height,

weight};

test_list={};

for(plotcnt=1, plotcnt<=nitems(plot_param), plotcnt++,

pct_50=Col Quantile( If(excluded(rowstate(Row())),., column(plot_param[plotcnt])), 0.5);

test_list[plotcnt]=pct_50;

);

Re: Exclude rows in Col Quantile Calculation

LatentDataTiger — Thu, 21 Mar 2024 23:04:25 GMT

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

plot_param={
	
	height,
	weight};

test_list={};

for(plotcnt=1, plotcnt<=nitems(plot_param), plotcnt++,

	pct_50=Col Quantile( If(excluded(rowstate(Row())),., column(plot_param[plotcnt])), 0.5);
	test_list[plotcnt]=pct_50;
);

Re: Exclude rows in Col Quantile Calculation

hogi — Fri, 22 Mar 2024 15:22:37 GMT

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
plot_param={
	height,
	weight};
test_list={};

for(plotcnt=1, plotcnt<=nitems(plot_param), plotcnt++,

	Eval (Substitute (Expr(pct_50=Col Quantile( If(excluded(rowstate(Row())),., _col_), 0.5)), Expr(_col_), column(plot_param[plotcnt])));
	test_list[plotcnt]=pct_50;
);

Re: Exclude rows in Col Quantile Calculation

hogi — Fri, 22 Mar 2024 15:56:39 GMT

Due to a typo, I just noticed that Eval (substitute()) is enough, even if you substitute 1 with 1:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
plot_param = {height, weight};
test_list = {};
For( plotcnt = 1, plotcnt <= N Items( plot_param ), plotcnt++, 

	pct_50 = Eval( Substitute( Expr( Col Quantile( If( Excluded( Row State( Row() ) ), ., Column( plot_param[plotcnt] ) ), 0.5 ) ), 1, 1 ) );
	test_list[plotcnt] = pct_50;
);
show(test_list);

Re: Exclude rows in Col Quantile Calculation

hogi — Sat, 20 Apr 2024 16:05:38 GMT

@LatentDataTiger , feedback from JMP Support (TS-00139301)

Yes, this is a bug. The developers have verified it is a caching issue with Col Quantile [& Col Median] specifically. The other Col functions do not have the issue. Using Substitute makes a new expression with each iteration of the loop so that caching does not get used improperly. Thanks for reporting this and I will file a bug report.

So, under the line, it's not Substitute, but the protection via Eval(Expr()) which fixes the bug.

nice example from @Arn_Cota [adapted version] illustrating the effect:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
plot_param = {height, weight};
test_list = {};
For( plotcnt = 1, plotcnt <= N Items( plot_param ), plotcnt++, 
// Without Exclude, returns correct values
	pct_50_v0 = Col Quantile( Column( plot_param[plotcnt] ), 0.5 );

// With Exclude, returns the Quantile for height
	pct_50_v1 = Col Quantile(
		If( Excluded( Row State( Row() ) ),
			.,
			Column( plot_param[plotcnt] )
		),
		0.5
	);

// With Eval and Substitute, returns correct values
	pct_50_v2 = Eval(
		Substitute(
				Expr(
					Col Quantile(
						If( Excluded( Row State( Row() ) ),
							.,
							Column( plot_param[plotcnt] )
						),
						0.5
					)
				),
			1, 1
		)
	);
 
 // use Eval(Expr() to protect the expression against inappropriate caching
 	pct_50_v3 = Eval(Expr(Col Quantile(
		If( Excluded( Row State( Row() ) ),
			.,
			Column( plot_param[plotcnt] )
		),
		0.5
	)));

        // Show different versions
	Show(
		plotcnt,
		Column( plot_param[plotcnt] ),
		pct_50_v0,
		pct_50_v1,
		pct_50_v2,
		pct_50_v3
	);
	test_list[plotcnt] = pct_50_v3;
);

Re: Exclude rows in Col Quantile Calculation

hogi — Sat, 20 Apr 2024 16:07:33 GMT

example/workaround added to