topic Re: Quantiles with missing values in Discussions

Quantiles with missing values

Djtjhin — Fri, 09 Jun 2023 19:49:19 GMT

If I have a column named X with 100 values which is a sequence from {1-100}, and I want to retrieve the 10 smallest values, I know I can use the command below:

dt = current data table();
temp1 = dt:X << get values;
temp2 = quantile(0.1,temp1);
dt << get rows where(:X<temp2);

Let's say I'm replacing the 10 largest values (i.e. 91-100) with missing values, is there a way to get the same 10 smallest values ? I believe the Quantile function will not take consideration of the missing values.

Appreciate the help

Re: Quantiles with missing values

jthi — Fri, 11 Jun 2021 12:42:21 GMT

Do you want to get the rows or values? Here is one example how you should be able to get 10 smallest non missing values

temp1 = Sort Ascending(dt:X << get values); //get values in column Value
temp1 = temp1[Loc(temp1)]; //ignore missing
tensmallest = temp1[1::10]; //get 10 first values in list

Re: Quantiles with missing values

Mark_Bailey — Fri, 11 Jun 2021 12:43:22 GMT

See for yourself.

Names Default to Here( 1 );

dt = New Table( "Untitled 2",
	Add Rows( 10 ),
	New Column( "Full",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] )
	),
	New Column( "Partial",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 3, 4, 5, 6, 7, ., ., .] )
	)
);

values = :full << Get As Matrix;
Show( Quantile( 0.1, values ) );

values = :Partial << Get As Matrix;
Show( Quantile( 0.1, values ) );

The presence of missing values changes the sample quantile, so it will of curse change the rows that are selected.

Re: Quantiles with missing values

Djtjhin — Fri, 11 Jun 2021 14:45:43 GMT

This works for the solution that I was working on. Thanks!