How to convert the percentile value in CDF to be exclusive in R script?

[Wei Keong Tan]

I'm creating a CDF with various percentiles that consist P97, P97.5, P98, P98.5, P99, P99.5 using R script. I would like to know if we want to convert the percentile to be exclusive (PERCENTILE.EXC function in excel), may I know is it change the percentiles / 100 to (percentiles - 0.0001) / 100? (I tried this change, but it doesn't work). I would appreciate any advice or suggestion.

# define a list of percentile values

percentiles = c(97, 97.5, 98, 98.5, 99, 99.5)

# extract unique values of grouping column

groups = unique(df[,groupingColumnName])

# loop through groups

for (gg in groups) {

 # subset the data frame to include only the rows corresponding to the current group

 x = df[df[groupingColumnName] == gg, analysisColumnName]

 # sort the values and remove missing values

 x = sort(x, decreasing = FALSE, na.last = NA)

 N = length(x)

 # loop through the list of percentiles and calculate the corresponding percentile values and store in a list

 percentile_values = lapply(percentiles, function(p) quantile(x, p/100))

 # create a new data frame containing the sorted values and corresponding cumulative probabilities and percentile values

 cdfTable_g = data.frame(

  group = rep(gg, N),

  prob = ((1:N) - 1) / (N - 1),

  value = x

 )

 for (i in 1:length(percentiles)){

  cdfTable_g[paste0("percentile_",percentiles)] = percentile_values[]

 }

 # modify the cumulative probabilities based on the current percentile values

 for (i in 1:length(percentiles)){

  cdfTable_g$prob[cdfTable_g$value > percentile_values[]] = percentiles / 100

 }

 # append the data frame to the master cdfTable using rbind

 if (gg == groups[1]) {

  cdfTable = cdfTable_g

 } else {

  cdfTable = rbind(cdfTable, cdfTable_g)

 }

}

[Gaia Paolini]

it is a lot of code, and without data, context and clarification of the goals it is difficult to debug.

[Wei Keong Tan]

Hi, thanks a lot for your reply. Appreciate it. I have attached .dxp file and data.

I would like to convert the percentile to be exclusive (this is similar to the PERCENTILERANK.EXC function in Excel).

Refer to the data as attached, the 95th percentile of the TotalTestTime (Inclusive) is 375.274.

If we use the PERCENTILERANK.EXC function in Excel to convert the percentile to be exclusive, the 95th percentile of the TotalTestTime is 375.6325.

Therefore, it's interested to know how can we convert the percentile to be exclusive in Spotfire using R.

[Gaia Paolini]

Interpreting your question purely as 'how to calculate PERCENTILE_EXC in R':

The PERCENTILE_EXC formula is for calculating the percentile values.

Using this: https://www.statology.org/percentile-exc-vs-percentile-inc-excel/ , the short answer is to add 'type=6' in your calculation of the percentile values.

So instead of 

percentile_values = lapply(percentiles, function(p) quantile(x, p/100))

you would use this:

percentile_values = lapply(percentiles, function(p) quantile(x, p/100,type=6))

This would give 375.6325 as your 95% percentile.

[Wei Keong Tan]

Hi, that's great! I verified it, it works! Didn't know we can calculate the exclusive quantile through the parameter of quantile()!

Thank you very much Gaia!