topic Re: What is the function to calculate the column percentile of a specific number? in Discussions

What is the function to calculate the column percentile of a specific number?

CurseOfLizard13 — Fri, 09 Jun 2023 00:53:12 GMT

Hi,

As an example, I have a data table with column "X" with values "1,2,3,....13, 14, 15".

I understand it is easy to calculate the 20% quantile using function: Col Quantile(:X, 0.2) = 3.2.

Is there a function to calculate the percentile of a number in this column "X", for example 3?

If so, is there a way to calculate it in the "Table Summary" for multiple columns?

Thank you!

Re: What is the function to calculate the column percentile of a specific number?

txnelson — Mon, 01 Aug 2022 16:51:41 GMT

Here is the entry from the Scripting Index for the Normal Distribution function which returns the probability for a given value, with a given mu and sigma.

So if I am understanding your question correctly, you would specify

p=normal distribution(3, col mean(:x), col stddev(:x) );

Re: What is the function to calculate the column percentile of a specific number?

CurseOfLizard13 — Mon, 01 Aug 2022 21:06:38 GMT

Hi, txnelson:

Thanks for your reply!

I am not a statistician, but wonder if there is way to calculate it for data which is not limited to normal distribution.

What I was really looking for is a function doing the reverse calculation of "Col Quantile (:X, 0.2)--> 3.2", meaning f(:X, 3.2)--> 20%. Or mathematically N rows ( where (:X<0.32) ) / N rows(:X).

Re: What is the function to calculate the column percentile of a specific number?

jthi — Mon, 01 Aug 2022 21:25:48 GMT

Maybe

Col Rank(:X) / Col Number(:X)

or maybe you could use CDF()

Re: What is the function to calculate the column percentile of a specific number?

txnelson — Mon, 01 Aug 2022 22:26:55 GMT

Expanding on @jthi idea, here is a function that works. Remember, the value returned is partially made up from a linear interpolation between the adjacent cumulative probabilities.

Names Default To Here( 1 );
dt = 
// Open Data Table: big class.jmp
// → Data Table( "big class" )
Open( "$SAMPLE_DATA/big class.jmp" );

quantile Distribution = Function( {col, value},
	{defaultlocal}, 
	{quant, cumprob} = CDF( Column( col ) << get values );
	result = Interpolate( value, quant, cumprob );
	Return( result );
);

res = quantile distribution( "height", 53 );